Mercurial > hg > vamp-website

annotate forum/Sources/DbPackages-postgresql.php @ 85:6d7b61434be7 website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add a copy of this here, just in case!
author Chris Cannam
date Mon, 20 Jan 2014 11:02:36 +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 database functionality specifically designed for packages to utilize.
Chris@76 18
Chris@76 19 bool smf_db_create_table(string table_name, array columns, array indexes = array(),
Chris@76 20 array parameters = array(), string if_exists = 'ignore')
Chris@76 21 - Can be used to create a table without worrying about schema compatabilities.
Chris@76 22 - If the table exists will, by default, do nothing.
Chris@76 23 - Builds table with columns as passed to it - at least one column must be sent.
Chris@76 24 The columns array should have one sub-array for each column - these sub arrays contain:
Chris@76 25 + 'name' = Column name
Chris@76 26 + 'type' = Type of column - values from (smallint,mediumint,int,text,varchar,char,tinytext,mediumtext,largetext)
Chris@76 27 + 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc. If not
Chris@76 28 set SMF will pick a size.
Chris@76 29 + 'default' = Default value - do not set if no default required.
Chris@76 30 + 'null' => Can it be null (true or false) - if not set default will be false.
Chris@76 31 + 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set
Chris@76 32 from what it should begin counting.
Chris@76 33 - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
Chris@76 34 + 'name' => Index name (If left empty SMF will generate).
Chris@76 35 + 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
Chris@76 36 + 'columns' => Array containing columns that form part of key - in the order the index is to be created.
Chris@76 37 - parameters: (None yet)
Chris@76 38 - if_exists values:
Chris@76 39 + 'ignore' will do nothing if the table exists. (And will return true)
Chris@76 40 + 'overwrite' will drop any existing table of the same name.
Chris@76 41 + 'error' will return false if the table already exists.
Chris@76 42
Chris@76 43 */
Chris@76 44
Chris@76 45 // Add the file functions to the $smcFunc array.
Chris@76 46 function db_packages_init()
Chris@76 47 {
Chris@76 48 global $smcFunc, $reservedTables, $db_package_log, $db_prefix;
Chris@76 49
Chris@76 50 if (!isset($smcFunc['db_create_table']) || $smcFunc['db_create_table'] != 'smf_db_create_table')
Chris@76 51 {
Chris@76 52 $smcFunc += array(
Chris@76 53 'db_add_column' => 'smf_db_add_column',
Chris@76 54 'db_add_index' => 'smf_db_add_index',
Chris@76 55 'db_calculate_type' => 'smf_db_calculate_type',
Chris@76 56 'db_change_column' => 'smf_db_change_column',
Chris@76 57 'db_create_table' => 'smf_db_create_table',
Chris@76 58 'db_drop_table' => 'smf_db_drop_table',
Chris@76 59 'db_table_structure' => 'smf_db_table_structure',
Chris@76 60 'db_list_columns' => 'smf_db_list_columns',
Chris@76 61 'db_list_indexes' => 'smf_db_list_indexes',
Chris@76 62 'db_remove_column' => 'smf_db_remove_column',
Chris@76 63 'db_remove_index' => 'smf_db_remove_index',
Chris@76 64 );
Chris@76 65 $db_package_log = array();
Chris@76 66 }
Chris@76 67
Chris@76 68 // We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up!
Chris@76 69 $reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
Chris@76 70 'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
Chris@76 71 'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
Chris@76 72 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
Chris@76 73 'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
Chris@76 74 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
Chris@76 75 'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
Chris@76 76 'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
Chris@76 77 'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
Chris@76 78 'themes', 'topics');
Chris@76 79 foreach ($reservedTables as $k => $table_name)
Chris@76 80 $reservedTables[$k] = strtolower($db_prefix . $table_name);
Chris@76 81
Chris@76 82 // We in turn may need the extra stuff.
Chris@76 83 db_extend('extra');
Chris@76 84 }
Chris@76 85
Chris@76 86 // Create a table.
Chris@76 87 function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
Chris@76 88 {
Chris@76 89 global $reservedTables, $smcFunc, $db_package_log, $db_prefix;
Chris@76 90
Chris@76 91 // Strip out the table name, we might not need it in some cases
Chris@76 92 $real_prefix = preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76 93
Chris@76 94 // With or without the database name, the fullname looks like this.
Chris@76 95 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76 96 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 97
Chris@76 98 // First - no way do we touch SMF tables.
Chris@76 99 if (in_array(strtolower($table_name), $reservedTables))
Chris@76 100 return false;
Chris@76 101
Chris@76 102 // Log that we'll want to remove this on uninstall.
Chris@76 103 $db_package_log[] = array('remove_table', $table_name);
Chris@76 104
Chris@76 105 // This... my friends... is a function in a half - let's start by checking if the table exists!
Chris@76 106 $tables = $smcFunc['db_list_tables']();
Chris@76 107 if (in_array($full_table_name, $tables))
Chris@76 108 {
Chris@76 109 // This is a sad day... drop the table? If not, return false (error) by default.
Chris@76 110 if ($if_exists == 'overwrite')
Chris@76 111 $smcFunc['db_drop_table']($table_name);
Chris@76 112 else
Chris@76 113 return $if_exists == 'ignore';
Chris@76 114 }
Chris@76 115
Chris@76 116 // If we've got this far - good news - no table exists. We can build our own!
Chris@76 117 $smcFunc['db_transaction']('begin');
Chris@76 118 $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
Chris@76 119 foreach ($columns as $column)
Chris@76 120 {
Chris@76 121 // If we have an auto increment do it!
Chris@76 122 if (!empty($column['auto']))
Chris@76 123 {
Chris@76 124 $smcFunc['db_query']('', '
Chris@76 125 CREATE SEQUENCE ' . $table_name . '_seq',
Chris@76 126 array(
Chris@76 127 'security_override' => true,
Chris@76 128 )
Chris@76 129 );
Chris@76 130 $default = 'default nextval(\'' . $table_name . '_seq\')';
Chris@76 131 }
Chris@76 132 elseif (isset($column['default']) && $column['default'] !== null)
Chris@76 133 $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
Chris@76 134 else
Chris@76 135 $default = '';
Chris@76 136
Chris@76 137 // Sort out the size...
Chris@76 138 $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
Chris@76 139 list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
Chris@76 140 if ($size !== null)
Chris@76 141 $type = $type . '(' . $size . ')';
Chris@76 142
Chris@76 143 // Now just put it together!
Chris@76 144 $table_query .= "\n\t\"" . $column['name'] . '" ' . $type . ' ' . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
Chris@76 145 }
Chris@76 146
Chris@76 147 // Loop through the indexes a sec...
Chris@76 148 $index_queries = array();
Chris@76 149 foreach ($indexes as $index)
Chris@76 150 {
Chris@76 151 $columns = implode(',', $index['columns']);
Chris@76 152
Chris@76 153 // Primary goes in the table...
Chris@76 154 if (isset($index['type']) && $index['type'] == 'primary')
Chris@76 155 $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
Chris@76 156 else
Chris@76 157 {
Chris@76 158 if (empty($index['name']))
Chris@76 159 $index['name'] = implode('_', $index['columns']);
Chris@76 160 $index_queries[] = 'CREATE ' . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
Chris@76 161 }
Chris@76 162 }
Chris@76 163
Chris@76 164 // No trailing commas!
Chris@76 165 if (substr($table_query, -1) == ',')
Chris@76 166 $table_query = substr($table_query, 0, -1);
Chris@76 167
Chris@76 168 $table_query .= ')';
Chris@76 169
Chris@76 170 // Create the table!
Chris@76 171 $smcFunc['db_query']('', $table_query,
Chris@76 172 array(
Chris@76 173 'security_override' => true,
Chris@76 174 )
Chris@76 175 );
Chris@76 176 // And the indexes...
Chris@76 177 foreach ($index_queries as $query)
Chris@76 178 $smcFunc['db_query']('', $query,
Chris@76 179 array(
Chris@76 180 'security_override' => true,
Chris@76 181 )
Chris@76 182 );
Chris@76 183
Chris@76 184 // Go, go power rangers!
Chris@76 185 $smcFunc['db_transaction']('commit');
Chris@76 186 }
Chris@76 187
Chris@76 188 // Drop a table.
Chris@76 189 function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
Chris@76 190 {
Chris@76 191 global $reservedTables, $smcFunc, $db_prefix;
Chris@76 192
Chris@76 193 // After stripping away the database name, this is what's left.
Chris@76 194 $real_prefix = preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76 195
Chris@76 196 // Get some aliases.
Chris@76 197 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76 198 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 199
Chris@76 200 // God no - dropping one of these = bad.
Chris@76 201 if (in_array(strtolower($table_name), $reservedTables))
Chris@76 202 return false;
Chris@76 203
Chris@76 204 // Does it exist?
Chris@76 205 if (in_array($full_table_name, $smcFunc['db_list_tables']()))
Chris@76 206 {
Chris@76 207 // We can then drop the table.
Chris@76 208 $smcFunc['db_transaction']('begin');
Chris@76 209
Chris@76 210 // the table
Chris@76 211 $table_query = 'DROP TABLE ' . $table_name;
Chris@76 212
Chris@76 213 // and the assosciated sequence, if any
Chris@76 214 $sequence_query = 'DROP SEQUENCE IF EXISTS ' . $table_name . '_seq';
Chris@76 215
Chris@76 216 // drop them
Chris@76 217 $smcFunc['db_query']('',
Chris@76 218 $table_query,
Chris@76 219 array(
Chris@76 220 'security_override' => true,
Chris@76 221 )
Chris@76 222 );
Chris@76 223 $smcFunc['db_query']('',
Chris@76 224 $sequence_query,
Chris@76 225 array(
Chris@76 226 'security_override' => true,
Chris@76 227 )
Chris@76 228 );
Chris@76 229
Chris@76 230 $smcFunc['db_transaction']('commit');
Chris@76 231
Chris@76 232 return true;
Chris@76 233 }
Chris@76 234
Chris@76 235 // Otherwise do 'nout.
Chris@76 236 return false;
Chris@76 237 }
Chris@76 238
Chris@76 239 // Add a column.
Chris@76 240 function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76 241 {
Chris@76 242 global $smcFunc, $db_package_log, $txt, $db_prefix;
Chris@76 243
Chris@76 244 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 245
Chris@76 246 // Log that we will want to uninstall this!
Chris@76 247 $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
Chris@76 248
Chris@76 249 // Does it exist - if so don't add it again!
Chris@76 250 $columns = $smcFunc['db_list_columns']($table_name, false);
Chris@76 251 foreach ($columns as $column)
Chris@76 252 if ($column == $column_info['name'])
Chris@76 253 {
Chris@76 254 // If we're going to overwrite then use change column.
Chris@76 255 if ($if_exists == 'update')
Chris@76 256 return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
Chris@76 257 else
Chris@76 258 return false;
Chris@76 259 }
Chris@76 260
Chris@76 261 // Get the specifics...
Chris@76 262 $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
Chris@76 263 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
Chris@76 264 if ($size !== null)
Chris@76 265 $type = $type . '(' . $size . ')';
Chris@76 266
Chris@76 267 // Now add the thing!
Chris@76 268 $query = '
Chris@76 269 ALTER TABLE ' . $table_name . '
Chris@76 270 ADD COLUMN ' . $column_info['name'] . ' ' . $type;
Chris@76 271 $smcFunc['db_query']('', $query,
Chris@76 272 array(
Chris@76 273 'security_override' => true,
Chris@76 274 )
Chris@76 275 );
Chris@76 276
Chris@76 277 // If there's more attributes they need to be done via a change on PostgreSQL.
Chris@76 278 unset($column_info['type'], $column_info['size']);
Chris@76 279
Chris@76 280 if (count($column_info) != 1)
Chris@76 281 return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
Chris@76 282 else
Chris@76 283 return true;
Chris@76 284 }
Chris@76 285
Chris@76 286 // Remove a column.
Chris@76 287 function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
Chris@76 288 {
Chris@76 289 global $smcFunc, $db_prefix;
Chris@76 290
Chris@76 291 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 292
Chris@76 293 // Does it exist?
Chris@76 294 $columns = $smcFunc['db_list_columns']($table_name, true);
Chris@76 295 foreach ($columns as $column)
Chris@76 296 if ($column['name'] == $column_name)
Chris@76 297 {
Chris@76 298 // If there is an auto we need remove it!
Chris@76 299 if ($column['auto'])
Chris@76 300 $smcFunc['db_query']('',
Chris@76 301 'DROP SEQUENCE ' . $table_name . '_seq',
Chris@76 302 array(
Chris@76 303 'security_override' => true,
Chris@76 304 )
Chris@76 305 );
Chris@76 306
Chris@76 307 $smcFunc['db_query']('', '
Chris@76 308 ALTER TABLE ' . $table_name . '
Chris@76 309 DROP COLUMN ' . $column_name,
Chris@76 310 array(
Chris@76 311 'security_override' => true,
Chris@76 312 )
Chris@76 313 );
Chris@76 314
Chris@76 315 return true;
Chris@76 316 }
Chris@76 317
Chris@76 318 // If here we didn't have to work - joy!
Chris@76 319 return false;
Chris@76 320 }
Chris@76 321
Chris@76 322 // Change a column.
Chris@76 323 function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
Chris@76 324 {
Chris@76 325 global $smcFunc, $db_prefix;
Chris@76 326
Chris@76 327 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 328
Chris@76 329 // Check it does exist!
Chris@76 330 $columns = $smcFunc['db_list_columns']($table_name, true);
Chris@76 331 $old_info = null;
Chris@76 332 foreach ($columns as $column)
Chris@76 333 if ($column['name'] == $old_column)
Chris@76 334 $old_info = $column;
Chris@76 335
Chris@76 336 // Nothing?
Chris@76 337 if ($old_info == null)
Chris@76 338 return false;
Chris@76 339
Chris@76 340 // Now we check each bit individually and ALTER as required.
Chris@76 341 if (isset($column_info['name']) && $column_info['name'] != $old_column)
Chris@76 342 {
Chris@76 343 $smcFunc['db_query']('', '
Chris@76 344 ALTER TABLE ' . $table_name . '
Chris@76 345 RENAME COLUMN ' . $old_column . ' TO ' . $column_info['name'],
Chris@76 346 array(
Chris@76 347 'security_override' => true,
Chris@76 348 )
Chris@76 349 );
Chris@76 350 }
Chris@76 351 // Different default?
Chris@76 352 if (isset($column_info['default']) && $column_info['default'] != $old_info['default'])
Chris@76 353 {
Chris@76 354 $action = $column_info['default'] !== null ? 'SET DEFAULT \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'' : 'DROP DEFAULT';
Chris@76 355 $smcFunc['db_query']('', '
Chris@76 356 ALTER TABLE ' . $table_name . '
Chris@76 357 ALTER COLUMN ' . $column_info['name'] . ' ' . $action,
Chris@76 358 array(
Chris@76 359 'security_override' => true,
Chris@76 360 )
Chris@76 361 );
Chris@76 362 }
Chris@76 363 // Is it null - or otherwise?
Chris@76 364 if (isset($column_info['null']) && $column_info['null'] != $old_info['null'])
Chris@76 365 {
Chris@76 366 $action = $column_info['null'] ? 'DROP' : 'SET';
Chris@76 367 $smcFunc['db_transaction']('begin');
Chris@76 368 if (!$column_info['null'])
Chris@76 369 {
Chris@76 370 // We have to set it to something if we are making it NOT NULL.
Chris@76 371 $setTo = isset($column_info['default']) ? $column_info['default'] : '';
Chris@76 372 $smcFunc['db_query']('', '
Chris@76 373 UPDATE ' . $table_name . '
Chris@76 374 SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
Chris@76 375 WHERE ' . $column_info['name'] . ' = NULL',
Chris@76 376 array(
Chris@76 377 'security_override' => true,
Chris@76 378 )
Chris@76 379 );
Chris@76 380 }
Chris@76 381 $smcFunc['db_query']('', '
Chris@76 382 ALTER TABLE ' . $table_name . '
Chris@76 383 ALTER COLUMN ' . $column_info['name'] . ' ' . $action . ' NOT NULL',
Chris@76 384 array(
Chris@76 385 'security_override' => true,
Chris@76 386 )
Chris@76 387 );
Chris@76 388 $smcFunc['db_transaction']('commit');
Chris@76 389 }
Chris@76 390 // What about a change in type?
Chris@76 391 if (isset($column_info['type']) && ($column_info['type'] != $old_info['type'] || (isset($column_info['size']) && $column_info['size'] != $old_info['size'])))
Chris@76 392 {
Chris@76 393 $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
Chris@76 394 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
Chris@76 395 if ($size !== null)
Chris@76 396 $type = $type . '(' . $size . ')';
Chris@76 397
Chris@76 398 // The alter is a pain.
Chris@76 399 $smcFunc['db_transaction']('begin');
Chris@76 400 $smcFunc['db_query']('', '
Chris@76 401 ALTER TABLE ' . $table_name . '
Chris@76 402 ADD COLUMN ' . $column_info['name'] . '_tempxx ' . $type,
Chris@76 403 array(
Chris@76 404 'security_override' => true,
Chris@76 405 )
Chris@76 406 );
Chris@76 407 $smcFunc['db_query']('', '
Chris@76 408 UPDATE ' . $table_name . '
Chris@76 409 SET ' . $column_info['name'] . '_tempxx = CAST(' . $column_info['name'] . ' AS ' . $type . ')',
Chris@76 410 array(
Chris@76 411 'security_override' => true,
Chris@76 412 )
Chris@76 413 );
Chris@76 414 $smcFunc['db_query']('', '
Chris@76 415 ALTER TABLE ' . $table_name . '
Chris@76 416 DROP COLUMN ' . $column_info['name'],
Chris@76 417 array(
Chris@76 418 'security_override' => true,
Chris@76 419 )
Chris@76 420 );
Chris@76 421 $smcFunc['db_query']('', '
Chris@76 422 ALTER TABLE ' . $table_name . '
Chris@76 423 RENAME COLUMN ' . $column_info['name'] . '_tempxx TO ' . $column_info['name'],
Chris@76 424 array(
Chris@76 425 'security_override' => true,
Chris@76 426 )
Chris@76 427 );
Chris@76 428 $smcFunc['db_transaction']('commit');
Chris@76 429 }
Chris@76 430 // Finally - auto increment?!
Chris@76 431 if (isset($column_info['auto']) && $column_info['auto'] != $old_info['auto'])
Chris@76 432 {
Chris@76 433 // Are we removing an old one?
Chris@76 434 if ($old_info['auto'])
Chris@76 435 {
Chris@76 436 // Alter the table first - then drop the sequence.
Chris@76 437 $smcFunc['db_query']('', '
Chris@76 438 ALTER TABLE ' . $table_name . '
Chris@76 439 ALTER COLUMN ' . $column_info['name'] . ' SET DEFAULT \'0\'',
Chris@76 440 array(
Chris@76 441 'security_override' => true,
Chris@76 442 )
Chris@76 443 );
Chris@76 444 $smcFunc['db_query']('', '
Chris@76 445 DROP SEQUENCE ' . $table_name . '_seq',
Chris@76 446 array(
Chris@76 447 'security_override' => true,
Chris@76 448 )
Chris@76 449 );
Chris@76 450 }
Chris@76 451 // Otherwise add it!
Chris@76 452 else
Chris@76 453 {
Chris@76 454 $smcFunc['db_query']('', '
Chris@76 455 CREATE SEQUENCE ' . $table_name . '_seq',
Chris@76 456 array(
Chris@76 457 'security_override' => true,
Chris@76 458 )
Chris@76 459 );
Chris@76 460 $smcFunc['db_query']('', '
Chris@76 461 ALTER TABLE ' . $table_name . '
Chris@76 462 ALTER COLUMN ' . $column_info['name'] . ' SET DEFAULT nextval(\'' . $table_name . '_seq\')',
Chris@76 463 array(
Chris@76 464 'security_override' => true,
Chris@76 465 )
Chris@76 466 );
Chris@76 467 }
Chris@76 468 }
Chris@76 469 }
Chris@76 470
Chris@76 471 // Add an index.
Chris@76 472 function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76 473 {
Chris@76 474 global $smcFunc, $db_package_log, $db_prefix;
Chris@76 475
Chris@76 476 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 477
Chris@76 478 // No columns = no index.
Chris@76 479 if (empty($index_info['columns']))
Chris@76 480 return false;
Chris@76 481 $columns = implode(',', $index_info['columns']);
Chris@76 482
Chris@76 483 // No name - make it up!
Chris@76 484 if (empty($index_info['name']))
Chris@76 485 {
Chris@76 486 // No need for primary.
Chris@76 487 if (isset($index_info['type']) && $index_info['type'] == 'primary')
Chris@76 488 $index_info['name'] = '';
Chris@76 489 else
Chris@76 490 $index_info['name'] = $table_name . implode('_', $index_info['columns']);
Chris@76 491 }
Chris@76 492 else
Chris@76 493 $index_info['name'] = $table_name . $index_info['name'];
Chris@76 494
Chris@76 495 // Log that we are going to want to remove this!
Chris@76 496 $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
Chris@76 497
Chris@76 498 // Let's get all our indexes.
Chris@76 499 $indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76 500 // Do we already have it?
Chris@76 501 foreach ($indexes as $index)
Chris@76 502 {
Chris@76 503 if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
Chris@76 504 {
Chris@76 505 // If we want to overwrite simply remove the current one then continue.
Chris@76 506 if ($if_exists != 'update' || $index['type'] == 'primary')
Chris@76 507 return false;
Chris@76 508 else
Chris@76 509 $smcFunc['db_remove_index']($table_name, $index_info['name']);
Chris@76 510 }
Chris@76 511 }
Chris@76 512
Chris@76 513 // If we're here we know we don't have the index - so just add it.
Chris@76 514 if (!empty($index_info['type']) && $index_info['type'] == 'primary')
Chris@76 515 {
Chris@76 516 $smcFunc['db_query']('', '
Chris@76 517 ALTER TABLE ' . $table_name . '
Chris@76 518 ADD PRIMARY KEY (' . $columns . ')',
Chris@76 519 array(
Chris@76 520 'security_override' => true,
Chris@76 521 )
Chris@76 522 );
Chris@76 523 }
Chris@76 524 else
Chris@76 525 {
Chris@76 526 $smcFunc['db_query']('', '
Chris@76 527 CREATE ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
Chris@76 528 array(
Chris@76 529 'security_override' => true,
Chris@76 530 )
Chris@76 531 );
Chris@76 532 }
Chris@76 533 }
Chris@76 534
Chris@76 535 // Remove an index.
Chris@76 536 function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
Chris@76 537 {
Chris@76 538 global $smcFunc, $db_prefix;
Chris@76 539
Chris@76 540 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 541
Chris@76 542 // Better exist!
Chris@76 543 $indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76 544 if ($index_name != 'primary')
Chris@76 545 $index_name = $table_name . '_' . $index_name;
Chris@76 546
Chris@76 547 foreach ($indexes as $index)
Chris@76 548 {
Chris@76 549 // If the name is primary we want the primary key!
Chris@76 550 if ($index['type'] == 'primary' && $index_name == 'primary')
Chris@76 551 {
Chris@76 552 // Dropping primary key is odd...
Chris@76 553 $smcFunc['db_query']('', '
Chris@76 554 ALTER TABLE ' . $table_name . '
Chris@76 555 DROP CONSTRAINT ' . $index['name'],
Chris@76 556 array(
Chris@76 557 'security_override' => true,
Chris@76 558 )
Chris@76 559 );
Chris@76 560
Chris@76 561 return true;
Chris@76 562 }
Chris@76 563 if ($index['name'] == $index_name)
Chris@76 564 {
Chris@76 565 // Drop the bugger...
Chris@76 566 $smcFunc['db_query']('', '
Chris@76 567 DROP INDEX ' . $index_name,
Chris@76 568 array(
Chris@76 569 'security_override' => true,
Chris@76 570 )
Chris@76 571 );
Chris@76 572
Chris@76 573 return true;
Chris@76 574 }
Chris@76 575 }
Chris@76 576
Chris@76 577 // Not to be found ;(
Chris@76 578 return false;
Chris@76 579 }
Chris@76 580
Chris@76 581 // Get the schema formatted name for a type.
Chris@76 582 function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
Chris@76 583 {
Chris@76 584 // Generic => Specific.
Chris@76 585 if (!$reverse)
Chris@76 586 {
Chris@76 587 $types = array(
Chris@76 588 'varchar' => 'character varying',
Chris@76 589 'char' => 'character',
Chris@76 590 'mediumint' => 'int',
Chris@76 591 'tinyint' => 'smallint',
Chris@76 592 'tinytext' => 'character varying',
Chris@76 593 'mediumtext' => 'text',
Chris@76 594 'largetext' => 'text',
Chris@76 595 );
Chris@76 596 }
Chris@76 597 else
Chris@76 598 {
Chris@76 599 $types = array(
Chris@76 600 'character varying' => 'varchar',
Chris@76 601 'character' => 'char',
Chris@76 602 'integer' => 'int',
Chris@76 603 );
Chris@76 604 }
Chris@76 605
Chris@76 606 // Got it? Change it!
Chris@76 607 if (isset($types[$type_name]))
Chris@76 608 {
Chris@76 609 if ($type_name == 'tinytext')
Chris@76 610 $type_size = 255;
Chris@76 611 $type_name = $types[$type_name];
Chris@76 612 }
Chris@76 613 // Numbers don't have a size.
Chris@76 614 if (strpos($type_name, 'int') !== false)
Chris@76 615 $type_size = null;
Chris@76 616
Chris@76 617 return array($type_name, $type_size);
Chris@76 618 }
Chris@76 619
Chris@76 620 // Get table structure.
Chris@76 621 function smf_db_table_structure($table_name, $parameters = array())
Chris@76 622 {
Chris@76 623 global $smcFunc, $db_prefix;
Chris@76 624
Chris@76 625 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 626
Chris@76 627 return array(
Chris@76 628 'name' => $table_name,
Chris@76 629 'columns' => $smcFunc['db_list_columns']($table_name, true),
Chris@76 630 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
Chris@76 631 );
Chris@76 632 }
Chris@76 633
Chris@76 634 // Return column information for a table.
Chris@76 635 function smf_db_list_columns($table_name, $detail = false, $parameters = array())
Chris@76 636 {
Chris@76 637 global $smcFunc, $db_prefix;
Chris@76 638
Chris@76 639 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 640
Chris@76 641 $result = $smcFunc['db_query']('', '
Chris@76 642 SELECT column_name, column_default, is_nullable, data_type, character_maximum_length
Chris@76 643 FROM information_schema.columns
Chris@76 644 WHERE table_name = \'' . $table_name . '\'
Chris@76 645 ORDER BY ordinal_position',
Chris@76 646 array(
Chris@76 647 'security_override' => true,
Chris@76 648 )
Chris@76 649 );
Chris@76 650 $columns = array();
Chris@76 651 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 652 {
Chris@76 653 if (!$detail)
Chris@76 654 {
Chris@76 655 $columns[] = $row['column_name'];
Chris@76 656 }
Chris@76 657 else
Chris@76 658 {
Chris@76 659 $auto = false;
Chris@76 660 // What is the default?
Chris@76 661 if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
Chris@76 662 {
Chris@76 663 $default = null;
Chris@76 664 $auto = true;
Chris@76 665 }
Chris@76 666 elseif (trim($row['column_default']) != '')
Chris@76 667 $default = strpos($row['column_default'], '::') === false ? $row['column_default'] : substr($row['column_default'], 0, strpos($row['column_default'], '::'));
Chris@76 668 else
Chris@76 669 $default = null;
Chris@76 670
Chris@76 671 // Make the type generic.
Chris@76 672 list ($type, $size) = $smcFunc['db_calculate_type']($row['data_type'], $row['character_maximum_length'], true);
Chris@76 673
Chris@76 674 $columns[$row['column_name']] = array(
Chris@76 675 'name' => $row['column_name'],
Chris@76 676 'null' => $row['is_nullable'] ? true : false,
Chris@76 677 'default' => $default,
Chris@76 678 'type' => $type,
Chris@76 679 'size' => $size,
Chris@76 680 'auto' => $auto,
Chris@76 681 );
Chris@76 682 }
Chris@76 683 }
Chris@76 684 $smcFunc['db_free_result']($result);
Chris@76 685
Chris@76 686 return $columns;
Chris@76 687 }
Chris@76 688
Chris@76 689 // What about some index information?
Chris@76 690 function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
Chris@76 691 {
Chris@76 692 global $smcFunc, $db_prefix;
Chris@76 693
Chris@76 694 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 695
Chris@76 696 $result = $smcFunc['db_query']('', '
Chris@76 697 SELECT CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary,
Chris@76 698 CASE WHEN i.indisunique THEN 1 ELSE 0 END AS is_unique,
Chris@76 699 c2.relname AS name,
Chris@76 700 pg_get_indexdef(i.indexrelid) AS inddef
Chris@76 701 FROM pg_class AS c, pg_class AS c2, pg_index AS i
Chris@76 702 WHERE c.relname = \'' . $table_name . '\'
Chris@76 703 AND c.oid = i.indrelid
Chris@76 704 AND i.indexrelid = c2.oid',
Chris@76 705 array(
Chris@76 706 'security_override' => true,
Chris@76 707 )
Chris@76 708 );
Chris@76 709 $indexes = array();
Chris@76 710 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 711 {
Chris@76 712 // Try get the columns that make it up.
Chris@76 713 if (preg_match('~\(([^\)]+?)\)~i', $row['inddef'], $matches) == 0)
Chris@76 714 continue;
Chris@76 715
Chris@76 716 $columns = explode(',', $matches[1]);
Chris@76 717
Chris@76 718 if (empty($columns))
Chris@76 719 continue;
Chris@76 720
Chris@76 721 foreach ($columns as $k => $v)
Chris@76 722 $columns[$k] = trim($v);
Chris@76 723
Chris@76 724 // Fix up the name to be consistent cross databases
Chris@76 725 if (substr($row['name'], -5) == '_pkey' && $row['is_primary'] == 1)
Chris@76 726 $row['name'] = 'PRIMARY';
Chris@76 727 else
Chris@76 728 $row['name'] = str_replace($table_name . '_', '', $row['name']);
Chris@76 729
Chris@76 730 if (!$detail)
Chris@76 731 $indexes[] = $row['name'];
Chris@76 732 else
Chris@76 733 {
Chris@76 734 $indexes[$row['name']] = array(
Chris@76 735 'name' => $row['name'],
Chris@76 736 'type' => $row['is_primary'] ? 'primary' : ($row['is_unique'] ? 'unique' : 'index'),
Chris@76 737 'columns' => $columns,
Chris@76 738 );
Chris@76 739 }
Chris@76 740 }
Chris@76 741 $smcFunc['db_free_result']($result);
Chris@76 742
Chris@76 743 return $indexes;
Chris@76 744 }
Chris@76 745
Chris@76 746 ?>