Catalogversion tool for database change management l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

CatalogVersion Tool for Database Change Management PowerPoint PPT Presentation


  • 128 Views
  • Uploaded on
  • Presentation posted in: General

CatalogVersion Tool for Database Change Management. Jacob Nikom. November 8, 2010. Outline. Revision Control System Basics How software developers track changes in their source code Major Revision Control operations Intro into Database Change Management Version Control of Database Changes

Download Presentation

CatalogVersion Tool for Database Change Management

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Catalogversion tool for database change management l.jpg

CatalogVersion Tool forDatabase Change Management

Jacob Nikom

November 8, 2010


Outline l.jpg

Outline

  • Revision Control System Basics

    • How software developers track changes in their source code

    • Major Revision Control operations

  • Intro into Database Change Management

    • Version Control of Database Changes

    • Table ‘catalog_version.version_files’

    • Table ‘catalog_version.revision’

    • Database Delta Scripts

  • CatalogVersion Program

    • CatalogVersion Tool Command

    • CatalogVersion Tool Design

    • Why not to use Java Runtime.exec() method?

    • CatalogVersion Source Code

  • Summary


Revision control system basics l.jpg

Revision Control System Basics

How software developers track changes in their source code

Checkins:

The simplest scenario is checking in a file (list.txt) and modifying it over time

File list.txt


How software developers track changes in their source code l.jpg

How software developers track changes in their source code

Checkouts and Editing:

I may have to check out, edit and check in


How software developers track changes in their source code5 l.jpg

How software developers track changes in their source code

Diffs or Deltas:

The trunk has a history of changes as a file evolves. Diffs or Deltas are the changes I made while editing


Intro to database change management l.jpg

Intro to Database Change Management

  • Reasons for database change

    • Database evolution due to requirement changes

    • Team of developers working on one central database (merging problem)

    • Team of developers using multiple local databases (synchronization problem)

  • Basic questions to answer

    • What version a database is in a particular environment?

    • How to synchronize multiple databases?

    • Who made changes to your database objects and when?

    • Do you have an audit trail recording what changes have occurred and at what point in time?

    • Could you if error occurs, back-out the corrupt version and rollback to a known state of integrity?

  • Database Change Management Procedures

    • Create a database change log

    • Create database delta scripts

    • Use a naming convention for the scripts

    • Apply the scripts

    • Check in the script into Version Control System


Version control of database changes l.jpg

Version Control of Database Changes

Volume serial number is 00350032 1C00:4ED7

C:.

├───1.0

│ |-aaa_install_sequence_1.0.txt

│ |-mysql_user_flush_privs.sql

………………………………………………………

│ |-nops_update_patch_v1.1.1.sql

│ `-nops_update_patch_v1.1.2.sql

├───1.1

│ |-aaa_install_sequence_1.1.txt

│ │-cybs_00300_ddl_otf_alter_table_mpi_order.sql

……………………………………………………….

│ `- cybs_01000_ddl_dmce_alter_table_content.sql

├───1.2

│ │-aaa_install_sequence_1.2.txt

│ │-nokia_policy_02-18-2010.sql

………………………………………………………

│ │-redb_policy_02-18-2010.sql

│ `-reuser_ddl_add_reuser_and_privileges.sql

├───1.3

│ │-aaa_install_sequence_1.3.txt

│ │-otf_ddl_callbackserver_config1.sql

……………………………………………………..

│ │-otf_dml_mpi_ignore_config.sql

│ `otf_dml_mpi_ignore_data_delete.sql

CatalogVersion table

Repository

CatalogVersion

tool

Revision-controlled SQL script directory

This is compact, precise and revision controlled representation of the database state


Table catalog version version files l.jpg

Table ‘catalog_version.version_files’

Each SQL script is delta

CatalogVerion tool does not overrides existing files

This feature allows to build new data on the top of old ones


Table catalog version revision l.jpg

Table ‘catalog_version.revision’

CatalogVersion tables

Repository

One-to-one mapping between database state and source code state

Source code

CatalogVersion

tool

Every time when source code changes, CatalogVersion tool runs using existing list of SQL scripts


Database delta scripts l.jpg

Database Delta Scripts

Content explanation

Execution order number

Schema or product name

Creation date

Content category

File name convention

redb_00300_dml_insert_data_into_all_redb_tables_10_23_2010.sql

1. Schema or product name

  • cybx – Cybersource – related file. It affects NOPS 1.0 schemata and tables

  • redb – files affecting NOPS 1.2 schema, tables, columns, foreign keys and other constraints. Also deal with stored function in NOPS 1.2

2. Execution order number

  • Through order of the SQL scripts execution. It is incremented in hundreds allowing to insert additional numbers.

3. Content category or sql file classification

  • dcl – file affecting users and their privileges (Data Control Language)

  • ddl – file affecting schema, tables, columns, foreign keys and other constraints. Also deals with stored procedures, stored functions, triggers and indexes (Data Definition Language)

  • dml – file affecting the data (static or lookout data) (Data Manipulation Language)

  • dtl – file affecting the transactional data (Data Manipulation Language)


Catalogversion program l.jpg

