453 lines
No EOL
16 KiB
PHP
453 lines
No EOL
16 KiB
PHP
<?php
|
|
|
|
/**
|
|
* Wrapper for WPDB (WordPress DB Class)
|
|
*
|
|
* Thic class wrap the access to WordPress DB class ($wpdb) and
|
|
* allows us to abstract from the WordPress code and to expand it
|
|
* with convenience method specific for ai1ec
|
|
*
|
|
* @author Time.ly Network, Inc.
|
|
* @since 2.0
|
|
* @package Ai1EC
|
|
* @subpackage Ai1EC.Dbi
|
|
*/
|
|
class Ai1ec_Dbi {
|
|
|
|
/**
|
|
* @var Ai1ec_Registry_Object Instance of object registry.
|
|
*/
|
|
protected $_registry = null;
|
|
|
|
/**
|
|
* @var wpdb Instance of database interface object
|
|
*/
|
|
protected $_dbi = null;
|
|
|
|
/**
|
|
* @var array Queries executed for log.
|
|
*/
|
|
protected $_queries = array();
|
|
|
|
/**
|
|
* @var bool Set to true when logging is enabled.
|
|
*/
|
|
protected $_log_enabled = false;
|
|
|
|
/**
|
|
* Constructor assigns injected database access object to class variable.
|
|
*
|
|
* @param Ai1ec_Registry_Object $registry Injected registry.
|
|
* @param wpdb $dbi Injected database access object.
|
|
*
|
|
* @return void Constructor does not return.
|
|
*/
|
|
public function __construct(
|
|
Ai1ec_Registry_Object $registry,
|
|
$dbi = null
|
|
) {
|
|
if ( null === $dbi ) {
|
|
global $wpdb;
|
|
$dbi = $wpdb;
|
|
}
|
|
$this->_dbi = $dbi;
|
|
$this->_registry = $registry;
|
|
$this->_registry->get( 'controller.shutdown' )->register(
|
|
array( $this, 'shutdown' )
|
|
);
|
|
add_action(
|
|
'ai1ec_loaded',
|
|
array( $this, 'check_debug' ),
|
|
PHP_INT_MAX
|
|
);
|
|
$this->set_timezone();
|
|
}
|
|
|
|
/**
|
|
* Set timezone to UTC to avoid conversion errors.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function set_timezone() {
|
|
$this->_dbi->query( "SET time_zone = '+0:00'" );
|
|
}
|
|
|
|
/**
|
|
* Call explicitly when debug output must be disabled.
|
|
*
|
|
* @return void Method is not meant to return.
|
|
*/
|
|
public function disable_debug() {
|
|
$this->_log_enabled = false;
|
|
}
|
|
|
|
/**
|
|
* Only attempt to enable debug after all add-ons are loaded.
|
|
*
|
|
* @wp_hook ai1ec_loaded
|
|
*
|
|
* @uses apply_filters ai1ec_dbi_debug
|
|
*
|
|
* @return void
|
|
*/
|
|
public function check_debug() {
|
|
$this->_log_enabled = apply_filters(
|
|
'ai1ec_dbi_debug',
|
|
( false !== AI1EC_DEBUG )
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Perform a MySQL database query, using current database connection.
|
|
*
|
|
* @param string $sql_query Database query
|
|
*
|
|
* @return int|false Number of rows affected/selected or false on error
|
|
*/
|
|
public function query( $sql_query ) {
|
|
$this->_query_profile( $sql_query );
|
|
$result = $this->_dbi->query( $sql_query );
|
|
$this->_query_profile( $result );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Retrieve one column from the database.
|
|
*
|
|
* Executes a SQL query and returns the column from the SQL result.
|
|
* If the SQL result contains more than one column, this function returns the column specified.
|
|
* If $query is null, this function returns the specified column from the previous SQL result.
|
|
*
|
|
* @param string|null $query Optional. SQL query. Defaults to previous query.
|
|
* @param int $col Optional. Column to return. Indexed from 0.
|
|
*
|
|
* @return array Database query result. Array indexed from 0 by SQL result row number.
|
|
*/
|
|
public function get_col( $query = null , $col = 0 ) {
|
|
$this->_query_profile( $query );
|
|
$result = $this->_dbi->get_col( $query, $col );
|
|
$this->_query_profile( count( $result ) );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Check if the terms variable is set in the Wpdb object
|
|
*/
|
|
public function are_terms_set() {
|
|
return isset( $this->_dbi->terms );
|
|
}
|
|
|
|
/**
|
|
* Prepares a SQL query for safe execution. Uses sprintf()-like syntax.
|
|
*
|
|
* The following directives can be used in the query format string:
|
|
* %d (integer)
|
|
* %f (float)
|
|
* %s (string)
|
|
* %% (literal percentage sign - no argument needed)
|
|
*
|
|
* All of %d, %f, and %s are to be left unquoted in the query string and they need an argument passed for them.
|
|
* Literals (%) as parts of the query must be properly written as %%.
|
|
*
|
|
* This function only supports a small subset of the sprintf syntax; it only supports %d (integer), %f (float), and %s (string).
|
|
* Does not support sign, padding, alignment, width or precision specifiers.
|
|
* Does not support argument numbering/swapping.
|
|
*
|
|
* May be called like {@link http://php.net/sprintf sprintf()} or like {@link http://php.net/vsprintf vsprintf()}.
|
|
*
|
|
* Both %d and %s should be left unquoted in the query string.
|
|
*
|
|
* @param string $query Query statement with sprintf()-like placeholders
|
|
* @param array|mixed $args The array of variables to substitute into the query's placeholders if being called like
|
|
* {@link http://php.net/vsprintf vsprintf()}, or the first variable to substitute into the query's placeholders if
|
|
* being called like {@link http://php.net/sprintf sprintf()}.
|
|
* @param mixed $args,... further variables to substitute into the query's placeholders if being called like
|
|
* {@link http://php.net/sprintf sprintf()}.
|
|
*
|
|
* @return null|false|string Sanitized query string, null if there is no query, false if there is an error and string
|
|
* if there was something to prepare
|
|
*/
|
|
public function prepare( $query, $args ) {
|
|
|
|
if ( null === $query ) {
|
|
return null;
|
|
}
|
|
|
|
$args = func_get_args();
|
|
array_shift( $args );
|
|
// If args were passed as an array (as in vsprintf), move them up
|
|
if ( isset( $args[0] ) && is_array( $args[0] ) ) {
|
|
$args = $args[0];
|
|
}
|
|
$query = str_replace( "'%s'", '%s', $query ); // in case someone mistakenly already singlequoted it
|
|
$query = str_replace( '"%s"', '%s', $query ); // doublequote unquoting
|
|
$query = preg_replace( '|(?<!%)%f|', '%F', $query ); // Force floats to be locale unaware
|
|
$query = preg_replace( '|(?<!%)%s|', "'%s'", $query ); // quote the strings, avoiding escaped strings like %%s
|
|
array_walk( $args, array( $this->_dbi, 'escape_by_ref' ) );
|
|
return @vsprintf( $query, $args );
|
|
}
|
|
|
|
/**
|
|
* Retrieve an entire SQL result set from the database (i.e., many rows)
|
|
*
|
|
* Executes a SQL query and returns the entire SQL result.
|
|
*
|
|
* @param string $query SQL query.
|
|
* @param string $output Optional. Any of ARRAY_A | ARRAY_N | OBJECT | OBJECT_K constants. With one of the first three, return an array of rows indexed from 0 by SQL result row number.
|
|
* Each row is an associative array (column => value, ...), a numerically indexed array (0 => value, ...), or an object. ( ->column = value ), respectively.
|
|
* With OBJECT_K, return an associative array of row objects keyed by the value of each row's first column's value. Duplicate keys are discarded.
|
|
*
|
|
* @return mixed Database query results
|
|
*/
|
|
public function get_results( $query, $output = OBJECT ){
|
|
$this->_query_profile( $query );
|
|
$result = $this->_dbi->get_results( $query, $output );
|
|
$this->_query_profile( count( $result ) );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Retrieve one variable from the database.
|
|
*
|
|
* Executes a SQL query and returns the value from the SQL result.
|
|
* If the SQL result contains more than one column and/or more than one row, this function returns the value in the column and row specified.
|
|
* If $query is null, this function returns the value in the specified column and row from the previous SQL result.
|
|
*
|
|
* @param string|null $query SQL query. Defaults to null, use the result from the previous query.
|
|
* @param int $col Column of value to return. Indexed from 0.
|
|
* @param int $row Row of value to return. Indexed from 0.
|
|
*
|
|
* @return string|null Database query result (as string), or null on failure
|
|
*/
|
|
public function get_var( $query = null, $col = 0, $row = 0 ) {
|
|
$this->_query_profile( $query );
|
|
$result = $this->_dbi->get_var( $query, $col, $row );
|
|
$this->_query_profile( null !== $result );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Retrieve one row from the database.
|
|
*
|
|
* Executes a SQL query and returns the row from the SQL result
|
|
*
|
|
* @param string|null $query SQL query.
|
|
* @param string $output Optional. one of ARRAY_A | ARRAY_N | OBJECT constants. Return an associative array (column => value, ...),
|
|
* a numerically indexed array (0 => value, ...) or an object ( ->column = value ), respectively.
|
|
* @param int $row Optional. Row to return. Indexed from 0.
|
|
*
|
|
* @return mixed Database query result in format specified by $output or null on failure
|
|
*/
|
|
public function get_row( $query = null, $output = OBJECT, $row = 0 ) {
|
|
$this->_query_profile( $query );
|
|
$result = $this->_dbi->get_row( $query, $output, $row );
|
|
$this->_query_profile( null !== $result );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Insert a row into a table.
|
|
*
|
|
* @param string $table table name
|
|
* @param array $data Data to insert (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).
|
|
* @param array|string $format Optional. An array of formats to be mapped to each of the value in $data. If string, that format will be used for all of the values in $data.
|
|
* A format is one of '%d', '%f', '%s' (integer, float, string). If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.
|
|
*
|
|
* @return int|false The number of rows inserted, or false on error.
|
|
*/
|
|
public function insert( $table, $data, $format = null ) {
|
|
$this->_query_profile(
|
|
'INSERT INTO ' . $table . '; data: ' . json_encode( $data )
|
|
);
|
|
$result = $this->_dbi->insert(
|
|
$this->get_table_name( $table ),
|
|
$data,
|
|
$format
|
|
);
|
|
$this->_query_profile( $result );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Perform removal from table.
|
|
*
|
|
* @param string $table Table to remove from.
|
|
* @param array $where Where conditions
|
|
* @param array $format Format entities for where.
|
|
*
|
|
* @return int|false Number of rows deleted or false.
|
|
*/
|
|
public function delete( $table, $where, $format = null ) {
|
|
$this->_query_profile(
|
|
'DELETE FROM ' . $table . '; conditions: ' . json_encode( $where )
|
|
);
|
|
$result = $this->_dbi->delete(
|
|
$this->get_table_name( $table ),
|
|
$where,
|
|
$format
|
|
);
|
|
$this->_query_profile( $result );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Update a row in the table
|
|
*
|
|
* @param string $table table name
|
|
* @param array $data Data to update (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).
|
|
* @param array $where A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
|
|
* @param array|string $format Optional. An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data.
|
|
* A format is one of '%d', '%f', '%s' (integer, float, string). If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.
|
|
* @param array|string $where_format Optional. An array of formats to be mapped to each of the values in $where. If string, that format will be used for all of the items in $where. A format is one of '%d', '%f', '%s' (integer, float, string). If omitted, all values in $where will be treated as strings.
|
|
*
|
|
* @return int|false The number of rows updated, or false on error.
|
|
*/
|
|
public function update( $table, $data, $where, $format = null, $where_format = null ) {
|
|
$this->_query_profile( 'UPDATE ' . $table . ': ' . implode( '//', $data ) );
|
|
$result = $this->_dbi->update( $table, $data, $where, $format, $where_format );
|
|
$this->_query_profile( $result );
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Retrieve all results from given table.
|
|
*
|
|
* @param string $table Name of table.
|
|
* @param array $columns List of columns to retrieve.
|
|
* @param string $output See {@see self::get_results()} $output for more.
|
|
*
|
|
* @return array Collection.
|
|
*/
|
|
public function select( $table, array $columns, $output = OBJECT ) {
|
|
$sql_query = 'SELECT `' . implode( '`, `', $columns ) . '` FROM `' .
|
|
$this->get_table_name( $table ) . '`';
|
|
return $this->get_results( $sql_query, $output );
|
|
}
|
|
|
|
/**
|
|
* The database version number.
|
|
*
|
|
* @return false|string false on failure, version number on success
|
|
*/
|
|
public function db_version() {
|
|
return $this->_dbi->db_version();
|
|
}
|
|
|
|
/**
|
|
* Return the id of last `insert` operation.
|
|
*
|
|
* @return int Returns integer optionally zero when no insert was performed.
|
|
*/
|
|
public function get_insert_id() {
|
|
return $this->_dbi->insert_id;
|
|
}
|
|
|
|
/**
|
|
* Return the full name for the table.
|
|
*
|
|
* @param string $table Table name.
|
|
*
|
|
* @return string Full table name for the table requested.
|
|
*/
|
|
public function get_table_name( $table = '' ) {
|
|
static $prefix_len = null;
|
|
if ( ! isset( $this->_dbi->{$table} ) ) {
|
|
if ( null === $prefix_len ) {
|
|
$prefix_len = strlen( $this->_dbi->prefix );
|
|
}
|
|
if ( 0 === strncmp( $this->_dbi->prefix, $table, $prefix_len ) ) {
|
|
return $table;
|
|
}
|
|
return $this->_dbi->prefix . $table;
|
|
}
|
|
return $this->_dbi->{$table};
|
|
}
|
|
|
|
/**
|
|
* Return escaped value.
|
|
*
|
|
* @param string $input Value to be escaped.
|
|
*
|
|
* @return string Escaped value.
|
|
*/
|
|
public function escape( $input ) {
|
|
$this->_dbi->escape_by_ref( $input );
|
|
return $input;
|
|
}
|
|
|
|
/**
|
|
* In debug mode prints DB queries table.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function shutdown() {
|
|
if ( ! $this->_log_enabled ) {
|
|
return false;
|
|
}
|
|
echo '<div class="timely timely-debug">
|
|
<table class="table table-striped">
|
|
<thead>
|
|
<tr>
|
|
<th>N.</th>
|
|
<th>Query</th>
|
|
<th>Duration, ms</th>
|
|
<th>Row Count</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>';
|
|
$i = 0;
|
|
$time = 0;
|
|
foreach ( $this->_queries as $query ) {
|
|
$time += $query['d'];
|
|
echo '<tr>
|
|
<td>', ++$i, '</td>
|
|
<td>', $query['q'], '</td>
|
|
<td>', round( $query['d'] * 1000, 2 ), '</td>
|
|
<td>', (int)$query['r'], '</td>
|
|
</tr>';
|
|
}
|
|
echo '
|
|
</tbody>
|
|
<tfoot>
|
|
<tr>
|
|
<th colspan="4">Total time, ms: ',
|
|
round( $time * 1000, 2 ), '</th>
|
|
</tr>
|
|
</tfoot>
|
|
</table>
|
|
</div>';
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Method aiding query profiling.
|
|
*
|
|
* How to use:
|
|
* - on method resulting in query start call _query_profiler( 'SQL query' )
|
|
* - on it's end call _query_profiler( (int)number_of_rows|(bool)false )
|
|
*
|
|
* @param mixed $query_or_result Query on first call, result on second.
|
|
*
|
|
* @return void
|
|
*/
|
|
protected function _query_profile( $query_or_result ) {
|
|
static $last = null;
|
|
if ( null === $last ) {
|
|
$last = array(
|
|
'd' => microtime( true ),
|
|
'q' => $query_or_result,
|
|
);
|
|
} else {
|
|
if ( count( $this->_queries ) > 200 ) {
|
|
array_shift( $this->_queries );
|
|
}
|
|
$this->_queries[] = array(
|
|
'd' => microtime( true ) - $last['d'],
|
|
'q' => $last['q'],
|
|
'r' => $query_or_result,
|
|
);
|
|
$last = null;
|
|
}
|
|
}
|
|
|
|
} |