Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Alexander Farber]

Hello, I appreciate your comments, thank you

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Melvin Davidson]

On Mon, Sep 18, 2017 at 9:30 AM, Ron Johnson  wrote:

> On 09/18/2017 08:17 AM, Melvin Davidson wrote:
> [snip]
>
> I don't have any specific suggestion for an additional column, other than
> Berend's idea. However, I strongly advise against the use
> of ENUM's. They can create a major problem in the event one needs to be
> removed.
>
>
> Because it will internally renumber them?
>
> --
> World Peace Through Nuclear Pacification
>
>
>Because it will internally renumber them?
No, because while there is a facility to ADD a value to an enum, there is
none to delete/drop a value.


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Ron Johnson]

On 09/18/2017 08:17 AM, Melvin Davidson wrote:
[snip]
I don't have any specific suggestion for an additional column, other than 
Berend's idea. However, I strongly advise against the use
of ENUM's. They can create a major problem in the event one needs to be 
removed.


Because it will internally renumber them?

--
World Peace Through Nuclear Pacification

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Melvin Davidson]

On Mon, Sep 18, 2017 at 7:59 AM, Berend Tober  wrote:

> Alexander Farber wrote:
>
>> Good evening,
>>
>> In a word game I store all player moves in the table:
>>
>> CREATE TYPE words_action AS ENUM (
>> 'play',
>> 'skip',
>> 'swap',
>> 'resign',
>> 'ban',
>> 'expire'
>> );
>>
>> CREATE TABLE words_moves (
>> mid BIGSERIAL 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)
>> );
>>
>> I could run a cron job on all moves played each day and select the
>> "spectacular" ones by it,
>> i.e. when a very big score has been achieved in the move or all 7 tiles
>> have been played...
>>
>> Then I (as admin of the game) would manually review the daily mails sent
>> by that cronjob and
>> select the few I have found interesting - for later publishing them as
>> "daily puzzle" in my day.
>>
>> However I don't want to do the reviewing every day as that would be
>> tedious, but more like once
>> per week and then select several such moves at once (for the future
>> dates).
>>
>> My question (and thanks for reading my mail sofar) is: which column would
>> you add to the
>> words_moves table for such a purpose?
>>
>> If I add a boolean (is a daily puzzle move: true or false) - then it is
>> difficult to keep the
>> order of the daily puzzles, I think.
>>
>> If I add a timestamptz, then to which date to set it, when I do my manual
>> review once a week?
>>
>> I need to add a useful column, so that it would be easy to me to create a
>> web script which would
>> display today's and all past "daily puzzle" records - and wouldn't change
>> the already published
>> puzzles...
>>
>> If you have a good idea here, please share with me. If not, sorry for the
>> maybe offtopic
>> question.
>>
>
> I like the idea of a new column in words_games that allows nulls and to be
> filled in subsequently with the review date, but here's another idea to
> consider:
>
> If you have another place to store control information, you could store
> the mid value of the last-reviewed  words_moves table row. That serial
> column also keeps track of the order, btw.
>
> Or maybe you define another table capturing more detail, if you need it,
> such as
>
> CREATE TABLE spectacular_moves (
> mid BIGINTEGER REFERENCES words_games,
> review_date  timestamptz NOT NULL,
> publication_date timestamptz /*NULL allowed ... date to be filled
> in subsequently */,
> );
>
> Or those last two columns could both be appended to the word_games table,
> again, allowing NULL, but then filled in as the events occur.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I don't have any specific suggestion for an additional column, other than
Berend's idea. However, I strongly advise against the use
of ENUM's. They can create a major problem in the event one needs to be
removed. It is a lot easier and simpler to use a
Foreign Key table instead.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Berend Tober]

Alexander Farber wrote:

Good evening,

In a word game I store all player moves in the table:

CREATE TYPE words_action AS ENUM (
'play',
'skip',
'swap',
'resign',
'ban',
'expire'
);

CREATE TABLE words_moves (
mid BIGSERIAL 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)
);

I could run a cron job on all moves played each day and select the 
"spectacular" ones by it,
i.e. when a very big score has been achieved in the move or all 7 tiles have 
been played...

Then I (as admin of the game) would manually review the daily mails sent by 
that cronjob and
select the few I have found interesting - for later publishing them as "daily 
puzzle" in my day.

However I don't want to do the reviewing every day as that would be tedious, 
but more like once
per week and then select several such moves at once (for the future dates).

My question (and thanks for reading my mail sofar) is: which column would you 
add to the
words_moves table for such a purpose?

If I add a boolean (is a daily puzzle move: true or false) - then it is 
difficult to keep the
order of the daily puzzles, I think.

If I add a timestamptz, then to which date to set it, when I do my manual 
review once a week?

I need to add a useful column, so that it would be easy to me to create a web 
script which would
display today's and all past "daily puzzle" records - and wouldn't change the 
already published
puzzles...

If you have a good idea here, please share with me. If not, sorry for the maybe 
offtopic
question.


I like the idea of a new column in words_games that allows nulls and to be filled in subsequently 
with the review date, but here's another idea to consider:


If you have another place to store control information, you could store the mid value of the 
last-reviewed  words_moves table row. That serial column also keeps track of the order, btw.


Or maybe you define another table capturing more detail, if you need it, such as

CREATE TABLE spectacular_moves (
mid BIGINTEGER REFERENCES words_games,
review_date  timestamptz NOT NULL,
publication_date timestamptz /*NULL allowed ... date to be filled in 
subsequently */,
);

Or those last two columns could both be appended to the word_games table, again, allowing NULL, but 
then filled in as the events occur.




--
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] Selecting a daily puzzle record - which type of column to add?

[David G. Johnston]

On Sun, Sep 17, 2017 at 1:13 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> I need to add a useful column, so that it would be easy to me to create a
> web script which would display today's and all past "daily puzzle" records
> - and wouldn't change the already published puzzles...
>
>
​Serial (i.e. integer/bigint with an sequence generator).  I would then add
another table that simply stores the "last manually reviewed id" as its
only record (or you could record manual reviews and take the max of that
field from the table).

David J.

[GENERAL] Selecting a daily puzzle record - which type of column to add?

[Alexander Farber]

Good evening,

In a word game I store all player moves in the table:

CREATE TYPE words_action AS ENUM (
'play',
'skip',
'swap',
'resign',
'ban',
'expire'
);

CREATE TABLE words_moves (
mid BIGSERIAL 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)
);

I could run a cron job on all moves played each day and select the
"spectacular" ones by it, i.e. when a very big score has been achieved in
the move or all 7 tiles have been played...

Then I (as admin of the game) would manually review the daily mails sent by
that cronjob and select the few I have found interesting - for later
publishing them as "daily puzzle" in my day.

However I don't want to do the reviewing every day as that would be
tedious, but more like once per week and then select several such moves at
once (for the future dates).

My question (and thanks for reading my mail sofar) is: which column would
you add to the words_moves table for such a purpose?

If I add a boolean (is a daily puzzle move: true or false) - then it is
difficult to keep the order of the daily puzzles, I think.

If I add a timestamptz, then to which date to set it, when I do my manual
review once a week?

I need to add a useful column, so that it would be easy to me to create a
web script which would display today's and all past "daily puzzle" records
- and wouldn't change the already published puzzles...

If you have a good idea here, please share with me. If not, sorry for the
maybe offtopic question.

Thanks
Alex