database.pgsql.inc 26.8 KB
Newer Older
Dries's avatar
   
Dries committed
1
2
3
4
5
<?php
// $Id$

/**
 * @file
Dries's avatar
   
Dries committed
6
 * Database interface code for PostgreSQL database servers.
Dries's avatar
   
Dries committed
7
8
9
 */

/**
Dries's avatar
   
Dries committed
10
 * @ingroup database
Dries's avatar
   
Dries committed
11
12
13
 * @{
 */

14
15
16
17
/**
 * Report database status.
 */
function db_status_report() {
18
  $t = get_t();
19

20
  $version = db_version();
21

22
23
24
25
26
27
28
29
30
  $form['pgsql'] = array(
    'title' => $t('PostgreSQL database'),
    'value' => $version,
  );

  if (version_compare($version, DRUPAL_MINIMUM_PGSQL) < 0) {
    $form['pgsql']['severity'] = REQUIREMENT_ERROR;
    $form['pgsql']['description'] = $t('Your PostgreSQL Server is too old. Drupal requires at least PostgreSQL %version.', array('%version' => DRUPAL_MINIMUM_PGSQL));
  }
31
32
33
34

  return $form;
}

35
36
37
38
39
40
41
42
43
/**
 * Returns the version of the database server currently in use.
 *
 * @return Database server version
 */
function db_version() {
  return db_result(db_query("SHOW SERVER_VERSION"));
}

Dries's avatar
   
Dries committed
44
45
46
47
/**
 * Initialize a database connection.
 */
