Shaking the foundations
Let’s continue where we left last time. We want to create the database, the initial tables which we think could be enough for starting the development.
Assume we have chosen the database server we will use for beneath our application and write the SQL script for creating the tables — and we used database specific elements too. But then our client tells us, that the database does not fit his/her needs so we should change to another. Drat. But we re-write our script as follows and we avoid to use any specific keywords.
CREATE TABLE VEHICLE ( ID NUMBER NOT NULL, LICENSE_PLATE VARCHAR(30) NOT NULL, VIN VARCHAR(30) NOT NULL, LICENSE_EXPIRES DATE NOT NULL, CAPACITY NUMBER NOT NULL, CURB_WEIGHT NUMBER NOT NULL, GROSS_WEIGHT NUMBER NOT NULL, MANUFACTURER_NAME VARCHAR(50) NOT NULL, MODEL VARCHAR(255), COLOR VARCHAR(255), CONSTRAINT PK_VEHICLE PRIMARY KEY (ID) ); CREATE TABLE DRIVER ( ID NUMBER NOT NULL, NAME VARCHAR(255) NOT NULL, DRIVING_LICENCE_NUMBER VARCHAR(30) NOT NULL, DRIVING_LICENCE_EXPIRES DATE NOT NULL, DRIVING_LICENCE_CATEGORY VARCHAR(30) NOT NULL, CONSTRAINT PK_DRIVER PRIMARY KEY (ID) ); CREATE TABLE ROUTE ( ID NUMBER NOT NULL, CITY_FROM VARCHAR(255) NOT NULL, CITY_TO VARCHAR(255) NOT NULL, DISTANCE NUMBER NOT NULL, CONSTRAINT PK_ROUTE PRIMARY KEY (ID) ); CREATE TABLE SHIPPING_NOTE ( ID NUMBER NOT NULL, VEHICLE_ID NUMBER NOT NULL, DRIVER_ID NUMBER NOT NULL, ROUTE_ID NUMBER NOT NULL, SHIPPING_START TIMESTAMP NOT NULL, SHIPPING_END TIMESTAMP NOT NULL, CARGO_WEIGHT NUMBER NOT NULL, CARGO_DESCRIPTION VARCHAR(255) NOT NULL, CONSTRAINT PK_SHIPPING_NOTE PRIMARY KEY (ID) ); CREATE TABLE USER ( ID NUMBER NOT NULL, NAME VARCHAR(255) NOT NULL, LOGIN VARCHAR(30) NOT NULL, PASSWORD VARCHAR(30) NOT NULL, E_MAIL_ADDRESS VARCHAR(255) NOT NULL, CONSTRAINT PK_USER PRIMARY KEY (ID) );
Fine we create the tables with our script. But wait: how do we manage our database-changes in the future?
Well, there are many possible solutions and answers.
- “I write a one-version-application.” — OK, that’s nice, but I’ve never seen an app which worked out-of-the-box with the initial configuration despite the database.
- “I write my app alone for one client.” — In that case it’s a straightforward solution to write SQL statements, “hack” the database for your client on demand.
- “I write my app alone for multiple clients.” — Alright, almost the same solution than above, however in this case it would be nice to maintain a release-cycle and apply the same DDL modifications to all your customers. It’s manageable too.
- “We have more developers and we use a weird solution for managing DB changes (versioning, a table to log changes or something similar).” — I know, if you say this you are on a very good way that you use one of the tools I’m going to mention (but if you don’t I’m shocked 😉 )
So by the time reading this line you will probably know which tools did I mention.
We will take a look at Liquibase (http://www.liquibase.org/) and FlyWay (http://flywaydb.org/), two currently hip database change management tools. I will create and alter the database with both of theese tools (in separate posts) and eventually I’ll write a comparsion.
For Database I’ll use H2 DB (http://www.h2database.com/html/main.html) because it’s easy to set up and if you mess up your data you can easily throw away the datastructure and create a new DB. Detailed instructions how to install and run you’ll find at the project’s WebSite. I use the one-zip version, it works as fine as the installer on Windows — but it’s portable to other environments (such as Linux or Mac OS). But you can install any Database you like, I’ll eventually provide some configurations in the next session for H2, Oracle, MySQL and SQLServer — and the SQL scripts will be always DBMS independent (or not, Liquibase or FlyWay will do the nasty things).
Stay tuned 😉