Online Documentation for SQL Manager for PostgreSQL

Change Management

Database Change Management

Change Management integrated into version control system (VCS) allows you to:

• manage database related development processes;
• remember database states;
• get a script with differences between two database states;
• test generated scripts on a test database;
• rollback database to any of its previous versions;
• browse history of database and/or object changes;
• store any SQL scripts in version control system repository of a database.

Change management makes the process of deploying changes and updates from development to testing and production environments easier and more controllable.

Supported VCS: Concurrent Versions System (CVS), Subversion (SVN), Visual SourceSafe (VSS), Team Foundation Server (TFS).

 

This tool can be useful not only for development teams, but also for single developers, who are in need of a mean for managing database versions, or whose databases have complex business logic in procedures, triggers etc.

 

Change management tools can be accessed through the respective section of database context menu or Tools section of the main menu.

 

 

To enable Change Management for a database you need to define connection settings for the VCS and create database repository. Open the Database Registration Info dialog at the Change management tab and launch the Version Control Repository Management Wizard for this purpose.

 

What VCS stores

Database VCS repository stores:

1. All database objects in XML files. All the object properties that affect object definition are recorded into these files
2. SQL scripts with all changes in the database
3. Database backups created with each label
4. User can add custom files to VCS (so-called shared scripts)

 

SQL manager automatically records each changing of a database object (made in object editor, or by SQL script) to the respective file and to database history and then commits these changes to VCS.

 

If database was changed in other program, not in SQL Manager, Check Version Control Repository Wizard provides resolving of differences in database and its description in the VCS repository. It is strongly recommended to launch Check Repository Wizard to reveal and remove such differences.

 

Repository stores metadata only, not data.

 

Branches, labels, tags

Change Management tool allows you to create database branches. Database branch is a copy of original DB and corresponding branch in the database repository. Database that has been originally added to VCS is a trunk branch of the repository. On branch creation any PostgreSQL server can be specified as destination for created branch database if its version coincides with the version of original server.

 

Database tags and labels can be created as well. Database tag is a mark for all database files in VCS repository. Using tags is recommended for marking definite database state or version. This provides possibility to get differential script to this state.

 

Database label is a database tag plus database backup copy. It enables restore of the database to the labeled state. Label is always created when creating a branch. In this case label gets the name of the branch with “_AutoLabel” ending. During initial creation of database repository, the label named “Trunk_AugoLabel” is created automatically.

 

Get Change script

One of the key features of Change Management is the possibility to get script of database changes from tag/time point/current database state to another tag/date/current database state. Script can be generated in two ways: differential or step-by-step.

 

Differential script is a result of comparing of two database states. Step-by-step script is an aggregate of all database changes occurred in a period. Step-by-step script can generated only for two contiguous database states and only in forward direction. For example, such script can be generated for two database states within a single branch. Step-by-step script contains all SQL statements that have been performed from start time point to destination one. It means that all intermediate changes of an object will be included to script. Instead of step-by-step script, differential script contains only sum of these changes. Step-by-step scripts, as opposed to differential ones, can contain DML statements in addition to DDL statements. Using of step-by-step scripts is more secure if all changes to database have been made in SQL Manager only.

 

Change Management tools

Tools available:

1. Check Version Control Repository Wizard. Defines whether object description in repository matches object properties in database. If differences persist, wizard corrects repository in order to eliminate them and adds the script with missing statements to DB history.
2. Create Tag Wizard. Provides tag, label or branch creation. Also allows restore of database from an existing label. Such database will not be included in change management, and can be used, for example, for testing scripts.
3. Get Change Script Wizard. Enables getting script with changes from tag/date/current database state to tag/date/current database state.
4. Release New Version of Database Wizard. Automates intermediate processes that are required when releasing new version of database. Allows to get script with changes between previous version of database and current one and to test this script on previous database version. The wizard includes the following operations:
5. 1. VCS repository is checked to ensure that description exactly matches current database state (same as Check Version Control Repository Wizard performs).
   2. Current database state is marked in the VCS repository with tag or label. This provides ability to get change script with possible additional changes.
   3. Script with changes between previous version of database and current database state is generated.
   4. Test database is created from for example an existing label. Generated scripts are tested on this database. After script is executed, test database is compared with current database state to ensure that there no differences between them.
5. Database History. Shows all changes of database in specified branch. History is a list of scripts performed on database.

Object History. Shows all changes of a DB object similar to file history in VCS.

 

Connect to VCS

 

To access change management tools:

Select the Tools | Change Management item of the main menu

or

Select the Change Management item from the database or host context menus.

 

For a single database object only object history can be browsed.

 

 

---