From f972745ac092a1657d40d9fb01f7c68508a7c416 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Mon, 3 Oct 2022 16:30:13 +0200 Subject: Add contact list with friends and blacklisted users. --- schema.sql | 28 +++++++++++++++++++++++++++- 1 file changed, 27 insertions(+), 1 deletion(-) (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql index 0613da4..4fcb505 100644 --- a/schema.sql +++ b/schema.sql @@ -79,6 +79,31 @@ create view user_profile_view as natural left join user_last_seen ; +-- Friend and Block Lists -- + +create table if not exists contacts ( + me integer, + you integer, + relation integer, + primary key (me, you) +) without rowid; + +drop view if exists contact_view; +create view contact_view as + select + contacts.me, + users.user_id, + users.name, + user_last_seen.atime, + contacts.relation + from + contacts + left join users on contacts.you = users.user_id + left join user_last_seen on contacts.you = user_last_seen.user_id + order by + users.name +; + -- Messages -- create table if not exists messages ( @@ -229,7 +254,7 @@ create table if not exists game_replay ( game_id integer, role text, action text, - arguments text + arguments json -- numeric affinity is more compact for numbers ); create index if not exists game_replay_idx on game_replay(game_id); @@ -358,6 +383,7 @@ begin delete from tokens where user_id = old.user_id; delete from user_last_seen where user_id = old.user_id; delete from last_notified where user_id = old.user_id; + delete from contacts where me = old.user_id or you = old.user_id; delete from messages where from_id = old.user_id or to_id = old.user_id; delete from posts where author_id = old.user_id; delete from threads where author_id = old.user_id; -- cgit v1.2.3