From 47b5c03d286eb047b62f6d03dddf7d6bc6b3978e Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Fri, 22 Sep 2023 15:46:45 +0200
Subject: Avoid "natural" joins for future proofing schema changes.

---
 schema.sql | 12 ++++++------
 1 file changed, 6 insertions(+), 6 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index f38ba53..04c613b 100644
--- a/schema.sql
+++ b/schema.sql
@@ -86,7 +86,7 @@ create view user_profile_view as
 		user_id, name, mail, notify, ctime, atime, about, is_banned
 	from
 		users
-		natural left join user_last_seen
+		left join user_last_seen using(user_id)
 	;
 
 drop view if exists user_dynamic_view;
@@ -179,7 +179,7 @@ create view rating_view as
 		title_id, name, rating, count, last
 	from
 		ratings
-		natural join users
+		join users using(title_id, user_id)
 	order by
 		title_id,
 		rating desc
@@ -194,8 +194,8 @@ create view player_rating_view as
 		coalesce(rating, 1500) as rating,
 		coalesce(count, 0) as count
 	from players
-	natural join games
-	natural left join ratings
+	join games using(game_id)
+	left join ratings using(title_id, user_id)
 ;
 
 -- Friend and Block Lists --
@@ -393,7 +393,7 @@ create view game_chat_view as
 		game_id, chat_id, time, name, message
 	from
 		game_chat
-		natural join users
+		join users using(user_id)
 	;
 
 create table if not exists game_replay (
@@ -439,7 +439,7 @@ create view game_view as
 		user_count = join_count and join_count > 1 as is_opposed
 	from
 		games
-		natural join titles
+		join titles using(title_id)
 		join users as owner
 			on owner.user_id = games.owner_id
 	;
-- 
cgit v1.2.3

