Schnittstelle CampusOnline (TU Graz) zu SuperX-Modul COSTAGE
http://www.superx-projekt.de/doku/costage_modul/
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
441 lines
9.4 KiB
441 lines
9.4 KiB
3 years ago
|
216^ETL_MAKROS^<#macro ETL_STEPS_FUELLEN>\
|
||
|
\
|
||
|
\
|
||
|
create temp table tmp_etl_step(\
|
||
|
tid INTEGER, \
|
||
|
uniquename VARCHAR(255) , \
|
||
|
caption VARCHAR(255) , \
|
||
|
systeminfo_id INTEGER not null, \
|
||
|
step_type INTEGER, \
|
||
|
step_type_uniquename VARCHAR(255), \
|
||
|
sortnr SMALLINT not null, \
|
||
|
force_continue SMALLINT,\
|
||
|
etl_job_id INTEGER , \
|
||
|
parent_step_id INTEGER , \
|
||
|
parent_step_uniquename varchar(255),\
|
||
|
parent_job_uniquename varchar(255),\
|
||
|
logfile varchar(255),\
|
||
|
custom_step smallint,\
|
||
|
already_exists smallint\
|
||
|
) \
|
||
|
;\
|
||
|
\
|
||
|
create temp table tmp_etl_job(\
|
||
|
tid INTEGER, \
|
||
|
uniquename VARCHAR(255) , \
|
||
|
caption VARCHAR(255) , \
|
||
|
systeminfo_id INTEGER not null,\
|
||
|
logfile varchar(255),\
|
||
|
already_exists smallint,\
|
||
|
custom_job smallint\
|
||
|
) \
|
||
|
;\
|
||
|
\
|
||
|
create temp table tmp_etl_job_param(\
|
||
|
tid SERIAL not null, \
|
||
|
etl_job_id INTEGER , \
|
||
|
uniquename VARCHAR(255) not null, \
|
||
|
name VARCHAR(255) , \
|
||
|
param_default VARCHAR(255) \
|
||
|
) \
|
||
|
;\
|
||
|
\
|
||
|
\
|
||
|
create temp table tmp_etl_step_property(\
|
||
|
tid SERIAL not null, \
|
||
|
etl_step_id INTEGER not null, \
|
||
|
prop_name VARCHAR(255) , \
|
||
|
prop_value text\
|
||
|
\
|
||
|
) \
|
||
|
;\
|
||
|
\
|
||
|
create temp table tmp_etl_step_relation(\
|
||
|
tid SERIAL not null, \
|
||
|
step_id INTEGER not null, \
|
||
|
parent_step_id INTEGER , \
|
||
|
job_id INTEGER not null, \
|
||
|
force_continue SMALLINT default 1 , \
|
||
|
step_active SMALLINT default 1, \
|
||
|
sortnr SMALLINT default 1,\
|
||
|
custom_step SMALLINT default 1\
|
||
|
\
|
||
|
) \
|
||
|
;\
|
||
|
\
|
||
|
<#foreach etl_job in etl_jobs>\
|
||
|
\
|
||
|
truncate table tmp_etl_job;\
|
||
|
truncate table tmp_etl_step;\
|
||
|
truncate table tmp_etl_job_param;\
|
||
|
truncate table tmp_etl_step_property;\
|
||
|
truncate table tmp_etl_step_relation;\
|
||
|
\
|
||
|
--tids der jobs dürfen sich nicht ändern, daher \
|
||
|
--\
|
||
|
-- 1. vorh. Jobs updaten\
|
||
|
-- 2. neue Jobs einfügen\
|
||
|
-- 3. alte Jobs löschen\
|
||
|
\
|
||
|
insert into tmp_etl_job(uniquename,caption,systeminfo_id,logfile,already_exists,custom_job)\
|
||
|
values ('${etl_job.uniquename}',\
|
||
|
'${etl_job.name}',\
|
||
|
${etl_job.systeminfo_id},\
|
||
|
'${etl_job.logfile}',0,0);\
|
||
|
\
|
||
|
<#if SQLdialect='Postgres'>\
|
||
|
--Postgres Dialekt:\
|
||
|
\
|
||
|
update tmp_etl_job set tid=J.tid,\
|
||
|
already_exists=1,\
|
||
|
caption=J.caption,\
|
||
|
logfile=J.logfile,\
|
||
|
custom_job=J.custom_job\
|
||
|
from etl_job J where J.uniquename=tmp_etl_job.uniquename\
|
||
|
and J.systeminfo_id=tmp_etl_job.systeminfo_id\
|
||
|
;\
|
||
|
\
|
||
|
<#else>\
|
||
|
\
|
||
|
--Informix Dialekt:\
|
||
|
update tmp_etl_job set (tid,\
|
||
|
already_exists,\
|
||
|
caption,\
|
||
|
logfile,\
|
||
|
custom_job) \
|
||
|
= ((select \
|
||
|
tid,\
|
||
|
1 as already_exists,\
|
||
|
caption,\
|
||
|
logfile,\
|
||
|
custom_job\
|
||
|
from etl_job J where J.uniquename=tmp_etl_job.uniquename\
|
||
|
and J.systeminfo_id=tmp_etl_job.systeminfo_id))\
|
||
|
where 0 <(select count(*)\
|
||
|
from etl_job J where J.uniquename=tmp_etl_job.uniquename\
|
||
|
and J.systeminfo_id=tmp_etl_job.systeminfo_id)\
|
||
|
\
|
||
|
;\
|
||
|
\
|
||
|
</#if>\
|
||
|
\
|
||
|
\
|
||
|
\
|
||
|
--TODO Informix\
|
||
|
\
|
||
|
--neue jobs:\
|
||
|
insert into etl_job(uniquename,caption,systeminfo_id,custom_job)\
|
||
|
select uniquename,caption,systeminfo_id,custom_job\
|
||
|
from tmp_etl_job\
|
||
|
where already_exists=0;\
|
||
|
--tid von neuen Jobs ermitteln:\
|
||
|
update tmp_etl_job set tid=(select J.tid\
|
||
|
from etl_job J where J.uniquename=tmp_etl_job.uniquename\
|
||
|
and J.systeminfo_id=tmp_etl_job.systeminfo_id)\
|
||
|
where already_exists=0\
|
||
|
;\
|
||
|
\
|
||
|
--TODO\
|
||
|
--obsolete Jobs: sollen bei Deinstallation des Moduls entfernt werden\
|
||
|
\
|
||
|
\
|
||
|
--Parameter:\
|
||
|
<#foreach etl_job_param in etl_job_params>\
|
||
|
<#if etl_job_param.etl_job==etl_job.uniquename>\
|
||
|
\
|
||
|
insert into tmp_etl_job_param(\
|
||
|
etl_job_id , \
|
||
|
uniquename, \
|
||
|
name , \
|
||
|
param_default)\
|
||
|
select J.tid,\
|
||
|
'${etl_job_param.param_name}',\
|
||
|
'${etl_job_param.name}',\
|
||
|
'${etl_job_param.param_default}'\
|
||
|
from tmp_etl_job J\
|
||
|
;\
|
||
|
</#if>\
|
||
|
</#foreach>\
|
||
|
\
|
||
|
\
|
||
|
\
|
||
|
--ETL-Schritte \
|
||
|
<#assign sortnr=0 />\
|
||
|
<#foreach etl_step in etl_steps>\
|
||
|
<#if etl_step.etl_job==etl_job.uniquename>\
|
||
|
\
|
||
|
<#assign sortnr=sortnr+1 />\
|
||
|
\
|
||
|
insert into tmp_etl_step(\
|
||
|
uniquename , \
|
||
|
caption , \
|
||
|
systeminfo_id , \
|
||
|
step_type_uniquename,\
|
||
|
sortnr, \
|
||
|
force_continue,\
|
||
|
etl_job_id , \
|
||
|
parent_step_uniquename,\
|
||
|
parent_job_uniquename,\
|
||
|
logfile,\
|
||
|
custom_step,\
|
||
|
already_exists\
|
||
|
)\
|
||
|
select '${etl_step.uniquename}',\
|
||
|
'${etl_step.name}',\
|
||
|
${etl_job.systeminfo_id},\
|
||
|
'${etl_step.type}' as step_type_uniquename,\
|
||
|
${sortnr}*10 as sortnr,\
|
||
|
0 as force_continue,\
|
||
|
J.tid as etl_job_id,\
|
||
|
<#if etl_step.parent?exists && etl_step.parent !="" >\
|
||
|
'${etl_step.parent}',\
|
||
|
<#else>\
|
||
|
'' as parent_step_uniquename,\
|
||
|
</#if>\
|
||
|
J.uniquename,\
|
||
|
'${etl_job.logfile}' as logfile,\
|
||
|
0,\
|
||
|
0\
|
||
|
from etl_job J\
|
||
|
where J.uniquename='${etl_job.uniquename}'\
|
||
|
and J.systeminfo_id=${etl_job.systeminfo_id};\
|
||
|
\
|
||
|
\
|
||
|
\
|
||
|
\
|
||
|
</#if> --Ende steps eines job\
|
||
|
</#foreach>\
|
||
|
\
|
||
|
--erst job-params einfügen:\
|
||
|
\
|
||
|
delete from etl_job_param\
|
||
|
where etl_job_id in (\
|
||
|
SELECT distinct \
|
||
|
etl_job_id\
|
||
|
FROM tmp_etl_job_param )\
|
||
|
;\
|
||
|
\
|
||
|
insert into etl_job_param\
|
||
|
(\
|
||
|
etl_job_id,\
|
||
|
uniquename,\
|
||
|
name,\
|
||
|
param_default\
|
||
|
)\
|
||
|
SELECT \
|
||
|
etl_job_id,\
|
||
|
uniquename,\
|
||
|
name,\
|
||
|
param_default\
|
||
|
FROM tmp_etl_job_param \
|
||
|
;\
|
||
|
\
|
||
|
--nun steps einfügen:\
|
||
|
\
|
||
|
\
|
||
|
update tmp_etl_step set step_type=(select T.tid from etl_step_type T\
|
||
|
where T.uniquename=tmp_etl_step.step_type_uniquename);\
|
||
|
\
|
||
|
select * from tmp_etl_step\
|
||
|
where step_type is null;--_uniquename from tmp_etl_step;\
|
||
|
\
|
||
|
\
|
||
|
--vorhandene Steps erkennen:\
|
||
|
<#if SQLdialect='Postgres'>\
|
||
|
--Postgres Dialekt:\
|
||
|
\
|
||
|
update tmp_etl_step set tid=S.tid,\
|
||
|
already_exists=1,\
|
||
|
caption=S.caption,\
|
||
|
logfile=S.logfile,\
|
||
|
custom_step=S.custom_step\
|
||
|
from etl_step S where S.uniquename=tmp_etl_step.uniquename\
|
||
|
and S.systeminfo_id=tmp_etl_step.systeminfo_id\
|
||
|
;\
|
||
|
<#else>\
|
||
|
\
|
||
|
--Informix Dialekt:\
|
||
|
update tmp_etl_step set (tid,\
|
||
|
already_exists,\
|
||
|
caption,\
|
||
|
logfile,\
|
||
|
custom_step) \
|
||
|
= ((select \
|
||
|
tid,\
|
||
|
1 as already_exists,\
|
||
|
caption,\
|
||
|
logfile,\
|
||
|
custom_step\
|
||
|
from etl_step S where S.uniquename=tmp_etl_step.uniquename\
|
||
|
and S.systeminfo_id=tmp_etl_step.systeminfo_id))\
|
||
|
where 0 <(select count(*)\
|
||
|
from etl_step S where S.uniquename=tmp_etl_step.uniquename\
|
||
|
and S.systeminfo_id=tmp_etl_step.systeminfo_id)\
|
||
|
;\
|
||
|
\
|
||
|
\
|
||
|
\
|
||
|
</#if>\
|
||
|
\
|
||
|
\
|
||
|
--neue Steps einfügen:\
|
||
|
insert into etl_step(\
|
||
|
uniquename,\
|
||
|
caption,\
|
||
|
systeminfo_id,\
|
||
|
step_type_id,\
|
||
|
logfile,\
|
||
|
custom_step)\
|
||
|
select \
|
||
|
uniquename,\
|
||
|
caption,\
|
||
|
systeminfo_id,\
|
||
|
step_type,\
|
||
|
logfile,\
|
||
|
custom_step\
|
||
|
FROM tmp_etl_step \
|
||
|
where already_exists=0\
|
||
|
;\
|
||
|
\
|
||
|
--tid von neuen steps ermitteln:\
|
||
|
update tmp_etl_step set tid=(select S.tid\
|
||
|
from etl_step S where S.uniquename=tmp_etl_step.uniquename\
|
||
|
and S.systeminfo_id=tmp_etl_step.systeminfo_id)\
|
||
|
where already_exists=0\
|
||
|
;\
|
||
|
\
|
||
|
--parent ermitteln:\
|
||
|
update tmp_etl_step set parent_step_id=(select S.tid\
|
||
|
from etl_step S where S.uniquename=tmp_etl_step.parent_step_uniquename\
|
||
|
and S.systeminfo_id=tmp_etl_step.systeminfo_id)\
|
||
|
;\
|
||
|
\
|
||
|
\
|
||
|
delete from etl_step_property\
|
||
|
where etl_step_id in (select T.tid\
|
||
|
FROM tmp_etl_step T )\
|
||
|
;\
|
||
|
delete from etl_step_relation\
|
||
|
where job_id in (select J.tid\
|
||
|
FROM tmp_etl_job J)\
|
||
|
and custom_step=0\
|
||
|
;\
|
||
|
\
|
||
|
\
|
||
|
--jetzt step-params:\
|
||
|
\
|
||
|
<#foreach etl_step_property in etl_step_properties>\
|
||
|
insert into tmp_etl_step_property(\
|
||
|
etl_step_id,\
|
||
|
prop_name)\
|
||
|
select \
|
||
|
T.tid as etl_step_id,\
|
||
|
'${etl_step_property.prop_name}'\
|
||
|
\
|
||
|
FROM tmp_etl_step T \
|
||
|
where uniquename ='${etl_step_property.etl_step}'\
|
||
|
;\
|
||
|
\
|
||
|
</#foreach>\
|
||
|
\
|
||
|
--einfügen in echte Tabelle:\
|
||
|
insert into etl_step_property(\
|
||
|
etl_step_id,\
|
||
|
prop_name)\
|
||
|
select \
|
||
|
T.etl_step_id,\
|
||
|
T.prop_name\
|
||
|
FROM tmp_etl_step_property T \
|
||
|
;\
|
||
|
\
|
||
|
--jetzt step-relation:\
|
||
|
insert into tmp_etl_step_relation(\
|
||
|
step_id,\
|
||
|
parent_step_id,\
|
||
|
job_id,\
|
||
|
force_continue,\
|
||
|
step_active,\
|
||
|
sortnr,\
|
||
|
custom_step\
|
||
|
)\
|
||
|
select \
|
||
|
tid,\
|
||
|
parent_step_id,\
|
||
|
etl_job_id,\
|
||
|
0 as force_continue,\
|
||
|
1 as step_active,\
|
||
|
sortnr,\
|
||
|
0\
|
||
|
FROM tmp_etl_step \
|
||
|
;\
|
||
|
\
|
||
|
insert into etl_step_relation(\
|
||
|
step_id,\
|
||
|
parent_step_id,\
|
||
|
job_id,\
|
||
|
force_continue,\
|
||
|
step_active,\
|
||
|
sortnr,\
|
||
|
custom_step\
|
||
|
)\
|
||
|
select \
|
||
|
step_id,\
|
||
|
parent_step_id,\
|
||
|
job_id,\
|
||
|
force_continue,\
|
||
|
step_active,\
|
||
|
sortnr,\
|
||
|
custom_step\
|
||
|
FROM tmp_etl_step_relation \
|
||
|
;\
|
||
|
</#foreach> --Ende job\
|
||
|
\
|
||
|
\
|
||
|
drop table tmp_etl_step;\
|
||
|
drop table tmp_etl_job;\
|
||
|
drop table tmp_etl_job_param;\
|
||
|
drop table tmp_etl_step_property;\
|
||
|
drop table tmp_etl_step_relation;\
|
||
|
\
|
||
|
<#if testfaelle?exists>\
|
||
|
\
|
||
|
<#foreach testfall in testfaelle>\
|
||
|
select 'testfall ${testfall.testcase}: ERFOLG'\
|
||
|
from xdummy\
|
||
|
where ${testfall.assertion}=(${testfall.sql})\
|
||
|
;\
|
||
|
select 'testfall ${testfall.testcase}: FEHLER bei ${testfall.testcase}'\
|
||
|
from xdummy\
|
||
|
where ${testfall.assertion}!=(${testfall.sql})\
|
||
|
;\
|
||
|
\
|
||
|
\
|
||
|
</#foreach> --Ende Testfälle\
|
||
|
</#if>\
|
||
|
\
|
||
|
<#if SQLdialect='Informix'> \
|
||
|
--nun xupdates:\
|
||
|
--bei informix kann man TEXT Spalten nicht updaten, daher per jdbc updaten:\
|
||
|
--damit DOSQL nicht den xupdater beim Start dieses Scriptes anwirft,\
|
||
|
--wird das in eine temp. Datei ausgelagert.\
|
||
|
! echo "<xup""date>" > "./tmp"$MANDANTID".sql"\
|
||
|
<#foreach etl_step_property in etl_step_properties>\
|
||
|
\
|
||
|
! echo '<text table="etl_step_property" field="prop_value" where="prop_name='\\''${etl_step_property.prop_name}'\\'' and etl_step_id=(select S.tid from etl_step S where S.uniquename='\\''${etl_step_property.etl_step}'\\'')">${etl_step_property.prop_value}</text>' >>"./tmp"$MANDANTID".sql"\
|
||
|
\
|
||
|
\
|
||
|
</#foreach>\
|
||
|
! echo "</xup""date>" >> "./tmp"$MANDANTID".sql"\
|
||
|
! DOSQL "./tmp"$MANDANTID".sql"\
|
||
|
\
|
||
|
<#else>\
|
||
|
--Postgres:\
|
||
|
<#foreach etl_step_property in etl_step_properties>\
|
||
|
\
|
||
|
update etl_step_property set prop_value='${etl_step_property.prop_value}' where prop_name='${etl_step_property.prop_name}' and etl_step_id=(select S.tid from etl_step S where S.uniquename='${etl_step_property.etl_step}');\
|
||
|
\
|
||
|
</#foreach>\
|
||
|
</#if>\
|
||
|
\
|
||
|
</#macro>^Datenbankunabhängigkeit^^1^
|