-- the transfer cost (ct) for all queries if the fragment with id idf is evaluated at site ids

--    we consider the current situation (the sites where the fragments are stored)  

--    in the subquery the transfer cost is 0 if the fragment with ID idf is stored at the site with ID ids (so it does not need to be transfered)



CREATE OR REPLACE VIEW cost1 as 

  SELECT idf,ids, sum(dimf*dimu*nr_repetition*coef) as ct

  FROM 
  (SELECT a.idq,a.ids,e.idf,a.nr_repetition,dimu,dimf,m.ids as ids1,
     CASE WHEN m.ids IS null THEN 1 ELSE 0 end as coef 

   FROM
     ((queries a INNER JOIN evaluation e ON a.idq=e.idq)
      INNER JOIN fragments f ON e.idf=f.idf
     )
      LEFT JOIN memf m ON f.idf=m.idf and a.ids=m.ids

   )
  GROUP BY idf,ids;


-- the total transfer cost for all queries

-- (ids, idf) which are not used (the query is not processed at site ids or the fragment with ID idf is not used by the query)

--     are not considered in the query 

--    we consider the current situation (the sites where the fragments are stored) 

CREATE OR REPLACE VIEW inf_costtotal1 as
  SELECT sum(ct) as cost FROM cost1;

-- the transfer cost (ct) for all queries if the fragment with id idf is evaluated at site ids

--    we do not consider the current situation (the sites where the fragments are stored)

-- the view is useful in fragment redistribution



CREATE OR REPLACE VIEW cost2 as 
 SELECT e.idf,a.ids,sum(dimf*dimu*a.nr_repetition) as ct 

   FROM
    (queries a INNER JOIN evaluation e ON a.idq=e.idq)
   INNER JOIN fragments f ON e.idf=f.idf
  GROUP BY e.idf,a.ids;



-- the total transfer cost for all queries

-- (ids, idf) which are not used (the query is not processed at site ids or the fragment with ID idf is not used by the query)

--     are not considered in the query 

--  we do not consider the current situation (the sites where the fragments are stored)



CREATE OR REPLACE VIEW inf_costtotal2 as
 SELECT sum(ct) as cost FROM costuri2;





CREATE OR REPLACE VIEW cost1_new as 
  SELECT idf,ids,sum(dimf*dimu*nr_repetition*coef) as ct 

  FROM 
     (SELECT a.idq,a.ids,e.idf,a.nr_repetition,dimu,dimf,m.ids as ids1,
        CASE WHEN m.ids IS null THEN 1 ELSE 0 end as coef 

      FROM
        ((queries a INNER JOIN evaluation e on a.idq=e.idq)
             INNER JOIN fragments f on e.idf=f.idf
       )
      LEFT JOIN (SELECT * FROM memf where init=0) m 

      ON f.idf=m.idf and a.ids=m.ids)
  GROUP BY idf,ids;
 




CREATE OR REPLACE VIEW inf_costtotal1_new as
  SELECT sum(ct) as cost FROM cost1_new;