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