Database versioning with liquibase

Database versioning with liquibase

• 1.
  Core Systems TransformationSolutions Database versioning with Liquibase Maxim Bukov
• 2.
  Confidential 2 Agenda In scope:What is liquibase? How does liquibase database versioning works? How to start using liquibase? Liquibase main features. Examples.
• 3.
  Confidential 3 Database Versioning:Problem DEV • Nick’s Database • Andrew’s Database • Michael’s Database TEST • Database 1 • Database 2 PROD • Database The questions are: • What state is the database in on this machine? • Has this script already been applied or not? • Has the quick fix in production been applied in test afterwards? • How do you set up a new database instance?
• 4.
  Confidential 4 Database Versioning:Solution Defined Upgrade Path History Of Changes Intelligent update Rollback ability Database Versioning Major benefits: • Recreate a database from scratch. • Make it clear at all times what state a database is in. • Migrate in a deterministic way from your current version of the database to a newer or even elder one
• 5.
  Confidential 5 Database Versioning:Tools State-driven Migration-driven Red Gate SQL Compare $ Visual Studio Database Projects $ Liquibase Flyway Datical $
• 6.
  Confidential 6 Liquibase: Intro Liquibaseis a open source database-independent tool for managing and executing database changes. Version Control System Continuous Integration Tool Liquibase Target Database Main features: • Provides update / rollback / comparison capabilities. • Supports multiple databases (Oracle, MySQL, MSSQL, etc.). • Works via cmd, Ant, Maven, servlet container or Spring framework.
• 7.
  Confidential 7 Formats XML SQL Oracle MySQL MSSQL … YAML JSON Liquibase: ChangeSet Change Set a.k.a migration - atomic database change description.
• 8.
  Confidential 8 Liquibase: ChangeSet structure <changeSet author="mbukov" id="lb-play-example-01-01"> <preConditions onFail="HALT"> <sqlCheck expectedResult="0"> SELECT SUM(DECODE(TABLE_NAME, 'EMPLOYEE',1, 0)) FROM dba_tables WHERE owner = sys_context('USERENV','SESSION_SCHEMA') GROUP BY OWNER; </sqlCheck> </preConditions> <comment>table with data on each employee</comment> <createTable tableName="EMPLOYEE"> <column name="EMPLOYEE_ID" type="NUMBER"> <constraints nullable="false"/> </column> <column name="NAME" type="VARCHAR2(50)"/> <column name="HIRED_DATE" type="date"/> <column name="IS_ACTIVE" type="NUMBER" defaultValueNumeric="1"> <constraints nullable="false"/> </column> </createTable> <rollback> drop table EMPLOYEE; </rollback> </changeSet>
• 9.
  Confidential 9 Liquibase: ChangeLog Master Change Log Change Log 1 Change Set 1 Change Set 2 Change Set 3 Change Log 2 Change Set 4 Change Set 5 Change Log 3 Change Set 6 Change Log - file with list of database changes to apply.
• 10.
  Confidential 10 java -jarliquibase.jar --classpath=ojdbc.jar --driver=oracle.jdbc.OracleDriver --url=jdbc:oracle:thin:@test:1821:orcl --username=schema_name --password=p@ssw0rd --logLevel=info --changeLogFile=db.changelog.master.xml --defaultsFile=./liquibase.properties generateChangeLog update rollbackToDate 2016-01-01T00:00:00 … -Dchl_param=test_val Liquibase: Run with cmd
• 11.
  Confidential 11 Liquibase: Generatechange log Data Tables Columns Views Primary keys Unique constraints Indexes Foreign keys Sequences Included: Procedures Functions Packages Excluded: generateChangeLog Generates change log to create the current database schema Special parameters diffTypes List of objects to take into account. generateChangeLog Output file name. Format depends on file extension. Example #1
• 12.
  Confidential 12 Liquibase: Update updateUpdates database to current version. updateCount <value> Applies the next <value> change sets. Special parameters --changeLogFile=<path and filename> The changelog file to use. DATABASECHANGELOG ID AUTHOR FILENAME DATEEXECUTED ORDEREXECUTED MD5SUM DESCRIPTION COMMENTS TAG CONTEXTS DATABASECHANGELOGLOCK ID LOCKED LOCKGRANTED LOCKEDBY Example #2
• 13.
  Confidential 13 Liquibase: Rollback updateTestingRollbackUpdates, then rolls changes back and updates again. rollback <tag> Rolls back to the state when it was tagged. rollbackCount <value> Rolls back the last <value> change sets. rollbackToDate <date/time> Rolls back to the state it was in at the given date/time. Special parameters --changeLogFile=<path and filename> The changelog file to use. Example #3 Liquibase refactorings with auto generated rollback: AddColumn AddDefaultValue AddForeignKeyConstraint AddLookupTable AddNotNullConstraint AddPrimaryKey AddUniqueConstraint CreateIndex CreateSequence CreateTable CreateView DropNotNullConstraint RenameColumn RenameTable RenameView TagDatabase
• 14.
  Confidential 14 Liquibase: Checksum Checksumis MD5 hash used to detect differences between what is currently in the changelog and what was actually ran against the database. Checksum is individually calculated and stored for each changeset. Check sum is affected by: • Database change related tags • SQL formatting • Liquibase/changelog parameters Check sum is not affected by: • Non-database change related tags. • XML formatting Checksum fix tips: • runOnChange • validCheckSum • clearCheckSums Example #4
• 15.
  Confidential 15 Liquibase: Othercommands status Outputs count (list if --verbose) of unrun change sets. validate Checks the changelog for errors. updateSQL Writes SQL to update database to current version. futureRollbackSQL Writes SQL to roll back the database to the current state after the changes in the changelog have been applied. diff Writes description of differences to standard out. dbDoc Generates Javadoc-like documentation based on current database and change log. dropAll Drops all database objects owned by the user.
• 16.
  Confidential 16 Conclusion Questions? TBC: How towrite change sets Multiple database support Making most out of XML How to organize files How to test scripts