Proyectos de Subversion Moodle

<?php
/**
 * SQLite3 driver
 *
 * @link https://www.sqlite.org/
 *
 * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
 *
 * @package ADOdb
 * @link https://adodb.org Project's web site and documentation
 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
 *
 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
 * any later version. This means you can use it in proprietary products.
 * See the LICENSE.md file distributed with this source code for details.
 * @license BSD-3-Clause
 * @license LGPL-2.1-or-later
 *
 * @copyright 2000-2013 John Lim
 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
 */

// security - hide paths
if (!defined('ADODB_DIR')) die();

/**
 * Class ADODB_sqlite3
 */
class ADODB_sqlite3 extends ADOConnection {
        var $databaseType = "sqlite3";
        var $dataProvider = "sqlite";
        var $replaceQuote = "''"; // string to use to replace quotes
        var $concat_operator='||';
        var $_errorNo = 0;
        var $hasLimit = true;
        var $hasInsertID = true;                /// supports autoincrement ID?
        var $hasAffectedRows = true;    /// supports affected rows for update/delete?
        var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
        var $sysDate = "DATE('now','localtime')";
        var $sysTimeStamp = "DATETIME('now','localtime')";
        var $fmtTimeStamp = "'Y-m-d H:i:s'";

        /** @var SQLite3 */
        var $_connectionID;

        function ServerInfo()
        {
                $version = SQLite3::version();
                $arr['version'] = $version['versionString'];
                $arr['description'] = 'SQLite 3';
                return $arr;
        }

        function BeginTrans()
        {
                if ($this->transOff) {
                        return true;
                }
                $this->Execute("BEGIN TRANSACTION");
                $this->transCnt += 1;
                return true;
        }

        function CommitTrans($ok=true)
        {
                if ($this->transOff) {
                        return true;
                }
                if (!$ok) {
                        return $this->RollbackTrans();
                }
                $ret = $this->Execute("COMMIT");
                if ($this->transCnt > 0) {
                        $this->transCnt -= 1;
                }
                return !empty($ret);
        }

        function RollbackTrans()
        {
                if ($this->transOff) {
                        return true;
                }
                $ret = $this->Execute("ROLLBACK");
                if ($this->transCnt > 0) {
                        $this->transCnt -= 1;
                }
                return !empty($ret);
        }

        function metaType($t,$len=-1,$fieldobj=false)
        {

                if (is_object($t))
                {
                        $fieldobj = $t;
                        $t = $fieldobj->type;
                        $len = $fieldobj->max_length;
                }

                $t = strtoupper($t);

                if (array_key_exists($t,$this->customActualTypes))
                        return  $this->customActualTypes[$t];

                /*
                * We are using the Sqlite affinity method here
                * @link https://www.sqlite.org/datatype3.html
                */
                $affinity = array(
                'INT'=>'INTEGER',
                'INTEGER'=>'INTEGER',
                'TINYINT'=>'INTEGER',
                'SMALLINT'=>'INTEGER',
                'MEDIUMINT'=>'INTEGER',
                'BIGINT'=>'INTEGER',
                'UNSIGNED BIG INT'=>'INTEGER',
                'INT2'=>'INTEGER',
                'INT8'=>'INTEGER',

                'CHARACTER'=>'TEXT',
                'VARCHAR'=>'TEXT',
                'VARYING CHARACTER'=>'TEXT',
                'NCHAR'=>'TEXT',
                'NATIVE CHARACTER'=>'TEXT',
                'NVARCHAR'=>'TEXT',
                'TEXT'=>'TEXT',
                'CLOB'=>'TEXT',

                'BLOB'=>'BLOB',

                'REAL'=>'REAL',
                'DOUBLE'=>'REAL',
                'DOUBLE PRECISION'=>'REAL',
                'FLOAT'=>'REAL',

                'NUMERIC'=>'NUMERIC',
                'DECIMAL'=>'NUMERIC',
                'BOOLEAN'=>'NUMERIC',
                'DATE'=>'NUMERIC',
                'DATETIME'=>'NUMERIC'
                );

                if (!isset($affinity[$t]))
                        return ADODB_DEFAULT_METATYPE;

                $subt = $affinity[$t];
                /*
                * Now that we have subclassed the provided data down
                * the sqlite 'affinity', we convert to ADOdb metatype
                */

                $subclass = array('INTEGER'=>'I',
                                                  'TEXT'=>'X',
                                                  'BLOB'=>'B',
                                                  'REAL'=>'N',
                                                  'NUMERIC'=>'N');

                return $subclass[$subt];
        }
        // mark newnham
        function MetaColumns($table, $normalize=true)
        {
                global $ADODB_FETCH_MODE;
                $false = false;
                $save = $ADODB_FETCH_MODE;
                $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
                if ($this->fetchMode !== false) {
                        $savem = $this->SetFetchMode(false);
                }
                $rs = $this->Execute("PRAGMA table_info('$table')");
                if (isset($savem)) {
                        $this->SetFetchMode($savem);
                }
                if (!$rs) {
                        $ADODB_FETCH_MODE = $save;
                        return $false;
                }
                $arr = array();
                while ($r = $rs->FetchRow()) {
                        $type = explode('(',$r['type']);
                        $size = '';
                        if (sizeof($type)==2) {
                                $size = trim($type[1],')');
                        }
                        $fn = strtoupper($r['name']);
                        $fld = new ADOFieldObject;
                        $fld->name = $r['name'];
                        $fld->type = $type[0];
                        $fld->max_length = $size;
                        $fld->not_null = $r['notnull'];
                        $fld->default_value = $r['dflt_value'];
                        $fld->scale = 0;
                        if (isset($r['pk']) && $r['pk']) {
                                $fld->primary_key=1;
                        }
                        if ($save == ADODB_FETCH_NUM) {
                                $arr[] = $fld;
                        } else {
                                $arr[strtoupper($fld->name)] = $fld;
                        }
                }
                $rs->Close();
                $ADODB_FETCH_MODE = $save;
                return $arr;
        }

