Agile Databases

Any project following an Agile methodology will usually find itself releasing to production at least 15 - 20 times per year. Even if only half of these releases involve database changes, that's 10 changes to production databases so you need a good lean process to ensure you get a good paper trail but at the same time you don't want something that that will slow you just unduly down. So, some tips in this regard:

Tip 1: Introduce a DB Log table

Use a DB Log table to capture every script run, who ran it, when it was run, what ticket it was associated with etc. Here is an example DDL for such a table for PostGres:

create sequence db_log_id_seq;
create table db_log (id int8 not null DEFAULT nextval('db_log_id_seq'), created timestamp not null,  db_owner varchar(255), db_user varchar(255), project_version varchar(255), script_link varchar(255), jira varchar(255));

W.R.T. the table columns:

• id - primary key for table. 
• timestamp - the time the script was run. This is useful.  Believe me. 
• db_owner - the user who executed the script. 
• db_user - the user who wrote the script 
• project_version_number - the version of your application / project the script was generated in.
• scrip_link - a URL link to a source controlled version of the script 
• jira - a URL to the ticket associated with the script. 

Tip 2: All Scripts should be Transactional

For every script, make sure it happens within a transaction and within the transaction make sure there is an appropriate entry into the db log table. For example, here is a script which removes a column

BEGIN;
ALTER TABLE security.platform_session DROP COLUMN IF EXISTS ttl;
INSERT INTO db_log (
       db_owner, db_user, project_version, script_link, jira, created)
VALUES (
       current_user,
       'alexstaveley',
       '1.1.4',
       'http://ldntools/labs/cp/blob/master/platform/scripts/db/updates/1.1.4/CP-643.sql',
       'CP-643',
       current_timestamp
);
COMMIT;

Tip 3: Scripts should be Idempotent

Try to make the scripts idempotent. If you have 10 developers on a team, every now and again someone will run a script twice by accident. Your db_log will tell you this, but try to ensure that when accidents happen that there is no serious damage. This means you get a simple fail safe,  rather than some newbie freaking out.   In the above script, if it is run twice the answer will be the exact same.

Tip 4: Source Control your Schema

Source control a master DDL for the entire project. This is updated anytime the schema changes. Meaning you have update scripts and a complete master script containing the DDL for entire project. The master script is run at the beginning of every CI, meaning that:

• Your CI always starts with a clean database 
• If a developer forgets to upgrade the master script, the CI will fail and your team will quickly know the master script needs to be updated.
• When you have a master script it gives you two clear advantages: 
• New developers get up and running with a clean database very quickly
• It becomes very easy to provision new environments. Just run the master script! 

Tip 5: Be Dev Friendly

Make it easy for developers to generate the master script. Otherwise when the heat is on, it won't get done.

Tip 6: Upgrade and Revert

For every upgrade script write a corresponding revert script. Something unexpected happens in production, you gotta be able to reverse the truck back out!

BEGIN;

ALTER TABLE security.platform_session ADD COLUMN hard_ttl INT4;
UPDATE security.platform_session  SET hard_ttl = -1 WHERE hard_ttl IS NULL;
ALTER TABLE security.platform_session ALTER COLUMN hard_ttl SET NOT NULL;

ALTER TABLE security.platform_session ADD COLUMN ttl INT4;
UPDATE security.platform_session  SET ttl = -1 WHERE ttl IS NULL;
ALTER TABLE security.platform_session ALTER COLUMN ttl SET NOT NULL;


INSERT INTO db_log (
       db_owner, db_user, platform_version, script_link, jira, created)
       values (
       current_user,
       'alexstaveley',
       '1.1.4',
       'http://ldntools/labs/cp/blob/master/platform/scripts/db/reverts/1.1.4/revert-CP-463.sql',
       'CP-463',
       current_timestamp
    );

COMMIT;

Until the next time take care of yourselves.

Custom User types in GORM

Recently, I wanted to model a Merchant which like many things in a domain model had an Address. I thought it made sense that the Address was embedded inside the Merchant. Reasons:

• It had no lifecycle outside the Merchant. Merchant dies so should the address.
• It only ever belonged to one and only one Merchant

So pretty obvious this was a composition relationship.

Now, it is possible to model composition relationships in GORM. See here. However, this approach comes with the caveat that the Address must be a GORM object. I didn't want the Address being a GORM object because GORM objects are powerful in Grails. With all their dynamic finders and GORM APIs they are essentially like really a DAO on steroids. If a developer gets their hands on can do lots of things (not always good things). I didn't want or need any of this. In addition, a good architecture makes it difficult for developers to make mistakes when they are working under pressure at fast speeds. That means, when you are making design decisions you need to think about the power you need to give, should give and will give.

So with that in mind, I looked into wiring up a custom type for Address. This would just be a data structure that would model the address, could be reused outside the Merchant (thus promoting consistency and again thus promoting good design) and wouldn't come with the power of the GORM. There is some documentation in the GORM doc's for custom types but there isn't a full working example. I had a look at some Hibernate examples and then put managed to put this together and get working.

Here is my address object.

@Immutable
class Address {

    private final String city;
    private final String country;
    private final String state;
    private final String street1;
    private final String street2;
    private final String street3;
    private final String zip;

    public String getCity() {
        return city;
    }

    public String getCountry() {
        return country;
    }

    public String getZip() {
        return zip;
    }

    public String getState() {
        return state;
    }

    public String getStreet1() {
        return street1;
    }

    public String getStreet2() {
        return street2;
    }

    public String getStreet3() {
        return street3;
    }
}

Here is my AddressUserType object:

class AddressUserType implements UserType {

    public int[] sqlTypes() {
        return [
            StringType.INSTANCE.sqlType(),
            StringType.INSTANCE.sqlType(),
            StringType.INSTANCE.sqlType(),
            StringType.INSTANCE.sqlType(),
            StringType.INSTANCE.sqlType(),
            StringType.INSTANCE.sqlType(),
            StringType.INSTANCE.sqlType()
        ] as int[]
    }

    public Class getReturnedClass() {
        return Address.class;
    }

    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws SQLException {
        assert names.length == 7;
        log.debug(">>mullSafeGet(name=${names}")
        String city = StringType.INSTANCE.get(rs, names[0], session); // already handles null check
        String country = StringType.INSTANCE.get(rs, names[1], session ); // already handles null check
        String state = StringType.INSTANCE.get(rs, names[2], session ); // already handles null check
        String street1 = StringType.INSTANCE.get(rs, names[3], session ); // already handles null check
        String street2 = StringType.INSTANCE.get(rs, names[4], session ); // already handles null check
        String street3 = StringType.INSTANCE.get(rs, names[5], session ); // already handles null check
        String zip = StringType.INSTANCE.get(rs, names[6], session ); // already handles null check

        return city == null && v == null ? null : new GAddress(city: city, country: country, state: state, street1: street1, street2: street2,  street3: street3, zip: zip);
    }

