FoxORM ORM - API Documentation

Multi-Databases Registry

Bases instanciation

The Bases class is a map registry for multiple databases. You can make a new one with regular "new" keyword or use the singleton defined in Facade API class B. use FoxORM\Bases; $bases = new Bases(); use FoxORM\F; $bases = F::getBases();

Bases options

The Bases object can be configured to set the default config of the databases. Each database, and even each table, can be configured independently and overload the default config.
If you keep the default config for primary key you can make a database which will be compatible with RedBeanPHP strict convention. //The followings showed parameters are the defaults $bases->setModelClassPrefix('EntityModel\\'); $bases->setModelClassDefault('stdClass'); $bases->setPrimaryKeyDefault('id'); $bases->setUniqTextKeyDefault('uniq'); //append an other prefix for entity class lookup $bases->appendModelClassPrefix($modelClassPrefix); //prepend an other prefix for entity class lookup $bases->prependModelClassPrefix($modelClassPrefix); //enable output of queries $bases->debug(true);

Bases definitions

The Bases object can be used like an array with magic acessor. The "0" index is the main database and default one used by Facade API. For relational databases, you can define your dsn like a regular dsn or with assiocative keys. The frozen parameter tell FoxORM to not modify this database structure anymore to feet the request. It's to enable when you won't modify the schema and you want optimizations on production server.
Set databases: $bases[0] = [ 'type'=>'mysql', 'host'=>'localhost', 'port'=>'3306', 'name'=>'my_db_name', 'primaryKey'=>'id', 'uniqTextKey'=>'name', 'tablePrefix'=>'mydb_', //'frozen'=>true, ]; $bases['my_sqlite_base'] = [ 'dsn'=>'sqlite:'.REDCAT_CWD.'.data/db.sqlite', ]; $bases['my_sqlite_base2'] = [ 'type'=>'sqlite', 'file'=>REDCAT_CWD.'.data/db2.sqlite', ]; $bases['my_mariadb_base'] = [ 'mysql:dbname=test', //dsn 'root', //user 'root', //password ]; $bases['my_postgres_base'] = [ 'dsn'=>'pgsql:dbname=test;host=127.0.0.1;port=5432', 'user'=>'postgres', 'password'=>'postgres', ]; When you access databases offset, the Bases object return you the configured DataSource Object instead of config array. Access databases: $base = $bases[0]; $table = $base->getAll('SELECT * FROM test_table');

Database Methods

The Database is an extended class of DataSource which the name correspond to the type of database. The basic persistence workflow, called CRUD, can be accessed from diffrent API levels: Static Facade, Database Object, Table Object.

create

You can pass arguments to "create" in various way but if you pass it a data set which have allready a primary key defined and if that primary key allready exists in database table, the create will process an update instead an insert. $user = new stdClass; //or any other class $user->name = 'Jo'; $base->create('user',$user); $user = new stdClass; //or any other class $user->_type = 'user'; $user->name = 'Jo'; $base->create($user); $base->create('user',[ 'name'=>'Jo', ]); $base->create([ '_type'=>'user', 'name'=>'Jo', ]); /* if you don't pass explicitly the type and if the "_type" meta attribute is not defined the type will be deducted using the class name and the "modelClassPrefix" (by default " EntityModel\ "), if it cannot be deduct an Exception will be throwed. */ $user = new \EntityModel\User; $user->name = 'Jo'; $base->create($user);

read

If you pass a string instead of an integer as second argument to "read", tha base will not try to lookup data by primary key but by "uniqTextKey". The default "uniqTextKey" is "uniq" but it can be configured on table like in the example. $user = $base->read('user',1); $base['user']->setUniqTextKey('email'); $user = $base->read('user','jo@surikat.pro');

update

Like with create, you can pass arguments to "update" in various way but if you pass it a data set which don't have a primary key or a "uniqTextKey" defined the update will process an insert instead of update. $user = new stdClass; //or any other class $user->id = 1; $user->name = 'Jo'; $user->mtime = time(); $base->update('user',$user); $user = new stdClass; //or any other class $user->_type = 'user'; $user->id = 1; $user->name = 'Jo'; $user->mtime = time(); $base->update($user); $base->update('user',[ 'id'=>1, 'name'=>'Jo', 'mtime'=>time(), ]); $base->update([ '_type'=>'user', 'id'=>1, 'name'=>'Jo', 'mtime'=>time(), ]); /* if you don't pass explicitly the type and if the "_type" meta attribute is not defined the type will be deducted using the class name and the "modelClassPrefix" (by default " EntityModel\ "), if it cannot be deduct an Exception will be thrown. */ $user = new \EntityModel\User; $user->name = 'Jo'; $base->update($user);

delete

You can pass arguments to "delete" in various way. $base->delete('user',1); $base->delete([ '_type'=>'user', 'id'=>1 ]); $base['user']->setUniqTextKey('email'); $base->delete('user','jo@surikat.pro'); /* if you don't pass explicitly the type and if the "_type" meta attribute is not defined the type will be deducted using the class name and the "modelClassPrefix" (by default " EntityModel\ "), if it cannot be deduct an Exception will be thrown. */ $user = new \EntityModel\User; $user->id = 1; $user->name = 'Jo'; $base->delete($user);

one2manyDelete

Will delete all row of a table owned by another row and with a keeping optional parameter. $keeping = [1,2,3]; //history id $base->one2manyDelete($user,'history',$keeping);

many2manyDelete

Will delete all row of a table owned by another row and with a keeping optional parameter. $keeping = [1,2,3]; //history id // many to many relation table between user and history, // by default null, according to alphabetical order // it will implicit the join table will be history_user $via = null; $base->one2manyDelete($user,'history',$via,$keeping);

entityFactory

The entityFactory method will create an object corresponding to type name looking for a class corresponding to type name in "modelClassPrefix", and if this class is not found it will use the "entityClassDefault" (by default "stdClass"). The entity factory will add the "_type" meta property to the instancied object. $user = $base->entityFactory('user');

setEntityFactory

You can overload the entityFactory method using setEntityFactory. $base->setEntityFactory(function($type)use($base){ $c = $base->findModelClass($type); $row = new $c; $row->callSomething(); return $row; });

put

The put method will determine automatically if a "create" or an "update" is needed, you can use it exactly like theses two methods.

snippet

Some database offers a support for snippet extracting around searched terms from full text search, like sqlite or postgresql. But for those lacking this feature, here is a common snippet extractor. $base->snippet($text,$searchedTerms,$tokens=15,$start='',$end='',$sep=' ... ');

Relational Database Methods

getAll

The getAll method allow you to fetch all rows from a query. $rows = $base->getAll('SELECT * FROM user WHERE status = ? LIMIT 10', [ $status ]); foreach($rows as $row){ echo $row->email."\n"; }

getRow

The getRow method allow you to fetch a specific row from a query. $row = $base->getRow('SELECT * FROM user WHERE id = ?', [ 1 ]); $email = $row->email;

getCol

The getCol method allow you to fetch a specific column from a query. $col = $base->getCol('SELECT email FROM user WHERE status = ?', [ $status ]); foreach($col as $email){ }

getCell

The getCell method allow you to fetch a specific cell from a query. $email = $base->getCell('SELECT id FROM user WHERE email = ?', [ $email ]);

execute

The execute method allow you execute a query. $base->execute('INSERT INTO user(name, email) VALUES( ?, ? )', [ $name, $email ] ); $affectedRows = $base->execute('UPDATE user SET email = ? WHERE id = ?', [ $email, $id ] );

fetch

The fetch method allow you direct access to a cursor from a query, so you get each row in a loop and don't overflow your php memory usage while traversing a whole table for example. while($row = $base->fetch('SELECT * FROM user WHERE id != 1', [ $id ])){ }

begin

The begin method open a transaction.
FoxORM support nested transactions, using "SAVEPOINT", with no extra code needed.
Be carefull with transaction when the database is not "frozen", because the queries which altering the schema will do implicit commit in some databases like MySQL or Oracle. There is some workaround, with a secondary PDO object, that make rollback possible but not for the structuring queries.
$base->begin();

commit

The commit method validate and end a transaction. $base->commit();

rollback

The rollback method cancel and end a transaction. $base->rollback();

getDatabaseType

getDatabaseType return the dsn prefix: mysql for MySQL or MariaDB, pgsl for PostgreSQL, sqlite for SQLite or cubrid for CUBRID. $base->getDatabaseType();

getDatabaseVersion

getDatabaseVersion return the numeric decimal version of the database. $base->getDatabaseVersion();

getPDO

getPDO return the native PDO object used by DataSource. $pdo = $base->getPDO();

isConnected

isConnected return true if connected, null if not allready connected, and false is connection has fail. $base->isConnected();

debug

The debug method enable queries output, and optionaly, results and query explain output. $base->debug($enable=true,$loggingResult=true,$loggingExplain=true)

nestBinding

The nestBinding method, used automatically in internal query writer, is a helper allowing you to use nested array of parameters and even, contrary to native PDO, mix named placeholders and mark placeholders. $bindings = [ ':colour'=>['red','green'], 15 ]; $sql = 'SELECT name, colour, calories FROM fruit WHERE colour IN :colour AND weight < ?'; list($sql,$bindings) = FoxORM\DataSource\SQL::nestBinding($sql,$bindings); And the output will be flattened like this: $sql = 'SELECT name, colour, calories FROM fruit WHERE colour IN (?,?) AND weight < ?'; $bindings = ['red','green',15];

findOne

The findOne method allow you to retrieve one object from database query using sql snippet. $base->findOne('user','name = ?',['Jo']);

find

The findOne method allow you to retrieve an array of objects from database query using sql snippet. $base->find('user','mtime < ?',[ time()-3600 ]);

findAll

The findAll method allow you to retrieve an array of objects from database query using sql snippet. There is no implicit WHERE, so, use it if you don't want to add any conditions but you want to order or limit. $base->findAll('user','ORDER BY name DESC LIMIT 10');

findRow

The findRow method allow you to retrieve one result as an associative array from database query using sql snippet. There is no implicit WHERE. $base->findRow('user','WHERE name = ?',['Jo']);

findRows

The findRow method allow you to retrieve an array of results, as associatives arrays, from database query using sql snippet. There is no implicit WHERE. $base->findRows('user','WHERE mtime < ? ORDER BY name DESC LIMIT 10',[ time()-3600 ]);

bindRead

The bindRead method allow you to wrap SELECT columns in functions. $base->bindRead('location','point','asText'); $location = $base->read('location',1); You can also add nested function using mark ? in substitution of column name. $base->bindRead('location','point','SUBSTR(asText(?),5)'); $location = $base->read('location',1); And even alias them. $base->bindRead('location','point','asText(?) as point_text'); $location = $base->read('location',1);

bindWrite

The bindRead method allow you to wrap INSERT and UPDATE columns in functions. $location->point = 'POINT(14 6)'; $base->bindWrite('location','point','GeomFromText'); $base->create('location',$location); You can also add nested function using mark ? in substitution of column name. $location->point = '(14 6)'; $base->bindWrite('location','point',"GeomFromText(CONCAT('POINT',?))"); $base->create('location',$location);

unbindRead

The unbindRead method unset bindRead functions from stack.
If you pass it one argument it will unset all bindRead functions for the specified type.
If you pass it two argument it will unset all bindRead functions for the specified type and column.
If you pass it three argument it will unset all bindRead functions for the specified type and column and which is the same function as passed in third argument.
$base->unbindRead('location'); $base->unbindRead('location','point'); $base->unbindRead('location','point','asText');

unbindWrite

The unbindWrite method unset bindWrite functions from stack.
If you pass it one argument it will unset all bindWrite functions for the specified type.
If you pass it two argument it will unset all bindWrite functions for the specified type and column.
If you pass it three argument it will unset all bindWrite functions for the specified type and column and which is the same function as passed in third argument.
$base->unbindWrite('location'); $base->unbindWrite('location','point'); $base->unbindWrite('location','point','GeomFromText');

setSQLFiltersRead

Reset all bindRead functions and define a map manually. $base->setSQLFiltersRead([ 'location'=>[ 'point'=>'asText', ] ]);

setSQLFiltersWrite

Reset all bindWrite functions and define a map manually. $base->setSQLFiltersWrite([ 'location'=>[ 'point'=>'GeomFromText', ] ]);

getSQLFiltersRead

Return the bindRead functions map. $bindReadMap = $base->getSQLFiltersRead();

getSQLFiltersWrite

Return the bindWrite functions map. $bindWriteMap = $base->getSQLFiltersWrite();

getReadSnippetCol

Return sql snippet related to column select from bindRead map. Sql snippet is for use query. $columnSqlSnippet = $base->getReadSnippetCol('location','point'); $base->getAll("SELECT $type.*, $columnSqlSnippet FROM $type");

check

Will throw an InvalidArgumentException Exception if you pass to it an invalid table or column name (alphanumeric, hyphen -, underscore _). To protect dynamic table or column name in manual query building from SQL injection. $base->check($struct);

esc

Check table or column name is valid and add quote specific to current database type. $columnOrTableWithQuotes = $base->esc($esc);

escTable

Check table name is valid, add prefix if there is a prefix table associated to the current database, and then add quote specific to current database type. $tableWithPrefixAndQuotes = $base->escTable($type);

prefixTable

Check table name is valid and add prefix if there is a prefix table associated to the current database. $tableWithPrefix = $base->prefixTable($type);

unprefixTable

Remove the prefix if there is a prefix table associated to the current database and if it's present in parameter. $tableWithoutPrefix = $base->unprefixTable($tableWithPrefix);

unEsc

Trim quote specific to current database type from parameter. $tableOrColumnWithoutQuotes = $base->unEsc($esc);

getQuoteCharacter

Return quote specific to current database type. $quoteCharacter = $base->getQuoteCharacter();

getTablePrefix

Return prefix table associated to the current database. $tablePrefix = $base->getTablePrefix();

tableExists

Check if a table exists. Second parameter, set to true by default, is for automatically add prefix to table. if($base->tableExists($type)){ } if($base->tableExists($tableWithPrefix,false)){ }

getTables

Get the table names list. $tables = $base->getTables();

columnExists

Check if a column exists in a table. Table name is automatically prefixed. if($base->columnExists($type,$column)){ }

getColumns

Get the columns names list for a table. Table name is automatically prefixed. $columns = $base->getColumns($type);

addColumn

Add a colum to table manually. Table name is automatically prefixed. $base->addColumn($type,$column,$field);

changeColumn

Change a table's colum manually. Table name is automatically prefixed. $base->changeColumn($type,$column,$field);

createTable

Create a table manually. Table name is automatically prefixed. $base->createTable($type,$pk='id');

drops

Drop a list of table of the database. Table names are automatically prefixed. You can pass it a list of scalar names or arrays. $base->drops('table1','table2'); $base->drops(['table1','table2']);

drop

Drop a specific table of the database. Table name is automatically prefixed. $base->drop('mytable');

dropAll

Drop all table of the database. $base->dropAll();

many2one

Getting the one (parent). Get a type of related by column object from another object. For instance, retrieve the row from user table corresponding to user_id (if primaryKey of user is id) from table project where project id = 1. $project = $base->read('project',1); $user = $base->many2one($project,'user');

one2many

Getting the many (owns). Get a type of related by column objects from an object. For instance, retrieve the rows from project table where user_id (if primaryKey of user is id) = 1. $user = $base->read('user',1); $projectList = $base->one2many($user,'project');

many2many

Getting the many (shared). Get a type of related objects from an object via a junction table. If you don't specify a junction table, the convention is to order names of tables alphabetically and join them with an underscore "_". For instance with user and project, the implicit junction table will be "project_user". $user = $base->read('user',1); $projectList = $base->many2many($user,'project'); $project = $base->read('project',1); $userList = $base->many2many($project,'user'); $project = $base->read('project',1); $authorList = $base->many2many($project,'user','author');

many2manyLink

Getting the many (shared). Get a type of related objects from an object via a junction table. Difference with many2many is that you can specify the foreign key target. $project = $base->read('project',1); $authorList = $base->many2manyLink($project,'user','author','creator_id');

addFK

Manually add foreign key to a table. $base->addFK($type,$targetType,$column,$targetColumn,$cascade); $base->addFK('project','user','user_id','id',true);

getKeyMapForType

Get the foreign key map of a table. $fkMap = $base->getKeyMapForType('project'); $fkMap will contain an associative array like this one [ 'from_user_id_to_table_user_col_id'=>[ //conventional label 'name' => 'project_user_id_fkey', //index name 'table' => 'user', 'from' => 'user_id', 'to' => 'id', 'on_update' => 'CASCADE', 'on_delete' => 'CASCADE' ], ]

addUniqueConstraint

Manually add unique constraint to one or multiple column of a table. If you use multiple columns it's not the same as using multiple time a single column, multiple column on a unique constraint mean that it's the combination of these columns which should be unique. $base->addUniqueConstraint('user','email'); $base->addUniqueConstraint('user',['name','lastname']);

addIndex

Manually add a named index to a column of a table. $base->addIndex($type,$name,$column); $base->addIndex('user','email_index','email');

clear

Remove all entries from a table without altering it's structure. $base->clear('user');

drop

Remove a whole table from the database and, of course, all it's entries. $base->drop('user');

dropAll

Remove all tables from the database. $base->dropAll();

F Static Facade API

getBases()

Return the database container, the "Bases" object. $bases = F::getBases(); $base = $bases[0]; //get the default main db

setup

Set the default main database (key 0) dsn, user, password and config. F::setup($dsn = null, $user = null, $password = null, $config = []); F::setup('mysql:dbname=myproject;host=localhost', 'root', 'root', [ 'tablePrefix'=>'myproject_', 'frozen'=>false ]);

addDatabase

Add a database to the bases registry. If no database has been selected (or added) it will be selected automatically. F::addDatabase($key,$dsn,$user=null,$password=null,$config=[]); F::addDatabase('myproject','mysql:dbname=myproject;host=localhost', 'root', 'root', [ 'tablePrefix'=>'myproject_', 'frozen'=>false ]);

selectDatabase

Select (and add before if more than one argument are specified) a database from bases registry. For select the default main database, setted via F::setup, use the key "0". Return the database object. F::selectDatabase($key); $base = F::selectDatabase(0); $base = F::selectDatabase(0,'myproject','mysql:dbname=myproject;host=localhost', 'root', 'root', [ 'tablePrefix'=>'myproject_', 'frozen'=>false ]);

exists

Alias of readId method. It will check if an interger id (primaryKey) or "uniqTextKey" exist in a table. F::exists('user',1); F::exists('user','jo@surikat.pro');

dispense

Alias of entityFactory method. It will create an entity model object if class found or default entity class (stdClass by default). $user = F::dispense('user');

getDatabase

Return the current database (DataSource object) used (selected) by static facade api. $base = F::getDatabase();

getTable

Return the DataTable object corresponding to a table from the current selected database. $table = F::getTable($type); $users = F::getTable('user');

on

Attach a callback to a table event. If callback is string, it will be interpreted as a method of the entity object. If no callback is specified it will take the event name as a method to call on entity object. You can also use a zIndex like and a prepend option. F::on($type,$event,$call); F::on('user','afterCreate',function($entity,$db){ echo "User '{$entity->name}' created !"; }); F::on('user','afterCreate',function($entity,$db){ echo "First (prepended) callback after user '{$entity->name}' created !"; },0,true);

off

Detach a callback from a table event. If no callback specified it will remove all callbacks attached to the table event. You can use the zIndex like as last parameter. F::off($type,$event,$call); $userCreated = function($entity,$db){ echo "First Callback after user '{$entity->name}' created !"; }; F::off('user','afterCreate',$userCreated,1); F::off('user','afterCreate',$userCreated); F::off('user','afterCreate',null,1); F::off('user','afterCreate');

loadMany2one

Load the "_one_" object property from another table to an object. F::loadMany2one($obj,$type); $project = F::read('project',1); F::loadMany2one($project,'user'); $user = $project->_one_user;

loadOne2many

Load the "_many_" array of objects property from another table to an object. F::loadOne2many($obj,$type); $user = F::read('user',1); F::loadOne2many($user,'project'); $projects = $user->_many_project;

loadMany2many

Load the "_many2many_" array of objects property from another table to an object. A third parameter can specified for the junction table. If you don't specify a junction table, the convention is to order names of tables alphabetically and join them with an underscore "_". For instance with user and project, the implicit junction table will be "project_user". F::loadMany2many($obj,$type,$via=null); $user = F::read('user',1); F::loadMany2many($user,'project'); $projects = $user->_many2many_project; $project = F::read('project',1); F::loadMany2many($project,'user'); $users = $user->_many2many_user; $user = F::read('user',1); F::loadMany2many($user,'project','author'); $projects = $user->_many2many_project; $project = F::read('project',1); F::loadMany2many($project,'user','author'); $users = $user->_many2many_user;

relayed to the Bases object

All these followings methods are relayed to the Bases object. F::setModelClassPrefix($modelClassPrefix='EntityModel\\'); F::appendModelClassPrefix($modelClassPrefix); F::prependModelClassPrefix($modelClassPrefix); F::setModelClassDefault($entityClassDefault='stdClass'); F::setPrimaryKeyDefault($primaryKeyDefault='id'); F::setUniqTextKeyDefault($uniqTextKeyDefault='uniq');

debug

Enable debug mode for the current database. F::debug();

magic __callStatic

All methods that was not cited before in "F Static Facade API" are relayed automatically to the current database object, so you can use all methods explained before in "Database Methods" and "Relational Database Methods" via the facade API. //eg: F::create('user',['name'=>'Jo']); //is equivalent to $base->create('user',['name'=>'Jo']);

Table

getPrimaryKey

Get the primary key column name of the table (default "id"). $primaryKey = $table->getPrimaryKey();

getUniqTextKey

Get the "uniqTextKey" column name of the table (default "uniq"). "uniqTextKey" is used as a secondary main index to identify table's rows by a string. $uniqTextKey = $table->getUniqTextKey();

getDataSource

Get the database (DataSource class) parent object of the table. $base = $table->getDataSource();

setUniqTextKey

Set the "uniqTextKey" column name of the table (default "uniq"). "uniqTextKey" is used as a secondary main index to identify table's rows by a string. $table->setUniqTextKey($uniqTextKey='uniq'); $users->setUniqTextKey('email');

magic offset

The greatest API feature behind FoxORM is that you can manipulate rows and tables like arrays. That approach was inspired by the cool, but not allready mature Maphper. Array access interface to create, read, readId, update and delete methods. $table[] = $row; // $table->create($id,$row); $row = $table[1]; // $table->read($id); $exists = isset($table[1]); // $table->readId($id); $table[1] = $row; // $table->update($id,$row); unset($table[1]); // $table->delete($id); $user = $users[1]; $user = $users['jo@surikat.pro']; // offset methods $table->offsetExists($id); $table->offsetGet($id); $table->offsetSet($id,$obj); $table->offsetUnset($id);

magic iterator

With the magic iterator you can make a loop on the table and traverse the whole table or a filtered table, if object was configured.
For instance with the relational database implementation, a cursor will be used, so, traversing a whole big table will not overflow your php memory usage. The key will correspond to primaryKey id. foreach($table as $id=>$row){ } // iterator methods $table->rewind(); // reset($table); $table->current(); // current($table); $table->key(); // key($table); $table->valid(); $table->next(); // next($table);

magic count

If you call count function on a table, it will trigger the "count" method. For instance, in relational table it will count all rows of a table or the expected results if table object was configured. If you used the setCounter method to define a count callback it will use the specified callback. $count = count($table); //count method $count = $table->count();

paginate

The paginate method will create a "Pagination" helper object configured with parameters and count() from table, then it will resolve it to check the pagination is correct, adapt it's properties and finally it will return it to you. $pagination = $table->paginate($page,$limit=2,$href='',$prefix='?page=',$maxCols=6); //pagination properties $pagination->start; $pagination->end; $pagination->max; $pagination->count; $pagination->limit; $pagination->offset; $pagination->page; $pagination->maxCols; $pagination->href; $pagination->prefix; $pagination->pagesTotal;

setCache

Disable or enable row caching mechanism. Row caching keep rows when loaded via read/offsetGet or on cursor iteration. Cache is enabled by default, disable it with setCache before iterate on big set of data to avoid php memory overflow. $table->setCache(false);

resetCache

Clean cached rows. $table->resetCache();

readId

Check if an integer id exists or return an integer id from "uniqTextKey" if it exists. $table->readId($id);

readRow

Return a row as array from id or "uniqTextKey". $table->readRow($id);

putRow

Create or update a row, depends if primary key or "uniqTextKey" is specified, as object property or second parameter, and if allready exists in table or not. $table->putRow($obj,$id=null);

deleteRow

Delete a row from integer primary key or "uniqTextKey". $table->deleteRow($id);

update

Updating storage of a modified row by it's primary key or "uniqTextKey". $table->update($id);

getClone

Get a clone of table object, usefull when you want to filter a table and building dynamic sql query around an iterator, counter and other featured object that is DataTable. $tableFilter = $table->getClone(); //chainable equivalent of $tableFilter = clone $table;

loadOne

Load the "_one_" object property from another table to an object. $table->loadOne($obj); $project = $projects[1]; $users->loadOne($project); $user = $project->_one_user;

loadMany

Load the "_many_" array of objects property from another table to an object. $table->loadMany($obj); $user = $users[1]; $projects->loadMany($user); $userProjects = $user->_many_project;

loadMany2many

Load the "_many2many_" array of objects property from another table to an object. A third parameter can be specified for the junction table. If you don't specify a junction table, the convention is to order names of tables alphabetically and join them with an underscore "_". For instance with user and project, the implicit junction table will be "project_user". $table->loadMany2many($obj,$via=null); $user = $users[1]; $projects->loadMany2many($user); $userProjects = $user->_many2many_project; $project = $project[1]; $users->loadMany2many($project); $projectUsers = $user->_many2many_user; $user = $users[1]; $projects->loadMany2many($user,'author'); $authorProjects = $user->_many2many_project; $project = $projects[1]; $users->loadMany2many($project,'author'); $projectAuthors = $user->_many2many_user;

one

Get the "_one_" object from another table. $table->one($obj); $user = $users->one($project);

many

Get the "_many_" array of objects from another table. $table->many($obj);

many2many

Load the "_many2many_" array of objects from another table. A third parameter can be specified for the junction table. If you don't specify a junction table, the convention is to order names of tables alphabetically and join them with an underscore "_". For instance with user and project, the implicit junction table will be "project_user". $table->many2many($obj,$via=null); $user = $users[1]; $userProjects = $projects->many2many($user); $project = $project[1]; $projectUsers = $users->many2many($project); $user = $users[1]; $authorProjects = $projects->many2many($user,'author'); $project = $projects[1]; $projectAuthors = $users->many2many($project,'author');

on

Attach a callback to a table event. If callback is string, it will be interpreted as a method of the entity object. If no callback is specified it will take the event name as a method to call on entity object. You can also use a zIndex like and a prepend option. $table->on($event,$call); $users->on('afterCreate',function($entity,$db){ echo "User '{$entity->name}' created !"; }); $users->on('afterCreate',function($entity,$db){ echo "First (prepended) callback after user '{$entity->name}' created !"; },0,true);

off

Detach a callback from a table event. If no callback specified it will remove all callbacks attached to the table event. You can use the zIndex like as last parameter. $table->off($event,$call); $userCreated = function($entity,$db){ echo "First Callback after user '{$entity->name}' created !"; }; $users->off('afterCreate',$userCreated,1); $users->off('afterCreate',$userCreated); $users->off('afterCreate',null,1); $users->off('afterCreate');

trigger

Trigger as indicated by its name, trigger an event on a table and targetting a row. The third parameter enable recursivity. The fourth parameter allow you to configure recursivity order, if is null it will recurse from called to attached row entities, if is true it will recurse from enclosers to nested row entities and if is false it will recurse from nested row entities to enclosers. $table->trigger($event, $row, $recursive=false, $flow=null); $table->trigger('openRecursive', $row, true, true); $table->trigger('closeRecursive', $row, true, false); $table->trigger('beforeCreate', $row, true); $table->trigger('afterCreate', $row, true); $table->trigger('mySingleRowEvent', $row);

setCounter

Set a counter callback to use instead of default or implemented one in a DataTable specification. The counter callback will be invoked on call to count method or function. $table->setCounter($call); $table = clone $aTable; //for mysql fulltext $table->setCounter(function()use($table,$cols,$tableName,$text){ return $table->dataSource->getCell('SELECT COUNT(IF(MATCH ('.$cols.') AGAINST (?), 1, NULL)) FROM '.$tableName,[$text]); }); $countFulltextSearchResults = count($table); $countFulltextSearchResults = $table->count();

Relational Table

exists

Check if a table exists. $table->exists();

fetch

The fetch method allow you direct access to a cursor from a table, so you get each row in a loop and don't overflow your php memory usage while traversing a whole table for example. while($row = $table->fetch()){ }

getRow

Usefull to retrieve one row as an associative array if the configured table query is targetting a single row. $row = $table->getRow();

getAll

Retrieve all row as an array of associative array from the whole table or configured table query. $rows = $table->getAll();

magic iterator

With the magic iterator you can make a loop on the table and traverse the whole table or a filtered table, if object was configured.
A cursor will be used, so, traversing a whole big table will not overflow your php memory usage. The key will correspond to primaryKey id. foreach($table as $id=>$row){ } // iterator methods $table->rewind(); // reset($table); $table->current(); // current($table); $table->key(); // key($table); $table->valid(); $table->next(); // next($table);

magic count

If you call count function on a table, it will trigger the "count" method. It will count all rows of a table or the expected results if table object was configured. If you used the setCounter method to define a count callback it will use the specified callback else it will use countSimple method. $count = count($table); //count method $count = $table->count();

countSimple

The countSimple method use the configured query of the table removing it's "order by" clauses and selected columns and select instead a "COUNT(*)" on the remaining query. $count = $table->countSimple();

countNested

The countNested method, like the countSimple, use the configured query of the table removing it's "order by" clauses and selected columns and select instead just the primary key on the remaining query. The difference is that it will execute the query into as a subquery and then count the result. So the count process will be slower than in countSimple direct query but more relevant in some complex faceted search use cases. $count = $table->countNested();

countAll

The countAll method will return the total count of rows in a table independently from the configured query. $table->countAll();

createSelect

Get a "SqlComposer\Select" query object configured with the table name as main table, and, corresponding to the parent database of table, the quote character and table prefix. $select = $table->createSelect();

Table Sql Composer API

The following methods are callable on table to configure the "SqlComposer\Select" attached to it. You can use them with "SqlComposer\Select" object apart the table object to build dynamic SQL queries directly.
The following methods change the query but nothing is executed before you access table via getAll, fetch or loop on iterator.

getQuery

Get the query as string. $sql = $table->getQuery();

getParams

Get the binded parameters. $params = $table->getParams();

selectMany2many

Add a column select from a table related to current table via a the conventional junction table between two tables. The primary key of the target table is automatically selected. $table->selectMany2many($select,$colAlias=null); $articles->selectMany2many('tag.name'); $articles->selectMany2many('tag.name','tag_name');

selectMany

Add a column select from a table related to current table via foreign key of target table. The primary key of the target table is automatically selected. $table->selectMany($select,$colAlias=null); $users->selectMany('project.title');

selectOne

Add a column select from a table related to current table via foreign key of the current table. The primary key of the target table is automatically selected. $table->selectOne($select,$colAlias=null); $projects->selectMany('user.email'); $projects->selectMany('user.name');

selectRelational

Select relations via multidimensional queries. Here is a convention:

  • < one ( parent row )
  • > many ( own rows )
  • <> many2many ( many to many / shared rows )

You can access data through an infinity of tables via their junctions. Whites spaces have no signification, they will be removed but you can use them to aerate the code.
The relational select will build multidimensional sub arrays by exploding the result of sql aggregate functions (specific to each database). $table->selectRelational($select,$colAlias=null); $project->selectRelational(' < user.email '); //equivalent of $projects->selectOne('user.email'); $users->selectRelational(' > project.title'); //equivalent of $users->selectMany('project.title'); $articles->selectRelational(' <> tag.name '); //equivalent of $articles->selectMany2many('tag.name'); //get tag names used in others articles from the user related to each article $articles->selectRelational(' articles <>tag .name ');

hasSelectRelational

Check if the configured query has at least one relational select using aggregation and configured before with one of the following methods: selectRelational, selectOne, selectMany, selectMany2many. if($table->hasSelectRelational()){ }

tableJoin

Add a simple join on a table adding join table in first parameter, on junction in second parameter and binding parameters optionaly in third parameter. $table->tableJoin($table, $join, array $params = null); $projects->tableJoin('JOIN user', 'ON user.id = project.user_id');

joinAdd

Add a join parameter. $table->joinAdd($join,array $params = null); $projects->joinAdd('JOIN user ON user.id = project.user_id');

join

Add a join parameter with implicit regular JOIN. $table->join($join, array $params = null); $projects->join('user ON user.id = project.user_id');

joinLeft

Add a join left parameter. $table->joinLeft($join, array $params = null); $projects->joinLeft('user ON user.id = project.user_id');

joinRight

Add a join right parameter. $table->joinRight($join, array $params = null); $projects->joinRight('user ON user.id = project.user_id');

joinOn

Add a "join on" snippet which will be related to ordered previous join. $table->joinOn($join, array $params = null); $projects->joinLeft('user ON user.id'); $projects->joinOn('user.id = project.user_id');

from

Add a table or a sub-query to "FROM" clause. $table->from($table, array $params = null);

unTableJoin

$table->unTableJoin($table=null,$join=null,$params=null);

unJoin

$table->unJoin($join=null,$params=null);

unFrom

$table->unFrom($table=null,$params=null);

setParam

$table->setParam($k,$v);

getParam

$table->getParam($k);

unWhere

$table->unWhere($where=null,$params=null);

unWith

$table->unWith($with=null,$params=null);

unWhereIn

$table->unWhereIn($where,$params=null);

unWhereOp

$table->unWhereOp($column, $op, array $params=null);

unOpenWhereAnd

$table->unOpenWhereAnd();

unOpenWhereOr

$table->unOpenWhereOr();

unOpenWhereNotAnd

$table->unOpenWhereNotAnd();

unOpenWhereNotOr

$table->unOpenWhereNotOr();

unCloseWhere

$table->unCloseWhere();

where

$table->where($where, array $params = null);

whereIn

$table->whereIn($where, array $params);

whereOp

$table->whereOp($column, $op, array $params=null);

openWhereAnd

$table->openWhereAnd();

openWhereOr

$table->openWhereOr();

openWhereNotAnd

$table->openWhereNotAnd();

openWhereNotOr

$table->openWhereNotOr();

closeWhere

$table->closeWhere();

with

$table->with($with, array $params = null);

select

$table->select($select, array $params = null);

distinct

$table->distinct($distinct = true);

groupBy

$table->groupBy($group_by, array $params = null);

withRollup

$table->withRollup($with_rollup = true);

orderBy

$table->orderBy($order_by, array $params = null);

sort

$table->sort($desc=false);

limit

$table->limit($limit);

offset

$table->offset($offset);

having

$table->having($having, array $params = null);

havingIn

$table->havingIn($having, array $params);

havingOp

$table->havingOp($column, $op, array $params=null);

openHavingAnd

$table->openHavingAnd();

openHavingOr

$table->openHavingOr();

openHavingNotAnd

$table->openHavingNotAnd();

openHavingNotOr

$table->openHavingNotOr();

closeHaving

$table->closeHaving();

unSelect

$table->unSelect($select=null, array $params = null);

unDistinct

$table->unDistinct();

unGroupBy

$table->unGroupBy($group_by=null, array $params = null);

unWithRollup

$table->unWithRollup();

unOrderBy

$table->unOrderBy($order_by=null, array $params = null);

unSort

$table->unSort();

unLimit

$table->unLimit();

unOffset

$table->unOffset();

unHaving

$table->unHaving($having=null, array $params = null);

unHavingIn

$table->unHavingIn($having, array $params);

unHavingOp

$table->unHavingOp($column, $op, array $params=null);

unOpenHavingAnd

$table->unOpenHavingAnd();

unOpenHavingOr

$table->unOpenHavingOr();

unOpenHavingNotAnd

$table->unOpenHavingNotAnd();

unOpenHavingNotOr

$table->unOpenHavingNotOr();

unCloseHaving

$table->unCloseHaving();

hasColumn

$table->hasColumn();

getColumn

$table->getColumn();

hasTable

$table->hasTable();

getTable

$table->getTable();

hasJoin

$table->hasJoin();

getJoin

$table->getJoin();

hasFrom

$table->hasFrom();

getFrom

$table->getFrom();

hasWhere

$table->hasWhere();

hasWith

$table->hasWith();

getWhere

$table->getWhere();

getWith

$table->getWith();

hasSelect

$table->hasSelect();

getSelect

$table->getSelect();

hasDistinct

$table->hasDistinct();

hasGroupBy

$table->hasGroupBy();

getGroupBy

$table->getGroupBy();

hasWithRollup

$table->hasWithRollup();

hasHaving

$table->hasHaving();

getHaving

$table->getHaving();

hasOrderBy

$table->hasOrderBy();

getOrderBy

$table->getOrderBy();

hasSort

$table->hasSort();

getSort

$table->getSort();

hasLimit

$table->hasLimit();

getLimit

$table->getLimit();

hasOffset

$table->hasOffset();

getOffset

$table->getOffset();

Entity Model

The object representing a row of table is called entity. The set of the classes to instanciate for make theses objects is called entity model. EntityModel is a basic class that you can extends to build evenemential entities. You can also implements the FoxORM Observer interface if you don't want to extends EntityModel.
The corresponding class to a table will be used when factory, create, read, update or delete a row object.
Folowing is not evenemential because is not an instance of Observer, even if you add the events methods they will not be triggered: namespace EntityModel; class User{ } You have to add all the events methods. namespace EntityModel; class User implements FoxORM\Observer{ function beforeRecursive(){} function beforePut(){} function beforeCreate(){} function beforeRead(){} function beforeUpdate(){} function beforeDelete(){} function afterPut(){} function afterCreate(){} function afterRead(){} function afterUpdate(){} function afterDelete(){} function afterRecursive(){} } The events methods are allready implemented but you can overload the ones which interest you. namespace EntityModel; class User extends \FoxORM\EntityModel{ function afterCreate(){} function afterRead(){} function afterUpdate(){} function afterDelete(){} }

Observer Workflow

The observer workflow is a complete evenemential system on which you can rely to control your entities model. The set of events are:

See Entity Model for methods implementation.

beforeRecursive

This event is triggered recursively from the top encloser entity to the nested ones when a put (create or update) is performed and before all other events. You can use it to validate the set of all the entities you are about to store.

beforePut

This event is triggered recursively in the order the entities will be stored when a put (insert or update) is performed. This event is trigerred after beforeRecursive and before beforeCreate or beforeUpdate event.

beforeCreate

This event is triggered recursively in the order the entities will be stored when a create (insert) is performed. This event is trigerred after beforePut event and before the storage execution.

beforeRead

This event is triggered after an entitie object is factorised and before performing the read query to the database.

beforeUpdate

This event is triggered recursively in the order the entities will be stored when an update is performed. This event is trigerred after beforePut event and before the storage execution.

beforeDelete

This event is triggered when an delete is performed. This event is trigerred before the storage execution.

afterPut

This event is triggered recursively in the order the entities was stored when a put (insert or update) is performed. This event is trigerred after the storage execution and after afterCreate or afterUpdate event.

afterCreate

This event is triggered recursively in the order the entities was stored when a create (insert) is performed. This event is trigerred after the storage execution and before afterRecursive event.

afterRead

This event is triggered after an entity object has been loaded with the data from performed database read query.

afterUpdate

This event is triggered recursively in the order the entities was stored when an update is performed. This event is trigerred after the storage execution and before afterRecursive event.

afterDelete

This event is triggered when an delete is performed. This event is trigerred after the storage execution.

afterRecursive

This event is triggered recursively from the nested entities to the top encloser one when a put (create or update) is performed and after all other events.

Relations

You can access or make relations in many ways, here is somes examples:
Using facade way to read it directly: foreach(F::many2one($project,'user') as $user){ debug($user); } F::loadMany2one($project,'user'); debug($project); foreach(F::one2many($user,'project') as $project){ debug($project); } Storing with explicit "_many_" meta prefix which do a one to many relation. The _x_ suffix specify that is an exclusive link. When it's an exclusive relation the list are considered to be dependent on their parent row. If it's removed, they will be deleted as well. $user = F::create('user',[ 'name'=>'test', '_many_note_x_'=>[ [ 'description'=>'Testing relational CRUD' ], ], ]); Storing implicit relations: if there is no prefix to key of not scalar value, there are implicits rules. An array of objects mean many, and an object mean one. F::create('user',[ 'name'=>'test', 'project_x_'=>[ (object)[ 'name'=>'myproject' ], (object)[ 'name'=>'myproject2' ], ], ]); F::create('project',[ 'name'=>'myproject', 'user_x_'=>(object)[ 'name'=>'test' ], ]); You can even build directly some many to many rows related by an implicit conventional junction table. $user = F::create('user',[ 'name'=>'test', '_many2many_project_x_'=>[ [ 'name'=>'myproject' ], [ 'name'=>'myproject2' ], ], ]); Load the related rows before updating one if you don't want to remove all of them when storing. $user = F::read('user',1); $user->_many2many_project_x_ = F::many2many($user,'project')->getAll(); $user->_many2many_project_x_[21]['description'] = 'updated '.time(); $user = F::update($user); Use alias relations with key different from "_type" meta property. $user = F::create('user',[ 'name'=>'test', '_many_note_x_'=>[ [ '_type'=>'info', 'description'=>'Making CRUD alias' ], ], ]); $user = F::create('info',[ 'name'=>'Making CRUD alias', '_one_author_x_'=>[ '_type'=>'user', 'name'=>'test', ], ]);

Data Source Plugin

To create a DataSource plugin for support an other database you have to create tow class with a name uppercased on first character which correspond to the name of database type. One of theses two class have to extends DataSource or DataSource\SQL if it's an SQL database and placed in DataSource sub namespace. The second have to extends DataTable or DataTable\SQL if it's SQL database and placed in DataTable sub namespace. Let's take an example, for database type called mybase:
in DataSource/Mybase.php namespace RedCat\FoxORM\DataSource; class Mybase extends \FoxORM\DataSource{ } in DataTable/Mybase.php namespace RedCat\FoxORM\DataTable; class Mybase extends \FoxORM\DataTable{ } and use it: $bases[0] = [ 'type'=>'mybase', 'host'=>'localhost', 'port'=>'3306', 'name'=>'my_db_name', ]; Implements the abstract required methods and see how other classes works by taking a look on their respectives codes to understand how to design your database layers.