1 <?php

     2 // $Id: database.mysql-common.inc,v 1.17.2.1 2008/02/07 10:17:26 goba Exp $

     3 

     4 /**

     5  * @file

     6  * Functions shared between mysql and mysqli database engines.

     7  */

     8 

     9 /**

    10  * Runs a basic query in the active database.

    11  *

    12  * User-supplied arguments to the query should be passed in as separate

    13  * parameters so that they can be properly escaped to avoid SQL injection

    14  * attacks.

    15  *

    16  * @param $query

    17  *   A string containing an SQL query.

    18  * @param ...

    19  *   A variable number of arguments which are substituted into the query

    20  *   using printf() syntax. Instead of a variable number of query arguments,

    21  *   you may also pass a single array containing the query arguments.

    22  *

    23  *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose

    24  *   in '') and %%.

    25  *

    26  *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,

    27  *   and TRUE values to decimal 1.

    28  *

    29  * @return

    30  *   A database query result resource, or FALSE if the query was not

    31  *   executed correctly.

    32  */

    33 function db_query($query) {

    34   $args = func_get_args();

    35   array_shift($args);

    36   $query = db_prefix_tables($query);

    37   if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax

    38     $args = $args[0];

    39   }

    40   _db_query_callback($args, TRUE);

    41   $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);

    42   return _db_query($query);

    43 }

    44 

    45 /**

    46  * @ingroup schemaapi

    47  * @{

    48  */

    49 

    50 /**

    51  * Generate SQL to create a new table from a Drupal schema definition.

    52  *

    53  * @param $name

    54  *   The name of the table to create.

    55  * @param $table

    56  *   A Schema API table definition array.

    57  * @return

    58  *   An array of SQL statements to create the table.

    59  */

    60 function db_create_table_sql($name, $table) {

    61 

    62   if (empty($table['mysql_suffix'])) {

    63     $table['mysql_suffix'] = "/*!40100 DEFAULT CHARACTER SET UTF8 */";

    64   }

    65 

    66   $sql = "CREATE TABLE {". $name ."} (\n";

    67 

    68   // Add the SQL statement for each field.

    69   foreach ($table['fields'] as $field_name => $field) {

    70     $sql .= _db_create_field_sql($field_name, _db_process_field($field)) .", \n";

    71   }

    72 

    73   // Process keys & indexes.

    74   $keys = _db_create_keys_sql($table);

    75   if (count($keys)) {

    76     $sql .= implode(", \n", $keys) .", \n";

    77   }

    78 

    79   // Remove the last comma and space.

    80   $sql = substr($sql, 0, -3) ."\n) ";

    81 

    82   $sql .= $table['mysql_suffix'];

    83 

    84   return array($sql);

    85 }

    86 

    87 function _db_create_keys_sql($spec) {

    88   $keys = array();

    89 

    90   if (!empty($spec['primary key'])) {

    91     $keys[] = 'PRIMARY KEY ('. _db_create_key_sql($spec['primary key']) .')';

    92   }

    93   if (!empty($spec['unique keys'])) {

    94     foreach ($spec['unique keys'] as $key => $fields) {

    95       $keys[] = 'UNIQUE KEY '. $key .' ('. _db_create_key_sql($fields) .')';

    96     }

    97   }

    98   if (!empty($spec['indexes'])) {

    99     foreach ($spec['indexes'] as $index => $fields) {

   100       $keys[] = 'INDEX '. $index .' ('. _db_create_key_sql($fields) .')';

   101     }

   102   }

   103 

   104   return $keys;

   105 }

   106 

   107 function _db_create_key_sql($fields) {

   108   $ret = array();

   109   foreach ($fields as $field) {

   110     if (is_array($field)) {

   111       $ret[] = $field[0] .'('. $field[1] .')';

   112     }

   113     else {

   114       $ret[] = $field;

   115     }

   116   }

   117   return implode(', ', $ret);

   118 }

   119 

   120 /**

   121  * Set database-engine specific properties for a field.

   122  *

   123  * @param $field

   124  *   A field description array, as specified in the schema documentation.

   125  */

   126 function _db_process_field($field) {

   127 

   128   if (!isset($field['size'])) {

   129     $field['size'] = 'normal';

   130   }

   131 

   132   // Set the correct database-engine specific datatype.

   133   if (!isset($field['mysql_type'])) {

   134     $map = db_type_map();

   135     $field['mysql_type'] = $map[$field['type'] .':'. $field['size']];

   136   }

   137 

   138   if ($field['type'] == 'serial') {

   139     $field['auto_increment'] = TRUE;

   140   }

   141 

   142   return $field;

   143 }

   144 

   145 /**

   146  * Create an SQL string for a field to be used in table creation or alteration.

   147  *

   148  * Before passing a field out of a schema definition into this function it has

   149  * to be processed by _db_process_field().

   150  *

   151  * @param $name

   152  *    Name of the field.

   153  * @param $spec

   154  *    The field specification, as per the schema data structure format.

   155  */

   156 function _db_create_field_sql($name, $spec) {

   157   $sql = "`". $name ."` ". $spec['mysql_type'];

   158 

   159   if (isset($spec['length'])) {

   160     $sql .= '('. $spec['length'] .')';

   161   }

   162   elseif (isset($spec['precision']) && isset($spec['scale'])) {

   163     $sql .= '('. $spec['precision'] .', '. $spec['scale'] .')';

   164   }

   165 

   166   if (!empty($spec['unsigned'])) {

   167     $sql .= ' unsigned';

   168   }

   169 

   170   if (!empty($spec['not null'])) {

   171     $sql .= ' NOT NULL';

   172   }

   173 

   174   if (!empty($spec['auto_increment'])) {

   175     $sql .= ' auto_increment';

   176   }

   177 

   178   if (isset($spec['default'])) {

   179     if (is_string($spec['default'])) {

   180       $spec['default'] = "'". $spec['default'] ."'";

   181     }

   182     $sql .= ' DEFAULT '. $spec['default'];

   183   }

   184 

   185   if (empty($spec['not null']) && !isset($spec['default'])) {

   186     $sql .= ' DEFAULT NULL';

   187   }

   188 

   189   return $sql;

   190 }

   191 

   192 /**

   193  * This maps a generic data type in combination with its data size

   194  * to the engine-specific data type.

   195  */

   196 function db_type_map() {

   197   // Put :normal last so it gets preserved by array_flip.  This makes

   198   // it much easier for modules (such as schema.module) to map

   199   // database types back into schema types.

   200   $map = array(

   201     'varchar:normal'  => 'VARCHAR',

   202     'char:normal'     => 'CHAR',

   203 

   204     'text:tiny'       => 'TINYTEXT',

   205     'text:small'      => 'TINYTEXT',

   206     'text:medium'     => 'MEDIUMTEXT',

   207     'text:big'        => 'LONGTEXT',

   208     'text:normal'     => 'TEXT',

   209 

   210     'serial:tiny'     => 'TINYINT',

   211     'serial:small'    => 'SMALLINT',

   212     'serial:medium'   => 'MEDIUMINT',

   213     'serial:big'      => 'BIGINT',

   214     'serial:normal'   => 'INT',

   215 

   216     'int:tiny'        => 'TINYINT',

   217     'int:small'       => 'SMALLINT',

   218     'int:medium'      => 'MEDIUMINT',

   219     'int:big'         => 'BIGINT',

   220     'int:normal'      => 'INT',

   221 

   222     'float:tiny'      => 'FLOAT',

   223     'float:small'     => 'FLOAT',

   224     'float:medium'    => 'FLOAT',

   225     'float:big'       => 'DOUBLE',

   226     'float:normal'    => 'FLOAT',

   227 

   228     'numeric:normal'  => 'DECIMAL',

   229 

   230     'blob:big'        => 'LONGBLOB',

   231     'blob:normal'     => 'BLOB',

   232 

   233     'datetime:normal' => 'DATETIME',

   234   );

   235   return $map;

   236 }

   237 

   238 /**

   239  * Rename a table.

   240  *

   241  * @param $ret

   242  *   Array to which query results will be added.

   243  * @param $table

   244  *   The table to be renamed.

   245  * @param $new_name

   246  *   The new name for the table.

   247  */

   248 function db_rename_table(&$ret, $table, $new_name) {

   249   $ret[] = update_sql('ALTER TABLE {'. $table .'} RENAME TO {'. $new_name .'}');

   250 }

   251 

   252 /**

   253  * Drop a table.

   254  *

   255  * @param $ret

   256  *   Array to which query results will be added.

   257  * @param $table

   258  *   The table to be dropped.

   259  */

   260 function db_drop_table(&$ret, $table) {

   261   $ret[] = update_sql('DROP TABLE {'. $table .'}');

   262 }

   263 

   264 /**

   265  * Add a new field to a table.

   266  *

   267  * @param $ret

   268  *   Array to which query results will be added.

   269  * @param $table

   270  *   Name of the table to be altered.

   271  * @param $field

   272  *   Name of the field to be added.

   273  * @param $spec

   274  *   The field specification array, as taken from a schema definition.

   275  *   The specification may also contain the key 'initial', the newly

   276  *   created field will be set to the value of the key in all rows.

   277  *   This is most useful for creating NOT NULL columns with no default

   278  *   value in existing tables.

   279  * @param $keys_new

   280  *   Optional keys and indexes specification to be created on the

   281  *   table along with adding the field. The format is the same as a

   282  *   table specification but without the 'fields' element.  If you are

   283  *   adding a type 'serial' field, you MUST specify at least one key

   284  *   or index including it in this array. @see db_change_field for more

   285  *   explanation why.

   286  */

   287 function db_add_field(&$ret, $table, $field, $spec, $keys_new = array()) {

   288   $fixnull = FALSE;

   289   if (!empty($spec['not null']) && !isset($spec['default'])) {

   290     $fixnull = TRUE;

   291     $spec['not null'] = FALSE;

   292   }

   293   $query = 'ALTER TABLE {'. $table .'} ADD ';

   294   $query .= _db_create_field_sql($field, _db_process_field($spec));

   295   if (count($keys_new)) {

   296     $query .= ', ADD '. implode(', ADD ', _db_create_keys_sql($keys_new));

   297   }

   298   $ret[] = update_sql($query);

   299   if (isset($spec['initial'])) {

   300     // All this because update_sql does not support %-placeholders.

   301     $sql = 'UPDATE {'. $table .'} SET '. $field .' = '. db_type_placeholder($spec['type']);

   302     $result = db_query($sql, $spec['initial']);

   303     $ret[] = array('success' => $result !== FALSE, 'query' => check_plain($sql .' ('. $spec['initial'] .')'));

   304   }

   305   if ($fixnull) {

   306     $spec['not null'] = TRUE;

   307     db_change_field($ret, $table, $field, $field, $spec);

   308   }

   309 }

   310 

   311 /**

   312  * Drop a field.

   313  *

   314  * @param $ret

   315  *   Array to which query results will be added.

   316  * @param $table

   317  *   The table to be altered.

   318  * @param $field

   319  *   The field to be dropped.

   320  */

   321 function db_drop_field(&$ret, $table, $field) {

   322   $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP '. $field);

   323 }

   324 

   325 /**

   326  * Set the default value for a field.

   327  *

   328  * @param $ret

   329  *   Array to which query results will be added.

   330  * @param $table

   331  *   The table to be altered.

   332  * @param $field

   333  *   The field to be altered.

   334  * @param $default

   335  *   Default value to be set. NULL for 'default NULL'.

   336  */

   337 function db_field_set_default(&$ret, $table, $field, $default) {

   338   if ($default == NULL) {

   339     $default = 'NULL';

   340   }

   341   else {

   342     $default = is_string($default) ? "'$default'" : $default;

   343   }

   344 

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

   346 }

   347 

   348 /**

   349  * Set a field to have no default value.

   350  *

   351  * @param $ret

   352  *   Array to which query results will be added.

   353  * @param $table

   354  *   The table to be altered.

   355  * @param $field

   356  *   The field to be altered.

   357  */

   358 function db_field_set_no_default(&$ret, $table, $field) {

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

   360 }

   361 

   362 /**

   363  * Add a primary key.

   364  *

   365  * @param $ret

   366  *   Array to which query results will be added.

   367  * @param $table

   368  *   The table to be altered.

   369  * @param $fields

   370  *   Fields for the primary key.

   371  */

   372 function db_add_primary_key(&$ret, $table, $fields) {

   373   $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD PRIMARY KEY ('.

   374     _db_create_key_sql($fields) .')');

   375 }

   376 

   377 /**

   378  * Drop the primary key.

   379  *

   380  * @param $ret

   381  *   Array to which query results will be added.

   382  * @param $table

   383  *   The table to be altered.

   384  */

   385 function db_drop_primary_key(&$ret, $table) {

   386   $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP PRIMARY KEY');

   387 }

   388 

   389 /**

   390  * Add a unique key.

   391  *

   392  * @param $ret

   393  *   Array to which query results will be added.

   394  * @param $table

   395  *   The table to be altered.

   396  * @param $name

   397  *   The name of the key.

   398  * @param $fields

   399  *   An array of field names.

   400  */

   401 function db_add_unique_key(&$ret, $table, $name, $fields) {

   402   $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD UNIQUE KEY '.

   403     $name .' ('. _db_create_key_sql($fields) .')');

   404 }

   405 

   406 /**

   407  * Drop a unique key.

   408  *

   409  * @param $ret

   410  *   Array to which query results will be added.

   411  * @param $table

   412  *   The table to be altered.

   413  * @param $name

   414  *   The name of the key.

   415  */

   416 function db_drop_unique_key(&$ret, $table, $name) {

   417   $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP KEY '. $name);

   418 }

   419 

   420 /**

   421  * Add an index.

   422  *

   423  * @param $ret

   424  *   Array to which query results will be added.

   425  * @param $table

   426  *   The table to be altered.

   427  * @param $name

   428  *   The name of the index.

   429  * @param $fields

   430  *   An array of field names.

   431  */

   432 function db_add_index(&$ret, $table, $name, $fields) {

   433   $query = 'ALTER TABLE {'. $table .'} ADD INDEX '. $name .' ('. _db_create_key_sql($fields) .')';

   434   $ret[] = update_sql($query);

   435 }

   436 

   437 /**

   438  * Drop an index.

   439  *

   440  * @param $ret

   441  *   Array to which query results will be added.

   442  * @param $table

   443  *   The table to be altered.

   444  * @param $name

   445  *   The name of the index.

   446  */

   447 function db_drop_index(&$ret, $table, $name) {

   448   $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP INDEX '. $name);

   449 }

   450 

   451 /**

   452  * Change a field definition.

   453  *

   454  * IMPORTANT NOTE: To maintain database portability, you have to explicitly

   455  * recreate all indices and primary keys that are using the changed field.

   456  *

   457  * That means that you have to drop all affected keys and indexes with

   458  * db_drop_{primary_key,unique_key,index}() before calling db_change_field().

   459  * To recreate the keys and indices, pass the key definitions as the

   460  * optional $keys_new argument directly to db_change_field().

   461  *

   462  * For example, suppose you have:

   463  * @code

   464  * $schema['foo'] = array(

   465  *   'fields' => array(

   466  *     'bar' => array('type' => 'int', 'not null' => TRUE)

   467  *   ),

   468  *   'primary key' => array('bar')

   469  * );

   470  * @endcode

   471  * and you want to change foo.bar to be type serial, leaving it as the

   472  * primary key.  The correct sequence is:

   473  * @code

   474  * db_drop_primary_key($ret, 'foo');

   475  * db_change_field($ret, 'foo', 'bar', 'bar',

   476  *   array('type' => 'serial', 'not null' => TRUE),

   477  *   array('primary key' => array('bar')));

   478  * @endcode

   479  *

   480  * The reasons for this are due to the different database engines:

   481  *

   482  * On PostgreSQL, changing a field definition involves adding a new field

   483  * and dropping an old one which* causes any indices, primary keys and

   484  * sequences (from serial-type fields) that use the changed field to be dropped.

   485  *

   486  * On MySQL, all type 'serial' fields must be part of at least one key

   487  * or index as soon as they are created.  You cannot use

   488  * db_add_{primary_key,unique_key,index}() for this purpose because

   489  * the ALTER TABLE command will fail to add the column without a key

   490  * or index specification.  The solution is to use the optional

   491  * $keys_new argument to create the key or index at the same time as

   492  * field.

   493  *

   494  * You could use db_add_{primary_key,unique_key,index}() in all cases

   495  * unless you are converting a field to be type serial. You can use

   496  * the $keys_new argument in all cases.

   497  *

   498  * @param $ret

   499  *   Array to which query results will be added.

   500  * @param $table

   501  *   Name of the table.

   502  * @param $field

   503  *   Name of the field to change.

   504  * @param $field_new

   505  *   New name for the field (set to the same as $field if you don't want to change the name).

   506  * @param $spec

   507  *   The field specification for the new field.

   508  * @param $keys_new

   509  *   Optional keys and indexes specification to be created on the

   510  *   table along with changing the field. The format is the same as a

   511  *   table specification but without the 'fields' element.

   512  */

   513 

   514 function db_change_field(&$ret, $table, $field, $field_new, $spec, $keys_new = array()) {

   515   $sql = 'ALTER TABLE {'. $table .'} CHANGE '. $field .' '.

   516     _db_create_field_sql($field_new, _db_process_field($spec));

   517   if (count($keys_new)) {

   518     $sql .= ', ADD '. implode(', ADD ', _db_create_keys_sql($keys_new));

   519   }

   520   $ret[] = update_sql($sql);

   521 }

   522 

   523 /**

   524  * Returns the last insert id.

   525  *

   526  * @param $table

   527  *   The name of the table you inserted into.

   528  * @param $field

   529  *   The name of the autoincrement field.

   530  */

   531 function db_last_insert_id($table, $field) {

   532   return db_result(db_query('SELECT LAST_INSERT_ID()'));

   533 }