    void nullSafeSet(java.sql.PreparedStatement st, java.lang.Object value, int index, org.hibernate.engine.spi.SessionImplementor session) throws org.hibernate.HibernateException, java.sql.SQLException {
        if ( value == null ) {
            StringType.INSTANCE.set( st, null, index );
            StringType.INSTANCE.set( st, null, index+1 );
            StringType.INSTANCE.set( st, null, index+2 );
            StringType.INSTANCE.set( st, null, index+3 );
            StringType.INSTANCE.set( st, null, index+4 );
            StringType.INSTANCE.set( st, null, index+5 );
            StringType.INSTANCE.set( st, null, index+6 );
        }
        else {
            final Address address = (Address) value;
            StringType.INSTANCE.set( st, address.getCity(), index,session );
            StringType.INSTANCE.set( st, address.getCountry(), index+1,session);
            StringType.INSTANCE.set( st, address.getState(), index+2,session);
            StringType.INSTANCE.set( st, address.getStreet1(), index+3,session);
            StringType.INSTANCE.set( st, address.getStreet2(), index+4,session);
            StringType.INSTANCE.set( st, address.getStreet3(), index+5,session);
            StringType.INSTANCE.set( st, address.getZip(), index+6,session);
        }
    }


    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        // for now
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        assert (x != null);
        return x.hashCode();
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        return original;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        return cached;
    }

    public Class returnedClass() {
        return Address.class;
    }
}

And here is my Merchant which has an Address.

class Merchant {
    UUID id;

    String color;
    String displayName;
    //...
    //...

    Address address
    
    static mapping = {
        address type: AddressUserType, {
            column name: "city"
            column name: "country"
            column name: "zip"
            column name: "state"
            column name: "street1"
            column name: "street2"
            column name: "street3"
        }
    }

}

As stated, with this approach, the Address data structure could be used in other GORM objects. Until the next time take care of yourselves.

Postgres indexes

Recently, I had a situation where I needed to think how I was using Postgres indexes. I had a simple Book table with the following schema...

>\d book

                  Table "shopping.book"
       Column        |          Type          | Modifiers 
---------------------+------------------------+-----------
 id                  | uuid                   | not null
 version             | bigint                 | not null
 amount_minor_units  | integer                | not null
 currency            | character varying(255) | not null
 author       | character varying(255) | not null
 publisher           | character varying(255) |    

The author and publisher columns were just String pointers to actual Author and Publisher references that were on another system meaning that classical foreign keys couldn't be used and that these dudes were just normal columns.

I needed to get an idea how the table would perform with lots of data, so first up some simple SQL to put in lots of test data:

 
> CREATE EXTENSION "uuid-ossp";
> insert into book (id, version, amount_minor_units, currency, author, publisher) 
select uuid_generate_v4(), 2, 22, 'USD', 'author' || x.id, 'publisher' || x.id from generate_series(1,1000000) AS x(id); 

This table was going to be hit lots of times with this simple query:

select * from book where author = 'Tony Biggins' and publisher='Books unlimited';

To get the explain plain, I did:

dublintech=> EXPLAIN (FORMAT JSON) select * from book where author = 'Tony Biggins' and publisher = 'Books unlimited';
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 [                                                                                                                                +
   {                                                                                                                              +
     "Plan": {                                                                                                                    +
       "Node Type": "Seq Scan",                                                                                                   +
       "Relation Name": "book",                                                                                                   +
       "Alias": "book",                                                                                                           +
       "Startup Cost": 0.00,                                                                                                      +
       "Total Cost": 123424.88,                                                                                                   +
       "Plan Rows": 1,                                                                                                            +
       "Plan Width": 127,                                                                                                         +
       "Filter": "(((author)::text = 'Tony Biggins'::text) AND ((publisher)::text = 'Books unlimited'::text))"+
     }                                                                                                                            +
   }                                                                                                                              +
 ]
(1 row)

As can be seen, Postgres is doing a Seq Scan, aka a table scan. I wanted to speed things up. There was only one index on the table which was for the id. This was just a conventional B-Tree index which would be useless in this query since it wasn't even in the where clause. Some of options I was thinking about:

• Create an index on author or publisher
• Create an index on author and create an index on publisher
• Create a combination index on both index and publisher.

Hmmm... let the investigations begin. Start by indexing just author.

dublintech=> create index author_idx on book(author);
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 [                                                                            +
   {                                                                          +
     "Plan": {                                                                +
       "Node Type": "Index Scan",                                             +
       "Scan Direction": "Forward",                                           +
       "Index Name": "author_idx",                                  +
       "Relation Name": "book",                                               +
       "Alias": "book",                                                       +
       "Startup Cost": 0.42,                                                  +
       "Total Cost": 8.45,                                                    +
       "Plan Rows": 1,                                                        +
       "Plan Width": 127,                                                     +
       "Index Cond": "((author)::text = 'author3'::text)",+
       "Filter": "((publisher)::text = 'publisher3'::text)"                     +
     }                                                                        +
   }                                                                          +
 ]
(1 row)

As can be seen Postgres performs an index scan and the total cost is much lower than the same query which uses a table scan. What about the multiple column index approach? Surely, since both are used in the query it should be faster again, right?

dublintech=> create index author_publisher_idx on book(author, publisher);
CREATE INDEX
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 [                                                                                                                        +
   {                                                                                                                      +
     "Plan": {                                                                                                            +
       "Node Type": "Index Scan",                                                                                         +
       "Scan Direction": "Forward",                                                                                       +
       "Index Name": "author_publisher_idx",                                                                     +
       "Relation Name": "book",                                                                                           +
       "Alias": "book",                                                                                                   +
       "Startup Cost": 0.42,                                                                                              +
       "Total Cost": 8.45,                                                                                                +
       "Plan Rows": 1,                                                                                                    +
       "Plan Width": 127,                                                                                                 +
       "Index Cond": "(((author)::text = 'author3'::text) AND ((publisher)::text = 'publisher3'::text))"+
     }                                                                                                                    +
   }                                                                                                                      +
 ]
(1 row)

This time Postgres, uses the multi-index, but the query doesn't go any faster. Mai, pourquoi? Recall, how we populated the table.

insert into book (id, version, amount_minor_units, currency, author, publisher) 
select uuid_generate_v4(), 2, 22, 'USD', 'author' || x.id, 'publisher' || x.id from generate_series(1,1000000) AS x(id); 

There are lots of rows, but every row has a unique author value and a unique publisher value. That would mean the author index for this query should perform just as well. An analogy would be, you go into a music shop looking for a new set of loudspeakers someone has told you to buy that have a particular cost and a particular power output (number of watts). When you enter the shop, you see the speakers are nicely ordered by cost and you know what? No two sets of loudspeakers have the same cost. Think about it. Are you going to find the speakers any faster if you use just use the cost or you use the cost and the loudspeaker?

Now, imagine the case if lots of the loudspeakers were the same cost. Then of course using both the cost and the power will be faster.

Now, let's take this point to the extremes in our test data. Suppose all the authors were the same. The author index becomes useless and if we don't have the author / publisher combination index we would go back to table scan.

