create or replace PROCEDURE CREATE_TABLES AS 
BEGIN

BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE T_ORIG_LOG';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;

  execute immediate 
  'CREATE TABLE T_ORIG_LOG 
   (	YY NUMBER(4,0) NOT NULL ENABLE, 
	MM NUMBER(2,0) NOT NULL ENABLE, 
	DD NUMBER(2,0) NOT NULL ENABLE, 
	HH NUMBER(2,0) NOT NULL ENABLE, 
	MI NUMBER(2,0) NOT NULL ENABLE, 
	SS NUMBER(2,0) NOT NULL ENABLE, 
	SEQ NUMBER(10,0) NOT NULL ENABLE, 
	THETIME TIMESTAMP (0) NOT NULL ENABLE, 
	LOGID NUMBER, 
	CLIENTIP VARCHAR2(20 BYTE), 
	REQUESTOR VARCHAR2(40 BYTE), 
	SERVER VARCHAR2(20 BYTE), 
	DBNAME VARCHAR2(20 BYTE), 
	ACC VARCHAR2(20 BYTE), 
	ELAPSED NUMBER, 
	BUSY NUMBER, 
	NRROWS NUMBER, 
	STATEMENT VARCHAR2(4000 BYTE), 
	ERROR NUMBER(5,0) NOT NULL ENABLE, 
	ERRORMESSAGE VARCHAR2(100 BYTE), 
	IS_MODIFIED NUMBER
   )';



BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE FROM_WHERE_STATEMENTS';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;

  execute immediate 
  'CREATE TABLE FROM_WHERE_STATEMENTS 
   (	ID NUMBER NOT NULL ENABLE, 
	WHERE_TEMPLATE VARCHAR2(4000 BYTE), 
	FROM_TEMPLATE VARCHAR2(4000 BYTE), 
  SELECT_TEMPLATE VARCHAR2(4000 BYTE), 
	COUNT NUMBER,
  MIN_TIME TIMESTAMP (6), 
	MAX_TIME TIMESTAMP (6), 
	OCCURANCE_PART FLOAT(126), 
	DISTINCT_IPS_COUNT NUMBER,
  IS_SIMPLE NUMBER
   )' ;




BEGIN

    EXECUTE IMMEDIATE 'DROP INDEX FROM_WHERE_STATEMENTS_INDEX';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;

  execute immediate 
  '  CREATE INDEX FROM_WHERE_STATEMENTS_INDEX ON FROM_WHERE_STATEMENTS (FROM_TEMPLATE)';
 
  
BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE FREQUENCY_TABLE';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;
  execute immediate 
  '   CREATE TABLE FREQUENCY_TABLE 
   (	FIRST_STATEMENT NUMBER, 
	SECOND_STATEMENT NUMBER, 
	COUNT NUMBER,
  ANTIPATTERNTYPE number,
  CAN_BE_STIFLE number default 0
   ) ';

BEGIN

    EXECUTE IMMEDIATE 'DROP INDEX FREQUENCY_TABLE_INDEX';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;
  execute immediate 
  'CREATE INDEX FREQUENCY_TABLE_INDEX ON FREQUENCY_TABLE (FIRST_STATEMENT, SECOND_STATEMENT)';
  
   
BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE PARSED_STATEMENTS';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;


BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE VIEW_SERIES';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;

  execute immediate 
  'CREATE TABLE VIEW_SERIES
   (	
   FIRST_STATEMENT NUMBER, 
   SECOND_STATEMENT NUMBER, 
   COUNT NUMBER, 
   WHERE_TEMPLATE1 VARCHAR2(4000 BYTE), 
   FROM_TEMPLATE1 VARCHAR2(4000 BYTE), 
   SELECT_TEMPLATE1 VARCHAR2(4000 BYTE),  
   WHERE_TEMPLATE2 VARCHAR2(4000 BYTE), 
   FROM_TEMPLATE2 VARCHAR2(4000 BYTE), 
   SELECT_TEMPLATE2 VARCHAR2(4000 BYTE),
   ANTIPATTERNTYPE number,
   CAN_BE_STIFLE number default 0
   )' ;
 
   
   BEGIN

    EXECUTE IMMEDIATE 'DROP INDEX INDEX_VIEW_SERIES_FIRST_SECOND';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;
   execute immediate 
  'CREATE INDEX INDEX_VIEW_SERIES_FIRST_SECOND ON VIEW_SERIES (FIRST_STATEMENT, SECOND_STATEMENT)';
  
BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE STATISTICS';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;
      
    execute immediate 
  'CREATE TABLE STATISTICS
  (
    STEP_TIME TIMESTAMP (6) NOT NULL ENABLE, 
    ROWS_BEFORE NUMBER(10,0) NOT NULL ENABLE, 
    ROWS_AFTER NUMBER(10,0) NOT NULL ENABLE, 
    ROWS_MODIFIED NUMBER(10,0) NOT NULL ENABLE
  )' ;
   
     execute immediate 
  '
  CREATE TABLE PARSED_STATEMENTS 
   (	YY NUMBER(4,0) NOT NULL ENABLE, 
	MM NUMBER(2,0) NOT NULL ENABLE, 
	DD NUMBER(2,0) NOT NULL ENABLE, 
	HH NUMBER(2,0) NOT NULL ENABLE, 
	MI NUMBER(2,0) NOT NULL ENABLE, 
	SS NUMBER(2,0) NOT NULL ENABLE, 
	SEQ NUMBER(10,0) NOT NULL ENABLE, 
	THETIME TIMESTAMP (0) NOT NULL ENABLE, 
	LOGID NUMBER, 
	CLIENTIP VARCHAR2(20 BYTE), 
	REQUESTOR VARCHAR2(40 BYTE), 
	SERVER VARCHAR2(20 BYTE), 
	DBNAME VARCHAR2(20 BYTE), 
	ACC VARCHAR2(20 BYTE), 
	ELAPSED NUMBER, 
	BUSY NUMBER, 
	NRROWS NUMBER, 
	STATEMENT VARCHAR2(4000 BYTE), 
	ERROR NUMBER(5,0) NOT NULL ENABLE, 
	ERRORMESSAGE VARCHAR2(100 BYTE), 
	SELECT_STATEMENT VARCHAR2(4000 BYTE), 
	FROM_STATEMENT VARCHAR2(4000 BYTE), 
  FROM_ST_REAL VARCHAR2(4000 BYTE),
	WHERE_STATEMENT VARCHAR2(4000 BYTE), 
  WHERE_ST_REAL VARCHAR2(4000 BYTE), 
	STAT_ID NUMBER,
  CAN_BE_STIFLE NUMBER DEFAULT 0
   ) ';

   BEGIN

    EXECUTE IMMEDIATE 'DROP INDEX PARSED_STATEMENTS_INDEX1_';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;
     execute immediate 
  '
  CREATE INDEX PARSED_STATEMENTS_INDEX1_ ON PARSED_STATEMENTS (CLIENTIP, LOGID, STAT_ID, THETIME, SEQ) ';
 
    BEGIN

    EXECUTE IMMEDIATE 'DROP INDEX PARSED_STATEMENTS_INDEX2_';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;
     execute immediate 
  '
  CREATE INDEX PARSED_STATEMENTS_INDEX2_ ON PARSED_STATEMENTS (THETIME) ';
  
