| Code Coverage
| Lines | Functions and Methods | Classes and Traits
Total | 57.81% covered (warning) | 57.81% | 296 / 512 | 31.25% covered (danger) | 31.25% | 10 / 32 | CRAP | 0.00% covered (danger) | 0.00% | 0 / 1
SeedDMS_Core_DatabaseAccess | 57.81% covered (warning) | 57.81% | 296 / 512 | 31.25% covered (danger) | 31.25% | 10 / 32 | 3328.72 | 0.00% covered (danger) | 0.00% | 0 / 1
TableList | 62.50% covered (warning) | 62.50% | 10 / 16 | 0.00% covered (danger) | 0.00% | 0 / 1 | 7.90 |
hasTable | 53.33% covered (warning) | 53.33% | 8 / 15 | 0.00% covered (danger) | 0.00% | 0 / 1 | 9.66 |
ViewList | 62.50% covered (warning) | 62.50% | 10 / 16 | 0.00% covered (danger) | 0.00% | 0 / 1 | 7.90 |
__construct | 82.61% covered (warning) | 82.61% | 19 / 23 | 0.00% covered (danger) | 0.00% | 0 / 1 | 4.08 |
getDriver | 100.00% covered (success) | 100.00% | 1 / 1 | 100.00% covered (success) | 100.00% | 1 / 1 | 1 |
useViews | 100.00% covered (success) | 100.00% | 1 / 1 | 100.00% covered (success) | 100.00% | 1 / 1 | 1 |
__destruct | 33.33% covered (danger) | 33.33% | 1 / 3 | 0.00% covered (danger) | 0.00% | 0 / 1 | 5.67 |
setLogFp | 100.00% covered (success) | 100.00% | 1 / 1 | 100.00% covered (success) | 100.00% | 1 / 1 | 1 |
connect | 59.38% covered (warning) | 59.38% | 19 / 32 | 0.00% covered (danger) | 0.00% | 0 / 1 | 27.14 |
ensureConnected | 100.00% covered (success) | 100.00% | 2 / 2 | 100.00% covered (success) | 100.00% | 1 / 1 | 2 |
qstr | 100.00% covered (success) | 100.00% | 1 / 1 | 100.00% covered (success) | 100.00% | 1 / 1 | 2 |
rbt | 100.00% covered (success) | 100.00% | 1 / 1 | 100.00% covered (success) | 100.00% | 1 / 1 | 1 |
concat | 55.56% covered (warning) | 55.56% | 5 / 9 | 0.00% covered (danger) | 0.00% | 0 / 1 | 5.40 |
getResultArray | 76.92% covered (warning) | 76.92% | 10 / 13 | 0.00% covered (danger) | 0.00% | 0 / 1 | 7.60 |
getResult | 63.64% covered (warning) | 63.64% | 7 / 11 | 0.00% covered (danger) | 0.00% | 0 / 1 | 9.36 |
startTransaction | 80.00% covered (warning) | 80.00% | 4 / 5 | 0.00% covered (danger) | 0.00% | 0 / 1 | 4.13 |
rollbackTransaction | 80.00% covered (warning) | 80.00% | 4 / 5 | 0.00% covered (danger) | 0.00% | 0 / 1 | 4.13 |
commitTransaction | 80.00% covered (warning) | 80.00% | 4 / 5 | 0.00% covered (danger) | 0.00% | 0 / 1 | 4.13 |
inTransaction | 0.00% covered (danger) | 0.00% | 0 / 1 | 0.00% covered (danger) | 0.00% | 0 / 1 | 2 |
getInsertID | 66.67% covered (warning) | 66.67% | 2 / 3 | 0.00% covered (danger) | 0.00% | 0 / 1 | 2.15 |
getErrorMsg | 100.00% covered (success) | 100.00% | 2 / 2 | 100.00% covered (success) | 100.00% | 1 / 1 | 1 |
getErrorNo | 100.00% covered (success) | 100.00% | 1 / 1 | 100.00% covered (success) | 100.00% | 1 / 1 | 1 |
__createTemporaryTable | 50.35% covered (warning) | 50.35% | 72 / 143 | 0.00% covered (danger) | 0.00% | 0 / 1 | 246.75 |
__dropTemporaryTable | 61.11% covered (warning) | 61.11% | 11 / 18 | 0.00% covered (danger) | 0.00% | 0 / 1 | 13.76 |
__createView | 50.83% covered (warning) | 50.83% | 61 / 120 | 0.00% covered (danger) | 0.00% | 0 / 1 | 240.79 |
createTemporaryTable | 100.00% covered (success) | 100.00% | 3 / 3 | 100.00% covered (success) | 100.00% | 1 / 1 | 2 |
dropTemporaryTable | 66.67% covered (warning) | 66.67% | 2 / 3 | 0.00% covered (danger) | 0.00% | 0 / 1 | 2.15 |
getDateExtract | 36.36% covered (danger) | 36.36% | 4 / 11 | 0.00% covered (danger) | 0.00% | 0 / 1 | 15.28 |
getCurrentDatetime | 33.33% covered (danger) | 33.33% | 5 / 15 | 0.00% covered (danger) | 0.00% | 0 / 1 | 21.52 |
getCurrentTimestamp | 44.44% covered (danger) | 44.44% | 4 / 9 | 0.00% covered (danger) | 0.00% | 0 / 1 | 6.74 |
castToText | 60.00% covered (warning) | 60.00% | 3 / 5 | 0.00% covered (danger) | 0.00% | 0 / 1 | 2.26 |
createDump | 100.00% covered (success) | 100.00% | 18 / 18 | 100.00% covered (success) | 100.00% | 1 / 1 | 8 |

