* @license http://www.gnu.org/copyleft/gpl.html GNU GPL version 3 or later * @copyright For copyright information on Mahara, please see the README file distributed with this software. * @copyright (C) 2001-3001 Martin Dougiamas http://dougiamas.com * */ defined('INTERNAL') || die(); /** * Return a table name, properly prefixed and escaped * */ function db_table_name($name) { return db_quote_identifier(get_config('dbprefix') . $name); } /** * Searches through a query for strings looking like {name}, to replace with * correctly quoted and prefixed table names * * @param string $sql The SQL to replace the placeholders in * @return string */ function db_quote_table_placeholders($sql) { return preg_replace_callback('/\{([a-z][a-z0-9_]+)\}/', '_db_quote_table_placeholders_callback', $sql); } /** * A callback function used only in db_quote_table_placeholders * @param array $matches */ function _db_quote_table_placeholders_callback($matches) { return db_table_name($matches[1]); } /** * Given a table name or other identifier, return it quoted for the appropriate * database engine currently being used * * @param string $identifier The identifier to quote * @return string */ function db_quote_identifier($identifier) { // Currently, postgres and mysql (in postgres compat. mode) both support // the sql standard " if (strpos($identifier, '"') !== false) { return $identifier; } return '"' . $identifier . '"'; } /** * Check whether the db's default character encoding is utf8 * * @return bool */ function db_is_utf8() { global $db; if (!is_a($db, 'ADOConnection')) { throw new SQLException('Database connection is not available '); } if (is_mysql()) { $result = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); return $result->fields['Value'] == 'utf8'; } if (is_postgres()) { $result = $db->Execute("SHOW SERVER_ENCODING"); return $result->fields['server_encoding'] == 'UTF8'; } return false; } function db_total_size() { global $db; if (!is_a($db, 'ADOConnection')) { throw new SQLException('Database connection is not available '); } $dbname = db_quote(get_config('dbname')); if (is_mysql()) { $result = $db->Execute(" SELECT SUM( data_length + index_length ) AS dbsize FROM information_schema.tables WHERE table_schema = $dbname "); return $result->fields['dbsize']; } if (is_postgres()) { $result = $db->Execute("SELECT * FROM pg_database_size($dbname)"); return $result->fields['pg_database_size']; } return false; } function column_collation_is_default($table, $column) { global $db; if (!is_a($db, 'ADOConnection')) { throw new SQLException('Database connection is not available '); } if (is_mysql()) { $result = $db->Execute("SHOW VARIABLES LIKE 'collation_database'"); $defaultcollation = $result->fields['Value']; $command = 'SHOW FULL COLUMNS FROM ' . db_table_name($table) . ' WHERE field = ?'; $stmt = $db->Prepare($command); $result = $db->Execute($stmt, array($column)); return $result->fields['Collation'] == $defaultcollation; } return true; } /** * Execute a given sql command string * * Completely general function - it just runs some SQL and reports success. * * @uses $db * @param string $command The sql string you wish to be executed. * @param array $values When using prepared statements, this is the value array (optional). * @return string * @throws SQLException */ function execute_sql($command, $values=null) { global $db; if (!is_a($db, 'ADOConnection')) { throw new SQLException('Database connection is not available '); } $command = db_quote_table_placeholders($command); // @todo need to do more research into this flag - what is it for, we // probably want to just turn it off because we can catch the exceptions $olddebug = $db->debug; $db->debug = false; try { if (!empty($values) && is_array($values) && count($values) > 0) { $stmt = $db->Prepare($command); $result = $db->Execute($stmt, $values); } else { $result = $db->Execute($command); } } catch (ADODB_Exception $e) { log_debug($e->getMessage() . "Command was: $command"); $db->debug = $olddebug; throw new SQLException('Could not execute command: ' . $command); } $db->debug = $olddebug; return true; } /// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS //////////////////////////////////////// /** * Test whether a record exists in a table where all the given fields match the given values. * * The record to test is specified by giving up to three fields that must * equal the corresponding values. * * @param string $table The table to check. * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (requred if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (requred if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (requred if field3 is given, else optional). * @return bool true if a matching record exists, else false. * @throws SQLException */ function record_exists($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) { $select = where_clause_prepared($field1, $field2, $field3); $values = where_values_prepared($value1, $value2, $value3); return record_exists_sql('SELECT * FROM ' . db_table_name($table) .' '. $select, $values); } /** * Test whether any records exists in a table which match a particular WHERE clause. * * This function returns true if at least one record is returned * * @param string $table The database table to be checked against. * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call. * @param array $values When using prepared statements, this is the value array (optional). * @return bool true if a matching record exists, else false. * @throws SQLException */ function record_exists_select($table, $select='', $values=null) { global $CFG; if ($select) { $select = 'WHERE '.$select; } return record_exists_sql('SELECT * FROM '. db_table_name($table) . ' ' . $select, $values); } /** * Test whether a SQL SELECT statement returns any records. * * This function returns true if at least one record is returned. * * @param string $sql The SQL statement to be executed. If using $values, placeholder ?s are expected. If not, the string should be escaped correctly. * @param array $values When using prepared statements, this is the value array (optional). * @return bool true if the SQL executes without errors and returns at least one record. * @throws SQLException */ function record_exists_sql($sql, $values=null) { $rs = get_recordset_sql($sql, $values, 0, 1); return $rs->RecordCount() > 0; } /** * Count the records in a table where all the given fields match the given values. * * @param string $table The table to query. * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (requred if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (requred if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (requred if field3 is given, else optional). * @return int The count of records returned from the specified criteria. * @throws SQLException */ function count_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) { $select = where_clause_prepared($field1, $field2, $field3); $values = where_values_prepared($value1, $value2, $value3); return count_records_sql('SELECT COUNT(*) FROM '. db_table_name($table) . ' ' . $select, $values); } /** * Count the records in a table which match a particular WHERE clause. * * @param string $table The database table to be checked against. * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call. * @param array $values if using a prepared statement with placeholders in $select, pass values here. optional * @param string $countitem The count string to be used in the SQL call. Default is COUNT(*). * @return int The count of records returned from the specified criteria. * @throws SQLException */ function count_records_select($table, $select='', $values=null, $countitem='COUNT(*)') { if ($select) { $select = 'WHERE ' . $select; } return count_records_sql('SELECT '. $countitem .' FROM '. db_table_name($table) . ' ' . $select, $values); } /** * Get the result of a SQL SELECT COUNT(...) query. * * Given a query that counts rows, return that count. (In fact, * given any query, return the first field of the first record * returned. However, this method should only be used for the * intended purpose.) If an error occurrs, 0 is returned. * * @uses $db * @param string $sql The SQL string you wish to be executed. * @param array $values When using prepared statements, this is the value array (optional). * @return int The count. * @throws SQLException */ function count_records_sql($sql, $values=null) { $rs = get_recordset_sql($sql, $values); return reset($rs->fields); } /// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA /////////////////////////////////// /** * Get a single record as an object * * @param string $table The table to select from. * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (required if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (required if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (required if field3 is given, else optional). * @return mixed a fieldset object containing the first mathcing record, or false if none found. * @throws SQLException */ function get_record($table, $field1, $value1, $field2=null, $value2=null, $field3=null, $value3=null, $fields='*') { $select = where_clause_prepared($field1, $field2, $field3); $values = where_values_prepared($value1, $value2, $value3); return get_record_sql('SELECT ' . $fields . ' FROM ' . db_table_name($table) . ' ' . $select, $values); } /** * Get a single record as an object using an SQL statement * * This function is designed to retrieve ONE record. If your query returns more than one record, * an exception is thrown. If you want more than one record, use get_records_sql_array or get_records_sql_assoc * * @param string $sql The SQL string you wish to be executed, should normally only return one record. * @param array $values When using prepared statements, this is the value array (optional). * @return Found record as object. False if not found * @throws SQLException */ function get_record_sql($sql, $values=null) { $limitfrom = 0; $limitnum = 0; # regex borrowed from htdocs/lib/adodb/adodb-lib.inc.php if (!preg_match('/\sLIMIT\s+[0-9]+/i', $sql)) { $limitfrom = 0; $limitnum = 2; } if (!$rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum)) { return false; } $recordcount = $rs->RecordCount(); if ($recordcount == 0) { // Found no records return false; } else if ($recordcount == 1) { // Found one record return (object)$rs->fields; } else { // Error: found more than one record throw new SQLException('get_record_sql found more than one row. If you meant to retrieve more ' . 'than one record, use get_records_*, otherwise check your code or database for inconsistencies'); } } /** * Gets one record from a table, as an object * * @param string $table The database table to be checked against. * @param string $select A fragment of SQL to be used in a where clause in the SQL call. * @param array $values When using prepared statements, this is the value array (optional). * @param string $fields A comma separated list of fields to be returned from the chosen table. * @return object Returns an array of found records (as objects) * @throws SQLException */ function get_record_select($table, $select='', $values=null, $fields='*') { if ($select) { $select = 'WHERE '. $select; } return get_record_sql('SELECT '. $fields .' FROM ' . db_table_name($table) .' '. $select, $values); } /** * Get a number of records as an ADODB RecordSet. * * Selects records from the table $table. * * If specified, only records where the field $field has value $value are retured. * * If specified, the results will be sorted as specified by $sort. This * is added to the SQL as "ORDER BY $sort". Example values of $sort * mightbe "time ASC" or "time DESC". * * If $fields is specified, only those fields are returned. * * This function is internal to datalib, and should NEVER should be called directly * from general Moodle scripts. Use get_record, get_records_* etc. * * If you only want some of the records, specify $limitfrom and $limitnum. * The query will skip the first $limitfrom records (according to the sort * order) and then return the next $limitnum records. If either of $limitfrom * or $limitnum is specified, both must be present. * * The return value is an ADODB RecordSet object * @link http://phplens.com/adodb/reference.functions.adorecordset.html * if the query succeeds. If an error occurrs, an exception is thrown. * * @param string $table the table to query. * @param string $field a field to check (optional). * @param string $value the value the field must have (required if field1 is given, else optional). * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an ADODB RecordSet object. * @throws SQLException */ function get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') { $values = null; if ($field) { $select = db_quote_identifier($field) . " = ?"; $values = array($value); } else { $select = ''; } return get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum); } /** * Get a number of records as an ADODB RecordSet. * * If given, $select is used as the SELECT parameter in the SQL query, * otherwise all records from the table are returned. * * Other arguments and the return type as for @see function get_recordset. * * @param string $table the table to query. * @param string $select A fragment of SQL to be used in a where clause in the SQL call. * @param array $values When using prepared statements, this is the value array (optional). * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an ADODB RecordSet object * @throws SQLException */ function get_recordset_select($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') { if ($select) { $select = ' WHERE '. $select; } if ($limitfrom !== '') { $limit = 'LIMIT ' . intval($limitnum) . ' OFFSET ' . intval($limitfrom); } else { $limit = ''; } if ($sort) { $sort = ' ORDER BY '. $sort; } return get_recordset_sql('SELECT '. $fields .' FROM '. db_table_name($table) . $select . $sort .' '. $limit, $values); } /** * Get a number of records as an ADODB RecordSet. $sql must be a complete SQL query. * This function is internal to datalib, and should NEVER should be called directly * from general Moodle scripts. Use get_record, get_records_* etc. * * The return type is as for @see function get_recordset. * * @uses $db * @param string $sql the SQL select query to execute. * @param array $values When using prepared statements, this is the value array (optional). * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an ADODB RecordSet object * @throws SQLException */ function get_recordset_sql($sql, $values=null, $limitfrom=null, $limitnum=null) { global $db; if (!is_a($db, 'ADOConnection')) { throw new SQLException('Database connection is not available '); } $sql = db_quote_table_placeholders($sql); try { if ($limitfrom || $limitnum) { ///Special case, 0 must be -1 for ADOdb $limitfrom = empty($limitfrom) ? -1 : $limitfrom; $limitnum = empty($limitnum) ? -1 : $limitnum; $rs = $db->SelectLimit($sql, $limitnum, $limitfrom, $values); } else { $rs = false; if (!empty($values) && is_array($values) && count($values) > 0) { $stmt = $db->Prepare($sql); $rs = $db->Execute($stmt, $values); } else { $rs = $db->Execute($sql); } } } catch (ADODB_Exception $e) { throw new SQLException(create_sql_exception_message($e, $sql, $values)); } return $rs; } /** * Utility function to turn a result set into an array of records * * @param object an ADODB RecordSet object. * @return mixed an array of objects, or false if the RecordSet was empty. * @throws SQLException */ function recordset_to_array($rs) { if ($rs && $rs->RecordCount() > 0) { $array = $rs->GetArray(); foreach ($array as &$a) { $a = (object)$a; } return $array; } else { return false; } } // // Generic data retrieval functions - get_records* // /** * Utility function to turn a result set into an associative array of records * This method turns a result set into a hash of records (keyed by the first * field in the result set) * * @param object $rs An ADODB RecordSet object. * @return mixed An array of objects, or false if the RecordSet was empty. * @throws SQLException * @access private */ function recordset_to_assoc($rs) { if ($rs && $rs->RecordCount() > 0) { // First of all, we are going to get the name of the first column // to introduce it back after transforming the recordset to assoc array // See http://docs.moodle.org/en/XMLDB_Problems, fetch mode problem. $firstcolumn = $rs->FetchField(0); // Get the whole associative array if ($records = $rs->GetAssoc(true)) { foreach ($records as $key => $record) { $record[$firstcolumn->name] = $key; $objects[$key] = (object) $record; } return $objects; } else { return false; } } else { return false; } } /** * Get a number of records as an associative array of objects. (WARNING: this * does not return an array, it returns an associative array keyed by the first * column in the result set. As a result, you may lose some rows! Please use * {@link get_records_*_array} instead where possible) * * If the query succeeds and returns at least one record, the * return value is an array of objects, one object for each * record found. The array key is the value from the first * column of the result set. The object associated with that key * has a member variable for each column of the results. * * @param string $table the table to query. * @param string $field a field to check (optional). * @param string $value the value the field must have (requred if field1 is given, else optional). * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an array of objects, or false if no records were found. * @throws SQLException */ function get_records_assoc($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') { $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum); return recordset_to_assoc($rs); } /** * Get a number of records as an array of objects. * * If the query succeeds and returns at least one record, the * return value is an array of objects, one object for each * record found. The array key is the value from the first * column of the result set. The object associated with that key * has a member variable for each column of the results. * * @param string $table the table to query. * @param string $field a field to check (optional). * @param string $value the value the field must have (requred if field1 is given, else optional). * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an array of objects, or false if no records were found. * @throws SQLException */ function get_records_array($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') { $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum); return recordset_to_array($rs); } /** * Get a number of records as an associative array of objects. * * Return value as for @see function get_records_assoc * * @param string $table the table to query. * @param string $select A fragment of SQL to be used in a where clause in the SQL call. * @param array $values When using prepared statements, this is the value array (optional). * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an array of objects, or false if no records were found. * @throws SQLException */ function get_records_select_assoc($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') { $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum); return recordset_to_assoc($rs); } /** * Get a number of records as an array of objects. * * Return value as for {@link get_records_array}. * * @param string $table the table to query. * @param string $select A fragment of SQL to be used in a where clause in the SQL call. * @param array $values When using prepared statements, this is the value array (optional). * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an array of objects, or false if no records were found. * @throws SQLException */ function get_records_select_array($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') { $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum); return recordset_to_array($rs); } /** * Get a number of records as an associative array of objects. * * Return value as for @see function get_records_assoc * * @param string $sql the SQL select query to execute. * @param array $values When using prepared statements, this is the value array. * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an array of objects, or false if no records were found. * @throws SQLException */ function get_records_sql_assoc($sql,$values, $limitfrom='', $limitnum='') { $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum); return recordset_to_assoc($rs); } /** * Get a number of records as an array of objects. * * Return value as for {@link get_records_array} * * @param string $sql the SQL select query to execute. * @param array $values When using prepared statements, this is the value array. * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). * @return mixed an array of objects, or false if no records were found. * @throws SQLException */ function get_records_sql_array($sql,$values, $limitfrom='', $limitnum='') { $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum); return recordset_to_array($rs); } // // Menu related functions - get_records_*_menu // /** * Utility function used by the following 3 methods. * * @param object $rs an ADODB RecordSet object with two columns. * @return mixed an associative array, or false if an error occurred or the RecordSet was empty. * @access private */ function recordset_to_menu($rs) { global $CFG; if ($rs && $rs->RecordCount() > 0) { $keys = array_keys($rs->fields); $key0 = $keys[0]; if (isset($keys[1])) { $key1 = $keys[1]; } else { $key1 = $keys[0]; } while (!$rs->EOF) { $menu[$rs->fields[$key0]] = $rs->fields[$key1]; $rs->MoveNext(); } return $menu; } else { return false; } } /** * Get the first two columns from a number of records as an associative array. * * Arguments as for {@link get_recordset}. * * If no errors occur, and at least one records is found, the return value * is an associative whose keys come from the first field of each record, * and whose values are the corresponding second fields. If no records are found, * or an error occurs, false is returned. * * @param string $table the table to query. * @param string $field a field to check (optional). * @param string $value the value the field must have (requred if field1 is given, else optional). * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). * @return mixed an associative array, or false if no records were found or an error occurred. */ function get_records_menu($table, $field='', $value='', $sort='', $fields='*') { $rs = get_recordset($table, $field, $value, $sort, $fields); return recordset_to_menu($rs); } /** * Get the first two columns from a number of records as an associative array. * * Arguments as for @see function get_recordset_select. * Return value as for @see function get_records_menu. * * @param string $table The database table to be checked against. * @param string $select A fragment of SQL to be used in a where clause in the SQL call. * @param array $values When using prepared statements, this is the value array (optional). * @param string $sort Sort order (optional) - a valid SQL order parameter * @param string $fields A comma separated list of fields to be returned from the chosen table. * @return mixed an associative array, or false if no records were found or an error occurred. */ function get_records_select_menu($table, $select='', $values=null, $sort='', $fields='*') { $rs = get_recordset_select($table, $select, $values, $sort, $fields); return recordset_to_menu($rs); } /** * Get the first two columns from a number of records as an associative array. * * Arguments as for @see function get_recordset_sql. * Return value as for @see function get_records_menu. * * @param string $sql The SQL string you wish to be executed. * @param array $values When using prepared statements, this is the value array (optional). * @return mixed an associative array, or false if no records were found or an error occured. */ function get_records_sql_menu($sql,$values=null) { $rs = get_recordset_sql($sql,$values); return recordset_to_menu($rs); } // // Field related data access - get_field* // /** * Get a single value from a table row where all the given fields match the given values. * * @param string $table the table to query. * @param string $field the field to return the value of. * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (requred if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (requred if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (requred if field3 is given, else optional). * @return mixed the specified value * @throws SQLException */ function get_field($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) { $select = where_clause_prepared($field1, $field2, $field3); $values = where_values_prepared($value1, $value2, $value3); return get_field_sql('SELECT ' . $field . ' FROM ' . db_table_name($table) . ' ' . $select, $values); } /** * Get a single value from a table. * * @param string $sql an SQL statement expected to return a single value. * @param array $values When using prepared statements, this is the value array (optional). * @return mixed the specified value. * @throws SQLException */ function get_field_sql($sql, $values=null) { $rs = get_recordset_sql($sql, $values); if ($rs && $rs->RecordCount() == 1) { return reset($rs->fields); } else { return false; } } // // Column related data access - get_column* // /** * Get a single column from a table where all the given fields match the given values. * * @param string $table the table to query. * @param string $field the field to return the value of. * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (requred if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (requred if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (requred if field3 is given, else optional). * @return mixed the specified value * @throws SQLException */ function get_column($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) { $select = where_clause_prepared($field1, $field2, $field3); $values = where_values_prepared($value1, $value2, $value3); return get_column_sql('SELECT ' . $field . ' FROM ' . db_table_name($table) . ' ' . $select, $values); } /** * Get a single column from a table. * * @param string $sql an SQL statement expected to return a single value. * @param array $values When using prepared statements, this is the value array (optional). * @return mixed the specified value. * @throws SQLException */ function get_column_sql($sql, $values=null) { global $db; $sql = db_quote_table_placeholders($sql); try { return $db->GetCol($sql, $values); } catch (ADODB_Exception $e) { throw new SQLException(create_sql_exception_message($e, $sql, $values)); } } // // Field related data modification - set_field* // /** * Set a single field in every table row where all the given fields match the given values. * * @uses $db * @param string $table The database table to be checked against. * @param string $newfield the field to set. * @param string $newvalue the value to set the field to. * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (requred if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (requred if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (requred if field3 is given, else optional). * @return mixed An ADODB RecordSet object with the results from the SQL call or false. * @throws SQLException */ function set_field($table, $newfield, $newvalue, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) { global $db; $select = where_clause_prepared($field1, $field2, $field3); $values = where_values_prepared($value1, $value2, $value3); return set_field_select($table, $newfield, $newvalue, $select, $values); } function set_field_select($table, $newfield, $newvalue, $select, $values) { global $db; // @todo Catalyst IT Ltd if (!empty($select) && !preg_match('/^\s*where/i', $select)) { $select = ' WHERE ' . $select; } $select = db_quote_table_placeholders($select); $values = array_merge(array($newvalue), $values); $sql = 'UPDATE '. db_table_name($table) .' SET '. db_quote_identifier($newfield) .' = ? ' . $select; try { $stmt = $db->Prepare($sql); return $db->Execute($stmt, $values); } catch (ADODB_Exception $e) { throw new SQLException(create_sql_exception_message($e, $sql, $values)); } } // // Delete based functions - delete_records* // /** * Delete the records from a table where all the given fields match the given values. * * @uses $db * @param string $table the table to delete from. * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (requred if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (requred if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (requred if field3 is given, else optional). * @return mixed An ADODB RecordSet object with the results from the SQL call or false. * @throws SQLException */ function delete_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) { global $db; $select = where_clause_prepared($field1, $field2, $field3); $values = where_values_prepared($value1, $value2, $value3); $sql = 'DELETE FROM '. db_table_name($table) . ' ' . $select; try { $stmt = $db->Prepare($sql); return $db->Execute($stmt,$values); } catch (ADODB_Exception $e) { throw new SQLException(create_sql_exception_message($e, $sql, $values)); } } /** * Delete one or more records from a table * * @uses $db * @param string $table The database table to be checked against. * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). * @param array $values When using prepared statements, this is the value array (optional). * @return object A PHP standard object with the results from the SQL call. * @throws SQLException */ function delete_records_select($table, $select='',$values=null) { if ($select) { $select = 'WHERE '.$select; } return delete_records_sql('DELETE FROM '. db_table_name($table) .' '. $select, $values); } /** * @todo This function does nothing delete specific. The functionality * it has with the $values parameter should be merged with the execute_sql * function */ function delete_records_sql($sql, $values=null) { global $db; $sql = db_quote_table_placeholders($sql); try { $result = false; if (!empty($values) && is_array($values) && count($values) > 0) { $stmt = $db->Prepare($sql); $result = $db->Execute($stmt, $values); } else { $result = $db->Execute($sql); } } catch (ADODB_Exception $e) { throw new SQLException(create_sql_exception_message($e, $sql, $values)); } return $result; } /** * Insert a record into a table and return the "id" field if required * * If the return ID isn't required, then this just reports success as true/false. * $dataobject is an object containing needed data * * @uses $db * @param string $table The database table to be checked against. * @param array $dataobject A data object with values for one or more fields in the record * @param string $primarykey The primary key of the table we are inserting into (almost always "id") * @param bool $returnpk Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned. * @throws SQLException */ function insert_record($table, $dataobject, $primarykey=false, $returnpk=false) { // $INSERTRECORD_NOCACHE is yet another work around of dmllib/adodb's ineptitude. // It's all nice to cache the table columns lookup, but what if the table // columns change over the life of the page load? This happens when an // upgrade is running. All of a sudden, the table_column cache is out of // date and we can't insert new data properly. // Temporary solution: set INSERTRECORD_NOCACHE to true before your calls // that need a new lookup, and unset it afterwards global $db, $INSERTRECORD_NOCACHE; static $table_columns; // Determine all the fields in the table if (empty($INSERTRECORD_NOCACHE) && is_array($table_columns) && array_key_exists($table, $table_columns)) { $columns = $table_columns[$table]; } else { if (!$columns = $db->MetaColumns(get_config('dbprefix') . $table)) { throw new SQLException('Table "' . get_config('dbprefix') . $table . '" does not appear to exist'); } $table_columns[$table] = $columns; } if (!empty($primarykey)) { unset($dataobject->{$primarykey}); if (!empty($returnpk) && is_postgres()) { $pksql = "SELECT NEXTVAL('" . get_config('dbprefix') . "{$table}_{$primarykey}_seq')"; if ($nextval = (int)get_field_sql($pksql)) { $setfromseq = true; $dataobject->{$primarykey} = $nextval; } } } $data = (array)$dataobject; // Pull out data matching these fields $ddd = array(); foreach ($columns as $column) { if (isset($data[$column->name])) { if ($column->name == $primarykey && empty($setfromseq)) { continue; } $ddd[$column->name] = $data[$column->name]; } } // Construct SQL queries $numddd = count($ddd); $count = 0; $insertSQL = 'INSERT INTO '. db_table_name($table) .' ('; $fields = ''; $values = ''; foreach ($ddd as $key => $value) { $count++; $fields .= '"' . $key . '"'; $values .= '?'; if ($count < $numddd) { $fields .= ', '; $values .= ', '; } } $insertSQL .= $fields.') VALUES ('.$values.')'; // Run the SQL statement try { $stmt = $db->Prepare($insertSQL); $rs = $db->Execute($stmt,$ddd); } catch (ADODB_Exception $e) { throw new SQLException(create_sql_exception_message($e, $insertSQL, $ddd)); } // If a return ID is not needed then just return true now if (empty($returnpk)) { return true; } // We already know the record PK if it's been passed explicitly, // or if we've retrieved it from a sequence (Postgres). if (!empty($dataobject->{$primarykey})) { return $dataobject->{$primarykey}; } // This only gets triggered with non-Postgres databases // however we have some postgres fallback in case we failed // to find the sequence. $id = $db->Insert_ID(); if (is_postgres()) { // try to get the primary key based on id try { $oidsql = 'SELECT ' . $primarykey . ' FROM '. db_table_name($table) . ' WHERE oid = ' . $id; $rs = $db->Execute($oidsql); if ($rs->RecordCount() == 1) { return (integer)$rs->fields[0]; } throw new SQLException('WTF: somehow got more than one record when searching for a primary key'); } catch (ADODB_Exception $e) { throw new SQLException("Trying to get pk from oid failed: " . $e->getMessage() . " sql was $oidsql"); } } return (integer)$id; } /** * Inserts a record, only if the record does not already exist. * If the record DOES exist, it is updated. * * @uses $db * @param string $table The database table to be checked against. * @param array $whereobject A data object with values for one or more fields in the record (to determine whether the record exists or not) * @param array $dataobject A data object with values for one or more fields in the record (to be inserted or updated) * @param string $primarykey The primary key of the table we are inserting into (almost always "id") * @param bool $returnpk Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned. * @throws SQLException */ function ensure_record_exists($table, $whereobject, $dataobject, $primarykey=false, $returnpk=false) { $columns = (array)$whereobject; $where = array(); $values = array(); $toreturn = false; foreach ($columns as $key => $value) { if (is_null($value)) { $where[] = db_quote_identifier($key) . ' IS NULL '; continue; } $where[] = db_quote_identifier($key) . ' = ? '; $values[] = $value; } $where = implode(' AND ', $where); if (is_postgres()) { $where .= ' FOR UPDATE '; } else { // @TODO maybe some mysql specific stuff here } db_begin(); if ($exists = get_record_select($table, $where, $values)) { if ($returnpk) { $toreturn = $exists->{$primarykey}; } else { $toreturn = true; } if ($dataobject && $dataobject != $whereobject) { // we want to update it) update_record($table, $dataobject, $whereobject); } } else { $toreturn = insert_record($table, $dataobject, $primarykey, $returnpk); } db_commit(); return $toreturn; } /** * Update a record in a table * * $dataobject is an object containing needed data * Relies on $dataobject having a variable "id" to * specify the record to update * * @uses $db * @param string $table The database table to be checked against. * @param array $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. * @param mixed $where defines the WHERE part of the upgrade. Can be string (key) or array (keys) or hash (keys/values). * If the first two, values are expected to be in $dataobject. * @return bool * @throws SQLException */ function update_record($table, $dataobject, $where=null) { global $db; if (is_object($dataobject)) { $dataobject = clone $dataobject; } if (empty($where)) { $where = 'id'; if (!isset($dataobject->id) ) { // nothing to put in the where clause and we don't want to update everything throw new SQLException('update_record called with no where clause and no ID'); } } $wherefields = array(); $wherevalues = array(); $values = array(); if (is_string($where)) { // treat it like a stack (ie, field in dataobject) $where = array($where); } if (is_object($where) || is_hash($where)) { // the values are contained in the where ... foreach ((array)$where as $field => $value) { $wherefields[] = $field; $wherevalues[] = $value; unset($dataobject->{$field}); } } else if (is_array($where)) { // look for the values in $dataobject and complain bitterly if they're not there foreach ($where as $field) { if (!isset($dataobject->{$field})) { throw new SQLException('Field in where clause not in the update object'); } $wherefields[] = $field; $wherevalues[] = $dataobject->{$field}; unset($dataobject->{$field}); } } else { throw new SQLException('the $where object is in a very odd form'); } static $table_columns; // Determine all the fields in the table if (is_array($table_columns) && isset($table_columns[$table])) { $columns = $table_columns[$table]; } else { if (!$columns = $db->MetaColumns(get_config('dbprefix') . $table)) { throw new SQLException('Could not get columns for table ' . $table); } $table_columns[$table] = $columns; } $data = (array)$dataobject; // Pull out data matching these fields $ddd = array(); foreach ($columns as $column) { if (!in_array($column->name, $wherefields) && array_key_exists($column->name, $data) ) { $ddd[$column->name] = $data[$column->name]; // PostgreSQL bytea support if (is_postgres() && $column->type == 'bytea') { $ddd[$column->name] = $db->BlobEncode($ddd[$column->name]); } } } // Construct SQL queries $numddd = count($ddd); $count = 0; $update = ''; foreach ($ddd as $key => $value) { $count++; $update .= db_quote_identifier($key) .' = ? '; if ($count < $numddd) { $update .= ', '; } $values[] = $value; } $whereclause = ''; $count = 0; $numddd = count($wherefields); foreach ($wherefields as $field) { $count++; $whereclause .= db_quote_identifier($field) .' = ? '; if ($count < $numddd) { $whereclause .= ' AND '; } } $sql = 'UPDATE '. db_table_name($table) .' SET '. $update .' WHERE ' . $whereclause; try { $stmt = $db->Prepare($sql); $rs = $db->Execute($stmt,array_merge($values, $wherevalues)); return true; } catch (ADODB_Exception $e) { throw new SQLException(create_sql_exception_message($e, $sql, array_merge($values, $wherevalues))); } } /** * Prepare a SQL WHERE clause to select records where the given fields match the given values. * * Prepares a where clause of the form * WHERE field1 = value1 AND field2 = value2 AND field3 = value3 * except that you need only specify as many arguments (zero to three) as you need. * * @param string $field1 the first field to check (optional). * @param string $value1 the value field1 must have (requred if field1 is given, else optional). * @param string $field2 the second field to check (optional). * @param string $value2 the value field2 must have (requred if field2 is given, else optional). * @param string $field3 the third field to check (optional). * @param string $value3 the value field3 must have (requred if field3 is given, else optional). */ function where_clause($field1='', $value1='', $field2='', $value2='', $field3='', $value3='') { if ($field1) { $select = "WHERE \"$field1\" = '$value1'"; if ($field2) { $select .= " AND \"$field2\" = '$value2'"; if ($field3) { $select .= " AND \"$field3\" = '$value3'"; } } } else { $select = ''; } return $select; } /** * Prepares a SQL WHERE clause to select records where the given fields match some values. * Uses ? as placeholders for prepared statments * * @param string $field1 the first field to check (optional). * @param string $field2 the second field to check (optional). * @param string $field3 the third field to check (optional). * @private */ function where_clause_prepared($field1='', $field2='', $field3='') { $select = ''; if (!empty($field1)) { $select = " WHERE \"$field1\" = ? "; if (!empty($field2)) { $select .= " AND \"$field2\" = ? "; if (!empty($field3)) { $select .= " AND \"$field3\" = ? "; } } } return $select; } /* * Useful helper function to only push optional values into the array * for prepared statements to avoid empty slots. * all parameters are optional. * * @private */ function where_values_prepared($value1=null, $value2=null, $value3=null, $value4=null) { $values = array(); if (isset($value1)) { $values[] = $value1; if (isset($value2)) { $values[] = $value2; if (isset($value3)) { $values[] = $value3; if (isset($value4)) { $values[] = $value4; } } } } return $values; } /** * Get the data type of a table column, using an ADOdb MetaType() call. * * @uses $db * @param string $table The name of the database table * @param string $column The name of the field in the table * @return string Field type or false if error */ function column_type($table, $column) { global $db; if(!$rs = $db->Execute('SELECT ' . $column.' FROM ' . db_table_name($table) . ' WHERE 1=2')) { return false; } $field = $rs->FetchField(0); return $rs->MetaType($field->type); } /** * This function will execute an array of SQL commands, returning * true/false if any error is found and stopping/continue as desired. * It's widely used by all the ddllib.php functions * * @private * @param array sqlarr array of sql statements to execute * @param boolean continue to specify if must continue on error (true) or stop (false) * @param boolean feedback to specify to show status info (true) or not (false) * @param boolean true if everything was ok, false if some error was found */ // in dml not ddl because we want to keep ddl 'clean upstream' - p function execute_sql_arr($sqlarr, $continue=true, $feedback=true) { if (!is_array($sqlarr)) { return false; } $status = true; foreach($sqlarr as $sql) { try { if (!execute_sql($sql)) { $status = false; if (!$continue) { break; } } } catch (Exception $e) { $status = false; if (!$continue) { break; } } } return $status; } /** * Format the timestamp $ts in the format the database accepts; this can be a * Unix integer timestamp or an ISO format Y-m-d H:i:s. Uses the fmtTimeStamp * field, which holds the format to use. If null or false or '' is passed in, * it will be converted to an SQL null. * * Returns the timestamp as a quoted string. * * @param ts a timestamp in Unix date time format. * * @return timestamp string in database timestamp format */ function db_format_timestamp($ts) { global $db; // Otherwise $db->BindTimeStamp() returns the string 'null', which is not // what we want if (empty($ts)) { return null; } return $db->BindTimeStamp($ts); } /** * Given a field name, this returns a function call suitable for the current * database to return a unix timestamp * * @param field the field to apply the function to * @param as what to name the field (optional, defaults to $field). If false, * no naming is done. * * @return timestamp string in database timestamp format */ function db_format_tsfield($field, $as = null) { $tsfield = ''; if (is_postgres()) { $tsfield = "FLOOR(EXTRACT(EPOCH FROM {$field} AT TIME ZONE CURRENT_SETTING('TIMEZONE')))"; } else if (is_mysql()) { $tsfield = "IF($field >= '1970-01-01', UNIX_TIMESTAMP($field), TIMESTAMPDIFF(SECOND, '1970-01-01', $field))"; } else { throw new SQLException('db_format_tsfield() is not implemented for your database engine (' . get_config('dbtype') . ')'); } if ($as === null) { $as = $field; } if (!empty($as)) { $tsfield .= " AS $as"; } return $tsfield; } /** * This function, called from setup.php includes all the configuration * needed to properly work agains any DB. It setups connection encoding * and some other variables. */ function configure_dbconnection() { global $db, $CFG; if (!empty($CFG->perftolog) || !empty($CFG->perftofoot)) { $db->fnExecute = 'increment_perf_db'; $db->fnCacheExecute = 'increment_perf_db_cached'; } $db->Execute("SET NAMES 'utf8'"); if (is_mysql()) { $db->Execute("SET SQL_MODE='POSTGRESQL'"); $db->Execute("SET CHARACTER SET utf8"); $db->Execute("SET SQL_BIG_SELECTS=1"); } $db->SetTransactionMode('READ COMMITTED'); if (!empty($CFG->dbtimezone)) { if (is_postgres()) { $db->Execute("SET SESSION TIME ZONE '{$CFG->dbtimezone}'"); } if (is_mysql()) { $db->Execute("SET time_zone='{$CFG->dbtimezone}'"); } } } function is_postgres() { return (strpos(get_config('dbtype'), 'postgres') === 0); } function is_mysql() { return (strpos(get_config('dbtype'), 'mysql') === 0); } /** * function to convert an array to * an array of placeholders (?) * with the right number of values * * @param array $array input array */ function db_array_to_ph($array) { return array_pad(array(), count($array), '?'); } // This is used by the SQLException, to detect if there is a transaction when // an error occurs, so it can roll the transaction back $GLOBALS['_TRANSACTION_LEVEL'] = 0; /** * This function starts a smart transaction * */ function db_begin() { global $db; $GLOBALS['_TRANSACTION_LEVEL']++; $db->StartTrans(); } /** * This function commits a smart transaction * * If the transaction has failed for any reason, an exception is thrown. * */ function db_commit() { global $db; $GLOBALS['_TRANSACTION_LEVEL']--; if ($GLOBALS['_TRANSACTION_LEVEL'] == 0) { if ($db->HasFailedTrans()) { $db->CompleteTrans(); throw new SQLException('Transaction Failed'); } } return $db->CompleteTrans(); } /** * This function rolls back a smart transaction */ function db_rollback() { global $db; $db->FailTrans(); for ($i = $GLOBALS['_TRANSACTION_LEVEL']; $i >= 0; $i--) { $db->CompleteTrans(); } $GLOBALS['_TRANSACTION_LEVEL'] = 0; } /** * This function escapes a single value suitable for insertion into an SQL * string * * @param string The value to escape * * @returns string the escaped value */ function db_quote($value) { global $db; return $db->Quote($value); } function create_sql_exception_message($e, $sql, $values) { $message = 'Failed to get a recordset: ' . $e->getMessage() . "Command was: $sql"; if (is_array($values) && count($values) > 0) { $message .= ' and values was (' . implode(',', $values) . ')'; } return $message; } function &increment_perf_db($db, $sql, $inputarray) { global $PERF; // searching for these rather than just select as subqueries may have select in them. if (preg_match('/^(update|insert|delete|alter|create)/i', trim($sql))) { $PERF->dbwrites++; } else { $PERF->dbreads++; } $null = null; return $null; } function increment_perf_db_cached($db, $secs2cache, $sql, $inputarray) { global $PERF; $PERF->dbcached++; } /** * Gives the caller the ability to disable logging of SQL exceptions in the * SQLException constructor. * * This is only used by the config loading code to prevent spurious errors * about the config table not existing going to the logs. If you are going to * use this function, you had better have a very good reason! * * @param bool $status Whether to ignore logging exceptions or not. If null, * you can retrieve the current value of this setting */ function db_ignore_sql_exceptions($status=null) { global $DB_IGNORE_SQL_EXCEPTIONS; // Initialise it if being called for the first time if ($DB_IGNORE_SQL_EXCEPTIONS === null) { $DB_IGNORE_SQL_EXCEPTIONS = false; } // Return the value if asked for if ($status === null) { return $DB_IGNORE_SQL_EXCEPTIONS; } $DB_IGNORE_SQL_EXCEPTIONS = (bool)$status; } /** * Returns the SQL keyword required to do LIKE in a case insensitive fashion. * * MySQL, as long as you use a case insensitive collation (as is the default), * uses LIKE for this, while real databases use ILIKE. */ function db_ilike() { if (is_mysql()) { return 'LIKE'; } return 'ILIKE'; } function db_random() { if (is_postgres()) { return 'RANDOM()'; } else if (is_mysql()) { return 'RAND()'; } } /** * Search and replace strings in the entire database. * Used for xhtml upgrade, but can also be used, for eg, to change links. * This function prints every query * (Adapted from moodle function of same name) * * @param array $replacearray keys = search, values = replacements. */ function db_replace($replacearray) { global $db; /// Turn off time limits, sometimes upgrades can be slow. @set_time_limit(0); @ob_implicit_flush(true); while(@ob_end_flush()); if (!$tables = $db->Metatables() ) { // No tables yet at all. return false; } foreach ($tables as $table) { if (in_array($table, array('config', 'adodb_logsql'))) { // Don't process these continue; } if ($columns = $db->MetaColumns($table, false)) { $db->debug = true; foreach ($columns as $column => $data) { if (in_array($data->type, array('text','mediumtext','longtext','varchar'))) { // Text stuff only foreach ($replacearray as $s => $r) { $db->execute('UPDATE ' . db_quote_table_placeholders('{' . $table . '}') . ' SET ' . db_quote_identifier($column) . ' = REPLACE(' . db_quote_identifier($column) . ', ?, ?)' , array($s, $r)); } } } $db->debug = false; } } } function db_interval($s) { if (is_postgres()) { return "INTERVAL '$s seconds'"; } if (is_mysql()) { return "INTERVAL $s SECOND"; } } function postgres_language_exists($language) { if (!is_postgres()) { throw new SQLException('postgres_language_exists() expects a postgres database'); } return get_field_sql('SELECT 1 FROM pg_catalog.pg_language WHERE lanname = ?', array($language)) == 1; } function postgres_create_language($language) { if (!is_postgres()) { throw new SQLException('postgres_create_language() expects a postgres database'); } // CREATE LANGUAGE fails if the language already exists if (postgres_language_exists($language)) { return true; } try { execute_sql("CREATE LANGUAGE $language;"); } catch (SQLException $e) { // Instead of dying with a generic SQLException, return false below and // let the caller decide what to do when the language cannot be created, // e.g. throw a ConfigSanityException with a useful error message. } return postgres_language_exists($language); } function mysql_has_trigger_privilege() { // Finding out whether the current user has trigger permission // seems to be quite hard. It would require parsing the output // from SHOW GRANTS. It's much easier to try and create one. execute_sql("CREATE TABLE {testtable} (testcolumn INT);"); try { execute_sql("CREATE TRIGGER {testtrigger} BEFORE INSERT ON {testtable} FOR EACH ROW BEGIN END;"); execute_sql("DROP TRIGGER {testtrigger};"); $success = true; } catch (SQLException $e) { // Instead of dying with a generic SQLException, return false below and // let the caller decide what to do when the trigger cannot be created, // e.g. throw a ConfigSanityException with a useful error message. $success = false; } execute_sql("DROP TABLE {testtable};"); return $success; } /** * Creates a database row trigger * * @param string $name trigger name * @param string $time trigger action time, e.g. AFTER * @param string $event trigger event, one of (INSERT, UPDATE, DELETE) * @param string $table table name * @param string $body function body */ function db_create_trigger($name, $time, $event, $table, $body) { if ($time != 'AFTER' || ($event != 'INSERT' && $event != 'UPDATE' && $event != 'DELETE')) { throw new SQLException("db_create_trigger() not implemented for $time $event"); } if (is_postgres()) { $functionname = $name . '_function'; $triggername = $name . '_trigger'; execute_sql(' CREATE FUNCTION {' . $functionname . '}() RETURNS TRIGGER AS $$ BEGIN ' . $body . ' RETURN NULL; END $$ LANGUAGE plpgsql; CREATE TRIGGER {' . $triggername . '} ' . $time . ' ' . $event . ' ON {' . $table . '} FOR EACH ROW EXECUTE PROCEDURE {' . $functionname . '}();' ); } else if (is_mysql()) { $triggername = $name . '_trigger'; execute_sql(' CREATE TRIGGER {' . $triggername . '} ' . $time . ' ' . $event . ' ON {' . $table . '} FOR EACH ROW BEGIN ' . $body . ' END' ); } else { throw new SQLException("db_create_trigger() is not implemented for your database engine"); } } function db_drop_trigger($name, $table) { if (is_postgres()) { $functionname = $name . '_function'; $triggername = $name . '_trigger'; execute_sql('DROP TRIGGER IF EXISTS {' . $triggername . '} ON {' . $table . '}'); execute_sql('DROP FUNCTION IF EXISTS {' . $functionname . '}()'); } else if (is_mysql()) { $triggername = $name . '_trigger'; execute_sql('DROP TRIGGER IF EXISTS {' . $triggername . '}'); } else { throw new SQLException("db_drop_trigger() is not implemented for your database engine"); } } function mysql_get_variable($name) { global $db; if (!is_mysql()) { throw new SQLException('mysql_get_variable() expects a mysql database'); } if (empty($name) || preg_match('/[^a-z_]/', $name)) { throw new SQLException('mysql_get_variable: invalid variable name'); } $result = $db->Execute("SHOW VARIABLES LIKE ?", array($name)); return $result->fields['Value']; }