function db_connect($url) {
48
  // Check if MySQL support is present in PHP
49
50
51
52
53
54
55
56
  if (!function_exists('pg_connect')) {
    drupal_maintenance_theme();
    drupal_set_title('PHP PostgreSQL support not enabled');
    print theme('maintenance_page', '<p>We were 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.</p>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
    exit;
  }

Dries's avatar
   
Dries committed
57
  $url = parse_url($url);
58
  $conn_string = '';
Dries's avatar
   
Dries committed
59

60
  // Decode url-encoded information in the db connection string
61
  if (isset($url['user'])) {
62
    $conn_string .= ' user='. urldecode($url['user']);
63
64
  }
  if (isset($url['pass'])) {
65
    $conn_string .= ' password='. urldecode($url['pass']);
66
67
  }
  if (isset($url['host'])) {
68
    $conn_string .= ' host='. urldecode($url['host']);
69
70
  }
  if (isset($url['path'])) {
71
    $conn_string .= ' dbname='. substr(urldecode($url['path']), 1);
72
73
  }
  if (isset($url['port'])) {
74
    $conn_string .= ' port='. urldecode($url['port']);
75
  }
76
77
78
79
80
81
82
83
84
85

  // pg_last_error() does not return a useful error message for database
  // connection errors. We must turn on error tracking to get at a good error
  // message, which will be stored in $php_errormsg.
  $track_errors_previous = ini_get('track_errors');
  ini_set('track_errors', 1);

  $connection = @pg_connect($conn_string);
  if (!$connection) {
    drupal_maintenance_theme();
86
    drupal_set_header('HTTP/1.1 503 Service Unavailable');
87
    drupal_set_title('Unable to connect to database');
88
89
    print theme('maintenance_page', '<p>If you still have to install Drupal, proceed to the <a href="'. base_path() .'install.php">installation page</a>.</p>
<p>If you have already finished installed Drupal, this either means that the username and password information in your <code>settings.php</code> file is incorrect or that we can\'t connect to the PostgreSQL database server. This could mean your hosting provider\'s database server is down.</p>
90
91
92
93
94
95
96
97
98
99
100
101
102
103
<p>The PostgreSQL error was: '. theme('placeholder', decode_entities($php_errormsg)) .'</p>
<p>Currently, the database is '. theme('placeholder', substr($url['path'], 1)) .', the username is '. theme('placeholder', $url['user']) .', and the database server is '. theme('placeholder', $url['host']) .'.</p>
<ul>
  <li>Are you sure you have the correct username and password?</li>
  <li>Are you sure that you have typed the correct hostname?</li>
  <li>Are you sure you have the correct database name?</li>
  <li>Are you sure that the database server is running?</li>
</ul>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
    exit;
  }

  // Restore error tracking setting
  ini_set('track_errors', $track_errors_previous);
Dries's avatar
   
Dries committed
104
105
106
107

  return $connection;
}

108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
/**
 * Runs a basic query in the active database.
 *
 * User-supplied arguments to the query should be passed in as separate
 * parameters so that they can be properly escaped to avoid SQL injection
 * attacks.
 *
 * @param $query
 *   A string containing an SQL query.
 * @param ...
 *   A variable number of arguments which are substituted into the query
 *   using printf() syntax. Instead of a variable number of query arguments,
 *   you may also pass a single array containing the query arguments.
 *
 *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 *   in '') and %%.
 *
 *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 *   and TRUE values to decimal 1.
 *
 * @return
 *   A database query result resource, or FALSE if the query was not
 *   executed correctly.
 */
function db_query($query) {
  $args = func_get_args();
  array_shift($args);
  $query = db_prefix_tables($query);
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
  }
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  return _db_query($query);
}

Dries's avatar
   
Dries committed
144
145
146
147
/**
 * Helper function for db_query().
 */
function _db_query($query, $debug = 0) {
148
  global $active_db, $last_result, $queries;
Dries's avatar
   
Dries committed
149

150
  if (variable_get('dev_query', 0)) {
Dries's avatar
   
Dries committed
151
152
153
154
155
156
    list($usec, $sec) = explode(' ', microtime());
    $timer = (float)$usec + (float)$sec;
  }

  $last_result = pg_query($active_db, $query);

157
  if (variable_get('dev_query', 0)) {
158
    $bt = debug_backtrace();
159
    $query = $bt[2]['function'] ."\n". $query;
Dries's avatar
   
Dries committed
160
161
162
163
164
165
166
    list($usec, $sec) = explode(' ', microtime());
    $stop = (float)$usec + (float)$sec;
    $diff = $stop - $timer;
    $queries[] = array($query, $diff);
  }

  if ($debug) {
167
    print '<p>query: '. $query .'<br />error:'. pg_last_error($active_db) .'</p>';
Dries's avatar
   
Dries committed
168
169
170
171
172
173
  }

  if ($last_result !== FALSE) {
    return $last_result;
  }
  else {
174
175
    // Indicate to drupal_error_handler that this is a database error.
    ${DB_ERROR} = TRUE;
176
    trigger_error(check_plain(pg_last_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
177
    return FALSE;
Dries's avatar
   
Dries committed
178
179
180
181
182
183
184
185
186
  }
}

/**
 * Fetch one result row from the previous query as an object.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
187
188
 *   An object representing the next row of the result, or FALSE. The attributes
 *   of this object are the table fields selected by the query.
Dries's avatar
   
Dries committed
189
190
191
192
193
194
195
196
197
198
199
200
201
 */
function db_fetch_object($result) {
  if ($result) {
    return pg_fetch_object($result);
  }
}

/**
 * Fetch one result row from the previous query as an array.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
Dries's avatar
Dries committed
202
203
 *   An associative array representing the next row of the result, or FALSE.
 *   The keys of this object are the names of the table fields selected by the
204
 *   query, and the values are the field values for this result row.
Dries's avatar
   
Dries committed
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
 */
function db_fetch_array($result) {
  if ($result) {
    return pg_fetch_assoc($result);
  }
}

/**
 * Return an individual result field from the previous query.
 *
 * Only use this function if exactly one field is being selected; otherwise,
 * use db_fetch_object() or db_fetch_array().
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
221
 *   The resulting field or FALSE.
Dries's avatar
   
Dries committed
222
 */
223
224
225
226
function db_result($result) {
  if ($result && pg_num_rows($result) > 0) {
    $array = pg_fetch_row($result);
    return $array[0];
Dries's avatar
   
Dries committed
227
  }
228
  return FALSE;
Dries's avatar
   
Dries committed
229
230
231
232
233
234
}

/**
 * Determine whether the previous query caused an error.
 */
function db_error() {
235
236
  global $active_db;
  return pg_last_error($active_db);
Dries's avatar
   
Dries committed
237
238
239
}

/**
240
 * Returns the last insert id. This function is thread safe.
241
 *
242
243
244
245
 * @param $table
 *   The name of the table you inserted into.
 * @param $field
 *   The name of the autoincrement field.
Dries's avatar
   
Dries committed
246
 */
247
248
function db_last_insert_id($table, $field) {
  return db_result(db_query("SELECT currval('%s_seq')", db_prefix_tables('{'. $table .'}') . '_'. $field));
Dries's avatar
   
Dries committed
249
250
251
252
253
254
255
}

/**
 * Determine the number of rows changed by the preceding query.
 */
function db_affected_rows() {
  global $last_result;
256
  return empty($last_result) ? 0 : pg_affected_rows($last_result);
Dries's avatar
   
Dries committed
257
258
259
260
261
}

/**
 * Runs a limited-range query in the active database.
 *
262
263
264
265
266
 * Use this as a substitute for db_query() when a subset of the query
 * is to be returned.
 * User-supplied arguments to the query should be passed in as separate
 * parameters so that they can be properly escaped to avoid SQL injection
 * attacks.
Dries's avatar
   
Dries committed
267
268
269
270
 *
 * @param $query
 *   A string containing an SQL query.
 * @param ...
271
272
273
274
275
276
277
278
279
 *   A variable number of arguments which are substituted into the query
 *   using printf() syntax. Instead of a variable number of query arguments,
 *   you may also pass a single array containing the query arguments.
 *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 *   in '') and %%.
 *
 *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 *   and TRUE values to decimal 1.
 *
Dries's avatar
   
Dries committed
280
281
282
283
284
285
286
287
288
289
290
291
 * @param $from
 *   The first result row to return.
 * @param $count
 *   The maximum number of result rows to return.
 * @return
 *   A database query result resource, or FALSE if the query was not executed
 *   correctly.
 */
function db_query_range($query) {
  $args = func_get_args();
  $count = array_pop($args);
  $from = array_pop($args);
292
  array_shift($args);
293
294

  $query = db_prefix_tables($query);
295
296
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
Dries's avatar
   
Dries committed
297
  }
298
299
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
300
  $query .= ' LIMIT '. (int)$count .' OFFSET '. (int)$from;
Dries's avatar
   
Dries committed
301
  return _db_query($query);
302
303
304
305
306
307
308
309
310
311
312
313
}

/**
 * Runs a SELECT query and stores its results in a temporary table.
 *
 * Use this as a substitute for db_query() when the results need to stored
 * in a temporary table. Temporary tables exist for the duration of the page
 * request.
 * User-supplied arguments to the query should be passed in as separate parameters
 * so that they can be properly escaped to avoid SQL injection attacks.
 *
 * Note that if you need to know how many results were returned, you should do
314
315
 * a SELECT COUNT(*) on the temporary table afterwards. db_affected_rows() does
 * not give consistent result across different database types in this case.
316
317
318
319
 *
 * @param $query
 *   A string containing a normal SELECT SQL query.
 * @param ...
320
321
322
323
324
325
326
327
328
 *   A variable number of arguments which are substituted into the query
 *   using printf() syntax. The query arguments can be enclosed in one
 *   array instead.
 *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 *   in '') and %%.
 *
 *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 *   and TRUE values to decimal 1.
 *
329
 * @param $table
330
331
 *   The name of the temporary table to select into. This name will not be
 *   prefixed as there is no risk of collision.
332
333
334
335
336
337
338
 * @return
 *   A database query result resource, or FALSE if the query was not executed
 *   correctly.
 */
function db_query_temporary($query) {
  $args = func_get_args();
  $tablename = array_pop($args);
339
  array_shift($args);
340

341
  $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' AS SELECT', db_prefix_tables($query));
342
343
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
344
  }
345
346
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
347
  return _db_query($query);
Dries's avatar
   
Dries committed
348
349
350
351
}

