Our ODTUG presentation about the QA-tool

Like Nathalie Roman announced in the previous blog I had the honour to give my first presentation abroad last Monday at the Big Easy.

It was Jan Huyzentruyt’s idea to work out an Apex QA-tool for our company. Because we wanted to share our idea with the other Apex-adepts we submitted our abstract “Use the Power of APEX Dictionary Views to Increase the Quality of Your APEX Applications”.

Nathalie was our ambassador. She welcomed the audience and she introduced us. First I explained our Apex Development Approach: the broader context in which our quality system originated. With different steps and life demonstrations in between I then showed how the QA-tool evolved from basic to a customizable QA system.

The audience was very attentive and enthusiastic. More than 50 people attended our session. Everything went well until Murphy dropped in. Because of a broken socket we had problems with the power supply.

At the end of the presentation and also afterwards we got a lot of positive feedback: nice presentation, great idea, you should bring it out, … ! Well, we will make it available as soon as possible.

It really was a great experience to present on ODTUG!

For those who attended our session, we hope you enjoyed the presentation!
People who weren’t at ODTUG can download the paper and the presentation from our company website www.iadvise.be.

CU at OOW!

QA-4-Apex: the sequel

In one of my previous blogs, I already told we grouped the different QA-checks-you-want-to-perform in a single Apex application.

A special attention point is that this application needs to be imported in the workspace where the application resides that you want to control. This is due to the way the apex_ repository views are designed: you only see those applications where the different schemas assigned to the workspace have access to.

Since the last blog, we changed a little bit our approach. At that time, we had the idea tho group all related qa-checks in one UNION’ed view, containing ALL the queries for the different controls.

The main disadvantage of this implementation is that each time you want to add an extra check, you need to modify the view; this means doing some DDL.

Therefore, we decided to store the SQL-statements in a separate column related to each different control. Following screenshot, shows you the page that is used to add/modify a pre-defined control:

The sample query is used to check whether the Report Column Headings are always aligned the same way: Left, Right or Center. As you can see we do not hardcode one of those possible options in the where clause; but retrieve the standard-alignment-for-the-columns via a function call (Pck$QA_APEX_Standards.heading_alignment).

Therefore we created a separate table where we define all kind of parameters of which the value can differ from applicaton to application. The following screenshot shows the page where the parameter “heading_alignment” is defined with his default value.

If you want to differ from this default value for a given application, we have foreseen the possibility (via a second table), to store for a specific application (GWI in the example) the standard-value we agreed on with our end-users. This permits to have different standards for different apex-applications in the same workspace.

Apex: Our QA-application gets more and more substantial …

In my previous post I emphasized the power of the Apex dictionary views.

On this matter, have also a look at the very complete presentation of Patrick Wolf.

In the meanwhile, we further elaborated our idea of building a custom Apex application that should help in doing quality checks on coding standards and guidelines.

We gathered the different checks that we wanted to perform in different groups. For each group, we have built a view consisting of different UNIONed selects as illustrated in my previous blog.

This is a list of groups we distinguish so far:

  • APR => Application Page Regions (QA_APEX_APP_PAGE_REGIONS)
  • APRC => Application Page Rep Columns (QA_APEX_APP_PAGE_RPT_COLS)
  • API => Application Page Items (QA_APEX_APP_PAGE_ITEMS)
  • APP => Application Page Processes (QA_APEX_APP_PAGE_PROC)
  • APV => Application Page Validations (QA_APEX_APP_PAGE_VAL)

Within each group, we defined one or more checks, corresponding with a specific select against the apex repository. Each check we gave a short identifying code.

  • APR-001 = Wrong Reports Region Template
  • APR-002 = Wrong no_data_found_message
  • APR-003 = Wrong maximum_rows_to_query
  • APR-004 = Wrong pagination_scheme
  • APR-005 = Wrong enable_csv_output
  • APR-006 = Wrong report_null_value_as
  • APR-007 = No ORDER BY – No Dflt sort sequence
  • API-001 = Wrong date format in Date Picker
  • API-002 = Date column NOT based on Date Picker
  • APP-001 = Verify (Length) PL SQL Source
  • APP-002 = No Process Error Message
  • APV-001 = Wrong error_display_location
  • APRC-001 = Wrong date format
  • APRC-002 = Wrong Heading Alignment