// drop combination index and just leave author index on table 
dublintech=> drop index author_uesr_ref_idx;
DROP INDEX
dublintech=> update book set author='author3';
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 [                                                                                                                    +
   {                                                                                                                  +
     "Plan": {                                                                                                        +
       "Node Type": "Seq Scan",                                                                                       +
       "Relation Name": "book",                                                                                       +
       "Alias": "book",                                                                                               +
       "Startup Cost": 0.00,                                                                                          +
       "Total Cost": 153088.88,                                                                                       +
       "Plan Rows": 1,                                                                                                +
       "Plan Width": 123,                                                                                             +
       "Filter": "(((publisher)::text = 'publisher3'::text) AND ((author)::text = 'author3'::text))"+
     }                                                                                                                +
   }                                                                                                                  +
 ]
(1 row)

So we can conclude from this that single column indexes for combination searches can perform as well as combinational indexes when there is a huge degree of variance in the data of that single column. However, when there isn't, they won't perform as well and a combinational index should be used. Yes, I have tested by going to extremes but that is the best way to make principles clear.And please note: For the case when there is maximum variance in data, adding another index to the other column in the where clause, publisher made no difference. This is as expected.

Ok, let's stick with the case when there is massive variance in data values in the column. Consider the case of maximum variance and the query only ever involves exact matching. In this case, all authors values are guaranteed to be unique and you never have any interest in doing anything like less than or greater than. So why not use a hash index instead of a B-Tree index?

dublintech=> create index author_hash on book using hash (author);
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 [                                                                            +
   {                                                                          +
     "Plan": {                                                                +
       "Node Type": "Index Scan",                                             +
       "Scan Direction": "NoMovement",                                        +
       "Index Name": "author_hash",                                 +
       "Relation Name": "book",                                               +
       "Alias": "book",                                                       +
       "Startup Cost": 0.00,                                                  +
       "Total Cost": 8.02,                                                    +
       "Plan Rows": 1,                                                        +
       "Plan Width": 127,                                                     +
       "Index Cond": "((author)::text = 'author3'::text)",+
       "Filter": "((publisher)::text = 'publisher3'::text)"                     +
     }                                                                        +
   }                                                                          +
 ]
(1 row)

Interesting, we have gone faster again. Not a massive difference this time around but an improvement nonetheless that could be more relevant with more data growth and / or when a more complex query with more computation is required. We can safely conclude from this part that yeah if you are only interested in exact matches then the hash index beats the b-tree index. Until the next time take care of yourselves. References:

• http://www.postgresql.org/docs/9.1/static/using-explain.html
• http://www.postgresql.org/docs/9.3/static/indexes-bitmap-scans.html

Using separate Postgres Schemas for the same database in a Grails App

Recently, I wanted to use the same Postgres Database but split my persistence layer into separate components which used separate schemas. The motivation was to promote modular design, separate concerns and stop developers tripping up over each other. Vertical domain models can be difficult to achieve but not impossible.

In my shopping application, I had a user component, a shopping component and a product component. Now this is pretty easy if you are using separate databases, but sometimes it's nice to just get the separation of concerns using separate schemas in the same database, since using the same database can make things like DR, log shipping, replication etc easier.

While I could find doc for separate databases, I found it difficult to find Grails doc to advice on my specific problem - how to use separate schemas when using the same database when using Postgres. So here is how I ended up doing it.

Here is my datasource.groovy.

String db_url = "jdbc:postgresql://localhost:5432/testdb"
String usernameVar = "db_user"
String passwordVar = "db_secret"
String dbCreateVar = "update"
String dialect = "net.kaleidos.hibernate.PostgresqlExtensionsDialect"

dataSource_user {
    pooled = true
    jmxExport = true
    dialect = dialect
    driverClassName = "org.postgresql.Driver"
    username = usernameVar
    password = passwordVar
    url = platform_url
    dbCreate= "validate"
}

hibernate_user {
    cache.use_second_level_cache = false
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
    singleSession = true // configure OSIV singleSession mode
    default_schema = "user"
}

dataSource_shopping {
    pooled = true
    jmxExport = true
    dialect = dialect
    driverClassName = "org.postgresql.Driver"
    username = usernameVar
    password = passwordVar
    url = platform_url
    dbCreate = "validate"
}

hibernate_shopping {
    cache.use_second_level_cache = false
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
    singleSession = true // configure OSIV singleSession mode
    default_schema = "shopping"
}

dataSource_product {
    pooled = true
    jmxExport = true
    dialect = dialect
    driverClassName = "org.postgresql.Driver"
    username = usernameVar
    password = passwordVar
    url = platform_url
    dbCreate= "validate"
}

hibernate_product {
    cache.use_second_level_cache = false
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
    singleSession = true // configure OSIV singleSession mode
    default_schema = "product"
}

Note: there are some obvious optimisations in config above, but the above just makes explaining simple.

I then mapped each GORM object to the appropriate schema.

class Cart {
    // ...
    // ...
    static mapping = {
        datasource 'shopping'
        // ... 
    }
}

class Address {
    // ...
    // ...

    static mapping = {
        datasource 'user'
    }
}

class Stock {
    // ...
    // ...

    static mapping = {
        datasource 'product'
    }
}

I then started my app and said "yippe, this works" had a little tea break and moved onto the next problem. As can be seen the trick is to use a separate hibernate closure, specify the schema in there and name the closure using the same naming format for separate database, but make the database closures point to the same database.

SQL Server tips

Recently, I was working on a project which involved SQL Server. I made a note of some useful commands that help me find useful things out. Here they are:

Check column types for a specific table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TOSENV'

Check column constraints

SELECT * FROM CONSTRAINT_COLUMN_USAGE

Check length of text in a varchar column

SELECT LEN(MYCOLUMN) FROM MYTABLE;

List all tables that have a column named EntityID

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'EntityID'

Check all constrains...

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Find blocked processes

SELECT * FROM  SYS.SYSPROCESSES WHERE BLOCKED > 0

To see long running open transactions

DBCC OPENTRAN;

Check the lock timeout

SELECT @@LOCK_TIMEOUT

Check current session

SSP_WHO2

Find creation date of databases

SELECT NAME, CRDATE FROM MASTER..SYSDATABASES

Get the database id for a database.

SELECT DB_ID('PLANES')

Get the median value for a column named responsetime in a table allrequests

SELECT( (SELECT MAX(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime) AS BottomHalf) + (SELECT MIN(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime DESC) AS TopHalf)) / 2 AS Median

Most CPU intensive queries

SELECT HIGHEST_CPU_QUERIES.PLAN_HANDLE, HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME, Q.DBID, Q.OBJECTID, Q.NUMBER, Q.ENCRYPTED, Q.[TEXT]FROM (SELECT TOP 50 QS.PLAN_HANDLE, QS.TOTAL_WORKER_TIME FROM SYS.DM_EXEC_QUERY_STATS QS ORDER BY QS.TOTAL_WORKER_TIME DESC) AS HIGHEST_CPU_QUERIES CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS QORDER BY HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME DESC INDEXES AND KEYS

Get me all the tables that have a FK to the table named Person

SELECT OBJECT_NAME ([PARENT_OBJECT_ID]) AS 'REFERENCING TABLE', * FROM SYS.FOREIGN_KEY_COLUMNSWHERE [REFERENCED_OBJECT_ID] = OBJECT_ID ('PERSON'));

Check all objects that have changed in last ten days

