Catalog Version Tool for Database Change Management Jacob

Catalog. Version Tool for Database Change Management Jacob Nikom November 8, 2010 Slide 1

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_files’ – Table ‘catalog_version. revision’ – Database Delta Scripts • Catalog. Version Program – Catalog. Version Tool Command – Catalog. Version Tool Design – Why not to use Java Runtime. exec() method? – Catalog. Version Source Code • Summary Slide number 2

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 Slide number 3

How software developers track changes in their source code Checkouts and Editing: I may have to check out, edit and check in Slide number 4

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 Slide number 5

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 Slide number 6

Version Control of Database Changes Volume serial number is 00350032 1 C 00: 4 ED 7 C: . ├───1. 0 │ |-aaa_install_sequence_1. 0. txt │ |-mysql_user_flush_privs. sql …………………………… │ |-nops_update_patch_v 1. 1. 1. sql │ `-nops_update_patch_v 1. 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_config 1. sql …………………………. . │ │-otf_dml_mpi_ignore_config. sql │ `otf_dml_mpi_ignore_data_delete. sql Catalog. Version table Catalog. Version tool Repository Revision-controlled SQL script directory This is compact, precise and revision controlled representation of the database state Slide number 7

Table ‘catalog_version_files’ Table_id SQL file name Revision Id Revision id (Major) (Minor) Insert time Comments 1 cybs_00100_dcl_12_24_2009. sql 1. 2 4755 2010 -01 -25 15: 50: 00 Jacob change 2 cybs_00200_ddl_dml_nokia 1. 0_11_25_2009. sql 1. 2 4756 2010 -01 -25 15: 50: 00 Mark change 3 cybs_00400_dml_DMCE_sunil_12_23_2009. sql 1. 2 4757 2010 -01 -25 15: 50: 00 John change ……. . …………… 23 redb_00200_ddl_create_all_redb_tables 1. 3 5531 2010 -01 -25 15: 50: 00 Sam change 24 redb_00300_dml_insert_data_into_all_redb_tab les 1. 3 5514 2010 -01 -25 15: 50: 00 Frank change ……. . ……………. . Each SQL script is delta Catalog. Verion tool does not overrides existing files This feature allows to build new data on the top of old ones Slide number 8

Table ‘catalog_version. revision’ One-to-one mapping between database state and source code state Source code Catalog. Version tables Catalog. Version tool Repository Table_id Revision Id Revision id Runt time Comments PK FK (Minor) 1 1 4755 2010 -01 -25 15: 50: 00 Jacob change 2 1 4756 2010 -01 -25 15: 50: 00 Mark change 3 2 4757 2010 -01 -25 15: 50: 00 John change ……………. . 23 23 5531 2010 -01 -25 15: 50: 00 Sam change 24 23 5514 2010 -01 -25 15: 50: 00 Frank change ……………. . ……. . Every time when source code changes, Catalog. Version tool runs using existing list of SQL scripts Slide number 9

Database Delta Scripts File name convention redb_00300_dml_insert_data_into_all_redb_tables_10_23_2010. sql Schema or product name Execution order number Content category Content explanation Creation date 1. Schema or product name 1. cybx – Cybersource – related file. It affects NOPS 1. 0 schemata and tables 2. 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 1. Through order of the SQL scripts execution. It is incremented in hundreds allowing to insert additional numbers. 3. Content category or sql file classification 1. dcl – file affecting users and their privileges (Data Control Language) 2. ddl – file affecting schema, tables, columns, foreign keys and other constraints. Also deals with stored procedures, stored functions, triggers and indexes (Data Definition Language) 3. dml – file affecting the data (static or lookout data) (Data Manipulation Language) 4. dtl – file affecting the transactional data (Data Manipulation Language) Slide number 10

Catalog. Version Program • • Catalog. Version tool is Java program (JDK 1. 4 and better) Applies SQL scripts to the My. SQL server Registers SQL script in the catalog_version schema (unique name) Needs four JAR files to run Filename Contents Catalog. Version. jar main program log 4 j-1. 2. 15. jar logging and debugging routines log 4 j-props. jar logging formats and management data mysql-connector-java-5. 1. 7 -bin. jar My. SQL driver for Java Slide number 11

