Source of file DbSqlite3.php

Size: 21,287 Bytes - Last Modified: 2016-05-18T03:08:27+02:00

buildproject/core/module_system/system/db/DbSqlite3.php

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715
<?php
/*"******************************************************************************************************
*   (c) 2004-2006 by MulchProductions, www.mulchprod.de                                                 *
*   (c) 2007-2016 by Kajona, www.kajona.de                                                              *
*       Published under the GNU LGPL v2.1, see /system/licence_lgpl.txt                                 *
********************************************************************************************************/

namespace Kajona\System\System\Db;

use Kajona\System\System\Database;
use Kajona\System\System\DbDatatypes;
use Kajona\System\System\Exception;
use Kajona\System\System\Filesystem;
use SQLite3;
use SQLite3Stmt;


/**
 * db-driver for sqlite3 using the php-sqlite3-interface.
 * Based on the sqlite2 driver by phwolfer
 *
 * @since 3.3.0.1
 * @author sidler@mulchprod.de
 * @package module_system
 */
class DbSqlite3 extends DbBase
{

    /**
     * @var SQLite3
     */
    private $linkDB;
    private $strDbFile;

    /**
     * This method makes sure to connect to the database properly
     *
     * @param string $strHost
     * @param string $strUsername
     * @param string $strPass
     * @param string $strDbName
     * @param int $intPort
     *
     * @throws Exception
     * @return bool
     */
    public function dbconnect($strHost, $strUsername, $strPass, $strDbName, $intPort)
    {

        if ($strDbName == "") {
            return false;
        }

        $this->strDbFile = _projectpath_.'/dbdumps/'.$strDbName.'.db3';

        try {


            $strPath = _realpath_.$this->strDbFile;
            /*if(defined("_autotesting_")) {
                $strPath = ":memory:";
            }*/
            $this->linkDB = new SQLite3($strPath);
            $this->_pQuery('PRAGMA encoding = "UTF-8"', array());
            //TODO deprecated in sqlite, so may be removed
            $this->_pQuery('PRAGMA short_column_names = ON', array());
            $this->_pQuery("PRAGMA journal_mode = TRUNCATE", array());
            if (method_exists($this->linkDB, "busyTimeout")) {
                $this->linkDB->busyTimeout(5000);
            }

            return true;
        }
        catch (Exception $e) {
            throw new Exception("Error connecting to database: ".$e, Exception::$level_FATALERROR);
        }

    }

    /**
     * Closes the connection to the database
     *
     * @return void
     */
    public function dbclose()
    {
        $this->linkDB->close();
    }