SELECT NAME AS OBJECT_NAME ,SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME ,TYPE_DESC ,CREATE_DATE ,MODIFY_DATEFROM SYS.OBJECTS WHERE MODIFY_DATE > GETDATE() - 10 ORDER BY MODIFY_DATE;
Disable indexes

ALTER INDEX [IX_NAME] ON TABLE DISABLE

Enable indexes

ALTER INDEX [IX_NAME] ON TABLE REBUILD USERS CHECK CURRENT USER

Check current user

SELECT SYSTEM_USER;

Check database owner

SELECT NAME, SUSER_SNAME(OWNER_SID) FROM SYS.DATABASES STATS

Check last time Stats was run

SELECT NAME AS STATS_NAME, STATS_DATE(OBJECT_ID, STATS_ID) AS STATISTICS_UPDATE_DATE FROM SYS.STATSWHERE OBJECT_ID = OBJECT_ID('DBO.TASCONTACT');

Run stats for a table named Person in Sales

UPDATE STATISTICS SALES.PERSON

Check the size of various tables

SELECT OBJECT_NAME(P.OBJECT_ID) AS "TABLE", P.INDEX_ID, F.NAME, SUM(TOTAL_PAGES)/128 AS "SIZE IN MB", CONVERT(VARCHAR(10), GETDATE(), 101), COUNT(*) AS PARTITIONSFROM SYS.PARTITIONS P JOIN SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID JOIN SYS.FILEGROUPS F ON A.DATA_SPACE_ID = F.DATA_SPACE_IDGROUP BY P.OBJECT_ID, P.INDEX_ID, F.NAMEORDER BY SUM(TOTAL_PAGES)/128 DESC

Find the creation time for all tables.

SELECT NAME AS TABLENAME, CREATE_DATE AS CREATEDDATE FROM SYS.TABLES VERSION

Check version

SELECT @@VERSION;

or for more info

SELECT SERVERPROPERTY('EDITION') AS SQL_Server_Edtition, SERVERPROPERTY('PRODUCTLEVEL') AS SQL_Product_Level, SERVERPROPERTY('PRODUCTVERSION') AS SQL_Version;

Extending your JPA POJOs

Extensibility is an important characteristic in many architectures.  It is a measure of how easy (or difficult) it is to add or change functionality without impacting existing core system functionality.

Let's take a simple example.  Suppose your company has a core product to track all the users in a sports club.  Within your product architecture, you have a domain model represented by JPA POJOs.  The domain model contains many POJOs including - of course - a User POJO. 

package com.alex.staveley.persistence
/**
 * User entity.  Represents Users in the Sports Club. 
 *
 * Note: The SQL to generate a table for this in MySQL is:
 *
 * CREATE TABLE USER (ID INT NOT NULL auto_increment, NAME varchar(255) NOT NULL, 
 *  PRIMARY KEY (ID)) ENGINE=InnoDB;
 */ 
@Entity
public class User {
    /* Surrogate Key - automatically generated by DB. */ 
    @GeneratedValue(strategy=GenerationType.IDENTITY) 
    @Id
    private int id;
 
    private String name;

    public int getId() {
        return id;
    }
 
    public void setName(String name) {
        this.name=name;
    }
 
    public String getName() {
        return name;
    }
}

Now, some customers like your product but they need some customisations done before they buy it.  For example, one customer wants the attribute birthplace added to the User and wants this persisted.  The logical place for this attribute is - of course - in the User POJO, but no other customer wants this attribute.  So what do you do? Do you make a specific User class just for this customer and then swap it in just for them?  What happens when you change your Product User class then?  What happens if another customer wants another customisation?  Or changes their mind?  Are you sensing things are going to get messy?

Thankfully, one implementation of JPA: Eclipselink helps out here.  The 2.3 release (available since June 2011, latest release being a 2.3.2 maintenance released just recently, 9th December, 2011) includes some very features which work a treat for this type of scenario.  Let's elaborate.  By simply adding the @VirtualAccessmethods Eclipselink annotation to a POJO we signal to Eclipselink that the POJO may have some extra (also known as virtual) attributes.  You don't have to specify any of these extra attributes in code, otherwise they wouldn't be very virtual!  You just have to specify a generic getter and setter to cater for their getting and setting.  You also have to have somewhere to store them in memory, something like a good old hashmap - which of course should be transient because we don't persist the hashmap itself.  Note: They don't have to be stored in a HashMap, it's just a popular choice!

Let's take a look at our revamped User which is now extensible!

@Entity
@VirtualAccessMethods
public class User {
    /* Surrogate Key - automatically generated by DB. */ 
    @GeneratedValue(strategy=GenerationType.IDENTITY) 
    @Id
    private int id;
 
    private String name;
 
    @Transient
    private Map extensions = new HashMap();

    public int getId() {
        return id;
    }
 
    public void setName(String name) {
        this.name=name;
    }
 
    public String getName() {
        return name;
    }
 
    public  T get(String name) {
        return (T) extensions.get(name);
    }
 
    public Object set(String name, Object value) {
        return extensions.put(name, value);
    } 
}

So, is that it?  Well there's a little bit more magic.  You have to tell eclipselink about your additional attributes.  More specifically: what their names and datatypes are. You do this by updating your eclipselink-orm.xml which resides in the same META-INF folder that the persistent.xml is in. 
Now this configuration simply states, the User entity has an additional attribute which in java is "thebirthplace" and it is virtual.  This means it is not explictly defined in the POJO but if we were to debug things, we'd see the attribute having the name 'thebirthplace' in memory.  This configuration also states that the corresponding database column for the attribute is birthplace.  And eclipselink can get and set this attribute using the generic get /set methods.

You wanna test it? Well add the column to your database table.  In MySql this would be:  
        alter table user add column birthplace varchar(64)

Then run this simple test:

So now, we can have one User POJO in our product code which is extensible.  Each customer can have their own attributes added to the User - as they wish.  And of course, each customer is separated from all other customers very easily by just ensuring each customer's extensions resides in a specific eclipslink-orm.xml.  Remember, you are free to name these files as you want and if you don't use the default names you just update the persistence.xml file to state what names you are using.

This approach to extending the User, means that when we want to update the User POJO in our product, we only have to update one (and only one) User POJO.  But, when specific attributes have to be added for specific customer(s), we don't touch the User POJO code.  We simple make the changes to the XML and do not have to recompile anything from the core product. And of course, at any time it is easy to see what the customisations for any customer are by just simply looking at the appropriate eclipselink-orm.file.

Ye Ha. Happy Extending! 

References:

1. http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_JPA_Development/Extensible_Entities
2. http://www.eclipse.org/eclipselink/

The A to Z of DB2

In nearly every software architecture there is a relational database - somewhere.  And in nearly every relational database there is a range of concepts and buzzwords.  Some unique to a particular database vendor but many not.  In this post we run through some concepts / buzzwords for DB2 - alphabetically!

Authority Levels

A DB2 Authority Level is a security level representing a collection of privileges and higher-level database manager maintenance and utility operations.  SYSADM, SYSCTRL, SYSMAINT, SYSMON are instance level authorities and can only be assigned to a group. DBADM, SECADM and LOAD are database level authorities.

SYSADM is the only authority which can:

•  update the DMB CFG file

•  grant SYS* authorities to other groups. Grant DBADM authority to users / groups

