-- Hello emacs, please make this buffer -*- sql -*-
 -- Andre DOS ANJOS <Andre.dos.Anjos@cern.ch>
 
 -- $Author: simon $
 -- $Revision: 1.5 $
 -- $Date: 2007/04/26 13:15:15 $
 
 -- Creates a set of stored procedures that help us to introduce new lines in
 -- HLT schema, by looking to all the necessary constraints of the operational
 -- model. 
 -- WARNING: The user must have the CREATE/ALTER/CALL ROUTINE permissions to 
 -- create, manage and execute these.
 
 -- Oracle annotations (may be very incomplete):
 -- 1. The drop statement does not have an IF EXISTS clause associated to it in 
 -- Oracle, you must use something like CREATE OR ALTER FUNCTION...
 
 -- 2. Oracle requires the keyword 'in' between the input parameter declaration 
 -- and the type. For MySQL that is not accepted.
 
 -- 3. In the end of the function declaration, Oracle uses 'RETURN' instead of
 -- 'RETURNS' as used by MySQL
 
 -- 4. Oracle needs a 'AS' keyword before the 'BEGIN' keyword starting a
 -- function body
 
 -- 5. Oracle terminates function declarations with the character '/'. So, to
 -- make MySQL more Oracle friendly we choose the same delimiter. For an Oracle
 -- insertion you have to comment out the following line, probably.
 
 -- 6. If you remove the delimiter clause, remember to reset the occurrences of
 -- the delimiters along the code
 
 -- 7. It is not clear how to declare temporary variables in Oracle. A hint is
 -- to use the keyword DECLARE to declare all variables in one shot. E.g.:
 -- DECLARE
 --   RETVAL number(10) default NULL;
 --   VERSION number(11,5);
 -- According to the manual, this has to come before 'BEGIN' keyword.
 
 -- 8. WHILE loops should not be ended by END WHILE, but by END LOOP. This
 -- happens because the correct syntax for while loops in Oracle should be
 -- something like WHILE <condition> LOOP statements; END LOOP;
 
 -- 9. The stopping condition for cursors in Oracle is a bit different then that
 -- in MySQL. A sentence like 'exit when cursor%NOTFOUND' should be sufficient to
 -- do the trick in Oracle. MySQL needs a bit more.
 
 -- ##########################################################################
 -- Start of the code
 -- ##########################################################################
 
 -- Makes MySQL ignore the ';' as an end of statement, so we can input
 -- multi-statement constructions like functions and procedures.
 delimiter /
 
 -- #####################################
 -- Stored procedures for job insertion #
 -- #####################################
 
 -- Creates a set of temporary tables needed for the job insertion
 drop procedure if exists HLT_BOOT_SETUP /
 create procedure HLT_BOOT_SETUP ()
   begin
 
     create temporary table if not exists TMP_PAR (
       ID int(10) not null,
       constraint ID_PK primary key (ID)
     );
     truncate table TMP_PAR;
 
     create temporary table if not exists TMP_COMP (
       ID int(10) not null,
       constraint ID_PK primary key (ID)
     );
     truncate table TMP_COMP;
 
     create temporary table if not exists TMP_FORCE_DLL (
       NAME varchar(50) not null,
       constraint NAME_PK primary key (NAME)
     );
     truncate table TMP_FORCE_DLL;
   end;
 /
 
 -- Destroyes the set of temporary tables needed for the job insertion
 drop procedure if exists HLT_SHUTDOWN_SETUP /
 create procedure HLT_SHUTDOWN_SETUP ()
   begin
     drop table if exists TMP_PAR;
     drop table if exists TMP_COMP;
     drop table if exists TMP_FORCE_DLL;
   end;
 /
 
 -- Allows the user to insert a property in the HLT_PARAMETER table. This
 -- function will respect the uniqueness constraint on this table, searching if
 -- there are no other properties already inserted with the same values for all
 -- attributes. If the search is successful (there is already a value with 
 -- *exactly* the same properties) the insertion is cancelled and the identifier
 -- of such an entry is returned. On the contrary case, a new version of the 
 -- property is inserted and the identifier of that column is returned instead.
 drop function if exists HLT_INSERT_PARAMETER /
 create function HLT_INSERT_PARAMETER (NAME      varchar(50),
                                       OP        varchar(30),
                                       VALUE     varchar(40000)) returns int(10)
   begin
     declare RETVAL int(10) default NULL;
 
     select HPA_ID into RETVAL from HLT_PARAMETER where 
       binary HPA_NAME = NAME and HPA_OP = OP and HPA_VALUE = VALUE;
 
     if RETVAL is null then 
       insert into HLT_PARAMETER 
         (HPA_NAME, HPA_OP, HPA_VALUE) values (NAME, OP, VALUE);
       select HPA_ID into RETVAL from HLT_PARAMETER where 
               binary HPA_NAME = NAME and HPA_OP = OP and HPA_VALUE = VALUE;
     end if;
     insert into TMP_PAR (ID) values (RETVAL);
     return(RETVAL);
   end;
 /
 
 -- Resets the temporary parameter table
 drop procedure if exists HLT_RESET_PARAMETER /
 create procedure HLT_RESET_PARAMETER ()
   begin
     delete from TMP_PAR;
   end;
 /
 
 -- Returns 0 if I find at least one line in the HLT_CP_TO_PA table that
 -- contains both identifiers or 1 if not.
 drop function if exists HLT_SEEK_CP2PA /
 create function HLT_SEEK_CP2PA (COMP_ID int(10),
                                 PAR_ID int(10)) returns int(1)
   begin
     declare TEST int(10);
     select max(HLT_CP_TO_PA.HCP2PA_ID) into TEST 
       from HLT_CP_TO_PA where COMP_ID = HLT_CP_TO_PA.HCP2PA_COMPONENT_ID and
                               HLT_CP_TO_PA.HCP2PA_PARAMETER_ID = PAR_ID;
     if TEST is not null then 
       return(1); -- found something
     end if;
     return(0); -- didn't find a match
   end;
 /
 
 -- Returns the number of parameters a given registered component has.
 drop function if exists HLT_COUNT_CP2PA /
 create function HLT_COUNT_CP2PA (COMP_ID int(10)) returns int(10)
   begin
     declare TEST int(10);
     select count(HLT_CP_TO_PA.HCP2PA_ID) into TEST
       from HLT_CP_TO_PA where COMP_ID = HLT_CP_TO_PA.HCP2PA_COMPONENT_ID;
   return(TEST);
   end;
 /
 
 -- Returns the identifier of a HLT_COMPONENT row that contains the required
 -- component entry. This entry corresponds to either a newly created entry in
 -- the table or to one that matches all the required values for the component
 -- that one wishes to insert, w.r.t. the relationship between the component and
 -- its properties. This is how it works:
 -- 1) You insert the properties that you want to have on your component in the
 -- HLT_PARAMETER table using the HLT_INSERT_PARAMETER function and store, for
 -- each entry, the row ID returned by that function into a temporary table
 -- named TMP_PAR that contains a single column named ID that defines all the
 -- parameter identifiers you have inserted. The single column should also be
 -- the primary key of this table.
 -- 2) I'll scan this table to see if I can find an component that already has
 -- the same relationships, and has the same name/alias. If that is the case,
 -- I'll not insert the new component, but return its unique ID. Otherwise, I'll
 -- insert a new component/alias in the HLT_COMPONENT table and build the
 -- relationships with the required parameters.
 -- 3) Upon return, this function will truncate the table TMP_PAR so you can
 -- re-use it immediately
 drop function if exists HLT_INSERT_COMPONENT /
 create function HLT_INSERT_COMPONENT (NAME varchar(150),
                                       ALIAS varchar(150),
                                       TOPALG int(1)) returns int(10)
   begin
     declare TEST int(10) DEFAULT 0;
     declare NPAR int(10);
     declare COMP_ID int(10);
     declare PAR_ID int(10);
     declare VERSION decimal(11,5);
 
     select count(TMP_PAR.ID) into NPAR from TMP_PAR;
     -- for every version of the given name/alias pair; go backwards
     -- since it is potentially faster.
     select max(HLT_COMPONENT.HCP_ID) into COMP_ID from HLT_COMPONENT
       where HLT_COMPONENT.HCP_NAME = NAME and
             HLT_COMPONENT.HCP_ALIAS = ALIAS and
             HLT_COMPONENT.HCP_TOPALG = TOPALG;
     while COMP_ID is not null do
       -- for all rows of TMP_PAR, check if we have a match, accumulates in TEST
       select 0 into TEST;
       select min(TMP_PAR.ID) into PAR_ID from TMP_PAR;
       while PAR_ID is not null do
         select TEST + HLT_SEEK_CP2PA(COMP_ID,PAR_ID) into TEST;
         select min(TMP_PAR.ID) into PAR_ID from TMP_PAR 
           where TMP_PAR.ID > PAR_ID;
       end while;
       -- if TEST is NPAR and the total number is the same, we get a match
       if TEST = NPAR and HLT_COUNT_CP2PA(COMP_ID) = NPAR then
         delete from TMP_PAR; -- cleanup
         insert into TMP_COMP (ID) values (COMP_ID);
         return COMP_ID;
       end if;
       -- in the case you get here, you have to continue testing
       select max(HLT_COMPONENT.HCP_ID) into COMP_ID from HLT_COMPONENT
         where HLT_COMPONENT.HCP_NAME = NAME and
               HLT_COMPONENT.HCP_ALIAS = ALIAS and
               HLT_COMPONENT.HCP_TOPALG = TOPALG and
               HLT_COMPONENT.HCP_ID < COMP_ID;
     end while;
 
     -- in the case we get here, all tests have failed and you don't have a
     -- component that have all the matches you need. Now we proceed like for
     -- a parameter in HLT_INSERT_PARAMETER() and insert a brand new component
     -- with all the parameters defined at the TMP_PAR table
     select max(HLT_COMPONENT.HCP_VERSION) into VERSION from HLT_COMPONENT
       where HLT_COMPONENT.HCP_NAME = NAME and
             HLT_COMPONENT.HCP_ALIAS = ALIAS;
     select if(VERSION is null, 1.0, floor(VERSION) + 1.0) into VERSION;
     insert into HLT_COMPONENT
         (HCP_NAME, HCP_VERSION, HCP_ALIAS, HCP_TOPALG) values
         (NAME, VERSION, ALIAS, TOPALG);
     select max(HCP_ID) into COMP_ID from HLT_COMPONENT where 
         HCP_VERSION = VERSION and HCP_ALIAS = ALIAS; -- UNIQUE CONSTRAINT
 
     -- now we just create all the links in the HLT_CP_TO_PA table
     select min(TMP_PAR.ID) into PAR_ID from TMP_PAR;
     while PAR_ID is not null do
       insert into HLT_CP_TO_PA (HCP2PA_COMPONENT_ID, HCP2PA_PARAMETER_ID)
         values (COMP_ID, PAR_ID);
       select min(TMP_PAR.ID) into PAR_ID from TMP_PAR 
         where TMP_PAR.ID > PAR_ID;
     end while;
 
     -- and return gracefully
     delete from TMP_PAR; -- cleanup
     insert into TMP_COMP (ID) values (COMP_ID);
     return(COMP_ID);
   end;
 /
 
 -- Resets the temporary component table
 drop procedure if exists HLT_RESET_COMPONENT /
 create procedure HLT_RESET_COMPONENT ()
   begin
     delete from TMP_COMP;
   end;
 /
 
 -- Allows the insertion of DLLs that are supposed to be forced into a setup,
 -- hidding from the user the names of the temporary tables needed to do that.
 drop procedure if exists HLT_NEW_FORCE_DLL /
 create procedure HLT_NEW_FORCE_DLL (in NAME varchar(50))
   begin
     declare RETVAL int(10);
     insert into TMP_FORCE_DLL (NAME) values (NAME);
   end;
 /
 
 -- Resets the temporary parameter table
 drop procedure if exists HLT_RESET_FORCE_DLL /
 create procedure HLT_RESET_FORCE_DLL ()
   begin
     delete from TMP_FORCE_DLL;
   end;
 /
 
 -- Returns 0 if I find at least one line in the HLT_ST_TO_CP table that
 -- contains both identifiers or 1 if not.
 drop function if exists HLT_SEEK_ST2CP /
 create function HLT_SEEK_ST2CP (SETUP_ID int(10),
                                 COMP_ID int(10)) returns int(1)
   begin
     declare TEST int(10);
     select max(HLT_ST_TO_CP.HST2CP_ID) into TEST 
       from HLT_ST_TO_CP where COMP_ID = HLT_ST_TO_CP.HST2CP_COMPONENT_ID and
                               HLT_ST_TO_CP.HST2CP_SETUP_ID = SETUP_ID;
     if TEST is not null then 
       return(1); -- found something
     end if;
     return(0); -- didn't find a match
   end;
 /
 
 -- Returns the number of components in a given setup
 drop function if exists HLT_COUNT_ST2CP /
 create function HLT_COUNT_ST2CP (SETUP_ID int(10)) returns int(10)
   begin
     declare TEST int(10);
     select count(HLT_ST_TO_CP.HST2CP_ID) into TEST 
       from HLT_ST_TO_CP where HLT_ST_TO_CP.HST2CP_SETUP_ID = SETUP_ID;
     return(TEST);
   end;
 /
 
 -- Returns 0 if I find at least one line in the HLT_FORCE_DLL table that 
 -- points to the setup defined by the first parameter. Returns 1 otherwise.
 drop function if exists HLT_SEEK_ST2FD /
 create function HLT_SEEK_ST2FD (SETUP_ID int(10),
                                 FDLL varchar(50)) returns int(1)
   begin
     declare TEST int(10);
     select max(HLT_FORCE_DLL.HFD_ID) into TEST
       from HLT_FORCE_DLL where FDLL = HLT_FORCE_DLL.HFD_DLL_NAME and
                                SETUP_ID = HLT_FORCE_DLL.HFD_SETUP_ID;
     if TEST is not null then
       return(1);
     end if;
     return(0);
   end;
 / 
 
 -- Counts the number of forced DLLs in a setup
 drop function if exists HLT_COUNT_ST2FD /
 create function HLT_COUNT_ST2FD (SETUP_ID int(10)) returns int(10)
   begin
     declare TEST int(10);
     select count(HLT_FORCE_DLL.HFD_ID) into TEST
       from HLT_FORCE_DLL where SETUP_ID = HLT_FORCE_DLL.HFD_SETUP_ID;
     return(TEST);
   end;
 / 
 
 -- Returns the identifier of a HLT_SETUP row that contains the required
 -- setup entry. This entry corresponds to either a newly created entry in
 -- the table or to one that matches all the required values for the setup
 -- that one wishes to insert, w.r.t. the relationship between the setup, 
 -- its components and forced DLL's. This is how it works:
 -- 1) You insert the components that you want to have on your setup in the
 -- HLT_COMPONENT table using the HLT_INSERT_COMPONENT function and store, for
 -- each entry, the row ID returned by that function into a temporary table
 -- named TMP_COMP that contains a single column named ID that defines all the
 -- component identifiers you have inserted. The single column should also be
 -- the primary key of this table. Also, create a temporary table holding the
 -- names of the forced DLLs you want to have in this setup, in a single
 -- column. Call it TMP_FORCE_DLL.
 -- 2) I'll scan these tables to see if I can find a setup that already has
 -- the same relationships, and has the same name. If that is the case,
 -- I'll not insert the new setup, but return its unique ID. Otherwise, I'll
 -- insert a new setup in the HLT_SETUP table and build the relationships
 -- between this table and its components and equally insert new entries in the
 -- HLT_FORCE_DLL table pointing to the current setup.
 -- 3) Upon return, this function will truncate tables TMP_FORCE_DLL and
 -- TMP_COMP so you can re-use them immediately
 --
 -- To-do: For future upgrades one may consider that the TMP_FORCE_DLL table
 -- does not contain a unique list of DLLs that have to forced, but a complete
 -- DLL list as removed from the original job. In this scenario, before
 -- inserting the entries in HLT_FORCE_DLL table, one would have to calculate
 -- which of those DLLs would not get automatically inserted into the job by the
 -- usage of the components in the setup. The list of DLLs that would go into the
 -- HLT_FORCE_DLL table would be the ones in TMP_FORCE_DLL subtracted from the
 -- ones brought in automatically by the components in the setup.
 drop function if exists HLT_INSERT_SETUP /
 create function HLT_INSERT_SETUP (NAME varchar(50)) returns int(10)
   begin
     declare TEST_COMP int(20) DEFAULT 0;
     declare TEST_FDLL int(20) DEFAULT 0;
     declare NCOMP int(10);
     declare NFDLL int(10);
     declare SETUP_ID int(10);
     declare COMP_ID int(10);
     declare FDLL varchar(50);
     declare VERSION decimal(11,5);
 
     select count(TMP_COMP.ID) into NCOMP from TMP_COMP;
     select count(TMP_FORCE_DLL.NAME) into NFDLL from TMP_FORCE_DLL;
 
     -- for every version of the given name; go backwards
     -- since it is potentially faster.
     select max(HLT_SETUP.HST_ID) into SETUP_ID from HLT_SETUP
       where HLT_SETUP.HST_NAME = NAME;
     while SETUP_ID is not null do
       -- for all rows of TMP_COMP, check if we have a match, accumulates in TEST
       select 0 into TEST_COMP;
       select min(TMP_COMP.ID) into COMP_ID from TMP_COMP;
       while COMP_ID is not null do
         select TEST_COMP + HLT_SEEK_ST2CP(SETUP_ID,COMP_ID) into TEST_COMP;
         select min(TMP_COMP.ID) into COMP_ID from TMP_COMP 
           where TMP_COMP.ID > COMP_ID;
       end while;
       -- for all rows of TMP_FORCE_DLL, check if we have a match
       select 0 into TEST_FDLL;
       select min(TMP_FORCE_DLL.NAME) into FDLL from TMP_FORCE_DLL;
       while FDLL is not null do
         select TEST_FDLL + HLT_SEEK_ST2FD(SETUP_ID,FDLL) into TEST_FDLL;
         select min(TMP_FORCE_DLL.NAME) into FDLL from TMP_FORCE_DLL
           where TMP_FORCE_DLL.NAME > FDLL;
       end while;
 
       -- if TESTs fit and the countings are correct, we get a match!, return
       -- this ID 
       if NCOMP = TEST_COMP and 
          NFDLL = TEST_FDLL and 
          NCOMP = HLT_COUNT_ST2CP(SETUP_ID) and
          NFDLL = HLT_COUNT_ST2FD(SETUP_ID) then
         delete from TMP_COMP; -- cleanup
         delete from TMP_FORCE_DLL; -- cleanup
         return SETUP_ID;
       end if;
 
       -- in the case you get here, you have to continue testing
       select max(HLT_SETUP.HST_ID) into SETUP_ID from HLT_SETUP
         where HLT_SETUP.HST_NAME = NAME and 
               HLT_SETUP.HST_ID < SETUP_ID; -- get the previous one
     end while;
 
     -- in the case we get here, all tests have failed and you don't have a
     -- component that have all the matches you need. Now we proceed like for
     -- a component in HLT_INSERT_COMPONENT() and insert a brand new setup
     -- with all the components and forced DLLs defined at the temporary tables
     select max(HLT_SETUP.HST_VERSION) into VERSION from HLT_SETUP
       where HLT_SETUP.HST_NAME = NAME;
     select if(VERSION is null, 1.0, floor(VERSION) + 1.0) into VERSION;
     insert into HLT_SETUP (HST_NAME, HST_VERSION) values (NAME, VERSION);
     select max(HST_ID) into SETUP_ID from HLT_SETUP where 
       HST_VERSION = VERSION and HST_NAME = NAME; -- UNIQUE CONSTRAINT
 
     -- now we just create all the links in the HLT_ST_TO_CP table
     select min(TMP_COMP.ID) into COMP_ID from TMP_COMP;
     while COMP_ID is not null do
       insert into HLT_ST_TO_CP (HST2CP_SETUP_ID, HST2CP_COMPONENT_ID)
         values (SETUP_ID, COMP_ID);
       select min(TMP_COMP.ID) into COMP_ID from TMP_COMP 
         where TMP_COMP.ID > COMP_ID;
     end while;
 
 
     -- and the entries in the HLT_FORCE_DLL table.
     select min(TMP_FORCE_DLL.NAME) into FDLL from TMP_FORCE_DLL;
     while FDLL is not null do
       insert into HLT_FORCE_DLL (HFD_DLL_NAME, HFD_SETUP_ID) values
         (FDLL, SETUP_ID);
       select min(TMP_FORCE_DLL.NAME) into FDLL from TMP_FORCE_DLL
         where TMP_FORCE_DLL.NAME > FDLL;
     end while;
 
 
 
     -- and return gracefully
     delete from TMP_COMP; -- cleanup
     delete from TMP_FORCE_DLL; -- cleanup
     return(SETUP_ID);
   end;
 /
 
 -- #########################################
 -- Stored procedures for release insertion #
 -- #########################################
 
 -- Creates a set of temporary tables needed for the release insertion
 drop procedure if exists HLT_BOOT_RELEASE /
 create procedure HLT_BOOT_RELEASE ()
   begin
 
     create temporary table if not exists TMP_PROP (
       ID int(10) not null,
       constraint ID_PK primary key (ID)
     );
     truncate table TMP_PROP;
 
     create temporary table if not exists TMP_SOURCE (
       ID int(10) not null,
       constraint ID_PK primary key (ID)
     );
     truncate table TMP_SOURCE;
 
     create temporary table if not exists TMP_DLL (
       ID int(10) not null,
       constraint ID_PK primary key (ID)
     );
     truncate table TMP_DLL;
 
   end;
 /
 
 -- Destroyes the set of temporary tables needed for the release insertion
 drop procedure if exists HLT_SHUTDOWN_RELEASE /
 create procedure HLT_SHUTDOWN_RELEASE ()
   begin
     drop table if exists TMP_PROP;
     drop table if exists TMP_SOURCE;
     drop table if exists TMP_DLL;
   end;
 /
 
 -- Allows the user to insert a property in the HLT_PROPERTY table. This
 -- function will respect the uniqueness constraint on this table, searching if
 -- there are no other properties already inserted with the same values for all
 -- attributes. If the search is successful (there is already a value with 
 -- *exactly* the same properties) the insertion is cancelled and the identifier
 -- of such an entry is returned. On the contrary case, a new version of the 
 -- property is inserted and the identifier of that column is returned instead.
 drop function if exists HLT_INSERT_PROPERTY /
 create function HLT_INSERT_PROPERTY (NAME      varchar(50),
                                      PROP_TYPE varchar(50),
                                      VALUE     varchar(4000)) returns int(10)
   begin
     declare RETVAL int(10) default NULL;
     declare VERSION decimal(11,5);
 
     select HPY_ID into RETVAL from HLT_PROPERTY where 
       binary HPY_NAME = NAME and HPY_TYPE = PROP_TYPE and HPY_VALUE = VALUE;
 
     if RETVAL is null then 
       insert into HLT_PROPERTY 
         (HPY_NAME, HPY_TYPE, HPY_VALUE) values (NAME, PROP_TYPE, VALUE);
       select max(HPY_ID) into RETVAL from HLT_PROPERTY where 
         binary HPY_NAME = NAME and HPY_TYPE = PROP_TYPE and HPY_VALUE = VALUE;
     end if;
     insert into TMP_PROP (ID) values (RETVAL);
     return(RETVAL);
   end;
 /
 
 -- Resets the temporary property table
 drop procedure if exists HLT_RESET_PROPERTY /
 create procedure HLT_RESET_PROPERTY ()
   begin
     delete from TMP_PROP;
   end;
 /
 
 -- Returns 0 if I find at least one line in the HLT_SO_TO_PY table that
 -- contains both identifiers or 1 if not.
 drop function if exists HLT_SEEK_SO2PY /
 create function HLT_SEEK_SO2PY (SOURCE_ID int(10),
                                 PROPERTY_ID int(10)) returns int(1)
   begin
     declare TEST int(10);
     select max(HLT_SO_TO_PY.HSO2PY_ID) into TEST 
       from HLT_SO_TO_PY where SOURCE_ID = HLT_SO_TO_PY.HSO2PY_SOURCE_ID and
                               HLT_SO_TO_PY.HSO2PY_PROPERTY_ID = PROPERTY_ID;
     if TEST is not null then 
       return(1); -- found something
     end if;
     return(0); -- didn't find a match
   end;
 /
 
 -- Returns the number of properties a given registered source has.
 drop function if exists HLT_COUNT_SO2PY /
 create function HLT_COUNT_SO2PY (SOURCE_ID int(10)) returns int(10)
   begin
     declare TEST int(10);
     select count(HLT_SO_TO_PY.HSO2PY_ID) into TEST
       from HLT_SO_TO_PY where SOURCE_ID = HLT_SO_TO_PY.HSO2PY_SOURCE_ID;
   return(TEST);
   end;
 /
 
 -- Returns the identifier of a HLT_SOURCE row that contains the required
 -- component entry. This entry corresponds to either a newly created entry in
 -- the table or to one that matches all the required values for the source
 -- that one wishes to insert, w.r.t. the relationship between the source and
 -- its properties. This is how it works similarly to HLT_INSERT_COMPONENT()
 drop function if exists HLT_INSERT_SOURCE /
 create function HLT_INSERT_SOURCE (NAME varchar(50),
                                    GAUDI_TYPE varchar(50)) returns int(10)
   begin
     declare TEST int(10) DEFAULT 0;
     declare NPROP int(10);
     declare SOURCE_ID int(10);
     declare PROP_ID int(10);
 
     select count(TMP_PROP.ID) into NPROP from TMP_PROP;
     -- for every version of the given name/alias pair; go backwards
     -- since it is potentially faster.
     select max(HLT_SOURCE.HSO_ID) into SOURCE_ID from HLT_SOURCE
       where HLT_SOURCE.HSO_NAME = NAME and
             HLT_SOURCE.HSO_GAUDI_TYPE = GAUDI_TYPE;
     while SOURCE_ID is not null do
       -- for all rows of TMP_PROP, check if we have a match, accumulates in TEST
       select 0 into TEST;
       select min(TMP_PROP.ID) into PROP_ID from TMP_PROP;
       while PROP_ID is not null do
         select TEST + HLT_SEEK_SO2PY(SOURCE_ID,PROP_ID) into TEST;
         select min(TMP_PROP.ID) into PROP_ID from TMP_PROP 
           where TMP_PROP.ID > PROP_ID;
       end while;
       -- if TEST is NPROP and the total number is the same, we get a match
       if TEST = NPROP and HLT_COUNT_SO2PY(SOURCE_ID) = NPROP then
         delete from TMP_PROP; -- cleanup
         insert into TMP_SOURCE (ID) values (SOURCE_ID);
         return SOURCE_ID;
       end if;
       -- in the case you get here, you have to continue testing
       select max(HLT_SOURCE.HSO_ID) into SOURCE_ID from HLT_SOURCE
         where HLT_SOURCE.HSO_NAME = NAME and
               HLT_SOURCE.HSO_GAUDI_TYPE = GAUDI_TYPE and
               HLT_SOURCE.HSO_ID < SOURCE_ID;
     end while;
 
     -- in the case we get here, all tests have failed and you don't have a
     -- source that have all the matches you need. Now we proceed like for
     -- a property in HLT_INSERT_PROPERTY() and insert a brand new source
     -- with all the parameters defined at the TMP_PROP table
     insert into HLT_SOURCE (HSO_NAME, HSO_GAUDI_TYPE) values (NAME, GAUDI_TYPE);
     select max(HSO_ID) into SOURCE_ID from HLT_SOURCE where 
       HSO_NAME = NAME and HSO_GAUDI_TYPE = GAUDI_TYPE;
 
     -- now we just create all the links in the HLT_SO_TO_PY table
     select min(TMP_PROP.ID) into PROP_ID from TMP_PROP;
     while PROP_ID is not null do
       insert into HLT_SO_TO_PY (HSO2PY_SOURCE_ID, HSO2PY_PROPERTY_ID)
         values (SOURCE_ID, PROP_ID);
       select min(TMP_PROP.ID) into PROP_ID from TMP_PROP 
         where TMP_PROP.ID > PROP_ID;
     end while;
 
     -- and return gracefully
     delete from TMP_PROP; -- cleanup
     insert into TMP_SOURCE (ID) values (SOURCE_ID);
     return(SOURCE_ID);
   end;
 /
 
 -- Resets the temporary source table
 drop procedure if exists HLT_RESET_SOURCE /
 create procedure HLT_RESET_SOURCE ()
   begin
     delete from TMP_SOURCE;
   end;
 /
 
 -- Returns 0 if I find at least one line in the HLT_SO_TO_DL table that
 -- contains both identifiers or 1 if not.
 drop function if exists HLT_SEEK_SO2DL /
 create function HLT_SEEK_SO2DL (DLL_ID int(10),
                                 SOURCE_ID int(10)) returns int(1)
   begin
     declare TEST int(10);
     select max(HLT_SO_TO_DL.HSO2DL_ID) into TEST 
       from HLT_SO_TO_DL where DLL_ID = HLT_SO_TO_DL.HSO2DL_DLL_ID and
                               HLT_SO_TO_DL.HSO2DL_SOURCE_ID = SOURCE_ID;
     if TEST is not null then 
       return(1); -- found something
     end if;
     return(0); -- didn't find a match
   end;
 /
 
 -- Returns the number of sources a given registered dll has.
 drop function if exists HLT_COUNT_SO2DL /
 create function HLT_COUNT_SO2DL (DLL_ID int(10)) returns int(10)
   begin
     declare TEST int(10);
     select count(HLT_SO_TO_DL.HSO2DL_ID) into TEST
       from HLT_SO_TO_DL where DLL_ID = HLT_SO_TO_DL.HSO2DL_DLL_ID;
   return(TEST);
   end;
 /
 
 -- Returns the identifier of a HLT_DLL row that contains the required
 -- component entry. This entry corresponds to either a newly created entry in
 -- the table or to one that matches all the required values for the dll
 -- that one wishes to insert, w.r.t. the relationship between the dll and
 -- its properties. This is works similarly to HLT_INSERT_SOURCE().
 drop function if exists HLT_INSERT_DLL /
 create function HLT_INSERT_DLL (NAME varchar(50),
                                 PACKAGE varchar(50),
                                 CMT_VERSION varchar(60)) returns int(10)
   begin
     declare TEST int(10) DEFAULT 0;
     declare NSOURCE int(10);
     declare DLL_ID int(10);
     declare SOURCE_ID int(10);
 
     select count(TMP_SOURCE.ID) into NSOURCE from TMP_SOURCE;
 
     -- the following respects an uniqueness condition, so no loop required!
     select HLT_DLL.HDL_ID into DLL_ID from HLT_DLL where 
       HDL_NAME = NAME and HDL_PACKAGE = PACKAGE and HDL_CMT_VERSION = CMT_VERSION;
     if DLL_ID is not null then
       -- for all rows of TMP_SOURCE, check if we have a match, accumulates 
       select 0 into TEST;
       select min(TMP_SOURCE.ID) into SOURCE_ID from TMP_SOURCE;
       while SOURCE_ID is not null do
         select TEST + HLT_SEEK_SO2DL(DLL_ID,SOURCE_ID) into TEST;
         select min(TMP_SOURCE.ID) into SOURCE_ID from TMP_SOURCE
           where TMP_SOURCE.ID > SOURCE_ID;
       end while;
       -- if TEST is NSOURCE and the total number is the same, we get a match
       if TEST = NSOURCE and HLT_COUNT_SO2DL(DLL_ID) = NSOURCE then
         delete from TMP_SOURCE; -- cleanup
         insert into TMP_DLL (ID) values (DLL_ID);
         return DLL_ID;
       end if;
     end if;
 
     -- in the case we get here, all tests have failed and you don't have a
     -- dll that have all the matches you need. Now we proceed like for
     -- a source in HLT_INSERT_SOURCE() and insert a brand new dll
     -- with all the sources defined at the TMP_SOURCE table
     insert into HLT_DLL (HDL_NAME, HDL_PACKAGE, HDL_CMT_VERSION)
       values (NAME, PACKAGE, CMT_VERSION);
     select HDL_ID into DLL_ID from HLT_DLL where 
       HDL_NAME = NAME and HDL_PACKAGE = PACKAGE and HDL_CMT_VERSION = CMT_VERSION;
 
     -- now we just create all the links in the HLT_SO_TO_DL table
     select min(TMP_SOURCE.ID) into SOURCE_ID from TMP_SOURCE;
     while SOURCE_ID is not null do
       insert into HLT_SO_TO_DL (HSO2DL_DLL_ID, HSO2DL_SOURCE_ID)
         values (DLL_ID, SOURCE_ID);
       select min(TMP_SOURCE.ID) into SOURCE_ID from TMP_SOURCE 
         where TMP_SOURCE.ID > SOURCE_ID;
     end while;
 
     -- and return gracefully
     delete from TMP_SOURCE; -- cleanup
     insert into TMP_DLL (ID) values (DLL_ID);
     return(DLL_ID);
   end;
 /
 
 -- Resets the temporary dll table
 drop procedure if exists HLT_RESET_DLL /
 create procedure HLT_RESET_DLL ()
   begin
     delete from TMP_DLL;
   end;
 /
 
 -- Returns 0 if I find at least one line in the HLT_RE_TO_DL table that
 -- contains both identifiers or 1 if not.
 --drop function if exists HLT_SEEK_RE2DL /
 --create function HLT_SEEK_RE2DL (RELEASE_ID int(10),
 --                                DLL_ID int(10)) returns int(1)
 --  begin
 --    declare TEST int(10);
 --    select max(HLT_RE_TO_DL.HRE2DL_ID) into TEST 
 --      from HLT_RE_TO_DL where RELEASE_ID = HLT_RE_TO_DL.HRE2DL_RELEASE_ID and
 --                              HLT_RE_TO_DL.HRE2DL_DLL_ID = DLL_ID;
 --    if TEST is not null then 
 --      return(1); -- found something
 --    end if;
 --    return(0); -- didn't find a match
 --  end;
 --/
 --
 -- Returns the number of dlls a given registered release has.
 --drop function if exists HLT_COUNT_RE2DL /
 --create function HLT_COUNT_RE2DL (RELEASE_ID int(10)) returns int(10)
 --  begin
 --    declare TEST int(10);
 --    select count(HLT_RE_TO_DL.HRE2DL_ID) into TEST
  --     from HLT_RE_TO_DL where RELEASE_ID = HLT_RE_TO_DL.HRE2DL_RELEASE_ID;
 --  return(TEST);
 --  end;
 --/
 
 -- Returns the identifier of a HLT_RELEASE row that contains the required
 -- dll entries. This entry corresponds to either a newly created entry in
 -- the table or to one that matches all the required values for the release
 -- that one wishes to insert, w.r.t. the relationship between the release and
 -- its properties. This is works similarly to HLT_INSERT_DLL().
 drop function if exists HLT_INSERT_RELEASE /
 create function HLT_INSERT_RELEASE (NAME varchar(50),
                                     VERSION varchar(50)) returns int(10)
   begin
     declare TEST int(10) DEFAULT 0;
     declare NDLL int(10);
     declare RELEASE_ID int(10);
     declare DLL_ID int(10);
 
     select count(TMP_DLL.ID) into NDLL from TMP_DLL;
 
     -- the following respects an uniqueness condition, so no loop required!
     select HLT_RELEASE.HRE_ID into RELEASE_ID from HLT_RELEASE
       where HLT_RELEASE.HRE_NAME = NAME and HLT_RELEASE.HRE_VERSION = VERSION;
 
     if RELEASE_ID is not null then
       -- for all rows of TMP_DLL, check if we have a match, accumulates
       select 0 into TEST;
       select min(TMP_DLL.ID) into DLL_ID from TMP_DLL;
       while DLL_ID is not null do
         select TEST + HLT_SEEK_RE2DL(RELEASE_ID,DLL_ID) into TEST;
         select min(TMP_DLL.ID) into DLL_ID from TMP_DLL
           where TMP_DLL.ID > DLL_ID;
       end while;
       -- if TEST is NDLL and the total number is the same, we get a match
       if TEST = NDLL and HLT_COUNT_RE2DL(RELEASE_ID) = NDLL then
         delete from TMP_DLL; -- cleanup
         return RELEASE_ID;
       end if;
     end if;
 
     -- in the case we get here, all tests have failed and you don't have a
     -- release that have all the matches you need. Now we proceed like for
     -- a dll in HLT_INSERT_DLL() and insert a brand new release
     -- with all the dlls defined at the TMP_DLL table
     insert into HLT_RELEASE (HRE_NAME, HRE_VERSION) values (NAME, VERSION);
     select HRE_ID into RELEASE_ID from HLT_RELEASE where 
       HRE_NAME = NAME and HRE_VERSION = VERSION;
 
     -- now we just create all the links in the HLT_RE_TO_DL table
 --    select min(TMP_DLL.ID) into DLL_ID from TMP_DLL;
 --    while DLL_ID is not null do
 --     insert into HLT_RE_TO_DL (HRE2DL_RELEASE_ID, HRE2DL_DLL_ID)
 --        values (RELEASE_ID, DLL_ID);
 --      select min(TMP_DLL.ID) into DLL_ID from TMP_DLL 
 --        where TMP_DLL.ID > DLL_ID;
 --    end while;
 
     -- and return gracefully
     delete from TMP_DLL; -- cleanup
     return(RELEASE_ID);
   end;
 /
 
 -- ##########################################
 -- Stored procedures for easeing production #
 -- ##########################################
 
 -- This procedure will prune the TMP_FORCE_DLL table in view of DLL names that 
 -- would get included anyhow with the components selected for a given setup. 
 -- As input you have to specify against which release we are talking about. For
 -- the implementation, we use cursors to simplify the mechanics. It is not 
 -- necessary to look for duplicates since the PK condition of this table is that
 -- names are unique and, therefore, it is impossible to make the system input
 -- duplicates for this feature.
 drop procedure if exists PRUNE_FORCE_DLL /
 create procedure PRUNE_FORCE_DLL (IN NAME varchar(50),
                                   IN VERSION varchar(50))
   begin
     declare DLL_NAME varchar(50);
     declare REMOVE int(1) default 0;
     declare DONE int(1) default 0;
     declare DLL cursor for select HLT_DLL.HDL_NAME 
       from TMP_COMP, HLT_COMPONENT, HLT_SOURCE, HLT_SO_TO_DL, HLT_DLL
       where TMP_COMP.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;
     declare continue handler for sqlstate '02000' set DONE = 1;
 
     open DLL;
     repeat
       fetch DLL into DLL_NAME;
       select count(TMP_FORCE_DLL.NAME) into REMOVE from TMP_FORCE_DLL
         where TMP_FORCE_DLL.NAME = DLL_NAME;
       if REMOVE > 0 then -- this is to avoid the MySQL specific keyword IGNORE
         delete from TMP_FORCE_DLL where TMP_FORCE_DLL.NAME = DLL_NAME;
       end if;
     until DONE = 1 end repeat;
     close DLL;
 
   end;
 /
 
 -- Restores the default delimiter to MySQL
 delimiter ;
 
 -- #############################
 -- Views to help me read stuff #
 -- #############################
 
 -- Reads all parameters of a given component
 create or replace view HLT_CP_TO_PA_VIEW (HCP_ID, HPA_ID, HPA_NAME, HPA_VALUE, 
                                           HPA_OP)
   as
   select HLT_CP_TO_PA.HCP2PA_COMPONENT_ID,
          HLT_PARAMETER.HPA_ID,
          HLT_PARAMETER.HPA_NAME,
          HLT_PARAMETER.HPA_VALUE,
          HLT_PARAMETER.HPA_OP
   from HLT_CP_TO_PA, HLT_PARAMETER 
   where HLT_CP_TO_PA.HCP2PA_PARAMETER_ID = HLT_PARAMETER.HPA_ID;
 
 -- Reads all properties of a given source
 create or replace view HLT_SO_TO_PY_VIEW (HSO_ID, HPY_ID, HPY_NAME, HPY_VALUE, 
                                           HPY_TYPE)
   as
   select HLT_SO_TO_PY.HSO2PY_SOURCE_ID,
          HLT_PROPERTY.HPY_ID,
          HLT_PROPERTY.HPY_NAME,
          HLT_PROPERTY.HPY_VALUE,
          HLT_PROPERTY.HPY_TYPE
   from HLT_SO_TO_PY, HLT_PROPERTY 
   where HLT_SO_TO_PY.HSO2PY_PROPERTY_ID = HLT_PROPERTY.HPY_ID;
 
 -- Generates a view where we get all setups associated with a given
 -- master key. 
 create or replace view HLT_MK_TO_SETUP_VIEW (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_TC_TO_TS, -- trigger-chain -> trigger-signature
        HLT_TRIGGER_SIGNATURE, -- trigger signatures
        HLT_TS_TO_TE,  -- trigger-signature  -> trigger-element
        HLT_TE_TO_CP, -- trigger-elements -> setup
        HLT_SETUP -- 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;

Due to the LXR bug, the updates fail sometimes to remove references to deleted files. The Saturday's full rebuilds fix these problems
This page was automatically generated by the LXR engine.