execute immediate
'create or replace PROCEDURE CALCULATE_FREQUENCY AS 

     count_ number := -1;
     prev_stat number := -1;
     prev_client_id varchar(100) := '''';
     prev_log_id number := -1;
     prev_time timestamp(0);
     cnt NUMBER :=0;
     prev_can_be_stifle number := -1;
     can_be_stifle_v number := -1;

 cursor stat_data_cursor is
     SELECT CLIENTIP,LOGID, THETIME, STAT_ID, CAN_BE_STIFLE
     FROM PARSED_STATEMENTS
     ORDER BY CLIENTIP,LOGID, THETIME;
     
BEGIN
 execute immediate ''TRUNCATE TABLE FREQUENCY_TABLE'';
 commit;
        
 FOR stat_data in stat_data_cursor
   LOOP
begin
  if (stat_data.STAT_ID IS NOT NULL) THEN
  BEGIN
  if (prev_stat = -1) then
  begin
   prev_stat := stat_data.STAT_ID;
   prev_client_id := stat_data.CLIENTIP;
   prev_log_id := stat_data.LOGID;
   prev_time := stat_data.THETIME;
   prev_can_be_stifle := stat_data.CAN_BE_STIFLE;
  end;
  else
  begin
  if NOT ((prev_client_id = stat_data.CLIENTIP) AND  (prev_log_id = stat_data.LOGID)) then
  BEGIN
    prev_stat := -1;
    prev_client_id := -1;
    prev_log_id := -1;
  END;
  ELSE
  BEGIN
   SELECT COUNT(*)
   INTO cnt
    FROM FREQUENCY_TABLE
  where FIRST_STATEMENT = prev_stat and SECOND_STATEMENT = stat_data.STAT_ID;

  IF( cnt > 0 ) then
  begin
    UPDATE FREQUENCY_TABLE
    SET COUNT = COUNT + 1
    WHERE FIRST_STATEMENT = prev_stat and SECOND_STATEMENT = stat_data.STAT_ID;
  end;
  else
  begin 
  can_be_stifle_v := prev_can_be_stifle * stat_data.CAN_BE_STIFLE;
  INSERT
INTO FREQUENCY_TABLE
  (
    FIRST_STATEMENT,
    SECOND_STATEMENT,
    COUNT,
    CAN_BE_STIFLE
  )
  VALUES
  (
    prev_stat,
    stat_data.STAT_ID,
    1,
    can_be_stifle_v
  );
  commit;
  end;
  end if;
  
  
   
  END; END iF;
  end;
  prev_stat := stat_data.STAT_ID;
   prev_client_id := stat_data.CLIENTIP;
   prev_log_id := stat_data.LOGID;
   prev_time := stat_data.THETIME;
   prev_can_be_stifle := stat_data.CAN_BE_STIFLE;
  end if;
end;
END IF;
END;
end loop;

  execute immediate ''TRUNCATE TABLE VIEW_SERIES'';
commit;
INSERT INTO VIEW_SERIES (FIRST_STATEMENT, SECOND_STATEMENT, COUNT, WHERE_TEMPLATE1, FROM_TEMPLATE1, SELECT_TEMPLATE1,  WHERE_TEMPLATE2, FROM_TEMPLATE2, SELECT_TEMPLATE2, CAN_BE_STIFLE)
SELECT res.FIRST_STATEMENT, res.SECOND_STATEMENT, res.COUNT, res.WHERE_TEMPLATE1, res.FROM_TEMPLATE1, res.SELECT_TEMPLATE1, fws2.WHERE_TEMPLATE as WHERE_TEMPLATE2, fws2.FROM_TEMPLATE as FROM_TEMPLATE2, fws2.SELECT_TEMPLATE as SELECT_TEMPLATE2, res.CAN_BE_STIFLE
FROM
(
SELECT ft.FIRST_STATEMENT, ft.SECOND_STATEMENT, ft.COUNT, fws.WHERE_TEMPLATE as WHERE_TEMPLATE1, fws.FROM_TEMPLATE as FROM_TEMPLATE1, fws.SELECT_TEMPLATE as SELECT_TEMPLATE1, ft.CAN_BE_STIFLE
FROM FREQUENCY_TABLE ft
INNER JOIN FROM_WHERE_STATEMENTS fws on fws.ID = ft.FIRST_STATEMENT) res
INNER JOIN FROM_WHERE_STATEMENTS fws2 on fws2.ID = res.SECOND_STATEMENT;

commit;
END CALCULATE_FREQUENCY;';

     execute immediate 
  '
create or replace PROCEDURE MARK_STATEMENTS AS 

     seq number (10,0);
     maxtime timestamp(0);
     mintime timestamp(0);
     working_days number;
      i number;
BEGIN
seq := 1;
  execute immediate ''TRUNCATE TABLE FROM_WHERE_STATEMENTS'';
  

  INSERT INTO FROM_WHERE_STATEMENTS
  (
    ID,
    WHERE_TEMPLATE,
    FROM_TEMPLATE,
    SELECT_TEMPLATE,
    COUNT,
    MIN_TIME,
   MAX_TIME,
   DISTINCT_IPS_COUNT
  )
  SELECT 0, WHERE_STATEMENT, FROM_STATEMENT, SELECT_STATEMENT, count(*), min(thetime), max(thetime), count(distinct clientip)
FROM PARSED_STATEMENTS
group by FROM_STATEMENT, WHERE_STATEMENT, SELECT_STATEMENT
order by count(*) desc;
commit;

select min(thetime), max(thetime) into mintime, maxtime
from PARSED_STATEMENTS;

select trunc(extract(day from maxtime  - mintime)) into working_days from dual;

if (working_days = 0)
then working_days := 1;
end if;
     update FROM_WHERE_STATEMENTS
    set ID = rownum,
    OCCURANCE_PART = trunc(extract(day from max_time  - min_time)) / working_days
    ;
     
  commit;  
END MARK_STATEMENTS;
';

     execute immediate 
  '
create or replace PROCEDURE PROCEDURE_UPDATE_STAT AS 
   curr_time timestamp;
next_time timestamp;
max_time timestamp;
row_count number;
count_ number := -1;
BEGIN
 execute immediate ''TRUNCATE TABLE TABLE_UPDATED_TIME'';
  select count(*) into row_count
  from TABLE_UPDATED_TIME;
  
  if (row_count = 0) then
  begin 
  select min(thetime)  into curr_time from PARSED_STATEMENTS;
  INSERT INTO TABLE_UPDATED_TIME
  (CURRENTUPDATEDTIME
  ) VALUES
  (curr_time );
  end;
  end if;
  select CURRENTUPDATEDTIME into curr_time from TABLE_UPDATED_TIME;
  
  select max(thetime)  into max_time from PARSED_STATEMENTS;
  --select curr_time + 100 into max_time from dual;
  while (curr_time < max_time) loop
  select curr_time +1/1440 into next_time from dual;
      UPDATE PARSED_STATEMENTS PS
      SET STAT_ID =  
      (SELECT ID FROM FROM_WHERE_STATEMENTS WHERE ((WHERE_TEMPLATE = PS.WHERE_STATEMENT) OR (WHERE_TEMPLATE IS NULL and ps.where_statement is null)) AND FROM_TEMPLATE = PS.FROM_STATEMENT AND SELECT_TEMPLATE = PS.SELECT_STATEMENT)
      WHERE PS.thetime >=curr_time and PS.thetime < next_time;      

  curr_time := next_time;
   update TABLE_UPDATED_TIME
    set CURRENTUPDATEDTIME = curr_time;
    commit;
 end loop;
END PROCEDURE_UPDATE_STAT;';

execute immediate
'
create or replace PROCEDURE PARSE_STATEMENT AS 
   curr_time timestamp;
next_time timestamp;
max_time timestamp;
row_count number;
   cursor statements_cursor is
     SELECT *
     FROM T_ORIG_LOG
     where thetime >=curr_time and thetime < next_time;
     
     seq number (10,0);
     
     --split statement
v_statement varchar2(4000) :='''';
v_select_part varchar2(4000) :='''';
v_from_part varchar2(4000) :='''';
v_where_part varchar2(4000) :='''';
position_select pls_integer :=-1;
position_from pls_integer :=-1;
position_where pls_integer :=-1;
position_order pls_integer :=-1;
length_select pls_integer :=-1;
length_from pls_integer :=-1;
length_where pls_integer :=-1;
V_can_be_stifle  pls_integer :=-1;

where_part_template varchar2(8000) :='''';
from_part_template varchar2(8000) :='''';
where_part_real varchar2(8000) :='''';
from_part_real varchar2(8000) :='''';
lower_select_part varchar2(8000) := '''';
lower_from_part varchar2(8000) := '''';
lower_where_part_template varchar2(8000) := '''';

type int_array is varray(5) of pls_integer;

array_positions int_array := int_array(-1,-1,-1);
position_nextStatement pls_integer;