•  access data within any database
•  do anything SYSCTRL can do

SYSCTRL:

•  only access data in database if given privilege
•  can create/drop databases, tablespaces
•  do anything SYSMAINT can do

SYSMAINT can:

•  db2start/ db2stop / backup / restore/ runstats

DBADM can:

• do anything for a particular database
• grant load authority to other users

Authentication Types

The following Authentication types are available:

SERVER: authentication takes place on the Server

SERVER_ENCRYPT: authentication takes place on the Server username / password is encrypted on the client before being sent.

CLIENT, KERBEROS, KRB_SERVER_ENCRYPT, DATA_ENCRYPT, DATA_ENCRYPT_CMP, GSSPLUGIN, GSS_SERVER_ENCRYPT.

If a Client uses the SERVER_ENCRYPT and the Server uses SERVER authentication type and error will occur when client tries to connect to the Server.

Common Table Expressions

Common Table Expressions exist only for the life of the SQL statement that created them. They are used for special cases e.g. recursion in a query.  

The syntax is: WITH [tablename] ...

Command Editor

Interactive GUI for SQL commands

Configuration Assistant

The Configuration Assistant enables users to configure clients so that they can access databases stored on remote DB2 servers. The configuration assistant allows users to catalog, uncatalog databases, bind applications, set DB2 registry parameters, changes passwords, test database connections.

Connect Enterprise Edition

Connect Enterprise Edition is an add on product that allows data to be moved between Linux, Unix, Windows, iSeries and zSeries based DB2 servers.

Constraints

Constraints are used to enforce business rules (e.g. attributes in a column cannot be null).  The SQL used to create constraints is stored in the System Catalog.

Control Center

Performs admin for instances / databases / bufferpools / tablespaces/ views / indexes... Catalog / Uncatalog databases. And all sorts of other DB tasks.

Cursor Operations

Update and delete operations can be performed using position operations or search operations.  In a position operation the cursor must be first created, opened and then positioned.  When a cursor is declared with 'WITH HOLD' option, it will remain open across transactions until it is explicitly closed.

Cursor Usage Steps

The steps to use a cursor are: DECLARE CURSOR, OPEN, FETCH, CLOSE.

Database Manager Configuration File

Stores the names of the groups which have been given instance level authorities (SYSADM, SYSCTRL, SYSMAINT, SYSMON)

Decimal

There four ways to define decimal types: DECIMAL(percision, scale), DEC(precision, scale), NUMERIC(precision, scale) and NUM(precision, scale)

DCS directory

The DCS directory stores database information for remote databases on z/OS iSeries.

Declared temporary tables

User defined tables to hold non persistent data.   They are created by the application and destroyed when the application terminates.

Design Advisor

The design advisor makes recommendations for new indexes, deletion of indexes, Materialized Query Tables, Multi Dimensional Clustering

Developer Workbench (Development Center in v8)

Used to create, edit, debug, deploy, test DB2 stored procedures and user defined functions. Also to develop and run XML queries.

Distinct Type

A distinct data type is a user-defined data type that is derived from one of the built in data types in DB2. Example of syntax creation: CREATE DISTINCT TYPE euro AS DECIMAL (9,3) WITH COMPARISON.  Disinct types are strongly typed; they cannot be used as an argument for a built-in data type in a built-in function, even if they are derived from them (and vice versa).  If the WITH COMPARISON syntax is used during creation, it means that comparison functions (<>, <, > , <=, >=, >) and ORDER BY, GROUP BY clauses can be used for the distinct type. Two casting functions are created anytime a distinct type is created.  This is used to convert to / from its base type and has the same name as the distinct type.

Extenders

Extenders are advanced data types that are not part of the built-in datatypes.  There are 6 types of extenders in DB2.

• DB2 AVI extender
• DB2 Text extender
• DB2 Net search extender
• DB2 XML extender
• DB2 Spatial extender
• DB2 Geodetic extender - can treat earth like globe rather than flat map.

Foreign Key Constraints for Delete:

ON DELETE CASCADE: When entity is deleted from parent table, any entity which has a FK to the parent entity will be deleted.

ON DELETE SET NULL: When entity is deleted from parent table, FK will be set to null

ON DELETE RESTRICT: When entity is deleted from parent table, FK values must point to same value

ON DELETE NO ACTION: When entity is deleted from parent table, FK values must point to something valid but can change

Foreign Key Constrains for Update:

ON UPDATE RESTRICT: When entity is updated in parent table, the FK values must have to have the same values

ON UPDATE NO ACTION: When the entity is updated in the parent table, the FKs values can change but must always point at something.

Grant All (table)

GRANT ALL privileges for table means: ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES privileges.  Note there is no 'ADD' privilege.

Graphic

Graphic is a fixed length double-byte character type.

Group By

Used to specify columns that are to be grouped together and to provide input into aggregate functions such as SUM() and AVG()

Group By Cube 

Used to group in multiple dimensions e.g. SELECT workdept, gender, AVG(salaray) AS avg_salary FROM employee GROUP BY CUBE (workdept, gender);

Having

The having clause is used to by further selection criteria to a GROUP BY clause. It refers to data that has already been grouped by a GROUP BY clause.  It uses same syntax as WHERE clause and can only be used in by the GROUP BY clause.

Health Center

The Health Center tool is used to select instance and database objects that you want to monitor

Identity Column

Identity columns must be a numeric data type with a scale of 0.

Indexes 

The creation of an index provides logical ordering of rows in a table in ascending order of the index.

Isolation levels

Repeatable read isolation level will lock rows scanned in a query.

Read stability isolation level will only lock the rows returned in the result data set.

Cursor stability isolation level will only lock the result set that the cursor is currently pointing to.

The uncommitted read isolation level will not lock any rows during normal read processing (unless another user tries to alter or drop the table being read).

Journal

The DB2 journal is an interactive GUI that tracks historical information about tasks, database actions and operations

License Center

the License Center Allows users to view information about licenses

Like (table creation)

CREATE TABLE ... LIKE ... - creates table which has exact same name, datatype and nullability characteristics.

Locks

Locks can only be applied to table spaces, tables and rows.

Lock conversion

The act of changing a lock to a more restrictive state.  In most cases, lock conversation happens for row level locks, e.g. if an Update(U) lock is held and an Exclusive(X) lock is needed, the update(U)lock will be converted to an Exclusive lock

Lock escalation

Lock escalation is when the size of a lock changes.  For example from Row to Table size. This is usually to free up some space in the Lock list.

Lock list

The specific amount of memory set aside to hold a structure that DB2 uses to manage locks.

Lock state (or mode)

DB2 locks can have various states: Intent None, Intent share, Next Key Share, Share, Intent Exclusive, Share with Intent Exclusive, Update, Next Key Exclusive, Exclusive, Weak Exclusive, Super Exclusive.  DB2 determines the lock state by looking at isolation level and the SQL being executed.

Materialised Query Tables:

User defined tables whose definition is based on the result of a query used for query optimization.

Null

For predicates use IS NULL. In result sets, - means null.  Unique indexes can one null value. Unique constraints can have never have a null value.  Nulls can't be in used with IN clauses.

Operating System Support