        public function metaForeignKeys($table, $owner = '', $upper =  false, $associative =  false)
        {
            global $ADODB_FETCH_MODE;
                if ($ADODB_FETCH_MODE == ADODB_FETCH_ASSOC
                || $this->fetchMode == ADODB_FETCH_ASSOC)
                $associative = true;

            /*
                * Read sqlite master to find foreign keys
                */
                $sql = "SELECT sql
                                 FROM (
                                SELECT sql sql, type type, tbl_name tbl_name, name name
                                  FROM sqlite_master
                                  )
                                WHERE type != 'meta'
                                  AND sql NOTNULL
                                  AND LOWER(name) ='" . strtolower($table) . "'";

                $tableSql = $this->getOne($sql);

                $fkeyList = array();
                $ylist = preg_split("/,+/",$tableSql);
                foreach ($ylist as $y)
                {
                        if (!preg_match('/FOREIGN/',$y))
                                continue;

                        $matches = false;
                        preg_match_all('/\((.+?)\)/i',$y,$matches);
                        $tmatches = false;
                        preg_match_all('/REFERENCES (.+?)\(/i',$y,$tmatches);

                        if ($associative)
                        {
                                if (!isset($fkeyList[$tmatches[1][0]]))
                                        $fkeyList[$tmatches[1][0]]      = array();
                                $fkeyList[$tmatches[1][0]][$matches[1][0]] = $matches[1][1];
                        }
                        else
                                $fkeyList[$tmatches[1][0]][] = $matches[1][0] . '=' . $matches[1][1];
                }

                if ($associative)
                {
                        if ($upper)
                                $fkeyList = array_change_key_case($fkeyList,CASE_UPPER);
                        else
                                $fkeyList = array_change_key_case($fkeyList,CASE_LOWER);
                }
                return $fkeyList;
        }


        function _init($parentDriver)
        {
                $parentDriver->hasTransactions = false;
                $parentDriver->hasInsertID = true;
        }

        protected function _insertID($table = '', $column = '')
        {
                return $this->_connectionID->lastInsertRowID();
        }

        function _affectedrows()
        {
                return $this->_connectionID->changes();
        }

        function ErrorMsg()
        {
                if ($this->_logsql) {
                        return $this->_errorMsg;
                }
                return ($this->_errorNo) ? $this->ErrorNo() : ''; //**tochange?
        }

        function ErrorNo()
        {
                return $this->_connectionID->lastErrorCode(); //**tochange??
        }

