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