DB2 Type / OS	Linux	Windows	Solaris	Mobile OS	AIX	HP-UX	System i
DB2 Everyplace	No	No	No	Yes	No	No	No
DB2 PE	Yes	Yes	No	No	No	No	No
DB2 Express C	Yes	Yes	No	No	No	No	No
DB2 Express	Yes	Yes	Yes	No	No	No	No
DB2 i5 / Os	No	No	No	No	No	No	Yes
DB2 WSE	Yes	Yes	Yes	No	Yes	Yes	No
DB2 ESE	Yes	Yes	Yes	No	Yes	Yes	No

Packages

A package is an object that contains the information needed to process SQL statements associated with a source code file of an application program.

Privilege: Alter (Table)

The alter table privilege allows user to add columns to a table, add / change table comments, create a table pk, unique/check constraint, triggers for table. 

Privilege: Control 

The control privilege that applies to Table, View, Nicknames, Packages and Indexes. It includes every privilege including the privilege to drop the object from the DB.  The owner of a table automatically receives control privilege (and all other privileges).  Only users with SYSADM or DBADM authority are allowed to explictily grant CONTROL privilege on any object.  A user with CONTROL privilege can grant any table privilege except CONTROL privilege.

Privilege: Connect 

(Database)

Connect Privilege applies to a database. It allows users to connect.

Privilege: References (Table)

Reference table privilege allows a user to create and drop foreign key constrains that reference a table in a parent relationship. This can be granted for entire table of just specific columns in 

the table.

Privilege: Usage (sequences)

Allows the PREVIOUS VALUE and NEXT VALUE expression associated with the sequence to be changed.

Privileges: Packages 

The BINDADD privilege at database allows a user to create packages in the database.

Privileges (Explicit / Implicity)

Explicit privileges have to be granted explicitly. Implictly privileges do not. For example, CONTROL is granted to anyone who creates a Table or View.  CREATEIN, ALTERIN, DROPIN is granted to anyone who creates a schema.

Privileges: View / Nickname

There is no Alter, Index or Reference privilege for View. Otherwise they have have the same privileges as Tables. Nickhaves have the same privileges possible as Tables.

Privileges:Index

There is only one Index privilege - it is Control.

Replication Center

Facilitates data replication between a DB2 database and any other relational database

Routine

A routine is a user defined function or stored procedure

Satellite Admin Center

Allows users set up and administer a group of DB2 Servers

Sequences

Sequences, identity columns and triggers can be used to generate values for a column. But, only sequences can be referenced in an INSERT statement.

SET operators

A set operator is is used to combined two or more queries into a single query. Examples: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL

Spatial Extenders

Spatial extender treats the world as flat map; the DB2 geodetic extender treats the world as a globe.

SQL Performance Monitor 

To analyse database operations performed againsts a DB2 for i5/OS database

Storage

Char = (number of characters * 1) bytes required; 1 and 254 characters

Varchar = (number of characters + 4) bytes requires; 32,672 characters

LONG VARCHAR = (number of characters + 24) bytes required; 32,672 characters (table space agnostic)

GRAPHIC = (number of characters * 2) bytes required; 127 characters

VARGRAPHIC = (number of characters * 2) + 4 bytes required; 16, 336

LONG VARGRAPHIC = (number of characters * 2) + 24 bytes required; 16,350 characters (table space agnostic)

Structured Data Type

A structured data type is a user-defined type that contains one or more attributes, each of which has a name and a data type of its own.  A SDT can also be created as a subtype of another structured type.   SDT are created by the CREATE TYPE sql statement.

Table Locks

Share mode - Other transactions are allowed read data but not change the data that is locked.

Exclusive mode - Other transactions can neither read nor modify the data that is locked.

Task Center

Allows users schedule, run tasks and send notifications about them

Time / Timestamp

Timestamp can store date time. Time can only store time.

Triggers

• A trigger can be activated whenever an insert, update or delete operation is performed (not a select).
• A trigger event can be activated before, after or instead of the trigger event
• Trigger granularity: They can be activated for every row updated (FOR EACH ROW) or just for every statement (FOR EACH STATEMENT)
• To stop trigger events setting off other triggers use the NO CASCADE option
• A trigger event can reference old or new data using the 'REFERENCE OLD AS' or 'REFERENCE NEW AS' syntax
• A trigger can send signals.This can be used to prevent actions, for example: SIGNAL SQLSTATE '75002' SET MESSAGE TEXT 'Deletes not allowed'.
• The SQL used to create Triggers is stored in the system catalog.

Typed tables

User defined tables whose column definitions are based on the attributes of a user defined structured data type.

Universal Developer's Edition

Contains tools to build application supported Linux, UNIX, Windows and DRDA Application Requestor

View Tables

Do not contain real data but instead refer to data in real tables. Only the view definition itself is stored in the database.

Useful for controlling access to data.   The WITH LOCAL CHECK OPTION can be used to enforce data constraints for inserts, updates.

The WITH CASCADED CHECK OPTION can be used to cascade constraints to subsequent views.  Characteristics of View tables are stored in the system catalog not the SQL that created them.

Visual Explain

Gives visual representation of data access plan

XML Columns

XML columns are used to store documents as a hierarchial set of entities. The XML data type does not have a fixed length.

P.S.  Well done Seán O'Brien on another super performance! Well done Ireland beating Italy and making the World Cup Quarter finals!

Seán O'Brien

SQL or NOSQL that is the question?

So what's the deal with NoSQL?

Is NoSQL just a controversial buzzword? Could you imagaine if the term 'Object Orientated' didn't exist and instead architectures based on concepts such as encapsulation, polymorphism and inhertiance were referred to as 'NoProcedural'?  Could you imagine if .net was called 'NoJava'?  Leinster was called 'NoMunster'?

Well controversial name aside,  a good way to appreciate the hype about NoSQL is to consider scalability - the classical non-functional architectural concern. In a classical OLTP architecture, when load increases and your JVM is under pressure, you need to scale.  You have two choices:

• vertical scaling - adding more CPU power to your JVM
• horizontal scaling - adding more JVMs (usally one more boxes)

It's generally never any problem scaling the business tier horizontally. Follow J2EE / JEE specs and unless you've done something crazy your business tier will scale.  Just add more JVMs and load balance between them.  However, while the business tier may be straighforward, the persistence tier ain't so easy.   Let's say you are using a classical relational database (such as MySQL, SQLServer, DB2 or Oracle) for your persistence, you can't just add database machines like you can add JVMs.  Why not?  Imagine trying to do SQL joins when tables are on the same machine and when the tables are on different machines! Imagine trying to do maintain ACID characteristics for your transactions when your database is split across various CPUs?  Now think trying to do all that on 5 machines, 50 , 500, 5000 machines?  The more machines the harder it gets.

The leading relational databases will scale horizontally.  But only by so much.  To get around this an architect usually will consider:

• Scaling vertically - putting the database on the best hardware that can be afforded
• Partitioning out legacy data and thus reduce things like the size of index tables. This will boost performance and put less pressure on the need to scale
• Remove the amount of pressure on the database by caching more in the business tier
• Pay a DBA a lot of money!

But what if you just run out of all possible database optimisations options and you have to scale horizontally? Not just to a few machines but to a few hundred if not thousand. This is where NoSQL architectures become relevant.

