lib/adodb/drivers/adodb-sqlite.inc.php

   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 }