[Summary view]

Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403]

   1  <?php
   2  /**
   3   * SQLite driver
   4   *
   5   * @link https://www.sqlite.org/
   6   *
   7   * @deprecated Use SQLite3 driver instead
   8   *
   9   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
  10   *
  11   * @package ADOdb
  12   * @link https://adodb.org Project's web site and documentation
  13   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  14   *
  15   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  16   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  17   * any later version. This means you can use it in proprietary products.
  18   * See the LICENSE.md file distributed with this source code for details.
  19   * @license BSD-3-Clause
  20   * @license LGPL-2.1-or-later
  21   *
  22   * @copyright 2000-2013 John Lim
  23   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  24   */
  25  
  26  // security - hide paths
  27  if (!defined('ADODB_DIR')) die();
  28  
  29  class ADODB_sqlite extends ADOConnection {
  30  	 var $databaseType = "sqlite";
  31  	 var $dataProvider = "sqlite";
  32  	 var $replaceQuote = "''"; // string to use to replace quotes
  33  	 var $concat_operator='||';
  34  	 var $_errorNo = 0;
  35  	 var $hasLimit = true;
  36  	 var $hasInsertID = true; 	 	 /// supports autoincrement ID?
  37  	 var $hasAffectedRows = true; 	 /// supports affected rows for update/delete?
  38  	 var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
  39  	 var $sysDate = "adodb_date('Y-m-d')";
  40  	 var $sysTimeStamp = "adodb_date('Y-m-d H:i:s')";
  41  	 var $fmtTimeStamp = "'Y-m-d H:i:s'";
  42  
  43  	function ServerInfo()
  44  	 {
  45  	 	 $arr['version'] = sqlite_libversion();
  46  	 	 $arr['description'] = 'SQLite ';
  47  	 	 $arr['encoding'] = sqlite_libencoding();
  48  	 	 return $arr;
  49  	 }
  50  
  51  	function BeginTrans()
  52  	 {
  53  	 	 if ($this->transOff) {
  54  	 	 	 return true;
  55  	 	 }
  56  	 	 $ret = $this->Execute("BEGIN TRANSACTION");
  57  	 	 $this->transCnt += 1;
  58  	 	 return true;
  59  	 }
  60  
  61  	function CommitTrans($ok=true)
  62  	 {
  63  	 	 if ($this->transOff) {
  64  	 	 	 return true;
  65  	 	 }
  66  	 	 if (!$ok) {
  67  	 	 	 return $this->RollbackTrans();
  68  	 	 }
  69  	 	 $ret = $this->Execute("COMMIT");
  70  	 	 if ($this->transCnt > 0) {
  71  	 	 	 $this->transCnt -= 1;
  72  	 	 }
  73  	 	 return !empty($ret);
  74  	 }
  75  
  76  	function RollbackTrans()
  77  	 {
  78  	 	 if ($this->transOff) {
  79  	 	 	 return true;
  80  	 	 }
  81  	 	 $ret = $this->Execute("ROLLBACK");
  82  	 	 if ($this->transCnt > 0) {
  83  	 	 	 $this->transCnt -= 1;
  84  	 	 }
  85  	 	 return !empty($ret);
  86  	 }
  87  
  88  	 // mark newnham
  89  	function MetaColumns($table, $normalize=true)
  90  	 {
  91  	 	 global $ADODB_FETCH_MODE;
  92  	 	 $false = false;
  93  	 	 $save = $ADODB_FETCH_MODE;
  94  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
  95  	 	 if ($this->fetchMode !== false) {
  96  	 	 	 $savem = $this->SetFetchMode(false);
  97  	 	 }
  98  	 	 $rs = $this->Execute("PRAGMA table_info('$table')");
  99  	 	 if (isset($savem)) {
 100  	 	 	 $this->SetFetchMode($savem);
 101  	 	 }
 102  	 	 if (!$rs) {
 103  	 	 	 $ADODB_FETCH_MODE = $save;
 104  	 	 	 return $false;
 105  	 	 }
 106  	 	 $arr = array();
 107  	 	 while ($r = $rs->FetchRow()) {
 108  	 	 	 $type = explode('(',$r['type']);
 109  	 	 	 $size = '';
 110  	 	 	 if (sizeof($type)==2) {
 111  	 	 	 	 $size = trim($type[1],')');
 112  	 	 	 }
 113  	 	 	 $fn = strtoupper($r['name']);
 114  	 	 	 $fld = new ADOFieldObject;
 115  	 	 	 $fld->name = $r['name'];
 116  	 	 	 $fld->type = $type[0];
 117  	 	 	 $fld->max_length = $size;
 118  	 	 	 $fld->not_null = $r['notnull'];
 119  	 	 	 $fld->default_value = $r['dflt_value'];
 120  	 	 	 $fld->scale = 0;
 121  	 	 	 if (isset($r['pk']) && $r['pk']) {
 122  	 	 	 	 $fld->primary_key=1;
 123  	 	 	 }
 124  	 	 	 if ($save == ADODB_FETCH_NUM) {
 125  	 	 	 	 $arr[] = $fld;
 126  	 	 	 } else {
 127  	 	 	 	 $arr[strtoupper($fld->name)] = $fld;
 128  	 	 	 }
 129  	 	 }
 130  	 	 $rs->Close();
 131  	 	 $ADODB_FETCH_MODE = $save;
 132  	 	 return $arr;
 133  	 }
 134  
 135  	function _init($parentDriver)
 136  	 {
 137  	 	 $parentDriver->hasTransactions = false;
 138  	 	 $parentDriver->hasInsertID = true;
 139  	 }
 140  
 141  	protected function _insertID($table = '', $column = '')
 142  	 {
 143  	 	 return sqlite_last_insert_rowid($this->_connectionID);
 144  	 }
 145  
 146  	function _affectedrows()
 147  	 {
 148  	 	 return sqlite_changes($this->_connectionID);
 149  	 }
 150  
 151  	function ErrorMsg()
 152   	 {
 153  	 	 if ($this->_logsql) {
 154  	 	 	 return $this->_errorMsg;
 155  	 	 }
 156  	 	 return ($this->_errorNo) ? sqlite_error_string($this->_errorNo) : '';
 157  	 }
 158  
 159  	function ErrorNo()
 160  	 {
 161  	 	 return $this->_errorNo;
 162  	 }
 163  
 164  	function SQLDate($fmt, $col=false)
 165  	 {
 166  	 	 $fmt = $this->qstr($fmt);
 167  	 	 return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
 168  	 }
 169  
 170  
 171  	function _createFunctions()
 172  	 {
 173  	 	 @sqlite_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1);
 174  	 	 @sqlite_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);
 175  	 }
 176  
 177  
 178  	 // returns true or false
 179  	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 180  	 {
 181  	 	 if (!function_exists('sqlite_open')) {
 182  	 	 	 return null;
 183  	 	 }
 184  	 	 if (empty($argHostname) && $argDatabasename) {
 185  	 	 	 $argHostname = $argDatabasename;
 186  	 	 }
 187  
 188  	 	 $this->_connectionID = sqlite_open($argHostname);
 189  	 	 if ($this->_connectionID === false) {
 190  	 	 	 return false;
 191  	 	 }
 192  	 	 $this->_createFunctions();
 193  	 	 return true;
 194  	 }
 195  
 196  	 // returns true or false
 197  	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 198  	 {
 199  	 	 if (!function_exists('sqlite_open')) {
 200  	 	 	 return null;
 201  	 	 }
 202  	 	 if (empty($argHostname) && $argDatabasename) {
 203  	 	 	 $argHostname = $argDatabasename;
 204  	 	 }
 205  
 206  	 	 $this->_connectionID = sqlite_popen($argHostname);
 207  	 	 if ($this->_connectionID === false) {
 208  	 	 	 return false;
 209  	 	 }
 210  	 	 $this->_createFunctions();
 211  	 	 return true;
 212  	 }
 213  
 214  	 // returns query ID if successful, otherwise false
 215  	function _query($sql,$inputarr=false)
 216  	 {
 217  	 	 $rez = sqlite_query($sql,$this->_connectionID);
 218  	 	 if (!$rez) {
 219  	 	 	 $this->_errorNo = sqlite_last_error($this->_connectionID);
 220  	 	 }
 221  	 	 // If no data was returned, we don't need to create a real recordset
 222  	 	 // Note: this code is untested, as I don't have a sqlite2 setup available
 223  	 	 elseif (sqlite_num_fields($rez) == 0) {
 224  	 	 	 $rez = true;
 225  	 	 }
 226  
 227  	 	 return $rez;
 228  	 }
 229  
 230  	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 231  	 {
 232  	 	 $nrows = (int) $nrows;
 233  	 	 $offset = (int) $offset;
 234  	 	 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 235  	 	 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 236  	 	 if ($secs2cache) {
 237  	 	 	 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 238  	 	 } else {
 239  	 	 	 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 240  	 	 }
 241  
 242  	 	 return $rs;
 243  	 }
 244  
 245  	 /*
 246  	 	 This algorithm is not very efficient, but works even if table locking
 247  	 	 is not available.
 248  
 249  	 	 Will return false if unable to generate an ID after $MAXLOOPS attempts.
 250  	 */
 251  	 var $_genSeqSQL = "create table %s (id integer)";
 252  
 253  	function GenID($seq='adodbseq',$start=1)
 254  	 {
 255  	 	 // if you have to modify the parameter below, your database is overloaded,
 256  	 	 // or you need to implement generation of id's yourself!
 257  	 	 $MAXLOOPS = 100;
 258  	 	 //$this->debug=1;
 259  	 	 while (--$MAXLOOPS>=0) {
 260  	 	 	 @($num = $this->GetOne("select id from $seq"));
 261  	 	 	 if ($num === false) {
 262  	 	 	 	 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 263  	 	 	 	 $start -= 1;
 264  	 	 	 	 $num = '0';
 265  	 	 	 	 $ok = $this->Execute("insert into $seq values($start)");
 266  	 	 	 	 if (!$ok) {
 267  	 	 	 	 	 return false;
 268  	 	 	 	 }
 269  	 	 	 }
 270  	 	 	 $this->Execute("update $seq set id=id+1 where id=$num");
 271  
 272  	 	 	 if ($this->affected_rows() > 0) {
 273  	 	 	 	 $num += 1;
 274  	 	 	 	 $this->genID = $num;
 275  	 	 	 	 return $num;
 276  	 	 	 }
 277  	 	 }
 278  	 	 if ($fn = $this->raiseErrorFn) {
 279  	 	 	 $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 280  	 	 }
 281  	 	 return false;
 282  	 }
 283  
 284  	function CreateSequence($seqname='adodbseq',$start=1)
 285  	 {
 286  	 	 if (empty($this->_genSeqSQL)) {
 287  	 	 	 return false;
 288  	 	 }
 289  	 	 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 290  	 	 if (!$ok) {
 291  	 	 	 return false;
 292  	 	 }
 293  	 	 $start -= 1;
 294  	 	 return $this->Execute("insert into $seqname values($start)");
 295  	 }
 296  
 297  	 var $_dropSeqSQL = 'drop table %s';
 298  	function DropSequence($seqname = 'adodbseq')
 299  	 {
 300  	 	 if (empty($this->_dropSeqSQL)) {
 301  	 	 	 return false;
 302  	 	 }
 303  	 	 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 304  	 }
 305  
 306  	 // returns true or false
 307  	function _close()
 308  	 {
 309  	 	 return @sqlite_close($this->_connectionID);
 310  	 }
 311  
 312  	function MetaIndexes($table, $primary = FALSE, $owner = false)
 313  	 {
 314  	 	 $false = false;
 315  	 	 // save old fetch mode
 316  	 	 global $ADODB_FETCH_MODE;
 317  	 	 $save = $ADODB_FETCH_MODE;
 318  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 319  	 	 if ($this->fetchMode !== FALSE) {
 320  	 	 	 $savem = $this->SetFetchMode(FALSE);
 321  	 	 }
 322  	 	 $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
 323  	 	 $rs = $this->Execute($SQL);
 324  	 	 if (!is_object($rs)) {
 325  	 	 	 if (isset($savem)) {
 326  	 	 	 	 $this->SetFetchMode($savem);
 327  	 	 	 }
 328  	 	 	 $ADODB_FETCH_MODE = $save;
 329  	 	 	 return $false;
 330  	 	 }
 331  
 332  	 	 $indexes = array ();
 333  	 	 while ($row = $rs->FetchRow()) {
 334  	 	 	 if ($primary && preg_match("/primary/i",$row[1]) == 0) {
 335  	 	 	 	 continue;
 336  	 	 	 }
 337  	 	 	 if (!isset($indexes[$row[0]])) {
 338  	 	 	 	 $indexes[$row[0]] = array(
 339  	 	 	 	 	 'unique' => preg_match("/unique/i",$row[1]),
 340  	 	 	 	 	 'columns' => array()
 341  	 	 	 	 );
 342  	 	 	 }
 343  	 	 	 /**
 344  	 	 	  * There must be a more elegant way of doing this,
 345  	 	 	  * the index elements appear in the SQL statement
 346  	 	 	  * in cols[1] between parentheses
 347  	 	 	  * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 348  	 	 	  */
 349  	 	 	 $cols = explode("(",$row[1]);
 350  	 	 	 $cols = explode(")",$cols[1]);
 351  	 	 	 array_pop($cols);
 352  	 	 	 $indexes[$row[0]]['columns'] = $cols;
 353  	 	 }
 354  	 	 if (isset($savem)) {
 355  	 	 	 $this->SetFetchMode($savem);
 356  	 	 	 $ADODB_FETCH_MODE = $save;
 357  	 	 }
 358  	 	 return $indexes;
 359  	 }
 360  
 361  	 /**
 362  	 * Returns the maximum size of a MetaType C field. Because of the
 363  	 * database design, sqlite places no limits on the size of data inserted
 364  	 *
 365  	 * @return int
 366  	 */
 367  	function charMax()
 368  	 {
 369  	 	 return ADODB_STRINGMAX_NOLIMIT;
 370  	 }
 371  
 372  	 /**
 373  	 * Returns the maximum size of a MetaType X field. Because of the
 374  	 * database design, sqlite places no limits on the size of data inserted
 375  	 *
 376  	 * @return int
 377  	 */
 378  	function textMax()
 379  	 {
 380  	 	 return ADODB_STRINGMAX_NOLIMIT;
 381  	 }
 382  
 383  	 /*
 384  	  * Converts a date to a month only field and pads it to 2 characters
 385  	  *
 386  	  * @param 	 str	 	 $fld	 The name of the field to process
 387  	  * @return	 str	 	 	 	 The SQL Statement
 388  	  */
 389  	function month($fld)
 390  	 {
 391  	 	 $x = "strftime('%m',$fld)";
 392  
 393  	 	 return $x;
 394  	 }
 395  
 396  	 /*
 397  	  * Converts a date to a day only field and pads it to 2 characters
 398  	  *
 399  	  * @param 	 str	 	 $fld	 The name of the field to process
 400  	  * @return	 str	 	 	 	 The SQL Statement
 401  	  */
 402  	function day($fld) {
 403  	 	 $x = "strftime('%d',$fld)";
 404  	 	 return $x;
 405  	 }
 406  
 407  	 /*
 408  	  * Converts a date to a year only field
 409  	  *
 410  	  * @param 	 str	 	 $fld	 The name of the field to process
 411  	  * @return	 str	 	 	 	 The SQL Statement
 412  	  */
 413  	function year($fld) {
 414  	 	 $x = "strftime('%Y',$fld)";
 415  
 416  	 	 return $x;
 417  	 }
 418  }
 419  
 420  /*--------------------------------------------------------------------------------------
 421  	 	 Class Name: Recordset
 422  --------------------------------------------------------------------------------------*/
 423  
 424  class ADORecordset_sqlite extends ADORecordSet {
 425  
 426  	 var $databaseType = "sqlite";
 427  	 var $bind = false;
 428  
 429  	function __construct($queryID,$mode=false)
 430  	 {
 431  
 432  	 	 if ($mode === false) {
 433  	 	 	 global $ADODB_FETCH_MODE;
 434  	 	 	 $mode = $ADODB_FETCH_MODE;
 435  	 	 }
 436  	 	 switch($mode) {
 437  	 	 	 case ADODB_FETCH_NUM:
 438  	 	 	 	 $this->fetchMode = SQLITE_NUM;
 439  	 	 	 	 break;
 440  	 	 	 case ADODB_FETCH_ASSOC:
 441  	 	 	 	 $this->fetchMode = SQLITE_ASSOC;
 442  	 	 	 	 break;
 443  	 	 	 default:
 444  	 	 	 	 $this->fetchMode = SQLITE_BOTH;
 445  	 	 	 	 break;
 446  	 	 }
 447  	 	 $this->adodbFetchMode = $mode;
 448  
 449  	 	 $this->_queryID = $queryID;
 450  
 451  	 	 $this->_inited = true;
 452  	 	 $this->fields = array();
 453  	 	 if ($queryID) {
 454  	 	 	 $this->_currentRow = 0;
 455  	 	 	 $this->EOF = !$this->_fetch();
 456  	 	 	 @$this->_initrs();
 457  	 	 } else {
 458  	 	 	 $this->_numOfRows = 0;
 459  	 	 	 $this->_numOfFields = 0;
 460  	 	 	 $this->EOF = true;
 461  	 	 }
 462  
 463  	 	 return $this->_queryID;
 464  	 }
 465  
 466  
 467  	function FetchField($fieldOffset = -1)
 468  	 {
 469  	 	 $fld = new ADOFieldObject;
 470  	 	 $fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
 471  	 	 $fld->type = 'VARCHAR';
 472  	 	 $fld->max_length = -1;
 473  	 	 return $fld;
 474  	 }
 475  
 476  	function _initrs()
 477  	 {
 478  	 	 $this->_numOfRows = @sqlite_num_rows($this->_queryID);
 479  	 	 $this->_numOfFields = @sqlite_num_fields($this->_queryID);
 480  	 }
 481  
 482  	function Fields($colname)
 483  	 {
 484  	 	 if ($this->fetchMode != SQLITE_NUM) {
 485  	 	 	 return $this->fields[$colname];
 486  	 	 }
 487  	 	 if (!$this->bind) {
 488  	 	 	 $this->bind = array();
 489  	 	 	 for ($i=0; $i < $this->_numOfFields; $i++) {
 490  	 	 	 	 $o = $this->FetchField($i);
 491  	 	 	 	 $this->bind[strtoupper($o->name)] = $i;
 492  	 	 	 }
 493  	 	 }
 494  
 495  	 	 return $this->fields[$this->bind[strtoupper($colname)]];
 496  	 }
 497  
 498  	function _seek($row)
 499  	 {
 500  	 	 return sqlite_seek($this->_queryID, $row);
 501  	 }
 502  
 503  	function _fetch($ignore_fields=false)
 504  	 {
 505  	 	 $this->fields = @sqlite_fetch_array($this->_queryID,$this->fetchMode);
 506  	 	 return !empty($this->fields);
 507  	 }
 508  
 509  	function _close()
 510  	 {
 511  	 }
 512  
 513  }