810 lines
24 KiB
PHP
810 lines
24 KiB
PHP
<?php
|
|
$drivers["sqlite"] = "SQLite 3";
|
|
$drivers["sqlite2"] = "SQLite 2";
|
|
|
|
if (isset($_GET["sqlite"]) || isset($_GET["sqlite2"])) {
|
|
define("DRIVER", (isset($_GET["sqlite"]) ? "sqlite" : "sqlite2"));
|
|
if (class_exists(isset($_GET["sqlite"]) ? "SQLite3" : "SQLiteDatabase")) {
|
|
if (isset($_GET["sqlite"])) {
|
|
|
|
class Min_SQLite {
|
|
var $extension = "SQLite3", $server_info, $affected_rows, $errno, $error, $_link;
|
|
|
|
function __construct($filename) {
|
|
$this->_link = new SQLite3($filename);
|
|
$version = $this->_link->version();
|
|
$this->server_info = $version["versionString"];
|
|
}
|
|
|
|
function query($query) {
|
|
$result = @$this->_link->query($query);
|
|
$this->error = "";
|
|
if (!$result) {
|
|
$this->errno = $this->_link->lastErrorCode();
|
|
$this->error = $this->_link->lastErrorMsg();
|
|
return false;
|
|
} elseif ($result->numColumns()) {
|
|
return new Min_Result($result);
|
|
}
|
|
$this->affected_rows = $this->_link->changes();
|
|
return true;
|
|
}
|
|
|
|
function quote($string) {
|
|
return (is_utf8($string)
|
|
? "'" . $this->_link->escapeString($string) . "'"
|
|
: "x'" . reset(unpack('H*', $string)) . "'"
|
|
);
|
|
}
|
|
|
|
function store_result() {
|
|
return $this->_result;
|
|
}
|
|
|
|
function result($query, $field = 0) {
|
|
$result = $this->query($query);
|
|
if (!is_object($result)) {
|
|
return false;
|
|
}
|
|
$row = $result->_result->fetchArray();
|
|
return $row[$field];
|
|
}
|
|
}
|
|
|
|
class Min_Result {
|
|
var $_result, $_offset = 0, $num_rows;
|
|
|
|
function __construct($result) {
|
|
$this->_result = $result;
|
|
}
|
|
|
|
function fetch_assoc() {
|
|
return $this->_result->fetchArray(SQLITE3_ASSOC);
|
|
}
|
|
|
|
function fetch_row() {
|
|
return $this->_result->fetchArray(SQLITE3_NUM);
|
|
}
|
|
|
|
function fetch_field() {
|
|
$column = $this->_offset++;
|
|
$type = $this->_result->columnType($column);
|
|
return (object) array(
|
|
"name" => $this->_result->columnName($column),
|
|
"type" => $type,
|
|
"charsetnr" => ($type == SQLITE3_BLOB ? 63 : 0), // 63 - binary
|
|
);
|
|
}
|
|
|
|
function __desctruct() {
|
|
return $this->_result->finalize();
|
|
}
|
|
}
|
|
|
|
} else {
|
|
|
|
class Min_SQLite {
|
|
var $extension = "SQLite", $server_info, $affected_rows, $error, $_link;
|
|
|
|
function __construct($filename) {
|
|
$this->server_info = sqlite_libversion();
|
|
$this->_link = new SQLiteDatabase($filename);
|
|
}
|
|
|
|
function query($query, $unbuffered = false) {
|
|
$method = ($unbuffered ? "unbufferedQuery" : "query");
|
|
$result = @$this->_link->$method($query, SQLITE_BOTH, $error);
|
|
$this->error = "";
|
|
if (!$result) {
|
|
$this->error = $error;
|
|
return false;
|
|
} elseif ($result === true) {
|
|
$this->affected_rows = $this->changes();
|
|
return true;
|
|
}
|
|
return new Min_Result($result);
|
|
}
|
|
|
|
function quote($string) {
|
|
return "'" . sqlite_escape_string($string) . "'";
|
|
}
|
|
|
|
function store_result() {
|
|
return $this->_result;
|
|
}
|
|
|
|
function result($query, $field = 0) {
|
|
$result = $this->query($query);
|
|
if (!is_object($result)) {
|
|
return false;
|
|
}
|
|
$row = $result->_result->fetch();
|
|
return $row[$field];
|
|
}
|
|
}
|
|
|
|
class Min_Result {
|
|
var $_result, $_offset = 0, $num_rows;
|
|
|
|
function __construct($result) {
|
|
$this->_result = $result;
|
|
if (method_exists($result, 'numRows')) { // not available in unbuffered query
|
|
$this->num_rows = $result->numRows();
|
|
}
|
|
}
|
|
|
|
function fetch_assoc() {
|
|
$row = $this->_result->fetch(SQLITE_ASSOC);
|
|
if (!$row) {
|
|
return false;
|
|
}
|
|
$return = array();
|
|
foreach ($row as $key => $val) {
|
|
$return[idf_unescape($key)] = $val;
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function fetch_row() {
|
|
return $this->_result->fetch(SQLITE_NUM);
|
|
}
|
|
|
|
function fetch_field() {
|
|
$name = $this->_result->fieldName($this->_offset++);
|
|
$pattern = '(\[.*]|"(?:[^"]|"")*"|(.+))';
|
|
if (preg_match("~^($pattern\\.)?$pattern\$~", $name, $match)) {
|
|
$table = ($match[3] != "" ? $match[3] : idf_unescape($match[2]));
|
|
$name = ($match[5] != "" ? $match[5] : idf_unescape($match[4]));
|
|
}
|
|
return (object) array(
|
|
"name" => $name,
|
|
"orgname" => $name,
|
|
"orgtable" => $table,
|
|
);
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
} elseif (extension_loaded("pdo_sqlite")) {
|
|
class Min_SQLite extends Min_PDO {
|
|
var $extension = "PDO_SQLite";
|
|
|
|
function __construct($filename) {
|
|
$this->dsn(DRIVER . ":$filename", "", "");
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
if (class_exists("Min_SQLite")) {
|
|
class Min_DB extends Min_SQLite {
|
|
|
|
function __construct() {
|
|
parent::__construct(":memory:");
|
|
$this->query("PRAGMA foreign_keys = 1");
|
|
}
|
|
|
|
function select_db($filename) {
|
|
if (is_readable($filename) && $this->query("ATTACH " . $this->quote(preg_match("~(^[/\\\\]|:)~", $filename) ? $filename : dirname($_SERVER["SCRIPT_FILENAME"]) . "/$filename") . " AS a")) { // is_readable - SQLite 3
|
|
parent::__construct($filename);
|
|
$this->query("PRAGMA foreign_keys = 1");
|
|
$this->query("PRAGMA busy_timeout = 500");
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
function multi_query($query) {
|
|
return $this->_result = $this->query($query);
|
|
}
|
|
|
|
function next_result() {
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
class Min_Driver extends Min_SQL {
|
|
|
|
function insertUpdate($table, $rows, $primary) {
|
|
$values = array();
|
|
foreach ($rows as $set) {
|
|
$values[] = "(" . implode(", ", $set) . ")";
|
|
}
|
|
return queries("REPLACE INTO " . table($table) . " (" . implode(", ", array_keys(reset($rows))) . ") VALUES\n" . implode(",\n", $values));
|
|
}
|
|
|
|
function tableHelp($name) {
|
|
if ($name == "sqlite_sequence") {
|
|
return "fileformat2.html#seqtab";
|
|
}
|
|
if ($name == "sqlite_master") {
|
|
return "fileformat2.html#$name";
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
function idf_escape($idf) {
|
|
return '"' . str_replace('"', '""', $idf) . '"';
|
|
}
|
|
|
|
function table($idf) {
|
|
return idf_escape($idf);
|
|
}
|
|
|
|
function connect() {
|
|
global $adminer;
|
|
list(, , $password) = $adminer->credentials();
|
|
if ($password != "") {
|
|
return lang('Database does not support password.');
|
|
}
|
|
return new Min_DB;
|
|
}
|
|
|
|
function get_databases() {
|
|
return array();
|
|
}
|
|
|
|
function limit($query, $where, $limit, $offset = 0, $separator = " ") {
|
|
return " $query$where" . ($limit !== null ? $separator . "LIMIT $limit" . ($offset ? " OFFSET $offset" : "") : "");
|
|
}
|
|
|
|
function limit1($table, $query, $where, $separator = "\n") {
|
|
global $connection;
|
|
return (preg_match('~^INTO~', $query) || $connection->result("SELECT sqlite_compileoption_used('ENABLE_UPDATE_DELETE_LIMIT')")
|
|
? limit($query, $where, 1, 0, $separator)
|
|
: " $query WHERE rowid = (SELECT rowid FROM " . table($table) . $where . $separator . "LIMIT 1)" //! use primary key in tables with WITHOUT rowid
|
|
);
|
|
}
|
|
|
|
function db_collation($db, $collations) {
|
|
global $connection;
|
|
return $connection->result("PRAGMA encoding"); // there is no database list so $db == DB
|
|
}
|
|
|
|
function engines() {
|
|
return array();
|
|
}
|
|
|
|
function logged_user() {
|
|
return get_current_user(); // should return effective user
|
|
}
|
|
|
|
function tables_list() {
|
|
return get_key_vals("SELECT name, type FROM sqlite_master WHERE type IN ('table', 'view') ORDER BY (name = 'sqlite_sequence'), name");
|
|
}
|
|
|
|
function count_tables($databases) {
|
|
return array();
|
|
}
|
|
|
|
function table_status($name = "") {
|
|
global $connection;
|
|
$return = array();
|
|
foreach (get_rows("SELECT name AS Name, type AS Engine, 'rowid' AS Oid, '' AS Auto_increment FROM sqlite_master WHERE type IN ('table', 'view') " . ($name != "" ? "AND name = " . q($name) : "ORDER BY name")) as $row) {
|
|
$row["Rows"] = $connection->result("SELECT COUNT(*) FROM " . idf_escape($row["Name"]));
|
|
$return[$row["Name"]] = $row;
|
|
}
|
|
foreach (get_rows("SELECT * FROM sqlite_sequence", null, "") as $row) {
|
|
$return[$row["name"]]["Auto_increment"] = $row["seq"];
|
|
}
|
|
return ($name != "" ? $return[$name] : $return);
|
|
}
|
|
|
|
function is_view($table_status) {
|
|
return $table_status["Engine"] == "view";
|
|
}
|
|
|
|
function fk_support($table_status) {
|
|
global $connection;
|
|
return !$connection->result("SELECT sqlite_compileoption_used('OMIT_FOREIGN_KEY')");
|
|
}
|
|
|
|
function fields($table) {
|
|
global $connection;
|
|
$return = array();
|
|
$primary = "";
|
|
foreach (get_rows("PRAGMA table_info(" . table($table) . ")") as $row) {
|
|
$name = $row["name"];
|
|
$type = strtolower($row["type"]);
|
|
$default = $row["dflt_value"];
|
|
$return[$name] = array(
|
|
"field" => $name,
|
|
"type" => (preg_match('~int~i', $type) ? "integer" : (preg_match('~char|clob|text~i', $type) ? "text" : (preg_match('~blob~i', $type) ? "blob" : (preg_match('~real|floa|doub~i', $type) ? "real" : "numeric")))),
|
|
"full_type" => $type,
|
|
"default" => (preg_match("~'(.*)'~", $default, $match) ? str_replace("''", "'", $match[1]) : ($default == "NULL" ? null : $default)),
|
|
"null" => !$row["notnull"],
|
|
"privileges" => array("select" => 1, "insert" => 1, "update" => 1),
|
|
"primary" => $row["pk"],
|
|
);
|
|
if ($row["pk"]) {
|
|
if ($primary != "") {
|
|
$return[$primary]["auto_increment"] = false;
|
|
} elseif (preg_match('~^integer$~i', $type)) {
|
|
$return[$name]["auto_increment"] = true;
|
|
}
|
|
$primary = $name;
|
|
}
|
|
}
|
|
$sql = $connection->result("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = " . q($table));
|
|
preg_match_all('~(("[^"]*+")+|[a-z0-9_]+)\s+text\s+COLLATE\s+(\'[^\']+\'|\S+)~i', $sql, $matches, PREG_SET_ORDER);
|
|
foreach ($matches as $match) {
|
|
$name = str_replace('""', '"', preg_replace('~^"|"$~', '', $match[1]));
|
|
if ($return[$name]) {
|
|
$return[$name]["collation"] = trim($match[3], "'");
|
|
}
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function indexes($table, $connection2 = null) {
|
|
global $connection;
|
|
if (!is_object($connection2)) {
|
|
$connection2 = $connection;
|
|
}
|
|
$return = array();
|
|
$sql = $connection2->result("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = " . q($table));
|
|
if (preg_match('~\bPRIMARY\s+KEY\s*\((([^)"]+|"[^"]*"|`[^`]*`)++)~i', $sql, $match)) {
|
|
$return[""] = array("type" => "PRIMARY", "columns" => array(), "lengths" => array(), "descs" => array());
|
|
preg_match_all('~((("[^"]*+")+|(?:`[^`]*+`)+)|(\S+))(\s+(ASC|DESC))?(,\s*|$)~i', $match[1], $matches, PREG_SET_ORDER);
|
|
foreach ($matches as $match) {
|
|
$return[""]["columns"][] = idf_unescape($match[2]) . $match[4];
|
|
$return[""]["descs"][] = (preg_match('~DESC~i', $match[5]) ? '1' : null);
|
|
}
|
|
}
|
|
if (!$return) {
|
|
foreach (fields($table) as $name => $field) {
|
|
if ($field["primary"]) {
|
|
$return[""] = array("type" => "PRIMARY", "columns" => array($name), "lengths" => array(), "descs" => array(null));
|
|
}
|
|
}
|
|
}
|
|
$sqls = get_key_vals("SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = " . q($table), $connection2);
|
|
foreach (get_rows("PRAGMA index_list(" . table($table) . ")", $connection2) as $row) {
|
|
$name = $row["name"];
|
|
$index = array("type" => ($row["unique"] ? "UNIQUE" : "INDEX"));
|
|
$index["lengths"] = array();
|
|
$index["descs"] = array();
|
|
foreach (get_rows("PRAGMA index_info(" . idf_escape($name) . ")", $connection2) as $row1) {
|
|
$index["columns"][] = $row1["name"];
|
|
$index["descs"][] = null;
|
|
}
|
|
if (preg_match('~^CREATE( UNIQUE)? INDEX ' . preg_quote(idf_escape($name) . ' ON ' . idf_escape($table), '~') . ' \((.*)\)$~i', $sqls[$name], $regs)) {
|
|
preg_match_all('/("[^"]*+")+( DESC)?/', $regs[2], $matches);
|
|
foreach ($matches[2] as $key => $val) {
|
|
if ($val) {
|
|
$index["descs"][$key] = '1';
|
|
}
|
|
}
|
|
}
|
|
if (!$return[""] || $index["type"] != "UNIQUE" || $index["columns"] != $return[""]["columns"] || $index["descs"] != $return[""]["descs"] || !preg_match("~^sqlite_~", $name)) {
|
|
$return[$name] = $index;
|
|
}
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function foreign_keys($table) {
|
|
$return = array();
|
|
foreach (get_rows("PRAGMA foreign_key_list(" . table($table) . ")") as $row) {
|
|
$foreign_key = &$return[$row["id"]];
|
|
//! idf_unescape in SQLite2
|
|
if (!$foreign_key) {
|
|
$foreign_key = $row;
|
|
}
|
|
$foreign_key["source"][] = $row["from"];
|
|
$foreign_key["target"][] = $row["to"];
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function view($name) {
|
|
global $connection;
|
|
return array("select" => preg_replace('~^(?:[^`"[]+|`[^`]*`|"[^"]*")* AS\s+~iU', '', $connection->result("SELECT sql FROM sqlite_master WHERE name = " . q($name)))); //! identifiers may be inside []
|
|
}
|
|
|
|
function collations() {
|
|
return (isset($_GET["create"]) ? get_vals("PRAGMA collation_list", 1) : array());
|
|
}
|
|
|
|
function information_schema($db) {
|
|
return false;
|
|
}
|
|
|
|
function error() {
|
|
global $connection;
|
|
return h($connection->error);
|
|
}
|
|
|
|
function check_sqlite_name($name) {
|
|
// avoid creating PHP files on unsecured servers
|
|
global $connection;
|
|
$extensions = "db|sdb|sqlite";
|
|
if (!preg_match("~^[^\\0]*\\.($extensions)\$~", $name)) {
|
|
$connection->error = lang('Please use one of the extensions %s.', str_replace("|", ", ", $extensions));
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function create_database($db, $collation) {
|
|
global $connection;
|
|
if (file_exists($db)) {
|
|
$connection->error = lang('File exists.');
|
|
return false;
|
|
}
|
|
if (!check_sqlite_name($db)) {
|
|
return false;
|
|
}
|
|
try {
|
|
$link = new Min_SQLite($db);
|
|
} catch (Exception $ex) {
|
|
$connection->error = $ex->getMessage();
|
|
return false;
|
|
}
|
|
$link->query('PRAGMA encoding = "UTF-8"');
|
|
$link->query('CREATE TABLE adminer (i)'); // otherwise creates empty file
|
|
$link->query('DROP TABLE adminer');
|
|
return true;
|
|
}
|
|
|
|
function drop_databases($databases) {
|
|
global $connection;
|
|
$connection->__construct(":memory:"); // to unlock file, doesn't work in PDO on Windows
|
|
foreach ($databases as $db) {
|
|
if (!@unlink($db)) {
|
|
$connection->error = lang('File exists.');
|
|
return false;
|
|
}
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function rename_database($name, $collation) {
|
|
global $connection;
|
|
if (!check_sqlite_name($name)) {
|
|
return false;
|
|
}
|
|
$connection->__construct(":memory:");
|
|
$connection->error = lang('File exists.');
|
|
return @rename(DB, $name);
|
|
}
|
|
|
|
function auto_increment() {
|
|
return " PRIMARY KEY" . (DRIVER == "sqlite" ? " AUTOINCREMENT" : "");
|
|
}
|
|
|
|
function alter_table($table, $name, $fields, $foreign, $comment, $engine, $collation, $auto_increment, $partitioning) {
|
|
global $connection;
|
|
$use_all_fields = ($table == "" || $foreign);
|
|
foreach ($fields as $field) {
|
|
if ($field[0] != "" || !$field[1] || $field[2]) {
|
|
$use_all_fields = true;
|
|
break;
|
|
}
|
|
}
|
|
$alter = array();
|
|
$originals = array();
|
|
foreach ($fields as $field) {
|
|
if ($field[1]) {
|
|
$alter[] = ($use_all_fields ? $field[1] : "ADD " . implode($field[1]));
|
|
if ($field[0] != "") {
|
|
$originals[$field[0]] = $field[1][0];
|
|
}
|
|
}
|
|
}
|
|
if (!$use_all_fields) {
|
|
foreach ($alter as $val) {
|
|
if (!queries("ALTER TABLE " . table($table) . " $val")) {
|
|
return false;
|
|
}
|
|
}
|
|
if ($table != $name && !queries("ALTER TABLE " . table($table) . " RENAME TO " . table($name))) {
|
|
return false;
|
|
}
|
|
} elseif (!recreate_table($table, $name, $alter, $originals, $foreign, $auto_increment)) {
|
|
return false;
|
|
}
|
|
if ($auto_increment) {
|
|
queries("BEGIN");
|
|
queries("UPDATE sqlite_sequence SET seq = $auto_increment WHERE name = " . q($name)); // ignores error
|
|
if (!$connection->affected_rows) {
|
|
queries("INSERT INTO sqlite_sequence (name, seq) VALUES (" . q($name) . ", $auto_increment)");
|
|
}
|
|
queries("COMMIT");
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function recreate_table($table, $name, $fields, $originals, $foreign, $auto_increment, $indexes = array()) {
|
|
global $connection;
|
|
if ($table != "") {
|
|
if (!$fields) {
|
|
foreach (fields($table) as $key => $field) {
|
|
if ($indexes) {
|
|
$field["auto_increment"] = 0;
|
|
}
|
|
$fields[] = process_field($field, $field);
|
|
$originals[$key] = idf_escape($key);
|
|
}
|
|
}
|
|
$primary_key = false;
|
|
foreach ($fields as $field) {
|
|
if ($field[6]) {
|
|
$primary_key = true;
|
|
}
|
|
}
|
|
$drop_indexes = array();
|
|
foreach ($indexes as $key => $val) {
|
|
if ($val[2] == "DROP") {
|
|
$drop_indexes[$val[1]] = true;
|
|
unset($indexes[$key]);
|
|
}
|
|
}
|
|
foreach (indexes($table) as $key_name => $index) {
|
|
$columns = array();
|
|
foreach ($index["columns"] as $key => $column) {
|
|
if (!$originals[$column]) {
|
|
continue 2;
|
|
}
|
|
$columns[] = $originals[$column] . ($index["descs"][$key] ? " DESC" : "");
|
|
}
|
|
if (!$drop_indexes[$key_name]) {
|
|
if ($index["type"] != "PRIMARY" || !$primary_key) {
|
|
$indexes[] = array($index["type"], $key_name, $columns);
|
|
}
|
|
}
|
|
}
|
|
foreach ($indexes as $key => $val) {
|
|
if ($val[0] == "PRIMARY") {
|
|
unset($indexes[$key]);
|
|
$foreign[] = " PRIMARY KEY (" . implode(", ", $val[2]) . ")";
|
|
}
|
|
}
|
|
foreach (foreign_keys($table) as $key_name => $foreign_key) {
|
|
foreach ($foreign_key["source"] as $key => $column) {
|
|
if (!$originals[$column]) {
|
|
continue 2;
|
|
}
|
|
$foreign_key["source"][$key] = idf_unescape($originals[$column]);
|
|
}
|
|
if (!isset($foreign[" $key_name"])) {
|
|
$foreign[] = " " . format_foreign_key($foreign_key);
|
|
}
|
|
}
|
|
queries("BEGIN");
|
|
}
|
|
foreach ($fields as $key => $field) {
|
|
$fields[$key] = " " . implode($field);
|
|
}
|
|
$fields = array_merge($fields, array_filter($foreign));
|
|
$temp_name = ($table == $name ? "adminer_$name" : $name);
|
|
if (!queries("CREATE TABLE " . table($temp_name) . " (\n" . implode(",\n", $fields) . "\n)")) {
|
|
// implicit ROLLBACK to not overwrite $connection->error
|
|
return false;
|
|
}
|
|
if ($table != "") {
|
|
if ($originals && !queries("INSERT INTO " . table($temp_name) . " (" . implode(", ", $originals) . ") SELECT " . implode(", ", array_map('idf_escape', array_keys($originals))) . " FROM " . table($table))) {
|
|
return false;
|
|
}
|
|
$triggers = array();
|
|
foreach (triggers($table) as $trigger_name => $timing_event) {
|
|
$trigger = trigger($trigger_name);
|
|
$triggers[] = "CREATE TRIGGER " . idf_escape($trigger_name) . " " . implode(" ", $timing_event) . " ON " . table($name) . "\n$trigger[Statement]";
|
|
}
|
|
$auto_increment = $auto_increment ? 0 : $connection->result("SELECT seq FROM sqlite_sequence WHERE name = " . q($table)); // if $auto_increment is set then it will be updated later
|
|
if (!queries("DROP TABLE " . table($table)) // drop before creating indexes and triggers to allow using old names
|
|
|| ($table == $name && !queries("ALTER TABLE " . table($temp_name) . " RENAME TO " . table($name)))
|
|
|| !alter_indexes($name, $indexes)
|
|
) {
|
|
return false;
|
|
}
|
|
if ($auto_increment) {
|
|
queries("UPDATE sqlite_sequence SET seq = $auto_increment WHERE name = " . q($name)); // ignores error
|
|
}
|
|
foreach ($triggers as $trigger) {
|
|
if (!queries($trigger)) {
|
|
return false;
|
|
}
|
|
}
|
|
queries("COMMIT");
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function index_sql($table, $type, $name, $columns) {
|
|
return "CREATE $type " . ($type != "INDEX" ? "INDEX " : "")
|
|
. idf_escape($name != "" ? $name : uniqid($table . "_"))
|
|
. " ON " . table($table)
|
|
. " $columns"
|
|
;
|
|
}
|
|
|
|
function alter_indexes($table, $alter) {
|
|
foreach ($alter as $primary) {
|
|
if ($primary[0] == "PRIMARY") {
|
|
return recreate_table($table, $table, array(), array(), array(), 0, $alter);
|
|
}
|
|
}
|
|
foreach (array_reverse($alter) as $val) {
|
|
if (!queries($val[2] == "DROP"
|
|
? "DROP INDEX " . idf_escape($val[1])
|
|
: index_sql($table, $val[0], $val[1], "(" . implode(", ", $val[2]) . ")")
|
|
)) {
|
|
return false;
|
|
}
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function truncate_tables($tables) {
|
|
return apply_queries("DELETE FROM", $tables);
|
|
}
|
|
|
|
function drop_views($views) {
|
|
return apply_queries("DROP VIEW", $views);
|
|
}
|
|
|
|
function drop_tables($tables) {
|
|
return apply_queries("DROP TABLE", $tables);
|
|
}
|
|
|
|
function move_tables($tables, $views, $target) {
|
|
return false;
|
|
}
|
|
|
|
function trigger($name) {
|
|
global $connection;
|
|
if ($name == "") {
|
|
return array("Statement" => "BEGIN\n\t;\nEND");
|
|
}
|
|
$idf = '(?:[^`"\s]+|`[^`]*`|"[^"]*")+';
|
|
$trigger_options = trigger_options();
|
|
preg_match(
|
|
"~^CREATE\\s+TRIGGER\\s*$idf\\s*(" . implode("|", $trigger_options["Timing"]) . ")\\s+([a-z]+)(?:\\s+OF\\s+($idf))?\\s+ON\\s*$idf\\s*(?:FOR\\s+EACH\\s+ROW\\s)?(.*)~is",
|
|
$connection->result("SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = " . q($name)),
|
|
$match
|
|
);
|
|
$of = $match[3];
|
|
return array(
|
|
"Timing" => strtoupper($match[1]),
|
|
"Event" => strtoupper($match[2]) . ($of ? " OF" : ""),
|
|
"Of" => idf_unescape($of),
|
|
"Trigger" => $name,
|
|
"Statement" => $match[4],
|
|
);
|
|
}
|
|
|
|
function triggers($table) {
|
|
$return = array();
|
|
$trigger_options = trigger_options();
|
|
foreach (get_rows("SELECT * FROM sqlite_master WHERE type = 'trigger' AND tbl_name = " . q($table)) as $row) {
|
|
preg_match('~^CREATE\s+TRIGGER\s*(?:[^`"\s]+|`[^`]*`|"[^"]*")+\s*(' . implode("|", $trigger_options["Timing"]) . ')\s*(.*?)\s+ON\b~i', $row["sql"], $match);
|
|
$return[$row["name"]] = array($match[1], $match[2]);
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function trigger_options() {
|
|
return array(
|
|
"Timing" => array("BEFORE", "AFTER", "INSTEAD OF"),
|
|
"Event" => array("INSERT", "UPDATE", "UPDATE OF", "DELETE"),
|
|
"Type" => array("FOR EACH ROW"),
|
|
);
|
|
}
|
|
|
|
function begin() {
|
|
return queries("BEGIN");
|
|
}
|
|
|
|
function last_id() {
|
|
global $connection;
|
|
return $connection->result("SELECT LAST_INSERT_ROWID()");
|
|
}
|
|
|
|
function explain($connection, $query) {
|
|
return $connection->query("EXPLAIN QUERY PLAN $query");
|
|
}
|
|
|
|
function found_rows($table_status, $where) {
|
|
}
|
|
|
|
function types() {
|
|
return array();
|
|
}
|
|
|
|
function schemas() {
|
|
return array();
|
|
}
|
|
|
|
function get_schema() {
|
|
return "";
|
|
}
|
|
|
|
function set_schema($scheme) {
|
|
return true;
|
|
}
|
|
|
|
function create_sql($table, $auto_increment, $style) {
|
|
global $connection;
|
|
$return = $connection->result("SELECT sql FROM sqlite_master WHERE type IN ('table', 'view') AND name = " . q($table));
|
|
foreach (indexes($table) as $name => $index) {
|
|
if ($name == '') {
|
|
continue;
|
|
}
|
|
$return .= ";\n\n" . index_sql($table, $index['type'], $name, "(" . implode(", ", array_map('idf_escape', $index['columns'])) . ")");
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function truncate_sql($table) {
|
|
return "DELETE FROM " . table($table);
|
|
}
|
|
|
|
function use_sql($database) {
|
|
}
|
|
|
|
function trigger_sql($table) {
|
|
return implode(get_vals("SELECT sql || ';;\n' FROM sqlite_master WHERE type = 'trigger' AND tbl_name = " . q($table)));
|
|
}
|
|
|
|
function show_variables() {
|
|
global $connection;
|
|
$return = array();
|
|
foreach (array("auto_vacuum", "cache_size", "count_changes", "default_cache_size", "empty_result_callbacks", "encoding", "foreign_keys", "full_column_names", "fullfsync", "journal_mode", "journal_size_limit", "legacy_file_format", "locking_mode", "page_size", "max_page_count", "read_uncommitted", "recursive_triggers", "reverse_unordered_selects", "secure_delete", "short_column_names", "synchronous", "temp_store", "temp_store_directory", "schema_version", "integrity_check", "quick_check") as $key) {
|
|
$return[$key] = $connection->result("PRAGMA $key");
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function show_status() {
|
|
$return = array();
|
|
foreach (get_vals("PRAGMA compile_options") as $option) {
|
|
list($key, $val) = explode("=", $option, 2);
|
|
$return[$key] = $val;
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
function convert_field($field) {
|
|
}
|
|
|
|
function unconvert_field($field, $return) {
|
|
return $return;
|
|
}
|
|
|
|
function support($feature) {
|
|
return preg_match('~^(columns|database|drop_col|dump|indexes|descidx|move_col|sql|status|table|trigger|variables|view|view_trigger)$~', $feature);
|
|
}
|
|
|
|
function driver_config() {
|
|
$types = array("integer" => 0, "real" => 0, "numeric" => 0, "text" => 0, "blob" => 0);
|
|
return array(
|
|
'possible_drivers' => array((isset($_GET["sqlite"]) ? "SQLite3" : "SQLite"), "PDO_SQLite"),
|
|
'jush' => "sqlite",
|
|
'types' => $types,
|
|
'structured_types' => array_keys($types),
|
|
'unsigned' => array(),
|
|
'operators' => array("=", "<", ">", "<=", ">=", "!=", "LIKE", "LIKE %%", "IN", "IS NULL", "NOT LIKE", "NOT IN", "IS NOT NULL", "SQL"), // REGEXP can be user defined function
|
|
'functions' => array("distinct", "hex", "length", "lower", "round", "unixepoch", "upper"),
|
|
'grouping' => array("avg", "count", "count distinct", "group_concat", "max", "min", "sum"),
|
|
'edit_functions' => array(
|
|
array(
|
|
// "text" => "date('now')/time('now')/datetime('now')",
|
|
), array(
|
|
"integer|real|numeric" => "+/-",
|
|
// "text" => "date/time/datetime",
|
|
"text" => "||",
|
|
)
|
|
),
|
|
);
|
|
}
|
|
}
|