" . 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");
$result = $this->result;
$this->query("SHOW DATABASES", $flags, "dbList");
$r = [];
while($f = $this->fetch()) {
$r[] = $f->Database;
};
$this->result = $result;
return $r;
}
public function tableList(int $flags = 0) {
if(empty($this->db) && !$this->SQLite)
throw new SSQLException("(tableList): No database selected");
$result = $this->result;
$this->query(($this->SQLite ? "SELECT `name` FROM `sqlite_master` WHERE type='table' ORDER BY `name`" : ("SHOW TABLES FROM `{$this->db}`")), "tableList", $flags);
$r = [];
while($f = $this->fetch(self::FETCH_ARRAY)) {
$r[] = $f[0];
};
$this->result = $result;
return $r;
}
public function result() {
return $this->result;
}
public function charset(string $charset, int $flags = 0) {
return $this->query("SET NAMES " . $charset, $flags, "charset");
}
public function fetch(int $flags = 512) {
$id = $this->result;
return $id->fetch($flags);
}
public function deleteDB(string $db, int $flags) {
if($this->SQLite)
throw new SSQLException("Not supported in SQLite");
if($this->query("DROP DATABASE `$db`", $flags, "deleteDB")) {
return true;
}
else {
throw new SSQLException("(deleteDB): Cannot delete database " . $db);
return false;
};
}
public function changeDB(string $newDB) {
if($this->result instanceof SSQLResult)
$this->result->__destruct();
$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") {
if(is_array($cols)) {
foreach($cols as $k => $v) {
if(is_int($k))
$cols[$k] = "`{$v}` AS `{$k}`";
else
$cols[$k] = "`{$v}`";
};
$colsValue = implode(", ", $cols);
}
else
$colsValue = strval($cols);
$limitString = ($limit===NULL || $limit==="") ? "" : " LIMIT " . intval($this->escape($limit));
if(!empty($order))
$order = " ORDER BY `" . $order . "`" . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
return $this->query("
SELECT {$colsValue} FROM `{$table}`{$limitString}{$order}
", $flags, $name);
}
public function selectWhere(string $table, $cond, string $order = "", $cols = "*", $limit = NULL, int $flags = 129, string $name = "selectWhere") {
$all = !boolval($flags & self::COND_OR);
if(is_array($cols)) {
foreach($cols as $k => $v) {
if(gettype($k)!="integer")
$cols[$k] = "`{$v}` AS `{$k}`";
else
$cols[$k] = "`{$v}`";
};
$colsValue = implode(", ", $cols);
}
else
$colsValue = strval($cols);
$condString = $this->getCondString($cond, $all);
$limitString = ($limit===NULL || $limit==="") ? "" : " LIMIT " . intval($this->escape($limit));
if(!empty($order))
$order = " ORDER BY `{$order}`" . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
return $this->query("
SELECT {$colsValue} FROM `{$table}` WHERE {$condString}{$limitString}{$order}
", $flags, $name);
}
public function selectJoin(string $table, string $join, $on, string $order = "", $cols = "*", $limit = NULL, int $flags = 133, string $name = "selectJoin") {
$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)) {
foreach($cols as $k => $v) {
if(gettype($k)!="integer")
$cols[$k] = "`{$v}` AS `{$k}`";
else
$cols[$k] = "`{$v}`";
};
$colsValue = implode(", ", $cols);
}
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 `" . $order . "` " . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
return $this->query("
SELECT {$colsValue}
FROM `{$table}`
{$jt} JOIN `{$join}` ON {$onString}{$limitString}{$order}
", $flags, $name);
}
public function selectJoinWhere(string $table, string $join, $on, $cond, string $order = "", $cols = "*", $limit = NULL, int $flags = 133, string $name = "selectJoinWhere") {
$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)) {
foreach($cols as $k => $v) {
if(gettype($k)!="integer")
$cols[$k] = "`{$v}` AS `{$k}`";
else
$cols[$k] = "`{$v}`";
};
$colsValue = implode(", ", $cols);
}
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 `{$order}` " . (boolval($flags & self::ORDER_DESC) ? " DESC" : " ASC");
return $this->query("
SELECT {$colsValue}
FROM `{$table}`
{$jt} JOIN `{$join}` ON {$onString}
WHERE {$condString}{$limitString}{$order}
", $flags, $name);
}
public function exists(string $table, $cond, int $flags = 129, string $name = "exists") {
$all = !boolval($flags & self::COND_OR);
$this->selectWhere($table, $cond, "", "*", NULL, $flags, $name);
$noFetch = !$this->fetch();
return !$noFetch;
}
public function truncate(string $table, int $flags = 0) {
if($this->SQLite)
$query = "DELETE FROM `{$table}`";
else
$query = "TRUNCATE `{$table}`";
return $this->query($query, $flags, "truncate");
}
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_values(array_flip($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))
$valueString.= "'" . $this->escape($value) . "'";
elseif($this->SQLite)
$valueString.= "x'" . bin2hex($value) . "'";
else
$valueString.= "0x" . bin2hex($value);
};
$r = $this->query("
INSERT INTO {$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 `{$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(preg_match("/^[\x{0020}-\x{007E}\x{00A0}-ſ]*$/", $value))
$string.= "'" . $this->escape($value) . "'";
elseif($this->SQLite)
$string.= "x'" . bin2hex($value) . "'";
else
$string.= "0x" . bin2hex($value);
};
return $this->query("
UPDATE `{$table}` SET {$string} WHERE {$condString}
", $flags, $name);
}
public function add(string $table, string $name, string $type, int $length, bool $null, string $where, string$key, string $data = "", int $flags = 0) {
if(!empty($data))
$data = " " . $data;
$type = strtoupper($type);
$where = strtoupper($where);
return $this->query("
ALTER TABLE `{$table}` ADD '{$name}' {$type}({$length}) " . ($null ? "NULL" : "NOT NULL") . "{$data} {$where} '{$key}'
", $flags, "drop");
}
public function drop(string $table, string $name) {
return $this->query("
ALTER TABLE `{$table}` DROP '{$name}'
", $flags, "drop");
}
public function change(string $table, string $name, string $newname, string $type, int $length, bool $null, string $data = "", int $flags = 0) {
if(!empty($data))
$data = " " . $data;
$type = strtoupper($type);
$where = strtoupper($where);
return $this->query("
ALTER TABLE `{$table}` CHANGE '{$name}' {$newname} {$type}({$length}) " . ($null ? "NULL" : "NOT NULL") . $data
, $flags, "change");
}
public function selectAll(string $table, int $flags = 129) {
$r = $this->result;
$this->select($table, "", "*", NULL, $flags);
$f = $this->fetch($flags | self::FETCH_ALL);
$this->result = $r;
return $f;
}
public function fetchWhere(string $table, $cond, int $flags = 129) {
$r = $this->result;
$this->selectWhere($table, $cond, "", "*", NULL, $flags);
$f = $this->fetch();
$this->result = $r;
return $f;
}
public function read(string $table, $cond = [], int $flags = 4225) {
$r = $this->result;
if(is_int($cond) && $flags==4225) {
$flags = $cond;
$cond = [];
};
if($cond===[])
$this->select($table);
else
$this->selectWhere($table, $cond, "", "*", NULL, $flags);
return $this->fetch($flags);
}
public function createTable(string $table, array $params, $primary = NULL, int $flags = 0) {
$parameters = $this->getParameters($params, $primary);
$valueString = implode(",\n", $parameters);
$query = "CREATE TABLE `{$table}` ({$valueString})";
return $this->query($query, $flags);
}
public function renameTable(string $table, string $newname, int $flags = 0) {
return $this->query("
ALTER TABLE `{$table}` RENAME TO {$newname}
", $flags, "renameTable");
}
public function deleteTable(string $table, int $flags = 0) {
return $this->query("
DROP TABLE `{$table}`
", $flags, "deleteTable");
}
private function getParameters(array $params, $primary = NULL) {
foreach($params as $k => $v) {
$name = $k;
$t = $v['type'];
$l = empty($v['length']) ? NULL : $v['length'];
$n = $v['NULL'] ? "NULL" : "NOT NULL";
$o = empty($v['other']) ? NULL : $v['other'];
if(empty($l))
$r[] = "`{$name}` {$t} {$n} {$o}";
else
$r[] = "`{$name}` {$t}({$l}) {$n} {$o}";
};
if($primary)
$r[] = "PRIMARY KEY ({$primary})";
return $r;
}
public function q(string $q, ...$a) {
if($a==[])
return $this->query($q);
else
return $this->queryf($q, $a);
}
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 = 4096) {
if(is_int($options) && $flags==4096) {
$flags = $options;
$options = [];
};
if(empty($options['fetch']))
$fetch = boolval($flags & self::FETCH_SMART) ? self::FETCH_SMART : (boolval($flags & self::FETCH_ALL) ? self::FETCH_ALL : (boolval($flags & self::FETCH_ARRAY) ? self::FETCH_ARRAY : (boolval($flags & self::FETCH_OBJECT) ? self::FETCH_OBJECT : self::FETCH_SMART)));
else
$fetch = $options['fetch'];
if(($fetch==self::FETCH_ALL || $fetch==self::FETCH_SMART) && boolval($flags & self::FETCH_ARRAY))
$fetch = $fetch | self::FETCH_ARRAY;
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'];
if($flags & self::FETCH_OBJECT)
$flags-= self::FETCH_OBJECT;
if($flags & self::FETCH_ARRAY)
$flags-= self::FETCH_ARRAY;
if($flags & self::FETCH_ALL)
$flags-= self::FETCH_ALL;
if($flags & self::FETCH_SMART)
$flags-= self::FETCH_SMART;
if($flags & self::COND_AND)
$flags-= self::COND_AND;
if($flags & self::COND_OR)
$flags-= self::COND_OR;
if($flags & self::ORDER_ASC)
$flags-= self::ORDER_ASC;
if($flags & self::ORDER_DESC)
$flags-= self::ORDER_DESC;
if($flags & self::JOIN_INNER)
$flags-= self::JOIN_INNER;
if($flags & self::JOIN_LEFT)
$flags-= self::JOIN_LEFT;
if($flags & self::JOIN_RIGHT)
$flags-= self::JOIN_RIGHT;
if($flags & self::JOIN_FULL)
$flags-= self::JOIN_FULL;
$cond = empty($options['cond']) ? false : $options['cond'];
$join = empty($options['join']) ? false : $options['join'];
$on = empty($options['on']) ? false : $options['on'];
$cols = empty($options['cols']) ? "*" : $options['cols'];
$order = empty($options['order']) ? "" : $options['order'];
$limit = empty($options['limit']) ? NULL : $options['limit'];
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->fetch($flags | $fetch);
}
public function put(string $table, $data, $cond = NULL, int $flags = 0) {
if(is_int($cond) && $flags==0) {
$flags = $cond;
$cond = NULL;
};
if(empty($cond))
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)) {
foreach($v as $k2 => $v2) {
$col = false;
if(!is_numeric($k) && str_split($v2)[0]=="`" && end(str_split($v2))=="`") {
$va = str_split($v);
unset($va[0]);
unset($va[count($va-1)]);
$col = true;
};
if(!is_numeric($v2))
$v3 = $this->escape($v2);
if(is_numeric($k))
$r.= $v;
else {
$r.= "`{$this->escape($k)}`";
$r.= " = ";
if(is_numeric($v3))
$v3 = intval($v3);
if($v3===NULL)
$r.= "IS NULL";
elseif(is_numeric($v3))
$r.= $v;
else
$r.= ($on || $col) ? "`{$v3}`" : "'{$v3}'";
};
$r.= $and ? " AND " : " OR ";
};
return rtrim($r, $and ? " AND " : " OR ");
}
else {
$col = false;
if(!is_numeric($k) && str_split($v)[0]=="`" && end(str_split($v))=="`") {
$va = str_split($v);
unset($va[0]);
unset($va[count($va)]);
$col = true;
};
if(!is_numeric($v))
$v = $this->escape($v);
if(is_numeric($k))
$r.= $v;
else {
$r.= "`" . $this->escape($k) . "`";
$r.= " = ";
if(is_numeric($v))
$v = intval($v);
if(is_numeric($v))
$r.= $v;
else
$r.= ($on || $col) ? "`{$v}`" : "'{$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() {
if($this->result instanceof PDOStatement)
$this->result->__destruct();
$this->connect = NULL;
}
public function __wakeup() {
$this->__construct($this->host, $this->user, $this->password, $this->db);
}
public function __destruct() {
if($this->result instanceof PDOStatement)
$this->result->__destruct();
$this->connect = NULL;
}
};
class SSQLResult {
private $pdos;
public $columnCount = 0;
public function __construct(PDOStatement $pdos) {
$this->pdos = $pdos;
$this->columnCount = $this->pdos->columnCount();
}
public function getColumns() {
$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]->len = rtrim(explode("(", $meta['sqlite:decl_type'])[1], ")");
}
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";
};
$columns[$i - 1]->length = $meta['len'];
$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']]);
};
};
return $columns;
}
public function fetch(int $flags = 512) {
$id = $this->pdos;
if(boolval($flags & SQ::FETCH_ALL) || boolval($flags & SQ::FETCH_SMART)) {
$return = [];
while($row = (boolval($flags & SQ::FETCH_ARRAY) ? $id->fetch() : $id->fetchObject())) {
$return[] = $row;
};
if(boolval($flags & SQ::FETCH_SMART) && count($return)<2)
return count($return)<1 ? false : $return[0];
}
elseif(boolval($flags & SQ::FETCH_ARRAY))
$return = $id->fetch();
else
$return = $id->fetchObject();
return $return;
}
public function dump($limit = 256, bool $columntypes = true, bool $print = true) {
$limit = $limit ? intval($limit) : false;
$f = $this->fetch(SQ_FETCH_ALL);
$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.= ""; }; $h.= "
|---|
| columnCount . "\" style=\"border: 1px solid black; padding: 5px;\">No rows returned |
" . ($val===NULL ? "NULL" : ($limit && (strlen($val)>$limit) ? "Value longer than " . $limit . " bytes" : ("" . ((($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.= "