10 November 2011

The magic of primary and unique keys

One of the first things Nooku developers experience with Nooku Framework is the magic with database tables. It’s a redemption for Joomla developers that there is no need to create table classes which describe the tables’ structure. Nooku Framework is smart enough to deal with primary and unique keys out of the box. And to go further, it can handle composite keys too, which is rarely supported by other PHP frameworks.

It’s all in the schema

If primary and unique keys are properly defined in the database schema, it is possible to retrieve an item without writing any line of code. In com_harbour’s boats table, harbour_boat_id is a primary key, and slug is a unique key. It is possible to get the same boat by using any of these keys:

index.php?option=com_harbour&view=boat&id=4
index.php?option=com_harbour&view=boat&slug=queen-mary-2

There is an important thing to note here. If an identity column exists (an auto increment key), the name “id” is used for it in the framework. So for example the column harbour_boat_id is accessed as $boat->id.

A look under the hood

Let’s look deep into how this works under the hood. In the first step, the database class fetches column and index information from the database. In the second step, the model requests the parsed information from the table class and creates a state for each key.

In the model, states are inserted for primary and unique keys automatically. It uses KConfigState::insert, which accepts 5 parameters:

  • name: The name of the state, in this case the name of the column.
  • filter: Defines how the value should be validated and sanitized. This information is also fetched from the table schema. See KDatabaseAdapterMysqli::_typemap for how different types are mapped to filters.
  • default: The state’s default value. This is set to NULL by the model when primary and unique keys are inserted.
  • unique: It tells the state whether the variable is unique or not. In this case the value is obviously TRUE.
  • required: This fifth parameter is important in case of composite unique and primary keys. Composite keys consist of two or more columns. In this parameter, Nooku Framework stores the name of the other columns which belong to the key. This way, it can be easily validated if all states are set for a composite key.

Fetching a row

If the request is unique (it contains at least one unique state), Nooku Framework is able to fetch the row. The magic happens in KModelTable::_buildQueryWhere:

$states = $this->_state->getData(true);

if(!empty($states))
{
    $states = $this->getTable()->mapColumns($states);
    foreach($states as $key => $value)
    {
        if(isset($value)) {
            $query->where('tbl.'.$key, 'IN', $value);
        }
    }
}

The 1st line requests the unique states from KConfigState. If there are any (3rd line), it iterates through them (6th line) and adds a WHERE statement to the query (9th line).

As you can see, Nooku Framework does a powerful job in the background. It doesn’t require you to specify information that is already out there. With this magic, developers save a lot of time.

In a next post we will dive deeper into foreign keys. Happy coding !

Posted in Framework, Tutorial and tagged as

  • http://pulse.yahoo.com/_ERSDRML2R7NQGXUCO7LIGXRVSU Alexander

    Can we handle Many-to-Many relations with the Database Adapter aswel?

  • http://johan.janssens.me Johan Janssens

    Many to many relations are not handled out of the box yet. This is being planned for the upcoming Alpha 5.

blog comments powered by Disqus