Mercurial > hg > vamp-website

comparison forum/Sources/DbExtra-mysql.php @ 76:e3e11437ecea website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add forum code
author Chris Cannam
date Sun, 07 Jul 2013 11:25:48 +0200
parents
children
comparison
equal deleted inserted replaced
75:72f59aa7e503 76:e3e11437ecea
1 <?php
2
3 /**
4 * Simple Machines Forum (SMF)
5 *
6 * @package SMF
7 * @author Simple Machines http://www.simplemachines.org
8 * @copyright 2011 Simple Machines
9 * @license http://www.simplemachines.org/about/smf/license.php BSD
10 *
11 * @version 2.0
12 */
13
14 if (!defined('SMF'))
15 die('Hacking attempt...');
16
17 /* This file contains rarely used extended database functionality.
18
19 void db_extra_init()
20 - add this file's functions to the $smcFunc array.
21
22 resource smf_db_backup_table($table, $backup_table)
23 - backup $table to $backup_table.
24 - returns the request handle to the table creation query
25
26 string function smf_db_get_version()
27 - get the version number.
28
29 string db_insert_sql(string table_name)
30 - gets all the necessary INSERTs for the table named table_name.
31 - goes in 250 row segments.
32 - returns the query to insert the data back in.
33 - returns an empty string if the table was empty.
34
35 array smf_db_list_tables($db = false, $filter = false)
36 - lists all tables in the database
37 - could be filtered according to $filter
38 - returns an array of table names. (strings)
39
40 float smf_db_optimize_table($table)
41 - optimize a table
42 - $table - the table to be optimized
43 - returns how much it was gained
44
45 string db_table_sql(string table_name)
46 - dumps the CREATE for the specified table. (by table_name.)
47 - returns the CREATE statement.
48
49 */
50
51 // Add the file functions to the $smcFunc array.
52 function db_extra_init()
53 {
54 global $smcFunc;
55
56 if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
57 $smcFunc += array(
58 'db_backup_table' => 'smf_db_backup_table',
59 'db_optimize_table' => 'smf_db_optimize_table',
60 'db_insert_sql' => 'smf_db_insert_sql',
61 'db_table_sql' => 'smf_db_table_sql',
62 'db_list_tables' => 'smf_db_list_tables',
63 'db_get_version' => 'smf_db_get_version',
64 );
65 }
66
67 // Backup $table to $backup_table.
68 function smf_db_backup_table($table, $backup_table)
69 {
70 global $smcFunc, $db_prefix;
71
72 $table = str_replace('{db_prefix}', $db_prefix, $table);
73
74 // First, get rid of the old table.
75 $smcFunc['db_query']('', '
76 DROP TABLE IF EXISTS {raw:backup_table}',
77 array(
78 'backup_table' => $backup_table,
79 )
80 );
81
82 // Can we do this the quick way?
83 $result = $smcFunc['db_query']('', '
84 CREATE TABLE {raw:backup_table} LIKE {raw:table}',
85 array(
86 'backup_table' => $backup_table,
87 'table' => $table
88 ));
89 // If this failed, we go old school.
90 if ($result)
91 {
92 $request = $smcFunc['db_query']('', '
93 INSERT INTO {raw:backup_table}
94 SELECT *
95 FROM {raw:table}',
96 array(
97 'backup_table' => $backup_table,
98 'table' => $table
99 ));
100
101 // Old school or no school?
102 if ($request)
103 return $request;
104 }
105
106 // At this point, the quick method failed.
107 $result = $smcFunc['db_query']('', '
108 SHOW CREATE TABLE {raw:table}',
109 array(
110 'table' => $table,
111 )
112 );
113 list (, $create) = $smcFunc['db_fetch_row']($result);
114 $smcFunc['db_free_result']($result);
115
116 $create = preg_split('/[\n\r]/', $create);
117
118 $auto_inc = '';
119 // Default engine type.
120 $engine = 'MyISAM';
121 $charset = '';
122 $collate = '';
123
124 foreach ($create as $k => $l)
125 {
126 // Get the name of the auto_increment column.
127 if (strpos($l, 'auto_increment'))
128 $auto_inc = trim($l);
129
130 // For the engine type, see if we can work out what it is.
131 if (strpos($l, 'ENGINE') !== false || strpos($l, 'TYPE') !== false)
132 {
133 // Extract the engine type.
134 preg_match('~(ENGINE|TYPE)=(\w+)(\sDEFAULT)?(\sCHARSET=(\w+))?(\sCOLLATE=(\w+))?~', $l, $match);
135
136 if (!empty($match[1]))
137 $engine = $match[1];
138
139 if (!empty($match[2]))
140 $engine = $match[2];
141
142 if (!empty($match[5]))
143 $charset = $match[5];
144
145 if (!empty($match[7]))
146 $collate = $match[7];
147 }
148
149 // Skip everything but keys...
150 if (strpos($l, 'KEY') === false)
151 unset($create[$k]);
152 }
153
154 if (!empty($create))
155 $create = '(
156 ' . implode('
157 ', $create) . ')';
158 else
159 $create = '';
160
161 $request = $smcFunc['db_query']('', '
162 CREATE TABLE {raw:backup_table} {raw:create}
163 ENGINE={raw:engine}' . (empty($charset) ? '' : ' CHARACTER SET {raw:charset}' . (empty($collate) ? '' : ' COLLATE {raw:collate}')) . '
164 SELECT *
165 FROM {raw:table}',
166 array(
167 'backup_table' => $backup_table,
168 'table' => $table,
169 'create' => $create,
170 'engine' => $engine,
171 'charset' => empty($charset) ? '' : $charset,
172 'collate' => empty($collate) ? '' : $collate,
173 )
174 );
175
176 if ($auto_inc != '')
177 {
178 if (preg_match('~\`(.+?)\`\s~', $auto_inc, $match) != 0 && substr($auto_inc, -1, 1) == ',')
179 $auto_inc = substr($auto_inc, 0, -1);
180
181 $smcFunc['db_query']('', '
182 ALTER TABLE {raw:backup_table}
183 CHANGE COLUMN {raw:column_detail} {raw:auto_inc}',
184 array(
185 'backup_table' => $backup_table,
186 'column_detail' => $match[1],
187 'auto_inc' => $auto_inc,
188 )
189 );
190 }
191
192 return $request;
193 }
194
195 // Optimize a table - return data freed!
196 function smf_db_optimize_table($table)
197 {
198 global $smcFunc, $db_name, $db_prefix;
199
200 $table = str_replace('{db_prefix}', $db_prefix, $table);
201
202 // Get how much overhead there is.
203 $request = $smcFunc['db_query']('', '
204 SHOW TABLE STATUS LIKE {string:table_name}',
205 array(
206 'table_name' => str_replace('_', '\_', $table),
207 )
208 );
209 $row = $smcFunc['db_fetch_assoc']($request);
210 $smcFunc['db_free_result']($request);
211
212 $data_before = isset($row['Data_free']) ? $row['Data_free'] : 0;
213 $request = $smcFunc['db_query']('', '
214 OPTIMIZE TABLE `{raw:table}`',
215 array(
216 'table' => $table,
217 )
218 );
219 if (!$request)
220 return -1;
221
222 // How much left?
223 $request = $smcFunc['db_query']('', '
224 SHOW TABLE STATUS LIKE {string:table}',
225 array(
226 'table' => str_replace('_', '\_', $table),
227 )
228 );
229 $row = $smcFunc['db_fetch_assoc']($request);
230 $smcFunc['db_free_result']($request);
231
232 $total_change = isset($row['Data_free']) && $data_before > $row['Data_free'] ? $data_before / 1024 : 0;
233
234 return $total_change;
235 }
236
237 // List all the tables in the database.
238 function smf_db_list_tables($db = false, $filter = false)
239 {
240 global $db_name, $smcFunc;
241
242 $db = $db == false ? $db_name : $db;
243 $db = trim($db);
244 $filter = $filter == false ? '' : ' LIKE \'' . $filter . '\'';
245
246 $request = $smcFunc['db_query']('', '
247 SHOW TABLES
248 FROM `{raw:db}`
249 {raw:filter}',
250 array(
251 'db' => $db[0] == '`' ? strtr($db, array('`' => '')) : $db,
252 'filter' => $filter,
253 )
254 );
255 $tables = array();
256 while ($row = $smcFunc['db_fetch_row']($request))
257 $tables[] = $row[0];
258 $smcFunc['db_free_result']($request);
259
260 return $tables;
261 }
262
263 // Get the content (INSERTs) for a table.
264 function smf_db_insert_sql($tableName)
265 {
266 global $smcFunc, $db_prefix;
267
268 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
269
270 // This will be handy...
271 $crlf = "\r\n";
272
273 // Get everything from the table.
274 $result = $smcFunc['db_query']('', '
275 SELECT /*!40001 SQL_NO_CACHE */ *
276 FROM `{raw:table}`',
277 array(
278 'table' => $tableName,
279 )
280 );
281
282 // The number of rows, just for record keeping and breaking INSERTs up.
283 $num_rows = $smcFunc['db_num_rows']($result);
284 $current_row = 0;
285
286 if ($num_rows == 0)
287 return '';
288
289 $fields = array_keys($smcFunc['db_fetch_assoc']($result));
290 $smcFunc['db_data_seek']($result, 0);
291
292 // Start it off with the basic INSERT INTO.
293 $data = 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
294
295 // Loop through each row.
296 while ($row = $smcFunc['db_fetch_row']($result))
297 {
298 $current_row++;
299
300 // Get the fields in this row...
301 $field_list = array();
302 for ($j = 0; $j < $smcFunc['db_num_fields']($result); $j++)
303 {
304 // Try to figure out the type of each field. (NULL, number, or 'string'.)
305 if (!isset($row[$j]))
306 $field_list[] = 'NULL';
307 elseif (is_numeric($row[$j]) && (int) $row[$j] == $row[$j])
308 $field_list[] = $row[$j];
309 else
310 $field_list[] = '\'' . $smcFunc['db_escape_string']($row[$j]) . '\'';
311 }
312
313 // 'Insert' the data.
314 $data .= '(' . implode(', ', $field_list) . ')';
315
316 // All done!
317 if ($current_row == $num_rows)
318 $data .= ';' . $crlf;
319 // Start a new INSERT statement after every 250....
320 elseif ($current_row > 249 && $current_row % 250 == 0)
321 $data .= ';' . $crlf . 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
322 // Otherwise, go to the next line.
323 else
324 $data .= ',' . $crlf . "\t";
325 }
326 $smcFunc['db_free_result']($result);
327
328 // Return an empty string if there were no rows.
329 return $num_rows == 0 ? '' : $data;
330 }
331
332 // Get the schema (CREATE) for a table.
333 function smf_db_table_sql($tableName)
334 {
335 global $smcFunc, $db_prefix;
336
337 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
338
339 // This will be needed...
340 $crlf = "\r\n";
341
342 // Drop it if it exists.
343 $schema_create = 'DROP TABLE IF EXISTS `' . $tableName . '`;' . $crlf . $crlf;
344
345 // Start the create table...
346 $schema_create .= 'CREATE TABLE `' . $tableName . '` (' . $crlf;
347
348 // Find all the fields.
349 $result = $smcFunc['db_query']('', '
350 SHOW FIELDS
351 FROM `{raw:table}`',
352 array(
353 'table' => $tableName,
354 )
355 );
356 while ($row = $smcFunc['db_fetch_assoc']($result))
357 {
358 // Make the CREATE for this column.
359 $schema_create .= ' `' . $row['Field'] . '` ' . $row['Type'] . ($row['Null'] != 'YES' ? ' NOT NULL' : '');
360
361 // Add a default...?
362 if (!empty($row['Default']) || $row['Null'] !== 'YES')
363 {
364 // Make a special case of auto-timestamp.
365 if ($row['Default'] == 'CURRENT_TIMESTAMP')
366 $schema_create .= ' /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */';
367 // Text shouldn't have a default.
368 elseif ($row['Default'] !== null)
369 {
370 // If this field is numeric the default needs no escaping.
371 $type = strtolower($row['Type']);
372 $isNumericColumn = strpos($type, 'int') !== false || strpos($type, 'bool') !== false || strpos($type, 'bit') !== false || strpos($type, 'float') !== false || strpos($type, 'double') !== false || strpos($type, 'decimal') !== false;
373
374 $schema_create .= ' default ' . ($isNumericColumn ? $row['Default'] : '\'' . $smcFunc['db_escape_string']($row['Default']) . '\'');
375 }
376 }
377
378 // And now any extra information. (such as auto_increment.)
379 $schema_create .= ($row['Extra'] != '' ? ' ' . $row['Extra'] : '') . ',' . $crlf;
380 }
381 $smcFunc['db_free_result']($result);
382
383 // Take off the last comma.
384 $schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
385
386 // Find the keys.
387 $result = $smcFunc['db_query']('', '
388 SHOW KEYS
389 FROM `{raw:table}`',
390 array(
391 'table' => $tableName,
392 )
393 );
394 $indexes = array();
395 while ($row = $smcFunc['db_fetch_assoc']($result))
396 {
397 // IS this a primary key, unique index, or regular index?
398 $row['Key_name'] = $row['Key_name'] == 'PRIMARY' ? 'PRIMARY KEY' : (empty($row['Non_unique']) ? 'UNIQUE ' : ($row['Comment'] == 'FULLTEXT' || (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT') ? 'FULLTEXT ' : 'KEY ')) . '`' . $row['Key_name'] . '`';
399
400 // Is this the first column in the index?
401 if (empty($indexes[$row['Key_name']]))
402 $indexes[$row['Key_name']] = array();
403
404 // A sub part, like only indexing 15 characters of a varchar.
405 if (!empty($row['Sub_part']))
406 $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`(' . $row['Sub_part'] . ')';
407 else
408 $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`';
409 }
410 $smcFunc['db_free_result']($result);
411
412 // Build the CREATEs for the keys.
413 foreach ($indexes as $keyname => $columns)
414 {
415 // Ensure the columns are in proper order.
416 ksort($columns);
417
418 $schema_create .= ',' . $crlf . ' ' . $keyname . ' (' . implode($columns, ', ') . ')';
419 }
420
421 // Now just get the comment and type... (MyISAM, etc.)
422 $result = $smcFunc['db_query']('', '
423 SHOW TABLE STATUS
424 LIKE {string:table}',
425 array(
426 'table' => strtr($tableName, array('_' => '\\_', '%' => '\\%')),
427 )
428 );
429 $row = $smcFunc['db_fetch_assoc']($result);
430 $smcFunc['db_free_result']($result);
431
432 // Probably MyISAM.... and it might have a comment.
433 $schema_create .= $crlf . ') ENGINE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) . ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
434
435 return $schema_create;
436 }
437
438 // Get the version number.
439 function smf_db_get_version()
440 {
441 global $smcFunc;
442
443 $request = $smcFunc['db_query']('', '
444 SELECT VERSION()',
445 array(
446 )
447 );
448 list ($ver) = $smcFunc['db_fetch_row']($request);
449 $smcFunc['db_free_result']($request);
450
451 return $ver;
452 }
453
454 ?>