PDO 개요

GOLDILOCKS PDO (PDO_GOLDILOCKS) 드라이버는 PDO에서 GOLDILOCKS 데이터베이스에 접근할 수 있도록 PDO 인터페이스를 제공하며, PECL 패키지로 제공된다.

PDO_GOLDILOCKS는 GOLDILOCKS CLI 드라이버를 기반으로 작성되었다.

설치/구성

요구 사항

PHP와 동일한 시스템에 GOLDILOCKS의 클라이언트 패키지를 설치해야 하고 $GOLDILOCKS_HOME 환경 변수도 설정해야 한다.

설치

PECL을 이용한 설치

PDO_GOLDILOCKS 패키지 파일이 없을 경우 패키지 파일을 생성한다.

% pecl pickle
Attempting to process the second package file
Package PDO_GOLDILOCKS-3.2.0.tgz done

PDO_GOLDILOCKS 패키지 파일이 있을 경우 pecl를 이용해 패키지를 설치한다.

% sudo -E pecl install PDO_GOLDILOCKS-3.2.0.tgz

소스 빌드를 통한 설치

1. PDO_GOLDILOCKS 압축 해제

% tar xvzf PDO_GOLDILOCKS-3.2.0.tgz
% cd PDO_GOLDILOCKS-3.2.0

2. 빌드 환경 생성

phpize 명령어를 사용하여 PDO_GOLDILOCKS 확장 모듈을 위한 빌드 환경을 만들어 준다.
만약 phpize 명령어를 찾지 못할 경우, 적절한 버전의 PHP devel 패키지를 설치해야 한다.

% whereis phpize
phpize: /usr/bin/phpize /usr/share/man/man1/phpize.1.gz

% /usr/bin/phpize
Configuring for:
PHP Api Version:         20121113
Zend Module Api No:      20121212
Zend Extension Api No:   220121212

3. 빌드

--with-pdo-goldilocks 옵션에 $GOLDILOCKS_HOME 경로를 추가하고 --with-php-config 옵션에 php-config 경로를 추가하여 configure를 실행한 후에 빌드한다.

% whereis php-config
php-config: /usr/bin/php-config /usr/share/man/man1/php-config.1.gz

% ./configure --with-php-config=/usr/bin/php-config --with-pdo-goldilocks=/home/goldilocks/goldilocks_home/

% make

4. 설치

% sudo -E make install

PHP 설정 파일 수정

CentOS 6.0 이상 버전이나 Fedora 15 이상 버전을 사용할 경우 /etc/php.d 디렉토리에 pdo_goldilocks.ini 파일을 생성하고 다음과 같이 extension 속성을 추가해야 한다.

extension=pdo_goldilocks.so

다른 운영체제를 사용할 경우 php.ini 파일에 extension_dir을 적합하게 설정한 후에 다음과 같이 extension 속성을 추가한다.

extension=pdo_goldilocks.so

웹 서버 재시작

웹 서버를 다시 시작한다.

PDO_GOLDILOCKS 설치 확인

phpinfo()를 사용하여 PDO_GOLDILOCKS 활성화 여부를 확인한다.

% php -i | grep PDO
PDO
PDO support => enabled
PDO drivers => goldilocks, sqlite
PDO Driver for GOLDILOCKS => enabled
PDO Driver for SQLite 3.x => enabled

사용

데이터 원본 이름 (DSN)

PDO_GOLDILOCKS의 데이터 원본 이름 (DSN)은 다음과 같이 구성된다.

사용 예

• Connecting to GOLDILOCKS

print "\n[Connecting to GOLDILOCKS]\n";
$db = new PDO('goldilocks:HOST=192.168.0.16;PORT=22581', 'test', 'test');
//$db = new PDO('goldilocks:DSN=GOLDILOCKS;UID=test;PWD=test');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

• Set test data

$result = $db->exec('DROP TABLE IF EXISTS t1');
$result = $db->exec('CREATE TABLE t1 ( id INTEGER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(32) )');
$result = $db->exec("INSERT INTO t1(name) VALUES ('Carol')");
$result = $db->exec("INSERT INTO t1(name) VALUES ('Ted')");
$result = $db->exec("INSERT INTO t1(name) VALUES (null)");
$result = $db->exec("INSERT INTO t1(name) VALUES ('William')");
$result = $db->exec("INSERT INTO t1(name) VALUES ('Chelsea')");
$result = $db->exec("INSERT INTO t1(name) VALUES ('Colin')");

$result = $db->exec('DROP TABLE IF EXISTS t2');
$result = $db->exec('CREATE TABLE t2 ( id INTEGER, name VARCHAR(32) )');
$result = $db->exec("INSERT INTO t2 VALUES (1, 'John')");
$result = $db->exec("INSERT INTO t2 VALUES (1, 'John')");
$result = $db->exec("INSERT INTO t2 VALUES (1, 'John')");
$result = $db->exec("INSERT INTO t2 VALUES (2, 'Smith')");
$result = $db->exec("INSERT INTO t2 VALUES (2, 'Smith')");

