v_extradata

proc_label: BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE extradataID INT;

  DECLARE extradataname VARCHAR(254);

  DECLARE basetablename VARCHAR(30);

  DECLARE pkey VARCHAR(100);

  DECLARE pkeyalias VARCHAR(30);

  DECLARE pname VARCHAR(100);

  DECLARE pnamecreate VARCHAR(1000);

  DECLARE tempTableName VARCHAR(100);

  DECLARE cur1 CURSOR FOR SELECT ID, NAME FROM extrafield WHERE Form = FID;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  -- This procedure is special and needs a higher group_contact_max_len. Set it here for the session.

  SET SESSION group_concat_max_len = 10000000;

  -- Before declaring the cursor, let's set the variables that change based upon what FID/Form we're scanning against.

  -- pkey is the primary key of the newly created table, and pname is the name column we use to identify the 2nd column in the table.

    -- SPECIAL CASE! Tickets does not have a pname. This has an exception later.

  CASE

    WHEN VName = 'Computers' OR VName = 'Probe' THEN

        SET pkey = 'computerid';

        SET pname = 'name';

        SET basetablename = 'Computers';

    WHEN VName = 'Locations' THEN

        SET pkey = 'locationid';

        SET pname = 'name';

        SET basetablename = 'Locations';

    WHEN VName = 'Clients' THEN

        SET pkey = 'clientid';

        SET pname = 'name';

        SET basetablename = 'Clients';

    WHEN VName = 'NetDevice' THEN

        SET pkey = 'id';

        SET pname = 'name';

        SET basetablename = 'NetworkDevices';

    WHEN VName = 'Tickets' THEN

        SET pkey = 'id';

        SET basetablename = 'Tickets';

    WHEN VName = 'Groups' THEN

        SET pkey = 'groupid';

        SET pname = 'name';

        SET basetablename = 'MasterGroups';

  END CASE;

  -- Define Ticket's exception for the create table statement below.

  CASE

    WHEN VName <> 'Tickets' THEN

        SELECT CONCAT(' `', pname, '` VARCHAR(50),', CHAR(10)) INTO pnamecreate;

    ELSE

        SELECT '' INTO pnamecreate;

  END CASE;

  -- Create a temporary table name

  SELECT CONCAT('`v_extradata_swap-',UUID(),'`') INTO tempTableName;

  -- Dynamically build the query that creates the table for us.

  -- Temporary table will hold the new data until it is ready to be quickly swapped

  -- Give Tickets it's special exception here.

  SELECT CONCAT('Create table ', tempTableName, ' (',

        pkey, ' int(11) NOT NULL DEFAULT '0' primary key,', CHAR(10),

        pnamecreate,

        GROUP_CONCAT(

                    CONCAT(' `',

                            NAME,

                            '` longtext not null'

                          ) SEPARATOR ',

'

                    ), ') ENGINE=InnoDB DEFAULT CHARSET=utf8;'

        ) FROM extrafield WHERE Form = FID

  INTO @createstatement;

  -- Recreate the table and wipe the create statement.

  PREPARE stmtcreate FROM @createstatement;

  EXECUTE stmtcreate;

  IF VName ='Computers' THEN

    CALL v_ExtraDataRefreshComputers(tempTableName);

      SELECT CONCAT('DROP TABLE IF EXISTS `v_extradata', VName, '`;')

    INTO @dropstatement;

    PREPARE stmtdrop FROM @dropstatement;

    EXECUTE stmtdrop;

    BEGIN

    -- Possible for target table to exist in a race condition between two running instances. If this happens, clean up and carry on.

        DECLARE EXIT HANDLER FOR 1050, SQLSTATE '42S01' -- Both are for 'ER_TABLE_EXISTS_ERROR'

        BEGIN

          SELECT CONCAT('DROP TABLE IF EXISTS ', tempTableName ,';') INTO @droptempstatement;

          PREPARE stmtdroptemp FROM @droptempstatement;

          EXECUTE stmtdroptemp;

        END;

        SELECT CONCAT('RENAME TABLE ', tempTableName, ' TO `v_extradata', VName, '`;') INTO @renamestatements;

        PREPARE stmtrename FROM @renamestatements;

        EXECUTE stmtrename;

        SET @renamestatements = '';

        LEAVE proc_label;

    END;

  END IF;

  SET @createstatement = '';

  OPEN cur1;

  REPEAT

    FETCH cur1 INTO extradataID, extradataname;

    -- Insert the data.

        CASE

            WHEN VName IN ('Locations', 'Clients', 'Probe', 'Groups') THEN

                SELECT CONCAT('INSERT IGNORE INTO ', tempTableName, ' (`',

                                                                            pkey, '`, `',

                                                                            pname, '`, `',

                                                                            extradataname,

                                                                        '`)',

                            ' SELECT `', pkey, '`,

 `',

                                        pname, '`,

',

                                        'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`

',

                            'from `', basetablename, '` `src` 

',

                            'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`', pkey, '` and `efd`.`extrafieldid`=', extradataID, ' 

',

                            'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '

',

                            'Group by `src`.`', pkey, '`

',

                            'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;

            WHEN VName = 'Tickets' THEN

                SELECT CONCAT('INSERT IGNORE INTO ', tempTableName, ' (`',

                                                                pkey, '`, `',

                                                                extradataname,

                                                            '`)',

                ' SELECT `TicketID`,

',

                        'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`

',

                'from `', basetablename, '` `src` 

',

                'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`TicketID` and `efd`.`extrafieldid`=', extradataID, ' 

',

                'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '

',

                'Group by `src`.`TicketID`

',

                'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;

            WHEN VName = 'NetDevice' THEN

                SELECT CONCAT('INSERT IGNORE INTO ', tempTableName, ' (`',

                                                                pkey, '`, `',

                                                                pname, '`, `',

                                                                extradataname,

                                                            '`)',

                ' SELECT `deviceid`,

 `',

                        pname, '`,

',

                        'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`

',

                'from `', basetablename, '` `src` 

',

                'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`deviceid` and `efd`.`extrafieldid`=', extradataID, ' 

',

                'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '

',

                'Group by `src`.`deviceid`

',

                'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;

        END CASE;

        PREPARE stmtinsert FROM @datastatement;

        EXECUTE stmtinsert;

        SET @datastatement = '';

    UNTIL done END REPEAT;

  -- Dynamically build the drop table statement.

  SELECT CONCAT('DROP TABLE IF EXISTS `v_extradata', VName, '`;')

  INTO @dropstatement;

  PREPARE stmtdrop FROM @dropstatement;

  EXECUTE stmtdrop;

  SET @dropstatement = '';

  -- Rename the previously built temptable, hopefully quick enough to avoid race conditions with other threads

  BEGIN

    -- Possible for target table to exist in a race condition between two running instances. If this happens, clean up and carry on.

    DECLARE EXIT HANDLER FOR 1050, SQLSTATE '42S01' -- Both are for 'ER_TABLE_EXISTS_ERROR'

    BEGIN

      SELECT CONCAT('DROP TABLE IF EXISTS ', tempTableName ,';') INTO @droptempstatement;

      PREPARE stmtdroptemp FROM @droptempstatement;

      EXECUTE stmtdroptemp;

    END;

    SELECT CONCAT('RENAME TABLE ', tempTableName, ' TO `v_extradata', VName, '`;') INTO @renamestatements;

    PREPARE stmtrename FROM @renamestatements;

    EXECUTE stmtrename;

    SET @renamestatements = '';

  END;

  CLOSE cur1;

END