/**
 * Returns a properly formatted Binary Large OBject value.
352
 * In case of PostgreSQL encodes data for insert into bytea field.
Dries's avatar
   
Dries committed
353
354
355
356
357
358
359
 *
 * @param $data
 *   Data to encode.
 * @return
 *  Encoded data.
 */
function db_encode_blob($data) {
360
  return "'". pg_escape_bytea($data) ."'";
Dries's avatar
   
Dries committed
361
362
363
364
}

/**
 * Returns text from a Binary Large OBject value.
365
 * In case of PostgreSQL decodes data after select from bytea field.
Dries's avatar
   
Dries committed
366
367
368
369
370
371
372
 *
 * @param $data
 *   Data to decode.
 * @return
 *  Decoded data.
 */
function db_decode_blob($data) {
373
  return pg_unescape_bytea($data);
Dries's avatar
   
Dries committed
374
375
}

Dries's avatar
   
Dries committed
376
377
378
379
380
381
382
383
/**
 * Prepare user input for use in a database query, preventing SQL injection attacks.
 * Note: This function requires PostgreSQL 7.2 or later.
 */
function db_escape_string($text) {
  return pg_escape_string($text);
}

384
385
386
387
388
/**
 * Lock a table.
 * This function automatically starts a transaction.
 */
