labtech Database

Parameters

Name	Type	Mode
FID	int	IN
VName	varchar(30)	IN

Definition

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