    private function buildAndCopyTempTables($strTargetTableName, $arrSourceTableInfo, $arrTargetTableInfo)
    {
        $bitReturn = true;

        /* Get existing table info */
        $arrPragmaTableInfo = $this->getPArray("PRAGMA table_info('{$strTargetTableName}')", array());
        $arrColumnsPragma = array();
        foreach($arrPragmaTableInfo as $arrRow) {
            $arrColumnsPragma[$arrRow['name']] = $arrRow;
        }

        $arrSourceColumns = array();
        array_walk($arrSourceTableInfo, function ($arrValue) use (&$arrSourceColumns) {
            $arrSourceColumns[] = $arrValue["columnName"];
        });

        $arrTargetColumns = array();
        array_walk($arrTargetTableInfo, function ($arrValue) use (&$arrTargetColumns) {
            $arrTargetColumns[] = $arrValue["columnName"];
        });


        //build the a temp table
        $strQuery = "CREATE TABLE ".$strTargetTableName."_temp ( \n";

        //loop the fields
        $arrColumns = array();
        $arrPks = array();
        foreach ($arrTargetTableInfo as $arrOneColumn) {
            $arrRow = null;

            if(array_key_exists($arrOneColumn["columnName"], $arrColumnsPragma)) {
                $arrRow = $arrColumnsPragma[$arrOneColumn["columnName"]];
            }
            else {
                $arrRow["name"] = $arrOneColumn["columnName"];
                $arrRow["type"] = $arrOneColumn["columnType"];
            }

            //column settings
            $strColumn = " ".$arrRow["name"]." ".$arrRow["type"];

            if(array_key_exists("notnull", $arrRow) && $arrRow["notnull"] === 1) {
                $strColumn .= " NOT NULL ";
            }
            elseif(array_key_exists("notnull", $arrRow) && $arrRow["notnull"] === 0) {
                $strColumn .= " NULL ";
            }

            if(array_key_exists("dflt_value", $arrRow) && $arrRow["dflt_value"] !== null) {
                $strColumn .= " DEFAULT {$arrRow["dflt_value"]} ";
            }
            $arrColumns[] = $strColumn;

            //primary key?
            if(array_key_exists("pk", $arrRow) && $arrRow["pk"] === 1) {
                $arrPks[] = $arrRow["name"];
            }
        }

        //columns
        $strQuery .= implode(",\n", $arrColumns);

        //primary keys
        if(count($arrPks) > 0) {
            $strQuery .= ",PRIMARY KEY (";
            $strQuery .= implode(",", $arrPks);
            $strQuery .= ")\n";
        }

        $strQuery .= ")\n";

        $bitReturn = $bitReturn && $this->_pQuery($strQuery, array());

        //copy all values
        $strQuery = "INSERT INTO ".$strTargetTableName."_temp (".implode(",", $arrTargetColumns).") SELECT ".implode(",", $arrSourceColumns)." FROM ".$strTargetTableName;
        $bitReturn = $bitReturn && $this->_pQuery($strQuery, array());

        $strQuery = "DROP TABLE ".$strTargetTableName;
        $bitReturn = $bitReturn && $this->_pQuery($strQuery, array());

        return $bitReturn && $this->renameTable($strTargetTableName."_temp", $strTargetTableName);
    }


    /**
     * Renames a single column of the table
     *
     * @param $strTable
     * @param $strOldColumnName
     * @param $strNewColumnName
     * @param $strNewDatatype
     *
     * @return bool
     * @since 4.6
     */
    public function changeColumn($strTable, $strOldColumnName, $strNewColumnName, $strNewDatatype)
    {

        $arrTableInfo = $this->getColumnsOfTable($strTable);
        $arrTargetTableInfo = array();
        foreach ($arrTableInfo as $arrOneColumn) {
            if ($arrOneColumn["columnName"] == $strOldColumnName) {
                $arrNewRow = array(
                    "columnName" => $strNewColumnName,
                    "columnType" => $this->getDatatype($strNewDatatype)
                );

                $arrTargetTableInfo[] = $arrNewRow;
            }
            else {
                $arrTargetTableInfo[] = $arrOneColumn;
            }

        }

        return $this->buildAndCopyTempTables($strTable, $arrTableInfo, $arrTargetTableInfo);
    }


    /**
     * removes a single column from the table
     *
     * @param $strTable
     * @param $strColumn
     *
     * @return bool
     * @since 4.6
     */
    public function removeColumn($strTable, $strColumn)
    {

        $arrTableInfo = $this->getColumnsOfTable($strTable);
        $arrTargetTableInfo = array();
        foreach ($arrTableInfo as $arrOneColumn) {
            if ($arrOneColumn["columnName"] != $strColumn) {
                $arrTargetTableInfo[] = $arrOneColumn;
            }

        }

        return $this->buildAndCopyTempTables($strTable, $arrTargetTableInfo, $arrTargetTableInfo);
    }


