DbPackages-postgresql.php 24 KB

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