Freitag, 25. November 2011

Creating the view(s)...


I have to sort out the referential integrity pit. I have a problem at least on two pages, so I take a general decision to avoid this pit.
My application will not access the tables directly anymore. I will create views instead. The information will be prepared in those views. Only html enhancements and apex API calls will be performed within the page.

CREATE OR REPLACE FORCE VIEW plsql_profiler_runs_vw
AS
  (SELECT r.runid ,
    r.related_run ,
    r.run_owner ,
    TO_TIMESTAMP(TO_CHAR(r.run_date, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') AS run_date ,
    r.run_comment ,
    r.run_total_time ,
    r.run_comment1,
    (SELECT MAX(run_total_time) FROM plsql_profiler_runs
    ) AS max_run_total_time
  FROM plsql_profiler_runs r
  )

Source

The referential integrity pit will be handled by an instead of trigger.

CREATE OR REPLACE TRIGGER plsql_profiler_runs_vw_io_trg INSTEAD OF
  DELETE OR
  UPDATE ON plsql_profiler_runs_vw REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE BEGIN IF DELETING THEN
  DELETE FROM plsql_profiler_data WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_units WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_runs WHERE runid = :old.runid;
ELSIF UPDATING THEN
  UPDATE plsql_profiler_runs
  SET run_comment = :new.run_comment
  WHERE runid     = :new.runid;
END IF;
END plsql_profiler_runs_vw_io_trg;
/

Source

I also have to handle the update functionality within the trigger. That gives me a chance to restrict the update on the column "run_comment". I get some security for free so to say.

I change the query of the interactive report,

SELECT apex_item.checkbox2 (1,runid) AS chk,
  runid ,
  related_run ,
  run_owner ,
  run_date ,
  run_comment ,
  run_total_time ,
  '>div style="width:100px;height:14px;background:#cccccc;border-top:1px solid #aaaaaa;border-left:1px solid #aaaaaa;border-bottom:1px solid #ffffff;border-right:1px solid #ffffff;"<>img src="/i/1px_trans.gif" width="'
  || ROUND((run_total_time * 100) / max_run_total_time, 0)
  || '" alt="40" height="14" border="0" style="background:#777777;"<>/div<' AS graph
FROM plsql_profiler_runs_vw

which takes away some complexity and gives me the chance to change the implementation without changing the gui. The source of the process has to be changed as well

BEGIN
  FORALL i IN apex_application.g_f01.FIRST .. apex_application.g_f01.LAST
    DELETE FROM plsql_profiler_runs_vw
    WHERE       runid = apex_application.g_f01(i);

  apex_application.g_print_success_message := SQL%ROWCOUNT || ' row(s) deleted';
END;

A click on the button deletes all marked runs and shows a message telling me the number of deleted rows. All relational data is gone as well.



=> Source Page 4 is far from being usable!!!

Keine Kommentare:

Kommentar veröffentlichen