        function SQLDate($fmt, $col=false)
        {
                /*
                * In order to map the values correctly, we must ensure the proper
                * casing for certain fields
                * Y must be UC, because y is a 2 digit year
                * d must be LC, because D is 3 char day
                * A must be UC  because a is non-portable am
                * Q must be UC  because q means nothing
                */
                $fromChars = array('y','D','a','q');
                $toChars   = array('Y','d','A','Q');
                $fmt       = str_replace($fromChars,$toChars,$fmt);

                $fmt = $this->qstr($fmt);
                return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
        }

        function _createFunctions()
        {
                $this->_connectionID->createFunction('adodb_date', 'adodb_date', 1);
                $this->_connectionID->createFunction('adodb_date2', 'adodb_date2', 2);
        }

        /** @noinspection PhpUnusedParameterInspection */
        function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
        {
                if (empty($argHostname) && $argDatabasename) {
                        $argHostname = $argDatabasename;
                }
                $this->_connectionID = new SQLite3($argHostname);
                $this->_createFunctions();

                return true;
        }

        function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
        {
                // There's no permanent connect in SQLite3
                return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
        }

        function _query($sql,$inputarr=false)
        {
                $rez = $this->_connectionID->query($sql);
                if ($rez === false) {
                        $this->_errorNo = $this->_connectionID->lastErrorCode();
                }
                // If no data was returned, we don't need to create a real recordset
                elseif ($rez->numColumns() == 0) {
                        $rez->finalize();
                        $rez = true;
                }

                return $rez;
        }

        function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
        {
                $nrows = (int) $nrows;
                $offset = (int) $offset;
                $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
                $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
                if ($secs2cache) {
                        $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
                } else {
                        $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
                }

                return $rs;
        }

        /*
                This algorithm is not very efficient, but works even if table locking
                is not available.

                Will return false if unable to generate an ID after $MAXLOOPS attempts.
        */
        var $_genSeqSQL = "create table %s (id integer)";

        function GenID($seq='adodbseq',$start=1)
        {
                // if you have to modify the parameter below, your database is overloaded,
                // or you need to implement generation of id's yourself!
                $MAXLOOPS = 100;
                //$this->debug=1;
                while (--$MAXLOOPS>=0) {
                        @($num = $this->GetOne("select id from $seq"));
                        if ($num === false) {
                                $this->Execute(sprintf($this->_genSeqSQL ,$seq));
                                $start -= 1;
                                $num = '0';
                                $ok = $this->Execute("insert into $seq values($start)");
                                if (!$ok) {
                                        return false;
                                }
                        }
                        $this->Execute("update $seq set id=id+1 where id=$num");

                        if ($this->affected_rows() > 0) {
                                $num += 1;
                                $this->genID = $num;
                                return $num;
                        }
                }
                if ($fn = $this->raiseErrorFn) {
                        $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
                }
                return false;
        }

        function createSequence($seqname='adodbseq', $startID=1)
        {
                if (empty($this->_genSeqSQL)) {
                        return false;
                }
                $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
                if (!$ok) {
                        return false;
                }
                $startID -= 1;
                return $this->Execute("insert into $seqname values($startID)");
        }

        var $_dropSeqSQL = 'drop table %s';
        function DropSequence($seqname = 'adodbseq')
        {
                if (empty($this->_dropSeqSQL)) {
                        return false;
                }
                return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
        }

        // returns true or false
        function _close()
        {
                return $this->_connectionID->close();
        }

