-- Hello emacs, please make this buffer -*- sql -*-
 -- Andre DOS ANJOS <Andre.dos.Anjos@cern.ch>
 
 -- $Author: stelzer $
 -- $Revision: 1.5 $
 -- $Date: 2007/05/10 10:07:59 $
 
 -- Creates a set of views that are useful for HLT (jobOption) configuration, 
 -- using INNER JOINS statements. Because the selects are very long, do *not*
 -- pin it down using a specific masterkey. If you make changes here, observe 
 -- the following:
 
 -- 1. DISTINCT keyword on selects, as well as other sorting, uniq'ing utilities
 --    require the SQL server to fetch the whole table sometimes, so let it for
 --    your last call.
 
 -- 2. Do *never* jump from N-to-N relationship table to another N-to-N relation
 --    ship table since the SQL server cannot find the index between the connected
 --    tables this way. Try to go from table to table in single direct steps.
 
 -- 3. If you are working with ORACLE, remember to make indexes out of the foreign
 --    keys of your tables, otherwise these views might be quite slow.
 
 -- Generates a view where we get all trigger elements associated with a given
 -- master key. This view is only used to gather the algorithms that are part
 -- of a run so the component query can be made easier. It is not necessary for
 -- normal JO calculations
 -- OPTIMIZATION STATUS: queries to a fixed HMT_ID gives a constant access time,
 -- independent of the size of the tables involved. Queries without a HMT_ID will
 -- issue a full scan on HLT_MASTER_TABLE, for MySQL5
 CREATE OR REPLACE VIEW HLT_MK_TO_TE_VIEW (HMT_ID, HTE_ID) AS
   SELECT HLT_MASTER_TABLE.HMT_ID, HLT_TRIGGER_ELEMENT.HTE_ID
   FROM HLT_MASTER_TABLE, -- Defines the trigger-menu to use
        HLT_TM_TO_TC, -- trigger-menu  -> trigger-chain
        HLT_TC_TO_TS, -- trigger-chain -> trigger-signature
        HLT_TS_TO_TE, -- trigger-signature  -> trigger-element
        HLT_TRIGGER_ELEMENT
   WHERE HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID IS NOT NULL AND
         HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID = 
           HLT_TM_TO_TC.HTM2TC_TRIGGER_MENU_ID AND
         HLT_TM_TO_TC.HTM2TC_TRIGGER_CHAIN_ID = 
           HLT_TC_TO_TS.HTC2TS_TRIGGER_CHAIN_ID AND
         HLT_TC_TO_TS.HTC2TS_TRIGGER_SIGNATURE_ID = 
           HLT_TS_TO_TE.HTS2TE_TRIGGER_SIGNATURE_ID AND
         HLT_TS_TO_TE.HTS2TE_TRIGGER_ELEMENT_ID =
           HLT_TRIGGER_ELEMENT.HTE_ID;
 
 
 -- Chooses the setup from the chains in a master key
 CREATE OR REPLACE VIEW HLT_MK_TO_ST_VIEW1 (HMT_ID, HST_ID) AS
   SELECT HLT_MASTER_TABLE.HMT_ID, HLT_SETUP.HST_ID
   FROM HLT_MASTER_TABLE,
        HLT_TM_TO_TC,
        HLT_TRIGGER_CHAIN,
        HLT_SETUP 
   WHERE HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID IS NOT NULL AND
         HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID = HLT_TM_TO_TC.HTM2TC_TRIGGER_MENU_ID AND
         HLT_TM_TO_TC.HTM2TC_TRIGGER_CHAIN_ID = HLT_TRIGGER_CHAIN.HTC_ID AND
         HLT_TRIGGER_CHAIN.HTC_SETUP_ID = HLT_SETUP.HST_ID;
 
 -- Chooses the setup from the forced setup entry in the master table
 -- OPTIMIZATION STATUS: queries to a fixed HMT_ID gives a constant access time,
 -- independent of the size of the tables involved. Queries without a HMT_ID will
 -- issue a full scan on HLT_MASTER_TABLE, for MySQL5
 CREATE OR REPLACE VIEW HLT_MK_TO_ST_VIEW2 (HMT_ID, HST_ID) AS
   SELECT HLT_MASTER_TABLE.HMT_ID, HLT_SETUP.HST_ID
   FROM HLT_MASTER_TABLE, -- mastertable
        HLT_SETUP
   WHERE HLT_MASTER_TABLE.HMT_FORCED_SETUP_ID = HLT_SETUP.HST_ID;
 
 -- This partial view gets all masterkeys and relations to the setups in one shot
 -- but you have to pay the price for derivation in SQL (otherwise it should be 
 -- be relatively fast if you pin down the masterkey).
 -- OPTIMIZATION STATUS: queries to a fixed HMT_ID gives an access time 
 -- proportional to the full scan of the dependent views, so it depends on the 
 -- size of the dependent views. Full scans on HLT_TM_TO_TC and _twice_ on 
 -- HLT_MASTER_TABLE also happen.
 CREATE OR REPLACE VIEW HLT_MK_TO_ST_VIEW (HMT_ID, HST_ID) AS
   SELECT * FROM HLT_MK_TO_ST_VIEW1
   UNION
   SELECT * FROM HLT_MK_TO_ST_VIEW2;
 
 -- This is a comparison view that replaces the UNION statement above and the
 -- two other tables in a single select statement for the two paths binded by
 -- an OR clause. This table should *not* be used normally. It is just a proof
 -- that, despite MySQL5 might sound to optimize it better in the overall case
 -- it doesn't help too much, the query takes longer to complete than the UNION
 -- query above. In my machine, this one takes a constant timing of 1.8 seconds
 -- against 0.20 seconds of the UNION query.
 CREATE OR REPLACE VIEW HLT_MK_TO_ST_VIEW3 (HMT_ID, HST_ID) AS
   SELECT HLT_MASTER_TABLE.HMT_ID, HLT_SETUP.HST_ID
   FROM HLT_MASTER_TABLE, -- Defines the trigger-menu to use
        HLT_TRIGGER_MENU, -- trigger menus
        HLT_TM_TO_TC, -- trigger-menu  -> trigger-chain
        HLT_TRIGGER_CHAIN, -- trigger chains
        HLT_SETUP -- trigger setup 
   WHERE (HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID IS NOT NULL AND
         HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID = HLT_TRIGGER_MENU.HTM_ID AND
         HLT_TRIGGER_MENU.HTM_ID = HLT_TM_TO_TC.HTM2TC_TRIGGER_MENU_ID AND
         HLT_TM_TO_TC.HTM2TC_TRIGGER_CHAIN_ID = HLT_TRIGGER_CHAIN.HTC_ID AND
         HLT_TRIGGER_CHAIN.HTC_SETUP_ID = HLT_SETUP.HST_ID) OR
         (HLT_MASTER_TABLE.HMT_FORCED_SETUP_ID = HLT_SETUP.HST_ID);
 
 
 
 -- This partial view shows the components from the relationship between
 -- the master table and the trigger elements
 -- OPTIMIZATION STATUS: queries with a fixed HMT_ID takes an amount of time
 -- which is independent of the table sizes involved. Queries without a master
 -- key require a full scan on HLT_MASTER_TABLE and HLT_TM_TO_TC for MySQL5.
 CREATE OR REPLACE VIEW HLT_MK_TO_CP_VIEW1 (HMT_ID, HCP_ID) AS
   SELECT HLT_MK_TO_TE_VIEW.HMT_ID, HLT_COMPONENT.HCP_ID
   FROM HLT_MK_TO_TE_VIEW,
        HLT_TE_TO_CP,
        HLT_COMPONENT
   WHERE HLT_MK_TO_TE_VIEW.HTE_ID = HLT_TE_TO_CP.HTE2CP_TRIGGER_ELEMENT_ID AND
         HLT_TE_TO_CP.HTE2CP_COMPONENT_ID = HLT_COMPONENT.HCP_ID;
 
 -- This partial view shows the components derived from the setups that need to
 -- be run.
 -- OPTIMIZATION STATUS: Queries without a fixed HMT_ID take a full scan on
 -- HLT_TM_TO_TC, twice on HLT_MASTER_TABLE and once on the HLT_MK_TO_ST_VIEW,
 -- which can be costly (depends on the DB sizes). Queries with a fixed master
 -- key don't change too much the scenario. This view requires careful thinking
 -- for optimization in the long term since it depends on a view that is based
 -- on a UNION statement (too difficult for the optimizer to digest). Despite
 -- all this, for the time being (17/Feb/2006), this view is still quite 
 -- performant, taking something like 0.18 seconds in my machine (pcuw32).
 CREATE OR REPLACE VIEW HLT_MK_TO_CP_VIEW2 (HMT_ID, HCP_ID) AS
   SELECT HLT_MK_TO_ST_VIEW.HMT_ID, HLT_COMPONENT.HCP_ID
   FROM HLT_MK_TO_ST_VIEW,
        HLT_ST_TO_CP,
        HLT_COMPONENT
   WHERE HLT_MK_TO_ST_VIEW.HST_ID = HLT_ST_TO_CP.HST2CP_SETUP_ID AND
         HLT_ST_TO_CP.HST2CP_COMPONENT_ID = HLT_COMPONENT.HCP_ID;
 
 -- Binds together all possible components that need to run for a masterkey
 -- OPTIMIZATION STATUS: Queries with or without a masterkey are dependent
 -- on results of HLT_MK_TO_CP_VIEW1 (optimal) and HLT_MK_TO_CP_VIEW2,
 -- which is not very optimized. So, if you fix the master key or not, that
 -- won't matter too much here. This needs work w.r.t. the previous views
 -- it depends on and because of potential other views as well, since it 
 -- introduces a UNION. For the time being, it executes in something like
 -- 0.25 seconds on my machine.
 CREATE OR REPLACE VIEW HLT_MK_TO_CP_VIEW (HMT_ID, HCP_ID) AS
   SELECT * FROM HLT_MK_TO_CP_VIEW1
   UNION
   SELECT * FROM HLT_MK_TO_CP_VIEW2;
 
 -- Generates a view where we get all services/tools/converters/algorithms from
 -- a master key. We use the component view above to get all possible setups
 -- involved in a run. The sources are also checked for consistency between what
 -- is available in the release pointed by the master table.
 -- OPTIMIZATION STATUS: This one is interesting! If you fix a master-key, at
 -- least for MySQL 5 (5.0.18 running now), you get a slower performance!
 -- Probably because it has to conduct so many full scans on derived tables, I
 -- really don't know. MySQL5's optimizer claims it again needs to perform full
 -- scan on 2 derived tables (oh my!) and a couple of times in HLT_MASTER_TABLE
 -- and HLT_TM_TO_TC. I do understand full scans on HLT_MASTER_TABLE, but not on
 -- the HLT_TM_TO_TC... Anyways, the query takes like 0.25 seconds w/o a master
 -- key and around 0.25 seconds w/o a key.
 -- One important observation: There are missing indexes for the jump between 
 -- HCP_NAME and HSO_NAME. This might speed up this query.
 --     CREATE OR REPLACE VIEW HLT_MK_TO_SO_VIEW (HMT_ID, HCP_ID,
 --                                               HCP_NAME, HCP_ALIAS, HCP_TOPALG,
 --                                               HSO_GAUDI_TYPE, HSO_ID) AS
 --       SELECT HLT_MASTER_TABLE.HMT_ID, 
 --              HLT_COMPONENT.HCP_ID, 
 --              HLT_COMPONENT.HCP_NAME,
 --              HLT_COMPONENT.HCP_ALIAS, 
 --              HLT_COMPONENT.HCP_TOPALG, 
 --              HLT_SOURCE.HSO_GAUDI_TYPE, 
 --              HLT_SOURCE.HSO_ID
 --       FROM HLT_MASTER_TABLE,
 --            HLT_MK_TO_CP_VIEW, -- components(s) for a master key
 --            HLT_COMPONENT, -- component table
 --            HLT_RE_TO_DL,  -- release -> dll
 --            HLT_DLL, -- dlls available in the release
 --            HLT_SO_TO_DL,  -- dll -> source
 --            HLT_SOURCE     -- source table
 --       WHERE HLT_MASTER_TABLE.HMT_ID = HLT_MK_TO_CP_VIEW.HMT_ID AND -- fix master key
 --             HLT_MK_TO_CP_VIEW.HCP_ID = HLT_COMPONENT.HCP_ID AND -- components that run
 --             HLT_MASTER_TABLE.HMT_RELEASE_ID = HLT_RE_TO_DL.HRE2DL_RELEASE_ID AND
 --             HLT_RE_TO_DL.HRE2DL_DLL_ID = HLT_DLL.HDL_ID AND
 --             HLT_DLL.HDL_ID = HLT_SO_TO_DL.HSO2DL_DLL_ID AND
 --             HLT_SO_TO_DL.HSO2DL_SOURCE_ID = HLT_SOURCE.HSO_ID AND
 --             HLT_SOURCE.HSO_NAME = HLT_COMPONENT.HCP_NAME;
 
 -- These are the forced DLL's that I have to force loading. This only depends
 -- on the setups used. This is only a partial view of required DLL's to run.
 -- [Joerg 17.06.2006: in case the release part of the HLT db is not loaded, this table
 -- contains all dll's that are needed to run this setup]
 -- OPTIMIZATION STATUS: MySQL5's optimizer claims it needs to do a full scan on
 -- the dependent view, plus a full scan on the HLT_FORCE_DLL table, another at
 -- HLT_TM_TO_TC and twice on HLT_MASTER_TABLE. The other queries are cross
 -- references which are easily digested by the optimizer. Takes like 0.20
 -- seconds on my machine with today's (17/Feb/2006) DB load.
 
 
 -- CREATE OR REPLACE VIEW HLT_MK_TO_FD_VIEW (HMT_ID, HDL_NAME) AS
 --   SELECT HLT_MK_TO_ST_VIEW.HMT_ID, 
 --          HLT_FORCE_DLL.HFD_DLL_NAME
 -- 
 --   FROM HLT_MK_TO_ST_VIEW, -- Defines the setups needed
 --        HLT_FD_TO_ST, -- dll's <-> setup's
 --        HLT_FORCE_DLL -- Defines the forced DLL identifiers
 --   WHERE HLT_MK_TO_ST_VIEW.HST_ID = HLT_FD_TO_ST.HFD2ST_SETUP_ID AND
 --         HLT_FD_TO_ST.HFD2ST_FORCE_DLL_ID = HLT_FORCE_DLL.HFD_ID;
  
 
 
 CREATE OR REPLACE VIEW HLT_MK_TO_FD_VIEW (HMT_ID, HDL_NAME) AS
   SELECT HLT_MK_TO_ST_VIEW.HMT_ID, 
          HLT_FORCE_DLL.HFD_DLL_NAME
 
   FROM HLT_MK_TO_ST_VIEW, -- Defines the setups needed
        HLT_FORCE_DLL -- Defines the forced DLL identifiers
   WHERE HLT_MK_TO_ST_VIEW.HST_ID = HLT_FORCE_DLL.hfd_setup_id;
 
 
 -- Generates a partial view with the required DLL's for components
 -- OPTIMIZATION STATUS: This is a big query! Two full scans on devirations and
 -- other generally bad stuff. It is much faster than before, but it may get
 -- stuck as we get more data in the DB.
 -- WARNING: we may need an index to do this faster here (HCP_NAME ->
 -- HSO_NAME mapping)
 --     CREATE OR REPLACE VIEW HLT_MK_TO_CD_VIEW (HMT_ID, HDL_NAME) AS
 --       SELECT HLT_MK_TO_CP_VIEW.HMT_ID, HLT_DLL.HDL_NAME
 --       FROM HLT_MK_TO_CP_VIEW,
 --            HLT_COMPONENT,
 --            HLT_SOURCE,
 --            HLT_SO_TO_DL, -- source -> dll mappings
 --            HLT_DLL
 --       WHERE HLT_MK_TO_CP_VIEW.HCP_ID = HLT_COMPONENT.HCP_ID AND
 --             HLT_COMPONENT.HCP_NAME = HLT_SOURCE.HSO_NAME AND
 --             HLT_SOURCE.HSO_ID = HLT_SO_TO_DL.HSO2DL_SOURCE_ID AND
 --             HLT_SO_TO_DL.HSO2DL_DLL_ID = HLT_DLL.HDL_ID;
         
 -- A combined view from the two views above
 -- OPTIMIZATION STATUS: This is even bigger than the query before! Three or
 -- four full scan on derived tables + HLT_SO_TO_DL, HLT_MASTER_TABLE three or
 -- four times and so on. The problem is upstairs and this still adds a union
 -- clause to the following views. Takes like 0.45 seconds now, with the present
 -- DB load (17/Feb/2006).
 --     CREATE OR REPLACE VIEW HLT_MK_TO_DL_VIEW1 (HMT_ID, HDL_NAME) AS
 --       SELECT * FROM HLT_MK_TO_FD_VIEW
 --       UNION
 --       SELECT * FROM HLT_MK_TO_CD_VIEW;
 
 -- A combined view of all DLL's needed for a given master key
 -- OPTIMIZATION STATUS: 55 joins are executed for this one... No need to say
 -- is one of the slowest views. Still, taking 0.65 seconds on my machine 
 -- (pcuw32) with the present DB load (17/Feb/2006).
 --     CREATE OR REPLACE VIEW HLT_MK_TO_DL_VIEW (HMT_ID, HDL_NAME, HDL_CMT_VERSION) AS
 --       SELECT HLT_MASTER_TABLE.HMT_ID, HLT_DLL.HDL_NAME, HLT_DLL.HDL_CMT_VERSION
 --       FROM HLT_MASTER_TABLE, -- unified master-key entry
 --            HLT_MK_TO_DL_VIEW1,
 --            HLT_RE_TO_DL,  -- release -> dll
 --            HLT_DLL -- DLL specifications
 --       WHERE HLT_MASTER_TABLE.HMT_ID = HLT_MK_TO_DL_VIEW1.HMT_ID AND
 --      HLT_MASTER_TABLE.HMT_RELEASE_ID = HLT_RE_TO_DL.HRE2DL_RELEASE_ID AND
 --             HLT_RE_TO_DL.HRE2DL_DLL_ID = HLT_DLL.HDL_ID AND
 --             HLT_DLL.HDL_NAME = HLT_MK_TO_DL_VIEW1.HDL_NAME;
 
 -- Generates a view with the required parameters to run services. This is
 -- extracted from the list of components that will run and are available with 
 -- correlating sources in the chosen release.
 -- OPTIMIZATION STATUS: I originally thought this was one of the worst views,
 -- bound to be the slowest, but I'm wrong! It is not so bad... Could be
 -- optimized though, if the dependent views are better written. Currently
 -- (17/Feb/2006), taking 0.25 seconds to execute in my machine (pcuw32). 
 -- MySQL5 has to perform 32 queries to get this done. Full scans being executed
 -- on HLT_MASTER_TABLE and HLT_TM_TO_TC, several times.
 CREATE OR REPLACE VIEW HLT_MK_TO_PA_VIEW (HMT_ID, HCP_ALIAS, HPA_NAME, 
                                           HPA_OP, HPA_VALUE) AS
   SELECT HLT_MK_TO_CP_VIEW.HMT_ID, 
          HLT_COMPONENT.HCP_ALIAS, 
          HLT_PARAMETER.HPA_NAME, 
          HLT_PARAMETER.HPA_OP, 
          HLT_PARAMETER.HPA_VALUE
   FROM HLT_MK_TO_CP_VIEW, -- get components that will run
        HLT_COMPONENT, -- the components
        HLT_CP_TO_PA, -- component -> parameter
        HLT_PARAMETER -- parameter specifications
   WHERE HLT_MK_TO_CP_VIEW.HCP_ID = HLT_CP_TO_PA.HCP2PA_COMPONENT_ID AND
         HLT_MK_TO_CP_VIEW.HCP_ID = HLT_COMPONENT.HCP_ID AND
         HLT_CP_TO_PA.HCP2PA_PARAMETER_ID = HLT_PARAMETER.HPA_ID;
 
 
 
 -- These three views allow to access the parameters of all componenents in a job
 -- for a given hlt_master_id and hlt_triggerlevel
 CREATE OR REPLACE VIEW HLT_MK_TL_TO_ST_VIEW (HMT_ID, HTC_L2EF, HST_ID) AS
   SELECT DISTINCT HLT_MASTER_TABLE.HMT_ID, HLT_TRIGGER_CHAIN.HTC_L2_OR_EF, HLT_SETUP.HST_ID
   FROM HLT_MASTER_TABLE,
        HLT_TM_TO_TC,
        HLT_TRIGGER_CHAIN,
        HLT_SETUP 
   WHERE HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID IS NOT NULL AND
         HLT_MASTER_TABLE.HMT_TRIGGER_MENU_ID = HLT_TM_TO_TC.HTM2TC_TRIGGER_MENU_ID AND
         HLT_TM_TO_TC.HTM2TC_TRIGGER_CHAIN_ID = HLT_TRIGGER_CHAIN.HTC_ID AND
         HLT_TRIGGER_CHAIN.HTC_SETUP_ID = HLT_SETUP.HST_ID;
 
 
 CREATE OR REPLACE VIEW HLT_MK_TL_TO_CP_VIEW (HMT_ID, HTC_L2EF, HCP_ID) AS
   SELECT HLT_MK_TL_TO_ST_VIEW.HMT_ID, HLT_MK_TL_TO_ST_VIEW.HTC_L2EF, HLT_COMPONENT.HCP_ID
   FROM HLT_MK_TL_TO_ST_VIEW,
        HLT_ST_TO_CP,
        HLT_COMPONENT
   WHERE HLT_MK_TL_TO_ST_VIEW.HST_ID = HLT_ST_TO_CP.HST2CP_SETUP_ID AND
         HLT_ST_TO_CP.HST2CP_COMPONENT_ID = HLT_COMPONENT.HCP_ID;
 
 
 CREATE OR REPLACE VIEW HLT_MK_TL_TO_PA_VIEW (HMT_ID, HTC_L2EF, HCP_ALIAS, HPA_NAME, 
                                           HPA_OP, HPA_VALUE) AS
   SELECT HLT_MK_TL_TO_CP_VIEW.HMT_ID,
          HLT_MK_TL_TO_CP_VIEW.HTC_L2EF,
          HLT_COMPONENT.HCP_ALIAS, 
          HLT_PARAMETER.HPA_NAME, 
          HLT_PARAMETER.HPA_OP, 
          HLT_PARAMETER.HPA_VALUE
   FROM HLT_MK_TL_TO_CP_VIEW, -- get components that will run
        HLT_COMPONENT, -- the components
        HLT_CP_TO_PA, -- component -> parameter
        HLT_PARAMETER -- parameter specifications
   WHERE HLT_MK_TL_TO_CP_VIEW.HCP_ID = HLT_CP_TO_PA.HCP2PA_COMPONENT_ID AND
         HLT_MK_TL_TO_CP_VIEW.HCP_ID = HLT_COMPONENT.HCP_ID AND
         HLT_CP_TO_PA.HCP2PA_PARAMETER_ID = HLT_PARAMETER.HPA_ID;

This page was automatically generated by the LXR engine.