1
proc_label: BEGIN
2
DECLARE done INT DEFAULT 0;
3
DECLARE extradataID INT;
4
DECLARE extradataname VARCHAR(254);
5
DECLARE basetablename VARCHAR(30);
6
DECLARE pkey VARCHAR(100);
7
DECLARE pkeyalias VARCHAR(30);
8
DECLARE pname VARCHAR(100);
9
DECLARE pnamecreate VARCHAR(1000);
10
DECLARE tempTableName VARCHAR(100);
11
DECLARE cur1 CURSOR FOR SELECT ID, NAME FROM extrafield WHERE Form = FID;
12
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
13
-- This procedure is special and needs a higher group_contact_max_len. Set it here for the session.
14
SET SESSION group_concat_max_len = 10000000;
15
-- Before declaring the cursor, let's set the variables that change based upon what FID/Form we're scanning against.
16
-- 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.
17
-- SPECIAL CASE! Tickets does not have a pname. This has an exception later.
18
CASE
19
WHEN VName = 'Computers' OR VName = 'Probe' THEN
20
SET pkey = 'computerid';
21
SET pname = 'name';
22
SET basetablename = 'Computers';
23
WHEN VName = 'Locations' THEN
24
SET pkey = 'locationid';
25
SET pname = 'name';
26
SET basetablename = 'Locations';
27
WHEN VName = 'Clients' THEN
28
SET pkey = 'clientid';
29
SET pname = 'name';
30
SET basetablename = 'Clients';
31
WHEN VName = 'NetDevice' THEN
32
SET pkey = 'id';
33
SET pname = 'name';
34
SET basetablename = 'NetworkDevices';
35
WHEN VName = 'Tickets' THEN
36
SET pkey = 'id';
37
SET basetablename = 'Tickets';
38
WHEN VName = 'Groups' THEN
39
SET pkey = 'groupid';
40
SET pname = 'name';
41
SET basetablename = 'MasterGroups';
42
END CASE;
43
-- Define Ticket's exception for the create table statement below.
44
CASE
45
WHEN VName <> 'Tickets' THEN
46
SELECT CONCAT(' `', pname, '` VARCHAR(50),', CHAR(10)) INTO pnamecreate;
47
ELSE
48
SELECT '' INTO pnamecreate;
49
END CASE;
50
-- Create a temporary table name
51
SELECT CONCAT('`v_extradata_swap-',UUID(),'`') INTO tempTableName;
52
-- Dynamically build the query that creates the table for us.
53
-- Temporary table will hold the new data until it is ready to be quickly swapped
54
-- Give Tickets it's special exception here.
55
SELECT CONCAT('Create table ', tempTableName, ' (',
56
pkey, ' int(11) NOT NULL DEFAULT '0' primary key,', CHAR(10),
57
pnamecreate,
58
GROUP_CONCAT(
59
CONCAT(' `',
60
NAME,
61
'` longtext not null'
62
) SEPARATOR ',
63
'
64
), ') ENGINE=InnoDB DEFAULT CHARSET=utf8;'
65
) FROM extrafield WHERE Form = FID
66
INTO @createstatement;
67
-- Recreate the table and wipe the create statement.
68
PREPARE stmtcreate FROM @createstatement;
69
EXECUTE stmtcreate;
70
IF VName ='Computers' THEN
71
CALL v_ExtraDataRefreshComputers(tempTableName);
72
SELECT CONCAT('DROP TABLE IF EXISTS `v_extradata', VName, '`;')
73
INTO @dropstatement;
74
PREPARE stmtdrop FROM @dropstatement;
75
EXECUTE stmtdrop;
76
BEGIN
77
-- Possible for target table to exist in a race condition between two running instances. If this happens, clean up and carry on.
78
DECLARE EXIT HANDLER FOR 1050, SQLSTATE '42S01' -- Both are for 'ER_TABLE_EXISTS_ERROR'
79
BEGIN
80
SELECT CONCAT('DROP TABLE IF EXISTS ', tempTableName ,';') INTO @droptempstatement;
81
PREPARE stmtdroptemp FROM @droptempstatement;
82
EXECUTE stmtdroptemp;
83
END;
84
SELECT CONCAT('RENAME TABLE ', tempTableName, ' TO `v_extradata', VName, '`;') INTO @renamestatements;
85
PREPARE stmtrename FROM @renamestatements;
86
EXECUTE stmtrename;
87
SET @renamestatements = '';
88
LEAVE proc_label;
89
END;
90
END IF;
91
SET @createstatement = '';
92
OPEN cur1;
93
REPEAT
94
FETCH cur1 INTO extradataID, extradataname;
95
-- Insert the data.
96
CASE
97
WHEN VName IN ('Locations', 'Clients', 'Probe', 'Groups') THEN
98
SELECT CONCAT('INSERT IGNORE INTO ', tempTableName, ' (`',
99
pkey, '`, `',
100
pname, '`, `',
101
extradataname,
102
'`)',
103
' SELECT `', pkey, '`,
104
`',
105
pname, '`,
106
',
107
'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`
108
',
109
'from `', basetablename, '` `src`
110
',
111
'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`', pkey, '` and `efd`.`extrafieldid`=', extradataID, '
112
',
113
'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '
114
',
115
'Group by `src`.`', pkey, '`
116
',
117
'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;
118
WHEN VName = 'Tickets' THEN
119
SELECT CONCAT('INSERT IGNORE INTO ', tempTableName, ' (`',
120
pkey, '`, `',
121
extradataname,
122
'`)',
123
' SELECT `TicketID`,
124
',
125
'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`
126
',
127
'from `', basetablename, '` `src`
128
',
129
'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`TicketID` and `efd`.`extrafieldid`=', extradataID, '
130
',
131
'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '
132
',
133
'Group by `src`.`TicketID`
134
',
135
'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;
136
WHEN VName = 'NetDevice' THEN
137
SELECT CONCAT('INSERT IGNORE INTO ', tempTableName, ' (`',
138
pkey, '`, `',
139
pname, '`, `',
140
extradataname,
141
'`)',
142
' SELECT `deviceid`,
143
`',
144
pname, '`,
145
',
146
'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`
147
',
148
'from `', basetablename, '` `src`
149
',
150
'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`deviceid` and `efd`.`extrafieldid`=', extradataID, '
151
',
152
'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '
153
',
154
'Group by `src`.`deviceid`
155
',
156
'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;
157
END CASE;
158
PREPARE stmtinsert FROM @datastatement;
159
EXECUTE stmtinsert;
160
SET @datastatement = '';
161
UNTIL done END REPEAT;
162
-- Dynamically build the drop table statement.
163
SELECT CONCAT('DROP TABLE IF EXISTS `v_extradata', VName, '`;')
164
INTO @dropstatement;
165
PREPARE stmtdrop FROM @dropstatement;
166
EXECUTE stmtdrop;
167
SET @dropstatement = '';
168
-- Rename the previously built temptable, hopefully quick enough to avoid race conditions with other threads
169
BEGIN
170
-- Possible for target table to exist in a race condition between two running instances. If this happens, clean up and carry on.
171
DECLARE EXIT HANDLER FOR 1050, SQLSTATE '42S01' -- Both are for 'ER_TABLE_EXISTS_ERROR'
172
BEGIN
173
SELECT CONCAT('DROP TABLE IF EXISTS ', tempTableName ,';') INTO @droptempstatement;
174
PREPARE stmtdroptemp FROM @droptempstatement;
175
EXECUTE stmtdroptemp;
176
END;
177
SELECT CONCAT('RENAME TABLE ', tempTableName, ' TO `v_extradata', VName, '`;') INTO @renamestatements;
178
PREPARE stmtrename FROM @renamestatements;
179
EXECUTE stmtrename;
180
SET @renamestatements = '';
181
END;
182
CLOSE cur1;
183
END