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

   1 <?php
   2 /*
   3 V5.14 8 Sept 2011  (c) 2000-2011 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'] = sqlite_libversion(); //**tochange
  53                 $arr['description'] = 'SQLite '; //**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                 $temp = $this->_connectionID;
 132                 return $temp->lastInsertRowID();
 133         }
 134
 135         function _affectedrows()
 136         {
 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) ? sqlite_error_string($this->_errorNo) : ''; //**tochange?
 145         }
 146
 147         function ErrorNo()
 148         {
 149                 return $this->_errorNo; //**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                 $this->_connectionID = new SQLite3('/path/mydb'); // hack
 173                 if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename; // hack
 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         // returns true or false
 188         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) //**tochange
 189         {
 190                 if (!function_exists('sqlite_open')) return null;
 191                 if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 192
 193                 $this->_connectionID = sqlite_popen($argHostname);
 194                 if ($this->_connectionID === false) return false;
 195                 $this->_createFunctions();
 196                 return true;
 197         }
 198
 199         // returns query ID if successful, otherwise false
 200         function _query($sql,$inputarr=false)
 201         {
 202                 //$rez = sqlite_query($sql,$this->_connectionID);//**change
 203                 $rez = $this->_connectionID->query($sql);
 204                 if (!$rez) {
 205                         //$this->_errorNo = sqlite3_last_error($this->_connectionID);**change
 206                         $this->_connectionID->lastErrorCode();
 207                 }
 208
 209                 return $rez;
 210         }
 211
 212         function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 213         {
 214                 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 215                 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 216                 if ($secs2cache)
 217                         $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 218                 else
 219                         $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 220
 221                 return $rs;
 222         }
 223
 224         /*
 225                 This algorithm is not very efficient, but works even if table locking
 226                 is not available.
 227
 228                 Will return false if unable to generate an ID after $MAXLOOPS attempts.
 229         */
 230         var $_genSeqSQL = "create table %s (id integer)";
 231
 232         function GenID($seq='adodbseq',$start=1)
 233         {
 234                 // if you have to modify the parameter below, your database is overloaded,
 235                 // or you need to implement generation of id's yourself!
 236                 $MAXLOOPS = 100;
 237                 //$this->debug=1;
 238                 while (--$MAXLOOPS>=0) {
 239                         @($num = $this->GetOne("select id from $seq"));
 240                         if ($num === false) {
 241                                 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 242                                 $start -= 1;
 243                                 $num = '0';
 244                                 $ok = $this->Execute("insert into $seq values($start)");
 245                                 if (!$ok) return false;
 246                         }
 247                         $this->Execute("update $seq set id=id+1 where id=$num");
 248
 249                         if ($this->affected_rows() > 0) {
 250                                 $num += 1;
 251                                 $this->genID = $num;
 252                                 return $num;
 253                         }
 254                 }
 255                 if ($fn = $this->raiseErrorFn) {
 256                         $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 257                 }
 258                 return false;
 259         }
 260
 261         function CreateSequence($seqname='adodbseq',$start=1)
 262         {
 263                 if (empty($this->_genSeqSQL)) return false;
 264                 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 265                 if (!$ok) return false;
 266                 $start -= 1;
 267                 return $this->Execute("insert into $seqname values($start)");
 268         }
 269
 270         var $_dropSeqSQL = 'drop table %s';
 271         function DropSequence($seqname)
 272         {
 273                 if (empty($this->_dropSeqSQL)) return false;
 274                 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 275         }
 276
 277         // returns true or false
 278         function _close()
 279         {
 280                 //return @sqlite3_close($this->_connectionID);**change
 281                 return $this->_connectionID->close();
 282         }
 283
 284         function MetaIndexes($table, $primary = FALSE, $owner=false, $owner = false)
 285         {
 286                 $false = false;
 287                 // save old fetch mode
 288         global $ADODB_FETCH_MODE;
 289         $save = $ADODB_FETCH_MODE;
 290         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 291         if ($this->fetchMode !== FALSE) {
 292                $savem = $this->SetFetchMode(FALSE);
 293         }
 294                 $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
 295         $rs = $this->Execute($SQL);
 296         if (!is_object($rs)) {
 297                         if (isset($savem))
 298                                 $this->SetFetchMode($savem);
 299                         $ADODB_FETCH_MODE = $save;
 300             return $false;
 301         }
 302
 303                 $indexes = array ();
 304                 while ($row = $rs->FetchRow()) {
 305                         if ($primary && preg_match("/primary/i",$row[1]) == 0) continue;
 306             if (!isset($indexes[$row[0]])) {
 307
 308                         $indexes[$row[0]] = array(
 309                                    'unique' => preg_match("/unique/i",$row[1]),
 310                                    'columns' => array());
 311                         }
 312                         /**
 313                           * There must be a more elegant way of doing this,
 314                           * the index elements appear in the SQL statement
 315                           * in cols[1] between parentheses
 316                           * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 317                           */
 318                         $cols = explode("(",$row[1]);
 319                         $cols = explode(")",$cols[1]);
 320                         array_pop($cols);
 321                         $indexes[$row[0]]['columns'] = $cols;
 322                 }
 323                 if (isset($savem)) {
 324             $this->SetFetchMode($savem);
 325                         $ADODB_FETCH_MODE = $save;
 326                 }
 327         return $indexes;
 328         }
 329
 330 }
 331
 332 /*--------------------------------------------------------------------------------------
 333                  Class Name: Recordset
 334 --------------------------------------------------------------------------------------*/
 335
 336 //class ADORecordset_sqlite extends ADORecordSet {**change
 337 class ADORecordset_sqlite3 extends ADORecordSet {
 338
 339         //var $databaseType = "sqlite";**change
 340         var $databaseType = "sqlite3";
 341         var $bind = false;
 342
 343         //function ADORecordset_sqlite($queryID,$mode=false)**change
 344         function ADORecordset_sqlite3($queryID,$mode=false)
 345         {
 346
 347                 if ($mode === false) {
 348                         global $ADODB_FETCH_MODE;
 349                         $mode = $ADODB_FETCH_MODE;
 350                 }
 351                 switch($mode) {
 352                 //case ADODB_FETCH_NUM: $this->fetchMode = SQLITE_NUM; break;**change
 353                 case ADODB_FETCH_NUM: $this->fetchMode = SQLITE3_NUM; break;
 354                 //case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE_ASSOC; break;**change
 355                 case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE3_ASSOC; break;
 356                 //default: $this->fetchMode = SQLITE_BOTH; break;**change
 357                 default: $this->fetchMode = SQLITE3_BOTH; break;
 358                 }
 359                 $this->adodbFetchMode = $mode;
 360
 361                 $this->_queryID = $queryID;
 362
 363                 $this->_inited = true;
 364                 $this->fields = array();
 365                 if ($queryID) {
 366                         $this->_currentRow = 0;
 367                         $this->EOF = !$this->_fetch();
 368                         @$this->_initrs();
 369                 } else {
 370                         $this->_numOfRows = 0;
 371                         $this->_numOfFields = 0;
 372                         $this->EOF = true;
 373                 }
 374
 375                 return $this->_queryID;
 376         }
 377
 378
 379         function FetchField($fieldOffset = -1)
 380         {
 381                 $fld = new ADOFieldObject;
 382                 //$fld->name = sqlite3_field_name($this->_queryID, $fieldOffset);**change
 383                 $fld->name->columnName($this->_queryID, $fieldOffset);
 384                 $fld->type = 'VARCHAR';
 385                 $fld->max_length = -1;
 386                 return $fld;
 387         }
 388
 389    function _initrs()
 390    {
 391                 //$this->_numOfRows = @sqlite_num_rows($this->_queryID); **tochange but sqlite3 doesn't implement this!
 392                 $this->_numOfRows = 1;
 393                 //$this->_numOfFields = @sqlite3_num_fields($this->_queryID);**change
 394                 $this->_numOfFields = $this->_queryID->numColumns();
 395
 396    }
 397
 398         function Fields($colname)
 399         {
 400                 //if ($this->fetchMode != SQLITE_NUM) return $this->fields[$colname];**change
 401                 if ($this->fetchMode != SQLITE3_NUM) return $this->fields[$colname];
 402                 if (!$this->bind) {
 403                         $this->bind = array();
 404                         for ($i=0; $i < $this->_numOfFields; $i++) {
 405                                 $o = $this->FetchField($i);
 406                                 $this->bind[strtoupper($o->name)] = $i;
 407                         }
 408                 }
 409
 410                  return $this->fields[$this->bind[strtoupper($colname)]];
 411         }
 412
 413    function _seek($row)
 414    {
 415                 return sqlite3_seek($this->_queryID, $row);//**tochange but sqlite3 seems not to implement seek!
 416    }
 417
 418         function _fetch($ignore_fields=false)
 419         {
 420                 //$this->fields = @sqlite3_fetch_array($this->_queryID,$this->fetchMode);**change
 421                 $this->fields = $this->_queryID->fetchArray($this->fetchMode);
 422                 return !empty($this->fields);
 423         }
 424
 425         function _close()
 426         {
 427         }
 428
 429 }
 430 ?>