How to implement really small and fast ORM with PHP (Part 7: IDE)

Fork me on GitHub

Queries are gaining more and more complexity, data is getting bigger and bigger. Most optimizations in database technology are done in the database server. This is an approach to optimize queries on the client side.

With this ORM, queries ...
  • don't select more data than needed
  • contain less joins when data is expected to be consistent
  • can be written manually in pure SQL
  • are not written in a new query language

We need a good API, so ...

  • it should be easy to learn
  • method names must be short and intuitive
  • the goal is to map datasets and relations to objects
  • the API should offer method chaining
  • special features like auto-increments should be included
  • the code should be small, no getters and setters
  • the database schema is created before writing PHP code
  • relationships should be defined in the database, not in the code
  • we get low latencies combined with low memory usage

To make things easier, we make some restrictions:

  • only UTF-8
  • only MySQL/MariaDB (mysqli)
  • only PHP 5.4.0+
  • only buffered queries

Our ORM should have the same efficiency as handwritten SQL. So the following statements should produce only 1 query:

  • DBo::Guestbook(42)->Comments()->delete(); // "Guestbook" and "Comments" are tables
  • DBo::Guestbook(42)->Comments()->update('hidden', 1);
  • DBo::Guestbook(42)->Comments()->count();
  • DBo::Student()->Attend()->Lecture()->Uses()->Book(); // "Uses", "Book", etc. are tables

The following statements should only update 1 column:

  • DBo::Guestbook(42)->Comments()->update('hidden', 1);
  • DBo::Guestbook(42)->update('title', 'hello');

