1 | <?php |
||
2 | |||
3 | /** |
||
4 | * This file contains database functionality specifically designed for packages (mods) to utilize. |
||
5 | * |
||
6 | * Simple Machines Forum (SMF) |
||
7 | * |
||
8 | * @package SMF |
||
9 | * @author Simple Machines https://www.simplemachines.org |
||
10 | * @copyright 2022 Simple Machines and individual contributors |
||
11 | * @license https://www.simplemachines.org/about/smf/license.php BSD |
||
12 | * |
||
13 | * @version 2.1.0 |
||
14 | */ |
||
15 | |||
16 | if (!defined('SMF')) |
||
17 | die('No direct access...'); |
||
18 | |||
19 | /** |
||
20 | * Add the file functions to the $smcFunc array. |
||
21 | */ |
||
22 | function db_packages_init() |
||
23 | { |
||
24 | global $smcFunc, $reservedTables, $db_package_log, $db_prefix; |
||
25 | |||
26 | if (!isset($smcFunc['db_create_table']) || $smcFunc['db_create_table'] != 'smf_db_create_table') |
||
27 | { |
||
28 | $smcFunc += array( |
||
29 | 'db_add_column' => 'smf_db_add_column', |
||
30 | 'db_add_index' => 'smf_db_add_index', |
||
31 | 'db_calculate_type' => 'smf_db_calculate_type', |
||
32 | 'db_change_column' => 'smf_db_change_column', |
||
33 | 'db_create_table' => 'smf_db_create_table', |
||
34 | 'db_drop_table' => 'smf_db_drop_table', |
||
35 | 'db_table_structure' => 'smf_db_table_structure', |
||
36 | 'db_list_columns' => 'smf_db_list_columns', |
||
37 | 'db_list_indexes' => 'smf_db_list_indexes', |
||
38 | 'db_remove_column' => 'smf_db_remove_column', |
||
39 | 'db_remove_index' => 'smf_db_remove_index', |
||
40 | ); |
||
41 | $db_package_log = array(); |
||
42 | } |
||
43 | |||
44 | // We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up! |
||
45 | $reservedTables = array( |
||
46 | 'admin_info_files', 'approval_queue', 'attachments', |
||
47 | 'background_tasks', 'ban_groups', 'ban_items', 'board_permissions', |
||
48 | 'board_permissions_view', 'boards', 'calendar', 'calendar_holidays', |
||
49 | 'categories', 'custom_fields', 'group_moderators', 'log_actions', |
||
50 | 'log_activity', 'log_banned', 'log_boards', 'log_comments', |
||
51 | 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', |
||
52 | 'log_mark_read', 'log_member_notices', 'log_notify', 'log_online', |
||
53 | 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments', |
||
54 | 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', |
||
55 | 'log_search_subjects', 'log_search_topics', 'log_spider_hits', |
||
56 | 'log_spider_stats', 'log_subscribed', 'log_topics', 'mail_queue', |
||
57 | 'member_logins', 'membergroups', 'members', 'mentions', |
||
58 | 'message_icons', 'messages', 'moderator_groups', 'moderators', |
||
59 | 'package_servers', 'permission_profiles', 'permissions', |
||
60 | 'personal_messages', 'pm_labeled_messages', 'pm_labels', |
||
61 | 'pm_recipients', 'pm_rules', 'poll_choices', 'polls', 'qanda', |
||
62 | 'scheduled_tasks', 'sessions', 'settings', 'smiley_files', 'smileys', |
||
63 | 'spiders', 'subscriptions', 'themes', 'topics', 'user_alerts', |
||
64 | 'user_alerts_prefs', 'user_drafts', 'user_likes', |
||
65 | ); |
||
66 | foreach ($reservedTables as $k => $table_name) |
||
67 | $reservedTables[$k] = strtolower($db_prefix . $table_name); |
||
68 | |||
69 | // We in turn may need the extra stuff. |
||
70 | db_extend('extra'); |
||
71 | } |
||
72 | |||
73 | /** |
||
74 | * This function can be used to create a table without worrying about schema |
||
75 | * compatibilities across supported database systems. |
||
76 | * - If the table exists will, by default, do nothing. |
||
77 | * - Builds table with columns as passed to it - at least one column must be sent. |
||
78 | * The columns array should have one sub-array for each column - these sub arrays contain: |
||
79 | * 'name' = Column name |
||
80 | * 'type' = Type of column - values from (smallint, mediumint, int, text, varchar, char, tinytext, mediumtext, largetext) |
||
81 | * 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc. |
||
82 | * If not set SMF will pick a size. |
||
83 | * - 'default' = Default value - do not set if no default required. |
||
84 | * - 'not_null' => Can it be null (true or false) - if not set default will be false. |
||
85 | * - 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set from what |
||
86 | * it should begin counting. |
||
87 | * - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are: |
||
88 | * - 'name' => Index name (If left empty SMF will generate). |
||
89 | * - 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'. |
||
90 | * - 'columns' => Array containing columns that form part of key - in the order the index is to be created. |
||
91 | * - parameters: (None yet) |
||
92 | * - if_exists values: |
||
93 | * - 'ignore' will do nothing if the table exists. (And will return true) |
||
94 | * - 'overwrite' will drop any existing table of the same name. |
||
95 | * - 'error' will return false if the table already exists. |
||
96 | * - 'update' will update the table if the table already exists (no change of ai field and only colums with the same name keep the data) |
||
97 | * |
||
98 | * @param string $table_name The name of the table to create |
||
99 | * @param array $columns An array of column info in the specified format |
||
100 | * @param array $indexes An array of index info in the specified format |
||
101 | * @param array $parameters Extra parameters. Currently only 'engine', the desired MySQL storage engine, is used. |
||
102 | * @param string $if_exists What to do if the table exists. |
||
103 | * @param string $error |
||
104 | * @return boolean Whether or not the operation was successful |
||
105 | */ |
||
106 | function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal') |
||
107 | { |
||
108 | global $reservedTables, $smcFunc, $db_package_log, $db_prefix, $db_character_set, $db_name; |
||
109 | |||
110 | static $engines = array(); |
||
111 | |||
112 | $old_table_exists = false; |
||
113 | |||
114 | // Strip out the table name, we might not need it in some cases |
||
115 | $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix; |
||
116 | |||
117 | // With or without the database name, the fullname looks like this. |
||
118 | $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name); |
||
119 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
120 | |||
121 | // First - no way do we touch SMF tables. |
||
122 | if (in_array(strtolower($table_name), $reservedTables)) |
||
123 | return false; |
||
124 | |||
125 | // Log that we'll want to remove this on uninstall. |
||
126 | $db_package_log[] = array('remove_table', $table_name); |
||
127 | |||
128 | // Slightly easier on MySQL than the others... |
||
129 | $tables = $smcFunc['db_list_tables'](); |
||
130 | if (in_array($full_table_name, $tables)) |
||
131 | { |
||
132 | // This is a sad day... drop the table? If not, return false (error) by default. |
||
133 | if ($if_exists == 'overwrite') |
||
134 | $smcFunc['db_drop_table']($table_name); |
||
135 | elseif ($if_exists == 'update') |
||
136 | { |
||
137 | $smcFunc['db_transaction']('begin'); |
||
138 | $db_trans = true; |
||
0 ignored issues
–
show
Unused Code
introduced
by
Loading history...
|
|||
139 | $smcFunc['db_drop_table']($table_name . '_old'); |
||
140 | $smcFunc['db_query']('', ' |
||
141 | RENAME TABLE ' . $table_name . ' TO ' . $table_name . '_old', |
||
142 | array( |
||
143 | 'security_override' => true, |
||
144 | ) |
||
145 | ); |
||
146 | $old_table_exists = true; |
||
147 | } |
||
148 | else |
||
149 | return $if_exists == 'ignore'; |
||
150 | } |
||
151 | |||
152 | // Righty - let's do the damn thing! |
||
153 | $table_query = 'CREATE TABLE ' . $table_name . "\n" . '('; |
||
154 | foreach ($columns as $column) |
||
155 | $table_query .= "\n\t" . smf_db_create_query_column($column) . ','; |
||
156 | |||
157 | // Loop through the indexes next... |
||
158 | foreach ($indexes as $index) |
||
159 | { |
||
160 | $columns = implode(',', $index['columns']); |
||
161 | |||
162 | // Is it the primary? |
||
163 | if (isset($index['type']) && $index['type'] == 'primary') |
||
164 | $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),'; |
||
165 | else |
||
166 | { |
||
167 | if (empty($index['name'])) |
||
168 | $index['name'] = implode('_', $index['columns']); |
||
169 | $table_query .= "\n\t" . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : 'KEY') . ' ' . $index['name'] . ' (' . $columns . '),'; |
||
170 | } |
||
171 | } |
||
172 | |||
173 | // No trailing commas! |
||
174 | if (substr($table_query, -1) == ',') |
||
175 | $table_query = substr($table_query, 0, -1); |
||
176 | |||
177 | // Which engine do we want here? |
||
178 | if (empty($engines)) |
||
179 | { |
||
180 | // Figure out which engines we have |
||
181 | $get_engines = $smcFunc['db_query']('', 'SHOW ENGINES', array()); |
||
182 | |||
183 | while ($row = $smcFunc['db_fetch_assoc']($get_engines)) |
||
184 | { |
||
185 | if ($row['Support'] == 'YES' || $row['Support'] == 'DEFAULT') |
||
186 | $engines[] = $row['Engine']; |
||
187 | } |
||
188 | |||
189 | $smcFunc['db_free_result']($get_engines); |
||
190 | } |
||
191 | |||
192 | // If we don't have this engine, or didn't specify one, default to InnoDB or MyISAM |
||
193 | // depending on which one is available |
||
194 | if (!isset($parameters['engine']) || !in_array($parameters['engine'], $engines)) |
||
195 | { |
||
196 | $parameters['engine'] = in_array('InnoDB', $engines) ? 'InnoDB' : 'MyISAM'; |
||
197 | } |
||
198 | |||
199 | $table_query .= ') ENGINE=' . $parameters['engine']; |
||
200 | if (!empty($db_character_set) && $db_character_set == 'utf8') |
||
201 | $table_query .= ' DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci'; |
||
202 | |||
203 | // Create the table! |
||
204 | $smcFunc['db_query']('', $table_query, |
||
205 | array( |
||
206 | 'security_override' => true, |
||
207 | ) |
||
208 | ); |
||
209 | |||
210 | // Fill the old data |
||
211 | if ($old_table_exists) |
||
212 | { |
||
213 | $same_col = array(); |
||
214 | |||
215 | $request = $smcFunc['db_query']('', ' |
||
216 | SELECT count(*), column_name |
||
217 | FROM information_schema.columns |
||
218 | WHERE table_name in ({string:table1},{string:table2}) AND table_schema = {string:schema} |
||
219 | GROUP BY column_name |
||
220 | HAVING count(*) > 1', |
||
221 | array( |
||
222 | 'table1' => $table_name, |
||
223 | 'table2' => $table_name . '_old', |
||
224 | 'schema' => $db_name, |
||
225 | ) |
||
226 | ); |
||
227 | |||
228 | while ($row = $smcFunc['db_fetch_assoc']($request)) |
||
229 | { |
||
230 | $same_col[] = $row['column_name']; |
||
231 | } |
||
232 | |||
233 | $smcFunc['db_query']('', ' |
||
234 | INSERT INTO ' . $table_name . '(' |
||
235 | . implode(',', $same_col) . |
||
236 | ') |
||
237 | SELECT ' . implode(',', $same_col) . ' |
||
238 | FROM ' . $table_name . '_old', |
||
239 | array() |
||
240 | ); |
||
241 | |||
242 | $smcFunc['db_drop_table']($table_name . '_old'); |
||
243 | } |
||
244 | |||
245 | return true; |
||
246 | } |
||
247 | |||
248 | /** |
||
249 | * Drop a table. |
||
250 | * |
||
251 | * @param string $table_name The name of the table to drop |
||
252 | * @param array $parameters Not used at the moment |
||
253 | * @param string $error |
||
254 | * @return boolean Whether or not the operation was successful |
||
255 | */ |
||
256 | function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal') |
||
257 | { |
||
258 | global $reservedTables, $smcFunc, $db_prefix; |
||
259 | |||
260 | // After stripping away the database name, this is what's left. |
||
261 | $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix; |
||
262 | |||
263 | // Get some aliases. |
||
264 | $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name); |
||
265 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
266 | |||
267 | // God no - dropping one of these = bad. |
||
268 | if (in_array(strtolower($table_name), $reservedTables)) |
||
269 | return false; |
||
270 | |||
271 | // Does it exist? |
||
272 | if (in_array($full_table_name, $smcFunc['db_list_tables']())) |
||
273 | { |
||
274 | $query = 'DROP TABLE ' . $table_name; |
||
275 | $smcFunc['db_query']('', |
||
276 | $query, |
||
277 | array( |
||
278 | 'security_override' => true, |
||
279 | ) |
||
280 | ); |
||
281 | |||
282 | return true; |
||
283 | } |
||
284 | |||
285 | // Otherwise do 'nout. |
||
286 | return false; |
||
287 | } |
||
288 | |||
289 | /** |
||
290 | * This function adds a column. |
||
291 | * |
||
292 | * @param string $table_name The name of the table to add the column to |
||
293 | * @param array $column_info An array of column info ({@see smf_db_create_table}) |
||
294 | * @param array $parameters Not used? |
||
295 | * @param string $if_exists What to do if the column exists. If 'update', column is updated. |
||
296 | * @param string $error |
||
297 | * @return boolean Whether or not the operation was successful |
||
298 | */ |
||
299 | function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal') |
||
300 | { |
||
301 | global $smcFunc, $db_package_log, $db_prefix; |
||
302 | |||
303 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
304 | |||
305 | // Log that we will want to uninstall this! |
||
306 | $db_package_log[] = array('remove_column', $table_name, $column_info['name']); |
||
307 | |||
308 | // Does it exist - if so don't add it again! |
||
309 | $columns = $smcFunc['db_list_columns']($table_name, false); |
||
310 | foreach ($columns as $column) |
||
311 | if ($column == $column_info['name']) |
||
312 | { |
||
313 | // If we're going to overwrite then use change column. |
||
314 | if ($if_exists == 'update') |
||
315 | return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info); |
||
316 | else |
||
317 | return false; |
||
318 | } |
||
319 | |||
320 | // Get the specifics... |
||
321 | $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null; |
||
322 | |||
323 | // Now add the thing! |
||
324 | $query = ' |
||
325 | ALTER TABLE ' . $table_name . ' |
||
326 | ADD ' . smf_db_create_query_column($column_info) . (empty($column_info['auto']) ? '' : ' primary key' |
||
327 | ); |
||
328 | $smcFunc['db_query']('', $query, |
||
329 | array( |
||
330 | 'security_override' => true, |
||
331 | ) |
||
332 | ); |
||
333 | |||
334 | return true; |
||
335 | } |
||
336 | |||
337 | /** |
||
338 | * Removes a column. |
||
339 | * |
||
340 | * @param string $table_name The name of the table to drop the column from |
||
341 | * @param string $column_name The name of the column to drop |
||
342 | * @param array $parameters Not used? |
||
343 | * @param string $error |
||
344 | * @return boolean Whether or not the operation was successful |
||
345 | */ |
||
346 | function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal') |
||
347 | { |
||
348 | global $smcFunc, $db_prefix; |
||
349 | |||
350 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
351 | |||
352 | // Does it exist? |
||
353 | $columns = $smcFunc['db_list_columns']($table_name, true); |
||
354 | foreach ($columns as $column) |
||
355 | if ($column['name'] == $column_name) |
||
356 | { |
||
357 | $smcFunc['db_query']('', ' |
||
358 | ALTER TABLE ' . $table_name . ' |
||
359 | DROP COLUMN ' . $column_name, |
||
360 | array( |
||
361 | 'security_override' => true, |
||
362 | ) |
||
363 | ); |
||
364 | |||
365 | return true; |
||
366 | } |
||
367 | |||
368 | // If here we didn't have to work - joy! |
||
369 | return false; |
||
370 | } |
||
371 | |||
372 | /** |
||
373 | * Change a column. |
||
374 | * |
||
375 | * @param string $table_name The name of the table this column is in |
||
376 | * @param string $old_column The name of the column we want to change |
||
377 | * @param array $column_info An array of info about the "new" column definition (see {@link smf_db_create_table()}) |
||
378 | * @return bool |
||
379 | */ |
||
380 | function smf_db_change_column($table_name, $old_column, $column_info) |
||
381 | { |
||
382 | global $smcFunc, $db_prefix; |
||
383 | |||
384 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
385 | |||
386 | // Check it does exist! |
||
387 | $columns = $smcFunc['db_list_columns']($table_name, true); |
||
388 | $old_info = null; |
||
389 | foreach ($columns as $column) |
||
390 | if ($column['name'] == $old_column) |
||
391 | $old_info = $column; |
||
392 | |||
393 | // Nothing? |
||
394 | if ($old_info == null) |
||
395 | return false; |
||
396 | |||
397 | // backward compatibility |
||
398 | if (isset($column_info['null'])) |
||
399 | $column_info['not_null'] = !$column_info['null']; |
||
400 | if (isset($old_info['null'])) |
||
401 | $old_info['not_null'] = !$old_info['null']; |
||
402 | // Get the right bits. |
||
403 | if (!isset($column_info['name'])) |
||
404 | $column_info['name'] = $old_column; |
||
405 | if (!isset($column_info['default'])) |
||
406 | $column_info['default'] = $old_info['default']; |
||
407 | if (!isset($column_info['not_null'])) |
||
408 | $column_info['not_null'] = $old_info['not_null']; |
||
409 | if (!isset($column_info['auto'])) |
||
410 | $column_info['auto'] = $old_info['auto']; |
||
411 | if (!isset($column_info['type'])) |
||
412 | $column_info['type'] = $old_info['type']; |
||
413 | if (!isset($column_info['size']) || !is_numeric($column_info['size'])) |
||
414 | $column_info['size'] = $old_info['size']; |
||
415 | if (!isset($column_info['unsigned']) || !in_array($column_info['type'], array('int', 'tinyint', 'smallint', 'mediumint', 'bigint'))) |
||
416 | $column_info['unsigned'] = ''; |
||
417 | |||
418 | list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']); |
||
419 | |||
420 | // Allow for unsigned integers (mysql only) |
||
421 | $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column_info['unsigned']) ? 'unsigned ' : ''; |
||
422 | |||
423 | if ($size !== null) |
||
424 | $type = $type . '(' . $size . ')'; |
||
425 | |||
426 | $smcFunc['db_query']('', ' |
||
427 | ALTER TABLE ' . $table_name . ' |
||
428 | CHANGE COLUMN `' . $old_column . '` `' . $column_info['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (!empty($column_info['not_null']) ? 'NOT NULL' : '') . ' ' . |
||
429 | (!isset($column_info['default']) ? '' : 'default \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'') . ' ' . |
||
430 | (empty($column_info['auto']) ? '' : 'auto_increment') . ' ', |
||
431 | array( |
||
432 | 'security_override' => true, |
||
433 | ) |
||
434 | ); |
||
435 | } |
||
436 | |||
437 | /** |
||
438 | * Add an index. |
||
439 | * |
||
440 | * @param string $table_name The name of the table to add the index to |
||
441 | * @param array $index_info An array of index info (see {@link smf_db_create_table()}) |
||
442 | * @param array $parameters Not used? |
||
443 | * @param string $if_exists What to do if the index exists. If 'update', the definition will be updated. |
||
444 | * @param string $error |
||
445 | * @return boolean Whether or not the operation was successful |
||
446 | */ |
||
447 | function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal') |
||
448 | { |
||
449 | global $smcFunc, $db_package_log, $db_prefix; |
||
450 | |||
451 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
452 | |||
453 | // No columns = no index. |
||
454 | if (empty($index_info['columns'])) |
||
455 | return false; |
||
456 | $columns = implode(',', $index_info['columns']); |
||
457 | |||
458 | // No name - make it up! |
||
459 | if (empty($index_info['name'])) |
||
460 | { |
||
461 | // No need for primary. |
||
462 | if (isset($index_info['type']) && $index_info['type'] == 'primary') |
||
463 | $index_info['name'] = ''; |
||
464 | else |
||
465 | $index_info['name'] = implode('_', $index_info['columns']); |
||
466 | } |
||
467 | |||
468 | // Log that we are going to want to remove this! |
||
469 | $db_package_log[] = array('remove_index', $table_name, $index_info['name']); |
||
470 | |||
471 | // Let's get all our indexes. |
||
472 | $indexes = $smcFunc['db_list_indexes']($table_name, true); |
||
473 | // Do we already have it? |
||
474 | foreach ($indexes as $index) |
||
475 | { |
||
476 | if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary')) |
||
477 | { |
||
478 | // If we want to overwrite simply remove the current one then continue. |
||
479 | if ($if_exists != 'update' || $index['type'] == 'primary') |
||
480 | return false; |
||
481 | else |
||
482 | $smcFunc['db_remove_index']($table_name, $index_info['name']); |
||
483 | } |
||
484 | } |
||
485 | |||
486 | // If we're here we know we don't have the index - so just add it. |
||
487 | if (!empty($index_info['type']) && $index_info['type'] == 'primary') |
||
488 | { |
||
489 | $smcFunc['db_query']('', ' |
||
490 | ALTER TABLE ' . $table_name . ' |
||
491 | ADD PRIMARY KEY (' . $columns . ')', |
||
492 | array( |
||
493 | 'security_override' => true, |
||
494 | ) |
||
495 | ); |
||
496 | } |
||
497 | else |
||
498 | { |
||
499 | $smcFunc['db_query']('', ' |
||
500 | ALTER TABLE ' . $table_name . ' |
||
501 | ADD ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : 'INDEX') . ' ' . $index_info['name'] . ' (' . $columns . ')', |
||
502 | array( |
||
503 | 'security_override' => true, |
||
504 | ) |
||
505 | ); |
||
506 | } |
||
507 | } |
||
508 | |||
509 | /** |
||
510 | * Remove an index. |
||
511 | * |
||
512 | * @param string $table_name The name of the table to remove the index from |
||
513 | * @param string $index_name The name of the index to remove |
||
514 | * @param array $parameters Not used? |
||
515 | * @param string $error |
||
516 | * @return boolean Whether or not the operation was successful |
||
517 | */ |
||
518 | function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal') |
||
519 | { |
||
520 | global $smcFunc, $db_prefix; |
||
521 | |||
522 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
523 | |||
524 | // Better exist! |
||
525 | $indexes = $smcFunc['db_list_indexes']($table_name, true); |
||
526 | |||
527 | foreach ($indexes as $index) |
||
528 | { |
||
529 | // If the name is primary we want the primary key! |
||
530 | if ($index['type'] == 'primary' && $index_name == 'primary') |
||
531 | { |
||
532 | // Dropping primary key? |
||
533 | $smcFunc['db_query']('', ' |
||
534 | ALTER TABLE ' . $table_name . ' |
||
535 | DROP PRIMARY KEY', |
||
536 | array( |
||
537 | 'security_override' => true, |
||
538 | ) |
||
539 | ); |
||
540 | |||
541 | return true; |
||
542 | } |
||
543 | if ($index['name'] == $index_name) |
||
544 | { |
||
545 | // Drop the bugger... |
||
546 | $smcFunc['db_query']('', ' |
||
547 | ALTER TABLE ' . $table_name . ' |
||
548 | DROP INDEX ' . $index_name, |
||
549 | array( |
||
550 | 'security_override' => true, |
||
551 | ) |
||
552 | ); |
||
553 | |||
554 | return true; |
||
555 | } |
||
556 | } |
||
557 | |||
558 | // Not to be found ;( |
||
559 | return false; |
||
560 | } |
||
561 | |||
562 | /** |
||
563 | * Get the schema formatted name for a type. |
||
564 | * |
||
565 | * @param string $type_name The data type (int, varchar, smallint, etc.) |
||
566 | * @param int $type_size The size (8, 255, etc.) |
||
567 | * @param boolean $reverse |
||
568 | * @return array An array containing the appropriate type and size for this DB type |
||
569 | */ |
||
570 | function smf_db_calculate_type($type_name, $type_size = null, $reverse = false) |
||
571 | { |
||
572 | // MySQL is actually the generic baseline. |
||
573 | |||
574 | $type_name = strtolower($type_name); |
||
575 | // Generic => Specific. |
||
576 | if (!$reverse) |
||
577 | { |
||
578 | $types = array( |
||
579 | 'inet' => 'varbinary', |
||
580 | ); |
||
581 | } |
||
582 | else |
||
583 | { |
||
584 | $types = array( |
||
585 | 'varbinary' => 'inet', |
||
586 | ); |
||
587 | } |
||
588 | |||
589 | // Got it? Change it! |
||
590 | if (isset($types[$type_name])) |
||
591 | { |
||
592 | if ($type_name == 'inet' && !$reverse) |
||
593 | { |
||
594 | $type_size = 16; |
||
595 | $type_name = 'varbinary'; |
||
596 | } |
||
597 | elseif ($type_name == 'varbinary' && $reverse && $type_size == 16) |
||
598 | { |
||
599 | $type_name = 'inet'; |
||
600 | $type_size = null; |
||
601 | } |
||
602 | elseif ($type_name == 'varbinary') |
||
603 | $type_name = 'varbinary'; |
||
604 | else |
||
605 | $type_name = $types[$type_name]; |
||
606 | } |
||
607 | elseif ($type_name == 'boolean') |
||
608 | $type_size = null; |
||
609 | |||
610 | return array($type_name, $type_size); |
||
611 | } |
||
612 | |||
613 | /** |
||
614 | * Get table structure. |
||
615 | * |
||
616 | * @param string $table_name The name of the table |
||
617 | * @return array An array of table structure - the name, the column info from {@link smf_db_list_columns()} and the index info from {@link smf_db_list_indexes()} |
||
618 | */ |
||
619 | function smf_db_table_structure($table_name) |
||
620 | { |
||
621 | global $smcFunc, $db_prefix; |
||
622 | |||
623 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
624 | |||
625 | // Find the table engine and add that to the info as well |
||
626 | $table_status = $smcFunc['db_query']('', ' |
||
627 | SHOW TABLE STATUS |
||
628 | LIKE {string:table}', |
||
629 | array( |
||
630 | 'table' => strtr($table_name, array('_' => '\\_', '%' => '\\%')) |
||
631 | ) |
||
632 | ); |
||
633 | |||
634 | // Only one row, so no need for a loop... |
||
635 | $row = $smcFunc['db_fetch_assoc']($table_status); |
||
636 | |||
637 | $smcFunc['db_free_result']($table_status); |
||
638 | |||
639 | return array( |
||
640 | 'name' => $table_name, |
||
641 | 'columns' => $smcFunc['db_list_columns']($table_name, true), |
||
642 | 'indexes' => $smcFunc['db_list_indexes']($table_name, true), |
||
643 | 'engine' => $row['Engine'], |
||
644 | ); |
||
645 | } |
||
646 | |||
647 | /** |
||
648 | * Return column information for a table. |
||
649 | * |
||
650 | * @param string $table_name The name of the table to get column info for |
||
651 | * @param bool $detail Whether or not to return detailed info. If true, returns the column info. If false, just returns the column names. |
||
652 | * @param array $parameters Not used? |
||
653 | * @return array An array of column names or detailed column info, depending on $detail |
||
654 | */ |
||
655 | function smf_db_list_columns($table_name, $detail = false, $parameters = array()) |
||
656 | { |
||
657 | global $smcFunc, $db_prefix, $db_name; |
||
658 | |||
659 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
660 | |||
661 | $result = $smcFunc['db_query']('', ' |
||
662 | SELECT column_name "Field", COLUMN_TYPE "Type", is_nullable "Null", COLUMN_KEY "Key" , column_default "Default", extra "Extra" |
||
663 | FROM information_schema.columns |
||
664 | WHERE table_name = {string:table_name} |
||
665 | AND table_schema = {string:db_name} |
||
666 | ORDER BY ordinal_position', |
||
667 | array( |
||
668 | 'table_name' => $table_name, |
||
669 | 'db_name' => $db_name, |
||
670 | ) |
||
671 | ); |
||
672 | $columns = array(); |
||
673 | while ($row = $smcFunc['db_fetch_assoc']($result)) |
||
674 | { |
||
675 | if (!$detail) |
||
676 | { |
||
677 | $columns[] = $row['Field']; |
||
678 | } |
||
679 | else |
||
680 | { |
||
681 | // Is there an auto_increment? |
||
682 | $auto = strpos($row['Extra'], 'auto_increment') !== false ? true : false; |
||
683 | |||
684 | // Can we split out the size? |
||
685 | if (preg_match('~(.+?)\s*\((\d+)\)(?:(?:\s*)?(unsigned))?~i', $row['Type'], $matches) === 1) |
||
686 | { |
||
687 | $type = $matches[1]; |
||
688 | $size = $matches[2]; |
||
689 | if (!empty($matches[3]) && $matches[3] == 'unsigned') |
||
690 | $unsigned = true; |
||
691 | } |
||
692 | else |
||
693 | { |
||
694 | $type = $row['Type']; |
||
695 | $size = null; |
||
696 | } |
||
697 | |||
698 | $columns[$row['Field']] = array( |
||
699 | 'name' => $row['Field'], |
||
700 | 'not_null' => $row['Null'] != 'YES', |
||
701 | 'null' => $row['Null'] == 'YES', |
||
702 | 'default' => isset($row['Default']) ? $row['Default'] : null, |
||
703 | 'type' => $type, |
||
704 | 'size' => $size, |
||
705 | 'auto' => $auto, |
||
706 | ); |
||
707 | |||
708 | if (isset($unsigned)) |
||
709 | { |
||
710 | $columns[$row['Field']]['unsigned'] = $unsigned; |
||
711 | unset($unsigned); |
||
712 | } |
||
713 | } |
||
714 | } |
||
715 | $smcFunc['db_free_result']($result); |
||
716 | |||
717 | return $columns; |
||
718 | } |
||
719 | |||
720 | /** |
||
721 | * Get index information. |
||
722 | * |
||
723 | * @param string $table_name The name of the table to get indexes for |
||
724 | * @param bool $detail Whether or not to return detailed info. |
||
725 | * @param array $parameters Not used? |
||
726 | * @return array An array of index names or a detailed array of index info, depending on $detail |
||
727 | */ |
||
728 | function smf_db_list_indexes($table_name, $detail = false, $parameters = array()) |
||
729 | { |
||
730 | global $smcFunc, $db_prefix; |
||
731 | |||
732 | $table_name = str_replace('{db_prefix}', $db_prefix, $table_name); |
||
733 | |||
734 | $result = $smcFunc['db_query']('', ' |
||
735 | SHOW KEYS |
||
736 | FROM {raw:table_name}', |
||
737 | array( |
||
738 | 'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`', |
||
739 | ) |
||
740 | ); |
||
741 | $indexes = array(); |
||
742 | while ($row = $smcFunc['db_fetch_assoc']($result)) |
||
743 | { |
||
744 | if (!$detail) |
||
745 | $indexes[] = $row['Key_name']; |
||
746 | else |
||
747 | { |
||
748 | // What is the type? |
||
749 | if ($row['Key_name'] == 'PRIMARY') |
||
750 | $type = 'primary'; |
||
751 | elseif (empty($row['Non_unique'])) |
||
752 | $type = 'unique'; |
||
753 | elseif (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT') |
||
754 | $type = 'fulltext'; |
||
755 | else |
||
756 | $type = 'index'; |
||
757 | |||
758 | // This is the first column we've seen? |
||
759 | if (empty($indexes[$row['Key_name']])) |
||
760 | { |
||
761 | $indexes[$row['Key_name']] = array( |
||
762 | 'name' => $row['Key_name'], |
||
763 | 'type' => $type, |
||
764 | 'columns' => array(), |
||
765 | ); |
||
766 | } |
||
767 | |||
768 | // Is it a partial index? |
||
769 | if (!empty($row['Sub_part'])) |
||
770 | $indexes[$row['Key_name']]['columns'][] = $row['Column_name'] . '(' . $row['Sub_part'] . ')'; |
||
771 | else |
||
772 | $indexes[$row['Key_name']]['columns'][] = $row['Column_name']; |
||
773 | } |
||
774 | } |
||
775 | $smcFunc['db_free_result']($result); |
||
776 | |||
777 | return $indexes; |
||
778 | } |
||
779 | |||
780 | /** |
||
781 | * Creates a query for a column |
||
782 | * |
||
783 | * @param array $column An array of column info |
||
784 | * @return string The column definition |
||
785 | */ |
||
786 | function smf_db_create_query_column($column) |
||
787 | { |
||
788 | global $smcFunc; |
||
789 | |||
790 | // Auto increment is easy here! |
||
791 | if (!empty($column['auto'])) |
||
792 | { |
||
793 | $default = 'auto_increment'; |
||
794 | } |
||
795 | elseif (isset($column['default']) && $column['default'] !== null) |
||
796 | $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\''; |
||
797 | else |
||
798 | $default = ''; |
||
799 | |||
800 | // Sort out the size... and stuff... |
||
801 | $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null; |
||
802 | list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']); |
||
803 | |||
804 | // Allow unsigned integers (mysql only) |
||
805 | $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column['unsigned']) ? 'unsigned ' : ''; |
||
806 | |||
807 | if ($size !== null) |
||
808 | $type = $type . '(' . $size . ')'; |
||
809 | |||
810 | // Now just put it together! |
||
811 | return '`' . $column['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (!empty($column['not_null']) ? 'NOT NULL' : '') . ' ' . $default; |
||
812 | } |
||
813 | |||
814 | ?> |