After having defined the different checks and their corresponding query, the only thing we still needed to do was finding a way to give the developer or code reviewer an easy interface to perform those checks for a specific Apex application.

The following figure shows you how we finally visualized those checks:

In this screenshot, you can see for example which date item on which page and region does not yet correspond with the date format we decided on at the start of the development.

We also added an export to Csv. Like Carl Backstrom suggested, we could extract easily this data to PDF by using the PDF-Printing facilities available since Apex 3.0.

We have the impression that this QA-application can be extended and ameliorated in different ways. We are convinced that it have a lot of potential. For example, for the Apex project we are currently working on, we needed to present a global Quality Plan for the project. In that document we could refer to this QA-application as one of the ways to guarantee the quality of the application that we have to deliver within a few months.

(to be continued…)

Apex: Build Your own QA-system by Fully Exploiting the Apex Dictionary Views

A few weeks ago we started a new APEX project.

One of the typical tasks at the beginning of a new project is agree on some formal development aspects; you need to formalize some guidelines to guarantee consistency throughout the Apex application.

The development team needs to agree on questions like:

  • Which is the standard reports region Template ?
  • Which pagination schema will we use in the reports ?
  • Which format for dates will be used ?

We use a standard form like displayed in following figure:

Standards and guidelines are good, but how can you control that they are followed ?

I was looking for a way to control whether the developers follow those formal conventions we agreed on. We can do some code review, but that is a very time consuming tasks.
I needed a more automated approach.

Since APEX stores all his metadata in tables, it must be possible to query and check that data. So I took a closer look to all those Apex Dictionary Views that are exposed since Version 2.2.

For example to see whether there exists regions that are not based on the standard template “Reports Region”, we can query the apex_application_page_regions view and look at the template column. To check whether the pagination scheme is set on every page to “Row Ranges X to Y of Z (with pagination)”, query the pagination_scheme column in the same view. You can UNION those two queries and you get something like in following figure:

SELECT xapg.page_id, xapr.page_name, xapr.region_name,
xapr.template as wrong_value,
xapp.application_id as apex_app_id
FROM apex_application_page_regions xapr
JOIN apex_application_pages xapg
ON (xapg.page_id = xapr.page_id AND
xapg.application_id = xapr.application_id)
JOIN apex_applications xapp
ON xapp.application_id = xapg.application_id
WHERE xapg.page_id 0 -- exclude page zero
AND source_type IN ('Report','Tabular Form', 'PL/SQL')
AND template not like ‘Reports Region’
SELECT xapg.page_id, xapr.page_name, xapr.region_name,
xapr.pagination_scheme as wrong_value,
xapp.application_id as apex_app_id
FROM apex_application_page_regions xapr
JOIN apex_application_pages xapg
ON (xapg.page_id = xapr.page_id AND
xapg.application_id = xapr.application_id)
JOIN apex_applications xapp
ON xapp.application_id = xapg.application_id
WHERE xapg.page_id 0 -- exclude page zero
AND source_type IN ('Report','Tabular Form', 'PL/SQL')
AND xapr.pagination_scheme
‘Row Ranges X to Y of Z (with pagination)’

This way, you can assemble all different kind of queries that check on the different conventions your development team decided on.

One special thing, you should always keep in mind when you query those apex-views is that you must run those queries via the owing schema of your application. Those views are built that way that you can only see metadata of those applications where your user has access rights to.

This is the base of my tailor-made quality system. In one of my following blogs I will elaborate this basic idea. The next step of course is to build on top of this queries some reporting interface, …with Apex of course …