1: <?php
2:
3: namespace Himedia\Padocc\DB;
4:
5: use GAubry\Helpers\Helpers;
6: use PDO;
7:
8: class PDOAdapter implements DBAdapterInterface
9: {
10: /**
11: * List of backend \PDO instances indexed by DSN.
12: * @var array
13: */
14: private static $aPDOInstances = array();
15:
16: /**
17: * Backend PDO instance.
18: * @var PDO
19: * @see connect()
20: * @see setPDOInstance()
21: */
22: private $oPDO;
23:
24: /**
25: * Database source name: array(
26: * 'driver' => (string) e.g. 'pdo_mysql', 'pdo_pgsql',
27: * 'hostname' => (string),
28: * 'port' => (int),
29: * 'db_name' => (string),
30: * 'username' => (string),
31: * 'password' => (string)
32: * );
33: * @var array
34: */
35: private $aDSN;
36:
37: /**
38: * List of instances indexed by DSN.
39: * @var PDOAdapter[]
40: */
41: private static $aInstances = array();
42:
43: /**
44: * Return a PDOAdapter instance.
45: * Lazy connection to backend PDO instance.
46: *
47: * @param array $aDSN Database source name: array(
48: * 'driver' => (string) e.g. 'pdo_mysql' ou 'pdo_pgsql',
49: * 'hostname' => (string),
50: * 'port' => (int),
51: * 'db_name' => (string),
52: * 'username' => (string),
53: * 'password' => (string)
54: * );
55: * @param string $sQueriesLogPath path where to log all queries by DB
56: * @return PDOAdapter
57: */
58: public static function getInstance (array $aDSN, $sQueriesLogPath = '')
59: {
60: $sKey = implode('|', $aDSN);
61: if (! isset(self::$aInstances[$sKey])) {
62: self::$aInstances[$sKey] = new PDOAdapter($aDSN, $sQueriesLogPath);
63: }
64: return self::$aInstances[$sKey];
65: }
66:
67: /**
68: * Constructor.
69: *
70: * @param array $aDSN Database source name: array(
71: * 'driver' => (string) e.g. 'pdo_mysql' ou 'pdo_pgsql',
72: * 'hostname' => (string),
73: * 'port' => (int),
74: * 'db_name' => (string),
75: * 'username' => (string),
76: * 'password' => (string)
77: * );
78: */
79: public function __construct(array $aDSN)
80: {
81: $this->oPDO = null;
82: $this->aDSN = $aDSN;
83: }
84:
85: /**
86: * Set backend PDO instance.
87: *
88: * @param PDO $oPDO PDO instance
89: * @throws \BadMethodCallException iff backend PDO instance is already set.
90: */
91: public function setPDOInstance (PDO $oPDO)
92: {
93: if ($this->oPDO === null) {
94: $this->oPDO = $oPDO;
95: } else {
96: throw new \BadMethodCallException('PDO instance already set!');
97: }
98: }
99:
100: /**
101: * Establishes the connection with the database and set backend PDO instance.
102: */
103: private function connect ()
104: {
105: if ($this->oPDO === null) {
106: $aDSN = $this->aDSN;
107: $sKey = implode('|', $aDSN);
108: if (! isset(self::$aPDOInstances[$sKey])) {
109: if (in_array($aDSN['driver'], array('pdo_mysql', 'pdo_pgsql'))) {
110: $sDSN = sprintf(
111: '%s:host=%s;port=%s;dbname=%s',
112: substr($aDSN['driver'], 4),
113: $aDSN['hostname'],
114: $aDSN['port'],
115: $aDSN['db_name']
116: );
117: try {
118: $oPDO = new PDO(
119: $sDSN,
120: $aDSN['username'],
121: $aDSN['password'],
122: array(
123: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
124: PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
125: PDO::ATTR_EMULATE_PREPARES => true,
126: PDO::ATTR_TIMEOUT => 5
127: )
128: );
129: } catch (\PDOException $oException) {
130: $sMsg = $oException->getMessage() . ". DSN was: '$sDSN'.";
131: throw new \RuntimeException($sMsg, 1, $oException);
132: }
133: $oPDO->query("SET NAMES 'UTF8';");
134:
135: // Set UTC timezone:
136: if ($aDSN['driver'] == 'pdo_mysql') {
137: $oPDO->query("SET time_zone = '+00:00';");
138: $oPDO->query("SET SESSION time_zone = '+00:00';");
139: }
140:
141: // Set application_name if Postgresql AND v9+:
142: if ($aDSN['driver'] == 'pdo_pgsql') {
143: $oPDOStatement = $oPDO->query("SHOW server_version_num;");
144: $iPgVersion = (int)$oPDOStatement->fetchColumn(0);
145: if ($iPgVersion >= 90000) {
146: $oPDO->query("SET application_name TO 'Padocc';");
147: }
148: }
149:
150: self::$aPDOInstances[$sKey] = $oPDO;
151: } else {
152: throw new \UnexpectedValueException("Unknown driver type '{$aDSN['driver']}'", 1);
153: }
154: }
155: $this->oPDO = self::$aPDOInstances[$sKey];
156: }
157: }
158:
159: /**
160: * Returns content of specified column of the first row of query's result.
161: *
162: * @param string $sQuery Query to execute.
163: * @param int $iColumnNumber 0-indexed number of the column you wish to retrieve from the row.
164: * If no value is supplied, PDOStatement::fetchColumn() fetches the first column.
165: * @return string content of specified column of the first row of query's result
166: */
167: public function fetchColumn ($sQuery, $iColumnNumber = 0)
168: {
169: $oPDOStatement = $this->query($sQuery);
170: $sResult = $oPDOStatement->fetchColumn($iColumnNumber);
171: return $sResult;
172: }
173:
174: /**
175: * Fetches the first row of of the specified SQL statement.
176: * The row is an array indexed by column name.
177: * If a result set row contains multiple columns with the same name,
178: * then returns only a single value per column name.
179: *
180: * @param string $sQuery Statement to execute.
181: * @return array returns the first row of of the specified SQL statement.
182: */
183: public function fetchRow ($sQuery)
184: {
185: $oPDOStatement = $this->query($sQuery);
186: $aRow = $oPDOStatement->fetch(PDO::FETCH_ASSOC);
187: return $aRow;
188: }
189:
190: /**
191: * Returns an array containing all of the result set rows of the specified SQL statement.
192: * Each row is an array indexed by column name.
193: * If a result set row contains multiple columns with the same name,
194: * then returns only a single value per column name.
195: *
196: * @param string $sQuery Statement to execute.
197: * @return array returns an array containing
198: * all of the remaining rows in the result set. The array represents each
199: * row as an array of column values.
200: */
201: public function fetchAll ($sQuery)
202: {
203: $oPDOStatement = $this->query($sQuery);
204: $aRows = $oPDOStatement->fetchAll(PDO::FETCH_ASSOC);
205: return $aRows;
206: }
207:
208: /**
209: * Executes the specified SQL statement, returning a result set as a PDOStatement object.
210: *
211: * @param string $sQuery Statement to execute.
212: * @return \PDOStatement a PDOStatement object
213: * @throws \PDOException on error
214: */
215: public function query ($sQuery)
216: {
217: if ($this->oPDO === null) {
218: $this->connect();
219: }
220:
221: try {
222: $oPDOStatement = $this->oPDO->query($sQuery);
223: } catch (\PDOException $oException) {
224: $sMsg = $oException->getMessage() . " Query was: $sQuery.";
225: throw new \PDOException($sMsg, (int)$oException->getCode(), $oException);
226: }
227: return $oPDOStatement;
228: }
229:
230: /**
231: * Execute an SQL statement and return the number of affected rows.
232: *
233: * @param string $sQuery The SQL statement to prepare and execute.
234: * @throws \PDOException on error
235: * @return int the number of rows that were modified
236: * or deleted by the SQL statement. If no rows were affected returns 0.
237: */
238: public function exec ($sQuery)
239: {
240: if ($this->oPDO === null) {
241: $this->connect();
242: }
243:
244: try {
245: $iNbRows = $this->oPDO->exec($sQuery);
246: } catch (\PDOException $oException) {
247: $sMsg = $oException->getMessage() . " Query was: $sQuery.";
248: throw new \PDOException($sMsg, (int)$oException->getCode(), $oException);
249: }
250: return $iNbRows;
251: }
252:
253: /**
254: * Prepares a statement for execution and returns a statement object.
255: *
256: * Emulated prepared statements does not communicate with the database server
257: * so prepare() does not check the statement.
258: *
259: * @param string $sQuery SQL statement
260: * @throws \PDOException if error
261: * @return \PDOStatement a PDOStatement object.
262: */
263: public function prepare ($sQuery)
264: {
265: if ($this->oPDO === null) {
266: $this->connect();
267: }
268:
269: return $this->oPDO->prepare($sQuery);
270: }
271:
272: public function executePreparedStatement (\PDOStatement $oStatement, array $aValues)
273: {
274: try {
275: $bResult = $oStatement->execute($aValues);
276: } catch (\PDOException $oException) {
277: $sMsg = $oException->getMessage()
278: . " Query was: $oStatement->queryString. Values was: " . print_r($aValues, true);
279: throw new \PDOException($sMsg, (int)$oException->getCode(), $oException);
280: }
281: return $bResult;
282: }
283:
284: /**
285: * Returns the ID of the last inserted row or sequence value.
286: *
287: * @param string $sSequenceName [optional] Name of the sequence object from which the ID should be returned.
288: * @return string If a sequence name was not specified returns a
289: * string representing the row ID of the last row that was inserted into
290: * the database, else returns a string representing the last value retrieved from the specified sequence
291: * object.
292: */
293: public function lastInsertId ($sSequenceName = null)
294: {
295: if ($this->oPDO === null) {
296: $this->connect();
297: }
298:
299: return $this->oPDO->lastInsertId($sSequenceName);
300: }
301:
302: /**
303: * Quotes a string for use in a query.
304: *
305: * @param string $sValue The string to be quoted.
306: * @param int $iType [optional] Provides a data type hint for drivers that have alternate quoting styles.
307: * @return string a quoted string that is theoretically safe to pass into an
308: * SQL statement.
309: *
310: * Returns <b>FALSE</b> if the driver does not support quoting in
311: * this way.
312: */
313: public function quote ($sValue, $iType = \PDO::PARAM_STR)
314: {
315: if ($this->oPDO === null) {
316: $this->connect();
317: }
318:
319: return ($this->oPDO->quote($sValue, $iType) ?: $sValue);
320: }
321:
322: /**
323: * Initiates a transaction.
324: *
325: * @return bool TRUE on success or FALSE on failure.
326: */
327: public function beginTransaction ()
328: {
329: if ($this->oPDO === null) {
330: $this->connect();
331: }
332:
333: return $this->oPDO->beginTransaction();
334: }
335:
336: /**
337: * Rolls back a transaction.
338: *
339: * @return bool TRUE on success or FALSE on failure.
340: */
341: public function rollBack ()
342: {
343: if ($this->oPDO === null) {
344: $this->connect();
345: }
346:
347: return $this->oPDO->rollBack();
348: }
349:
350: /**
351: * Commits a transaction.
352: *
353: * @return bool TRUE on success or FALSE on failure.
354: */
355: public function commit ()
356: {
357: if ($this->oPDO === null) {
358: $this->connect();
359: }
360:
361: return $this->oPDO->commit();
362: }
363:
364: public function formatValue ($mValue)
365: {
366: if ($mValue === null) {
367: return 'NULL';
368: } elseif ($mValue === true) {
369: return "'t'";
370: } elseif ($mValue === false) {
371: return "'f'";
372: } else {
373: if ($this->oPDO === null) {
374: $this->connect();
375: }
376: return $this->oPDO->quote(Helpers::utf8Encode($mValue));
377: }
378: }
379: }
380: