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('
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:
- beforeRecursive
- beforePut
- beforeCreate
- beforeRead
- beforeUpdate
- beforeDelete
- afterPut
- afterCreate
- afterRead
- afterUpdate
- afterDelete
- afterRecursive
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.