· 8 years ago · Nov 16, 2017, 10:08 AM
1#!/bin/bash
2shopt -s nullglob
3
4user="root"
5password="ctdspass"
6host="192.168.32.134"
7database="gm_operational"
8
9mysql -u"$user" -p"$password" -h"$host" migration_tmp -sNB -e"delete from migration_check WHERE migration_script='filters_blacklists';"
10
11#drop pairing table
12mysql -u"$user" -p"$password" -h"$host" "$database" -e"DROP TABLE IF EXISTS _tmp_pers_object_filters_blacklists;
13create table _tmp_pers_object_filters_blacklists (
14 pers_object_id bigint,
15 object_type_id smallint,
16 object_name varchar(255), -- pers_object.name
17 tso_id bigint,
18 nalg int,
19 lalg int,
20 talg int,
21 object_order varchar(1), -- order from UCTE
22 branch_ident1 varchar(255), -- branch identifier in old RIS (node1(7)_node2(7)_ordercode)
23 branch_ident2 varchar(255) -- branch identifier with switched nodes (node2(7)_node1(7)_ordercode)
24 );
25create index _tmp_pers_object_idx1 on _tmp_pers_object_filters_blacklists(branch_ident1);
26create index _tmp_pers_object_idx2 on _tmp_pers_object_filters_blacklists(branch_ident2);"
27
28
29
30mysql -u"$user" -p"$password" -h"$host" "$database" -e"insert into _tmp_pers_object_filters_blacklists (pers_object_id, object_type_id, object_name, tso_id, nalg, lalg, talg, object_order)
31select distinct po.pers_object_id, po.object_type_id, po.name, po.tso_id, tso.nalg, tso.lalg, tso.talg, t.order
32 from pers_object po
33 join trf t on (po.pers_object_id = t.pers_object_id)
34 join tso tso on (po.tso_id = tso.tso_id)
35union
36select distinct po.pers_object_id, po.object_type_id, po.name, po.tso_id, tso.nalg, tso.lalg, tso.talg, l.order
37 from pers_object po
38 join line l on (po.pers_object_id = l.pers_object_id)
39 join tso tso on (po.tso_id = tso.tso_id)
40union
41select distinct po.pers_object_id, po.object_type_id, po.name, po.tso_id, tso.nalg, tso.lalg, tso.talg, null
42 from pers_object po
43 join tso tso on (po.tso_id = tso.tso_id)
44 where po.object_type_id in (4,8);"
45
46
47
48mysql -u"$user" -p"$password" -h"$host" "$database" -e"update _tmp_pers_object_filters_blacklists
49 set object_order = case lalg
50 when 1 -- node (7) + node(7) + ordercode
51 then substring(object_name,17,1)
52 when 3 -- node(8) + node(8) + ordercode
53 then substring(object_name,19,1)
54 else null -- giving up to find the ordercode
55 end
56 where object_type_id = 3
57 and object_order is null;"
58
59
60mysql -u"$user" -p"$password" -h"$host" "$database" -e"update _tmp_pers_object_filters_blacklists
61 set object_order = case talg
62 when 1 -- node (7) + node(7) + ordercode
63 then substring(object_name,17,1)
64 when 3 -- node(8) + node(8) + ordercode
65 then substring(object_name,19,1)
66 else null -- giving up to find the ordercode
67 end
68 where object_type_id = 9
69 and object_order is null;"
70
71mysql -u"$user" -p"$password" -h"$host" "$database" -e"update _tmp_pers_object_filters_blacklists
72 set branch_ident1 = case
73 when substring(object_name,8,2) = '__'
74 then concat(
75 substring(object_name,1,7),
76 '_',
77 substring(object_name,10,7),
78 ifnull(concat('_',object_order),'')
79 )
80 else concat(
81 substring(object_name,1,7),
82 '_',
83 substring(substring(object_name,8,char_length(object_name)),instr(substring(object_name,8,char_length(object_name)),'_')+1,7),
84 ifnull(concat('_',object_order),'')
85 )
86 end,
87 branch_ident2 = case
88 when substring(object_name,8,2) = '__'
89 then concat(
90 substring(object_name,10,7),
91 '_',
92 substring(object_name,1,7),
93 ifnull(concat('_',object_order),'')
94 )
95 else concat(
96 substring(substring(object_name,8,char_length(object_name)),instr(substring(object_name,8,char_length(object_name)),'_')+1,7),
97 '_',
98 substring(object_name,1,7),
99 ifnull(concat('_',object_order),'')
100 )
101 end
102 where object_type_id in (3,9);"
103
104
105
106
107
108
109
110
111
112#loop over json strings in sm_filter
113while IFS=$'\t' read -r id name select_orig _default description author tso last_modified json_orig; do
114 element_list_orig=$(echo "$json_orig" | jq --compact-output '.[] | select( .item.name == "elementName" and .operator == "in" ) | .value[]' | tr -d "\\r\"")
115 element_list_orig+=$(echo "$json_orig" | jq --compact-output '.[] | select( .item.name == "elementName" and .operator == "=" ) | .value' | tr -d "\\r\"")
116
117 select_migrated=$select_orig
118 json_migrated=$json_orig
119
120 if [ -z "$element_list_orig" ]; then
121 # filter does not have any elements, skipping element migration
122 echo ""
123 else
124 echo ""
125 #loop over element names
126 while read -r element_orig; do
127 #migrate element
128 read -r element_migrated element_migrated_name < <(mysql -u"$user" -p"$password" -h"$host" "$database" -sNB -e"SELECT GROUP_CONCAT(pers_object_id),GROUP_CONCAT(object_name) FROM _tmp_pers_object_filters_blacklists po1 WHERE ((((po1.object_order is null or substring('$element_orig',17,1) in ('Z','Y','X','W','V','U','T','S','R')) and substring('$element_orig',1,15) in (substring(po1.branch_ident1,1,15), substring(po1.branch_ident2,1,15))) or (po1.object_order is not null and '$element_orig' in (po1.branch_ident1, po1.branch_ident2))) and po1.object_type_id in (3,9));")
129 #add quotes to pers object names
130 element_migrated_name=${element_migrated_name//','/'","'}
131 if [ "$element_migrated" = "NULL" ]; then
132 echo "Pers object $element_orig was not found!!!! We will use ID:\"0\" and name:\"$element_orig:notfound\" as a placeholders."
133 element_migrated="0"
134 element_migrated_name="$element_orig:notfound"
135 mysql -u"$user" -p"$password" -h"$host" "$database" -sNB -e"INSERT INTO migration_tmp.migration_check (migration_script, database_used, source_table, joined_table, source_element_id, node1_node2, obj_type, notes) VALUES ( 'filters_blacklists', '$database', 'export_data:sm_filter', '$database:pers_object', $id, '$element_orig', '3 or 9', 'filter name: $name' );"
136 fi
137 echo "$element_orig has been migrated to: $element_migrated with names: \"$element_migrated_name\""
138 #apply to json string value
139 json_migrated=${json_migrated/'"'"$element_orig"'"'/"$element_migrated"}
140 #apply to json string friendly_value
141 json_migrated=${json_migrated/'"'"$element_orig"'"'/'"'"$element_migrated_name"'"'}
142 #add spaces between elements for select string
143 element_migrated_select=${element_migrated/','/' , '}
144 #apply to select string
145 select_migrated=${select_migrated/ \'"$element_orig"\' / \'"$element_migrated_select"\' }
146 element_list_migrated+="$element_migrated"$'\n'
147 done <<< "$element_list_orig"
148 #loop over element names
149 fi
150
151 echo "Changing CSPE and CTDS_CENTRAL to CENTRAL in all filters json and select strings"
152 json_migrated=${json_migrated//'CTDS_CENTRAL'/'CENTRAL'}
153 select_migrated=${select_migrated//'CTDS_CENTRAL'/'CENTRAL'}
154 json_migrated=${json_migrated//'CSPE'/'CENTRAL'}
155 select_migrated=${select_migrated//'CSPE'/'CENTRAL'}
156 echo ""
157 echo "Changing CSPE and CTDS_CENTRAL to CENTRAL in tso"
158 tso=${tso//'CTDS_CENTRAL'/'CENTRAL'}
159 tso=${tso//'CSPE'/'CENTRAL'}
160
161
162 echo "Changing TSC JO and TSCNET_JO to TSCNET in name"
163 name=${name//'TCS JO'/'TSCNET'}
164 name=${name//'TSCNET_JO'/'TSCNET'}
165
166 echo "Replacing name for pers_object_id in select string"
167 select_migrated=${select_migrated//'name'/'pers_object_id'}
168
169 echo "Resetting author to default tso user 1"
170 case $tso in
171 CENTRAL)
172 author="ucentral1"
173 ;;
174 AMPRION)
175 author="uamprion1"
176 ;;
177 CEPS)
178 author="uceps1"
179 ;;
180 TRANSNETBW)
181 author="utransnetbw1"
182 ;;
183 PSE)
184 author="upse1"
185 ;;
186 SWISSGRID)
187 author="uswissgrid1"
188 ;;
189 TENNET_NL)
190 author="utennetnl1"
191 ;;
192 TENNET_DE)
193 author="utennetde1"
194 ;;
195 50HERTZ)
196 author="u50hertz1"
197 ;;
198 APG)
199 author="uapg1"
200 ;;
201 ELES)
202 author="ueles1"
203 ;;
204 HEP-OPS)
205 author="uhep-ops1"
206 ;;
207 MAVIR)
208 author="umavir1"
209 ;;
210 ENERGINET)
211 author="uenerginet1"
212 ;;
213 SEPS)
214 author="useps1"
215 ;;
216 CREOS)
217 author="ucreos1"
218 ;;
219 *)
220 echo "ERROR! TSO $tso in filter $name not found!"
221 ;;
222 esac
223
224 echo ""
225 echo "--------------------------------"
226 echo "ID: $id"
227 echo "name: $name"
228 echo "_default: $_default"
229 echo "description: $description"
230 echo "author: $author"
231 echo "TSO: $tso"
232 echo "last_modified: $last_modified"
233 echo "Json string original: $json_orig"
234 echo "Json string migrated: $json_migrated"
235 echo "Select string original: $select_orig"
236 echo "Select string migrated: $select_migrated"
237 echo "--------------------------------"
238 echo ""
239
240 echo mysql -u"$user" -p"$password" -h"$host" "$database" -e"INSERT IGNORE INTO sm_filter (id, name, select_string, json_string,_default, description, author, tso, last_modified) VALUES ( '$id','$name','$select_migrated','$json_migrated','$_default','$description','$author','$tso', '$last_modified' );"
241
242done < <(mysql -NB -u"$user" -p"$password" -h"$host" migration_tmp -e"SELECT id,name,select_string,_default,description,author,tso,last_modified,json_string FROM sm_filter")
243#loop over json strings in sm_filter
244
245
246echo "End of sm_filter migration"
247
248
249echo "Start of sm_blacklist migration"
250
251
252#loop over json strings in sm_blacklist
253while IFS=$'\t' read -r id name select_orig _default description author tso last_modified json_orig; do
254 element_list_orig=$(echo "$json_orig" | jq --compact-output '.[] | select( .item.name == "elementName" and .operator == "in" ) | .value[]' | tr -d "\\r\"")
255 element_list_orig+=$(echo "$json_orig" | jq --compact-output '.[] | select( .item.name == "elementName" and .operator == "=" ) | .value' | tr -d "\\r\"")
256
257 select_migrated=$select_orig
258 json_migrated=$json_orig
259
260 if [ -z "$element_list_orig" ]; then
261 # filter does not have any elements, skipping element migration
262 echo ""
263 else
264 echo ""
265 #loop over element names
266 while read -r element_orig; do
267 #migrate element
268 read -r element_migrated element_migrated_name < <(mysql -u"$user" -p"$password" -h"$host" "$database" -sNB -e"SELECT GROUP_CONCAT(pers_object_id),GROUP_CONCAT(object_name) FROM _tmp_pers_object_filters_blacklists po1 WHERE ((((po1.object_order is null or substring('$element_orig',17,1) in ('Z','Y','X','W','V','U','T','S','R')) and substring('$element_orig',1,15) in (substring(po1.branch_ident1,1,15), substring(po1.branch_ident2,1,15))) or (po1.object_order is not null and '$element_orig' in (po1.branch_ident1, po1.branch_ident2))) and po1.object_type_id in (3,9));")
269 #add quotes to pers object names
270 element_migrated_name=${element_migrated_name//','/'","'}
271 if [ "$element_migrated" = "NULL" ]; then
272 echo "Pers object $element_orig was not found!!!! We will use ID:\"0\" and name:\"$element_orig:notfound\" as a placeholders."
273 element_migrated="0"
274 element_migrated_name="$element_orig:notfound"
275 mysql -u"$user" -p"$password" -h"$host" "$database" -sNB -e"INSERT INTO migration_tmp.migration_check (migration_script, database_used, source_table, joined_table, source_element_id, node1_node2, obj_type, notes) VALUES ( 'filters_blacklists', '$database', 'export_data:sm_blacklist', '$database:pers_object', $id, '$element_orig', '3 or 9', 'blacklist name: $name' );"
276 fi
277 echo "$element_orig has been migrated to: $element_migrated with names: \"$element_migrated_name\""
278 #apply to json string value
279 json_migrated=${json_migrated/'"'"$element_orig"'"'/"$element_migrated"}
280 #apply to json string friendly_value
281 json_migrated=${json_migrated/'"'"$element_orig"'"'/'"'"$element_migrated_name"'"'}
282 #add spaces between elements for select string
283 element_migrated_select=${element_migrated/','/' , '}
284 #apply to select string
285 select_migrated=${select_migrated/ \'"$element_orig"\' / \'"$element_migrated_select"\' }
286 element_list_migrated+="$element_migrated"$'\n'
287 done <<< "$element_list_orig"
288 #loop over element names
289 fi
290
291 echo "Changing CSPE and CTDS_CENTRAL to CENTRAL in all blacklists json and select strings"
292 json_migrated=${json_migrated//'CTDS_CENTRAL'/'CENTRAL'}
293 select_migrated=${select_migrated//'CTDS_CENTRAL'/'CENTRAL'}
294 json_migrated=${json_migrated//'CSPE'/'CENTRAL'}
295 select_migrated=${select_migrated//'CSPE'/'CENTRAL'}
296 echo ""
297 echo "Changing CSPE and CTDS_CENTRAL to CENTRAL in tso"
298 tso=${tso//'CTDS_CENTRAL'/'CENTRAL'}
299 tso=${tso//'CSPE'/'CENTRAL'}
300
301
302 echo "Changing TSC JO and TSCNET_JO to TSCNET in name"
303 name=${name//'TCS JO'/'TSCNET'}
304 name=${name//'TSCNET_JO'/'TSCNET'}
305
306 echo "Replacing name for pers_object_id in select string"
307 select_migrated=${select_migrated//'name'/'pers_object_id'}
308
309 echo "Resetting author to default tso user 1"
310 case $tso in
311 CENTRAL)
312 author="ucentral1"
313 ;;
314 AMPRION)
315 author="uamprion1"
316 ;;
317 CEPS)
318 author="uceps1"
319 ;;
320 TRANSNETBW)
321 author="utransnetbw1"
322 ;;
323 PSE)
324 author="upse1"
325 ;;
326 SWISSGRID)
327 author="uswissgrid1"
328 ;;
329 TENNET_NL)
330 author="utennetnl1"
331 ;;
332 TENNET_DE)
333 author="utennetde1"
334 ;;
335 50HERTZ)
336 author="u50hertz1"
337 ;;
338 APG)
339 author="uapg1"
340 ;;
341 ELES)
342 author="ueles1"
343 ;;
344 HEP-OPS)
345 author="uhep-ops1"
346 ;;
347 MAVIR)
348 author="umavir1"
349 ;;
350 ENERGINET)
351 author="uenerginet1"
352 ;;
353 SEPS)
354 author="useps1"
355 ;;
356 CREOS)
357 author="ucreos1"
358 ;;
359 *)
360 echo "ERROR! TSO $tso in blacklist $name not found!"
361 ;;
362 esac
363
364 echo ""
365 echo "--------------------------------"
366 echo "ID: $id"
367 echo "name: $name"
368 echo "_default: $_default"
369 echo "description: $description"
370 echo "author: $author"
371 echo "TSO: $tso"
372 echo "last_modified: $last_modified"
373 #now we have list of original elements and list of their pers object ids
374 echo "Json string original: $json_orig"
375 echo "Json string migrated: $json_migrated"
376 echo "Select string original: $select_orig"
377 echo "Select string migrated: $select_migrated"
378 echo "--------------------------------"
379 echo ""
380
381 echo mysql -u"$user" -p"$password" -h"$host" "$database" -e"INSERT IGNORE INTO sm_blacklist (id, name, select_string, json_string,_default, description, author, tso, last_modified) VALUES ( '$id','$name','$select_migrated','$json_migrated','$_default','$description','$author','$tso', '$last_modified' );"
382
383done < <(mysql -NB -u"$user" -p"$password" -h"$host" migration_tmp -e"SELECT id,name,select_string,_default,description,author,tso,last_modified,json_string FROM sm_blacklist")
384#loop over json strings in sm_blacklist
385
386
387#cleanup
388mysql -u"$user" -p"$password" -h"$host" "$database" -e"DROP TABLE IF EXISTS _tmp_pers_object_filters_blacklists;"