With a NoSQL database there is no strict schema.  Everything is effectively collapsed into one very fat table - a bit like an old skool flat file, but where each row stores a huge amout of data.   So, instead of having a table for Users and a table for Activities (representing User's activities), you put all the User information together in one fat row. This means there are no joins across tables.  It also means there is a lot of data redundancy which means more storage space required. In addition, more computational power will be needed for writes. But because data that is used together is located at the very same place - within the same row - it means no complex joins and hence it is easier to scale. The computational requirement for reads is also less.  So reads can go faster.

Another advantage of NoSQL databases is derived from the freedom that comes with not having to be tied to strict schema.  You know that headache where a change to a data model  can cause big problems? Well since there is no strict schema with NoSQL - this problem does not exist.  This makes the architecture more flexible and more extensible.

Right now, it's fair to say NoSQL is only relevant in the minority of architectures. But could this be another case of technical innovation driving business innovation as we have seen with smart phones?  There wasn't a need for smart phones but the technical innovation provided business opportunities. I think the same could happen with NoSQL Architectures.

Take a step back from Computer Science and just think Science.  Science used to be hypotheisis centric, now it is becoming more and more data centric. CERN, genome sequencing, climate change analysis - all involve tonnes and tonnes of data. Surely NoSQL architectures allied with searching technologies such as MapReduce / Hadoop will open up new ways to do Science?

So any disadvantages with NoSQL architectures? Well it's still an immature technology. Indexing, Security models are just not as sophisticated as they are with classical relational databases. And because most of it is coming from the open source community the support is not as good as it is for relational databases.  So don't throw out your SQL just yet!

PS Well done Dublin and winning the All Ireland!

References:
1. http://about.digg.com/blog/looking-future-cassandra
2. http://www.techrepublic.com/blog/10things/10-things-you-should-know-about-nosql-databases/1772
3. http://nosqltapes.com/

Vector Clocks

Vector clocks are a way of versioning data so that there is an indication of the history of which actors in your system have been involved in the history of that data.

Confused?  Let's consider a 'real world' analogy to the problem vector clocks helps to solve.  You have four rugby fans:  a Leinster fan; a Munster fan; a Connacht fan and an Ulster fan.  Together they are going to try to determine who they think should be wearing the number 6 jersey for Ireland in the 2011 Rugby World Cup.  Now:

• The fans are all able to communicate their selection for the jersey with each other
• They can send their selection to everyone or just one other fan
• They are able to change their mind
• They are able to communicate concurrently. This means the Munster fan can send a text to Leinster fan while at the same time the Connacht fan can send a text to the Ulster fan.  
• Each fan is able to give an indication of the version he got before he sends his selection on. This may or may not have influenced their own selection!

Sean O'Brien

So it starts off. The Leinster fan is using Twitter and has the other 3 lads subscribed.  He tweets: Sean O'Brien.   The Munster fan thinks about this for a while.  He can't deal with it.  He's a proud Munster fan.  The pressure gets to him and he sends a text message to the Connacht fan that the man who should be wearing number 6 is  Denis Leamy.  It's just a text to the Connacht fan; the Ulster and Leinster fan are unaware of this communication.   The Connacht fan decides decides to amuse himself and decides to agree.  He texts back Denis Leamy to the Munster lad. The Ulster lad then sends a text to the Connacht lad which reads: Stephen Ferris.  The Connacht fan can see the potential tension brewing; especially between the Munster and Leinster lads so he decides to be diplomatic and decides to change his selection to Stephen Ferris.

The Leinster lad then looks for the other lads' answers.  Unfortunately he's lost the Ulster lads phones number so he can only communicate with the Munster and Connacht lads.  He sees the Munster lad has chosen Leamy and the Connacht lad has chosen Ferris.  He can see there's clearly been a contradiction.  But he can also see that the Connacht lad has received communication from the Munster lad and the Ulster lad and that the Connacht lad has changed his mind to agree with the Ulster lad.  He sees the consensus and decides to go with Ferris (O'Brien can always play 7 or 8). Eventually the Munster fan sees it's completely overruled and goes with Ferris.

So how are fans communicating not only their selections but indicating what they knew before their selections? They are using vector clocks!

You see, these lads aren't just rugby fans; they are nerds.

The lads agree an communication protocol between themselves. Not only will they text their choice for the number 6 jersey they will also include a vector of 4 numbers.  The first number will correspond to the Leinster version, the second the Ulster version, the third the Connacht version and the fourth the Munster version. Why are they doing this?  So they can indicate what they know about other people's selection before they made their own.

Ok, I appreciate that's confusing.  But rugby itself can be confusing. So be patient.  Let's go back to the example and examine the full messages they sent.

The Leinster fan starts - he sends out his tweet to all other fans: Sean O'Brien, {1, 0, 0, 0}.  This means Sean O'Brien is the first version from the Leinster fan (indicated by the value 1 as the first element in the vector).  It also indicates the Leinster fan had no record of any other version from any other fan before sending this out (indicated by the value 0 as the other three elements).  Then the Munster fan sends Denis Leamy {1, 0, 0, 1} to the Connacht fan. This indicates two things to the Connacht fan:

Denis Leamy

• The Munster fan got Sean O'Brien {1, 0, 0, 1} from the Leinster fan. The Connacht fan can be sure of this.  Because the first digit in the vector corresponds to the Leinster version.  This is 1.
• The Munster fan has updated his selection to Denis Leamy. This is indicated by the last element in vector having the value 1.  The last element corresponds to the Munster version.

Then, the Connacht fan sends Denis Leamy {1, 0, 1, 1} back to the Munster fan.  The Munster fan ascertains from this message that:

• The Connacht fan got the original Leinster choice  - since the first digit is one
• The Connacht fan got the Munster fans 1st selection  - since the last digit is one
• And poor old John Muldoon  because the Connacht fan has made his selection and agrees with the Munster fan.  This is ascertainable because the message is Denis Leamy and the third digit is 1.

Then, the Ulster fan sends a text with his selection to just the Connacht fan: Stephen Ferris {1, 1, 0, 0}. This indicates:

• The Ulster fan got the original Leinster choice - since first digit is one.
• The Ulster fan has indicated his preference for the Stephen Ferris - from the message itself and because the second digit is one.
• The Ulster fan knows nothing of the Munster selection or Connacht's selection - since last two digits are 0.

The Connacht fan sees things are getting messy now.  He's knows:

• Leinster fan chose O'Brien
• Muster fan chose Leamy
• Ulster fan chose Ferris

He thinks about it and decides to change his mind to Ferris.  So he sends Ulster: Ferris {1, 1, 2, 1}
With this message, the Connacht fan is saying:

Stephen Ferris

• I saw the Leinster version 1. You know that's O'Brien.
• I also saw the Munster's version 1.  You don't know that's Leamy.
• My initial selection was something other than Ferris. You don't know what that was either.
• My current selection is Ferris.

So overall, it is possible now to see that:

• Leinster's selection is O'Brien; 
• Ulster and Connacht say Ferris and Munster says Leamy

and very importantly

• Everyone saw Leinster's selection
• Connacht saw Munster and Ulster's selection
• Ulster saw Connacht's second selection

