Freitag, 25. November 2011

Toggle the damn button...

I know, I will be killed by my test manager(for creating moving targets), but I have to choose between html buttons (could be disabled) and template based buttons(no way to be disabled but stylish!!!). I decided to go for template based buttons and hide them (Sorry for the testteam).

I really don't like buttons that ask me if I really want to do something (delete) and tell me later that I should have picked something as a target first (0 rows deleted). So in the best possible world I would disable the delete button as long as no record is marked (checkbox) checked and enable the button otherwise. Unfortunatly that does not work, so I hide/show the button instead.

To be able to toggle the delete button I have to identify the button. Firebug shows these credetials.

<a id="B3926323806548112" class="uButton" role="button" href="javascript:apex.confirm(htmldb_delete_message,'DELETE');"><span>Delete</span> </a>

I don't want to deal with cryptic id's so I set the the attribute Static Id of the button to P3_DELETE_BUTTON. Aaahhh much better.

<a id="P3_DELETE_BUTTON" class="uButton" role="button" href="javascript:apex.confirm(htmldb_delete_message,'DELETE');"><span>Delete</span> </a>

Now I'm able to hide the button already during page start(I'm sure that there is no marked record when the page loads). I simply add this code

$('#P3_DELETE_BUTTON').hide();

to the JavaScript/Execute when Page Loads section. A Test shows that the button is not visible.