    /**
     * Creates a single query in order to insert multiple rows at one time.
     * For most databases, this will create s.th. like
     * INSERT INTO $strTable ($arrColumns) VALUES (?, ?), (?, ?)...
     * Please note that this method is used to create the query itself, based on the Kajona-internal syntax.
     * The query is fired to the database by Database
     *
     * @param string $strTable
     * @param string[] $arrColumns
     * @param array $arrValueSets
     * @param Database $objDb
     *
     * @return bool
     */
    public function triggerMultiInsert($strTable, $arrColumns, $arrValueSets, Database $objDb)
    {

        $arrVersion = SQLite3::version();
        if (version_compare("3.7.11", $arrVersion["versionString"], "<=")) {
            return parent::triggerMultiInsert($strTable, $arrColumns, $arrValueSets, $objDb);
        }
        //legacy code
        else {

            $arrSafeColumns = array();
            $arrPlaceholder = array();
            foreach ($arrColumns as $strOneColumn) {
                $arrSafeColumns[] = $this->encloseColumnName($strOneColumn);
                $arrPlaceholder[] = "?";
            }

            $arrParams = array();

            $strQuery = "INSERT INTO ".$this->encloseTableName($strTable)."  (".implode(",", $arrSafeColumns).") ";
            for ($intI = 0; $intI < count($arrValueSets); $intI++) {

                $arrTemp = array();
                for ($intK = 0; $intK < count($arrColumns); $intK++) {
                    $arrTemp[] = " ? AS ".$this->encloseColumnName($arrColumns[$intK]);
                }

                if ($intI == 0) {
                    $strQuery .= " SELECT ".implode(", ", $arrTemp);
                }
                else {
                    $strQuery .= " UNION SELECT ".implode(", ", $arrTemp);
                }

                $arrParams = array_merge($arrParams, $arrValueSets[$intI]);
            }

            return $objDb->_pQuery($strQuery, $arrParams);
        }
    }

    /**
     * Sends a prepared statement to the database. All params must be represented by the ? char.
     * The params themselves are stored using the second params using the matching order.
     *
     * @param string $strQuery
     * @param array $arrParams
     *
     * @return bool
     * @since 3.4
     */
    public function _pQuery($strQuery, $arrParams)
    {
        $strQuery = $this->fixQuoting($strQuery);
        $strQuery = $this->processQuery($strQuery);

        $objStmt = $this->getPreparedStatement($strQuery);
        if ($objStmt === false) {
            return false;
        }
        $intCount = 1;
        foreach ($arrParams as $strOneParam) {
            if ($strOneParam === null) {
                $objStmt->bindValue(':param'.$intCount++, $strOneParam, SQLITE3_NULL);
            }
            //else if(is_double($strOneParam))
            //    $objStmt->bindValue(':param'.$intCount++ , $strOneParam, SQLITE3_FLOAT);
            //else if(is_numeric($strOneParam))
            //    $objStmt->bindValue(':param'.$intCount++ , $strOneParam, SQLITE3_INTEGER);
            else {
                $objStmt->bindValue(':param'.$intCount++, $strOneParam, SQLITE3_TEXT);
            }
        }

        if ($objStmt->execute() === false) {
            return false;
        }

        return true;
    }

    /**
     * This method is used to retrieve an array of resultsets from the database using
     * a prepared statement
     *
     * @param string $strQuery
     * @param array $arrParams
     *
     * @since 3.4
     * @return array
     */
    public function getPArray($strQuery, $arrParams)
    {
        $strQuery = $this->fixQuoting($strQuery);
        $strQuery = $this->processQuery($strQuery);

        $objStmt = $this->getPreparedStatement($strQuery);
        if ($objStmt === false) {
            return false;
        }

        $intCount = 1;
        foreach ($arrParams as $strOneParam) {
            if ($strOneParam === null) {
                $objStmt->bindValue(':param'.$intCount++, $strOneParam, SQLITE3_NULL);
            }
            //else if(is_double($strOneParam))
            //    $objStmt->bindValue(':param'.$intCount++ , $strOneParam, SQLITE3_FLOAT);
            //else if(is_numeric($strOneParam))
            //    $objStmt->bindValue(':param'.$intCount++ , $strOneParam, SQLITE3_INTEGER);
            else {
                $objStmt->bindValue(':param'.$intCount++, $strOneParam, SQLITE3_TEXT);
            }
        }

        $arrResult = array();
        $objResult = $objStmt->execute();

        if ($objResult === false) {
            return false;
        }

        while ($arrTemp = $objResult->fetchArray()) {
            $arrResult[] = $arrTemp;
        }

        return $arrResult;
    }


    /**
     * Returns the last error reported by the database.
     * Is being called after unsuccessful queries
     *
     * @return string
     */
    public function getError()
    {
        return $this->linkDB->lastErrorMsg();
    }

