DEY6zJ80

1create table myTable
2(   -- your main table, the one you cherish
3    `id` int auto_increment primary key, -- ignore this
4    `seqNum` int not null, -- FOCUS ON THIS
5    `others` varchar(100) not null
6) ENGINE=InnoDB;
7
8create table reuseMe
9(   -- table for sequence numbers to reuse
10    `seqNum` int not null primary key, -- FOCUS ON THIS
11    `reused` int not null -- 0 upon entry, 1 when used up (reused)
12    -- the primary key enforces uniqueness
13) ENGINE=InnoDB;;
14
15CREATE TABLE `sequences` (
16    -- table of sequence numbers system-wide
17    -- this is the table that allocates the incrementors to you
18    `id` int NOT NULL AUTO_INCREMENT,
19    `sectionType` varchar(200) NOT NULL,
20    `nextSequence` int NOT NULL,
21    PRIMARY KEY (`id`),
22    UNIQUE KEY `sectionType` (`sectionType`)
23) ENGINE=InnoDB;
24INSERT sequences(sectionType,nextSequence) values ('devices',1); -- this is the focus
25INSERT sequences(sectionType,nextSequence) values ('plutoSerialNum',1); -- not this
26INSERT sequences(sectionType,nextSequence) values ('nextOtherThing',1); -- not this
27-- the other ones are conceptuals for multi-use of a sequence table
28	
29DROP PROCEDURE IF EXISTS uspGetNextSequence;
30DELIMITER $$
31CREATE PROCEDURE uspGetNextSequence(p_sectionType varchar(200))
32BEGIN
33    -- a stored proc to manage next sequence numbers handed to you.
34    -- driven by the simple concept of a name. So we call it a section type.
35    -- uses SAFE INNODB Intention Locks to support concurrency
36    DECLARE valToUse INT;
37
38    START TRANSACTION;
39    SELECT nextSequence into valToUse from sequences where sectionType=p_sectionType FOR UPDATE;
40    IF valToUse is null THEN
41        SET valToUse=-1;
42    END IF;
43    UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType;
44    COMMIT; -- get it and release INTENTION LOCK ASAP
45    SELECT valToUse as yourSeqNum; -- return as a 1 column, 1 row resultset
46END$$
47DELIMITER ;
48-- ****************************************************************************************
49-- test:
50call uspGetNextSequence('devices'); -- your section is 'devices'
51	
52DROP PROCEDURE IF EXISTS uspAddToReuseList;
53DELIMITER $$
54CREATE PROCEDURE uspAddToReuseList(p_reuseNum INT)
55BEGIN
56    -- a stored proc to insert a sequence num into the reuse list
57    -- marks it available for reuse (a status column called `reused`)
58    INSERT reuseMe(seqNum,reused) SELECT p_reuseNum,0; -- 0 means it is avail, 1 not
59END$$
60DELIMITER ;
61-- ****************************************************************************************
62-- test:
63call uspAddToReuseList(701); -- 701 needs to be reused
64	
65DROP PROCEDURE IF EXISTS uspGetOneToReuse;
66DELIMITER $$
67CREATE PROCEDURE uspGetOneToReuse()
68BEGIN
69    -- a stored proc to get an available sequence num for reuse
70    -- a return of -1 means there aren't any
71    -- the slot will be marked as reused, the row will remain
72    DECLARE retNum int; -- the seq number to return, to reuse, -1 means there isn't one
73
74    START TRANSACTION;
75
76    -- it is important that 0 or 1 rows hit the following condition
77    -- also note that FOR UPDATE is the innodb Intention Lock
78    -- The lock is for concurrency (multiple users at once)
79    SELECT seqNum INTO retNum 
80    FROM reuseMe WHERE reused=0 ORDER BY seqNum LIMIT 1 FOR UPDATE;
81
82    IF retNum is null THEN
83        SET retNum=-1;
84    ELSE 
85        UPDATE reuseMe SET reused=1 WHERE seqNum=retNum; -- slot used
86    END IF;
87    COMMIT; -- release INTENTION LOCK ASAP
88
89    SELECT retNum as yoursToReuse; -- >0 or -1 means there is none
90END$$
91DELIMITER ;
92-- ****************************************************************************************
93-- test:
94call uspGetOneToReuse();
95	
96DROP PROCEDURE IF EXISTS uspCleanReuseList;
97DELIMITER $$
98CREATE PROCEDURE uspCleanReuseList()
99BEGIN
100    -- a stored proc to remove rows that have been successfully reused
101    DELETE FROM reuseMe where reused=1;
102END$$
103DELIMITER ;
104-- ****************************************************************************************
105-- test:
106call uspCleanReuseList();
107	
108DROP PROCEDURE IF EXISTS uspOoopsResetToAvail;
109DELIMITER $$
110CREATE PROCEDURE uspOoopsResetToAvail(p_reuseNum INT)
111BEGIN
112    -- a stored proc to deal with a reuse attempt (sent back to you)
113    -- that you need to reset the number as still available, 
114    -- perhaps because of a failed INSERT when trying to reuse it
115    UPDATE reuseMe SET reused=0 WHERE seqNum=p_reuseNum;
116END$$
117DELIMITER ;
118-- ****************************************************************************************
119-- test:
120call uspOoopsResetToAvail(701);