sql_alter

The sql_alter() function is used to send an ALTER type SQL command to the database server to modify the structure of the database.

The function accepts 3 parameters:

  1. $q is the query string (without the term "ALTER") to be executed
  2. $serveur,
  3. $option

Note: This function directly assumes an SQL formatted command, so it is important to respect the SQL standards. It is possible in future versions of SPIP, that the $q parameter will accept a more structured table as input in order to simplify porting to other systems.

The function is used as shown in this example:

  1. sql_alter("TABLE table ADD COLUMN column_name INT");
  2. sql_alter("TABLE table ADD column_name INT"); // COLUMN is an optional keyword for this SQL command
  3. sql_alter("TABLE table CHANGE column_name column_name INT DEFAULT '0'");
  4. sql_alter("TABLE table ADD INDEX column_name (column_name)");
  5. sql_alter("TABLE table DROP INDEX column_name");
  6. sql_alter("TABLE table DROP COLUMN column_name");
  7. sql_alter("TABLE table DROP column_name"); // COLUMN is an optional keyword for this command
  8. sql_alter("TABLE spip_tradlang RENAME spip_tradlangs");
  9. // You may pass several actions, but be careful about other DBMS ports:
  10. sql_alter("TABLE table DROP column_nameA, DROP column_nameB");

Download

The sql_alter() function is particularly used during updates for plugins in the {plugin_name}_upgrade() functions for the various plugins you may have installed.

Example

Add a "composition" column to the spip_articles table (plugin "Composition"):

  1. sql_alter("TABLE spip_articles ADD composition varchar(255) DEFAULT '' NOT NULL");

Add "css" to the "spip_menus" table (plugin "Menus"):

  1. sql_alter("TABLE spip_menus ADD COLUMN css tinytext DEFAULT '' NOT NULL");

The "TradRub" plugin includes in its installation procedure an instruction to add the "id_trad" column to the spip_rubriques table by using the maj_tables() function provided for such a purpose, then adds an index on that same column using sql_alter():

  1. function tradrub_upgrade($nom_meta_base_version, $version_cible){
  2. $current_version = 0.0;
  3.  
  4. if ( (!isset($GLOBALS['meta'][$nom_meta_base_version]) )
  5. || (($current_version = $GLOBALS['meta'][$nom_meta_base_version]) != $version_cible))
  6. {
  7. include_spip('base/tradrub');
  8. if ($current_version==0.0){
  9. include_spip('base/create');
  10. maj_tables('spip_rubriques');
  11. // index on the new field
  12. sql_alter("TABLE spip_rubriques ADD INDEX (id_trad)");
  13. ecrire_meta($nom_meta_base_version, $current_version=$version_cible, 'non');
  14. }
  15. }
  16. }

Download

Author Mark Baber Published : Updated : 20/02/12

Translations : English, français