NotORM

NotORM is a PHP library for simple reading data from the database. The most interesting feature is a very easy work with table relationships. The overall performance is also very important and NotORM can actually run faster than a native driver. NotORM for databases is like SimpleXML for XML documents.

Examples
API
Performance
Column names
Persistence
FAQ
Dibi
Why?

Database schema NotORM provides a very intuitive API. Examples are showing the API on a simple database that is included in the download.

Connecting

include "NotORM.php";
$connection = new PDO("mysql:dbname=software");
$software = new NotORM($connection);

NotORM uses the PDO driver to access the database.

Reading data

foreach ($software->application() as $application) { // get all applications
	echo "$application[title]\n"; // print application title
}

Use a method call to get rows from the table. Use square brackets to access a column value.

Limiting result set

$applications = $software->application()
	->select("id, title")
	->where("web LIKE ?", "http://%")
	->order("title")
	->limit(10)
;
foreach ($applications as $id => $application) {
	echo "$application[title]\n";
}

The example gets first ten applications with HTTP URL ordered by a title.

Getting a single row

$application = $software->application("title = ?", "Adminer")->fetch();
$application = $software->application[1]; // get by primary key

Relationships

echo $application->author["name"] . "\n"; // get name of the application author
foreach ($application->application_tag() as $application_tag) { // get all tags of $application
	echo $application_tag->tag["name"] . "\n"; // print the tag name
}

Simple work with relationships is a killer feature of NotORM. Method call ->() on a row object gets all rows from referencing table, member access -> stands for table referenced by the current row and array access [] gets always a value in the row.

Database

$db = new NotORM($pdo[, $structure[, $cache]]) Get database representation

Result

`$table = $db->$tableName([$where[, $parameters[, ...]]])`	Get representation of table `$tableName`
`$table->where($where[, $parameters[, ...]])`	Set `WHERE` (explained later)
`$table->order($columns)`	Set `ORDER BY`, can be expression (`"columm DESC, id DESC"`)
`$table->select($columns)`	Set retrieved columns, can be expression (`"col, MD5(col) AS hash"`)
`$table->limit($limit[, $offset])`	Set `LIMIT` and `OFFSET`
`$table->group($functions[, $having])`	Get result of aggregation functions as array (`"COUNT(*), MAX(published)"`)
`count($table)`	Get number of rows in result set
`(string) $table`	Get SQL query

Repetitive calls of where, order and select are possible – it will append values to the previous calls (where by AND). These methods plus limit provide a fluent interface so $table->where()->order()->limit() is possible (in any order). All methods are lazy – the query is not executed until necessary.

The $where parameter can contain ? or :name which is bound by PDO to $parameters (so no manual escaping is required). The $parameters[, ...] can be one array, one associative array or zero or more scalars. If the question mark and colon are missing but $parameters are still passed then the behavior is this:

`$table->where("field", "x")`	Translated to `field = 'x'` (with automatic escaping)
`$table->where("field", null)`	Translated to `field IS NULL`
`$table->where("field", array(1, 2))`	Translated to `field IN ('1', '2')` (with automatic escaping)
`$table->where("field", $db->$tableName())`	Translated to `field IN (SELECT id FROM $tableName)`

Row

`foreach ($table as $id => $row)`	Iterate all rows in result
`$row = $db->$tableName[$id]`	Get single row with ID `$id` from table `$tableName`
`$row = $table[$id]`	Get row with ID `$id` from the result
`$row = $table->fetch()`	Get next row from the result
`$data = $row[$column]`	Get data from column `$column`
`$row2 = $row->$tableName`	Get row from referenced table
`$table2 = $row->$tableName([$where[, $parameters[, ...]]])`	Get result from referencing table
`$row->__toString()`	Get primary key value

$row["author_id"] gets the numerical value of column, $row->author gets row representing the author from the table of authors.

$row->$tableName() builds "SELECT * FROM $tableName WHERE {$myTable}_id = $row[$primary]". The returned object can be customized as usual table results (with order, limit, ...) and then executed. All results are lazy and execute only a single query for each set of rows.

Structure

The object passed in second argument of NotORM constructor must implement the NotORM_Structure interface:

`getPrimary($table)`	Get primary key of a table in `$db->$table()`
`getReferencingColumn($name, $table)`	Get column holding foreign key in `$table[$id]->$name()`
`getReferencingTable($name, $table)`	Get target table in `$table[$id]->$name()`
`getReferencedColumn($name, $table)`	Get column holding foreign key in `$table[$id]->$name`
`getReferencedTable($name, $table)`	Get table holding foreign key in `$table[$id]->$name`

NotORM defines a structure suitable for databases with some convention (this is the default structure):

$structure = new NotORM_Structure_Convention(
	$primary = 'id',
	$foreign = '%s_id',
	$table = '%s'
);

There is also an auto-discovery class for MySQL. It reads meta-information from the database, which slows things down.

$structure = new NotORM_Structure_Discovery($pdo);

Cache

The object passed in third argument of NotORM constructor must implement the NotORM_Cache interface (default null stands for no cache).