function getPosition (statement in varchar2, sqlElement in varchar2)
return pls_integer is
j pls_integer :=1;
position_element pls_integer := -1;
partleng pls_integer :=0;
begin
while position_element = -1 loop
exit when INSTR(statement,sqlElement,1,j) = 0;
partleng := length(REGEXP_REPLACE(substr(statement,1,INSTR(statement,sqlElement,1,j)),''[^(]''))-length(REGEXP_REPLACE(substr(statement,1,INSTR(statement,sqlElement,1,j)),''[^)]''));
if partleng <=0 or partleng is null then
position_element := INSTR(statement,sqlElement,1,j);
else j := j + 1;
end if;
end loop;
return position_element;
end;

function getNextStatementPos (startStatement in pls_integer, nextStatement in int_array, minPosition in out pls_integer)
  return pls_integer is
  j pls_integer :=1;
  begin
  if startStatement < 0 then return -1; end if;
  while j <=3 loop
  if nextStatement(j) != 0 and nextStatement(j) < minPosition and nextStatement(j) > startStatement then minPosition := nextStatement(j); end if;
  j := j+1;
  end loop;
  return minPosition;
end;

BEGIN
execute immediate ''TRUNCATE TABLE PARSED_STATEMENTS'';
  execute immediate ''TRUNCATE TABLE TABLE_UPDATED_TIME'';
  commit;
   select count(*) into row_count
  from TABLE_UPDATED_TIME;
  
  if (row_count = 0) then
  begin 
  select min(thetime)  into curr_time from T_ORIG_LOG;
  INSERT INTO TABLE_UPDATED_TIME
  (CURRENTUPDATEDTIME
  ) VALUES
  (curr_time );
  end;
  end if;
  select CURRENTUPDATEDTIME into curr_time from TABLE_UPDATED_TIME;
  
  select max(thetime)  into max_time from T_ORIG_LOG;
  --select curr_time + 100 into max_time from dual;
  while (curr_time < max_time) loop
   select curr_time +1/1440 into next_time from dual;
BEGIN
   FOR statement_rec in statements_cursor
   LOOP
begin
   v_statement  :='''';
v_select_part :='''';
v_from_part  :='''';
v_where_part  :='''';
position_select  :=-1;
position_from  :=-1;
position_where :=-1;
length_select :=-1;
length_from :=-1;
length_where :=-1;
V_can_be_stifle := 0;


