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