Sqlite.php

浏览该文件的文档。

<?php

class FLEA_Db_Driver_Sqlite
{
    var $NEXT_ID_SQL    = "UPDATE %s SET id = LAST_INSERT_ID(id + 1)";
    var $CREATE_SEQ_SQL = "CREATE TABLE %s (id INT NOT NULL)";
    var $INIT_SEQ_SQL   = "INSERT INTO %s VALUES (%s)";
    var $DROP_SEQ_SQL   = "DROP TABLE %s";

    var $TRUE_VALUE  = 1;
    var $FALSE_VALUE = 0;
    var $NULL_VALUE = 'NULL';

    var $META_COLUMNS_SQL = "SELECT sql FROM sqlite_master WHERE type='table' and name='%s'";//sqlite 用这个表来保存数据库的SQL

    var $dsn = null;

    var $conn = null;

    var $log = array();

    var $enableLog = false;

    var $lasterr = null;

    var $lasterrcode = null;

    var $_insertId = null;

    var $_transCount = 0;

    var $_transCommit = true;

    function FLEA_Db_Driver_Sqlite($dsn = false)
    {
        $tmp = (array)$dsn;
        unset($tmp['password']);
        $this->dsn = $dsn;
        $this->enableLog = !defined('DEPLOY_MODE') || DEPLOY_MODE != true;
        if (!function_exists('log_message')) {
            $this->enableLog = false;
        }
    }

    function connect($dsn = false)
    {
        $dsn = $dsn? $dsn : $this->dsn;
        $this->conn=false;
        if(file_exists($dsn['db'])){
            $this->conn=sqlite_open($dsn['db']);
            if($this->conn){
                return $this->conn;
            }
        }
        FLEA::loadClass('FLEA_Db_Exception_SqlQuery');
       __THROW(new FLEA_Db_Exception_SqlQuery("connect('{$dsn['db']}') failed!"));
        return false;
    }

    function close()
    {
        if ($this->conn) {
            sqlite_close($this->conn);
        }
        $this->conn = null;
        $this->lasterr = null;
        $this->lasterrcode = null;
        $this->_insertId = null;
        $this->_transCount = 0;
        $this->_transCommit = true;
    }

    function execute($sql, $inputarr = null, $throw = true)
    {
        if(substr($sql,0,11)=="INSERT INTO") {
            // 删除SQL中的指定的表，SQLITE不支持在插入中语句有表名在前面
            $len1 = strpos($sql, '(');
            $len2 = strpos($sql, ')');
            $len3 = strpos($sql,'VALUES');
            $temp = array();
            if($len2 < $len3){
                $temp[]= substr($sql,0,$len1);
                $temp[] = substr($sql,$len1,$len2-$len1);
                $temp[] = substr($sql,$len2);
                $temp[1] = eregi_replace("[a-z_0-9]+\\.", "", $temp[1]);
                $sql = implode($temp);
            }
        }
        if (is_array($inputarr)) {
            $sql = $this->_prepareSql($sql, $inputarr);
        }
        if ($this->enableLog) {
            $this->log[] = $sql;
            log_message("sql:\n{$sql}", 'debug');
        }

        $result = @sqlite_query($sql, $this->conn);
        if ($result !== false) {
            $this->lasterr = null;
            $this->lasterrcode = null;
            return $result;
        }
        $this->lasterrcode = sqlite_last_error($this->conn);
        $this->lasterr = sqlite_error_string($this->lasterrcode);
        if (!$throw) { return false; }

        FLEA::loadClass('FLEA_Db_Exception_SqlQuery');
        __THROW(new FLEA_Db_Exception_SqlQuery($sql, $this->lasterr, $this->lasterrcode));
        return false;
    }

    function qstr($value)
    {
        if (is_bool($value)) { return $value ? $this->TRUE_VALUE : $this->FALSE_VALUE; }
        if (is_null($value)) { return $this->NULL_VALUE; }
        return "'" . sqlite_escape_string($value) . "'";
    }

    function qtable($tableName)
    {
        return $tableName;//SQLite 对转换支持不是很好，经常出错
    }

    function qfield($fieldName, $tableName = null)
    {
        $pos = strpos($fieldName, '.');
        if ($pos !== false) {
            $tableName = substr($fieldName, 0, $pos);
            $fieldName = substr($fieldName, $pos + 1);
        }
        if ($tableName != '') {
            if ($fieldName != '*') {
                return "{$tableName}.{$fieldName}";
            } else {
                return "{$tableName}.*";
            }
        } else {
            if ($fieldName != '*') {
                return "{$fieldName}";
            } else {
                return "*";
            }
        }
    }

