KDatabase

Version 20, last updated by pagchen at November 07, 2011 23:35 UTC

This wiki has been updated for version 0.7-alpha4

1. Naming conventions

When you're defining one or more tables to use in your component, it's always a good idea to put some thought in how you'll name them. Nooku doesn't force you to use any convention, but it does make your life a lot easier when you follow these best practices.

1.1 Prefix

First of all, we have the #__ prefix, which is replaced with the user defined prefix. When installing Joomla, the default prefix is jos_, but it could be anything. By using #__, you never have to worry about it again.

1.2. Name

Next, we want to prevent name collisions. Component names in Joomla are unique, so using that for the first part of your table names will fix that: #__mycomponent_. Don't use #__com_mycomponent_, that's unnecessary luggage.

Let's take a classic example: a component that manages a book collection, called com_library. The table name should reflect the contents of the table. This is always plural: you use a table to store multiple items. Bad: #__library_book. Good: #__library_books. Even if your component uses only one table, you shouldn't use #__mycomponent. You might need more tables later, eg #__library_authors and #__library_publishers.

1.2.1. Relationship tables

For 1:N relationships, tables must be named #component_singleentity_pluralentity, e.g.: #component_customer_orders

For N:N relationships, tables must be named #component_pluralentity_pluralentity, e.g.: #component_usergroups_users

1.3. Primary key

For the primary key, use the table name, in singular, followed by id.

'#__library_books -> library_book_id'
'#__library_authors -> library_author_id'

There are some very important advantages to this naming strategy. It's easier for you or other people reading your code, what tables belong to what component, and what's in them. Relations between tables are now very clear as well. #__library_books for example has two additional fields called library_author_id and library_publisher_id, which of course link an author and a publisher to the book.

But more importantly, it's easy to write code now that can detect those relations automatically. As your com_library becomes smarter, you could add data mapping functionality that can understand the relations between tables, without the need to hardcode them.

1.4. SERIAL keyword

In most situations, you'll want to use auto_increment for primary keys. The SERIAL keyword is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

BIGINT, combined with UNSIGNED, allows your primary keys to be as big as possible.

1.5. Wrapping up

The table creation statement in your install.sql file will look something like this:

