Context Navigation

The database library

This section describes the new DBX library (X for eXtended).

Loading

Loading is done with the using() statement, which technically makes this a module. It is however located in the libraries folder, and it doubles as a library, so for now we will leave it as a library.

Lepton::using('dbx');

Autoloading

The DBX library can be autoloaded using the Lepton::autoload() method:

Lepton::autoload('dbx','db');

This will give you access to $this->db in all your controllers and models.

Configuration

The following keys are used:

Key	Default	Description
`lepton.dbx.sets.SETNAME`	n/a	contains all the components of the specified set.
`lepton.dbx.options.defaultset`	`default`	the default set to use (normally 'default')
`lepton.dbx.options.balanceload`	`true`	boolean, if true a bit of randomness is applied to balance the load
`lepton.dbx.options.cache`	n/i	query cache duration in seconds
`lepton.dbx.options.lockschemas`	`true`	If this key is set to true, no tables can be created from within the code
`lepton.dbx.options.exclusivereaders`	`false`	If this key is set to true, a writer is not accepted as a reader. Note that this means that an additional connection will be created if a page is to both read and write from the database.

Each database set consists of a dictionary holding the name of the hosts and the settings.

Key	Type	Example	Description
`hostname`	String	`localhost`	The name of the host. Omit this for PostgreSQL in order to use domain sockets.
`port`	Integer	`3306`	The port (or null for default).
`username`	String	`dbuser`	The username to connect as.²
`password`	String	`dbpass`	The password to connect with.³
`database`	String	`dbname`	The database to use.¹
`type`	String	`mysqli`	The driver/connector to use. Currently supported values are `mysqli`, `pgsql` and `sqlite3`
`socket`	String	`/var/mysql.sock`	The socket to use instead of port.
`cluster`	String	`RW 1`	The role and priority of the host separated by a space. The priority is optional. The role consists of a "R" for reads and "W" for writes. Doesn't apply to SQLite.
`sslmode`	n/a	n/a	The SSL mode to use, passed to the connector.

^{1) For the SQLite connector this is the database filename.} ^{2) Authentication is not available for SQLite databases} ^{3) For SQLite the password is used as the encryption key}

Example

// lepton.dbx: The new minty fresh database api. Hostname, Port, Username,
// Password, Database and Type should be fairly obvious. Cluster sets the
// role of the server in the set. "R" for Reading queries, "W" for Writing
// (updating) queries. The number specifies priority. The lower the value,
// the higher the priority.
//
// Place any additional set in lepton.dbx.sets.SETNAME
config::set('lepton.dbx.sets.default', array(
	// First host in set. Handles both reads and writes.
	'localhost' => array(
		'hostname'	=> 'db.host.tld',
		'port'		=> null,
		'username'	=> 'username',
		'password'	=> 'password',
		'database'	=> 'database',
		'type'		=> 'mysqli',
		'cluster'	=> 'RW 2'
	),
	// Second host in set, this one handles reads only.
	'replicated' => array(
		'hostname'	=> 'db2.host.tld',
		'port'		=> null,
		'username'	=> 'username',
		'password'	=> 'password',
		'database'	=> 'database',
		'type'		=> 'mysqli',
		'cluster'	=> 'R 1'
	)
));
// Select what set to load as default, if the load should be balanced
// evenly to hosts. This one might be a bit funky. Cache sets the lifetime
// of the items in the query cache. Set to 0 or null to disable.
config::set('lepton.dbx.options.defaultset','default'); 
config::set('lepton.dbx.options.balanceload',true); // Distribute hosts
config::set('lepton.dbx.options.cache',300); // Cache for 5 minutes

Basic usage

The DBX library is using the Singleton class to provide singleton behavior. Therefore all you have to do in order to use the library is to request an instance of it, with the set as the optional second parameter:

$db1 = DBX::getInstance(DBX,'myset'); // Create an instance of myset
$db2 = DBX::getInstance(DBX,'myset'); // Retrieves the same instance

The default set is named "default". You can change this in the configuration using the lepton.db.options.defaultset key.

Running queries

Queries are performed using four basic methods:

$db->getRows() returns a set of rows as a raw array. Use with SELECT.
$db->getSingleRow() returns a single row. Use with SELECT.
$db->insertRow() inserts a row (or a set of rows). Will return the autonumber (last insert id). Use with INSERT.
$db->updateRow() works like insertRow() but doesn't return the autonumber. Use with REPLACE, DELETE, and UPDATE.

All four of these methods use sprintf to format the strings, so provide the string and the values:

$rows = $db->getRows("SELECT * FROM foo WHERE bar='%s'", $baz);

Recordsets

Lepton also comes with a utilityclass named Recordset that can be used to enumerate rows in a more record oriented fashion:

$rs = new RecordSet($db->getRows('SELECT * FROM test;'));
while(!$rs->eof()) {
  echo $rs->field;
  $rs->moveNext();
}

Writing connectors

A connector is a class named XxxConnection that extends the DatabaseConnection class. Each connector needs to implement the functions defined in the !IDatabaseConnection interface:

interface IDatabaseConnection {
   public function connect();
   public function disconnect();
   public function getDriver();
   public function getHandle();
   public function getAutoNumber();
   public function get($sql);
   public function escape($args);
}

The connector class must implement the connect method, which establishes the connection. This function accepts no parameters, but the initialization data is passed to the constructor and unless overriden stored in $this->conninfo. It is recommended that you call on the parent::__construct() method from within your constructor if you decided to override it. This is to make sure that everything is set up correctly for the connection pool.

It also needs to implement the disconnect method to close the connection, the getAutoNumber to return the last autonumber assigned by an INSERT statement, and the get method that considering its ambiguous name is responsible for all requests, not only the SELECTs. For more information and reference implementations, see trunk/lepton/modules/dbx.php and trunk/lepton/modules/dbx-mysqli.php.

Also, if the connector is to be able to make use of the schemamanager, it needs to inherit the abstract class SchemaManager that implements the ISchemaManager interface:

interface ISchemaManager {
   public function hasSchema($schema);
   public function loadSchema($schema,$force=false);
   public function checkSchema($schema);
   public function getSchema($schema);
}

Creating tables

Tables are refered to as "schemas" in Lepton. In the future there will be some sort of neat way to describe these schemas to bring the actual table creation (and possibly data updating) a little closer to being a ORM implementation. Lepton will probably never be a full featured ORM tho, so get used to SQL. Table creation must be made easier to integrate in the code tho.

Tables are created using a SchemaManager. Since this is a feature that is very much under development, it won't be covered in much depth. To use the current implementation, create a file named tablename.sql in the /lepton/schemas/ or /application/schemas/ folder. You can then use the checkSchema() method to check if it exists, and if not create it.

$db = DBX::getInstance(DBX);
$sm = $db->getSchemaManager();
$sm->checkSchema('foo');

Also see:

Download in other formats:

Plain Text