The following statements should only select 1 column (that's the hard one):

  • foreach (DBo::Guestbook() as $gb) echo $gb->title;

This gets implemented with a two-pass method: We store the columns used in the first run in DBo::usage_col[code position] and reuse it in the second run. The "code position" is the line where the constructor was called (returned from debug_backtrace()).

Meta data like columns or indexes should be fetched statically into the code (not during runtime). To create a join out of "Guestbook()->Comments()", the relevant columns are chosen during runtime. The syntax is:
  • table.primary_key_field = other_table.table_primary_key_field
  • e.g. sale.id = salepos.sale_id

To export the schema, we use:


// export schema to schema.php
DBo::conn(new mysqli('127.0.0.1', 'root', 'some_pw'));
DBo::exportSchema();

To keep control over the queries, we allow normal queries and debugging:


$entries = DBo::query('SELECT * FROM guestbook WHERE id=?', [42]); // Iterator
foreach ($entries as $obj) {...}

$id = DBo::query('INSERT INTO guestbook VALUES (...)'); // LastInsert ID
// 42

$affected = DBo::query('UPDATE guestbook SET active=0'); // Affected rows
// 10

$subject = DBo::value('SELECT subject FROM guestbook WHERE id=42'); // String
// 'Hello World'

$categories = DBo::values('SELECT DISTINCT categories FROM guestbook'); // Array
// ['Sports', 'Movies', 'Music']

$row = DBo::one('SELECT * FROM guestbook WHERE id=42'); // Array
// [id=>42, title=>'hello']

$row = DBo::keyValue('SELECT id,title FROM guestbook'); // Array
// [42=>'hello', 43=>'world']

$row = DBo::keyValues('SELECT id,title,subject FROM guestbook'); // Array
// [42=>['title'=>'hello', 'subject'=>'world'], 43=>['title'=>...]

echo DBo::Guestbook(42)->Comments(); // SQL String
// SELECT a.* FROM Comments a, Guestbook b WHERE ...

echo DBo::Guestbook(42)->Comments()->explain(); // explain SQL string
// EXPLAIN SELECT ... id | select_type | table | type ...

DBo::Guestbook(42)->Comments()->print_r(); // print_r related comments
// Array( id=... )

We also allow transactions:


DBo::begin();
$dbo = DBo::Guestbook(42);
$dbo->Comments()->delete(); // DELETE FROM Comments ...
$dbo->update('comments_count', 0); // UPDATE Guestbook SET ...
DBo::commit();

Here are some examples how the ORM should work:


// create a new entry in table "Guestbook"
// set attribute values to "hello" and "world"
// finally print out the primary key (auto-generated by auto-increment)
// - gives INSERT INTO Guestbook SET subject='hello', details='world'
$obj = DBo::Guestbook();
$obj->subject = 'hello';
$obj->details = 'world';
echo $obj->insert(); // 43 (from auto_increment)
echo $obj->id; // 43
// or $_POST = ['subject'=>'hello', ...]
echo DBo::Guestbook()->insert($_POST); // 43

// map entry in table "Guestbook" with primary key "42" to "$obj"
// - gives SELECT * FROM Guestbook WHERE id=42
$obj = DBo::Guestbook(42);
if (!$obj->exists()) {...}
echo $obj->subject;
// or
echo DBo::Guestbook(42)->subject;

// update antry in table "Guestbook" with primary key "42", set "hidden" to "1"
// - gives UPDATE Guestbook SET hidden=1 WHERE id=42
$obj = DBo::Guestbook(42);
$obj->hidden = 1;
$obj->update();
// or
DBo::Guestbook(42)->update('hidden', 1);
// or $_POST = ['hidden'=>'1', ...]
DBo::Guestbook(42)->update($_POST);

// delete entry in table "Guestbook" with primary key "42"
// - gives DELETE FROM Guestbook WHERE id=42
DBo::Guestbook(42)->delete();

// increment a field in table "Guestbook" with primary key "42"
// - gives UPDATE Guestbook SET likes=likes+1 WHERE id=42
DBo::Guestbook(42)->update('likes=likes+1');

Doing 1:n and n:m relations should be also very easy:


// get all Comments for Guestbook entry with primary key 42
// join a 1:n relationship (table.id = table2.table_id)
// - gives SELECT * FROM Comments WHERE guestbook_id=42
$comments = DBo::Guestbook(42)->Comments();
foreach ($comments as $comment) {...}

// update comments
// - gives UPDATE Comments SET hidden=1 WHERE guestbook_id=24
// - note that traditional ORMs do one update statement for each dataset
DBo::Guestbook(42)->Comments()->update('hidden', 1);

// update comments with where predicate
// - gives UPDATE Comments SET active=0 WHERE active=1 AND guestbook_id=24
DBo::Guestbook(42)->Comments('active=1')->update('active', 0);

// deleting comments works in the same way
// - gives DELETE FROM Comments WHERE guestbook_id=24
DBo::Guestbook(42)->Comments()->delete();

// n:m Students attend Lectures
// - gives SELECT a.* FROM Lecture a, Attend b WHERE b.student_id=21 AND
//   b.lecture_id = a.id
$lectures = DBo::Student(21)->Attend()->Lecture();
foreach ($lectures as $lecture) {...}

// n:m Students attend Lectures, Lecture uses Books
// - gives SELECT a.* FROM Book a, Uses b, Lecture c, Attend d
//          WHERE d.student_id = 21 AND d.lecture_id = c.id
//            AND c.id = b.lecture_id AND b.book_id = a.id
$books = DBo::Student(21)->Attend()->Lecture()->Uses()->Book();
foreach ($books as $book) {...}

Sometimes it is better to avoid normalization and store multiple values inside a string. This reduces the number of tables, relations and costly joins, e.g. using a string value like "100,101,102" instead of a join. The data can be also encoded as a JSON string with '[100,101,102]' or '["100","101","102"]'. To do the encoding and decoding automatically, the names of the members can be amended with "_arr" and "_json". Here is an example: 


// automatic encoding and decoding of values
// - gives UPDATE Guestbook SET tags_arr='sport,music,tv' WHERE id=42
//         UPDATE Guestbook SET tags_json='{"a":"b","c":"d"}' WHERE id=42
$obj = DBo::Guestbook(42);
$obj->update('tags_arr', ['sport','music','tv']); // field tags (Varchar)
// or
$obj->update('tags_json', ['a'=>'b', 'c'=>'d']); // field tags2 (Varchar)

$obj = DBo::Guestbook(42);
print_r($obj->tags_arr); // Array([0] => sport\n [1] => music\n [2] => tv)
// or
print_r($obj->tags_json); // Array([a] => b\n [c] => d)

Predicates can be defined in several ways:


// select one dataset
// - gives SELECT * FROM Guestbook WHERE id=10
DBo::Guestbook('id=10');
DBo::Guestbook('id=?', 10);
DBo::Guestbook(['id'=>10]);
DBo::Guestbook(10); // id is a numeric primary key

// select multiple datasets
// - gives SELECT * FROM Guestbook WHERE id IN (10,11,12)
DBo::Guestbook('id in (10,11,12)');
DBo::Guestbook('id in ?', [10,11,12]);
DBo::Guestbook(['id'=>[10,11,12]]);
DBo::Guestbook([10,11,12]); // id is a primary key

// select multiple primary keys
// - gives SELECT * FROM Guestbook WHERE (id,id2) IN ((10,11))
DBo::Guestbook('id=10 and id2=11');
DBo::Guestbook('(id,id2) in ?', [10,11]);
DBo::Guestbook('id=? and id2=?', 10, 11);
DBo::Guestbook(['id'=>10, 'id2'=>11]);
DBo::Guestbook([[10,11]]); // id and id2 are a primary key

// select multiple datasets with multiple primary keys
// - gives SELECT * FROM Guestbook WHERE (id,id2) IN ((10,1),(11,2))
DBo::Guestbook('(id,id2) in ((10,1), (11,2))');
DBo::Guestbook('(id,id2) in ?', [[10,1], [11,2]]);
DBo::Guestbook([[10,1], [11,2]]); // id and id2 are a primary key

// additional predicates
DBo::Guestbook(10, "active=1");
DBo::Guestbook("active=1 and foo=?", "bar");

// limit
foreach (DBo::Order("status=open")->limit(100) as $obj) {...

Joins are skipped when data is expected to be consistent:


foreach (DBo::guestbook(42)->comments() as $comment) {...}
// gives SELECT * FROM comments WHERE guestbook_id=42

Custom SQL can be used:


$comments = DBo::object("SELECT * FROM Comments WHERE guestbook_id=?", [42]);
foreach ($comments as $comment) {...}

Aggregate functions can be used:


// SELECT count(*) FROM order where customer_id=42
DBo::Customer(42)->Order()->count(); // 40

// SELECT sum(price) FROM order where customer_id=42
DBo::Customer(42)->Order()->sum('price'); // 600

// SELECT avg(*) FROM order where customer_id=42
DBo::Customer(42)->Order()->avg('price'); // 23

// SELECT stddev(*) FROM order where customer_id=42
DBo::Customer(42)->Order()->stddev('price'); // 8

Data can be archived:


// archiving is not yet implemented

// create archive table
// CREATE TABLE Customer_archive LIKE Customer;
// # remove primary & auto_increment, add index and timestamp
// ALTER TABLE Customer_archive DROP primary key, MODIFY id int,
//   ADD index(id), ADD ts timestamp;

// archive record before updating
// Customer.id = primary key, Customer_archive.id = index
// INSERT INTO test.Customer_archive
//   SELECT a.*, now() FROM test.Customer a WHERE a.id=42
// UPDATE test.Customer a SET a.hidden=1 WHERE a.id=42
DBo::Customer(42)->archive()->update('hidden', 1);

// copy record to table
// INSERT INTO somedb.sometable
//   SELECT a.* FROM test.Customer a WHERE a.id=42
DBo::Customer(42)->copyTo('somedb.sometable');

Custom classes can also be used:


class DBo_Sales extends DBo {
  public function buildData($insert=false) {
    // custom validation
    $data = parent::buildData();
    if (empty($data["some_val"])) throw new Exception(...);
    return $data;
  }

  public function insert($arr=null) {
    // execute some pre trigger
    parent::insert($arr);
    // execute some post trigger
  }

  public function delete() {
    if ($this->status != 'draft') throw new Exception(...);
    parent::delete();
  }

  public function completed() {
    return DBo::query('SELECT * FROM sales WHERE completed=1');
  }

  public function get_age() {
    return date_diff(new DateTime($this->birthdate), new DateTime())->y;
  }
}

// DBo_{table} is automatically used as class
print_r(DBo::Sales());
=> DBo_Sales Object (...

print_r(DBo::Sales()->completed());
=> returns completed() from Dbo_Sales

print_r(DBo::object("SELECT * FROM Sales")->completed());
=> returns completed() from Dbo_Sales

// get_{field}() is automatically used when the member not exists
print_r(DBo::Sales()->age);
=> returns get_age()

The database connection should be opened when the first query is being executed. We use:


class mysqli_lazy extends mysqli {
  public function query($query) {
    if (!@$this->host_info) parent::connect('127.0.0.1', 'root', '', 'db');
    // or persistent connection: 'p:127.0.0.1'
    return parent::query($query);
  }
}
DBo::conn(new mysqli_lazy, 'db');

// instead of
DBo::conn(new mysqli('127.0.0.1', 'root', '', 'db'), 'db');
// or persistent connection: 'p:127.0.0.1'

To get all queries on stdout, we use:


class mysqli_log extends mysqli {
  public function query($query) {
    echo $query."\n";
    return parent::query($query);
  }
}
DBo::conn(new mysqli_log('127.0.0.1', 'root', '', 'db'), 'db');

The fastest way to get data is reading it from a hash table in the main memory. With the APC extension, we can persist data between many requests. The syntax for caching looks like this:


// caching is not yet implemented

// cache categories for 60 seconds
$payments = DBo::Categories()->cache(60);
// [{id=>0, name=>Sports}, {id=>1, name=>Movies}, ...]

$payments = DBo::Categories()->oarray(60);
// [[id=>0, name=>Sports], [id=>1, name=>Movies], ...]

$payments = DBo::Categories()->ovalues('col_name', 60);
// [Sports, Movies, ...]

$payments = DBo::Categories()->okeyValue('col_id', 'col_name', 60);
// [0=>Sports, 1=>Movies, ...]

$payments = DBo::Categories()->count(60); // 42

Broken queries or connection errors can be handled with try-catch:


try {
  DBo::conn(new mysqli('127.0.0.1', 'root', '', 'db'), 'db');
  DBo::query('select * from invalid');
}
catch (mysqli_sql_exception $e) {
  echo $e->getMessage();
  exit(1);
}

If column names are ambiguous, we need to prefix them with "@":


// SELECT a.* FROM app a, os b WHERE a.os_id=b.id AND id=42 AND id=13
DBo::os('id=42')->app('id=13');

// SELECT a.* FROM app a, os b WHERE a.os_id=b.id AND b.id=42 AND a.id=13
DBo::os('@id=42')->app('@id=13');

Security:

Scalar parameters are escaped automatically if the previous parameter is a string. When using scalar parameters without predicates, you need to cast them manually:


DBo::Guestbook('id=?', $_GET['id']); // automatic
DBo::Guestbook((int)$_GET['id']); // manual!
DBo::Guestbook((array)$_GET['ids']); // manual!

Other parameters and field names are automatically escaped:


DBo::Guestbook([ $_GET['id'], $_GET['val'] ]); // automatic
DBo::Guestbook([ 'id' => $_GET['id'] ]); // automatic

DBo::Guestbook()->setFrom($_POST)->insert(); // automatic
DBo::Guestbook()->limit($_POST['limit']); // automatic

IDE integration: (e.g. PhpStorm)


// auto-generation of PHPdoc hints is not yet implemented

// type hint
$sales = DBo::Sales(); /* @var $sales DBo_Sales */

// or class hint
/**
 * @method static DBo_Sales Sales some description
 */
class DBo implements IteratorAggregate {...}

// or extended class hint
/**
 * @method static DBo_Sales Sales some description
 */
class HDBo extends DBo {}

$sales = HDBo::Sales();

/**
 * @property int id
 * @property decimal_6_2 price
 * @property varchar_40 desc
 */
class DBo_Sales extends DBo {}





Keyboard shortcuts:
- auto-complete: Ctrl+space
- documentation: Ctrl+q
- variable info: Ctrl+mouseover


The implementation in detail:

Running DBo::Student()->Attend()->Lecture()->array() as a single query can be implemented by using a stack. The query itself presents a chain of tables being joined by some predicates. Attend() and others are handled by PHP's magic __call(). Every table in the chain pushes a new element on the stack containing the name of the table, the primary key and the required predicate(s).

Even with the best ORM, you can still write bad code:


foreach (DBo::os(10)->app() as $app) {
  if (!$app->active) continue; // bad
  ...
}
foreach (DBo::os(10)->app('active=1') as $app) {...} // good, less data

foreach (DBo::os(10)->app() as $app) {
  foreach ($app->compontent() as $compontent) {...} // bad, many queries
}
foreach (DBo::os(10)->app()->compontent() as $compontent) {...} // good

foreach (DBo::os(10)->app() as $app) $app->update('active', 1); // bad
DBo::os(10)->app()->update('active', 1); // good, 1 query

foreach (DBo::sale(10)->salepos() as $salepos) {
  if (DBo::logistics($salepos->id, 'shipped=1')->exists()) {
    $salepos->update('complete', 1); // bad
  }
}
DBo::sale(10)->salepos()->logistics('shipped=1')
  ->salepos()->update('complete', 1); // good

In general, it is better to select as little data as possible from the database. Joins are often expensive, but if the database handles them correctly, it is much more efficient than performing joins directly in PHP.

Changelog:


The code is available at: https://github.com/thomasbley/DBo (~500 loc, test coverage 100%)


Comments

Post a Comment

Popular posts from this blog

How to show only month and year fields in android Date-picker?

Image
Android default DatePicker show day/month/year fields but you have to customize this DatePicker as your requirements. Suppose that you want to show only month and year fields in android datepicker . To do this, you have to customize the Dialog DatePicker source code but it is not an easy task. In this tutorial I will show you a tricky way to solve this problem. Lets see how to show only month and year field in android DatePicker as a dialog.   Step to show only month and year fields in android date-picker: Some days ago I got this same problem and here I am trying to explain this in a sequential manner. I want to show a dialog datepicker when user click on an EditText. So if user click on EditText, A dialog datepicker will be invocked and it will show only month and year fields. To do this, at first we have to create a xml layout to hold the EditText fields. So create an android project and name it DatePickerExample. In the main.xml file just try to write this simple code....
Read more

How to construct a B+ tree with example

Image
A B+ tree contains n-1 search key values K1K2…….Kn-1 and have n pointers.   In a B+ tree the data are ordered  sequentially.  The leaf node may hold up to n pointers and must hold at least N/2 pointers. The root node must hold at least two pointers, unless the tree consists of only one node.  Now we construct a B+ tree. Construct a B+ tree for the following set of key values: (2,3,5,7,11,17,19,23,29,31) Assume that the tree is initially empty and values are added in ascending order. Construct a B+ tree where the pointer number is Four. Solution: When a node exceeds n-1 search key value then we split it into two nodes. First node contains (ceiling(n-1)/2) values.  2 nd node contains remaining node. Copy the smallest search key value of the second node to the parent node. Let's start, we know each node has N pointer and has N-1 search key values. In our example pointer number is 4, so the search key value is 4-1=3. So each node has 4 pointers and 3 search key val...
Read more

Conflict Serializability in database

Image
To maintains database state consistency we introduce serializability. Using serializability we can understand which schedule will ensure consistency, and which schedule will not. When we are capable of converting a concurrent schedule into a serial schedule then we cal it serializable schedule. A schedule is serializable if it is equivalent to a serial schedule. We divided this concepts into two parts 1. Conflict serializability  & 2. View serializability In this discussion I just clarify you the conflict serializability. Consider two instruction read, write and the data item A and B. When two instruction execute different data item such as read(A) and read(B), then we can swap the instruction without affecting the result of the schedule. But when the data item is equivalent to one another we need to consider following four condition.    a.   read (A ),    read ( A ).   they   don’t conflict.    b.   read ( Q),  ...
Read more