includes/DatabaseSqlite.php

   1 <?php
   2 /**
   3  * This script is the SQLite database abstraction layer
   4  *
   5  * See maintenance/sqlite/README for development notes and other specific information
   6  * @ingroup Database
   7  * @file
   8  */
   9
  10 /**
  11  * @ingroup Database
  12  */
  13 class DatabaseSqlite extends Database {
  14
  15         var $mAffectedRows;
  16         var $mLastResult;
  17         var $mDatabaseFile;
  18
  19         /**
  20          * Constructor
  21          */
  22         function __construct($server = false, $user = false, $password = false, $dbName = false, $failFunction = false, $flags = 0) {
  23                 global $wgOut,$wgSQLiteDataDir;
  24                 if ("$wgSQLiteDataDir" == '') $wgSQLiteDataDir = dirname($_SERVER['DOCUMENT_ROOT']).'/data';
  25                 if (!is_dir($wgSQLiteDataDir)) mkdir($wgSQLiteDataDir,0700);
  26                 if (!isset($wgOut)) $wgOut = NULL; # Can't get a reference if it hasn't been set yet
  27                 $this->mOut =& $wgOut;
  28                 $this->mFailFunction = $failFunction;
  29                 $this->mFlags = $flags;
  30                 $this->mDatabaseFile = "$wgSQLiteDataDir/$dbName.sqlite";
  31                 $this->open($server, $user, $password, $dbName);
  32         }
  33
  34         /**
  35          * todo: check if these should be true like parent class
  36          */
  37         function implicitGroupby()   { return false; }
  38         function implicitOrderby()   { return false; }
  39
  40         static function newFromParams($server, $user, $password, $dbName, $failFunction = false, $flags = 0) {
  41                 return new DatabaseSqlite($server, $user, $password, $dbName, $failFunction, $flags);
  42         }
  43
  44         /** Open an SQLite database and return a resource handle to it
  45          *  NOTE: only $dbName is used, the other parameters are irrelevant for SQLite databases
  46          */
  47         function open($server,$user,$pass,$dbName) {
  48                 $this->mConn = false;
  49                 if ($dbName) {
  50                         $file = $this->mDatabaseFile;
  51                         if ($this->mFlags & DBO_PERSISTENT) $this->mConn = new PDO("sqlite:$file",$user,$pass,array(PDO::ATTR_PERSISTENT => true));
  52                         else $this->mConn = new PDO("sqlite:$file",$user,$pass);
  53                         if ($this->mConn === false) wfDebug("DB connection error: $err\n");;
  54                         $this->mOpened = $this->mConn;
  55                         $this->mConn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT); # set error codes only, dont raise exceptions
  56                 }
  57                 return $this->mConn;
  58         }
  59
  60         /**
  61          * Close an SQLite database
  62          */
  63         function close() {
  64                 $this->mOpened = false;
  65                 if (is_object($this->mConn)) {
  66                         if ($this->trxLevel()) $this->immediateCommit();
  67                         $this->mConn = null;
  68                 }
  69                 return true;
  70         }
  71
  72         /**
  73          * SQLite doesn't allow buffered results or data seeking etc, so we'll use fetchAll as the result
  74          */
  75         function doQuery($sql) {
  76                 $res = $this->mConn->query($sql);
  77                 if ($res === false) $this->reportQueryError($this->lastError(),$this->lastErrno(),$sql,__FUNCTION__);
  78                 else {
  79                         $r = $res instanceof ResultWrapper ? $res->result : $res;
  80                         $this->mAffectedRows = $r->rowCount();
  81                         $res = new ResultWrapper($this,$r->fetchAll());
  82                 }
  83                 return $res;
  84         }
  85
  86         function freeResult(&$res) {
  87                 if ($res instanceof ResultWrapper) $res->result = NULL; else $res = NULL;
  88         }
  89
  90         function fetchObject(&$res) {
  91                 if ($res instanceof ResultWrapper) $r =& $res->result; else $r =& $res;
  92                 $cur = current($r);
  93                 if (is_array($cur)) {
  94                         next($r);
  95                         $obj = new stdClass;
  96                         foreach ($cur as $k => $v) if (!is_numeric($k)) $obj->$k = $v;
  97                         return $obj;
  98                 }
  99                 return false;
 100         }
 101
 102         function fetchRow(&$res) {
 103                 if ($res instanceof ResultWrapper) $r =& $res->result; else $r =& $res;
 104                 $cur = current($r);
 105                 if (is_array($cur)) {
 106                         next($r);
 107                         return $cur;
 108                 }
 109                 return false;
 110         }
 111
 112         /**
 113          * The PDO::Statement class implements the array interface so count() will work
 114          */
 115         function numRows(&$res) {
 116                 $r = $res instanceof ResultWrapper ? $res->result : $res;
 117                 return count($r);
 118         }
 119
 120         function numFields(&$res) {
 121                 $r = $res instanceof ResultWrapper ? $res->result : $res;
 122                 return is_array($r) ? count($r[0]) : 0;
 123         }
 124
 125         function fieldName(&$res,$n) {
 126                 $r = $res instanceof ResultWrapper ? $res->result : $res;
 127                 if (is_array($r)) {
 128                         $keys = array_keys($r[0]);
 129                         return $keys[$n];
 130                 }
 131                 return  false;
 132         }
 133
 134         /**
 135          * Use MySQL's naming (accounts for prefix etc) but remove surrounding backticks
 136          */
 137         function tableName($name) {
 138                 return str_replace('`','',parent::tableName($name));
 139         }
 140
 141         /**
 142          * This must be called after nextSequenceVal
 143          */
 144         function insertId() {
 145                 return $this->mConn->lastInsertId();
 146         }
 147
 148         function dataSeek(&$res,$row) {
 149                 if ($res instanceof ResultWrapper) $r =& $res->result; else $r =& $res;
 150                 reset($r);
 151                 if ($row > 0) for ($i = 0; $i < $row; $i++) next($r);
 152         }
 153
 154         function lastError() {
 155                 if (!is_object($this->mConn)) return "Cannot return last error, no db connection";
 156                 $e = $this->mConn->errorInfo();
 157                 return isset($e[2]) ? $e[2] : '';
 158         }
 159
 160         function lastErrno() {
 161                 if (!is_object($this->mConn)) return "Cannot return last error, no db connection";
 162                 return $this->mConn->errorCode();
 163         }
 164
 165         function affectedRows() {
 166                 return $this->mAffectedRows;
 167         }
 168
 169         /**
 170          * Returns information about an index
 171          * - if errors are explicitly ignored, returns NULL on failure
 172          */
 173         function indexInfo($table, $index, $fname = 'Database::indexExists') {
 174                 return false;
 175         }
 176
 177         function indexUnique($table, $index, $fname = 'Database::indexUnique') {
 178                 return false;
 179         }
 180
 181         /**
 182          * Filter the options used in SELECT statements
 183          */
 184         function makeSelectOptions($options) {
 185                 foreach ($options as $k => $v) if (is_numeric($k) && $v == 'FOR UPDATE') $options[$k] = '';
 186                 return parent::makeSelectOptions($options);
 187         }
 188
 189         /**
 190          * Based on MySQL method (parent) with some prior SQLite-sepcific adjustments
 191          */
 192         function insert($table, $a, $fname = 'DatabaseSqlite::insert', $options = array()) {
 193                 if (!count($a)) return true;
 194                 if (!is_array($options)) $options = array($options);
 195
 196                 # SQLite uses OR IGNORE not just IGNORE
 197                 foreach ($options as $k => $v) if ($v == 'IGNORE') $options[$k] = 'OR IGNORE';
 198
 199                 # SQLite can't handle multi-row inserts, so divide up into multiple single-row inserts
 200                 if (isset($a[0]) && is_array($a[0])) {
 201                         $ret = true;
 202                         foreach ($a as $k => $v) if (!parent::insert($table,$v,"$fname/multi-row",$options)) $ret = false;
 203                 }
 204                 else $ret = parent::insert($table,$a,"$fname/single-row",$options);
 205
 206                 return $ret;
 207         }
 208
 209         /**
 210          * SQLite does not have a "USE INDEX" clause, so return an empty string
 211          */
 212         function useIndexClause($index) {
 213                 return '';
 214         }
 215
 216         # Returns the size of a text field, or -1 for "unlimited"
 217         function textFieldSize($table, $field) {
 218                 return -1;
 219         }
 220
 221         /**
 222          * No low priority option in SQLite
 223          */
 224         function lowPriorityOption() {
 225                 return '';
 226         }
 227
 228         /**
 229          * Returns an SQL expression for a simple conditional.
 230          * - uses CASE on SQLite
 231          */
 232         function conditional($cond, $trueVal, $falseVal) {
 233                 return " (CASE WHEN $cond THEN $trueVal ELSE $falseVal END) ";
 234         }
 235
 236         function wasDeadlock() {
 237                 return $this->lastErrno() == SQLITE_BUSY;
 238         }
 239
 240         /**
 241          * @return string wikitext of a link to the server software's web site
 242          */
 243         function getSoftwareLink() {
 244                 return "[http://sqlite.org/ SQLite]";
 245         }
 246
 247         /**
 248          * @return string Version information from the database
 249          */
 250         function getServerVersion() {
 251                 global $wgContLang;
 252                 $ver = $this->mConn->getAttribute(PDO::ATTR_SERVER_VERSION);
 253                 $size = $wgContLang->formatSize(filesize($this->mDatabaseFile));
 254                 $file = basename($this->mDatabaseFile);
 255                 return $ver." ($file: $size)";
 256         }
 257
 258         /**
 259          * Query whether a given column exists in the mediawiki schema
 260          */
 261         function fieldExists($table, $field) { return true; }
 262
 263         function fieldInfo($table, $field) { return SQLiteField::fromText($this, $table, $field); }
 264
 265         function begin() {
 266                 if ($this->mTrxLevel == 1) $this->commit();
 267                 $this->mConn->beginTransaction();
 268                 $this->mTrxLevel = 1;
 269         }
 270
 271         function commit() {
 272                 if ($this->mTrxLevel == 0) return;
 273                 $this->mConn->commit();
 274                 $this->mTrxLevel = 0;
 275         }
 276
 277         function rollback() {
 278                 if ($this->mTrxLevel == 0) return;
 279                 $this->mConn->rollBack();
 280                 $this->mTrxLevel = 0;
 281         }
 282
 283         function limitResultForUpdate($sql, $num) {
 284                 return $sql;
 285         }
 286
 287         function strencode($s) {
 288                 return substr($this->addQuotes($s),1,-1);
 289         }
 290
 291         function encodeBlob($b) {
 292                 return $this->strencode($b);
 293         }
 294
 295         function decodeBlob($b) {
 296                 return $b;
 297         }
 298
 299         function addQuotes($s) {
 300                 return $this->mConn->quote($s);
 301         }
 302
 303         function quote_ident($s) { return $s; }
 304
 305         /**
 306          * For now, does nothing
 307          */
 308         function selectDB($db) { return true; }
 309
 310         /**
 311          * not done
 312          */
 313         public function setTimeout($timeout) { return; }
 314
 315         function ping() {
 316                 wfDebug("Function ping() not written for SQLite yet");
 317                 return true;
 318         }
 319
 320         /**
 321          * How lagged is this slave?
 322          */
 323         public function getLag() {
 324                 return 0;
 325         }
 326
 327         /**
 328          * Called by the installer script (when modified according to the MediaWikiLite installation instructions)
 329          * - this is the same way PostgreSQL works, MySQL reads in tables.sql and interwiki.sql using dbsource (which calls db->sourceFile)
 330          */
 331         public function setup_database() {
 332                 global $IP,$wgSQLiteDataDir,$wgDBTableOptions;
 333                 $wgDBTableOptions = '';
 334                 $mysql_tmpl  = "$IP/maintenance/tables.sql";
 335                 $mysql_iw    = "$IP/maintenance/interwiki.sql";
 336                 $sqlite_tmpl = "$IP/maintenance/sqlite/tables.sql";
 337
 338                 # Make an SQLite template file if it doesn't exist (based on the same one MySQL uses to create a new wiki db)
 339                 if (!file_exists($sqlite_tmpl)) {
 340                         $sql = file_get_contents($mysql_tmpl);
 341                         $sql = preg_replace('/^\s*--.*?$/m','',$sql); # strip comments
 342                         $sql = preg_replace('/^\s*(UNIQUE)?\s*(PRIMARY)?\s*KEY.+?$/m','',$sql);
 343                         $sql = preg_replace('/^\s*(UNIQUE )?INDEX.+?$/m','',$sql); # These indexes should be created with a CREATE INDEX query
 344                         $sql = preg_replace('/^\s*FULLTEXT.+?$/m','',$sql);        # Full text indexes
 345                         $sql = preg_replace('/ENUM\(.+?\)/','TEXT',$sql); # Make ENUM's into TEXT's
 346                         $sql = preg_replace('/binary\(\d+\)/','BLOB',$sql);
 347                         $sql = preg_replace('/(TYPE|MAX_ROWS|AVG_ROW_LENGTH)=\w+/','',$sql);
 348                         $sql = preg_replace('/,\s*\)/s',')',$sql); # removing previous items may leave a trailing comma
 349                         $sql = str_replace('binary','',$sql);
 350                         $sql = str_replace('auto_increment','PRIMARY KEY AUTOINCREMENT',$sql);
 351                         $sql = str_replace(' unsigned','',$sql);
 352                         $sql = str_replace(' int ',' INTEGER ',$sql);
 353                         $sql = str_replace('NOT NULL','',$sql);
 354
 355                         # Tidy up and write file
 356                         $sql = preg_replace('/^\s*^/m','',$sql); # Remove empty lines
 357                         $sql = preg_replace('/;$/m',";\n",$sql); # Separate each statement with an empty line
 358                         file_put_contents($sqlite_tmpl,$sql);
 359                 }
 360
 361                 # Parse the SQLite template replacing inline variables such as /*$wgDBprefix*/
 362                 $err = $this->sourceFile($sqlite_tmpl);
 363                 if ($err !== true) $this->reportQueryError($err,0,$sql,__FUNCTION__);
 364
 365                 # Use DatabasePostgres's code to populate interwiki from MySQL template
 366                 $f = fopen($mysql_iw,'r');
 367                 if ($f == false) dieout("<li>Could not find the interwiki.sql file");
 368                 $sql = "INSERT INTO interwiki(iw_prefix,iw_url,iw_local) VALUES ";
 369                 while (!feof($f)) {
 370                         $line = fgets($f,1024);
 371                         $matches = array();
 372                         if (!preg_match('/^\s*(\(.+?),(\d)\)/', $line, $matches)) continue;
 373                         $this->query("$sql $matches[1],$matches[2])");
 374                 }
 375         }
 376
 377 }
 378
 379 /**
 380  * @ingroup Database
 381  */
 382 class SQLiteField extends MySQLField {
 383
 384         function __construct() {
 385         }
 386
 387         static function fromText($db, $table, $field) {
 388                 $n = new SQLiteField;
 389                 $n->name = $field;
 390                 $n->tablename = $table;
 391                 return $n;
 392         }
 393
 394 } // end DatabaseSqlite class
 395