    function qfields($fields, $tableName = null)
    {
        if (!is_array($fields)) {
            $fields = explode(',', $fields);
        }
        $return = array();
        foreach ($fields as $fieldName) {
            $fieldName = trim($fieldName);
            if ($fieldName == '') { continue; }
            $pos = strpos($fieldName, '.');
            if ($pos !== false) {
                $tableName = substr($fieldName, 0, $pos);
                $fieldName = substr($fieldName, $pos + 1);
            }
            if ($tableName != '') {
                if ($fieldName != '*') {
                    $return[] = "{$tableName}.{$fieldName}";
                } else {
                    $return[] = "{$tableName}.*";
                }
            } else {
                if ($fieldName != '*') {
                    $return[] = "{$fieldName}";
                } else {
                    $return[] = '*';
                }
            }
        }
        return implode(', ', $return);
    }

    function nextId($seqName = 'sdboseq', $startValue = 1)
    {
        $result = $this->execute(sprintf($this->NEXT_ID_SQL, $seqName), null, false);
        if ($result === false) {
            if (!$this->createSeq($seqName, $startValue)) { return false; }
            $this->execute(sprintf($this->NEXT_ID_SQL, $seqName));
        }
        $id = $this->insertId();
        if ($id) { return $id; }
        if ($this->execute(sprintf($this->INIT_SEQ_SQL, $seqName, $startValue))) {
            return $startValue;
        }
        return false;
    }

    function createSeq($seqName = 'sdboseq', $startValue = 1)
    {
        if ($this->execute(sprintf($this->CREATE_SEQ_SQL, $seqName))) {
            return $this->execute(sprintf($this->INIT_SEQ_SQL, $seqName, $startValue - 1));
        } else {
            return false;
        }
    }

    function dropSeq($seqName = 'sdboseq')
    {
        return $this->execute(sprintf($this->DROP_SEQ_SQL, $seqName));
    }

    function insertId()
    {
       return sqlite_last_insert_rowid($this->conn);
    }

    function affectedRows()
    {
        return sqlite_num_rows($this->conn);//这里仅对select有效
    }

    function fetchRow($res)
    {
        $row = sqlite_fetch_array($res);
        $temp = array();
        foreach($row as $key => $value){
            $key = eregi_replace('^[a-z0-9_]+\.','',$key);
            $temp[$key] = $value;
        }
        return $temp;
    }

    function fetchAssoc($res)
    {
        $row = sqlite_fetch_array($res,SQLITE_ASSOC);//Sqlite 没有这个功能
        $temp = array();
        foreach($row as $key => $value){
            $key = eregi_replace('^[a-z0-9_]+\.','',$key);
            $temp[$key] = $value;
        }
        return $temp;
    }

    function freeRes($res)
    {
        //return sqlite_free_result($res);
        return true;//sqlite 没有这样的函数
    }

    function selectLimit($sql, $length = null, $offset = null)
    {
        if (!is_null($offset)) {
            $sql .= "\nLIMIT " . (int)$offset;
            if (!is_null($length)) {
                $sql .= ', ' . (int)$length;
            } else {
                $sql .= ', 4294967294';
            }
        } elseif (!is_null($length)) {
            $sql .= "\nLIMIT " . (int)$length;
        }
        return $this->execute($sql);
    }

