X-Git-Url: http://git.euphorik.ch/?p=cl7.git;a=blobdiff_plain;f=php%2Fupdate_db.php;fp=php%2Fupdate_db.php;h=66a32169c06ad072453f9ccbe573ef12213bbe9e;hp=7f43381c5a9ec484e6a003349d80ae16481977e0;hb=6b9eac51629ad6fcec7da5f48b92f4c2b54c5901;hpb=7356c3443f39799adef2f8c803fb44509068c92f

diff --git a/php/update_db.php b/php/update_db.php
index 7f43381..66a3216 100644
--- a/php/update_db.php
+++ b/php/update_db.php
@@ -10,131 +10,125 @@ $connexion = new Connexion();
   
 function creer_db()
 {
-   mysql_query("   
-      CREATE TABLE IF NOT EXISTS config (
+   pg_query("   
+      CREATE TABLE 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("
+   pg_query("
       CREATE TABLE pizzas (
-        id mediumint(3) unsigned NOT NULL auto_increment,
+        id serial NOT NULL,
         nom varchar(40) NOT NULL,
         composition varchar(255) NOT NULL,
-        prix tinyint(3) unsigned default '0',
+        prix smallint NOT NULL,
         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',
+   pg_query("
+      CREATE TABLE jeux (
+        id serial NOT NULL,
+        nom varchar(200) NOT NULL,
         PRIMARY KEY (id),
-        UNIQUE KEY nom_unique (nom)
-      ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
+        UNIQUE (nom)
+      );
    ");
-   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;
+   pg_query("
+      CREATE TABLE participants (
+        id serial NOT NULL,
+        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 boolean NOT NULL default FALSE,
+        a_paye boolean NOT NULL default FALSE,
+        pizza int DEFAULT NULL,
+        pizza_paye boolean NOT NULL default FALSE,
+        PRIMARY KEY (id),
+        UNIQUE (pseudo),
+        FOREIGN KEY (pizza) REFERENCES pizzas (id) ON DELETE SET NULL ON UPDATE SET NULL
+      );
+   ");
+   pg_query("
+      CREATE TABLE jeux_choisis (
+        participant_id int NOT NULL,
+        jeu_id int NOT NULL,
+        PRIMARY KEY (participant_id, jeu_id),
+        FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
+        FOREIGN KEY (jeu_id) REFERENCES jeux (id) ON DELETE CASCADE ON UPDATE CASCADE
+      );
+   ");   
+   pg_query("
+      CREATE TABLE periodes (
+        id serial NOT NULL,
+        nom varchar(200) NOT NULL,
+        nom_abrege varchar(20) NOT NULL DEFAULT '',
+        PRIMARY KEY (id)
+      );
    ");
-   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;
+   pg_query("
+      CREATE TABLE participations (
+        participant_id serial NOT NULL,
+        periode_id int NOT NULL,
+        PRIMARY KEY (participant_id, periode_id),
+        FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
+        FOREIGN KEY (periode_id) REFERENCES periodes (id) ON DELETE CASCADE ON UPDATE CASCADE
+      );
    ");
 }
 
 function initialiser_db()
 {
-   mysql_query("INSERT INTO config (nom, valeur) VALUES ('version', 1)");
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('version', 1)");
+   
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('cout_periode', '15')");
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('cout_total', '40')");
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('nb_max_participant', '25')");
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('nb_votes_jeux', '3')");
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('inscription_terminees', 'FALSE')");
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('pizza_visible', 'FALSE')");
+   pg_query("INSERT INTO config (nom, valeur) VALUES ('pizza_peut_commander', 'TRUE')");
+   
+   pg_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('vendredi soir à samedi', 'v->s')");
+   pg_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('samedi à dimanche', 's->d')");
+   pg_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('dimanche à lundi', 'd->l')");
 }
 
 function update_db()
 {
    # si la table 'config' n'existe pas alors on suppose qu'aucune table n'existe
    $version = 0;
-   $res = @mysql_fetch_object(mysql_query("SELECT valeur FROM config WHERE nom = 'version'"));
-   if (!mysql_error())      
+   $res = @pg_fetch_object(pg_query("SELECT valeur FROM config WHERE nom = 'version'"));
+   if (pg_result_status($res) != PGSQL_COMMAND_OK)
       $version = $res->valeur;
       
    if($version == 0)
    {
-      mysql_query("BEGIN TRANSACTION");
+      echo "Création de la base de donnée, version = 1";
+      pg_query("BEGIN");
       creer_db();
       initialiser_db();
-      mysql_query("COMMIT");
+      pg_query("COMMIT");
       $version = 1;
    }
    
-   # version 1 -> 2
+   # version 1 -> 2 (à utiliser pour une utilisation future)
+   /*
    if ($version == 1)
    {
-      mysql_query("BEGIN TRANSACTION");
-      mysql_query("ALTER TABLE participants ADD CONSTRAINT UNIQUE INDEX pseudo_unique (pseudo);");
-      mysql_query("
-         CREATE TABLE IF NOT EXISTS periodes (
-           id mediumint(3) unsigned NOT NULL auto_increment,
-           nom varchar(200) NOT NULL,
-           nom_abrege varchar(20) NOT NULL DEFAULT '',
-           PRIMARY KEY (id)
-         ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
-      ");
-      mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('vendredi soir à samedi', 'v->s')");
-      mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('samedi à dimanche', 's->d')");
-      mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('dimanche à lundi', 'd->l')");
-      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;
-      ");
-      $participations = mysql_query("SELECT participants.id as participant_id, periodes.id as periode_id FROM participants, periodes");
-      while ($participation = mysql_fetch_object($participations))
-      {
-         mysql_query("
-            INSERT INTO participations (participant_id, periode_id)
-            VALUES (".$participation->participant_id.",".$participation->periode_id.")"
-         );
-      }
-      mysql_query("UPDATE config SET valeur = '2' WHERE nom = 'version'");
+      pg_query("BEGIN");
       
-      mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_periode', '15')");
-      mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_total', '40')");
-      mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_max_participant', '25')");
-      mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_votes_jeux', '3')");
-      mysql_query("INSERT INTO config (nom, valeur) VALUES ('inscription_terminees', 'FALSE')");
-      mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_visible', 'FALSE')");
-      mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_peut_commander', 'TRUE')");
+      # queries...      
+      pg_query("UPDATE config SET valeur = '2' WHERE nom = 'version'");     
       
-      mysql_query("COMMIT");
-   }
+      pg_query("COMMIT");
+   }*/
 }
 
 update_db();