Scripting for budget activation (part 1)
Some time ago, I had the opportunity to participate in a large international project as part of an activation team. The essence of the project was to automate the execution of a number of commands on Cisco equipment. Development of activation scripts was conducted on JavaScript. The main idea that I brought out of this project was that the development and debugging of JavaScript activation scripts is extremely time-consuming. Intensive development was carried out for 1 year, and only our activation group included about 10 developers (testers required no less).
In June of this year, local management already made me happy with a new activation project, in which Cisco routers changed to PBX Alcatel S12 and M200. The second difference of this project was that I had to develop the entire activation part for me alone, from scratch, for half a year.
Of course, I didn’t even think to use JavaScript or something similar for activation scripts. I needed a budget script option. After some reflection, I decided to store activation scripts in an Oracle database, using the following structure for storage:
')
This diagram shows that the script parts (AE_SCRIPT_PART), which are a script (AE_SCRIPT) or a command (AE_COMMAND), are linked into a hierarchical structure using the AE_SUBSCRIPT table (originally AE_SCRIPT_PART simply contained the PARENT field referring to the same table, but this solution prevented the reuse of script fragments). Table AE_SETTING is used to bind to the scripts and commands the values of a number of settings defined by AE_SETTING_TYPE, such as the execution condition (if_condition) or the loop variable (for_each).
Initially, it was possible to create command templates (AE_COMMAND_TEMPLATE) associated with a set of predefined settings, but this possibility was redundant and, at present, almost not used. In fact, this data scheme allows you to store AST .
In order not to get lost in all this disgrace, parallel to the filling of the script in the database, the following Excel-file was kept:
This made it possible to develop, but to transfer this work (or part of the work) to another employee, in this form, of course, was not possible. Of course, I started thinking about more friendly scripting.
About a month ago, I was asked to conduct a series of classes on the architecture of the new activation project (not only scripting, but the entire project as a whole). When developing one of the presentations, the following picture was drawn:
The text in the bottom arrow served as the prototype of the scripting language being developed. I just thought that it would be nice using Perl, for example, to remove the script description from the database and present it in a compact, readable form in order to check it for errors.
Then I thought a little more and decided to use PL / SQL, not Perl. Indeed, with a single call to the stored procedure, I could generate the script text in CLOB, and it was no worse than the script in a text file. Here is the result:
After building the connect by script tree with the query, the rest is almost trivial. Some difficulties were associated only with generating the correct sequence of procedural brackets '{' and '}' that define nesting scripts. For the if_condition and foreach_var settings, more familiar forms of the if and foreach statements are generated.
Here is the result of the work of this (the whole script I do not cite):
Each line of the script (with the exception of closing procedural brackets) defines a script or command. The command is determined by the symbols '<' and '>', which determine the direction of data transfer (to and from equipment). Settings are determined by the following sequence:
The symbol ';' is a settings separator and should not be used inside the setting value (generally speaking, in S12 commands this symbol is used as a command separator, but I finish the commands with the symbol '.' and the need to use ';' within the command does not arise). In any case, it is not at all difficult to add shielding of service characters within values.
An important but not obligatory part of the script is the numbers in square brackets. These are the recommended ID values for placing a script or command in the database. By specifying the same ID value for commands or scripts, you can achieve re-use of a script fragment (provided that the marked fragments are really identical) by placing this fragment in the database once. If the ID value is not specified, it is assigned automatically when the script is loaded into the database.
So, I learned how to extract scripts from the description in the database and present them in a compact and visual form, but the appetite comes with eating, and I want more. Indeed, how great it would be to be able to fix the script in text form and load it back into the database.
In the next article , I will deal with the implementation of this feature.
In June of this year, local management already made me happy with a new activation project, in which Cisco routers changed to PBX Alcatel S12 and M200. The second difference of this project was that I had to develop the entire activation part for me alone, from scratch, for half a year.
Of course, I didn’t even think to use JavaScript or something similar for activation scripts. I needed a budget script option. After some reflection, I decided to store activation scripts in an Oracle database, using the following structure for storage:
')
This diagram shows that the script parts (AE_SCRIPT_PART), which are a script (AE_SCRIPT) or a command (AE_COMMAND), are linked into a hierarchical structure using the AE_SUBSCRIPT table (originally AE_SCRIPT_PART simply contained the PARENT field referring to the same table, but this solution prevented the reuse of script fragments). Table AE_SETTING is used to bind to the scripts and commands the values of a number of settings defined by AE_SETTING_TYPE, such as the execution condition (if_condition) or the loop variable (for_each).
Initially, it was possible to create command templates (AE_COMMAND_TEMPLATE) associated with a set of predefined settings, but this possibility was redundant and, at present, almost not used. In fact, this data scheme allows you to store AST .
In order not to get lost in all this disgrace, parallel to the filling of the script in the database, the following Excel-file was kept:
This made it possible to develop, but to transfer this work (or part of the work) to another employee, in this form, of course, was not possible. Of course, I started thinking about more friendly scripting.
About a month ago, I was asked to conduct a series of classes on the architecture of the new activation project (not only scripting, but the entire project as a whole). When developing one of the presentations, the following picture was drawn:
The text in the bottom arrow served as the prototype of the scripting language being developed. I just thought that it would be nice using Perl, for example, to remove the script description from the database and present it in a compact, readable form in order to check it for errors.
Then I thought a little more and decided to use PL / SQL, not Perl. Indeed, with a single call to the stored procedure, I could generate the script text in CLOB, and it was no worse than the script in a text file. Here is the result:
ae_scripting.sql
create or replace package body ae_scripting as g_if_setting constant varchar2(100) default 'if_condition'; g_foreach_setting constant varchar2(100) default 'foreach_var'; g_target_setting constant varchar2(100) default 'target_platform'; g_if_statement constant varchar2(100) default 'if'; g_foreach_statement constant varchar2(100) default 'foreach'; procedure extract(p_id in number) as cursor c_cmd is select d.id, d.nm, d.lv, d.type_id, p.name platform, t0.value target, f.direction_id direction from ( select level lv, sys_connect_by_path(to_char(c.nm, '00000'), '/') pt, sys_connect_by_path(c.id, '.') nm, c.id id, c.type_id from ( select a.id id, a.type_id type_id, b.parent_id parent_id, b.order_num nm from ae_script_part a, ae_subscript b where b.child_id(+) = a.id ) c start with c.id = p_id connect by prior c.id = c.parent_id order by pt ) d left join ae_script e on (e.id = d.id) left join ae_command f on (f.id = d.id) left join ae_platform p on (p.id = e.platform_id) left join ae_setting t0 on (t0.object_id = d.id and t0.setting_type_id = 1000001); r_cmd c_cmd%rowtype; cursor c_par(p_cmd_id number) is select t.name name, s.value value from ae_script_part a inner join ae_setting s on (s.object_id = a.id) inner join ae_setting_type t on (t.id = s.setting_type_id) where a.id = p_cmd_id union all select t.name, s.value from ae_script_part a inner join ae_command c on (c.id = a.id) inner join ae_setting s on (s.object_id = c.template_id) inner join ae_setting_type t on (t.id = s.setting_type_id) where a.id = p_cmd_id; r_par c_par%rowtype; l_str varchar2(1000) default null; l_lob CLOB; l_lvl number default 0; l_plv number default 0; l_cnt number default null; l_stt varchar2(50) default null; begin delete from ae_script_src where id = p_id; insert into ae_script_src(id, text) values (p_id, empty_clob()); select text into l_lob from ae_script_src where id = p_id; dbms_lob.open(l_lob, dbms_lob.lob_readwrite); open c_cmd; loop fetch c_cmd into r_cmd; exit when c_cmd%notfound; while r_cmd.lv <= l_plv loop l_str := ' ' || lpad('}', 2 * l_lvl) || chr(13) || chr(10); dbms_lob.writeappend(l_lob, length(l_str), l_str); l_plv := l_plv - 1; l_lvl := l_lvl - 1; end loop; l_str := '[' || trim(to_char(r_cmd.id, '000000')) || '] '; if not r_cmd.direction is null then if r_cmd.direction = '1' then l_str := l_str || '<'; else l_str := l_str || '>'; end if; else l_str := l_str || ' '; end if; l_str := l_str || lpad(' ', 2 * r_cmd.lv); if not r_cmd.platform is null then l_str := l_str || 'platform:' || r_cmd.platform || '; '; end if; if not r_cmd.target is null then l_str := l_str || 'target:' || r_cmd.target || '; '; end if; open c_par(r_cmd.id); l_stt := null; loop fetch c_par into r_par; exit when c_par%notfound; if l_stt is null and r_par.name = g_if_setting then l_str := l_str || g_if_statement || ' (' || r_par.value || ') { '; l_stt := r_par.name; end if; if l_stt is null and r_par.name = g_foreach_setting then l_str := l_str || g_foreach_statement || ' (' || r_par.value || ') { '; l_stt := r_par.name; end if; end loop; close c_par; open c_par(r_cmd.id); loop fetch c_par into r_par; exit when c_par%notfound; if l_stt is null or l_stt <> r_par.name then if r_par.name <> g_target_setting then l_str := l_str || r_par.name || ':' || r_par.value || '; '; end if; end if; end loop; close c_par; select count(*) into l_cnt from ae_subscript where parent_id = r_cmd.id; if r_cmd.type_id = 1 and l_cnt > 0 then if l_stt is null then l_str := l_str || '{'; end if; l_lvl := l_lvl + 1; l_plv := r_cmd.lv; else if not l_stt is null then l_str := l_str || ' }'; end if; l_plv := r_cmd.lv - 1; end if; l_str := l_str || chr(13) || chr(10); dbms_lob.writeappend(l_lob, length(l_str), l_str); end loop; close c_cmd; while l_lvl > 0 loop l_str := ' ' || lpad('}', 2 * l_lvl) || chr(13) || chr(10); dbms_lob.writeappend(l_lob, length(l_str), l_str); l_lvl := l_lvl - 1; end loop; dbms_lob.close(l_lob); commit; exception when others then if c_cmd%isopen then close c_cmd; end if; if c_par%isopen then close c_par; end if; if dbms_lob.isopen(l_lob) = 1 then dbms_lob.close(l_lob); end if; rollback; raise; end; end ae_scripting; / After building the connect by script tree with the query, the rest is almost trivial. Some difficulties were associated only with generating the correct sequence of procedural brackets '{' and '}' that define nesting scripts. For the if_condition and foreach_var settings, more familiar forms of the if and foreach statements are generated.
Here is the result of the work of this (the whole script I do not cite):
1420.ae
[001420] target:ats.type; foreach (params) { [003101] platform:S-12; if (dou_off.dou = 'REDIRECT_NOANSWER') { [031010] < text:MODIFY-SUBSCR:DN=K'%s,CFWD=DEACT&CFWDNOR.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003111] platform:S-12; if (dou_off.dou = 'REDIRECT_BUSY') { [031110] < text:MODIFY-SUBSCR:DN=K'%s, CFWD=DEACT&CFWDBSUB.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003121] platform:S-12; if (dou_off.dou = 'REDIRECT_AUTOINF') { [031210] < text:MODIFY-SUBSCR:DN=K'%s, CFWD=DEACT&CFWDFIXA.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003131] platform:S-12; if (dou_off.dou = 'REDIRECT') { [031310] < text:MODIFY-SUBSCR:DN=K'%s, CFWD=DEACT&CFWDUVAR.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003071] platform:S-12; if (dou_off.dou = 'SET_ALARM_CLOCK') { [030710] < text:MODIFY-SUBSCR:DN=K'%s,ALMCALL=DEACT.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001009] var_list:is_alarm_param = 1; } } Each line of the script (with the exception of closing procedural brackets) defines a script or command. The command is determined by the symbols '<' and '>', which determine the direction of data transfer (to and from equipment). Settings are determined by the following sequence:
< >:<>; The symbol ';' is a settings separator and should not be used inside the setting value (generally speaking, in S12 commands this symbol is used as a command separator, but I finish the commands with the symbol '.' and the need to use ';' within the command does not arise). In any case, it is not at all difficult to add shielding of service characters within values.
An important but not obligatory part of the script is the numbers in square brackets. These are the recommended ID values for placing a script or command in the database. By specifying the same ID value for commands or scripts, you can achieve re-use of a script fragment (provided that the marked fragments are really identical) by placing this fragment in the database once. If the ID value is not specified, it is assigned automatically when the script is loaded into the database.
So, I learned how to extract scripts from the description in the database and present them in a compact and visual form, but the appetite comes with eating, and I want more. Indeed, how great it would be to be able to fix the script in text form and load it back into the database.
In the next article , I will deal with the implementation of this feature.
Source: https://habr.com/ru/post/162865/
All Articles