diff --git a/DEVELOPING.md b/DEVELOPING.md
index 9a7868b..f177efa 100644
--- a/DEVELOPING.md
+++ b/DEVELOPING.md
@@ -1,203 +1,208 @@
# Boz PHP - Another PHP Framework
## Project structure
* `load.php` - Should be unversioned (database credentials, `ROOT`, `ABSPATH` constants, etc.)
* `load-post.php` - Can be versioned (roles, permissions, JavaScript and CSS files, etc.)
## Sample page
Here an example of `index.php`:
```php
...
...
```
Long story short: just put a `require 'load.php'` in top of your template to access the framework.
In the next examples the `require 'load.php'` part is implicit.
## JSON
An example of a JSON API page could be this:
```php
// do some pre-conditions
if( !isset( $_GET['ping'] ) ) {
// die with an HTTP status code 400 (Bad Request) and show a message
json_error( 400, 'missing-ping', "Please specify the 'ping' GET argument" );
}
json( [
'success' => true,
'pong' => $_GET['ping'],
] );
```
Note that to optimize the data transfer, `false` and `null` values and empty objects will be automagically removed from the JSON.
## Query
Note that the database connection is established automagically only if you need it. Now some examples.
### Query (declarative)
```php
query( "ALTER TABLE ADD COLUMN `hello` ..." );
```
### Select rows (full list)
```php
// results as a pure array
$posts = query_results( "SELECT post_title FROM wp_post" );
// as example, let's expose them in JSON
json( $posts );
```
Note that `query_results()` is better over `query_generator()` if:
* You need the full list (e.g. to count them, etc.)
* You want to read the list multiple times
### Select rows (generator)
```php
// generator of results
$posts = query_generator( "SELECT post_title FROM wp_post" );
if( $posts->valid() ) {
foreach( $posts as $post ) {
echo $post->post_title;
}
}
```
Note that `query_generator()` is better over `query_results()` if:
* You do not need the full list (but just one at time)
* You do not need to read the list multiple times.
### Query builder (object-oriented)
```php
// get a pure array
$posts = ( new Query() )
->from( 'post' )
->queryResults();
```
```php
// generator of results
$posts = ( new Query() )
->from( 'post' )
->queryGenerator();
```
```php
$posts = ( new Query() )
->from( 'post' )
->whereStr( 'post_title', $title )
->whereInt( 'post_author_id', $author )
->whereSomethingIn( 'post_status', [ 'private', 'stub', 'deleted' ] )
// implicit join + condition
->from( 'comment' )
->where( 'comment.user_ID = user.user_ID' )
// explicit JOIN ON
->joinOn( 'LEFT', 'user', 'user.user_ID', 'post.post_author_id' )
->queryResults();
```
## Insert
Declarative way:
```php
insert_row( 'post', [
- new DBCol( 'post_ID', $id, 'd' ),
- new DBCol( 'post_title', $title, 's' ),
+ // quick sanitize an integer
+ 'post_ID' => 2,
+
+ // quick sanitize a string
+ 'post_title' => 'Title unsafe',
+
+ // assign a MySQL function to a column
new DBCol( 'post_creation_date', 'NOW()', '-' ),
] );
```
Object-oriented way:
```php
( new Query() )
->from( 'post' )
->insertRow( [
new DBCol( 'post_ID', $id, 'd' ),
new DBCol( 'post_title', $title, 's' ),
new DBCol( 'post_creation_date', 'NOW()', '-' ),
] );
```
## Delete
```php
( new Query() )
->from( 'post' )
->whereStr( 'post_author', 'jhon' )
->whereInt( 'post_stub, 1 )
->delete();
```
## Database row → object (mapping)
```php
/**
* Declaration of my custom class
*/
class Post extends Queried {
/**
* Table name without table prefix
*/
const T = 'post';
/**
* An example method
*
* @return string
*/
public function getTitle() {
return $this->post_title;
}
}
// now you have a query builder
$post = Post::factory()
->whereInt( 'post_ID', 1 )
->queryRow();
// do stuff
if( $post ) {
// this method exists
$title = $post->getTitle();
}
```
## CSRF protection
To both identify a form and secure it against [Cross-site request forgery](https://en.wikipedia.org/wiki/Cross-site_request_forgery) you can use `form_action()` and `is_action()`:
```php
```
diff --git a/class-DB.php b/class-DB.php
index 3fdd3b0..5cde21a 100644
--- a/class-DB.php
+++ b/class-DB.php
@@ -1,491 +1,513 @@
.
/*
* Use: esc_html(), DEBUG, force_array()
*/
/**
* A simple but effective database class
*
* This class was forked from my defunct project called Open Student.
*/
class DB {
/**
* MySQL connection link
*/
private $mysqli;
/**
* Table prefix
*
* @var string
*/
private $prefix;
/**
* Number of executed queries
*
* Read by get_num_queries()
*
* @var int
*/
public $queries = 0;
/**
* Last query result
*
* @var query-result
*/
private $last = false;
/**
* Singleton instance
*
* @var self
*/
private static $_instance;
/**
* Get the singleton instance of this class
*
* @return self
*/
public static function instance() {
if( ! self::$_instance ) {
self::$_instance = new self();
}
return self::$_instance;
}
/**
* Check if ::instance() was called
*
* @return boolean
*/
public static function instanced() {
return isset( self::$_instance );
}
/**
* Prepare the DB object.
*
* @param type $username Database username
* @param type $password Database password
* @param type $location Database location
* @param type $database Database name
* @param type $prefix Table Prefix
*/
function __construct( $username = null, $password = null, $location = null, $database = null, $prefix = '', $charset = 'utf8' ) {
// default credentials usually defined from your load.php
if( ! func_num_args() ) {
$username = $GLOBALS['username'];
$password = $GLOBALS['password'];
$location = $GLOBALS['location'];
$database = $GLOBALS['database'];
$prefix = $GLOBALS['prefix'];
}
$this->prefix = $prefix;
@$this->mysqli = new mysqli( $location, $username, $password, $database );
if( $this->mysqli->connect_errno ) {
$length = strlen( $password );
throw new SucklessException( "unable to connect to the database '$database' using user '$username' and password ($length characters) on MySQL/MariaDB host '$location'" );
} else {
$this->mysqli->set_charset( $charset );
}
}
/**
* Execute an SQL query
*
* @param string $query The SQL query to execute
*/
public function query( $query ) {
$this->queries++;
if( $this->last !== false && $this->last !== true) {
$this->last->free();
}
$this->last = $this->mysqli->query( $query );
if( DEBUG_QUERIES ) {
error( "query n. {$this->queries}: $query" );
}
if( !$this->last ) {
throw new SucklessException( $this->getQueryErrorMessage( $query ) );
}
return $this->last;
}
/**
* Select only a row from the database
*/
public function getRow( $query, $class_name = null, $params = [] ) {
$results = $this->getResults( $query, $class_name, $params );
return isset( $results[ 0 ] ) ? $results[ 0 ] : null;
}
/**
* Select only a column from a single row
*/
public function getValue( $query, $column_name, $class_name = null, $params = [] ) {
$row = $this->getRow( $query, $class_name, $params );
return isset( $row->{ $column_name } ) ? $row->{ $column_name } : null;
}
/**
* Execute a query and return an array of $class_name objects.
*
* @param string $query Database SQL query.
* @param string $class_name The name of the class to instantiate.
* @param array $params Optional data for the $class_name constructor.
* @See http://php.net/manual/en/mysqli-result.fetch-object.php
*/
public function getResults($query, $class_name = null, $params = [] ) {
// IS_ARRAY() IS SHIT FOR HISTORICAL REASONS
if( $class_name === null || is_array( $class_name ) ) {
$class_name = 'Queried';
}
// IS_ARRAY() IS SHIT FOR HISTORICAL REASONS
$this->query( $query );
$res = [];
while( $row = $this->last->fetch_object( $class_name, $params ) ) {
$res[] = $row;
}
return $res;
}
/**
* Execute a query and return a generator of $class_name objects.
*
* @param string $query Database SQL query.
* @param string $class_name The name of the class to instantiate.
- * @param array $params Optional data for the $class_name constructor.
+ * @param array $params Optional data for the $class_name constructor.
* @See http://php.net/manual/en/mysqli-result.fetch-object.php
*/
public function getGenerator( $query, $class_name = null, $params = [] ) {
if( !$class_name ) {
$class_name = 'Queried';
}
$result = $this->query( $query );
$this->last = true; // to don't be killed from another query() call
while( $row = $result->fetch_object( $class_name, $params ) ) {
yield $row;
}
$result->free();
}
/**
* Run an INSERT query for a single row
*
- * @param string $table
+ * @param string $table
* @param DBCol[] $cols
- * @param $args arguments like 'replace-into
+ * @param array $args arguments like 'replace-into
*/
public function insertRow( $table, $cols, $args = [] ) {
+
+ $cols = DBCol::normalizeArray( $cols );
+
// build column names
$columns = [];
foreach( $cols as $col ) {
$columns[] = "`{$col->column}`";
}
// sanitize values
$values = [];
foreach( $cols as $col ) {
$values[] = $this->forceType( $col->value, $col->forceType );
}
// default arguments
$args = array_replace( [
'replace-into' => false,
], $args );
$what = $args[ 'replace-into' ] ? 'REPLACE' : 'INSERT';
$table = $this->getTable( $table, false );
$columns_comma = implode( ', ', $columns );
$values_comma = implode( ', ', $values );
return $this->query("$what INTO $table ($columns_comma) VALUES ($values_comma)");
}
/**
* Executes one or multiple queries which are concatenated by a semicolon
*
* @param $queries string
*/
public function multiQuery( $queries ) {
$i = 1;
if( !$this->mysqli->multi_query( $queries ) ) {
throw new SucklessException( "error in MySQLi#multi_query() with statement n. $i (starting from 1): {$this->mysqli->error}" );
}
while( $this->mysqli->more_results() ) {
$i++;
if( !$this->mysqli->next_result() ) {
throw new SucklessException( "error in MySQLi#multi_query() with statement n. $i (starting from 1): {$this->mysqli->error}" );
}
}
}
/**
* Run an INSERT query for multiple rows
*
* @param string $table Table Name without prefix
* @param array $columns Assoc array of types ('ID' => 'null', 'name' => 's', ..)
* @param array $rows Array of rows (or just a row)
* @param array $args Extra arguments
*/
public function insert( $table, $columns, $rows, $args = [] ) {
// default arguments
$args = array_replace( [
'replace-into' => false,
], $args );
+ // allow columns to be specified as an associative array
+ $columns = DBCol::normalizeArray( $columns );
+
// backticked column names
$column_names = [];
foreach( $columns as $column => $type ) {
$column_names[] = "`$column`";
}
$columns_comma = implode( ', ', $column_names );
// backward compatibility
force_array( $rows );
if( ! @is_array( $rows[0] ) ) {
$rows = [$rows];
}
// just the types (in order to be indexed numerically)
$types = array_values( $columns );
$n_columns = count( $types );
$value_groups = [];
foreach( $rows as $i => $row ) {
$query_values = [];
if( $n_columns !== count( $row ) ) {
throw new SucklessException( sprintf(
"error using insert() in table %s: %d columns but %d values in row %d",
$table,
$n_columns,
count( $row ),
$i
) );
}
$values_escaped = [];
foreach( $types as $j => $type ) {
$values_escaped[] = $this->forceType( $row[ $j ], $type );
}
$values_grouped = implode( ', ', $values_escaped );
$value_groups[] = "($values_grouped)";
}
$value_groups_comma = implode( ', ', $value_groups );
$action = $args['replace-into'] ? 'REPLACE' : 'INSERT';
$table = $this->getTable( $table, false );
return $this->query( "$action INTO $table ($columns_comma) VALUES $value_groups_comma" );
}
/**
* Run an UPDATE query
*
- * @param string $table Table name without prefix and backticks
- * @param array $columns array of DBCol(s)
- * @param string $conditions part after WHERE
+ * @param string $table Table name without prefix and backticks
+ * @param array $columns Array of DBCol(s)
+ * @param string $conditions Part after WHERE
* @param string $after
*/
public function update( $table, $columns, $conditions, $after = '' ) {
+ $table = $this->getTable( $table, true );
+ return $this->query( $this->buildUpdateQuery( $table, $columns, $conditions, $after ) );
+ }
- // backward compatibility
+ /**
+ * Build an UPDATE query
+ *
+ * @param string $table_raw Table name with prefix and backticks
+ * @param array $columns Array of DBCol[], or an associative array of column and its value
+ * @param string $conditions part after WHERE
+ * @param string $after
+ * @return string
+ */
+ public function buildUpdateQuery( $table_raw, $columns, $conditions, $after = '' ) {
+
+ // for backward compatibility allow a single column
force_array( $columns );
- $table = $this->getTable( $table, false );
+ // allow columns to be specified as an associative array
+ $columns = DBCol::normalizeArray( $columns );
+ // build the value assignation for each column
$sets = [];
foreach( $columns as $column ) {
$name = $column->column;
$value = $this->forceType( $column->value, $column->forceType );
$sets[] = "`$name` = $value";
}
-
$sets_comma = implode( ', ', $sets );
- return $this->query( "UPDATE $table SET $sets_comma WHERE $conditions $after" );
+
+ return "UPDATE $table_raw SET $sets_comma WHERE $conditions $after";
}
/**
* Get the last inserted ID
*
* @return string
*/
public function getLastInsertedID() {
return $this->mysqli->insert_id;
}
/**
* Get the last number of rows retrieved
*
* @return int
*/
public function getLastNumRows() {
return $this->last->num_rows;
}
/**
* Escape a string
*
* @param string $s String to be escaped
* @return string String escaped.
*/
public function escapeString( $s ) {
return $this->mysqli->real_escape_string( $s );
}
/**
* Get the table prefix
*
* @return string
*/
public function getPrefix() {
return $this->prefix;
}
/**
* Get table name with it's prefix (if any)
*
* @param string $name Table name
* @param mixed $as True to create an alias without the table prefix, or specify your alias
* @return string Table eventually with the alias
*/
public function getTable( $name, $as = true ) {
$table = "`{$this->prefix}$name`";
if( $as ) {
if( $as === true ) {
$as = $name;
}
$table .= " AS `$as`";
}
return $table;
}
/**
* Get the list of every table name inserted as arguments or as an array
*
* @return string
*/
public function getTables( $args = [] ) {
$tables = [];
if( ! is_array( $args ) ) {
$args = func_get_args();
}
foreach( $args as $arg ) {
$tables[] = $this->getTable( $arg, true );
}
return implode( ' JOIN ', $tables );
}
/**
* Force a string to a defined type
*
* @param string $s String to be forced.
* @param string $type Type ('d' for integer, 's' for string, 'f' for float, 'null' for autoincrement values or for "don't care" values).
* @see http://news.php.net/php.bugs/195815
* @return string Forced string
*/
public function forceType( $s, $type ) {
if( $type === 'd' )
return (int) $s; // Integer
if( $type === 'f' )
return (float) $s; // Float
if( $type === 'dnull' )
return ($s === null) ? 'NULL' : (int) $s; // Integer - or NULL
if( $type === 'fnull' )
return ($s === null) ? 'NULL' : (float) $s; // Float - or NULL
if( $type === 's' )
return "'{$this->escapeString($s)}'"; // String escaped
if( $type === 'snull' )
return ($s === null) ? 'NULL' : "'{$this->escapeString($s)}'"; // String escaped - or NULL
if( $type === 'f' )
return (float) $s; // Float value
if( $type === '-' )
return $s; // Float value
if( $type === null || $type === 'null' )
return 'NULL'; // 'NULL' literally for indexes
error( "type $type unexpected in DB::forceType() and so it will be seen as 's'" );
return $this->forceType( $s, 's' );
}
/**
* Get the number of affected rows
*
* @return int
*/
public function affectedRows() {
$this->mysqli->affected_rows();
}
/**
* Please close the door when you leave a room
*/
public function closeConnection() {
if( $this->mysqli ) {
@$this->mysqli->close();
}
}
/**
* Automatically close the door when you leave the room
*/
public function __destruct() {
$this->closeConnection();
}
/**
* Show a friendly error about last MySQL query
*
* @param string $query SQL query executed during the error
* @return string
*/
private function getQueryErrorMessage( $query ) {
return sprintf(
"error executing the query n. %d |%s| error: %s",
$this->queries,
$query,
$this->mysqli->error
);
}
}
diff --git a/class-DBCol.php b/class-DBCol.php
index 25fb392..4b31fce 100644
--- a/class-DBCol.php
+++ b/class-DBCol.php
@@ -1,74 +1,119 @@
.
/**
- * A database column
+ * A database column sanification descriptor
*
* This class is useful to sanitize values before inserting them into the database
*/
class DBCol {
public $column;
+
public $forceType;
+
public $value;
/**
+ * Constructor
+ *
* @param $column string Name of the column e.g. 'user_ID'
* @param $value string Related value e.g. '1'
* @param $forceType string
* 's' string
* 'snull' string or NULL
* 'd' integer
* 'dnull' integer or NULL
* 'f' float
* 'fnull' float or NULL
* '-' no sanitization at ALL
*/
function __construct( $column, $value, $forceType ) {
- $this->column = $column;
- $this->value = $value;
+ $this->column = $column;
+ $this->value = $value;
$this->forceType = $forceType;
}
public function getType() {
return $this->forceType;
}
public function getValue() {
return $this->value;
}
public function setValue( $value ) {
return $this->value = $value;
}
/**
* Empty strings or zeroes can be promoted to NULL values.
*/
public function promoteNULL() {
$type = $this->forceType;
$value = $this->value;
if( 'snull' === $type && '' === $value || 'dnull' === $type && !$value ) {
$this->value = null;
}
}
+ /**
+ * Check if the value will be a string
+ *
+ * @return boolean
+ */
public function isString() {
return $this->forceType === 's' ||
( $this->forceType === 'snull' && $this->value !== null );
}
-}
+ /**
+ * Normalize a mixed array of DBCol and string/int values
+ *
+ * It's useful to allow syntax like:
+ * insertRow( [ 'name' => 'Mario' )
+ * Instead of:
+ * insertRow( [ new DBCol( 'name', 'mario', 's' ) ] );
+ *
+ * @return array An array of DBCol values
+ */
+ public static function normalizeArray( $columns ) {
+
+ // values to be returned
+ $definitive = [];
+
+ // check every user column
+ foreach( $columns as $key => $value ) {
+
+ if( !is_object( $value ) ) {
+
+ // auto-guess type
+ $type = 's';
+ if( $value === null ) {
+ $type = null;
+ } elseif( is_int( $value ) ) {
+ $type = 'd';
+ }
+
+ $value = new self( $key, $value, $type );
+ }
+
+ $definitive[] = $value;
+ }
+ return $definitive;
+ }
+
+}
diff --git a/class-Query.php b/class-Query.php
index 4298ae7..e019a09 100644
--- a/class-Query.php
+++ b/class-Query.php
@@ -1,626 +1,624 @@
.
/**
* Class useful to build a database query
*/
class Query {
+
private $db;
private $class_name;
private $selectFields = [];
+
private $tables = [];
+
private $from = [];
+
private $groups = [];
+
private $having;
+
private $conditions;
+
private $offset;
+
private $rowCount;
+
private $orders;
/**
* Constructor
*
* @param object $db Database object (class DB)
* @param string $class_name Class to encapsulate the database result
*/
public function __construct( $db = null, $class_name = null ) {
$this->db = $db ? $db : DB::instance(); // Dependency injection
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->tables ) {
$previous = array_pop( $this->tables );
$previous = $this->db->getTable( $previous, true );
} elseif( $this->from ) {
$previous = array_pop( $this->from );
} 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
}
/**
* 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
+ * @param array $columns Array of DBCol[]
* @return array
*/
public function update( $columns ) {
- force_array( $columns );
-
- $sets = [];
- foreach( $columns as $column ) {
- $name = $column->column;
- $value = $this->db->forceType( $column->value, $column->forceType );
- $sets[] = "`$name` = $value";
- }
-
- $sets_comma = implode( ', ', $sets );
- $query = "UPDATE {$this->getFrom()} SET $sets_comma WHERE {$this->conditions}";
- $query .= $this->getLimitClause();
+ $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->class_name = $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->class_name;
}
/**
* 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 );
}
}