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