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

   1 <?php
   2 /*
   3 V4.20 22 Feb 2004  (c) 2000-2004 John Lim (jlim@natsoft.com.my). 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://php.weblogs.com/
   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 class ADODB_sqlite extends ADOConnection {
  19         var $databaseType = "sqlite";
  20         var $replaceQuote = "''"; // string to use to replace quotes
  21         var $concat_operator='||';
  22         var $_errorNo = 0;
  23         var $hasLimit = true;
  24         var $hasInsertID = true;                /// supports autoincrement ID?
  25         var $hasAffectedRows = true;    /// supports affected rows for update/delete?
  26         var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
  27         var $sysDate = "adodb_date('Y-m-d')";
  28         var $sysTimeStamp = "adodb_date('Y-m-d H:i:s')";
  29         var $fmtTimeStamp = "'Y-m-d H:i:s'";
  30
  31         function ADODB_sqlite()
  32         {
  33         }
  34
  35 /*
  36   function __get($name)
  37   {
  38         switch($name) {
  39         case 'sysDate': return "'".date($this->fmtDate)."'";
  40         case 'sysTimeStamp' : return "'".date($this->sysTimeStamp)."'";
  41         }
  42   }*/
  43
  44         function ServerInfo()
  45         {
  46                 $arr['version'] = sqlite_libversion();
  47                 $arr['description'] = 'SQLite ';
  48                 $arr['encoding'] = sqlite_libencoding();
  49                 return $arr;
  50         }
  51
  52         function BeginTrans()
  53         {
  54                  if ($this->transOff) return true;
  55                  $ret = $this->Execute("BEGIN TRANSACTION");
  56                  $this->transCnt += 1;
  57                  return true;
  58         }
  59
  60         function CommitTrans($ok=true)
  61         {
  62                 if ($this->transOff) return true;
  63                 if (!$ok) return $this->RollbackTrans();
  64                 $ret = $this->Execute("COMMIT");
  65                 if ($this->transCnt>0)$this->transCnt -= 1;
  66                 return !empty($ret);
  67         }
  68
  69         function RollbackTrans()
  70         {
  71                 if ($this->transOff) return true;
  72                 $ret = $this->Execute("ROLLBACK");
  73                 if ($this->transCnt>0)$this->transCnt -= 1;
  74                 return !empty($ret);
  75         }
  76
  77         function _insertid()
  78         {
  79                 return sqlite_last_insert_rowid($this->_connectionID);
  80         }
  81
  82         function _affectedrows()
  83         {
  84         return sqlite_changes($this->_connectionID);
  85     }
  86
  87         function ErrorMsg()
  88         {
  89                 if ($this->_logsql) return $this->_errorMsg;
  90                 return ($this->_errorNo) ? sqlite_error_string($this->_errorNo) : '';
  91         }
  92
  93         function ErrorNo()
  94         {
  95                 return $this->_errorNo;
  96         }
  97
  98         function SQLDate($fmt, $col=false)
  99         {
 100                 $fmt = $this->qstr($fmt);
 101                 return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
 102         }
 103
 104         function &MetaColumns($tab)
 105         {
 106         global $ADODB_FETCH_MODE;
 107
 108                 $rs = $this->Execute("select * from $tab limit 1");
 109                 if (!$rs) return false;
 110                 $arr = array();
 111                 for ($i=0,$max=$rs->_numOfFields; $i < $max; $i++) {
 112                         $fld =& $rs->FetchField($i);
 113                         if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] =& $fld;
 114                         else $arr[strtoupper($fld->name)] =& $fld;
 115                 }
 116                 $rs->Close();
 117                 return $arr;
 118         }
 119
 120         function _createFunctions()
 121         {
 122                 @sqlite_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1);
 123                 @sqlite_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);
 124         }
 125
 126
 127         // returns true or false
 128         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 129         {
 130                 if (!function_exists('sqlite_open')) return false;
 131
 132                 $this->_connectionID = sqlite_open($argHostname);
 133                 if ($this->_connectionID === false) return false;
 134                 $this->_createFunctions();
 135                 return true;
 136         }
 137
 138         // returns true or false
 139         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 140         {
 141                 if (!function_exists('sqlite_open')) return false;
 142
 143                 $this->_connectionID = sqlite_popen($argHostname);
 144                 if ($this->_connectionID === false) return false;
 145                 $this->_createFunctions();
 146                 return true;
 147         }
 148
 149         // returns query ID if successful, otherwise false
 150         function _query($sql,$inputarr=false)
 151         {
 152                 $rez = sqlite_query($sql,$this->_connectionID);
 153                 if (!$rez) {
 154                         $this->_errorNo = sqlite_last_error($this->_connectionID);
 155                 }
 156
 157                 return $rez;
 158         }
 159
 160         function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 161         {
 162                 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 163                 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 164                 if ($secs2cache)
 165                         $rs =& $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 166                 else
 167                         $rs =& $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 168
 169                 return $rs;
 170         }
 171
 172         /*
 173                 This algorithm is not very efficient, but works even if table locking
 174                 is not available.
 175
 176                 Will return false if unable to generate an ID after $MAXLOOPS attempts.
 177         */
 178         var $_genSeqSQL = "create table %s (id integer)";
 179
 180         function GenID($seq='adodbseq',$start=1)
 181         {
 182                 // if you have to modify the parameter below, your database is overloaded,
 183                 // or you need to implement generation of id's yourself!
 184                 $MAXLOOPS = 100;
 185                 //$this->debug=1;
 186                 while (--$MAXLOOPS>=0) {
 187                         $num = $this->GetOne("select id from $seq");
 188                         if ($num === false) {
 189                                 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 190                                 $start -= 1;
 191                                 $num = '0';
 192                                 $ok = $this->Execute("insert into $seq values($start)");
 193                                 if (!$ok) return false;
 194                         }
 195                         $this->Execute("update $seq set id=id+1 where id=$num");
 196
 197                         if ($this->affected_rows() > 0) {
 198                                 $num += 1;
 199                                 $this->genID = $num;
 200                                 return $num;
 201                         }
 202                 }
 203                 if ($fn = $this->raiseErrorFn) {
 204                         $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 205                 }
 206                 return false;
 207         }
 208
 209         function CreateSequence($seqname='adodbseq',$start=1)
 210         {
 211                 if (empty($this->_genSeqSQL)) return false;
 212                 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 213                 if (!$ok) return false;
 214                 $start -= 1;
 215                 return $this->Execute("insert into $seqname values($start)");
 216         }
 217
 218         var $_dropSeqSQL = 'drop table %s';
 219         function DropSequence($seqname)
 220         {
 221                 if (empty($this->_dropSeqSQL)) return false;
 222                 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 223         }
 224
 225         // returns true or false
 226         function _close()
 227         {
 228                 return @sqlite_close($this->_connectionID);
 229         }
 230
 231
 232 }
 233
 234 /*--------------------------------------------------------------------------------------
 235                  Class Name: Recordset
 236 --------------------------------------------------------------------------------------*/
 237
 238 class ADORecordset_sqlite extends ADORecordSet {
 239
 240         var $databaseType = "sqlite";
 241         var $bind = false;
 242
 243         function ADORecordset_sqlite($queryID,$mode=false)
 244         {
 245
 246                 if ($mode === false) {
 247                         global $ADODB_FETCH_MODE;
 248                         $mode = $ADODB_FETCH_MODE;
 249                 }
 250                 switch($mode) {
 251                 case ADODB_FETCH_NUM: $this->fetchMode = SQLITE_NUM; break;
 252                 case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE_ASSOC; break;
 253                 default: $this->fetchMode = SQLITE_BOTH; break;
 254                 }
 255
 256                 $this->_queryID = $queryID;
 257
 258                 $this->_inited = true;
 259                 $this->fields = array();
 260                 if ($queryID) {
 261                         $this->_currentRow = 0;
 262                         $this->EOF = !$this->_fetch();
 263                         @$this->_initrs();
 264                 } else {
 265                         $this->_numOfRows = 0;
 266                         $this->_numOfFields = 0;
 267                         $this->EOF = true;
 268                 }
 269
 270                 return $this->_queryID;
 271         }
 272
 273
 274         function &FetchField($fieldOffset = -1)
 275         {
 276                 $fld = new ADOFieldObject;
 277                 $fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
 278                 $fld->type = 'VARCHAR';
 279                 $fld->max_length = -1;
 280                 return $fld;
 281         }
 282
 283    function _initrs()
 284    {
 285                 $this->_numOfRows = @sqlite_num_rows($this->_queryID);
 286                 $this->_numOfFields = @sqlite_num_fields($this->_queryID);
 287    }
 288
 289         function Fields($colname)
 290         {
 291                 if ($this->fetchMode != SQLITE_NUM) return $this->fields[$colname];
 292                 if (!$this->bind) {
 293                         $this->bind = array();
 294                         for ($i=0; $i < $this->_numOfFields; $i++) {
 295                                 $o = $this->FetchField($i);
 296                                 $this->bind[strtoupper($o->name)] = $i;
 297                         }
 298                 }
 299
 300                  return $this->fields[$this->bind[strtoupper($colname)]];
 301         }
 302
 303    function _seek($row)
 304    {
 305                 return sqlite_seek($this->_queryID, $row);
 306    }
 307
 308         function _fetch($ignore_fields=false)
 309         {
 310                 $this->fields = @sqlite_fetch_array($this->_queryID,$this->fetchMode);
 311                 return !empty($this->fields);
 312         }
 313
 314         function _close()
 315         {
 316         }
 317
 318 }
 319 ?>