From: <pri...@us...> - 2008-04-29 20:31:43
|
Revision: 5218 http://mantisbt.svn.sourceforge.net/mantisbt/?rev=5218&view=rev Author: prichards Date: 2008-04-29 13:30:09 -0700 (Tue, 29 Apr 2008) Log Message: ----------- documentation update + minor fix to db_query_bound + add ability to specify to ignore timezones in time formatting queries. Modified Paths: -------------- trunk/mantisbt/core/database_api.php Modified: trunk/mantisbt/core/database_api.php =================================================================== --- trunk/mantisbt/core/database_api.php 2008-04-29 06:02:42 UTC (rev 5217) +++ trunk/mantisbt/core/database_api.php 2008-04-29 20:30:09 UTC (rev 5218) @@ -21,11 +21,17 @@ # $Id$ # -------------------------------------------------------- - ### Database ### + /** + * Database + * + * This is the general interface for all database calls. + * Modifications required for database support, outside of adodb support should occur here. + * + * @package DatabaseAPI + * @uses config_api.php + * @uses gpc_api.php + */ - # This is the general interface for all database calls. - # Use this as a starting point to port to other databases - $t_core_dir = dirname( __FILE__ ).DIRECTORY_SEPARATOR; require_once( $t_core_dir . 'gpc_api.php' ); @@ -34,20 +40,41 @@ # it in include path and removing the one distributed with Mantis (see #7907). require_once( 'adodb/adodb.inc.php' ); - # An array in which all executed queries are stored. This is used for profiling + /** + * An array in which all executed queries are stored. This is used for profiling + * @global array $g_queries_array + */ $g_queries_array = array(); - # Stores whether a database connection was succesfully opened. + /** + * Stores whether a database connection was succesfully opened. + * @global bool $g_db_connected + */ $g_db_connected = false; - # Store whether to log queries ( used for show_queries_count/query list) + /** + * Store whether to log queries ( used for show_queries_count/query list) + * @global bool $g_db_log_queries + */ $g_db_log_queries = config_get_global( 'show_queries_count' ); - # set adodb fetch mode - $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; + /** + * set adodb fetch mode + * @global bool $ADODB_FETCH_MODE + */ + $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; - # -------------------- - # Make a connection to the database + /** + * Open a connection to the database. + * @param string $p_dsn Database connection string ( specified instead of other params) + * @param string $p_hostname Database server hostname + * @param string $p_username database server username + * @param string $p_password database server password + * @param string $p_database_name database name + * @param string $p_db_schema Schema name (only used if database type is DB2) + * @param bool $p_pconnect Use a Persistent connection to database + * @return bool indicating if the connection was successful + */ function db_connect( $p_dsn, $p_hostname = null, $p_username = null, $p_password = null, $p_database_name = null, $p_db_schema = null, $p_pconnect = false ) { global $g_db_connected, $g_db; @@ -92,22 +119,21 @@ return true; } - # -------------------- - # Make a persistent connection to the database - function db_pconnect( $p_dsn, $p_hostname = null, $p_username = null, $p_password = null, $p_database_name = null, $p_db_schema = null ) { - return db_connect( $p_dsn, $p_hostname, $p_username, $p_password, $p_database_name, $p_db_schema, /* $p_pconnect */ true ); - } - - # -------------------- - # Returns whether a connection to the database exists + /** + * Returns whether a connection to the database exists + * @global stores database connection state + * @return bool indicating if the a database connection has been made + */ function db_is_connected() { global $g_db_connected; return $g_db_connected; } - # -------------------- - # Checks if the database is MySQL + /** + * Checks if the database driver is MySQL + * @return bool true if mysql + */ function db_is_mysql() { $t_db_type = config_get_global( 'db_type' ); @@ -120,8 +146,10 @@ return false; } - # -------------------- - # Check is the database is PostgreSQL + /** + * Checks if the database driver is PostgreSQL + * @return bool true if postgres + */ function db_is_pgsql() { $t_db_type = config_get_global( 'db_type' ); @@ -136,8 +164,10 @@ return false; } - # -------------------- - # Check is the database is DB2 + /** + * Checks if the database driver is DB2 + * @return bool true if db2 + */ function db_is_db2() { $t_db_type = config_get_global( 'db_type' ); @@ -149,10 +179,18 @@ return false; } - # -------------------- - # execute query, requires connection to be opened - # If $p_error_on_failure is true (default) an error will be triggered - # if there is a problem executing the query. + /** + * execute query, requires connection to be opened + * An error will be triggered if there is a problem executing the query. + * @global array of previous executed queries for profiling + * @global adodb database connection object + * @global boolean indicating whether queries array is populated + * @param string $p_query Query string to execute + * @param int $p_limit Number of results to return + * @param int $p_offset offset query results for paging + * @return ADORecordSet|bool adodb result set or false if the query failed. + * @deprecated db_query_bound should be used in preference to this function. This function will likely be removed in 1.2.0 final + */ function db_query( $p_query, $p_limit = -1, $p_offset = -1 ) { global $g_queries_array, $g_db, $g_db_log_queries; @@ -193,6 +231,18 @@ } } + /** + * execute query, requires connection to be opened + * An error will be triggered if there is a problem executing the query. + * @global array of previous executed queries for profiling + * @global adodb database connection object + * @global boolean indicating whether queries array is populated + * @param string $p_query Parameterlised Query string to execute + * @param array $arr_parms Array of parameters matching $p_query + * @param int $p_limit Number of results to return + * @param int $p_offset offset query results for paging + * @return ADORecordSet|bool adodb result set or false if the query failed. + */ function db_query_bound($p_query, $arr_parms = null, $p_limit = -1, $p_offset = -1 ) { global $g_queries_array, $g_db, $g_db_log_queries; @@ -201,7 +251,7 @@ $t_db_type = config_get_global( 'db_type' ); $t_start = microtime_float(); - + $t_backtrace = debug_backtrace(); $t_caller = basename( $t_backtrace[0]['file'] ); $t_caller .= ":" . $t_backtrace[0]['line']; @@ -225,10 +275,7 @@ $t_elapsed = number_format( microtime_float() - $t_start, 4); $lastoffset = 0; $i = 1; - if ( !is_null( $arr_parms ) ) { - if ($arr_parms[0] === null) { - debug_print_backtrace(); - } + if ( !( is_null( $arr_parms ) || empty( $arr_parms ) ) ) { while (preg_match('/(\?)/', $p_query, $matches, PREG_OFFSET_CAPTURE, $lastoffset)) { if ( $i <= count($arr_parms)) { if (is_null($arr_parms[$i-1])) @@ -271,27 +318,43 @@ } } - # -------------------- + /** + * Generate a string to insert a parameter into a database query string + * @param int $p_param Integer representing ordered paramater for a future bound query. + * @return string 'wildcard' matching a paramater in correct ordered format for the current database. + */ function db_param($p_param) { global $g_db; return $g_db->Param($p_param); } - # -------------------- + /** + * Retrieve number of rows returned for a specific database query + * @param ADORecordSet $p_result Database Query Record Set to retrieve record count for. + * @return int Record Count + */ function db_num_rows( $p_result ) { global $g_db; return $p_result->RecordCount( ); } - # -------------------- + /** + * Retrieve number of rows affected by a specific database query + * @param ADORecordSet $p_result Database Query Record Set to retrieve affected rows for. + * @return int Affected Rows + */ function db_affected_rows() { global $g_db; return $g_db->Affected_Rows( ); } - # -------------------- + /** + * Retrieve the next row returned from a specific database query + * @param bool|ADORecordSet $p_result Database Query Record Set to retrieve next result for. + * @return array Database result + */ function db_fetch_array( & $p_result ) { global $g_db, $g_db_type; @@ -330,7 +393,13 @@ } } - # -------------------- + /** + * Retrieve a result returned from a specific database query + * @param bool|ADORecordSet $p_result Database Query Record Set to retrieve next result for. + * @param int $p_index1 Row to retrieve (optional) + * @param int $p_index2 Column to retrieve (optional) + * @return mixed Database result + */ function db_result( $p_result, $p_index1=0, $p_index2=0 ) { global $g_db; @@ -351,8 +420,11 @@ return false; } - # -------------------- - # return the last inserted id + /** + * return the last inserted id for a specific database table + * @param string $p_table a valid database table name + * @return int last successful insert id + */ function db_insert_id($p_table = null) { global $g_db; @@ -364,10 +436,11 @@ return $g_db->Insert_ID( ); } - # -------------------- - # Check if the specified table exists. - # @param $p_table_name Table name. - # @returns true: table found, false: table not found. + /** + * Check if the specified table exists. + * @param string $p_table_name a valid database table name + * @return bool indicating whether the table exists + */ function db_table_exists( $p_table_name ) { global $g_db, $g_db_schema; @@ -393,6 +466,12 @@ return false; } + /** + * Check if the specified table index exists. + * @param string $p_table_name a valid database table name + * @param string $p_index_name a valid database index name + * @return bool indicating whether the index exists + */ function db_index_exists( $p_table_name, $p_index_name ) { global $g_db, $g_db_schema; @@ -412,62 +491,54 @@ return false; } - - # -------------------- + /** + * Check if the specified field exists in a given table + * @param string $p_field_name a database field name + * @param string $p_table_name a valid database table name + * @return bool indicating whether the field exists + */ function db_field_exists( $p_field_name, $p_table_name ) { global $g_db; return in_array ( $p_field_name , $g_db->MetaColumnNames( $p_table_name ) ) ; } - # -------------------- + /** + * Retrieve list of fields for a given table + * @param string $p_table_name a valid database table name + * @return array array of fields on table + */ function db_field_names( $p_table_name ) { global $g_db; return $g_db->MetaColumnNames( $p_table_name ); } - # -------------------- - # Check if there is an index defined on the specified table/field and with - # the specified type. - # - # @@@ thraxisp - this only works with MySQL - # - # $p_table: Name of table to check - # $p_field: Name of field to check - # $p_key: key type to check for (eg: PRI, MUL, ...etc) - function db_key_exists_on_field( $p_table, $p_field, $p_key ) { - $c_table = db_prepare_string( $p_table ); - $c_field = db_prepare_string( $p_field ); - $c_key = db_prepare_string( $p_key ); - - $query = "DESCRIBE $c_table"; - $result = db_query_bound( $query ); - $count = db_num_rows( $result ); - for ( $i=0 ; $i < $count ; $i++ ) { - $row = db_fetch_array( $result ); - - if ( $row['Field'] == $c_field ) { - return ( $row['Key'] == $c_key ); - } - } - return false; - } - - # -------------------- + /** + * Returns the last error number. The error number is reset after every call to Execute(). If 0 is returned, no error occurred. + * @return int last error number + * @todo Use/Behaviour of this function should be reviewed before 1.2.0 final + */ function db_error_num() { global $g_db; return $g_db->ErrorNo(); } - # -------------------- + /** + * Returns the last status or error message. Returns the last status or error message. The error message is reset when Execute() is called. + * This can return a string even if no error occurs. In general you do not need to call this function unless an ADOdb function returns false on an error. + * @return string last error string + * @todo Use/Behaviour of this function should be reviewed before 1.2.0 final + */ function db_error_msg() { global $g_db; return $g_db->ErrorMsg(); } - # -------------------- - # display both the error num and error msg + /** + * send both the error number and error message and query (optional) as paramaters for a triggered error + * @todo Use/Behaviour of this function should be reviewed before 1.2.0 final + */ function db_error( $p_query=null ) { if ( null !== $p_query ) { error_parameters( db_error_num(), db_error_msg(), $p_query ); @@ -476,20 +547,22 @@ } } - # -------------------- - # close the connection. - # Not really necessary most of the time since a connection is - # automatically closed when a page finishes loading. + /** + * close the connection. + * Not really necessary most of the time since a connection is automatically closed when a page finishes loading. + */ function db_close() { global $g_db; $t_result = $g_db->Close(); } - # -------------------- - # prepare a string before DB insertion - # @@@ should default be return addslashes( $p_string ); or generate an error - # @@@ Consider using ADODB escaping for all databases. + /** + * prepare a string before DB insertion + * @param string $p_string unprepared string + * @return string prepared database query string + * @deprecated db_query_bound should be used in preference to this function. This function may be removed in 1.2.0 final + */ function db_prepare_string( $p_string ) { global $g_db; $t_db_type = config_get_global( 'db_type' ); @@ -537,8 +610,12 @@ } } - # -------------------- - # prepare a binary string before DB insertion + /** + * prepare a binary string before DB insertion + * @param string $p_string unprepared binary data + * @return string prepared database query string + * @todo Use/Behaviour of this function should be reviewed before 1.2.0 final + */ function db_prepare_binary_string( $p_string ) { $t_db_type = config_get_global( 'db_type' ); @@ -555,104 +632,155 @@ } } - # prepare a date for binding in the format database accepts. - # @param p_date can be a Unix integer timestamp or an ISO format Y-m-d. If null or false or '' is passed in, it will be converted to an SQL null. + /** + * prepare a date for binding in the format database accepts. + * @param string|int $p_date can be a Unix integer timestamp or an ISO format Y-m-d. If null or false or '' is passed in, it will be converted to an SQL null. + * @return string Formatted Date for DB insertion e.g. 1970-01-01 ready for database insertion + */ function db_bind_date( $p_date ) { global $g_db; return $g_db->BindDate( $p_date ); } - function db_bind_timestamp( $p_date ) { + /** + * prepare a date/time for binding in the format database accepts. + * @param string|int $p_date can be a Unix integer timestamp or an ISO format Y-m-d h:m:s. If null or false or '' is passed in, it will be converted to an SQL null. + * @param bool $p_gmt whether to use GMT or current timezone (default false) + * @return string Formatted Date for DB insertion e.g. 1970-01-01 00:00:00 ready for database insertion + */ + function db_bind_timestamp( $p_date, $p_gmt = false ) { global $g_db; - return $g_db->BindTimeStamp( $p_date ); + if ( $p_gmt ) + return $g_db->BindTimestamp($g_db->UserTimeStamp($p_date, 'Y-m-d H:i:s', true)); + else + return $g_db->BindTimeStamp( $p_date ); } - # -------------------- - # prepare an integer before DB insertion + /** + * prepare a int for database insertion. + * @param int $p_int integer + * @return int integer + * @deprecated db_query_bound should be used in preference to this function. This function may be removed in 1.2.0 final + * @todo Use/Behaviour of this function should be reviewed before 1.2.0 final + */ function db_prepare_int( $p_int ) { return (int)$p_int; } - # -------------------- - # prepare a double before DB insertion + /** + * prepare a double for database insertion. + * @param double $p_double double + * @return double double + * @deprecated db_query_bound should be used in preference to this function. This function may be removed in 1.2.0 final + * @todo Use/Behaviour of this function should be reviewed before 1.2.0 final + */ function db_prepare_double( $p_double ) { return (double)$p_double; } - # -------------------- - # prepare a boolean before DB insertion - function db_prepare_bool( $p_bool ) { + /** + * prepare a boolean for database insertion. + * @param boolean $p_boolean boolean + * @return int integer representing boolean + * @deprecated db_query_bound should be used in preference to this function. This function may be removed in 1.2.0 final + * @todo Use/Behaviour of this function should be reviewed before 1.2.0 final + */ + function db_prepare_bool( $p_bool ) { return (int)(bool)$p_bool; } - # -------------------- - # return current timestamp for DB + /** + * return current timestamp for DB + * @todo add param bool $p_gmt whether to use GMT or current timezone (default false) + * @return string Formatted Date for DB insertion e.g. 1970-01-01 00:00:00 ready for database insertion + */ function db_now() { global $g_db; return $g_db->BindTimeStamp(time()); } - # -------------------- - # generate a unixtimestamp of a date - # > SELECT UNIX_TIMESTAMP(); - # -> 882226357 - # > SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); - # -> 875996580 - function db_timestamp( $p_date=null ) { + /** + * generate a date/time in database format of a date + * @param $p_date Date + * @param bool $p_gmt whether to use GMT or current timezone (default false) + * @return string Formatted Date for DB insertion e.g. 1970-01-01 00:00:00 ready for database insertion + * @todo review date handling + */ + function db_timestamp( $p_date=null, $p_gmt = false ) { global $g_db; if ( null !== $p_date ) { - $p_timestamp = $g_db->UnixTimeStamp($p_date); + $p_timestamp = $g_db->UnixTimeStamp($p_date, $p_gmt); } else { $p_timestamp = time(); } return $g_db->BindTimeStamp($p_timestamp) ; } - function db_unixtimestamp( $p_date=null ) { + /** + * generate a integer unixtimestamp of a date + * @param $p_date Date + * @param bool $p_gmt whether to use GMT or current timezone (default false) + * @return int unix timestamp of a date + * @todo review date handling + */ + function db_unixtimestamp( $p_date=null, $p_gmt = false ) { global $g_db; if ( null !== $p_date ) { - $p_timestamp = $g_db->UnixTimeStamp($p_date); + $p_timestamp = $g_db->UnixTimeStamp($p_date, $p_gmt); } else { $p_timestamp = time(); } return $p_timestamp ; } - # convert unix timestamp to db compatible date - function db_date( $p_timestamp=null ) { + /** + * convert unix timestamp to db compatible date + * @param $p_timestamp Date + * @param bool $p_gmt whether to use GMT or current timezone (default false) + * @return string Formatted Date + * @todo review date handling + */ + function db_date( $p_timestamp=null, $p_gmt = false ) { global $g_db; if ( null !== $p_timestamp ) { - $p_date = $g_db->UserTimeStamp($p_timestamp); + $p_date = $g_db->UserTimeStamp($p_timestamp, $p_gmt); } else { - $p_date = $g_db->UserTimeStamp(time()); + $p_date = $g_db->UserTimeStamp(time(), $p_gmt); } return $p_date; } - # return a DB compatible date, representing a unixtime(0) + 1 second. + /** + * return a DB compatible date, representing a unixtime(0) + 1 second. Internally considered a NULL date + * @return string Formatted Date for DB insertion e.g. 1970-01-01 00:00:00 ready for database insertion + */ function db_null_date() { global $g_db; return $g_db->BindTimestamp($g_db->UserTimeStamp(1, 'Y-m-d H:i:s', true)); } - # -------------------- - # convert minutes to a time format [h]h:mm + /** + * convert minutes to a time format [h]h:mm + * @param int $p_min integer representing number of minutes + * @return string representing formatted duration string in hh:mm format. + */ function db_minutes_to_hhmm( $p_min = 0 ) { return sprintf( '%02d:%02d', $p_min / 60, $p_min % 60 ); } - # -------------------- - # A helper function that generates a case-sensitive or case-insensitive like phrase based on the current db type. - # $p_field_name - The name of the field to filter on. - # $p_value - The value that includes the pattern (can include % for wild cards) - not including the quotations. - # $p_case_sensitive - true: case sensitive, false: case insensitive - # returns (field LIKE 'value') OR (field ILIKE 'value') - # The field name and value are assumed to be safe to insert in a query (i.e. already cleaned). + /** + * A helper function that generates a case-sensitive or case-insensitive like phrase based on the current db type. + * The field name and value are assumed to be safe to insert in a query (i.e. already cleaned). + * @param string $p_field_name The name of the field to filter on. + * @param int $p_param_id An integer pointing to use for a query_bound value that will represent the value that includes the pattern (can include % for wild cards) - not including the quotations. + * @param bool $p_case_sensitive true: case sensitive, false: case insensitive + * @return string returns (field LIKE 'value') OR (field ILIKE 'value') + */ function db_helper_like( $p_field_name, $p_param_id, $p_case_sensitive = false ) { $t_like_keyword = 'LIKE'; @@ -665,10 +793,14 @@ return "($p_field_name $t_like_keyword " . db_param( $p_param_id ) . ')'; } - # -------------------- - # helper function to compare two dates against a certain number of days - # limitstring can be '> 1' '<= 2 ' etc - # @@@ Check if there is a way to do that using ADODB rather than implementing it here. + /** + * A helper function to compare two dates against a certain number of days + * @param $p_date1_id_or_column + * @param $p_date2_id_or_column + * @param $p_limitstring + * @return string returns database query component to compare dates + * @todo Check if there is a way to do that using ADODB rather than implementing it here. + */ function db_helper_compare_days($p_date1_id_or_column, $p_date2_id_or_column, $p_limitstring) { $t_db_type = config_get_global( 'db_type' ); @@ -709,16 +841,20 @@ } } - # -------------------- - # count queries + /** + * count queries + * @return int + */ function db_count_queries () { global $g_queries_array; return count( $g_queries_array ); } - # -------------------- - # count unique queries + /** + * count unique queries + * @return int + */ function db_count_unique_queries () { global $g_queries_array; @@ -733,8 +869,10 @@ return $t_unique_queries; } - # -------------------- - # get total time for queries + /** + * get total time for queries + * @return int + */ function db_time_queries () { global $g_queries_array; $t_count = count( $g_queries_array ); @@ -745,6 +883,10 @@ return $t_total; } + /** + * get database table name + * @return string containing full database table name + */ function db_get_table( $p_option ) { if ( isset( $GLOBALS['g_db_table'][$p_option] ) ) { $t_value = config_eval( $GLOBALS['g_db_table'][$p_option] ); @@ -757,7 +899,11 @@ trigger_error( ERROR_CONFIG_OPT_NOT_FOUND, WARNING ); } } - + + /** + * get list database tables + * @return array containing table names + */ function db_get_table_list() { $t_tables = Array(); foreach( $GLOBALS['g_db_table'] as $t_table ) { @@ -772,7 +918,7 @@ if ( OFF == $g_use_persistent_connections ) { db_connect( config_get_global( 'dsn', false ), $g_hostname, $g_db_username, $g_db_password, $g_database_name, config_get_global( 'db_schema' ) ); } else { - db_pconnect( config_get_global( 'dsn', false ), $g_hostname, $g_db_username, $g_db_password, $g_database_name, config_get_global( 'db_schema' ) ); + db_connect( config_get_global( 'dsn', false ), $g_hostname, $g_db_username, $g_db_password, $g_database_name, config_get_global( 'db_schema' ), true ); } } else { define( 'PLUGINS_DISABLED', true ); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |