db.changelog.20210226-generate-event-columns.yml Maven / Gradle / Ivy
databaseChangeLog:
- changeSet:
id: generate-event-timestamp
author: fletch
changes:
- dropIndex:
tableName: event
indexName: event_uid_timestamp_idx
- dropIndex:
tableName: event
indexName: event_scope_ref_timestamp_idx
- dropIndex:
tableName: event
indexName: event_scope_application_timestamp_idx
- dropColumn:
tableName: event
columnName: application
- sql:
sql: |
alter table event
add column ref_gen varchar(255) generated always as (json ->> '$.ref')
not null
after scope;
- sql:
sql: |
alter table event
add column application varchar(255) generated always as (json ->> '$.application')
not null
after ref_gen;
- sql:
sql: |
update event
set json = json_set(
json,
'$.timestamp',
concat(substring(json->>'$.timestamp', 1, 19), '.000Z')
)
where length(json -> '$.timestamp') = 22;
- sql:
sql: |
alter table event
add column timestamp_gen datetime(3) generated always as (str_to_date(json->>'$.timestamp', '%Y-%m-%dT%T.%fZ'))
after application;
- createIndex:
tableName: event
indexName: event_uid_timestamp_idx
columns:
- column:
name: uid
- column:
name: timestamp_gen
descending: true
- createIndex:
tableName: event
indexName: event_scope_ref_timestamp_idx
columns:
- column:
name: scope
- column:
name: ref_gen
- column:
name: timestamp_gen
descending: true
- createIndex:
tableName: event
indexName: event_scope_application_timestamp_idx
columns:
- column:
name: scope
- column:
name: application
- column:
name: timestamp_gen
descending: true