This information is very important as it makes it easier to resolve the conflict. It is not the responsibility of Vector clocks to resolve the conflict. That is someone else's responsibility.  But Vector clocks make it easier for someone else to resolve the conflict because they indicate what each actor knows or doesn't know about other actor's selection. Are you listening Declan Kidney?

Declan Kidney - does he use Vector clocks?

What does this mean in architectural terms?
The Rugby fan analogy serves as a nice intro to the use of vector clocks in distributed architectures.  In a simple and conventional architecture, you often have just one relational database.   It's running on a powerful box with lots of CPU power and disk space. The database is not distributed and it is not replicated.  You can have a column for each domain entity in its corresponding table to track its version.  This is usually a numeric value which indicates the version of the domain entity.  At any time a client can check the version it has in memory with the version it has in the database to ensure it does not have stale data.  All easy. All cool.

Now, sometimes you have replication.  Usually one database is the master and one (or more) is then the slave.  If the master goes offline, the slave takes over. When the master comes back online, resync happens. Again all straight forward.  No need for vector clocks.

In a high end architecture you are going to be under pressure to scale.  You'll be under pressure to ditch relational database as they don't scale well and use a NoSql architecture instead. In this architecture to provide availability the data is replicated and it is distributed.  There is usually no dedicated master because this makes it harder to scale.  Instead there is just a ring of database nodes ( also just called servers).  Scaling involves adding more nodes with the intent of distributing the work out evenly - you see there really is no master.  The nodes can communicate with each other in a peer to peer fashion.  They need to be able to indicate what version of the data they have relative to all other nodes.  To do this they use vector clocks.

There's no master and data can still go out of sync

So suppose your data is distributed over four nodes and to guarantee high availability each piece of data replicated is across the nodes (it doesn't have to be replicated on every node but let's just keep things simple). When the data gets updated, unless you block until every single node is up to date as part of the one transaction your nodes can go out of sync.  That's slow and even if you do decide to do it, what happens if a Node goes down or a new Node joins the group?  You are going to still have conflicts.

Ok, so say you try to add a version column to your tables to represent entity versions. This will only get you so far.  It will only tell you what version the entity is in the scope of each node. It's quite possible for two Nodes to both have version 4 but because they both could have gone down and come back up at different times they data may not match!  So it's easy to see why version columns which only hold a single version number will not work.

Cue Vector Clocks!  

The vector clock tells you the version information not just for a single actor in your system but for a range of actors in your system.

Reverting back to our Rugby fan analogy.  Let's say there are four servers.

• a request comes into your system from the leinster fan which makes the selection: Sean O'Brien. Leinster fan is identified as the first actor in the system so the vector clock is {1, 0, 0, 0}. All nodes get this version
• a request then comes in from Munster fan.  Munster fan is identified as the last actor in the vector clock. Now, only the 4th database node gets this the update because all other servers are down.  The vector clock is {1, 0, 0, 1}. But remember only the 4th node gets this because all the other servers are down.
• The third server comes back online and 4th node tells the 3rd node about Denis Leamy and that the vector clock is {1, 0, 0, 1}. It does this by using something like the Gossip protocol (text messages where one user contacts just one other user corresponds to the gossip protocol in our rugby fan analogy).  The 3rd Node updates to Denis Leamy and informs the 4th Node that it has done so.  Again it can do all this by Gossip protocol. It sends the message Denis Leamy {1, 0, 1, 1} back to the 4th Node.
• Now the Ulster fan sends a request in and suggests Ferris.  The 1st node is still offline and it's a bad day for the sys admin team because the 3rd and 4th node have gone offline again.  However, the second node can process it because it has come back online.  It updates to Stephen Ferris and updates its Vector clock to {1, 1, 0, 0}. Remember the second node got no updates from the 3rd and 4th node.  It would have got them eventually but it got the request from the Ulster fan first.
• Again the 3rd Node comes back online and again through the Gossip protocol it gets the update for Ferris.  It sees the contradiction and decides to resolve it by updating to Ferris.  It tells the 2nd node {1, 1, 2, 1}.   This shows the 2nd node that it had versions from itself and the 4th node that the 2nd node did not know about.  The 2nd node is cool with this because he also has Ferris so he has no conflict.
• The 1st node comes back online.  Through the gossip protocol he eventually sees that the 3rd is at Ferris {1, 1, 2, 1} and the 4th Node is at Leamy {1, 0 , 0, 1}. It's a conflict.  But he sees that the 3rd node is more up to date so he goes for Ferris. It's clear that Ferris is more up to date.  Every element in its vector clock is equal to or later than the every element in the 4th node's vector clock so he updates to Ferris.  The 4th eventually realises the same and does the same.

Choosing the Actor

You'll notice in my example, I have four fans (corresponding to four clients) and four servers.  You'll also notice I had the Ulster fan hitting the 2nd Node, the Connacht fan hitting the 3rd node and the Munster fan hitting the 4th node.   
In reality:

• Request could be hitting any node. Usually much more than one unless sys admin are having a bad day and are turning off machines to annoy you.
• The data was been replicated on every node.  In reality you wouldn't usually do this. It would be overkill
• And in the real world, you'd usually have way more clients than servers.  

Who were the actors? 
In the above example, it may have seemed like we chose the servers as the actors in our vector clocks but it was actually the clients.  Recall, the Leinster fan initially hit all four servers but the vector clock was just update to {1, 0, 0, 0} not {1, 1, 1, 1}. But let's consider the consequences of choosing the servers or the clients to be the actors for our vector clocks.

The server is the actor
Because there are less servers in a system this means your vector clocks will be smaller. If you've four servers your vector clocks will look like {S1, S2, S3, S4} instead of {C1, C2, C3, C4, C5, C6, C7, C8, C9, ...,C99999}. This means comparison between vector clocks will be quicker.  However it also means you can lose updates.  For example, suppose two clients are mapped to the same server and makes different updates.  The first update will be lost.

The client is the actor

In this case, vector clocks are much longer, comparisons take longer but all updates are tracked. This means that there can be a higher degree of certainty that conflicts get properly resolved.  In order to deal with the vector clocks getting unsustainable long, they can be culled periodically:  very old versions can be discarded.

Even when the client is the actor, the vector clock is still stored with the data in the server nodes.  The vector clock is always stored in the database nodes.

When are vector clocks used?
The primary use is in distributed systems. They are not responsible for resolving conflicts they are a versioning system that help someone else resolve them.  Sometimes it can be easy to resolve conflicts. For example, when there are two conflicting vector clocks, if every element in one vector clock is equal to or greater than every element in the other vector than it is easy to ascertain that that is the one more uptodate.  When that can't be done, the conflict has to be resolved by some other logic.

Vector clocks are used in systems such as Amazon's Dynamo.

References:
1. Basho's blog http://blog.basho.com/2010/04/05/why-vector-clocks-are-hard/
2. Basho's blog http://blog.basho.com/2010/01/29/why-vector-clocks-are-easy/
3. Amazon's Dynamo - http://www.allthingsdistributed.com/2007/10/amazons_dynamo.html
4. Rutgers http://www.cs.rutgers.edu/~pxk/rutgers/notes/clocks/index.html