    /**
     * Returns ALL tables in the database currently connected to.
     * The method should return an array using the following keys:
     * name => Table name
     *
     * @return array
     */
    public function getTables()
    {
        $arrReturn = array();
        $resultSet = $this->linkDB->query("SELECT name FROM sqlite_master WHERE type='table'");
        while ($arrRow = $resultSet->fetchArray(SQLITE3_ASSOC)) {
            $arrReturn[] = array("name" => $arrRow["name"]);
        }
        return $arrReturn;
    }

    /**
     * Looks up the columns of the given table.
     * Should return an array for each row consisting of:
     * array ("columnName", "columnType")
     *
     * @param string $strTableName
     *
     * @return array
     */
    public function getColumnsOfTable($strTableName)
    {
        $arrTableInfo = $this->getPArray("PRAGMA table_info('{$strTableName}')", array());

        $arrColumns = array();
        foreach($arrTableInfo as $arrRow) {
            $arrColumns[] = array(
                "columnName" => $arrRow['name'],
                "columnType" => $arrRow['type']
            );
        }

        return $arrColumns;
    }

    /**
     * Used to send a create table statement to the database
     * By passing the query through this method, the driver can
     * add db-specific commands.
     * The array of fields should have the following structure
     * $array[string columnName] = array(string datatype, boolean isNull [, default (only if not null)])
     * whereas datatype is one of the following:
     *         int
     *      long
     *         double
     *         char10
     *         char20
     *         char100
     *         char254
     *      char500
     *         text
     *      longtext
     *
     * @param string $strName
     * @param array $arrFields array of fields / columns
     * @param array $arrKeys array of primary keys
     * @param array $arrIndices array of additional indices
     * @param bool $bitTxSafe Should the table support transactions?
     *
     * @return bool
     */
    public function createTable($strName, $arrFields, $arrKeys, $arrIndices = array(), $bitTxSafe = true)
    {
        $arrTables = $this->getTables();
        foreach ($arrTables as $arrTable) {
            if ($arrTable["name"] == $strName) {
                return true;
            }
        }

        $strQuery = "";

        //build the mysql code
        $strQuery .= "CREATE TABLE ".$strName." ( \n";

        //loop the fields
        foreach ($arrFields as $strFieldName => $arrColumnSettings) {
            $strQuery .= " ".$strFieldName." ";

            $strQuery .= $this->getDatatype($arrColumnSettings[0]);

            //any default?
            if (isset($arrColumnSettings[2])) {
                $strQuery .= " DEFAULT ".$arrColumnSettings[2]." ";
            }

            //nullable?
            if ($arrColumnSettings[1] === true) {
                $strQuery .= ", \n";
            }
            else {
                $strQuery .= " NOT NULL, \n";
            }

        }

        //primary keys
        $strQuery .= " PRIMARY KEY (".implode(", ", $arrKeys).") \n";

        $strQuery .= ") ";

        $bitCreate = $this->_pQuery($strQuery, array());

        if ($bitCreate && count($arrIndices) > 0) {
            foreach ($arrIndices as $strOneIndex) {
                if (is_array($strOneIndex)) {
                    $strQuery = "CREATE INDEX ix_".generateSystemid()." ON ".$strName." ( ".implode(", ", $strOneIndex).") ";
                } else {
                    $strQuery = "CREATE INDEX ix_".generateSystemid()." ON ".$strName." ( ".$strOneIndex.") ";
                }
                $bitCreate = $bitCreate && $this->_pQuery($strQuery, array());
            }
        }

        return $bitCreate;
    }


    /**
     * Starts a transaction
     *
     * @return void
     */
    public function transactionBegin()
    {
        $this->_pQuery("BEGIN TRANSACTION", array());
    }

    /**
     * Ends a successful operation by Committing the transaction
     *
     * @return void
     */
    public function transactionCommit()
    {
        $this->_pQuery("COMMIT TRANSACTION", array());
    }

    /**
     * Ends a non-successfull transaction by using a rollback
     *
     * @return void
     */
    public function transactionRollback()
    {
        $this->_pQuery("ROLLBACK TRANSACTION", array());
    }

