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;