`load($key)`	Get data stored in the cache (return null if there is no data)
`save($key, $data)`	Save data to cache

NotORM defines following caches:

`new NotORM_Cache_Session`	Uses `$_SESSION["NotORM"]`
`new NotORM_Cache_File($filename)`	Uses `$filename`
`new NotORM_Cache_Database(PDO $connection)`	Uses `notorm` table in database
`new NotORM_Cache_Memcache(Memcache $memcache)`	Uses `NotORM.` prefix in Memcache
`new NotORM_Cache_APC`	Uses `NotORM.` prefix in APC

The table used by NotORM_Cache_Database must already exist (script should not have privileges to create it anyway):

CREATE TABLE notorm (
	id varchar(255) NOT NULL,
	data text NOT NULL,
	PRIMARY KEY (id)
)

Error reporting

NotORM does not escalate errors. Non-existing table produces SQL error that is reported by PDO conforming to PDO::ATTR_ERRMODE. Non-existing columns produces the same E_NOTICE as an attempt to access non-existing key in array.

NotORM does not use neither repetitive queries nor joins because they can be slow.

Repetitive queries

One of the biggest bottlenecks in communication with databases is repetitive queries. Each round-trip to send a query costs some time and sending queries in a loop is a very bad practice.

// this is a wrong approach
foreach ($pdo->query("SELECT * FROM application") as $application) {
	$author = $pdo->query("SELECT * FROM author WHERE id = $application[author_id]")->fetch();
}

Joins

Joins are much better but they have also drawbacks:

Complex query with many joins may be difficult to evaluate by a SQL server.
The same data are usually transferred several times.

To explain the second point, consider the following query getting all applications with their tags:

SELECT application.*, tag.*
FROM application
LEFT JOIN application_tag ON application.id = application_tag.application_id
LEFT JOIN tag ON application_tag.tag_id = tag.id
ORDER BY application.id

id	author_id	title	web	slogan	id	name
1	11	Adminer	http://www.adminer.org/	Database management in single PHP file	21	PHP
1	11	Adminer	http://www.adminer.org/	Database management in single PHP file	22	MySQL
2	11	JUSH	http://jush.sourceforge.net/	JavaScript Syntax Highlighter	23	JavaScript
3	12	Nette	http://nettephp.com/	Nette Framework for PHP 5	21	PHP
4	12	Dibi	http://dibiphp.com/	Database Abstraction Library for PHP 5	21	PHP
4	12	Dibi	http://dibiphp.com/	Database Abstraction Library for PHP 5	22	MySQL

Notice how both the application and tag data are transferred repetitively. This costs time and memory.

Constant number of queries

NotORM uses a different approach – it sends a constant number of simple queries (one query for each table type).

foreach ($software->application() as $application) {
	echo "$application[title]\n";
	foreach ($application->application_tag() as $application_tag) {
		echo "\t" . $application_tag->tag["name"] . "\n";
	}
}

This code, which gets all applications and their tags, sends just three simple queries:

SELECT * FROM application;
SELECT * FROM application_tag WHERE application_id IN ('1', '2', '3', '4');
SELECT * FROM tag WHERE id IN ('21', '22', '23', '24');

The volume of transferred data is minimized and the number of queries stays low no matter how many rows are processed.

This approach is also great for MySQL query cache which is deleted each time when somebody modifies any row in any table joined in a query. Query cache utilization of this approach is optimal because each table has its own query so periodic modifications of some table does not affect query cache of other queries.

Limiting selected columns

Another bottleneck in a database communication is selecting columns which are not used afterwards, usually by SELECT *. First of all, NotORM has the select method which can be used to limit retrieved columns. However, this method does not need to be used if you use a cache to store the information about used columns. Take this example:

$software = new NotORM($pdo, null, new NotORM_Cache_Session);
foreach ($software->application() as $application) {
	echo "$application[title]\n";
}

The first query will be SELECT * FROM application. Then NotORM stores the information about used columns to the cache and all next requests will issue SELECT id, title FROM application. If you change the script and add more columns then one extra SELECT * will be issued and all next queries will be optimal again.

Please note that the cache key is ($table, $where) and that no columns are ever removed from the cache (you have to clear the cache after removing some columns from the script to get optimal queries).

NotORM uses a simple convention for table and column names by default:

Tables	`table`
Primary keys	`id`
Foreign keys	`table_id` (target table)

It is however possible to use any other convention – plural table names, primary key including the table name and so on. You just need to create an object implementing the NotORM_Structure interface or use a Convention object with custom parameters:

$structure = new NotORM_Structure_Convention(
	$primary = "id_%s", // id_$table
	$foreign = "id_%s", // id_$table
	$table = "%ss" // {$table}s
);
$software = new NotORM($pdo, $structure);

Simple conventions can be described by constructor parameters (%1$s or first %s stands for the first method argument, %2$s for the second). Exceptions from these conventions can be defined by an extension class. Lets say that the database uses a common convention but there are columns created_by_id and modified_by_id in several tables all linking to the table user:

class UserStructure extends NotORM_Structure_Convention {
	function getReferencedTable($name, $table) {
		if ($name == 'created_by' || $name == 'modified_by') {
			return 'user';
		}
		return parent::getReferencedTable($name, $table);
	}
}
$structure = new UserStructure;

There is also an auto-discovery class for MySQL >= 5 which can work with InnoDB tables in the database without any convention. It however costs some time to discover the primary and foreign keys from meta-information.

NotORM supports data modification (insert, update and delete) in branch update. No data validation is performed by NotORM but all database errors are reported by the standard PDO error reporting.

API

`$id = $db->$tableName()->insert($array)`	Insert row into table `$tableName` and return the auto increment value
`$id = $db->$tableName($array)`	Shortcut for `$db->$tableName()->insert($array)`
`$affected = $table->update($array)`	Update all rows in table result
`$affected = $table->delete()`	Delete all rows in table result
`$affected = $row->update($array)`	Update single row
`$affected = $row->update()`	Update single row by real modifications
`$affected = $row->delete()`	Delete single row
`$table->freeze()`	Disable persistence for this result and its children

The $array parameter holds column names in keys and data in values. Values are automatically quoted, SQL literals can be passed as objects of class NotORM_Literal, for example:

$array = array(
	"title" => "NotORM",
	"author_id" => null,
	"created" => new NotORM_Literal("NOW()"),
);

Example

$id = $software->application(array(
	"author_id" => 11,
	"title" => "NotORM",
));
$application = $software->application[$id]; // row is retrieved from database to get real stored values
$application["web"] = "http://www.notorm.com/";
$application->update(); // only web will be saved, no other columns
$application->delete();

This code runs following queries:

INSERT INTO application (author_id, title) VALUES ('11', 'NotORM');
SELECT * FROM application WHERE (id = '5');
UPDATE application SET web = 'http://www.notorm.com/' WHERE (id = '5');
DELETE FROM application WHERE (id = '5');

Does NotORM require primary keys in all tables?
No. If the column returned by getPrimary method does not exist in the table or if it is not included in retrieved data (specified by select method) then the returned rows are indexed by their order. Then it is not possible to reference other tables with ->() from this row.

Does NotORM support multi-column primary keys?
No, if you want to access relationships. These tables are treated same as tables without primary keys. Methods getPrimary, getReferencingColumn and getReferencedColumn must return single column.

Does NotORM automatically escape table and column names?
No, NotORM doesn't escape any identifiers (in contrast to data, which is always escaped) so it is your responsibility to escape identifier if the column or table name collides with an SQL keyword. For example if a column have name order (which is an SQL keyword) then you have to call where("`order` IS NOT NULL") for MySQL. Returned column names are however always without quotes so you always access $row["order"]. Tables are even more tricky – if the table has a name select then you have to call $db->{"`select`"}(). Some escaping can be also done in NotORM_Structure implementation. The best approach is to avoid using the SQL keywords for identifiers.

Does NotORM forbid some table names?
If the table name collides with PHP magic method (starting with two underscores or offsetExists, offsetGet, offsetSet, offsetUnset and getIterator) then it can be accessed as $row->__call('offsetGet'). There are no magic properties used by NotORM.

What about memory consumption?
NotORM stores all data retrieved from the database to memory. This is the same approach as buffered queries use (enabled by default for MySQL). To work with extremely large result sets (for example to generate Sitemaps) it is better to retrieve them in chunks for example with $table->limit(1000). Your database server will be glad too.

Dibi is tiny and smart database abstraction layer for PHP. NotORM support for Dibi is available in branch dibi. Differences:

NotORM constructor accepts DibiConnection.
Formatting in where accepts Dibi modifiers. All parameters of where are passed to DibiConnection::query.
Rows are returned as DibiRow instances, not associative arrays.
No cache support yet (maybe it will be part of Dibi itself).
Discovery is delegated to Dibi, currently supported for Firebird and SQLite 3.

Object-relational mapping (ORM) systems usually require defining classes for entities (often mapping to tables) which is boring and repetitive task. Some of them can generate these classes from database (or vice versa) which is just another boring work – you have to run regeneration after each change.

ORM is also usually a wrong place for data validation – if you want consistent data then you need to validate it on the database level. Otherwise, it can get inconsistent for example after access from the database console. Most checks on the database level are simple:

Table references can be checked by foreign keys.
Maximum data length can be checked by column size, for example varchar(40).
Required fields can be checked by NOT NULL.
Other constraints can be checked by CHECK clause.

More complex checks as an e-mail validation can be processed in the form handler because they need to display the error message as close to error as possible (by JavaScript or PHP).

Administration interface to manage data in the whole database can be created with Adminer Editor for free.

ORMs also usually do not help with efficient reading of relationship data, which is the most common task in many database applications. They sometimes solve the inefficiency by data caching which is not optimal approach for two reasons:

Cached data gets stalled.
It consumes memory that can be used for something more useful.

The best approach is to efficiently get data directly from the primary storage, which is exactly what NotORM does.

Examples