function db_lock_table($table) {
389
  db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN EXCLUSIVE MODE');
390
391
392
393
}

/**
 * Unlock all locked tables.
394
 * This function automatically commits a transaction.
395
396
397
398
399
 */
function db_unlock_tables() {
  db_query('COMMIT');
}

drumm's avatar
drumm committed
400
401
402
403
/**
 * Check if a table exists.
 */
function db_table_exists($table) {
404
  return db_result(db_query("SELECT COUNT(*) FROM pg_class WHERE relname = '{". db_escape_table($table) ."}'"));
drumm's avatar
drumm committed
405
406
}

407
408
409
410
411
412
413
/**
 * Check if a column exists in the given table.
 */
function db_column_exists($table, $column) {
  return 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='%s'", $column));
}

414
415
416
417
/**
 * Verify if the database is set up correctly.
 */
function db_check_setup() {
418
419
  $t = get_t();

420
421
  $encoding = db_result(db_query('SHOW server_encoding'));
  if (!in_array(strtolower($encoding), array('unicode', 'utf8'))) {
422
    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');
423
424
425
  }
}

426
427
428
429
430
431
432
433
434
435
436
437
438
439
/**
 * Wraps the given table.field entry with a DISTINCT(). The wrapper is added to
 * the SELECT list entry of the given query and the resulting query is returned.
 * This function only applies the wrapper if a DISTINCT doesn't already exist in
 * the query.
 *
 * @param $table Table containing the field to set as DISTINCT
 * @param $field Field to set as DISTINCT
 * @param $query Query to apply the wrapper to
 * @return SQL query with the DISTINCT wrapper surrounding the given table.field.
 */
function db_distinct_field($table, $field, $query) {
  $field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";
  // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
440
  $query = preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
441
  $query = preg_replace('/(ORDER BY )(?!'. $table .'\.'. $field .')/', '\1'."$table.$field, ", $query);
442
443
444
  return $query;
}

Dries's avatar
   
Dries committed
445
/**
Dries's avatar
   
Dries committed
446
 * @} End of "ingroup database".
Dries's avatar
   
Dries committed
447
448
 */

449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
/**
 * @ingroup schemaapi
 * @{
 */

/**
 * This maps a generic data type in combination with its data size
 * to the engine-specific data type.
 */
