db.changelog.20210404-resource-version-table.yml Maven / Gradle / Ivy
databaseChangeLog:
- changeSet:
id: resource-version-table
author: fletch
changes:
- createTable:
tableName: resource_version
columns:
- column:
name: resource_uid
type: char(26)
constraints:
nullable: false
- column:
name: version
type: integer
defaultValueNumeric: 1
constraints:
nullable: false
- column:
name: spec
type: json
constraints:
nullable: false
- sql:
sql: |
insert into resource_version
select lr.uid, r.version, r.spec
from resource r, resource_with_metadata lr
where r.id = lr.id;
- addColumn:
tableName: environment_resource
columns:
- column:
name: resource_version
type: integer
constraints:
nullable: true
- sql:
sql: |
update environment_resource
join resource on resource.uid = environment_resource.resource_uid
join resource_with_metadata on resource.id = resource_with_metadata.id
set environment_resource.resource_uid = resource_with_metadata.uid,
environment_resource.resource_version = resource.version;
- addNotNullConstraint:
tableName: environment_resource
columnName: resource_version
columnDataType: integer
- sql:
sql: |
delete r1 from resource r1
inner join resource r2
where r1.version < r2.version
and r1.id = r2.id;
- dropColumn:
tableName: resource
columns:
- column:
name: spec
- column:
name: version
- dropView:
viewName: resource_with_metadata
- createView:
viewName: resource_with_metadata
selectQuery: |
select
resource.uid,
resource.id,
resource.application,
resource.kind,
json_object(
'uid', resource.uid,
'id', resource.id,
'version', resource_version.version,
'application', resource.application,
'environment', latest_environment.uid,
'environmentName', latest_environment.name,
'deliveryConfig', delivery_config.uid,
'serviceAccount', delivery_config.service_account
) as metadata,
resource_version.spec
from resource
join resource_version
on resource.uid = resource_version.resource_uid
and resource_version.version = (
select max(rv2.version)
from resource_version rv2
where resource.uid = rv2.resource_uid
)
left join environment_resource
on resource.uid = environment_resource.resource_uid
join latest_environment
on latest_environment.uid = environment_resource.environment_uid
and latest_environment.version = environment_resource.environment_version
left join delivery_config
on delivery_config.uid = latest_environment.delivery_config_uid;