$result = $db->exec('DROP TABLE IF EXISTS images');
$result = $db->exec('CREATE TABLE images ( id INTEGER, contenttype VARCHAR(256), imagedata LONG VARBINARY )');

$result = $db->exec('DROP PROCEDURE IF EXISTS sp_out_string');
$result = $db->exec("CREATE OR REPLACE PROCEDURE sp_out_string( v1 OUT VARCHAR(32) ) ".
                    "IS ".
                    "BEGIN ".
                    "  v1 := 'WORLD'; ".
                    "END; ");

$result = $db->exec('DROP PROCEDURE IF EXISTS sp_inout_string');
$result = $db->exec("CREATE OR REPLACE PROCEDURE sp_inout_string( v1 INOUT VARCHAR(32) ) ".
                    "IS ".
                    "BEGIN ".
                    "  v1 := V1 || ' WORLD'; ".
                    "END; ");

• Quoting a normal string

print "\n[Quoting a normal string]\n";
$string = 'Nice';

print "Unquoted string: $string\n";
print "Quoted string  : " . $db->quote($string) . "\n";

• Quoting a dangerous string

print "\n[Quoting a dangerous string]\n";
$string = 'Naughty \' string';

print "Unquoted string: $string\n";
print "Quoted string  :" . $db->quote($string) . "\n";

• Quoting a complex string

print "\n[Quoting a complex string]\n";
$string = "Co'mpl''ex \"st'\"ring";

print "Unquoted string: $string\n";
print "Quoted string  : " . $db->quote($string) . "\n";

• Handling an error

print "\n[Error Handling]\n";
try
{
    //connect as appropriate as above
    $db->query('invalid query'); //invalid query!
}
catch(PDOException $ex)
{
    print "ERROR:".$ex->getMessage()."\n";
}

• Retrieving column metadata

print "\n[Retrieving column metadata]\n";
$stmt = $db->query('SELECT * FROM t1');
foreach(range(0, $stmt->columnCount() - 1) as $column_index)
{
    $meta = $stmt->getColumnMeta($column_index);
    var_dump($meta);
}

• Running simple select statements

print "\n[Running Simple Select Statements]\n";

print "\n#1\n";
foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row['ID'].' '.$row['NAME']."\n";
}

print "\n#2\n";
$stmt = $db->query('SELECT * FROM t1');
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    print $row['ID'].' '.$row['NAME']."\n";
}

• Fetching rows using different fetch styles

print "\n[Fetching rows using different fetch styles]\n";
$stmt = $db->prepare("SELECT id, name FROM t1");
$stmt->execute();

print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name\n");
$result = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("\n");

print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number\n");
$result = $stmt->fetch(PDO::FETCH_BOTH);
print_r($result);
print("\n");

print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $stmt->fetch(PDO::FETCH_LAZY);
print_r($result);
print("\n");

print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $stmt->fetch(PDO::FETCH_OBJ);
print $result->NAME;
print("\n");

• Fetching rows with a scrollable cursor

print "\n[Fetching rows using different fetch styles]\n";
print "\n[Fetching rows with a scrollable cursor]\n";
$sql = 'SELECT id, name FROM t1 ORDER BY id';

print "Reading forwards:\n";
try
{
    $stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    while( $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT) )
    {
        $data = $row[0] . "\t" . $row[1] . "\n";
        print $data;
    }
    $stmt = null;
}
catch (PDOException $e)
{
    print $e->getMessage();
}

print "Reading backwards:\n";
try
{
    $stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
    do
    {
        $data = $row[0] . "\t" . $row[1] . "\n";
        print $data;
    } while( $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR) );

    $stmt = null;
}
catch (PDOException $e)
{
    print $e->getMessage();
}

• Constructing an order

print "\n[Construction order]\n";
class Person
{
    private $NAME;

    public function __construct()
    {
        $this->tell();
    }

    public function tell()
    {
        if (isset($this->NAME))
        {
            print "I am {$this->NAME}.\n";
        }
        else
        {
            print "I don't have a name yet.\n";
        }
    }
}
$stmt = $db->query("SELECT name FROM t1");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'Person');
$person = $stmt->fetch();
$person->tell();
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Person');
$person = $stmt->fetch();
$person->tell();

• Running simple INSERT, UPDATE, or DELETE statements

print "\n[Running Simple INSERT, UPDATE, or DELETE statements]\n";
$affected_rows = $db->exec("INSERT INTO t1(name) VALUES ('John'), ('Marry')");
print $affected_rows." were affected\n";

