Discussion: Database Abstraction (better: Query Abstraction)

I am developing an Abstraction class based on PDO. As PDO already provides DB Abstraction, I am focussing on abstracting SQL statements.

I'd like to discuss the synopsis.

Search example:

Code:

   $this->db = wbDatabase::singleton(
   array(
   'user' => DB_USERNAME,
   'pass' => DB_PASSWORD,
   'dbname' => DB_NAME,
   'prefix' => TABLE_PREFIX,
   )
   );

   $result = $this->db->search(
   array(
   'tables' => array( 'sections', 'pages' ),
   'join' => array( 't1.page_id == t2.page_id' ),
   'fields' => array( 'module', 'page_title' ),
   'where' => 't1.page_id == ? and section_id == ?',
   'params' => array( 1, 1 )
   )
   );

Resulting SQL statement:
SELECT module, page_title FROM wb_sections AS t1 LEFT JOIN wb_pages AS t2 ON t1.page_id = t2.page_id WHERE t1.page_id = ? AND section_id = ?

$result (Example):

Code:

array (
  0 =>
  array (
   'module' => 'wysiwyg',
   0 => 'wysiwyg',
   'page_title' => 'Homepage',
   1 => 'Homepage',
  ),
)

As you can see, the search() Syntax uses positional bind params to populate the query.

tables Attribute

All tables in the list are aliased t#, where # is starting with 1 for the first table, so the alias for the first table in the list is always t1, for the second table is t2, and so on. (If you only have one table, the tables attribute can be a string instead of array.)

fields Attribute

You can give a list of fields to retrieve. If you omit the attribute, '*' (all fields) is used.

join Attribute

Provide a valid join condition.

where Attribute

Provide a valid where condition.

params Attribute

A list of positional bind params to be used. Number of params must match the number of ? in the statement.

Example without 'join' and 'fields':

Code:

$result = $this->db->search(
   array(
   'tables' => array( 'sections', 'pages' ),
   'where' => 't1.page_id == ? and section_id == ?',
   'params' => array( 1, 1 )
   )
   );

SELECT * FROM wb_sections as t1, wb_pages as t2 WHERE t1.page_id = ? AND section_id = ?

Conditions

To abstract conditions, there are some operators and conjunction aliases.

Operators and their equivalents for mySQL:

'=' => '=', 'eq' => '=', 'ne' => '<>', '==' => '=', '!=' => '<>', '=~' => 'REGEXP', '!~' => 'NOT REGEXP',

Conjunctions:

'and' => 'AND', 'AND' => 'AND', 'OR' => 'OR', 'or' => 'OR', '&&' => 'AND', '||' => 'OR'

(Please note: There's no alias for LIKE at the moment.)

Let me know what you think.