Using the SQL Adapter

Tonic comes with a MySQL adapter that can be used to quickly connect to a database and expose the rows in a database table as resources.

You can get the source files for this tutorial in this archive.

Lets run through an example of connecting to a database and loading a table of user data.

<?php
CREATE TABLE `user` (
  `uid` int(11) NOT NULL auto_increment,
  `firstname` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `company_uid` int(11) NOT NULL,
  PRIMARY KEY (`uid`)
);
?>

We use it in exactly the same way as the file adapter:

<?php

require_once 'tonic'.DIRECTORY_SEPARATOR.'lib'.DIRECTORY_SEPARATOR.'request.php';
require_once 'tonic'.DIRECTORY_SEPARATOR.'lib'.DIRECTORY_SEPARATOR.'resource.php';
require_once 'tonic'.DIRECTORY_SEPARATOR.'lib'.DIRECTORY_SEPARATOR.'smartyresource.php';
require_once 'tonic'.DIRECTORY_SEPARATOR.'lib'.DIRECTORY_SEPARATOR.'response.php';
require_once 'tonic'.DIRECTORY_SEPARATOR.'adapters'.DIRECTORY_SEPARATOR.'fileadapter.php';
require_once 'tonic'.DIRECTORY_SEPARATOR.'adapters'.DIRECTORY_SEPARATOR.'mysqladapter.php';

$mimetypes = array(
	'xml' => 'text/xml'
);

$request =& new Request();

$fileAdapter =& new FileAdapter($mimetypes, 'resources');

$userAdapter =& new MysqlAdapter(
	$mimetypes, // our list of mimetypes
	'user', // database table
	'%^/([0-9]+)%', // regex to extract primary keys from the URL
	'/%d', // template to turn primary keys back into a URL
	array('uid'), // list of primary keys
	array('uid', 'firstname', 'surname', 'email', 'company_uid') // list of fields
);
$userAdapter->connect(
	'localhost', // hostname
	'username', // database username
	'password', // database password
	'test' // database name
);

$resource =& $request->load($userAdapter, array(
	TONIC_FIND_FORCE_METADATA => array(
		'representation' => array(
			'/user.xml'
		)
	)
));
$response =& $request->exec($userAdapter, $resource);

if ($resource && $representation =& $resource->loadRepresentation($fileAdapter)) {
    $response =& $representation->get($request);
}

$response->output();

?>
dispatch.php

The interesting part of this is the MysqlAdapter itself, let's have a look at it in detail.

First of all, we create an instance of the MysqlAdapter class, the parameters configure the adapter wiring the database up to Tonic.

$keyValues
The third parameter is a regular expression used to extract the primary keys from the request URL. In this case, our data has a single primary key of uid and we'll use it to identify our resources.
$template
The fourth parameter provides a printf template for turning our primary keys back into a URL, for this example /%d will do the trick.
$primaryKeys
Our fifth parameter lists the primary keys our table has.
$fields
Our sixth parameter lists the fields we want to fetch from our table. If not given, then our adapter will inspect the database to find the primary keys and fields that exist.
$datetimeFields
Finally, the seventh parameter lists fields that the adapter should treat as datetime fields and automagically convert into timestamps for us.

Next we need to connect to our database by providing connection details and then we're free to start using our database adapter in the same way as our file adapter.

Finally we need to define our output representation otherwise we'll just get the standard Tonic output format. So if we want XML, we'll need a resource containing our XML representation which we'll store in the file adapter:

class: SmartyResource
mimetype: text/xml

<?xml version="1.0"?>
<user uid="{$resource->uid|escape}">
	<firstname>{$resource->firstname|escape}</firstname>
	<surname>{$resource->surname|escape}</surname>
	<email>{$resource->email|escape}</email>
</user>
resources/user.xml

Adding an index resource

So we've exposed our database table as resources with XML represenations, but how do we know which rows exist and how to access them? Simple, we need a collection resource to act as an index to our data.

<?php

class Users extends SmartyResource {
	
	function &get(&$request) {
		
		global $userAdapter;
		$users =& Resource::findAll($userAdapter, '/');
		
		$this->_smarty->assign('users', $users);
		
		return parent::get($request);
	}
	
}

?>
lib/users.php

Here our GET method is finding all of the resources in our SQL adapter and assigning them to Smarty. Yes, we're using an evil global to access our adapter, feel free to refactor this into something nicer.

class: Users
mimetype: text/xml

<?xml version="1.0"?>
<users>
	{foreach from=$users item=user}
	<user href="{$request->rootUrl}/{$user->uid|escape}">{$user->firstname|escape}</user>
	{/foreach}
</users>
resources/default.xml

Our representation is starting to look familiar, a Smarty template in the Tonic format referencing our resource class in it's metadata. The only new thing is the {$request->rooturl} Smarty variable that returns us the URL up to the root of our application.

if ($request->url == '/') {
	
	$resource =& $request->load($fileAdapter);
	$response =& $request->exec($fileAdapter, $resource);
	
} else {
	
	$resource =& $request->load($userAdapter, array(
		TONIC_FIND_FORCE_METADATA => array(
			'representation' => array(
				'/user.xml'
			)
		)
	));
	$response =& $request->exec($userAdapter, $resource);
	
}
dispatch.php

Finally we need to adjust our dispatcher to look in our file adapter for the root resource and we're ready to go.

Making it read/write

Our data is exposed as resources and we can read it, but sometimes we want to be able to write to it too. To update our resources we do a HTTP PUT to the resources URL, to create a new resource we POST to our collection resource, and finally to delete one we use HTTP DELETE.

<?php

class Users extends SmartyResource {
	
	function &get(&$request) {
		
		global $userAdapter;
		$users =& Resource::findAll($userAdapter, '/');
		
		$this->_smarty->assign('users', $users);
		
		return parent::get($request);
	}
	
	function &post(&$request, &$adapter) {
		
		global $userAdapter;
		
		return $this->_appendResource($request, $userAdapter);
	}
	
}

?>
lib/users.php

The addition of a post method in our Users resource class will allow us to POST a user representation to the collection, we pass in the MySQL adapter so that the new resource gets created in our database rather than the file system where our collection resource is based.

Again, we're using an evil global variable to get access to our database adapter. A better solution would be to have it loaded up as part of the collection resource creation process or to use the singleton pattern to grab it.

<?php

class User extends Resource {
	
	function &put(&$request) {
		return $this->_updateResource($request);
	}
	
	function &delete(&$request) {
		return $this->_deleteResource($request);
	}
	
}

?>
lib/user.php

Adding put and delete methods to our user resource allows us to update and delete a specific database row. This time we don't need to pass in our database adapter as the resource already knows where it was loaded from.

Conclusion

So we've seen how to use the MySQL adapter to hook up a MySQL database table to Tonic. The MySQL adapter extends the more generic SQLAdapter class that can be used as a base for writing an adapter to any SQL database.

Download the source files for this tutorial. Back to documentation home
Created Jan 19, 2008, last modified Feb 15, 2008