CatalogVersion Program

  • CatalogVersion tool is Java program (JDK 1.4 and better)

  • Applies SQL scripts to the MySQL server

  • Registers SQL script in the catalog_version schema (unique name)

  • Needs four JAR files to run


Catalogversion tool command l.jpg

CatalogVersion Tool Command

To run the CatalogVersion tool use the command:

java -jar CatalogVersion.jar -u username -p password -h serverIP -f sql_file_name -c “Comments”


Catalogversion tool command cont l.jpg

CatalogVersion Tool Command (cont.)

File update_local_server_sql_files_10_25_2010_dfile.bat content:

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/1.0/install_sequence_1.0.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/1.1/install_sequence_1.1.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/1.2/install_sequence_1.2.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/seed/install_sequence_seed.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/1.3/install_sequence_1.3.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/MIDScripts/install_sequence_mid.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 –f ./DBCreate_10_25_2010/index_scripts/install_sequence_index.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/utf8_conversion/install_sequence_utf8.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/1.4/install_sequence_1.4.txt

java -Dfile.encoding=UTF-8 -cp .;./log4j-1.2.15.jar; -jar CatalogVersion.jar -u root -p password -h 10.48.92.153 -f ./DBCreate_10_25_2010/2.0/install_sequence_2.0.txt

File install_sequence_1.2.txt content:

# These are SQL files for execution

reuser_ddl_add_reuser_and_privileges.sql

redb_00200_ddl_create_all_redb_tables.sql

redb_00400_dcl_add_execution_permission.sql

redb_00500_dml_set_receipt_number_to_zero.sql

redb_00600_ddl_function_get_next_receipt_number.sql

redb_02500_ddl_payment_provider_create_unique_key_01_19_2010_01.sql

redb_03500_ddl_payment_method_include_add_column_01_22_2010.sql

redb_04200_ddl_payment_provider_receipt_suffix_country_02_03_2010_02.sql

redb_policy_02-18-2010.sql

nokia_policy_02-18-2010.sql


Catalogversion tool design l.jpg

CatalogVersion Tool Design

Five Java files

CatalogVersion.java – main driver

DbmsConfig.java – command linhe parsing, static data repository

DbmsConnector.java – manages database connection

DbmsInitializer.java – initializes tables and verifies their structure

SqlScriptRunner.java – parses and executes SQL scripts

VersionFilesTable.java – manages tables data

3350 lines of source code

Why not to use Java Runtime.exec() method?

String commandString = “mysql –u root –ppassword < sql_script.sql”;

Process process = Runtime.getRuntime().exec(commandString);

You have to provide all execution environment and catch up standard and error output

Parsing SQL is easier and more reliable


Catalogversion source code l.jpg

CatalogVersion Source Code

try

{

// Get the file reader

LineNumberReader lineReader = new LineNumberReader(reader);

// Start of the file reading loop (line by line)

while ((line = lineReader.readLine()) != null)

{

// restart the number of executions for each command

executeCount = 0;

if (command == null)

{

command = new StringBuffer();

}

// handling the delimiter. It plays different roles inside the function

// and outside one. The alternative delimiter separates function's SQL code

// from the rest of the SQL code.

// The default delimiter separates sentences inside the "normal" SQL code

// The word "DELIMITER" should not be executed by Java - it is not SQL

// ligitimate word, so it should be skipped

if (line.startsWith("DELIMITER"))

{

if (delimiterCount == 0)

{

setDelimiter(ALTERNATIVE_DELIMITER, true); // met delimiter 1st time

delimiterCount = 1; // increment the delimiterCount

}

else if (delimiterCount == 1)

{

setDelimiter(DEFAULT_DELIMITER, false); // met delimiter 2nd time

// restore the initial condition

delimiterCount = 0; // reset the delimiterCount

}

}

// Got clean string

String trimmedLine = line.trim();

m_logger.debug("SqlScriptRunner::runScript2: trimLine = "+trimmedLine);

// Actual parsing

if (trimmedLine.startsWith("--"))

{

// println(trimmedLine);

m_logger.debug("SqlScriptRunner::runScript2: trimmedLine = "+trimmedLine);

}

else if (trimmedLine.startsWith("DELIMITER")) // Hardcoded work in MySQL dialect

{

m_logger.debug("SqlScriptRunner::runScript2: trimmedLine = "+trimmedLine);

}

else if (trimmedLine.length() < 1 || trimmedLine.startsWith("//"))

{

// Do nothing

}

else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--"))

{

// Do nothing

}

else if (!fullLineDelimiter && trimmedLine.endsWith(getDelimiter())

|| fullLineDelimiter && trimmedLine.equals(getDelimiter()))

{

command.append(line.substring(0, line.lastIndexOf(getDelimiter())));

command.append(" ");

Statement stmt = conn.createStatement();


Summary l.jpg

Summary

  • Database server evolution requires database change management

  • CatalogVersion Tool has been developed to implements necessary functionality

    • The tool allows to update multiple servers

    • Synchronize different servers

    • Organizes database changes in a manageable manner

    • Major drawback – lack of transactionality (will work it out)

  • CatalogVersion Tool easy to use, it is very efficient and it is free


  • Login