workshop_instructions.html

<h1>Database Refactoring Workshop</h1>

<p>Course materials for the Liquibase Workshop.</p>

<h2>Requirements</h2>

<ul>
<li>JDK 1.5+</li>
<li><a href="http://gradle.org/downloads.html">Gradle 1.0_M1+</a></li>
</ul>

<h2>Environment Setup</h2>

<ol>
<li><p>Download workshop files</p>

<ul>
<li>Use Git:
<ul>
<li>git clone https://github.com/tlberglund/liquibase-workshop</li>
</ul></li>
<li>Download the ZIP:
<ul>
<li><a href="https://github.com/tlberglund/liquibase-workshop/zipball/master">https://github.com/tlberglund/liquibase-workshop/zipball/master</a></li>
</ul></li>
<li>Put workshop files in a directory of your choosing.</li>
</ul></li>
<li><p>Install Gradle using the wrapper</p>

<ul>
<li>Run the <code>gradlew</code> script to download and install the Gradle executable locally</li>
</ul></li>
<li><p>Be sure to keep a browser window open to the excellent <a href="http://www.liquibase.org/manual/home">Liquibase docs</a></p></li>
</ol>

<h2>Database Setup</h2>

<ol>
<li><p>To initialize your embedded H2 Database schema, run the following:</p>

<p><code>gradlew buildSchema</code></p></li>
<li><p>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. </p>

<p><code>gradlew startDatabase</code></p></li>
<li><p>Alternatively, run the <code>gradle createDatabaseScript</code> task, which will produce a script called <code>starth2</code> (on Mac and Linux) or <code>starth2.bat</code> (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.</p></li>
<li><p>To log into the H2 Database web interface, visit <code>http://localhost:8082</code>. You will see a connect dialog with default values. Enter <code>jdbc:h2:db/liquibase\_workshop;FILE\_LOCK=NO</code> for the JDBC URL, but leave the defaults everywhere else. Click the connect button to log in.</p></li>
<li><p>To begin using Liquibase on the embedded database, run the following two commands:</p>

<p><code>gradle generateChangeLog</code></p>

<p><code>gradle changeLogSync</code></p></li>
</ol>

<h2>Exercises</h2>

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