Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Make every record returned by PDO an ORM class that can load it's own relations. This is just a simple concept idea. The record objects can figure out what table they belong to from their keys and what we know of the schema.
<?php
define('START_TIME', microtime(TRUE));
register_shutdown_function(function() {
print dump(round(microtime(TRUE) - START_TIME, 3) . ' seconds');
print dump(round(memory_get_peak_usage() / 1024) . " kb peak usage");
print dump(DB::$q);
});
function dump() {
foreach (func_get_args() as $value) {
print "\n\n<pre>" . ($value ? print_r($value, true) : var_export($value)) . "</pre>\n\n";
}
}
$config = array(
'db' => array(
'dsn' => 'mysql:dbname=api;host=localhost',
'user' => 'root',
'pass' => '',
)
);
$pdo = new PDO(
$config['db']['dsn'],
$config['db']['user'],
$config['db']['pass'],
array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_CLASS,
// override what statement class to use, and provide constructor arguments (found out by trial and error)
PDO::ATTR_STATEMENT_CLASS => array('PDOStatementWithClass', array('DB_Row'))
)
);
require('DB.php');
DB::$c = $pdo;
// http://stackoverflow.com/a/7617462/99923
class PDOStatementWithClass extends PDOStatement
{
// PHP complained when I tried to make this public
protected function __construct( $fetch_class = 'StdClass' )
{
parent::setFetchMode(PDO::FETCH_CLASS, $fetch_class);
}
}
class DB_Row
{
public static $schema;
public static $tables;
protected function loadSchema()
{
if(static::$schema) {
return static::$schema;
}
$sql = "SELECT * FROM information_schema.columns"
. " WHERE table_schema = DATABASE()"
. " ORDER BY table_name, ordinal_position";
$columns = array();
foreach(DB::fetch($sql) as $column) {
if($column) {
$columns[$column->TABLE_NAME][$column->COLUMN_NAME] = array(
'default' => $column->COLUMN_DEFAULT ?: null,
'nullable' => $column->IS_NULLABLE === 'YES',
'type' => $column->DATA_TYPE,
'length' => $column->CHARACTER_MAXIMUM_LENGTH ?: null,
'precision' => $column->NUMERIC_PRECISION ?: null,
'scale' => $column->NUMERIC_SCALE ?: null,
'comment' => $column->COLUMN_COMMENT ?: null,
// PRI, MUL, etc...
'index' => $column->COLUMN_KEY ? true : null,
'primary' => $column->COLUMN_KEY === 'PRI',
'unique' => $column->COLUMN_KEY === 'UNI',
);
}
}
$sql = "SELECT * FROM information_schema.KEY_COLUMN_USAGE"
. " WHERE table_schema = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL"
. " ORDER BY table_name, ordinal_position";
foreach(DB::fetch($sql) as $row) {
$columns[$row->TABLE_NAME][$row->COLUMN_NAME]['table'] = $row->REFERENCED_TABLE_NAME;
$columns[$row->TABLE_NAME][$row->COLUMN_NAME]['column'] = $row->REFERENCED_COLUMN_NAME;
}
// Add the lookups
foreach($columns as $table => $fields) {
static::$tables[md5(join('', array_keys($fields)))] = $table;
}
return static::$schema = $columns;
}
public function getTableName()
{
static::loadSchema();
$hash = md5(join('', array_keys((array) $this)));
// print dump(array_keys((array) $row), $hash);
if(isset(static::$tables[$hash])) {
return static::$tables[$hash];
}
}
public function get($table) {
if(isset($this->{$table . '_id'})) {
$column = 'id';
$id = $this->{$table . '_id'};
} else {
$column = $this->getTableName() . '_id';
$id = $this->id;
}
return DB::fetch("SELECT * FROM $table WHERE $column = ?", array($id));
}
}
$user = DB::row('SELECT * FROM user WHERE id = 3 LIMIT 1');
print dump($user);
$topics = $user->get('topic');
foreach($topics as $topic) {
print dump($topic);
$replies = $topic->get('reply');
print dump($replies);
}
/* Is hashing the keys costly? No, only takes .001 ms on my macbook
$start = microtime(TRUE);
for ($i=0; $i < 1000; $i++) {
$keys = array_keys((array) $row);
$hash = md5(join('', $keys));
if($i === 1) { print dump($keys, $hash); }
}
print dump((microtime(TRUE) - $start) . ' seconds');
*/
DB_Row Object
(
[id] => 3
[email] => email@example.com
[name] => Alex Smith
[updated_at] => 2014-10-07 15:34:29
[created_at] =>
[admin] =>
)
DB_Row Object
(
[id] => 4
[body] => This is the content of my new topic
[title] => Another topic
[forum_id] => 25
[user_id] => 3
[created_at] =>
[updated_at] =>
)
Array
(
[0] => DB_Row Object
(
[id] => 3
[body] => This is the reply
[title] =>
[topic_id] => 4
[user_id] => 3
[created_at] => 2014-10-03 17:55:59
[updated_at] => 2014-10-03 17:55:59
)
[1] => DB_Row Object
(
[id] => 5
[body] => And another one
[title] =>
[topic_id] => 4
[user_id] => 3
[created_at] => 2014-10-27 15:19:14
[updated_at] => 2014-10-27 15:19:14
)
)
DB_Row Object
(
[id] => 5
[body] => Recently, while visiting some friends I was ...
[title] => When the time comes
[forum_id] => 25
[user_id] => 3
[created_at] =>
[updated_at] =>
)
Array
(
[0] => DB_Row Object
(
[id] => 4
[body] => This is my reply
[title] =>
[topic_id] => 5
[user_id] => 3
[created_at] => 2014-10-03 18:05:06
[updated_at] => 2014-10-03 18:05:06
)
)
DB_Row Object
(
[id] => 6
[body] =>
With some body stuff and what not.
Hello world!
[title] => My Topic
[forum_id] => 25
[user_id] => 3
[created_at] => 2014-10-27 15:06:18
[updated_at] => 2014-10-27 15:24:26
)
Array
(
[0] => DB_Row Object
(
[id] => 6
[body] => Hello world!
[title] =>
[topic_id] => 6
[user_id] => 8
[created_at] => 2014-10-30 18:34:37
[updated_at] => 2014-10-30 18:34:37
)
)
0.007 seconds
696 kb peak usage
Array
(
[0] => SELECT * FROM user WHERE id = 3 LIMIT 1
[1] => SELECT * FROM information_schema.columns WHERE table_schema = DATABASE() ORDER BY table_name, ordinal_position
[2] => SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL ORDER BY table_name, ordinal_position
[3] => SELECT * FROM topic WHERE user_id = ?
[4] => SELECT * FROM reply WHERE topic_id = ?
[5] => SELECT * FROM reply WHERE topic_id = ?
[6] => SELECT * FROM reply WHERE topic_id = ?
)
@Xeoncross

This comment has been minimized.

Copy link
Owner Author

@Xeoncross Xeoncross commented Nov 3, 2014

This system uses https://github.com/Xeoncross/DByte just so could wrap the DB in some kind of singleton to test the idea. Dependency injection would be used in a real library.

Also see https://github.com/Xeoncross/ACRUD

Please leave feedback on the idea

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment