Introducing PHP Data Objects - Wez Furlong

scacchicgardenΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 8 μήνες)

56 εμφανίσεις

Introducing

PHP Data Objects

Wez Furlong

wez@omniti.com

The Problem


No consistency of API between DB
extensions


Sometimes no self
-
consistency within
a given extension


Duplicated code (but not)


High maintenance

The PDO solution


Move PHP specific stuff into one
extension


Database specific stuff (only) in their
own extensions


Data
access

abstraction, not
database

abstraction

Features


Performance


Native C code beats a scripted solution


Takes advantage of latest PHP 5 internals


Power


Gives you common DB features as a base


Still be able to access specialist functions


Easy


Non
-
intrusive


Clear


Runtime extensible


Drivers can be loaded at runtime

Available Drivers


Oracle OCI [PDO_OCI]


ODBC V3, IBM DB2 [PDO_ODBC]


MySQL 3.x [PDO_MYSQL]


Postgres [PDO_PGSQL]


SQLite 3.x [PDO_SQLITE]


Firebird [PDO_FIREBIRD]

Getting PDO [unix]


Build PHP 5


--
with
-
zlib
--
prefix=/usr/local/php5


pear download PDO
-
alpha


tar xzf PDO
-
*.tgz


cd PDO
-
*


PATH=/usr/local/php5/bin:$PATH


phpize && ./configure && make


make install

Getting PDO [unix] 2


Select the driver(s) you need


pear download PDO_XXX
-
alpha


tar xzf PDO_XXX*.tgz


cd PDO_XXX*


phpize && ./configure && make


make install

Getting PDO [win32]


Grab the DLLs from the snaps site

http://snaps.php.net/win32/PECL_5_0/


You need:


php_pdo.dll


php_pdo_XXX.dll


Put them in C:
\
php5
\
ext

Switching it on


Need to enable PDO in your php.ini


MUST load PDO first


Unix:

extension=pdo.so

extension=pdo_XXX.so


Windows

extension=php_pdo.dll

extension=php_pdo_XXX.dll

Connecting via PDO

try {


$dbh = new PDO($dsn,


$user, $password, $options);

} catch (PDOException $e) {


echo “Failed to connect:”


. $e
-
>getMessage();

}

DSN format in PDO


Driver
:
optional_driver_specific_stuff


sqlite:/path/to/db/file


sqlite::memory:


mysql:host=
name
;dbname=
dbname


pgsql:
native_pgsql_connection_string


oci:dbname=
dbname
;charset=
charset


firebird:dbname=
dbname
;charset=
char
set
;role=
role


odbc:
odbc_dsn


DSN Aliasing


uri:uri


Specify location of a file containing actual DSN
on the first line


Works with streams interface, so remote URLs
can work too


name

(with no colon)


Maps to pdo.dsn.
name

in your php.ini


pdo.dsn.name=sqlite:/path/to/name.db


$dbh = new PDO(‘name’);


$dbh = new PDO(‘sqlite:/path/to/name.db’);


Neither of these allows for user/pass
(yet!)

Connection management

try {


$dbh = new PDO($dsn, $user, $pw);

} catch (PDOException $e) {


echo “connect failed:” . $e
-
>getMessage();

}

// use the database here

// …


// done; release the connection

$dbh = null;

Persistent PDO

$dbh = new PDO($dsn, $user, $pass,


array(


PDO_ATTR_PERSISTENT => true


)

);


Can specify a string instead of true


Useful for keeping 2 connections open
with similar credentials

Persistent PDO 2


PDO_ODBC supports native
connection pooling by default


Likely to be more resource efficient
than PDO ‘pconnect’


Can turn it off in php.ini:

pdo_odbc.connection_pooling=off


Need to restart web server after
changing it

Let’s get data

$dbh = new PDO($dsn);

$stmt = $dbh
-
>prepare(


‘SELECT * FROM FOO’);

$stmt
-
>execute();

while ($row = $stmt
-
>fetch()) {


print_r($row);

}

Fetch types


$stmt
-
>fetch(PDO_FETCH_BOTH)


