MOD déplacement des fichiers php dans le dossier PHP (sauf index.php)

[cl7.git] / php / update_db.php

diff --git a/php/update_db.php b/php/update_db.php
new file mode 100644 (file)
index 0000000..5ff5904
--- /dev/null
+++ b/php/update_db.php
@@ -0,0 +1,117 @@
+<?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();
+
+?>