December 17, 2012 at 16:50Scripting for budget activation (part 1)
Some time ago, I happened 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. The development of activation scripts was conducted in JavaScript. The main idea that I learned from this project was that the development and debugging of activation scripts in JavaScript is extremely time-consuming. Intensive development was carried out for 1 year, and only our activation group included about 10 developers (at least testers were required).
In June this year, already the local leadership, made me happy with a new activation project in which Cisco routers were replaced by Alcatel S12 and M200 PBXs. The second difference of this project was that I had to develop the entire activation part alone, from scratch, within six months.
Of course, I didn’t even think of using JavaScript or something similar for activation scripts. I needed a budget scripting option. After some thought, I decided to store the activation scripts in the Oracle database, using the following structure for storage:
This diagram shows that the parts of the script (AE_SCRIPT_PART) that are a script (AE_SCRIPT) or a command (AE_COMMAND) are linked into a hierarchical structure using the AE_SUBSCRIPT table (initially AE_SCRIPT_PART simply contained a PARENT field that refers to the same table, but such a solution prevented reuse of script fragments). The AE_SETTING table is used to bind to scripts and commands values of a number of settings defined by AE_SETTING_TYPE, such as an execution condition (if_condition) or a loop variable (for_each).
Initially, it was possible to create command templates (AE_COMMAND_TEMPLATE) associated with a set of predefined settings, but this feature turned out to be redundant and, at present, almost never used. In fact, this data scheme allows you to store AST .
In order not to get lost in all this disgrace, parallel to filling the script in the database, an Excel file of the following type was conducted:
This made it possible to conduct development, but it was, of course, not possible to transfer this work (or part of the work) to another employee in this form. Of course, I began to think 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 lower arrow served as a prototype of the developed scripting language. I just thought it would be nice using Perl, for example, to take out 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 one call to the stored procedure, I could generate the script text in the CLOB, and this was no worse than the script in the text file. Here is the result:
After building the connect by query tree, the rest is almost trivial. Some difficulties were associated only with the generation of the correct sequence of procedural brackets '{' and '}' that determine the nesting of scripts. For the if_condition and foreach_var settings, the more familiar forms of the if and foreach statements are generated.
Here is the result of the work of this storage (I don’t give the whole script):
Each line of the script (with the exception of closing procedural brackets) defines a script or command. The command is determined by the characters '<' and '>', which determine the direction of data transfer (to and from equipment). Settings are determined by the following sequence:
The character ';' is a settings separator and should not be used inside the setting value (generally speaking, in S12 commands this character is used as a command separator, but I end the commands with the '.' symbol and there is no need to use ';' inside the command). In any case, adding escaping service characters inside values is not at all difficult.
An important but optional part of the script is the numbers in square brackets. These are recommended ID values for placing a script or command in the database. By setting the same ID value for commands or scripts, you can reuse a fragment of the script (provided that the marked fragments are really identical) by placing this fragment in the database once. If the ID value is not set, it is assigned automatically,
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, it would be great to be able to fix the script in text form and load it back into the database.
In the next article , I will implement this feature.
In June this year, already the local leadership, made me happy with a new activation project in which Cisco routers were replaced by Alcatel S12 and M200 PBXs. The second difference of this project was that I had to develop the entire activation part alone, from scratch, within six months.
Of course, I didn’t even think of using JavaScript or something similar for activation scripts. I needed a budget scripting option. After some thought, I decided to store the activation scripts in the Oracle database, using the following structure for storage:
This diagram shows that the parts of the script (AE_SCRIPT_PART) that are a script (AE_SCRIPT) or a command (AE_COMMAND) are linked into a hierarchical structure using the AE_SUBSCRIPT table (initially AE_SCRIPT_PART simply contained a PARENT field that refers to the same table, but such a solution prevented reuse of script fragments). The AE_SETTING table is used to bind to scripts and commands values of a number of settings defined by AE_SETTING_TYPE, such as an execution condition (if_condition) or a loop variable (for_each).
Initially, it was possible to create command templates (AE_COMMAND_TEMPLATE) associated with a set of predefined settings, but this feature turned out to be redundant and, at present, almost never used. In fact, this data scheme allows you to store AST .
In order not to get lost in all this disgrace, parallel to filling the script in the database, an Excel file of the following type was conducted:
This made it possible to conduct development, but it was, of course, not possible to transfer this work (or part of the work) to another employee in this form. Of course, I began to think 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 lower arrow served as a prototype of the developed scripting language. I just thought it would be nice using Perl, for example, to take out 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 one call to the stored procedure, I could generate the script text in the CLOB, and this was no worse than the script in the 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 query tree, the rest is almost trivial. Some difficulties were associated only with the generation of the correct sequence of procedural brackets '{' and '}' that determine the nesting of scripts. For the if_condition and foreach_var settings, the more familiar forms of the if and foreach statements are generated.
Here is the result of the work of this storage (I don’t give the whole script):
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 characters '<' and '>', which determine the direction of data transfer (to and from equipment). Settings are determined by the following sequence:
<Имя настройки>:<Значение>;
The character ';' is a settings separator and should not be used inside the setting value (generally speaking, in S12 commands this character is used as a command separator, but I end the commands with the '.' symbol and there is no need to use ';' inside the command). In any case, adding escaping service characters inside values is not at all difficult.
An important but optional part of the script is the numbers in square brackets. These are recommended ID values for placing a script or command in the database. By setting the same ID value for commands or scripts, you can reuse a fragment of the script (provided that the marked fragments are really identical) by placing this fragment in the database once. If the ID value is not set, it is assigned automatically,
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, it would be great to be able to fix the script in text form and load it back into the database.
In the next article , I will implement this feature.