lib/ezFunctions.php
<?php
declare(strict_types=1);
namespace ezsql\functions;
use ezsql\Db;
use ezsql\ezQuery;
use ezsql\ezSchema;
use ezsql\Database;
use ezsql\ezQueryInterface;
use ezsql\DatabaseInterface;
use ezsql\ezsqlModelInterface;
if (!\function_exists('ezFunctions')) {
/**
* Returns the global database class, last created instance.
*
* @return ezQueryInterface|null
*/
function getInstance(): ?ezQueryInterface
{
return Db::get('global');
}
/**
* Clear/unset the global database class instance.
*/
function clearInstance(): void
{
Db::clear('global');
}
/**
* Initialize and connect a vendor's database.
*
* @param string $sqlDriver - SQL driver
* @param array $connectionSetting SQL connection parameters, in the following:
*```js
* [
* user, // The database user name.
* password, // The database users password.
* database, // The name of the database.
* host, // The host name or IP address of the database server. Default is localhost
* port // The database TCP/IP port. Default is: 5432 - PostgreSQL, 3306 - MySQL
* ]
*```
* for: **mysqli** - (`username`, `password`, `database`, `host`, `port`, `charset`)
* - `charset` // The database charset,
* Default is empty string
*
* for: **postgresql** - (`username`, `password`, `database`, `host`, `port`)
*
* for: **sqlserver** - (`username`, `password`, `database`, `host`, `convertMysqlToMssqlQuery`)
* - `convertMysqlToMssqlQuery` // convert Queries in MySql syntax to MS-SQL syntax
* Default is false
*
* for: **pdo** - (`dsn`, `username`, `password`, `options`, `isFile`?)
* - `dsn` // The PDO DSN connection parameter string
* - `options` // Array for setting connection options as MySQL
* - `isFile` // File based databases like SQLite don't need
* user and password, they work with path in the dsn parameter
* Default is false
*
* for: **sqlite3** - (`filePath`, `database`)
* - `filePath` // The path to open an SQLite database
*
* @param string $instanceTag - Store the instance for later use
* @return \ezsql\Database\ez_pdo|\ezsql\Database\ez_pgsql|\ezsql\Database\ez_sqlsrv|\ezsql\Database\ez_sqlite3|\ezsql\Database\ez_mysqli
*/
function database(string $sqlDriver = null, array $connectionSetting = null, string $instanceTag = null)
{
return Database::initialize($sqlDriver, $connectionSetting, $instanceTag);
}
/**
* Returns an already initialized database instance that was created with an tag.
*
* @param string $getTag - An stored tag instance
* @return \ezsql\Database\ez_pdo|\ezsql\Database\ez_pgsql|\ezsql\Database\ez_sqlsrv|\ezsql\Database\ez_sqlite3|\ezsql\Database\ez_mysqli
*/
function tagInstance(string $getTag = null)
{
return database($getTag);
}
/**
* Initialize an mysqli database.
*
* @param array $databaseSetting SQL connection parameters
* - [ `username`, `password`, `database`, host, port, charset ]
* @param string $instanceTag - Store the instance for later use
*
* @return \ezsql\Database\ez_mysqli
*/
function mysqlInstance(array $databaseSetting = null, string $instanceTag = null)
{
return database(\MYSQLI, $databaseSetting, $instanceTag);
}
/**
* Initialize an pgsql database.
*
* @param array $databaseSetting SQL connection parameters
* - [ `username`, `password`, `database`, host, port ]
* @param string $instanceTag - Store the instance for later use
*
* @return \ezsql\Database\ez_pgsql
*/
function pgsqlInstance(array $databaseSetting = null, string $instanceTag = null)
{
return database(\PGSQL, $databaseSetting, $instanceTag);
}
/**
* Initialize an mssql database.
*
* @param array $databaseSetting - SQL connection parameters
* @param string $instanceTag - Store the instance for later use
*
* @return \ezsql\Database\ez_sqlsrv
*/
function mssqlInstance(array $databaseSetting = null, string $instanceTag = null)
{
return database(\MSSQL, $databaseSetting, $instanceTag);
}
/**
* Initialize an pdo database.
*
* @param array $databaseSetting - SQL connection parameters
* @param string $instanceTag - Store the instance for later use
*
* @return \ezsql\Database\ez_pdo
*/
function pdoInstance(array $databaseSetting = null, string $instanceTag = null)
{
return database(\Pdo, $databaseSetting, $instanceTag);
}
/**
* Initialize an sqlite3 database.
*
* @param array $databaseSetting - SQL connection parameters
* @param string $instanceTag - Store the instance for later use
*
* @return \ezsql\Database\ez_sqlite3
*/
function sqliteInstance(array $databaseSetting = null, string $instanceTag = null)
{
return database(\SQLITE3, $databaseSetting, $instanceTag);
}
/**
* Returns database vendor string, either the global instance, or provided class instance.
* @param \ezsql\DatabaseInterface|null $instance
*
* @return string|null `mysqli`|`pgsql`|`sqlite3`|`sqlsrv`
*/
function get_vendor(DatabaseInterface $instance = null): ?string
{
return ezSchema::vendor($instance);
}
/**
* Convert array to string, and attach '`,`' for separation, if none is provided.
*
* @return string
*/
function to_string($arrays, $separation = ','): string
{
return ezQuery::to_string($arrays, $separation);
}
/**
* Creates an database column as:
* - `column`, data`type`, ...value/options `arguments`.
*
* // datatype are global `CONSTANTS` and can be written out like:
* - VARCHAR, 32, notNULL, PRIMARY, SEQUENCE|AUTO, ....
* // SEQUENCE|AUTO constants will replaced with the proper auto sequence for the SQL driver
*
* @param string $column | `CONSTRAINT`, - column name/CONSTRAINT usage for PRIMARY|FOREIGN KEY
* @param string $type | constraintName, - data type for column/primary|foreign constraint name
* @param mixed ...$arguments any remainder assignments `ordered` like:
* - @param mixed $size, or/and
* - @param mixed $value, - or/and column should be `NULLS`|`notNULL`. If omitted, assumes `NULLS`
* - @param mixed $default, - or/and Optional. It is the value to assign to the column
* - @param mixed $autoNumber, or/and `AUTO` for vendor's auto numbering
* - @param mixed $primaryForeignKeys | or/and `PRIMARY`|`FOREIGN`
*
* @return string|bool - SQL schema string, or false for error
*/
function column(string $column = null, string $type = null, ...$arguments)
{
return ezSchema::column($column, $type, ...$arguments);
}
function primary(string $primaryName, ...$primaryKeys)
{
\array_unshift($primaryKeys, \PRIMARY);
return column(\CONSTRAINT, $primaryName, ...$primaryKeys);
}
function foreign(string $foreignName, ...$foreignKeys)
{
\array_unshift($foreignKeys, \FOREIGN);
return column(\CONSTRAINT, $foreignName, ...$foreignKeys);
}
function unique(string $uniqueName, ...$uniqueKeys)
{
\array_unshift($uniqueKeys, \UNIQUE);
return column(\CONSTRAINT, $uniqueName, ...$uniqueKeys);
}
function index(string $indexName, ...$indexKeys)
{
return column(\INDEX, $indexName, ...$indexKeys);
}
function addColumn(string $columnName, ...$datatype)
{
return column(\ADD, $columnName, ...$datatype);
}
function dropColumn(string $columnName, ...$data)
{
return column(\DROP, $columnName, ...$data);
}
function changingColumn(string $columnName, ...$datatype)
{
return column(\CHANGER, $columnName, ...$datatype);
}
/**
* Creates an equality comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function eq($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \EQ, $y, $and, ...$args);
return $expression;
}
/**
* Creates a non equality comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $operator, - One of
* '<', '>', '=', '!=', '>=', '<=', '<>', 'IN',, 'NOT IN', 'LIKE',
* 'NOT LIKE', 'BETWEEN', 'NOT BETWEEN', 'IS', 'IS NOT', or the constants above.
*
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* function comparison($x, $operator, $y, $and=null, ...$args)
* {
* return array($x, $operator, $y, $and, ...$args);
* }
*
* @return array
*/
function neq($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \NEQ, $y, $and, ...$args);
return $expression;
}
/**
* Creates the other non equality comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function ne($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \NE, $y, $and, ...$args);
return $expression;
}
/**
* Creates a lower-than comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function lt($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \LT, $y, $and, ...$args);
return $expression;
}
/**
* Creates a lower-than-equal comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function lte($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \LTE, $y, $and, ...$args);
return $expression;
}
/**
* Creates a greater-than comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function gt($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \GT, $y, $and, ...$args);
return $expression;
}
/**
* Creates a greater-than-equal comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function gte($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \GTE, $y, $and, ...$args);
return $expression;
}
/**
* Creates an IS NULL expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function isNull($x, $y = 'null', $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_isNULL, $y, $and, ...$args);
return $expression;
}
/**
* Creates an IS NOT NULL expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function isNotNull($x, $y = 'null', $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_notNULL, $y, $and, ...$args);
return $expression;
}
/**
* Creates a LIKE() comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function like($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_LIKE, $y, $and, ...$args);
return $expression;
}
/**
* Creates a NOT LIKE() comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function notLike($x, $y, $and = null, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_notLIKE, $y, $and, ...$args);
return $expression;
}
/**
* Creates a IN () comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function in($x, $y, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_IN, $y, ...$args);
return $expression;
}
/**
* Creates a NOT IN () comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function notIn($x, $y, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_notIN, $y, ...$args);
return $expression;
}
/**
* Creates a BETWEEN () comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function between($x, $y, $y2, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_BETWEEN, $y, $y2, \_AND, ...$args);
return $expression;
}
/**
* Creates a NOT BETWEEN () comparison expression with the given arguments.
*
* @param strings $x, - The left expression.
* @param strings $y, - The right expression.
* @param strings $and, - combine additional expressions with, 'AND','OR', 'NOT', 'AND NOT'.
* @param strings $args - for any extras
*
* @return array
*/
function notBetween($x, $y, $y2, ...$args): array
{
$expression = array();
\array_push($expression, $x, \_notBETWEEN, $y, $y2, \_AND, ...$args);
return $expression;
}
/**
* Get multiple row result set from the database (previously cached results).
* Returns a multi dimensional array.
*
* Each element of the array contains one row of results and can be
* specified to be either an `object`, `json`, `associative array` or `numerical
* array`.
* - If no results are found then the function returns `false`,
* enabling you to use the function within logic statements such as if.
*
* **OBJECT** - `Returning results as an object` is the quickest way to get and
* display results. It is also useful that you are able to put
* `$object->var` syntax directly inside print statements without
* having to worry about causing php parsing errors.
*
* **ARRAY_A** - `Returning results as an associative array` is useful if you would
* like dynamic access to column names.
*
* **ARRAY_N** - `Returning results as a numerical array` is useful if you are using
* completely dynamic queries with varying column names but still need
* a way to get a handle on the results.
*
* **JSON** - `Returning results as JSON encoded` is useful for any interactive dynamic queries.
*
* @param constant $output Either: `OBJECT`|`ARRAY_A`|`ARRAY_N`|`JSON`
* @param object|null $instance `ez_pdo`|`ez_pgsql`|`ez_sqlsrv`|`ez_sqlite3`|`ez_mysqli`
* @return bool|object|array - results as objects (default)
*/
function get_results($output = \OBJECT, $instance = null)
{
$ezQuery = empty($instance) || !is_object($instance) ? getInstance() : $instance;
return ($ezQuery instanceof ezsqlModelInterface)
? $ezQuery->get_results(null, $output, false)
: false;
}
/**
* Clean input string of XSS, html, javascript, etc...
* @param string $string
*
* @return string cleaned string
*/
function clean_string(string $string): string
{
$patterns = array( // strip out:
'@<script[^>]*?>.*?</script>@si', // Strip out javascript
'@<[\/\!]*?[^<>]*?>@si', // HTML tags
'@<style[^>]*?>.*?</style>@siU', // Strip style tags properly
'@<![\s\S]*?--[ \t\n\r]*>@' // Strip multi-line comments
);
$string = \preg_replace($patterns, '', $string);
$string = \trim($string);
$string = \stripslashes($string);
return \htmlentities($string);
}
/**
* Check if path/filename is directory traversal attack.
*
* @param string $basePath base directory to check against
* @param string $filename will be preprocess with `sanitize_path()`
* @return boolean
*/
function is_traversal(string $basePath, string $filename): bool
{
if (\strpos(\urldecode($filename), '..') !== false)
return true;
$realBase = \rtrim(\realpath($basePath), _DS);
$userPath = $realBase . _DS . sanitize_path($filename);
$realUserPath = \realpath($userPath);
// Reassign with un-sanitized if file does not exits
if ($realUserPath === false)
$realUserPath = $filename;
return (\strpos($realUserPath, $realBase) !== 0);
}
/**
* Sanitize path to prevent directory traversal.
*
* Example:
*
* `sanitize_path("../../../../config.php-");`
* - Returns `config.php` without the path traversal
* @param string $path original file/path to be sanitized.
* @return string
*/
function sanitize_path(string $path): string
{
$file = \preg_replace("/\.[\.]+/", "", $path);
$file = \preg_replace("/^[\/]+/", "", $file);
$file = \preg_replace("/^[A-Za-z][:\|][\/]?/", "", $file);
$file = \trim($file, '.-_');
return ($file);
}
/**
* Creates self signed certificate
*
* @param string $privatekeyFile
* @param string $certificateFile
* @param string $signingFile
* // param string $caCertificate
* @param string $ssl_path
* @param array $details - certificate details
*
* Example:
* array $details = [
* "countryName" => '',
* "stateOrProvinceName" => '',
* "localityName" => '',
* "organizationName" => '',
* "organizationalUnitName" => '',
* "commonName" => '',
* "emailAddress" => ''
* ];
*
* @return string certificate path
*/
function create_certificate(
string $privatekeyFile = 'certificate.key',
string $certificateFile = 'certificate.crt',
string $signingFile = 'certificate.csr',
// string $caCertificate = null,
string $ssl_path = null,
array $details = ["commonName" => "localhost"]
) {
if (empty($ssl_path)) {
$ssl_path = \getcwd();
$ssl_path = \preg_replace('/\\\/', \_DS, $ssl_path) . \_DS;
} else
$ssl_path = $ssl_path . \_DS;
$opensslConfig = array("config" => $ssl_path . 'openssl.cnf');
// Generate a new private (and public) key pair
$privatekey = \openssl_pkey_new($opensslConfig);
// Generate a certificate signing request
$csr = \openssl_csr_new($details, $privatekey, $opensslConfig);
// Create a self-signed certificate valid for 365 days
$sslcert = \openssl_csr_sign($csr, null, $privatekey, 365, $opensslConfig);
// Create key file. Note no passphrase
\openssl_pkey_export_to_file($privatekey, $ssl_path . $privatekeyFile, null, $opensslConfig);
// Create server certificate
\openssl_x509_export_to_file($sslcert, $ssl_path . $certificateFile, false);
// Create a signing request file
\openssl_csr_export_to_file($csr, $ssl_path . $signingFile);
return $ssl_path;
}
/**
* Preforms a `select` method call on a already preset `table name`, and optional `prefix`
*
* This function **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
* Returns an `result` set, given the
* - column fields, conditions or conditional array.
*
* In the following format:
* ```php
* selecting(
* columns,
* innerJoin() | leftJoin() | rightJoin() | fullJoin(), // alias of joining(inner|left|right|full, leftTable, rightTable, leftColumn, rightColumn, equal condition),
* where( eq( columns, values, _AND ), like( columns, _d ) ),
* groupBy( columns ),
* having( between( columns, values1, values2 ) ),
* orderBy( columns, desc ),
* limit( numberOfRecords, offset ),
* union(table, columnFields, conditions), // Returns an select SQL string with `UNION`
* unionAll(table, columnFields, conditions) // Returns an select SQL string with `UNION ALL`
*);
* ```
*
* @param mixed $columns fields, string or array
* @param mixed ...$conditions - of the following parameters:
*
* @param $joins, - `joining` clause (type, left table, right table, left column, right column, condition = EQ)
* - Either: `innerJoin()`, `leftJoin()`, `rightJoin()`, `fullJoin()`
* - Alias of: `joining(inner|left|right|full, leftTable, rightTable, leftColumn, rightColumn, equal condition)`
* @param $whereCondition, - `where` clause ( comparison(x, y, and) )
* @param $groupBy, - `groupBy` clause
* @param $having, - `having` clause ( comparison(x, y, and) )
* @param $orderby, - `orderby` clause for the query
* @param $limit, - `limit` clause the number of records
* @param $union/$unionAll - `union` clause combine the result sets and removes duplicate rows/does not remove
*
* @return mixed|object result set - see docs for more details, or false for error
*/
function selecting($columns = '*', ...$conditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->selecting($columns, ...$conditions)
: false;
}
/**
* Preforms a `insert` method call on a already preset `table name`, and optional `prefix`
*
* This function **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
*
* Does an `insert` query with an array
* @param array $keyValue - table fields, assoc array with key = value (doesn't need escaping)
* @return int|bool bool/id of inserted record, or false for error
*/
function inserting(array $keyValue)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->inserting($keyValue)
: false;
}
/**
* Preforms a `update` method call on a already preset `table name`, and optional `prefix`
*
* This function **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
*
* Does an `update` query with an array, by conditional operator array
* @param array $keyValue, - table fields, assoc array with key = value (doesn't need escaped)
* @param mixed ...$whereConditions, - where clause `eq(x, y, _AND), another clause - same as array(x, =, y, and, extra)`
* - In the following format:
*```js
* eq('key/Field/Column', $value, _AND), // combine next expression
* neq('key/Field/Column', $value, _OR), // will combine next expression if
* ne('key/Field/Column', $value), // the default is _AND so will combine next expression
* lt('key/Field/Column', $value)
* lte('key/Field/Column', $value)
* gt('key/Field/Column', $value)
* gte('key/Field/Column', $value)
* isNull('key/Field/Column')
* isNotNull('key/Field/Column')
* like('key/Field/Column', '_%')
* notLike('key/Field/Column', '_%')
* in('key/Field/Column', $values)
* notIn('key/Field/Column', $values)
* between('key/Field/Column', $value, $value2)
* notBetween('key/Field/Column', $value, $value2)
*```
* @return mixed bool/results - false for error
*/
function updating(array $keyValue, ...$whereConditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->updating($keyValue, ...$whereConditions)
: false;
}
/**
* Preforms a `create` method call on a already preset `table name`, and optional `prefix`
*
* This function **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
*
* Creates an database table with columns, by either:
*```js
* - array( column, datatype, ...value/options arguments ) // calls create_schema()
* - column( column, datatype, ...value/options arguments ) // returns string
* - primary( primary_key_label, ...primaryKeys) // returns string
* - foreign( foreign_key_label, ...foreignKeys) // returns string
* - unique( unique_key_label, ...uniqueKeys) // returns string
*```
* @param array ...$schemas An array of:
*
* - @param string `$column | CONSTRAINT,` - column name/CONSTRAINT usage for PRIMARY|FOREIGN KEY
* - @param string `$type | $constraintName,` - data type for column/primary | foreign constraint name
* - @param mixed `$size | ...$primaryForeignKeys,`
* - @param mixed `$value,` - column should be NULL or NOT NULL. If omitted, assumes NULL
* - @param mixed `$default` - Optional. It is the value to assign to the column
*
* @return mixed results of query() call
*/
function creating(...$schemas)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->creating(...$schemas)
: false;
}
/**
* Preforms a `delete` method call on a already preset `table name`, and optional `prefix`
*
* This function **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
*
* Does an `delete` query with an array
* @param $table, - database table to access
* @param $whereConditions, - where clause `eq(x, y, _AND), another clause - same as array(x, =, y, and, extra)`
* - In the following format:
*```js
* eq('key/Field/Column', $value, _AND), // combine next expression
* neq('key/Field/Column', $value, _OR), // will combine next expression if
* ne('key/Field/Column', $value), // the default is _AND so will combine next expression
* lt('key/Field/Column', $value)
* lte('key/Field/Column', $value)
* gt('key/Field/Column', $value)
* gte('key/Field/Column', $value)
* isNull('key/Field/Column')
* isNotNull('key/Field/Column')
* like('key/Field/Column', '_%')
* notLike('key/Field/Column', '_%')
* in('key/Field/Column', $values)
* notIn('key/Field/Column', $values)
* between('key/Field/Column', $value, $value2)
* notBetween('key/Field/Column', $value, $value2)
*```
* @return mixed bool/results - false for error
*/
function deleting(...$whereConditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->deleting(...$whereConditions)
: false;
}
/**
* Preforms a `replace` method call on a already preset `table name`, and optional `prefix`
*
* This function **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
*
* Does an `replace` query with an array
* @param array $keyValue - table fields, assoc array with key = value (doesn't need escaping)
* @return mixed bool/id of replaced record, or false for error
*/
function replacing(array $keyValue)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->replacing($keyValue)
: false;
}
/**
* Preforms a `drop` method call on a already preset `table name`, and optional `prefix`
*
* This function **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
*
* Does an `drop` table query if table exists.
*
* @return bool|int
*/
function dropping()
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->dropping()
: false;
}
/**
* Preforms a `alter` method call on a already preset `table name`, and optional `prefix`
*
* This method **expects** either `table_setup(name, prefix)`, `set_table(name)`, or `set_prefix(append)`
* to have been called **before usage**, otherwise will return `false`, if no `table name` previous stored.
*
* Modify columns in an existing database table, by either:
*```js
* - array( column_name, datatype, ...value/options arguments ) // calls create_schema()
* - addColumn( column_name, datatype, ...value/options arguments ) // returns string
* - dropColumn( column_name ) // returns string
* - changingColumn( column_name, datatype, ...value/options arguments ) // returns string
*```
* @param array ...$alteringSchema An array of:
*
* - @param string `$name,` - column name
* - @param string `$type,` - data type for the column
* - @param mixed `$size,` | `$value,`
* - @param mixed `...$anyOtherArgs`
*
* @return mixed results of query() call
*/
function altering(...$alteringSchema)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->altering(...$alteringSchema)
: false;
}
/**
* Set table `name` and `prefix` for usage on calls to database `CRUD`
* **method/function** *names* ending with `ing`.
*
* @param string $name
* @param string $prefix
*/
function table_setup(string $name = '', string $prefix = '')
{
$ezQuery = getInstance();
if (!$ezQuery instanceof ezsqlModelInterface)
return false;
$ezQuery->tableSetup($name, $prefix);
}
/**
* Set table `name` to use on calls to database `CRUD` **method/function** *names* ending with `ing`.
*
* @param string $name
*/
function set_table(string $name = '')
{
$ezQuery = getInstance();
if (!$ezQuery instanceof ezsqlModelInterface)
return false;
$ezQuery->setTable($name);
}
/**
* Add a `prefix` to **append** to `table` name on calls to database `CRUD`
* **method/function** *names* ending with `ing`.
*
* @param string $append
*/
function set_prefix(string $append = '')
{
$ezQuery = getInstance();
if (!$ezQuery instanceof ezsqlModelInterface)
return false;
$ezQuery->setPrefix($append);
}
/**
* Does an `select into` statement by calling `select` method
* @param string $newTable, - new database table to be created
* @param mixed $fromColumns - the columns from old database table
* @param string $oldTable - old database table
* @param mixed $fromWhereConditions, - where clause `eq(x, y, _AND), another clause - same as array(x, =, y, and, extra)`
* - In the following format:
*```js
* eq('key/Field/Column', $value, _AND), // combine next expression
* neq('key/Field/Column', $value, _OR), // will combine next expression if
* ne('key/Field/Column', $value), // the default is _AND so will combine next expression
* lt('key/Field/Column', $value)
* lte('key/Field/Column', $value)
* gt('key/Field/Column', $value)
* gte('key/Field/Column', $value)
* isNull('key/Field/Column')
* isNotNull('key/Field/Column')
* like('key/Field/Column', '_%')
* notLike('key/Field/Column', '_%')
* in('key/Field/Column', $values)
* notIn('key/Field/Column', $values)
* between('key/Field/Column', $value, $value2)
* notBetween('key/Field/Column', $value, $value2)
*```
* @return mixed bool/result - false for error
* @codeCoverageIgnore
*/
function select_into(string $newTable, $fromColumns = '*', string $oldTable = null, ...$fromWhereConditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->select_into($newTable, $fromColumns, $oldTable, ...$fromWhereConditions)
: false;
}
/**
* Does an `insert into select` statement by calling insert method helper then `select` method
* @param $toTable, - database table to insert table into
* @param $toColumns - the receiving columns from other table columns, leave blank for all or array of column fields
* @param $fromTable, - from database table to use
* @param $fromColumns - the columns from old database table
* @param $whereConditions, - where clause `eq(x, y, _AND), another clause - same as array(x, =, y, and, extra)`
*
* @return mixed bool/id of inserted record, or false for error
*/
function insert_select($totable = '', $toColumns = '*', $fromTable = null, $fromColumns = '*', ...$whereConditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->insert_select($totable, $toColumns, $fromTable, $fromColumns, ...$whereConditions)
: false;
}
/**
* Does an `create select` statement by calling `select` method
*
* @param $newTable, - new database table to be created
* @param $fromColumns - the columns from old database table
* @param $oldTable - old database table
* @param $fromWhereConditions, - where clause `eq(x, y, _AND), another clause - same as array(x, =, y, and, extra)`
*
* @return mixed bool/result - false for error
*/
function create_select($newTable, $fromColumns = '*', $oldTable = null, ...$fromWhereConditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->create_select($newTable, $fromColumns, $oldTable, ...$fromWhereConditions)
: false;
}
/**
* Returns an `WHERE` **sql clause** string.
*
* format:
* `where( comparison(x, y, and) )`
*
* example:
* `where( eq(key, value ), like('key', '_%?');`
*
* @param array $conditions - In the following format:
*```js
* eq('key/Field/Column', $value, _AND), // combine next expression
* neq('key/Field/Column', $value, _OR), // will combine next expression if
* ne('key/Field/Column', $value), // the default is _AND so will combine next expression
* lt('key/Field/Column', $value)
* lte('key/Field/Column', $value)
* gt('key/Field/Column', $value)
* gte('key/Field/Column', $value)
* isNull('key/Field/Column')
* isNotNull('key/Field/Column')
* like('key/Field/Column', '_%')
* notLike('key/Field/Column', '_%')
* in('key/Field/Column', $values)
* notIn('key/Field/Column', $values)
* between('key/Field/Column', $value, $value2)
* notBetween('key/Field/Column', $value, $value2)
*```
* @return mixed bool/string - WHERE sql statement, or false on error
*/
function where(...$conditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->where(...$conditions)
: false;
}
/**
* Adds WHERE `grouping` to the conditions
*
* format:
* `grouping( comparison(x, y, and) )`
*
* example:
* `grouping( eq(key, value, combiner ), eq(key, value, combiner ) );`
*
* @param array $conditions - In the following format:
*```js
* eq('key/Field/Column', $value, _AND), // combine next expression
* neq('key/Field/Column', $value, _OR), // will combine next expression again
* ne('key/Field/Column', $value), // the default is _AND so will combine next expression
* lt('key/Field/Column', $value)
* lte('key/Field/Column', $value)
* gt('key/Field/Column', $value)
* gte('key/Field/Column', $value)
* isNull('key/Field/Column')
* isNotNull('key/Field/Column')
* like('key/Field/Column', '_%')
* notLike('key/Field/Column', '_%')
*```
* @return array modified conditions
*/
function grouping(...$conditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->grouping(...$conditions)
: false;
}
/**
* Specifies a grouping over the results of the query.
*<code>
* selecting('table',
* 'columns',
* where( eq( 'columns', values, _AND ), like( 'columns', _d ) ),
* groupBy( 'columns' ),
* having( between( 'columns', values1, values2 ) ),
* orderBy( 'columns', 'desc' );
*</code>
* @param string|array $column The grouping expression.
*
* @return string - GROUP BY SQL statement, or false on error
*/
function groupBy($column)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->groupBy($column)
: false;
}
/**
* Specifies a `restriction` over the groups of the query.
*
* format
* `having( array(x, =, y, and, extra) );` or
* `having( "x = y and extra" );`
*
* example:
* `having( array(key, operator, value, combine, extra) );`or
* `having( "key operator value combine extra" );`
*
* @param array $conditions - In the following format:
*```js
* eq('key/Field/Column', $value, _AND), // combine next expression
* neq('key/Field/Column', $value, _OR), // will combine next expression if
* ne('key/Field/Column', $value), // the default is _AND so will combine next expression
* lt('key/Field/Column', $value)
* lte('key/Field/Column', $value)
* gt('key/Field/Column', $value)
* gte('key/Field/Column', $value)
* isNull('key/Field/Column')
* isNotNull('key/Field/Column')
* like('key/Field/Column', '_%')
* notLike('key/Field/Column', '_%')
* in('key/Field/Column', $values)
* notIn('key/Field/Column', $values)
* between('key/Field/Column', $value, $value2)
* notBetween('key/Field/Column', $value, $value2)
*```
* @return bool/string - HAVING SQL statement, or false on error
*/
function having(...$conditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->having(...$conditions)
: false;
}
/**
* Return all rows from multiple tables where the join condition is met.
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $tableAs -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
function innerJoin(
$leftTable = '',
$rightTable = '',
$leftColumn = null,
$rightColumn = null,
$tableAs = null,
$condition = \EQ
) {
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->innerJoin($leftTable, $rightTable, $leftColumn, $rightColumn, $tableAs, $condition)
: false;
}
/**
* This type of join returns all rows from the LEFT-hand table
* specified in the ON condition and only those rows from the other table
* where the joined fields are equal (join condition is met).
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $tableAs -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
function leftJoin(
$leftTable = '',
$rightTable = '',
$leftColumn = null,
$rightColumn = null,
$tableAs = null,
$condition = \EQ
) {
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->leftJoin($leftTable, $rightTable, $leftColumn, $rightColumn, $tableAs, $condition)
: false;
}
/**
* This type of join returns all rows from the RIGHT-hand table
* specified in the ON condition and only those rows from the other table
* where the joined fields are equal (join condition is met).
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $tableAs -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
function rightJoin(
$leftTable = '',
$rightTable = '',
$leftColumn = null,
$rightColumn = null,
$tableAs = null,
$condition = \EQ
) {
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->rightJoin($leftTable, $rightTable, $leftColumn, $rightColumn, $tableAs, $condition)
: false;
}
/**
* This type of join returns all rows from the LEFT-hand table and RIGHT-hand table
* with NULL values in place where the join condition is not met.
*
* - Will perform an equal on tables by left column key,
* left column key and left table, left column key and right table,
* if `rightColumn` is null.
*
* - Will perform an equal on tables by,
* left column key and left table, right column key and right table,
* if `rightColumn` not null, and `$condition` not changed.
*
* - Will perform the `condition` on passed in arguments, for left column, and right column.
* if `$condition`, is in the array
*
* @param string $leftTable -
* @param string $rightTable -
* @param string $leftColumn -
* @param string $rightColumn -
* @param string $tableAs -
* @param string $condition -
*
* @return bool|string JOIN sql statement, false for error
*/
function fullJoin(
$leftTable = '',
$rightTable = '',
$leftColumn = null,
$rightColumn = null,
$tableAs = null,
$condition = \EQ
) {
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->fullJoin($leftTable, $rightTable, $leftColumn, $rightColumn, $tableAs, $condition)
: false;
}
/**
* Returns an `UNION` SELECT `SQL` string, given the
* - table, column fields, conditions or conditional array.
*
* In the following format:
* ```php
* union(
* table,
* columns,
* innerJoin() | leftJoin() | rightJoin() | fullJoin(), // alias of joining(inner|left|right|full, leftTable, rightTable, leftColumn, rightColumn, equal condition),
* where( eq( columns, values, _AND ), like( columns, _d ) ),
* groupBy( columns ),
* having( between( columns, values1, values2 ) ),
* orderBy( columns, desc ),
* limit( numberOfRecords, offset )
*);
* ```
* @param $table, - database table to access
* @param $columnFields, - table columns, string or array
* @param mixed $conditions - same as selecting method.
*
* @return bool|string - false for error
*/
function union($table = '', $columnFields = '*', ...$conditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->union($table, $columnFields, ...$conditions)
: false;
}
/**
* Returns an `UNION ALL` SELECT `SQL` string, given the
* - table, column fields, conditions or conditional array.
*
* In the following format:
* ```php
* unionAll(
* table,
* columns,
* innerJoin() | leftJoin() | rightJoin() | fullJoin(), // alias of joining(inner|left|right|full, leftTable, rightTable, leftColumn, rightColumn, equal condition),
* where( eq( columns, values, _AND ), like( columns, _d ) ),
* groupBy( columns ),
* having( between( columns, values1, values2 ) ),
* orderBy( columns, desc ),
* limit( numberOfRecords, offset )
*);
* ```
* @param $table, - database table to access
* @param $columnFields, - table columns, string or array
* @param mixed $conditions - same as `select` method.
*
* @return bool|string - false for error
*/
function unionAll($table = '', $columnFields = '*', ...$conditions)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->unionAll($table, $columnFields, ...$conditions)
: false;
}
/**
* Specifies an ordering for the query results.
* @param string|array $column - Which columns to use for ordering.
* @param string $order - The ordering direction, either `ASC`|`DESC`.
*
* @return string - ORDER BY SQL statement, or false on error
*/
function orderBy($column, $order)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->orderBy($column, $order)
: false;
}
/**
* Specifies records from one or more tables in a database and
* limit the number of records returned.
*
* @param int $numberOf - set limit number of records to be returned.
* @param int $offset - Optional. The first row returned by LIMIT will be determined by offset value.
*
* @return string - LIMIT and/or OFFSET SQL statement, or false on error
*/
function limit($numberOf, $offset = null)
{
$ezQuery = getInstance();
return ($ezQuery instanceof DatabaseInterface)
? $ezQuery->limit($numberOf, $offset)
: false;
}
/**
* @codeCoverageIgnore
*/
function ezFunctions()
{
return true;
}
}