-- Hello emacs, please make this buffer -*- sql -*-
 -- Andre DOS ANJOS <Andre.dos.Anjos@cern.ch>
 
 -- $Author: stelzer $
 -- $Revision: 1.4 $
 -- $Date: 2006/10/17 10:03:07 $
 
 -- 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 /
 
 
 drop table TMP_PAR;
 drop table TMP_COMP;
 drop table TMP_FORCE_DLL;
 drop table TMP_PROP;
 drop table TMP_SOURCE;
 drop table TMP_DLL;
 
 create table TMP_PAR (
   ID INTEGER       not null,
   constraint TMPPAR_ID_PK primary key (ID)
 );
 
 create table TMP_COMP (
    ID INTEGER       not null,
    constraint TMPCOMP_ID_PK primary key (ID)
 );
 
 create table TMP_FORCE_DLL (
   NAME VARCHAR2(50) not null,
   constraint NAME_PK primary key (NAME)
 );
 
 create table TMP_PROP (
   ID NUMBER(10) not null,
   constraint TMPPROP_ID_PK primary key (ID)
 );
 
 create table TMP_SOURCE (
   ID NUMBER(10) not null,
   constraint TMPSOURCE_ID_PK primary key (ID)
 );
 
 create table TMP_DLL (
   ID NUMBER(10) not null,
   constraint TMPDLL_ID_PK primary key (ID)
 );
 
 
 -- #####################################
 -- Stored procedures for job insertion #
 -- #####################################
 
 
 -- Creates a set of temporary tables needed for the job insertion
 create or replace procedure HLT_BOOT_SETUP
 as
 begin
     execute immediate 'truncate table TMP_PAR';
     execute immediate 'truncate table TMP_COMP';
     execute immediate 'truncate table TMP_FORCE_DLL';
 end;
 /
 
 -- Destroyes the set of temporary tables needed for the job insertion
 create or replace procedure HLT_SHUTDOWN_SETUP
 as
 begin
     execute immediate 'truncate table TMP_PAR';
     execute immediate 'truncate table TMP_COMP';
     execute immediate 'truncate table 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.
 create or replace function HLT_INSERT_PARAMETER
   (NAME in VARCHAR2, OP in VARCHAR2, VALUE in VARCHAR2
   )
   return int
 as
   RETVAL int default NULL;
 begin
   select HPA_ID into RETVAL from HLT_PARAMETER where 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 
        HPA_NAME = NAME and HPA_OP = OP and HPA_VALUE = VALUE;
   end if;
   execute immediate 'insert into TMP_PAR (ID) values (RETVAL)';
   return RETVAL;
 end;
 /
 
 
 create or replace function HLT_INSERT_PARAMETER
   (NAME in VARCHAR2, OP in VARCHAR2, VALUE in VARCHAR2
   )
   return INTEGER
 is
   RETVAL INTEGER default NULL;
 begin
   select HPA_ID into RETVAL from HLT_PARAMETER where 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 
       HPA_NAME = NAME and HPA_OP = OP and HPA_VALUE = VALUE;
   end if;
   execute immediate 'insert into TMP_PAR (ID) values (RETVAL)';
   return(RETVAL);
 end;
 /
 
 
 -- Resets the temporary parameter table
 create or replace procedure HLT_RESET_PARAMETER
 as
 begin
   execute immediate 'truncate table 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.
 create or replace function HLT_SEEK_CP2PA (
   COMP_ID in INTEGER,
   PAR_ID  in INTEGER
   ) return INTEGER
 as
   TEST INTEGER(10);
 begin
   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.
 create or replace function HLT_COUNT_CP2PA (COMP_ID INTEGER)
   return INTEGER
 as
   TEST INTEGER(10);
 begin
   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
 create or replace function HLT_INSERT_COMPONENT (NAME VARCHAR2,
                                       ALIAS VARCHAR2,
                                       TOPALG INTEGER)
   return INTEGER
 as
   TEST    INTEGER(10) DEFAULT 0;
   NPAR    INTEGER(10);
   COMP_ID INTEGER(10);
   PAR_ID  INTEGER(10);
   VERSION decimal(11,5);
 begin
   execute immediate '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
   loop
     -- for all rows of TMP_PAR, check if we have a match, accumulates in TEST
     TEST := 0;
     execute immediate 'select min(TMP_PAR.ID) into PAR_ID from TMP_PAR';
     while PAR_ID is not null
     loop
       TEST := TEST + HLT_SEEK_CP2PA(COMP_ID,PAR_ID);
       execute immediate 'select min(TMP_PAR.ID) into PAR_ID from TMP_PAR where TMP_PAR.ID > PAR_ID';
     end loop;
     -- 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
       execute immediate 'delete from TMP_PAR'; -- cleanup
       execute immediate '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 loop;
 
   -- 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;
   if VERSION is null then
     VERSION := 1.0;
   else
     VERSION := floor(VERSION) + 1.0;
   end if;
   execute immediate '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
   execute immediate 'select min(TMP_PAR.ID) into PAR_ID from TMP_PAR';
   while PAR_ID is not null
   loop
     insert into HLT_CP_TO_PA (HCP2PA_COMPONENT_ID, HCP2PA_PARAMETER_ID) values (COMP_ID, PAR_ID);
     execute immediate 'select min(TMP_PAR.ID) into PAR_ID from TMP_PAR where TMP_PAR.ID > PAR_ID';
   end loop;
 
   -- and return gracefully
   execute immediate 'delete from TMP_PAR'; -- cleanup
   execute immediate 'insert into TMP_COMP (ID) values (COMP_ID)';
   return(COMP_ID);
 end;
 /
 
 
 -- Resets the temporary component table
 create or replace procedure HLT_RESET_COMPONENT
 is
 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.
 create or replace procedure HLT_NEW_FORCE_DLL (NAME in VARCHAR2)
 as
   RETVAL INTEGER(10);
 begin
   insert into TMP_FORCE_DLL (NAME) values (NAME);
 end;
 /
 
 
 -- Resets the temporary parameter table
 create or replace procedure HLT_RESET_FORCE_DLL
 as
 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.
 create or replace function HLT_SEEK_ST2CP (
   SETUP_ID INTEGER,
   COMP_ID INTEGER) 
   return INTEGER
 as
   TEST INTEGER(10);
 begin
   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
 create or replace function HLT_COUNT_ST2CP (SETUP_ID int)
   return int
 as
   TEST int(10);
 begin
   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.
 create or replace function HLT_SEEK_ST2FD (
     SETUP_ID INTEGER,
     FDLL VARCHAR2)
   return INTEGER
 as
   TEST int(10);
 begin
   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
 create or replace function HLT_COUNT_ST2FD (SETUP_ID INTEGER)
   return INTEGER
 as
   TEST int(10);
 begin
   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.
 create or replace function HLT_INSERT_SETUP (NAME VARCHAR2) 
   return int
 as
  TEST_COMP int(20) DEFAULT 0;
  TEST_FDLL int(20) DEFAULT 0;
  NCOMP     int(10);
  NFDLL     int(10);
  SETUP_ID  int(10);
  COMP_ID   int(10);
  FDLL      VARCHAR2(50);
  VERSION   decimal(11,5);
 begin
   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
   loop
     -- for all rows of TMP_COMP, check if we have a match, accumulates in TEST
     TEST_COMP := 0;
     select min(TMP_COMP.ID) into COMP_ID from TMP_COMP;
     while COMP_ID is not null
     loop
       TEST_COMP := TEST_COMP + HLT_SEEK_ST2CP(SETUP_ID,COMP_ID);
       select min(TMP_COMP.ID) into COMP_ID from TMP_COMP 
         where TMP_COMP.ID > COMP_ID;
     end loop;
     -- for all rows of TMP_FORCE_DLL, check if we have a match
     TEST_FDLL := 0;
     select min(TMP_FORCE_DLL.NAME) into FDLL from TMP_FORCE_DLL;
     while FDLL is not null
     loop
       TEST_FDLL := TEST_FDLL + HLT_SEEK_ST2FD(SETUP_ID,FDLL);
       select min(TMP_FORCE_DLL.NAME) into FDLL from TMP_FORCE_DLL
         where TMP_FORCE_DLL.NAME > FDLL;
     end loop;
 
     -- 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 loop;
 
   -- 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;
 
   if VERSION is null then
     VERSION := 1.0;
   else
     VERSION := floor(VERSION) + 1.0;
   end if;
   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
   loop
     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 loop;
 
 
   -- 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
   loop
     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 loop;
 
 
 
   -- 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
 create or replace procedure HLT_BOOT_RELEASE
 as
 begin
   execute immediate 'truncate table TMP_PROP';
   execute immediate 'truncate table TMP_SOURCE';
   execute immediate 'truncate table TMP_DLL';
 
 end;
 /
 
 -- Destroyes the set of temporary tables needed for the release insertion
 create or replace procedure HLT_SHUTDOWN_RELEASE
 as
 begin
   execute immediate 'truncate table TMP_PROP';
   execute immediate 'truncate table TMP_SOURCE';
   execute immediate 'truncate table 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.
 create or replace function HLT_INSERT_PROPERTY (
     NAME      IN VARCHAR2,
     PROP_TYPE IN VARCHAR2,
     VALUE     IN VARCHAR2)
   return INTEGER
 as
   RETVAL  int(10) default NULL;
   VERSION decimal(11,5);
 begin
   select HPY_ID into RETVAL from HLT_PROPERTY where 
     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 
       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
 create or replace procedure HLT_RESET_PROPERTY
 as
 begin
   execute immediate 'truncate table 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.
 create or replace function HLT_SEEK_SO2PY (
     SOURCE_ID   IN INTEGER,
     PROPERTY_ID IN INTEGER) 
   return int
 as
   TEST int(10);
 begin
   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.
 create or replace function HLT_COUNT_SO2PY (SOURCE_ID IN INTEGER) 
   return INTEGER
 as
   TEST int(10);
 begin
   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()
 create or replace function HLT_INSERT_SOURCE (
     NAME VARCHAR2,
     GAUDI_TYPE VARCHAR2)
   return INTEGER
 as
   TEST      int(10) DEFAULT 0;
   NPROP     int(10);
   SOURCE_ID int(10);
   PROP_ID   int(10);
 begin
 
   execute immediate '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
   loop
     -- for all rows of TMP_PROP, check if we have a match, accumulates in TEST
     TEST := 0;
     execute immediate 'select min(TMP_PROP.ID) into PROP_ID from TMP_PROP';
     while PROP_ID is not null
     loop
       TEST := TEST + HLT_SEEK_SO2PY(SOURCE_ID,PROP_ID);
       select min(TMP_PROP.ID) into PROP_ID from TMP_PROP 
         where TMP_PROP.ID > PROP_ID;
     end loop;
     -- 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 loop;
 
   -- 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
   loop
     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 loop;
 
   -- 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
 create or replace procedure HLT_RESET_SOURCE
 as
 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.
 create or replace function HLT_SEEK_SO2DL (
     DLL_ID    IN INTEGER,
     SOURCE_ID IN INTEGER)
   return INTEGER
 as
   TEST int(10);
 begin
   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.
 create or replace function HLT_COUNT_SO2DL (
     DLL_ID INTEGER) 
   return INTEGER
 as
   TEST int(10);
 begin
   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().
 create or replace function HLT_INSERT_DLL (
     NAME VARCHAR2,
     PACKAGE VARCHAR2,
     CMT_VERSION VARCHAR2)
   return INTEGER
 as
   TEST      int(10) DEFAULT 0;
   NSOURCE   int(10);
   DLL_ID    int(10);
   SOURCE_ID int(10);
 begin
   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 
     TEST := 0;
     select min(TMP_SOURCE.ID) into SOURCE_ID from TMP_SOURCE;
     while SOURCE_ID is not null
     loop
       TEST := TEST + HLT_SEEK_SO2DL(DLL_ID,SOURCE_ID);
       select min(TMP_SOURCE.ID) into SOURCE_ID from TMP_SOURCE
         where TMP_SOURCE.ID > SOURCE_ID;
     end loop;
     -- 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
   loop
     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 loop;
 
   -- 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
 create or replace procedure HLT_RESET_DLL
 as
 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.
 create or replace function HLT_SEEK_RE2DL (
     RELEASE_ID INTEGER,
     DLL_ID     INTEGER) 
   return INTEGER
 as
   TEST int(10);
 begin
   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.
 create or replace function HLT_COUNT_RE2DL (RELEASE_ID IN INTEGER)
   return INTEGER
 as
   TEST int(10);
 begin
   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().
 create or replace function HLT_INSERT_RELEASE (
     NAME    IN VARCHAR2,
     VERSION IN VARCHAR2)
   return INTEGER
 as
   TEST       int(10) DEFAULT 0;
   NDLL       int(10);
   RELEASE_ID int(10);
   DLL_ID     int(10);
 begin
   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
     TEST := 0;
     select min(TMP_DLL.ID) into DLL_ID from TMP_DLL;
     while DLL_ID is not null
     loop
       TEST := TEST + HLT_SEEK_RE2DL(RELEASE_ID,DLL_ID);
       select min(TMP_DLL.ID) into DLL_ID from TMP_DLL
         where TMP_DLL.ID > DLL_ID;
     end loop;
     -- 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
   loop
     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 loop;
 
   -- 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.
 --      create or replace procedure PRUNE_FORCE_DLL (
 --          NAME    IN VARCHAR2,
 --          VERSION IN VARCHAR2)
 --      as
 --        DLL_NAME VARCHAR2(50);
 --        REMOVE   int(1) default 0;
 --        DONE     int(1) default 0;
 --        DLL cursor;
 --      begin
 --      
 --        select HLT_DLL.HDL_NAME into DLL
 --          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;
 --        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;
 --      /
 
 -- #############################
 -- 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.