Re: [GENERAL] Forward declaration of table

2016-08-25 Thread Alexander Farber 
Thank you and apologies for the misformated mail - I kept fixing it for
many minutes, but once I hit the "Send" button in Gmail it went south again.

Re: [GENERAL] Forward declaration of table

2016-08-24 Thread Adrian Klaver 

On 08/24/2016 01:27 PM, Alexander Farber wrote:

Hello again,

I have went the ALTER TABLE route to add my 2 "cyclic" FKs:

https://gist.github.com/afarber/c40b9fc5447335db7d24

And now I have these 2 tables in my 9.5.3 database:





Why aren't m.tiles and m.score returned please?


Reformatted your LEFT JOIN query(courtesy of http://sqlformat.darold.net/) :

SELECT
m.tiles,
m.score,
g.gid,
extract (
EPOCH
FROM
g.created ) ::INT AS created,
g.player1,
COALESCE (
g.player2,
0 ) AS player2,
COALESCE (
extract (
EPOCH
FROM
g.played1 ) ::INT,
0 ) AS played1,
COALESCE (
extract (
EPOCH
FROM
g.played2 ) ::INT,
0 ) AS played2,
array_to_string (
g.hand1,
'' ) AS hand1,
array_to_string (
g.hand2,
'' ) AS hand2,
g.bid
FROM
words_games g
LEFT JOIN words_moves m ON (
g.mid1 = m.mid
OR g.mid2 = m.mid )
WHERE
g.player1 = 1
OR g.player2 = 1;

Looking at your tables I would start with something like:

SELECT
wm.tiles, wm.score
FROM
word_games AS wg
JOIN
word_moves AS wm
ON
wg.gid = wm.gid
WHERE
(wg.player1 = 1
OR
wg.player2 = 1)



Regards
Alex



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Forward declaration of table

2016-08-24 Thread David G. Johnston 
On Wed, Aug 24, 2016 at 4:38 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>>
>> Why aren't m.tiles and m.score returned please?
>>
>>
> ​How about you output g.mid1 and g.mid2 in the first query and confirm
> that the rows being returned from words_games actually have a value in the
> set {2,3,4} in one of those columns.
>
>
​Also, NULL can be annoying here...might want to try IS DISTINCT FROM or
some similar.

Dave
​

Re: [GENERAL] Forward declaration of table

2016-08-24 Thread David G. Johnston 
On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> Why aren't m.tiles and m.score returned please?
>
>
​How about you output g.mid1 and g.mid2 in the first query and confirm that
the rows being returned from words_games actually have a value in the set
{2,3,4} in one of those columns.

David J.
​

Re: [GENERAL] Forward declaration of table

2016-08-24 Thread Alexander Farber 
Hello again,

I have went the ALTER TABLE route to add my 2 "cyclic" FKs:

https://gist.github.com/afarber/c40b9fc5447335db7d24

And now I have these 2 tables in my 9.5.3 database:

#TABLE words_moves;

 mid | action | gid | uid |played |

  tiles

   | score

-++-+-+---++---

   2 | play   |   1 |   1 | 2016-08-24 20:36:39.888224+02 | [{"col": 7,
"row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1,
"letter": "Е"}, {"col": 7, "row": 6, "value": 10, "letter": "Ф"}]

|13

   3 | play   |   2 |   1 | 2016-08-24 21:48:14.448361+02 | [{"col": 7,
"row": 12, "value": 5, "letter": "Ь"}, {"col": 7, "row": 10, "value": 1,
"letter": "Е"}, {"col": 7, "row": 9, "value": 1, "letter": "О"}, {"col": 7,
"row": 11, "value": 10, "letter": "Ш"}, {"col": 7, "row": 8, "value": 2,
"letter": "Р"}, {"col": 7, "row": 7, "value": 2, "letter": "П"}] |31

   4 | play   |   1 |   2 | 2016-08-24 21:50:55.231266+02 | [{"col": 8,
"row": 8, "value": 2, "letter": "Й"}, {"col": 8, "row": 7, "value": 1,
"letter": "А"}, {"col": 8, "row": 6, "value": 2, "letter": "Р"}, {"col": 8,
"row": 5, "value": 2, "letter": "С"}]
|33

(3 rows)

# SELECT
  gid,

EXTRACT(EPOCH FROM
created)::int AS created,
  player1,

COALESCE(player2, 0) AS player2,
  COALESCE(EXTRACT(EPOCH FROM
played1)::int, 0) AS played1,
COALESCE(EXTRACT(EPOCH FROM played2)::int, 0) AS played2,
  ARRAY_TO_STRING(hand1, '') AS hand1,
  ARRAY_TO_STRING(hand2,
'') AS hand2,
bid

FROM words_games
  WHERE player1 = 1

  OR player2 = 1;

 gid |  created   | player1 | player2 |  played1   |  played2   |  hand1  |
 hand2  | bid
-++-+-+++-+-+-
   2 | 1472068074 |   1 |   0 | 1472068094 |  0 | ЫТОВЕРЛ |
ЕНХЯЭАК |   1
   1 | 1472063658 |   1 |   2 | 1472063800 | 1472068255 | ВГЦЕСИУ |
ННДНСВТ |   1
(2 rows)

Then I am trying to perform the LEFT JOIN to return active games and recent
moves for player 1, but for some reason the first two columns are empty:

# SELECT
  m.tiles,

  m.score,

g.gid,

EXTRACT(EPOCH FROM g.created)::int AS created,
g.player1,

  COALESCE(g.player2, 0) AS player2,

COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played1,
  COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS
played2,   ARRAY_TO_STRING(g.hand1, '') AS
hand1,
  ARRAY_TO_STRING(g.hand2, '') AS hand2,
g.bid

FROM words_games g LEFT JOIN words_moves m
ON (g.mid1 = m.mid OR g.mid2 = m.mid)
  WHERE g.player1 = 1

OR g.player2 = 1;

 tiles | score | gid |  created   | player1 | player2 |  played1   |
 played2   |  hand1  |  hand2  | bid
---+---+-++-+-+++-+-+-
   |   |   2 | 1472068074 |   1 |   0 | 1472068094 |
   0 | ЫТОВЕРЛ | ЕНХЯЭАК |   1
   |   |   1 | 1472063658 |   1 |   2 | 1472063800 |
1472068255 | ВГЦЕСИУ | ННДНСВТ |   1
(2 rows)

Why aren't m.tiles and m.score returned please?

Regards
Alex

Re: [GENERAL] Forward declaration of table

2016-08-23 Thread Igor Neyman 
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 3:33 PM
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Forward declaration of table

Hi Igor,

On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman 
<iney...@perceptron.com<mailto:iney...@perceptron.com>> wrote:
mailto:pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org>]
 On Behalf Of Alexander Farber
