Mercurial, Fabric, Liquibase, Kohana (part 3)

My new application is going to require an authentication. I am not looking for anything fancy. For starters the ORM authentication available in Kohana 3.3 will do. I may extend it later. This means that I am going to use database to store user accounts. As I am a big fan of database code versioning I'll be using LiquiBase to do that. So how to go about it?

Kohana has an SQL script for MySQL to create everything ORM needs. It is located in "project/kohana/modules/orm/auth-schema-mysql.sql". To manage all db stuff by LiqiBase I decided to give a try to a following approach:

1. Create an empty DB. This will be just temporary thing, erased once I am done.
2. I play Kohana SQL script against that DB.
3. Now I use LiquiBase functionality to capture current DB structure into a LiquiBase XML file. The process I wrote earlier over here.
4. I now clean the code a bit. Change the author and id in each changeset so it is readable.
5. Remains to add data inserted by SQL script. In this case data is represented by two rows inserted into "roles" table. This I do manually.

Now I create a "db" directory in root of my project and copy created file there. I could continue and write future changesets into this file. However as I am lazy I know I could reuse the file. So instead I start new file for the project and include in it the ORM file, I just created using LiquiBase. So here is how the project directory looks like:

project
  |- application
  |- db
  |  |- master.xml
  |  |- orm-auth.xml
  |
  |- kohana
  |- .htaccess
  |- .htignore
  |- index.php
  |- fabfile.py

And here is what I put into "master.xml":

<databasechangelog 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
  xsi:schemalocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
 <include file="orm-auth.xml" />
</databasechangelog>

This approach has some further advantages:

1. I can reuse "orm-auth.xml" file in different projects as well.
2. I can create more include files (and reference them from within "master.xml") which will keep structures, procedures and other database artifacts in separate files so I won't get lost in single large file. 

In following part I'll automate database creation and migration by putting LiquiBase and Fabric together.

Here is a content of generated orm-auth.xml:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
    <changeSet author="kohana" id="1">
        <createTable tableName="roles">
            <column autoIncrement="true" name="id" type="INT UNSIGNED">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(32)">
                <constraints nullable="false"/>
            </column>
            <column name="description" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet author="kohana" id="2">
        <createTable tableName="roles_users">
            <column name="user_id" type="INT UNSIGNED">
                <constraints nullable="false"/>
            </column>
            <column name="role_id" type="INT UNSIGNED">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet author="kohana" id="3">
        <createTable tableName="user_tokens">
            <column autoIncrement="true" name="id" type="INT UNSIGNED">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="user_id" type="INT UNSIGNED">
                <constraints nullable="false"/>
            </column>
            <column name="user_agent" type="VARCHAR(40)">
                <constraints nullable="false"/>
            </column>
            <column name="token" type="VARCHAR(40)">
                <constraints nullable="false"/>
            </column>
            <column name="created" type="INT UNSIGNED">
                <constraints nullable="false"/>
            </column>
            <column name="expires" type="INT UNSIGNED">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet author="kohana" id="4">
        <createTable tableName="users">
            <column autoIncrement="true" name="id" type="INT UNSIGNED">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="email" type="VARCHAR(254)">
                <constraints nullable="false"/>
            </column>
            <column defaultValue="" name="username" type="VARCHAR(32)">
                <constraints nullable="false"/>
            </column>
            <column name="password" type="VARCHAR(64)">
                <constraints nullable="false"/>
            </column>
            <column defaultValueNumeric="0" name="logins" type="INT UNSIGNED">
                <constraints nullable="false"/>
            </column>
            <column name="last_login" type="INT UNSIGNED"/>
        </createTable>
    </changeSet>
    <changeSet author="kohana" id="5">
        <addPrimaryKey columnNames="user_id, role_id" tableName="roles_users"/>
    </changeSet>
    <changeSet author="kohana" id="6">
        <addForeignKeyConstraint baseColumnNames="role_id" baseTableName="roles_users" constraintName="roles_users_ibfk_2" deferrable="false" initiallyDeferred="false" onDelete="CASCADE" onUpdate="NO ACTION" referencedColumnNames="id" referencedTableName="roles" referencesUniqueColumn="false"/>
    </changeSet>
    <changeSet author="kohana" id="7">
        <addForeignKeyConstraint baseColumnNames="user_id" baseTableName="roles_users" constraintName="roles_users_ibfk_1" deferrable="false" initiallyDeferred="false" onDelete="CASCADE" onUpdate="NO ACTION" referencedColumnNames="id" referencedTableName="users" referencesUniqueColumn="false"/>
    </changeSet>
    <changeSet author="kohana" id="8">
        <addForeignKeyConstraint baseColumnNames="user_id" baseTableName="user_tokens" constraintName="user_tokens_ibfk_1" deferrable="false" initiallyDeferred="false" onDelete="CASCADE" onUpdate="NO ACTION" referencedColumnNames="id" referencedTableName="users" referencesUniqueColumn="false"/>
    </changeSet>
    <changeSet author="kohana" id="9">
        <createIndex indexName="uniq_name" tableName="roles" unique="true">
            <column name="name"/>
        </createIndex>
    </changeSet>
    <changeSet author="kohana" id="10">
        <createIndex indexName="expires" tableName="user_tokens" unique="false">
            <column name="expires"/>
        </createIndex>
    </changeSet>
    <changeSet author="kohana" id="11">
        <createIndex indexName="uniq_token" tableName="user_tokens" unique="true">
            <column name="token"/>
        </createIndex>
    </changeSet>
    <changeSet author="kohana" id="12">
        <createIndex indexName="uniq_email" tableName="users" unique="true">
            <column name="email"/>
        </createIndex>
    </changeSet>
    <changeSet author="kohana" id="13">
        <createIndex indexName="uniq_username" tableName="users" unique="true">
            <column name="username"/>
        </createIndex>
    </changeSet>
    <changeSet author="kohana" id="14">
        <insert tableName="roles">
            <column name="id" valueNumeric="1"/>
            <column name="name" value="login"/>
            <column name="description" value="Login privileges, granted after account confirmation"/>
        </insert>
        <insert tableName="roles">
            <column name="id" valueNumeric="2"/>
            <column name="name" value="admin"/>
            <column name="description" value="Administrative user, has access to everything."/>
        </insert>
    </changeSet>
</databaseChangeLog>