dbHandler.php

Go to the documentation of this file.

<?php

    require_once('ppyrd.base.php');


    class dbHandler extends ppyrd {
        var $db;

        public function __construct() {
        // connects or creates sqlite db file
        $this->open();

        // updating table model
        $this->update();

        //$this->alterTableAddColumns("testtable", "createdDate", " TEXT");
        //$this->alterTableDropColumns("testtable", "createdDate,modifiedDate,publishedDate");

        } // End constructor


        function query($query)
        {
            $result = $this->db->query($query);
            if (!$result) {
                    // the query failed
                    $this->output("There was an error in query: $query");
                    $this->output($this->db->lastErrorMsg());
                    return false;
            }
            return $result;
        }

        function exec($query)
        {
                $result = $this->db->exec($query);
                if (!$result) {
                        // the query failed
                        $this->output("There was an error in query: $query");
                        $this->output(
                            $this->db->lastErrorMsg());
                }
                return $result;
        }

        function getActiveArchiveRules ()
        {
            return $this->query("SELECT * FROM rule_archive WHERE isActive = 1");
        }

        function getActiveSenders ()
        {
            return $this->query("SELECT * FROM rule_senders WHERE isActive = 1");
        }

        function getConfigValue ($varname)
        {
                $results = $this->query("SELECT * FROM config WHERE configVariable = '$varname'");
                if ($results == false)
                    return false;
                $row = $results->fetchArray();
                return $row['configValue'];
        }


        function getPersonalVariables ()
        {
            return $this->query("SELECT * FROM rule_personalInfo WHERE isActive = 1");
        }


        function getActiveSubjects ()
        {
            return $this->query("SELECT * FROM rule_subjects WHERE isActive = 1");
        }

        function getActiveRecipients ()
        {
            return $this->query("SELECT * FROM rule_recipients WHERE isActive = 1");
        }

        function writeLog($oldName, $newName, $content, $log)
        {
            $safe = SQLite3::escapeString($content);
            $this->exec("INSERT INTO logs (oldFileName, newFileName, fileContent, log) VALUES ('$oldName', '$newName', '$safe', '$log');");
        }


        function open()
        {
            $this->db = new SQLite3("/data/database/paperyard.sqlite");
            $this->db->busyTimeout(15000);
        // WAL mode has better control over concurrency.
        // Source: https://www.sqlite.org/wal.html
            $this->db->exec('PRAGMA journal_mode = wal;');
        }

        function update()
        {

            $this->output("looking for DB updates");

            // changing to sqlite update directory
            chdir("/www/updates/sqlite/");

            // getting all .sql files
            $updates = glob("*.sql");

            // checking each file
            foreach($updates as $update){
                $dbversion = $this->getConfigValue("databaseVersion");
                if ($dbversion == false)
                    $dbversion = 0;
                $version = str_replace(".sql", "", $update);
                // really only execute next one - if that fails no further updates shall be attempted
                if ($version == $dbversion+1) {
                    $this->output ("applying " . $update);
                    $this->output("found update script:" . $version);
                    $sql = file_get_contents ($update);
                    $this->exec($sql);
                }
            }
        }

        function close()
        {

            $res = $this->db->close();
            unset($this->db);
        }

    }

?>