Catalog. Version Tool Command To run the Catalog. Version tool use the command: java -jar Catalog. Version. jar -u username -p password -h server. IP -f sql_file_name -c “Comments” Option Definition java The Java Virtual Machine (JVM) executable -jar Catalog. Version. jar Indicates to the JVM that the program is located in the jar file whose name follows -jar (in this case, –u username The username credential that permits a connection to the My. SQL server. Mandatory. –p password The password credential to the username that permits a connection to the My. SQL server. Mandatory –h server. IP The IP address of the computer where My. SQL server runs. If this option not included, the program assumes the local server. –f sql. File. List Name of the file (sequence file) containing the list of the sql script names (no default) –P port_number Port number to which My. SQL server is listening. If this option is not included, the program assumes port number 3306. -q sql. File. Name name of the SQL script file containing sql commands (no default) -s nonstop Permits execution of the next SQL script even in case of SQL exception (in new version only) –c “Comments” Provide additional comments. The comments must be in quotes. If this option is not used, the program assumes “This is test sql file version". Slide number 12

Catalog. Version Tool Command (cont. ) File update_local_server_sql_files_10_25_2010_dfile. bat content: java -Dfile. encoding=UTF-8 -cp. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. jar -u root -p password -h 10. 48. 92. 153 -f. /DBCreate_10_25_2010/utf 8_conversion/install_sequence_utf 8. txt java -Dfile. encoding=UTF-8 -cp. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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. ; . /log 4 j-1. 2. 15. jar; -jar Catalog. Version. 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 Slide number 13

Catalog. Version Tool Design Five Java files Catalog. Version. java – main driver Dbms. Config. java – command linhe parsing, static data repository Dbms. Connector. java – manages database connection Dbms. Initializer. java – initializes tables and verifies their structure Sql. Script. Runner. java – parses and executes SQL scripts Version. Files. Table. java – manages tables data 3350 lines of source code Why not to use Java Runtime. exec() method? String command. String = “mysql –u root –ppassword < sql_script. sql”; Process process = Runtime. get. Runtime(). exec(command. String); You have to provide all execution environment and catch up standard and error output Parsing SQL is easier and more reliable Slide number 14

Catalog. Version Source Code try { // Get the file reader Line. Number. Reader line. Reader = new Line. Number. Reader(reader); // Start of the file reading loop (line by line) while ((line = line. Reader. read. Line()) != null) { // restart the number of executions for each command execute. Count = 0; if (command == null) { command = new String. Buffer(); } // 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. starts. With("DELIMITER")) { if (delimiter. Count == 0) { set. Delimiter(ALTERNATIVE_DELIMITER, true); // met delimiter 1 st time delimiter. Count = 1; // increment the delimiter. Count } else if (delimiter. Count == 1) { set. Delimiter(DEFAULT_DELIMITER, false); // met delimiter 2 nd time // restore the initial condition delimiter. Count = 0; // reset the delimiter. Count } } // Got clean string String trimmed. Line = line. trim(); m_logger. debug("Sql. Script. Runner: : run. Script 2: trim. Line = "+trimmed. Line); // Actual parsing if (trimmed. Line. starts. With("--")) { // println(trimmed. Line); m_logger. debug("Sql. Script. Runner: : run. Script 2: trimmed. Line = "+trimmed. Line); } else if (trimmed. Line. starts. With("DELIMITER")) // Hardcoded work in My. SQL dialect { m_logger. debug("Sql. Script. Runner: : run. Script 2: trimmed. Line = "+trimmed. Line); } else if (trimmed. Line. length() < 1 || trimmed. Line. starts. With("//")) { // Do nothing } else if (trimmed. Line. length() < 1 || trimmed. Line. starts. With("--")) { // Do nothing } else if (!full. Line. Delimiter && trimmed. Line. ends. With(get. Delimiter()) || full. Line. Delimiter && trimmed. Line. equals(get. Delimiter())) { command. append(line. substring(0, line. last. Index. Of(get. Delimiter()))); command. append(" "); Statement stmt = conn. create. Statement(); Slide number 15

Summary • Database server evolution requires database change management • Catalog. Version 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) • Catalog. Version Tool easy to use, it is very efficient and it is free Slide number 16
- Slides: 16