https://gist.github.com/afarber/c40b9fc5447335db7d24

Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete 
cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:

gid integer REFERENCES WORD_GAMES ON DELETE CASCADE


you are correct, but I need to send most recent move in each game together with 
the other game data.

If I don't store the recent moves in mid1, mid2 then I'd have to retrieve them 
every time dynamically with
WITH last_moves AS (
  SELECT *
  FROM words_moves wm1
  WHERE
played = (SELECT max(played)
  FROM words_moves wm2
  WHERE wm1.gid = wm2.gid))
SELECT *
FROM words_games wg
  LEFT JOIN last_moves lm
ON (wg.gid = lm.gid)
WHERE
  player1 = 1 OR
  player2 = 1;

Regards
Alex

Or, for the last moves you could probably have the third table LAST_MOVES 
maintained through triggers on WORDS_MOVES table.
Then, you just join WORDS_GAMES and LAST_MOVES tables.

Regards,
Igor

Re: [GENERAL] Forward declaration of table

2016-08-23 Thread Alexander Farber 
Hi Igor,

On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman  wrote:

> mailto:pgsql-general-ow...@postgresql.org] *On Behalf Of *Alexander Farber
>
>
https://gist.github.com/afarber/c40b9fc5447335db7d24
>
>
>
> Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on
> delete cascade).
>
> So, you don’t need mid1, mid2 columns in WORD_GAMES table.
>
> What you need is this column in WORD_MOVES table:
>
>
>
> gid integer REFERENCES WORD_GAMES ON DELETE CASCADE
>
>
>

you are correct, but I need to send most recent move in each game together
with the other game data.

If I don't store the recent moves in mid1, mid2 then I'd have to retrieve
them every time dynamically with

WITH last_moves AS (
  SELECT *
  FROM words_moves wm1
  WHERE
played = (SELECT max(played)
  FROM words_moves wm2
  WHERE wm1.gid = wm2.gid))
SELECT *
FROM words_games wg
  LEFT JOIN last_moves lm
ON (wg.gid = lm.gid)
WHERE
  player1 = 1 OR
  player2 = 1;


Regards
Alex

Re: [GENERAL] Forward declaration of table

2016-08-23 Thread Igor Neyman 



Regards,
Igor

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 1:11 PM
To: pgsql-general 
Subject: [GENERAL] Forward declaration of table

Good evening,

with PostgreSQL 9.5.3 I am using the following table to store 2-player games:
DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile  varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);

This has worked well for me (when a user connects to the game server, I send 
her all games she is taking part in), but then I have decided to add another 
table to act as a "logging journal" for player moves:
DROP TABLE IF EXISTS words_moves;
DROP TYPE IF EXISTS words_action;
CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score > 0)
);

Also, in the former table words_games I wanted to add references to the latest 
moves performed by players:

-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,

The intention is: whenever a player connects to the server, sent her all active 
games and status updates on the recent opponent moves.

However the 2 added columns do not work:
ERROR:  relation "words_moves" does not exist
ERROR:  relation "words_games" does not exist
ERROR:  relation "words_moves" does not exist

