See Release Notes
Long Term Support Release
Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401]
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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body