#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    18
    Rep Power
    0

    Generating a formatted autoincrement?


    A user requirement for one of my tables is to have a unique sessionID composed of a three letter string, followed by an auto incrementing integer, followed by the year.
    eg RAM-1-2013 then RAM-2-2013 then RAM-3-2013 etc.

    I've looked at similar threads and have an idea this can be done with triggers and an auto incrementing column somewhere else but am not sure how to do it

    Then there is a further requirement. The incrementing integer must reset to 1 at the first insert of a new year.
    So the first insert in 2014 would generate RAM-1-2014 etc.

    I've been thinking, playing, experimenting and worrying about race conditions for over a week now.

    Can anyone offer me a way to achive this behaviour (with innoDB tables)?

    The three character string will probably be taken from another table via a simple 'select prefix from area_codes where area_name = xxx'
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    18
    Rep Power
    0

    Generating a formatted autoincrement


    Originally Posted by dochsm
    A user requirement for one of my tables is to have a unique sessionID composed of a three letter string, followed by an auto incrementing integer, followed by the year.
    eg RAM-1-2013 then RAM-2-2013 then RAM-3-2013 etc.

    I've looked at similar threads and have an idea this can be done with triggers and an auto incrementing column somewhere else but am not sure how to do it

    Then there is a further requirement. The incrementing integer must reset to 1 at the first insert of a new year.
    So the first insert in 2014 would generate RAM-1-2014 etc.

    I've been thinking, playing, experimenting and worrying about race conditions for over a week now.

    Can anyone offer me a way to achive this behaviour (with innoDB tables)?

    The three character string will probably be taken from another table via a simple 'select prefix from area_codes where area_name = xxx'
    Finally found a way using a separate table that just holds the formatedSessionIDs and a couple of other useful fields and using a stored procedure to generate and store a pseudo formatted autoincremented value that can then be used as the primary key elsewhere.

    Here is the code in case anyone else wants something similar.
    you can see it here http://sqlfiddle.com/#!2/d232c/8


    The table DDL
    Code:
    CREATE TABLE `SessionIDs` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `DateOfSession` date DEFAULT NULL,
      `FormattedSessionID` varchar(45) DEFAULT NULL,
      `Area` varchar(45) DEFAULT NULL,
      `SequenceNumber` int(11) DEFAULT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
    The code for the stored procedure
    Code:
    DELIMITER $$
    
    CREATE PROCEDURE GenerateFormattedSessionID (TheArea varchar(45), TheSessionDate  Date, out NewSessionID int)
    -- generates a formatted SessionID of the form XXX-n-YYYY where XXX is the first three letters of the area
    -- n is a sequence number for that Area that resets each year and YYY is the Year
    -- it outputs the session ID as a single integer for use as a PK in the sessions table or to look up the formatted session id
    BEGIN
    DECLARE HighestYear varchar(4);
    DECLARE ThisSessionYear varchar(4);
    DECLARE HighestSequenceNumber INT;
    DECLARE NewFromattedSessionID VARCHAR(45);
    
    SET ThisSessionYear = year(TheSessionDate);
    SET HighestYear = (select max(year(DateOfSession) )from SessionIDs where Area = TheArea);
    
    if  (HighestYear is null) THEN -- no entries at all yet for this Area
        SET HighestSequenceNumber = 1; -- init sequence to 1
    ELSE
        IF HighestYear < ThisSessionYear THEN -- first insert of a new year
    		set HighestSequenceNumber = 1; -- init sequence back to 1
    	ELSE -- simply add one to existing highest sequence number for this team and this year
    		SET HighestSequenceNumber = (select max(SequenceNumber) 
    	                                                    from SessionIDs 
    	                                                    where Area = TheArea and year(DateOfSession) = year(TheSessionDate) ) + 1;
        END IF; 
    END IF;
    -- now we have the correct sequence number, get the first three letters from the Area and create the formatted sessionID
    SET NewFromattedSessionID = CONCAT(UPPER(LEFT(TheArea,3)), '-', CAST(HighestSequenceNumber as CHAR(10)), '-',ThisSessionYear);
    -- now insert the new record
    INSERT into SessionIDs 
    	(DateOfSession,FormattedSessionID,Area,SequenceNumber) 
    VALUES 
    	(TheSessionDate,NewFromattedSessionID,TheArea,HighestSequenceNumber) ; 
    SET NewSessionID = LAST_INSERT_ID(); -- send out the last record id to use as the PK in the Sessions table
    END;
    and its called with something like
    Code:
    call StoreNewSession('London', '2014-02-07',@ThePK);

IMN logo majestic logo threadwatch logo seochat tools logo