        function metaIndexes($table, $primary = FALSE, $owner = false)
        {
                $false = false;
                // save old fetch mode
                global $ADODB_FETCH_MODE;
                $save = $ADODB_FETCH_MODE;
                $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
                if ($this->fetchMode !== FALSE) {
                        $savem = $this->SetFetchMode(FALSE);
                }

                $pragmaData = array();

                /*
                * If we want the primary key, we must extract
                * it from the table statement, and the pragma
                */
                if ($primary)
                {
                        $sql = sprintf('PRAGMA table_info([%s]);',
                                                   strtolower($table)
                                                   );
                        $pragmaData = $this->getAll($sql);
                }

                /*
                * Exclude the empty entry for the primary index
                */
                $sqlite = "SELECT name,sql
                                         FROM sqlite_master
                                        WHERE type='index'
                                          AND sql IS NOT NULL
                                          AND LOWER(tbl_name)='%s'";

                $SQL = sprintf($sqlite,
                                     strtolower($table)
                                         );

                $rs = $this->execute($SQL);

                if (!is_object($rs)) {
                        if (isset($savem)) {
                                $this->SetFetchMode($savem);
                        }
                        $ADODB_FETCH_MODE = $save;
                        return $false;
                }

                $indexes = array ();

                while ($row = $rs->FetchRow())
                {

                        if (!isset($indexes[$row[0]])) {
                                $indexes[$row[0]] = array(
                                        'unique' => preg_match("/unique/i",$row[1]),
                                        'columns' => array()
                                );
                        }
                        /**
                         * The index elements appear in the SQL statement
                         * in cols[1] between parentheses
                         * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
                         */
                        preg_match_all('/\((.*)\)/',$row[1],$indexExpression);
                        $indexes[$row[0]]['columns'] = array_map('trim',explode(',',$indexExpression[1][0]));
                }

                if (isset($savem)) {
                        $this->SetFetchMode($savem);
                        $ADODB_FETCH_MODE = $save;
                }

                /*
                * If we want primary, add it here
                */
                if ($primary){

                        /*
                        * Check the previously retrieved pragma to search
                        * with a closure
                        */

                        $pkIndexData = array('unique'=>1,'columns'=>array());

                        $pkCallBack = function ($value, $key) use (&$pkIndexData) {

                                /*
                                * As we iterate the elements check for pk index and sort
                                */
                                if ($value[5] > 0)
                                {
                                        $pkIndexData['columns'][$value[5]] = strtolower($value[1]);
                                        ksort($pkIndexData['columns']);
                                }
                        };

                        array_walk($pragmaData,$pkCallBack);

                        /*
                        * If we found no columns, there is no
                        * primary index
                        */
                        if (count($pkIndexData['columns']) > 0)
                                $indexes['PRIMARY'] = $pkIndexData;
                }

                return $indexes;
        }

        /**
        * Returns the maximum size of a MetaType C field. Because of the
        * database design, sqlite places no limits on the size of data inserted
        *
        * @return int
        */
        function charMax()
        {
                return ADODB_STRINGMAX_NOLIMIT;
        }

        /**
        * Returns the maximum size of a MetaType X field. Because of the
        * database design, sqlite places no limits on the size of data inserted
        *
        * @return int
        */
        function textMax()
        {
                return ADODB_STRINGMAX_NOLIMIT;
        }

        /**
         * Converts a date to a month only field and pads it to 2 characters
         *
         * This uses the more efficient strftime native function to process
         *
         * @param string $fld   The name of the field to process
         *
         * @return string The SQL Statement
         */
        function month($fld)
        {
                return "strftime('%m',$fld)";
        }

        /**
         * Converts a date to a day only field and pads it to 2 characters
         *
         * This uses the more efficient strftime native function to process
         *
         * @param string $fld   The name of the field to process
         *
         * @return string The SQL Statement
         */
        function day($fld) {
                return "strftime('%d',$fld)";
        }

        /**
         * Converts a date to a year only field
         *
         * This uses the more efficient strftime native function to process
         *
         * @param string $fld   The name of the field to process
         *
         * @return string The SQL Statement
         */
        function year($fld)
        {
                return "strftime('%Y',$fld)";
        }

        /**
         * SQLite update for blob
         *
         * SQLite must be a fully prepared statement (all variables must be bound),
         * so $where can either be an array (array params) or a string that we will
         * do our best to unpack and turn into a prepared statement.
         *
         * @param string $table
         * @param string $column
         * @param string $val      Blob value to set
         * @param mixed  $where    An array of parameters (key => value pairs),
         *                         or a string (where clause).
         * @param string $blobtype ignored
         *
         * @return bool success
         */
        function updateBlob($table, $column, $val, $where, $blobtype = 'BLOB')
        {
                if (is_array($where)) {
                        // We were passed a set of key=>value pairs
                        $params = $where;
                } else {
                        // Given a where clause string, we have to disassemble the
                        // statements into keys and values
                        $params = array();
                        $temp = preg_split('/(where|and)/i', $where);
                        $where = array_filter($temp);

                        foreach ($where as $wValue) {
                                $wTemp = preg_split('/[= \']+/', $wValue);
                                $wTemp = array_filter($wTemp);
                                $wTemp = array_values($wTemp);
                                $params[$wTemp[0]] = $wTemp[1];
                        }
                }

                $paramWhere = array();
                foreach ($params as $bindKey => $bindValue) {
                        $paramWhere[] = $bindKey . '=?';
                }

                $sql = "UPDATE $table SET $column=? WHERE "
                        . implode(' AND ', $paramWhere);

                // Prepare the statement
                $stmt = $this->_connectionID->prepare($sql);

                // Set the first bind value equal to value we want to update
                if (!$stmt->bindValue(1, $val, SQLITE3_BLOB)) {
                        return false;
                }

                // Build as many keys as available
                $bindIndex = 2;
                foreach ($params as $bindValue) {
                        if (is_integer($bindValue) || is_bool($bindValue) || is_float($bindValue)) {
                                $type = SQLITE3_NUM;
                        } elseif (is_object($bindValue)) {
                                // Assume a blob, this should never appear in
                                // the binding for a where statement anyway
                                $type = SQLITE3_BLOB;
                        } else {
                                $type = SQLITE3_TEXT;
                        }

                        if (!$stmt->bindValue($bindIndex, $bindValue, $type)) {
                                return false;
                        }

                        $bindIndex++;
                }

                // Now execute the update. NB this is SQLite execute, not ADOdb
                $ok = $stmt->execute();
                return is_object($ok);
        }

