gistfile1.sql

-- Abridged Schema

CREATE TABLE `tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_status` smallint(6) NOT NULL DEFAULT '0',
  `owner_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `tickets_pt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` int(11) NOT NULL,
  `later_time` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ticket_id` (`ticket_id`)
) ENGINE=InnoDB;

CREATE TABLE `shop_pagers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shop_id` int(11) NOT NULL DEFAULT '0',
  `pager` varchar(255) NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `last_page` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- Ticket Queue definition
-- English: Tickets that are in an open status (0) with no assigned owner (owner_id) in transport time order.
SELECT t.id 
FROM tickets t 
JOIN tickets_pt tpt ON tpt.ticket_id = t.id 
WHERE ticket_status = 0 AND owner_id = 0 
ORDER BY later_time
FOR UPDATE;

-- User Queue definition (not confident this is correct)
-- English: Users in the pager pool (user_id <> 0) that have no assigned (owner_id) open (ticket_status) tickets. 
-- i.e. Closed tickets (3,4,7) can be assigned to a user and not affect that user's ability to be "in queue".
SELECT 
  sp.user_id 
FROM shop_pagers sp 
LEFT JOIN tickets t ON t.owner_id = sp.user_id AND t.ticket_status NOT IN (3,4,7)
WHERE t.id IS NULL
AND sp.user_id <> 0
AND sp.shop_id = 1
FOR UPDATE;


-- Command-line mysql session demonstrating application logic and behavior

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

-- First, grab the Ticket Queue

mysql> SELECT t.id 
    -> FROM tickets t 
    -> JOIN tickets_pt tpt ON tpt.ticket_id = t.id 
    -> WHERE ticket_status = 0 AND owner_id = 0 
    -> ORDER BY later_time
    -> FOR UPDATE;
+------+
| id   |
+------+
| 1305 |
| 1306 |
| 1319 |
| 1320 |
| 1321 |
| 1322 |
| 1323 |
| 1324 |
| 1325 |
| 1326 |
| 1327 |
| 1309 |
+------+
12 rows in set (0.00 sec)

-- Then, grab the User Queue

mysql> SELECT 
    ->   sp.user_id 
    -> FROM shop_pagers sp 
    -> LEFT JOIN tickets t ON t.owner_id = sp.user_id AND t.ticket_status NOT IN (3,4,7)
    -> WHERE t.id IS NULL
    -> AND sp.user_id <> 0
    -> AND sp.shop_id = 1
    -> FOR UPDATE;
+---------+
| user_id |
+---------+
|      19 |
|      36 |
+---------+
2 rows in set (0.00 sec)

-- Now lets assign in order (top to bottom) each user to each ticket, stopping after N.
-- N is the size of the smaller queue.

mysql> UPDATE tickets SET owner_id = 19, ticket_status = 1 WHERE id = 1305;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE tickets SET owner_id = 36, ticket_status = 1 WHERE id = 1306;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)