def update_schema(engine: Engine):
"""
Check and apply any missing schema changes to the database.
This is run by an administrator.
See the `schema_is_latest()` function above: this should apply updates
that it requires.
"""
# This will typically check if something exists (like a newly added column), and
# run the SQL of the change inside a single transaction.
# Empty, as no schema changes have been made recently.
# -> If you need to write one, look at the Git history of this
# function for some examples.
# Post 1.8 DB Incremental Sync triggers
if not pg_column_exists(engine, schema_qualified('dataset'), 'updated'):
_LOG.info("Adding 'updated'/'added' fields and triggers to schema.")
c = engine.connect()
c.execute('begin')
install_timestamp_trigger(c)
install_added_column(c)
c.execute('commit')
c.close()
else:
_LOG.info("No schema updates required.")
def update_schema(engine):
"""
Instead of versioning our schema, this function ensures we are running against the latest
version of the database schema.
"""
is_unification = pg_exists(engine, schema_qualified('dataset_type'))
if not is_unification:
raise ValueError('Pre-unification database cannot be updated.')
# Removal of surrogate key from dataset_source: it makes the table larger for no benefit.
if pg_exists(engine, schema_qualified('uq_dataset_source_dataset_ref')):
_LOG.info('Applying surrogate-key update')
engine.execute("""
begin;
alter table {schema}.dataset_source drop constraint pk_dataset_source;
alter table {schema}.dataset_source drop constraint uq_dataset_source_dataset_ref;
alter table {schema}.dataset_source add constraint pk_dataset_source primary key(dataset_ref, classifier);
alter table {schema}.dataset_source drop column id;
commit;
""".format(schema=SCHEMA_NAME))
_LOG.info('Completed surrogate-key update')
# float8range is needed if the user uses the double-range field type.
if not engine.execute(
"SELECT 1 FROM pg_type WHERE typname = 'float8range'").scalar():
engine.execute(TYPES_INIT_SQL)
if not pg_column_exists(engine, schema_qualified('dataset_location'),
'archived'):
_LOG.info('Applying dataset_location.archived update')
engine.execute("""
alter table {schema}.dataset_location add column archived TIMESTAMP WITH TIME ZONE
""".format(schema=SCHEMA_NAME))
_LOG.info('Completed dataset_location.archived update')
# Update uri indexes to allow dataset search-by-uri.
if not pg_exists(
engine,
schema_qualified('ix_{schema}_dataset_location_dataset_ref'.format(
schema=SCHEMA_NAME))):
_LOG.info('Applying uri-search update')
engine.execute("""
begin;
-- Add a separate index by dataset.
create index ix_{schema}_dataset_location_dataset_ref on {schema}.dataset_location (dataset_ref);
-- Replace (dataset, uri) index with (uri, dataset) index.
alter table {schema}.dataset_location add constraint uq_dataset_location_uri_scheme unique (uri_scheme, uri_body, dataset_ref);
alter table {schema}.dataset_location drop constraint uq_dataset_location_dataset_ref;
commit;
""".format(schema=SCHEMA_NAME))
_LOG.info('Completed uri-search update')
def schema_is_latest(engine):
"""
Is the schema up-to-date?
"""
# We may have versioned schema in the future.
# For now, we know updates have been applied if certain objects exist,
location_first_index = 'ix_{schema}_dataset_location_dataset_ref'.format(
schema=SCHEMA_NAME)
has_dataset_source_update = not pg_exists(
engine, schema_qualified('uq_dataset_source_dataset_ref'))
has_uri_searches = pg_exists(engine,
schema_qualified(location_first_index))
has_dataset_location = pg_column_exists(
engine, schema_qualified('dataset_location'), 'archived')
return has_dataset_source_update and has_uri_searches and has_dataset_location
def schema_is_latest(engine):
"""
Over the lifetime of ODC there have been a couple of schema updates. For now we don't
version the schema, but we do need to check we're running against the latest.
We may have a versioned schema in the future.
For now, we know updates have been applied if certain objects exist.
"""
# We may have versioned schema in the future.
# For now, we know updates have been applied if certain objects exist,
location_first_index = 'ix_{schema}_dataset_location_dataset_ref'.format(schema=SCHEMA_NAME)
has_dataset_source_update = not pg_exists(engine, schema_qualified('uq_dataset_source_dataset_ref'))
has_uri_searches = pg_exists(engine, schema_qualified(location_first_index))
has_dataset_location = pg_column_exists(engine, schema_qualified('dataset_location'), 'archived')
return has_dataset_source_update and has_uri_searches and has_dataset_location