Project.net database scripts rewriting proposal

  1. Project.net database scripts rewriting proposal
      1. New database scripts folder structure
      2. Scripts structure
    2. Changing/updating existing scripts
      1. Tables changing
      2. Views changing
      3. Package changing
    3. Preparing database script for a new release

Here is suggestion how we can improve current database scripts. Right now we have about 900 SQL scripts and all of them we execute when create database from scratch. Most of those SQL scripts are almost the same, so we just need to keep the last version of the script for current application version. For example, for latest (8.3) version of application we don't need to execute pkg_project.sql script from version 2.0.3 and then to execute it 16 more times in order to get its latest version. The same is with all other scripts.
I want to suggest for new application version new approach for SQL scripts organization inside the SVN repository and changes in those scripts.
For new version (8.5.0) of application I suggest this organization. Every new application version will have 2 separate parts for SQL scripts.

  • First part will be for database creation from scratch. That part will be used for new customers. It will contain all SQL scripts needed in order to application work properly (SQL scripts for tablespaces, users, privileges and all database objects). In this way we will only need to execute several database scripts in order to create database we need. We can keep batch/shell script configuration in order to keep consistency in SQL scripts.
  • Second part will be only for application upgrade from previous version to current one. Here will be placed only changes made in current database version. So for example, if we add new token in application we will not delete entire PN_PROPERTY table and then insert more then 6000 records for each version of application but we will add only new records. If we update some existing record in database then here will be placed SQL update statement.

New database scripts folder structure

new -- Main folder for new database scripts. This folder will reside as a subfolder in every release.

admin -- Folder where we have scripts for tablespace and user creation for application. Those scripts are executed first.
packages -- Database packages related files are placed here. Every package has two files one for body and one for specification with extension bdy and spc, respectively.
sequences -- Database sequences. Every sequence has its own script.
synonyms -- Database synonyms. There are synonyms for packages, tables, sequences and views and each of them has its own script. All synonyms are PRIVATE.
tables -- Table related scripts. Here we have scripts for tables creation, population tables with data. Indexes are also created here, they are not in separate script.
triggers -- This folder contain scripts for triggers. Each trigger has its own script with extension trg.
views -- This folder contain scripts for views. Each view has its own script with extension vw.

Right now, all new database scripts are created and placed in core/db/oracle/create-scripts/versions/8.5.0/new folder. At the moment those scripts exists only in trunk, and only version for Windows is available, Linux based version will be developed shortly.

Scripts structure

In the folder new there are two BATCH files(it will be two shell scripts) pnetDB.bat and pnetMasterDBBuild.bat. They are the same as those old BATCH scripts we already have for creating new database with old scripts. Main script is pnetMasterDBBuild.bat and only that script user should modify. There are several properties to set inside that BATCH file and explanations with examples are inside. When user sets all those properties properly, then it is ready to start this script. This scirpt will create all database object and will insert all data.

new -- Main folder for new database scripts. This folder will reside as a subfolder in every release.

pnetMasterDBBuild.bat -- Folder where we have scripts for tablespace and user creation for application. Those scripts are executed first.
pnetDB.bat -- BATCH file which actually executes all SQL scripts, called from pnetMasterDBBuild.bat. It will be merged with pnetMasterDBBuild.bat and removed shortly.
main.sql -- As its name says it is main script. This script executes all other scripts in this folder in proper order.
tables.sql -- Script which calls all other scripts in tables subfolder.
packages.sql -- Script which calls all other scripts in packages subfolder.
sequences.sql -- Script which calls all other scripts in sequences subfolder.
synonyms.sql -- Script which calls all other scripts in synonyms subfolder.
triggers.sql -- Script which calls all other scripts in triggers subfolder.
views.sql -- Script which calls all other scripts in views subfolder.

First when pnetMasterDBBuild BATCH file is executed database tablespaces are created, and there will be placed all database objects for the application. After that database users are created. Right now we have two database users PNET and PNET_USER. Tablespaces and users creates database admin, and all other actions should be performed as PNET user. PNET user owns all objects and PNET_USER uses private synonyms to access them. This is done automatically and developer don't have to take care about it. Scripts for these actions are placed in admin subfolder.
In the core/db/oracle/create-scripts/versions/8.5.0/new folder there are several SQL scripts. You can see existing scripts and their structure above. Main of them is main.sql. This SQL script calls all the other scripts in the proper order. The other scripts which has the same name subfolder calls additional SQL scripts placed in those subfolder. So for example, main.sql calls views.sql scripts in the same folder, and views.sql scripts calls additional scripts for view creating placed under views subfolder in folder core/db/oracle/create-scripts/versions/8.5.0/new.
Some files are used more often then other ones, so because of that I've excluded update_system_properties.sql script from main script for tables population with data. This script is placed in core/db/oracle/create-scripts/versions/8.5.0/new/tables folder. In the same folder there are two more scripts, data.sql and tables.sql. First one contain insert SQL commands for data inserting into tables and the second one contains DDL commands for tables creation.
All SQL scripts are well commented and comments writing in all modifications is a must.
VERY IMPORTANT: BATCH file at the end executes versions/8.5.0/prm_db_patch_8.5.0.sql script (in the future it will be versions/x.x.x/prm_db_patch_x.x.x.sql script) so all changes developers want to perform should be placed ONLY in scripts which are executed/called from this script (prm_db_patch_x.x.x.sql)). All changes are performed ONLY in one place.
We worked this way so far, so we'll continue to work that way. It is NOT aloud for developers to change database scripts inside versions/8.5.0/new folder (versions/x.x.x/new in the future).

