Mercurial > hg > vamp-website

comparison forum/Sources/DbPackages-postgresql.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;
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 // This... my friends... is a function in a half - let's start by checking if the table exists!
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 // If we've got this far - good news - no table exists. We can build our own!
117 $smcFunc['db_transaction']('begin');
118 $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
119 foreach ($columns as $column)
120 {
121 // If we have an auto increment do it!
122 if (!empty($column['auto']))
123 {
124 $smcFunc['db_query']('', '
125 CREATE SEQUENCE ' . $table_name . '_seq',
126 array(
127 'security_override' => true,
128 )
129 );
130 $default = 'default nextval(\'' . $table_name . '_seq\')';
131 }
132 elseif (isset($column['default']) && $column['default'] !== null)
133 $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
134 else
135 $default = '';
136
137 // Sort out the size...
138 $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
139 list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
140 if ($size !== null)
141 $type = $type . '(' . $size . ')';
142
143 // Now just put it together!
144 $table_query .= "\n\t\"" . $column['name'] . '" ' . $type . ' ' . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
145 }
146
147 // Loop through the indexes a sec...
148 $index_queries = array();
149 foreach ($indexes as $index)
150 {
151 $columns = implode(',', $index['columns']);
152
153 // Primary goes in the table...
154 if (isset($index['type']) && $index['type'] == 'primary')
155 $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
156 else
157 {
158 if (empty($index['name']))
159 $index['name'] = implode('_', $index['columns']);
160 $index_queries[] = 'CREATE ' . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
161 }
162 }
163
164 // No trailing commas!
165 if (substr($table_query, -1) == ',')
166 $table_query = substr($table_query, 0, -1);
167
168 $table_query .= ')';
169
170 // Create the table!
171 $smcFunc['db_query']('', $table_query,
172 array(
173 'security_override' => true,
174 )
175 );
176 // And the indexes...
177 foreach ($index_queries as $query)
178 $smcFunc['db_query']('', $query,
179 array(
180 'security_override' => true,
181 )
182 );
183
184 // Go, go power rangers!
185 $smcFunc['db_transaction']('commit');
186 }
187
188 // Drop a table.
189 function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
190 {
191 global $reservedTables, $smcFunc, $db_prefix;
192
193 // After stripping away the database name, this is what's left.
194 $real_prefix = preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
195
196 // Get some aliases.
197 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
198 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
199
200 // God no - dropping one of these = bad.
201 if (in_array(strtolower($table_name), $reservedTables))
202 return false;
203
204 // Does it exist?
205 if (in_array($full_table_name, $smcFunc['db_list_tables']()))
206 {
207 // We can then drop the table.
208 $smcFunc['db_transaction']('begin');
209
210 // the table
211 $table_query = 'DROP TABLE ' . $table_name;
212
213 // and the assosciated sequence, if any
214 $sequence_query = 'DROP SEQUENCE IF EXISTS ' . $table_name . '_seq';
215
216 // drop them
217 $smcFunc['db_query']('',
218 $table_query,
219 array(
220 'security_override' => true,
221 )
222 );
223 $smcFunc['db_query']('',
224 $sequence_query,
225 array(
226 'security_override' => true,
227 )
228 );
229
230 $smcFunc['db_transaction']('commit');
231
232 return true;
233 }
234
235 // Otherwise do 'nout.
236 return false;
237 }
238
239 // Add a column.
240 function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
241 {
242 global $smcFunc, $db_package_log, $txt, $db_prefix;
243
244 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
245
246 // Log that we will want to uninstall this!
247 $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
248
249 // Does it exist - if so don't add it again!
250 $columns = $smcFunc['db_list_columns']($table_name, false);
251 foreach ($columns as $column)
252 if ($column == $column_info['name'])
253 {
254 // If we're going to overwrite then use change column.
255 if ($if_exists == 'update')
256 return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
257 else
258 return false;
259 }
260
261 // Get the specifics...
262 $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
263 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
264 if ($size !== null)
265 $type = $type . '(' . $size . ')';
266
267 // Now add the thing!
268 $query = '
269 ALTER TABLE ' . $table_name . '
270 ADD COLUMN ' . $column_info['name'] . ' ' . $type;
271 $smcFunc['db_query']('', $query,
272 array(
273 'security_override' => true,
274 )
275 );
276
277 // If there's more attributes they need to be done via a change on PostgreSQL.
278 unset($column_info['type'], $column_info['size']);
279
280 if (count($column_info) != 1)
281 return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
282 else
283 return true;
284 }
285
286 // Remove a column.
287 function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
288 {
289 global $smcFunc, $db_prefix;
290
291 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
292
293 // Does it exist?
294 $columns = $smcFunc['db_list_columns']($table_name, true);
295 foreach ($columns as $column)
296 if ($column['name'] == $column_name)
297 {
298 // If there is an auto we need remove it!
299 if ($column['auto'])
300 $smcFunc['db_query']('',
301 'DROP SEQUENCE ' . $table_name . '_seq',
302 array(
303 'security_override' => true,
304 )
305 );
306
307 $smcFunc['db_query']('', '
308 ALTER TABLE ' . $table_name . '
309 DROP COLUMN ' . $column_name,
310 array(
311 'security_override' => true,
312 )
313 );
314
315 return true;
316 }
317
318 // If here we didn't have to work - joy!
319 return false;
320 }
321
322 // Change a column.
323 function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
324 {
325 global $smcFunc, $db_prefix;
326
327 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
328
329 // Check it does exist!
330 $columns = $smcFunc['db_list_columns']($table_name, true);
331 $old_info = null;
332 foreach ($columns as $column)
333 if ($column['name'] == $old_column)
334 $old_info = $column;
335
336 // Nothing?
337 if ($old_info == null)
338 return false;
339
340 // Now we check each bit individually and ALTER as required.
341 if (isset($column_info['name']) && $column_info['name'] != $old_column)
342 {
343 $smcFunc['db_query']('', '
344 ALTER TABLE ' . $table_name . '
345 RENAME COLUMN ' . $old_column . ' TO ' . $column_info['name'],
346 array(
347 'security_override' => true,
348 )
349 );
350 }
351 // Different default?
352 if (isset($column_info['default']) && $column_info['default'] != $old_info['default'])
353 {
354 $action = $column_info['default'] !== null ? 'SET DEFAULT \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'' : 'DROP DEFAULT';
355 $smcFunc['db_query']('', '
356 ALTER TABLE ' . $table_name . '
357 ALTER COLUMN ' . $column_info['name'] . ' ' . $action,
358 array(
359 'security_override' => true,
360 )
361 );
362 }
363 // Is it null - or otherwise?
364 if (isset($column_info['null']) && $column_info['null'] != $old_info['null'])
365 {
366 $action = $column_info['null'] ? 'DROP' : 'SET';
367 $smcFunc['db_transaction']('begin');
368 if (!$column_info['null'])
369 {
370 // We have to set it to something if we are making it NOT NULL.
371 $setTo = isset($column_info['default']) ? $column_info['default'] : '';
372 $smcFunc['db_query']('', '
373 UPDATE ' . $table_name . '
374 SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
375 WHERE ' . $column_info['name'] . ' = NULL',
376 array(
377 'security_override' => true,
378 )
379 );
380 }
381 $smcFunc['db_query']('', '
382 ALTER TABLE ' . $table_name . '
383 ALTER COLUMN ' . $column_info['name'] . ' ' . $action . ' NOT NULL',
384 array(
385 'security_override' => true,
386 )
387 );
388 $smcFunc['db_transaction']('commit');
389 }
390 // What about a change in type?
391 if (isset($column_info['type']) && ($column_info['type'] != $old_info['type'] || (isset($column_info['size']) && $column_info['size'] != $old_info['size'])))
392 {
393 $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
394 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
395 if ($size !== null)
396 $type = $type . '(' . $size . ')';
397
398 // The alter is a pain.
399 $smcFunc['db_transaction']('begin');
400 $smcFunc['db_query']('', '
401 ALTER TABLE ' . $table_name . '
402 ADD COLUMN ' . $column_info['name'] . '_tempxx ' . $type,
403 array(
404 'security_override' => true,
405 )
406 );
407 $smcFunc['db_query']('', '
408 UPDATE ' . $table_name . '
409 SET ' . $column_info['name'] . '_tempxx = CAST(' . $column_info['name'] . ' AS ' . $type . ')',
410 array(
411 'security_override' => true,
412 )
413 );
414 $smcFunc['db_query']('', '
415 ALTER TABLE ' . $table_name . '
416 DROP COLUMN ' . $column_info['name'],
417 array(
418 'security_override' => true,
419 )
420 );
421 $smcFunc['db_query']('', '
422 ALTER TABLE ' . $table_name . '
423 RENAME COLUMN ' . $column_info['name'] . '_tempxx TO ' . $column_info['name'],
424 array(
425 'security_override' => true,
426 )
427 );
428 $smcFunc['db_transaction']('commit');
429 }
430 // Finally - auto increment?!
431 if (isset($column_info['auto']) && $column_info['auto'] != $old_info['auto'])
432 {
433 // Are we removing an old one?
434 if ($old_info['auto'])
435 {
436 // Alter the table first - then drop the sequence.
437 $smcFunc['db_query']('', '
438 ALTER TABLE ' . $table_name . '
439 ALTER COLUMN ' . $column_info['name'] . ' SET DEFAULT \'0\'',
440 array(
441 'security_override' => true,
442 )
443 );
444 $smcFunc['db_query']('', '
445 DROP SEQUENCE ' . $table_name . '_seq',
446 array(
447 'security_override' => true,
448 )
449 );
450 }
451 // Otherwise add it!
452 else
453 {
454 $smcFunc['db_query']('', '
455 CREATE SEQUENCE ' . $table_name . '_seq',
456 array(
457 'security_override' => true,
458 )
459 );
460 $smcFunc['db_query']('', '
461 ALTER TABLE ' . $table_name . '
462 ALTER COLUMN ' . $column_info['name'] . ' SET DEFAULT nextval(\'' . $table_name . '_seq\')',
463 array(
464 'security_override' => true,
465 )
466 );
467 }
468 }
469 }
470
471 // Add an index.
472 function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
473 {
474 global $smcFunc, $db_package_log, $db_prefix;
475
476 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
477
478 // No columns = no index.
479 if (empty($index_info['columns']))
480 return false;
481 $columns = implode(',', $index_info['columns']);
482
483 // No name - make it up!
484 if (empty($index_info['name']))
485 {
486 // No need for primary.
487 if (isset($index_info['type']) && $index_info['type'] == 'primary')
488 $index_info['name'] = '';
489 else
490 $index_info['name'] = $table_name . implode('_', $index_info['columns']);
491 }
492 else
493 $index_info['name'] = $table_name . $index_info['name'];
494
495 // Log that we are going to want to remove this!
496 $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
497
498 // Let's get all our indexes.
499 $indexes = $smcFunc['db_list_indexes']($table_name, true);
500 // Do we already have it?
501 foreach ($indexes as $index)
502 {
503 if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
504 {
505 // If we want to overwrite simply remove the current one then continue.
506 if ($if_exists != 'update' || $index['type'] == 'primary')
507 return false;
508 else
509 $smcFunc['db_remove_index']($table_name, $index_info['name']);
510 }
511 }
512
513 // If we're here we know we don't have the index - so just add it.
514 if (!empty($index_info['type']) && $index_info['type'] == 'primary')
515 {
516 $smcFunc['db_query']('', '
517 ALTER TABLE ' . $table_name . '
518 ADD PRIMARY KEY (' . $columns . ')',
519 array(
520 'security_override' => true,
521 )
522 );
523 }
524 else
525 {
526 $smcFunc['db_query']('', '
527 CREATE ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
528 array(
529 'security_override' => true,
530 )
531 );
532 }
533 }
534
535 // Remove an index.
536 function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
537 {
538 global $smcFunc, $db_prefix;
539
540 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
541
542 // Better exist!
543 $indexes = $smcFunc['db_list_indexes']($table_name, true);
544 if ($index_name != 'primary')
545 $index_name = $table_name . '_' . $index_name;
546
547 foreach ($indexes as $index)
548 {
549 // If the name is primary we want the primary key!
550 if ($index['type'] == 'primary' && $index_name == 'primary')
551 {
552 // Dropping primary key is odd...
553 $smcFunc['db_query']('', '
554 ALTER TABLE ' . $table_name . '
555 DROP CONSTRAINT ' . $index['name'],
556 array(
557 'security_override' => true,
558 )
559 );
560
561 return true;
562 }
563 if ($index['name'] == $index_name)
564 {
565 // Drop the bugger...
566 $smcFunc['db_query']('', '
567 DROP INDEX ' . $index_name,
568 array(
569 'security_override' => true,
570 )
571 );
572
573 return true;
574 }
575 }
576
577 // Not to be found ;(
578 return false;
579 }
580
581 // Get the schema formatted name for a type.
582 function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
583 {
584 // Generic => Specific.
585 if (!$reverse)
586 {
587 $types = array(
588 'varchar' => 'character varying',
589 'char' => 'character',
590 'mediumint' => 'int',
591 'tinyint' => 'smallint',
592 'tinytext' => 'character varying',
593 'mediumtext' => 'text',
594 'largetext' => 'text',
595 );
596 }
597 else
598 {
599 $types = array(
600 'character varying' => 'varchar',
601 'character' => 'char',
602 'integer' => 'int',
603 );
604 }
605
606 // Got it? Change it!
607 if (isset($types[$type_name]))
608 {
609 if ($type_name == 'tinytext')
610 $type_size = 255;
611 $type_name = $types[$type_name];
612 }
613 // Numbers don't have a size.
614 if (strpos($type_name, 'int') !== false)
615 $type_size = null;
616
617 return array($type_name, $type_size);
618 }
619
620 // Get table structure.
621 function smf_db_table_structure($table_name, $parameters = array())
622 {
623 global $smcFunc, $db_prefix;
624
625 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
626
627 return array(
628 'name' => $table_name,
629 'columns' => $smcFunc['db_list_columns']($table_name, true),
630 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
631 );
632 }
633
634 // Return column information for a table.
635 function smf_db_list_columns($table_name, $detail = false, $parameters = array())
636 {
637 global $smcFunc, $db_prefix;
638
639 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
640
641 $result = $smcFunc['db_query']('', '
642 SELECT column_name, column_default, is_nullable, data_type, character_maximum_length
643 FROM information_schema.columns
644 WHERE table_name = \'' . $table_name . '\'
645 ORDER BY ordinal_position',
646 array(
647 'security_override' => true,
648 )
649 );
650 $columns = array();
651 while ($row = $smcFunc['db_fetch_assoc']($result))
652 {
653 if (!$detail)
654 {
655 $columns[] = $row['column_name'];
656 }
657 else
658 {
659 $auto = false;
660 // What is the default?
661 if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
662 {
663 $default = null;
664 $auto = true;
665 }
666 elseif (trim($row['column_default']) != '')
667 $default = strpos($row['column_default'], '::') === false ? $row['column_default'] : substr($row['column_default'], 0, strpos($row['column_default'], '::'));
668 else
669 $default = null;
670
671 // Make the type generic.
672 list ($type, $size) = $smcFunc['db_calculate_type']($row['data_type'], $row['character_maximum_length'], true);
673
674 $columns[$row['column_name']] = array(
675 'name' => $row['column_name'],
676 'null' => $row['is_nullable'] ? true : false,
677 'default' => $default,
678 'type' => $type,
679 'size' => $size,
680 'auto' => $auto,
681 );
682 }
683 }
684 $smcFunc['db_free_result']($result);
685
686 return $columns;
687 }
688
689 // What about some index information?
690 function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
691 {
692 global $smcFunc, $db_prefix;
693
694 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
695
696 $result = $smcFunc['db_query']('', '
697 SELECT CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary,
698 CASE WHEN i.indisunique THEN 1 ELSE 0 END AS is_unique,
699 c2.relname AS name,
700 pg_get_indexdef(i.indexrelid) AS inddef
701 FROM pg_class AS c, pg_class AS c2, pg_index AS i
702 WHERE c.relname = \'' . $table_name . '\'
703 AND c.oid = i.indrelid
704 AND i.indexrelid = c2.oid',
705 array(
706 'security_override' => true,
707 )
708 );
709 $indexes = array();
710 while ($row = $smcFunc['db_fetch_assoc']($result))
711 {
712 // Try get the columns that make it up.
713 if (preg_match('~\(([^\)]+?)\)~i', $row['inddef'], $matches) == 0)
714 continue;
715
716 $columns = explode(',', $matches[1]);
717
718 if (empty($columns))
719 continue;
720
721 foreach ($columns as $k => $v)
722 $columns[$k] = trim($v);
723
724 // Fix up the name to be consistent cross databases
725 if (substr($row['name'], -5) == '_pkey' && $row['is_primary'] == 1)
726 $row['name'] = 'PRIMARY';
727 else
728 $row['name'] = str_replace($table_name . '_', '', $row['name']);
729
730 if (!$detail)
731 $indexes[] = $row['name'];
732 else
733 {
734 $indexes[$row['name']] = array(
735 'name' => $row['name'],
736 'type' => $row['is_primary'] ? 'primary' : ($row['is_unique'] ? 'unique' : 'index'),
737 'columns' => $columns,
738 );
739 }
740 }
741 $smcFunc['db_free_result']($result);
742
743 return $indexes;
744 }
745
746 ?>