My ticket lottery engine based on SQL CTE procedure
This my project is clone of https://www.bountycompetitions.co.uk/ and deployed on Google Cloud. In this page I will describe SQL engine of this project
Related SQL engine based on 3 tables
1: CREATE TABLE `users` (
2: `UID` varchar(32) NOT NULL,
3: `USER_BALANCE` decimal(15,2) DEFAULT NULL,
4: ...
5: PRIMARY KEY (`UID`)
6: ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1: CREATE TABLE `userbalancelog` (
2: `i` int NOT NULL AUTO_INCREMENT,
3: `UID` varchar(32) DEFAULT NULL,
4: `DATE` timestamp NULL DEFAULT NULL,
5: `AMOUNT` decimal(15,2) DEFAULT NULL,
6: `COMPETITION_ID` varchar(32) DEFAULT NULL,
7: `PAYMENT_REF` varchar(32) DEFAULT NULL,
8: PRIMARY KEY (`i`)
9: ) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1: CREATE TABLE `competitions` (
2: `UID` varchar(32) NOT NULL,
3: ....
4: `crDate` datetime DEFAULT NULL,
5: `competitionprize` varchar(45) DEFAULT NULL,
6: `minnumberoftickets` int DEFAULT NULL,
7: `competitionenddate` date DEFAULT NULL,
8: `competitionticketsize` int DEFAULT NULL,
9: `Winner` varchar(32) DEFAULT NULL,
10: `competitionStatus` int DEFAULT NULL,
11: `haveTickets` int DEFAULT NULL,
12: `lastTickedAdded` datetime DEFAULT NULL,
13: `enoughBidsTriggerDone` int DEFAULT NULL,
14: `endTimeTaskId` varchar(255) DEFAULT NULL,
15: `timeTaskDone` datetime DEFAULT NULL,
16: PRIMARY KEY (`UID`)
17: ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
So, first procedure in this puzzle allow to add user balance. More correctly split this table to 2 separate table, one - is income money, secont - spend money, but I decide to merge both operation to one table.
1: CREATE DEFINER=`admin`@`%` PROCEDURE `add_amount`(IN ID varchar(32), IN amount decimal(15,2), IN paymentRef varchar(32), IN competitionId varchar(32))
2: BEGIN
3: UPDATE users set USER_BALANCE=IFNULL(USER_BALANCE, 0 ) + amount WHERE `UID`= ID;
4: INSERT INTO userbalancelog (UID,DATE,AMOUNT,PAYMENT_REF,COMPETITION_ID) VALUES (ID, NOW() , amount , paymentRef , competitionId);
5: END
Next point is transform amount to ticket number, ticket is calculating as competition price divide to ticket size (for example 5 dollars).
1: CREATE
2: ALGORITHM = UNDEFINED
3: DEFINER = `admin`@`%`
4: SQL SECURITY DEFINER
5: VIEW `GetTicketNumbers` AS
6: SELECT
7: `userbalancelog`.`i` AS `i`,
8: `userbalancelog`.`UID` AS `UID`,
9: `userbalancelog`.`COMPETITION_ID` AS `COMPETITION_ID`,
10: -(CEILING((`userbalancelog`.`AMOUNT` / (`competitions`.`competitionprize` / `competitions`.`competitionticketsize`)))) AS `TicketNumbers`
11: FROM
12: (`userbalancelog`
13: JOIN `competitions` ON ((`competitions`.`UID` = `userbalancelog`.`COMPETITION_ID`)))
For example in this case user 09901a22c7c3acc6786847c775f1d113 add to competition 53e6f1ff2bac1ce2888b0e539dfa9c4e number of time for 204 tickets
Next point we can calculate ticket number to each competiton.
1: CREATE DEFINER=`admin`@`%` PROCEDURE `update_ticketcount`(competitionID varchar(32))
2: BEGIN
3: CREATE TEMPORARY TABLE IF NOT EXISTS TMP_TAB AS SELECT sum(TicketNumbers) as TicketNumbers FROM GetTicketNumbers where COMPETITION_ID = competitionID;
4: Update competitions
5: set haveTickets=(select TicketNumbers from TMP_TAB LIMIT 1),
6: lastTickedAdded=now()
7: where UID = competitionID;
8: END
After user balance changed we need to refresh current ticket number, more correctly is merge line 150 and 151 to one transaction, but I don't do it in current project
So, we have 4488 tickets currently in competition 53e6f1ff2bac1ce2888b0e539dfa9c4e (this is debugging DB, therefore this number not related to common ticket investment)
Competition can complete in this project on two case - if common ticket count (haveTicket) exceed to minimum ticket count (what user define firstly when he create competition) of on time trigger (if of course user also receive minimum money he define).
First trigger fires after each adding competition
And secondary, when competition time exceed I check what competition is ready (this query I named readytocomplete)
1: SELECT UID,crDate,competitionStatus,minnumberoftickets,competitionenddate,competitionticketsize,haveTickets,lastTickedAdded,enoughBidsTriggerDone,endTimeTaskId,timeTaskDone
2: FROM Chicken.competitions where haveTickets >= minnumberoftickets and competitionenddate >= ? and competitionStatus is null;
and also fires finalize competition.
So, this API periodically call by Google Scheduler
I hide names, because this API is opened public and not protected by Google Cloud for DDOS-attack, only with with x-api-key
In both trigger (time exceed and minimal ticket exceed) Google Task with frontend notification will created
So, we have take shallow a look common project logic, and now we going to most interesting part of this puzzle. So, we have a lot of tickets, for example 1,000,000 and we need to select winner.
First step is simplest, we need simple require to Random.ORG and receive random number bitween 1 and haveTicket.
But next question is more sophisticated, how to receive winner with minimal system upload? We have for example 1,000,000 sequence numbers of user tickets (and additional question how to receive that sequence number of tickets) - how to avoid to transfer 1,000,000 number from SQL server to backend?
This is real highlight of whole this engine and whole backend - look to recursive SQL CTE produre I have created to this project!
1: CREATE DEFINER=`admin`@`%` PROCEDURE `get_winner`(competitionID varchar(32), winnerTicketNumber integer)
2: BEGIN
3: SET SESSION cte_max_recursion_depth = 1000000;
4: CREATE TEMPORARY TABLE IF NOT EXISTS TMP1 ENGINE=MEMORY as
5: (
6: WITH RECURSIVE CTE AS (
7: SELECT i, UID, TicketNumbers, 1 AS RowNo FROM Chicken.GetTicketNumbers where COMPETITION_ID = competitionID
8: UNION ALL
9: SELECT i, UID, TicketNumbers, RowNo+1
10: FROM CTE c
11: WHERE RowNo < TicketNumbers
12: )
13: SELECT UID, ROW_NUMBER() OVER (ORDER BY i,RowNo) as TicketNumber
14: FROM CTE
15: );
16: Select UID from TMP1 where TicketNumber=winnerTicketNumber;
17: END
And API finalizecompetition just call this procedure and receive winner.
To understand better my final solution you can see intermediate procedure, a middleway in my thinking about this solution.
1: CREATE DEFINER=`admin`@`%` PROCEDURE `get_tickets`(competitionID varchar(32))
2: BEGIN
3: SET SESSION cte_max_recursion_depth = 1000000;
4: WITH RECURSIVE CTE AS (
5: SELECT i, UID, TicketNumbers, 1 AS RowNo FROM Chicken.GetTicketNumbers where COMPETITION_ID = competitionID
6: UNION ALL
7: SELECT i, UID, TicketNumbers, RowNo+1
8: FROM CTE c
9: WHERE RowNo < TicketNumbers
10: )
11: SELECT *, ROW_NUMBER() OVER (ORDER BY i,RowNo) as TicketNumber
12: FROM CTE;
13: END
Happy programming !
)
| <SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME> |