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

   1 <?php
   2 /*
   3 V4.92a 29 Aug 2006  (c) 2000-2006 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://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($tab)
  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('$tab')");
  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 ($save == ADODB_FETCH_NUM) $arr[] = $fld;
 109             else $arr[strtoupper($fld->name)] = $fld;
 110           }
 111           $rs->Close();
 112           $ADODB_FETCH_MODE = $save;
 113           return $arr;
 114         }
 115
 116         function _init($parentDriver)
 117         {
 118
 119                 $parentDriver->hasTransactions = false;
 120                 $parentDriver->hasInsertID = true;
 121         }
 122
 123         function _insertid()
 124         {
 125                 return sqlite_last_insert_rowid($this->_connectionID);
 126         }
 127
 128         function _affectedrows()
 129         {
 130         return sqlite_changes($this->_connectionID);
 131     }
 132
 133         function ErrorMsg()
 134         {
 135                 if ($this->_logsql) return $this->_errorMsg;
 136                 return ($this->_errorNo) ? sqlite_error_string($this->_errorNo) : '';
 137         }
 138
 139         function ErrorNo()
 140         {
 141                 return $this->_errorNo;
 142         }
 143
 144         function SQLDate($fmt, $col=false)
 145         {
 146                 $fmt = $this->qstr($fmt);
 147                 return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
 148         }
 149
 150
 151         function _createFunctions()
 152         {
 153                 @sqlite_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1);
 154                 @sqlite_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);
 155         }
 156
 157
 158         // returns true or false
 159         function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 160         {
 161                 if (!function_exists('sqlite_open')) return null;
 162                 if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 163
 164                 $this->_connectionID = sqlite_open($argHostname);
 165                 if ($this->_connectionID === false) return false;
 166                 $this->_createFunctions();
 167                 return true;
 168         }
 169
 170         // returns true or false
 171         function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 172         {
 173                 if (!function_exists('sqlite_open')) return null;
 174                 if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 175
 176                 $this->_connectionID = sqlite_popen($argHostname);
 177                 if ($this->_connectionID === false) return false;
 178                 $this->_createFunctions();
 179                 return true;
 180         }
 181
 182         // returns query ID if successful, otherwise false
 183         function _query($sql,$inputarr=false)
 184         {
 185                 $rez = sqlite_query($sql,$this->_connectionID);
 186                 if (!$rez) {
 187                         $this->_errorNo = sqlite_last_error($this->_connectionID);
 188                 }
 189
 190                 return $rez;
 191         }
 192
 193         function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 194         {
 195                 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 196                 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 197                 if ($secs2cache)
 198                         $rs =& $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 199                 else
 200                         $rs =& $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 201
 202                 return $rs;
 203         }
 204
 205         /*
 206                 This algorithm is not very efficient, but works even if table locking
 207                 is not available.
 208
 209                 Will return false if unable to generate an ID after $MAXLOOPS attempts.
 210         */
 211         var $_genSeqSQL = "create table %s (id integer)";
 212
 213         function GenID($seq='adodbseq',$start=1)
 214         {
 215                 // if you have to modify the parameter below, your database is overloaded,
 216                 // or you need to implement generation of id's yourself!
 217                 $MAXLOOPS = 100;
 218                 //$this->debug=1;
 219                 while (--$MAXLOOPS>=0) {
 220                         @($num = $this->GetOne("select id from $seq"));
 221                         if ($num === false) {
 222                                 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 223                                 $start -= 1;
 224                                 $num = '0';
 225                                 $ok = $this->Execute("insert into $seq values($start)");
 226                                 if (!$ok) return false;
 227                         }
 228                         $this->Execute("update $seq set id=id+1 where id=$num");
 229
 230                         if ($this->affected_rows() > 0) {
 231                                 $num += 1;
 232                                 $this->genID = $num;
 233                                 return $num;
 234                         }
 235                 }
 236                 if ($fn = $this->raiseErrorFn) {
 237                         $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 238                 }
 239                 return false;
 240         }
 241
 242         function CreateSequence($seqname='adodbseq',$start=1)
 243         {
 244                 if (empty($this->_genSeqSQL)) return false;
 245                 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 246                 if (!$ok) return false;
 247                 $start -= 1;
 248                 return $this->Execute("insert into $seqname values($start)");
 249         }
 250
 251         var $_dropSeqSQL = 'drop table %s';
 252         function DropSequence($seqname)
 253         {
 254                 if (empty($this->_dropSeqSQL)) return false;
 255                 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 256         }
 257
 258         // returns true or false
 259         function _close()
 260         {
 261                 return @sqlite_close($this->_connectionID);
 262         }
 263
 264         function &MetaIndexes($table, $primary = FALSE, $owner=false)
 265         {
 266                 $false = false;
 267                 // save old fetch mode
 268         global $ADODB_FETCH_MODE;
 269         $save = $ADODB_FETCH_MODE;
 270         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 271         if ($this->fetchMode !== FALSE) {
 272                $savem = $this->SetFetchMode(FALSE);
 273         }
 274                 $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
 275         $rs = $this->Execute($SQL);
 276         if (!is_object($rs)) {
 277                         if (isset($savem))
 278                                 $this->SetFetchMode($savem);
 279                         $ADODB_FETCH_MODE = $save;
 280             return $false;
 281         }
 282
 283                 $indexes = array ();
 284                 while ($row = $rs->FetchRow()) {
 285                         if ($primary && preg_match("/primary/i",$row[1]) == 0) continue;
 286             if (!isset($indexes[$row[0]])) {
 287
 288                         $indexes[$row[0]] = array(
 289                                    'unique' => preg_match("/unique/i",$row[1]),
 290                                    'columns' => array());
 291                         }
 292                         /**
 293                           * There must be a more elegant way of doing this,
 294                           * the index elements appear in the SQL statement
 295                           * in cols[1] between parentheses
 296                           * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 297                           */
 298                         $cols = explode("(",$row[1]);
 299                         $cols = explode(")",$cols[1]);
 300                         array_pop($cols);
 301                         $indexes[$row[0]]['columns'] = $cols;
 302                 }
 303                 if (isset($savem)) {
 304             $this->SetFetchMode($savem);
 305                         $ADODB_FETCH_MODE = $save;
 306                 }
 307         return $indexes;
 308         }
 309
 310 }
 311
 312 /*--------------------------------------------------------------------------------------
 313                  Class Name: Recordset
 314 --------------------------------------------------------------------------------------*/
 315
 316 class ADORecordset_sqlite extends ADORecordSet {
 317
 318         var $databaseType = "sqlite";
 319         var $bind = false;
 320
 321         function ADORecordset_sqlite($queryID,$mode=false)
 322         {
 323
 324                 if ($mode === false) {
 325                         global $ADODB_FETCH_MODE;
 326                         $mode = $ADODB_FETCH_MODE;
 327                 }
 328                 switch($mode) {
 329                 case ADODB_FETCH_NUM: $this->fetchMode = SQLITE_NUM; break;
 330                 case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE_ASSOC; break;
 331                 default: $this->fetchMode = SQLITE_BOTH; break;
 332                 }
 333                 $this->adodbFetchMode = $mode;
 334
 335                 $this->_queryID = $queryID;
 336
 337                 $this->_inited = true;
 338                 $this->fields = array();
 339                 if ($queryID) {
 340                         $this->_currentRow = 0;
 341                         $this->EOF = !$this->_fetch();
 342                         @$this->_initrs();
 343                 } else {
 344                         $this->_numOfRows = 0;
 345                         $this->_numOfFields = 0;
 346                         $this->EOF = true;
 347                 }
 348
 349                 return $this->_queryID;
 350         }
 351
 352
 353         function &FetchField($fieldOffset = -1)
 354         {
 355                 $fld = new ADOFieldObject;
 356                 $fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
 357                 $fld->type = 'VARCHAR';
 358                 $fld->max_length = -1;
 359                 return $fld;
 360         }
 361
 362    function _initrs()
 363    {
 364                 $this->_numOfRows = @sqlite_num_rows($this->_queryID);
 365                 $this->_numOfFields = @sqlite_num_fields($this->_queryID);
 366    }
 367
 368         function Fields($colname)
 369         {
 370                 if ($this->fetchMode != SQLITE_NUM) return $this->fields[$colname];
 371                 if (!$this->bind) {
 372                         $this->bind = array();
 373                         for ($i=0; $i < $this->_numOfFields; $i++) {
 374                                 $o = $this->FetchField($i);
 375                                 $this->bind[strtoupper($o->name)] = $i;
 376                         }
 377                 }
 378
 379                  return $this->fields[$this->bind[strtoupper($colname)]];
 380         }
 381
 382    function _seek($row)
 383    {
 384                 return sqlite_seek($this->_queryID, $row);
 385    }
 386
 387         function _fetch($ignore_fields=false)
 388         {
 389                 $this->fields = @sqlite_fetch_array($this->_queryID,$this->fetchMode);
 390                 return !empty($this->fields);
 391         }
 392
 393         function _close()
 394         {
 395         }
 396
 397 }
 398 ?>