MediaWiki  REL1_22
DatabaseSqlite.php
Go to the documentation of this file.
00001 <?php
00028 class DatabaseSqlite extends DatabaseBase {
00029 
00030     private static $fulltextEnabled = null;
00031 
00032     var $mAffectedRows;
00033     var $mLastResult;
00034     var $mDatabaseFile;
00035     var $mName;
00036 
00040     protected $mConn;
00041 
00051     function __construct( $server = false, $user = false, $password = false, $dbName = false, $flags = 0 ) {
00052         $this->mName = $dbName;
00053         parent::__construct( $server, $user, $password, $dbName, $flags );
00054         // parent doesn't open when $user is false, but we can work with $dbName
00055         if ( $dbName && !$this->isOpen() ) {
00056             global $wgSharedDB;
00057             if ( $this->open( $server, $user, $password, $dbName ) && $wgSharedDB ) {
00058                 $this->attachDatabase( $wgSharedDB );
00059             }
00060         }
00061     }
00062 
00066     function getType() {
00067         return 'sqlite';
00068     }
00069 
00075     function implicitGroupby() {
00076         return false;
00077     }
00078 
00090     function open( $server, $user, $pass, $dbName ) {
00091         global $wgSQLiteDataDir;
00092 
00093         $this->close();
00094         $fileName = self::generateFileName( $wgSQLiteDataDir, $dbName );
00095         if ( !is_readable( $fileName ) ) {
00096             $this->mConn = false;
00097             throw new DBConnectionError( $this, "SQLite database not accessible" );
00098         }
00099         $this->openFile( $fileName );
00100         return $this->mConn;
00101     }
00102 
00111     function openFile( $fileName ) {
00112         $this->mDatabaseFile = $fileName;
00113         try {
00114             if ( $this->mFlags & DBO_PERSISTENT ) {
00115                 $this->mConn = new PDO( "sqlite:$fileName", '', '',
00116                     array( PDO::ATTR_PERSISTENT => true ) );
00117             } else {
00118                 $this->mConn = new PDO( "sqlite:$fileName", '', '' );
00119             }
00120         } catch ( PDOException $e ) {
00121             $err = $e->getMessage();
00122         }
00123         if ( !$this->mConn ) {
00124             wfDebug( "DB connection error: $err\n" );
00125             throw new DBConnectionError( $this, $err );
00126         }
00127         $this->mOpened = !!$this->mConn;
00128         # set error codes only, don't raise exceptions
00129         if ( $this->mOpened ) {
00130             $this->mConn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
00131             # Enforce LIKE to be case sensitive, just like MySQL
00132             $this->query( 'PRAGMA case_sensitive_like = 1' );
00133             return true;
00134         }
00135     }
00136 
00141     protected function closeConnection() {
00142         $this->mConn = null;
00143         return true;
00144     }
00145 
00152     public static function generateFileName( $dir, $dbName ) {
00153         return "$dir/$dbName.sqlite";
00154     }
00155 
00160     function checkForEnabledSearch() {
00161         if ( self::$fulltextEnabled === null ) {
00162             self::$fulltextEnabled = false;
00163             $table = $this->tableName( 'searchindex' );
00164             $res = $this->query( "SELECT sql FROM sqlite_master WHERE tbl_name = '$table'", __METHOD__ );
00165             if ( $res ) {
00166                 $row = $res->fetchRow();
00167                 self::$fulltextEnabled = stristr( $row['sql'], 'fts' ) !== false;
00168             }
00169         }
00170         return self::$fulltextEnabled;
00171     }
00172 
00177     static function getFulltextSearchModule() {
00178         static $cachedResult = null;
00179         if ( $cachedResult !== null ) {
00180             return $cachedResult;
00181         }
00182         $cachedResult = false;
00183         $table = 'dummy_search_test';
00184 
00185         $db = new DatabaseSqliteStandalone( ':memory:' );
00186 
00187         if ( $db->query( "CREATE VIRTUAL TABLE $table USING FTS3(dummy_field)", __METHOD__, true ) ) {
00188             $cachedResult = 'FTS3';
00189         }
00190         $db->close();
00191         return $cachedResult;
00192     }
00193 
00204     function attachDatabase( $name, $file = false, $fname = __METHOD__ ) {
00205         global $wgSQLiteDataDir;
00206         if ( !$file ) {
00207             $file = self::generateFileName( $wgSQLiteDataDir, $name );
00208         }
00209         $file = $this->addQuotes( $file );
00210         return $this->query( "ATTACH DATABASE $file AS $name", $fname );
00211     }
00212 
00220     function isWriteQuery( $sql ) {
00221         return parent::isWriteQuery( $sql ) && !preg_match( '/^ATTACH\b/i', $sql );
00222     }
00223 
00231     protected function doQuery( $sql ) {
00232         $res = $this->mConn->query( $sql );
00233         if ( $res === false ) {
00234             return false;
00235         } else {
00236             $r = $res instanceof ResultWrapper ? $res->result : $res;
00237             $this->mAffectedRows = $r->rowCount();
00238             $res = new ResultWrapper( $this, $r->fetchAll() );
00239         }
00240         return $res;
00241     }
00242 
00246     function freeResult( $res ) {
00247         if ( $res instanceof ResultWrapper ) {
00248             $res->result = null;
00249         } else {
00250             $res = null;
00251         }
00252     }
00253 
00258     function fetchObject( $res ) {
00259         if ( $res instanceof ResultWrapper ) {
00260             $r =& $res->result;
00261         } else {
00262             $r =& $res;
00263         }
00264 
00265         $cur = current( $r );
00266         if ( is_array( $cur ) ) {
00267             next( $r );
00268             $obj = new stdClass;
00269             foreach ( $cur as $k => $v ) {
00270                 if ( !is_numeric( $k ) ) {
00271                     $obj->$k = $v;
00272                 }
00273             }
00274 
00275             return $obj;
00276         }
00277         return false;
00278     }
00279 
00284     function fetchRow( $res ) {
00285         if ( $res instanceof ResultWrapper ) {
00286             $r =& $res->result;
00287         } else {
00288             $r =& $res;
00289         }
00290         $cur = current( $r );
00291         if ( is_array( $cur ) ) {
00292             next( $r );
00293             return $cur;
00294         }
00295         return false;
00296     }
00297 
00305     function numRows( $res ) {
00306         $r = $res instanceof ResultWrapper ? $res->result : $res;
00307         return count( $r );
00308     }
00309 
00314     function numFields( $res ) {
00315         $r = $res instanceof ResultWrapper ? $res->result : $res;
00316         return is_array( $r ) ? count( $r[0] ) : 0;
00317     }
00318 
00324     function fieldName( $res, $n ) {
00325         $r = $res instanceof ResultWrapper ? $res->result : $res;
00326         if ( is_array( $r ) ) {
00327             $keys = array_keys( $r[0] );
00328             return $keys[$n];
00329         }
00330         return false;
00331     }
00332 
00340     function tableName( $name, $format = 'quoted' ) {
00341         // table names starting with sqlite_ are reserved
00342         if ( strpos( $name, 'sqlite_' ) === 0 ) {
00343             return $name;
00344         }
00345         return str_replace( '"', '', parent::tableName( $name, $format ) );
00346     }
00347 
00355     function indexName( $index ) {
00356         return $index;
00357     }
00358 
00364     function insertId() {
00365         // PDO::lastInsertId yields a string :(
00366         return intval( $this->mConn->lastInsertId() );
00367     }
00368 
00373     function dataSeek( $res, $row ) {
00374         if ( $res instanceof ResultWrapper ) {
00375             $r =& $res->result;
00376         } else {
00377             $r =& $res;
00378         }
00379         reset( $r );
00380         if ( $row > 0 ) {
00381             for ( $i = 0; $i < $row; $i++ ) {
00382                 next( $r );
00383             }
00384         }
00385     }
00386 
00390     function lastError() {
00391         if ( !is_object( $this->mConn ) ) {
00392             return "Cannot return last error, no db connection";
00393         }
00394         $e = $this->mConn->errorInfo();
00395         return isset( $e[2] ) ? $e[2] : '';
00396     }
00397 
00401     function lastErrno() {
00402         if ( !is_object( $this->mConn ) ) {
00403             return "Cannot return last error, no db connection";
00404         } else {
00405             $info = $this->mConn->errorInfo();
00406             return $info[1];
00407         }
00408     }
00409 
00413     function affectedRows() {
00414         return $this->mAffectedRows;
00415     }
00416 
00424     function indexInfo( $table, $index, $fname = __METHOD__ ) {
00425         $sql = 'PRAGMA index_info(' . $this->addQuotes( $this->indexName( $index ) ) . ')';
00426         $res = $this->query( $sql, $fname );
00427         if ( !$res ) {
00428             return null;
00429         }
00430         if ( $res->numRows() == 0 ) {
00431             return false;
00432         }
00433         $info = array();
00434         foreach ( $res as $row ) {
00435             $info[] = $row->name;
00436         }
00437         return $info;
00438     }
00439 
00446     function indexUnique( $table, $index, $fname = __METHOD__ ) {
00447         $row = $this->selectRow( 'sqlite_master', '*',
00448             array(
00449                 'type' => 'index',
00450                 'name' => $this->indexName( $index ),
00451             ), $fname );
00452         if ( !$row || !isset( $row->sql ) ) {
00453             return null;
00454         }
00455 
00456         // $row->sql will be of the form CREATE [UNIQUE] INDEX ...
00457         $indexPos = strpos( $row->sql, 'INDEX' );
00458         if ( $indexPos === false ) {
00459             return null;
00460         }
00461         $firstPart = substr( $row->sql, 0, $indexPos );
00462         $options = explode( ' ', $firstPart );
00463         return in_array( 'UNIQUE', $options );
00464     }
00465 
00473     function makeSelectOptions( $options ) {
00474         foreach ( $options as $k => $v ) {
00475             if ( is_numeric( $k ) && ( $v == 'FOR UPDATE' || $v == 'LOCK IN SHARE MODE' ) ) {
00476                 $options[$k] = '';
00477             }
00478         }
00479         return parent::makeSelectOptions( $options );
00480     }
00481 
00486     function makeUpdateOptions( $options ) {
00487         $options = self::fixIgnore( $options );
00488         return parent::makeUpdateOptions( $options );
00489     }
00490 
00495     static function fixIgnore( $options ) {
00496         # SQLite uses OR IGNORE not just IGNORE
00497         foreach ( $options as $k => $v ) {
00498             if ( $v == 'IGNORE' ) {
00499                 $options[$k] = 'OR IGNORE';
00500             }
00501         }
00502         return $options;
00503     }
00504 
00509     function makeInsertOptions( $options ) {
00510         $options = self::fixIgnore( $options );
00511         return parent::makeInsertOptions( $options );
00512     }
00513 
00518     function insert( $table, $a, $fname = __METHOD__, $options = array() ) {
00519         if ( !count( $a ) ) {
00520             return true;
00521         }
00522 
00523         # SQLite can't handle multi-row inserts, so divide up into multiple single-row inserts
00524         if ( isset( $a[0] ) && is_array( $a[0] ) ) {
00525             $ret = true;
00526             foreach ( $a as $v ) {
00527                 if ( !parent::insert( $table, $v, "$fname/multi-row", $options ) ) {
00528                     $ret = false;
00529                 }
00530             }
00531         } else {
00532             $ret = parent::insert( $table, $a, "$fname/single-row", $options );
00533         }
00534 
00535         return $ret;
00536     }
00537 
00545     function replace( $table, $uniqueIndexes, $rows, $fname = __METHOD__ ) {
00546         if ( !count( $rows ) ) {
00547             return true;
00548         }
00549 
00550         # SQLite can't handle multi-row replaces, so divide up into multiple single-row queries
00551         if ( isset( $rows[0] ) && is_array( $rows[0] ) ) {
00552             $ret = true;
00553             foreach ( $rows as $v ) {
00554                 if ( !$this->nativeReplace( $table, $v, "$fname/multi-row" ) ) {
00555                     $ret = false;
00556                 }
00557             }
00558         } else {
00559             $ret = $this->nativeReplace( $table, $rows, "$fname/single-row" );
00560         }
00561 
00562         return $ret;
00563     }
00564 
00571     function textFieldSize( $table, $field ) {
00572         return -1;
00573     }
00574 
00578     function unionSupportsOrderAndLimit() {
00579         return false;
00580     }
00581 
00587     function unionQueries( $sqls, $all ) {
00588         $glue = $all ? ' UNION ALL ' : ' UNION ';
00589         return implode( $glue, $sqls );
00590     }
00591 
00595     function wasDeadlock() {
00596         return $this->lastErrno() == 5; // SQLITE_BUSY
00597     }
00598 
00602     function wasErrorReissuable() {
00603         return $this->lastErrno() == 17; // SQLITE_SCHEMA;
00604     }
00605 
00609     function wasReadOnlyError() {
00610         return $this->lastErrno() == 8; // SQLITE_READONLY;
00611     }
00612 
00616     public function getSoftwareLink() {
00617         return "[{{int:version-db-sqlite-url}} SQLite]";
00618     }
00619 
00623     function getServerVersion() {
00624         $ver = $this->mConn->getAttribute( PDO::ATTR_SERVER_VERSION );
00625         return $ver;
00626     }
00627 
00631     public function getServerInfo() {
00632         return wfMessage( self::getFulltextSearchModule() ? 'sqlite-has-fts' : 'sqlite-no-fts', $this->getServerVersion() )->text();
00633     }
00634 
00643     function fieldInfo( $table, $field ) {
00644         $tableName = $this->tableName( $table );
00645         $sql = 'PRAGMA table_info(' . $this->addQuotes( $tableName ) . ')';
00646         $res = $this->query( $sql, __METHOD__ );
00647         foreach ( $res as $row ) {
00648             if ( $row->name == $field ) {
00649                 return new SQLiteField( $row, $tableName );
00650             }
00651         }
00652         return false;
00653     }
00654 
00655     protected function doBegin( $fname = '' ) {
00656         if ( $this->mTrxLevel == 1 ) {
00657             $this->commit( __METHOD__ );
00658         }
00659         try {
00660             $this->mConn->beginTransaction();
00661         } catch ( PDOException $e ) {
00662             throw new DBUnexpectedError( $this, 'Error in BEGIN query: ' . $e->getMessage() );
00663         }
00664         $this->mTrxLevel = 1;
00665     }
00666 
00667     protected function doCommit( $fname = '' ) {
00668         if ( $this->mTrxLevel == 0 ) {
00669             return;
00670         }
00671         try {
00672             $this->mConn->commit();
00673         } catch ( PDOException $e ) {
00674             throw new DBUnexpectedError( $this, 'Error in COMMIT query: ' . $e->getMessage() );
00675         }
00676         $this->mTrxLevel = 0;
00677     }
00678 
00679     protected function doRollback( $fname = '' ) {
00680         if ( $this->mTrxLevel == 0 ) {
00681             return;
00682         }
00683         $this->mConn->rollBack();
00684         $this->mTrxLevel = 0;
00685     }
00686 
00691     function strencode( $s ) {
00692         return substr( $this->addQuotes( $s ), 1, - 1 );
00693     }
00694 
00699     function encodeBlob( $b ) {
00700         return new Blob( $b );
00701     }
00702 
00707     function decodeBlob( $b ) {
00708         if ( $b instanceof Blob ) {
00709             $b = $b->fetch();
00710         }
00711         return $b;
00712     }
00713 
00718     function addQuotes( $s ) {
00719         if ( $s instanceof Blob ) {
00720             return "x'" . bin2hex( $s->fetch() ) . "'";
00721         } elseif ( is_bool( $s ) ) {
00722             return (int)$s;
00723         } elseif ( strpos( $s, "\0" ) !== false ) {
00724             // SQLite doesn't support \0 in strings, so use the hex representation as a workaround.
00725             // This is a known limitation of SQLite's mprintf function which PDO should work around,
00726             // but doesn't. I have reported this to php.net as bug #63419:
00727             // https://bugs.php.net/bug.php?id=63419
00728             // There was already a similar report for SQLite3::escapeString, bug #62361:
00729             // https://bugs.php.net/bug.php?id=62361
00730             return "x'" . bin2hex( $s ) . "'";
00731         } else {
00732             return $this->mConn->quote( $s );
00733         }
00734     }
00735 
00739     function buildLike() {
00740         $params = func_get_args();
00741         if ( count( $params ) > 0 && is_array( $params[0] ) ) {
00742             $params = $params[0];
00743         }
00744         return parent::buildLike( $params ) . "ESCAPE '\' ";
00745     }
00746 
00750     public function getSearchEngine() {
00751         return "SearchSqlite";
00752     }
00753 
00758     public function deadlockLoop( /*...*/ ) {
00759         $args = func_get_args();
00760         $function = array_shift( $args );
00761         return call_user_func_array( $function, $args );
00762     }
00763 
00768     protected function replaceVars( $s ) {
00769         $s = parent::replaceVars( $s );
00770         if ( preg_match( '/^\s*(CREATE|ALTER) TABLE/i', $s ) ) {
00771             // CREATE TABLE hacks to allow schema file sharing with MySQL
00772 
00773             // binary/varbinary column type -> blob
00774             $s = preg_replace( '/\b(var)?binary(\(\d+\))/i', 'BLOB', $s );
00775             // no such thing as unsigned
00776             $s = preg_replace( '/\b(un)?signed\b/i', '', $s );
00777             // INT -> INTEGER
00778             $s = preg_replace( '/\b(tiny|small|medium|big|)int(\s*\(\s*\d+\s*\)|\b)/i', 'INTEGER', $s );
00779             // floating point types -> REAL
00780             $s = preg_replace( '/\b(float|double(\s+precision)?)(\s*\(\s*\d+\s*(,\s*\d+\s*)?\)|\b)/i', 'REAL', $s );
00781             // varchar -> TEXT
00782             $s = preg_replace( '/\b(var)?char\s*\(.*?\)/i', 'TEXT', $s );
00783             // TEXT normalization
00784             $s = preg_replace( '/\b(tiny|medium|long)text\b/i', 'TEXT', $s );
00785             // BLOB normalization
00786             $s = preg_replace( '/\b(tiny|small|medium|long|)blob\b/i', 'BLOB', $s );
00787             // BOOL -> INTEGER
00788             $s = preg_replace( '/\bbool(ean)?\b/i', 'INTEGER', $s );
00789             // DATETIME -> TEXT
00790             $s = preg_replace( '/\b(datetime|timestamp)\b/i', 'TEXT', $s );
00791             // No ENUM type
00792             $s = preg_replace( '/\benum\s*\([^)]*\)/i', 'TEXT', $s );
00793             // binary collation type -> nothing
00794             $s = preg_replace( '/\bbinary\b/i', '', $s );
00795             // auto_increment -> autoincrement
00796             $s = preg_replace( '/\bauto_increment\b/i', 'AUTOINCREMENT', $s );
00797             // No explicit options
00798             $s = preg_replace( '/\)[^);]*(;?)\s*$/', ')\1', $s );
00799             // AUTOINCREMENT should immedidately follow PRIMARY KEY
00800             $s = preg_replace( '/primary key (.*?) autoincrement/i', 'PRIMARY KEY AUTOINCREMENT $1', $s );
00801         } elseif ( preg_match( '/^\s*CREATE (\s*(?:UNIQUE|FULLTEXT)\s+)?INDEX/i', $s ) ) {
00802             // No truncated indexes
00803             $s = preg_replace( '/\(\d+\)/', '', $s );
00804             // No FULLTEXT
00805             $s = preg_replace( '/\bfulltext\b/i', '', $s );
00806         }
00807         return $s;
00808     }
00809 
00817     function buildConcat( $stringList ) {
00818         return '(' . implode( ') || (', $stringList ) . ')';
00819     }
00820 
00829     function duplicateTableStructure( $oldName, $newName, $temporary = false, $fname = __METHOD__ ) {
00830         $res = $this->query( "SELECT sql FROM sqlite_master WHERE tbl_name=" . $this->addQuotes( $oldName ) . " AND type='table'", $fname );
00831         $obj = $this->fetchObject( $res );
00832         if ( !$obj ) {
00833             throw new MWException( "Couldn't retrieve structure for table $oldName" );
00834         }
00835         $sql = $obj->sql;
00836         $sql = preg_replace( '/(?<=\W)"?' . preg_quote( trim( $this->addIdentifierQuotes( $oldName ), '"' ) ) . '"?(?=\W)/', $this->addIdentifierQuotes( $newName ), $sql, 1 );
00837         if ( $temporary ) {
00838             if ( preg_match( '/^\\s*CREATE\\s+VIRTUAL\\s+TABLE\b/i', $sql ) ) {
00839                 wfDebug( "Table $oldName is virtual, can't create a temporary duplicate.\n" );
00840             } else {
00841                 $sql = str_replace( 'CREATE TABLE', 'CREATE TEMPORARY TABLE', $sql );
00842             }
00843         }
00844         return $this->query( $sql, $fname );
00845     }
00846 
00855     function listTables( $prefix = null, $fname = __METHOD__ ) {
00856         $result = $this->select(
00857             'sqlite_master',
00858             'name',
00859             "type='table'"
00860         );
00861 
00862         $endArray = array();
00863 
00864         foreach ( $result as $table ) {
00865             $vars = get_object_vars( $table );
00866             $table = array_pop( $vars );
00867 
00868             if ( !$prefix || strpos( $table, $prefix ) === 0 ) {
00869                 if ( strpos( $table, 'sqlite_' ) !== 0 ) {
00870                     $endArray[] = $table;
00871                 }
00872 
00873             }
00874         }
00875 
00876         return $endArray;
00877     }
00878 
00879 } // end DatabaseSqlite class
00880 
00885 class DatabaseSqliteStandalone extends DatabaseSqlite {
00886     public function __construct( $fileName, $flags = 0 ) {
00887         $this->mFlags = $flags;
00888         $this->tablePrefix( null );
00889         $this->openFile( $fileName );
00890     }
00891 }
00892 
00896 class SQLiteField implements Field {
00897     private $info, $tableName;
00898     function __construct( $info, $tableName ) {
00899         $this->info = $info;
00900         $this->tableName = $tableName;
00901     }
00902 
00903     function name() {
00904         return $this->info->name;
00905     }
00906 
00907     function tableName() {
00908         return $this->tableName;
00909     }
00910 
00911     function defaultValue() {
00912         if ( is_string( $this->info->dflt_value ) ) {
00913             // Typically quoted
00914             if ( preg_match( '/^\'(.*)\'$', $this->info->dflt_value ) ) {
00915                 return str_replace( "''", "'", $this->info->dflt_value );
00916             }
00917         }
00918         return $this->info->dflt_value;
00919     }
00920 
00924     function isNullable() {
00925         return !$this->info->notnull;
00926     }
00927 
00928     function type() {
00929         return $this->info->type;
00930     }
00931 
00932 } // end SQLiteField