update_db.php

   1 <?php # encoding:utf-8
   2 /**
   3   * Met à jour la base de données en fonction de la version courante de celle ci.
   4   * Si des tables n'existes pas elles sont automatiquement créées.
   5   */
   6
   7 include("connexion.php");
   8
   9 function creer_db()
  10 {
  11    mysql_query("
  12       CREATE TABLE IF NOT EXISTS config (
  13          nom varchar(50) NOT NULL,
  14          valeur varchar(255) NOT NULL,
  15          PRIMARY KEY (nom)
  16       ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
  17    ");
  18    mysql_query("
  19       CREATE TABLE pizzas (
  20         id mediumint(3) unsigned NOT NULL auto_increment,
  21         nom varchar(40) NOT NULL,
  22         composition varchar(255) NOT NULL,
  23         prix tinyint(3) unsigned default '0',
  24         PRIMARY KEY  (id)
  25       ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
  26    ");
  27    mysql_query("
  28       CREATE TABLE IF NOT EXISTS jeux (
  29         id mediumint(3) unsigned NOT NULL auto_increment,
  30         nom varchar(200) default '0',
  31         PRIMARY KEY (id),
  32         UNIQUE KEY nom_unique (nom)
  33       ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
  34    ");
  35    mysql_query("
  36       CREATE TABLE IF NOT EXISTS participants (
  37         id mediumint(3) unsigned NOT NULL auto_increment,
  38         pseudo varchar(50) default NULL,
  39         clan_nom varchar(30) default NULL,
  40         clan_tag varchar(10) default NULL,
  41         password varchar(10) default NULL,
  42         nom varchar(30) default NULL,
  43         prenom varchar(30) default NULL,
  44         age varchar(30) default NULL,
  45         e_mail varchar(50) default NULL,
  46         remarques varchar(255) default NULL,
  47         admin tinyint(1) unsigned NOT NULL default '0',
  48         a_paye tinyint(1) unsigned NOT NULL default '0',
  49         pizza mediumint(3) unsigned default NULL,
  50         pizza_paye tinyint(1) NOT NULL default '0',
  51         PRIMARY KEY  (id),
  52         KEY FK_pizza (pizza),
  53         CONSTRAINT FK_pizza FOREIGN KEY (pizza) REFERENCES pizzas (id) ON DELETE SET NULL ON UPDATE SET NULL
  54       ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  55    ");
  56    mysql_query("
  57       CREATE TABLE IF NOT EXISTS jeux_choisis (
  58         participant_id mediumint(3) unsigned NOT NULL default '0',
  59         jeu_id mediumint(3) unsigned NOT NULL default '0',
  60         PRIMARY KEY USING BTREE (participant_id,jeu_id),
  61         KEY FK_jeu (jeu_id),
  62         CONSTRAINT FK_participant FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
  63         CONSTRAINT FK_jeu FOREIGN KEY (jeu_id) REFERENCES jeux (id) ON DELETE CASCADE ON UPDATE CASCADE
  64       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  65    ");
  66 }
  67
  68 function initialiser_db()
  69 {
  70    mysql_query("INSERT INTO config (nom, valeur) VALUES ('version', 1)");
  71 }
  72
  73 function update_db()
  74 {
  75    # si la table 'config' n'existe pas alors on suppose qu'aucune table n'existe
  76    $version = 0;
  77    if(!$version = @mysql_fetch_object(mysql_query("SELECT valeur FROM config WHERE nom = 'version'")))
  78    {
  79       mysql_query("BEGIN TRANSACTION");
  80       creer_db();
  81       initialiser_db();
  82       mysql_query("COMMIT");
  83       $version = 1;
  84    }
  85 }
  86
  87 update_db();
  88
  89 ?>