So my question is if I can somehow "forward declare" the words_moves table?

Here are all tables of my game for more context:
https://gist.github.com/afarber/c40b9fc5447335db7d24

Thank you
Alex

Alex,
I think, you’ve got this reference “backwards”.

Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete 
cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:

gid integer REFERENCES WORD_GAMES ON DELETE CASCADE

Am right/wrong?

Regards,
Igor

Re: [GENERAL] Forward declaration of table

2016-08-23 Thread Adrian Klaver 

On 08/23/2016 10:29 AM, David G. Johnston wrote:

On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver
>wrote:


use ALTER TABLE ADD table_constraint :

https://www.postgresql.org/docs/9.5/static/sql-altertable.html


to add the FK references to word_games.


​Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore
hazards here.  Maybe pg_dump is smart enough to handle this correctly,
though - maybe by adding constraint definitions after all tables and
columns are present.


It does. Though the usual caveats about doing partial dumps apply, eg if 
I had only specified -t fk_child below I would not get fk_parent 
automatically:


postgres@test=# create table fk_child(id int, fk_id int);
CREATE TABLE
postgres@test=# create table fk_parent(id int, some_id int UNIQUE);
CREATE TABLE
postgres@test=# alter table fk_child ADD CONSTRAINT fk_constraint 
FOREIGN KEY (fk_id) REFERENCES fk_parent(some_id);

ALTER TABLE

pg_dump -d test -U postgres  -t fk_parent -t fk_child -f test.sql


--
-- Name: fk_child; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE fk_child (
id integer,
fk_id integer
);


ALTER TABLE fk_child OWNER TO postgres;

--
-- Name: fk_parent; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE fk_parent (
id integer,
some_id integer
);


ALTER TABLE fk_parent OWNER TO postgres;

--
-- Data for Name: fk_child; Type: TABLE DATA; Schema: public; Owner: 
postgres

--

COPY fk_child (id, fk_id) FROM stdin;
\.


--
-- Data for Name: fk_parent; Type: TABLE DATA; Schema: public; Owner: 
postgres

--

COPY fk_parent (id, some_id) FROM stdin;
\.


--
-- Name: fk_parent_some_id_key; Type: CONSTRAINT; Schema: public; Owner: 
postgres

--

ALTER TABLE ONLY fk_parent
ADD CONSTRAINT fk_parent_some_id_key UNIQUE (some_id);


--
-- Name: fk_constraint; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY fk_child
ADD CONSTRAINT fk_constraint FOREIGN KEY (fk_id) REFERENCES 
fk_parent(some_id);





David J.​




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Forward declaration of table

2016-08-23 Thread David G. Johnston 
On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver 
wrote:

>
> use ALTER TABLE ADD table_constraint :
>
> https://www.postgresql.org/docs/9.5/static/sql-altertable.html
>
> to add the FK references to word_games.
>
>
​Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore hazards
here.  Maybe pg_dump is smart enough to handle this correctly, though -
maybe by adding constraint definitions after all tables and columns are
present.

David J.​

Re: [GENERAL] Forward declaration of table

2016-08-23 Thread David G. Johnston 
On Tue, Aug 23, 2016 at 1:10 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> So my question is if I can somehow "forward declare" the words_moves table?
>
>
​A better way to phrase this is:

Is it possible to create circular foreign key dependencies between tables?

The answer is no.

You generally need to introduce a third table.  I haven't delved deep
enough into your scenario to be more specific.

David J.
​

Re: [GENERAL] Forward declaration of table

2016-08-23 Thread Adrian Klaver 

On 08/23/2016 10:10 AM, Alexander Farber wrote:

Good evening,

with PostgreSQL 9.5.3 I am using the following table to store 2-player
games:

DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE
CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile  varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);


This has worked well for me (when a user connects to the game server, I
send her all games she is taking part in), but then I have decided to
add another table to act as a "logging journal" for player moves:

DROP TABLE IF EXISTS words_moves;

DROP TYPE IF EXISTS words_action;

CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score > 0)
);

Also, in the former table words_games I wanted to add references to the
latest moves performed by players:

-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,

The intention is: whenever a player connects to the server, sent her all
active games and status updates on the recent opponent moves.

However the 2 added columns do not work:

ERROR:  relation "words_moves" does not exist
ERROR:  relation "words_games" does not exist
ERROR:  relation "words_moves" does not exist


So my question is if I can somehow "forward declare" the words_moves table?


Off the top of my head:

Change this:
--mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE, 
--mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,


to

mid1 integer
mid2 integer

and then after

CREATE TABLE words_moves ...

use ALTER TABLE ADD table_constraint :

https://www.postgresql.org/docs/9.5/static/sql-altertable.html

to add the FK references to word_games.



Here are all tables of my game for more context:
https://gist.github.com/afarber/c40b9fc5447335db7d24

Thank you
Alex




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general