-- PACKAGE tests:

--   generator of fragments, queries 

--   mathematical programming problem generator



CREATE OR REPLACE PACKAGE tests as
 

 PROCEDURE genfragments(n integer, mindimf integer, maxdimf integer);
   -- fragment's dimension generator 

   -- n = number of fragments

   -- mindimf = the minimum dimension of a fragment
   -- maxdimf = the maximum dimension of a fragment
   

 PROCEDURE memfragments(n integer, m integer, maxrepf integer);
   -- fragments storage generator at sites 

   -- n = number of fragments 

   -- m = number of sites

   -- maxrepf = maximum number of replication of a fragment 

   

 PROCEDURE memqueries(nq integer, m integer, maxrep integer, maxst integer);
   -- query generator

   -- nq = number of queries  

   -- m = number of sites

   -- maxrep = the maximum number of reiteration of a query in one site

   -- maxst = the maximum number of sites where one query is evaluted

   

 PROCEDURE memevaluation(nq integer, n integer, maxf integer);
  -- evaluation generator   

  -- nq = number of queries  

  -- n = number of fragments 

  -- maxf = maximum number of fragments used by a query   

  

 PROCEDURE pli_1;
 -- integer linear programming problem generator for supplementary replication

 -- the results will be extracted executing the procedure in SQL Developer or PL*SQL
 

 PROCEDURE pli_2;
 -- integer linear programming problem generator for complete redistribution of the fragments

 -- the results will be extracted executing the procedure in SQL Developer or PL*SQL
 

 PROCEDURE pli_2_a;
  -- integer linear programming problem generator for complete redistribution of the fragments

 --     if pli_2 fail with "SQL Error: ORA-01489: result of string concatenation is too long"
 -- the results will be extracted executing the procedure in SQL Developer or PL*SQL
 
END;

/