Changing/updating existing scripts

Right now this kind of architecture isn't the best because you'll have to change all scripts in 2 places. One is in the main folder for that application version and the second one is in "new" subfolder. For example, for version 8.5.0 you will have to change database scripts in core/db/oracle/create-scripts/versions/8.5.0 folder and in new scripts in core/db/oracle/create-scripts/versions/8.5.0/new subfolder. This wont be more efficient during development time but it will be much more efficient to the customers who will use those scripts. There are few rules all developers will have to know during the SQL scripts changing:

  • All changes will have to apply in one and only one place and that is script for database update, scripts which are called from prm_db_patch_x.x.x.sql script.
  • In the upgrade part in update_system_properties.sql file will be only new properties, all existing ones will be deleted. This will apply from the next version, for the version 8.5.0 will stay as it is. There are no need to have more then 6000 properties in the upgrade script when only a 20-30 new properties are added.
  • Test all scripts you create. It is very important if you don't test it to know what you are doing. If you are not sure about some action contact me, or some other person responsible for the database. Update/create scripts don't have any errors now and we've to make sure to keep it that way.

Tables changing

Every script for tables related changes should be placed in this location core/db/oracle/create-scripts/versions/x.x.x/tables. If this folder doesn't exist (as it would be in the very beginning) create it. Scripts which create new tables should be named with prefix "cr_" and followed by table name, for example, cr_some_table_name.sql. This script should contain comments for the table name, not just comments in the script but comments which will be displayed during script execution, for example:

prompt
prompt Creating table SOME_TABLE_NAME
prompt ======================================
prompt

All tables should be placed in tablespace DATA01, so please pay attention and add this clause in table creation scripts.
Indexes related to table should albe be placed in this script. Indexes are placed in tablespace INDEX01 so you need to specify this detail when you create new index.
Synonym should be placed also in this script. There are no need to keep synonym separated from the table. One important note is to always create synonym with clause "CREATE OR REPLACE PUBLIC SYNONYM", otherwise we'll have a conflict during second execution of this script.
If you are altering existing table then scripts should be named with prefix "altr_" and then table name, for example, altr_some_table_name.sql.
Comments are mandatory for indexes and synonyms too, not just for table.
If there are some data you need to insert into new/existing table, then those scripts should have prefix "insert_" followed by table name, for example, insert_some_table_name.sql.

Views changing

Every script for view related changes should be placed in this location core/db/oracle/create-scripts/versions/x.x.x/views. If this folder doesn't exist (as it would be in the very beginning) create it. Scripts which create new views should be named with prefix "cr_" and followed by view name, for example, cr_some_view_name.sql.
Scripts which alter existing views should be named with prefix "altr_" and followed by view name, for example, altr_some_view_name.sql. Please note that if you want to alter view you need to use "CREATE OR REPLACE VIEW" clause, otherwise we'll have a conflict in code. For all view changes you'll need current view definition. Current view definition you can find in core/db/oracle/create-scripts/versions/x.x.x/new/views folder.

Package changing

If you need to change some code in procedure then you should create (if already doesn't exist) folder packages, in this location core/db/oracle/create-scripts/versions/x.x.x/. So there you will need to place your changed package. Current package code you can find in core/db/oracle/create-scripts/versions/x.x.x/new/packages folder. Please pay attention that for each package you'll have two files, for body and for specification. You'll need to copy those into core/db/oracle/create-scripts/versions/x.x.x/packages folder, and then to apply your changes.

Preparing database script for a new release

For a new application release database scripts will have to be merged. This means that all scripts from upgrade part will have to be merged with scripts for database creation from a scratch. So in the very beginning of the new release we'll have folder/file structure like this:

x.x.x -- Subfolder of folder versions. It is created for new version of application.

new -- Folder which will contain database scripts for database creation from scratch.
prm_db_patch_x.x.x.sql -- Database script which will perform database update from latest released version to current x.x.x version.

One important action we need to perform before moving scripts to a new version is to change script name in pnetDB.bat to a new prm_db_patch_x.x.x.sql we have now for a new version. This is a last executed script so we need to point at the right one, otherwise we'll execute upgrade for version already released, previous version.