database abstraction layer database abstraction layers in PHP Lukas Smith BackendMedia

database abstraction layer

database abstraction layers in PHP

Lukas Smith

BackendMedia

database abstraction layer Overview • Introduction • Motivation • PDO extension • PEAR::MDB2 • Client API • SQL syntax • SQL concepts • Result sets • Error handling

database abstraction layer

Motivation

• Support for multiple RDBMS

• Forward compatibility

– new RDBMS versions

– new PHP (extension) versions (MySQLi/PDO)

• Vendor Lock in

• Train costs

• Pushing your own preference to the client

• Higher level API

database abstraction layer

PDO Extension

• C Extension

• OO Interface

• Driver model

• Client API Unification

• Planned for PHP 5.1

• Iterators

database abstraction layer

PEAR::MDB2 (1/2)

• OO Interface

• Driver model

• Compatible with PHP4 and PHP5

• PDO API compatible (eventually)

• On demand loading of advanced features

• Portability mode optional

• PEAR_Error and debug capabilities

• Iterators

database abstraction layer

PEAR::MDB2 (2/2)

• Installation:

– $> pear install MDB2-beta

– $> pear install MDB2_Schema-beta

• Already in production (planet-php.org)

• MDB was a merge of Metabase and DB • MDB2 consolidates this merge

• MDB2 is much faster than as Metabase, faster than DB, and as fast as ADODB (*)

• Marked with !

database abstraction layer

Client API

• Differences between RDBMS extensions

– Example: connect to RDBMS (! !):

• ibase_pconnect()/ibase_connect() • OCIPLogon()/OCILogon()

• sqlite_popen()/sqlite_open()

– Example: sending SQL to the server (! !):

• OCIExecute()

• mysql_query()/mysql_unbuffered_query() • pg_exec()

database abstraction layer

SQL syntax (1/5)

• SELECT LIMIT (

!

)

– Missing in some RDBMS (like Oracle) – Syntax differences

// set limit

$db->setLimit(10, 20);

• REPLACE (

!

)

– Only exists in MySQL and SQLite

• TRANSACTIONS

– Missing in some RDBMS (MySQL with MyISAM) – Syntax differences (! !)

database abstraction layer

SQL syntax (2/5)

• SUBSELECT (!)

– Missing in some RDBMS (z.B. MySQL < 4.x)

$sql = 'SELECT name FROM foo WHERE id ='. $db->quote(MDB2_Date::now(), 'time');

// subselect

$subselect = $db->subSelect($sql, 'text');

// sql string

$sql = 'SELECT is_active FROM bar WHERE id IN ('.$subselect.')';

database abstraction layer SQL syntax (3/5)

• Data types (

!

)

database abstraction layer SQL syntax (4/5) • Schema Management (!) – CREATE • Database • Table – Field – Index • Sequence – ALTER TABLE – SHOW • Databases • Tables • Indexes • Sequences • Views

database abstraction layer

SQL syntax (5/5)

• SQL functions

– Function names: SUBSTR() vs. SUBSTRING() – Parameter order

– Offset count starts at 0 or 1

• Stored Procedures

– Client API – Syntax

• Very hard to do

database abstraction layer

SQL concepts (1/5)

• Sequences vs. auto increment

– Sequences can emulate auto increment (!) – Auto increment can only emulate sequences

with a query builder

$id = $db->getBeforeID($table);

$sql = “INSERT INTO $table (“.$db->quote($id, 'integer').', 'foo')'; $db->query($sql);

database abstraction layer

SQL concepts (2/5)

• Prepared statements: ? placeholder (! !)

// create sql string

$sql = 'INSERT INTO foo (?, ?, ?)';

// send off query

$types = array('text', 'decimal', 'blob') $stmt = $db->prepare($sql, $types);

// bind values

$stmt->bindParam(0, $text);

database abstraction layer

SQL concepts (3/5)

• Prepared statements: named parameters (! !)

// create sql string

$sql = 'INSERT INTO foo (:url, :dm, :file)';

// send off query

$types = array('text', 'decimal', 'blob') $stmt = $db->prepare($sql, $types);

// bind values

$stmt->bindParam(‘url', $text); $stmt->bindParam(‘dm', $decimal);

database abstraction layer

SQL concepts (4/5)

• Execute statement (! !)

// send off query

$result = $stmt->execute();

// bind next set of data

$text = 'foo';

$decimal = 23453/8; $fp = 'bar.pdf';

// send off query

database abstraction layer

SQL concepts (5/5)

• Database (

!

)

• Foreign keys / Trigger

– Only possible with a query builder

• VIEWS

– Only possible with a query builder

• User management

database abstraction layer

Result sets

• (Un-)Buffered (! !)

• NULL vs. empty strings (! !) • Associative fetches (! !)

• Padded strings (! !) • Introspection (! !) • Data types (!)

// create sql string

$sql = 'SELECT dateBirth, decMoney FROM foo';

// send off query

database abstraction layer

Error handling

• Different error codes or no support for error

codes

• Error codes (

!

!

)

database abstraction layer

High level features (1/3)

• XML schema (

!

)

– CREATE and ALTER statement generation

• User defined data types (

!

)

– Serialize

• PEAR Error (

!

)

• Exceptions

database abstraction layer

High level features (2/3)

// expect no such table error

$db->expectError(MDB2_ERROR_NOSUCHTABLE);

// send off query, fetch all data and rekey

$res = $db->queryAll($sql, $types, MDB2_FETCHMODE_ASSOC, true);

$db->popExpect();

// table didnt exist?

if (MDB2::isError($res,

database abstraction layer

High level features (3/3)

• fetchAll() (

!

!

)

– Read all rows of a result set into a multi dimensional array all at once

– Result buffering – Rekey (!!)

• Use the first column as the key for the first array dimension instead of the row number

– Grouping (!!)

• All rows with the same value in the first column are grouped inside another array

database abstraction layer

Thank you

for your attention!