It has been a little bit quiet in the past months on the blog. Some rumoured that Nooku is death, others claimed we moved to Mars, … in good Nooku tradition no news is good news! Stick to the code, right?!

Those of you who followed us closely have noticed the constant stream of progress at lightning speed. Anything slower, is simply not fast enough.

Here is a little big update right from the coding frontline. Make yourself a coffee, get a beer, sit back relax. Here we go!

‘Git’-ting it

The Nooku code base has been moved to Git at the beginning of the year. The SVN repositories are abandoned. They are probably invested with vermin, bugs, and other little unwanted creatures. Lights have been turned off, go there on your own accord. You have been warned!

The Nooku codebase has been brought back to onto 2 spaces on Assembla: Nooku Framework & Nooku Server.
… continue reading …


An often asked question on the Nooku Framework mailing list is how to deal with relations in databases. Nooku Framework doesn’t support relations out of the box yet, but fortunately the InnoDB engine in MySQL does!

There are three important rules that must be met in order to create relations:

  1. Both tables must be InnoDB tables and must not be TEMPORARY tables.
  2. Keys must have similar internal data types.
  3. Both foreign and referenced keys need to be indexed.

A basic example

Let’s see the basics through an example:

Assume that we have an articles table with two columns: article_id and title. We have an authors table as well with the columns: author_id and name. Here is the SQL code for the tables:

CREATE TABLE IF NOT EXISTS `articles` (
`article_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`article_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS `authors` (
`author_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

We want to assign authors to articles. An author can belong to more than one articles, and an article can belong to more than one authors. So this is a many-to-many relation. That’s why we need a relation table too, which has article_id and author_id columns:

CREATE TABLE IF NOT EXISTS `articles_authors` (
`article_id` INT UNSIGNED NOT NULL,
`author_id` INT UNSIGNED NOT NULL,
FOREIGN KEY (`article_id`)
REFERENCES `articles`(`article_id`)
ON DELETE CASCADE,
FOREIGN KEY (`author_id`)
REFERENCES `authors`(`author_id`)
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

As you can see, we specified the foreign keys with CASCADE and RESTRICT options. Let’s try to delete an article. Notice that relations in the articles_authors table are deleted with it automatically. Now let’s try to remove an author. If the author is referenced in the relation table, then MySQL refuses to delete it. This ensures referential integrity.

Keeping referential integrity

By using InnoDB, you can prevent accidental removal of referenced rows. Other than its roboust data management features, it has usability advantages too. For example some of the database management tools support foreign keys too. For example in Sequel Pro, an arrow is displayed next to the column’s value, and it takes you to the referenced row when you click on it. This little feature can be useful many times.

foreignkeys

InnoDB has become the default engine in MySQL 5.5 and has many features over MyISAM. I am highly recommending every developer to consider using it in their applications.

This was just a basic introduction to foreign keys, it is capable of much more! If you are interested, visit the MySQL Reference Manual.


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.

… continue reading …