Postgresql – Procedure to read from crontab like table and populate another one periodically

performancepostgresqlquery-performancestored-procedures

Postgres 9.3
Debian 7

I'm working on a mobile game project (poker like) where an admin user can register a recurrent tournament. As it's recurrent, I modeled it crontab like as follows.

challenge_schedule
+-----------------------+---------+------------------------------------------------------------------------------------+---------+--------------+-------------+
|        Column         |  Type   |                                     Modifiers                                      | Storage | Stats target | Description |
+-----------------------+---------+------------------------------------------------------------------------------------+---------+--------------+-------------+
| challenge_schedule_id | integer | not null default nextval('challenge_schedule_challenge_schedule_id_seq'::regclass) | plain   |              |             |
| challenge_type_id     | integer | not null                                                                           | plain   |              |             |
| game_type_id          | integer | not null                                                                           | plain   |              |             |
| quorum                | integer | not null                                                                           | plain   |              |             |
| fee                   | integer |                                                                                    | plain   |              |             |
| winnings              | integer |                                                                                    | plain   |              |             |
| frequency_minute      | integer |                                                                                    | plain   |              |             |
| frequency_hour        | integer |                                                                                    | plain   |              |             |
| frequency_dom         | integer |                                                                                    | plain   |              |             |
| frequency_month       | integer |                                                                                    | plain   |              |             |
| frequency_dow         | integer |                                                                                    | plain   |              |             |
| frequency_year        | integer |                                                                                    | plain   |              |             |
| description           | varchar |                                                                                    |
+-----------------------+---------+------------------------------------------------------------------------------------+---------+--------------+-------------+

And I need to create a job (procedure) that will populate a queue of the next tournaments in the history table as follows.

challenge_history
+----------------------+-----------------------------+----------------------------------------------------------------------------------+----------+--------------+-------------+
|        Column        |            Type             |                                    Modifiers                                     | Storage  | Stats target | Description |
+----------------------+-----------------------------+----------------------------------------------------------------------------------+----------+--------------+-------------+
| challenge_history_id | integer                     | not null default nextval('challenge_history_challenge_history_id_seq'::regclass) | plain    |              |             |
| challenge_type_id    | integer                     |                                                                                  | plain    |              |             |
| game_type_id         | integer                     |                                                                                  | plain    |              |             |
| start_time           | timestamp without time zone |                                                                                  | plain    |              |             |
| end_time             | timestamp without time zone |                                                                                  | plain    |              |             |
| fee                  | integer                     |                                                                                  | plain    |              |             |
| winnings             | integer                     |                                                                                  | plain    |              |             |
| special              | boolean                     |                                                                                  | plain    |              |             |
| canceled_time        | timestamp without time zone |                                                                                  | plain    |              |             |
| canceled_reason      | character varying(255)      |                                                                                  | extended |              |             |
+----------------------+-----------------------------+----------------------------------------------------------------------------------+----------+--------------+-------------+

In other words, if I have only a daily tournament on schedule and run it to process the next 7 days, it needs to create 7 rows with the correct start_time on each. Consider that in a real world it will have a lot of recurrent tournaments daily, weekly, monthly… And it needs to populate everything for the next X days (7 in the example).

I was wondering the best way to solve this and want thoughts/ideas about.

Best Answer

After some time i figured out how to do this. Lets put as sample data 2 recurrent tournaments:

One daily at 16:20 and another every sunday at 14:00

insert into challenge_schedule(challenge_type_id, game_type_id, quorum, frequency_minute, frequency_hour, frequency_dom, frequency_month, frequency_dow, frequency_year, description) values (1, 4, 10, 20, 16, null, null, null, null, 'Daily 4:20 PM');
insert into challenge_schedule(challenge_type_id, game_type_id, quorum, frequency_minute, frequency_hour, frequency_dom, frequency_month, frequency_dow, frequency_year, description) values (1, 4, 10, 0, 14, null, null, 1, null, 'Sundays 14:00 PM');

My solution was to make a full join between the schedule and every minute timestamp (as it's the smallest division) from now until the days limit and comparing each timestamp field with each schedule as follows (In the example I'm calling the query to process 5 days (1440 minutes * 5)):

SELECT
  gs.start_time, cs.description
FROM
  challenge_schedule cs,
  (select date_trunc('minute', NOW()) + (gs||' minute')::INTERVAL as start_time from generate_series(1, 1440*2) gs) gs
