Back to the module list

OO Database Layer

Allow to manage a database connection by object-oriented methods Use an abstraction layer to manage the database engines for MySQL, SQLite or PostgreSQL All the requests are "preparated" before use. They are not sensible to SQL injection

Definition of the tables

$table1 = new dblayeroo ($dbconfig["dsn"], $dbconfig["username"],
                         $dbconfig["password"], $dbconfig["driver_options"]);
$table1->table ("groupedoo");
$table1->fields (array ("group"=>array ("varchar(255)", "not null"),
                        "object"=>array ("varchar(255)", "not null"),
                        "where"=>array ("varchar(255)", "not null"),
                        "with space"=>array ("varchar(255)")));
$table1->unique (array ());
$table1->primary ("group");
');

Get data from database: the SELECT method

Select all the rows

$res = $table1->select ()->execute ();

Select with one WHERE clause

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->execute ();

The where clause allow the operators: "=", "<=", ">=", "!=", "NOT LIKE", "LIKE", "IS NULL", "REGEXP", "NOT REGEXP"

Select with multiple WHERE clauses

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

The clauses are by default with AND To add a OR clause

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAddOr ()
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

It is also possible to add parenthesis with

$res = $table1->select ()
              ->whereAddParenthesisOpen ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAddOr ()
              ->whereAdd ("user", "=", "nobody")
              ->whereAddParenthesisClose ()
              ->execute ();

Return only two column

// The column order is defined by the order of adding. The first one is the
// first asked to the database
// The column can be defined by string separated by comma or array
$res = $table1->select ()
              ->displayAdd ("col1,col2")
              ->execute ();

It is also possible to add the group functions in "AVG", "COUNT", "GROUP_CONCAT", "MAX", "MIN","SUM".

$res = $table1->select ()
              ->displayAdd ("group_concat(col2)")
              ->execute ();

It is also possible to limit the result to DISTINCT elements

$res = $table1->select ()
              ->displayAdd ("DISTINCT col2")
              ->execute ();

Add limits

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->limitLines (3)
              ->execute (); ');

Or get only the 3 lines starting at 5th position

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->limit (5, 3)
              ->execute ();

Insert data in the database: the INSERT method

$res = $table1->insert ()
              ->setValues (array ("field1"=>"val1",
                                  "field2"=>"val2"))
              ->execute ();

Attention: can only add one row by call If you call multiple times setValues, only the last one is recorded

Update data in the database: the UPDATE method

$res = $table1->update ()
              ->setValues (array ("field1"=>"val1",
                                  "field2"=>"val2"))
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

If there is no whereAdd call, all the existing rows are overwrited

Remove data from the database: the DELETE method

$res = $table1->delete ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

If you don't specify the whereAdd clause, all the table is cleared

Join two tables

The library allow to join Inner, Left, Right. It need two dblayeroo objects, one for each table.

$res = $table1->select ()
              ->joinInner ($table2, array ("fieldATable1"=>"fieldBTable2",
                                           "fieldCTable1"=>"fieldDTable2"))
              ->execute ();

The class definition

Class \dblayeroo

Namespace \

Description

 Permit abstraction on the differents SQL databases available

Properties

No property available

Methods

public function DBException ($message)
 Error management
 @param string $message The message to throw in the exception

public function __construct ($dsn, $username=null, $password=null, $driver_options=null)
 Connection to the database engine
 See http://fr2.php.net/manual/en/pdo.construct.php for the $dsn format
 @param string $dsn PDO Data Source Name
 @param string|null $username Username to connect
 @param string|null $password Password to connect
 @param string|null $driver_options Driver options to the database

public function beginTransaction ()
 Start a new Transaction

public function checkValues ($update=false)
 Check the values before doing really the modification of the database
 @param boolean|null $update if true UPDATE request, else INSERT request

public function clearRequest ()
 Reinit the SQL request

public function command ($command)
 Define the command to execute. Can be
 "SELECT", "INSERT", "DELETE", "UPDATE".
 @param string $command The command to execute