        /**
         * SQLite update for blob from a file
         *
         * @param string $table
         * @param string $column
         * @param string $path      Filename containing blob data
         * @param mixed  $where    {@see updateBlob()}
         * @param string $blobtype ignored
         *
         * @return bool success
         */
        function updateBlobFile($table, $column, $path, $where, $blobtype = 'BLOB')
        {
                if (!file_exists($path)) {
                        return false;
                }

                // Read file information
                $fileContents = file_get_contents($path);
                if ($fileContents === false)
                        // Distinguish between an empty file and failure
                        return false;

                return $this->updateBlob($table, $column, $fileContents, $where, $blobtype);
        }

}

/*--------------------------------------------------------------------------------------
                Class Name: Recordset
--------------------------------------------------------------------------------------*/

class ADORecordset_sqlite3 extends ADORecordSet {

        var $databaseType = "sqlite3";
        var $bind = false;

        /** @var SQLite3Result */
        var $_queryID;

        /** @noinspection PhpMissingParentConstructorInspection */
        function __construct($queryID,$mode=false)
        {
                if ($mode === false) {
                        global $ADODB_FETCH_MODE;
                        $mode = $ADODB_FETCH_MODE;
                }
                switch($mode) {
                        case ADODB_FETCH_NUM:
                                $this->fetchMode = SQLITE3_NUM;
                                break;
                        case ADODB_FETCH_ASSOC:
                                $this->fetchMode = SQLITE3_ASSOC;
                                break;
                        default:
                                $this->fetchMode = SQLITE3_BOTH;
                                break;
                }
                $this->adodbFetchMode = $mode;

                $this->_queryID = $queryID;

                $this->_inited = true;
                $this->fields = array();
                if ($queryID) {
                        $this->_currentRow = 0;
                        $this->EOF = !$this->_fetch();
                        @$this->_initrs();
                } else {
                        $this->_numOfRows = 0;
                        $this->_numOfFields = 0;
                        $this->EOF = true;
                }

                return $this->_queryID;
        }


        function FetchField($fieldOffset = -1)
        {
                $fld = new ADOFieldObject;
                $fld->name = $this->_queryID->columnName($fieldOffset);
                $fld->type = 'VARCHAR';
                $fld->max_length = -1;
                return $fld;
        }

        function _initrs()
        {
                $this->_numOfFields = $this->_queryID->numColumns();

        }

        function Fields($colname)
        {
                if ($this->fetchMode != SQLITE3_NUM) {
                        return $this->fields[$colname];
                }
                if (!$this->bind) {
                        $this->bind = array();
                        for ($i=0; $i < $this->_numOfFields; $i++) {
                                $o = $this->FetchField($i);
                                $this->bind[strtoupper($o->name)] = $i;
                        }
                }

                return $this->fields[$this->bind[strtoupper($colname)]];
        }

        function _seek($row)
        {
                // sqlite3 does not implement seek
                if ($this->debug) {
                        ADOConnection::outp("SQLite3 does not implement seek");
                }
                return false;
        }

        function _fetch($ignore_fields=false)
        {
                $this->fields = $this->_queryID->fetchArray($this->fetchMode);
                return !empty($this->fields);
        }

        function _close()
        {
        }

}