Mercurial > hg > vamp-website

annotate forum/Sources/Subs-Db-sqlite.php @ 87:df86d318892b website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Link to SampleType doc
author Chris Cannam
date Mon, 10 Feb 2014 18:11:48 +0000
parents e3e11437ecea
children
rev   line source
Chris@76 1 <?php
Chris@76 2
Chris@76 3 /**
Chris@76 4 * Simple Machines Forum (SMF)
Chris@76 5 *
Chris@76 6 * @package SMF
Chris@76 7 * @author Simple Machines http://www.simplemachines.org
Chris@76 8 * @copyright 2011 Simple Machines
Chris@76 9 * @license http://www.simplemachines.org/about/smf/license.php BSD
Chris@76 10 *
Chris@76 11 * @version 2.0
Chris@76 12 */
Chris@76 13
Chris@76 14 if (!defined('SMF'))
Chris@76 15 die('Hacking attempt...');
Chris@76 16
Chris@76 17 /* This file has all the main functions in it that relate to the database.
Chris@76 18
Chris@76 19 smf_db_initiate() maps the implementations in this file (smf_db_function_name)
Chris@76 20 to the $smcFunc['db_function_name'] variable.
Chris@76 21
Chris@76 22 */
Chris@76 23
Chris@76 24 // Initialize the database settings
Chris@76 25 function smf_db_initiate($db_server, $db_name, $db_user, $db_passwd, $db_prefix, $db_options = array())
Chris@76 26 {
Chris@76 27 global $smcFunc, $mysql_set_mode, $db_in_transact, $sqlite_error;
Chris@76 28
Chris@76 29 // Map some database specific functions, only do this once.
Chris@76 30 if (!isset($smcFunc['db_fetch_assoc']) || $smcFunc['db_fetch_assoc'] != 'sqlite_fetch_array')
Chris@76 31 $smcFunc += array(
Chris@76 32 'db_query' => 'smf_db_query',
Chris@76 33 'db_quote' => 'smf_db_quote',
Chris@76 34 'db_fetch_assoc' => 'sqlite_fetch_array',
Chris@76 35 'db_fetch_row' => 'smf_db_fetch_row',
Chris@76 36 'db_free_result' => 'smf_db_free_result',
Chris@76 37 'db_insert' => 'smf_db_insert',
Chris@76 38 'db_insert_id' => 'smf_db_insert_id',
Chris@76 39 'db_num_rows' => 'sqlite_num_rows',
Chris@76 40 'db_data_seek' => 'sqlite_seek',
Chris@76 41 'db_num_fields' => 'sqlite_num_fields',
Chris@76 42 'db_escape_string' => 'sqlite_escape_string',
Chris@76 43 'db_unescape_string' => 'smf_db_unescape_string',
Chris@76 44 'db_server_info' => 'smf_db_libversion',
Chris@76 45 'db_affected_rows' => 'smf_db_affected_rows',
Chris@76 46 'db_transaction' => 'smf_db_transaction',
Chris@76 47 'db_error' => 'smf_db_last_error',
Chris@76 48 'db_select_db' => '',
Chris@76 49 'db_title' => 'SQLite',
Chris@76 50 'db_sybase' => true,
Chris@76 51 'db_case_sensitive' => true,
Chris@76 52 'db_escape_wildcard_string' => 'smf_db_escape_wildcard_string',
Chris@76 53 );
Chris@76 54
Chris@76 55 if (substr($db_name, -3) != '.db')
Chris@76 56 $db_name .= '.db';
Chris@76 57
Chris@76 58 if (!empty($db_options['persist']))
Chris@76 59 $connection = @sqlite_popen($db_name, 0666, $sqlite_error);
Chris@76 60 else
Chris@76 61 $connection = @sqlite_open($db_name, 0666, $sqlite_error);
Chris@76 62
Chris@76 63 // Something's wrong, show an error if its fatal (which we assume it is)
Chris@76 64 if (!$connection)
Chris@76 65 {
Chris@76 66 if (!empty($db_options['non_fatal']))
Chris@76 67 return null;
Chris@76 68 else
Chris@76 69 db_fatal_error();
Chris@76 70 }
Chris@76 71 $db_in_transact = false;
Chris@76 72
Chris@76 73 // This is frankly stupid - stop SQLite returning alias names!
Chris@76 74 @sqlite_query('PRAGMA short_column_names = 1', $connection);
Chris@76 75
Chris@76 76 // Make some user defined functions!
Chris@76 77 sqlite_create_function($connection, 'unix_timestamp', 'smf_udf_unix_timestamp', 0);
Chris@76 78 sqlite_create_function($connection, 'inet_aton', 'smf_udf_inet_aton', 1);
Chris@76 79 sqlite_create_function($connection, 'inet_ntoa', 'smf_udf_inet_ntoa', 1);
Chris@76 80 sqlite_create_function($connection, 'find_in_set', 'smf_udf_find_in_set', 2);
Chris@76 81 sqlite_create_function($connection, 'year', 'smf_udf_year', 1);
Chris@76 82 sqlite_create_function($connection, 'month', 'smf_udf_month', 1);
Chris@76 83 sqlite_create_function($connection, 'dayofmonth', 'smf_udf_dayofmonth', 1);
Chris@76 84 sqlite_create_function($connection, 'concat', 'smf_udf_concat');
Chris@76 85 sqlite_create_function($connection, 'locate', 'smf_udf_locate', 2);
Chris@76 86 sqlite_create_function($connection, 'regexp', 'smf_udf_regexp', 2);
Chris@76 87
Chris@76 88 return $connection;
Chris@76 89 }
Chris@76 90
Chris@76 91 // Extend the database functionality.
Chris@76 92 function db_extend($type = 'extra')
Chris@76 93 {
Chris@76 94 global $sourcedir, $db_type;
Chris@76 95
Chris@76 96 require_once($sourcedir . '/Db' . strtoupper($type[0]) . substr($type, 1) . '-' . $db_type . '.php');
Chris@76 97 $initFunc = 'db_' . $type . '_init';
Chris@76 98 $initFunc();
Chris@76 99 }
Chris@76 100
Chris@76 101 // SQLite doesn't actually need this!
Chris@76 102 function db_fix_prefix(&$db_prefix, $db_name)
Chris@76 103 {
Chris@76 104 return false;
Chris@76 105 }
Chris@76 106
Chris@76 107 function smf_db_replacement__callback($matches)
Chris@76 108 {
Chris@76 109 global $db_callback, $user_info, $db_prefix;
Chris@76 110
Chris@76 111 list ($values, $connection) = $db_callback;
Chris@76 112
Chris@76 113 if ($matches[1] === 'db_prefix')
Chris@76 114 return $db_prefix;
Chris@76 115
Chris@76 116 if ($matches[1] === 'query_see_board')
Chris@76 117 return $user_info['query_see_board'];
Chris@76 118
Chris@76 119 if ($matches[1] === 'query_wanna_see_board')
Chris@76 120 return $user_info['query_wanna_see_board'];
Chris@76 121
Chris@76 122 if (!isset($matches[2]))
Chris@76 123 smf_db_error_backtrace('Invalid value inserted or no type specified.', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 124
Chris@76 125 if (!isset($values[$matches[2]]))
Chris@76 126 smf_db_error_backtrace('The database value you\'re trying to insert does not exist: ' . htmlspecialchars($matches[2]), '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 127
Chris@76 128 $replacement = $values[$matches[2]];
Chris@76 129
Chris@76 130 switch ($matches[1])
Chris@76 131 {
Chris@76 132 case 'int':
Chris@76 133 if (!is_numeric($replacement) || (string) $replacement !== (string) (int) $replacement)
Chris@76 134 smf_db_error_backtrace('Wrong value type sent to the database. Integer expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 135 return (string) (int) $replacement;
Chris@76 136 break;
Chris@76 137
Chris@76 138 case 'string':
Chris@76 139 case 'text':
Chris@76 140 return sprintf('\'%1$s\'', sqlite_escape_string($replacement));
Chris@76 141 break;
Chris@76 142
Chris@76 143 case 'array_int':
Chris@76 144 if (is_array($replacement))
Chris@76 145 {
Chris@76 146 if (empty($replacement))
Chris@76 147 smf_db_error_backtrace('Database error, given array of integer values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 148
Chris@76 149 foreach ($replacement as $key => $value)
Chris@76 150 {
Chris@76 151 if (!is_numeric($value) || (string) $value !== (string) (int) $value)
Chris@76 152 smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 153
Chris@76 154 $replacement[$key] = (string) (int) $value;
Chris@76 155 }
Chris@76 156
Chris@76 157 return implode(', ', $replacement);
Chris@76 158 }
Chris@76 159 else
Chris@76 160 smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 161
Chris@76 162 break;
Chris@76 163
Chris@76 164 case 'array_string':
Chris@76 165 if (is_array($replacement))
Chris@76 166 {
Chris@76 167 if (empty($replacement))
Chris@76 168 smf_db_error_backtrace('Database error, given array of string values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 169
Chris@76 170 foreach ($replacement as $key => $value)
Chris@76 171 $replacement[$key] = sprintf('\'%1$s\'', sqlite_escape_string($value));
Chris@76 172
Chris@76 173 return implode(', ', $replacement);
Chris@76 174 }
Chris@76 175 else
Chris@76 176 smf_db_error_backtrace('Wrong value type sent to the database. Array of strings expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 177 break;
Chris@76 178
Chris@76 179 case 'date':
Chris@76 180 if (preg_match('~^(\d{4})-([0-1]?\d)-([0-3]?\d)$~', $replacement, $date_matches) === 1)
Chris@76 181 return sprintf('\'%04d-%02d-%02d\'', $date_matches[1], $date_matches[2], $date_matches[3]);
Chris@76 182 else
Chris@76 183 smf_db_error_backtrace('Wrong value type sent to the database. Date expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 184 break;
Chris@76 185
Chris@76 186 case 'float':
Chris@76 187 if (!is_numeric($replacement))
Chris@76 188 smf_db_error_backtrace('Wrong value type sent to the database. Floating point number expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 189 return (string) (float) $replacement;
Chris@76 190 break;
Chris@76 191
Chris@76 192 case 'identifier':
Chris@76 193 return '`' . strtr($replacement, array('`' => '', '.' => '')) . '`';
Chris@76 194 break;
Chris@76 195
Chris@76 196 case 'raw':
Chris@76 197 return $replacement;
Chris@76 198 break;
Chris@76 199
Chris@76 200 default:
Chris@76 201 smf_db_error_backtrace('Undefined type used in the database query. (' . $matches[1] . ':' . $matches[2] . ')', '', false, __FILE__, __LINE__);
Chris@76 202 break;
Chris@76 203 }
Chris@76 204 }
Chris@76 205
Chris@76 206 // Just like the db_query, escape and quote a string, but not executing the query.
Chris@76 207 function smf_db_quote($db_string, $db_values, $connection = null)
Chris@76 208 {
Chris@76 209 global $db_callback, $db_connection;
Chris@76 210
Chris@76 211 // Only bother if there's something to replace.
Chris@76 212 if (strpos($db_string, '{') !== false)
Chris@76 213 {
Chris@76 214 // This is needed by the callback function.
Chris@76 215 $db_callback = array($db_values, $connection == null ? $db_connection : $connection);
Chris@76 216
Chris@76 217 // Do the quoting and escaping
Chris@76 218 $db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
Chris@76 219
Chris@76 220 // Clear this global variable.
Chris@76 221 $db_callback = array();
Chris@76 222 }
Chris@76 223
Chris@76 224 return $db_string;
Chris@76 225 }
Chris@76 226
Chris@76 227 // Do a query. Takes care of errors too.
Chris@76 228 function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null)
Chris@76 229 {
Chris@76 230 global $db_cache, $db_count, $db_connection, $db_show_debug, $time_start;
Chris@76 231 global $db_unbuffered, $db_callback, $modSettings;
Chris@76 232
Chris@76 233 // Decide which connection to use.
Chris@76 234 $connection = $connection == null ? $db_connection : $connection;
Chris@76 235
Chris@76 236 // Special queries that need processing.
Chris@76 237 $replacements = array(
Chris@76 238 'birthday_array' => array(
Chris@76 239 '~DATE_FORMAT\(([^,]+),\s*([^\)]+)\s*\)~' => 'strftime($2, $1)'
Chris@76 240 ),
Chris@76 241 'substring' => array(
Chris@76 242 '~SUBSTRING~' => 'SUBSTR',
Chris@76 243 ),
Chris@76 244 'truncate_table' => array(
Chris@76 245 '~TRUNCATE~i' => 'DELETE FROM',
Chris@76 246 ),
Chris@76 247 'user_activity_by_time' => array(
Chris@76 248 '~HOUR\(FROM_UNIXTIME\((poster_time\s+\+\s+\{int:.+\})\)\)~' => 'strftime(\'%H\', datetime($1, \'unixepoch\'))',
Chris@76 249 ),
Chris@76 250 'unread_fetch_topic_count' => array(
Chris@76 251 '~\s*SELECT\sCOUNT\(DISTINCT\st\.id_topic\),\sMIN\(t\.id_last_msg\)(.+)$~is' => 'SELECT COUNT(id_topic), MIN(id_last_msg) FROM (SELECT DISTINCT t.id_topic, t.id_last_msg $1)',
Chris@76 252 ),
Chris@76 253 'alter_table_boards' => array(
Chris@76 254 '~(.+)~' => '',
Chris@76 255 ),
Chris@76 256 'get_random_number' => array(
Chris@76 257 '~RAND~' => 'RANDOM',
Chris@76 258 ),
Chris@76 259 'set_character_set' => array(
Chris@76 260 '~(.+)~' => '',
Chris@76 261 ),
Chris@76 262 'themes_count' => array(
Chris@76 263 '~\s*SELECT\sCOUNT\(DISTINCT\sid_member\)\sAS\svalue,\sid_theme.+FROM\s(.+themes)(.+)~is' => 'SELECT COUNT(id_member) AS value, id_theme FROM (SELECT DISTINCT id_member, id_theme, variable FROM $1) $2',
Chris@76 264 ),
Chris@76 265 'attach_download_increase' => array(
Chris@76 266 '~LOW_PRIORITY~' => '',
Chris@76 267 ),
Chris@76 268 'pm_conversation_list' => array(
Chris@76 269 '~ORDER BY id_pm~' => 'ORDER BY MAX(pm.id_pm)',
Chris@76 270 ),
Chris@76 271 'boardindex_fetch_boards' => array(
Chris@76 272 '~(.)$~' => '$1 ORDER BY b.board_order',
Chris@76 273 ),
Chris@76 274 'messageindex_fetch_boards' => array(
Chris@76 275 '~(.)$~' => '$1 ORDER BY b.board_order',
Chris@76 276 ),
Chris@76 277 'order_by_board_order' => array(
Chris@76 278 '~(.)$~' => '$1 ORDER BY b.board_order',
Chris@76 279 ),
Chris@76 280 'spider_check' => array(
Chris@76 281 '~(.)$~' => '$1 ORDER BY LENGTH(user_agent) DESC',
Chris@76 282 ),
Chris@76 283 );
Chris@76 284
Chris@76 285 if (isset($replacements[$identifier]))
Chris@76 286 $db_string = preg_replace(array_keys($replacements[$identifier]), array_values($replacements[$identifier]), $db_string);
Chris@76 287
Chris@76 288 // SQLite doesn't support count(distinct).
Chris@76 289 $db_string = trim($db_string);
Chris@76 290 $db_string = preg_replace('~^\s*SELECT\s+?COUNT\(DISTINCT\s+?(.+?)\)(\s*AS\s*(.+?))*\s*(FROM.+)~is', 'SELECT COUNT(*) $2 FROM (SELECT DISTINCT $1 $4)', $db_string);
Chris@76 291
Chris@76 292 // Or RLIKE.
Chris@76 293 $db_string = preg_replace('~AND\s*(.+?)\s*RLIKE\s*(\{string:.+?\})~', 'AND REGEXP(\1, \2)', $db_string);
Chris@76 294
Chris@76 295 // INSTR? No support for that buddy :(
Chris@76 296 if (preg_match('~INSTR\((.+?),\s(.+?)\)~', $db_string, $matches) === 1)
Chris@76 297 {
Chris@76 298 $db_string = preg_replace('~INSTR\((.+?),\s(.+?)\)~', '$1 LIKE $2', $db_string);
Chris@76 299 list(, $search) = explode(':', substr($matches[2], 1, -1));
Chris@76 300 $db_values[$search] = '%' . $db_values[$search] . '%';
Chris@76 301 }
Chris@76 302
Chris@76 303 // Lets remove ASC and DESC from GROUP BY clause.
Chris@76 304 if (preg_match('~GROUP BY .*? (?:ASC|DESC)~is', $db_string, $matches))
Chris@76 305 {
Chris@76 306 $replace = str_replace(array('ASC', 'DESC'), '', $matches[0]);
Chris@76 307 $db_string = str_replace($matches[0], $replace, $db_string);
Chris@76 308 }
Chris@76 309
Chris@76 310 // We need to replace the SUBSTRING in the sort identifier.
Chris@76 311 if ($identifier == 'substring_membergroups' && isset($db_values['sort']))
Chris@76 312 $db_values['sort'] = preg_replace('~SUBSTRING~', 'SUBSTR', $db_values['sort']);
Chris@76 313
Chris@76 314 // SQLite doesn't support TO_DAYS but has the julianday function which can be used in the same manner. But make sure it is being used to calculate a span.
Chris@76 315 $db_string = preg_replace('~\(TO_DAYS\(([^)]+)\) - TO_DAYS\(([^)]+)\)\) AS span~', '(julianday($1) - julianday($2)) AS span', $db_string);
Chris@76 316
Chris@76 317 // One more query....
Chris@76 318 $db_count = !isset($db_count) ? 1 : $db_count + 1;
Chris@76 319
Chris@76 320 if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && empty($db_values['security_override']))
Chris@76 321 smf_db_error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__);
Chris@76 322
Chris@76 323 if (empty($db_values['security_override']) && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false))
Chris@76 324 {
Chris@76 325 // Pass some values to the global space for use in the callback function.
Chris@76 326 $db_callback = array($db_values, $connection);
Chris@76 327
Chris@76 328 // Inject the values passed to this function.
Chris@76 329 $db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
Chris@76 330
Chris@76 331 // This shouldn't be residing in global space any longer.
Chris@76 332 $db_callback = array();
Chris@76 333 }
Chris@76 334
Chris@76 335 // Debugging.
Chris@76 336 if (isset($db_show_debug) && $db_show_debug === true)
Chris@76 337 {
Chris@76 338 // Get the file and line number this function was called.
Chris@76 339 list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
Chris@76 340
Chris@76 341 // Initialize $db_cache if not already initialized.
Chris@76 342 if (!isset($db_cache))
Chris@76 343 $db_cache = array();
Chris@76 344
Chris@76 345 if (!empty($_SESSION['debug_redirect']))
Chris@76 346 {
Chris@76 347 $db_cache = array_merge($_SESSION['debug_redirect'], $db_cache);
Chris@76 348 $db_count = count($db_cache) + 1;
Chris@76 349 $_SESSION['debug_redirect'] = array();
Chris@76 350 }
Chris@76 351
Chris@76 352 $st = microtime();
Chris@76 353 // Don't overload it.
Chris@76 354 $db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
Chris@76 355 $db_cache[$db_count]['f'] = $file;
Chris@76 356 $db_cache[$db_count]['l'] = $line;
Chris@76 357 $db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
Chris@76 358 }
Chris@76 359
Chris@76 360 $ret = @sqlite_query($db_string, $connection, SQLITE_BOTH, $err_msg);
Chris@76 361 if ($ret === false && empty($db_values['db_error_skip']))
Chris@76 362 $ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);
Chris@76 363
Chris@76 364 // Debugging.
Chris@76 365 if (isset($db_show_debug) && $db_show_debug === true)
Chris@76 366 $db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));
Chris@76 367
Chris@76 368 return $ret;
Chris@76 369 }
Chris@76 370
Chris@76 371 function smf_db_affected_rows($connection = null)
Chris@76 372 {
Chris@76 373 global $db_connection;
Chris@76 374
Chris@76 375 return sqlite_changes($connection == null ? $db_connection : $connection);
Chris@76 376 }
Chris@76 377
Chris@76 378 function smf_db_insert_id($table, $field = null, $connection = null)
Chris@76 379 {
Chris@76 380 global $db_connection, $db_prefix;
Chris@76 381
Chris@76 382 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 383
Chris@76 384 // SQLite doesn't need the table or field information.
Chris@76 385 return sqlite_last_insert_rowid($connection == null ? $db_connection : $connection);
Chris@76 386 }
Chris@76 387
Chris@76 388 // Keeps the connection handle.
Chris@76 389 function smf_db_last_error()
Chris@76 390 {
Chris@76 391 global $db_connection, $sqlite_error;
Chris@76 392
Chris@76 393 $query_errno = sqlite_last_error($db_connection);
Chris@76 394 return $query_errno || empty($sqlite_error) ? sqlite_error_string($query_errno) : $sqlite_error;
Chris@76 395 }
Chris@76 396
Chris@76 397 // Do a transaction.
Chris@76 398 function smf_db_transaction($type = 'commit', $connection = null)
Chris@76 399 {
Chris@76 400 global $db_connection, $db_in_transact;
Chris@76 401
Chris@76 402 // Decide which connection to use
Chris@76 403 $connection = $connection == null ? $db_connection : $connection;
Chris@76 404
Chris@76 405 if ($type == 'begin')
Chris@76 406 {
Chris@76 407 $db_in_transact = true;
Chris@76 408 return @sqlite_query('BEGIN', $connection);
Chris@76 409 }
Chris@76 410 elseif ($type == 'rollback')
Chris@76 411 {
Chris@76 412 $db_in_transact = false;
Chris@76 413 return @sqlite_query('ROLLBACK', $connection);
Chris@76 414 }
Chris@76 415 elseif ($type == 'commit')
Chris@76 416 {
Chris@76 417 $db_in_transact = false;
Chris@76 418 return @sqlite_query('COMMIT', $connection);
Chris@76 419 }
Chris@76 420
Chris@76 421 return false;
Chris@76 422 }
Chris@76 423
Chris@76 424 // Database error!
Chris@76 425 function smf_db_error($db_string, $connection = null)
Chris@76 426 {
Chris@76 427 global $txt, $context, $sourcedir, $webmaster_email, $modSettings;
Chris@76 428 global $forum_version, $db_connection, $db_last_error, $db_persist;
Chris@76 429 global $db_server, $db_user, $db_passwd, $db_name, $db_show_debug, $ssi_db_user, $ssi_db_passwd;
Chris@76 430 global $smcFunc;
Chris@76 431
Chris@76 432 // We'll try recovering the file and line number the original db query was called from.
Chris@76 433 list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
Chris@76 434
Chris@76 435 // Decide which connection to use
Chris@76 436 $connection = $connection == null ? $db_connection : $connection;
Chris@76 437
Chris@76 438 // This is the error message...
Chris@76 439 $query_errno = sqlite_last_error($connection);
Chris@76 440 $query_error = sqlite_error_string($query_errno);
Chris@76 441
Chris@76 442 // Get the extra error message.
Chris@76 443 $errStart = strrpos($db_string, '#!#');
Chris@76 444 $query_error .= '<br />' . substr($db_string, $errStart + 3);
Chris@76 445 $db_string = substr($db_string, 0, $errStart);
Chris@76 446
Chris@76 447 // Log the error.
Chris@76 448 if (function_exists('log_error'))
Chris@76 449 log_error($txt['database_error'] . ': ' . $query_error . (!empty($modSettings['enableErrorQueryLogging']) ? "\n\n" .$db_string : ''), 'database', $file, $line);
Chris@76 450
Chris@76 451 // Sqlite optimizing - the actual error message isn't helpful or user friendly.
Chris@76 452 if (strpos($query_error, 'no_access') !== false || strpos($query_error, 'database schema has changed') !== false)
Chris@76 453 {
Chris@76 454 if (!empty($context) && !empty($txt) && !empty($txt['error_sqlite_optimizing']))
Chris@76 455 fatal_error($txt['error_sqlite_optimizing'], false);
Chris@76 456 else
Chris@76 457 {
Chris@76 458 // Don't cache this page!
Chris@76 459 header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
Chris@76 460 header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
Chris@76 461 header('Cache-Control: no-cache');
Chris@76 462
Chris@76 463 // Send the right error codes.
Chris@76 464 header('HTTP/1.1 503 Service Temporarily Unavailable');
Chris@76 465 header('Status: 503 Service Temporarily Unavailable');
Chris@76 466 header('Retry-After: 3600');
Chris@76 467
Chris@76 468 die('Sqlite is optimizing the database, the forum can not be accessed until it has finished. Please try refreshing this page momentarily.');
Chris@76 469 }
Chris@76 470 }
Chris@76 471
Chris@76 472 // Nothing's defined yet... just die with it.
Chris@76 473 if (empty($context) || empty($txt))
Chris@76 474 die($query_error);
Chris@76 475
Chris@76 476 // Show an error message, if possible.
Chris@76 477 $context['error_title'] = $txt['database_error'];
Chris@76 478 if (allowedTo('admin_forum'))
Chris@76 479 $context['error_message'] = nl2br($query_error) . '<br />' . $txt['file'] . ': ' . $file . '<br />' . $txt['line'] . ': ' . $line;
Chris@76 480 else
Chris@76 481 $context['error_message'] = $txt['try_again'];
Chris@76 482
Chris@76 483 // A database error is often the sign of a database in need of updgrade. Check forum versions, and if not identical suggest an upgrade... (not for Demo/CVS versions!)
Chris@76 484 if (allowedTo('admin_forum') && !empty($forum_version) && $forum_version != 'SMF ' . @$modSettings['smfVersion'] && strpos($forum_version, 'Demo') === false && strpos($forum_version, 'CVS') === false)
Chris@76 485 $context['error_message'] .= '<br /><br />' . sprintf($txt['database_error_versions'], $forum_version, $modSettings['smfVersion']);
Chris@76 486
Chris@76 487 if (allowedTo('admin_forum') && isset($db_show_debug) && $db_show_debug === true)
Chris@76 488 {
Chris@76 489 $context['error_message'] .= '<br /><br />' . nl2br($db_string);
Chris@76 490 }
Chris@76 491
Chris@76 492 // It's already been logged... don't log it again.
Chris@76 493 fatal_error($context['error_message'], false);
Chris@76 494 }
Chris@76 495
Chris@76 496 // Insert some data...
Chris@76 497 function smf_db_insert($method = 'replace', $table, $columns, $data, $keys, $disable_trans = false, $connection = null)
Chris@76 498 {
Chris@76 499 global $db_in_transact, $db_connection, $smcFunc, $db_prefix;
Chris@76 500
Chris@76 501 $connection = $connection === null ? $db_connection : $connection;
Chris@76 502
Chris@76 503 if (empty($data))
Chris@76 504 return;
Chris@76 505
Chris@76 506 if (!is_array($data[array_rand($data)]))
Chris@76 507 $data = array($data);
Chris@76 508
Chris@76 509 // Replace the prefix holder with the actual prefix.
Chris@76 510 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 511
Chris@76 512 $priv_trans = false;
Chris@76 513 if (count($data) > 1 && !$db_in_transact && !$disable_trans)
Chris@76 514 {
Chris@76 515 $smcFunc['db_transaction']('begin', $connection);
Chris@76 516 $priv_trans = true;
Chris@76 517 }
Chris@76 518
Chris@76 519 if (!empty($data))
Chris@76 520 {
Chris@76 521 // Create the mold for a single row insert.
Chris@76 522 $insertData = '(';
Chris@76 523 foreach ($columns as $columnName => $type)
Chris@76 524 {
Chris@76 525 // Are we restricting the length?
Chris@76 526 if (strpos($type, 'string-') !== false)
Chris@76 527 $insertData .= sprintf('SUBSTR({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName);
Chris@76 528 else
Chris@76 529 $insertData .= sprintf('{%1$s:%2$s}, ', $type, $columnName);
Chris@76 530 }
Chris@76 531 $insertData = substr($insertData, 0, -2) . ')';
Chris@76 532
Chris@76 533 // Create an array consisting of only the columns.
Chris@76 534 $indexed_columns = array_keys($columns);
Chris@76 535
Chris@76 536 // Here's where the variables are injected to the query.
Chris@76 537 $insertRows = array();
Chris@76 538 foreach ($data as $dataRow)
Chris@76 539 $insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection);
Chris@76 540
Chris@76 541 foreach ($insertRows as $entry)
Chris@76 542 // Do the insert.
Chris@76 543 $smcFunc['db_query']('',
Chris@76 544 (($method === 'replace') ? 'REPLACE' : (' INSERT' . ($method === 'ignore' ? ' OR IGNORE' : ''))) . ' INTO ' . $table . '(' . implode(', ', $indexed_columns) . ')
Chris@76 545 VALUES
Chris@76 546 ' . $entry,
Chris@76 547 array(
Chris@76 548 'security_override' => true,
Chris@76 549 'db_error_skip' => $table === $db_prefix . 'log_errors',
Chris@76 550 ),
Chris@76 551 $connection
Chris@76 552 );
Chris@76 553 }
Chris@76 554
Chris@76 555 if ($priv_trans)
Chris@76 556 $smcFunc['db_transaction']('commit', $connection);
Chris@76 557 }
Chris@76 558
Chris@76 559 // Doesn't do anything on sqlite!
Chris@76 560 function smf_db_free_result($handle = false)
Chris@76 561 {
Chris@76 562 return true;
Chris@76 563 }
Chris@76 564
Chris@76 565 // Make sure we return no string indexes!
Chris@76 566 function smf_db_fetch_row($handle)
Chris@76 567 {
Chris@76 568 return sqlite_fetch_array($handle, SQLITE_NUM);
Chris@76 569 }
Chris@76 570
Chris@76 571 // Unescape an escaped string!
Chris@76 572 function smf_db_unescape_string($string)
Chris@76 573 {
Chris@76 574 return strtr($string, array('\'\'' => '\''));
Chris@76 575 }
Chris@76 576
Chris@76 577 // This function tries to work out additional error information from a back trace.
Chris@76 578 function smf_db_error_backtrace($error_message, $log_message = '', $error_type = false, $file = null, $line = null)
Chris@76 579 {
Chris@76 580 if (empty($log_message))
Chris@76 581 $log_message = $error_message;
Chris@76 582
Chris@76 583 if (function_exists('debug_backtrace'))
Chris@76 584 {
Chris@76 585 foreach (debug_backtrace() as $step)
Chris@76 586 {
Chris@76 587 // Found it?
Chris@76 588 if (strpos($step['function'], 'query') === false && !in_array(substr($step['function'], 0, 7), array('smf_db_', 'preg_re', 'db_erro', 'call_us')) && substr($step['function'], 0, 2) != '__')
Chris@76 589 {
Chris@76 590 $log_message .= '<br />Function: ' . $step['function'];
Chris@76 591 break;
Chris@76 592 }
Chris@76 593
Chris@76 594 if (isset($step['line']))
Chris@76 595 {
Chris@76 596 $file = $step['file'];
Chris@76 597 $line = $step['line'];
Chris@76 598 }
Chris@76 599 }
Chris@76 600 }
Chris@76 601
Chris@76 602 // A special case - we want the file and line numbers for debugging.
Chris@76 603 if ($error_type == 'return')
Chris@76 604 return array($file, $line);
Chris@76 605
Chris@76 606 // Is always a critical error.
Chris@76 607 if (function_exists('log_error'))
Chris@76 608 log_error($log_message, 'critical', $file, $line);
Chris@76 609
Chris@76 610 if (function_exists('fatal_error'))
Chris@76 611 {
Chris@76 612 fatal_error($error_message, $error_type);
Chris@76 613
Chris@76 614 // Cannot continue...
Chris@76 615 exit;
Chris@76 616 }
Chris@76 617 elseif ($error_type)
Chris@76 618 trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : ''), $error_type);
Chris@76 619 else
Chris@76 620 trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : ''));
Chris@76 621 }
Chris@76 622
Chris@76 623 // Emulate UNIX_TIMESTAMP.
Chris@76 624 function smf_udf_unix_timestamp()
Chris@76 625 {
Chris@76 626 return strftime('%s', 'now');
Chris@76 627 }
Chris@76 628
Chris@76 629 // Emulate INET_ATON.
Chris@76 630 function smf_udf_inet_aton($ip)
Chris@76 631 {
Chris@76 632 $chunks = explode('.', $ip);
Chris@76 633 return @$chunks[0] * pow(256, 3) + @$chunks[1] * pow(256, 2) + @$chunks[2] * 256 + @$chunks[3];
Chris@76 634 }
Chris@76 635
Chris@76 636 // Emulate INET_NTOA.
Chris@76 637 function smf_udf_inet_ntoa($n)
Chris@76 638 {
Chris@76 639 $t = array(0, 0, 0, 0);
Chris@76 640 $msk = 16777216.0;
Chris@76 641 $n += 0.0;
Chris@76 642 if ($n < 1)
Chris@76 643 return '0.0.0.0';
Chris@76 644
Chris@76 645 for ($i = 0; $i < 4; $i++)
Chris@76 646 {
Chris@76 647 $k = (int) ($n / $msk);
Chris@76 648 $n -= $msk * $k;
Chris@76 649 $t[$i] = $k;
Chris@76 650 $msk /= 256.0;
Chris@76 651 };
Chris@76 652
Chris@76 653 $a = join('.', $t);
Chris@76 654 return $a;
Chris@76 655 }
Chris@76 656
Chris@76 657 // Emulate FIND_IN_SET.
Chris@76 658 function smf_udf_find_in_set($find, $groups)
Chris@76 659 {
Chris@76 660 foreach (explode(',', $groups) as $key => $group)
Chris@76 661 {
Chris@76 662 if ($group == $find)
Chris@76 663 return $key + 1;
Chris@76 664 }
Chris@76 665
Chris@76 666 return 0;
Chris@76 667 }
Chris@76 668
Chris@76 669 // Emulate YEAR.
Chris@76 670 function smf_udf_year($date)
Chris@76 671 {
Chris@76 672 return substr($date, 0, 4);
Chris@76 673 }
Chris@76 674
Chris@76 675 // Emulate MONTH.
Chris@76 676 function smf_udf_month($date)
Chris@76 677 {
Chris@76 678 return substr($date, 5, 2);
Chris@76 679 }
Chris@76 680
Chris@76 681 // Emulate DAYOFMONTH.
Chris@76 682 function smf_udf_dayofmonth($date)
Chris@76 683 {
Chris@76 684 return substr($date, 8, 2);
Chris@76 685 }
Chris@76 686
Chris@76 687 // We need this since sqlite_libversion() doesn't take any parameters.
Chris@76 688 function smf_db_libversion($void = null)
Chris@76 689 {
Chris@76 690 return sqlite_libversion();
Chris@76 691 }
Chris@76 692
Chris@76 693 // This function uses variable argument lists so that it can handle more then two parameters.
Chris@76 694 // Emulates the CONCAT function.
Chris@76 695 function smf_udf_concat()
Chris@76 696 {
Chris@76 697 // Since we didn't specify any arguments we must get them from PHP.
Chris@76 698 $args = func_get_args();
Chris@76 699
Chris@76 700 // It really doesn't matter if there were 0 to 100 arguments, just slap them all together.
Chris@76 701 return implode('', $args);
Chris@76 702 }
Chris@76 703
Chris@76 704 // We need to use PHP to locate the position in the string.
Chris@76 705 function smf_udf_locate($find, $string)
Chris@76 706 {
Chris@76 707 return strpos($string, $find);
Chris@76 708 }
Chris@76 709
Chris@76 710 // This is used to replace RLIKE.
Chris@76 711 function smf_udf_regexp($exp, $search)
Chris@76 712 {
Chris@76 713 if (preg_match($exp, $match))
Chris@76 714 return 1;
Chris@76 715 return 0;
Chris@76 716 }
Chris@76 717
Chris@76 718 // Escape the LIKE wildcards so that they match the character and not the wildcard.
Chris@76 719 // The optional second parameter turns human readable wildcards into SQL wildcards.
Chris@76 720 function smf_db_escape_wildcard_string($string, $translate_human_wildcards=false)
Chris@76 721 {
Chris@76 722 $replacements = array(
Chris@76 723 '%' => '\%',
Chris@76 724 '\\' => '\\\\',
Chris@76 725 );
Chris@76 726
Chris@76 727 if ($translate_human_wildcards)
Chris@76 728 $replacements += array(
Chris@76 729 '*' => '%',
Chris@76 730 );
Chris@76 731
Chris@76 732 return strtr($string, $replacements);
Chris@76 733 }
Chris@76 734 ?>