PageRenderTime 54ms CodeModel.GetById 27ms RepoModel.GetById 1ms app.codeStats 0ms

/sources/database/DbPackages-postgresql.php

https://github.com/Arantor/Elkarte
PHP | 825 lines | 748 code | 15 blank | 62 comment | 13 complexity | 10dc003811190ecc0a2be77b400de617 MD5 | raw file
Possible License(s): BSD-3-Clause, LGPL-3.0
  1. <?php
  2. /**
  3. * @name ElkArte Forum
  4. * @copyright ElkArte Forum contributors
  5. * @license BSD http://opensource.org/licenses/BSD-3-Clause
  6. *
  7. * This software is a derived product, based on:
  8. *
  9. * Simple Machines Forum (SMF)
  10. * copyright: 2011 Simple Machines (http://www.simplemachines.org)
  11. * license: BSD, See included LICENSE.TXT for terms and conditions.
  12. *
  13. * @version 1.0 Alpha
  14. *
  15. * This file contains database functionality specifically designed for packages (mods) to utilize.
  16. *
  17. */
  18. if (!defined('ELKARTE'))
  19. die('No access...');
  20. /**
  21. * Add the file functions to the $smcFunc array.
  22. */
  23. function db_packages_init()
  24. {
  25. global $smcFunc, $reservedTables, $db_package_log, $db_prefix;
  26. if (!isset($smcFunc['db_create_table']) || $smcFunc['db_create_table'] != 'smf_db_create_table')
  27. {
  28. $smcFunc += array(
  29. 'db_add_column' => 'smf_db_add_column',
  30. 'db_add_index' => 'smf_db_add_index',
  31. 'db_calculate_type' => 'smf_db_calculate_type',
  32. 'db_change_column' => 'smf_db_change_column',
  33. 'db_create_table' => 'smf_db_create_table',
  34. 'db_drop_table' => 'smf_db_drop_table',
  35. 'db_table_structure' => 'smf_db_table_structure',
  36. 'db_list_columns' => 'smf_db_list_columns',
  37. 'db_list_indexes' => 'smf_db_list_indexes',
  38. 'db_remove_column' => 'smf_db_remove_column',
  39. 'db_remove_index' => 'smf_db_remove_index',
  40. );
  41. $db_package_log = array();
  42. }
  43. // We setup an array of tables we can't do auto-remove on - in case a mod writer cocks it up!
  44. $reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
  45. 'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
  46. 'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
  47. 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
  48. 'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
  49. 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
  50. 'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
  51. 'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
  52. 'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
  53. 'themes', 'topics');
  54. foreach ($reservedTables as $k => $table_name)
  55. $reservedTables[$k] = strtolower($db_prefix . $table_name);
  56. // We in turn may need the extra stuff.
  57. db_extend('extra');
  58. }
  59. /**
  60. * This function can be used to create a table without worrying about schema
  61. * compatabilities across supported database systems.
  62. * - If the table exists will, by default, do nothing.
  63. * - Builds table with columns as passed to it - at least one column must be sent.
  64. * The columns array should have one sub-array for each column - these sub arrays contain:
  65. * 'name' = Column name
  66. * 'type' = Type of column - values from (smallint, mediumint, int, text, varchar, char, tinytext, mediumtext, largetext)
  67. * 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc.
  68. * If not set it will pick a size.
  69. * - 'default' = Default value - do not set if no default required.
  70. * - 'null' => Can it be null (true or false) - if not set default will be false.
  71. * - 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set from what
  72. * it should begin counting.
  73. * - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
  74. * - 'name' => Index name (If left empty it will be generated).
  75. * - 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
  76. * - 'columns' => Array containing columns that form part of key - in the order the index is to be created.
  77. * - parameters: (None yet)
  78. * - if_exists values:
  79. * - 'ignore' will do nothing if the table exists. (And will return true)
  80. * - 'overwrite' will drop any existing table of the same name.
  81. * - 'error' will return false if the table already exists.
  82. *
  83. * @param string $table_name
  84. * @param array $columns in the format specified.
  85. * @param array $indexes default array(), in the format specified.
  86. * @param array $parameters default array()
  87. * @param string $if_exists default 'ignore'
  88. * @param string $error default 'fatal'
  89. */
  90. function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
  91. {
  92. global $reservedTables, $smcFunc, $db_package_log, $db_prefix;
  93. // Strip out the table name, we might not need it in some cases
  94. $real_prefix = preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
  95. // With or without the database name, the fullname looks like this.
  96. $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
  97. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  98. // First - no way do we touch our tables.
  99. if (in_array(strtolower($table_name), $reservedTables))
  100. return false;
  101. // Log that we'll want to remove this on uninstall.
  102. $db_package_log[] = array('remove_table', $table_name);
  103. // This... my friends... is a function in a half - let's start by checking if the table exists!
  104. $tables = $smcFunc['db_list_tables']();
  105. if (in_array($full_table_name, $tables))
  106. {
  107. // This is a sad day... drop the table? If not, return false (error) by default.
  108. if ($if_exists == 'overwrite')
  109. $smcFunc['db_drop_table']($table_name);
  110. else
  111. return $if_exists == 'ignore';
  112. }
  113. // If we've got this far - good news - no table exists. We can build our own!
  114. $smcFunc['db_transaction']('begin');
  115. $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
  116. foreach ($columns as $column)
  117. {
  118. // If we have an auto increment do it!
  119. if (!empty($column['auto']))
  120. {
  121. $smcFunc['db_query']('', '
  122. CREATE SEQUENCE ' . $table_name . '_seq',
  123. array(
  124. 'security_override' => true,
  125. )
  126. );
  127. $default = 'default nextval(\'' . $table_name . '_seq\')';
  128. }
  129. elseif (isset($column['default']) && $column['default'] !== null)
  130. $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
  131. else
  132. $default = '';
  133. // Sort out the size...
  134. $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
  135. list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
  136. if ($size !== null)
  137. $type = $type . '(' . $size . ')';
  138. // Now just put it together!
  139. $table_query .= "\n\t\"" . $column['name'] . '" ' . $type . ' ' . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
  140. }
  141. // Loop through the indexes a sec...
  142. $index_queries = array();
  143. foreach ($indexes as $index)
  144. {
  145. $columns = implode(',', $index['columns']);
  146. // Primary goes in the table...
  147. if (isset($index['type']) && $index['type'] == 'primary')
  148. $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
  149. else
  150. {
  151. if (empty($index['name']))
  152. $index['name'] = implode('_', $index['columns']);
  153. $index_queries[] = 'CREATE ' . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
  154. }
  155. }
  156. // No trailing commas!
  157. if (substr($table_query, -1) == ',')
  158. $table_query = substr($table_query, 0, -1);
  159. $table_query .= ')';
  160. // Create the table!
  161. $smcFunc['db_query']('', $table_query,
  162. array(
  163. 'security_override' => true,
  164. )
  165. );
  166. // And the indexes...
  167. foreach ($index_queries as $query)
  168. $smcFunc['db_query']('', $query,
  169. array(
  170. 'security_override' => true,
  171. )
  172. );
  173. // Go, go power rangers!
  174. $smcFunc['db_transaction']('commit');
  175. }
  176. /**
  177. * Drop a table.
  178. *
  179. * @param string $table_name
  180. * @param array $parameters default array()
  181. * @param string $error default 'fatal'
  182. */
  183. function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
  184. {
  185. global $reservedTables, $smcFunc, $db_prefix;
  186. // After stripping away the database name, this is what's left.
  187. $real_prefix = preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
  188. // Get some aliases.
  189. $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
  190. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  191. // God no - dropping one of these = bad.
  192. if (in_array(strtolower($table_name), $reservedTables))
  193. return false;
  194. // Does it exist?
  195. if (in_array($full_table_name, $smcFunc['db_list_tables']()))
  196. {
  197. // We can then drop the table.
  198. $smcFunc['db_transaction']('begin');
  199. // the table
  200. $table_query = 'DROP TABLE ' . $table_name;
  201. // and the assosciated sequence, if any
  202. $sequence_query = 'DROP SEQUENCE IF EXISTS ' . $table_name . '_seq';
  203. // drop them
  204. $smcFunc['db_query']('',
  205. $table_query,
  206. array(
  207. 'security_override' => true,
  208. )
  209. );
  210. $smcFunc['db_query']('',
  211. $sequence_query,
  212. array(
  213. 'security_override' => true,
  214. )
  215. );
  216. $smcFunc['db_transaction']('commit');
  217. return true;
  218. }
  219. // Otherwise do 'nout.
  220. return false;
  221. }
  222. /**
  223. * This function adds a column.
  224. *
  225. * @param string $table_name the name of the table
  226. * @param array $column_info with column information
  227. * @param array $parameters default array()
  228. * @param string $if_exists default 'update'
  229. * @param string $error default 'fatal'
  230. */
  231. function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
  232. {
  233. global $smcFunc, $db_package_log, $txt, $db_prefix;
  234. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  235. // Log that we will want to uninstall this!
  236. $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
  237. // Does it exist - if so don't add it again!
  238. $columns = $smcFunc['db_list_columns']($table_name, false);
  239. foreach ($columns as $column)
  240. if ($column == $column_info['name'])
  241. {
  242. // If we're going to overwrite then use change column.
  243. if ($if_exists == 'update')
  244. return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
  245. else
  246. return false;
  247. }
  248. // Get the specifics...
  249. $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
  250. list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
  251. if ($size !== null)
  252. $type = $type . '(' . $size . ')';
  253. // Now add the thing!
  254. $query = '
  255. ALTER TABLE ' . $table_name . '
  256. ADD COLUMN ' . $column_info['name'] . ' ' . $type;
  257. $smcFunc['db_query']('', $query,
  258. array(
  259. 'security_override' => true,
  260. )
  261. );
  262. // If there's more attributes they need to be done via a change on PostgreSQL.
  263. unset($column_info['type'], $column_info['size']);
  264. if (count($column_info) != 1)
  265. return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
  266. else
  267. return true;
  268. }
  269. /**
  270. * Removes a column.
  271. *
  272. * @param string $table_name
  273. * @param string $column_name
  274. * @param array $parameters default array()
  275. * @param string $error default 'fatal'
  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
  310. * @param $old_column
  311. * @param $column_info
  312. * @param array $parameters default array()
  313. * @param string $error default 'fatal'
  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
  461. * @param array $index_info
  462. * @param array $parameters default array()
  463. * @param string $if_exists default 'update'
  464. * @param string $error default 'fatal'
  465. */
  466. function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
  467. {
  468. global $smcFunc, $db_package_log, $db_prefix;
  469. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  470. // No columns = no index.
  471. if (empty($index_info['columns']))
  472. return false;
  473. $columns = implode(',', $index_info['columns']);
  474. // No name - make it up!
  475. if (empty($index_info['name']))
  476. {
  477. // No need for primary.
  478. if (isset($index_info['type']) && $index_info['type'] == 'primary')
  479. $index_info['name'] = '';
  480. else
  481. $index_info['name'] = $table_name . implode('_', $index_info['columns']);
  482. }
  483. else
  484. $index_info['name'] = $table_name . $index_info['name'];
  485. // Log that we are going to want to remove this!
  486. $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
  487. // Let's get all our indexes.
  488. $indexes = $smcFunc['db_list_indexes']($table_name, true);
  489. // Do we already have it?
  490. foreach ($indexes as $index)
  491. {
  492. if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
  493. {
  494. // If we want to overwrite simply remove the current one then continue.
  495. if ($if_exists != 'update' || $index['type'] == 'primary')
  496. return false;
  497. else
  498. $smcFunc['db_remove_index']($table_name, $index_info['name']);
  499. }
  500. }
  501. // If we're here we know we don't have the index - so just add it.
  502. if (!empty($index_info['type']) && $index_info['type'] == 'primary')
  503. {
  504. $smcFunc['db_query']('', '
  505. ALTER TABLE ' . $table_name . '
  506. ADD PRIMARY KEY (' . $columns . ')',
  507. array(
  508. 'security_override' => true,
  509. )
  510. );
  511. }
  512. else
  513. {
  514. $smcFunc['db_query']('', '
  515. CREATE ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
  516. array(
  517. 'security_override' => true,
  518. )
  519. );
  520. }
  521. }
  522. /**
  523. * Remove an index.
  524. *
  525. * @param string $table_name
  526. * @param string $index_name
  527. * @param array$parameters default array()
  528. * @param string $error default 'fatal'
  529. */
  530. function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
  531. {
  532. global $smcFunc, $db_prefix;
  533. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  534. // Better exist!
  535. $indexes = $smcFunc['db_list_indexes']($table_name, true);
  536. if ($index_name != 'primary')
  537. $index_name = $table_name . '_' . $index_name;
  538. foreach ($indexes as $index)
  539. {
  540. // If the name is primary we want the primary key!
  541. if ($index['type'] == 'primary' && $index_name == 'primary')
  542. {
  543. // Dropping primary key is odd...
  544. $smcFunc['db_query']('', '
  545. ALTER TABLE ' . $table_name . '
  546. DROP CONSTRAINT ' . $index['name'],
  547. array(
  548. 'security_override' => true,
  549. )
  550. );
  551. return true;
  552. }
  553. if ($index['name'] == $index_name)
  554. {
  555. // Drop the bugger...
  556. $smcFunc['db_query']('', '
  557. DROP INDEX ' . $index_name,
  558. array(
  559. 'security_override' => true,
  560. )
  561. );
  562. return true;
  563. }
  564. }
  565. // Not to be found ;(
  566. return false;
  567. }
  568. /**
  569. * Get the schema formatted name for a type.
  570. *
  571. * @param string $type_name
  572. * @param $type_size
  573. * @param $reverse
  574. */
  575. function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
  576. {
  577. // Let's be sure it's lowercase MySQL likes both, others no.
  578. $type_name = strtolower($type_name);
  579. // Generic => Specific.
  580. if (!$reverse)
  581. {
  582. $types = array(
  583. 'varchar' => 'character varying',
  584. 'char' => 'character',
  585. 'mediumint' => 'int',
  586. 'tinyint' => 'smallint',
  587. 'tinytext' => 'character varying',
  588. 'mediumtext' => 'text',
  589. 'largetext' => 'text',
  590. );
  591. }
  592. else
  593. {
  594. $types = array(
  595. 'character varying' => 'varchar',
  596. 'character' => 'char',
  597. 'integer' => 'int',
  598. );
  599. }
  600. // Got it? Change it!
  601. if (isset($types[$type_name]))
  602. {
  603. if ($type_name == 'tinytext')
  604. $type_size = 255;
  605. $type_name = $types[$type_name];
  606. }
  607. // Numbers don't have a size.
  608. if (strpos($type_name, 'int') !== false)
  609. $type_size = null;
  610. return array($type_name, $type_size);
  611. }
  612. /**
  613. * Get table structure.
  614. *
  615. * @param string $table_name
  616. * @param array $parameters default array()
  617. */
  618. function smf_db_table_structure($table_name, $parameters = array())
  619. {
  620. global $smcFunc, $db_prefix;
  621. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  622. return array(
  623. 'name' => $table_name,
  624. 'columns' => $smcFunc['db_list_columns']($table_name, true),
  625. 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
  626. );
  627. }
  628. /**
  629. * Return column information for a table.
  630. *
  631. * @param string $table_name
  632. * @param bool $detail
  633. * @param array $parameters default array()
  634. * @return mixed
  635. */
  636. function smf_db_list_columns($table_name, $detail = false, $parameters = array())
  637. {
  638. global $smcFunc, $db_prefix;
  639. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  640. $result = $smcFunc['db_query']('', '
  641. SELECT column_name, column_default, is_nullable, data_type, character_maximum_length
  642. FROM information_schema.columns
  643. WHERE table_name = \'' . $table_name . '\'
  644. ORDER BY ordinal_position',
  645. array(
  646. 'security_override' => true,
  647. )
  648. );
  649. $columns = array();
  650. while ($row = $smcFunc['db_fetch_assoc']($result))
  651. {
  652. if (!$detail)
  653. {
  654. $columns[] = $row['column_name'];
  655. }
  656. else
  657. {
  658. $auto = false;
  659. // What is the default?
  660. if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
  661. {
  662. $default = null;
  663. $auto = true;
  664. }
  665. elseif (trim($row['column_default']) != '')
  666. $default = strpos($row['column_default'], '::') === false ? $row['column_default'] : substr($row['column_default'], 0, strpos($row['column_default'], '::'));
  667. else
  668. $default = null;
  669. // Make the type generic.
  670. list ($type, $size) = $smcFunc['db_calculate_type']($row['data_type'], $row['character_maximum_length'], true);
  671. $columns[$row['column_name']] = array(
  672. 'name' => $row['column_name'],
  673. 'null' => $row['is_nullable'] ? true : false,
  674. 'default' => $default,
  675. 'type' => $type,
  676. 'size' => $size,
  677. 'auto' => $auto,
  678. );
  679. }
  680. }
  681. $smcFunc['db_free_result']($result);
  682. return $columns;
  683. }
  684. /**
  685. * Get index information.
  686. *
  687. * @param string $table_name
  688. * @param bool $detail
  689. * @param array $parameters
  690. * @return mixed
  691. */
  692. function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
  693. {
  694. global $smcFunc, $db_prefix;
  695. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  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. $columns = explode(',', $matches[1]);
  716. if (empty($columns))
  717. continue;
  718. foreach ($columns as $k => $v)
  719. $columns[$k] = trim($v);
  720. // Fix up the name to be consistent cross databases
  721. if (substr($row['name'], -5) == '_pkey' && $row['is_primary'] == 1)
  722. $row['name'] = 'PRIMARY';
  723. else
  724. $row['name'] = str_replace($table_name . '_', '', $row['name']);
  725. if (!$detail)
  726. $indexes[] = $row['name'];
  727. else
  728. {
  729. $indexes[$row['name']] = array(
  730. 'name' => $row['name'],
  731. 'type' => $row['is_primary'] ? 'primary' : ($row['is_unique'] ? 'unique' : 'index'),
  732. 'columns' => $columns,
  733. );
  734. }
  735. }
  736. $smcFunc['db_free_result']($result);
  737. return $indexes;
  738. }