1 | <?php
2 | declare(strict_types=1);
3 |
4 | /**
5 | * Implementation of database access using PDO
6 | *
7 | * @category DMS
8 | * @package SeedDMS_Core
9 | * @license GPL 2
10 | * @version @version@
11 | * @author Uwe Steinmann <uwe@steinmann.cx>
12 | * @copyright Copyright (C) 2012 Uwe Steinmann
13 | * @version Release: @package_version@
14 | */
15 | /** @noinspection PhpUndefinedClassInspection */
16 |
17 | /**
18 | * Class to represent the database access for the document management
19 | * This class uses PDO for the actual database access.
20 | *
21 | * @category DMS
22 | * @package SeedDMS_Core
23 | * @author Uwe Steinmann <uwe@steinmann.cx>
24 | * @copyright Copyright (C) 2012 Uwe Steinmann
25 | * @version Release: @package_version@
26 | */
27 | class SeedDMS_Core_DatabaseAccess {
28 | /**
29 | * @var boolean set to true for debug mode
30 | */
31 | public $_debug;
32 |
33 | /**
34 | * @var string name of database driver (mysql or sqlite)
35 | */
36 | protected $_driver;
37 |
38 | /**
39 | * @var string name of hostname
40 | */
41 | protected $_hostname;
42 |
43 | /**
44 | * @var int port number of database
45 | */
46 | protected $_port;
47 |
48 | /**
49 | * @var string name of database
50 | */
51 | protected $_database;
52 |
53 | /**
54 | * @var string name of database user
55 | */
56 | protected $_user;
57 |
58 | /**
59 | * @var string password of database user
60 | */
61 | protected $_passw;
62 |
63 | /**
64 | * @var object internal database connection
65 | */
66 | private $_conn;
67 |
68 | /**
69 | * @var boolean set to true if connection to database is established
70 | */
71 | private $_connected;
72 |
73 | /**
74 | * @var boolean set to true if temp. table for tree view has been created
75 | */
76 | private $_ttreviewid;
77 |
78 | /**
79 | * @var boolean set to true if temp. table for approvals has been created
80 | */
81 | private $_ttapproveid;
82 |
83 | /**
84 | * @var boolean set to true if temp. table for doc status has been created
85 | */
86 | private $_ttstatid;
87 |
88 | /**
89 | * @var boolean set to true if temp. table for doc content has been created
90 | */
91 | private $_ttcontentid;
92 |
93 | /**
94 | * @var boolean set to true if in a database transaction
95 | */
96 | private $_intransaction;
97 |
98 | /**
99 | * @var string set a valid file name for logging all sql queries
100 | */
101 | private $_logfile;
102 |
103 | /**
104 | * @var resource file pointer of log file
105 | */
106 | private $_logfp;
107 |
108 | /**
109 | * @var boolean set to true if views instead of temp. tables shall be used
110 | */
111 | private $_useviews;
112 |
113 | /**
114 | * Return list of all database tables
115 | *
116 | * This function is used to retrieve a list of database tables for backup
117 | *
118 | * @return string[]|bool list of table names
119 | */
120 | public function TableList() { /* {{{ */
121 | switch ($this->_driver) {
122 | case 'mysql':
123 | $sql = "SELECT `TABLE_NAME` AS `name` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA`='".$this->_database."' AND `TABLE_TYPE`='BASE TABLE'";
124 | break;
125 | case 'sqlite':
126 | $sql = "SELECT tbl_name AS name FROM sqlite_master WHERE type='table'";
127 | break;
128 | case 'pgsql':
129 | $sql = "select tablename as name from pg_catalog.pg_tables where schemaname='public'";
130 | break;
131 | default:
132 | return false;
133 | }
134 | $arr = $this->getResultArray($sql);
135 | $res = array();
136 | foreach ($arr as $tmp)
137 | $res[] = $tmp['name'];
138 | return $res;
139 | } /* }}} */
140 |
141 | /**
142 | * Check if database has a table
143 | *
144 | * This function will check if the database has a table with the given table name
145 | *
146 | * @return bool true if table exists, otherwise false
147 | */
148 | public function hasTable($name) { /* {{{ */
149 | switch ($this->_driver) {
150 | case 'mysql':
151 | $sql = "SELECT `TABLE_NAME` AS `name` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA`='".$this->_database."' AND `TABLE_TYPE`='BASE TABLE' AND `TABLE_NAME`=".$this->qstr($name);
152 | break;
153 | case 'sqlite':
154 | $sql = "SELECT tbl_name AS name FROM sqlite_master WHERE type='table' AND `tbl_name`=".$this->qstr($name);
155 | break;
156 | case 'pgsql':
157 | $sql = "SELECT tablename AS name FROM pg_catalog.pg_tables WHERE schemaname='public' AND tablename=".$this->qstr($name);
158 | break;
159 | default:
160 | return false;
161 | }
162 | $arr = $this->getResultArray($sql);
163 | if ($arr)
164 | return true;
165 | return false;
166 | } /* }}} */
167 |
168 | /**
169 | * Return list of all database views
170 | *
171 | * This function is used to retrieve a list of database views
172 | *
173 | * @return array list of view names
174 | */
175 | public function ViewList() { /* {{{ */
176 | switch ($this->_driver) {
177 | case 'mysql':
178 | $sql = "select TABLE_NAME as name from information_schema.views where TABLE_SCHEMA='".$this->_database."'";
179 | break;
180 | case 'sqlite':
181 | $sql = "select tbl_name as name from sqlite_master where type='view'";
182 | break;
183 | case 'pgsql':
184 | $sql = "select viewname as name from pg_catalog.pg_views where schemaname='public'";
185 | break;
186 | default:
187 | return false;
188 | }
189 | $arr = $this->getResultArray($sql);
190 | $res = array();
191 | foreach ($arr as $tmp)
192 | $res[] = $tmp['name'];
193 | return $res;
194 | } /* }}} */
195 |
196 | /**
197 | * Constructor of SeedDMS_Core_DatabaseAccess
198 | *
199 | * Sets all database parameters but does not connect.
200 | *
201 | * @param string $driver the database type e.g. mysql, sqlite
202 | * @param string $hostname host of database server
203 | * @param string $user name of user having access to database
204 | * @param string $passw password of user
205 | * @param bool|string $database name of database
206 | */
207 | public function __construct($driver, $hostname, $user, $passw, $database = false) { /* {{{ */
208 | $this->_driver = $driver;
209 | $tmp = explode(":", $hostname);
210 | $this->_hostname = $tmp[0];
211 | $this->_port = null;
212 | if (!empty($tmp[1]))
213 | $this->_port = $tmp[1];
214 | $this->_database = $database;
215 | $this->_user = $user;
216 | $this->_passw = $passw;
217 | $this->_connected = false;
218 | $this->_intransaction = 0;
219 | $this->_logfile = '';
220 | if ($this->_logfile) {
221 | $this->_logfp = fopen($this->_logfile, 'a+');
222 | if ($this->_logfp)
223 | fwrite($this->_logfp, microtime(true)." BEGIN ".$_SERVER['REQUEST_URI']." ------------------------------------------\n");
224 | } else
225 | $this->_logfp = null;
226 | // $tt*****id is a hack to ensure that we do not try to create the
227 | // temporary table twice during a single connection. Can be fixed by
228 | // using Views (MySQL 5.0 onward) instead of temporary tables.
229 | // CREATE ... IF NOT EXISTS cannot be used because it has the
230 | // unpleasant side-effect of performing the insert again even if the
231 | // table already exists.
232 | //
233 | // See createTemporaryTable() method for implementation.
234 | $this->_ttreviewid = false;
235 | $this->_ttapproveid = false;
236 | $this->_ttstatid = false;
237 | $this->_ttcontentid = false;
238 | $this->_useviews = false; // turn off views, because they are much slower then temp. tables. They also break the transaction management, because dropping a view will commit the current transaction.
239 | $this->_debug = false;
240 | } /* }}} */
241 |
242 | /**
243 | * Return driver
244 | *
245 | * @return string name of driver as set in constructor
246 | */
247 | public function getDriver() { /* {{{ */
248 | return $this->_driver;
249 | } /* }}} */
250 |
251 | /**
252 | * Turn on views instead of temp. tables
253 | *
254 | * @param bool $onoff turn use of views instead of temp. table on/off
255 | */
256 | public function useViews($onoff) { /* {{{ */
257 | $this->_useviews = $onoff;
258 | } /* }}} */
259 |
260 | /**
261 | * Destructor of SeedDMS_Core_DatabaseAccess
262 | */
263 | public function __destruct() { /* {{{ */
264 | if ($this->_logfile && $this->_logfp) {
265 | fwrite($this->_logfp, microtime(true)." END --------------------------------------------\n");
266 | fclose($this->_logfp);
267 | }
268 | } /* }}} */
269 |
270 | /**
271 | * Set the file pointer to a log file
272 | *
273 | * Once it is set, all queries will be logged into this file
274 | */
275 | public function setLogFp($fp) { /* {{{ */
276 | $this->_logfp = $fp;
277 | } /* }}} */
278 |
279 | /**
280 | * Connect to database
281 | *
282 | * @return boolean true if connection could be established, otherwise false
283 | */
284 | public function connect() { /* {{{ */
285 | switch ($this->_driver) {
286 | case 'mysql':
287 | case 'mysqli':
288 | case 'mysqlnd':
289 | case 'pgsql':
290 | $dsn = $this->_driver.":dbname=".$this->_database.";host=".$this->_hostname;
291 | if ($this->_port)
292 | $dsn .= ";port=".$this->_port;
293 | break;
294 | case 'sqlite':
295 | $dsn = $this->_driver.":".$this->_database;
296 | break;
297 | }
298 | try {
299 | /** @noinspection PhpUndefinedVariableInspection */
300 | $this->_conn = new PDO($dsn, $this->_user, $this->_passw);
301 | if (!$this->_conn)
302 | return false;
303 | /* Prevent PDO from throwing an exception because the code currently
304 | * cannot handle it. PDO::ERRMODE_EXCEPTION became the default as of php 8.0.0
305 | * PDO::ERRMODE_SILENT was the default before php 8.0.0
306 | */
307 | $this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
308 |
309 | switch ($this->_driver) {
310 | case 'mysql':
311 | $this->_conn->exec('SET NAMES utf8');
312 | // $this->_conn->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
313 | /* Turn this on if you want strict checking of default values, etc. */
314 | /* $this->_conn->exec("SET SESSION sql_mode = 'STRICT_TRANS_TABLES'"); */
315 | /* The following is the default on Ubuntu 16.04 */
316 | /* $this->_conn->exec("SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"); */
317 | break;
318 | case 'sqlite':
319 | $this->_conn->exec('PRAGMA foreign_keys = ON');
320 | break;
321 | }
322 | } catch (Exception $e) {
323 | return false;
324 | }
325 | if ($this->_useviews) {
326 | $tmp = $this->ViewList();
327 | foreach (array('ttreviewid', 'ttapproveid', 'ttstatid', 'ttcontentid') as $viewname) {
328 | if (in_array($viewname, $tmp)) {
329 | $this->{"_".$viewname} = true;
330 | }
331 | }
332 | }
333 |
334 | $this->_connected = true;
335 | return true;
336 | } /* }}} */
337 |
338 | /**
339 | * Make sure a database connection exisits
340 | *
341 | * This function checks for a database connection. If it does not exists
342 | * it will reconnect.
343 | *
344 | * @return boolean true if connection is established, otherwise false
345 | */
346 | public function ensureConnected() { /* {{{ */
347 | if (!$this->_connected) return $this->connect();
348 | else return true;
349 | } /* }}} */
350 |
351 | /**
352 | * Sanitize String used in database operations
353 | *
354 | * @param string $text
355 | * @return string sanitized string
356 | */
357 | public function qstr(?string $text): string { /* {{{ */
358 | return is_null($text) ? 'NULL' : $this->_conn->quote($text);
359 | } /* }}} */
360 |
361 | /**
362 | * Replace back ticks by '"'
363 | *
364 | * @param string $text
365 | * @return string sanitized string
366 | */
367 | public function rbt($text) { /* {{{ */
368 | return str_replace('`', '"', $text);
369 | } /* }}} */
370 |
371 | /**
372 | * Return sql to concat strings or fields
373 | *
374 | * @param array $arr list of field names or strings
375 | * @return string concated string
376 | */
377 | public function concat($arr) { /* {{{ */
378 | switch ($this->_driver) {
379 | case 'mysql':
380 | return 'concat('.implode(',', $arr).')';
381 | break;
382 | case 'pgsql':
383 | return implode(' || ', $arr);
384 | break;
385 | case 'sqlite':
386 | return implode(' || ', $arr);
387 | break;
388 | }
389 | return '';
390 | } /* }}} */
391 |
392 | /**
393 | * Execute SQL query and return result
394 | *
395 | * Call this function only with sql query which return data records.
396 | *
397 | * @param string $queryStr sql query
398 | * @param bool $retick
399 | * @return array|bool data if query could be executed otherwise false
400 | */
401 | public function getResultArray($queryStr, $retick = true) { /* {{{ */
402 | $resArr = array();
403 |
404 | if ($retick && $this->_driver == 'pgsql') {
405 | $queryStr = $this->rbt($queryStr);
406 | }
407 |
408 | if ($this->_logfp) {
409 | fwrite($this->_logfp, microtime(true)." ".($this->_conn->inTransaction() ? '*' : ' ')." ".$queryStr."\n");
410 | }
411 | $res = $this->_conn->query($queryStr);
412 | if ($res === false) {
413 | if ($this->_debug) {
414 | echo "error: ".$queryStr."<br />";
415 | print_r($this->_conn->errorInfo());
416 | }
417 | return false;
418 | }
419 | $resArr = $res->fetchAll(PDO::FETCH_ASSOC);
420 | // $res->Close();
421 | return $resArr;
422 | } /* }}} */
423 |
424 | /**
425 | * Execute SQL query
426 | *
427 | * Call this function only with sql query which do not return data records.
428 | *
429 | * @param string $queryStr sql query
430 | * @param boolean $retick replace all '`' by '"'
431 | * @return boolean true if query could be executed otherwise false
432 | */
433 | public function getResult($queryStr, $retick = true) { /* {{{ */
434 | if ($retick && $this->_driver == 'pgsql') {
435 | $queryStr = $this->rbt($queryStr);
436 | }
437 |
438 | if ($this->_logfp) {
439 | fwrite($this->_logfp, microtime(true)." ".($this->_conn->inTransaction() ? '*' : ' ')." ".$queryStr."\n");
440 | }
441 | $res = $this->_conn->exec($queryStr);
442 | if ($res === false) {
443 | if ($this->_debug) {
444 | echo "error: ".$queryStr."<br />";
445 | print_r($this->_conn->errorInfo());
446 | }
447 | return false;
448 | } else
449 | return true;
450 |
451 | return $res;
452 | } /* }}} */
453 |
454 | public function startTransaction() { /* {{{ */
455 | if (!$this->_intransaction) {
456 | $this->_conn->beginTransaction();
457 | }
458 | $this->_intransaction++;
459 | if ($this->_logfp) {
460 | fwrite($this->_logfp, microtime(true)." ".($this->_conn->inTransaction() ? '*' : ' ')." START ".$this->_intransaction."\n");
461 | }
462 | } /* }}} */
463 |
464 | public function rollbackTransaction() { /* {{{ */
465 | if ($this->_logfp) {
466 | fwrite($this->_logfp, microtime(true)." ".($this->_conn->inTransaction() ? '*' : ' ')." ROLLBACK ".$this->_intransaction."\n");
467 | }
468 | if ($this->_intransaction == 1) {
469 | $this->_conn->rollBack();
470 | }
471 | $this->_intransaction--;
472 | } /* }}} */
473 |
474 | public function commitTransaction() { /* {{{ */
475 | if ($this->_logfp) {
476 | fwrite($this->_logfp, microtime(true)." ".($this->_conn->inTransaction() ? '*' : ' ')." COMMIT ".$this->_intransaction."\n");
477 | }
478 | if ($this->_intransaction == 1) {
479 | $this->_conn->commit();
480 | }
481 | $this->_intransaction--;
482 | } /* }}} */
483 |
484 | public function inTransaction() { /* {{{ */
485 | return $this->_conn->inTransaction();
486 | } /* }}} */
487 |
488 | /**
489 | * Return the id of the last instert record
490 | *
491 | * @param string $tablename
492 | * @param string $fieldname
493 | * @return int id used in last autoincrement
494 | */
495 | public function getInsertID($tablename = '', $fieldname = 'id') { /* {{{ */
496 | if ($this->_driver == 'pgsql')
497 | return $this->_conn->lastInsertId('"'.$tablename.'_'.$fieldname.'_seq"');
498 | else
499 | return $this->_conn->lastInsertId();
500 | } /* }}} */
501 |
502 | public function getErrorMsg() { /* {{{ */
503 | $info = $this->_conn->errorInfo();
504 | return($info[2]);
505 | } /* }}} */
506 |
507 | public function getErrorNo() { /* {{{ */
508 | return $this->_conn->errorCode();
509 | } /* }}} */
510 |
511 | /**
512 | * Create various temporary tables to speed up and simplify sql queries
513 | *
514 | * @param string $tableName
515 | * @param bool $override
516 | * @return bool
517 | */
518 | private function __createTemporaryTable($tableName, $override = false) { /* {{{ */
519 | if (!strcasecmp($tableName, "ttreviewid")) {
520 | switch ($this->_driver) {
521 | case 'sqlite':
522 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreviewid` AS ".
523 | "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
524 | "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
525 | "FROM `tblDocumentReviewLog` ".
526 | "GROUP BY `tblDocumentReviewLog`.`reviewID` "; //.
527 | // "ORDER BY `maxLogID`";
528 | $queryStr .= "; CREATE INDEX `ttreviewid_idx` ON `ttreviewid` (`reviewID`);";
529 | $dropStr = "DROP TABLE IF EXISTS `ttreviewid`";
530 | break;
531 | case 'pgsql':
532 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreviewid` (`reviewID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`reviewID`));".
533 | "INSERT INTO `ttreviewid` SELECT `tblDocumentReviewLog`.`reviewID`, ".
534 | "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
535 | "FROM `tblDocumentReviewLog` ".
536 | "GROUP BY `tblDocumentReviewLog`.`reviewID` ";//.
537 | // "ORDER BY `maxLogID`";
538 | $dropStr = "DROP TABLE IF EXISTS `ttreviewid`";
539 | break;
540 | default:
541 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreviewid` (PRIMARY KEY (`reviewID`), INDEX (`maxLogID`)) ".
542 | "SELECT `tblDocumentReviewLog`.`reviewID`, ".
543 | "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
544 | "FROM `tblDocumentReviewLog` ".
545 | "GROUP BY `tblDocumentReviewLog`.`reviewID` "; //.
546 | // "ORDER BY `maxLogID`";
547 | $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttreviewid`";
548 | }
549 | if (!$this->_ttreviewid) {
550 | if (!$this->getResult($queryStr))
551 | return false;
552 | $this->_ttreviewid = true;
553 | }
554 | else {
555 | if (is_bool($override) && $override) {
556 | if (!$this->getResult($dropStr))
557 | return false;
558 | if (!$this->getResult($queryStr))
559 | return false;
560 | }
561 | }
562 | return $this->_ttreviewid;
563 | }
564 | elseif (!strcasecmp($tableName, "ttapproveid")) {
565 | switch ($this->_driver) {
566 | case 'sqlite':
567 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttapproveid` AS ".
568 | "SELECT `tblDocumentApproveLog`.`approveID` AS `approveID`, ".
569 | "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
570 | "FROM `tblDocumentApproveLog` ".
571 | "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
572 | // "ORDER BY `maxLogID`";
573 | $queryStr .= "; CREATE INDEX `ttapproveid_idx` ON `ttapproveid` (`approveID`);";
574 | $dropStr = "DROP TABLE IF EXISTS `ttapproveid`";
575 | break;
576 | case 'pgsql':
577 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttapproveid` (`approveID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`approveID`));".
578 | "INSERT INTO `ttapproveid` SELECT `tblDocumentApproveLog`.`approveID`, ".
579 | "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
580 | "FROM `tblDocumentApproveLog` ".
581 | "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
582 | // "ORDER BY `maxLogID`";
583 | $dropStr = "DROP TABLE IF EXISTS `ttapproveid`";
584 | break;
585 | default:
586 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttapproveid` (PRIMARY KEY (`approveID`), INDEX (`maxLogID`)) ".
587 | "SELECT `tblDocumentApproveLog`.`approveID`, ".
588 | "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
589 | "FROM `tblDocumentApproveLog` ".
590 | "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
591 | // "ORDER BY `maxLogID`";
592 | $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttapproveid`";
593 | }
594 | if (!$this->_ttapproveid) {
595 | if (!$this->getResult($queryStr))
596 | return false;
597 | $this->_ttapproveid = true;
598 | }
599 | else {
600 | if (is_bool($override) && $override) {
601 | if (!$this->getResult($dropStr))
602 | return false;
603 | if (!$this->getResult($queryStr))
604 | return false;
605 | }
606 | }
607 | return $this->_ttapproveid;
608 | }
609 | elseif (!strcasecmp($tableName, "ttstatid")) {
610 | switch ($this->_driver) {
611 | case 'sqlite':
612 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttstatid` AS ".
613 | "SELECT `tblDocumentStatusLog`.`statusID` AS `statusID`, ".
614 | "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
615 | "FROM `tblDocumentStatusLog` ".
616 | "GROUP BY `tblDocumentStatusLog`.`statusID` "; //.
617 | // "ORDER BY `maxLogID`";
618 | $queryStr .= "; CREATE INDEX `ttstatid_idx` ON `ttstatid` (`statusID`);";
619 | $dropStr = "DROP TABLE IF EXISTS `ttstatid`";
620 | break;
621 | case 'pgsql':
622 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttstatid` (`statusID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`statusID`));".
623 | "INSERT INTO `ttstatid` SELECT `tblDocumentStatusLog`.`statusID`, ".
624 | "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
625 | "FROM `tblDocumentStatusLog` ".
626 | "GROUP BY `tblDocumentStatusLog`.`statusID` "; //.
627 | // "ORDER BY `maxLogID`";
628 | $dropStr = "DROP TABLE IF EXISTS `ttstatid`";
629 | break;
630 | default:
631 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttstatid` (PRIMARY KEY (`statusID`), INDEX (`maxLogID`)) ".
632 | "SELECT `tblDocumentStatusLog`.`statusID`, ".
633 | "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
634 | "FROM `tblDocumentStatusLog` ".
635 | "GROUP BY `tblDocumentStatusLog`.`statusID` "; //.
636 | // "ORDER BY `maxLogID`";
637 | $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttstatid`";
638 | }
639 | if (!$this->_ttstatid) {
640 | if (!$this->getResult($queryStr))
641 | return false;
642 | $this->_ttstatid = true;
643 | }
644 | else {
645 | if (is_bool($override) && $override) {
646 | if (!$this->getResult($dropStr))
647 | return false;
648 | if (!$this->getResult($queryStr))
649 | return false;
650 | }
651 | }
652 | return $this->_ttstatid;
653 | }
654 | elseif (!strcasecmp($tableName, "ttcontentid")) {
655 | switch ($this->_driver) {
656 | case 'sqlite':
657 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttcontentid` AS ".
658 | "SELECT `tblDocumentContent`.`document` AS `document`, ".
659 | "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
660 | "FROM `tblDocumentContent` ".
661 | "GROUP BY `tblDocumentContent`.`document` ".
662 | "ORDER BY `tblDocumentContent`.`document`";
663 | $dropStr = "DROP TABLE IF EXISTS `ttcontentid`";
664 | break;
665 | case 'pgsql':
666 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttcontentid` (`document` INTEGER, `maxVersion` INTEGER, PRIMARY KEY (`document`)); ".
667 | "INSERT INTO `ttcontentid` SELECT `tblDocumentContent`.`document` AS `document`, ".
668 | "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
669 | "FROM `tblDocumentContent` ".
670 | "GROUP BY `tblDocumentContent`.`document` ".
671 | "ORDER BY `tblDocumentContent`.`document`";
672 | $dropStr = "DROP TABLE IF EXISTS `ttcontentid`";
673 | break;
674 | default:
675 | $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttcontentid` (PRIMARY KEY (`document`), INDEX (`maxVersion`)) ".
676 | "SELECT `tblDocumentContent`.`document`, ".
677 | "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
678 | "FROM `tblDocumentContent` ".
679 | "GROUP BY `tblDocumentContent`.`document` ".
680 | "ORDER BY `tblDocumentContent`.`document`";
681 | $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttcontentid`";
682 | }
683 | if (!$this->_ttcontentid) {
684 | if (!$this->getResult($queryStr))
685 | return false;
686 | $this->_ttcontentid = true;
687 | }
688 | else {
689 | if (is_bool($override) && $override) {
690 | if (!$this->getResult($dropStr))
691 | return false;
692 | if (!$this->getResult($queryStr))
693 | return false;
694 | }
695 | }
696 | return $this->_ttcontentid;
697 | }
698 | return false;
699 | } /* }}} */
700 |
701 | /**
702 | * Drop various temporary tables to enforce recreation when needed
703 | *
704 | * @param string $tableName
705 | *
706 | * @return bool
707 | */
708 | private function __dropTemporaryTable($tableName) { /* {{{ */
709 | $queryStr = '';
710 | if ($this->_driver == 'sqlite' || $this->_driver == 'pgsql')
711 | $t = '';
712 | else
713 | $t = 'TEMPORARY';
714 | if (!strcasecmp($tableName, "ttreviewid")) {
715 | $queryStr = "DROP ".$t." TABLE IF EXISTS `ttreviewid`";
716 | } elseif (!strcasecmp($tableName, "ttapproveid")) {
717 | $queryStr = "DROP ".$t." TABLE IF EXISTS `ttapproveid`";
718 | } elseif (!strcasecmp($tableName, "ttstatid")) {
719 | $queryStr = "DROP ".$t." TABLE IF EXISTS `ttstatid`";
720 | } elseif (!strcasecmp($tableName, "ttcontentid")) {
721 | $queryStr = "DROP ".$t." TABLE IF EXISTS `ttcontentid`";
722 | }
723 | if ($queryStr) {
724 | if (!$this->getResult($queryStr))
725 | return false;
726 | else {
727 | $this->{'_'.$tableName} = false;
728 | return true;
729 | }
730 | }
731 | return false;
732 | } /* }}} */
733 |
734 | /**
735 | * Create various views to speed up and simplify sql queries
736 | *
737 | * @param string $tableName
738 | * @param bool $override
739 | *
740 | * @return bool
741 | */
742 | private function __createView($tableName, $override = false) { /* {{{ */
743 | if (!strcasecmp($tableName, "ttreviewid")) {
744 | switch ($this->_driver) {
745 | case 'sqlite':
746 | $queryStr = "CREATE VIEW IF NOT EXISTS `ttreviewid` AS ".
747 | "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
748 | "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
749 | "FROM `tblDocumentReviewLog` ".
750 | "GROUP BY `tblDocumentReviewLog`.`reviewID` "; //.
751 | break;
752 | case 'pgsql':
753 | $queryStr = "CREATE VIEW `ttreviewid` AS ".
754 | "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
755 | "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
756 | "FROM `tblDocumentReviewLog` ".
757 | "GROUP BY `tblDocumentReviewLog`.`reviewID` ";
758 | break;
759 | default:
760 | $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttreviewid` AS ".
761 | "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
762 | "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
763 | "FROM `tblDocumentReviewLog` ".
764 | "GROUP BY `tblDocumentReviewLog`.`reviewID` ";
765 | }
766 | if (!$this->_ttreviewid) {
767 | if (!$this->getResult($queryStr))
768 | return false;
769 | $this->_ttreviewid = true;
770 | }
771 | else {
772 | if (is_bool($override) && $override) {
773 | // if (!$this->getResult("DROP VIEW `ttreviewid`"))
774 | // return false;
775 | if (!$this->getResult($queryStr))
776 | return false;
777 | }
778 | }
779 | return $this->_ttreviewid;
780 | }
781 | elseif (!strcasecmp($tableName, "ttapproveid")) {
782 | switch ($this->_driver) {
783 | case 'sqlite':
784 | $queryStr = "CREATE VIEW IF NOT EXISTS `ttapproveid` AS ".
785 | "SELECT `tblDocumentApproveLog`.`approveID` AS `approveID`, ".
786 | "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
787 | "FROM `tblDocumentApproveLog` ".
788 | "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
789 | break;
790 | case 'pgsql':
791 | $queryStr = "CREATE VIEW `ttapproveid` AS ".
792 | "SELECT `tblDocumentApproveLog`.`approveID` AS `approveID`, ".
793 | "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
794 | "FROM `tblDocumentApproveLog` ".
795 | "GROUP BY `tblDocumentApproveLog`.`approveID` ";
796 | break;
797 | default:
798 | $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttapproveid` AS ".
799 | "SELECT `tblDocumentApproveLog`.`approveID`, ".
800 | "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
801 | "FROM `tblDocumentApproveLog` ".
802 | "GROUP BY `tblDocumentApproveLog`.`approveID` ";
803 | }
804 | if (!$this->_ttapproveid) {
805 | if (!$this->getResult($queryStr))
806 | return false;
807 | $this->_ttapproveid = true;
808 | }
809 | else {
810 | if (is_bool($override) && $override) {
811 | // if (!$this->getResult("DROP VIEW `ttapproveid`"))
812 | // return false;
813 | if (!$this->getResult($queryStr))
814 | return false;
815 | }
816 | }
817 | return $this->_ttapproveid;
818 | }
819 | elseif (!strcasecmp($tableName, "ttstatid")) {
820 | switch ($this->_driver) {
821 | case 'sqlite':
822 | $queryStr = "CREATE VIEW IF NOT EXISTS `ttstatid` AS ".
823 | "SELECT `tblDocumentStatusLog`.`statusID` AS `statusID`, ".
824 | "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
825 | "FROM `tblDocumentStatusLog` ".
826 | "GROUP BY `tblDocumentStatusLog`.`statusID` ";
827 | break;
828 | case 'pgsql':
829 | $queryStr = "CREATE VIEW `ttstatid` AS ".
830 | "SELECT `tblDocumentStatusLog`.`statusID` AS `statusID`, ".
831 | "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
832 | "FROM `tblDocumentStatusLog` ".
833 | "GROUP BY `tblDocumentStatusLog`.`statusID` ";
834 | break;
835 | default:
836 | $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttstatid` AS ".
837 | "SELECT `tblDocumentStatusLog`.`statusID`, ".
838 | "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
839 | "FROM `tblDocumentStatusLog` ".
840 | "GROUP BY `tblDocumentStatusLog`.`statusID` ";
841 | }
842 | if (!$this->_ttstatid) {
843 | if (!$this->getResult($queryStr))
844 | return false;
845 | $this->_ttstatid = true;
846 | }
847 | else {
848 | if (is_bool($override) && $override) {
849 | // if (!$this->getResult("DROP VIEW `ttstatid`"))
850 | // return false;
851 | if (!$this->getResult($queryStr))
852 | return false;
853 | }
854 | }
855 | return $this->_ttstatid;
856 | }
857 | elseif (!strcasecmp($tableName, "ttcontentid")) {
858 | switch ($this->_driver) {
859 | case 'sqlite':
860 | $queryStr = "CREATE VIEW IF NOT EXISTS `ttcontentid` AS ".
861 | "SELECT `tblDocumentContent`.`document` AS `document`, ".
862 | "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
863 | "FROM `tblDocumentContent` ".
864 | "GROUP BY `tblDocumentContent`.`document` ".
865 | "ORDER BY `tblDocumentContent`.`document`";
866 | break;
867 | case 'pgsql':
868 | $queryStr = "CREATE VIEW `ttcontentid` AS ".
869 | "SELECT `tblDocumentContent`.`document` AS `document`, ".
870 | "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
871 | "FROM `tblDocumentContent` ".
872 | "GROUP BY `tblDocumentContent`.`document` ".
873 | "ORDER BY `tblDocumentContent`.`document`";
874 | break;
875 | default:
876 | $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttcontentid` AS ".
877 | "SELECT `tblDocumentContent`.`document`, ".
878 | "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
879 | "FROM `tblDocumentContent` ".
880 | "GROUP BY `tblDocumentContent`.`document` ".
881 | "ORDER BY `tblDocumentContent`.`document`";
882 | }
883 | if (!$this->_ttcontentid) {
884 | if (!$this->getResult($queryStr))
885 | return false;
886 | $this->_ttcontentid = true;
887 | }
888 | else {
889 | if (is_bool($override) && $override) {
890 | // if (!$this->getResult("DROP VIEW `ttcontentid`"))
891 | // return false;
892 | if (!$this->getResult($queryStr))
893 | return false;
894 | }
895 | }
896 | return $this->_ttcontentid;
897 | }
898 | return false;
899 | } /* }}} */
900 |
901 | /**
902 | * Create various temporary tables or view to speed up and simplify sql queries
903 | *
904 | * @param string $tableName
905 | * @param bool $override
906 | *
907 | * @return bool
908 | */
909 | public function createTemporaryTable($tableName, $override = false) { /* {{{ */
910 | if ($this->_useviews)
911 | return $this->__createView($tableName, $override);
912 | else
913 | return $this->__createTemporaryTable($tableName, $override);
914 | } /* }}} */
915 |
916 | /**
917 | * Drop various temporary tables to force recreation when next time needed
918 | *
919 | * @param string $tableName
920 | *
921 | * @return bool
922 | */
923 | public function dropTemporaryTable($tableName) { /* {{{ */
924 | if ($this->_useviews)
925 | return true; // No need to recreate a view
926 | else
927 | return $this->__dropTemporaryTable($tableName);
928 | } /* }}} */
929 |
930 | /**
931 | * Return sql statement for extracting the date part from a field
932 | * containing a unix timestamp
933 | *
934 | * @param string $fieldname name of field containing the timestamp
935 | * @param string $format
936 | * @return string sql code
937 | */
938 | public function getDateExtract($fieldname, $format = '%Y-%m-%d') { /* {{{ */
939 | switch ($this->_driver) {
940 | case 'mysql':
941 | return "from_unixtime(`".$fieldname."`, ".$this->qstr($format).")";
942 | break;
943 | case 'sqlite':
944 | return "strftime(".$this->qstr($format).", `".$fieldname."`, 'unixepoch')";
945 | break;
946 | case 'pgsql':
947 | switch ($format) {
948 | case '%Y-%m':
949 | return "to_char(to_timestamp(`".$fieldname."`), 'YYYY-MM')";
950 | break;
951 | default:
952 | return "to_char(to_timestamp(`".$fieldname."`), 'YYYY-MM-DD')";
953 | break;
954 | }
955 | break;
956 | }
957 | return '';
958 | } /* }}} */
959 |
960 | /**
961 | * Return sql statement for returning the current date and time
962 | * in format Y-m-d H:i:s
963 | *
964 | * @return string sql code
965 | */
966 | public function getCurrentDatetime($offset=0) { /* {{{ */
967 | switch ($this->_driver) {
968 | case 'mysql':
969 | if($offset)
970 | return "CURRENT_TIMESTAMP + INTERVAL ".((int) $offset)." SECOND";
971 | else
972 | return "CURRENT_TIMESTAMP";
973 | break;
974 | case 'sqlite':
975 | if($offset)
976 | return "datetime('now', 'localtime', '".((int) $offset)." seconds')";
977 | else
978 | return "datetime('now', 'localtime')";
979 | break;
980 | case 'pgsql':
981 | if($offset)
982 | return "now() + INTERVAL ".((int) $offset)." SECOND";
983 | else
984 | return "now()";
985 | break;
986 | }
987 | return '';
988 | } /* }}} */
989 |
990 | /**
991 | * Return sql statement for returning the current timestamp
992 | *
993 | * @return string sql code
994 | */
995 | public function getCurrentTimestamp() { /* {{{ */
996 | switch ($this->_driver) {
997 | case 'mysql':
998 | return "UNIX_TIMESTAMP()";
999 | break;
1000 | case 'sqlite':
1001 | return "strftime('%s', 'now')";
1002 | break;
1003 | case 'pgsql':
1004 | return "date_part('epoch',CURRENT_TIMESTAMP)::int";
1005 | break;
1006 | }
1007 | return '';
1008 | } /* }}} */
1009 |
1010 | /**
1011 | * Return sql statement for returning the current timestamp
1012 | *
1013 | * @param $field
1014 | * @return string sql code
1015 | */
1016 | public function castToText($field) { /* {{{ */
1017 | switch ($this->_driver) {
1018 | case 'pgsql':
1019 | return $field."::TEXT";
1020 | break;
1021 | }
1022 | return $field;
1023 | } /* }}} */
1024 |
1025 | /**
1026 | * Create an sql dump of the complete database
1027 | *
1028 | * @param resource $fp name of dump file
1029 | * @return bool
1030 | */
1031 | public function createDump($fp) { /* {{{ */
1032 | $tables = $this->TableList('TABLES');
1033 | foreach ($tables as $table) {
1034 | if ($table == 'sqlite_sequence')
1035 | continue;
1036 | $query = "SELECT * FROM `".$table."`";
1037 | $records = $this->getResultArray($query);
1038 | fwrite($fp, "\n-- TABLE: ".$table."--\n\n");
1039 | foreach ($records as $record) {
1040 | $values = "";
1041 | $i = 1;
1042 | foreach ($record as $column) {
1043 | if (is_null($column)) $values .= 'NULL';
1044 | elseif (is_numeric($column)) $values .= $column;
1045 | else $values .= $this->qstr($column);
1046 |
1047 | if ($i<(count($record))) $values .= ",";
1048 | $i++;
1049 | }
1050 |
1051 | fwrite($fp, "INSERT INTO `".$table."` VALUES (".$values.");\n");
1052 | }
1053 | }
1054 | return true;
1055 | } /* }}} */
1056 | }