Commits

Helge Plaschke committed 31f4d7e

initial commit
created sql script for gamdb database

Comments (0)

Files changed (1)

+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 ;