CREATE OR REPLACE PACKAGE body tests as


  PROCEDURE genfragments(n integer, mindimf integer, maxdimf integer) as
    i integer;
  BEGIN
    DELETE FROM fragments;
    FOR i in 1..n LOOP
      INSERT into fragments(idf, dimf) VALUES (i, trunc(dbms_random.value(mindimf, maxdimf)));
     -- for every fragment a dimension is generated

     END LOOP;
  END;


  PROCEDURE memfragments(n integer, m integer, maxrepf integer) as
    i integer;
    j integer;
    r integer;
    nr integer;
  BEGIN
    DELETE FROM tempmemf;
    FOR i in 1..n LOOP
      r:=trunc(dbms_random.value(1,maxrepf));
      -- r = number of replication of fragment i
      FOR j in 1..r LOOP
        INSERT into tempmemf(idf,ids) VALUES(i, trunc(dbms_random.value(1,m+0.999)));
        -- generation of a replication of fragment i  

     END LOOP;
   END LOOP;
   DELETE FROM memf;
   INSERT into memf(idf, ids)

     SELECT distinct idf,ids FROM tempmemf ORDER BY 1,2;
     -- it can be duplicate (idf,ids) pairs, they are eliminated and stored in table memf 

   UPDATE memf set init=1;
   -- update the dimension of sites

   DELETE FROM sites;
   INSERT into sites(ids,dims,dimlib)

     SELECT ids,sum(dimf) as d,0 

      FROM fragments f INNER JOIN memf m ON f.idf=m.idf 

      GROUP BY ids ORDER BY 1;
      -- it is possible to exists sites (1..m) without fragments due to the generator 

      --    they are refilled 

   FOR i in 1..m LOOP
     SELECT count(*) into nr 

       FROM sites WHERE ids=i;
     IF nr=0 then
       INSERT into sites(ids,dims,dimlib) VALUES (i,0,0);
     END IF;
   END LOOP;
 END;

 PROCEDURE memqueries(nq integer, m integer, maxrep integer, maxst integer) as
   i integer;
   j integer;
   s integer;
 BEGIN
   DELETE FROM tempqueries;
   FOR i in 1..nq LOOP
     s:=trunc(dbms_random.value(1,maxst+0.99));
     -- s = number of sites where query i is evaluated  

     FOR j in 1..s LOOP
      INSERT into tempqueries(idq,ids,nr_repetition)

        VALUES(i,trunc(dbms_random.value(1,m+0.99)),trunc(dbms_random.value(1,maxrep+0.99)));
      -- query i is evaluated in one site more times  

      --    (there can be duplicates for idq, ids, which will be eliminated)

     END LOOP;
   END LOOP;
   DELETE FROM queries;
   INSERT into queries 

     SELECT idq,ids,sum(nr_repetition) 

     FROM tempqueries

     GROUP BY idq,ids ORDER BY 1,2;

     -- duplicate elimination for (idq, ids) query, site pair

 END;


 PROCEDURE memevaluation(nq integer, n integer, maxf integer) as 

   i integer;
   j integer;
   f integer;
   d number(5,3);
 BEGIN
   DELETE FROM tempevaluation;
   FOR i in 1..nq LOOP
     f:=trunc(dbms_random.value(1,maxf+0.99));
     -- f = number of fragments used in query i
     FOR j in 1..f LOOP
       d:=dbms_random.value(0.2,1.2);
       -- dimension of fragments used by the next binary operator in the relational algebra tree

       IF d>1 then d:=1; END IF;
       INSERT into tempevaluation(idq,idf,dimu) 

         VALUES (i,trunc(dbms_random.value(1,n+0.99)),d);
     END LOOP;
   END LOOP;
  DELETE FROM evaluation;
  INSERT into evaluation

    SELECT idq,idf,max(dimu)

    FROM tempevaluation

    GROUP BY idq,idf

    ORDER BY 1,2;
  -- duplicate elimination for queries and fragments

 END;


 PROCEDURE pli_1 as
  -- necessary information for variables
  cursor inf1 is 

    SELECT ids,idf,ct FROM cost1 WHERE ct>0 ORDER BY 1,2;
  -- necessary information for the generator of the restrictions for site dimension

  cursor inf2 is 

    SELECT ids,dimlib as ds,listagg(to_char(dimf) || '*r_' || to_char(ids) || '_' || to_char(idf),'+') WITHIN GROUP (ORDER BY idf) as c 

    FROM 
      (SELECT c.ids,dimlib,c.idf,dimf FROM          

        (SELECT distinct ids,idf FROM cost1 WHERE ct>0) c
         INNER JOIN sites s ON c.ids=s.ids
         INNER JOIN fragments f ON c.idf=f.idf
      )

    GROUP BY ids,dimlib

    ORDER BY 1,2;
    -- list of variables used in the problem

  cursor inf3 is 

    SELECT ids, listagg('r_' || to_char(ids) || '_' || to_char(idf),',') WITHIN GROUP (ORDER BY idf) as c

    FROM (SELECT distinct ids,idf FROM cost1 WHERE ct>0)
    GROUP BY ids;
    -- necessary information for the generator of the restrictions for the variables' values

  cursor inf4 is

    SELECT listagg('0<=r_' || to_char(ids) || '_' || to_char(idf) || '<=1',';') WITHIN GROUP (ORDER BY idf) as c

    FROM (SELECT distinct ids,idf FROM cost1 WHERE ct>0)
    GROUP BY ids;

    c1 inf1%rowtype;
    c2 inf2%rowtype;

    c3 inf3%rowtype;

    c4 inf4%rowtype;

    i integer := 0;
    j integer;

  BEGIN
    dbms_output.enable(1000000);
    dbms_output.put_line('/* variables: r_idsnode_idfragment */');
    dbms_output.put_line('/* function */');

    --max: 
    dbms_output.put('max: ');

    j:=0;

    FOR c1 in inf1 LOOP
      IF i>0 then dbms_output.put('+'); END IF;

      i:=1;

     dbms_output.put(to_char(c1.ct) || '*' || 'r_' || to_char(c1.ids) || '_' || to_char(c1.idf));

      j:=j+1;

      IF j>10 then

        dbms_output.put_line('');

        j:=0;

      END IF;

    END LOOP;
    dbms_output.put_line(';');
    dbms_output.put_line('/* restrictions */');
    FOR c2 in inf2 LOOP
      dbms_output.put_line(c2.c || '<=' || to_char(c2.ds) || ';');

    END LOOP;

    dbms_output.put_line('/* variables used have VALUES between 0 and 1 */');
    FOR c4 in inf4 LOOP

      dbms_output.put_line(c4.c || ';');

    END LOOP;

    dbms_output.put_line('/* integer variables */');
    FOR c3 in inf3 LOOP
       dbms_output.put_line('int ' || to_char(c3.c) || ';');
    END LOOP;

  END;



  PROCEDURE pli_2 as

    -- necessary information for variables

    cursor inf1 is

      SELECT ids,idf,ct FROM cost2 WHERE ct>0 ORDER BY 1,2;

    -- necessary information for the generator of the restrictions for site dimension

    cursor inf2 is

      SELECT ids,dimlib as ds,

         listagg(to_char(dimf) || '*r_' || to_char(ids || '_' || to_char(idf)),'+') WITHIN GROUP (ORDER BY idf) as c 

      FROM (SELECT ids,dimlib,idf,dimf FROM sites cross join fragments)
      GROUP BY ids,dimlib
      ORDER BY 1;

    -- list of variables used by the problem

    cursor inf3 is

      SELECT ids, listagg('r_' || to_char(ids || '_' || to_char(idf)),',') WITHIN GROUP (ORDER BY idf) as c

      FROM (SELECT ids,idf FROM sites cross join fragments)

      GROUP BY ids

      ORDER BY 1;
    -- necessary information for the generator of the restrictions for number of fragments

    cursor inf3a is

       SELECT idf, listagg('r_' || to_char(ids || '_' || to_char(idf)),'+') WITHIN GROUP (ORDER BY ids) as c

       FROM (SELECT ids,idf FROM sites cross join fragments)
       GROUP BY idf

       ORDER BY 1;

    -- necessary information for the generator of the restrictions for the variables' values

    cursor inf4 is

      SELECT ids,listagg('0<=r_' || to_char(ids || '_' || to_char(idf)) ||'<=1',';') WITHIN GROUP (ORDER BY idf) as c

      FROM (SELECT ids,idf FROM sites cross join fragments)

      GROUP BY ids

      ORDER BY 1;
    c1 inf1%rowtype;

    c2 inf2%rowtype;

    c3 inf3%rowtype;

    c4 inf4%rowtype;
    i integer := 0;

    j integer;

  BEGIN

    dbms_output.enable(1000000);
    dbms_output.put_line('/* variables: r_idstatie_idfragment */');

    dbms_output.put_line('/* function */');

    --max:

    dbms_output.put('max: ');
    j:=0;
    FOR c1 in inf1 LOOP

      IF i>0 then

         dbms_output.put('+');

      END IF;
      i:=1;

      dbms_output.put(to_char(c1.ct) || '*' || 'r_' || to_char(c1.ids) || '_' || to_char(c1.idf));

      j:=j+1;
      IF j>10 then
        dbms_output.put_line('');

        j:=0;

      END IF;
    END LOOP;

    dbms_output.put_line(';');

    dbms_output.put_line('/* restrictions */');

    FOR c2 in inf2 LOOP

      dbms_output.put_line(c2.c || '<=' || to_char(c2.ds) || ';');
    END LOOP;

    dbms_output.put_line('/* variables used have VALUES between 0 and 1 */');
    FOR c4 in inf4 LOOP

      dbms_output.put_line(c4.c || ';');

    END LOOP;

    dbms_output.put_line('/* each fragment is stored */');

    FOR c3 in inf3a LOOP

      dbms_output.put_line(c3.c || '>=1;');

    END LOOP;

    dbms_output.put_line('/* integer variables */');

    FOR c3 in inf3 LOOP

      dbms_output.put_line('int ' || to_char(c3.c) || ';');
    END LOOP;

  END;


  PROCEDURE pli_2_a as

    -- necessary information for variables    

    cursor inf1 is

      SELECT ids,idf,ct FROM cost2 WHERE ct>0 ORDER BY 1,2;

    -- necessary information for the generator of the restrictions for site dimension

    cursor inf2 is

      SELECT ids,dimlib as ds, listagg(to_char(dimf) || '*r_' || to_char(ids || '_' || to_char(idf)),'+') WITHIN GROUP (ORDER BY idf) as c

      FROM 
       (SELECT ids,dimlib,idf,dimf FROM sites cross join fragments)
        GROUP BY ids,dimlib
        ORDER BY 1;

    -- list of variables used by the problem

    cursor inf3 is

      SELECT ids, listagg('r_' || to_char(ids || '_' || to_char(idf)),',') WITHIN GROUP (ORDER BY idf) as c

      FROM (SELECT ids,idf FROM sites cross join fragments)

      GROUP BY ids

      ORDER BY 1;

    -- necessary information for the generator of the restrictions for number of fragments

    cursor inf3a is 

      SELECT idf, listagg('r_' || to_char(ids || '_' || to_char(idf)),'+') WITHIN GROUP (ORDER BY ids) as c

      FROM (SELECT ids,idf FROM sites cross join fragments)

      GROUP BY idf
      ORDER BY 1;

    -- necessary information for the generator of the restrictions for the variables' values

    cursor inf4a is 

      SELECT ids,listagg('0<=r_' || to_char(ids || '_' || to_char(idf)) || '<=1',';') WITHIN GROUP (ORDER BY idf) as c

       FROM (SELECT ids,idf FROM sites cross join fragments WHERE idf<=200)

       GROUP BY ids
       ORDER BY 1;
    cursor inf4b is

      SELECT ids,listagg('0<=r_' || to_char(ids || '_' || to_char(idf)) || '<=1',';') WITHIN GROUP (ORDER BY idf) as c
      FROM (SELECT ids,idf FROM sites cross join fragments WHERE idf > 200)
      GROUP BY ids
      ORDER BY 1;
    c1 inf1%rowtype;

    c2 inf2%rowtype;

    c3 inf3%rowtype;

    c4a inf4a%rowtype;
    c4b inf4b%rowtype;

    i integer := 0;

    j integer;

  BEGIN
    dbms_output.enable(1000000);

    dbms_output.put_line('/* variables: r_idstatie_idfragment */');

    dbms_output.put_line('/* function */');

    --max: 
    dbms_output.put('max: ');
    j:=0;

   FOR c1 in inf1 LOOP

      IF i>0 then dbms_output.put('+'); END IF;

      i:=1;

       dbms_output.put(to_char(c1.ct) || '*' || 'r_' || to_char(c1.ids) || '_' || to_char(c1.idf));
      j:=j+1;
      IF j>10 then
        dbms_output.put_line('');

        j:=0;

      END IF;

    END LOOP;

    dbms_output.put_line(';');

    dbms_output.put_line('/* restrictions */');

    FOR c2 in inf2 LOOP
      dbms_output.put_line(c2.c || '<=' || to_char(c2.ds) || ';');

    END LOOP;

    dbms_output.put_line('/* variables used have VALUES between 0 and 1 */');

    FOR c4a in inf4a LOOP
      dbms_output.put_line(c4a.c || ';');

    END LOOP;

    FOR c4b in inf4b LOOP
      dbms_output.put_line(c4b.c || ';');

    END LOOP;
    dbms_output.put_line('/* each fragment is stored */');

    FOR c3 in inf3a LOOP
      dbms_output.put_line(c3.c || '>=1;');

    END LOOP;

    dbms_output.put_line('/* integer variables */');

    FOR c3 in inf3 LOOP

      dbms_output.put_line('int ' || to_char(c3.c) || ';');

    END LOOP;

  END;

END;