diff --git a/class-DB.php b/class-DB.php index 93ce615..6fe42a3 100644 --- a/class-DB.php +++ b/class-DB.php @@ -1,462 +1,467 @@ . /* * Use: esc_html(), error_die(), DEBUG, force_array() */ /** - * Database class + * 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 ); error_die( "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( !$this->last ) { error_die( $this->getQueryErrorMessage( $query ) ); } elseif( DEBUG_QUERIES ) { error( "query n. {$this->queries}: $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. * @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(); } /** - * To insert a single row. - * I have not time to check if $dbCols are DBCol objects. + * Run an INSERT query for a single row * - * @param string $table_name + * @param string $table * @param DBCol[] $cols * @param $args arguments like 'replace-into */ - public function insertRow( $table_name, $cols, $args = [] ) { - $SQL_columns = ''; - $n = count($cols); - for($i=0; $i<$n; $i++) { - if($i !== 0) { - $SQL_columns .= ', '; - } - $SQL_columns .= "`{$cols[$i]->column}`"; + public function insertRow( $table, $cols, $args = [] ) { + // build column names + $columns = []; + foreach( $cols as $col ) { + $columns[] = "`{$col->column}`"; } - $values = ''; - for($i=0; $i<$n; $i++) { - if($i !== 0) { - $values .= ', '; - } - $values .= $this->forceType($cols[$i]->value, $cols[$i]->forceType); + // 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'; - return $this->query("$what INTO {$this->getTable($table_name)} ($SQL_columns) VALUES ($values)"); + $table = $this->getTable( $table ); + + $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 ) { return $this->mysqli->multi_query( $queries ); } /** - * To execute clean insert SQL queries. + * Run an INSERT query for multiple rows * - * @param string $table_name Table Name without prefix + * @param string $table Table Name without prefix * @param array $columns Assoc array of types ('ID' => 'null', 'name' => 's', ..) - * @param array $rows Array of rows + * @param array $rows Array of rows (or just a row) * @param array $args Extra arguments */ - public function insert($table_name, $columns, $rows, $args = []) { + public function insert( $table, $columns, $rows, $args = [] ) { + + // default arguments $args = array_replace( [ - 'replace-into' => false + 'replace-into' => false, ], $args ); - force_array($rows); - - if( ! @is_array($rows[0]) ) { - $rows = [ $rows ]; // array_columns('value col 1', '..') => array_rows( array_columns( 'value col 1', ..) ) + // backticked column names + $column_names = []; + foreach( $columns as $column => $type ) { + $column_names[] = "`$column`"; } + $columns_comma = implode( ', ', $column_names ); - $n_columns = count($columns); - $n_rows = count($rows); - - $SQL_columns = ''; - - $first = key($columns); - foreach($columns as $column => $type) { - if($column !== $first) { - $SQL_columns .= ', '; - } - $SQL_columns .= "`$column`"; + // backward compatibility + force_array( $rows ); + if( ! @is_array( $rows[0] ) ) { + $rows = [$rows]; } - $insert = ($args['replace-into']) ? 'REPLACE' : 'INSERT'; + // just the types (in order to be indexed numerically) + $types = array_values( $columns ); + $n_columns = count( $types ); - $SQL = "$insert INTO {$this->getTable($table_name)} ($SQL_columns) VALUES"; - for($i=0; $i<$n_rows; $i++) { - $SQL .= ($i === 0) ? ' (' : ', ('; - $SQL_values = []; + $value_groups = []; + foreach( $rows as $i => $row ) { + $query_values = []; - if($n_columns != count($rows[$i])) { - error( sprintf( - __("Errore inserendo nella tabella %s. Colonne: %d. Colonne values[%d]: %d"), - esc_html($table_name), + if( $n_columns !== count( $row ) ) { + error_die( sprintf( + "error using insert() in table %s: %d columns but %d values in row %d", + $table, $n_columns, - $i, - count($rows[$i]) + count( $row ), + $i ) ); - return false; } - $j = 0; - foreach($columns as $column => $type) { - $SQL_values[] = $this->forceType($rows[$i][$j], $type); - $j++; + $values_escaped = []; + foreach( $types as $j => $type ) { + $values_escaped[] = $this->forceType( $row[ $j ], $type ); } - $SQL .= implode(', ', $SQL_values) . ')'; + $values_grouped = implode( ', ', $values_escaped ); + $value_groups[] = "($values_grouped)"; } - return $this->query($SQL); + + $value_groups_comma = implode( ', ', $value_groups ); + + $action = $args['replace-into'] ? 'REPLACE' : 'INSERT'; + + $table = $this->getTable( $table ); + + return $this->query( "$action INTO $table ($columns_comma) VALUES $value_groups_comma" ); } /** - * To execute update queries. + * 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 $after */ - public function update($table_name, $dbCols, $conditions, $after = '') { - force_array($dbCols); - - $SQL = "UPDATE {$this->getTable($table_name)} SET "; - $n_cols = count($dbCols); - for($i=0; $i<$n_cols; $i++) { - if($i !== 0) { - $SQL .= ', '; - } - $val = $this->forceType($dbCols[$i]->value, $dbCols[$i]->forceType); - $SQL .= "`{$dbCols[$i]->column}` = $val"; - } - if($after !== '') { - $after = " $after"; + public function update( $table, $columns, $conditions, $after = '' ) { + + // backward compatibility + force_array( $columns ); + + $table = $this->getTable( $table ); + + $sets = []; + foreach( $columns as $column ) { + $name = $column->column; + $value = $this->forceType( $column->value, $column->forceType ); + $sets[] = "`$name` = $value"; } - $SQL .= " WHERE {$conditions}{$after}"; - return $this->query($SQL); + + $sets_comma = implode( ', ', $sets ); + return $this->query( "UPDATE $table SET $sets_comma WHERE $conditions $after" ); } /** * Get the last inserted ID * * @return string */ public function getLastInsertedID() { return $this->mysqli->insert_id; } /** * 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); + public function escapeString( $s ) { + return $this->mysqli->real_escape_string( $s ); } public function getPrefix() { return $this->prefix; } /** - * Return a table name with it's school prefix + * Get table name with it's prefix (if any) * * @param string $name Table name - * @param boolean $as True if you want to access to the table $name in the SQL + * @param boolean $as True if you want to create an alias without the table alias * @return string Table $name with the prefix */ - public function getTable($name, $as = false) { - if($this->prefix === '') { + public function getTable( $name, $as = false ) { + if( $this->prefix === '' ) { $as = false; } - $r = "`{$this->prefix}$name`"; - if($as) { - $r .= " AS `$name`"; + $s = "`{$this->prefix}$name`"; + if( $as ) { + $s .= " AS `$name`"; } - return $r; + return $s; } /** - * Return the list of every table name inserted as arguments or as an [] + * Get the list of every table name inserted as arguments or as an array + * + * @return string */ - public function getTables($args = []) { + 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. + * 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 */ - private function forceType($s, $type) { + private 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( sprintf( - "Tipo '%s' non concesso in DB::forceType(). Vedi la documentazione (esiste?). SarĂ usato l'escape 's'.", - esc_html($type) - ) ); - - return $this->forceType($s, 's'); + 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(); } /** * Used to show "friendly" error. * - * @param string $SQL SQL query executed during the error. - * @return string Kind message. + * @param string $query SQL query executed during the error + * @return string */ - private function getQueryErrorMessage($SQL) { + private function getQueryErrorMessage( $query ) { return sprintf( - __("Errore eseguendo una query SQL: Query n. %d:
%s
%s"), + "error executing the query n. %d |%s| error: %s", $this->queries, - $SQL, - esc_html($this->mysqli->error) + $query, + esc_html( $this->mysqli->error ) ); } }