From cf52f753cfe1f06f4081510175d3584f122848af Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Mon, 24 Apr 2023 16:01:23 +0300 Subject: feat/db: multiple game/chapter support, improved map relations (#1) --- backend/database/categories.sql | 5 + backend/database/chapters.sql | 17 +++ backend/database/games.sql | 3 + backend/database/init.sql | 98 ++++++++--------- backend/database/maps.sql | 228 ++++++++++++++++++++-------------------- 5 files changed, 189 insertions(+), 162 deletions(-) create mode 100644 backend/database/categories.sql create mode 100644 backend/database/chapters.sql create mode 100644 backend/database/games.sql (limited to 'backend/database') diff --git a/backend/database/categories.sql b/backend/database/categories.sql new file mode 100644 index 0000000..5cadad5 --- /dev/null +++ b/backend/database/categories.sql @@ -0,0 +1,5 @@ +INSERT INTO categories(id, name) VALUES +(1, 'CM'), +(2, 'No SLA'), +(3, 'Inbounds SLA'), +(4, 'Any%'); \ No newline at end of file diff --git a/backend/database/chapters.sql b/backend/database/chapters.sql new file mode 100644 index 0000000..f01ae9c --- /dev/null +++ b/backend/database/chapters.sql @@ -0,0 +1,17 @@ +INSERT INTO chapters(id, game_id, name) VALUES +(1, 1, 'The Coutesy Call'), +(2, 1, 'The Cold Boot'), +(3, 1, 'The Return'), +(4, 1, 'The Surprise'), +(5, 1, 'The Escape'), +(6, 1, 'The Fall'), +(7, 1, 'The Reunion'), +(8, 1, 'The Itch'), +(9, 1, 'The Part Where He Kills You'), +(10, 2, 'Introduction'), +(11, 2, 'Team Building'), +(12, 2, 'Mass And Velocity'), +(13, 2, 'Hard-Light Surfaces'), +(14, 2, 'Excursion Funnels'), +(15, 2, 'Mobility Gels'), +(16, 2, 'Art Therapy'); \ No newline at end of file diff --git a/backend/database/games.sql b/backend/database/games.sql new file mode 100644 index 0000000..5e2f4ee --- /dev/null +++ b/backend/database/games.sql @@ -0,0 +1,3 @@ +INSERT INTO games(id, name) VALUES +(1, 'Portal 2 - Singleplayer'), +(2, 'Portal 2 - Cooperative'); \ No newline at end of file diff --git a/backend/database/init.sql b/backend/database/init.sql index 7e225f0..a82404a 100644 --- a/backend/database/init.sql +++ b/backend/database/init.sql @@ -1,13 +1,3 @@ -DROP TABLE IF EXISTS showcases; -DROP TABLE IF EXISTS titles; -DROP TABLE IF EXISTS records_sp; -DROP TABLE IF EXISTS records_mp; -DROP TABLE IF EXISTS maps; -DROP TABLE IF EXISTS users; -DROP TABLE IF EXISTS countries; - -DROP TABLE IF EXISTS demos; - CREATE TABLE users ( steam_id TEXT, username TEXT NOT NULL, @@ -18,14 +8,59 @@ CREATE TABLE users ( PRIMARY KEY (steam_id) ); +CREATE TABLE games ( + id SMALLSERIAL, + name TEXT NOT NULL, + PRIMARY KEY (id) +); + +CREATE TABLE chapters ( + id SMALLSERIAL, + game_id SMALLINT NOT NULL, + name TEXT NOT NULL, + PRIMARY KEY (id), + FOREIGN KEY (game_id) REFERENCES games(id) +); + +CREATE TABLE categories ( + id SMALLSERIAL, + name TEXT NOT NULL, + PRIMARY KEY (id) +); + CREATE TABLE maps ( id SMALLSERIAL, - map_name TEXT NOT NULL, - wr_score SMALLINT NOT NULL, - wr_time INTEGER NOT NULL, - is_coop BOOLEAN NOT NULL, + game_id SMALLINT NOT NULL, + chapter_id SMALLINT NOT NULL, + name TEXT NOT NULL, + description TEXT NOT NULL, + showcase TEXT NOT NULL, is_disabled BOOLEAN NOT NULL DEFAULT false, - PRIMARY KEY (id) + PRIMARY KEY (id), + FOREIGN KEY (game_id) REFERENCES games(id), + FOREIGN KEY (chapter_id) REFERENCES chapters(id) +); + +CREATE TABLE map_history ( + id SMALLSERIAL, + map_id SMALLINT NOT NULL, + user_id TEXT, + user_name TEXT NOT NULL, + score_count SMALLINT NOT NULL, + record_date TIMESTAMP NOT NULL, + PRIMARY KEY (id), + FOREIGN KEY (map_id) REFERENCES maps(id), + FOREIGN KEY (user_id) REFERENCES users(steam_id) +); + +CREATE TABLE map_rating ( + id SERIAL, + map_id SMALLINT NOT NULL, + user_id TEXT NOT NULL, + rating SMALLINT NOT NULL, + PRIMARY KEY (id), + FOREIGN KEY (map_id) REFERENCES maps(id), + FOREIGN KEY (user_id) REFERENCES users(steam_id) ); CREATE TABLE demos ( @@ -78,36 +113,3 @@ CREATE TABLE countries ( country_name TEXT NOT NULL, PRIMARY KEY (country_code) ); - --- CREATE TABLE community_maps ( --- id SMALLSERIAL, --- map_id SMALLINT NOT NULL, --- category TEXT NOT NULL, --- tutorial TEXT NOT NULL, --- video_link TEXT NOT NULL, --- PRIMARY KEY (id), --- FOREIGN KEY (map_id) REFERENCES maps(id) --- ); - --- TODO --- CREATE TABLE community_history ( --- id SERIAL, --- map_id SMALLINT NOT NULL, --- user_id TEXT NOT NULL, --- score_count SMALLINT NOT NULL, --- created_at TIMESTAMP NOT NULL DEFAULT now(), --- PRIMARY KEY (id), --- FOREIGN KEY (user_id) REFERENCES users(steam_id), --- FOREIGN KEY (map_id) REFERENCES maps(id) --- ); - --- CREATE TABLE community_posts ( --- id SERIAL, --- map_id SMALLINT NOT NULL, --- user_id TEXT NOT NULL, --- post TEXT NOT NULL, --- created_at TIMESTAMP NOT NULL DEFAULT now(), --- PRIMARY KEY (id), --- FOREIGN KEY (user_id) REFERENCES users(steam_id), --- FOREIGN KEY (map_id) REFERENCES maps(id) --- ); \ No newline at end of file diff --git a/backend/database/maps.sql b/backend/database/maps.sql index 127eb58..50689e2 100644 --- a/backend/database/maps.sql +++ b/backend/database/maps.sql @@ -1,129 +1,129 @@ -DELETE FROM maps; -INSERT INTO maps (map_name, wr_score, wr_time, is_coop, is_disabled) VALUES --- Singleplayer +INSERT INTO maps(game_id, chapter_id, name, description, showcase, is_disabled) VALUES +-- Portal 2 Singleplayer -- 1 -('Container Ride',0,-1,false,true), -('Portal Carousel',0,-1,false,true), -('Portal Gun',0,-1,false,false), -('Smooth Jazz',0,-1,false,false), -('Cube Momentum',1,-1,false,false), -('Future Starter',2,-1,false,false), -('Secret Panel',0,-1,false,false), -('Wakeup',0,-1,false,true), -('Incinerator',0,-1,false,false), +(1,1,'Container Ride','','',true), +(1,1,'Portal Carousel','','',true), +(1,1,'Portal Gun','','',false), +(1,1,'Smooth Jazz','','',false), +(1,1,'Cube Momentum','','',false), +(1,1,'Future Starter','','',false), +(1,1,'Secret Panel','','',false), +(1,1,'Wakeup','','',true), +(1,1,'Incinerator','','',false), -- 2 -('Laser Intro',0,-1,false,false), -('Laser Stairs',0,-1,false,false), -('Dual Lasers',2,-1,false,false), -('Laser Over Goo',0,-1,false,false), -('Catapult Intro',0,-1,false,true), -('Trust Fling',2,-1,false,false), -('Pit Flings',0,-1,false,false), -('Fizzler Intro',2,-1,false,false), +(1,2,'Laser Intro','','',false), +(1,2,'Laser Stairs','','',false), +(1,2,'Dual Lasers','','',false), +(1,2,'Laser Over Goo','','',false), +(1,2,'Catapult Intro','','',true), +(1,2,'Trust Fling','','',false), +(1,2,'Pit Flings','','',false), +(1,2,'Fizzler Intro','','',false), -- 3 -('Ceiling Catapult',0,-1,false,false), -('Ricochet',2,-1,false,false), -('Bridge Intro',2,-1,false,false), -('Bridge The Gap',0,-1,false,false), -('Turret Intro',0,-1,false,false), -('Laser Relays',0,-1,false,false), -('Turret Blocker',0,-1,false,false), -('Laser vs Turret',0,-1,false,false), -('Pull The Rug',0,-1,false,false), +(1,3,'Ceiling Catapult','','',false), +(1,3,'Ricochet','','',false), +(1,3,'Bridge Intro','','',false), +(1,3,'Bridge The Gap','','',false), +(1,3,'Turret Intro','','',false), +(1,3,'Laser Relays','','',false), +(1,3,'Turret Blocker','','',false), +(1,3,'Laser vs Turret','','',false), +(1,3,'Pull The Rug','','',false), -- 4 -('Column Blocker',0,-1,false,false), -('Laser Chaining',0,-1,false,false), -('Triple Laser',0,-1,false,false), -('Jail Break',2,-1,false,false), -('Escape',0,-1,false,false), +(1,4,'Column Blocker','','',false), +(1,4,'Laser Chaining','','',false), +(1,4,'Triple Laser','','',false), +(1,4,'Jail Break','','',false), +(1,4,'Escape','','',false), -- 5 -('Turret Factory',5,-1,false,false), -('Turret Sabotage',4,-1,false,false), -('Neurotoxin Sabotage',0,-1,false,false), -('Core',2,-1,false,false), +(1,5,'Turret Factory','','',false), +(1,5,'Turret Sabotage','','',false), +(1,5,'Neurotoxin Sabotage','','',false), +(1,5,'Core','','',false), -- 6 -('Underground',4,-1,false,false), -('Cave Johnson',4,-1,false,false), -('Repulsion Intro',0,-1,false,false), -('Bomb Flings',3,-1,false,false), -('Crazy Box',0,-1,false,false), -('PotatOS',5,-1,false,false), +(1,6,'Underground','','',false), +(1,6,'Cave Johnson','','',false), +(1,6,'Repulsion Intro','','',false), +(1,6,'Bomb Flings','','',false), +(1,6,'Crazy Box','','',false), +(1,6,'PotatOS','','',false), -- 7 -('Propulsion Intro',2,-1,false,false), -('Propulsion Flings',0,-1,false,false), -('Conversion Intro',9,-1,false,false), -('Three Gels',4,-1,false,false), +(1,7,'Propulsion Intro','','',false), +(1,7,'Propulsion Flings','','',false), +(1,7,'Conversion Intro','','',false), +(1,7,'Three Gels','','',false), -- 8 -('Test',2,-1,false,false), -('Funnel Intro',0,-1,false,false), -('Ceiling Button',0,-1,false,false), -('Wall Button',0,-1,false,false), -('Polarity',0,-1,false,false), -('Funnel Catch',2,-1,false,false), -('Stop The Box',0,-1,false,false), -('Laser Catapult',0,-1,false,false), -('Laser Platform',3,-1,false,false), -('Propulsion Catch',0,-1,false,false), -('Repulsion Polarity',2,-1,false,false), +(1,8,'Test','','',false), +(1,8,'Funnel Intro','','',false), +(1,8,'Ceiling Button','','',false), +(1,8,'Wall Button','','',false), +(1,8,'Polarity','','',false), +(1,8,'Funnel Catch','','',false), +(1,8,'Stop The Box','','',false), +(1,8,'Laser Catapult','','',false), +(1,8,'Laser Platform','','',false), +(1,8,'Propulsion Catch','','',false), +(1,8,'Repulsion Polarity','','',false), -- 9 -('Finale 1',0,-1,false,false), -('Finale 2',2,-1,false,false), -('Finale 3',6,-1,false,false), -('Finale 4',6,-1,false,false), --- Coop +(1,9,'Finale 1','','',false), +(1,9,'Finale 2','','',false), +(1,9,'Finale 3','','',false), +(1,9,'Finale 4','','',false), +-- Portal 2 Cooperative +-- 0 +(2,10,'Calibration','','',false), +(2,10,'Hub','','',true), -- 1 -('Calibration',4,-1,true,false), -('Hub',0,-1,false,true), -('Doors',0,-1,true,false), -('Buttons',2,-1,true,false), -('Lasers',3,-1,true,false), -('Rat Maze',2,-1,true,false), -('Laser Crusher',0,-1,true,false), -('Behind The Scenes',0,-1,true,false), +(2,11,'Doors','','',false), +(2,11,'Buttons','','',false), +(2,11,'Lasers','','',false), +(2,11,'Rat Maze','','',false), +(2,11,'Laser Crusher','','',false), +(2,11,'Behind The Scenes','','',false), -- 2 -('Flings',4,-1,true,false), -('Infinifling',0,-1,true,false), -('Team Retrieval',0,-1,true,false), -('Vertical Flings',2,-1,true,false), -('Catapults',4,-1,true,false), -('Multifling',2,-1,true,false), -('Fling Crushers',0,-1,true,false), -('Industrial Fan',0,-1,true,false), +(2,12,'Flings','','',false), +(2,12,'Infinifling','','',false), +(2,12,'Team Retrieval','','',false), +(2,12,'Vertical Flings','','',false), +(2,12,'Catapults','','',false), +(2,12,'Multifling','','',false), +(2,12,'Fling Crushers','','',false), +(2,12,'Industrial Fan','','',false), -- 3 -('Cooperative Bridges',3,-1,true,false), -('Bridge Swap',2,-1,true,false), -('Fling Block',2,-1,true,false), -('Catapult Block',4,-1,true,false), -('Bridge Fling',4,-1,true,false), -('Turret Walls',4,-1,true,false), -('Turret Assasin',0,-1,true,false), -('Bridge Testing',0,-1,true,false), +(2,13,'Cooperative Bridges','','',false), +(2,13,'Bridge Swap','','',false), +(2,13,'Fling Block','','',false), +(2,13,'Catapult Block','','',false), +(2,13,'Bridge Fling','','',false), +(2,13,'Turret Walls','','',false), +(2,13,'Turret Assasin','','',false), +(2,13,'Bridge Testing','','',false), -- 4 -('Cooperative Funnels',0,-1,true,false), -('Funnel Drill',0,-1,true,false), -('Funnel Catch',0,-1,true,false), -('Funnel Laser',0,-1,true,false), -('Cooperative Polarity',0,-1,true,false), -('Funnel Hop',0,-1,true,false), -('Advanced Polarity',0,-1,true,false), -('Funnel Maze',0,-1,true,false), -('Turret Warehouse',0,-1,true,false), +(2,14,'Cooperative Funnels','','',false), +(2,14,'Funnel Drill','','',false), +(2,14,'Funnel Catch','','',false), +(2,14,'Funnel Laser','','',false), +(2,14,'Cooperative Polarity','','',false), +(2,14,'Funnel Hop','','',false), +(2,14,'Advanced Polarity','','',false), +(2,14,'Funnel Maze','','',false), +(2,14,'Turret Warehouse','','',false), -- 5 -('Repulsion Jumps',0,-1,true,false), -('Double Bounce',2,-1,true,false), -('Bridge Repulsion',2,-1,true,false), -('Wall Repulsion',2,-1,true,false), -('Propulsion Crushers',0,-1,true,false), -('Turret Ninja',0,-1,true,false), -('Propulsion Retrieval',0,-1,true,false), -('Vault Entrance',0,-1,true,false), +(2,15,'Repulsion Jumps','','',false), +(2,15,'Double Bounce','','',false), +(2,15,'Bridge Repulsion','','',false), +(2,15,'Wall Repulsion','','',false), +(2,15,'Propulsion Crushers','','',false), +(2,15,'Turret Ninja','','',false), +(2,15,'Propulsion Retrieval','','',false), +(2,15,'Vault Entrance','','',false), -- 6 -('Seperation',0,-1,true,false), -('Triple Axis',0,-1,true,false), -('Catapult Catch',0,-1,true,false), -('Bridge Gels',2,-1,true,false), -('Maintenance',0,-1,true,false), -('Bridge Catch',0,-1,true,false), -('Double Lift',0,-1,true,false), -('Gel Maze',0,-1,true,false), -('Crazier Box',0,-1,true,false); \ No newline at end of file +(2,16,'Seperation','','',false), +(2,16,'Triple Axis','','',false), +(2,16,'Catapult Catch','','',false), +(2,16,'Bridge Gels','','',false), +(2,16,'Maintenance','','',false), +(2,16,'Bridge Catch','','',false), +(2,16,'Double Lift','','',false), +(2,16,'Gel Maze','','',false), +(2,16,'Crazier Box','','',false); \ No newline at end of file -- cgit v1.2.3