Database Refactoring Workshop

Course materials for the Liquibase Workshop.

Requirements

• JDK 1.6+

Environment Setup

1. Download workshop files

   • Use Git:
     • git clone https://github.com/stevesaliman/liquibase-workshop
   • Download the ZIP:
     • https://github.com/stevesaliman/liquibase-workshop/zipball/master
   • Put workshop files in a directory of your choosing.

2. Install Gradle using the wrapper

   • Run the gradlew script to download and install the Gradle executable locally

3. Be sure to keep a browser window open to the excellent Liquibase docs

Database Setup

1. To initialize your embedded H2 Database schema, run the following:

   gradlew buildSchema

2. To start up the H2 Database server and open the management interface in the browser, run the following command. Note that this task runs a Java program in the H2 Database distribution, and that program does not exit automatically. You will have to hit CTRL-C to return to the command line.

   gradlew startDatabase

3. Alternatively, run the gradle createDatabaseScript task, which will produce a script called starth2 (on Mac and Linux) or starth2.bat (on Windows). You can then start the database by running this script from the command line, which keeps the database running in the background while you continue to interact with workshop tasks.

4. To log into the H2 Database web interface, visit http://localhost:8082. You will see a connect dialog with default values. Enter jdbc:h2:db/liquibase_workshop;FILE_LOCK=NO for the JDBC URL, but leave the defaults everywhere else. Click the connect button to log in.

5. To begin using Liquibase on the embedded database, run the following two commands:

   gradlew generateChangeLog

   gradlew changeLogSync

Exercises

1. Rename Table
   • Rename the inv table to invoice
   • Rename the lineitem table to line_item
   • Rename the lidetail table to line_item_detail
2. Rename columns in the invoice table
   • Rename invid to id
   • Rename invnumber to invoice_number
   • Rename datetimecreated to date_created
3. Combine two columns using data transformation
   • invoice.udtime and invoice.uddate should be combined into invoice.date_updated
   • First populate the date_created new column with an UPDATE query that merges the udtime and uddate values
     • HINT: udtime + uddate
   • Discuss whether you should drop the two source columns in this refactoring.
     • What does it imply if you drop them?
     • What would you have to do if you kept them around?
4. Create tables
   • contact_ball_of_mud is too ambitious of a table (or insufficiently coherent). Let's begin splitting it up.
     • The contact table should contain name fields, gender, email address, street address, birthday, occupation, and national ID
     • The security_info table should contain password and mother's maiden name
     • The credit_card table should contain credit card type, number, expiration and CVV
     • Choice of data type for each column is left as an exercise for the student.
   • Don't run this refactoring yet!
5. Tagging and rolling back
   • Tag the database, then run the table rename refactoring written in the previous step
     • gradle tag -Dtag=<tagname>
   • Now roll back to continue development on the refactoring
     • gradle rollback -Dtag=<tagname>
6. Finish refactoring of contact_ball_of_mud
   • Write data transformation code to populate the three tables from their source
   • Remember that security_info and credit_card should have foreign keys to contact. Be sure to add these constraints with the appropriate refactorings
7. Add a column
   • Add a full_name column to contact
   • Write data transformation SQL to populate it with the three existing name fields combined
     • HINT: CONCAT_WS()
   • Don't drop of the source name columns.
8. Create a trigger
   • Create a directory called src/triggers under your project
   • Create a file called contact_insert.sql in src/triggers
     • Write trigger logic to keep full_name up to date with the fields for first name, middle initial, and last name every time a new record is inserted
   • Create a file called contact_update.sql in src/triggers
     • Same logic as the insert trigger
   • Write changeSets that use the sqlFile refactoring to install these triggers.
     • Remember the runOnChange attribute.
     • HINT: be sure the changeSet is idempotent!
9. Introduce lookup table
   • The invoice table has a field for payment terms, which should be normalized, not stored as strings values in the table
   • Create a table called payment_terms with an auto-incrementing id and a varchar(50) field called terms
   • Write a series of insertData refactorings to populate this table with all of the possible values of payment terms you found by inspecting the invoice table
   • Add a column to the invoice table called payment_terms_id
   • Add data transformation code to set payment_terms_id to refer to the appropriate rows of the payment_terms table
   • Test your work by manually executing a join on invoice and payment_terms
   • Add a foreign key constraint to payment_terms_id
     • The details of this constraint are left as an exercise for the student
   • Note that this step may best be implemented as more than one changeSet. Use your judgment.
10. Introduce surrogate key
    • Add an auto-incrementing column to line_item called id.
    • Add a primary key constraint on id.
11. Remap foreign keys
    • Add an integer column to line_item_detail called line_item_id
    • Write an UPDATE query to set its values to line_item.id