database.pgsql.inc 25.4 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
48
49
50
51
52
53
/**
 * Initialize a database connection.
 *
 * Note that you can change the pg_connect() call to pg_pconnect() if you
 * want to use persistent connections. This is not recommended on shared hosts,
 * and might require additional database/webserver tuning. It can increase
 * performance, however, when the overhead to connect to your database is high
 * (e.g. your database and web server live on different machines).
 */
function db_connect($url) {
54
55
56
57
58
59
60
61
62
   // Check if MySQL support is present in PHP
  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
63
  $url = parse_url($url);
64
  $conn_string = '';
Dries's avatar
 
Dries committed
65

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

  // 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();
92
    drupal_set_header('HTTP/1.1 503 Service Unavailable');
93
    drupal_set_title('Unable to connect to database');
94
95
    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>
96
97
98
99
100
101
102
103
104
105
106
107
108
109
<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
110
111
112
113
114
115
116
117

  return $connection;
}

/**
 * Helper function for db_query().
 */
function _db_query($query, $debug = 0) {
118
  global $active_db, $last_result, $queries;
Dries's avatar
 
Dries committed
119

120
  if (variable_get('dev_query', 0)) {
Dries's avatar
 
Dries committed
121
122
123
124
125
126
    list($usec, $sec) = explode(' ', microtime());
    $timer = (float)$usec + (float)$sec;
  }

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

127
  if (variable_get('dev_query', 0)) {
128
    $bt = debug_backtrace();
129
    $query = $bt[2]['function'] ."\n". $query;
Dries's avatar
 
Dries committed
130
131
132
133
134
135
136
    list($usec, $sec) = explode(' ', microtime());
    $stop = (float)$usec + (float)$sec;
    $diff = $stop - $timer;
    $queries[] = array($query, $diff);
  }

  if ($debug) {
137
    print '<p>query: '. $query .'<br />error:'. pg_last_error($active_db) .'</p>';
Dries's avatar
 
Dries committed
138
139
140
141
142
143
  }

  if ($last_result !== FALSE) {
    return $last_result;
  }
  else {
144
    trigger_error(check_plain(pg_last_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
145
    return FALSE;
Dries's avatar
 
Dries committed
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
  }
}

/**
 * Fetch one result row from the previous query as an object.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
 *   An object representing the next row of the result. The attributes of this
 *   object are the table fields selected by the query.
 */
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
 *   An associative array representing the next row of the result. The keys of
 *   this object are the names of the table fields selected by the query, and
 *   the values are the field values for this result row.
 */
function db_fetch_array($result) {
  if ($result) {
    return pg_fetch_assoc($result);
  }
}

/**
 * Determine how many result rows were found by the preceding query.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
 *   The number of result rows.
 */
function db_num_rows($result) {
  if ($result) {
    return pg_num_rows($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().
 * @param $row
 *   The index of the row whose result is needed.
 * @return
205
 *   The resulting field or FALSE.
Dries's avatar
 
Dries committed
206
207
208
209
210
211
212
 */
function db_result($result, $row = 0) {
  if ($result && pg_num_rows($result) > $row) {
    $res = pg_fetch_row($result, $row);

    return $res[0];
  }
213
  return FALSE;
Dries's avatar
 
Dries committed
214
215
216
217
218
219
}

/**
 * Determine whether the previous query caused an error.
 */
function db_error() {
220
221
  global $active_db;
  return pg_last_error($active_db);
Dries's avatar
 
Dries committed
222
223
224
225
226
227
228
229
230
}

/**
 * Return a new unique ID in the given sequence.
 *
 * For compatibility reasons, Drupal does not use auto-numbered fields in its
 * database tables. Instead, this function is used to return a new unique ID
 * of the type requested. If necessary, a new sequence with the given name
 * will be created.
231
232
233
 *
 * Note that the table name should be in curly brackets to preserve compatibility
 * with table prefixes. For example, db_next_id('{node}_nid');
Dries's avatar
 
Dries committed
234
235
 */
function db_next_id($name) {
236
  $id = db_result(db_query("SELECT nextval('%s_seq')", db_prefix_tables($name)));
Dries's avatar
 
Dries committed
237
238
239
240
241
242
243
244
245
246
247
248
249
250
  return $id;
}

/**
 * Determine the number of rows changed by the preceding query.
 */
function db_affected_rows() {
  global $last_result;
  return pg_affected_rows($last_result);
}

/**
 * Runs a limited-range query in the active database.
 *
251
252
253
254
255
 * 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
256
257
258
259
 *
 * @param $query
 *   A string containing an SQL query.
 * @param ...
260
261
262
263
264
265
266
267
268
 *   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
269
270
271
272
273
274
275
276
277
278
279
280
 * @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);
281
  array_shift($args);
282
283

  $query = db_prefix_tables($query);
284
285
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
Dries's avatar
 
Dries committed
286
  }
287
288
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
289
  $query .= ' LIMIT '. (int)$count .' OFFSET '. (int)$from;
Dries's avatar
 
Dries committed
290
  return _db_query($query);
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
}

/**
 * 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
 * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
 * db_affected_rows() do not give consistent result across different database
 * types in this case.
 *
 * @param $query
 *   A string containing a normal SELECT SQL query.
 * @param ...
310
311
312
313
314
315
316
317
318
 *   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.
 *
319
 * @param $table
320
321
 *   The name of the temporary table to select into. This name will not be
 *   prefixed as there is no risk of collision.
322
323
324
325
326
327
328
 * @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);
329
  array_shift($args);
330

331
  $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' AS SELECT', db_prefix_tables($query));
332
333
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
334
  }
335
336
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
337
  return _db_query($query);
Dries's avatar
 
Dries committed
338
339
340
341
}

/**
 * Returns a properly formatted Binary Large OBject value.
342
 * In case of PostgreSQL encodes data for insert into bytea field.
Dries's avatar
 
Dries committed
343
344
345
346
347
348
349
 *
 * @param $data
 *   Data to encode.
 * @return
 *  Encoded data.
 */
function db_encode_blob($data) {
350
  return "'". pg_escape_bytea($data) ."'";
Dries's avatar
 
Dries committed
351
352
353
354
}

/**
 * Returns text from a Binary Large OBject value.
355
 * In case of PostgreSQL decodes data after select from bytea field.
Dries's avatar
 
Dries committed
356
357
358
359
360
361
362
 *
 * @param $data
 *   Data to decode.
 * @return
 *  Decoded data.
 */
function db_decode_blob($data) {
363
  return pg_unescape_bytea($data);
Dries's avatar
 
Dries committed
364
365
}

Dries's avatar
   
Dries committed
366
367
368
369
370
371
372
373
/**
 * 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);
}

374
375
376
377
378
/**
 * Lock a table.
 * This function automatically starts a transaction.
 */
function db_lock_table($table) {
379
  db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN EXCLUSIVE MODE');
380
381
382
383
}

/**
 * Unlock all locked tables.
384
 * This function automatically commits a transaction.
385
386
387
388
389
 */
function db_unlock_tables() {
  db_query('COMMIT');
}

drumm's avatar
drumm committed
390
391
392
393
/**
 * Check if a table exists.
 */
function db_table_exists($table) {
394
  return db_num_rows(db_query("SELECT relname FROM pg_class WHERE relname = '{". db_escape_table($table) ."}'"));
drumm's avatar
drumm committed
395
396
}

397
398
399
400
401
402
403
/**
 * 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));
}

404
405
406
407
/**
 * Verify if the database is set up correctly.
 */
function db_check_setup() {
408
409
  $t = get_t();

410
411
  $encoding = db_result(db_query('SHOW server_encoding'));
  if (!in_array(strtolower($encoding), array('unicode', 'utf8'))) {
412
    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');
413
414
415
  }
}

416
417
418
419
420
421
422
423
424
425
426
427
428
429
/**
 * 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).
430
  $query = preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
431
  $query = preg_replace('/(ORDER BY )(?!'. $table .'\.'. $field .')/', '\1'."$table.$field, ", $query);
432
433
434
  return $query;
}

Dries's avatar
 
Dries committed
435
/**
Dries's avatar
   
Dries committed
436
 * @} End of "ingroup database".
Dries's avatar
 
Dries committed
437
438
 */

439
440
441
442
443
444
445
446
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
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
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
/**
 * @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;
}

/**
 *  Generate SQL to create a new table from a Drupal schema definition.
 *
 * @param $table
 *   A valid Drupal table definition array.
 * @return
 *   An array of SQL statements to create the table.
 */
function db_create_table_sql($table) {
  $sql_fields = array();
  foreach ($table['fields'] as $name => $field) {
    $sql_fields[] = _db_create_field_sql($name, _db_process_field($field));
  }

  $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'])) {
    foreach ($table['unique keys'] as $keyname => $key) {
      $sql_keys[] = 'CONSTRAINT {'. $table['name'] .'}_'. $keyname .'_key UNIQUE ('. implode(', ', $key) .')';
    }
  }

  $sql = "CREATE TABLE {". $table['name'] ."} (\n\t";
  $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'])) {
    foreach ($table['indexes'] as $keyname => $key) {
      $statements[] = _db_create_index_sql($table['name'], $keyname, $key);
    }
  }

  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']) {
   $sql .= ' NOT NULL';
  }
  if (isset($spec['default'])) {
    $default = is_string($spec['default']) ? "'". $spec['default'] ."'" : $spec['default'];
    $sql .= " default $default";
  }

  return $sql;
}

615
616
617
618
619
620
621
622
623
624
625
626
627
628
/**
 * 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 .'}');
}

629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
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
834
835
836
837
838
839
840
841
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
/**
 * 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
 *   The field specification array, as taken from a schema definition
 */
function db_add_field(&$ret, $table, $field, $spec) {
  $query = 'ALTER TABLE {'. $table .'} ADD COLUMN ';
  $query .= _db_create_field_sql($field, _db_process_field($spec));
  $ret[] = update_sql($query);
}

/**
 * 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.
 *
 * Remember that 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 are dropped and might need to be
 * recreated.
 *
 * @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".
 */
871