function db_type_map() {
  // Put :normal last so it gets preserved by array_flip.  This makes
  // it much easier for modules (such as schema.module) to map
  // database types back into schema types.
  $map = array(
    'varchar:normal' => 'varchar',

    'text:tiny' => 'text',
    'text:small' => 'text',
    'text:medium' => 'text',
    'text:big' => 'text',
    'text:normal' => 'text',

    'int:tiny' => 'smallint',
    'int:small' => 'smallint',
    'int:medium' => 'int',
    'int:big' => 'bigint',
    'int:normal' => 'int',

    'float:tiny' => 'real',
    'float:small' => 'real',
    'float:medium' => 'real',
    'float:big' => 'double precision',
    'float:normal' => 'real',

    'numeric:normal'  => 'numeric',

    'blob:big' => 'bytea',
    'blob:normal' => 'bytea',

    'datetime:normal' => 'timestamp',

    'serial:tiny' => 'serial',
    'serial:small' => 'serial',
    'serial:medium' => 'serial',
    'serial:big' => 'bigserial',
    'serial:normal' => 'serial',
  );
  return $map;
}

/**
500
 * Generate SQL to create a new table from a Drupal schema definition.
501
 *
502
503
 * @param $name
 *   The name of the table to create.
504
 * @param $table
505
 *   A Schema API table definition array.
506
507
508
 * @return
 *   An array of SQL statements to create the table.
 */
509
function db_create_table_sql($name, $table) {
510
  $sql_fields = array();
511
512
  foreach ($table['fields'] as $field_name => $field) {
    $sql_fields[] = _db_create_field_sql($field_name, _db_process_field($field));
513
514
515
516
517
518
519
  }

  $sql_keys = array();
  if (isset($table['primary key']) && is_array($table['primary key'])) {
    $sql_keys[] = 'PRIMARY KEY ('. implode(', ', $table['primary key']) .')';
  }
  if (isset($table['unique keys']) && is_array($table['unique keys'])) {
520
521
    foreach ($table['unique keys'] as $key_name => $key) {
      $sql_keys[] = 'CONSTRAINT {'. $name .'}_'. $key_name .'_key UNIQUE ('. implode(', ', $key) .')';
522
523
524
    }
  }

525
  $sql = "CREATE TABLE {". $name ."} (\n\t";
526
527
528
529
530
531
532
533
534
  $sql .= implode(",\n\t", $sql_fields);
  if (count($sql_keys) > 0) {
    $sql .= ",\n\t";
  }
  $sql .= implode(",\n\t", $sql_keys);
  $sql .= "\n)";
  $statements[] = $sql;

  if (isset($table['indexes']) && is_array($table['indexes'])) {
535
536
    foreach ($table['indexes'] as $key_name => $key) {
      $statements[] = _db_create_index_sql($name, $key_name, $key);
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
    }
  }

  return $statements;
}

function _db_create_index_sql($table, $name, $fields) {
  $query = 'CREATE INDEX {'. $table .'}_'. $name .'_idx ON {'. $table .'} (';
  $query .= _db_create_key_sql($fields) .')';
  return $query;
}

function _db_create_key_sql($fields) {
  $ret = array();
  foreach ($fields as $field) {
    if (is_array($field)) {
      $ret[] = 'substr('. $field[0] .', 1, '. $field[1] .')';
    }
    else {
      $ret[] = $field;
    }
  }
  return implode(', ', $ret);
}

/**
 * Set database-engine specific properties for a field.
 *
 * @param $field
 *   A field description array, as specified in the schema documentation.
 */
function _db_process_field($field) {
  if (!isset($field['size'])) {
    $field['size'] = 'normal';
  }
  // Set the correct database-engine specific datatype.
  if (!isset($field['pgsql_type'])) {
    $map = db_type_map();
    $field['pgsql_type'] = $map[$field['type'] .':'. $field['size']];
  }
  if ($field['type'] == 'serial') {
    unset($field['not null']);
  }
  return $field;
}

/**
 * Create an SQL string for a field to be used in table creation or alteration.
 *
 * Before passing a field out of a schema definition into this function it has
 * to be processed by _db_process_field().
 *
 * @param $name
 *    Name of the field.
 * @param $spec
 *    The field specification, as per the schema data structure format.
 */
