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.
7 include_once("connexion.php");
9 $connexion = new Connexion();
14 CREATE TABLE IF NOT EXISTS config (
15 nom varchar(50) NOT NULL,
16 valeur varchar(255) NOT NULL,
18 ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
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',
27 ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
30 CREATE TABLE IF NOT EXISTS jeux (
31 id mediumint(3) unsigned NOT NULL auto_increment,
32 nom varchar(200) default '0',
34 UNIQUE KEY nom_unique (nom)
35 ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
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',
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;
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),
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;
70 function initialiser_db()
72 mysql_query("INSERT INTO config (nom, valeur) VALUES ('version', 1)");
77 # si la table 'config' n'existe pas alors on suppose qu'aucune table n'existe
79 $res = @mysql_fetch_object(mysql_query("SELECT valeur FROM config WHERE nom = 'version'"));
81 $version = $res->valeur
;
85 mysql_query("BEGIN TRANSACTION");
88 mysql_query("COMMIT");
95 mysql_query("BEGIN TRANSACTION");
96 mysql_query("ALTER TABLE participants ADD CONSTRAINT UNIQUE INDEX pseudo_unique (pseudo);");
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 '',
103 ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
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')");
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;
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))
122 INSERT INTO participations (participant_id, periode_id)
123 VALUES (".$participation->participant_id
.",".$participation->periode_id
.")"
126 mysql_query("UPDATE config SET valeur = '2' WHERE nom = 'version'");
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')");
136 mysql_query("COMMIT");