    /**
     * returns an array with infos about the current database
     * The array returned should have tho following structure:
     * ["dbserver"]
     * ["dbclient"]
     * ["dbconnection"]
     *
     * @return mixed
     */
    public function getDbInfo()
    {
        $arrDB = $this->linkDB->version();
        $arrReturn = array();
        $arrReturn["dbdriver"] = "sqlite3-extension";
        $arrReturn["dbserver"] = "SQLite3 ".$arrDB["versionString"]." ".$arrDB["versionNumber"];
        $arrReturn["dbclient"] = "";
        $arrReturn["dbconnection"] = "";
        return $arrReturn;
    }

    /**
     * Creates an db-dump usind the given filename. the filename is relative to _realpath_
     * The dump must include, and ONLY include the pass tables
     *
     * @param string $strFilename
     * @param array $arrTables
     *
     * @return bool Indicates, if the dump worked or not
     */
    public function dbExport($strFilename, $arrTables)
    {
        // FIXME: Only export relevant tables.
        $objFilesystem = new Filesystem();
        return $objFilesystem->fileCopy($this->strDbFile, $strFilename);
    }

    /**
     * Imports the given db-dump file to the database. The filename ist relative to _realpath_
     *
     * @param string $strFilename
     *
     * @return bool
     */
    public function dbImport($strFilename)
    {
        $objFilesystem = new Filesystem();
        return $objFilesystem->fileCopy($strFilename, $this->strDbFile, true);
    }


    /**
     * Returns the db-specific datatype for the kajona internal datatype.
     * Currently, this are
     *      int
     *      long
     *      double
     *      char10
     *      char20
     *      char100
     *      char254
     *      char500
     *      text
     *      longtext
     *
     * @param string $strType
     *
     * @return string
     */
    public function getDatatype($strType)
    {
        $strReturn = "";

        if ($strType == DbDatatypes::STR_TYPE_INT) {
            $strReturn .= " INTEGER ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_LONG) {
            $strReturn .= " INTEGER ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_DOUBLE) {
            $strReturn .= " REAL ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_CHAR10) {
            $strReturn .= " TEXT ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_CHAR20) {
            $strReturn .= " TEXT ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_CHAR100) {
            $strReturn .= " TEXT ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_CHAR254) {
            $strReturn .= " TEXT ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_CHAR500) {
            $strReturn .= " TEXT ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_TEXT) {
            $strReturn .= " TEXT ";
        }
        elseif ($strType == DbDatatypes::STR_TYPE_LONGTEXT) {
            $strReturn .= " TEXT ";
        }
        else {
            $strReturn .= " TEXT ";
        }

        return $strReturn;
    }

    /**
     * Fixes the quoting of ' in queries.
     * By default ' is quoted as \', but it must be quoted as '' in sqlite.
     *
     * @param string $strSql
     *
     * @return string
     */
    private function fixQuoting($strSql)
    {
        $strSql = str_replace("\\'", "''", $strSql);
        $strSql = str_replace("\\\"", "\"", $strSql);
        return $strSql;
    }

    /**
     * Transforms the query into a valid sqlite-syntax
     *
     * @param string $strQuery
     *
     * @return string
     */
    private function processQuery($strQuery)
    {
        $intCount = 1;
        while (uniStrpos($strQuery, "?") !== false) {
            $intPos = uniStrpos($strQuery, "?");
            $strQuery = substr($strQuery, 0, $intPos).":param".$intCount++.substr($strQuery, $intPos + 1);
        }
        return $strQuery;
    }

    /**
     * Prepares a statement or uses an instance from the cache
     *
     * @param string $strQuery
     *
     * @return SQLite3Stmt
     */
    private function getPreparedStatement($strQuery)
    {

        $strName = md5($strQuery);

        if (isset($this->arrStatementsCache[$strName])) {
            return $this->arrStatementsCache[$strName];
        }

        $objStmt = $this->linkDB->prepare($strQuery);
        $this->arrStatementsCache[$strName] = $objStmt;

        return $objStmt;
    }

    public function encloseTableName($strTable)
    {
        return "'".$strTable."'";
    }

}