How to extract CS predictions from DB (another upgrade to be done)
Jump to navigation
Jump to search
Dear All, a first version of a procedure to download predictions has been produced, and tested for two CS doublets. At the end of this e-mail you can find the code, to be implemented and executed as your local OPERA account. It produces one view (in OPERA) and one procedure (in OPERAPUB). The procedure works under two assumptions: 1) you have copied all the program settings available in the Central DBs (PP_COPY_SYSTEM), so you also have local references to prediction operation settings; 2) you have one partition ready to host the CS doublet data (this can be obtained by calling PC_ADD_BRICK_SPACE from the OPERA account, using the proper brick number - e.g. for CS doublet 3066734, ID_BRICK = 66734, ID_SET = 'MY CS') - if not so, the procedure will fail. The input is very simple: you need to specify the CS doublet whose predictions you want to download, and the DBLink to the Central DB (OPITA or OPFRA). Please be aware that ONLY CONFIRMED CANDIDATES will be downloaded. This saves a lot of disk space in your local DB, but it will get only the information that is strictly needed for scanning, i.e. position and slopes of the candidate microtracks. However, in your TB_PREDICTED_EVENTS you will get the number of the event related to this prediction, so a correlation between the electronic DAQ and emulsion is now established. As for the meaning of TB_PREDICTED_TRACKS, please be aware that for each physical track you will find the Kalman and Linear fit in mm for each brick in the probability map - this means that having 16 or even 20 rows in this table for a single-muon event is perfectly normal - the full picture of the event is available only in the Central DBs (and in the Analysis Follower). Members of a scanning lab should look mostly at TB_CS_CANDIDATES and its dependent tables, which are fully supported in local scanning DBs. The VW_LOCAL_CS_CANDIDATES is useful mostly for CS-brick connection. It shows, for every CS sheet in the doublet, the candidate track (side = 0 if it is a base track, side = 1/2 if it is a microtrack). NOTICE: When preparing scanning data for publication to Central DBs, it is required that scanback paths be explicitly linked to CS candidates. This is obtained by adding one row to TB_CSCANDS_SBPATHS per each link. One CS candidate may have several related scanback paths, as in the following example: ID_CS_EVENTBRICK ID_CANDIDATE ID_EVENTBRICK ID_SCANBACK_PROCOPID PATH ------------------------- ------------------- -------------------- --------------------------------- -------- 3076789 9000000043435 1076789 7000000657799 1 3076789 9000000043435 1076789 7000000657799 2 3076789 9000000043436 1076789 7000000657799 3 3076789 9000000043437 1076789 7000000657799 4 Conversely, the same scanback path might be related to several CS candidates (this could easily happen in a shower). For Black CS doublets another "prescription" will be prepared in the next days. Best regards Cristiano ------------------ BEGIN CODE -----------------
create or replace view opera.vw_local_cs_candidates as
with csdata as
(
select idev, idbk, idpl, cand, idcand, idz, sd, idmu,
tb_mipmicrotracks.grains, posx, posy, refz as posz, slopex, slopey from
(select idev, idbk, tb_zones.id_plate as idpl, cand, idcand, idz, sd, idmu,
refz from
(select idev, idbk, cand, idcand, idz, sd, idmu, decode(sd,1,
tb_views.upz,2, tb_views.downz) as refz from
(select ca.id_event as idev, ca.id_eventbrick as idbk, ca.candidate as cand,
cb.id_candidate as idcand, cb.id_zone as idz, cb.side as sd,
cb.id_microtrack as idmu from
(select id_event, id_eventbrick, candidate, id from tb_cs_candidates) ca
inner join tb_cs_candidate_tracks cb on (ca.id_eventbrick = cb.id_eventbrick
and cb.id_candidate = ca.id))
inner join tb_views on (idbk = tb_views.id_eventbrick and idz =
tb_views.id_zone and sd = tb_views.side))
inner join tb_zones on (tb_zones.id_eventbrick = idbk and tb_zones.id =
idz))
inner join tb_mipmicrotracks on (tb_mipmicrotracks.id_eventbrick = idbk and
tb_mipmicrotracks.id_zone = idz and tb_mipmicrotracks.side = sd and
tb_mipmicrotracks.id = idmu)
)
select idev as id_event, idbk as id_cs_eventbrick, idpl as id_plate,
nvl2(sdprev, 0, sd) as side, nvl2(sdprev, g1 + g2, g1) as grains,
round(nvl2(sdprev, px2, px1),1) as posx, round(nvl2(sdprev, py2, py1),1) as
posy, round(nvl2(sdprev, (px2 - px1) / (pz2 - pz1), sx1),4) as slopex,
round(nvl2(sdprev, (py2 - py1) / (pz2 - pz1), sy1), 4) as slopey from
(
select idev, idbk, idpl, sd,
lag(sd,1,null) over (partition by idev, idbk, idcand, idpl order by sd) as
sdprev,
lead(sd,1,null) over (partition by idev, idbk, idcand, idpl order by sd) as
sdnext,
idcand, grains as g1, posx as px1, posy as py1, posz as pz1, slopex as sx1,
slopey as sy1,
lag(grains,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as g2,
lag(posx,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as px2,
lag(posy,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as py2,
lag(posz,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as pz2,
lag(slopex,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as sx2,
lag(slopey,1,null) over (partition by idev, idbk, idcand, idpl order by sd)
as sy2 from csdata
) where ((sd = 2 and sdprev = 1) or (sdprev is null and sdnext is null))
/
create public synonym vw_local_cs_candidates for
opera.vw_local_cs_candidates
/
grant select on vw_local_cs_candidates to rl_datareader
/
create or replace procedure operapub.pp_download_predictions(i_id_eventbrick
integer, i_dblink varchar2) as
type typcur is ref cursor;
pc typcur;
v_procid integer;
v_rootid integer;
begin
execute immediate 'select distinct id_processoperation from
tb_cs_candidates@' || i_dblink || ' zb where (zb.id_eventbrick, zb.id) in
(select id_eventbrick, id_candidate from tb_cs_candidate_validation@' ||
i_dblink || ' zc where id_eventbrick = ' || i_id_eventbrick || ' and valid =
''Y'')' into v_rootid;
v_procid := v_rootid;
while v_procid is not NULL loop
v_rootid := v_procid;
execute immediate 'select id_parent_operation from tb_proc_operations@' ||
i_dblink || ' where id = ' || v_rootid into v_procid;
end loop;
dbms_output.put_line('ROOT: ' || v_rootid);
insert into tb_eventbricks (select * from tb_eventbricks@opita where id =
i_id_eventbrick);
insert into tb_plates (select * from tb_plates@opita where id_eventbrick =
i_id_eventbrick);
insert into tb_templatemarksets (select * from tb_templatemarksets@opita
where id_eventbrick = i_id_eventbrick);
open pc for 'select id from tb_proc_operations@' || i_dblink || ' connect
by id_parent_operation = prior id start with id = ' || v_rootid;
loop
fetch pc into v_procid;
exit when pc%notfound;
dbms_output.put_line('PROCOP: ' || v_procid);
execute immediate 'insert into tb_proc_operations (select * from
tb_proc_operations@' || i_dblink || ' where id = ' || v_procid || ')';
execute immediate 'insert into tb_proc_operations (select * from
tb_proc_operations@' || i_dblink || ' p where id in (select
id_processoperation from tb_predicted_events@' || i_dblink || ' where id in
(select id_event from tb_cs_candidates@' || i_dblink || ' ca where
id_eventbrick = ' || i_id_eventbrick || ' and id_processoperation = ' ||
v_procid || ' and exists (select * from tb_cs_candidate_validation@' ||
i_dblink || ' cb where ca.id_eventbrick = cb.id_eventbrick and ca.id =
cb.id_candidate and cb.VALID = ''Y''))) and not exists (select * from
tb_proc_operations q where p.id = q.id) )';
execute immediate 'insert into tb_predicted_events (select * from
tb_predicted_events@' || i_dblink || ' where id in (select id_event from
tb_cs_candidates@' || i_dblink || ' ca where id_eventbrick = ' ||
i_id_eventbrick || ' and id_processoperation = ' || v_procid || ' and exists
(select * from tb_cs_candidate_validation@' || i_dblink || ' cb where
ca.id_eventbrick = cb.id_eventbrick and ca.id = cb.id_candidate and cb.VALID
= ''Y'')))';
execute immediate 'insert into tb_predicted_tracks (select * from
tb_predicted_tracks@' || i_dblink || ' where id_event in (select id_event
from tb_cs_candidates@' || i_dblink || ' ca where id_eventbrick = ' ||
i_id_eventbrick || ' and id_processoperation = ' || v_procid || ' and exists
(select * from tb_cs_candidate_validation@' || i_dblink || ' cb where
ca.id_eventbrick = cb.id_eventbrick and ca.id = cb.id_candidate and cb.VALID
= ''Y'')))';
execute immediate 'insert into tb_cs_candidates (select * from
tb_cs_candidates@' || i_dblink || ' ca where id_eventbrick = ' ||
i_id_eventbrick || 'and id_processoperation = ' || v_procid || ' and exists
(select * from tb_cs_candidate_validation@' || i_dblink || ' cb where
ca.id_eventbrick = cb.id_eventbrick and ca.id = cb.id_candidate and cb.VALID
= ''Y''))';
end loop;
close pc;
open pc for 'select id from tb_proc_operations connect by
id_parent_operation = prior id start with id = ' || v_rootid;
loop
fetch pc into v_procid;
exit when pc%notfound;
execute immediate 'insert into tb_zones (select * from tb_zones@' ||
i_dblink || ' p where id_eventbrick = ' || i_id_eventbrick || ' and
id_processoperation = ' || v_procid || ' and exists (select * from
tb_cs_candidate_tracks@' || i_dblink || ' q where p.id_eventbrick =
q.id_eventbrick and p.id = q.id_zone))';
execute immediate 'insert into tb_views (select * from tb_views@' ||
i_dblink || ' p where (id_eventbrick, id_zone) in (select id_eventbrick, id
from tb_zones where id_eventbrick = ' || i_id_eventbrick || ' and
id_processoperation = ' || v_procid || '))';
end loop;
close pc;
open pc for 'select id from tb_proc_operations connect by
id_parent_operation = prior id start with id = ' || v_rootid;
loop
fetch pc into v_procid;
exit when pc%notfound;
execute immediate 'insert into tb_mipmicrotracks (select * from
tb_mipmicrotracks@' || i_dblink || ' where (id_eventbrick, id_zone, side,
id) in (select id_eventbrick, id_zone, side, id_microtrack from
tb_cs_candidate_tracks@' || i_dblink || ' where (id_eventbrick,
id_candidate) in (select id_eventbrick, id from tb_cs_candidates where
id_eventbrick = ' || i_id_eventbrick || ' and id_processoperation = ' ||
v_procid || ')))';
end loop;
close pc;
open pc for 'select id from tb_proc_operations connect by
id_parent_operation = prior id start with id = ' || v_rootid;
loop
fetch pc into v_procid;
exit when pc%notfound;
execute immediate 'insert into tb_cs_candidate_tracks (select * from
tb_cs_candidate_tracks@' || i_dblink || ' where (id_eventbrick,
id_candidate) in (select id_eventbrick, id from tb_cs_candidates where
id_eventbrick = ' || i_id_eventbrick || ' and id_processoperation = ' ||
v_procid || '))';
execute immediate 'insert into tb_cs_candidate_checks (select * from
tb_cs_candidate_checks@' || i_dblink || ' where (id_eventbrick,
id_candidate) in (select id_eventbrick, id from tb_cs_candidates where
id_eventbrick = ' || i_id_eventbrick || ' and id_processoperation = ' ||
v_procid || '))';
end loop;
close pc;
end;