775 lines
26 KiB
PHP
775 lines
26 KiB
PHP
<?php
|
|
/**
|
|
* Class: QueryBuilder
|
|
* A generic SQL query builder.
|
|
*/
|
|
class QueryBuilder {
|
|
/**
|
|
* Function: build_select
|
|
* Creates a full SELECT query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $tables - Tables to select from.
|
|
* $fields - Columns to select.
|
|
* $order - What to order by.
|
|
* $limit - Limit of the result.
|
|
* $offset - Starting point for the result.
|
|
* $group - What to group by.
|
|
* $left_join - Any @LEFT JOIN@s to add.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*
|
|
* Returns:
|
|
* A @SELECT@ query string.
|
|
*/
|
|
public static function build_select(
|
|
$sql,
|
|
$tables,
|
|
$fields,
|
|
$conds,
|
|
$order = null,
|
|
$limit = null,
|
|
$offset = null,
|
|
$group = null,
|
|
$left_join = array(),
|
|
&$params = array()
|
|
): string {
|
|
$query = "SELECT ".self::build_select_header($sql, $fields, $tables)."\n".
|
|
"FROM ".self::build_from($sql, $tables)."\n";
|
|
|
|
foreach ($left_join as $join)
|
|
$query.= "LEFT JOIN \"__".$join["table"].
|
|
"\" ON ".
|
|
self::build_where($sql, $join["where"], $join["table"], $params).
|
|
"\n";
|
|
|
|
if ($conds)
|
|
$query.= "WHERE ".self::build_where($sql, $conds, $tables, $params)."\n";
|
|
|
|
if ($group)
|
|
$query.= "GROUP BY ".self::build_group($sql, $group, $tables)."\n";
|
|
|
|
if ($order)
|
|
$query.= "ORDER BY ".self::build_order($sql, $order, $tables)."\n";
|
|
|
|
if (empty($left_join))
|
|
$query.= self::build_limits($sql, $offset, $limit);
|
|
|
|
return $query;
|
|
}
|
|
|
|
/**
|
|
* Function: build_insert
|
|
* Creates a full insert query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $table - Table to insert into.
|
|
* $data - Data to insert.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*
|
|
* Returns:
|
|
* An @INSERT@ query string.
|
|
*/
|
|
public static function build_insert(
|
|
$sql,
|
|
$table,
|
|
$data,
|
|
&$params = array()
|
|
): string {
|
|
if (empty($params))
|
|
foreach ($data as $key => $val) {
|
|
if (is_object($val) and !$val instanceof Stringable)
|
|
$val = null;
|
|
|
|
if (is_bool($val))
|
|
$val = (int) $val;
|
|
|
|
if (is_float($val))
|
|
$val = (string) $val;
|
|
|
|
if (
|
|
$key == "updated_at" and
|
|
is_datetime_zero($val)
|
|
)
|
|
$val = SQL_DATETIME_ZERO;
|
|
|
|
$param = ":".str_replace(array("(", ")", "."), "_", $key);
|
|
$params[$param] = $val;
|
|
}
|
|
|
|
return "INSERT INTO \"__$table\"\n".
|
|
self::build_insert_header($sql, $data)."\n".
|
|
"VALUES\n".
|
|
"(".implode(", ", array_keys($params)).")\n";
|
|
}
|
|
|
|
/**
|
|
* Function: build_update
|
|
* Creates a full update query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $table - Table to update.
|
|
* $conds - Conditions to update rows by.
|
|
* $data - Data to update.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*
|
|
* Returns:
|
|
* An @UPDATE@ query string.
|
|
*/
|
|
public static function build_update(
|
|
$sql,
|
|
$table,
|
|
$conds,
|
|
$data,
|
|
&$params = array()
|
|
): string {
|
|
$query = "UPDATE \"__$table\"\n".
|
|
"SET ".self::build_update_values($sql, $data, $params)."\n";
|
|
|
|
if ($conds)
|
|
$query.= "WHERE ".self::build_where($sql, $conds, $table, $params);
|
|
|
|
return $query;
|
|
}
|
|
|
|
/**
|
|
* Function: build_delete
|
|
* Creates a full delete query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $table - Table to delete from.
|
|
* $conds - Conditions to delete by.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*
|
|
* Returns:
|
|
* A @DELETE@ query string.
|
|
*/
|
|
public static function build_delete(
|
|
$sql,
|
|
$table,
|
|
$conds,
|
|
&$params = array()
|
|
): string {
|
|
$query = "DELETE FROM \"__$table\"\n";
|
|
|
|
if ($conds)
|
|
$query.= "WHERE ".self::build_where($sql, $conds, $table, $params);
|
|
|
|
return $query;
|
|
}
|
|
|
|
/**
|
|
* Function: build_drop
|
|
* Creates a full drop table query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $table - Table to drop.
|
|
*
|
|
* Returns:
|
|
* A @DROP TABLE@ query string.
|
|
*/
|
|
public static function build_drop(
|
|
$sql,
|
|
$table
|
|
): string {
|
|
return "DROP TABLE IF EXISTS \"__$table\"";
|
|
}
|
|
|
|
/**
|
|
* Function: build_create
|
|
* Creates a full create table query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $table - Table to create.
|
|
* $cols - An array of column declarations.
|
|
*
|
|
* Returns:
|
|
* A @CREATE TABLE@ query string.
|
|
*/
|
|
public static function build_create(
|
|
$sql,
|
|
$table,
|
|
$cols
|
|
): string {
|
|
$query = "CREATE TABLE IF NOT EXISTS \"__$table\" (\n ".
|
|
implode(",\n ", $cols)."\n)";
|
|
|
|
switch ($sql->adapter) {
|
|
case "sqlite":
|
|
$query = str_ireplace(
|
|
"AUTO_INCREMENT",
|
|
"AUTOINCREMENT",
|
|
$query
|
|
);
|
|
break;
|
|
|
|
case "mysql":
|
|
$query = str_ireplace(
|
|
"AUTOINCREMENT",
|
|
"AUTO_INCREMENT",
|
|
$query
|
|
);
|
|
$query.= " DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci";
|
|
break;
|
|
|
|
case "pgsql":
|
|
$query = str_ireplace(
|
|
array(
|
|
"LONGTEXT",
|
|
"DATETIME"
|
|
),
|
|
array(
|
|
"TEXT",
|
|
"TIMESTAMP"
|
|
),
|
|
$query
|
|
);
|
|
$query = preg_replace(
|
|
"/INTEGER( (PRIMARY )?KEY)? AUTO_?INCREMENT/i",
|
|
"SERIAL$1",
|
|
$query
|
|
);
|
|
break;
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
/**
|
|
* Function: build_update_values
|
|
* Creates an update data part.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $data - Data to update.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*/
|
|
public static function build_update_values(
|
|
$sql,
|
|
$data,
|
|
&$params = array()
|
|
): string {
|
|
$set = self::build_conditions($sql, $data, $params, null, true);
|
|
return implode(",\n ", $set);
|
|
}
|
|
|
|
/**
|
|
* Function: build_insert_header
|
|
* Creates an insert header.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $data - Data to insert.
|
|
*/
|
|
public static function build_insert_header(
|
|
$sql,
|
|
$data
|
|
): string {
|
|
$set = array();
|
|
|
|
foreach (array_keys($data) as $field)
|
|
array_push($set, self::safecol($sql, $field));
|
|
|
|
return "(".implode(", ", $set).")";
|
|
}
|
|
|
|
/**
|
|
* Function: build_limits
|
|
* Creates the LIMIT part of a query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $offset - Offset of the result.
|
|
* $limit - Limit of the result.
|
|
*/
|
|
public static function build_limits(
|
|
$sql,
|
|
$offset,
|
|
$limit
|
|
): string {
|
|
if ($limit === null)
|
|
return "";
|
|
|
|
if ($offset !== null)
|
|
return "LIMIT ".$offset.", ".$limit;
|
|
|
|
return "LIMIT ".$limit;
|
|
}
|
|
|
|
/**
|
|
* Function: build_from
|
|
* Creates a FROM header for select queries.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $tables - Tables to select from.
|
|
*/
|
|
public static function build_from(
|
|
$sql,
|
|
$tables
|
|
): string {
|
|
if (!is_array($tables))
|
|
$tables = array($tables);
|
|
|
|
foreach ($tables as &$table) {
|
|
if (substr($table, 0, 2) != "__")
|
|
$table = "__".$table;
|
|
|
|
$table = "\"".$table."\"";
|
|
}
|
|
|
|
return implode(",\n ", $tables);
|
|
}
|
|
|
|
/**
|
|
* Function: build_count
|
|
* Creates a SELECT COUNT(1) query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $tables - Tables to tablefy with.
|
|
* $conds - Conditions to select by.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*/
|
|
public static function build_count(
|
|
$sql,
|
|
$tables,
|
|
$conds,
|
|
&$params = array()
|
|
): string {
|
|
$query = "SELECT COUNT(1) AS count\n".
|
|
"FROM ".self::build_from($sql, $tables)."\n";
|
|
|
|
if ($conds)
|
|
$query.= "WHERE ".self::build_where($sql, $conds, $tables, $params);
|
|
|
|
return $query;
|
|
}
|
|
|
|
/**
|
|
* Function: build_select_header
|
|
* Creates a SELECT fields header.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $fields - Columns to select.
|
|
* $tables - Tables to tablefy with.
|
|
*/
|
|
public static function build_select_header(
|
|
$sql,
|
|
$fields,
|
|
$tables = null
|
|
): string {
|
|
if (!is_array($fields))
|
|
$fields = array($fields);
|
|
|
|
$tables = (array) $tables;
|
|
|
|
foreach ($fields as &$field) {
|
|
self::tablefy($sql, $field, $tables);
|
|
$field = self::safecol($sql, $field);
|
|
}
|
|
|
|
return implode(",\n ", $fields);
|
|
}
|
|
|
|
/**
|
|
* Function: build_where
|
|
* Creates a WHERE query.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $conds - Conditions to select by.
|
|
* $tables - Tables to tablefy with.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*/
|
|
public static function build_where(
|
|
$sql,
|
|
$conds,
|
|
$tables = null,
|
|
&$params = array()
|
|
): string {
|
|
$conds = (array) $conds;
|
|
$tables = (array) $tables;
|
|
|
|
$conditions = self::build_conditions($sql, $conds, $params, $tables);
|
|
|
|
return empty($conditions) ?
|
|
"" :
|
|
"(".implode(")\n AND (", array_filter($conditions)).")" ;
|
|
}
|
|
|
|
/**
|
|
* Function: build_group
|
|
* Creates a GROUP BY argument.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $order - Columns to group by.
|
|
* $tables - Tables to tablefy with.
|
|
*/
|
|
public static function build_group(
|
|
$sql,
|
|
$by,
|
|
$tables = null
|
|
): string {
|
|
$by = (array) $by;
|
|
$tables = (array) $tables;
|
|
|
|
foreach ($by as &$column) {
|
|
self::tablefy($sql, $column, $tables);
|
|
$column = self::safecol($sql, $column);
|
|
}
|
|
|
|
return implode(
|
|
",\n ",
|
|
array_unique(array_filter($by))
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Function: build_order
|
|
* Creates an ORDER BY argument.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $order - Columns to order by.
|
|
* $tables - Tables to tablefy with.
|
|
*/
|
|
public static function build_order(
|
|
$sql,
|
|
$order,
|
|
$tables = null
|
|
): string {
|
|
$tables = (array) $tables;
|
|
|
|
if (!is_array($order)) {
|
|
$parts = array_map("trim", explode(",", $order));
|
|
$order = array_diff(array_unique($parts), array(""));
|
|
}
|
|
|
|
foreach ($order as &$by) {
|
|
self::tablefy($sql, $by, $tables);
|
|
$by = self::safecol($sql, $by);
|
|
}
|
|
|
|
return implode(",\n ", $order);
|
|
}
|
|
|
|
/**
|
|
* Function: build_list
|
|
* Creates a list of values.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $vals - An array of values.
|
|
* &$params - An associative array of parameters used in the query.
|
|
*/
|
|
public static function build_list(
|
|
$sql,
|
|
$vals,
|
|
$params = array()
|
|
): string {
|
|
$return = array();
|
|
|
|
foreach ($vals as $val) {
|
|
if (is_object($val) and !$val instanceof Stringable)
|
|
$val = null;
|
|
|
|
switch (gettype($val)) {
|
|
case "NULL":
|
|
$return[] = "NULL";
|
|
break;
|
|
case "boolean":
|
|
$return[] = (string) (int) $val;
|
|
break;
|
|
case "double":
|
|
$return[] = $sql->escape($val);
|
|
break;
|
|
case "integer":
|
|
$return[] = (string) $val;
|
|
break;
|
|
case "object":
|
|
$return[] = $sql->escape($val);
|
|
break;
|
|
case "string":
|
|
$return[] = isset($params[$val]) ?
|
|
$val :
|
|
$sql->escape($val) ;
|
|
break;
|
|
}
|
|
}
|
|
|
|
return "(".implode(", ", $return).")";
|
|
}
|
|
|
|
/**
|
|
* Function: safecol
|
|
* Encloses a column name in quotes if it is a subset of SQL keywords.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $name - Name of the column.
|
|
*/
|
|
public static function safecol(
|
|
$sql,
|
|
$name
|
|
): string|array|null {
|
|
$keywords = array(
|
|
"between", "case", "check", "collate", "constraint", "create",
|
|
"default", "delete", "distinct", "drop", "except", "from",
|
|
"groups?", "having", "insert", "intersect", "into", "join",
|
|
"left", "like", "limit", "null", "offset", "order", "primary",
|
|
"references", "select", "set", "table", "transaction", "union",
|
|
"unique", "update", "using", "values", "when", "where", "with"
|
|
);
|
|
|
|
$pattern = implode("|", $keywords);
|
|
return preg_replace(
|
|
"/(([^a-zA-Z0-9_]|^)($pattern)([^a-zA-Z0-9_]|$))/i",
|
|
'\\2"\\3"\\4',
|
|
$name
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Function: build_conditions
|
|
* Builds an associative array of SQL values into parameterized query strings.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* $conds - Conditions.
|
|
* &$params - Parameters array to fill.
|
|
* $tables - If specified, conditions will be tablefied with these tables.
|
|
* $insert - Is this an insert/update query?
|
|
*/
|
|
public static function build_conditions(
|
|
$sql,
|
|
$conds,
|
|
&$params,
|
|
$tables = null,
|
|
$insert = false
|
|
): array {
|
|
$conditions = array();
|
|
|
|
# PostgreSQL: cast to text to enable LIKE operator.
|
|
$text = ($sql->adapter == "pgsql") ? "::text" : "" ;
|
|
|
|
# ESCAPE clause for LIKE.
|
|
$escape = " ESCAPE '|'";
|
|
|
|
foreach ($conds as $key => $val) {
|
|
if (is_int($key)) {
|
|
# Full expression.
|
|
$cond = $val;
|
|
} else {
|
|
# Key => Val expression.
|
|
if (is_string($val) and str_starts_with($val, ":")) {
|
|
$cond = self::safecol($sql, $key)." = ".$val;
|
|
} else {
|
|
if (is_object($val) and !$val instanceof Stringable)
|
|
$val = null;
|
|
|
|
if (is_bool($val))
|
|
$val = (int) $val;
|
|
|
|
if (is_float($val))
|
|
$val = (string) $val;
|
|
|
|
$uck = strtoupper($key);
|
|
|
|
if (substr($uck, -4) == " NOT") {
|
|
# Negation.
|
|
$key = self::safecol($sql, substr($key, 0, -4));
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$key
|
|
);
|
|
|
|
if (is_array($val)) {
|
|
$cond = $key." NOT IN ".
|
|
self::build_list($sql, $val, $params);
|
|
} elseif ($val === null) {
|
|
$cond = $key." IS NOT NULL";
|
|
} else {
|
|
$cond = $key." != :".$param;
|
|
$params[":".$param] = $val;
|
|
}
|
|
} elseif (substr($uck, -9) == " LIKE ALL" and is_array($val)) {
|
|
# multiple LIKE (AND).
|
|
$key = self::safecol($sql, substr($key, 0, -9));
|
|
$likes = array();
|
|
|
|
foreach ($val as $index => $match) {
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$key
|
|
)."_".$index;
|
|
$likes[] = $key.$text." LIKE :".$param.$escape;
|
|
$params[":".$param] = $match;
|
|
}
|
|
|
|
$cond = "(".implode(" AND ", $likes).")";
|
|
} elseif (substr($uck, -9) == " NOT LIKE" and is_array($val)) {
|
|
# multiple NOT LIKE.
|
|
$key = self::safecol($sql, substr($key, 0, -9));
|
|
$likes = array();
|
|
|
|
foreach ($val as $index => $match) {
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$key
|
|
)."_".$index;
|
|
$likes[] = $key.$text." NOT LIKE :".$param.$escape;
|
|
$params[":".$param] = $match;
|
|
}
|
|
|
|
$cond = "(".implode(" AND ", $likes).")";
|
|
} elseif (substr($uck, -5) == " LIKE" and is_array($val)) {
|
|
# multiple LIKE (OR).
|
|
$key = self::safecol($sql, substr($key, 0, -5));
|
|
$likes = array();
|
|
|
|
foreach ($val as $index => $match) {
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$key
|
|
)."_".$index;
|
|
$likes[] = $key.$text." LIKE :".$param.$escape;
|
|
$params[":".$param] = $match;
|
|
}
|
|
|
|
$cond = "(".implode(" OR ", $likes).")";
|
|
} elseif (substr($uck, -9) == " NOT LIKE") {
|
|
# NOT LIKE.
|
|
$key = self::safecol($sql, substr($key, 0, -9));
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$key
|
|
);
|
|
$cond = $key.$text." NOT LIKE :".$param.$escape;
|
|
$params[":".$param] = $val;
|
|
} elseif (substr($uck, -5) == " LIKE") {
|
|
# LIKE.
|
|
$key = self::safecol($sql, substr($key, 0, -5));
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$key
|
|
);
|
|
$cond = $key.$text." LIKE :".$param.$escape;
|
|
$params[":".$param] = $val;
|
|
} elseif (substr_count($key, " ")) {
|
|
# Custom operation, e.g. array("foo >" => $bar).
|
|
list($param,) = explode(" ", $key);
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$param
|
|
);
|
|
$cond = self::safecol($sql, $key)." :".$param;
|
|
$params[":".$param] = $val;
|
|
} else {
|
|
# Equation.
|
|
if (is_array($val)) {
|
|
$cond = self::safecol($sql, $key)." IN ".
|
|
self::build_list($sql, $val, $params);
|
|
} elseif ($val === null and $insert) {
|
|
$cond = self::safecol($sql, $key)." = ''";
|
|
} elseif ($val === null) {
|
|
$cond = self::safecol($sql, $key)." IS NULL";
|
|
} else {
|
|
$param = str_replace(
|
|
array("(", ")", "."),
|
|
"_",
|
|
$key
|
|
);
|
|
$cond = self::safecol($sql, $key)." = :".$param;
|
|
|
|
if ($insert) {
|
|
if (
|
|
$key == "updated_at" and
|
|
is_datetime_zero($val)
|
|
)
|
|
$val = SQL_DATETIME_ZERO;
|
|
}
|
|
|
|
$params[":".$param] = $val;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($tables)
|
|
self::tablefy($sql, $cond, $tables);
|
|
|
|
$conditions[] = $cond;
|
|
}
|
|
|
|
return $conditions;
|
|
}
|
|
|
|
/**
|
|
* Function: tablefy
|
|
* Prepends table names and prefixes to a field if it doesn't already have them.
|
|
*
|
|
* Parameters:
|
|
* $sql - The SQL instance calling this method.
|
|
* &$field - The field to "tablefy".
|
|
* $tables - An array of tables. The first one will be used for prepending.
|
|
*/
|
|
public static function tablefy(
|
|
$sql,
|
|
&$field,
|
|
$tables
|
|
): void {
|
|
if (
|
|
!preg_match_all(
|
|
"/(\(|[\s]+|^)(?!__)([a-z0-9_\.\*]+)(\)|[\s]+|$)/",
|
|
$field,
|
|
$matches
|
|
)
|
|
)
|
|
return;
|
|
|
|
foreach ($matches[0] as $index => $full) {
|
|
$before = $matches[1][$index];
|
|
$name = $matches[2][$index];
|
|
$after = $matches[3][$index];
|
|
|
|
if (is_numeric($name))
|
|
continue;
|
|
|
|
# Does it not already have a table specified?
|
|
if (!substr_count($full, ".")) {
|
|
# Don't replace things that are already either prefixed or parameterized.
|
|
$field = preg_replace(
|
|
"/([^\.:'\"_]|^)".preg_quote($full, "/")."/",
|
|
"\\1".$before."\"__".$tables[0]."\".".$name.$after,
|
|
$field,
|
|
1
|
|
);
|
|
} else {
|
|
$field = preg_replace(
|
|
"/([^\.:'\"_]|^)".preg_quote($full, "/")."/",
|
|
"\\1".$before."\"__".str_replace(".", "\".", $name).$after,
|
|
$field,
|
|
1
|
|
);
|
|
}
|
|
}
|
|
|
|
$field = preg_replace("/AS ([^ ]+)\./i", "AS ", $field);
|
|
}
|
|
}
|