public function commit ()
 Commit (validate) a transaction

public function connect ($dsn, $username=null, $password=null, $driver_options=null)
 Connection to the database engine
 See http://fr2.php.net/manual/en/pdo.construct.php for the $dsn format
 @param string $dsn PDO Data Source Name
 @param string|null $username Username to connect
 @param string|null $password Password to connect
 @param string|null $driver_options Driver options to the database

public function createTable ()
 Create the table defined by the differents fields.
 Define the SQL syntax based on SQL engines
 $table = "dns zones";
 $fields = array (
   "id"=>array ("integer", "not null", "autoincrement"),
   "zo ne"=>array ("varchar(255)", "not null"),
   "vie wname"=>array ("varchar(255)"),
   "view clients"=>array ("varchar(255)"),
   "comme nt"=>array ("varchar(1024)"),
   "opendate"=>array ("datetime", "not null"),
   "closedate"=>array ("datetime"),
 );
 $primary = "id";
 $unique = array ("id", array ("zo ne", "vie wname"));
 $foreign = array ("zone"=>"table.field",...);

public function databasename ()
 Return the connected database name from DSN used to connect

public function debug ($debug=null)
 Get/Set the debug property
 @param integer|null $debug Set the debug value

public function delete ()
 Alias of command ("DELETE")

public function disconnect ()
 This function disconnect the database. It is normally only used in phpunit
 unit tests

public function displayAdd ($columnNames=array (), $aliasNames=array ())
 Set the columns to display for the next SELECT request.
 The columns are ordered by the first added to the last added
 @param array|string|null $columnNames The columns name, separated by comma
 By default, display all the columns if this method is not called
 If the value is null or not provided or an empty array, do not display
 any field
 @param array|string|null $aliasNames Add the Aliases to the displayed
 columns

public function displayColumn ($columnNames=array ())
 Changing the name displayColumns to displayAdd
 @param array|string|null $columnNames The columns name, separated by comma
 @deprecated 0.36

public function displayGet ($full=false)
 Get the columns set in the query by displayAdd. If the $full parameter
 is set, add the table prefix/name to the result.
 If the join object is set, ask to it the columns too
 @param boolean $full  Add the table prefix/name if set

public function dropTable ()
 Drop the table

public function dsn ($dsn=null)
 Get/Set the dsn property
 @param string|null $dsn Set the DSN property of PDO

public function execute ()
 Execute the pre-defined query
 Return the content array if SELECT command is choosed
 Return the Last ID if INSERT command is choosed
 Return the number of modified lines for UPDATE/DELETE command

public function exportConf ()
 Dump the configuration of the table in an array

public function fields ($fields=null)
 Get/Set the fields property
 The fields to define are in the format:
 array ("fieldName"=>array ("type"[, "not null"[, "autoincrement"]]))
 @param array|null $fields The fields to define

public function fieldsAll ($full=false)
 Get all the fields with the table name if needed.
 If the objectJoin is set, return the fields name too
 @param boolean|null $full Add the table name if the $full is set

public function foreign ($foreign=null)
 Get/Set the foreign property
 @param array|null $foreign The definition of the foreign constraint
 The format is :
 array (
   "field" => array ("parentTable", "parentField", "options if needed"),
 )
 Multiple field and parnentField can be provided, separated by comma

public function getDisplayQuery ()
 Return the query that will be executed

public function getSortOrder ()
 The method to get a new sorti order acrossed the differents objects

public function getTableSchema ($tableName)
 Get the informations about a table
 @param string $tableName The table to examine

public function groupByGet ($full=false)
 Get the GROUP BY fields defined. If a joinObject is set with GROUP BY
 statement, return the joinObject order with its tableprefix/name in
 addition of the ones of this object
 If the parameter $full is set, add the table prefix/name to the result
 @param boolean|null $full Add the table prefix/name if set

public function insert ()
 Alias of command ("INSERT")

public function joinInner ($object, $joinArray)
 Do a inner join between two dblayer objects
 The join array is a associated array with local field as key and distant
 field as value
 @param object $object The dblayeroo object to use for searching the join
 data
 @param array $joinArray The values to search for join

