Continuous integration of database development-Methods and Procedures

This series of articles
Continuous integration of database development-SQL Server deployment and upgrade Tool
Continuous integration of database development-SQL Server database structure comparison
Continuous integration of database development-Methods and Procedures
Continuous integration of database development-Liquibase introduction and application
Continuous integration of databases-project configuration of CruiseControl. Net

It is decided to use Castle ActiveRecord to develop a new system. Based on the object-oriented idea, the database design can be converted to the code design of the object, and the continuous integration and development of the program can be naturally introduced into the database development. Supplemented by liquibase, the continuous integration and development of databases is feasible, and the database deployment problem is also solved. After liquibase is introduced, database version management, deployment, and upgrade are simplified.
Of course, if you do not use ActiveRecord to develop a database, you can also use liquibase to achieve your own continuous integration. You only need to make some adjustments, change the part of the entity class in my process to the design and test of the database table.

1 Development Method

Use Domain Driven Design to develop databases and Design database tables in an object-oriented Design method. The specific methods include:

• Directly design the database entity class, and then use ActiveRecord to create a database table
• All attributes of the database table (the column type, length, constraints, indexes, relations between tables, and so on are all designed in the Entity class)
• The database access layer is implemented based on the entity class, and the business logic is prioritized by the database access layer through code. In addition, it is implemented in the stored procedure.

2. database development architecture

• Each database developer must design, code, and test on a local database instance.
• The continuous Integration Server triggers the continuous integration process by monitoring user submission on the SVN server, and verifies and deploys the new database structure on the baseline database.
• Continuous integration of baseline databases on databases
  • Its structure (including static database data) is the structure of the currently released system.
  • Only the continuous integration process can modify the baseline database
  • Developers can modify the database only through the continuous integration process.
  • Developers can regard the baseline database as a product database and obtain the database deployment script by comparing it with the local database.

3. Database Development Process

3.1 local development process

• All designs and tests are performed on the local database.
• Each access layer function and more complex entity class (such as foreign key relationships) should have a test case and must pass the test
• Database deployment script generation
  • When designing databases for multiple users, you must promptly obtain the SVN deployment script and execute it locally, consistent with the baseline database.
  • Use liquibase to compare local databases with baseline databases and generate deployment scripts
  • Note: The released database deployment script cannot be changed.

3.2 continuous integration process
3.2.1 Trunk Process

• This process first upgrades the baseline database GTCA, and then performs continuous integration on the baseline database (to ensure that the new assembly and upgrade script are compatible with the released product system)
• Any process error in may cause continuous integration failure. The database is rolled back to the status before integration.
• You can view all database structure modification logs in the database documentation.

3.2.2 Studio process

• This process is tested by creating a temporary database GTCATest.

4 encoding principles

• Discard the domain, rule, and default of SQL Server (the rule and default are not supported in 2005)
• Replace domain with the Nested Data of ActiveRecord
• Implement rule and default by defining ActiveRecord of Nested Data
• Exercise caution when using views and stored procedures

Considering my current new system development, this method and process are still feasible. It is estimated that there will still be many problems in practice, and the school friends who wish to discover the problems will not be enlightened.

Update 20080620 for more information about liquibase, see database development continuous integration-Liquibase introduction and application
Update 20080625 fixes the process based on actual application conditions and releases the configuration of continuous database integration-CruiseControl. Net.
The major correction is to consider that the test code can be used in both the Studio project and the Trunk project on the continuous Integration Server locally and meet the following requirements:
* The test code in the Trunk project is directly tested on the upgraded baseline database (to ensure that the newly released code and scripts are compatible with the released product database). Therefore, the database structure cannot be modified.
* The Studio project is tested on the test database.