WHERE
  (cs.frequency_minute = to_char(gs.start_time, 'MI')::INTEGER OR cs.frequency_minute IS NULL)
  AND (cs.frequency_hour = to_char(gs.start_time, 'HH24')::INTEGER OR cs.frequency_hour IS NULL)
  AND (cs.frequency_dom = to_char(gs.start_time, 'DD')::INTEGER OR cs.frequency_dom IS NULL)
  AND (cs.frequency_month = to_char(gs.start_time, 'MM')::INTEGER OR cs.frequency_month IS NULL)
  AND (cs.frequency_dow = to_char(gs.start_time, 'D')::INTEGER OR cs.frequency_dow IS NULL)
  AND (cs.frequency_year = to_char(gs.start_time, 'YYYY')::INTEGER OR cs.frequency_year IS NULL)
ORDER BY
  gs.start_time asc
;

In the comparisons each field needs to be equals to the generated timestamp field OR NULL that means every.

The resultset was:

2015-07-22 16:20:00 'Daily 4:20 PM'
2015-07-23 16:20:00 'Daily 4:20 PM'
2015-07-24 16:20:00 'Daily 4:20 PM'
2015-07-25 16:20:00 'Daily 4:20 PM'
2015-07-26 14:00:00 'Sundays 14:00 PM'
2015-07-26 16:20:00 'Daily 4:20 PM'

Hope it helps somebody.

Related Solutions

Sql-server – Programmatically copy index from one table to another, via a stored procedure

This is a really complicated thing to do in SQL code. It's complicated because indexes have so many options. Potential issues/complications:

Covering indexes
Included fields
Column order
Filtered indexes
Complicated index types (xml indexes, etc)

You will be much better off either scripting these out in SSMS or using the .NET SMO framework for this.

To appropriately account for all the above issues will require querying a large number of system tables and using CURSORs, WHILE loops, or other iterative constructs to get things in the right order.

It ain't pretty.

Sql-server – Move values from one table to another one via stored procedure in sql server 2008

You can easily implement your requirement using a trigger on Login table that will log info in the audit_login table.

the key is using table trigger to log info in the audit table for auditing.

I will see if I can provide a working code. But you got the idea now.

Update: below is a prototype code that will help you.

Note: you dont need any Stored procedure, A trigger will do the job.

You can put try/catch to handle errors, which I have not done as this is only to help you understand the concept.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Logins]') AND type in (N'U'))
DROP TABLE [dbo].[Logins]
GO
create table Logins (userid varchar(15) not null
                        ,username varchar(max)
                        ,[password] varchar(50)
                        ,[status] varchar(1)) -- S = Success, F= Fail
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit_login]') AND type in (N'U'))
DROP TABLE [dbo].[audit_login]
GO
SELECT [userid],[username],[status]  
    ,AuditDataState=CONVERT(VARCHAR(10),'')   
    ,AuditDMLAction=CONVERT(VARCHAR(10),'')    
    ,AuditUser =CONVERT(SYSNAME,'')  
    ,AuditDateTime=CONVERT(DATETIME,'01-JAN-1900')  
    into dbo.audit_login  
FROM dbo.Logins  
WHERE 1=2 

go                      
CREATE TRIGGER Logins_Insert 
ON dbo.Logins  
FOR INSERT  
AS  

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'New','Insert',SUSER_SNAME(),getdate()  FROM INSERTED   

GO  
CREATE TRIGGER Logins_Delete  
ON dbo.Logins  
FOR DELETE  
AS  

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'Old','Delete',SUSER_SNAME(),getdate()  FROM DELETED   
GO

CREATE TRIGGER Logins_Update  
ON dbo.Logins  
FOR UPDATE  
AS  

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'New','Update',SUSER_SNAME(),getdate()  FROM INSERTED   

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'Old','Update',SUSER_SNAME(),getdate()  FROM DELETED   

GO

Now insert some data :

-- Insert New Data
insert into Logins
values ('kin001', 'kin','$tr0ng01','s') 

select * from audit_login

-- Update the current Data
update Logins
set userid = 'kin-up-01'
where userid = 'kin001'

select * from audit_login

-- delete the data
delete from Logins
where userid = 'kin-up-01'

Below will be the end result:

Best Answer

Related Solutions

Sql-server – Programmatically copy index from one table to another, via a stored procedure

Sql-server – Move values from one table to another one via stored procedure in sql server 2008

Related Question