Mercurial > hg > vamp-website

comparison forum/Sources/DbPackages-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 database functionality specifically designed for packages to utilize.
18
19 bool smf_db_create_table(string table_name, array columns, array indexes = array(),
20 array parameters = array(), string if_exists = 'ignore')
21 - Can be used to create a table without worrying about schema compatabilities.
22 - If the table exists will, by default, do nothing.
23 - Builds table with columns as passed to it - at least one column must be sent.
24 The columns array should have one sub-array for each column - these sub arrays contain:
25 + 'name' = Column name
26 + 'type' = Type of column - values from (smallint,mediumint,int,text,varchar,char,tinytext,mediumtext,largetext)
27 + 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc. If not
28 set SMF will pick a size.
29 + 'default' = Default value - do not set if no default required.
30 + 'null' => Can it be null (true or false) - if not set default will be false.
31 + 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set
32 from what it should begin counting.
33 - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
34 + 'name' => Index name (If left empty SMF will generate).
35 + 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
36 + 'columns' => Array containing columns that form part of key - in the order the index is to be created.
37 - parameters: (None yet)
38 - if_exists values:
39 + 'ignore' will do nothing if the table exists. (And will return true)
40 + 'overwrite' will drop any existing table of the same name.
41 + 'error' will return false if the table already exists.
42
43 */
44
45 // Add the file functions to the $smcFunc array.
46 function db_packages_init()
47 {
48 global $smcFunc, $reservedTables, $db_package_log, $db_prefix;
49
50 if (!isset($smcFunc['db_create_table']) || $smcFunc['db_create_table'] != 'smf_db_create_table')
51 {
52 $smcFunc += array(
53 'db_add_column' => 'smf_db_add_column',
54 'db_add_index' => 'smf_db_add_index',
55 'db_calculate_type' => 'smf_db_calculate_type',
56 'db_change_column' => 'smf_db_change_column',
57 'db_create_table' => 'smf_db_create_table',
58 'db_drop_table' => 'smf_db_drop_table',
59 'db_table_structure' => 'smf_db_table_structure',
60 'db_list_columns' => 'smf_db_list_columns',
61 'db_list_indexes' => 'smf_db_list_indexes',
62 'db_remove_column' => 'smf_db_remove_column',
63 'db_remove_index' => 'smf_db_remove_index',
64 );
65 $db_package_log = array();
66 }
67
68 // We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up!
69 $reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
70 'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
71 'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
72 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
73 'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
74 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
75 'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
76 'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
77 'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
78 'themes', 'topics');
79 foreach ($reservedTables as $k => $table_name)
80 $reservedTables[$k] = strtolower($db_prefix . $table_name);
81
82 // We in turn may need the extra stuff.
83 db_extend('extra');
84 }
85
86 // Create a table.
87 function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
88 {
89 global $reservedTables, $smcFunc, $db_package_log, $db_prefix, $db_character_set;
90
91 // Strip out the table name, we might not need it in some cases
92 $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
93
94 // With or without the database name, the fullname looks like this.
95 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
96 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
97
98 // First - no way do we touch SMF tables.
99 if (in_array(strtolower($table_name), $reservedTables))
100 return false;
101
102 // Log that we'll want to remove this on uninstall.
103 $db_package_log[] = array('remove_table', $table_name);
104
105 // Slightly easier on MySQL than the others...
106 $tables = $smcFunc['db_list_tables']();
107 if (in_array($full_table_name, $tables))
108 {
109 // This is a sad day... drop the table? If not, return false (error) by default.
110 if ($if_exists == 'overwrite')
111 $smcFunc['db_drop_table']($table_name);
112 else
113 return $if_exists == 'ignore';
114 }
115
116 // Righty - let's do the damn thing!
117 $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
118 foreach ($columns as $column)
119 {
120 // Auto increment is easy here!
121 if (!empty($column['auto']))
122 {
123 $default = 'auto_increment';
124 }
125 elseif (isset($column['default']) && $column['default'] !== null)
126 $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
127 else
128 $default = '';
129
130 // Sort out the size... and stuff...
131 $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
132 list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
133
134 // Allow unsigned integers (mysql only)
135 $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column['unsigned']) ? 'unsigned ' : '';
136
137 if ($size !== null)
138 $type = $type . '(' . $size . ')';
139
140 // Now just put it together!
141 $table_query .= "\n\t`" .$column['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
142 }
143
144 // Loop through the indexes next...
145 foreach ($indexes as $index)
146 {
147 $columns = implode(',', $index['columns']);
148
149 // Is it the primary?
150 if (isset($index['type']) && $index['type'] == 'primary')
151 $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
152 else
153 {
154 if (empty($index['name']))
155 $index['name'] = implode('_', $index['columns']);
156 $table_query .= "\n\t" . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : 'KEY') . ' ' . $index['name'] . ' (' . $columns . '),';
157 }
158 }
159
160 // No trailing commas!
161 if (substr($table_query, -1) == ',')
162 $table_query = substr($table_query, 0, -1);
163
164 $table_query .= ') ENGINE=MyISAM';
165 if (!empty($db_character_set) && $db_character_set == 'utf8')
166 $table_query .= ' DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci';
167
168 // Create the table!
169 $smcFunc['db_query']('', $table_query,
170 array(
171 'security_override' => true,
172 )
173 );
174 }
175
176 // Drop a table.
177 function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
178 {
179 global $reservedTables, $smcFunc, $db_prefix;
180
181 // After stripping away the database name, this is what's left.
182 $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
183
184 // Get some aliases.
185 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
186 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
187
188 // God no - dropping one of these = bad.
189 if (in_array(strtolower($table_name), $reservedTables))
190 return false;
191
192 // Does it exist?
193 if (in_array($full_table_name, $smcFunc['db_list_tables']()))
194 {
195 $query = 'DROP TABLE ' . $table_name;
196 $smcFunc['db_query']('',
197 $query,
198 array(
199 'security_override' => true,
200 )
201 );
202
203 return true;
204 }
205
206 // Otherwise do 'nout.
207 return false;
208 }
209
210 // Add a column.
211 function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
212 {
213 global $smcFunc, $db_package_log, $txt, $db_prefix;
214
215 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
216
217 // Log that we will want to uninstall this!
218 $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
219
220 // Does it exist - if so don't add it again!
221 $columns = $smcFunc['db_list_columns']($table_name, false);
222 foreach ($columns as $column)
223 if ($column == $column_info['name'])
224 {
225 // If we're going to overwrite then use change column.
226 if ($if_exists == 'update')
227 return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
228 else
229 return false;
230 }
231
232 // Get the specifics...
233 $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
234 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
235
236 // Allow unsigned integers (mysql only)
237 $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column_info['unsigned']) ? 'unsigned ' : '';
238
239 if ($size !== null)
240 $type = $type . '(' . $size . ')';
241
242 // Now add the thing!
243 $query = '
244 ALTER TABLE ' . $table_name . '
245 ADD `' . $column_info['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (empty($column_info['null']) ? 'NOT NULL' : '') . ' ' .
246 (!isset($column_info['default']) ? '' : 'default \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'') . ' ' .
247 (empty($column_info['auto']) ? '' : 'auto_increment primary key') . ' ';
248 $smcFunc['db_query']('', $query,
249 array(
250 'security_override' => true,
251 )
252 );
253
254 return true;
255 }
256
257 // Remove a column.
258 function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
259 {
260 global $smcFunc, $db_prefix;
261
262 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
263
264 // Does it exist?
265 $columns = $smcFunc['db_list_columns']($table_name, true);
266 foreach ($columns as $column)
267 if ($column['name'] == $column_name)
268 {
269 $smcFunc['db_query']('', '
270 ALTER TABLE ' . $table_name . '
271 DROP COLUMN ' . $column_name,
272 array(
273 'security_override' => true,
274 )
275 );
276
277 return true;
278 }
279
280 // If here we didn't have to work - joy!
281 return false;
282 }
283
284 // Change a column.
285 function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
286 {
287 global $smcFunc, $db_prefix;
288
289 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
290
291 // Check it does exist!
292 $columns = $smcFunc['db_list_columns']($table_name, true);
293 $old_info = null;
294 foreach ($columns as $column)
295 if ($column['name'] == $old_column)
296 $old_info = $column;
297
298 // Nothing?
299 if ($old_info == null)
300 return false;
301
302 // Get the right bits.
303 if (!isset($column_info['name']))
304 $column_info['name'] = $old_column;
305 if (!isset($column_info['default']))
306 $column_info['default'] = $old_info['default'];
307 if (!isset($column_info['null']))
308 $column_info['null'] = $old_info['null'];
309 if (!isset($column_info['auto']))
310 $column_info['auto'] = $old_info['auto'];
311 if (!isset($column_info['type']))
312 $column_info['type'] = $old_info['type'];
313 if (!isset($column_info['size']) || !is_numeric($column_info['size']))
314 $column_info['size'] = $old_info['size'];
315 if (!isset($column_info['unsigned']) || !in_array($column_info['type'], array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')))
316 $column_info['unsigned'] = '';
317
318 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
319
320 // Allow for unsigned integers (mysql only)
321 $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column_info['unsigned']) ? 'unsigned ' : '';
322
323 if ($size !== null)
324 $type = $type . '(' . $size . ')';
325
326 $smcFunc['db_query']('', '
327 ALTER TABLE ' . $table_name . '
328 CHANGE COLUMN `' . $old_column . '` `' . $column_info['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (empty($column_info['null']) ? 'NOT NULL' : '') . ' ' .
329 (!isset($column_info['default']) ? '' : 'default \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'') . ' ' .
330 (empty($column_info['auto']) ? '' : 'auto_increment') . ' ',
331 array(
332 'security_override' => true,
333 )
334 );
335 }
336
337 // Add an index.
338 function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
339 {
340 global $smcFunc, $db_package_log, $db_prefix;
341
342 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
343
344 // No columns = no index.
345 if (empty($index_info['columns']))
346 return false;
347 $columns = implode(',', $index_info['columns']);
348
349 // No name - make it up!
350 if (empty($index_info['name']))
351 {
352 // No need for primary.
353 if (isset($index_info['type']) && $index_info['type'] == 'primary')
354 $index_info['name'] = '';
355 else
356 $index_info['name'] = implode('_', $index_info['columns']);
357 }
358 else
359 $index_info['name'] = $index_info['name'];
360
361 // Log that we are going to want to remove this!
362 $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
363
364 // Let's get all our indexes.
365 $indexes = $smcFunc['db_list_indexes']($table_name, true);
366 // Do we already have it?
367 foreach ($indexes as $index)
368 {
369 if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
370 {
371 // If we want to overwrite simply remove the current one then continue.
372 if ($if_exists != 'update' || $index['type'] == 'primary')
373 return false;
374 else
375 $smcFunc['db_remove_index']($table_name, $index_info['name']);
376 }
377 }
378
379 // If we're here we know we don't have the index - so just add it.
380 if (!empty($index_info['type']) && $index_info['type'] == 'primary')
381 {
382 $smcFunc['db_query']('', '
383 ALTER TABLE ' . $table_name . '
384 ADD PRIMARY KEY (' . $columns . ')',
385 array(
386 'security_override' => true,
387 )
388 );
389 }
390 else
391 {
392 $smcFunc['db_query']('', '
393 ALTER TABLE ' . $table_name . '
394 ADD ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : 'INDEX') . ' ' . $index_info['name'] . ' (' . $columns . ')',
395 array(
396 'security_override' => true,
397 )
398 );
399 }
400 }
401
402 // Remove an index.
403 function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
404 {
405 global $smcFunc, $db_prefix;
406
407 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
408
409 // Better exist!
410 $indexes = $smcFunc['db_list_indexes']($table_name, true);
411
412 foreach ($indexes as $index)
413 {
414 // If the name is primary we want the primary key!
415 if ($index['type'] == 'primary' && $index_name == 'primary')
416 {
417 // Dropping primary key?
418 $smcFunc['db_query']('', '
419 ALTER TABLE ' . $table_name . '
420 DROP PRIMARY KEY',
421 array(
422 'security_override' => true,
423 )
424 );
425
426 return true;
427 }
428 if ($index['name'] == $index_name)
429 {
430 // Drop the bugger...
431 $smcFunc['db_query']('', '
432 ALTER TABLE ' . $table_name . '
433 DROP INDEX ' . $index_name,
434 array(
435 'security_override' => true,
436 )
437 );
438
439 return true;
440 }
441 }
442
443 // Not to be found ;(
444 return false;
445 }
446
447 // Get the schema formatted name for a type.
448 function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
449 {
450 // MySQL is actually the generic baseline.
451 return array($type_name, $type_size);
452 }
453
454 // Get table structure.
455 function smf_db_table_structure($table_name, $parameters = array())
456 {
457 global $smcFunc, $db_prefix;
458
459 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
460
461 return array(
462 'name' => $table_name,
463 'columns' => $smcFunc['db_list_columns']($table_name, true),
464 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
465 );
466 }
467
468 // Return column information for a table.
469 function smf_db_list_columns($table_name, $detail = false, $parameters = array())
470 {
471 global $smcFunc, $db_prefix;
472
473 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
474
475 $result = $smcFunc['db_query']('', '
476 SHOW FIELDS
477 FROM {raw:table_name}',
478 array(
479 'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
480 )
481 );
482 $columns = array();
483 while ($row = $smcFunc['db_fetch_assoc']($result))
484 {
485 if (!$detail)
486 {
487 $columns[] = $row['Field'];
488 }
489 else
490 {
491 // Is there an auto_increment?
492 $auto = strpos($row['Extra'], 'auto_increment') !== false ? true : false;
493
494 // Can we split out the size?
495 if (preg_match('~(.+?)\s*\((\d+)\)(?:(?:\s*)?(unsigned))?~i', $row['Type'], $matches) === 1)
496 {
497 $type = $matches[1];
498 $size = $matches[2];
499 if (!empty($matches[3]) && $matches[3] == 'unsigned')
500 $unsigned = true;
501 }
502 else
503 {
504 $type = $row['Type'];
505 $size = null;
506 }
507
508 $columns[$row['Field']] = array(
509 'name' => $row['Field'],
510 'null' => $row['Null'] != 'YES' ? false : true,
511 'default' => isset($row['Default']) ? $row['Default'] : null,
512 'type' => $type,
513 'size' => $size,
514 'auto' => $auto,
515 );
516
517 if (isset($unsigned))
518 {
519 $columns[$row['Field']]['unsigned'] = $unsigned;
520 unset($unsigned);
521 }
522 }
523 }
524 $smcFunc['db_free_result']($result);
525
526 return $columns;
527 }
528
529 // What about some index information?
530 function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
531 {
532 global $smcFunc, $db_prefix;
533
534 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
535
536 $result = $smcFunc['db_query']('', '
537 SHOW KEYS
538 FROM {raw:table_name}',
539 array(
540 'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
541 )
542 );
543 $indexes = array();
544 while ($row = $smcFunc['db_fetch_assoc']($result))
545 {
546 if (!$detail)
547 $indexes[] = $row['Key_name'];
548 else
549 {
550 // What is the type?
551 if ($row['Key_name'] == 'PRIMARY')
552 $type = 'primary';
553 elseif (empty($row['Non_unique']))
554 $type = 'unique';
555 elseif (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT')
556 $type = 'fulltext';
557 else
558 $type = 'index';
559
560 // This is the first column we've seen?
561 if (empty($indexes[$row['Key_name']]))
562 {
563 $indexes[$row['Key_name']] = array(
564 'name' => $row['Key_name'],
565 'type' => $type,
566 'columns' => array(),
567 );
568 }
569
570 // Is it a partial index?
571 if (!empty($row['Sub_part']))
572 $indexes[$row['Key_name']]['columns'][] = $row['Column_name'] . '(' . $row['Sub_part'] . ')';
573 else
574 $indexes[$row['Key_name']]['columns'][] = $row['Column_name'];
575 }
576 }
577 $smcFunc['db_free_result']($result);
578
579 return $indexes;
580 }
581
582 ?>