database.pgsql.inc

Database interface code for PostgreSQL database servers.

File

drupal-6.x/includes/database.pgsql.inc
View source
  1. <?php
  2. /**
  3. * @file
  4. * Database interface code for PostgreSQL database servers.
  5. */
  6. /**
  7. * @ingroup database
  8. * @{
  9. */
  10. /**
  11. * Report database status.
  12. */
  13. function db_status_report() {
  14. $t = get_t();
  15. $version = db_version();
  16. $form['pgsql'] = array(
  17. 'title' => $t('PostgreSQL database'),
  18. 'value' => $version,
  19. );
  20. if (version_compare($version, DRUPAL_MINIMUM_PGSQL) < 0) {
  21. $form['pgsql']['severity'] = REQUIREMENT_ERROR;
  22. $form['pgsql']['description'] = $t('Your PostgreSQL Server is too old. Drupal requires at least PostgreSQL %version.', array('%version' => DRUPAL_MINIMUM_PGSQL));
  23. }
  24. return $form;
  25. }
  26. /**
  27. * Returns the version of the database server currently in use.
  28. *
  29. * @return Database server version
  30. */
  31. function db_version() {
  32. return db_result(db_query("SHOW SERVER_VERSION"));
  33. }
  34. /**
  35. * Initialize a database connection.
  36. */
  37. function db_connect($url) {
  38. // Check if PostgreSQL support is present in PHP
  39. if (!function_exists('pg_connect')) {
  40. _db_error_page('Unable to use the PostgreSQL database because the PostgreSQL extension for PHP is not installed. Check your <code>php.ini</code> to see how you can enable it.');
  41. }
  42. $url = parse_url($url);
  43. $conn_string = '';
  44. // Decode urlencoded information in the db connection string
  45. if (isset($url['user'])) {
  46. $conn_string .= ' user='. urldecode($url['user']);
  47. }
  48. if (isset($url['pass'])) {
  49. $conn_string .= ' password='. urldecode($url['pass']);
  50. }
  51. if (isset($url['host'])) {
  52. $conn_string .= ' host='. urldecode($url['host']);
  53. }
  54. if (isset($url['path'])) {
  55. $conn_string .= ' dbname='. substr(urldecode($url['path']), 1);
  56. }
  57. if (isset($url['port'])) {
  58. $conn_string .= ' port='. urldecode($url['port']);
  59. }
  60. // pg_last_error() does not return a useful error message for database
  61. // connection errors. We must turn on error tracking to get at a good error
  62. // message, which will be stored in $php_errormsg.
  63. $track_errors_previous = ini_get('track_errors');
  64. ini_set('track_errors', 1);
  65. $connection = @pg_connect($conn_string);
  66. if (!$connection) {
  67. require_once './includes/unicode.inc';
  68. _db_error_page(decode_entities($php_errormsg));
  69. }
  70. // Restore error tracking setting
  71. ini_set('track_errors', $track_errors_previous);
  72. pg_query($connection, "set client_encoding=\"UTF8\"");
  73. return $connection;
  74. }
  75. /**
  76. * Runs a basic query in the active database.
  77. *
  78. * User-supplied arguments to the query should be passed in as separate
  79. * parameters so that they can be properly escaped to avoid SQL injection
  80. * attacks.
  81. *
  82. * @param $query
  83. * A string containing an SQL query.
  84. * @param ...
  85. * A variable number of arguments which are substituted into the query
  86. * using printf() syntax. Instead of a variable number of query arguments,
  87. * you may also pass a single array containing the query arguments.
  88. *
  89. * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
  90. * in '') and %%.
  91. *
  92. * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
  93. * and TRUE values to decimal 1.
  94. *
  95. * @return
  96. * A database query result resource, or FALSE if the query was not
  97. * executed correctly.
  98. */
  99. function db_query($query) {
  100. $args = func_get_args();
  101. array_shift($args);
  102. $query = db_prefix_tables($query);
  103. if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
  104. $args = $args[0];
  105. }
  106. _db_query_callback($args, TRUE);
  107. $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  108. return _db_query($query);
  109. }
  110. /**
  111. * Helper function for db_query().
  112. */
  113. function _db_query($query, $debug = 0) {
  114. global $active_db, $last_result, $queries;
  115. if (variable_get('dev_query', 0)) {
  116. list($usec, $sec) = explode(' ', microtime());
  117. $timer = (float)$usec + (float)$sec;
  118. }
  119. $last_result = pg_query($active_db, $query);
  120. if (variable_get('dev_query', 0)) {
  121. $bt = debug_backtrace();
  122. $query = $bt[2]['function'] ."\n". $query;
  123. list($usec, $sec) = explode(' ', microtime());
  124. $stop = (float)$usec + (float)$sec;
  125. $diff = $stop - $timer;
  126. $queries[] = array($query, $diff);
  127. }
  128. if ($debug) {
  129. print '<p>query: '. $query .'<br />error:'. pg_last_error($active_db) .'</p>';
  130. }
  131. if ($last_result !== FALSE) {
  132. return $last_result;
  133. }
  134. else {
  135. // Indicate to drupal_error_handler that this is a database error.
  136. ${DB_ERROR} = TRUE;
  137. trigger_error(check_plain(pg_last_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
  138. return FALSE;
  139. }
  140. }
  141. /**
  142. * Fetch one result row from the previous query as an object.
  143. *
  144. * @param $result
  145. * A database query result resource, as returned from db_query().
  146. * @return
  147. * An object representing the next row of the result, or FALSE. The attributes
  148. * of this object are the table fields selected by the query.
  149. */
  150. function db_fetch_object($result) {
  151. if ($result) {
  152. return pg_fetch_object($result);
  153. }
  154. }
  155. /**
  156. * Fetch one result row from the previous query as an array.
  157. *
  158. * @param $result
  159. * A database query result resource, as returned from db_query().
  160. * @return
  161. * An associative array representing the next row of the result, or FALSE.
  162. * The keys of this object are the names of the table fields selected by the
  163. * query, and the values are the field values for this result row.
  164. */
  165. function db_fetch_array($result) {
  166. if ($result) {
  167. return pg_fetch_assoc($result);
  168. }
  169. }
  170. /**
  171. * Return an individual result field from the previous query.
  172. *
  173. * Only use this function if exactly one field is being selected; otherwise,
  174. * use db_fetch_object() or db_fetch_array().
  175. *
  176. * @param $result
  177. * A database query result resource, as returned from db_query().
  178. * @return
  179. * The resulting field or FALSE.
  180. */
  181. function db_result($result) {
  182. if ($result && pg_num_rows($result) > 0) {
  183. $array = pg_fetch_row($result);
  184. return $array[0];
  185. }
  186. return FALSE;
  187. }
  188. /**
  189. * Determine whether the previous query caused an error.
  190. */
  191. function db_error() {
  192. global $active_db;
  193. return pg_last_error($active_db);
  194. }
  195. /**
  196. * Returns the last insert id. This function is thread safe.
  197. *
  198. * @param $table
  199. * The name of the table you inserted into.
  200. * @param $field
  201. * The name of the autoincrement field.
  202. */
  203. function db_last_insert_id($table, $field) {
  204. return db_result(db_query("SELECT CURRVAL('{". db_escape_table($table) ."}_". db_escape_table($field) ."_seq')"));
  205. }
  206. /**
  207. * Determine the number of rows changed by the preceding query.
  208. */
  209. function db_affected_rows() {
  210. global $last_result;
  211. return empty($last_result) ? 0 : pg_affected_rows($last_result);
  212. }
  213. /**
  214. * Runs a limited-range query in the active database.
  215. *
  216. * Use this as a substitute for db_query() when a subset of the query
  217. * is to be returned.
  218. * User-supplied arguments to the query should be passed in as separate
  219. * parameters so that they can be properly escaped to avoid SQL injection
  220. * attacks.
  221. *
  222. * @param $query
  223. * A string containing an SQL query.
  224. * @param ...
  225. * A variable number of arguments which are substituted into the query
  226. * using printf() syntax. Instead of a variable number of query arguments,
  227. * you may also pass a single array containing the query arguments.
  228. * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
  229. * in '') and %%.
  230. *
  231. * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
  232. * and TRUE values to decimal 1.
  233. *
  234. * @param $from
  235. * The first result row to return.
  236. * @param $count
  237. * The maximum number of result rows to return.
  238. * @return
  239. * A database query result resource, or FALSE if the query was not executed
  240. * correctly.
  241. */
  242. function db_query_range($query) {
  243. $args = func_get_args();
  244. $count = array_pop($args);
  245. $from = array_pop($args);
  246. array_shift($args);
  247. $query = db_prefix_tables($query);
  248. if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
  249. $args = $args[0];
  250. }
  251. _db_query_callback($args, TRUE);
  252. $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  253. $query .= ' LIMIT '. (int)$count .' OFFSET '. (int)$from;
  254. return _db_query($query);
  255. }
  256. /**
  257. * Runs a SELECT query and stores its results in a temporary table.
  258. *
  259. * Use this as a substitute for db_query() when the results need to be stored
  260. * in a temporary table.
  261. *
  262. * User-supplied arguments to the query should be passed in as separate parameters
  263. * so that they can be properly escaped to avoid SQL injection attacks.
  264. *
  265. * Note that if you need to know how many results were returned, you should do
  266. * a SELECT COUNT(*) on the temporary table afterwards. db_affected_rows() does
  267. * not give consistent result across different database types in this case.
  268. *
  269. * @param $query
  270. * A string containing a normal SELECT SQL query.
  271. * @param ...
  272. * A variable number of arguments which are substituted into the query
  273. * using printf() syntax. The query arguments can be enclosed in one
  274. * array instead.
  275. * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
  276. * in '') and %%.
  277. *
  278. * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
  279. * and TRUE values to decimal 1.
  280. * @param $table
  281. * The name of the temporary table to select into. This name will not be
  282. * prefixed as there is no risk of collision.
  283. *
  284. * @return
  285. * A database query result resource, or FALSE if the query was not executed
  286. * correctly.
  287. */
  288. function db_query_temporary($query) {
  289. $args = func_get_args();
  290. $tablename = array_pop($args);
  291. array_shift($args);
  292. $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' AS SELECT', db_prefix_tables($query));
  293. if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
  294. $args = $args[0];
  295. }
  296. _db_query_callback($args, TRUE);
  297. $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  298. return _db_query($query);
  299. }
  300. /**
  301. * Returns a properly formatted Binary Large OBject value.
  302. * In case of PostgreSQL encodes data for insert into bytea field.
  303. *
  304. * @param $data
  305. * Data to encode.
  306. * @return
  307. * Encoded data.
  308. */
  309. function db_encode_blob($data) {
  310. return "'". pg_escape_bytea($data) ."'";
  311. }
  312. /**
  313. * Returns text from a Binary Large OBject value.
  314. * In case of PostgreSQL decodes data after select from bytea field.
  315. *
  316. * @param $data
  317. * Data to decode.
  318. * @return
  319. * Decoded data.
  320. */
  321. function db_decode_blob($data) {
  322. return pg_unescape_bytea($data);
  323. }
  324. /**
  325. * Prepare user input for use in a database query, preventing SQL injection attacks.
  326. * Note: This function requires PostgreSQL 7.2 or later.
  327. */
  328. function db_escape_string($text) {
  329. return pg_escape_string($text);
  330. }
  331. /**
  332. * Lock a table.
  333. * This function automatically starts a transaction.
  334. */
  335. function db_lock_table($table) {
  336. db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN EXCLUSIVE MODE');
  337. }
  338. /**
  339. * Unlock all locked tables.
  340. * This function automatically commits a transaction.
  341. */
  342. function db_unlock_tables() {
  343. db_query('COMMIT');
  344. }
  345. /**
  346. * Check if a table exists.
  347. *
  348. * @param $table
  349. * The name of the table.
  350. *
  351. * @return
  352. * TRUE if the table exists, and FALSE if the table does not exist.
  353. */
  354. function db_table_exists($table) {
  355. return (bool) db_result(db_query("SELECT COUNT(*) FROM pg_class WHERE relname = '{". db_escape_table($table) ."}'"));
  356. }
  357. /**
  358. * Check if a column exists in the given table.
  359. *
  360. * @param $table
  361. * The name of the table.
  362. * @param $column
  363. * The name of the column.
  364. *
  365. * @return
  366. * TRUE if the column exists, and FALSE if the column does not exist.
  367. */
  368. function db_column_exists($table, $column) {
  369. return (bool) db_result(db_query("SELECT COUNT(pg_attribute.attname) FROM pg_class, pg_attribute WHERE pg_attribute.attrelid = pg_class.oid AND pg_class.relname = '{". db_escape_table($table) ."}' AND attname = '". db_escape_table($column) ."'"));
  370. }
  371. /**
  372. * Verify if the database is set up correctly.
  373. */
  374. function db_check_setup() {
  375. $t = get_t();
  376. $encoding = db_result(db_query('SHOW server_encoding'));
  377. if (!in_array(strtolower($encoding), array('unicode', 'utf8'))) {
  378. drupal_set_message($t('Your PostgreSQL database is set up with the wrong character encoding (%encoding). It is possible it will not work as expected. It is advised to recreate it with UTF-8/Unicode encoding. More information can be found in the <a href="@url">PostgreSQL documentation</a>.', array('%encoding' => $encoding, '@url' => 'http://www.postgresql.org/docs/7.4/interactive/multibyte.html')), 'status');
  379. }
  380. }
  381. /**
  382. * @} End of "ingroup database".
  383. */
  384. /**
  385. * @ingroup schemaapi
  386. * @{
  387. */
  388. /**
  389. * This maps a generic data type in combination with its data size
  390. * to the engine-specific data type.
  391. */
  392. function db_type_map() {
  393. // Put :normal last so it gets preserved by array_flip. This makes
  394. // it much easier for modules (such as schema.module) to map
  395. // database types back into schema types.
  396. $map = array(
  397. 'varchar:normal' => 'varchar',
  398. 'char:normal' => 'character',
  399. 'text:tiny' => 'text',
  400. 'text:small' => 'text',
  401. 'text:medium' => 'text',
  402. 'text:big' => 'text',
  403. 'text:normal' => 'text',
  404. 'int:tiny' => 'smallint',
  405. 'int:small' => 'smallint',
  406. 'int:medium' => 'int',
  407. 'int:big' => 'bigint',
  408. 'int:normal' => 'int',
  409. 'float:tiny' => 'real',
  410. 'float:small' => 'real',
  411. 'float:medium' => 'real',
  412. 'float:big' => 'double precision',
  413. 'float:normal' => 'real',
  414. 'numeric:normal' => 'numeric',
  415. 'blob:big' => 'bytea',
  416. 'blob:normal' => 'bytea',
  417. 'datetime:normal' => 'timestamp without time zone',
  418. 'serial:tiny' => 'serial',
  419. 'serial:small' => 'serial',
  420. 'serial:medium' => 'serial',
  421. 'serial:big' => 'bigserial',
  422. 'serial:normal' => 'serial',
  423. );
  424. return $map;
  425. }
  426. /**
  427. * Generate SQL to create a new table from a Drupal schema definition.
  428. *
  429. * @param $name
  430. * The name of the table to create.
  431. * @param $table
  432. * A Schema API table definition array.
  433. * @return
  434. * An array of SQL statements to create the table.
  435. */
  436. function db_create_table_sql($name, $table) {
  437. $sql_fields = array();
  438. foreach ($table['fields'] as $field_name => $field) {
  439. $sql_fields[] = _db_create_field_sql($field_name, _db_process_field($field));
  440. }
  441. $sql_keys = array();
  442. if (isset($table['primary key']) && is_array($table['primary key'])) {
  443. $sql_keys[] = 'PRIMARY KEY ('. implode(', ', $table['primary key']) .')';
  444. }
  445. if (isset($table['unique keys']) && is_array($table['unique keys'])) {
  446. foreach ($table['unique keys'] as $key_name => $key) {
  447. $sql_keys[] = 'CONSTRAINT {'. $name .'}_'. $key_name .'_key UNIQUE ('. implode(', ', $key) .')';
  448. }
  449. }
  450. $sql = "CREATE TABLE {". $name ."} (\n\t";
  451. $sql .= implode(",\n\t", $sql_fields);
  452. if (count($sql_keys) > 0) {
  453. $sql .= ",\n\t";
  454. }
  455. $sql .= implode(",\n\t", $sql_keys);
  456. $sql .= "\n)";
  457. $statements[] = $sql;
  458. if (isset($table['indexes']) && is_array($table['indexes'])) {
  459. foreach ($table['indexes'] as $key_name => $key) {
  460. $statements[] = _db_create_index_sql($name, $key_name, $key);
  461. }
  462. }
  463. return $statements;
  464. }
  465. function _db_create_index_sql($table, $name, $fields) {
  466. $query = 'CREATE INDEX {'. $table .'}_'. $name .'_idx ON {'. $table .'} (';
  467. $query .= _db_create_key_sql($fields) .')';
  468. return $query;
  469. }
  470. function _db_create_key_sql($fields) {
  471. $ret = array();
  472. foreach ($fields as $field) {
  473. if (is_array($field)) {
  474. $ret[] = 'substr('. $field[0] .', 1, '. $field[1] .')';
  475. }
  476. else {
  477. $ret[] = $field;
  478. }
  479. }
  480. return implode(', ', $ret);
  481. }
  482. function _db_create_keys(&$ret, $table, $new_keys) {
  483. if (isset($new_keys['primary key'])) {
  484. db_add_primary_key($ret, $table, $new_keys['primary key']);
  485. }
  486. if (isset($new_keys['unique keys'])) {
  487. foreach ($new_keys['unique keys'] as $name => $fields) {
  488. db_add_unique_key($ret, $table, $name, $fields);
  489. }
  490. }
  491. if (isset($new_keys['indexes'])) {
  492. foreach ($new_keys['indexes'] as $name => $fields) {
  493. db_add_index($ret, $table, $name, $fields);
  494. }
  495. }
  496. }
  497. /**
  498. * Set database-engine specific properties for a field.
  499. *
  500. * @param $field
  501. * A field description array, as specified in the schema documentation.
  502. */
  503. function _db_process_field($field) {
  504. if (!isset($field['size'])) {
  505. $field['size'] = 'normal';
  506. }
  507. // Set the correct database-engine specific datatype.
  508. if (!isset($field['pgsql_type'])) {
  509. $map = db_type_map();
  510. $field['pgsql_type'] = $map[$field['type'] .':'. $field['size']];
  511. }
  512. if ($field['type'] == 'serial') {
  513. unset($field['not null']);
  514. }
  515. return $field;
  516. }
  517. /**
  518. * Create an SQL string for a field to be used in table creation or alteration.
  519. *
  520. * Before passing a field out of a schema definition into this function it has
  521. * to be processed by _db_process_field().
  522. *
  523. * @param $name
  524. * Name of the field.
  525. * @param $spec
  526. * The field specification, as per the schema data structure format.
  527. */
  528. function _db_create_field_sql($name, $spec) {
  529. $sql = $name .' '. $spec['pgsql_type'];
  530. if ($spec['type'] == 'serial') {
  531. unset($spec['not null']);
  532. }
  533. if (in_array($spec['type'], array('varchar', 'char', 'text')) && isset($spec['length'])) {
  534. $sql .= '('. $spec['length'] .')';
  535. }
  536. elseif (isset($spec['precision']) && isset($spec['scale'])) {
  537. $sql .= '('. $spec['precision'] .', '. $spec['scale'] .')';
  538. }
  539. if (!empty($spec['unsigned'])) {
  540. $sql .= " CHECK ($name >= 0)";
  541. }
  542. if (isset($spec['not null']) && $spec['not null']) {
  543. $sql .= ' NOT NULL';
  544. }
  545. if (isset($spec['default'])) {
  546. $default = is_string($spec['default']) ? "'". $spec['default'] ."'" : $spec['default'];
  547. $sql .= " default $default";
  548. }
  549. return $sql;
  550. }
  551. /**
  552. * Rename a table.
  553. *
  554. * @param $ret
  555. * Array to which query results will be added.
  556. * @param $table
  557. * The table to be renamed.
  558. * @param $new_name
  559. * The new name for the table.
  560. */
  561. function db_rename_table(&$ret, $table, $new_name) {
  562. $ret[] = update_sql('ALTER TABLE {'. $table .'} RENAME TO {'. $new_name .'}');
  563. }
  564. /**
  565. * Drop a table.
  566. *
  567. * @param $ret
  568. * Array to which query results will be added.
  569. * @param $table
  570. * The table to be dropped.
  571. */
  572. function db_drop_table(&$ret, $table) {
  573. $ret[] = update_sql('DROP TABLE {'. $table .'}');
  574. }
  575. /**
  576. * Add a new field to a table.
  577. *
  578. * @param $ret
  579. * Array to which query results will be added.
  580. * @param $table
  581. * Name of the table to be altered.
  582. * @param $field
  583. * Name of the field to be added.
  584. * @param $spec
  585. * The field specification array, as taken from a schema definition.
  586. * The specification may also contain the key 'initial', the newly
  587. * created field will be set to the value of the key in all rows.
  588. * This is most useful for creating NOT NULL columns with no default
  589. * value in existing tables.
  590. * @param $new_keys
  591. * Optional keys and indexes specification to be created on the
  592. * table along with adding the field. The format is the same as a
  593. * table specification but without the 'fields' element. If you are
  594. * adding a type 'serial' field, you MUST specify at least one key
  595. * or index including it in this array. See db_change_field() for more
  596. * explanation why.
  597. */
  598. function db_add_field(&$ret, $table, $field, $spec, $new_keys = array()) {
  599. $fixnull = FALSE;
  600. if (!empty($spec['not null']) && !isset($spec['default'])) {
  601. $fixnull = TRUE;
  602. $spec['not null'] = FALSE;
  603. }
  604. $query = 'ALTER TABLE {'. $table .'} ADD COLUMN ';
  605. $query .= _db_create_field_sql($field, _db_process_field($spec));
  606. $ret[] = update_sql($query);
  607. if (isset($spec['initial'])) {
  608. // All this because update_sql does not support %-placeholders.
  609. $sql = 'UPDATE {'. $table .'} SET '. $field .' = '. db_type_placeholder($spec['type']);
  610. $result = db_query($sql, $spec['initial']);
  611. $ret[] = array('success' => $result !== FALSE, 'query' => check_plain($sql .' ('. $spec['initial'] .')'));
  612. }
  613. if ($fixnull) {
  614. $ret[] = update_sql("ALTER TABLE {". $table ."} ALTER $field SET NOT NULL");
  615. }
  616. if (isset($new_keys)) {
  617. _db_create_keys($ret, $table, $new_keys);
  618. }
  619. }
  620. /**
  621. * Drop a field.
  622. *
  623. * @param $ret
  624. * Array to which query results will be added.
  625. * @param $table
  626. * The table to be altered.
  627. * @param $field
  628. * The field to be dropped.
  629. */
  630. function db_drop_field(&$ret, $table, $field) {
  631. $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP COLUMN '. $field);
  632. }
  633. /**
  634. * Set the default value for a field.
  635. *
  636. * @param $ret
  637. * Array to which query results will be added.
  638. * @param $table
  639. * The table to be altered.
  640. * @param $field
  641. * The field to be altered.
  642. * @param $default
  643. * Default value to be set. NULL for 'default NULL'.
  644. */
  645. function db_field_set_default(&$ret, $table, $field, $default) {
  646. if ($default == NULL) {
  647. $default = 'NULL';
  648. }
  649. else {
  650. $default = is_string($default) ? "'$default'" : $default;
  651. }
  652. $ret[] = update_sql('ALTER TABLE {'. $table .'} ALTER COLUMN '. $field .' SET DEFAULT '. $default);
  653. }
  654. /**
  655. * Set a field to have no default value.
  656. *
  657. * @param $ret
  658. * Array to which query results will be added.
  659. * @param $table
  660. * The table to be altered.
  661. * @param $field
  662. * The field to be altered.
  663. */
  664. function db_field_set_no_default(&$ret, $table, $field) {
  665. $ret[] = update_sql('ALTER TABLE {'. $table .'} ALTER COLUMN '. $field .' DROP DEFAULT');
  666. }
  667. /**
  668. * Add a primary key.
  669. *
  670. * @param $ret
  671. * Array to which query results will be added.
  672. * @param $table
  673. * The table to be altered.
  674. * @param $fields
  675. * Fields for the primary key.
  676. */
  677. function db_add_primary_key(&$ret, $table, $fields) {
  678. $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD PRIMARY KEY ('.
  679. implode(',', $fields) .')');
  680. }
  681. /**
  682. * Drop the primary key.
  683. *
  684. * @param $ret
  685. * Array to which query results will be added.
  686. * @param $table
  687. * The table to be altered.
  688. */
  689. function db_drop_primary_key(&$ret, $table) {
  690. $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP CONSTRAINT {'. $table .'}_pkey');
  691. }
  692. /**
  693. * Add a unique key.
  694. *
  695. * @param $ret
  696. * Array to which query results will be added.
  697. * @param $table
  698. * The table to be altered.
  699. * @param $name
  700. * The name of the key.
  701. * @param $fields
  702. * An array of field names.
  703. */
  704. function db_add_unique_key(&$ret, $table, $name, $fields) {
  705. $name = '{'. $table .'}_'. $name .'_key';
  706. $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD CONSTRAINT '.
  707. $name .' UNIQUE ('. implode(',', $fields) .')');
  708. }
  709. /**
  710. * Drop a unique key.
  711. *
  712. * @param $ret
  713. * Array to which query results will be added.
  714. * @param $table
  715. * The table to be altered.
  716. * @param $name
  717. * The name of the key.
  718. */
  719. function db_drop_unique_key(&$ret, $table, $name) {
  720. $name = '{'. $table .'}_'. $name .'_key';
  721. $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP CONSTRAINT '. $name);
  722. }
  723. /**
  724. * Add an index.
  725. *
  726. * @param $ret
  727. * Array to which query results will be added.
  728. * @param $table
  729. * The table to be altered.
  730. * @param $name
  731. * The name of the index.
  732. * @param $fields
  733. * An array of field names.
  734. */
  735. function db_add_index(&$ret, $table, $name, $fields) {
  736. $ret[] = update_sql(_db_create_index_sql($table, $name, $fields));
  737. }
  738. /**
  739. * Drop an index.
  740. *
  741. * @param $ret
  742. * Array to which query results will be added.
  743. * @param $table
  744. * The table to be altered.
  745. * @param $name
  746. * The name of the index.
  747. */
  748. function db_drop_index(&$ret, $table, $name) {
  749. $name = '{'. $table .'}_'. $name .'_idx';
  750. $ret[] = update_sql('DROP INDEX '. $name);
  751. }
  752. /**
  753. * Change a field definition.
  754. *
  755. * IMPORTANT NOTE: To maintain database portability, you have to explicitly
  756. * recreate all indices and primary keys that are using the changed field.
  757. *
  758. * That means that you have to drop all affected keys and indexes with
  759. * db_drop_{primary_key,unique_key,index}() before calling db_change_field().
  760. * To recreate the keys and indices, pass the key definitions as the
  761. * optional $new_keys argument directly to db_change_field().
  762. *
  763. * For example, suppose you have:
  764. * @code
  765. * $schema['foo'] = array(
  766. * 'fields' => array(
  767. * 'bar' => array('type' => 'int', 'not null' => TRUE)
  768. * ),
  769. * 'primary key' => array('bar')
  770. * );
  771. * @endcode
  772. * and you want to change foo.bar to be type serial, leaving it as the
  773. * primary key. The correct sequence is:
  774. * @code
  775. * db_drop_primary_key($ret, 'foo');
  776. * db_change_field($ret, 'foo', 'bar', 'bar',
  777. * array('type' => 'serial', 'not null' => TRUE),
  778. * array('primary key' => array('bar')));
  779. * @endcode
  780. *
  781. * The reasons for this are due to the different database engines:
  782. *
  783. * On PostgreSQL, changing a field definition involves adding a new field
  784. * and dropping an old one which* causes any indices, primary keys and
  785. * sequences (from serial-type fields) that use the changed field to be dropped.
  786. *
  787. * On MySQL, all type 'serial' fields must be part of at least one key
  788. * or index as soon as they are created. You cannot use
  789. * db_add_{primary_key,unique_key,index}() for this purpose because
  790. * the ALTER TABLE command will fail to add the column without a key
  791. * or index specification. The solution is to use the optional
  792. * $new_keys argument to create the key or index at the same time as
  793. * field.
  794. *
  795. * You could use db_add_{primary_key,unique_key,index}() in all cases
  796. * unless you are converting a field to be type serial. You can use
  797. * the $new_keys argument in all cases.
  798. *
  799. * @param $ret
  800. * Array to which query results will be added.
  801. * @param $table
  802. * Name of the table.
  803. * @param $field
  804. * Name of the field to change.
  805. * @param $field_new
  806. * New name for the field (set to the same as $field if you don't want to change the name).
  807. * @param $spec
  808. * The field specification for the new field.
  809. * @param $new_keys
  810. * Optional keys and indexes specification to be created on the
  811. * table along with changing the field. The format is the same as a
  812. * table specification but without the 'fields' element.
  813. */
  814. function db_change_field(&$ret, $table, $field, $field_new, $spec, $new_keys = array()) {
  815. $ret[] = update_sql('ALTER TABLE {'. $table .'} RENAME "'. $field .'" TO "'. $field .'_old"');
  816. $not_null = isset($spec['not null']) ? $spec['not null'] : FALSE;
  817. unset($spec['not null']);
  818. if (!array_key_exists('size', $spec)) {
  819. $spec['size'] = 'normal';
  820. }
  821. db_add_field($ret, $table, "$field_new", $spec);
  822. // We need to type cast the new column to best transfer the data
  823. // db_type_map will return possiblities that are not 'cast-able'
  824. // such as serial - they must be made 'int' instead.
  825. $map = db_type_map();
  826. $typecast = $map[$spec['type'] .':'. $spec['size']];
  827. if (in_array($typecast, array('serial', 'bigserial', 'numeric'))) {
  828. $typecast = 'int';
  829. }
  830. $ret[] = update_sql('UPDATE {'. $table .'} SET '. $field_new .' = CAST('. $field .'_old AS '. $typecast .')');
  831. if ($not_null) {
  832. $ret[] = update_sql("ALTER TABLE {". $table ."} ALTER $field_new SET NOT NULL");
  833. }
  834. db_drop_field($ret, $table, $field .'_old');
  835. if (isset($new_keys)) {
  836. _db_create_keys($ret, $table, $new_keys);
  837. }
  838. }
  839. /**
  840. * @} End of "ingroup schemaapi".
  841. */