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 Domframework\dblayeroo ($dbconfig["dsn"], $dbconfig["username"],
                         $dbconfig["password"], $dbconfig["driver_options"]);
$table1->table ("groupedoo");
$table1->fields (array ("mail"=>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 ();

Specify the real type of the field

If the field must store a mail, the SQL type will be varchar(255). It is possible to check if the provided value is a valid mail before doing the INSERT or UPDATE. To do that, add in table specification :

$table1->realTypes (array ("fieldName" => "type"));

The default types are :

mail, date, datetime, allowedchars(a23d),
uuid, time, array('val1','val2','val3'), regex(/^[a-zA-Z]{3,}$/i)
integer, integerPositive

Then if the provided data is not a valid mail, the INSERT or UPDATE will be stopped.

It is also possible to check the data to see if an error will occured. Use :

$errors = array_merge ($this->verify ($values, $emptyAllowed),
                       $this->checkRealTypes ($values, $emptyAllowed));

If the default types are insufficient, you can extend the dblayeroo class and create a new method named "checkRealType_XXX ($val, $definition)" where XXX is the new type to check. In this method, $val will be set to the user data, and definition is the parameters set to test the value Then create the specification :

$table1->realTypes (array ("fieldName" => "XXX"));

The class definition

Class Domframework\Dblayeroo

Namespace Domframework

Description

/**
 Permit abstraction on the differents SQL databases available

Properties

protected $debug=false;

/**
 Debug of the SQL

protected $debugDepth=1;

/**
 The debug depth (as we clone object, the depth is increased to debug
 easily the functions

Methods

public function DBException ( $message, $code=500)

/**
 Error management
 @param string $message The message to throw in the exception
 @param integer|null $code The error code to return to the user

public function DBExceptionMsg ( $message)

/**
 Return the $message adapted with the debug trace if needed
 @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 checkRealTypes ( $values, $allowEmpty=false)

/**
 Check the types of the data against the realTypes
 Return an array with the field name in error and a message
 If allowEmpty is set, do not test the "NOT NULL" feature
 In UPDATE, the values don't need be not set : they are already in database
 so $allowEmpty is true
 @param array $values The values to test
 @param boolean|null $allowEmpty Allow the "not null" to not be set
 @return array empty array if no error

public function checkValues ( $values, $update=false)

/**
 Check the values before doing really the modification of the database
 @param array $values The values to test
 @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 debugLog ( $message, $priority)

/**
 Debug function
 @param mixed ...$message The message to display in debug
 @param integer priority The display message if $priority <= $this->debug

public function delete ()

/**
 Alias of command ("DELETE")

public function directQuery ( $sql)

/**
 Execute a non prepared query in the database context.
 As there is no verification, this method is DANGEROUS and should not be
 used !
 @param string $sql The SQL request to directely send to the database
 @return PDOStatement The PDO Statement to traverse

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 sort 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 groupByNeeded ()

/**
 Return true if this object or one of the join objects need GROUP BY SQL
 part

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 meta ( $meta=null)

/**
 Get the meta data or clear them.
 The meta data are all the requests done by the dblayeroo, the time needed
 for each, the number of rows returned by the SQL server.
 They allow to debug the app
 @param boolean|null $meta If meta is set, clear the meta-data. If not set,
 return the actual value of the meta-data
 @return array or $this

public function normalize ( $values)

/**
 Normalize the values before using them.
 The normalize is called by methods : verify, checkRealTypes and setValues
 By default, remove the spaces (trim) at begin and end.
 This method can be overloaded by extending the class
 @param array $values The values to test or INSERT or UPDATE
 @return array the updated values

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", "NATASC",
  "NATDESC");

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 realTypes ( $realTypes=null)

/**
 Define a real type array
 Must be array ("field" => "realtype")
 @param array $realTypes The realTypes to set
 The allowed real types are defined in test method :
   checkRealType_TYPE ($val, $definition)
 The allowed real types are : mail, date, datetime, allowedchars(a23d),
 uuid, time, array('val1','val2','val3'), regex(/^[a-zA-Z]{3,}$/i)
 integer, integerPositive,
 To be done :
 integerNegative,
 php_function(), function($val) { if ($val > 0) return "MESSAGE";}

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