DbPackages-postgresql.php 26 KB

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