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

   1 <?php
   2 /*
   3 V5.19  23-Apr-2014  (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.
   4   Released under both BSD license and Lesser GPL library license.
   5   Whenever there is any discrepancy between the two licenses,
   6   the BSD license will take precedence.
   7
   8   Latest version is available at http://adodb.sourceforge.net
   9
  10   SQLite info: http://www.hwaci.com/sw/sqlite/
  11
  12   Install Instructions:
  13   ====================
  14   1. Place this in adodb/drivers
  15   2. Rename the file, remove the .txt prefix.
  16 */
  17
  18 // security - hide paths
  19 if (!defined('ADODB_DIR')) die();
  20
  21 // class ADODB_sqlite extends ADOConnection { **change
  22 class ADODB_sqlite3 extends ADOConnection {
  23         //var $databaseType = "sqlite"; **change
  24         var $databaseType = "sqlite3";
  25         var $replaceQuote = "''"; // string to use to replace quotes
  26         var $concat_operator='||';
  27         var $_errorNo = 0;
  28         var $hasLimit = true;
  29         var $hasInsertID = true;                /// supports autoincrement ID?
  30         var $hasAffectedRows = true;    /// supports affected rows for update/delete?
  31         var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
  32         var $sysDate = "adodb_date('Y-m-d')";
  33         var $sysTimeStamp = "adodb_date('Y-m-d H:i:s')";
  34         var $fmtTimeStamp = "'Y-m-d H:i:s'";
  35
  36         //function ADODB_sqlite3() **change
  37         function ADODB_sqlite3()
  38         {
  39         }
  40
  41 /*
  42   function __get($name)
  43   {
  44         switch($name) {
  45         case 'sysDate': return "'".date($this->fmtDate)."'";
  46         case 'sysTimeStamp' : return "'".date($this->sysTimeStamp)."'";
  47         }
  48   }*/
  49
  50         function ServerInfo()
  51         {
  52                 $arr['version'] = $this->_connectionID->version(); //**tochange
  53                 $arr['description'] = 'SQLite 3'; //**tochange
  54                 //$arr['encoding'] = sqlite_libencoding();//**tochange
  55                 return $arr;
  56         }
  57
  58         function BeginTrans()
  59         {
  60                  if ($this->transOff) return true;
  61                  $ret = $this->Execute("BEGIN TRANSACTION");
  62                  $this->transCnt += 1;
  63                  return true;
  64         }
  65
  66         function CommitTrans($ok=true)
  67         {
  68                 if ($this->transOff) return true;
  69                 if (!$ok) return $this->RollbackTrans();
  70                 $ret = $this->Execute("COMMIT");
  71                 if ($this->transCnt>0)$this->transCnt -= 1;
  72                 return !empty($ret);
  73         }
  74
  75         function RollbackTrans()
  76         {
  77                 if ($this->transOff) return true;
  78                 $ret = $this->Execute("ROLLBACK");
  79                 if ($this->transCnt>0)$this->transCnt -= 1;
  80                 return !empty($ret);
  81         }
  82
  83         // mark newnham
  84         function MetaColumns($table, $normalize=true)
  85         {
  86           global $ADODB_FETCH_MODE;
  87           $false = false;
  88           $save = $ADODB_FETCH_MODE;
  89           $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
  90           if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
  91           $rs = $this->Execute("PRAGMA table_info('$table')");
  92           if (isset($savem)) $this->SetFetchMode($savem);
  93           if (!$rs) {
  94             $ADODB_FETCH_MODE = $save;
  95             return $false;
  96           }
  97           $arr = array();
  98           while ($r = $rs->FetchRow()) {
  99
 100             $type = explode('(',$r['type']);
 101             $size = '';
 102             if (sizeof($type)==2)
 103             $size = trim($type[1],')');
 104             $fn = strtoupper($r['name']);
 105             $fld = new ADOFieldObject;
 106             $fld->name = $r['name'];
 107             $fld->type = $type[0];
 108             $fld->max_length = $size;
 109             $fld->not_null = $r['notnull'];
 110             $fld->default_value = $r['dflt_value'];
 111             $fld->scale = 0;
 112                 if (isset($r['pk']) && $r['pk']) $fld->primary_key=1;
 113             if ($save == ADODB_FETCH_NUM) $arr[] = $fld;
 114             else $arr[strtoupper($fld->name)] = $fld;
 115           }
 116           $rs->Close();
 117           $ADODB_FETCH_MODE = $save;
 118           return $arr;
 119         }
 120
 121         function _init($parentDriver)
 122         {
 123
 124                 $parentDriver->hasTransactions = false;
 125                 $parentDriver->hasInsertID = true;
 126         }
 127
 128         function _insertid()
 129         {
 130                 //return sqlite_last_insert_rowid($this->_connectionID)->; //**change
 131                 return $this->_connectionID->lastInsertRowID();
 132         }
 133
 134         function _affectedrows()
 135         {
 136                 return $this->_connectionID->changes();
 137        //return sqlite3_changes($this->_connectionID); //**tochange
 138     }
 139
 140         function ErrorMsg()
 141         {
 142                 if ($this->_logsql) return $this->_errorMsg;
 143
 144                 return ($this->_errorNo) ? $this->ErrorNo() : ''; //**tochange?
 145         }
 146
 147         function ErrorNo()
 148         {
 149                 return $this->_connectionID->lastErrorCode(); //**tochange??
 150         }
 151
 152         function SQLDate($fmt, $col=false)
 153         {
 154                 $fmt = $this->qstr($fmt);
 155                 return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
 156         }
 157
 158
 159         function _createFunctions()
 160         {
 161                 //@sqlite3_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1); *change
 162                 $this->_connectionID->createFunction('adodb_date', 'adodb_date', 1);
 163
 164                 //@sqlite3_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);**change
 165                 $this->_connectionID->createFunction('adodb_date2', 'adodb_date2', 2);
 166         }
 167
 168
 169         // returns true or false
 170         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) //**tochange: all the function need to be changed, just hacks for the moment
 171         {
 172                 if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 173                 $this->_connectionID = new SQLite3($argDatabasename);
 174                 $this->_createFunctions();
 175
 176                 return true; // hack
 177                 /*
 178                 if (!function_exists('sqlite_open')) return null;
 179                 if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 180
 181                 $this->_connectionID = sqlite_open($argHostname);
 182                 if ($this->_connectionID === false) return false;
 183                 $this->_createFunctions();
 184                 return true;
 185                 */
 186         }
 187
 188         // returns true or false
 189         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) //**tochange
 190         {
 191                 if (!function_exists('sqlite_open')) return null;
 192                 if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 193
 194                 $this->_connectionID = sqlite_popen($argHostname);
 195                 if ($this->_connectionID === false) return false;
 196                 $this->_createFunctions();
 197                 return true;
 198         }
 199
 200         // returns query ID if successful, otherwise false
 201         function _query($sql,$inputarr=false)
 202         {
 203                 //$rez = sqlite_query($sql,$this->_connectionID);//**change
 204                 $rez = $this->_connectionID->query($sql);
 205                 if (!$rez) {
 206                         //$this->_errorNo = sqlite3_last_error($this->_connectionID);**change
 207                         $this->_connectionID->lastErrorCode();
 208                 }
 209
 210                 return $rez;
 211         }
 212
 213         function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 214         {
 215                 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 216                 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 217                 if ($secs2cache)
 218                         $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 219                 else
 220                         $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 221
 222                 return $rs;
 223         }
 224
 225         /*
 226                 This algorithm is not very efficient, but works even if table locking
 227                 is not available.
 228
 229                 Will return false if unable to generate an ID after $MAXLOOPS attempts.
 230         */
 231         var $_genSeqSQL = "create table %s (id integer)";
 232
 233         function GenID($seq='adodbseq',$start=1)
 234         {
 235                 // if you have to modify the parameter below, your database is overloaded,
 236                 // or you need to implement generation of id's yourself!
 237                 $MAXLOOPS = 100;
 238                 //$this->debug=1;
 239                 while (--$MAXLOOPS>=0) {
 240                         @($num = $this->GetOne("select id from $seq"));
 241                         if ($num === false) {
 242                                 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 243                                 $start -= 1;
 244                                 $num = '0';
 245                                 $ok = $this->Execute("insert into $seq values($start)");
 246                                 if (!$ok) return false;
 247                         }
 248                         $this->Execute("update $seq set id=id+1 where id=$num");
 249
 250                         if ($this->affected_rows() > 0) {
 251                                 $num += 1;
 252                                 $this->genID = $num;
 253                                 return $num;
 254                         }
 255                 }
 256                 if ($fn = $this->raiseErrorFn) {
 257                         $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 258                 }
 259                 return false;
 260         }
 261
 262         function CreateSequence($seqname='adodbseq',$start=1)
 263         {
 264                 if (empty($this->_genSeqSQL)) return false;
 265                 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 266                 if (!$ok) return false;
 267                 $start -= 1;
 268                 return $this->Execute("insert into $seqname values($start)");
 269         }
 270
 271         var $_dropSeqSQL = 'drop table %s';
 272         function DropSequence($seqname)
 273         {
 274                 if (empty($this->_dropSeqSQL)) return false;
 275                 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 276         }
 277
 278         // returns true or false
 279         function _close()
 280         {
 281                 //return @sqlite3_close($this->_connectionID);**change
 282                 return $this->_connectionID->close();
 283         }
 284
 285         function MetaIndexes($table, $primary = FALSE, $owner=false, $owner = false)
 286         {
 287                 $false = false;
 288                 // save old fetch mode
 289         global $ADODB_FETCH_MODE;
 290         $save = $ADODB_FETCH_MODE;
 291         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 292         if ($this->fetchMode !== FALSE) {
 293                $savem = $this->SetFetchMode(FALSE);
 294         }
 295                 $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
 296         $rs = $this->Execute($SQL);
 297         if (!is_object($rs)) {
 298                         if (isset($savem))
 299                                 $this->SetFetchMode($savem);
 300                         $ADODB_FETCH_MODE = $save;
 301             return $false;
 302         }
 303
 304                 $indexes = array ();
 305                 while ($row = $rs->FetchRow()) {
 306                         if ($primary && preg_match("/primary/i",$row[1]) == 0) continue;
 307             if (!isset($indexes[$row[0]])) {
 308
 309                         $indexes[$row[0]] = array(
 310                                    'unique' => preg_match("/unique/i",$row[1]),
 311                                    'columns' => array());
 312                         }
 313                         /**
 314                           * There must be a more elegant way of doing this,
 315                           * the index elements appear in the SQL statement
 316                           * in cols[1] between parentheses
 317                           * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 318                           */
 319                         $cols = explode("(",$row[1]);
 320                         $cols = explode(")",$cols[1]);
 321                         array_pop($cols);
 322                         $indexes[$row[0]]['columns'] = $cols;
 323                 }
 324                 if (isset($savem)) {
 325             $this->SetFetchMode($savem);
 326                         $ADODB_FETCH_MODE = $save;
 327                 }
 328         return $indexes;
 329         }
 330
 331 }
 332
 333 /*--------------------------------------------------------------------------------------
 334                  Class Name: Recordset
 335 --------------------------------------------------------------------------------------*/
 336
 337 //class ADORecordset_sqlite extends ADORecordSet {**change
 338 class ADORecordset_sqlite3 extends ADORecordSet {
 339
 340         //var $databaseType = "sqlite";**change
 341         var $databaseType = "sqlite3";
 342         var $bind = false;
 343
 344         //function ADORecordset_sqlite($queryID,$mode=false)**change
 345         function ADORecordset_sqlite3($queryID,$mode=false)
 346         {
 347
 348                 if ($mode === false) {
 349                         global $ADODB_FETCH_MODE;
 350                         $mode = $ADODB_FETCH_MODE;
 351                 }
 352                 switch($mode) {
 353                 //case ADODB_FETCH_NUM: $this->fetchMode = SQLITE_NUM; break;**change
 354                 case ADODB_FETCH_NUM: $this->fetchMode = SQLITE3_NUM; break;
 355                 //case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE_ASSOC; break;**change
 356                 case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE3_ASSOC; break;
 357                 //default: $this->fetchMode = SQLITE_BOTH; break;**change
 358                 default: $this->fetchMode = SQLITE3_BOTH; break;
 359                 }
 360                 $this->adodbFetchMode = $mode;
 361
 362                 $this->_queryID = $queryID;
 363
 364                 $this->_inited = true;
 365                 $this->fields = array();
 366                 if ($queryID) {
 367                         $this->_currentRow = 0;
 368                         $this->EOF = !$this->_fetch();
 369                         @$this->_initrs();
 370                 } else {
 371                         $this->_numOfRows = 0;
 372                         $this->_numOfFields = 0;
 373                         $this->EOF = true;
 374                 }
 375
 376                 return $this->_queryID;
 377         }
 378
 379
 380         function FetchField($fieldOffset = -1)
 381         {
 382                 $fld = new ADOFieldObject;
 383                 //$fld->name = sqlite3_field_name($this->_queryID, $fieldOffset);**change
 384                 $fld->name->columnName($this->_queryID, $fieldOffset);
 385                 $fld->type = 'VARCHAR';
 386                 $fld->max_length = -1;
 387                 return $fld;
 388         }
 389
 390    function _initrs()
 391    {
 392                 //$this->_numOfRows = @sqlite_num_rows($this->_queryID); **tochange but sqlite3 doesn't implement this!
 393                 $this->_numOfRows = 1;
 394                 //$this->_numOfFields = @sqlite3_num_fields($this->_queryID);**change
 395                 $this->_numOfFields = $this->_queryID->numColumns();
 396
 397    }
 398
 399         function Fields($colname)
 400         {
 401                 //if ($this->fetchMode != SQLITE_NUM) return $this->fields[$colname];**change
 402                 if ($this->fetchMode != SQLITE3_NUM) return $this->fields[$colname];
 403                 if (!$this->bind) {
 404                         $this->bind = array();
 405                         for ($i=0; $i < $this->_numOfFields; $i++) {
 406                                 $o = $this->FetchField($i);
 407                                 $this->bind[strtoupper($o->name)] = $i;
 408                         }
 409                 }
 410
 411                  return $this->fields[$this->bind[strtoupper($colname)]];
 412         }
 413
 414    function _seek($row)
 415    {
 416                 return sqlite3_seek($this->_queryID, $row);//**tochange but sqlite3 seems not to implement seek!
 417    }
 418
 419         function _fetch($ignore_fields=false)
 420         {
 421                 //$this->fields = @sqlite3_fetch_array($this->_queryID,$this->fetchMode);**change
 422                 $this->fields = $this->_queryID->fetchArray($this->fetchMode);
 423                 return !empty($this->fields);
 424         }
 425
 426         function _close()
 427         {
 428         }
 429
 430 }