Commits

Helge Plaschke  committed b1196e1

forked gamdb, extended sql script with league tables,renamed sql script

  • Participants
  • Parent commits 31f4d7e

Comments (0)

Files changed (2)

File gamdb.sql

-DROP TABLE IF EXISTS genres;
-CREATE TABLE genres (
-  id int(11) NOT NULL AUTO_INCREMENT,
-  designation varchar(100) NOT NULL,
-  PRIMARY KEY (id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS platforms;
-CREATE TABLE platforms (
-  id int(11) NOT NULL AUTO_INCREMENT,
-  designation varchar(100) NOT NULL,
-  handle varchar(10) NOT NULL,
-  PRIMARY KEY (id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS publishers;
-CREATE TABLE publishers (
-  id int(11) NOT NULL AUTO_INCREMENT,
-  designation varchar(100) DEFAULT NULL,
-  PRIMARY KEY (id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS users;
-CREATE TABLE users (
-  id int(11) NOT NULL AUTO_INCREMENT,
-  username varchar(100) NOT NULL,
-  password varchar(100) NOT NULL,
-  firstname varchar(100) DEFAULT NULL,
-  lastname varchar(100) DEFAULT NULL,
-  userlevel int(10) unsigned DEFAULT NULL,
-  points int(11) DEFAULT NULL,
-  PRIMARY KEY (id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS games;
-CREATE TABLE games (
-  id int(11) NOT NULL AUTO_INCREMENT,
-  designation varchar(100) DEFAULT NULL,
-  genre_id int(11) DEFAULT NULL,
-  publisher_id int(11) DEFAULT NULL,
-  rating float DEFAULT NULL,
-  description text,
-  singleplayer tinyint(1) DEFAULT NULL,
-  multiplayer tinyint(1) DEFAULT NULL,
-  coop tinyint(1) DEFAULT NULL,
-  online tinyint(1) DEFAULT NULL,
-  releasedate date DEFAULT NULL,
-  platform_id int(11) DEFAULT NULL,
-  PRIMARY KEY (id),
-  KEY genre_id (genre_id),
-  KEY publisher_id (publisher_id),
-  KEY platform_id (platform_id),
-  CONSTRAINT games_ibfk_plat FOREIGN KEY (platform_id) REFERENCES platforms (id),
-  CONSTRAINT games_ibfk_gen FOREIGN KEY (genre_id) REFERENCES genres (id),
-  CONSTRAINT games_ibfk_pub FOREIGN KEY (publisher_id) REFERENCES publishers (id)
-) ENGINE=InnoDB;
-
-DROP TABLE IF EXISTS games_users;
-CREATE TABLE games_users (
-  user_id int(11) NOT NULL,
-  game_id int(11) NOT NULL,
-  rating int(11) DEFAULT NULL,
-  PRIMARY KEY (user_id,game_id),
-  KEY game_id (game_id),
-  CONSTRAINT games_users_ibfk_gam FOREIGN KEY (game_id) REFERENCES games (id),
-  CONSTRAINT games_users_ibfk_use FOREIGN KEY (user_id) REFERENCES users (id)
-) ENGINE=InnoDB;
-
-LOCK TABLES users WRITE;
-INSERT INTO users (username,password,userlevel) VALUES ('admin','5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8',1);
-UNLOCK TABLES;
-
-LOCK TABLES publishers WRITE;
-INSERT INTO publishers (designation) VALUES ('Activision');
-INSERT INTO publishers (designation) VALUES ('Blizzard');
-INSERT INTO publishers (designation) VALUES ('BlueByte');
-INSERT INTO publishers (designation) VALUES ('Take2');
-INSERT INTO publishers (designation) VALUES ('THQ');
-INSERT INTO publishers (designation) VALUES ('Ubisoft');
-INSERT INTO publishers (designation) VALUES ('Microsoft');
-INSERT INTO publishers (designation) VALUES ('Bethesda');
-INSERT INTO publishers (designation) VALUES ('BioWare');
-INSERT INTO publishers (designation) VALUES ('dtp');
-INSERT INTO publishers (designation) VALUES ('Ascaron');
-INSERT INTO publishers (designation) VALUES ('Capcom');
-INSERT INTO publishers (designation) VALUES ('Sega');
-INSERT INTO publishers (designation) VALUES ('Nintendo');
-INSERT INTO publishers (designation) VALUES ('Sony');
-INSERT INTO publishers (designation) VALUES ('Codemaster');
-INSERT INTO publishers (designation) VALUES ('Electronic Arts');
-UNLOCK TABLES;
-
-LOCK TABLES genres WRITE;
-INSERT INTO genres (designation) VALUES ('Adventure');
-INSERT INTO genres (designation) VALUES ('Action');
-INSERT INTO genres (designation) VALUES ('Arcade');
-INSERT INTO genres (designation) VALUES ('Action-Adventure');
-INSERT INTO genres (designation) VALUES ('Jump \'n\' Run');
-INSERT INTO genres (designation) VALUES ('Simulation');
-INSERT INTO genres (designation) VALUES ('FPS');
-INSERT INTO genres (designation) VALUES ('RTS');
-INSERT INTO genres (designation) VALUES ('RBS');
-INSERT INTO genres (designation) VALUES ('RPG');
-UNLOCK TABLES;
-
-LOCK TABLES platforms WRITE;
-INSERT INTO platforms (designation,handle) VALUES ('Personal Computer','PC');
-INSERT INTO platforms (designation,handle) VALUES ('Playstation One','PS1');
-INSERT INTO platforms (designation,handle) VALUES ('Playstation 2','PS2');
-INSERT INTO platforms (designation,handle) VALUES ('Playstation 3','PS3');
-INSERT INTO platforms (designation,handle) VALUES ('Playstation Portable','PSP');
-INSERT INTO platforms (designation,handle) VALUES ('XBOX','XBOX');
-INSERT INTO platforms (designation,handle) VALUES ('XBOX 360','XBOX 360');
-INSERT INTO platforms (designation,handle) VALUES ('Super Nintendo','SNES');
-INSERT INTO platforms (designation,handle) VALUES ('Nintendo Game Cube','GC');
-INSERT INTO platforms (designation,handle) VALUES ('Nintento Wii','WII');
-INSERT INTO platforms (designation,handle) VALUES ('GameBoy','GB');
-INSERT INTO platforms (designation,handle) VALUES ('GameBoy Advanced','GBA');
-INSERT INTO platforms (designation,handle) VALUES ('Nintendo DS','NDS');
-INSERT INTO platforms (designation,handle) VALUES ('Dreamcast','DC');
-UNLOCK TABLES;
-
-
-DELIMITER |
-CREATE TRIGGER ins_gamesusers AFTER INSERT ON games_users
-FOR EACH ROW
-UPDATE games SET games.rating = (SELECT AVG(games_users.rating) FROM games_users WHERE games_users.game_id = NEW.game_id) WHERE games.id = NEW.game_id |
-
-CREATE TRIGGER mod_gamesusers AFTER UPDATE ON games_users
-FOR EACH ROW
-UPDATE games SET games.rating = (SELECT AVG(games_users.rating) FROM games_users WHERE games_users.game_id = NEW.game_id) WHERE games.id = NEW.game_id |
-
-CREATE TRIGGER del_gamesusers AFTER DELETE ON games_users
-FOR EACH ROW
-UPDATE games SET games.rating = (SELECT AVG(games_users.rating) FROM games_users WHERE games_users.game_id = OLD.game_id) WHERE games.id = OLD.game_id |
-DELIMITER ;

File gdbleaguedb.sql

+DROP TABLE IF EXISTS genres;
+CREATE TABLE genres (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  designation varchar(100) NOT NULL,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS platforms;
+CREATE TABLE platforms (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  designation varchar(100) NOT NULL,
+  handle varchar(10) NOT NULL,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS publishers;
+CREATE TABLE publishers (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  designation varchar(100) DEFAULT NULL,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS tourneytypes;
+CREATE TABLE tourneytypes (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  designation varchar(100) DEFAULT NULL,
+  calcmode int(11) DEFAULT NULL,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS users;
+CREATE TABLE users (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  username varchar(100) NOT NULL,
+  password varchar(100) NOT NULL,
+  firstname varchar(100) DEFAULT NULL,
+  lastname varchar(100) DEFAULT NULL,
+  userlevel int(10) unsigned DEFAULT NULL,
+  points int(11) DEFAULT NULL,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS teams;
+CREATE TABLE teams (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  owner_id int(11) NOT NULL,
+  designation varchar(100) NOT NULL,
+  handle varchar(50) NOT NULL,
+  description text,
+  points int(11) DEFAULT NULL,
+  PRIMARY KEY (id),
+  KEY owner_id (owner_id),
+  CONSTRAINT teams_ibfk_use FOREIGN KEY (owner_id) REFERENCES users (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS games;
+CREATE TABLE games (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  designation varchar(100) DEFAULT NULL,
+  genre_id int(11) DEFAULT NULL,
+  publisher_id int(11) DEFAULT NULL,
+  rating float DEFAULT NULL,
+  description text,
+  singleplayer tinyint(1) DEFAULT NULL,
+  multiplayer tinyint(1) DEFAULT NULL,
+  coop tinyint(1) DEFAULT NULL,
+  online tinyint(1) DEFAULT NULL,
+  releasedate date DEFAULT NULL,
+  platform_id int(11) DEFAULT NULL,
+  PRIMARY KEY (id),
+  KEY genre_id (genre_id),
+  KEY publisher_id (publisher_id),
+  KEY platform_id (platform_id),
+  CONSTRAINT games_ibfk_plat FOREIGN KEY (platform_id) REFERENCES platforms (id),
+  CONSTRAINT games_ibfk_gen FOREIGN KEY (genre_id) REFERENCES genres (id),
+  CONSTRAINT games_ibfk_pub FOREIGN KEY (publisher_id) REFERENCES publishers (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS tourneys;
+CREATE TABLE tourneys (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  designation varchar(100) NOT NULL,
+  game_id int(11) NOT NULL,
+  tourneytype_id int(11) NOT NULL,
+  admin_id int(11) NOT NULL,
+  description text,
+  PRIMARY KEY (id),
+  KEY game_id (game_id),
+  KEY admin_id (admin_id),
+  KEY tourneytype_id (tourneytype_id),
+  CONSTRAINT tourneys_ibfk_tot FOREIGN KEY (tourneytype_id) REFERENCES tourneytypes (id),
+  CONSTRAINT tourneys_ibfk_gam FOREIGN KEY (game_id) REFERENCES games (id),
+  CONSTRAINT tourneys_ibfk_use FOREIGN KEY (admin_id) REFERENCES users (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS games_users;
+CREATE TABLE games_users (
+  user_id int(11) NOT NULL,
+  game_id int(11) NOT NULL,
+  rating int(11) DEFAULT NULL,
+  PRIMARY KEY (user_id,game_id),
+  KEY game_id (game_id),
+  CONSTRAINT games_users_ibfk_gam FOREIGN KEY (game_id) REFERENCES games (id),
+  CONSTRAINT games_users_ibfk_use FOREIGN KEY (user_id) REFERENCES users (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS matches;
+CREATE TABLE matches (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  dateplanned date NOT NULL,
+  tourney_id int(11) NOT NULL,
+  challengerpoints int(11) DEFAULT NULL,
+  opponentpoints int(11) DEFAULT NULL,
+  finished tinyint(1) NOT NULL,
+  PRIMARY KEY (id),
+  KEY tourney_id (tourney_id),
+  CONSTRAINT matches_ibfk_tou FOREIGN KEY (tourney_id) REFERENCES tourneys (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS teams_matches;
+CREATE TABLE teams_matches (
+  team_id int(11) NOT NULL,
+  match_id int(11) NOT NULL,
+  challenger tinyint(1) NOT NULL,
+  accepted tinyint(1) DEFAULT NULL,
+  PRIMARY KEY (team_id,match_id)
+) ENGINE=MyISAM;
+
+DROP TABLE IF EXISTS teams_tourneys;
+CREATE TABLE teams_tourneys (
+  team_id int(11) NOT NULL DEFAULT 0,
+  tourney_id int(11) NOT NULL DEFAULT 0,
+  points int(11) DEFAULT NULL,
+  win int(11) DEFAULT NULL,
+  loss int(11) DEFAULT NULL,
+  PRIMARY KEY (team_id,tourney_id),
+  KEY tourney_id (tourney_id),
+  CONSTRAINT teams_tourneys_ibfk_tou FOREIGN KEY (tourney_id) REFERENCES tourneys (id),
+  CONSTRAINT teams_tourneys_ibfk_tea FOREIGN KEY (team_id) REFERENCES teams (id)
+) ENGINE=InnoDB;
+
+DROP TABLE IF EXISTS users_matches;
+CREATE TABLE users_matches (
+  user_id int(11) NOT NULL,
+  match_id int(11) NOT NULL,
+  challenger tinyint(1) NOT NULL,
+  accepted tinyint(1) DEFAULT NULL,
+  PRIMARY KEY (user_id,match_id),
+  KEY match_id (match_id),
+  CONSTRAINT users_matches_ibfk_use FOREIGN KEY (user_id) REFERENCES users (id),
+  CONSTRAINT users_matches_ibfk_mat FOREIGN KEY (match_id) REFERENCES matches (id)
+) ENGINE=InnoB;
+
+DROP TABLE IF EXISTS users_teams;
+CREATE TABLE users_teams (
+  user_id int(11) NOT NULL,
+  team_id int(11) NOT NULL,
+  position int(11) DEFAULT NULL,
+  PRIMARY KEY (user_id,team_id),
+  KEY team_id (team_id),
+  CONSTRAINT users_teams_ibfk_use FOREIGN KEY (user_id) REFERENCES users (id),
+  CONSTRAINT users_teams_ibfk_tea FOREIGN KEY (team_id) REFERENCES teams (id)
+) ENGINE=InnoDB;
+
+
+
+LOCK TABLES users WRITE;
+INSERT INTO users (username,password,userlevel) VALUES ('admin','5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8',1);
+UNLOCK TABLES;
+
+LOCK TABLES tourneytypes WRITE;
+INSERT INTO tourneytypes (designation) VALUES ('Ladder');
+INSERT INTO tourneytypes (designation) VALUES ('Elimination');
+INSERT INTO tourneytypes (designation) VALUES ('OpenLadder');
+INSERT INTO tourneytypes (designation) VALUES ('Just4Fun');
+INSERT INTO tourneytypes (designation) VALUES ('Coop');
+UNLOCK TABLES;
+
+LOCK TABLES publishers WRITE;
+INSERT INTO publishers (designation) VALUES ('Activision');
+INSERT INTO publishers (designation) VALUES ('Blizzard');
+INSERT INTO publishers (designation) VALUES ('BlueByte');
+INSERT INTO publishers (designation) VALUES ('Take2');
+INSERT INTO publishers (designation) VALUES ('THQ');
+INSERT INTO publishers (designation) VALUES ('Ubisoft');
+INSERT INTO publishers (designation) VALUES ('Microsoft');
+INSERT INTO publishers (designation) VALUES ('Bethesda');
+INSERT INTO publishers (designation) VALUES ('BioWare');
+INSERT INTO publishers (designation) VALUES ('dtp');
+INSERT INTO publishers (designation) VALUES ('Ascaron');
+INSERT INTO publishers (designation) VALUES ('Capcom');
+INSERT INTO publishers (designation) VALUES ('Sega');
+INSERT INTO publishers (designation) VALUES ('Nintendo');
+INSERT INTO publishers (designation) VALUES ('Sony');
+INSERT INTO publishers (designation) VALUES ('Codemaster');
+INSERT INTO publishers (designation) VALUES ('Electronic Arts');
+UNLOCK TABLES;
+
+LOCK TABLES genres WRITE;
+INSERT INTO genres (designation) VALUES ('Adventure');
+INSERT INTO genres (designation) VALUES ('Action');
+INSERT INTO genres (designation) VALUES ('Arcade');
+INSERT INTO genres (designation) VALUES ('Action-Adventure');
+INSERT INTO genres (designation) VALUES ('Jump \'n\' Run');
+INSERT INTO genres (designation) VALUES ('Simulation');
+INSERT INTO genres (designation) VALUES ('FPS');
+INSERT INTO genres (designation) VALUES ('RTS');
+INSERT INTO genres (designation) VALUES ('RBS');
+INSERT INTO genres (designation) VALUES ('RPG');
+UNLOCK TABLES;
+
+LOCK TABLES platforms WRITE;
+INSERT INTO platforms (designation,handle) VALUES ('Personal Computer','PC');
+INSERT INTO platforms (designation,handle) VALUES ('Playstation One','PS1');
+INSERT INTO platforms (designation,handle) VALUES ('Playstation 2','PS2');
+INSERT INTO platforms (designation,handle) VALUES ('Playstation 3','PS3');
+INSERT INTO platforms (designation,handle) VALUES ('Playstation Portable','PSP');
+INSERT INTO platforms (designation,handle) VALUES ('XBOX','XBOX');
+INSERT INTO platforms (designation,handle) VALUES ('XBOX 360','XBOX 360');
+INSERT INTO platforms (designation,handle) VALUES ('Super Nintendo','SNES');
+INSERT INTO platforms (designation,handle) VALUES ('Nintendo Game Cube','GC');
+INSERT INTO platforms (designation,handle) VALUES ('Nintento Wii','WII');
+INSERT INTO platforms (designation,handle) VALUES ('GameBoy','GB');
+INSERT INTO platforms (designation,handle) VALUES ('GameBoy Advanced','GBA');
+INSERT INTO platforms (designation,handle) VALUES ('Nintendo DS','NDS');
+INSERT INTO platforms (designation,handle) VALUES ('Dreamcast','DC');
+UNLOCK TABLES;
+
+
+
+DELIMITER |
+CREATE TRIGGER ins_gamesusers AFTER INSERT ON games_users
+FOR EACH ROW
+UPDATE games SET games.rating = (SELECT AVG(games_users.rating) FROM games_users WHERE games_users.game_id = NEW.game_id) WHERE games.id = NEW.game_id |
+
+CREATE TRIGGER mod_gamesusers AFTER UPDATE ON games_users
+FOR EACH ROW
+UPDATE games SET games.rating = (SELECT AVG(games_users.rating) FROM games_users WHERE games_users.game_id = NEW.game_id) WHERE games.id = NEW.game_id |
+
+CREATE TRIGGER del_gamesusers AFTER DELETE ON games_users
+FOR EACH ROW
+UPDATE games SET games.rating = (SELECT AVG(games_users.rating) FROM games_users WHERE games_users.game_id = OLD.game_id) WHERE games.id = OLD.game_id |
+
+CREATE TRIGGER ins_usersmatches AFTER INSERT ON matches
+FOR EACH ROW
+UPDATE users SET users.points = (SELECT SUM(matches.challengerpoints) FROM matches INNER JOIN users_matches ON matches.id = users_matches.match_id WHERE users_matches.match_id = NEW.id && users_matches.challenger = TRUE) + (SELECT SUM(matches.opponentpoints) FROM matches INNER JOIN users_matches ON matches.id = users_matches.match_id WHERE users_matches.match_id = NEW.id AND users_matches.challenger = FALSE) |
+
+CREATE TRIGGER mod_usersmatches AFTER UPDATE ON matches
+FOR EACH ROW
+UPDATE users SET users.points = (SELECT SUM(matches.challengerpoints) FROM matches INNER JOIN users_matches ON matches.id = users_matches.match_id WHERE users_matches.match_id = NEW.id && users_matches.challenger = TRUE) + (SELECT SUM(matches.opponentpoints) FROM matches INNER JOIN users_matches ON matches.id = users_matches.match_id WHERE users_matches.match_id = NEW.id AND users_matches.challenger = FALSE) |
+
+CREATE TRIGGER del_usersmatches AFTER DELETE ON matches
+FOR EACH ROW
+UPDATE users SET users.points = (SELECT SUM(matches.challengerpoints) FROM matches INNER JOIN users_matches ON matches.id = users_matches.match_id WHERE users_matches.match_id = OLD.id && users_matches.challenger = TRUE) + (SELECT SUM(matches.opponentpoints) FROM matches INNER JOIN users_matches ON matches.id = users_matches.match_id WHERE users_matches.match_id = OLD.id AND users_matches.challenger = FALSE) |
+DELIMITER ;