1: <?php
2:
3: namespace Himedia\PDOTools;
4:
5: use GAubry\Helpers\Helpers;
6: use PDO;
7: use Psr\Log\LoggerInterface;
8: use Psr\Log\NullLogger;
9:
10: /**
11: * Base class to build databases to execute tests.
12: *
13: * Copyright (c) 2014 HiMedia
14: * Licensed under the GNU Lesser General Public License v3 (LGPL version 3).
15: *
16: * @package Himedia\PDOTools
17: * @copyright 2014 HiMedia
18: * @license http://www.gnu.org/licenses/lgpl.html
19: */
20: abstract class DbTestCase extends \PHPUnit_Framework_TestCase
21: {
22:
23: /**
24: * List of DB's name already built (PHPUnit_Framework_TestCase are instantiated more than once).
25: * @var array
26: */
27: private static $aBuiltDbs = array();
28:
29: /**
30: * Backend PDO instance of built DB.
31: * @var PDO
32: */
33: protected $oBuiltDbPdo;
34:
35: /**
36: * PDO driver name, e.g. 'pgsql' or 'mysql'.
37: * @var string
38: */
39: protected $sPdoDriverName;
40:
41: /**
42: * Test DB hostname.
43: * @var string
44: */
45: protected $sDbHostname;
46:
47: /**
48: * Test DB port.
49: * @var int
50: */
51: protected $iDbPort;
52:
53: /**
54: * Test DB name.
55: * @var string
56: */
57: protected $sDbName;
58:
59: /**
60: * Test DB username.
61: * @var string
62: */
63: protected $sDbUser;
64:
65: /**
66: * Test DB password.
67: * @var string
68: */
69: protected $sDbPassword;
70:
71: /**
72: * Default PDO options on new instantiation.
73: * @var array
74: */
75: protected $aPdoOptions = array(
76: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
77: PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
78: PDO::ATTR_EMULATE_PREPARES => true,
79: PDO::ATTR_TIMEOUT => 5
80: );
81:
82: /**
83: * Max number of test DBs to keep.
84: * @var int
85: */
86: protected $iMaxDbToKeep;
87:
88: /**
89: * Build a database for tests and drop too old ones.
90: *
91: * <var>$aDsn</var> structure: <code>array(
92: * 'driver' => 'pgsql|mysql|…',
93: * 'hostname' => '…',
94: * 'port' => '…',
95: * 'dbname' => '…',
96: * 'username' => '…',
97: * 'password' => '…',
98: * )</code>
99: *
100: * @param array $aDsn Data source name of DB to build.
101: * @param array $aPdoOptions Driver-specific options for PDO connection.
102: * @param int $iMaxDbToKeep Max number of test databases to keep.
103: * @param string $sDbBuildFile SQL directives to build database (@see loadSqlBuildFile())
104: *
105: * @param string $sTCName PHPUnit_Framework_TestCase's name.
106: * @param array $aTCData PHPUnit_Framework_TestCase's data.
107: * @param string $sTCDataName PHPUnit_Framework_TestCase's dataName parameter.
108: * @param LoggerInterface $oLogger Optional PSR-3 logger.
109: */
110: public function __construct(
111: array $aDsn,
112: array $aPdoOptions,
113: $sDbBuildFile,
114: $iMaxDbToKeep = 3,
115: $sTCName = null,
116: array $aTCData = array(),
117: $sTCDataName = '',
118: LoggerInterface $oLogger = null
119: ) {
120: parent::__construct($sTCName, $aTCData, $sTCDataName);
121:
122: $this->sPdoDriverName = $aDsn['driver'];
123: $this->sDbHostname = $aDsn['hostname'];
124: $this->iDbPort = (int) $aDsn['port'];
125: $this->sDbName = $aDsn['dbname'];
126: $this->sDbUser = $aDsn['username'];
127: $this->sDbPassword = $aDsn['password'];
128: $this->aPdoOptions = array_replace($this->aPdoOptions, $aPdoOptions);
129:
130: // 1 is min to not drop current database:
131: $this->iMaxDbToKeep = max(1, (int)$iMaxDbToKeep);
132: $this->oLogger = $oLogger ?: new NullLogger();
133:
134: // PHPUnit_Framework_TestCase are instantiated more than once…
135: if (! in_array($this->sDbName, self::$aBuiltDbs)) {
136: try {
137: $this->loadSqlBuildFile($sDbBuildFile, $this->sDbUser, $this->sDbName);
138: } catch (\RuntimeException $oException) {
139: var_dump($oException);
140: $this->fail('DB\'s building failed! ' . $oException->getMessage());
141: }
142: }
143:
144: $this->oBuiltDbPdo = $this->getNewPdoInstance($this->sDbUser, $this->sDbName);
145:
146: if (! in_array($this->sDbName, self::$aBuiltDbs)) {
147: $this->dropOldDbs($this->sDbName);
148: self::$aBuiltDbs[$this->sDbName] = true;
149: }
150: }
151:
152: /**
153: * Returns a new PDO instance.
154: *
155: * @param string $sDbUser
156: * @param string $sDbName
157: * @return PDO
158: */
159: private function getNewPdoInstance($sDbUser, $sDbName)
160: {
161: $sDsn = "$this->sPdoDriverName:host=$this->sDbHostname;port=$this->iDbPort;"
162: . "dbname=$sDbName;user=$sDbUser;password=";
163: return new PDO($sDsn, null, null, $this->aPdoOptions);
164: }
165:
166: /**
167: * Load SQL directives from config/build file,
168: * describing how to create a fresh database, roles/users and schemas, with listing of fixtures to load.
169: *
170: * Config/build file is a PHP file returning a list of array('db-user', 'db-name', 'SQL commands or SQL filename').
171: * All filenames must match following regexp: <code>/(\.sql|\.gz)$/i</code>.
172: * Available/injected variables: $sDbUser, $sDbName.
173: *
174: * Build file example here: /doc/db-build-file-example.php
175: *
176: * @param string $sDbBuildFile SQL directives
177: * @param string $sDbUser Optional username injected into $sInitDbFile
178: * @param string $sDbName Optional DB name injected into $sInitDbFile
179: * @SuppressWarnings(PHPMD.UnusedFormalParameter)
180: */
181: protected function loadSqlBuildFile(
182: $sDbBuildFile,
183: /** @noinspection PhpUnusedParameterInspection */
184: $sDbUser = '',
185: /** @noinspection PhpUnusedParameterInspection */
186: $sDbName = ''
187: ) {
188: /** @noinspection PhpIncludeInspection */
189: $aSQLDirectives = include($sDbBuildFile);
190: $this->loadSqlBuildArray($aSQLDirectives);
191: }
192:
193: /**
194: * Executes specified SQL statement.
195: *
196: * @param string $sSql One or more SQL statements
197: * @param string $sDbUser
198: * @param string $sDbName
199: */
200: private function execSql($sSql, $sDbUser, $sDbName)
201: {
202: $this->getNewPdoInstance($sDbUser, $sDbName)
203: ->exec($sSql);
204: }
205:
206: /**
207: * Load SQL dump content for file exceeding 1 Mio.
208: *
209: * @param string $sFilePath Path to raw or gzipped SQL file.
210: * @param string $sDbUser
211: * @param string $sDbName
212: * @see loadSqlDumpFile()
213: */
214: private function loadBigSqlDumpFile($sFilePath, $sDbUser, $sDbName)
215: {
216: if ($this->sPdoDriverName == 'pgsql') {
217: $sCmd = "psql -v ON_ERROR_STOP=1 -h $this->sDbHostname -p $this->iDbPort -U $sDbUser $sDbName "
218: . "--file '$sFilePath'";
219: $this->oLogger->debug("[DEBUG] shell# $sCmd");
220: Helpers::exec($sCmd);
221:
222: } else {
223: $sErrMsg = "Driver type '$this->sPdoDriverName' not handled for loading SQL commands!";
224: throw new \UnexpectedValueException($sErrMsg, 1);
225: }
226: }
227:
228: /**
229: * Load SQL dump file, possibly gzipped (.gz).
230: *
231: * @param string $sFilePath Dump file
232: * @param string $sDbUser By default the user specified in constructor.
233: * @param string $sDbName By default the DB name specified in constructor.
234: */
235: protected function loadSqlDumpFile($sFilePath, $sDbUser = '', $sDbName = '')
236: {
237: $sDbUser = $sDbUser ?: $this->sDbUser;
238: $sDbName = $sDbName ?: $this->sDbName;
239:
240: // If gzipped dump file:
241: if (preg_match('/\.gz$/i', $sFilePath)) {
242: $sTmpFilePath = tempnam(sys_get_temp_dir(), 'dw-db-builder_');
243: $sCmd = "zcat -f '$sFilePath' > '$sTmpFilePath'";
244: $this->oLogger->debug("[DEBUG] shell# $sCmd");
245: try {
246: Helpers::exec($sCmd);
247: } catch (\RuntimeException $oException) {
248: if (file_exists($sTmpFilePath)) {
249: unlink($sTmpFilePath);
250: }
251: throw $oException;
252: }
253:
254: // recursive call with uncompressed file:
255: $this->loadSqlDumpFile($sTmpFilePath, $sDbUser, $sDbName);
256:
257: $sCmd = "rm -f '$sTmpFilePath'";
258: $this->oLogger->debug("[DEBUG] shell# $sCmd");
259: Helpers::exec($sCmd);
260:
261: // If <1 Mio uncompressed dump file:
262: } elseif (filesize($sFilePath) < 1024*1024) {
263: $sSql = file_get_contents($sFilePath);
264: $this->execSql($sSql, $sDbUser, $sDbName);
265:
266: // If ≥1 Mio uncompressed dump file:
267: } else {
268: $this->loadBigSqlDumpFile($sFilePath, $sDbUser, $sDbName);
269: }
270: }
271:
272: /**
273: * Load SQL directives in specified array.
274: *
275: * SQL directives are a list of array('db-user', 'db-name', 'SQL commands or SQL filename').
276: * All filenames must match following regexp: <pre>/(\.sql|\.gz)$/i</pre>.
277: *
278: * Example here: /doc/db-build-file-example.php
279: *
280: * @param array $aSQLDirectives SQL directives to build database
281: * @see loadSqlBuildFile()
282: */
283: protected function loadSqlBuildArray(array $aSQLDirectives)
284: {
285: foreach ($aSQLDirectives as $aStatement) {
286: list($sDbUser, $sDbName, $sCommands) = $aStatement;
287: if (preg_match('/(\.sql|\.gz)$/i', $sCommands) === 1) {
288: $this->loadSqlDumpFile($sCommands, $sDbUser, $sDbName);
289: } else {
290: if (substr($sCommands, -1) != ';') {
291: $sCommands .= ';';
292: }
293: $this->execSql($sCommands, $sDbUser, $sDbName);
294: }
295: }
296: }
297:
298: /**
299: * List all test DBs created with following name pattern: <code>/^{$sPrefixDb}_[0-9]+$/</code>.
300: *
301: * @param string $sPrefixDb
302: * @return array list of all test DBs
303: */
304: private function getAllDb($sPrefixDb)
305: {
306: if ($this->sPdoDriverName == 'pgsql') {
307: $sQuery = "
308: SELECT datname AS dbname FROM pg_database
309: WHERE datname ~ '^{$sPrefixDb}_[0-9]+$' ORDER BY datname ASC";
310: } else {
311: $sErrMsg = "Driver type '$this->sPdoDriverName' not handled for listing all databases!";
312: throw new \UnexpectedValueException($sErrMsg, 1);
313: }
314: $aAllDb = $this->pdoFetchAll($sQuery);
315: return $aAllDb;
316: }
317:
318: /**
319: * Fetch all rows.
320: *
321: * @param string $sQuery
322: * @param array $aValues Values to bind to the SQL statement.
323: * @return array an associative array
324: */
325: protected function pdoFetchAll($sQuery, array $aValues = array())
326: {
327: $oPdoStatement = $this->oBuiltDbPdo->prepare($sQuery);
328: $oPdoStatement->execute($aValues);
329: return $oPdoStatement->fetchAll(PDO::FETCH_ASSOC);
330: }
331:
332: /**
333: * Drop old test DBs.
334: *
335: * @param string $sCurrentDbName Current DB name, not to remove.
336: */
337: private function dropOldDbs($sCurrentDbName)
338: {
339: if (preg_match('/^(.*)_[0-9]+$/i', $sCurrentDbName, $aMatches) === 1) {
340: $this->oLogger->info('Searching too old databases…');
341: $aAllDb = $this->getAllDb($aMatches[1]);
342: if (count($aAllDb) > $this->iMaxDbToKeep) {
343: $aTooOldDbs = array_slice($aAllDb, 0, -$this->iMaxDbToKeep);
344: foreach ($aTooOldDbs as $aDb) {
345: $sTooOldDb = $aDb['dbname'];
346: $this->oLogger->info(" Dropping '$sTooOldDb' database…");
347: $this->oBuiltDbPdo->exec("DROP DATABASE IF EXISTS $sTooOldDb;");
348: }
349: $this->oLogger->info(' Done.');
350: } else {
351: $this->oLogger->info(' No DB to delete.');
352: }
353: }
354: }
355:
356: /**
357: * Asserts that SQL query doesn't return any rows.
358: *
359: * @param string $sQuery
360: * @throws \PHPUnit_Framework_AssertionFailedError
361: */
362: protected function assertQueryReturnsNoRows($sQuery)
363: {
364: $oPdoStatement = $this->oBuiltDbPdo->query($sQuery);
365: $aRow = $oPdoStatement->fetch(PDO::FETCH_ASSOC);
366: $this->assertTrue($aRow === false || $aRow === null);
367: }
368:
369: /**
370: * Asserts that SQL query result is equal to CSV file content.
371: *
372: * In CSV files, following field's values are converted:
373: * <ul>
374: * <li>'∅' ⇒ null</li>
375: * <li>'t' ⇒ true</li>
376: * <li>'f' ⇒ false</li>
377: * </ul>
378: *
379: * <var>$sCsvPath</var> sample content:
380: * <pre>
381: * id,name,description
382: * 250,FR,France
383: * 826,GB,United Kingdom
384: * 840,US,United States
385: * </pre>
386: *
387: * @param string $sQuery
388: * @param string $sCsvPath CSV file whose first line contains headers
389: * @param string $sDelimiter CSV field delimiter
390: * @param string $sEnclosure CSV field enclosure
391: * @throws \PHPUnit_Framework_AssertionFailedError
392: */
393: protected function assertQueryResultEqualsCsv($sQuery, $sCsvPath, $sDelimiter = ',', $sEnclosure = '"')
394: {
395: $sResultCsv = $this->convertQuery2Csv($sQuery, $sDelimiter, $sEnclosure);
396: $sExpectedCsv = trim(file_get_contents($sCsvPath));
397: $this->assertSame($sExpectedCsv, $sResultCsv);
398: }
399:
400: /**
401: * Converts SQL query result to CSV string.
402: * First CSV line contains headers.
403: *
404: * Returned CSV example:
405: * <pre>
406: * id,name,description
407: * 250,FR,France
408: * 826,GB,United Kingdom
409: * 840,US,United States
410: * </pre>
411: *
412: * @param string $sQuery SQL query, typically SELECT… FROM…
413: * @param string $sDelimiter CSV field delimiter
414: * @param string $sEnclosure CSV field enclosure
415: * @return string
416: */
417: protected function convertQuery2Csv($sQuery, $sDelimiter = ',', $sEnclosure = '"')
418: {
419: $aRows = $this->pdoFetchAll($sQuery);
420: return Tools::exportToCSV($aRows, '', $sDelimiter, $sEnclosure);
421: }
422:
423: /**
424: * Returns a mocked \PDOStatement instance according to specified query,
425: * whose fetch method returns either a user callback
426: * or a callback consuming line per line a user specified CSV file.
427: *
428: * Allows to mock a PDO instance for several queries.
429: * Each query should be a key of $aData array parameter. Values are callbacks or CSV filenames.
430: *
431: * All queries are internally normalized to simplify matching.
432: *
433: * In CSV files, following field's values are converted:
434: * <ul>
435: * <li>'∅' ⇒ null</li>
436: * <li>'t' ⇒ true</li>
437: * <li>'f' ⇒ false</li>
438: * </ul>
439: *
440: * Example usage in a test method:
441: * <code>
442: * $oMockPDO = $this->getMock('Himedia\PDOTools\Mocks\PDO', array('query'));
443: * $that = $this;
444: * $oPDOMock->expects($this->any())->method('query')->will(
445: * $this->returnCallback(
446: * function ($sQuery) use ($that) {
447: * return $that->getPdoStmtMock(
448: * $sQuery,
449: * array(
450: * 'SELECT … FROM A' => '/path/to/csv',
451: * 'SELECT … FROM B' => function () {
452: * static $i = 0;
453: * return ++$i > 10 ? false : array('id' => $i, 'name' => md5(rand()));
454: * }
455: * )
456: * );
457: * }
458: * )
459: * );
460: * </code>
461: *
462: * @param string $sQuery SQL query behind \PDOStatement instance
463: * @param array $aData Associative array describing for each PDO query which callback to execute
464: * @return \PHPUnit_Framework_MockObject_MockObject|\PDOStatement
465: * @see Tools::normalizeQuery()
466: */
467: public function getPdoStmtMock($sQuery, array $aData)
468: {
469: // Normalize queries (keys of $aData):
470: foreach ($aData as $sRawQuery => $mValue) {
471: $sNormalizedQuery = Tools::normalizeQuery($sRawQuery);
472: unset($aData[$sRawQuery]);
473: $aData[$sNormalizedQuery] = $mValue;
474: }
475: $sNormalizedQuery = Tools::normalizeQuery($sQuery);
476:
477: if (! isset($aData[$sNormalizedQuery])) {
478: throw new \RuntimeException("Query not handled: '$sNormalizedQuery'!");
479:
480: } elseif (is_callable($aData[$sNormalizedQuery])) {
481: $callback = $aData[$sNormalizedQuery];
482:
483: } elseif (file_exists($aData[$sNormalizedQuery])) {
484: $aCsv = array_filter(file($aData[$sNormalizedQuery]));
485: $callback = function () use ($aCsv) {
486: static $idx = 0, $aHeaders, $iCount;
487: if ($idx == 0) {
488: $aHeaders = str_getcsv($aCsv[0], ',', '"', "\\");
489: $iCount = count($aCsv);
490: }
491: if (++$idx < $iCount) {
492: $aRow = array_combine($aHeaders, str_getcsv($aCsv[$idx], ',', '"', "\\"));
493: foreach ($aRow as $sKey => $sValue) {
494: if ($sValue == '∅') {
495: $aRow[$sKey] = null;
496: } elseif ($sValue == 't') {
497: $aRow[$sKey] = true;
498: } elseif ($sValue == 'f') {
499: $aRow[$sKey] = false;
500: }
501: }
502: return $aRow;
503: } else {
504: return false;
505: }
506: };
507:
508: } else {
509: $sMsg = "Value of key '$sNormalizedQuery' misformed: '{$aData[$sNormalizedQuery]}'.";
510: $callback = function () use ($sMsg) {
511: throw new \RuntimeException($sMsg);
512: };
513: }
514:
515: // Doesn't work if full chain pattern:
516: $oPdoStmt = $this->getMock('\PDOStatement');
517: $oPdoStmt
518: ->expects($this->any())->method('fetch')
519: ->will($this->returnCallback($callback));
520: return $oPdoStmt;
521: }
522: }
523: