db.changelog.20210208-versioned-resources.yml Maven / Gradle / Ivy
databaseChangeLog:
- changeSet:
id: versioned-resources
author: fletch
changes:
- addColumn:
tableName: resource
columns:
- name: version
type: integer
defaultValueNumeric: 1
afterColumn: id
constraints:
nullable: false
- dropUniqueConstraint:
tableName: resource
constraintName: name
- addUniqueConstraint:
tableName: resource
constraintName: resource_id_version_idx
columnNames: id, 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,
'application', resource.application,
'environment', environment.uid,
'environmentName', environment.name,
'deliveryConfig', delivery_config.uid,
'serviceAccount', delivery_config.service_account
) as metadata,
resource.spec
from resource
left outer join environment_resource on resource.uid = environment_resource.resource_uid
left outer join environment on environment.uid = environment_resource.environment_uid
left outer join delivery_config on delivery_config.uid = environment.delivery_config_uid
where resource.version = (select max(r2.version) from resource r2 where resource.id = r2.id)