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_once("connexion.php");
   8
   9 $connexion = new Connexion();
  10
  11 function creer_db()
  12 {
  13    mysql_query("
  14       CREATE TABLE IF NOT EXISTS config (
  15          nom varchar(50) NOT NULL,
  16          valeur varchar(255) NOT NULL,
  17          PRIMARY KEY (nom)
  18       ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
  19    ");
  20    mysql_query("
  21       CREATE TABLE pizzas (
  22         id mediumint(3) unsigned NOT NULL auto_increment,
  23         nom varchar(40) NOT NULL,
  24         composition varchar(255) NOT NULL,
  25         prix tinyint(3) unsigned default '0',
  26         PRIMARY KEY  (id)
  27       ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
  28    ");
  29    mysql_query("
  30       CREATE TABLE IF NOT EXISTS jeux (
  31         id mediumint(3) unsigned NOT NULL auto_increment,
  32         nom varchar(200) default '0',
  33         PRIMARY KEY (id),
  34         UNIQUE KEY nom_unique (nom)
  35       ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
  36    ");
  37    mysql_query("
  38       CREATE TABLE IF NOT EXISTS participants (
  39         id mediumint(3) unsigned NOT NULL auto_increment,
  40         pseudo varchar(50) default NULL,
  41         clan_nom varchar(30) default NULL,
  42         clan_tag varchar(10) default NULL,
  43         password varchar(10) default NULL,
  44         nom varchar(30) default NULL,
  45         prenom varchar(30) default NULL,
  46         age varchar(30) default NULL,
  47         e_mail varchar(50) default NULL,
  48         remarques varchar(255) default NULL,
  49         admin tinyint(1) unsigned NOT NULL default '0',
  50         a_paye tinyint(1) unsigned NOT NULL default '0',
  51         pizza mediumint(3) unsigned default NULL,
  52         pizza_paye tinyint(1) NOT NULL default '0',
  53         PRIMARY KEY  (id),
  54         KEY FK_pizza (pizza),
  55         CONSTRAINT FK_pizza FOREIGN KEY (pizza) REFERENCES pizzas (id) ON DELETE SET NULL ON UPDATE SET NULL
  56       ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  57    ");
  58    mysql_query("
  59       CREATE TABLE IF NOT EXISTS jeux_choisis (
  60         participant_id mediumint(3) unsigned NOT NULL,
  61         jeu_id mediumint(3) unsigned NOT NULL,
  62         PRIMARY KEY USING BTREE (participant_id,jeu_id),
  63         KEY FK_jeu (jeu_id),
  64         CONSTRAINT FK_participant FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
  65         CONSTRAINT FK_jeu FOREIGN KEY (jeu_id) REFERENCES jeux (id) ON DELETE CASCADE ON UPDATE CASCADE
  66       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  67    ");
  68 }
  69
  70 function initialiser_db()
  71 {
  72    mysql_query("INSERT INTO config (nom, valeur) VALUES ('version', 1)");
  73 }
  74
  75 function update_db()
  76 {
  77    # si la table 'config' n'existe pas alors on suppose qu'aucune table n'existe
  78    $version = 0;
  79    $res = @mysql_fetch_object(mysql_query("SELECT valeur FROM config WHERE nom = 'version'"));
  80    if (!mysql_error())
  81       $version = $res->valeur;
  82
  83    if($version == 0)
  84    {
  85       mysql_query("BEGIN TRANSACTION");
  86       creer_db();
  87       initialiser_db();
  88       mysql_query("COMMIT");
  89       $version = 1;
  90    }
  91
  92    # version 1 -> 2
  93    if ($version == 1)
  94    {
  95       mysql_query("BEGIN TRANSACTION");
  96       mysql_query("ALTER TABLE participants ADD CONSTRAINT UNIQUE INDEX pseudo_unique (pseudo);");
  97       mysql_query("
  98          CREATE TABLE IF NOT EXISTS periodes (
  99            id mediumint(3) unsigned NOT NULL auto_increment,
 100            nom varchar(200) NOT NULL,
 101            nom_abrege varchar(20) NOT NULL DEFAULT '',
 102            PRIMARY KEY (id)
 103          ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
 104       ");
 105       mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('vendredi soir à samedi', 'v->s')");
 106       mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('samedi à dimanche', 's->d')");
 107       mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('dimanche à lundi', 'd->l')");
 108       mysql_query("
 109          CREATE TABLE IF NOT EXISTS participations (
 110            participant_id mediumint(3) unsigned NOT NULL,
 111            periode_id mediumint(3) unsigned NOT NULL,
 112            PRIMARY KEY USING BTREE (participant_id, periode_id),
 113            KEY FK_periode (periode_id),
 114            CONSTRAINT FK_participant_participations FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
 115            CONSTRAINT FK_periode_participations FOREIGN KEY (periode_id) REFERENCES periodes (id) ON DELETE CASCADE ON UPDATE CASCADE
 116          ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
 117       ");
 118       $participations = mysql_query("SELECT participants.id as participant_id, periodes.id as periode_id FROM participants, periodes");
 119       while ($participation = mysql_fetch_object($participations))
 120       {
 121          mysql_query("
 122             INSERT INTO participations (participant_id, periode_id)
 123             VALUES (".$participation->participant_id.",".$participation->periode_id.")"
 124          );
 125       }
 126       mysql_query("UPDATE config SET valeur = '2' WHERE nom = 'version'");
 127
 128       mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_periode', '15')");
 129       mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_total', '40')");
 130       mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_max_participant', '25')");
 131       mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_votes_jeux', '3')");
 132       mysql_query("INSERT INTO config (nom, valeur) VALUES ('inscription_terminees', 'FALSE')");
 133       mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_visible', 'FALSE')");
 134       mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_peut_commander', 'TRUE')");
 135
 136       mysql_query("COMMIT");
 137    }
 138 }
 139
 140 update_db();
 141
 142 ?>