Array with numeric and string keys


default option


PDO_FETCH_NUM


Array with numeric keys


PDO_FETCH_ASSOC


Array with string keys


PDO_FETCH_OBJ


$obj
-
>name holds the ‘name’ column from the row


PDO_FETCH_BOUND


Just returns true until there are no more rows

Let’s change data

$deleted = $dbh
-
>query(


“DELETE FROM FOO WHERE 1”);


$changes = $dbh
-
>query(


“UPDATE FOO SET active=1 ”


. “WHERE NAME LIKE ‘%joe%’”);

Smarter Queries


Quoting is annoying, but essential


PDO offers a better way

$stmt
-
>prepare(‘INSERT INTO CREDITS
(extension, name) VALUES (:extension,
:name)’);

$stmt
-
>execute(array(


‘:extension’ => ‘xdebug’,


‘:name’ => ‘Derick Rethans’

));

Binding for output

$stmt = $dbh
-
>prepare(


"SELECT extension, name from CREDITS");

if ($stmt
-
>execute()) {


$stmt
-
>bindColumn(‘extension', $extension);


$stmt
-
>bindColumn(‘name', $name);


while ($stmt
-
>fetch(PDO_FETCH_BOUND)) {


echo “Extension: $extension
\
n”;


echo “Author: $name
\
n”;


}

}

Portability Aids


PDO aims to make it easier to write
db independent apps


Number of hacks^Wtweaks for this
purpose


$dbh
-
>setAttribute(


PDO_ATTR_ORACLE_NULLS, true);


Converts empty strings to NULL when fetched

PDO_ATTR_CASE


Some databases (notably, Oracle) insist on returning
column names in uppercase


$dbh
-
>setAttribute(PDO_ATTR_CASE, PDO_CASE_UPPER);

$stmt = $dbh
-
>prepare(


"SELECT extension, name from CREDITS");

if ($stmt
-
>execute()) {


$stmt
-
>bindColumn(‘EXTENSION', $extension);


$stmt
-
>bindColumn(‘NAME', $name);


while ($stmt
-
>fetch(PDO_FETCH_BOUND)) {


echo “Extension: $extension
\
n”;


echo “Author: $name
\
n”;


}

}

Data typing


Very loose


uses strings for data


Gives you more control over data
conversion


Error handling


PDO offers 3 different error modes

$dbh
-
>setAttribute(PDO_ATTR_ERRMODE, $mode);


PDO_ERRMODE_SILENT


PDO_ERRMODE_WARNING


PDO_ERRMODE_EXCEPTION


Attempts to map native codes to
PDO generic codes


But still offers native info too

PDO_ERRMODE_SILENT

if (!$dbh
-
>query($sql)) {


echo $dbh
-
>errorCode() . "<br>";


$info = $dbh
-
>errorInfo();


// $info[0] == $dbh
-
>errorCode()


// unified error code


// $info[1] is the driver specific


// error code


// $info[2] is the driver specific


// error string

}

PDO_ERRMODE_EXCEPTION

try {


$dbh
-
>exec($sql);

} catch (PDOException $e) {


// display warning message print


$e
-
>getMessage();


$info = $e
-
>errorInfo;


// $info[0] == $e
-
>code;


// unified error code


// $info[1] is the driver specific error code


// $info[2] is the driver specific error string

}

Transactions

try {


$dbh
-
>beginTransaction();


$dbh
-
>query(‘UPDATE …’);


$dbh
-
>query(‘UPDATE …’);


$dbh
-
>commit();

} catch (PDOException $e) {


$dbh
-
>rollBack();

}

Cool stuff on the horizon


Iterators (coming real soon)

foreach ($stmt
-
>execute() as $row)


LOB support via streams


Bind the parameter


fwrite, fread(), fseek() on the LOB


Scrollable cursors

Resources


Oracle Technology Network article

http://www.oracle.com/technology/pub/articles/php_expert
s/otn_pdo_oracle5.html


These slides and other PDO news
bytes

http://netevil.org


Bugs?

http://pecl.php.net/bugs/report.php?
package=PDO_XXX