Configuration Management database version control policy

Software configuration management (software MANAGEMENT,SCM) is an integral part of defining software, versioning changes to each part, and maintaining version associations between different parts, To make the software in the development process any time the content can be traced to the management process. Software configuration management usually includes version control, change control and process support three large aspects, involving configuration items, workspace management, version control, change control, status reporting, configuration audit and other specific activities, software configuration management generally throughout the software development life cycle and various knowledge areas, this article does not do a specific deployment.

The database version is one aspect of the development process that needs to be controlled, but the actual operation often does not use the configuration management idea to unify the management, especially for the research and development management not perfect team. This paper focuses on the topic of configuration management and discusses the database version control strategy in the development process. Start with the concept of database version, identify ideas and goals, and provide workflow and practice patterns.

A About database versions

There are three main concepts about the database version under Configuration management:

1. Configuration Items

Database-related configuration items include all database elements, such as model, DDL, DML, and various configuration files, which should be managed uniformly under the configuration management tools (common such as SVN, Git).

2. Change Set

Change set is the smallest unit of database changes, and a changeset is a script file in physical representation. The initialized database is updated uniformly with the change set, and each change set should have a corresponding rollback script, which is used for rollback if the update fails.

3. Baseline

Baseline is a baseline, which is a "snapshot" of a database at a specific time, providing a standard and starting point for subsequent work, typically establishing a database baseline based on the scope of the feature release.

Database versioning is often not as easy to control and manage as code versioning, and generally does not form a unified understanding of the development team, resulting in a service release process where only developers who are very familiar with the system perform database update operations by hand, with low efficiency and error-prone On the other hand, it is not conducive to teamwork. Here we would like to emphasize the importance of database versioning, personal summary of the following 6 points:

• Information Transparency Tracking: the role of a version is to provide a unified view to ensure that everyone knows what state the database is in, and how the information is transparent and tracked
• Early detection of problems: With the database version, the developer can use version information to determine whether the update is reasonable and to avoid exposing the problem during the operation of the database.
• Reduce the probability of error: If the database version information can be clearly visible, all developers can explicitly on the basis of this version of information on the next step, rather than through a variety of error-prone implicit information to judge
• Lower maintenance Costs: Unlike code, maintenance is a difficult task, and database version information provides a starting point for effective database maintenance.
• System Baseline Management: According to the concept of configuration management, we can have a baseline with a version, so the database version provides the basis for configuration management related work
• Incremental iterative Evolution: Changeset is actually an incremental idea, which is to ensure that the database meets certain functions from a steady state to the next stable state through each change set update.

Two Ideas and goals

In view of the characteristics and importance of database version control, our ideas:

• Development deployment separation: the development environment, test environment, and production environment strict separation is the first step in database versioning, usually there is no problem with the separation of the production environment from other environments, but also to ensure the separation of the development environment and the test environment. The separation of the development environment and the test environment is to ensure that database versioning in the development process gets the first round of validation in the test environment, avoiding problems in the production environment
• Version visible: version numbers ensure that everyone is visible during development, testing, and implementation the basis of database version control
• Version consistent: Version consistent refers to the system version to meet the equation, as follows:

• Version can be rolled back: Support version rollback to stable version when there is a problem with the latest version after update
• Version compatible: Version compatibility is an extensibility feature that can be used as needed. The version-compatible scenario is that if this update fails, you can ensure that the system is running correctly by not rolling back the database version, or by server version rollback, primarily to avoid unnecessary database rollbacks. In practice, the following ideas can be used: the server version and database version Two version information, a database version corresponding to a number of valid server versions, as follows:

Both the server version and the database version are added at the time of publication, and if the server version is located within a reasonable server version interval (system002~system006) for that database version, the database version can not be rolled and the reverse must be rolled back.

To meet these ideas and goals, we need to design database version elements, including:

• Version number: Unique number representing the current version of the database
• Version check: Each update and rollback, the version check to determine whether the target version of this update/rollback is the current database version, if not, this operation is not valid
• Version update: Database and version update operations after a successful version check
• Version rollback: If there is a problem with the update, restore the database to the previous stable state by version rollback
• Version baselines: Baseline management of a database through configuration management tools

At the same time, there is an important concept here is contraction scripts and expansion scripts, the former refers to the database initialization of the script content, the latter is the database after the initialization of all the incremental script content. Thus, each version baseline can be seen as contraction scripts, and each change set is actually expansion scripts. Three Process and Practice

