From 40537dace937fc96c851c56f0cb629f36dddfd03 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Tue, 20 Jun 2023 19:55:31 +0300
Subject: feat: improved map summary response, all routes init (#43, #46)

---
 backend/database/route.sql | 279 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 279 insertions(+)
 create mode 100644 backend/database/route.sql

(limited to 'backend/database/route.sql')

diff --git a/backend/database/route.sql b/backend/database/route.sql
new file mode 100644
index 0000000..6f090c7
--- /dev/null
+++ b/backend/database/route.sql
@@ -0,0 +1,279 @@
+INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALUES
+-- Portal 2 Singleplayer
+-- 1
+(3,1,3,'',''),
+(3,1,1,'',''),
+(3,1,0,'',''),
+(4,1,1,'',''),
+(4,1,0,'',''),
+(5,1,2,'',''),
+(5,1,1,'',''),
+(6,1,4,'',''),
+(6,1,3,'',''),
+(6,1,2,'',''),
+(9,1,4,'',''),
+(9,1,3,'',''),
+(9,1,2,'',''),
+(9,1,0,'',''),
+-- 2
+(10,1,2,'',''),
+(10,1,0,'',''),
+(11,1,2,'',''),
+(11,1,0,'',''),
+(12,1,2,'',''),
+(13,1,3,'',''),
+(13,1,2,'',''),
+(13,1,0,'',''),
+(15,1,2,'',''),
+(16,1,2,'',''),
+(16,1,0,'',''),
+(17,1,0,'',''),
+(17,1,0,'',''),
+-- 3
+(18,1,5,'',''),
+(18,1,4,'',''),
+(18,1,3,'',''),
+(18,1,2,'',''),
+(18,1,0,'',''),
+(19,1,2,'',''),
+(20,1,5,'',''),
+(20,1,4,'',''),
+(20,1,3,'',''),
+(20,1,2,'',''),
+(21,1,4,'',''),
+(21,1,2,'',''),
+(21,1,0,'',''),
+(22,1,0,'',''),
+(23,1,2,'',''),
+(23,1,0,'',''),
+(24,1,0,'',''),
+(25,1,0,'',''),
+(26,1,3,'',''),
+(26,1,2,'',''),
+(26,1,0,'',''),
+-- 4
+(27,1,2,'',''),
+(27,1,0,'',''),
+(28,1,7,'',''),
+(28,1,2,'',''),
+(28,1,0,'',''),
+(29,1,0,'',''),
+(30,1,2,'',''),
+(31,1,0,'',''),
+-- 5
+(32,1,6,'',''),
+(32,1,5,'',''),
+(33,1,7,'',''),
+(33,1,5,'',''),
+(33,1,4,'',''),
+(34,1,3,'',''),
+(34,1,2,'',''),
+(34,1,0,'',''),
+(35,1,2,'',''),
+-- 6
+(36,1,6,'',''),
+(36,1,5,'',''),
+(36,1,4,'',''),
+(36,1,2,'',''),
+(37,1,7,'',''),
+(37,1,6,'',''),
+(37,1,5,'',''),
+(37,1,4,'',''),
+(38,1,2,'',''),
+(38,1,0,'',''),
+(39,1,6,'',''),
+(39,1,5,'',''),
+(39,1,4,'',''),
+(39,1,3,'',''),
+(40,1,7,'',''),
+(40,1,6,'',''),
+(40,1,4,'',''),
+(40,1,3,'',''),
+(40,1,2,'',''),
+(40,1,0,'',''),
+(41,1,7,'',''),
+(41,1,6,'',''),
+(41,1,5,'',''),
+-- 7
+(42,1,4,'',''),
+(42,1,2,'',''),
+(43,1,5,'',''),
+(43,1,2,'',''),
+(43,1,0,'',''),
+(44,1,18,'',''),
+(44,1,13,'',''),
+(44,1,12,'',''),
+(44,1,11,'',''),
+(44,1,9,'',''),
+(45,1,23,'',''),
+(45,1,22,'',''),
+(45,1,17,'',''),
+(45,1,16,'',''),
+(45,1,15,'',''),
+(45,1,12,'',''),
+(45,1,10,'',''),
+(45,1,7,'',''),
+(45,1,4,'',''),
+-- 8
+(46,1,6,'',''),
+(46,1,2,'',''),
+(47,1,2,'',''),
+(47,1,0,'',''),
+(48,1,5,'',''),
+(48,1,2,'',''),
+(48,1,0,'',''),
+(49,1,4,'',''),
+(49,1,2,'',''),
+(49,1,0,'',''),
+(50,1,4,'',''),
+(50,1,2,'',''),
+(50,1,0,'',''),
+(51,1,3,'',''),
+(51,1,2,'',''),
+(52,1,0,'',''),
+(53,1,9,'',''),
+(53,1,2,'',''),
+(53,1,0,'',''),
+(54,1,7,'',''),
+(54,1,6,'',''),
+(54,1,5,'',''),
+(54,1,4,'',''),
+(54,1,3,'',''),
+(55,1,7,'',''),
+(55,1,3,'',''),
+(55,1,2,'',''),
+(55,1,0,'',''),
+(56,1,9,'',''),
+(56,1,5,'',''),
+(56,1,4,'',''),
+(56,1,2,'',''),
+-- 9
+(57,1,7,'',''),
+(57,1,5,'',''),
+(57,1,0,'',''),
+(58,1,2,'',''),
+(59,1,7,'',''),
+(59,1,6,'',''),
+(60,1,7,'',''),
+(60,1,6,'',''),
+-- Portal 2 Cooperative
+-- 1
+(63,1,0,'',''),
+(64,1,3,'',''),
+(64,1,2,'',''),
+(65,1,4,'',''),
+(65,1,3,'',''),
+(66,1,3,'',''),
+(66,1,2,'',''),
+(67,1,0,'',''),
+(68,1,0,'',''),
+-- 2
+(69,1,4,'',''),
+(70,1,6,'',''),
+(70,1,4,'',''),
+(70,1,2,'',''),
+(70,1,0,'',''),
+(71,1,3,'',''),
+(71,1,0,'',''),
+(72,1,4,'',''),
+(72,1,2,'',''),
+(73,1,9,'',''),
+(73,1,8,'',''),
+(73,1,7,'',''),
+(73,1,6,'',''),
+(73,1,4,'',''),
+(74,1,5,'',''),
+(74,1,7,'',''),
+(74,1,3,'',''),
+(74,1,2,'',''),
+(75,1,5,'',''),
+(75,1,4,'',''),
+(75,1,2,'',''),
+(75,1,0,'',''),
+(76,1,3,'',''),
+(76,1,0,'',''),
+-- 3
+(77,1,3,'',''),
+(78,1,4,'',''),
+(78,1,3,'',''),
+(78,1,2,'',''),
+(79,1,5,'',''),
+(79,1,4,'',''),
+(79,1,2,'',''),
+(80,1,5,'',''),
+(80,1,4,'',''),
+(81,1,7,'',''),
+(81,1,6,'',''),
+(81,1,5,'',''),
+(81,1,4,'',''),
+(82,1,4,'',''),
+(83,1,5,'',''),
+(83,1,2,'',''),
+(83,1,0,'',''),
+(84,1,6,'',''),
+(84,1,4,'',''),
+(84,1,2,'',''),
+(84,1,0,'',''),
+-- 4
+(85,1,3,'',''),
+(85,1,0,'',''),
+(86,1,3,'',''),
+(86,1,0,'',''),
+(87,1,3,'',''),
+(87,1,2,'',''),
+(87,1,0,'',''),
+(88,1,4,'',''),
+(88,1,0,'',''),
+(89,1,0,'',''),
+(90,1,4,'',''),
+(90,1,2,'',''),
+(90,1,0,'',''),
+(91,1,2,'',''),
+(91,1,0,'',''),
+(92,1,5,'',''),
+(92,1,4,'',''),
+(92,1,2,'',''),
+(92,1,0,'',''),
+(93,1,2,'',''),
+(93,1,0,'',''),
+-- 5
+(94,1,2,'',''),
+(94,1,0,'',''),
+(95,1,2,'',''),
+(96,1,4,'',''),
+(96,1,2,'',''),
+(97,1,7,'',''),
+(97,1,4,'',''),
+(97,1,2,'',''),
+(98,1,0,'',''),
+(99,1,3,'',''),
+(99,1,2,'',''),
+(99,1,0,'',''),
+(100,1,0,'',''),
+(101,1,2,'',''),
+(101,1,0,'',''),
+-- 6
+(102,1,3,'',''),
+(102,1,2,'',''),
+(102,1,0,'',''),
+(103,1,0,'',''),
+(104,1,0,'',''),
+(105,1,8,'',''),
+(105,1,5,'',''),
+(105,1,4,'',''),
+(105,1,3,'',''),
+(105,1,2,'',''),
+(106,1,4,'',''),
+(106,1,3,'',''),
+(106,1,0,'',''),
+(107,1,2,'',''),
+(107,1,0,'',''),
+(108,1,0,'',''),
+(109,1,5,'',''),
+(109,1,0,'',''),
+(110,1,15,'',''),
+(110,1,6,'',''),
+(110,1,5,'',''),
+(110,1,4,'',''),
+(110,1,2,'',''),
+(110,1,0,'','');
\ No newline at end of file
-- 
cgit v1.2.3


From cae45e130891cf97f93d743ace381eb8209df2a8 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Wed, 12 Jul 2023 09:56:11 +0300
Subject: fix: unique relation for map history and routes

Former-commit-id: f3ea2ce6784973385d63980b6680294bbcee0188
---
 backend/database/history.sql | 2 +-
 backend/database/init.sql    | 6 ++++--
 backend/database/route.sql   | 2 +-
 3 files changed, 6 insertions(+), 4 deletions(-)

(limited to 'backend/database/route.sql')

diff --git a/backend/database/history.sql b/backend/database/history.sql
index d7c4999..320d72f 100644
--- a/backend/database/history.sql
+++ b/backend/database/history.sql
@@ -27,7 +27,7 @@ INSERT INTO map_history(map_id,category_id,user_name,score_count,record_date) VA
 (15,1,'Tyronis',2,'2011-10-05 00:00:00'),
 (16,1,'LookLikeAKango',2,'2011-10-05 00:00:00'),
 (16,1,'jyjey',0,'2012-08-25 00:00:00'),
-(17,1,'rocoty',0,'2011-10-05 00:00:00'),
+(17,1,'rocoty',2,'2011-10-05 00:00:00'),
 (17,1,'Nidboj132',0,'2023-02-05 00:00:00'),
 -- 3
 (18,1,'The Last Tofus',5,'2011-05-08 00:00:00'),
diff --git a/backend/database/init.sql b/backend/database/init.sql
index c7d098c..196cb71 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -49,7 +49,8 @@ CREATE TABLE map_routes (
   showcase TEXT NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
-  FOREIGN KEY (category_id) REFERENCES categories(id)
+  FOREIGN KEY (category_id) REFERENCES categories(id),
+  UNIQUE (map_id, category_id, score_count)
 );
 
 CREATE TABLE map_history (
@@ -61,7 +62,8 @@ CREATE TABLE map_history (
   record_date TIMESTAMP NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (category_id) REFERENCES categories(id),
-  FOREIGN KEY (map_id) REFERENCES maps(id)
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  UNIQUE (map_id, category_id, score_count)
 );
 
 CREATE TABLE map_ratings (
diff --git a/backend/database/route.sql b/backend/database/route.sql
index 6f090c7..f26180e 100644
--- a/backend/database/route.sql
+++ b/backend/database/route.sql
@@ -27,7 +27,7 @@ INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALU
 (15,1,2,'',''),
 (16,1,2,'',''),
 (16,1,0,'',''),
-(17,1,0,'',''),
+(17,1,2,'',''),
 (17,1,0,'',''),
 -- 3
 (18,1,5,'',''),
-- 
cgit v1.2.3