php/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,
  59         jeu_id mediumint(3) unsigned NOT NULL,
  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    $res = @mysql_fetch_object(mysql_query("SELECT valeur FROM config WHERE nom = 'version'"));
  78    if (!mysql_error())
  79       $version = $res->valeur;
  80
  81    if($version == 0)
  82    {
  83       mysql_query("BEGIN TRANSACTION");
  84       creer_db();
  85       initialiser_db();
  86       mysql_query("COMMIT");
  87       $version = 1;
  88    }
  89
  90    # version 1 -> 2
  91    if ($version == 1)
  92    {
  93       mysql_query("BEGIN TRANSACTION");
  94       mysql_query("ALTER TABLE participants ADD CONSTRAINT UNIQUE INDEX pseudo_unique (pseudo);");
  95       mysql_query("
  96          CREATE TABLE IF NOT EXISTS periodes (
  97            id mediumint(3) unsigned NOT NULL auto_increment,
  98            nom varchar(200) NOT NULL,
  99            nom_abrege varchar(20) NOT NULL DEFAULT '',
 100            PRIMARY KEY (id)
 101          ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
 102       ");
 103       mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('vendredi soir à samedi', 'v->s')");
 104       mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('samedi à dimanche', 's->d')");
 105       mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('dimanche à lundi', 'd->l')");
 106       mysql_query("
 107          CREATE TABLE IF NOT EXISTS participations (
 108            participant_id mediumint(3) unsigned NOT NULL,
 109            periode_id mediumint(3) unsigned NOT NULL,
 110            PRIMARY KEY USING BTREE (participant_id, periode_id),
 111            KEY FK_periode (periode_id),
 112            CONSTRAINT FK_participant_participations FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
 113            CONSTRAINT FK_periode_participations FOREIGN KEY (periode_id) REFERENCES periodes (id) ON DELETE CASCADE ON UPDATE CASCADE
 114          ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
 115       ");
 116       $participations = mysql_query("SELECT participants.id as participant_id, periodes.id as periode_id FROM participants, periodes");
 117       while ($participation = mysql_fetch_object($participations))
 118       {
 119          mysql_query("
 120             INSERT INTO participations (participant_id, periode_id)
 121             VALUES (".$participation->participant_id.",".$participation->periode_id.")"
 122          );
 123       }
 124       mysql_query("UPDATE config SET valeur = '2' WHERE nom = 'version'");
 125
 126       mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_periode', '15')");
 127       mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_total', '40')");
 128       mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_max_participant', '25')");
 129       mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_votes_jeux', '3')");
 130       mysql_query("INSERT INTO config (nom, valeur) VALUES ('inscription_terminees', 'FALSE')");
 131       mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_visible', 'FALSE')");
 132       mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_peut_commander', 'TRUE')");
 133
 134       mysql_query("COMMIT");
 135    }
 136 }
 137
 138 update_db();
 139
 140 ?>