In this post, I am going to describe the schema maintenance procedures that were developed using Postgres which is a part of an enterprise system platform that has an inherent capability to exchange data between systems.
The platform is based on the principle that we must leverage all possible constraint capabilities in the database to improve the efficiency of developers/users and maintain the database's integrity.
The schema maintenance procedures create the schema based on the data dictionary definition and update it if the definition changes. The procedures also add ‘not null’, foreign key, and unique constraints.
In addition, the spsysSchemaCopy procedure sets up a new schema for a new business unit or organization. It will assign the schema a unique system id from the central data server and embed it in the primary key so that all records in all tables are created with that system id. It also sets up an admin user and organization contact, registers the system on the central server, and copies the core data (like reference tables) into the schema.
If you are interested in checking out the source code for the schema procedures, please click on the following link. www.3d-ess.com\sourcecode and look up the following procedures- spsysSchemaUpdate, spsysNotNullConstraintGenerate, spsysUniqueConstraintGenerate, spsysForeignKeyConstraintGenerate, spsysSchemaCopy
I hope the source code for these procedures will be useful to you in your next project. If you are interested in learning more about the 3D Enterprise System platform, please go to www.3d-ess.com.
Thank you for your attention. I appreciate your comments, questions, and likes.