Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
48.82% covered (danger)
48.82%
311 / 637
31.25% covered (danger)
31.25%
10 / 32
CRAP
0.00% covered (danger)
0.00%
0 / 1
SeedDMS_Core_DatabaseAccess
48.82% covered (danger)
48.82%
311 / 637
31.25% covered (danger)
31.25%
10 / 32
8290.75
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
84.00% covered (warning)
84.00%
21 / 25
0.00% covered (danger)
0.00%
0 / 1
4.07
 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
1
 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
40.20% covered (danger)
40.20%
82 / 204
0.00% covered (danger)
0.00%
0 / 1
856.88
 __dropTemporaryTable
77.78% covered (warning)
77.78%
14 / 18
0.00% covered (danger)
0.00%
0 / 1
9.89
 __createView
33.52% covered (danger)
33.52%
61 / 182
0.00% covered (danger)
0.00%
0 / 1
1229.33
 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
2declare(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 */
27class 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 temp. table for doc reception has been created
95     */
96    private $_ttreceiptid;
97
98    /**
99     * @var boolean set to true if temp. table for doc revision has been created
100     */
101    private $_ttrevisionid;
102
103    /**
104     * @var boolean set to true if in a database transaction
105     */
106    private $_intransaction;
107
108    /**
109     * @var string set a valid file name for logging all sql queries
110     */
111    private $_logfile;
112
113    /**
114     * @var resource file pointer of log file
115     */
116    private $_logfp;
117
118    /**
119     * @var boolean set to true if views instead of temp. tables shall be used
120     */
121    private $_useviews;
122
123    /**
124     * Return list of all database tables
125     *
126     * This function is used to retrieve a list of database tables for backup
127     *
128     * @return string[]|bool list of table names
129     */
130    function TableList() { /* {{{ */
131        switch($this->_driver) {
132            case 'mysql':
133                $sql = "SELECT `TABLE_NAME` AS `name` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA`='".$this->_database."' AND `TABLE_TYPE`='BASE TABLE'";
134                break;
135            case 'sqlite':
136                $sql = "SELECT tbl_name AS name FROM sqlite_master WHERE type='table'";
137                break;
138            case 'pgsql':
139                $sql = "select tablename as name from pg_catalog.pg_tables where schemaname='public'";
140                break;
141            default:
142                return false;
143        }
144        $arr = $this->getResultArray($sql);
145        $res = array();
146        foreach($arr as $tmp)
147            $res[] = $tmp['name'];
148        return $res;
149    }    /* }}} */
150
151    /**
152     * Check if database has a table
153     *
154     * This function will check if the database has a table with the given table name
155     *
156     * @return bool true if table exists, otherwise false
157     */
158    function hasTable($name) { /* {{{ */
159        switch($this->_driver) {
160            case 'mysql':
161                $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);
162                break;
163            case 'sqlite':
164                $sql = "SELECT tbl_name AS name FROM sqlite_master WHERE type='table' AND `tbl_name`=".$this->qstr($name);
165                break;
166            case 'pgsql':
167                $sql = "SELECT tablename AS name FROM pg_catalog.pg_tables WHERE schemaname='public' AND tablename=".$this->qstr($name);
168                break;
169            default:
170                return false;
171        }
172        $arr = $this->getResultArray($sql);
173        if($arr)
174            return true;
175        return false;
176    }    /* }}} */
177
178    /**
179     * Return list of all database views
180     *
181     * This function is used to retrieve a list of database views
182     *
183     * @return array list of view names
184     */
185    public function ViewList() { /* {{{ */
186        switch($this->_driver) {
187            case 'mysql':
188                $sql = "select TABLE_NAME as name from information_schema.views where TABLE_SCHEMA='".$this->_database."'";
189                break;
190            case 'sqlite':
191                $sql = "select tbl_name as name from sqlite_master where type='view'";
192                break;
193            case 'pgsql':
194                $sql = "select viewname as name from pg_catalog.pg_views where schemaname='public'";
195                break;
196            default:
197                return false;
198        }
199        $arr = $this->getResultArray($sql);
200        $res = array();
201        foreach($arr as $tmp)
202            $res[] = $tmp['name'];
203        return $res;
204    }    /* }}} */
205
206    /**
207     * Constructor of SeedDMS_Core_DatabaseAccess
208     *
209     * Sets all database parameters but does not connect.
210     *
211     * @param string $driver the database type e.g. mysql, sqlite
212     * @param string $hostname host of database server
213     * @param string $user name of user having access to database
214     * @param string $passw password of user
215     * @param bool|string $database name of database
216     */
217    function __construct($driver, $hostname, $user, $passw, $database = false) { /* {{{ */
218        $this->_driver = $driver;
219        $tmp = explode(":", $hostname);
220        $this->_hostname = $tmp[0];
221        $this->_port = null;
222        if(!empty($tmp[1]))
223            $this->_port = $tmp[1];
224        $this->_database = $database;
225        $this->_user = $user;
226        $this->_passw = $passw;
227        $this->_connected = false;
228        $this->_intransaction = 0;
229        $this->_logfile = '';
230        if($this->_logfile) {
231            $this->_logfp = fopen($this->_logfile, 'a+');
232            if($this->_logfp)
233                fwrite($this->_logfp, microtime(true)."    BEGIN ".$_SERVER['REQUEST_URI']." ------------------------------------------\n");
234        } else
235            $this->_logfp = null;
236        // $tt*****id is a hack to ensure that we do not try to create the
237        // temporary table twice during a single connection. Can be fixed by
238        // using Views (MySQL 5.0 onward) instead of temporary tables.
239        // CREATE ... IF NOT EXISTS cannot be used because it has the
240        // unpleasant side-effect of performing the insert again even if the
241        // table already exists.
242        //
243        // See createTemporaryTable() method for implementation.
244        $this->_ttreviewid = false;
245        $this->_ttapproveid = false;
246        $this->_ttstatid = false;
247        $this->_ttcontentid = false;
248        $this->_ttreceiptid = false;
249        $this->_ttrevisionid = false;
250        $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.
251        $this->_debug = false;
252    } /* }}} */
253
254    /**
255     * Return driver
256     *
257     * @return string name of driver as set in constructor
258     */
259    public function getDriver() { /* {{{ */
260        return $this->_driver;
261    } /* }}} */
262
263    /**
264     * Turn on views instead of temp. tables
265     *
266     * @param bool $onoff turn use of views instead of temp. table on/off
267     */
268    function useViews($onoff) { /* {{{ */
269        $this->_useviews = $onoff;
270    } /* }}} */
271
272    /**
273     * Destructor of SeedDMS_Core_DatabaseAccess
274     */
275    function __destruct() { /* {{{ */
276        if($this->_logfile && $this->_logfp) {
277            fwrite($this->_logfp, microtime(true)."    END --------------------------------------------\n");
278            fclose($this->_logfp);
279        }
280    } /* }}} */
281
282    /**
283     * Set the file pointer to a log file
284     *
285     * Once it is set, all queries will be logged into this file
286     */
287    function setLogFp($fp) { /* {{{ */
288        $this->_logfp = $fp;
289    } /* }}} */
290
291    /**
292     * Connect to database
293     *
294     * @return boolean true if connection could be established, otherwise false
295     */
296    function connect() { /* {{{ */
297        switch($this->_driver) {
298            case 'mysql':
299            case 'mysqli':
300            case 'mysqlnd':
301            case 'pgsql':
302                $dsn = $this->_driver.":dbname=".$this->_database.";host=".$this->_hostname;
303                if($this->_port)
304                    $dsn .= ";port=".$this->_port;
305                break;
306            case 'sqlite':
307                $dsn = $this->_driver.":".$this->_database;
308                break;
309        }
310        try {
311            /** @noinspection PhpUndefinedVariableInspection */
312            $this->_conn = new PDO($dsn, $this->_user, $this->_passw);
313            if (!$this->_conn)
314                return false;
315            /* Prevent PDO from throwing an exception because the code currently
316             * cannot handle it. PDO::ERRMODE_EXCEPTION became the default as of php 8.0.0
317             * PDO::ERRMODE_SILENT was the default before php 8.0.0
318             */
319            $this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
320
321            switch($this->_driver) {
322                case 'mysql':
323                    $this->_conn->exec('SET NAMES utf8');
324//                    $this->_conn->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
325                    /* Turn this on if you want strict checking of default values, etc. */
326                    /* $this->_conn->exec("SET SESSION sql_mode = 'STRICT_TRANS_TABLES'"); */
327                    /* The following is the default on Ubuntu 16.04 */
328                    /* $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'"); */
329                    break;
330                case 'sqlite':
331                    $this->_conn->exec('PRAGMA foreign_keys = ON');
332                    break;
333            }
334        } catch (Exception $e) {
335            return false;
336        }
337        if($this->_useviews) {
338            $tmp = $this->ViewList();
339            foreach(array('ttreviewid', 'ttapproveid', 'ttstatid', 'ttcontentid', 'ttreceiptid', 'ttrevisionid') as $viewname) {
340                if(in_array($viewname, $tmp)) {
341                    $this->{"_".$viewname} = true;
342                }
343            }
344        }
345
346        $this->_connected = true;
347        return true;
348    } /* }}} */
349
350    /**
351     * Make sure a database connection exisits
352     *
353     * This function checks for a database connection. If it does not exists
354     * it will reconnect.
355     *
356     * @return boolean true if connection is established, otherwise false
357     */
358    function ensureConnected() { /* {{{ */
359        if (!$this->_connected) return $this->connect();
360        else return true;
361    } /* }}} */
362
363    /**
364     * Sanitize String used in database operations
365     *
366     * @param string $text
367     * @return string sanitized string
368     */
369    function qstr($text) { /* {{{ */
370        return $this->_conn->quote($text);
371    } /* }}} */
372
373    /**
374     * Replace back ticks by '"'
375     *
376     * @param string $text
377     * @return string sanitized string
378     */
379    function rbt($text) { /* {{{ */
380        return str_replace('`', '"', $text);
381    } /* }}} */
382
383    /**
384     * Return sql to concat strings or fields
385     *
386     * @param array $arr list of field names or strings
387     * @return string concated string
388     */
389    function concat($arr) { /* {{{ */
390        switch($this->_driver) {
391        case 'mysql':
392            return 'concat('.implode(',', $arr).')';
393            break;
394        case 'pgsql':
395            return implode(' || ', $arr);
396            break;
397        case 'sqlite':
398            return implode(' || ', $arr);
399            break;
400        }
401        return '';
402    } /* }}} */
403
404    /**
405     * Execute SQL query and return result
406     *
407     * Call this function only with sql query which return data records.
408     *
409     * @param string $queryStr sql query
410     * @param bool $retick
411     * @return array|bool data if query could be executed otherwise false
412     */
413    function getResultArray($queryStr, $retick=true) { /* {{{ */
414        $resArr = array();
415        
416        if($retick && $this->_driver == 'pgsql') {
417            $queryStr = $this->rbt($queryStr);
418        }
419
420        if($this->_logfp) {
421            fwrite($this->_logfp, microtime(true)."    ".($this->_conn->inTransaction() ? '*' : ' ')." ".$queryStr."\n");
422        }
423        $res = $this->_conn->query($queryStr);
424        if ($res === false) {
425            if($this->_debug) {
426                echo "error: ".$queryStr."<br />";
427                print_r($this->_conn->errorInfo());
428            }
429            return false;
430        }
431        $resArr = $res->fetchAll(PDO::FETCH_ASSOC);
432//        $res->Close();
433        return $resArr;
434    } /* }}} */
435
436    /**
437     * Execute SQL query
438     *
439     * Call this function only with sql query which do not return data records.
440     *
441     * @param string $queryStr sql query
442     * @param boolean $retick replace all '`' by '"'
443     * @return boolean true if query could be executed otherwise false
444     */
445    function getResult($queryStr, $retick=true) { /* {{{ */
446        if($retick && $this->_driver == 'pgsql') {
447            $queryStr = $this->rbt($queryStr);
448        }
449
450        if($this->_logfp) {
451            fwrite($this->_logfp, microtime(true)."    ".($this->_conn->inTransaction() ? '*' : ' ')." ".$queryStr."\n");
452        }
453        $res = $this->_conn->exec($queryStr);
454        if($res === false) {
455            if($this->_debug) {
456                echo "error: ".$queryStr."<br />";
457                print_r($this->_conn->errorInfo());
458            }
459            return false;
460        } else
461            return true;
462
463        return $res;
464    } /* }}} */
465
466    function startTransaction() { /* {{{ */
467        if(!$this->_intransaction) {
468            $this->_conn->beginTransaction();
469        }
470        $this->_intransaction++;
471        if($this->_logfp) {
472            fwrite($this->_logfp, microtime(true)."    ".($this->_conn->inTransaction() ? '*' : ' ')." START ".$this->_intransaction."\n");
473        }
474    } /* }}} */
475
476    function rollbackTransaction() { /* {{{ */
477        if($this->_logfp) {
478            fwrite($this->_logfp, microtime(true)."    ".($this->_conn->inTransaction() ? '*' : ' ')." ROLLBACK ".$this->_intransaction."\n");
479        }
480        if($this->_intransaction == 1) {
481            $this->_conn->rollBack();
482        }
483        $this->_intransaction--;
484    } /* }}} */
485
486    function commitTransaction() { /* {{{ */
487        if($this->_logfp) {
488            fwrite($this->_logfp, microtime(true)."    ".($this->_conn->inTransaction() ? '*' : ' ')." COMMIT ".$this->_intransaction."\n");
489        }
490        if($this->_intransaction == 1) {
491            $this->_conn->commit();
492        }
493        $this->_intransaction--;
494    } /* }}} */
495
496    function inTransaction() { /* {{{ */
497        return $this->_conn->inTransaction();
498    } /* }}} */
499
500    /**
501     * Return the id of the last instert record
502     *
503     * @param string $tablename
504     * @param string $fieldname
505     * @return int id used in last autoincrement
506     */
507    function getInsertID($tablename='', $fieldname='id') { /* {{{ */
508        if($this->_driver == 'pgsql')
509            return $this->_conn->lastInsertId('"'.$tablename.'_'.$fieldname.'_seq"');
510        else
511            return $this->_conn->lastInsertId();
512    } /* }}} */
513
514    function getErrorMsg() { /* {{{ */
515        $info = $this->_conn->errorInfo();
516        return($info[2]);
517    } /* }}} */
518
519    function getErrorNo() { /* {{{ */
520        return $this->_conn->errorCode();
521    } /* }}} */
522
523    /**
524     * Create various temporary tables to speed up and simplify sql queries
525     *
526     * @param string $tableName
527     * @param bool $override
528     * @return bool
529     */
530    private function __createTemporaryTable($tableName, $override=false) { /* {{{ */
531        if (!strcasecmp($tableName, "ttreviewid")) {
532            switch($this->_driver) {
533                case 'sqlite':
534                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreviewid` AS ".
535                        "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
536                        "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
537                        "FROM `tblDocumentReviewLog` ".
538                        "GROUP BY `tblDocumentReviewLog`.`reviewID` "; //.
539//                        "ORDER BY `maxLogID`";
540                    $dropStr = "DROP TABLE IF EXISTS `ttreviewid`";
541                break;
542                case 'pgsql':
543                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreviewid` (`reviewID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`reviewID`));".
544                        "INSERT INTO `ttreviewid` SELECT `tblDocumentReviewLog`.`reviewID`, ".
545                        "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
546                        "FROM `tblDocumentReviewLog` ".
547                        "GROUP BY `tblDocumentReviewLog`.`reviewID` ";//.
548//                        "ORDER BY `maxLogID`";
549                    $dropStr = "DROP TABLE IF EXISTS `ttreviewid`";
550                break;
551                default:
552                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreviewid` (PRIMARY KEY (`reviewID`), INDEX (`maxLogID`)) ".
553                        "SELECT `tblDocumentReviewLog`.`reviewID`, ".
554                        "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
555                        "FROM `tblDocumentReviewLog` ".
556                        "GROUP BY `tblDocumentReviewLog`.`reviewID` "; //.
557//                        "ORDER BY `maxLogID`";
558                    $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttreviewid`";
559            }
560            if (!$this->_ttreviewid) {
561                if (!$this->getResult($queryStr))
562                    return false;
563                $this->_ttreviewid=true;
564            }
565            else {
566                if (is_bool($override) && $override) {
567                    if (!$this->getResult($dropStr))
568                        return false;
569                    if (!$this->getResult($queryStr))
570                        return false;
571                }
572            }
573            return $this->_ttreviewid;
574        }
575        elseif (!strcasecmp($tableName, "ttapproveid")) {
576            switch($this->_driver) {
577                case 'sqlite':
578                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttapproveid` AS ".
579                        "SELECT `tblDocumentApproveLog`.`approveID` AS `approveID`, ".
580                        "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
581                        "FROM `tblDocumentApproveLog` ".
582                        "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
583//                        "ORDER BY `maxLogID`";
584                    $dropStr = "DROP TABLE IF EXISTS `ttapproveid`";
585                    break;
586                case 'pgsql':
587                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttapproveid` (`approveID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`approveID`));".
588                        "INSERT INTO `ttapproveid` SELECT `tblDocumentApproveLog`.`approveID`, ".
589                        "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
590                        "FROM `tblDocumentApproveLog` ".
591                        "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
592//                        "ORDER BY `maxLogID`";
593                    $dropStr = "DROP TABLE IF EXISTS `ttapproveid`";
594                    break;
595                default:
596                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttapproveid` (PRIMARY KEY (`approveID`), INDEX (`maxLogID`)) ".
597                        "SELECT `tblDocumentApproveLog`.`approveID`, ".
598                        "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
599                        "FROM `tblDocumentApproveLog` ".
600                        "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
601//                        "ORDER BY `maxLogID`";
602                    $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttapproveid`";
603            }
604            if (!$this->_ttapproveid) {
605                if (!$this->getResult($queryStr))
606                    return false;
607                $this->_ttapproveid=true;
608            }
609            else {
610                if (is_bool($override) && $override) {
611                    if (!$this->getResult($dropStr))
612                        return false;
613                    if (!$this->getResult($queryStr))
614                        return false;
615                }
616            }
617            return $this->_ttapproveid;
618        }
619        elseif (!strcasecmp($tableName, "ttstatid")) {
620            switch($this->_driver) {
621                case 'sqlite':
622                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttstatid` AS ".
623                        "SELECT `tblDocumentStatusLog`.`statusID` AS `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                case 'pgsql':
631                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttstatid` (`statusID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`statusID`));".
632                        "INSERT INTO `ttstatid` SELECT `tblDocumentStatusLog`.`statusID`, ".
633                        "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
634                        "FROM `tblDocumentStatusLog` ".
635                        "GROUP BY `tblDocumentStatusLog`.`statusID` "; //.
636//                        "ORDER BY `maxLogID`";
637                    $dropStr = "DROP TABLE IF EXISTS `ttstatid`";
638                    break;
639                default:
640                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttstatid` (PRIMARY KEY (`statusID`), INDEX (`maxLogID`)) ".
641                        "SELECT `tblDocumentStatusLog`.`statusID`, ".
642                        "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
643                        "FROM `tblDocumentStatusLog` ".
644                        "GROUP BY `tblDocumentStatusLog`.`statusID` "; //.
645//                        "ORDER BY `maxLogID`";
646                    $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttstatid`";
647            }
648            if (!$this->_ttstatid) {
649                if (!$this->getResult($queryStr))
650                    return false;
651                $this->_ttstatid=true;
652            }
653            else {
654                if (is_bool($override) && $override) {
655                    if (!$this->getResult($dropStr))
656                        return false;
657                    if (!$this->getResult($queryStr))
658                        return false;
659                }
660            }
661            return $this->_ttstatid;
662        }
663        elseif (!strcasecmp($tableName, "ttcontentid")) {
664            switch($this->_driver) {
665                case 'sqlite':
666                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttcontentid` AS ".
667                        "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                case 'pgsql':
675                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttcontentid` (`document` INTEGER, `maxVersion` INTEGER, PRIMARY KEY (`document`)); ".
676                        "INSERT INTO `ttcontentid` SELECT `tblDocumentContent`.`document` AS `document`, ".
677                        "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
678                        "FROM `tblDocumentContent` ".
679                        "GROUP BY `tblDocumentContent`.`document` ".
680                        "ORDER BY `tblDocumentContent`.`document`";
681                    $dropStr = "DROP TABLE IF EXISTS `ttcontentid`";
682                    break;
683                default:
684                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttcontentid` (PRIMARY KEY (`document`), INDEX (`maxVersion`)) ".
685                        "SELECT `tblDocumentContent`.`document`, ".
686                        "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
687                        "FROM `tblDocumentContent` ".
688                        "GROUP BY `tblDocumentContent`.`document` ".
689                        "ORDER BY `tblDocumentContent`.`document`";
690                    $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttcontentid`";
691            }
692            if (!$this->_ttcontentid) {
693                if (!$this->getResult($queryStr))
694                    return false;
695                $this->_ttcontentid=true;
696            }
697            else {
698                if (is_bool($override) && $override) {
699                    if (!$this->getResult($dropStr))
700                        return false;
701                    if (!$this->getResult($queryStr))
702                        return false;
703                }
704            }
705            return $this->_ttcontentid;
706        }
707        elseif (!strcasecmp($tableName, "ttreceiptid")) {
708            switch($this->_driver) {
709                case 'sqlite':
710                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreceiptid` AS ".
711                        "SELECT `tblDocumentReceiptLog`.`receiptID` AS `receiptID`, ".
712                        "MAX(`tblDocumentReceiptLog`.`receiptLogID`) AS `maxLogID` ".
713                        "FROM `tblDocumentReceiptLog` ".
714                        "GROUP BY `tblDocumentReceiptLog`.`receiptID` ";
715//                        "ORDER BY `maxLogID`";
716                break;
717                case 'pgsql':
718                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreceiptid` (`receiptID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`receiptID`));".
719                        "INSERT INTO `ttreceiptid` SELECT `tblDocumentReceiptLog`.`receiptID`, ".
720                        "MAX(`tblDocumentReceiptLog`.`receiptLogID`) AS `maxLogID` ".
721                        "FROM `tblDocumentReceiptLog` ".
722                        "GROUP BY `tblDocumentReceiptLog`.`receiptID` ";
723//                        "ORDER BY `maxLogID`";
724                break;
725                default:
726                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreceiptid` (PRIMARY KEY (`receiptID`), INDEX (`maxLogID`)) ".
727                        "SELECT `tblDocumentReceiptLog`.`receiptID`, ".
728                        "MAX(`tblDocumentReceiptLog`.`receiptLogID`) AS `maxLogID` ".
729                        "FROM `tblDocumentReceiptLog` ".
730                        "GROUP BY `tblDocumentReceiptLog`.`receiptID` ";
731//                        "ORDER BY `maxLogID`";
732                    $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttreceiptid`";
733            }
734            if (!$this->_ttreceiptid) {
735                if (!$this->getResult($queryStr))
736                    return false;
737                $this->_ttreceiptid=true;
738            }
739            else {
740                if (is_bool($override) && $override) {
741                    if (!$this->getResult($dropStr))
742                        return false;
743                    if (!$this->getResult($queryStr))
744                        return false;
745                }
746            }
747            return $this->_ttreceiptid;
748        }
749        elseif (!strcasecmp($tableName, "ttrevisionid")) {
750            switch($this->_driver) {
751                case 'sqlite':
752                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttrevisionid` AS ".
753                        "SELECT `tblDocumentRevisionLog`.`revisionID` AS `revisionID`, ".
754                        "MAX(`tblDocumentRevisionLog`.`revisionLogID`) AS `maxLogID` ".
755                        "FROM `tblDocumentRevisionLog` ".
756                        "GROUP BY `tblDocumentRevisionLog`.`revisionID` ";
757//                        "ORDER BY `maxLogID`";
758                break;
759                case 'pgsql':
760                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttrevisionid` (`revisionID` INTEGER, `maxLogID` INTEGER, PRIMARY KEY (`revisionID`));".
761                        "INSERT INTO `ttrevisionid` SELECT `tblDocumentRevisionLog`.`revisionID`, ".
762                        "MAX(`tblDocumentRevisionLog`.`revisionLogID`) AS `maxLogID` ".
763                        "FROM `tblDocumentRevisionLog` ".
764                        "GROUP BY `tblDocumentRevisionLog`.`revisionID` ";
765//                        "ORDER BY `maxLogID`";
766                break;
767                default:
768                    $queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttrevisionid` (PRIMARY KEY (`revisionID`), INDEX (`maxLogID`)) ".
769                        "SELECT `tblDocumentRevisionLog`.`revisionID`, ".
770                        "MAX(`tblDocumentRevisionLog`.`revisionLogID`) AS `maxLogID` ".
771                        "FROM `tblDocumentRevisionLog` ".
772                        "GROUP BY `tblDocumentRevisionLog`.`revisionID` ";
773//                        "ORDER BY `maxLogID`";
774                    $dropStr = "DROP TEMPORARY TABLE IF EXISTS `ttrevisionid`";
775            }
776            if (!$this->_ttrevisionid) {
777                if (!$this->getResult($queryStr))
778                    return false;
779                $this->_ttrevisionid=true;
780            }
781            else {
782                if (is_bool($override) && $override) {
783                    if (!$this->getResult($dropStr))
784                        return false;
785                    if (!$this->getResult($queryStr))
786                        return false;
787                }
788            }
789            return $this->_ttrevisionid;
790        }
791        return false;
792    } /* }}} */
793
794    /**
795     * Drop various temporary tables to enforce recreation when needed
796     *
797     * @param string $tableName
798     *
799     * @return bool
800     */
801    private function __dropTemporaryTable($tableName) { /* {{{ */
802        $queryStr = '';
803        if($this->_driver == 'sqlite' || $this->_driver == 'pgsql')
804            $t = '';
805        else
806            $t = 'TEMPORARY';
807        if (!strcasecmp($tableName, "ttreviewid")) {
808            $queryStr = "DROP ".$t." TABLE IF EXISTS `ttreviewid`";
809        } elseif (!strcasecmp($tableName, "ttapproveid")) {
810            $queryStr = "DROP ".$t." TABLE IF EXISTS `ttapproveid`";
811        } elseif (!strcasecmp($tableName, "ttstatid")) {
812            $queryStr = "DROP ".$t." TABLE IF EXISTS `ttstatid`";
813        } elseif (!strcasecmp($tableName, "ttcontentid")) {
814            $queryStr = "DROP ".$t." TABLE IF EXISTS `ttcontentid`";
815        }
816        if($queryStr) {
817            if (!$this->getResult($queryStr))
818                return false;
819            else {
820                $this->{'_'.$tableName} = false;
821                return true;
822            }
823        }
824        return false;
825    } /* }}} */
826
827    /**
828     * Create various views to speed up and simplify sql queries
829     *
830     * @param string $tableName
831     * @param bool $override
832     *
833     * @return bool
834     */
835    private function __createView($tableName, $override=false) { /* {{{ */
836        if (!strcasecmp($tableName, "ttreviewid")) {
837            switch($this->_driver) {
838                case 'sqlite':
839                    $queryStr = "CREATE VIEW IF NOT EXISTS `ttreviewid` AS ".
840                        "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
841                        "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
842                        "FROM `tblDocumentReviewLog` ".
843                        "GROUP BY `tblDocumentReviewLog`.`reviewID` "; //.
844                break;
845                case 'pgsql':
846                    $queryStr = "CREATE VIEW `ttreviewid` AS ".
847                        "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
848                        "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
849                        "FROM `tblDocumentReviewLog` ".
850                        "GROUP BY `tblDocumentReviewLog`.`reviewID` ";
851                break;
852                default:
853                    $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttreviewid` AS ".
854                        "SELECT `tblDocumentReviewLog`.`reviewID` AS `reviewID`, ".
855                        "MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".
856                        "FROM `tblDocumentReviewLog` ".
857                        "GROUP BY `tblDocumentReviewLog`.`reviewID` ";
858            }
859            if (!$this->_ttreviewid) {
860                if (!$this->getResult($queryStr))
861                    return false;
862                $this->_ttreviewid=true;
863            }
864            else {
865                if (is_bool($override) && $override) {
866//                    if (!$this->getResult("DROP VIEW `ttreviewid`"))
867//                        return false;
868                    if (!$this->getResult($queryStr))
869                        return false;
870                }
871            }
872            return $this->_ttreviewid;
873        }
874        elseif (!strcasecmp($tableName, "ttapproveid")) {
875            switch($this->_driver) {
876                case 'sqlite':
877                    $queryStr = "CREATE VIEW IF NOT EXISTS `ttapproveid` AS ".
878                        "SELECT `tblDocumentApproveLog`.`approveID` AS `approveID`, ".
879                        "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
880                        "FROM `tblDocumentApproveLog` ".
881                        "GROUP BY `tblDocumentApproveLog`.`approveID` "; //.
882                    break;
883                case 'pgsql':
884                    $queryStr = "CREATE VIEW `ttapproveid` AS ".
885                        "SELECT `tblDocumentApproveLog`.`approveID` AS `approveID`, ".
886                        "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
887                        "FROM `tblDocumentApproveLog` ".
888                        "GROUP BY `tblDocumentApproveLog`.`approveID` ";
889                    break;
890                default:
891                    $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttapproveid` AS ".
892                        "SELECT `tblDocumentApproveLog`.`approveID`, ".
893                        "MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".
894                        "FROM `tblDocumentApproveLog` ".
895                        "GROUP BY `tblDocumentApproveLog`.`approveID` ";
896            }
897            if (!$this->_ttapproveid) {
898                if (!$this->getResult($queryStr))
899                    return false;
900                $this->_ttapproveid=true;
901            }
902            else {
903                if (is_bool($override) && $override) {
904//                    if (!$this->getResult("DROP VIEW `ttapproveid`"))
905//                        return false;
906                    if (!$this->getResult($queryStr))
907                        return false;
908                }
909            }
910            return $this->_ttapproveid;
911        }
912        elseif (!strcasecmp($tableName, "ttstatid")) {
913            switch($this->_driver) {
914                case 'sqlite':
915                    $queryStr = "CREATE VIEW IF NOT EXISTS `ttstatid` AS ".
916                        "SELECT `tblDocumentStatusLog`.`statusID` AS `statusID`, ".
917                        "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
918                        "FROM `tblDocumentStatusLog` ".
919                        "GROUP BY `tblDocumentStatusLog`.`statusID` ";
920                    break;
921                case 'pgsql':
922                    $queryStr = "CREATE VIEW `ttstatid` AS ".
923                        "SELECT `tblDocumentStatusLog`.`statusID` AS `statusID`, ".
924                        "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
925                        "FROM `tblDocumentStatusLog` ".
926                        "GROUP BY `tblDocumentStatusLog`.`statusID` ";
927                    break;
928                default:
929                    $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttstatid` AS ".
930                        "SELECT `tblDocumentStatusLog`.`statusID`, ".
931                        "MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".
932                        "FROM `tblDocumentStatusLog` ".
933                        "GROUP BY `tblDocumentStatusLog`.`statusID` ";
934            }
935            if (!$this->_ttstatid) {
936                if (!$this->getResult($queryStr))
937                    return false;
938                $this->_ttstatid=true;
939            }
940            else {
941                if (is_bool($override) && $override) {
942//                    if (!$this->getResult("DROP VIEW `ttstatid`"))
943//                        return false;
944                    if (!$this->getResult($queryStr))
945                        return false;
946                }
947            }
948            return $this->_ttstatid;
949        }
950        elseif (!strcasecmp($tableName, "ttcontentid")) {
951            switch($this->_driver) {
952                case 'sqlite':
953                    $queryStr = "CREATE VIEW IF NOT EXISTS `ttcontentid` AS ".
954                        "SELECT `tblDocumentContent`.`document` AS `document`, ".
955                        "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
956                        "FROM `tblDocumentContent` ".
957                        "GROUP BY `tblDocumentContent`.`document` ".
958                        "ORDER BY `tblDocumentContent`.`document`";
959                    break;
960                case 'pgsql':
961                    $queryStr = "CREATE VIEW `ttcontentid` AS ".
962                        "SELECT `tblDocumentContent`.`document` AS `document`, ".
963                        "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
964                        "FROM `tblDocumentContent` ".
965                        "GROUP BY `tblDocumentContent`.`document` ".
966                        "ORDER BY `tblDocumentContent`.`document`";
967                    break;
968                default:
969                    $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttcontentid` AS ".
970                        "SELECT `tblDocumentContent`.`document`, ".
971                        "MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".
972                        "FROM `tblDocumentContent` ".
973                        "GROUP BY `tblDocumentContent`.`document` ".
974                        "ORDER BY `tblDocumentContent`.`document`";
975            }
976            if (!$this->_ttcontentid) {
977                if (!$this->getResult($queryStr))
978                    return false;
979                $this->_ttcontentid=true;
980            }
981            else {
982                if (is_bool($override) && $override) {
983//                    if (!$this->getResult("DROP VIEW `ttcontentid`"))
984//                        return false;
985                    if (!$this->getResult($queryStr))
986                        return false;
987                }
988            }
989            return $this->_ttcontentid;
990        }
991        elseif (!strcasecmp($tableName, "ttreceiptid")) {
992            switch($this->_driver) {
993                case 'sqlite':
994                    $queryStr = "CREATE VIEW `ttreceiptid` AS ".
995                        "SELECT `tblDocumentReceiptLog`.`receiptID` AS `receiptID`, ".
996                        "MAX(`tblDocumentReceiptLog`.`receiptLogID`) AS `maxLogID` ".
997                        "FROM `tblDocumentReceiptLog` ".
998                        "GROUP BY `tblDocumentReceiptLog`.`receiptID` ";
999                break;
1000                case 'pgsql':
1001                    $queryStr = "CREATE VIEW `ttreceiptid` AS ".
1002                        "SELECT `tblDocumentReceiptLog`.`receiptID` AS `receiptID`, ".
1003                        "MAX(`tblDocumentReceiptLog`.`receiptLogID`) AS `maxLogID` ".
1004                        "FROM `tblDocumentReceiptLog` ".
1005                        "GROUP BY `tblDocumentReceiptLog`.`receiptID` ";
1006                break;
1007                default:
1008                    $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttreceiptid` AS ".
1009                        "SELECT `tblDocumentReceiptLog`.`receiptID`, ".
1010                        "MAX(`tblDocumentReceiptLog`.`receiptLogID`) AS `maxLogID` ".
1011                        "FROM `tblDocumentReceiptLog` ".
1012                        "GROUP BY `tblDocumentReceiptLog`.`receiptID` ";
1013            }
1014            if (!$this->_ttreceiptid) {
1015                if (!$this->getResult($queryStr))
1016                    return false;
1017                $this->_ttreceiptid=true;
1018            }
1019            else {
1020                if (is_bool($override) && $override) {
1021                    if (!$this->getResult("DROP VIEW `ttreceiptid`"))
1022                        return false;
1023                    if (!$this->getResult($queryStr))
1024                        return false;
1025                }
1026            }
1027            return $this->_ttreceiptid;
1028        }
1029        elseif (!strcasecmp($tableName, "ttrevisionid")) {
1030            switch($this->_driver) {
1031                case 'sqlite':
1032                    $queryStr = "CREATE VIEW `ttrevisionid` AS ".
1033                        "SELECT `tblDocumentRevisionLog`.`revisionID` AS `revisionID`, ".
1034                        "MAX(`tblDocumentRevisionLog`.`revisionLogID`) AS `maxLogID` ".
1035                        "FROM `tblDocumentRevisionLog` ".
1036                        "GROUP BY `tblDocumentRevisionLog`.`revisionID` ";
1037                break;
1038                case 'pgsql':
1039                    $queryStr = "CREATE VIEW `ttrevisionid` AS ".
1040                        "SELECT `tblDocumentRevisionLog`.`revisionID` AS `revisionID`, ".
1041                        "MAX(`tblDocumentRevisionLog`.`revisionLogID`) AS `maxLogID` ".
1042                        "FROM `tblDocumentRevisionLog` ".
1043                        "GROUP BY `tblDocumentRevisionLog`.`revisionID` ";
1044                break;
1045                default:
1046                    $queryStr = "CREATE".($override ? " OR REPLACE" : "")." VIEW `ttrevisionid` AS ".
1047                        "SELECT `tblDocumentRevisionLog`.`revisionID`, ".
1048                        "MAX(`tblDocumentRevisionLog`.`revisionLogID`) AS `maxLogID` ".
1049                        "FROM `tblDocumentRevisionLog` ".
1050                        "GROUP BY `tblDocumentRevisionLog`.`revisionID` ";
1051            }
1052            if (!$this->_ttrevisionid) {
1053                if (!$this->getResult($queryStr))
1054                    return false;
1055                $this->_ttrevisionid=true;
1056            }
1057            else {
1058                if (is_bool($override) && $override) {
1059                    if (!$this->getResult("DROP VIEW `ttrevisionid`"))
1060                        return false;
1061                    if (!$this->getResult($queryStr))
1062                        return false;
1063                }
1064            }
1065            return $this->_ttrevisionid;
1066        }
1067        return false;
1068    } /* }}} */
1069
1070    /**
1071     * Create various temporary tables or view to speed up and simplify sql queries
1072     *
1073     * @param string $tableName
1074     * @param bool $override
1075     *
1076     * @return bool
1077     */
1078    public function createTemporaryTable($tableName, $override=false) { /* {{{ */
1079        if($this->_useviews)
1080            return $this->__createView($tableName, $override);
1081        else
1082            return $this->__createTemporaryTable($tableName, $override);
1083    } /* }}} */
1084
1085    /**
1086     * Drop various temporary tables to force recreation when next time needed
1087     *
1088     * @param string $tableName
1089     *
1090     * @return bool
1091     */
1092    public function dropTemporaryTable($tableName) { /* {{{ */
1093        if($this->_useviews)
1094            return true; // No need to recreate a view
1095        else
1096            return $this->__dropTemporaryTable($tableName);
1097    } /* }}} */
1098
1099    /**
1100     * Return sql statement for extracting the date part from a field
1101     * containing a unix timestamp
1102     *
1103     * @param string $fieldname name of field containing the timestamp
1104     * @param string $format
1105     * @return string sql code
1106     */
1107    function getDateExtract($fieldname, $format='%Y-%m-%d') { /* {{{ */
1108        switch($this->_driver) {
1109            case 'mysql':
1110                return "from_unixtime(`".$fieldname."`, ".$this->qstr($format).")";
1111                break;
1112            case 'sqlite':
1113                return "strftime(".$this->qstr($format).", `".$fieldname."`, 'unixepoch')";
1114                break;
1115            case 'pgsql':
1116                switch($format) {
1117                case '%Y-%m':
1118                    return "to_char(to_timestamp(`".$fieldname."`), 'YYYY-MM')";
1119                    break;
1120                default:
1121                    return "to_char(to_timestamp(`".$fieldname."`), 'YYYY-MM-DD')";
1122                    break;
1123                }
1124                break;
1125        }
1126        return '';
1127    } /* }}} */
1128
1129    /**
1130     * Return sql statement for returning the current date and time
1131     * in format Y-m-d H:i:s
1132     *
1133     * @return string sql code
1134     */
1135    function getCurrentDatetime($dayoffset=0) { /* {{{ */
1136        switch($this->_driver) {
1137            case 'mysql':
1138                if($dayoffset)
1139                    return "DATE_ADD(CURRENT_TIMESTAMP, INTERVAL ".$dayoffset." DAY)";
1140                else
1141                    return "CURRENT_TIMESTAMP";
1142                break;
1143            case 'sqlite':
1144                if($dayoffset)
1145                    return "datetime('now', '".$dayoffset." days', 'localtime')";
1146                else
1147                    return "datetime('now', 'localtime')";
1148                break;
1149            case 'pgsql':
1150                if($dayoffset)
1151                    return "now() + interval '".$dayoffset." day'";
1152                else
1153                    return "now()";
1154                break;
1155        }
1156        return '';
1157    } /* }}} */
1158
1159    /**
1160     * Return sql statement for returning the current timestamp
1161     *
1162     * @return string sql code
1163     */
1164    function getCurrentTimestamp() { /* {{{ */
1165        switch($this->_driver) {
1166            case 'mysql':
1167                return "UNIX_TIMESTAMP()";
1168                break;
1169            case 'sqlite':
1170                return "strftime('%s', 'now')";
1171                break;
1172            case 'pgsql':
1173                return "date_part('epoch',CURRENT_TIMESTAMP)::int";
1174                break;
1175        }
1176        return '';
1177    } /* }}} */
1178
1179    /**
1180     * Return sql statement for returning the current timestamp
1181     *
1182     * @param $field
1183     * @return string sql code
1184     */
1185    function castToText($field) { /* {{{ */
1186        switch($this->_driver) {
1187            case 'pgsql':
1188                return $field."::TEXT";
1189                break;
1190        }
1191        return $field;
1192    } /* }}} */
1193
1194    /**
1195     * Create an sql dump of the complete database
1196     *
1197     * @param resource $fp name of dump file
1198     * @return bool
1199     */
1200    function createDump($fp) { /* {{{ */
1201        $tables = $this->TableList('TABLES');
1202        foreach($tables as $table) {
1203            if($table == 'sqlite_sequence')
1204                continue;
1205            $query = "SELECT * FROM `".$table."`";
1206            $records = $this->getResultArray($query);
1207            fwrite($fp,"\n-- TABLE: ".$table."--\n\n");
1208            foreach($records as $record) {
1209                $values="";
1210                $i = 1;
1211                foreach ($record as $column) {
1212                    if (is_null($column)) $values .= 'NULL';
1213                    elseif (is_numeric($column)) $values .= $column;
1214                    else $values .= $this->qstr($column);
1215
1216                    if ($i<(count($record))) $values .= ",";
1217                    $i++;
1218                }
1219
1220                fwrite($fp, "INSERT INTO `".$table."` VALUES (".$values.");\n");
1221            }
1222        }
1223        return true;
1224    } /* }}} */
1225}