includes/DatabaseSqlite.php

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