The current process of deployment custom project Database Queries which are stored in install_schema.sql/install_data.sql file under custom/install folder is very cumbersome and unfriendly. This is the case since developers have to remember which of the SQLs weren't executed on their database yet. Additionally, it's kind of painful to constantly Import language pack file.
To summarize all this we are looking to have a special OnDeploy event which will perform all of the above tasks and more.
Here is a summary and description of the process:
1. Task(s) "roll-out" to DEV or UAT server process which will happen using special Project Update script
2. As part of this process there is a need to run DB updates, this will be done by triggering DB deployment script which will execute all new SQL statements (described below).
3. All custom (project specific) SQL statements will be stored in custom/install/upgrades.sql file (instead of custom/install/install_data.sql / ...schema.sql) along with the special Comments.
4. Comments will have the following format:
#REV_123(121,123): Adding new section to sub-menu (13324)
where:
- REV_123 - shows DB revision ID (123 is INT set by developer)
- (121,123) - list of other DB revisions that current DB revision is depended on; basically if 121 and 123 revisions are NOT applied and we try applying 123 then some SQL in that revision will fail.
- Adding new section to sub-menu (13324) - a task from In-Business with it's corresponding number so developers know what this DB revision does.
5. 1 Comment will represent 1 DB revision and 1 task in In-Business and can have 1 or more related SQL statements
6. Each DB revision must be UNIQUE across current custom/install/upgrades.sql file. This means that developers ALWAYS must perform an Update on their Working Copy to check if new DB revisions came in and then increase their DB revision numbers accordingly before committing to Repository!
7. There will be a new AppliedDBRevisions field added to the Modules table to store the list of already applied DB revisions. This will make sure the deployment process will NOT run the same SQLs more then once in case if Development Manager need to roll out some particular Tasks and not the entire trunk (latest version of the project)
8. Create special script to deploy language "english.lang" which is part of custom module.
Here is a task and patches:
0001034: Improving Project Deployment Processes to DEV or LIVE servers
DA