    function & getAll($sql)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->execute($sql);
        }
        $data = array();
        while ($row = sqlite_fetch_array($res,SQLITE_ASSOC)) {
            $temp = array();
            foreach($row as $key => $value){
                $key = eregi_replace('^[a-z0-9_]+\.','',$key);
                $temp[$key] = $value;
            }
            $data[] = $temp;
        }
       // sqlite_free_result($res);
        return $data;
    }

    function & getAllGroupBy($sql, $groupBy)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->execute($sql);
        }
        $data = array();

        $row = sqlite_fetch_array($res,SQLITE_ASSOC);
        if ($row != false) {
            $temp = array();
            foreach($row as $key => $value){
                $key = eregi_replace('^[a-z0-9_]+\.','',$key);
                $temp[$key] = $value;
            }
            $row = $temp;
            if ($groupBy === true) {
                $groupBy = key($row);
            }
            do {
                $rkv = $row[$groupBy];
                unset($row[$groupBy]);
                $data[$rkv][] = $row;
            } while ($row = sqlite_fetch_array($res,SQLITE_ASSOC));
        }
        return $data;
    }

    function getAllWithFieldRefs($sql, $field, & $fieldValues, & $reference)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->execute($sql);
        }

        $fieldValues = array();
        $reference = array();
        $offset = 0;
        $data = array();
        while ($row = sqlite_fetch_array($res,SQLITE_ASSOC)) {
            $temp = array();
            foreach($row as $key => $value){
                $key = eregi_replace('^[a-z0-9_]+\.','',$key);
                $temp[$key] = $value;
            }
            $row = $temp;
            $fieldValue = $row[$field];
            unset($row[$field]);
            $data[$offset] = $row;
            $fieldValues[$offset] = $fieldValue;
            $reference[$fieldValue] =& $data[$offset];
            $offset++;
        }
        return $data;
    }

    function assemble($sql, & $assocRowset, $mappingName, $oneToOne, $refKeyName, $limit = null)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            if (!is_null($limit)) {
                if (is_array($limit)) {
                    list($length, $offset) = $limit;
                } else {
                    $length = $limit;
                    $offset = 0;
                }
                $res = $this->selectLimit($sql, $length, $offset);
            } else {
                $res = $this->execute($sql);
            }
        }

        if ($oneToOne) {
            // 一对一组装数据
            while ($row = sqlite_fetch_array($res,SQLITE_ASSOC)) {
                $temp = array();
                foreach($row as $key => $value){
                    $key = eregi_replace('^[a-z0-9_]+\.','',$key);
                    $temp[$key] = $value;
                }
                $row = $temp;
                $rkv = $row[$refKeyName];
                unset($row[$refKeyName]);
                $assocRowset[$rkv][$mappingName] = $row;
            }
        } else {
            // 一对多组装数据
            while ($row = sqlite_fetch_array($res,SQLITE_ASSOC)) {
                $rkv = $row[$refKeyName];
                unset($row[$refKeyName]);
                $temp = array();
                foreach($row as $key => $value){
                    $key = eregi_replace('^[a-z0-9_]+\.','',$key);
                    $temp[$key] = $value;
                }
                $assocRowset[$rkv][$mappingName][] = $temp;
            }
        }
    }

    function getOne($sql)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->execute($sql);
        }
        $row = sqlite_fetch_array($res,SQLITE_NUM);
        //sqlite_free_result($res);
        return isset($row[0]) ? $row[0] : null;
    }

    function & getRow($sql)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->execute($sql);
        }
        $row = sqlite_fetch_array($res,SQLITE_ASSOC);
        $temp = array();
        foreach($row as $key => $value){
            $key = eregi_replace('^[a-z0-9_]+\.','',$key);
            $temp[$key] = $value;
        }
        return $temp;
    }

    function & getCol($sql, $col = 0)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->execute($sql);
        }
        $data = array();
        while ($row = sqlite_fetch_array($res,SQLITE_NUM)) {
            $data[] = $row[$col];
        }
        return $data;
    }

    function & metaColumns($table)
    {
        static $typeMap = array(
            'BIT'           => 'I',
            'TINYINT'       => 'I',
            'BOOL'          => 'L',
            'BOOLEAN'       => 'L',
            'SMALLINT'      => 'I',
            'MEDIUMINT'     => 'I',
            'INT'           => 'I',
            'INTEGER'       => 'I',
            'BIGINT'        => 'I',
            'FLOAT'         => 'N',
            'DOUBLE'        => 'N',
            'DOUBLEPRECISION' => 'N',
            'FLOAT'         => 'N',
            'DECIMAL'       => 'N',
            'DEC'           => 'N',

            'DATE'          => 'D',
            'DATETIME'      => 'T',
            'TIMESTAMP'     => 'T',
            'TIME'          => 'T',
            'YEAR'          => 'I',

            'CHAR'          => 'C',
            'NCHAR'         => 'C',
            'VARCHAR'       => 'C',
            'NVARCHAR'      => 'C',
            'BINARY'        => 'B',
            'VARBINARY'     => 'B',
            'TINYBLOB'      => 'X',
            'TINYTEXT'      => 'X',
            'BLOB'          => 'X',
            'TEXT'          => 'X',
            'MEDIUMBLOB'    => 'X',
            'MEDIUMTEXT'    => 'X',
            'LONGBLOB'      => 'X',
            'LONGTEXT'      => 'X',
            'ENUM'          => 'C',
            'SET'           => 'C',
        );

        $rs = $this->execute(sprintf($this->META_COLUMNS_SQL, $table));
        if (!$rs) { return false; }
        $retarr = array();
        $sql = sqlite_fetch_array($rs);
        $sql = $sql[0];
        $firstPar   = strpos($sql, '(');
        $endPar     = strrpos($sql, ')')-1;
        $sql = substr($sql, ($firstPar+1), ($endPar - $firstPar));
        $sql = str_replace("\n", '', $sql);
        $sql = str_replace("'", '', $sql);
        $ligne = explode(',', $sql);

        //get index key
        $sql = "select sql from sqlite_master where type='index' and tbl_name='$table'";
        $rs = $this->execute($sql);
        $sql = sqlite_fetch_array($rs);
        $sql = $sql[0];
        $firstPar   = strpos($sql, '(');
        $endPar     = strrpos($sql, ')')-1;
        $sql = substr($sql, ($firstPar+1), ($endPar - $firstPar));
        $sql = str_replace("\n", '', $sql);
        $sql = str_replace("'", '', $sql);
        $temp = explode(',', $sql);
        $index = array();
        foreach ($temp as $value) {
            $value = trim($value);
            if($value){
                $index[$value] = true;
            }
        }

        while(list($ligneNum, $cont) = each($ligne)){
            $row = explode(' ', trim($cont));
            $field = array();
            $field['name'] = $row[0];
            $type = $row[1];
            $field['scale'] = null;
            $queryArray = false;
            if (preg_match('/^(.+)\((\d+),(\d+)/', $type, $queryArray)) {
                $field['type'] = $queryArray[1];
                $field['maxLength'] = is_numeric($queryArray[2]) ? $queryArray[2] : -1;
                $field['scale'] = is_numeric($queryArray[3]) ? $queryArray[3] : -1;
            } elseif (preg_match('/^(.+)\((\d+)/', $type, $queryArray)) {
                $field['type'] = $queryArray[1];
                $field['maxLength'] = is_numeric($queryArray[2]) ? $queryArray[2] : -1;
            } elseif (preg_match('/^(enum)\((.*)\)$/i', $type, $queryArray)) {
                $field['type'] = $queryArray[1];
                $arr = explode(",",$queryArray[2]);
                $field['enums'] = $arr;
                $zlen = max(array_map("strlen",$arr)) - 2; // PHP >= 4.0.6
                $field['maxLength'] = ($zlen > 0) ? $zlen : 1;
            } else {
                $field['type'] = $type;
                $field['maxLength'] = -1;
            }
            $field['simpleType'] = $typeMap[strtoupper($field['type'])];
            if ($field['simpleType'] == 'C' && $field['maxLength'] > 250) {
                $field['simpleType'] = 'X';
            }

            $temp = eregi('PRIMARY[[:space:]]KEY', $cont);
            $field['primaryKey'] = $temp || $index[$row[0]];
            $field['notNull'] = $field['primaryKey'] || (strtoupper($row[2]) == 'NOT');
            $field['autoIncrement'] = $temp;
            if ($field['autoIncrement']) { $field['simpleType'] = 'R'; }
            $field['binary'] = (strpos($type,'blob') !== false);
            $field['unsigned'] = (strpos($type,'unsigned') !== false);

            if (!$field['binary']) {
                $d = $row[4];
                if ($d != '' && $d != 'NULL') {
                    $field['hasDefault'] = true;
                    $field['defaultValue'] = $d;
                } else {
                    $field['hasDefault'] = false;
                }
            }
            $retarr[strtoupper($field['name'])] = $field;

        }
        return $retarr;
    }

    function dbTimeStamp($timestamp)
    {
        return date('Y-m-d H:i:s', $timestamp);
    }

    function startTrans()
    {
    }

    function completeTrans($commitOnNoErrors = true)
    {
        return false;
    }

    function failTrans()
    {
        $this->_transCommit = false;
    }

    function hasFailedTrans()
    {
        return true;
    }

    function _prepareSql($sql, & $inputarr)
    {
        $sqlarr = explode('?', $sql);
        $sql = '';
        $ix = 0;
        foreach ($inputarr as $v) {
            $sql .= $sqlarr[$ix];
            $typ = gettype($v);
            if ($typ == 'string') {
                $sql .= $this->qstr($v);
            } else if ($typ == 'double') {
                $sql .= $this->qstr(str_replace(',', '.', $v));
            } else if ($typ == 'boolean') {
                $sql .= $v ? $this->TRUE_VALUE : $this->FALSE_VALUE;
            } else if (is_null($v)) {
                $sql .= 'NULL';
            } else {
                $sql .= $v;
            }
            $ix += 1;
        }
        if (isset($sqlarr[$ix])) {
            $sql .= $sqlarr[$ix];
        }
        return $sql;
    }
}

---