• Getting the last insert id

print "\n[Getting the Last Insert Id]\n";
$affected_rows = $db->exec("INSERT INTO t1(name) VALUES ('Smith')");
$insertId = $db->lastInsertId();
print "last insert id : ".$insertId."\n";

• Running statements with parameters

print "\n[Running Statements With Parameters]\n";

print "\n#1 array\n";
$id = 1;
$name = 'John';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=? AND name=?");
$stmt->execute(array($id, $name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

print "\n#2 bindValue\n";
$id = 2;
$name = 'Smith';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

• Named placeholders

print "\n[Named Placeholders]\n";

print "\n#1 array\n";
$id = 1;
$name = 'John';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

print "\n#2 bindValue\n";
$id = 2;
$name = 'Smith';

$stmt = $db->prepare("SELECT * FROM t2 WHERE id=:id AND name=:name");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_dump($rows);

• Executing prepared statements in a loop

print "\n[Executing prepared statements in a loop]\n";
$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $db->prepare("INSERT INTO t1(name) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name)
{
   $stmt->execute();
}

foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

• Calling a stored procedure with an output parameter

print "\n[Calling a stored procedure with an output parameter]\n";
$stmt = $db->prepare("CALL sp_out_string(?)");
$stmt->bindParam(1, $value, PDO::PARAM_STR, 32); 
$stmt->execute();

print "procedure returned $value\n";

• Calling a stored procedure with an input/output parameter

print "\n[Calling a stored procedure with an input/output parameter]\n";
$stmt = $db->prepare("CALL sp_inout_string(?)");
$value = 'HELLO';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 32); 
$stmt->execute();

print "procedure returned $value\n";

• Transactions

print "\n[Transactions]\n";

print "\n#1 commit\n";
try
{
    $db->beginTransaction();
 
    $db->exec("INSERT INTO t1(name) VALUES('kim')");

    $name = 'lee';
    $stmt = $db->prepare("INSERT INTO t1(name) VALUES(?)");
    $stmt->execute(array($name));
 
    $id = 3;
    $name = 'park';
    $stmt = $db->prepare("INSERT INTO t2 VALUES(?, ?)");
    $stmt->execute(array($id, $name));
 
    $db->commit();
}
catch(PDOException $ex)
{
    //Something went wrong rollback!
    $db->rollBack();
    print $ex->getMessage()."\n";
}

print "\nT1\n";
foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

print "\nT2\n";
foreach($db->query('SELECT * FROM t2') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

print "\n#2 rollback\n";
try
{
    $db->beginTransaction();
 
    $db->exec("INSERT INTO t1(name) VALUES('KIM')");

    $name = 'LEE';
    $stmt = $db->prepare("INSERT INTO t1(name) VALUES(?)");
    $stmt->execute(array($name));
 
    $id = 3;
    $name = 'PARK';
    $stmt = $db->prepare("INSERT INTO invalid VALUES(?, ?)");
    $stmt->execute(array($id, $name));
 
    $db->commit();
}
catch(PDOException $ex)
{
    //Something went wrong rollback!
    $db->rollBack();
    print $ex->getMessage()."\n";
}

print "\nT1\n";
foreach($db->query('SELECT * FROM t1') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

print "\nT2\n";
foreach($db->query('SELECT * FROM t2') as $row)
{
    print $row[0].' '.$row[1]."\n";
}

• Inserting an image into a database

print "\n[Inserting an image into a database]\n";
try
{
    $stmt = $db->prepare("INSERT INTO images (id, contenttype, imagedata) VALUES (?, ?, ?)");

    $id = 1;
    $type = "image/png";
    $fp = fopen("logo.png", "rb");

    $stmt->bindParam(1, $id, PDO::PARAM_INT);
    $stmt->bindParam(2, $type, PDO::PARAM_STR, 256);
    $stmt->bindParam(3, $fp, PDO::PARAM_LOB);

    $db->beginTransaction();
    $stmt->execute();
    $db->commit();
}
catch(PDOException $ex)
{
    $db->rollBack();
    print $ex->getMessage()."\n";
}

foreach($db->query('SELECT id, contenttype, lengthb(imagedata) FROM images') as $row)
{
    print $row[0].' '.$row[1].' '.$row[2]."\n";
}

• Displaying an image from a database

print "\n[Displaying an image from a database]\n";
try
{
    $stmt = $db->prepare("SELECT contenttype, imagedata FROM images WHERE id = ?");

    $id = 1;
    $stmt->execute(array($id));
    $stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
    $stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
    $stmt->fetch(PDO::FETCH_BOUND);

    print 'Content-Type: '.$type."\n";
    print 'size: '.file_put_contents($id.".png", $lob)."\n";
}
catch(PDOException $ex)
{
    print $ex->getMessage()."\n";
}