Mercurial > hg > vamp-website

annotate forum/Sources/DbExtra-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 contains rarely used extended database functionality.
Chris@76 18
Chris@76 19 void db_extra_init()
Chris@76 20 - add this file's functions to the $smcFunc array.
Chris@76 21
Chris@76 22 resource smf_db_backup_table($table, $backup_table)
Chris@76 23 - backup $table to $backup_table.
Chris@76 24 - returns the request handle to the table creation query
Chris@76 25
Chris@76 26 string function smf_db_get_version()
Chris@76 27 - get the version number.
Chris@76 28
Chris@76 29 string db_insert_sql(string table_name)
Chris@76 30 - gets all the necessary INSERTs for the table named table_name.
Chris@76 31 - goes in 250 row segments.
Chris@76 32 - returns the query to insert the data back in.
Chris@76 33 - returns an empty string if the table was empty.
Chris@76 34
Chris@76 35 array smf_db_list_tables($db = false, $filter = false)
Chris@76 36 - lists all tables in the database
Chris@76 37 - could be filtered according to $filter
Chris@76 38 - returns an array of table names. (strings)
Chris@76 39
Chris@76 40 float smf_db_optimize_table($table)
Chris@76 41 - optimize a table
Chris@76 42 - $table - the table to be optimized
Chris@76 43 - returns how much it was gained
Chris@76 44
Chris@76 45 string db_table_sql(string table_name)
Chris@76 46 - dumps the CREATE for the specified table. (by table_name.)
Chris@76 47 - returns the CREATE statement.
Chris@76 48
Chris@76 49 */
Chris@76 50
Chris@76 51 // Add the file functions to the $smcFunc array.
Chris@76 52 function db_extra_init()
Chris@76 53 {
Chris@76 54 global $smcFunc;
Chris@76 55
Chris@76 56 if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
Chris@76 57 $smcFunc += array(
Chris@76 58 'db_backup_table' => 'smf_db_backup_table',
Chris@76 59 'db_optimize_table' => 'smf_db_optimize_table',
Chris@76 60 'db_insert_sql' => 'smf_db_insert_sql',
Chris@76 61 'db_table_sql' => 'smf_db_table_sql',
Chris@76 62 'db_list_tables' => 'smf_db_list_tables',
Chris@76 63 'db_get_backup' => 'smf_db_get_backup',
Chris@76 64 'db_get_version' => 'smf_db_get_version',
Chris@76 65 );
Chris@76 66 }
Chris@76 67
Chris@76 68 // Backup $table to $backup_table.
Chris@76 69 function smf_db_backup_table($table, $backup_table)
Chris@76 70 {
Chris@76 71 global $smcFunc, $db_prefix;
Chris@76 72
Chris@76 73 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 74
Chris@76 75 $result = $smcFunc['db_query']('', '
Chris@76 76 SELECT sql
Chris@76 77 FROM sqlite_master
Chris@76 78 WHERE type = {string:txttable}
Chris@76 79 AND name = {string:table}',
Chris@76 80 array(
Chris@76 81 'table' => $table,
Chris@76 82 'txttable' => 'table'
Chris@76 83 )
Chris@76 84 );
Chris@76 85 list ($create) = $smcFunc['db_fetch_row']($result);
Chris@76 86 $smcFunc['db_free_result']($result);
Chris@76 87
Chris@76 88 $create = preg_split('/[\n\r]/', $create);
Chris@76 89 $auto_inc = '';
Chris@76 90
Chris@76 91 // Remove the first line and check to see if the second one contain useless info.
Chris@76 92 unset($create[0]);
Chris@76 93 if (trim($create[1]) == '(')
Chris@76 94 unset($create[1]);
Chris@76 95 if (trim($create[count($create)]) == ')')
Chris@76 96 unset($create[count($create)]);
Chris@76 97
Chris@76 98 foreach ($create as $k => $l)
Chris@76 99 {
Chris@76 100 // Get the name of the auto_increment column.
Chris@76 101 if (strpos($l, 'primary') || strpos($l, 'PRIMARY'))
Chris@76 102 $auto_inc = trim($l);
Chris@76 103
Chris@76 104 // Skip everything but keys...
Chris@76 105 if ((strpos($l, 'KEY') !== false && strpos($l, 'PRIMARY KEY') === false) || strpos($l, $table) !== false || strpos(trim($l), 'PRIMARY KEY') === 0)
Chris@76 106 unset($create[$k]);
Chris@76 107 }
Chris@76 108
Chris@76 109 if (!empty($create))
Chris@76 110 $create = '(
Chris@76 111 ' . implode('
Chris@76 112 ', $create) . ')';
Chris@76 113 else
Chris@76 114 $create = '';
Chris@76 115
Chris@76 116 // Is there an extra junk at the end?
Chris@76 117 if (substr($create, -2, 1) == ',')
Chris@76 118 $create = substr($create, 0, -2) . ')';
Chris@76 119 if (substr($create, -2) == '))')
Chris@76 120 $create = substr($create, 0, -1);
Chris@76 121
Chris@76 122 $smcFunc['db_query']('', '
Chris@76 123 DROP TABLE {raw:backup_table}',
Chris@76 124 array(
Chris@76 125 'backup_table' => $backup_table,
Chris@76 126 'db_error_skip' => true,
Chris@76 127 )
Chris@76 128 );
Chris@76 129
Chris@76 130 $request = $smcFunc['db_quote']('
Chris@76 131 CREATE TABLE {raw:backup_table} {raw:create}',
Chris@76 132 array(
Chris@76 133 'backup_table' => $backup_table,
Chris@76 134 'create' => $create,
Chris@76 135 ));
Chris@76 136
Chris@76 137 $smcFunc['db_query']('', '
Chris@76 138 CREATE TABLE {raw:backup_table} {raw:create}',
Chris@76 139 array(
Chris@76 140 'backup_table' => $backup_table,
Chris@76 141 'create' => $create,
Chris@76 142 ));
Chris@76 143
Chris@76 144 $request = $smcFunc['db_query']('', '
Chris@76 145 INSERT INTO {raw:backup_table}
Chris@76 146 SELECT *
Chris@76 147 FROM {raw:table}',
Chris@76 148 array(
Chris@76 149 'backup_table' => $backup_table,
Chris@76 150 'table' => $table,
Chris@76 151 ));
Chris@76 152
Chris@76 153 return $request;
Chris@76 154 }
Chris@76 155
Chris@76 156 // Optimize a table - return data freed!
Chris@76 157 function smf_db_optimize_table($table)
Chris@76 158 {
Chris@76 159 global $smcFunc, $db_prefix;
Chris@76 160
Chris@76 161 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 162
Chris@76 163 $request = $smcFunc['db_query']('', '
Chris@76 164 VACUUM {raw:table}',
Chris@76 165 array(
Chris@76 166 'table' => $table,
Chris@76 167 )
Chris@76 168 );
Chris@76 169 if (!$request)
Chris@76 170 return -1;
Chris@76 171
Chris@76 172 $row = $smcFunc['db_fetch_assoc']($request);
Chris@76 173 $smcFunc['db_free_result']($request);
Chris@76 174
Chris@76 175 // The function returns nothing.
Chris@76 176 return 0;
Chris@76 177 }
Chris@76 178
Chris@76 179 // List all the tables in the database.
Chris@76 180 function smf_db_list_tables($db = false, $filter = false)
Chris@76 181 {
Chris@76 182 global $smcFunc;
Chris@76 183
Chris@76 184 $filter = $filter == false ? '' : ' AND name LIKE \'' . str_replace("\_", "_", $filter) . '\'';
Chris@76 185
Chris@76 186 $request = $smcFunc['db_query']('', '
Chris@76 187 SELECT name
Chris@76 188 FROM sqlite_master
Chris@76 189 WHERE type = {string:type}
Chris@76 190 {raw:filter}
Chris@76 191 ORDER BY name',
Chris@76 192 array(
Chris@76 193 'type' => 'table',
Chris@76 194 'filter' => $filter,
Chris@76 195 )
Chris@76 196 );
Chris@76 197 $tables = array();
Chris@76 198 while ($row = $smcFunc['db_fetch_row']($request))
Chris@76 199 $tables[] = $row[0];
Chris@76 200 $smcFunc['db_free_result']($request);
Chris@76 201
Chris@76 202 return $tables;
Chris@76 203 }
Chris@76 204
Chris@76 205 // Get the content (INSERTs) for a table.
Chris@76 206 function smf_db_insert_sql($tableName)
Chris@76 207 {
Chris@76 208 global $smcFunc, $db_prefix;
Chris@76 209
Chris@76 210 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76 211
Chris@76 212 // This will be handy...
Chris@76 213 $crlf = "\r\n";
Chris@76 214
Chris@76 215 // Get everything from the table.
Chris@76 216 $result = $smcFunc['db_query']('', '
Chris@76 217 SELECT *
Chris@76 218 FROM {raw:table}',
Chris@76 219 array(
Chris@76 220 'table' => $tableName,
Chris@76 221 )
Chris@76 222 );
Chris@76 223
Chris@76 224 // The number of rows, just for record keeping and breaking INSERTs up.
Chris@76 225 $num_rows = $smcFunc['db_num_rows']($result);
Chris@76 226
Chris@76 227 if ($num_rows == 0)
Chris@76 228 return '';
Chris@76 229
Chris@76 230 $fields = array_keys($smcFunc['db_fetch_assoc']($result));
Chris@76 231
Chris@76 232 // SQLite fetches an array so we need to filter out the numberic index for the columns.
Chris@76 233 foreach ($fields as $key => $name)
Chris@76 234 if (is_numeric($name))
Chris@76 235 unset($fields[$key]);
Chris@76 236
Chris@76 237 $smcFunc['db_data_seek']($result, 0);
Chris@76 238
Chris@76 239 // Start it off with the basic INSERT INTO.
Chris@76 240 $data = 'BEGIN TRANSACTION;' . $crlf;
Chris@76 241
Chris@76 242 // Loop through each row.
Chris@76 243 while ($row = $smcFunc['db_fetch_row']($result))
Chris@76 244 {
Chris@76 245 // Get the fields in this row...
Chris@76 246 $field_list = array();
Chris@76 247 for ($j = 0; $j < $smcFunc['db_num_fields']($result); $j++)
Chris@76 248 {
Chris@76 249 // Try to figure out the type of each field. (NULL, number, or 'string'.)
Chris@76 250 if (!isset($row[$j]))
Chris@76 251 $field_list[] = 'NULL';
Chris@76 252 elseif (is_numeric($row[$j]) && (int) $row[$j] == $row[$j])
Chris@76 253 $field_list[] = $row[$j];
Chris@76 254 else
Chris@76 255 $field_list[] = '\'' . $smcFunc['db_escape_string']($row[$j]) . '\'';
Chris@76 256 }
Chris@76 257 $data .= 'INSERT INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $field_list) . ');' . $crlf;
Chris@76 258 }
Chris@76 259 $smcFunc['db_free_result']($result);
Chris@76 260
Chris@76 261 // Return an empty string if there were no rows.
Chris@76 262 return $num_rows == 0 ? '' : $data . 'COMMIT;' . $crlf;
Chris@76 263 }
Chris@76 264
Chris@76 265 // Get the schema (CREATE) for a table.
Chris@76 266 function smf_db_table_sql($tableName)
Chris@76 267 {
Chris@76 268 global $smcFunc, $db_prefix;
Chris@76 269
Chris@76 270 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76 271
Chris@76 272 // This will be needed...
Chris@76 273 $crlf = "\r\n";
Chris@76 274
Chris@76 275 // Start the create table...
Chris@76 276 $schema_create = '';
Chris@76 277 $index_create = '';
Chris@76 278
Chris@76 279 // Let's get the create statement directly from SQLite.
Chris@76 280 $result = $smcFunc['db_query']('', '
Chris@76 281 SELECT sql
Chris@76 282 FROM sqlite_master
Chris@76 283 WHERE type = {string:type}
Chris@76 284 AND name = {string:table_name}',
Chris@76 285 array(
Chris@76 286 'type' => 'table',
Chris@76 287 'table_name' => $tableName,
Chris@76 288 )
Chris@76 289 );
Chris@76 290 list ($schema_create) = $smcFunc['db_fetch_row']($result);
Chris@76 291 $smcFunc['db_free_result']($result);
Chris@76 292
Chris@76 293 // Now the indexes.
Chris@76 294 $result = $smcFunc['db_query']('', '
Chris@76 295 SELECT sql
Chris@76 296 FROM sqlite_master
Chris@76 297 WHERE type = {string:type}
Chris@76 298 AND tbl_name = {string:table_name}',
Chris@76 299 array(
Chris@76 300 'type' => 'index',
Chris@76 301 'table_name' => $tableName,
Chris@76 302 )
Chris@76 303 );
Chris@76 304 $indexes = array();
Chris@76 305 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 306 if (trim($row['sql']) != '')
Chris@76 307 $indexes[] = $row['sql'];
Chris@76 308 $smcFunc['db_free_result']($result);
Chris@76 309
Chris@76 310 $index_create .= implode(';' . $crlf, $indexes);
Chris@76 311 $schema_create = empty($indexes) ? rtrim($schema_create) : $schema_create . ';' . $crlf . $crlf;
Chris@76 312
Chris@76 313 return $schema_create . $index_create;
Chris@76 314 }
Chris@76 315
Chris@76 316 // Get the version number.
Chris@76 317 function smf_db_get_version()
Chris@76 318 {
Chris@76 319 return sqlite_libversion();
Chris@76 320 }
Chris@76 321
Chris@76 322 // Simple return the database - and die!
Chris@76 323 function smf_db_get_backup()
Chris@76 324 {
Chris@76 325 global $db_name;
Chris@76 326
Chris@76 327 $db_file = substr($db_name, -3) === '.db' ? $db_name : $db_name . '.db';
Chris@76 328
Chris@76 329 // Add more info if zipped...
Chris@76 330 $ext = '';
Chris@76 331 if (isset($_REQUEST['compress']) && function_exists('gzencode'))
Chris@76 332 $ext = '.gz';
Chris@76 333
Chris@76 334 // Do the remaining headers.
Chris@76 335 header('Content-Disposition: attachment; filename="' . $db_file . $ext . '"');
Chris@76 336 header('Cache-Control: private');
Chris@76 337 header('Connection: close');
Chris@76 338
Chris@76 339 // Literally dump the contents. Try reading the file first.
Chris@76 340 if (@readfile($db_file) == null)
Chris@76 341 echo file_get_contents($db_file);
Chris@76 342
Chris@76 343 obExit(false);
Chris@76 344 }
Chris@76 345
Chris@76 346 ?>