CREATE TABLE IF NOT EXISTS `#__library_books` (
  `library_book_id` SERIAL,
  `title` VARCHAR(255) NOT NULL,
  `isbn` VARCHAR(20) NOT NULL,
  `description` TEXT NOT NULL,
  `pages` INT(11) NOT NULL,
  `published` TINYINT(1) SIGNED  NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Stores all the books in the library';

1.6. Casing

The column names need to be lowercase by convention to make the auto-quoting in queries work. If you are querying third party tables that have uppercase or capitalized column names, simply lowercase them in your query as the MySQL column names are not case-sensitive.

Also, MySQL reserved words must be in uppercase. For instance, aliasing a table with 'as' in lowercase will generate a syntax error in the generated query.

2. Basics

Nooku allows you to interact with the database with very little to no queries. This makes your code much easier to write and maintain, and allows for third party plugins to interact with your extension, because they don't have to jump through hoops to parse your queries. First let's make a database table. Add a couple of records.

CREATE TABLE IF NOT EXISTS `#__harbour_boats` (
   `harbour_boat_id` SERIAL,
   `name` varchar(255) NOT NULL,
   `description` text NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2.1. Getting a row from the database based on an id

Note: we're skipping MVC in the following examples, just to keep them simple and focus on the db. It's not the recommended way of building extensions.

In /administrator/components/com_harbour/harbour.php, we write the following code. Access the extension using: yoursite.com/administrator/index.php?option=com_harbour&id=1

<?php
// Get the id from the GET request, and sanitize it using the 'int' filter,
// to make sure it is an integer.
$id = KRequest::get('get.id', 'int');

// The plain php way of writing this would be:
// $id = (int) $_GET['id'];
// Don't do that anymore!

// Now we get a table object from the factory:
$table = KService::get('com://admin/harbour.database.table.boats');
// This tells the factory to create a table object that is linked to 
// the #__harbour_boats table. Please note that you don't need to define
// a HarbourTableBoats class, it's all handled automatically by Nooku.

// Now we ask the table object to give us a row object:
$row = $table->select($id, KDatabase::FETCH_ROW);

// We can now easily access each field in the row:
echo 'Name: '. $row->name .PHP_EOL;
echo 'Description: '. $row->description .PHP_EOL;
echo 'Id: '. $row->id .PHP_EOL;
// Note: $row->id is a magic alias of $row->harbour_boat_id

2.2. Getting a row from the database based on a where clause

Access the extension using : /administrator/index.php?option=com_harbour&name=Titanic

<?php
$name = KRequest::get('get.name', 'string');
$table = KService::get('com://admin/harbour.database.table.boats');
// $table is a KDatabaseTable object.

// We will now select a row based on the name.
// First we need a KDatabaseQuery object
$query = $table->getDatabase()->getQuery();

// Now we add a WHERE clause to the query
$query->where('name', '=', $name);

// We tell the table object to give us a single row that matches the query
$row = $table->select($query, KDatabase::FETCH_ROW);

// $row is now a KDatabaseRow object containing the data of the row with name = $name
// once again, we can now easily access each field in the row:
echo 'Name: '. $row->name .PHP_EOL;
echo 'Description: '. $row->description .PHP_EOL;
echo 'Id: '. $row->id .PHP_EOL;

2.3. Changing data in the database

The full name of the Titanic is 'RMS Titanic', so we will change that. We also want to add some info to the description

<?php
$table = KService::get('com://admin/harbour.database.table.boats');
$query = $table->getDatabase()
    ->getQuery()
    ->where('name', '=', 'Titanic');
$boat = $table->select($query, KDatabase::FETCH_ROW);

//Retrieve the first row from the rowset

// Let's change some data
// $boat->title= 'RMS Titanic';
$boat->description = $row->description.PHP_EOL.'Unsinkable!';

// Tell the row to save
$boat->save();
// If you look in the database, you should see the changes.

// We just got word that the Titanic has sunken! Too bad:
$boat->delete();

2.4. Selecting multiple rows from the database

The Titanic is lost, but we still have other boats out there! Let's find all the boats that are near icebergs.

<?php
$table = KService::get('com://admin/harbour.database.table.boats');
$query = $table->getDatabase()
    ->getQuery()
    ->where('description', 'LIKE', '%iceberg%');

// This time we don't fetch a single row, but a set (aka a collection)
// of all rows matching the new query
$boats = $table->select($query);

// $boats is a KDatabaseRowset. This is at the same time an object and an array.
// that allows us to treat it like a normal array:
echo 'There are '. count($boats) . ' in danger!'.PHP_EOL;

// Each item in the KDatabaseRowset is a separate KDatabaseRow object
foreach($boats as $boat)
{
    echo 'To the Captain of '.$boat->name
        .': Please be careful!'.PHP_EOL;
}

3. Behaviors

To create a custom database behavior the following steps are needed:

Create your behavior file and put it in the databases/behaviors subdirectory of your component.

e.g. to create a function to operate before a record is deleted:
```
 class ComFooDatabaseBehaviorMybehavior extends KDatabaseBehaviorAbstract
 {
     protected function _beforeTableDelete(KCommandContext $context)
     {
          ... your code here ...
         return true;
     }
 }
```
If your function returns false, then the following action will not be carried out

The events that can be added are:
- before.table.select
- before.table.insert
- before.table.update
- before.table.delete and after the same 4 events

Tell Koowa that you want your behavior actioned

In your model constructor :

 class ComHarbourModelBoats extends KModelTable
 {
 public function __construct(array $options = array())
 {
 $options['table_behaviors'] = array('lockable', 'creatable', 'modifiable');
 parent::__construct($options);
 }
 }

In your table constructor :

 class ComHarbourDatabaseTableBoats extends KDatabaseTableAbstract
 {
 public function __construct(array $options = array())
 {
 $options['behaviors'] = array('lockable', 'creatable', 'modifiable');
 parent::__construct($options);
 }
}

At runtime :

 KService::get('com://admin/harbour.database.table.boats')->addBehaviors('admin::com.foo.behavior.fooable');

4. Creating rows in different tables

One question someone new to Nooku will want to know is: "How do I add data to a different table?"

You might have a pricing database where the price information is stored separately to the item description,

Steps:

//Assemble the data you want into an array where the key is the column name and the value is the data.
$rowdata = array( "column1" => value, ... )
//if there is no "id" key, then the data will be inserted as a new row

//create a row object
$newrow =  KService::get('com://admin/foo.row.price);

//load the data
$newrow->setData($rowdata);

$newrow->save();