function _db_create_field_sql($name, $spec) {
  $sql = $name .' '. $spec['pgsql_type'];

  if ($spec['type'] == 'serial') {
    unset($spec['not null']);
  }
  if (!empty($spec['unsigned'])) {
    if ($spec['type'] == 'serial') {
      $sql .= " CHECK ($name >= 0)";
    }
    else {
      $sql .= '_unsigned';
    }
  }

  if (!empty($spec['length'])) {
    $sql .= '('. $spec['length'] .')';
  }
  elseif (isset($spec['precision']) && isset($spec['scale'])) {
    $sql .= '('. $spec['scale'] .', '. $spec['precision'] .')';
  }

  if (isset($spec['not null']) && $spec['not null']) {
617
    $sql .= ' NOT NULL';
618
619
620
621
622
623
624
625
626
  }
  if (isset($spec['default'])) {
    $default = is_string($spec['default']) ? "'". $spec['default'] ."'" : $spec['default'];
    $sql .= " default $default";
  }

  return $sql;
}

627
628
629
630
631
632
633
634
635
636
637
638
639
640
/**
 * Rename a table.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be renamed.
 * @param $new_name
 *   The new name for the table.
 */
function db_rename_table(&$ret, $table, $new_name) {
  $ret[] = update_sql('ALTER TABLE {'. $table .'} RENAME TO {'. $new_name .'}');
}

641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
/**
 * Drop a table.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be dropped.
 */
function db_drop_table(&$ret, $table) {
  $ret[] = update_sql('DROP TABLE {'. $table .'}');
}

/**
 * Add a new field to a table.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   Name of the table to be altered.
 * @param $field
 *   Name of the field to be added.
 * @param $spec
663
664
665
666
667
 *   The field specification array, as taken from a schema definition.
 *   The specification may also contain the key 'initial', the newly
 *   created field will be set to the value of the key in all rows.
 *   This is most useful for creating NOT NULL columns with no default
 *   value in existing tables.
668
669
 */
function db_add_field(&$ret, $table, $field, $spec) {
670
671
672
673
674
  $fixnull = FALSE;
  if (!empty($spec['not null']) && !isset($spec['default'])) {
    $fixnull = TRUE;
    $spec['not null'] = FALSE;
  }
675
676
677
  $query = 'ALTER TABLE {'. $table .'} ADD COLUMN ';
  $query .= _db_create_field_sql($field, _db_process_field($spec));
  $ret[] = update_sql($query);
678
679
680
681
682
683
684
685
686
  if (isset($spec['initial'])) {
    // All this because update_sql does not support %-placeholders.
    $sql = 'UPDATE {'. $table .'} SET '. $field .' = '. _db_type_placeholder($spec['type']);
    $result = db_query($sql, $spec['initial']);
    $ret[] = array('success' => $result !== FALSE, 'query' => check_plain($sql .' ('. $spec['initial'] .')'));
  }
  if ($fixnull) {
    $ret[] = update_sql("ALTER TABLE {". $table ."} ALTER $field SET NOT NULL");
  }
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
}

/**
 * Drop a field.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $field
 *   The field to be dropped.
 */
function db_drop_field(&$ret, $table, $field) {
  $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP COLUMN '. $field);
}

/**
 * Set the default value for a field.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $field
 *   The field to be altered.
 * @param $default
 *   Default value to be set. NULL for 'default NULL'.
 */
function db_field_set_default(&$ret, $table, $field, $default) {
  if ($default == NULL) {
    $default = 'NULL';
  }
  else {
    $default = is_string($default) ? "'$default'" : $default;
  }

  $ret[] = update_sql('ALTER TABLE {'. $table .'} ALTER COLUMN '. $field .' SET DEFAULT '. $default);
}

/**
 * Set a field to have no default value.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $field
 *   The field to be altered.
 */
function db_field_set_no_default(&$ret, $table, $field) {
  $ret[] = update_sql('ALTER TABLE {'. $table .'} ALTER COLUMN '. $field .' DROP DEFAULT');
}

/**
 * Add a primary key.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $fields
 *   Fields for the primary key.
 */
