Re: Displaying chat by punished users only to themselves (db fiddle attached)
Thank you, that was it!
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Thursday, May 5, 2022, Alexander Farber wrote: > Good evening, I still have a problem with my JOIN expression - > when I add more games, then messages from other games are displayed: > > https://dbfiddle.uk/?rdbms=postgres_14= > e2ff211f59090d1eeab879498148f907 > > CREATE OR REPLACE FUNCTION words_get_chat( > in_gid integer, > in_uid integer > ) RETURNS TABLE ( > out_mine integer, > out_game text, > out_msg text > ) AS > $func$ > SELECT > CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, > 'game #' || c.gid, > c.msg > FROMwords_chat c > JOINwords_games g USING (gid) > JOINwords_users opponent ON (opponent.uid IN (g.player1, > g.player2) AND opponent.uid <> in_uid) > WHERE c.gid = in_gid > -- always show myself my own chat messages > AND c.uid = in_uid > -- otherwise only show messages by not muted opponents > OR NOT opponent.muted > ORDER BY c.created ASC; > > $func$ LANGUAGE sql; > > I have tried making the JOIN words_users opponent even more restrictive > with: > > JOINwords_users opponent ON (opponent.uid IN (g.player1, > g.player2) AND in_uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) > > but still messages from the game #20 are displayed, even though I pass > in_gid = 10 > You want: gid and (uid or muted); what you have is: (gid and uid) or muted; based upon operator precedence. David J.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Good evening, I still have a problem with my JOIN expression - when I add more games, then messages from other games are displayed: https://dbfiddle.uk/?rdbms=postgres_14=e2ff211f59090d1eeab879498148f907 CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer, in_uid integer ) RETURNS TABLE ( out_mine integer, out_game text, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, 'game #' || c.gid, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; I have tried making the JOIN words_users opponent even more restrictive with: JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND in_uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) but still messages from the game #20 are displayed, even though I pass in_gid = 10 Best regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Good morning, this is a very insightful comment (among many) by you, David - On Wed, May 4, 2022 at 5:40 PM David G. Johnston wrote: > Well, that is basically why I was going on about the oddity of having > social be a part of the main query. Personally I would write it as > "myself.uid = in_uid", but you don't have an in_uid to reference. Decide > how you want to do something equivalent. > > so I will rewrite the stored functions in my game to be like that, to separate auth from functionality - https://dbfiddle.uk/?rdbms=postgres_14=dbdf1a6b82f7232be45e23b8139a8f0e CREATE OR REPLACE FUNCTION words_get_uid( in_social integer, in_sidtext ) RETURNS integer AS $func$ SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid; $func$ LANGUAGE sql IMMUTABLE; CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer, in_uid integer ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; SELECT words_get_chat(10, words_get_uid(100, 'abc')) AS nice_user; SELECT words_get_chat(10, words_get_uid(200, 'def')) AS muted_user; Thanks Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
David, thanks but what do you mean by the last comment - On Wed, May 4, 2022 at 7:44 PM David G. Johnston wrote: > Using (SELECT uid FROM myself) provides the same result without the > from/join reference; the usage in the case and the where clause could be > rewritten to use opponent.uid so myself.uid only appears once. > > I have applied your first 2 comments in https://dbfiddle.uk/?rdbms=postgres_14=c3982c2b5e71369f3c92ee0c06dc29bf WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINmyself ON TRUE JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; but where to put the (SELECT uid FROM myself), I do not understand?
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 10:23 AM Alexander Farber wrote: > Is that the right way to do it? > > > https://dbfiddle.uk/?rdbms=postgres_14=7bd74243397da61ddc4c216ad919c7cc > > WITH myself AS ( > SELECT uid > FROM words_social > WHERE social = in_social > AND sid = in_sid > LIMIT 1 > ) > SELECT > CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, > c.msg > FROMmyself > JOINwords_chat c ON TRUE > JOINwords_games g USING (gid) > JOINwords_users opponent ON (opponent.uid IN (g.player1, > g.player2) AND opponent.uid <> myself.uid) > WHERE c.gid = in_gid > -- always show myself my own chat messages > AND c.uid = myself.uid > -- otherwise only show messages by not muted opponents > OR NOT opponent.muted > ORDER BY c.created ASC; > Assuming it provides the correct result, yes. It's a bit odd to see "from myself" - listing words_chat first makes much more sense. You've defined (social,sid) as a primary key, your LIMIT 1 just makes you look like you don't know or trust that and leaves the reader wondering. Using (SELECT uid FROM myself) provides the same result without the from/join reference; the usage in the case and the where clause could be rewritten to use opponent.uid so myself.uid only appears once. David J.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Is that the right way to do it? https://dbfiddle.uk/?rdbms=postgres_14=7bd74243397da61ddc4c216ad919c7cc WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMmyself JOINwords_chat c ON TRUE JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC;
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I think I am very close with the following CTE, but do not understand how to bring it into the main SELECT query: https://dbfiddle.uk/?rdbms=postgres_14=ee264dc98b44dee75aa4523164a327b3 WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.CREATED ASC; The error message is: ERROR: missing FROM-clause entry for table "myself" LINE 64: ...uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid... ^
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 9:12 AM Alexander Farber wrote: > I have tried CROSS JOIN and CASE WHEN (why be greedy, right?): > > > https://dbfiddle.uk/?rdbms=postgres_14=43a33374d15a9330145007702138822c > > WITH myself AS ( > SELECT uid > FROM words_social > WHERE social = in_social > AND sid = in_sid > LIMIT 1 > ), > opponent AS ( > SELECT CASE WHEN player1 = myself.uid THEN player2 ELSE > player1 END > FROM words_games > WHERE gid = in_gid > ) > SELECT > CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, > c.msg > FROM myself CROSS JOIN opponent > WHERE (c.uid = myself.uid OR NOT opponent.muted) > ORDER BY c.CREATED ASC; > > but the error is: > > ERROR: missing FROM-clause entry for table "myself" > LINE 60: SELECT CASE WHEN player1 = myself.uid THEN play... > > What exactly are you trying to do in the "opponent" cte - and why do you think the myself cte is visible to it? David J.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I have tried CROSS JOIN and CASE WHEN (why be greedy, right?): https://dbfiddle.uk/?rdbms=postgres_14=43a33374d15a9330145007702138822c WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ), opponent AS ( SELECT CASE WHEN player1 = myself.uid THEN player2 ELSE player1 END FROM words_games WHERE gid = in_gid ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROM myself CROSS JOIN opponent WHERE (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; but the error is: ERROR: missing FROM-clause entry for table "myself" LINE 60: SELECT CASE WHEN player1 = myself.uid THEN play... ^
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 8:53 AM Alexander Farber wrote: > > JOINcte > WHERE c.gid= in_gid > AND (c.uid = myself.uid OR NOT opponent.muted) > ORDER BY c.CREATED ASC; > > ERROR: syntax error at or near "WHERE" > LINE 67: WHERE c.gid= in_gid > ^ > > And if I remove the "JOIN cte" line, then the error is: > > ERROR: missing FROM-clause entry for table "cte" > LINE 64: ...elf.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) > Try "CROSS JOIN cte" - that variant doesn't require a join condition. >
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I try with a CTE but cannot figure the syntax: https://dbfiddle.uk/?rdbms=postgres_14=acd6d06a7ea2efc73a0771530832d77e WITH cte AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ) SELECT CASE WHEN c.uid = cte.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> cte.uid) JOINcte WHERE c.gid= in_gid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; ERROR: syntax error at or near "WHERE" LINE 67: WHERE c.gid= in_gid ^ And if I remove the "JOIN cte" line, then the error is: ERROR: missing FROM-clause entry for table "cte" LINE 64: ...elf.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) ^ >
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 8:36 AM Alexander Farber wrote: > David, I try then the following - > > On Wed, May 4, 2022 at 5:28 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> You missed quoting the part where I describe the on clauses you need to >> distinguish between "them" and "me" >> >> Me: u.uid in (player...) and (s.uid = u.uid) >> Them: u.uid in (player...) and (s.uid <> u.uid) >> >> > > https://dbfiddle.uk/?rdbms=postgres_14=048b9b9c7c55256c1a478d7c90cd2667 > > SELECT > CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, > c.msg > FROMwords_chat c > JOINwords_games g USING (gid) > JOINwords_users myself ON (myself.uid IN (g.player1, > g.player2) AND myself.uid = s.uid) > JOINwords_users opponent ON (opponent.uid IN (g.player1, > g.player2) AND myself.uid <> s.uid) > JOINwords_social s ON (s.uid = myself.uid) > WHERE c.gid= in_gid > AND s.social = in_social > AND s.sid= in_sid > AND (c.uid = myself.uid OR NOT opponent.muted) > > ORDER BY c.CREATED ASC; > > And get the syntax error which don't quite understand: > > ERROR: missing FROM-clause entry for table "s" > LINE 57: ...yself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) > ^ > > Probably because "myself" needs "s" and vice versa? > > Well, that is basically why I was going on about the oddity of having social be a part of the main query. Personally I would write it as "myself.uid = in_uid", but you don't have an in_uid to reference. Decide how you want to do something equivalent. David J.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I am probably needing LEFT JOIN LATERAL here (and am completely lost)? Or to switch to CTE as you suggest
Re: Displaying chat by punished users only to themselves (db fiddle attached)
David, I try then the following - On Wed, May 4, 2022 at 5:28 PM David G. Johnston wrote: > You missed quoting the part where I describe the on clauses you need to > distinguish between "them" and "me" > > Me: u.uid in (player...) and (s.uid = u.uid) > Them: u.uid in (player...) and (s.uid <> u.uid) > > https://dbfiddle.uk/?rdbms=postgres_14=048b9b9c7c55256c1a478d7c90cd2667 SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> s.uid) JOINwords_social s ON (s.uid = myself.uid) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; And get the syntax error which don't quite understand: ERROR: missing FROM-clause entry for table "s" LINE 57: ...yself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) ^ Probably because "myself" needs "s" and vice versa?
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 8:21 AM Alexander Farber wrote: > David, I am trying your suggestion: > > On Wed, May 4, 2022 at 4:27 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> Assuming the base query is capable of returning all related chat messages >> for both users (I'd probably place that portion into a CTE) the rows you >> want to filter out are those whose c.uid is not my own, but only if their >> muted property is true. It makes it easier to understand if you join >> words_users twice, defining one as "them" and one as "me". Then you can >> say something like: WHERE (c.uid = me.uid) OR NOT(them.muted) >> >> > You missed quoting the part where I describe the on clauses you need to distinguish between "them" and "me" Me: u.uid in (player...) and (s.uid = u.uid) Them: u.uid in (player...) and (s.uid <> u.uid) In particular, the IN expression causes two rows to be returned, one for them and one for me - but for each join you only want one or the other. David J.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
David, I am trying your suggestion: On Wed, May 4, 2022 at 4:27 PM David G. Johnston wrote: > Assuming the base query is capable of returning all related chat messages > for both users (I'd probably place that portion into a CTE) the rows you > want to filter out are those whose c.uid is not my own, but only if their > muted property is true. It makes it easier to understand if you join > words_users twice, defining one as "them" and one as "me". Then you can > say something like: WHERE (c.uid = me.uid) OR NOT(them.muted) > > like this: https://dbfiddle.uk/?rdbms=postgres_14=4ab6a09cddae26a11140202fdc41cf5c CREATE OR REPLACE FUNCTION words_get_chat( in_gidinteger, in_social integer, in_sidtext ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2)) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2)) JOINwords_social s ON (s.uid = myself.uid) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; $func$ LANGUAGE sql;
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 7:40 AM Alexander Farber wrote: > > The "social dynamic" is needed, because I cannot pass real user id (via > HTTP) to SQL queries. > > Instead I pass social network type "social" (like 100 is facebook, 200 is > twitter) and the social network id "sid" returned by that network. This way > noone can read chats by other users, by just replacing the numeric "uid"... > > So I try your suggestion with: > > > https://dbfiddle.uk/?rdbms=postgres_14=48d4bef569d966021e94c72f86d9fce5 > > CREATE OR REPLACE FUNCTION words_get_chat( > in_gidinteger, > in_social integer, > in_sidtext > ) > I suppose it depends on how you call this function - I would personally separate external authentication and identity from internal business logic. i.e., look up the uid given the social information in one place and then write queries like this one against u_id. AFAICS, the social table provides no benefit to this query that cannot be gotten via uid. It serves to map social info to uid. If you must keep that logic here I strongly suggest you place it into a CTE to call out its purpose in mapping social to user for purposes of figuring out who "me" is. "them" is just going to be a join against user since you won't have any relevant social information for them anyway. > JOINwords_users u1 ON (u1.uid = g.player1) > JOINwords_users u2 ON (u2.uid = g.player2) > JOINwords_social s ON (s.uid IN (u1.uid, u2.uid)) > That wasn't my suggestion - you still don't know whether u1 is "me" or "them", you've just put player1 into the u1 slot. > ...but how to bring the u1.muted or u2.muted there? > > You can always write something like: CASE WHEN ... THEN u1.muted ELSE u2.muted END if you don't want to pre-define "me" and "them" David J.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
My real SQL function has one more param, an "auth" string generated by my game, which complements the social network id "sid". I have just omitted it in my test case. >
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Hi Ron, On Wed, May 4, 2022 at 4:56 PM Ron wrote: > > How do other web sites know to present only "my" data, even though they > don't encode "my" user id in the URL? > > that is the usual pattern with OAuth provided by: Facebook, Google, Amazon, Huawei, etc... After you auth with them in a game like mine, they give you a social network id, which is a string. Noone else gets that str. And then I (as game dev) use that str to id the user and when the user is visiting my for the 1st time, I give him a numeric id in my game. And an "auth" str generated by my game. Etc... it works ok. Regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On 5/4/22 09:40, Alexander Farber wrote: Thank you for replying, David! The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries. How do other web sites know to present only "my" data, even though they don't encode "my" user id in the URL? -- Angular momentum makes the world go 'round.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Thank you for replying, David! The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries. Instead I pass social network type "social" (like 100 is facebook, 200 is twitter) and the social network id "sid" returned by that network. This way noone can read chats by other users, by just replacing the numeric "uid"... So I try your suggestion with: https://dbfiddle.uk/?rdbms=postgres_14=48d4bef569d966021e94c72f86d9fce5 CREATE OR REPLACE FUNCTION words_get_chat( in_gidinteger, in_social integer, in_sidtext ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users u1 ON (u1.uid = g.player1) JOINwords_users u2 ON (u2.uid = g.player2) JOINwords_social s ON (s.uid IN (u1.uid, u2.uid)) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid ORDER BY c.CREATED ASC; $func$ LANGUAGE sql; ...but how to bring the u1.muted or u2.muted there? Best regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 5:48 AM Alexander Farber wrote: > > CREATE OR REPLACE FUNCTION words_get_chat( > in_gidinteger, > in_social integer, > in_sidtext > ) RETURNS TABLE ( > out_mine integer, > out_msg text > ) AS > $func$ > SELECT > CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, > c.msg > FROMwords_chat c > JOINwords_games g USING (gid) > JOINwords_users u ON (u.uid IN (g.player1, g.player2) > -- The condition below is broken if both users are not > muted > AND (u.muted OR (c.uid = u.uid AND NOT u.muted))) > JOINwords_social s ON (s.uid = u.uid) > WHERE c.gid= in_gid > AND s.social = in_social > AND s.sid= in_sid > ORDER BY c.CREATED ASC; > > $func$ LANGUAGE sql; > > For a chat of a bad and a nice user it seemingly works: > > SELECT words_get_chat(10, 100, 'abc') AS nice_user; > SELECT words_get_chat(10, 200, 'def') AS muted_user; > > But if you change both users to be not muted - it will break and they only > will see their own messages. > Optimize for performance second. I would move the test regarding muted to a where clause I'm not understanding how a given user can see anything but their own messages where you have the condition s.social = in_social. Assuming the base query is capable of returning all related chat messages for both users (I'd probably place that portion into a CTE) the rows you want to filter out are those whose c.uid is not my own, but only if their muted property is true. It makes it easier to understand if you join words_users twice, defining one as "them" and one as "me". Then you can say something like: WHERE (c.uid = me.uid) OR NOT(them.muted) Me: u.uid in (player...) and (s.uid = u.uid) Them: u.uid in (player...) and (s.uid <> u.uid) Hopefully you get the idea, your "social" dynamic makes this more challenging. If you can just pass "my uid" into the function then figuring out which uid is "me" and which is "not me" becomes quite a bit easier. David J.
Displaying chat by punished users only to themselves (db fiddle attached)
Hello,
I have developed a complete SQL fiddle for my question:
https://dbfiddle.uk/?rdbms=postgres_14=dcf063ba1615b392cc3cfa347a32c97b
The context is that I run an online game for two players using a PostgreSQL
14.2 backend.
I would like to make my game more friendly by hiding chat messages of
misbehaving users.
However, to prevent the punished users from noticing it and registering new
game accounts, I would like to still show them all messages :->
So here are the 4 tables used in my reduced test case:
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
muted BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE words_social (
sid text NOT NULL CHECK (sid ~ '\S'),
social integer NOT NULL CHECK (0 < social AND social <= 256),
given text NOT NULL CHECK (given ~ '\S'),
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL
CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE
);
CREATE TABLE words_chat (
cid BIGSERIAL PRIMARY KEY,
created timestamptz NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
msg textNOT NULL
);
Then I put 2 users into the game #10 and they start chatting:
-- create 2 users: one is ok, while the other is muted (punished)
INSERT INTO words_users (uid, muted) VALUES (1, false), (2, true);
INSERT INTO words_social (sid, social, given, uid) VALUES ('abc', 100,
'Nice user', 1), ('def', 200, 'Bad user', 2);
-- put these 2 users into a game #10
INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2);
-- both users in the game #10 start chatting
INSERT INTO words_chat (gid, uid, created, msg) VALUES
(10, 1, CURRENT_TIMESTAMP + INTERVAL '1 min', 'Hi how are you doing?'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '2 min', 'I am a nice user'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '3 min', 'F*** ***!!'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '4 min', 'I am a bad user'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '5 min','Are you there??');
Here is my custom stored function (in SQL, I would prefer not to switch to
PL/pgSQL):
CREATE OR REPLACE FUNCTION words_get_chat(
in_gidinteger,
in_social integer,
in_sidtext
) RETURNS TABLE (
out_mine integer,
out_msg text
) AS
$func$
SELECT
CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END,
c.msg
FROMwords_chat c
JOINwords_games g USING (gid)
JOINwords_users u ON (u.uid IN (g.player1, g.player2)
-- The condition below is broken if both users are not muted
AND (u.muted OR (c.uid = u.uid AND NOT u.muted)))
JOINwords_social s ON (s.uid = u.uid)
WHERE c.gid= in_gid
AND s.social = in_social
AND s.sid= in_sid
ORDER BY c.CREATED ASC;
$func$ LANGUAGE sql;
For a chat of a bad and a nice user it seemingly works:
SELECT words_get_chat(10, 100, 'abc') AS nice_user;
SELECT words_get_chat(10, 200, 'def') AS muted_user;
But if you change both users to be not muted - it will break and they only
will see their own messages.
I have tinkered a lot with my db fiddle... but still cannot figure it out
Thank you!
Alex
