477 lines
11 KiB
PHP
477 lines
11 KiB
PHP
<?php
|
|
|
|
/**
|
|
* Vvveb
|
|
*
|
|
* Copyright (C) 2022 Ziadin Givan
|
|
*
|
|
* This program is free software: you can redistribute it and/or modify
|
|
* it under the terms of the GNU Affero General Public License as
|
|
* published by the Free Software Foundation, either version 3 of the
|
|
* License, or (at your option) any later version.
|
|
*
|
|
* This program is distributed in the hope that it will be useful,
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
* GNU Affero General Public License for more details.
|
|
*
|
|
* You should have received a copy of the GNU Affero General Public License
|
|
* along with this program. If not, see <https://www.gnu.org/licenses/>.
|
|
*
|
|
*/
|
|
|
|
namespace Vvveb\System\Db;
|
|
|
|
define('SQL_VAR_REGEX',
|
|
'/:([a-zA-Z0-9\[][\.\'a-zA-Z0-9\[\]_-]+)/ms');
|
|
|
|
class DBDriver {
|
|
protected $quote;
|
|
|
|
public function _filter($data, $columns, $addMissingDefaults = false) {
|
|
//remove fields that are not table columns, $colums is returned by sqlp->getColumnsMeta()
|
|
foreach ($data as $key => $name) {
|
|
if (! isset($columns[$key])) {
|
|
unset($data[$key]);
|
|
} else {
|
|
if ($data[$key] === NULL) {
|
|
//set default value is empty value provided
|
|
if (isset($columns[$key]['d'])) {
|
|
$data[$key] = $columns[$key]['d'];
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($addMissingDefaults) {
|
|
foreach ($columns as $name => $options) {
|
|
$options['t'] = strtolower($options['t']);
|
|
|
|
if (isset($options['e']) && $options['e'] == 'auto_increment') {
|
|
continue;
|
|
}
|
|
//todo: validate based on data type (t)
|
|
//if there is no data for column and column is not nullable set default
|
|
if (! isset($data[$name]) && $options['n'] == false) {
|
|
if ($options['d']) {
|
|
$data[$name] = $options['d'];
|
|
} else {
|
|
if ($options['d'] == NULL) {
|
|
$data[$name] = '';
|
|
}
|
|
|
|
if ($options['t'] == 'int' || $options['t'] == 'decimal' || $options['t'] == 'tinyint') {
|
|
$data[$name] = 0;
|
|
}
|
|
|
|
if ($options['t'] == 'datetime') {
|
|
$data[$name] = date('Y-m-d H:i:s');
|
|
}
|
|
|
|
if ($options['t'] == 'date') {
|
|
$data[$name] = date('Y-m-d');
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
public function varType($var) {
|
|
//$type = gettype($var);
|
|
if (is_array($var)) {
|
|
$type = 'a';
|
|
} else {
|
|
//if (is_float($var)) {
|
|
if ($var == (string)(float)$var) {
|
|
$type = 'd';
|
|
} else {
|
|
if (is_numeric($var)) {
|
|
$type = 'i';
|
|
} else {
|
|
$type = 's';
|
|
}
|
|
}
|
|
}
|
|
|
|
return $type;
|
|
}
|
|
|
|
public function filter($data, $columns, $addMissingDefaults = false) {
|
|
//check if collection of rows or individual row
|
|
reset($data);
|
|
|
|
if (is_numeric(key($data))) {
|
|
//rows
|
|
foreach ($data as $key => $row) {
|
|
$return[$key] = $this->_filter($row, $columns, $addMissingDefaults);
|
|
}
|
|
|
|
return $return;
|
|
} else {
|
|
//row
|
|
return $this->_filter($data, $columns, $addMissingDefaults);
|
|
}
|
|
}
|
|
|
|
public function validate($data, $params, $table = '', $ignoreMissing = false) {
|
|
$errors = [];
|
|
|
|
foreach ($params as $name => $type) {
|
|
if (! isset($data[$name])) {
|
|
if (! $ignoreMissing) {
|
|
$errors[] = sprintf('%s missing!', $name);
|
|
}
|
|
|
|
continue;
|
|
}
|
|
|
|
if (is_array($type)) {
|
|
if (isset($data[$name]) && is_array($data[$name])) {
|
|
//check if collection
|
|
if (is_numeric(key($data[$name]))) {
|
|
$arr = $data[$name][0] ?? [];
|
|
} else {
|
|
$arr = $data[$name];
|
|
}
|
|
|
|
foreach ($type as $col => $attr) {
|
|
//skip auto increment
|
|
if ((isset($attr['e']) && $attr['e'] == 'auto_increment')
|
|
|| ($table && $col == $table . '_id')) {
|
|
continue;
|
|
}
|
|
|
|
if (! isset($arr[$col])) {
|
|
//skip columns that have default values
|
|
if (($attr['d'] === 'NULL' || $attr['d'] === NULL) && ! $ignoreMissing) {
|
|
$errors[] = sprintf('%s missing!', $name . '[' . $col . ']');
|
|
}
|
|
} else {
|
|
if (($attr['t'] == 'int' || $attr['t'] == 'decimal' || $attr['t'] == 'tinyint') && ! is_numeric($arr[$col])) {
|
|
$errors[] = sprintf('%s is not an integer!', $name . '[' . $col . ']');
|
|
}
|
|
|
|
if (isset($attr['l']) && $attr['l'] && (strlen($arr[$col]) > $attr['l'])) {
|
|
$errors[] = sprintf('%s is longer than %s!', $name . '[' . $col . ']', $attr['l']);
|
|
}
|
|
}
|
|
}
|
|
} else {
|
|
$errors[] = sprintf('%s is not an array!', $name);
|
|
}
|
|
} else {
|
|
switch ($type) {
|
|
case 'a':
|
|
if (! isset($data[$name]) || ! is_array($data[$name])) {
|
|
$errors[] = sprintf('%s is not an array!', $name);
|
|
} else {
|
|
if (substr_compare($name, '_id', -3 ,3) === 0 &&
|
|
! empty($data[$name]) &&
|
|
! is_numeric($data[$name][0])) {
|
|
$errors[] = sprintf('%s is not an integer!', $name . '[0]');
|
|
}
|
|
}
|
|
|
|
break;
|
|
|
|
case 'i':
|
|
if (! isset($data[$name]) || ! is_numeric($data[$name])) {
|
|
$errors[] = sprintf('%s is not an integer!', $name);
|
|
}
|
|
|
|
break;
|
|
|
|
case 's':
|
|
if (! isset($data[$name]) || ! is_string($data[$name])) {
|
|
$errors[] = sprintf('%s is not a string!', $name);
|
|
}
|
|
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
//var_dump($data);
|
|
//var_dump($params);
|
|
//var_dump($errors);
|
|
return $errors;
|
|
}
|
|
|
|
/*
|
|
* Expands arrays a = ['param' => 'value', 'second' => value]; to a[param], a[second]
|
|
*/
|
|
public function expandArray($array, $arrayName) {
|
|
$first = true;
|
|
$sql = '';
|
|
$parameters = [];
|
|
|
|
if (is_array($array)) {
|
|
foreach ($array as $key => $value) {
|
|
if (! $first) {
|
|
$sql .= ',';
|
|
}
|
|
$arrayKey = "['" . $arrayName . "']['" . $key . "']";
|
|
$sql .= ':' . $arrayKey;
|
|
$type = $this->varType($value);
|
|
$parameters[$arrayKey] = $type;
|
|
$first = false;
|
|
}
|
|
}
|
|
|
|
return [$sql, $parameters];
|
|
}
|
|
|
|
/**
|
|
* Generate a SQL list used for inserts
|
|
* input ['var1' => 1, 'var2'=> 2, 'var3'=> 3] output var1 = 1, var2 = 2, var3 = 3.
|
|
* @param mixed $array
|
|
* @param mixed $arrayName
|
|
*
|
|
* @return mixed
|
|
*/
|
|
public function expandList($array, $arrayName) {
|
|
$first = true;
|
|
$sql = '';
|
|
$parameters = [];
|
|
$quote = $this->quote;
|
|
|
|
foreach ($array as $key => $value) {
|
|
if (! $first) {
|
|
$sql .= ',';
|
|
}
|
|
$arrayKey = "['$arrayName']['$key']";
|
|
$sql .= "$quote$key$quote = :$arrayKey";
|
|
$type = $this->varType($value);
|
|
$parameters[$arrayKey] = $type;
|
|
$first = false;
|
|
}
|
|
|
|
return [$sql, $parameters];
|
|
}
|
|
|
|
public function sqlCount($query, $column, $table) {
|
|
//remove limit
|
|
//pgsql
|
|
$query = preg_replace('/LIMIT\s+(\d+|:\w+)\s+OFFSET\s*(\d+|:\w+)\s*;?$/', '', $query);
|
|
//mysql
|
|
$query = preg_replace('/LIMIT\s+(\d+|:\w+),\s*(\d+|:\w+)\s*;?$/', '', $query);
|
|
//remove order by
|
|
$query = preg_replace('/ORDER BY\s+([a-z\._\-]+|,|\s+|DESC|ASC)+/', '', $query);
|
|
|
|
$query = preg_replace("/^\s*SELECT .*?\s*FROM\s*$table\s/ms", "SELECT $column FROM $table ", $query);
|
|
|
|
return $query;
|
|
}
|
|
|
|
/*
|
|
* Convert array dot notation to php notation
|
|
* Ex: my.array.key to ['my']['array']['key']
|
|
*/
|
|
function sqlPhpArrayKey($key) {
|
|
return '[\'' . str_replace('.', '\'][\'', $key) . '\']';
|
|
}
|
|
|
|
/*
|
|
* Replace :named_params with ?
|
|
*/
|
|
public function paramsToQmark(&$sql, &$params = [], &$paramTypes = [], $placeholder = '?') {
|
|
$parameters = [];
|
|
$index = 1;
|
|
$sql = preg_replace_callback(
|
|
SQL_VAR_REGEX,
|
|
function ($matches) use (&$params, &$parameters, &$types, $paramTypes, &$index, $placeholder) {
|
|
$varName = $matches[1];
|
|
|
|
if (strpos($varName, '.') !== false) {
|
|
$varName = $this->sqlPhpArrayKey($varName);
|
|
}
|
|
//if parameters is array element
|
|
if ($varName[0] == '[') {
|
|
if (preg_match_all('/[\w_-]+/', $varName, $arrayKeys)) {
|
|
$type = $paramTypes[$varName] ?? 's';
|
|
|
|
$types .= $type;
|
|
|
|
$key1 = $arrayKeys[0][0];
|
|
$key2 = $arrayKeys[0][1];
|
|
|
|
$parameter = &$params[$key1][$key2];
|
|
//if (strpos($parameter, ')') && )
|
|
$parameters[] = $parameter;
|
|
}
|
|
} else {
|
|
if (isset($params[$varName])) {
|
|
$parameter = &$params[$varName];
|
|
|
|
if (isset($paramTypes[$varName])) {
|
|
$type = $paramTypes[$varName];
|
|
} else {
|
|
$type = $this->varType($parameter);
|
|
}
|
|
|
|
if ($type == 'a') {
|
|
$return = false;
|
|
|
|
foreach ($parameter as $key => $value) {
|
|
$parameters[] = $value;
|
|
|
|
if ($placeholder == '$') {
|
|
$holder = '$' . $index;
|
|
} else {
|
|
$holder = '?';
|
|
}
|
|
|
|
if ($return) {
|
|
$return .= ",$holder";
|
|
} else {
|
|
$return = $holder;
|
|
}
|
|
|
|
$type = $this->varType($value);
|
|
$types .= $type;
|
|
|
|
$index++;
|
|
}
|
|
|
|
return $return;
|
|
} else {
|
|
if (! $type) {
|
|
$type = $this->varType($parameter);
|
|
}
|
|
$types .= $type;
|
|
|
|
$parameters[] = $parameter;
|
|
}
|
|
} else {
|
|
return 'null';
|
|
}
|
|
}
|
|
|
|
if ($placeholder == '$') {
|
|
$holder = '$' . $index;
|
|
} else {
|
|
$holder = '?';
|
|
}
|
|
|
|
$return = $holder;
|
|
$index++;
|
|
|
|
return $return;
|
|
},
|
|
$sql);
|
|
|
|
return [$parameters, $types];
|
|
}
|
|
|
|
public function debugSql($sql, $params = [], $paramTypes = [], $placeholder = '?') {
|
|
list($parameters, $types) = $this->paramsToQmark($sql, $params, $paramTypes, $placeholder);
|
|
|
|
if ($placeholder == '$') {
|
|
$regex = '\$\d+';
|
|
} else {
|
|
$regex = "\\$placeholder";
|
|
}
|
|
$index = 0;
|
|
$sql = preg_replace_callback(
|
|
"/$regex/",
|
|
function ($matches) use (&$parameters, &$sql, &$index) {
|
|
$value = $parameters[$index++];
|
|
|
|
if (is_array($value)) {
|
|
$value = var_export($value, 1);
|
|
} else {
|
|
if (! is_numeric($value)) {
|
|
$value = "'$value'";
|
|
}
|
|
}
|
|
|
|
return $value;
|
|
}, $sql);
|
|
|
|
return $sql;
|
|
$sql = preg_replace_callback(
|
|
SQL_VAR_REGEX,
|
|
function ($matches) use (&$params, &$types, $paramTypes) {
|
|
//if parameters is array element
|
|
$varName = $matches[1];
|
|
$value = $params[$varName] ?? '';
|
|
|
|
if (strpos($varName, '.') !== false) {
|
|
$varName = $this->sqlPhpArrayKey($varName);
|
|
}
|
|
|
|
if ($varName[0] == '[') {
|
|
if (preg_match_all('/[\w_-]+/', $varName, $arrayKeys)) {
|
|
$type = $paramTypes[$varName] ?? 's';
|
|
|
|
$types .= $type;
|
|
|
|
$key1 = $arrayKeys[0][0];
|
|
$key2 = $arrayKeys[0][1];
|
|
|
|
$parameter = &$params[$key1][$key2];
|
|
//if (strpos($parameter, ')') && )
|
|
if ($type == 's') {
|
|
$parameter = '"' . (string)$parameter . '"';
|
|
}
|
|
|
|
return $parameter;
|
|
}
|
|
} else {
|
|
if (isset($params[$varName])) {
|
|
$parameter = &$params[$varName];
|
|
|
|
if (isset($paramTypes[$varName])) {
|
|
$type = $paramTypes[$varName];
|
|
} else {
|
|
$type = $this->varType($parameter);
|
|
}
|
|
|
|
if ($type == 'a') {
|
|
$return = false;
|
|
|
|
if ($parameter) {
|
|
foreach ($parameter as $key => $value) {
|
|
if ($return) {
|
|
$return .= ',';
|
|
}
|
|
|
|
if ($value) {
|
|
$return .= '"' . $value . '"';
|
|
}
|
|
}
|
|
}
|
|
|
|
return $return;
|
|
} else {
|
|
if (! $type) {
|
|
$type = 's';
|
|
}
|
|
$types .= $type;
|
|
}
|
|
|
|
if (! $type) {
|
|
$type = 's';
|
|
}
|
|
$types .= $type;
|
|
|
|
if ($type == 's') {
|
|
$parameter = '"' . $parameter . '"';
|
|
}
|
|
|
|
return $parameter;
|
|
} else {
|
|
return 'null';
|
|
}
|
|
}
|
|
|
|
return '?';
|
|
},
|
|
$sql);
|
|
|
|
return $sql;
|
|
}
|
|
}
|