Learn how to write update scripts for databases in a censhare system.

  • Find here more information on the database update process
  • Use new names for modified constraints, indexes, etc, remove the old entry and create a new one
  • Using history records in the db-schema.xml , allows to disable / enable entities which are version depending
  • Do not forget to update the database.version and the database.content.version in the db-schema.xml

Here are some common examples:

  • Removing a feature definition (post)
INSERT INTO asset_ccn_counter SELECT distinct(asset_id) FROM asset_feature WHERE feature='censhare:is-system-module-asset';
DELETE FROM feature WHERE key='censhare:is-system-module-asset';
DELETE FROM asset_feature WHERE feature='censhare:is-system-module-asset';
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.content.version';
COMMIT;

Oracle script examples

  • Adding a new field (pre)
PROMPT Add new field oc_sso_asset_id to table party
DECLARE
  cond NUMBER;
BEGIN
  SELECT count(*) into cond FROM user_tab_cols WHERE column_name='OC_SSO_ASSET_ID' and table_name='PARTY';
  IF (cond = 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE party ADD oc_sso_asset_id INTEGER';
  END IF;
END;
/
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Adding many fields at once (pre)
DECLARE
  cond NUMBER;
BEGIN
  SELECT count(*) into cond FROM user_tab_cols WHERE column_name='ASSET_VERSION_FROM' and table_name='ASSET_FEATURE';
  IF (cond = 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE asset_feature ADD (asset_version_from INTEGER, asset_version_to INTEGER)';
  END IF;
END;
/
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Removing a constraint (pre)
PROMPT Removing key asset_element_fk13
DECLARE
  cond NUMBER;
BEGIN
  SELECT count(*) INTO cond FROM user_constraints WHERE constraint_name='ASSET_ELEMENT_FK13';
  IF (cond <> 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE asset_element DROP CONSTRAINT asset_element_fk13';
  END IF;
END;
/
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Adding a constraint (pre)
PROMPT Add asset_feature_fk10 instead of asset_feature_fk1
DECLARE
  cond NUMBER;
BEGIN
  SELECT count(*) INTO cond FROM user_constraints WHERE constraint_name='ASSET_FEATURE_FK10';
  IF (cond = 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE asset_feature ADD CONSTRAINT asset_feature_fk10 FOREIGN KEY(asset_id, asset_version_from) REFERENCES asset(id, version) DEFERRABLE INITIALLY DEFERRED';
  END IF;
END;
/
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Removing an index (pre)
PROMPT Remove asset_feature_idx1
DECLARE
  cond NUMBER;
BEGIN
  SELECT count(*) INTO cond FROM user_indexes WHERE index_name='ASSET_FEATURE_IDX1';
  IF (cond <> 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE asset_feature DROP INDEX asset_feature_idx1';
  END IF;
END;
/
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Adding an index (pre)
PROMPT Add asset_feature_idx14 instead of asset_feature_idx1
DECLARE
  cond NUMBER;
BEGIN
  SELECT count(*) INTO cond FROM user_indexes WHERE index_name='ASSET_FEATURE_IDX14';
  IF (cond = 0) THEN
    EXECUTE IMMEDIATE 'CREATE INDEX asset_feature_idx14 ON asset_feature (asset_id, asset_version_from, asset_currversion)';
  END IF;
END;
/
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;

PostgreSQL script examples

PostgreSQL newer then 9.4 supports IF EXISTS and IF NOT EXISTS expressions for many types of ALTER queries. May be we will switch to them soon.

  • Adding a new field (pre)
START TRANSACTION;
DO $$
DECLARE
  cond INTEGER;
BEGIN
  RAISE NOTICE ' Add new field oc_sso_asset_id to table party';
  SELECT count(*) into cond FROM information_schema.columns WHERE column_name='oc_sso_asset_id' and table_name='party';
  IF (cond = 0) THEN
    EXECUTE 'ALTER TABLE party ADD oc_sso_asset_id INTEGER';
  END IF;
END $$;
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Adding many fields at once (pre)
START TRANSACTION;
DO $$
DECLARE
  cond INTEGER;
BEGIN
  SELECT count(*) into cond FROM information_schema.columns WHERE column_name='asset_version_from' and table_name='asset_feature';
  IF (cond = 0) THEN
    EXECUTE 'ALTER TABLE asset_feature ADD asset_version_from INTEGER, ADD asset_version_to INTEGER';
  END IF;
END $$;
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Removing a constraint (pre)
START TRANSACTION;
DO $$
DECLARE
  cond INTEGER;
BEGIN
  RAISE NOTICE ' Remove asset_element_fk13';
  SELECT count(*) into cond FROM information_schema.constraint_column_usage WHERE constraint_name='asset_element_fk13';
  IF (cond <> 0) THEN
    EXECUTE 'ALTER TABLE asset_element DROP CONSTRAINT asset_element_fk13';
  END IF;
END $$;
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Adding a constraint (pre)
START TRANSACTION;
DO $$
DECLARE
  cond INTEGER;
BEGIN
  RAISE NOTICE ' Add asset_feature_fk10 instead of asset_feature_fk1';
  SELECT count(*) INTO cond FROM information_schema.constraint_column_usage WHERE constraint_name='asset_feature_fk10';
  IF (cond = 0) THEN
    EXECUTE 'ALTER TABLE asset_feature ADD CONSTRAINT asset_feature_fk10 FOREIGN KEY(asset_id, asset_version_from) REFERENCES asset(id, version) DEFERRABLE INITIALLY DEFERRED';
  END IF;
END $$;
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Removing an index (pre)
START TRANSACTION;
DO $$
DECLARE
  cond INTEGER;
BEGIN
  RAISE NOTICE ' Remove asset_feature_idx1';
  SELECT count(*) INTO cond FROM pg_stat_all_indexes WHERE indexrelname='asset_feature_idx1';
  IF (cond <> 0) THEN
    EXECUTE 'DROP INDEX asset_feature_idx1';
  END IF;
END $$;
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;
  • Adding an index (pre)
START TRANSACTION;
DO $$
DECLARE
  cond INTEGER;
BEGIN
  RAISE NOTICE ' Add asset_feature_idx14 instead of asset_feature_idx1';
  SELECT count(*) INTO cond FROM pg_stat_all_indexes WHERE indexrelname='asset_feature_idx14';
  IF (cond = 0) THEN
    EXECUTE 'CREATE INDEX asset_feature_idx14 ON asset_feature (asset_id, asset_version_from, asset_currversion)';
  END IF;
END $$;
UPDATE database_info SET value='X.X.X.XXXX' WHERE key='database.version';
COMMIT;