public function joinLeft ($object, $joinArray)
 Do a left join between two dblayer objects
 The join array is a associated array with local field as key and distant
 field as value
 @param object $object The dblayeroo object to use for searching the join
 data
 @param array $joinArray The values to search for join

public function joinRight ($object, $joinArray)
 Do a right join between two dblayer objects
 The join array is a associated array with local field as key and distant
 field as value
 @param object $object The dblayeroo object to use for searching the join
 data
 @param array $joinArray The values to search for join

public function joinsGet ($joinObject=null)
 Get the join SQL part with recursive call of the child joins
 @param object|null $joinObject The joinObject to examine too

public function limit ($startLine, $nbLines)
 Define a LIMIT for the request.
 To use only the nbLines, put a 0 on startLine
 @param integer $startLine The starting line in the result list
 @param integer $nbLines The number of lines to return

public function limitLines ($nbLines)
 Define a LIMIT for the request.
 @param integer $nbLines The number of lines to return

public function listTables ()
 Return all the tables available in the database

public function orderAdd ($field, $sort="ASC")
 Add a new ORDER sort. The multiple ORDERS are used from the first added to
 the last added
 @param string $field The field to sort
 @param string|null $sort The sort order ("ASC", "DESC");

public function orderGet ($full=false)
 Get the ORDER fields defined. If a joinObject is set with ORDER statement,
 return the joinObject order with its tableprefix/name in addition of
 the ones of this object
 If the parameter $full is set, add the table prefix/name to the result
 @param boolean|null $full Add the table prefix/name if set

public function primary ($primary=null)
 Get/Set the primary property
 @param string|null $primary The primary key to use

public function rollback ()
 RollBack a transaction

public function select ()
 Alias of command ("SELECT")

public function sep ()
 Get the sep property

public function setDistinct ()
 Set the DISTINCT option

public function setForeignObj ($object)
 Define a new foreign object
 @param object $object The dblayeroo object to use for foreign constraint
 checks

public function setValues ($values)
 Set INSERT/UPDATE values
 - The provided array must be an associative array, the field name must be
   provided as key and the value as value. Each field=>val will be updated
 @param array $values The values to INSERT or UPDATE

public function table ($table=null)
 Get/Set the table property
 @param string|null $table The table to use

public function tableprefix ($tableprefix=null)
 Get/Set the tableprefix property
 @param string|null $tableprefix The prefix to append

public function titles ($titles=null, $full=false)
 Get/Set the titles property
 @param array|null $titles The titles of the fields
 @param boolean|null $full Add the table name if the $full is set

public function unique ($unique=null)
 Get/Set the unique property
 @param array|null $unique The unique fields constraint to add

public function update ()
 Alias of command ("UPDATE")

public function verify ($values, $update=false)
 Check the provided values which will be inserted or updated against the
 database structure.
 In update, do not forget to define the whereAdd parameters !
 @param array $values The values to test
 @param boolean|null $update if true UPDATE request, else INSERT request
 @return array The errors found by field

public function whereAdd ($field, $operator, $value="")
 Set a new WHERE expression value
 @param string $field The field to check
 @param string $operator The operator ("=", "<=", ">=", "!=", "NOT LIKE",
 "LIKE", "IS NULL", "REGEXP", "NOT REGEXP")
 @param string|null $value The value to search ("" if not provided)

public function whereAddAND ()
 Add a new AND to the WHERE expression

public function whereAddOR ()
 Add a new OR to the WHERE expression

public function whereAddParenthesisClose ()
 Add a new Close Parenthesis to the WHERE expression

public function whereAddParenthesisOpen ()
 Add a new Open Parenthesis to the WHERE expression

public function whereGetExpression ()
 Get the WHERE clause of the object.
 If the joinObject is set, return all the WHERE clauses

public function whereGetValues ()
 Get the WHERE values of the object.
 If the joinObject is set, return all the WHERE clauses with AND and
 parenthesis