This section, in conjunction with specific operating modes and practices, expands the database version features in detail and provides examples of subsequent SQL statements for thought-only reference and informal environment instances.

1. Version number

We create a version table to store and manage the version number, which is included in the version table:

• Database version: Updated every time
• Minimum server version: not necessarily updated
• Description of version update: Description of this update

When you create a version table, the version number is initialized at the same time, and the version table creation process under MySQL is as follows:

CREATE TABLE ' db_version ' (' database_version ' varchar (') ' is not null, ' min_server_version ' varchar (in) NOT NULL, ' Update_de Scription ' varchar ($) Not NULL) Engine=innodb DEFAULT Charset=utf8; Insertinto ' db_version ' VALUES (mc-1.0.0-0001, mc-1.0.0-0001, ' system initialization ');

With the release table and version compatibility Principles, service deployment fails if the current server version < minimum server version is available.

2. Version Verification

The version check compares the target version of this update to the current version in the database, and if the inconsistency breaks the update, throws an error, and the script reference for version checking through the stored procedure under MySQL:

CREATE PROCEDURE mobileclinic_check_version (p_pre_version varchar)   BEGIN     DECLARE l_ver_name VARCHAR (30) ;     DECLARE l_pre_version  VARCHAR (a);     SET l_ver_name = ' mobileclinic ';     SELECT database_version into l_pre_version from db_version WHERE NAME = l_ver_name;     SELECT l_pre_version as ' current version ', p_pre_version as ' target version ';               IF p_pre_version <> l_pre_version then         select Mysql_error ();     END IF; END;

3. Version update version update is compared to the old version number, update the new version number and description, and update the server minimum version number as needed, such as:

Call Mobileclinic_check_version (' mc-1.0.0-0002 '); update db_version d set d.database_version = ' mc-1.0.0-0003 ', d.MIN_ server_version = ' mc-1.0.0-0003 ', D.update_desc = ' new table ', D.update_time = Sysdate ();

4. Version rollback

Version rollback is compared to the new version number, rolling back the old version number, updating the description, and rolling back the updated content, such as:

Call Mobileclinic_check_version (' mc-1.0.0-0003 '); update db_version d set d.database_version = ' mc-1.0.0-0002 ', d.MIN_ server_version = ' mc-1.0.0-0002 ', D.update_desc = ' update rollback ', D.update_time = Sysdate ();---delete the updated data item--alter table TEMP Drop Co Lumn XXX;

5. Version baselines

The management policy for a version baseline is as follows: one that can be published to a version that is incrementally released to another

6. Principles of Database version control

The principles of database versioning are summarized as follows:

• Test process = Release process
• Determine baseline by function
• Contraction script, with incremental, including creation (Initialize DDL), initialization (initialize DML), and Cleanup (empty script)
• Expansionscript, change set as the smallest unit for submitting Tests and releases

7. Workflow for Database version control

The Workflow for database versioning is as follows, with each step referencing the literal meaning:

8. Update script (change Set) definition

The change set can be defined in the following ways:

• Name: Cs_baseline_yyyymmdd_patchno.sql, Example: Cs_tanggula2_20121223_01.sql
• Step: Update target database version check; ddl/dml update; Database (or minimum server) version update

9. Rollback script (roll back) definition

Roll back can be defined in the following ways:

• Name: Rb_baseline_yyyymmdd_patchno.sql, Example: Rb_tanggula2_20121223_01.sql
• Step: Rollback Target database version check, DDL/DML rollback, database (or minimum server) version rollback

10. Database versioning and configuration management in a configuration management tool such as SVN, configuration items can be managed in the following hierarchical management ways:

Four Process automation

Individuals do not advocate the process automation of database versioning, because automation is sometimes inflexible, and automation tools for databases are not very mature yet, use caution. The best database Automation tool on the market today should be liquibase (http://www.liquibase.org), supporting multiple databases, multi-format changeset, and roll-back. Liquibase data updates and rollback operations by using formatted SQL change set, the official example is as follows:

At the same time, liquibase can be seamlessly combined with Maven for the integration of the build life cycle:

About Liquibase This article does not expand, you can try as necessary.

Five Summary

Configuration management is the role of version traceability, process repeatability and process can be automated, database version control as a research and development team software configuration management of a component should also be managed, this article provides some ideas and methods for everyone to reference.

Configuration Management database version control policy