To do the toggling I add some functions to the JavasScript/Function and Global Variable Declaration section. (And I'm even more a Novice to JavaScript then to APEX)

var htmldb_delete_message='"DELETE_CONFIRM_MSG"';
var headername;

function onclick_all(self,button,name){
  //global variable to store the name of the header in the checkbox
  headername = self.name;
  $f_CheckFirstColumn(self); 
  if (self.checked){
    $(button).show();
    return;
  }else{
    $(button).hide();
    return;
  }
}

function onclick_single(self,button){
  if (self.checked){
    $(button).show();
    return;
  }else{
    var checkboxes = document.getElementsByName(self.name);
    if (headername) document.getElementsByName(headername)[0].checked = false;
    var boxes = checkboxes.length;
    for (i = 0; i < boxes; i++) {
      if (checkboxes[i].checked) return;
    }
    $(button).hide();
    return;
  }
}

So pleace have mercy! I have also added some functionality to uncheck the header whenever a single record is unchecked.

I have to capture the onClick event of the checkboxes. To do this I add thechange the call to apex_item funtcion to

SELECT apex_item.checkbox2(1
                          ,runid
                          ,'onclick="onclick_single(this,''#P3_DELETE_BUTTON'')"') as 
       chk
      ,...

Runing the query in the SQLDeveloper returns the following output.

<input type="checkbox" name="f01" value="4" onclick="onclick_single(this,'#P3_DELETE_BUTTON')" />

So I change the Column Heading of column CHK to

<input type="Checkbox" name="f01_header" onclick="onclick_all(this,'#P3_DELETE_BUTTON');">

Fine!

Source

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!!!

Establish the multi row delete button...

Unfortunatly there is no delete button on page 3. I could create one myself, but I prefer to copy the delete button and its behaviour from page 4 and modify it.
I copy the button region PLSQL Profiler Runs.

I run page 3, click on my button and nothing happens (That's ok, because I have only copied the button yet).
So I activate firebug (F12 in the most cases) and find the message "htmldb_delete_message is not defined" on the konsole.



I copy the javascript definition from page 4

var htmldb_delete_message='"DELETE_CONFIRM_MSG"';

and give it another try. I confirm the delete message, and nothing happens. No message can be found in firebug this time, so I have a look on the definition of the button.
First I remove the condition pointing on P4_RUNID. A simple condition will not help in our case.
Then I take a look at the "URL Target".

javascript:apex.confirm(htmldb_delete_message,'DELETE');

Ok, looks like if I confirm the htmldb_delete_message there will be a DELETE request submitted.

On page 4 the process Process Row of PLSQL_PROFILER_RUNS is handling this request, but this process is of type "Automatic Row Processing (DML)" and does not handle multiple rows.
So I will have to create a new one.
I create a new "Page processing" process with the name Process Row of PLSQL_PROFILER_RUNS on page 3. The condition has to be set to "Request = Expression 1" and Expression 1 of course to "DELETE".

This will be the source (for now)

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

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

Our checkboxes have the name f01 (see the last post) and all checked runid's will end up in the apex_application.g_f01 collection.

Everything could be fine, but when select at least on record and press the button ORA-02292 is shown. I'm trapped by referential integrity.

The same message is shown when I try to delete a single record on page 4.

To be continued...

Donnerstag, 24. November 2011

A checkbox to slave them all...

The checkboxes look nice so far, but I don't like the header "Chk". I would prefer to have a checkbox instead that checks/unchecks all visible checkboxes in that column. One day I came across Duncan Mein's Blog (At this point I would like to thank all the authors of all the wonderful posts I have not mentioned so far) and he gave the hint to replace the Column Heading with the following code.

<input type="Checkbox" onclick="$f_CheckFirstColumn(this)">

So I replaced the Column heading of the column named CHK to receive the following (working) result.


Of course I had to unselect all "Allow Users To" funtcions to ged rid of some freaky behaviour

Pimp the Interactive Report...

I like the interactive Report much more then the Tabular form, but now I want to add some functionality to the Interactive Report on Page 3. I would like to implement a multi record delete button. First I have to be able to select multiple in the interactive report. The best way to achieve this is to select the function apex_item.checkbox2.

Our query has to look like this


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
  (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
  )

change the "Display Text As" of the new Chk item to "Standard Report Column" and reorganize the columns of the report and you will achieve this result.



run the query in sqldeveloper (or something similar) and you will get this output for record 1 column chk

<input type="checkbox" name="f01" value="3"  />

youn will find the same code with firebug to.

An entry point to Page 4...

Page 4 is available now, but there is no entry point into this Page. I will link the records from page 3 to page 4.


  • open the Report Attributes => Link Column tab for the region PLSQL Profiler Runs
  • set the link column to "Link to custom Target"
  • pick a nice Link Item
  • set Target to "Page in this Application" and Page to 4
  • Name to "P4_RUNID" and Value to "#RUNID


confirm the changes and the Page looks like this



A click on the link Item will navigate to Page 4 and *it's magic* will fill the Form with the linked record.




Don't think about Page 4 in the moment! First we will add some more functionality to page 3.

A form region to update Profiler Run information

Ok lets gets ome life into Page 4. I know that we could have done it in one step (shame on me). At this point in time it will be just a location where I can link to, but don't worry, I will fix that soon.

I start with creating a region on Page 4


choose "Form" as type for the region.


decide to use a "Form on Table or View" as form type


The Form is based on the "plsql_profiler_runs" table


I only change the region name PLSQL Profiler Runs and the Region template Form Region. Everything else has been defined during the page creation and will remain.


I set the runid as primary key column and keep "Existing trigger" as Source type. I doesn't matter anyhow, because we don't want to create Data in this application.



In the next steps I pick the columns that I want to see in the form (everything except spare and comment1 for now)


prevent the creation of the "create" button


define page 3 as branch target


and confirm my decisions.


And that result will look like this


Breadcrumbs and Tabs are just fine. I will come back later to change the behaviour of the form. The cancel button branches to page 3 no way back so far.

Mittwoch, 23. November 2011

The next page...

I add a blank page in advance. I add it now, because I want to control the page numbers. This post will explain the necessary steps in detail.

I will only do this once. If you are an advanced APEX developer please skip this post!

I create a blank page (no rocket science)


I force it to have page number 4, because I like to have straight numbers (maybe I have to rethink my numbering for bigger apps).


I choose the name PLSQL Profiler Run Details for this page and the breadcrumb and pick the breadcrumb "PLSQL Profiler Runs" as parent entry.  Breadcrumb done without any extra steps.


I connect this page to the "Profiler" tab in the already existing tab set. Tabs done without any extra steps.


I confirm my changes.


And the result looks like this (approx 3 minutes later, and I'm still a novice).



Ps.: It does not make any sence to clone this page. You will have to pick the breadcrumb and tab settings anyhow. Next time I will build a master detail page out of this empty one.

Dienstag, 22. November 2011

Did I mention ...

that I'm not a PixelPusher?

PLSQL Profiler Runs => Adding a Gauge

What I like most about PLSQL Developer when it comes to profiler handling, is the representation of Share of total time as Gauge. Consider that max(run_total_time) is 100%, how much share in % does our actual run take? And how can I create that Gauge as part of an interactive Report?

I know that it must be possible somehow, because I have seen a Gauge on the "View Debug" screen!

It took me some time to find a way to implement the Gauge. One day I discovered a post showing how to add a Image to an Interactive report. They simple added HTML tags to the query. So I decided to give it a try.

First I have to adjust the Query to get the 100% value.

SELECT runid ,
  related_run ,
  run_owner ,
  run_date ,
  run_comment ,
  run_total_time ,
  run_comment1,
  max_run_total_time
FROM
  (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
  )

Don't question the Query for now. I know that the performance will be bad, but we will have to change the query anyhow later.

I'm not an HTML expert, so I will have to copy the tag somewhere. I switch on firebug and investigate the elements of the "debug view".


 I use the context menu to copy the HTML code.

<div style="background: #cccccc; border-bottom: 1px solid #ffffff; border-left: 1px solid 
#aaaaaa; border-right: 1px solid #ffffff; border-top: 1px solid #aaaaaa; height: 14px; width: 100px;">
<img alt="33" border="0" height="14" src="/i/1px_trans.gif" style="background: #777777;" width="33" />
</div>

After some thoughts my query will look like this :)
SELECT runid ,
  related_run ,
  run_owner ,
  run_date ,
  run_comment ,
  run_total_time ,
  run_comment1,
  '<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
  (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
  )
This will add a new column graph to my report. I have to adjust the Report Attributes of the column graph to make it work. So I change the Display type to "Standard Report Column" and switch of all "Allow User To" functions except "Hide" and "Sort".


I have to select the column graph on the interactive report to make it visible and voila, my page looks like this.


Source => Maintenance Dashboard 4.0

Montag, 21. November 2011

PLSQL Profiler Runs => At least a start!

After taking so many (necessary) steps to move into the proper position, I will start to evolve the first profiler page now. In the moment the page looks fairly empty.
I create a kind of tabular form to show the information about all performed profiler runs. I like the rich functionality of interactive reports so I will use them instead of a tabular form.
So I select regions on page 3 and choose "create" from the context menu.

I choose "Report" as region type,
and specialize it as "Interactive Report".
The Title of the Report (Area) shall be Profiler Runs and the "Region Template" shall be Reports Region . We want to add some buttons later.

I use this query as source for the report:

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
FROM plsql_profiler_runs r;


I use the function to_timestamp to force the usage of the Timestamp NLS format within the report.


 Finally I Create the region and run the page. The report will be created in the region body.

and the page will look like this.

Source => Maintenance Dashboard 2.0

Some minor changes...

...without walktrough (I will support you if needed).


  • Changed Breadcrumb Titles
  • Changed some Page Titles
  • Change theme again to Crimson
  • Deleted the other themes
  • Enable debugging
I will add the sources after the next step.

Sonntag, 20. November 2011

Getting started...(3) adjust Tabs

I like the breadcrumbs and how they act (until now) so nothing to improve here. But Tabs only makes sense if there are more than one.
So I will rename the first tab to Maintenance Dashbord and add a second one named  Profiler on the same level. All profiler related pages will belong to the profiler tab.

Tabs are shared components yada yada :) So lets change (manage) them.

I click on the pen to edit the Home tab. I change the Tab label to Maintenance Dashboard and remove the pages 2,3 from the "Tab Also Current for Pages" edit box to reset the tab information for those pages and apply the changes (of course).


I click the dashed add button next to the Maintenance Dashboard tab to create a new tab on that level and label the new tab Profiler.



I set the "Tab Current for Page" to Page 2.


Keep the sequence 20.


No conditions.


And create the Tab.


Nearly done. I only have to set the "Current Tab" for Page 3.So I edit the profiler tab and add 3 to the "Tab Also Current for Pages" edit box.


And now lets enjoy the result.



So the basic work is done.
And here is the link => Maintenance Dashboard V1.0 (No rocket science so far)