<?php # encoding:utf-8
/**
  * Met à jour la base de données en fonction de la version courante de celle ci.
  * Si des tables n'existes pas elles sont automatiquement créées.
  */
  
include("connexion.php");
  
function creer_db()
{
   mysql_query("   
      CREATE TABLE IF NOT EXISTS config (
         nom varchar(50) NOT NULL,
         valeur varchar(255) NOT NULL,
         PRIMARY KEY (nom)
      ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
   ");
   mysql_query("
      CREATE TABLE pizzas (
        id mediumint(3) unsigned NOT NULL auto_increment,
        nom varchar(40) NOT NULL,
        composition varchar(255) NOT NULL,
        prix tinyint(3) unsigned default '0',
        PRIMARY KEY  (id)
      ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
   ");
   mysql_query("
      CREATE TABLE IF NOT EXISTS jeux (
        id mediumint(3) unsigned NOT NULL auto_increment,
        nom varchar(200) default '0',
        PRIMARY KEY (id),
        UNIQUE KEY nom_unique (nom)
      ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
   ");
   mysql_query("
      CREATE TABLE IF NOT EXISTS participants (
        id mediumint(3) unsigned NOT NULL auto_increment,
        pseudo varchar(50) default NULL,
        clan_nom varchar(30) default NULL,
        clan_tag varchar(10) default NULL,
        password varchar(10) default NULL,
        nom varchar(30) default NULL,
        prenom varchar(30) default NULL,
        age varchar(30) default NULL,
        e_mail varchar(50) default NULL,
        remarques varchar(255) default NULL,
        admin tinyint(1) unsigned NOT NULL default '0',
        a_paye tinyint(1) unsigned NOT NULL default '0',
        pizza mediumint(3) unsigned default NULL,
        pizza_paye tinyint(1) NOT NULL default '0',
        PRIMARY KEY  (id),
        KEY FK_pizza (pizza),
        CONSTRAINT FK_pizza FOREIGN KEY (pizza) REFERENCES pizzas (id) ON DELETE SET NULL ON UPDATE SET NULL
      ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
   ");
   mysql_query("
      CREATE TABLE IF NOT EXISTS jeux_choisis (
        participant_id mediumint(3) unsigned NOT NULL,
        jeu_id mediumint(3) unsigned NOT NULL,
        PRIMARY KEY USING BTREE (participant_id,jeu_id),
        KEY FK_jeu (jeu_id),
        CONSTRAINT FK_participant FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT FK_jeu FOREIGN KEY (jeu_id) REFERENCES jeux (id) ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
   ");
}

function initialiser_db()
{
   mysql_query("INSERT INTO config (nom, valeur) VALUES ('version', 1)");
}

function update_db()
{
   # si la table 'config' n'existe pas alors on suppose qu'aucune table n'existe
   $version = 0;
   if(!$version = (int)@mysql_fetch_object(mysql_query("SELECT valeur FROM config WHERE nom = 'version'")))
   {
      mysql_query("BEGIN TRANSACTION");
      creer_db();
      initialiser_db();
      mysql_query("COMMIT");
      $version = 1;
   }
   
   # version 1 -> 2
   if ($version == 1)
   {
      mysql_query("BEGIN TRANSACTION");
      mysql_query("
         CREATE TABLE IF NOT EXISTS periodes (
           id mediumint(3) unsigned NOT NULL auto_increment,
           nom varchar(200) NOT NULL,
           PRIMARY KEY (id)
         ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
      ");
      mysql_query("INSERT INTO periodes (nom) VALUES ('Vendredi soir à samedi')");
      mysql_query("INSERT INTO periodes (nom) VALUES ('Samedi à dimanche')");
      mysql_query("INSERT INTO periodes (nom) VALUES ('Dimanche à lundi')");
      mysql_query("
         CREATE TABLE IF NOT EXISTS participations (
           participant_id mediumint(3) unsigned NOT NULL,
           periode_id mediumint(3) unsigned NOT NULL,
           PRIMARY KEY USING BTREE (participant_id, periode_id),
           KEY FK_periode (periode_id),
           CONSTRAINT FK_participant_participations FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
           CONSTRAINT FK_periode_participations FOREIGN KEY (periode_id) REFERENCES periodes (id) ON DELETE CASCADE ON UPDATE CASCADE
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
      ");
      mysql_query("UPDATE config SET valeur = '2' WHERE nom = 'version')");
      mysql_query("COMMIT");
   }
}

update_db();

?>