Page Menu
Home
GitPull.it
Search
Configure Global Search
Log In
Files
F15175866
No One
Temporary
Actions
View File
Edit File
Delete File
View Transforms
Subscribe
Mute Notifications
Award Token
Authored By
Unknown
Size
16 KB
Referenced Files
None
Subscribers
None
View Options
diff --git a/class-Query.php b/class-Query.php
index f285e2e..6ff7dea 100644
--- a/class-Query.php
+++ b/class-Query.php
@@ -1,682 +1,682 @@
<?php
# Copyright (C) 2017, 2018, 2019 Valerio Bozzolan
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU 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 General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
/**
* Class useful to build a database query
*/
class Query {
/**
* Connection to the database
*
* @var DB
*/
private $db;
/**
* Class used as default for the results
*
* @var string
*/
protected $resultClass;
/**
* Fields for the SELECT
*
* @var array
*/
private $selectFields = [];
/**
* Table names (without table prefix)
*
* @var array
*/
private $tables = [];
/**
* Stuff to build the FROM (custom sub-queries, etc.)
*
* @var array
*/
private $from = [];
/**
* "GROUP BY" statements
*
* @var array
*/
private $groups = [];
/**
* "HAVING" statements
*
* @var array
*/
private $having;
/**
* "WHERE" conditions
*
* @var array
*/
private $conditions;
/**
* "ORDER BY" statements
*
* @var string
*/
private $orders;
/**
* Offset for the LIMIT
*
* @var int
*/
private $offset;
/**
* Row count for the LIMIT
*
* @var int
*/
private $rowCount;
/**
* Constructor
*
* @param object $db Database object (class DB) or NULL for the default one
* @param string $class_name Class to encapsulate the database result
*/
public function __construct( $db = null, $class_name = null ) {
// assign the specified database or the default one
$this->db = $db ? $db : DB::instance();
if( $class_name ) {
$this->defaultClass( $class_name );
}
}
/**
* Constructor shortcut from class name
*
* @param string $class_name Class to encapsulate the database result
* @param object $db Database object (class DB)
* @return self
*/
public static function factory( $class_name = null, $db = null ) {
return new self( $db, $class_name );
}
/**
* Selected fields (SELECT).
*
* @param string|array $fields
* @return self
*/
public function select() {
return $this->appendInArray( func_get_args(), $this->selectFields );
}
/**
* Selected tables
*
* @param string|array $tables Table/tables without database prefix
* @return self
*/
public function from() {
return $this->appendInArray( func_get_args(), $this->tables );
}
/**
* Set a custom from value
*
* @param string e.g. "(SELECT * ...) as t1"
* @return self
*/
public function fromCustom( $from ) {
$this->from[] = $from;
return $this;
}
/**
* To compare two columns somehow
*
* @param string $one Something (NOT SANITIZED)
* @param string $verb Comparison method
* @param string $two Something (NOT SANITIZED)
* @param string $glue Conditions glue
* @return self
*/
public function compare( $one, $verb, $two, $glue = 'AND' ) {
return $this->where( "$one $verb $two", $glue );
}
/**
* To be used for PRIMARY KEY joins
*
* @param string $one Something (NOT SANITIZED)
* @param string $two Something (NOT SANITIZED)
* @return self
*/
public function equals( $one, $two ) {
return $this->compare( $one, '=', $two );
}
/**
* Append a query condition
*
* @param string $condition Something as 'field = 1'
* @param string $glue Conditions glue
* @return self
*/
public function where( $condition, $glue = 'AND' ) {
if( null !== $this->conditions ) {
$this->conditions .= " $glue ";
}
$this->conditions .= $condition;
return $this;
}
/**
* Intended to compare a column with a number
*
* @param string $one Column name
* @param int $value Value
* @param string $verb Compare method
* @return self
*/
public function whereInt( $column, $value, $verb = '=' ) {
return $this->compare( $column, $verb, (int)$value );
}
/**
* Intended to compare a column with a string
*
* @param string $one Column name
* @param string $value Value
* @param string $verb Compare method
* @return self
*/
public function whereStr( $column, $value, $verb = '=' ) {
$value = esc_sql( $value );
return $this->compare( $column, $verb, "'$value'" );
}
/**
* Filter by a LIKE command
*
* @param $column string Column name
* @param $value string Value to be liked
* @param $left string To have whatever before the value
* @param $right string To have whatever after the value
*/
public function whereLike( $column, $value, $left = true, $right = true ) {
$left = $left ? '%' : '';
$right = $right ? '%' : '';
$value = $left . esc_sql_like( $value ) . $right;
return $this->whereStr( $column, $value, 'LIKE' );
}
/**
* Append a custom join from the latest selected table
*
* @param $type string Join type e.g. INNER, LEFT, RIGHT, etc.
* @param $table string Table name
* @param $a string First column name for the ON clause (or complete ON condition if $b is empty)
* @param $b string Second column name for the ON clause (or complete ON condition if $a is empty)
* @param $alias mixed Table alias. As default is true, and the table prefix is removed.
*/
public function joinOn( $type, $table, $a, $b = null, $alias = true ) {
if( $this->from ) {
$previous = array_pop( $this->from );
} elseif( $this->tables ) {
$previous = array_pop( $this->tables );
$previous = $this->db->getTable( $previous, true );
} else {
throw new InvalidArgumentException( 'not enough tables' );
}
$table = $this->db->getTable( $table, $alias );
$conditions = [];
if( $a ) {
$conditions[] = $a;
}
if( $b ) {
$conditions[] = $b;
}
$on = implode( '=', $conditions );
$this->from[] = "$previous $type JOIN $table ON ($on)";
return $this;
}
/**
* Group by
*
* @param string|array $groups Group by
* @return self
*/
public function groupBy() {
return $this->appendInArray( func_get_args(), $this->groups );
}
/**
* You have double-selected a table but that wasn't your goal.
*
* @return self
*/
public function uniqueTables() {
$this->tables = array_unique( $this->tables );
return $this;
}
/**
* LIMIT count, offset.
*
* @param int $row_count Max numbers of elements.
* @param int $offset Starting offset
* @return self
*/
public function limit( $row_count, $offset = null ) {
$this->rowCount = $row_count;
$this->offset = $offset;
return $this;
}
/**
* Get the LIMIT SQL clause (if any)
*
* @return string
*/
public function getLimitClause() {
if( $this->rowCount !== null ) {
$query = ' LIMIT ';
if( $this->offset ) {
$query .= "{$this->offset}, ";
}
return $query . $this->rowCount;
}
}
/**
* Handy shortcut for `something IN (values)` condition.
*
* Actually the type (int or string) is inherited from the first value.
*
* @param string $heystack Column name
* @param string|array $needles Values to compare
* @return self
*/
public function whereSomethingIn( $heystack, $needles, $glue = 'AND', $not_in = false ) {
force_array( $needles );
// no needles no filter
$n_needles = count( $needles );
if( !$n_needles ) {
return $this;
}
// the type is inherited from the first value
$is_int = is_int( reset( $needles ) );
// case with just one element
if( $n_needles === 1 ) {
$needle = array_pop( $needles );
$verb = $not_in ? '!=' : '=';
return $is_int
? $this->whereInt( $heystack, $needle, $verb )
: $this->whereStr( $heystack, $needle, $verb );
}
// collect the series
$escaped_needles = [];
$force_type = $is_int ? 'd' : 's';
foreach( $needles as $needle ) {
$escaped_needles[] = $this->db->forceType( $needle, $force_type );
}
// build the condition
$values = implode( ', ', $escaped_needles );
$values = "($values)";
$verb = $not_in ? 'NOT IN' : 'IN';
return $this->compare( $heystack, $verb, $values, $glue );
}
/**
* Handy shortcut for `something NOT IN (values)` condition.
*
* @param string $heystack Field.
* @param string|array $needles Values to compare.
*/
public function whereSomethingNotIn( $heystack, $needles, $glue = 'AND' ) {
- return $this->appendConditionSomethingIn( $heystack, $needles, $glue, true ); // See true
+ return $this->whereSomethingIn( $heystack, $needles, $glue, true ); // See true
}
/**
* Get the FROM clause
*
* @return string
*/
public function getFrom() {
$tables = $this->db->getTables( $this->tables );
if( $this->from ) {
if( $tables ) {
$tables .= ' JOIN ';
}
$tables .= implode( ' JOIN ', $this->from );
}
return $tables;
}
/**
* Get the SELECT clause
*
* @return string
*/
public function getSelect() {
if( $this->selectFields ) {
return implode( ', ', $this->selectFields );
}
return '*';
}
/**
* Get the WHERE clause body
*
* @return string
*/
public function getWhere() {
return $this->conditions;
}
/**
* Get the GROUP BY clause body
*
* @return string
*/
public function getGroupBy() {
return implode(', ', $this->groups);
}
/**
* Set the HAVING clause
*
* @param string $having
* @return self
*/
public function having( $having ) {
$this->having = $having;
return $this;
}
/**
* Order by a field
*
* @param string $order_by Field to sort
* @param string $how Choose 'DESC' or 'ASC', failing to ASC
* @return self
*/
public function orderBy( $order_by, $how = null ) {
if( null !== $this->orders ) {
$this->orders .= ', ';
}
$this->orders .= $order_by;
if( $how ) {
$this->orders .= ' ' . self::filterDirection( $how );
}
return $this;
}
/**
* Mark a SELECT query as needed for an UPDATE
*
* «If you use FOR UPDATE with a storage engine that uses page or row locks,
* rows examined by the query are write-locked until the end of the current transaction.»
*
* @return self
*/
public function forUpdate() {
$this->forUpdate = true;
return $this;
}
/**
* Build an SQL SELECT query
*
* @return string SQL query
* @see https://dev.mysql.com/doc/refman/8.0/en/select.html
*/
public function getQuery() {
$sql = "SELECT {$this->getSelect()} FROM {$this->getFrom()}";
if( $this->conditions ) {
$sql .= " WHERE {$this->conditions}";
}
if( $this->groups ) {
$sql .= " GROUP BY {$this->getGroupBy()}";
}
if( $this->having ) {
$sql .= " HAVING {$this->having}";
}
if( $this->orders ) {
$sql .= " ORDER BY {$this->orders}";
}
$sql .= $this->getLimitClause();
if( isset( $this->forUpdate ) ) {
$sql .= " FOR UPDATE";
}
return $sql;
}
/**
* Get a DELETE query
*
* Note that you MUST specify a condition.
*
* Note that the SQL DELETE query has a strange syntax for
* table aliases. See https://stackoverflow.com/a/11005244
*
* @return string SQL DELETE query
*/
public function getDeleteQuery() {
$table = reset( $this->tables ); // just the only one
$table_full = $this->db->getTable( $table, true );
return "DELETE `$table` FROM $table_full WHERE {$this->conditions} {$this->getLimitClause()}";
}
/**
* Run an SQL DELETE query
*
* @see https://dev.mysql.com/doc/refman/8.0/en/delete.html
*/
public function delete() {
return $this->runDangerousQuery( $this->getDeleteQuery() );
}
/**
* Build and run an SQL UPDATE query
*
* Note that you MUST specify a condition.
*
* @param array $columns Array of DBCol[]
* @return array
*/
public function update( $columns ) {
$query = $this->db->buildUpdateQuery( $this->getFrom(), $columns, $this->conditions, $this->getLimitClause() );
return $this->runDangerousQuery( $query );
}
/**
* Run an INSERT query
*
* @param array $data Array of DBCol sanitized data
* @param array $args Associative array of arguments
* 'replace-into' boolean Run this query as a REPLACE INTO
*/
public function insertRow( $data, $args = [] ) {
if( empty( $this->tables[0] ) ) {
throw new SucklessException( "cannot insert without a table" );
}
return $this->db->insertRow( $this->tables[0], $data, $args );
}
/**
* Set the default class to incapsulate the result set
*
* @param string $class_name Class name
* @return self
*/
public function defaultClass( $class_name ) {
$this->resultClass = $class_name;
return $this;
}
/**
* Get the specified class name or the default one
*
* @param string $class_name
* @return string
*/
public function getDefaultClass( $class_name = null ) {
return $class_name ? $class_name : $this->resultClass;
}
/**
* Run the SELECT query
*
* @see DB#query()
*/
public function query() {
return $this->db->query( $this->getQuery() );
}
/**
* Get the array of result sets encapsulated in the specified class.
*
* @param string $class_name
* @see DB#getResults()
* @return array
*/
public function queryResults( $class_name = null, $params = [] ) {
return $this->db->getResults(
$this->getQuery(),
$this->getDefaultClass( $class_name ),
$params
);
}
/**
* Get a generator of $class_name objects.
*
* @param string $class_name
* @see DB#getResults()
* @return array
*/
public function queryGenerator( $class_name = null, $params = [] ) {
return $this->db->getGenerator(
$this->getQuery(),
$this->getDefaultClass( $class_name ),
$params
);
}
/**
* Get the result set encapsulated in the specified class.
*
* @param string $class_name
* @see DB#getRow()
* @return null|Object
*/
public function queryRow( $class_name = null, $params = [] ) {
return $this->db->getRow(
$this->getQuery(),
$this->getDefaultClass( $class_name ),
$params
);
}
/**
* Get the specified column from the first result set.
*
* @param string $column_name
* @see DB#getValue()
* @return mixed
*/
public function queryValue( $column_name ) {
return $this->db->getValue( $this->getQuery(), $column_name );
}
/**
* Append a value or some values into an array
*
* @param array|string $values An array of values or just a value
* @param array $array Array that will be modified
* @return self
*/
private function appendInArray( $values, & $array ) {
// retro-compatibility feature
if( isset( $values[ 0 ] ) && is_array( $values[ 0 ] ) ) {
$values = $values[ 0 ];
}
foreach( $values as $value ) {
$array[] = $value;
}
return $this;
}
/**
* Filter a direction
*
* The fallback is ASC.
*
* @param $dir string
* @return string DESC|ASC
*/
public static function filterDirection( $dir ) {
$dir = strtoupper( $dir );
return $dir === 'DESC' ? 'DESC' : 'ASC';
}
/**
* Check if this query is involving just a single table
*
* @return bool
*/
private function isSimpleFrom() {
return count( $this->from ) + count( $this->tables ) === 1;
}
/**
* Run a query that MUST involve just a single table and have a condition
*
* @param string $query SQL query
* @param string $kind Kind of query (just for the debug message)
*/
private function runDangerousQuery( $query ) {
if( !$this->conditions || !$this->isSimpleFrom() ) {
if( DEBUG_QUERIES ) {
error( $query );
}
if( $this->conditions ) {
throw new SucklessException( "for security reasons you cannot build this kind of query involving multiple tables" );
}
throw new SucklessException( "for security reasons you cannot build this kind of query without a condition" );
}
return $this->db->query( $query );
}
}
File Metadata
Details
Attached
Mime Type
text/x-diff
Expires
Thu, Nov 19, 03:09 (1 d, 6 h)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
2264177
Default Alt Text
(16 KB)
Attached To
Mode
R15 suckless-php: Another PHP framework that sucks less
Attached
Detach File
Event Timeline