[Summary view]

Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403] [Versions 400 and 403]

   1  <?php
   2  /**
   3   * SQLite3 driver
   4   *
   5   * @link https://www.sqlite.org/
   6   *
   7   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
   8   *
   9   * @package ADOdb
  10   * @link https://adodb.org Project's web site and documentation
  11   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  12   *
  13   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  14   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  15   * any later version. This means you can use it in proprietary products.
  16   * See the LICENSE.md file distributed with this source code for details.
  17   * @license BSD-3-Clause
  18   * @license LGPL-2.1-or-later
  19   *
  20   * @copyright 2000-2013 John Lim
  21   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  22   */
  23  
  24  // security - hide paths
  25  if (!defined('ADODB_DIR')) die();
  26  
  27  /**
  28   * Class ADODB_sqlite3
  29   */
  30  class ADODB_sqlite3 extends ADOConnection {
  31  	 var $databaseType = "sqlite3";
  32  	 var $dataProvider = "sqlite";
  33  	 var $replaceQuote = "''"; // string to use to replace quotes
  34  	 var $concat_operator='||';
  35  	 var $_errorNo = 0;
  36  	 var $hasLimit = true;
  37  	 var $hasInsertID = true; 	 	 /// supports autoincrement ID?
  38  	 var $hasAffectedRows = true; 	 /// supports affected rows for update/delete?
  39  	 var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
  40  	 var $sysDate = "DATE('now','localtime')";
  41  	 var $sysTimeStamp = "DATETIME('now','localtime')";
  42  	 var $fmtTimeStamp = "'Y-m-d H:i:s'";
  43  
  44  	 /** @var SQLite3 */
  45  	 var $_connectionID;
  46  
  47  	function ServerInfo()
  48  	 {
  49  	 	 $version = SQLite3::version();
  50  	 	 $arr['version'] = $version['versionString'];
  51  	 	 $arr['description'] = 'SQLite 3';
  52  	 	 return $arr;
  53  	 }
  54  
  55  	function BeginTrans()
  56  	 {
  57  	 	 if ($this->transOff) {
  58  	 	 	 return true;
  59  	 	 }
  60  	 	 $this->Execute("BEGIN TRANSACTION");
  61  	 	 $this->transCnt += 1;
  62  	 	 return true;
  63  	 }
  64  
  65  	function CommitTrans($ok=true)
  66  	 {
  67  	 	 if ($this->transOff) {
  68  	 	 	 return true;
  69  	 	 }
  70  	 	 if (!$ok) {
  71  	 	 	 return $this->RollbackTrans();
  72  	 	 }
  73  	 	 $ret = $this->Execute("COMMIT");
  74  	 	 if ($this->transCnt > 0) {
  75  	 	 	 $this->transCnt -= 1;
  76  	 	 }
  77  	 	 return !empty($ret);
  78  	 }
  79  
  80  	function RollbackTrans()
  81  	 {
  82  	 	 if ($this->transOff) {
  83  	 	 	 return true;
  84  	 	 }
  85  	 	 $ret = $this->Execute("ROLLBACK");
  86  	 	 if ($this->transCnt > 0) {
  87  	 	 	 $this->transCnt -= 1;
  88  	 	 }
  89  	 	 return !empty($ret);
  90  	 }
  91  
  92  	function metaType($t,$len=-1,$fieldobj=false)
  93  	 {
  94  
  95  	 	 if (is_object($t))
  96  	 	 {
  97  	 	 	 $fieldobj = $t;
  98  	 	 	 $t = $fieldobj->type;
  99  	 	 	 $len = $fieldobj->max_length;
 100  	 	 }
 101  
 102  	 	 $t = strtoupper($t);
 103  
 104  	 	 if (array_key_exists($t,$this->customActualTypes))
 105  	 	 	 return  $this->customActualTypes[$t];
 106  
 107  	 	 /*
 108  	 	 * We are using the Sqlite affinity method here
 109  	 	 * @link https://www.sqlite.org/datatype3.html
 110  	 	 */
 111  	 	 $affinity = array(
 112  	 	 'INT'=>'INTEGER',
 113  	 	 'INTEGER'=>'INTEGER',
 114  	 	 'TINYINT'=>'INTEGER',
 115  	 	 'SMALLINT'=>'INTEGER',
 116  	 	 'MEDIUMINT'=>'INTEGER',
 117  	 	 'BIGINT'=>'INTEGER',
 118  	 	 'UNSIGNED BIG INT'=>'INTEGER',
 119  	 	 'INT2'=>'INTEGER',
 120  	 	 'INT8'=>'INTEGER',
 121  
 122  	 	 'CHARACTER'=>'TEXT',
 123  	 	 'VARCHAR'=>'TEXT',
 124  	 	 'VARYING CHARACTER'=>'TEXT',
 125  	 	 'NCHAR'=>'TEXT',
 126  	 	 'NATIVE CHARACTER'=>'TEXT',
 127  	 	 'NVARCHAR'=>'TEXT',
 128  	 	 'TEXT'=>'TEXT',
 129  	 	 'CLOB'=>'TEXT',
 130  
 131  	 	 'BLOB'=>'BLOB',
 132  
 133  	 	 'REAL'=>'REAL',
 134  	 	 'DOUBLE'=>'REAL',
 135  	 	 'DOUBLE PRECISION'=>'REAL',
 136  	 	 'FLOAT'=>'REAL',
 137  
 138  	 	 'NUMERIC'=>'NUMERIC',
 139  	 	 'DECIMAL'=>'NUMERIC',
 140  	 	 'BOOLEAN'=>'NUMERIC',
 141  	 	 'DATE'=>'NUMERIC',
 142  	 	 'DATETIME'=>'NUMERIC'
 143  	 	 );
 144  
 145  	 	 if (!isset($affinity[$t]))
 146  	 	 	 return ADODB_DEFAULT_METATYPE;
 147  
 148  	 	 $subt = $affinity[$t];
 149  	 	 /*
 150  	 	 * Now that we have subclassed the provided data down
 151  	 	 * the sqlite 'affinity', we convert to ADOdb metatype
 152  	 	 */
 153  
 154  	 	 $subclass = array('INTEGER'=>'I',
 155  	 	 	 	 	 	   'TEXT'=>'X',
 156  	 	 	 	 	 	   'BLOB'=>'B',
 157  	 	 	 	 	 	   'REAL'=>'N',
 158  	 	 	 	 	 	   'NUMERIC'=>'N');
 159  
 160  	 	 return $subclass[$subt];
 161  	 }
 162  	 // mark newnham
 163  	function MetaColumns($table, $normalize=true)
 164  	 {
 165  	 	 global $ADODB_FETCH_MODE;
 166  	 	 $false = false;
 167  	 	 $save = $ADODB_FETCH_MODE;
 168  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 169  	 	 if ($this->fetchMode !== false) {
 170  	 	 	 $savem = $this->SetFetchMode(false);
 171  	 	 }
 172  	 	 $rs = $this->Execute("PRAGMA table_info('$table')");
 173  	 	 if (isset($savem)) {
 174  	 	 	 $this->SetFetchMode($savem);
 175  	 	 }
 176  	 	 if (!$rs) {
 177  	 	 	 $ADODB_FETCH_MODE = $save;
 178  	 	 	 return $false;
 179  	 	 }
 180  	 	 $arr = array();
 181  	 	 while ($r = $rs->FetchRow()) {
 182  	 	 	 $type = explode('(',$r['type']);
 183  	 	 	 $size = '';
 184  	 	 	 if (sizeof($type)==2) {
 185  	 	 	 	 $size = trim($type[1],')');
 186  	 	 	 }
 187  	 	 	 $fn = strtoupper($r['name']);
 188  	 	 	 $fld = new ADOFieldObject;
 189  	 	 	 $fld->name = $r['name'];
 190  	 	 	 $fld->type = $type[0];
 191  	 	 	 $fld->max_length = $size;
 192  	 	 	 $fld->not_null = $r['notnull'];
 193  	 	 	 $fld->default_value = $r['dflt_value'];
 194  	 	 	 $fld->scale = 0;
 195  	 	 	 if (isset($r['pk']) && $r['pk']) {
 196  	 	 	 	 $fld->primary_key=1;
 197  	 	 	 }
 198  	 	 	 if ($save == ADODB_FETCH_NUM) {
 199  	 	 	 	 $arr[] = $fld;
 200  	 	 	 } else {
 201  	 	 	 	 $arr[strtoupper($fld->name)] = $fld;
 202  	 	 	 }
 203  	 	 }
 204  	 	 $rs->Close();
 205  	 	 $ADODB_FETCH_MODE = $save;
 206  	 	 return $arr;
 207  	 }
 208  
 209  	public function metaForeignKeys($table, $owner = '', $upper =  false, $associative =  false)
 210  	 {
 211  	     global $ADODB_FETCH_MODE;
 212  	 	 if ($ADODB_FETCH_MODE == ADODB_FETCH_ASSOC
 213  	 	 || $this->fetchMode == ADODB_FETCH_ASSOC)
 214  	 	 $associative = true;
 215  
 216  	     /*
 217  	 	 * Read sqlite master to find foreign keys
 218  	 	 */
 219  	 	 $sql = "SELECT sql
 220  	 	 	 	  FROM (
 221  	 	 	 	 SELECT sql sql, type type, tbl_name tbl_name, name name
 222  	 	 	 	   FROM sqlite_master
 223  	 	 	           )
 224  	 	 	 	 WHERE type != 'meta'
 225  	 	 	 	   AND sql NOTNULL
 226  	 	 	 	   AND LOWER(name) ='" . strtolower($table) . "'";
 227  
 228  	 	 $tableSql = $this->getOne($sql);
 229  
 230  	 	 $fkeyList = array();
 231  	 	 $ylist = preg_split("/,+/",$tableSql);
 232  	 	 foreach ($ylist as $y)
 233  	 	 {
 234  	 	 	 if (!preg_match('/FOREIGN/',$y))
 235  	 	 	 	 continue;
 236  
 237  	 	 	 $matches = false;
 238  	 	 	 preg_match_all('/\((.+?)\)/i',$y,$matches);
 239  	 	 	 $tmatches = false;
 240  	 	 	 preg_match_all('/REFERENCES (.+?)\(/i',$y,$tmatches);
 241  
 242  	 	 	 if ($associative)
 243  	 	 	 {
 244  	 	 	 	 if (!isset($fkeyList[$tmatches[1][0]]))
 245  	 	 	 	 	 $fkeyList[$tmatches[1][0]]	 = array();
 246  	 	 	 	 $fkeyList[$tmatches[1][0]][$matches[1][0]] = $matches[1][1];
 247  	 	 	 }
 248  	 	 	 else
 249  	 	 	 	 $fkeyList[$tmatches[1][0]][] = $matches[1][0] . '=' . $matches[1][1];
 250  	 	 }
 251  
 252  	 	 if ($associative)
 253  	 	 {
 254  	 	 	 if ($upper)
 255  	 	 	 	 $fkeyList = array_change_key_case($fkeyList,CASE_UPPER);
 256  	 	 	 else
 257  	 	 	 	 $fkeyList = array_change_key_case($fkeyList,CASE_LOWER);
 258  	 	 }
 259  	 	 return $fkeyList;
 260  	 }
 261  
 262  
 263  	function _init($parentDriver)
 264  	 {
 265  	 	 $parentDriver->hasTransactions = false;
 266  	 	 $parentDriver->hasInsertID = true;
 267  	 }
 268  
 269  	protected function _insertID($table = '', $column = '')
 270  	 {
 271  	 	 return $this->_connectionID->lastInsertRowID();
 272  	 }
 273  
 274  	function _affectedrows()
 275  	 {
 276  	 	 return $this->_connectionID->changes();
 277  	 }
 278  
 279  	function ErrorMsg()
 280   	 {
 281  	 	 if ($this->_logsql) {
 282  	 	 	 return $this->_errorMsg;
 283  	 	 }
 284  	 	 return ($this->_errorNo) ? $this->ErrorNo() : ''; //**tochange?
 285  	 }
 286  
 287  	 function ErrorNo()
 288  	 {
 289  	 	 return $this->_connectionID->lastErrorCode(); //**tochange??
 290  	 }
 291  
 292  	 function SQLDate($fmt, $col=false)
 293  	 {
 294  	 	 /*
 295  	 	 * In order to map the values correctly, we must ensure the proper
 296  	 	 * casing for certain fields
 297  	 	 * Y must be UC, because y is a 2 digit year
 298  	 	 * d must be LC, because D is 3 char day
 299  	 	 * A must be UC  because a is non-portable am
 300  	 	 * Q must be UC  because q means nothing
 301  	 	 */
 302  	 	 $fromChars = array('y','D','a','q');
 303  	 	 $toChars   = array('Y','d','A','Q');
 304  	 	 $fmt       = str_replace($fromChars,$toChars,$fmt);
 305  
 306  	 	 $fmt = $this->qstr($fmt);
 307  	 	 return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
 308  	 }
 309  
 310  	function _createFunctions()
 311  	 {
 312  	 	 $this->_connectionID->createFunction('adodb_date', 'adodb_date', 1);
 313  	 	 $this->_connectionID->createFunction('adodb_date2', 'adodb_date2', 2);
 314  	 }
 315  
 316  	 /** @noinspection PhpUnusedParameterInspection */
 317  	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 318  	 {
 319  	 	 if (empty($argHostname) && $argDatabasename) {
 320  	 	 	 $argHostname = $argDatabasename;
 321  	 	 }
 322  	 	 $this->_connectionID = new SQLite3($argHostname);
 323  	 	 $this->_createFunctions();
 324  
 325  	 	 return true;
 326  	 }
 327  
 328  	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 329  	 {
 330  	 	 // There's no permanent connect in SQLite3
 331  	 	 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
 332  	 }
 333  
 334  	 // returns query ID if successful, otherwise false
 335  	function _query($sql,$inputarr=false)
 336  	 {
 337  	 	 $rez = $this->_connectionID->query($sql);
 338  	 	 if ($rez === false) {
 339  	 	 	 $this->_errorNo = $this->_connectionID->lastErrorCode();
 340  	 	 }
 341  	 	 // If no data was returned, we don't need to create a real recordset
 342  	 	 elseif ($rez->numColumns() == 0) {
 343  	 	 	 $rez->finalize();
 344  	 	 	 $rez = true;
 345  	 	 }
 346  
 347  	 	 return $rez;
 348  	 }
 349  
 350  	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 351  	 {
 352  	 	 $nrows = (int) $nrows;
 353  	 	 $offset = (int) $offset;
 354  	 	 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 355  	 	 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 356  	 	 if ($secs2cache) {
 357  	 	 	 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 358  	 	 } else {
 359  	 	 	 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 360  	 	 }
 361  
 362  	 	 return $rs;
 363  	 }
 364  
 365  	 /*
 366  	 	 This algorithm is not very efficient, but works even if table locking
 367  	 	 is not available.
 368  
 369  	 	 Will return false if unable to generate an ID after $MAXLOOPS attempts.
 370  	 */
 371  	 var $_genSeqSQL = "create table %s (id integer)";
 372  
 373  	function GenID($seq='adodbseq',$start=1)
 374  	 {
 375  	 	 // if you have to modify the parameter below, your database is overloaded,
 376  	 	 // or you need to implement generation of id's yourself!
 377  	 	 $MAXLOOPS = 100;
 378  	 	 //$this->debug=1;
 379  	 	 while (--$MAXLOOPS>=0) {
 380  	 	 	 @($num = $this->GetOne("select id from $seq"));
 381  	 	 	 if ($num === false) {
 382  	 	 	 	 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 383  	 	 	 	 $start -= 1;
 384  	 	 	 	 $num = '0';
 385  	 	 	 	 $ok = $this->Execute("insert into $seq values($start)");
 386  	 	 	 	 if (!$ok) {
 387  	 	 	 	 	 return false;
 388  	 	 	 	 }
 389  	 	 	 }
 390  	 	 	 $this->Execute("update $seq set id=id+1 where id=$num");
 391  
 392  	 	 	 if ($this->affected_rows() > 0) {
 393  	 	 	 	 $num += 1;
 394  	 	 	 	 $this->genID = $num;
 395  	 	 	 	 return $num;
 396  	 	 	 }
 397  	 	 }
 398  	 	 if ($fn = $this->raiseErrorFn) {
 399  	 	 	 $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 400  	 	 }
 401  	 	 return false;
 402  	 }
 403  
 404  	function createSequence($seqname='adodbseq', $startID=1)
 405  	 {
 406  	 	 if (empty($this->_genSeqSQL)) {
 407  	 	 	 return false;
 408  	 	 }
 409  	 	 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 410  	 	 if (!$ok) {
 411  	 	 	 return false;
 412  	 	 }
 413  	 	 $startID -= 1;
 414  	 	 return $this->Execute("insert into $seqname values($startID)");
 415  	 }
 416  
 417  	 var $_dropSeqSQL = 'drop table %s';
 418  	function DropSequence($seqname = 'adodbseq')
 419  	 {
 420  	 	 if (empty($this->_dropSeqSQL)) {
 421  	 	 	 return false;
 422  	 	 }
 423  	 	 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 424  	 }
 425  
 426  	 // returns true or false
 427  	function _close()
 428  	 {
 429  	 	 return $this->_connectionID->close();
 430  	 }
 431  
 432  	function metaIndexes($table, $primary = FALSE, $owner = false)
 433  	 {
 434  	 	 $false = false;
 435  	 	 // save old fetch mode
 436  	 	 global $ADODB_FETCH_MODE;
 437  	 	 $save = $ADODB_FETCH_MODE;
 438  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 439  	 	 if ($this->fetchMode !== FALSE) {
 440  	 	 	 $savem = $this->SetFetchMode(FALSE);
 441  	 	 }
 442  
 443  	 	 $pragmaData = array();
 444  
 445  	 	 /*
 446  	 	 * If we want the primary key, we must extract
 447  	 	 * it from the table statement, and the pragma
 448  	 	 */
 449  	 	 if ($primary)
 450  	 	 {
 451  	 	 	 $sql = sprintf('PRAGMA table_info([%s]);',
 452  	 	 	 	 	 	    strtolower($table)
 453  	 	 	 	 	 	    );
 454  	 	 	 $pragmaData = $this->getAll($sql);
 455  	 	 }
 456  
 457  	 	 /*
 458  	 	 * Exclude the empty entry for the primary index
 459  	 	 */
 460  	 	 $sqlite = "SELECT name,sql
 461  	 	 	 	 	  FROM sqlite_master
 462  	 	 	 	 	 WHERE type='index'
 463  	 	 	 	 	   AND sql IS NOT NULL
 464  	 	 	 	 	   AND LOWER(tbl_name)='%s'";
 465  
 466  	 	 $SQL = sprintf($sqlite,
 467  	 	 	 	      strtolower($table)
 468  	 	 	 	 	  );
 469  
 470  	 	 $rs = $this->execute($SQL);
 471  
 472  	 	 if (!is_object($rs)) {
 473  	 	 	 if (isset($savem)) {
 474  	 	 	 	 $this->SetFetchMode($savem);
 475  	 	 	 }
 476  	 	 	 $ADODB_FETCH_MODE = $save;
 477  	 	 	 return $false;
 478  	 	 }
 479  
 480  	 	 $indexes = array ();
 481  
 482  	 	 while ($row = $rs->FetchRow())
 483  	 	 {
 484  
 485  	 	 	 if (!isset($indexes[$row[0]])) {
 486  	 	 	 	 $indexes[$row[0]] = array(
 487  	 	 	 	 	 'unique' => preg_match("/unique/i",$row[1]),
 488  	 	 	 	 	 'columns' => array()
 489  	 	 	 	 );
 490  	 	 	 }
 491  	 	 	 /**
 492  	 	 	  * The index elements appear in the SQL statement
 493  	 	 	  * in cols[1] between parentheses
 494  	 	 	  * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 495  	 	 	  */
 496  	 	 	 preg_match_all('/\((.*)\)/',$row[1],$indexExpression);
 497  	 	 	 $indexes[$row[0]]['columns'] = array_map('trim',explode(',',$indexExpression[1][0]));
 498  	 	 }
 499  
 500  	 	 if (isset($savem)) {
 501  	 	 	 $this->SetFetchMode($savem);
 502  	 	 	 $ADODB_FETCH_MODE = $save;
 503  	 	 }
 504  
 505  	 	 /*
 506  	 	 * If we want primary, add it here
 507  	 	 */
 508  	 	 if ($primary){
 509  
 510  	 	 	 /*
 511  	 	 	 * Check the previously retrieved pragma to search
 512  	 	 	 * with a closure
 513  	 	 	 */
 514  
 515  	 	 	 $pkIndexData = array('unique'=>1,'columns'=>array());
 516  
 517  	 	 	 $pkCallBack = function ($value, $key) use (&$pkIndexData) {
 518  
 519  	 	 	 	 /*
 520  	 	 	 	 * As we iterate the elements check for pk index and sort
 521  	 	 	 	 */
 522  	 	 	 	 if ($value[5] > 0)
 523  	 	 	 	 {
 524  	 	 	 	 	 $pkIndexData['columns'][$value[5]] = strtolower($value[1]);
 525  	 	 	 	 	 ksort($pkIndexData['columns']);
 526  	 	 	 	 }
 527  	 	 	 };
 528  
 529  	 	 	 array_walk($pragmaData,$pkCallBack);
 530  
 531  	 	 	 /*
 532  	 	 	 * If we found no columns, there is no
 533  	 	 	 * primary index
 534  	 	 	 */
 535  	 	 	 if (count($pkIndexData['columns']) > 0)
 536  	 	 	 	 $indexes['PRIMARY'] = $pkIndexData;
 537  	 	 }
 538  
 539  	 	 return $indexes;
 540  	 }
 541  
 542  	 /**
 543  	 * Returns the maximum size of a MetaType C field. Because of the
 544  	 * database design, sqlite places no limits on the size of data inserted
 545  	 *
 546  	 * @return int
 547  	 */
 548  	function charMax()
 549  	 {
 550  	 	 return ADODB_STRINGMAX_NOLIMIT;
 551  	 }
 552  
 553  	 /**
 554  	 * Returns the maximum size of a MetaType X field. Because of the
 555  	 * database design, sqlite places no limits on the size of data inserted
 556  	 *
 557  	 * @return int
 558  	 */
 559  	function textMax()
 560  	 {
 561  	 	 return ADODB_STRINGMAX_NOLIMIT;
 562  	 }
 563  
 564  	 /**
 565  	  * Converts a date to a month only field and pads it to 2 characters
 566  	  *
 567  	  * This uses the more efficient strftime native function to process
 568  	  *
 569  	  * @param string $fld	 The name of the field to process
 570  	  *
 571  	  * @return string The SQL Statement
 572  	  */
 573  	function month($fld)
 574  	 {
 575  	 	 return "strftime('%m',$fld)";
 576  	 }
 577  
 578  	 /**
 579  	  * Converts a date to a day only field and pads it to 2 characters
 580  	  *
 581  	  * This uses the more efficient strftime native function to process
 582  	  *
 583  	  * @param string $fld	 The name of the field to process
 584  	  *
 585  	  * @return string The SQL Statement
 586  	  */
 587  	function day($fld) {
 588  	 	 return "strftime('%d',$fld)";
 589  	 }
 590  
 591  	 /**
 592  	  * Converts a date to a year only field
 593  	  *
 594  	  * This uses the more efficient strftime native function to process
 595  	  *
 596  	  * @param string $fld	 The name of the field to process
 597  	  *
 598  	  * @return string The SQL Statement
 599  	  */
 600  	function year($fld)
 601  	 {
 602  	 	 return "strftime('%Y',$fld)";
 603  	 }
 604  
 605  	 /**
 606  	  * SQLite update for blob
 607  	  *
 608  	  * SQLite must be a fully prepared statement (all variables must be bound),
 609  	  * so $where can either be an array (array params) or a string that we will
 610  	  * do our best to unpack and turn into a prepared statement.
 611  	  *
 612  	  * @param string $table
 613  	  * @param string $column
 614  	  * @param string $val      Blob value to set
 615  	  * @param mixed  $where    An array of parameters (key => value pairs),
 616  	  *                         or a string (where clause).
 617  	  * @param string $blobtype ignored
 618  	  *
 619  	  * @return bool success
 620  	  */
 621  	function updateBlob($table, $column, $val, $where, $blobtype = 'BLOB')
 622  	 {
 623  	 	 if (is_array($where)) {
 624  	 	 	 // We were passed a set of key=>value pairs
 625  	 	 	 $params = $where;
 626  	 	 } else {
 627  	 	 	 // Given a where clause string, we have to disassemble the
 628  	 	 	 // statements into keys and values
 629  	 	 	 $params = array();
 630  	 	 	 $temp = preg_split('/(where|and)/i', $where);
 631  	 	 	 $where = array_filter($temp);
 632  
 633  	 	 	 foreach ($where as $wValue) {
 634  	 	 	 	 $wTemp = preg_split('/[= \']+/', $wValue);
 635  	 	 	 	 $wTemp = array_filter($wTemp);
 636  	 	 	 	 $wTemp = array_values($wTemp);
 637  	 	 	 	 $params[$wTemp[0]] = $wTemp[1];
 638  	 	 	 }
 639  	 	 }
 640  
 641  	 	 $paramWhere = array();
 642  	 	 foreach ($params as $bindKey => $bindValue) {
 643  	 	 	 $paramWhere[] = $bindKey . '=?';
 644  	 	 }
 645  
 646  	 	 $sql = "UPDATE $table SET $column=? WHERE "
 647  	 	 	 . implode(' AND ', $paramWhere);
 648  
 649  	 	 // Prepare the statement
 650  	 	 $stmt = $this->_connectionID->prepare($sql);
 651  
 652  	 	 // Set the first bind value equal to value we want to update
 653  	 	 if (!$stmt->bindValue(1, $val, SQLITE3_BLOB)) {
 654  	 	 	 return false;
 655  	 	 }
 656  
 657  	 	 // Build as many keys as available
 658  	 	 $bindIndex = 2;
 659  	 	 foreach ($params as $bindValue) {
 660  	 	 	 if (is_integer($bindValue) || is_bool($bindValue) || is_float($bindValue)) {
 661  	 	 	 	 $type = SQLITE3_NUM;
 662  	 	 	 } elseif (is_object($bindValue)) {
 663  	 	 	 	 // Assume a blob, this should never appear in
 664  	 	 	 	 // the binding for a where statement anyway
 665  	 	 	 	 $type = SQLITE3_BLOB;
 666  	 	 	 } else {
 667  	 	 	 	 $type = SQLITE3_TEXT;
 668  	 	 	 }
 669  
 670  	 	 	 if (!$stmt->bindValue($bindIndex, $bindValue, $type)) {
 671  	 	 	 	 return false;
 672  	 	 	 }
 673  
 674  	 	 	 $bindIndex++;
 675  	 	 }
 676  
 677  	 	 // Now execute the update. NB this is SQLite execute, not ADOdb
 678  	 	 $ok = $stmt->execute();
 679  	 	 return is_object($ok);
 680  	 }
 681  
 682  	 /**
 683  	  * SQLite update for blob from a file
 684  	  *
 685  	  * @param string $table
 686  	  * @param string $column
 687  	  * @param string $path      Filename containing blob data
 688  	  * @param mixed  $where    {@see updateBlob()}
 689  	  * @param string $blobtype ignored
 690  	  *
 691  	  * @return bool success
 692  	  */
 693  	function updateBlobFile($table, $column, $path, $where, $blobtype = 'BLOB')
 694  	 {
 695  	 	 if (!file_exists($path)) {
 696  	 	 	 return false;
 697  	 	 }
 698  
 699  	 	 // Read file information
 700  	 	 $fileContents = file_get_contents($path);
 701  	 	 if ($fileContents === false)
 702  	 	 	 // Distinguish between an empty file and failure
 703  	 	 	 return false;
 704  
 705  	 	 return $this->updateBlob($table, $column, $fileContents, $where, $blobtype);
 706  	 }
 707  
 708  }
 709  
 710  /*--------------------------------------------------------------------------------------
 711  	 	 Class Name: Recordset
 712  --------------------------------------------------------------------------------------*/
 713  
 714  class ADORecordset_sqlite3 extends ADORecordSet {
 715  
 716  	 var $databaseType = "sqlite3";
 717  	 var $bind = false;
 718  
 719  	 /** @var SQLite3Result */
 720  	 var $_queryID;
 721  
 722  	 /** @noinspection PhpMissingParentConstructorInspection */
 723  	function __construct($queryID,$mode=false)
 724  	 {
 725  	 	 if ($mode === false) {
 726  	 	 	 global $ADODB_FETCH_MODE;
 727  	 	 	 $mode = $ADODB_FETCH_MODE;
 728  	 	 }
 729  	 	 switch($mode) {
 730  	 	 	 case ADODB_FETCH_NUM:
 731  	 	 	 	 $this->fetchMode = SQLITE3_NUM;
 732  	 	 	 	 break;
 733  	 	 	 case ADODB_FETCH_ASSOC:
 734  	 	 	 	 $this->fetchMode = SQLITE3_ASSOC;
 735  	 	 	 	 break;
 736  	 	 	 default:
 737  	 	 	 	 $this->fetchMode = SQLITE3_BOTH;
 738  	 	 	 	 break;
 739  	 	 }
 740  	 	 $this->adodbFetchMode = $mode;
 741  
 742  	 	 $this->_queryID = $queryID;
 743  
 744  	 	 $this->_inited = true;
 745  	 	 $this->fields = array();
 746  	 	 if ($queryID) {
 747  	 	 	 $this->_currentRow = 0;
 748  	 	 	 $this->EOF = !$this->_fetch();
 749  	 	 	 @$this->_initrs();
 750  	 	 } else {
 751  	 	 	 $this->_numOfRows = 0;
 752  	 	 	 $this->_numOfFields = 0;
 753  	 	 	 $this->EOF = true;
 754  	 	 }
 755  
 756  	 	 return $this->_queryID;
 757  	 }
 758  
 759  
 760  	function FetchField($fieldOffset = -1)
 761  	 {
 762  	 	 $fld = new ADOFieldObject;
 763  	 	 $fld->name = $this->_queryID->columnName($fieldOffset);
 764  	 	 $fld->type = 'VARCHAR';
 765  	 	 $fld->max_length = -1;
 766  	 	 return $fld;
 767  	 }
 768  
 769  	function _initrs()
 770  	 {
 771  	 	 $this->_numOfFields = $this->_queryID->numColumns();
 772  
 773  	 }
 774  
 775  	function Fields($colname)
 776  	 {
 777  	 	 if ($this->fetchMode != SQLITE3_NUM) {
 778  	 	 	 return $this->fields[$colname];
 779  	 	 }
 780  	 	 if (!$this->bind) {
 781  	 	 	 $this->bind = array();
 782  	 	 	 for ($i=0; $i < $this->_numOfFields; $i++) {
 783  	 	 	 	 $o = $this->FetchField($i);
 784  	 	 	 	 $this->bind[strtoupper($o->name)] = $i;
 785  	 	 	 }
 786  	 	 }
 787  
 788  	 	 return $this->fields[$this->bind[strtoupper($colname)]];
 789  	 }
 790  
 791  	function _seek($row)
 792  	 {
 793  	 	 // sqlite3 does not implement seek
 794  	 	 if ($this->debug) {
 795  	 	 	 ADOConnection::outp("SQLite3 does not implement seek");
 796  	 	 }
 797  	 	 return false;
 798  	 }
 799  
 800  	function _fetch($ignore_fields=false)
 801  	 {
 802  	 	 $this->fields = $this->_queryID->fetchArray($this->fetchMode);
 803  	 	 return !empty($this->fields);
 804  	 }
 805  
 806  	function _close()
 807  	 {
 808  	 }
 809  
 810  }