where_part_template  :='''';
from_part_template  :='''';
lower_select_part := '''';
lower_from_part := '''';
lower_where_part_template  := '''';
      
    
      v_statement := REPLACE(LOWER(statement_rec.statement),''[br]'','' '');
      if not (v_statement like ''%--%'') then
      begin
    position_select := INSTR(v_statement,''select'');
    if (position_select = 1) then
    begin
    position_from := getPosition(v_statement, ''from'');
    position_where := getPosition(v_statement, ''where'');
    position_order := getPosition(v_statement, ''order'');
    
    array_positions(1):=position_from;
    array_positions(2):=position_where;
    array_positions(3):=position_order;
    
    position_nextStatement := length(v_statement)+1;
    length_select := getNextStatementPos(position_select,array_positions,position_nextStatement)-position_select-6;
    position_nextStatement := length(v_statement)+1;
    length_from := getNextStatementPos(position_from,array_positions,position_nextStatement)-position_from-4;
    position_nextStatement := length(v_statement)+1;
    length_where := getNextStatementPos(position_where,array_positions,position_nextStatement)-position_where-5;
    v_select_part := LTRIM(SUBSTR(v_statement,position_select+6,length_select));
    v_from_part := LTRIM(SUBSTR(v_statement,position_from+4,length_from));
    v_where_part := LTRIM(SUBSTR(v_statement,position_where+5,length_where));



--generalize WHERE_PART
where_part_template := v_where_part;
where_part_template := REGEXP_REPLACE(where_part_template,''(\s)+='',''='');
where_part_template := REGEXP_REPLACE(where_part_template,''=(\s)'',''='');
where_part_template := REGEXP_REPLACE(where_part_template,''(\s)+<'',''<'');
where_part_template := REGEXP_REPLACE(where_part_template,''<(\s)'',''<'');
where_part_template := REGEXP_REPLACE(where_part_template,''(\s)+>'',''>'');
where_part_template := REGEXP_REPLACE(where_part_template,''>(\s)'',''>'');
where_part_template := REGEXP_REPLACE(where_part_template,''(\s)+'','' '');
where_part_template := REGEXP_REPLACE(where_part_template,''[(]\s+'',''('');
where_part_template := REGEXP_REPLACE(where_part_template,''\s[)]+'','')'');
where_part_template := REGEXP_REPLACE(where_part_template,''[,]\s+'','','');
where_part_template := REGEXP_REPLACE(where_part_template,''\s[,]+'','','');
where_part_template := REGEXP_REPLACE(where_part_template,''[[]'','''');
where_part_template := REGEXP_REPLACE(where_part_template,''[]]'','''');
where_part_template := RTRIM(where_part_template);
where_part_template := LTRIM(where_part_template);
where_part_real := where_part_template;
where_part_template := REGEXP_REPLACE(where_part_template,''(0x[[:xdigit:]]+)|([-]?((\d)+([,.x:](\d)+)*)+)'',''NUM''); --Replace Numbers and Hexadecimal Numbers
where_part_template := replace(where_part_template,''.NUM'',''NUM'');
where_part_template := REGEXP_REPLACE(where_part_template,''([''''"]+\w*[''''"]+)'',''STRING''); --Replace Strings     
where_part_template := REGEXP_REPLACE(where_part_template,''[''''][%][^%]*[%]['''']'',''LIKE_STRING''); --Replace Strings     
V_can_be_stifle := getPosition(where_part_template, ''and'');
V_can_be_stifle := V_can_be_stifle - getPosition(where_part_template, ''or'');
if (V_can_be_stifle = 0) then
begin
if REGEXP_LIKE (where_part_template, ''^[^=()<>!&/%,-]+=NUM$'')
then V_can_be_stifle := 1;
end if;

if REGEXP_LIKE (where_part_template, ''^[^=()<>!&/%,-]+=STRING$'')
then V_can_be_stifle := 1;
end if;
end;
else
V_can_be_stifle := 0;
end if;

from_part_template := v_from_part;
from_part_template := REGEXP_REPLACE(from_part_template,''(\s)+='',''='');
from_part_template := REGEXP_REPLACE(from_part_template,''=(\s)'',''='');
from_part_template := REGEXP_REPLACE(from_part_template,''(\s)+<'',''<'');
from_part_template := REGEXP_REPLACE(from_part_template,''<(\s)'',''<'');
from_part_template := REGEXP_REPLACE(from_part_template,''(\s)+>'',''>'');
from_part_template := REGEXP_REPLACE(from_part_template,''>(\s)'',''>'');
from_part_template := REGEXP_REPLACE(from_part_template,''\s'','' '');
from_part_template := REGEXP_REPLACE(from_part_template,''[(]\s+'',''('');
from_part_template := REGEXP_REPLACE(from_part_template,''\s[)]+'','')'');
from_part_template := REGEXP_REPLACE(from_part_template,''[,]\s+'','','');
from_part_template := REGEXP_REPLACE(from_part_template,''\s[,]+'','','');
from_part_template := REGEXP_REPLACE(from_part_template,''[[]'','''');
from_part_template := REGEXP_REPLACE(from_part_template,''[]]'','''');
from_part_template := RTRIM(from_part_template);
from_part_template := LTRIM(from_part_template);
from_part_real := from_part_template;
from_part_template := REGEXP_REPLACE(from_part_template,''([-]?(\d)+(([.](\d)+)*))'',''NUM''); --Replace Numbers and Hexadecimal Numbers
from_part_template := replace(from_part_template,''.NUM'',''NUM'');
from_part_template := REGEXP_REPLACE(from_part_template,''([''''"]+\w*[''''"]+)'',''STRING''); --Replace Strings  

lower_select_part := lower(v_select_part);
lower_from_part := lower(from_part_template);
lower_where_part_template := lower(where_part_template);

INSERT INTO PARSED_STATEMENTS (yy, mm, dd, hh, mi, ss, seq, thetime, logid, clientip, requestor, server, dbname, acc, elapsed, busy, nrrows, statement, error, ERRORMESSAGE,
SELECT_STATEMENT, FROM_STATEMENT, WHERE_STATEMENT, FROM_ST_REAL, WHERE_ST_REAL,can_be_Stifle)
      VALUES (statement_rec.yy, statement_rec.mm, statement_rec.dd, 
      statement_rec.hh, statement_rec.mi, statement_rec.ss, statement_rec.seq, statement_rec.thetime, 
      1, ''clientip'', statement_rec.requestor, statement_rec.server, statement_rec.dbname, 
      statement_rec.acc, statement_rec.elapsed, statement_rec.busy, statement_rec.nrrows, 
      statement_rec.statement, statement_rec.error, statement_rec.ERRORMESSAGE, lower_select_part, lower_from_part, lower_where_part_template, from_part_real, where_part_real, V_can_be_stifle);
      commit;
end;
  end if;
  end;
  end if;
  EXCEPTION
  WHEN ZERO_DIVIDE THEN  -- handles ''division by zero'' error
     ROLLBACK;
   WHEN OTHERS THEN  -- handles all other errors
      ROLLBACK;
END;
   END LOOP;
END;
 curr_time := next_time;
 update TABLE_UPDATED_TIME
    set CURRENTUPDATEDTIME = curr_time;
    commit;
end loop;
END PARSE_STATEMENT;';

execute immediate 'create or replace PROCEDURE DELETE_DUPLICATES AS 
time_dupl pls_integer :=5;
BEGIN

  execute immediate ''TRUNCATE TABLE STATISTICS'';
  update T_ORIG_LOG t
set ERROR = 0;

--delete T_ORIG_LOG
--where nrrows > 500000;

delete T_ORIG_LOG t
where exists 
(
select seq 
from T_ORIG_LOG t1
where t.seq > t1.seq and t.STATEMENT = t1.STATEMENT and t.clientip = t1.clientip and t.logid = t1.logid and t.THETIME < t1.thetime
and ( CAST(t1.thetime AS DATE ) - CAST(t.thetime AS DATE ) ) * 86400 < time_dupl);

delete T_ORIG_LOG t
where exists 
(
select seq 
from T_ORIG_LOG t1
where t.seq > t1.seq and t.STATEMENT = t1.STATEMENT and t.clientip = t1.clientip and t.logid = t1.logid and t.THETIME = t1.thetime);
END DELETE_DUPLICATES;';

execute immediate 'create or replace PROCEDURE UPDATE_VIEW_SERIES AS 
BEGIN
  UPDATE VIEW_SERIES vs
set ANTIPATTERNTYPE = NULL;

update VIEW_SERIES vs
set ANTIPATTERNTYPE = 0;

UPDATE VIEW_SERIES vs
set ANTIPATTERNTYPE = 2
where vs.WHERE_TEMPLATE1 = vs.WHERE_TEMPLATE2 and vs.FROM_TEMPLATE1 = vs.FROM_TEMPLATE2 and vs.SELECT_TEMPLATE1 <> vs.SELECT_TEMPLATE2 and vs.CAN_BE_STIFLE = 1;

UPDATE VIEW_SERIES vs
set ANTIPATTERNTYPE = 1
where vs.WHERE_TEMPLATE1 = vs.WHERE_TEMPLATE2 and vs.FROM_TEMPLATE1 = vs.FROM_TEMPLATE2 and vs.SELECT_TEMPLATE1 = vs.SELECT_TEMPLATE2 and vs.CAN_BE_STIFLE = 1;

UPDATE VIEW_SERIES vs
set ANTIPATTERNTYPE = 4
where vs.WHERE_TEMPLATE1 = vs.WHERE_TEMPLATE2 and vs.FROM_TEMPLATE1 <> vs.FROM_TEMPLATE2 and vs.CAN_BE_STIFLE = 1;

UPDATE FROM_WHERE_STATEMENTS
set IS_SIMPLE = 1 
where (REGEXP_LIKE(WHERE_TEMPLATE, ''^[^=()<>!&/%,-]+=num$''))
and not WHERE_TEMPLATE like ''%and%'' and not WHERE_TEMPLATE like ''%or%'';


UPDATE FROM_WHERE_STATEMENTS
set IS_SIMPLE = 1 
where (REGEXP_LIKE( where_template, ''^[^=()<>!&/%,-]+=string$''))
and not WHERE_TEMPLATE like ''%and%'' and not WHERE_TEMPLATE like ''%or%'';

update VIEW_SERIES vs
set vs.ANTIPATTERNTYPE = 8
where vs.SECOND_STATEMENT in (select id from FROM_WHERE_STATEMENTS where IS_SIMPLE = 1)
and vs.SECOND_STATEMENT <> vs.FIRST_STATEMENT 
and not exists (select id from FROM_WHERE_STATEMENTS where id = vs.FIRST_STATEMENT and IS_SIMPLE = 1); 
     

END UPDATE_VIEW_SERIES;';


BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE T_TRANSFORMED_LOG';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;

   
execute immediate 'CREATE TABLE T_TRANSFORMED_LOG
   (	YY NUMBER(4,0) NOT NULL ENABLE, 
	MM NUMBER(2,0) NOT NULL ENABLE, 
	DD NUMBER(2,0) NOT NULL ENABLE, 
	HH NUMBER(2,0) NOT NULL ENABLE, 
	MI NUMBER(2,0) NOT NULL ENABLE, 
	SS NUMBER(2,0) NOT NULL ENABLE, 
	SEQ NUMBER(10,0) NOT NULL ENABLE, 
	THETIME TIMESTAMP (0) NOT NULL ENABLE, 
	LOGID NUMBER, 
	CLIENTIP VARCHAR2(20 BYTE), 
	REQUESTOR VARCHAR2(40 BYTE), 
	SERVER VARCHAR2(20 BYTE), 
	DBNAME VARCHAR2(20 BYTE), 
	ACC VARCHAR2(20 BYTE), 
	ELAPSED NUMBER, 
	BUSY NUMBER, 
	NRROWS NUMBER, 
	STATEMENT VARCHAR2(4000 BYTE), 
	ERROR NUMBER(5,0) NOT NULL ENABLE, 
	ERRORMESSAGE VARCHAR2(100 BYTE), 
  IS_MODIFIED NUMBER,
  CONSTRAINT D_PK_'||substr(sys_guid(), 1, 8)||' PRIMARY KEY (THETIME, SEQ)
   )  ';

execute immediate 'CREATE INDEX I_'||substr(sys_guid(), 1, 8) ||' ON T_TRANSFORMED_LOG (CLIENTIP, LOGID) ';
  
execute immediate 'create or replace PROCEDURE REPAIRE_ANTIPATTERNS AS 
  
  new_query varchar2(8000) :='''';

  curr_pattern number := -1;
  has_pattern number := 0;

  new_stifle1 number:= 1;
  new_query_row_count number :=0;
  
    ROWS_BEFORE_ number := 0;
    ROWS_AFTER_ number := 0;
    ROWS_MODIFIED_ number := 0;
    
  prev_tuple PARSED_STATEMENTS%rowtype;
  curr_tuple PARSED_STATEMENTS%rowtype;
  