function db_add_primary_key(&$ret, $table, $fields) {
  $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD PRIMARY KEY ('.
    implode(',', $fields) .')');
}

/**
 * Drop the primary key.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 */
function db_drop_primary_key(&$ret, $table) {
  $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP CONSTRAINT {'. $table .'}_pkey');
}

/**
 * Add a unique key.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $name
 *   The name of the key.
 * @param $fields
 *   An array of field names.
 */
function db_add_unique_key(&$ret, $table, $name, $fields) {
  $name = '{'. $table .'}_'. $name .'_key';
  $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD CONSTRAINT '.
    $name .' UNIQUE ('. implode(',', $fields) .')');
}

/**
 * Drop a unique key.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $name
 *   The name of the key.
 */
function db_drop_unique_key(&$ret, $table, $name) {
  $name = '{'. $table .'}_'. $name .'_key';
  $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP CONSTRAINT '. $name);
}

/**
 * Add an index.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $name
 *   The name of the index.
 * @param $fields
 *   An array of field names.
 */
function db_add_index(&$ret, $table, $name, $fields) {
  $ret[] = update_sql(_db_create_index_sql($table, $name, $fields));
}

/**
 * Drop an index.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   The table to be altered.
 * @param $name
 *   The name of the index.
 */
function db_drop_index(&$ret, $table, $name) {
  $name = '{'. $table .'}_'. $name .'_idx';
  $ret[] = update_sql('DROP INDEX '. $name);
}

/**
 * Change a field definition.
 *
834
835
836
837
838
839
840
841
 * IMPORTANT NOTE: On some database systems (notably PostgreSQL),
 * changing a field definition involves adding a new field and
 * dropping an old one. This means that any indices, primary keys and
 * sequences (from serial-type fields) that use the field to be
 * changed get dropped.  For database portability, you MUST drop them
 * explicitly before calling db_change_field() and then re-create them
 * afterwards.  Use db_{add,drop}_{primary_key,unique_key,index} for
 * this purpose.
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   Name of the table.
 * @param $field
 *   Name of the field to change.
 * @param $field_new
 *   New name for the field (set to the same as $field if you don't want to change the name).
 * @param $spec
 *   The field specification for the new field.
 */
function db_change_field(&$ret, $table, $field, $field_new, $spec) {
  $ret[] = update_sql("ALTER TABLE {". $table ."} RENAME $field TO ". $field ."_old");
  $not_null = isset($spec['not null']) ? $spec['not null'] : FALSE;
  unset($spec['not null']);
  db_add_field($ret, $table, "$field_new", $spec);
  $ret[] = update_sql("UPDATE {". $table ."} SET $field_new = ". $field ."_old");
  if ($not_null) {
    $ret[] = update_sql("ALTER TABLE {". $table ."} ALTER $field_new SET NOT NULL");
  }
  db_drop_field($ret, $table, $field .'_old');
}

/**
 * Update a field definition to match its schema. If the field is
 * involved in any keys or indexes, recreate them if necessary.
 *
 * @param $ret
 *   Array to which query results will be added.
 * @param $table
 *   Name of the table.
 * @param $field
 *   Name of the field to update.
 */
function db_update_field(&$ret, $table, $field) {
  $spec = drupal_get_schema($table);

  db_change_field($ret, $table, $field, $field, $spec['fields'][$field]);
  if (isset($spec['primary key'])) {
    if (array_search($field, db_field_names($spec['primary key'])) !== FALSE) {
      db_add_primary_key($ret, $table, $spec['primary key']);
    }
  }
  if (isset($spec['unique keys'])) {
    foreach ($spec['unique keys'] as $name => $fields) {
      if (array_search($field, db_field_names($fields)) !== FALSE) {
        db_add_unique_key($ret, $table, $fields);
      }
    }
  }
  if (isset($spec['indexes'])) {
    foreach ($spec['indexes'] as $name => $fields) {
      if (array_search($field, db_field_names($fields)) !== FALSE) {
        db_add_index($ret, $table, $fields);
      }
    }
  }
}

/**
 * @} End of "ingroup schemaapi".
 */
905