" . ucwords($fnc) . ": " . htmlentities($query) . ""; if(empty($this->db) && !$this->SQLite && !in_array($fnc, ["__construct", "changeDB", "dbList"])) throw new SSQLException("(" . $fnc . "): No database selected"); try { $qr = $this->connect->query($query); } catch(PDOException $e) { if($flags & self::NO_ERROR) return false; else throw new SSQLException("(" . $fnc . "): Database error: {$e->getMessage()} SQL query:
" . (empty($query) ? "Missing" : $query) . "");
};
if(!$qr && !($flags & self::NO_ERROR))
throw new SSQLException("(" . $fnc . "): Database error: {$this->connect->errorInfo()[2]} SQL query: " . (empty($query) ? "Missing" : $query) . "");
elseif(!$qr)
return false;
$this->result = new SSQLResult($qr);
return $this->result;
}
public function queryf(string $q, array $a, int $flags = 0, string $fnc = "queryf") {
for($i = (count($a) - 1); $i>=0; $i--) {
$k = array_keys($a)[$i];
$v = $a[$k];
$q = str_replace("%" . $k, $this->escape($v), $q);
};
return $this->query($q, $flags, $fnc);
}
public function __set(string $name, string $query) {
$this->fncs[$name] = $query;
}
public function __get(string $name) {
return $this->fncs[$name];
}
public function __call(string $name, array $params) {
if(isset($params[0]) && is_array($params[0]))
$this->execFnc($name, $params[0]);
else
$this->execFnc($name, $params);
}
public function __isset($name) {
return isset($this->fncs[$name]);
}
public function __unset($name) {
if(isset($this->fncs[$name]))
unset($this->fncs[$name]);
return true;
}
public function setFnc(string $name, string $query) {
$this->fncs[$name] = $query;
}
public function execFnc(string $name, array $params = [], int $flags = 0) {
if(isset($this->fncs[$name]))
$this->queryf($this->fncs[$name], $params, $flags, $name);
else
throw new SSQLException("(" . $name . "): This function is not defined");
}
public function dbList(int $flags = 0) {
if($this->SQLite)
throw new SSQLException("(dbList): Not supported in SQLite");
$this->query("SHOW DATABASES", $flags, "dbList");
$r = [];
foreach($this->result as $k => $v) {
$r[] = $v->Database;
};
return $r;
}
public function tableList(int $flags = 0) {
if(empty($this->db) && !$this->SQLite)
throw new SSQLException("(tableList): No database selected");
$this->query(($this->SQLite ? "SELECT `name` FROM `sqlite_master` WHERE type = 'table' ORDER BY `name` ASC" : ("SHOW TABLES FROM `{$this->escape($this->db)}`")), $flags, "tableList");
$r = [];
foreach($this->result as $k => $v) {
$r[] = $v->name;
};
return $r;
}
public function charset(string $charset, int $flags = 0) {
return $this->query("SET NAMES {$this->escape($charset)}", $flags, "charset");
}
public function deleteDB(string $db, int $flags) {
if($this->SQLite)
throw new SSQLException("Not supported in SQLite");
if($this->query("DROP DATABASE `{$this->escape($db)}`", $flags, "deleteDB")) {
return true;
}
else {
throw new SSQLException("(deleteDB): Cannot delete database " . $db);
return false;
};
}
public function changeDB(string $newDB) {
$this->connect = NULL;
if($this->SQLite)
$this->host = $newDB;
else
$this->db = $newDB;
$this->reload();
}
public function select(string $table, string $order = "", $cols = "*", $limit = NULL, int $flags = 129, string $name = "select") {
$callbacks = [];
if(is_array($cols)) {
$colsValue = "";
foreach($cols as $k => $v) {
if($v instanceof Closure) {
$callbacks[$k] = $v;
continue;
};
if(!empty($colsValue))
$colsValue.= ", ";
if(is_int($k))
$colsValue.= "`{$this->escape($v)}`";
else
$colsValue.= "`{$this->escape($v)}` AS `{$this->escape($k)}`";
};
}
else
$colsValue = strval($cols);
$limitString = ($limit===NULL || $limit==="") ? "" : " LIMIT " . intval($this->escape($limit));
if(!empty($order))
$order = " ORDER BY `{$this->escape($order)}`" . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
$r = $this->query("
SELECT {$colsValue} FROM `{$this->escape($table)}`{$order}{$limitString}
", $flags, $name);
$r->callbacks = $callbacks;
return $r;
}
public function selectWhere(string $table, $cond, string $order = "", $cols = "*", $limit = NULL, int $flags = 129, string $name = "selectWhere") {
$callbacks = [];
$all = !boolval($flags & self::COND_OR);
if(is_array($cols)) {
$colsValue = "";
foreach($cols as $k => $v) {
if($v instanceof Closure) {
$callbacks[$k] = $v;
continue;
};
if(!empty($colsValue))
$colsValue.= ", ";
if(is_int($k))
$colsValue.= "`{$this->escape($v)}`";
else
$colsValue.= "`{$this->escape($v)}` AS `{$this->escape($k)}`";
};
}
else
$colsValue = strval($cols);
$condString = $this->getCondString($cond, $all);
$limitString = ($limit===NULL || $limit==="") ? "" : " LIMIT " . intval($this->escape($limit));
if(!empty($order))
$order = " ORDER BY `{$this->escape($order)}`" . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
$r = $this->query("
SELECT {$colsValue} FROM `{$this->escape($table)}` WHERE {$condString}{$order}{$limitString}
", $flags, $name);
$r->callbacks = $callbacks;
return $r;
}
public function selectJoin(string $table, string $join, $on, string $order = "", $cols = "*", $limit = NULL, int $flags = 133, string $name = "selectJoin") {
$callbacks = [];
$all = !boolval($flags & self::COND_OR);
switch(true) {
case boolval($flags & self::JOIN_LEFT): $jt = "LEFT OUTER"; break;
case boolval($flags & self::JOIN_RIGHT): $jt = "RIGHT OUTER"; break;
case boolval($flags & self::JOIN_FULL): $jt = "FULL OUTER"; break;
default: $jt = "INNER";
};
if(is_array($cols)) {
$colsValue = "";
foreach($cols as $k => $v) {
if($v instanceof Closure) {
$callbacks[$k] = $v;
continue;
};
if(!empty($colsValue))
$colsValue.= ", ";
if(is_int($k))
$colsValue.= "`{$this->escape($v)}`";
else
$colsValue.= "`{$this->escape($v)}` AS `{$this->escape($k)}`";
};
}
else
$colsValue = strval($cols);
$onString = $this->getCondString($on, $all, true);
$limitString = ($limit===NULL || $limit==="") ? "" : " LIMIT " . intval($this->escape($limit));
if(!empty($order))
$order = " ORDER BY `{$this->escape($order)}` " . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
$r = $this->query("
SELECT {$colsValue}
FROM `{$this->escape($table)}`
{$jt} JOIN `{$this->escape($join)}` ON {$onString}{$order}{$limitString}
", $flags, $name);
$r->callbacks = $callbacks;
return $r;
}
public function selectJoinWhere(string $table, string $join, $on, $cond, string $order = "", $cols = "*", $limit = NULL, int $flags = 133, string $name = "selectJoinWhere") {
$callbacks = [];
$all = !boolval($flags & self::COND_OR);
switch(true) {
case boolval($flags & self::JOIN_LEFT): $jt = "LEFT OUTER"; break;
case boolval($flags & self::JOIN_RIGHT): $jt = "RIGHT OUTER"; break;
case boolval($flags & self::JOIN_FULL): $jt = "FULL OUTER"; break;
default: $jt = "INNER";
};
if(is_array($cols)) {
$colsValue = "";
foreach($cols as $k => $v) {
if($v instanceof Closure) {
$callbacks[$k] = $v;
continue;
};
if(!empty($colsValue))
$colsValue.= ", ";
if(is_int($k))
$colsValue.= "{$this->escape($v)}";
else
$colsValue.= "`{$this->escape($v)}` AS `{$this->escape($k)}`";
};
}
else
$colsValue = strval($cols);
$onString = $this->getCondString($on, $all, true);
$condString = $this->getCondString($cond, $all);
$limitString = ($limit===NULL || $limit==="") ? "" : " LIMIT " . intval($this->escape($limit));
if(!empty($order))
$order = " ORDER BY `{$this->escape($order)}` " . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
$r = $this->query("
SELECT {$colsValue}
FROM `{$this->escape($table)}`
{$jt} JOIN `{$this->escape($join)}` ON {$onString}
WHERE {$condString}{$order}{$limitString}
", $flags, $name);
$r->callbacks = $callbacks;
return $r;
}
public function exists(string $table, $cond, int $flags = 129, string $name = "exists") {
$this->selectWhere($table, $cond, "", "*", NULL, $flags, $name);
return isset($this->result[0]);
}
public function truncate(string $table, int $flags = 0, string $name = "truncate") {
if($this->SQLite)
$query = "DELETE FROM `{$this->escape($table)}`";
else
$query = "TRUNCATE `{$this->escape($table)}`";
return $this->query($query, $flags, $name);
}
public function insert(string $table, array $values, int $flags = 0, string $name = "insert") {
$cols = [""];
$useCols = true;
foreach($values as $k => $v) {
if(gettype($k)=="integer")
$useCols = false;
};
if($useCols)
$cols = array_keys($values);
$values = array_values($values);
if($cols==[""] || $cols=="")
$colString = "";
else {
$colString = " (";
foreach($cols as $key => $value) {
if($key!=0) $colString.= ", ";
$colString.= "`" . $this->escape($value) . "`";
};
$colString.= ")";
};
$valueString = "";
foreach($values as $key => $value) {
if($key!=array_keys($values, array_values($values)[0])[0]) $valueString.= ", ";
if($value===NULL)
$valueString.= "NULL";
elseif(preg_match("/^[\x{0020}-\x{007E}\x{00A0}-ſ]*$/", $value) && !preg_match("/\[x{0027}\x{005C}]/", $value))
$valueString.= "'" . $this->escape($value) . "'";
elseif($this->SQLite)
$valueString.= "x'" . bin2hex($value) . "'";
else
$valueString.= "0x" . bin2hex($value);
};
$r = $this->query("
INSERT INTO `{$this->escape($table)}`{$colString} VALUES ({$valueString})
", $flags, $name);
return (boolval($flags & self::INSERT_RETURN_ID) ? $this->connect->lastInsertId() : $r);
}
public function delete(string $table, $cond, int $flags = 128, string $name = "delete") {
$all = !boolval($flags & self::COND_OR);
$condString = $this->getCondString($cond, $all);
return $this->query("
DELETE FROM `{$this->escape($table)}` WHERE {$condString}
", $flags, $name);
}
public function update(string $table, $cond, array $values, int $flags = 128, string $name = "update") {
$all = !boolval($flags & self::COND_OR);
$condString = $this->getCondString($cond, $all);
$string = "";
foreach($values as $key => $value) {
if($string!="")
$string.= ", ";
$string.= "`" . $this->escape($key) . "` = ";
if($value===NULL)
$string.= "NULL";
elseif(is_int($value) || is_float($value))
$string.= strval($value);
elseif(is_bool($value))
$string.= $value ? "1" : "0";
elseif(preg_match("/^[\x{0020}-\x{007E}\x{00A0}-ſ]*$/", $value) && !preg_match("/[\x{0027}\x{005C}]/", $value))
$string.= "'" . $this->escape($value) . "'";
elseif($this->SQLite)
$string.= "x'" . bin2hex($value) . "'";
else
$string.= "0x" . bin2hex($value);
};
return $this->query("
UPDATE `{$this->escape($table)}` SET {$string} WHERE {$condString}
", $flags, $name);
}
public function addColumn(string $table, string $name, array $params, int $flags = 0) {
$valueString = $this->getParameters([$name => $params], "addColumn")[0];
return $this->query("
ALTER TABLE `{$this->escape($table)}`\nADD {$valueString}
", $flags, "addColumn");
}
public function removeColumn(string $table, string $name, int $flags = 0) {
return $this->query("
ALTER TABLE `{$this->escape($table)}` DROP COLUMN `{$this->escape($name)}`
", $flags, "removeColumn");
}
public function renameColumn(string $table, string $oldName, string $newName, int $flags = 0) {
return $this->query("
ALTER TABLE `{$this->escape($table)}` RENAME COLUMN `{$this->escape($oldName)}` TO `{$this->escape($newName)}`
", $flags, "renameColumn");
}
public function read(string $table, $cond = [], int $flags = 129) {
$r = $this->result;
if(is_int($cond) && $flags==129) {
$flags = $cond;
$cond = [];
};
if($cond===[])
$this->select($table, "", "*", NULL, $flags, "read");
else
$this->selectWhere($table, $cond, "", "*", NULL, $flags, "read");
return $this->result;
}
public function createTable(string $table, array $columns, $primary = NULL, int $flags = 0) {
$parameters = $this->getParameters($columns, "createTable", $primary);
$valueString = implode(",\n", $parameters);
$query = "CREATE TABLE `{$this->escape($table)}` ({$valueString})";
return $this->query($query, $flags, "createTable");
}
public function renameTable(string $table, string $newname, int $flags = 0) {
return $this->query("
ALTER TABLE `{$this->escape($table)}` RENAME TO `{$this->escape($newname)}`
", $flags, "renameTable");
}
public function deleteTable(string $table, int $flags = 0) {
return $this->query("
DROP TABLE `{$this->escape($table)}`
", $flags, "deleteTable");
}
private function getParameters(array $columns, string $name, $primary = NULL) {
foreach($columns as $k => $v) {
$cname = $this->escape($k);
$t = $this->escape($v['type']);
$s = (isset($v['size']) && is_int($v['size'])) ? strval($v['size']) : NULL;
$n = ($v['NULL'] ?? false) ? "NULL" : "NOT NULL";
$d = ($v['default'] ?? NULL)!==NULL ? (" DEFAULT " . ((is_int($v['default']) || is_float($v['default']) || is_bool($v['default'])) ? ($v['default']===false ? 0 : strval($v['default'])) : "\"" . $this->escape(strval($v['default'])) . "\"")) : "";
$o = empty($v['other']) ? "" : (" " . $v['other']);
$a = (!$this->SQLite && $name=="addColumn" && !empty($v['after'])) ? (" AFTER `{$this->escape($v['after'])}`") : "";
if(!$s)
$r[] = "`{$cname}` {$t} {$n}{$d}{$o}{$a}";
else
$r[] = "`{$cname}` {$t}({$s}) {$n}{$d}{$o}{$a}";
};
if($name!="add" && $primary)
$r[] = "PRIMARY KEY (`{$this->escape($primary)}`)";
return $r;
}
public function q(string $q, ...$a) {
if($a==[])
return $this->query($q, 0, "q");
else
return $this->queryf($q, $a, 0, "q");
}
public function qs(...$qs) {
$r = [];
foreach($qs as $k => $v) {
$r[] = $this->query($v);
};
if(count($r)==1)
$r = $r[0];
return $r;
}
public function p(string $q) {
return new SSQLQuery($this, $q);
}
public function get(string $table, $options = [], int $flags = 0) {
if(is_int($options) && $flags==0) {
$flags = $options;
$options = [];
};
if(empty($options['cond_type']))
$condtype = boolval($flags & self::COND_OR) ? self::COND_OR : self::COND_AND;
else
$condtype = $options['cond_type'];
if(empty($options['order_type']))
$ordertype = boolval($flags & self::ORDER_DESC) ? self::ORDER_DESC : self::ORDER_ASC;
else
$ordertype = $options['order_type'];
if(empty($options['join_type']))
$jointype = boolval($flags & self::JOIN_FULL) ? self::JOIN_FULL : (boolval($flags & self::JOIN_RIGHT) ? self::JOIN_RIGHT : (boolval($flags & self::JOIN_LEFT) ? self::JOIN_LEFT : self::JOIN_INNER));
else
$jointype = $options['join_type'];
$cond = $options['cond'] ?? false;
$join = $options['join'] ?? false;
$on = $options['on'] ?? false;
$cols = $options['cols'] ?? "*";
$order = $options['order'] ?? "";
$limit = $options['limit'] ?? NULL;
if(!$cond && (!$join || !$on))
$result = $this->select($table, $order, $cols, $limit, $flags | $ordertype, "get");
if($cond && (!$join || !$on))
$result = $this->selectWhere($table, $cond, $order, $cols, $limit, $flags | $condtype | $ordertype, "get");
if(!$cond && $join)
$result = $this->selectJoin($table, $join, $on, $order, $cols, $limit, $flags | $jointype | $ordertype, "get");
if($cond && $join)
$result = $this->selectJoinWhere($table, $join, $on, $cond, $order, $cols, $limit, $flags | $jointype | $condtype | $ordertype, "get");
return $this->result;
}
public function put(string $table, $data, $cond = NULL, int $flags = 0) {
if(is_int($cond) && $flags==0) {
$flags = $cond;
$cond = NULL;
};
if(empty($cond)) {
if($data==NULL)
return $this->truncate($table, $flags, "put");
else
return $this->insert($table, $data, $flags, "put");
};
if($data===NULL)
return $this->delete($table, $cond, $flags, "put");
return $this->update($table, $cond, $data, $flags, "put");
}
public function cond($cond = "") {
return new SSQLCond($this, $cond);
}
private function getCondString($a, bool $and, bool $on = false) {
if(!is_array($a))
return strval($a);
$r = "";
foreach($a as $k => $v) {
if(is_array($v)) {
$r.= "(";
foreach($v as $k2 => $v2) {
if(is_numeric($k))
$r.= $v;
else {
$r.= "`{$this->escape($k)}`";
$r.= $v===NULL ? " IS " : " = ";
if(is_numeric($v2))
$v2 = intval($v2);
if(is_numeric($v2))
$r.= $v2;
elseif($on)
$r.= "`{$this->escape($v2)}`";
elseif($v2===NULL)
$r.= "NULL";
elseif(preg_match("/^[\x{0020}-\x{007E}\x{00A0}-ſ]*$/", $v2) && !preg_match("/[\x{0027}\x{005C}]/", $v2))
$r.= "'" . $this->escape($v2) . "'";
elseif($this->SQLite)
$r.= "x'" . bin2hex($v2) . "'";
else
$r.= "0x" . bin2hex($v2);
};
$r.= " OR ";
};
$r = rtrim($r, " OR ");
$r.= $and ? ") AND " : ") OR ";
}
else {
if(is_numeric($k))
$r.= $v;
else {
$r.= "`{$this->escape($k)}`";
$r.= $v===NULL ? " IS " : " = ";
if(is_numeric($v))
$v = intval($v);
if(is_numeric($v))
$r.= $v;
elseif($on)
$r.= "`{$this->escape($v)}`";
elseif($v===NULL)
$r.= "NULL";
elseif(preg_match("/^[\x{0020}-\x{007E}\x{00A0}-ſ]*$/", $v) && !preg_match("/[\x{0027}\x{005C}]/", $v))
$r.= "'" . $this->escape($v) . "'";
elseif($this->SQLite)
$r.= "x'" . bin2hex($v) . "'";
else
$r.= "0x" . bin2hex($v);
};
$r.= $and ? " AND " : " OR ";
};
};
return rtrim($r, $and ? " AND " : " OR ");
}
public function __debugInfo() {
return ['db' => ($this->SQLite ? "SQLite" : "MySQL"), ($this->SQLite ? 'file' : 'host') => $this->host, 'user' => $this->user, 'password' => preg_replace("/./", "*", $this->password), 'database' => $this->db, 'lastError' => $this->connect->errorInfo()];
}
public function __sleep() {
$this->connect = NULL;
}
public function __wakeup() {
$this->__construct($this->host, $this->user, $this->password, $this->db);
}
};
class SSQLResult implements ArrayAccess, Iterator, Countable {
private $pdos;
private $data = NULL;
private $columnCount = 0;
private $columns = NULL;
private $callbacks = [];
private $secondLevel = false;
private $pos = 0;
public function __construct(PDOStatement $pdos, $row = NULL) {
$this->pdos = $pdos;
$this->columnCount = $this->pdos->columnCount();
if($row!==NULL) {
$this->secondLevel = true;
$this->data = [$row];
};
}
public function getColumns() {
if($this->columns!==NULL)
return $this->columns;
$columns = [];
$i = 0;
while($i++ < $this->columnCount) {
$meta = $this->pdos->getColumnMeta($i - 1);
if(!$meta) {
$columns[$i - 1] = NULL;
continue;
};
$columns[$i - 1] = new StdClass();
$columns[$i - 1]->name = $meta['name'];
$columns[$i - 1]->table = $meta['table'];
if(isset($meta['mysql:decl_type']))
$columns[$i - 1]->type = strtoupper($meta['mysql:decl_type']);
elseif(isset($meta['sqlite:decl_type'])) {
$columns[$i - 1]->type = strtoupper(explode("(", $meta['sqlite:decl_type'])[0]);
if(count(explode("(", $meta['sqlite:decl_type'])) > 1)
$columns[$i - 1]->size = intval(rtrim(explode("(", $meta['sqlite:decl_type'])[1], ")"));
else
$columns[$i - 1]->size = NULL;
}
else {
switch($meta['pdo_type']) {
case PDO::PARAM_BOOL: $columns[$i - 1]->type = "BOOL"; break;
case PDO::PARAM_INT: $columns[$i - 1]->type = "INT"; break;
case PDO::PARAM_STR: $columns[$i - 1]->type = $meta['native_type']=="VAR_STRING" ? "VARCHAR" : ($meta['native_type']=="LONGLONG" ? "BIGINT" : "CHAR"); break;
default: $columns[$i - 1]->type = NULL;
};
if($meta['native_type']=="TINY")
$columns[$i - 1]->type = "TINYINT";
if($meta['native_type']=="DOUBLE")
$columns[$i - 1]->type = "DOUBLE";
if($meta['native_type']=="DATE")
$columns[$i - 1]->type = "DATE";
};
if($columns[$i - 1]->type=="INTEGER")
$columns[$i - 1]->type = "INT";
$columns[$i - 1]->flags = $meta['flags'];
if(isset(array_flip($meta['flags'])['blob'])) {
$columns[$i - 1]->type = "BLOB";
unset($columns[$i - 1]->flags[array_flip($meta['flags'])['blob']]);
};
};
$this->columns = $columns;
return $columns;
}
private function fetch() {
if($this->data!==NULL)
return $this->data;
$data = [];
while(($row = $this->pdos->fetchObject())!==false) {
if(!empty($row) && !empty($this->callbacks)) {
foreach($this->callbacks as $k => $v) {
if($k==="")
$v($row);
else
$row->$k = $v($row);
};
};
$data[] = $row;
};
$this->data = $data;
return $data;
}
public function data() {
return $this->fetch();
}
public function dump($limit = 256, bool $columntypes = true, bool $print = true) {
$f = $this->fetch();
$limit = $limit ? intval($limit) : false;
$h = "";
$h.= "| "; if(count($tables) > 1) $h.= $columns[$i - 1]->table . "."; if($columns[$i - 1]) { $h.= $columns[$i - 1]->name; if($columntypes) { $h.= " – "; $h.= $columns[$i - 1]->type; $h.= ""; }; $h.= " | "; } elseif(count($f) > 0) { $h.= array_keys(get_object_vars($f[0]))[$i - 1]; $h.= ""; } else $h.= ""; }; foreach($this->callbacks as $k => $v) { $h.= ""; $h.= $k; $h.= " | "; }; $h.= "
|---|---|
| columnCount . "\" style=\"border: 1px solid black; padding: 5px;\">No rows returned | |
" . ($val===NULL ? "NULL" : ($limit && (strlen($val) > $limit) ? "Value longer than " . $limit . " bytes" : ("" . (((!empty($columns[$i]) && $columns[$i]->type=="BLOB") || !preg_match("/^[\x{0020}-\x{007E}\x{00A0}-ſΑ-ωА-я‐- ⁰-₿℀-⋿①-⓿]*$/", $val)) ? ("HEX: " . bin2hex($val) . "") : str_replace(["\n", "\r"], "", nl2br(htmlspecialchars($val, ENT_QUOTES, "UTF-8")))) . ""))) . " | ";
$i++;
};
$h.= "|