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();
projects / cl7.git / blobdiff