BEGIN
 
 execute immediate ''TRUNCATE TABLE T_TRANSFORMED_LOG'';
  update preprocessed_data set repaired_stifle=0;
  commit;
 begin 
     curr_tuple.CLIENTIP := '''';
     curr_tuple.LOGID := -1;
     curr_tuple.STAT_ID := -1;
  FOR tuple IN (SELECT * FROM PARSED_STATEMENTS  where CAN_BE_STIFLE=1
    order by CLIENTIP, LOGID, stat_id, thetime, seq) 
  LOOP

  begin
    --getOlder
    prev_tuple := curr_tuple;
    
    --init current
    curr_tuple := tuple;
    
    if ((prev_tuple.CLIENTIP = curr_tuple.CLIENTIP) AND (prev_tuple.LOGID = curr_tuple.LOGID)) then
    begin 
      select count(*) into has_pattern
      from VIEW_SERIES
      where (FIRST_STATEMENT = prev_tuple.STAT_ID) and (SECOND_STATEMENT = curr_tuple.STAT_ID);
      
      if (has_pattern > 0) then
      begin 
       select ANTIPATTERNTYPE into curr_pattern
        from VIEW_SERIES
        where (FIRST_STATEMENT = prev_tuple.STAT_ID) and (SECOND_STATEMENT = curr_tuple.STAT_ID);
        

          if (curr_pattern = 1) then
          begin
            if (new_stifle1 = 1) then
            begin
              new_query := ''select ''|| prev_tuple.SELECT_STATEMENT || '' from '' || prev_tuple.FROM_ST_REAL || '' where '' || prev_tuple.WHERE_ST_REAL || '' OR '' || curr_tuple.WHERE_ST_REAL;
              new_query_row_count := prev_tuple.NRROWS + curr_tuple.NRROWS;
              new_stifle1 := 0;
            end;
            else
            begin
              new_query := new_query || '' OR '' || curr_tuple.WHERE_ST_REAL;
              new_query_row_count := new_query_row_count + curr_tuple.NRROWS;
              if (length(new_query) > 3800) then
              begin
              if (new_query is not null) then
              INSERT
              INTO T_TRANSFORMED_LOG
                (
                  YY,
                  MM,
                  DD,
                  HH,
                  MI,
                  SS,
                  SEQ,
                  THETIME,
                  LOGID,
                  CLIENTIP,
                  REQUESTOR,
                  SERVER,
                  DBNAME,
                  ACC,
                  ELAPSED,
                  BUSY,
                  NRROWS,
                  STATEMENT,
                  ERROR,
                  ERRORMESSAGE,
                  IS_MODIFIED
                )
                VALUES
                (
                  tuple.YY,
                  tuple.MM,
                  tuple.DD,
                  tuple.HH,
                  tuple.MI,
                  tuple.SS,
                  tuple.SEQ,
                  tuple.THETIME,
                  tuple.LOGID,
                  tuple.CLIENTIP,
                  tuple.REQUESTOR,
                  tuple.SERVER,
                  tuple.DBNAME,
                  tuple.ACC,
                  tuple.ELAPSED,
                  tuple.BUSY,
                  new_query_row_count,
                  new_query,
                  1,
                  tuple.ERRORMESSAGE,
                  1
                );
                end if;
                new_query := ''select ''|| prev_tuple.SELECT_STATEMENT || '' from '' || prev_tuple.FROM_ST_REAL || '' where '' || prev_tuple.WHERE_ST_REAL || '' OR '' || curr_tuple.WHERE_ST_REAL;
                new_stifle1 := 0;
                new_query_row_count := prev_tuple.NRROWS + curr_tuple.NRROWS;
              end;
              end if;
            end;
            end if;
          end;
          else
          begin 
            if (curr_pattern = 2) then
            begin
            
            if (new_query is not null) then
              INSERT
              INTO T_TRANSFORMED_LOG
                (
                  YY,
                  MM,
                  DD,
                  HH,
                  MI,
                  SS,
                  SEQ,
                  THETIME,
                  LOGID,
                  CLIENTIP,
                  REQUESTOR,
                  SERVER,
                  DBNAME,
                  ACC,
                  ELAPSED,
                  BUSY,
                  NRROWS,
                  STATEMENT,
                  ERROR,
                  ERRORMESSAGE,
                  IS_MODIFIED
                )
                VALUES
                (
                  prev_tuple.YY,
                  prev_tuple.MM,
                  prev_tuple.DD,
                  prev_tuple.HH,
                  prev_tuple.MI,
                  prev_tuple.SS,
                  prev_tuple.SEQ,
                  prev_tuple.THETIME,
                  prev_tuple.LOGID,
                  prev_tuple.CLIENTIP,
                  prev_tuple.REQUESTOR,
                  prev_tuple.SERVER,
                  prev_tuple.DBNAME,
                  prev_tuple.ACC,
                  prev_tuple.ELAPSED,
                  prev_tuple.BUSY,
                  new_query_row_count,
                  new_query,
                  0,
                  prev_tuple.ERRORMESSAGE,
                  1
                );
                end if;
                new_query := null;
                new_stifle1 := 1;

              new_query := ''select ''|| prev_tuple.SELECT_STATEMENT ||'',''  || curr_tuple.SELECT_STATEMENT || '' from '' || curr_tuple.FROM_ST_REAL || '' where '' || curr_tuple.WHERE_ST_REAL;
              new_query_row_count := curr_tuple.NRROWS;
              --write new_query
              if (new_query is not null) then
              INSERT
              INTO T_TRANSFORMED_LOG
                (
                  YY,
                  MM,
                  DD,
                  HH,
                  MI,
                  SS,
                  SEQ,
                  THETIME,
                  LOGID,
                  CLIENTIP,
                  REQUESTOR,
                  SERVER,
                  DBNAME,
                  ACC,
                  ELAPSED,
                  BUSY,
                  NRROWS,
                  STATEMENT,
                  ERROR,
                  ERRORMESSAGE,
                  IS_MODIFIED
                )
                VALUES
                (
                  tuple.YY,
                  tuple.MM,
                  tuple.DD,
                  tuple.HH,
                  tuple.MI,
                  tuple.SS,
                  tuple.SEQ,
                  tuple.THETIME,
                  tuple.LOGID,
                  tuple.CLIENTIP,
                  tuple.REQUESTOR,
                  tuple.SERVER,
                  tuple.DBNAME,
                  tuple.ACC,
                  tuple.ELAPSED,
                  tuple.BUSY,
                  tuple.NRROWS,
                  new_query,
                  2,
                  tuple.ERRORMESSAGE,
                  1
                ); end if;
              new_query := null;
              new_stifle1 := 1;

            end;
            else
            begin
            --write both queries
            
            if (new_query is not null) then
              INSERT
              INTO T_TRANSFORMED_LOG
                (
                  YY,
                  MM,
                  DD,
                  HH,
                  MI,
                  SS,
                  SEQ,
                  THETIME,
                  LOGID,
                  CLIENTIP,
                  REQUESTOR,
                  SERVER,
                  DBNAME,
                  ACC,
                  ELAPSED,
                  BUSY,
                  NRROWS,
                  STATEMENT,
                  ERROR,
                  ERRORMESSAGE,
                  IS_MODIFIED
                )
                VALUES
                (
                  prev_tuple.YY,
                  prev_tuple.MM,
                  prev_tuple.DD,
                  prev_tuple.HH,
                  prev_tuple.MI,
                  prev_tuple.SS,
                  prev_tuple.SEQ,
                  prev_tuple.THETIME,
                  prev_tuple.LOGID,
                  prev_tuple.CLIENTIP,
                  prev_tuple.REQUESTOR,
                  prev_tuple.SERVER,
                  prev_tuple.DBNAME,
                  prev_tuple.ACC,
                  prev_tuple.ELAPSED,
                  prev_tuple.BUSY,
                  new_query_row_count,
                  new_query,
                  1,
                  prev_tuple.ERRORMESSAGE,
                  1
                );
                end if;

                new_query := null;
                new_stifle1 := 1;

            end;
            end if;
          end;
          end if;

        
        
      end;
      end if;
      
    end;
    else
    begin 
       if (new_query is not null) then
        INSERT
         INTO T_TRANSFORMED_LOG
                      (
                        YY,
                        MM,
                        DD,
                        HH,
                        MI,
                        SS,
                        SEQ,
                        THETIME,
                        LOGID,
                        CLIENTIP,
                        REQUESTOR,
                        SERVER,
                        DBNAME,
                        ACC,
                        ELAPSED,
                        BUSY,
                        NRROWS,
                        STATEMENT,
                        ERROR,
                        ERRORMESSAGE, 
                        IS_MODIFIED
                      )
                      VALUES
                      (
                        prev_tuple.YY,
                        prev_tuple.MM,
                        prev_tuple.DD,
                        prev_tuple.HH,
                        prev_tuple.MI,
                        prev_tuple.SS,
                        prev_tuple.SEQ,
                        prev_tuple.THETIME,
                        prev_tuple.LOGID,
                        prev_tuple.CLIENTIP,
                        prev_tuple.REQUESTOR,
                        prev_tuple.SERVER,
                        prev_tuple.DBNAME,
                        prev_tuple.ACC,
                        prev_tuple.ELAPSED,
                        prev_tuple.BUSY,
                        new_query_row_count,
                        new_query,
                        4,
                        prev_tuple.ERRORMESSAGE,
                        1
                      ); end if;
      new_query := null;
      new_stifle1 := 1;

    end;
    end if;
  end;    
    
end loop;


INSERT
INTO T_TRANSFORMED_LOG
  (
    YY,
    MM,
    DD,
    HH,
    MI,
    SS,
    SEQ,
    THETIME,
    LOGID,
    CLIENTIP,
    REQUESTOR,
    SERVER,
    DBNAME,
    ACC,
    ELAPSED,
    BUSY,
    NRROWS,
    STATEMENT,
    ERROR,
    ERRORMESSAGE
  )

SELECT 
YY,
    MM,
    DD,
    HH,
    MI,
    SS,
    SEQ,
    THETIME,
    LOGID,
    CLIENTIP,
    REQUESTOR,
    SERVER,
    DBNAME,
    ACC,
    ELAPSED,
    BUSY,
    NRROWS,
    STATEMENT,
    ERROR,
    ERRORMESSAGE
    FROM PARSED_STATEMENTS  where CAN_BE_STIFLE=0;
    
    select count(*) into ROWS_BEFORE_
  from T_ORIG_LOG;
  
    select count(*) into ROWS_AFTER_
  from T_TRANSFORMED_LOG;
  
    select count(*) into ROWS_MODIFIED_
  from T_TRANSFORMED_LOG
  where IS_MODIFIED =1;
    INSERT
INTO STATISTICS
  (
    STEP_TIME,
    ROWS_BEFORE,
    ROWS_AFTER,
    ROWS_MODIFIED
  )
  VALUES
  (
    CURRENT_TIMESTAMP,
    ROWS_BEFORE_,
    ROWS_AFTER_,
    ROWS_MODIFIED_
  );
commit; 
end;

END REPAIRE_ANTIPATTERNS;';

BEGIN

   execute immediate '
  CREATE TABLE STATISTICS 
   (	STEP_TIME TIMESTAMP (6), 
	ROWS_BEFORE NUMBER, 
	ROWS_AFTER NUMBER, 
	ROWS_MODIFIED NUMBER
   );';

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;


END CREATE_TABLES;