Creating ExtJS Tree JSON Objects with Custom Authorisation in SQL

Okay, the title to this post may seem a tad specific but the principles are transferable to creating pretty much any Ext Tree JSON object under any circumstances as long as you have the data in the database.

Previously I have be using Mark Lancaster’s example for creating the JSON object for an Ext Tree using the APEX List template. This has been working flawlessly for a good while and I love the ability to rejig the tree hierarchy using the list functionality in APEX.

However, I recently had requirement to implement a custom authorisation for the tree – hiding all entries that the user was not allowed to see. This would have been easy normally – I was using the APEX list functionality so I could just apply conditional display. Unfortunately, I couldn’t use this approach because my target was an URL with a target like:


This meant that APEX didn’t know which page the entry was targeting and I therefore couldn’t pass it through to the authorisation function to see if the option was available.

So, I set about creating my own JSON object generating SQL query, I had a google about and many people have done this in PL/SQL but it is normally limited a certain number of levels in the tree and these levels are hard-coded.

Creating the initial object with no authorisation was reasonably straight forward, I had Mark’s example for the  syntax and I created a small list in APEX using that template so that I had a reference point to work to.

To add the authorisation I added the page that I wanted the list entry to be checked against to the ‘User defined attributes’ in APEX. In the apex_application_list_entries table, this is stored as entry_attribute_01.

My problems started where there where multiple levels in the tree and the user was authorised to an entry at the lowest level but not to some of the levels above that in the same path. This was overcome by getting the whole path for each entry, checking if the user was authorised to the lowest level and then walking back up the path to make sure they then were shown all the folders in order to get to that leaf.

My colleague, Kevan Gelling, suggested that a pipelined function would help with this so we created this simple one:

CREATE OR REPLACE TYPE t_string_to_table

    p_String                       VARCHAR2,
    p_Delim                        VARCHAR2 DEFAULT ':',
    p_Start                        VARCHAR2 DEFAULT 'N',
    p_End                          VARCHAR2 DEFAULT 'N'
  RETURN  t_string_to_table PIPELINED
    l_Char                         VARCHAR2(1);
    l_Element                      VARCHAR2(4000);
    l_loopStart                    NUMBER;


    l_Element := NULL;

    IF p_Start = 'Y' THEN

      l_loopStart := 2;


      l_loopStart := 1;

    END IF;

FOR i IN l_loopStart .. LENGTH(p_String)

l_Char := SUBSTR(p_String,i,1);

      IF l_Char = p_Delim THEN

        PIPE ROW (l_Element);

        l_Element := NULL;

ELSIF p_End = 'N' AND i = LENGTH(p_String) THEN

        l_Element := l_Element||l_Char;
        PIPE ROW (l_Element);


        l_Element := l_Element||l_Char;

      END IF;




Now the SQL for the JSON object is as follows (thanks to Kevan for rationalising the last part a bit):

menuList AS
       SELECT list_entry_id,
-- In this example, the target is an URL (like javascript:loadPage(1);)
-- entry_attribute_01 is used to denote the target page number
-- Insert your authorisation function here
-- 'Y' is just hardcoded for demonstration
-- purposes for example -
-- my_auth.isPageAuthorised(:p_user_name,
--                          :p_app_id,
--                           entry_attribute_01)
       FROM   apex_application_list_entries
       WHERE  application_id      = :p_app_id
       AND    list_name           = 'Main Menu'
menuHier AS
      SELECT  list_entry_id,
ROWNUM all_row_num,
              LEVEL                                      the_level,
              CONNECT_BY_ISLEAF                          isleaf,
SYS_CONNECT_BY_PATH( isAuthorised, ':' ) authPath,
SYS_CONNECT_BY_PATH( list_entry_id, ':' ) idPath,
                SELECT  COUNT(*)
                FROM    apex_application_list_entries  l2
                WHERE   l2.list_entry_parent_id = menuList.list_entry_id
              )                                          child_count
FROM menuList
      START WITH list_entry_parent_id IS NULL
      CONNECT BY PRIOR list_entry_id = list_entry_parent_id
      ORDER SIBLINGS BY display_sequence,
menuAuth AS
SELECT menuHier.*,
              COUNT(*) OVER ()                                      row_count,
              ROW_NUMBER() OVER (ORDER BY all_row_num)              auth_row_num,
              ROW_NUMBER() OVER (PARTITION BY the_level,
                                 ORDER     BY all_row_num )         item_row_num,
              LEAD(the_level, 1, 1) OVER(ORDER BY all_row_num)      lead_level
FROM menuHier
      WHERE   list_entry_id IN
                  SELECT  DISTINCT
FROM menuHier,
TABLE(stringToTable(idPath)) i
--                         Last character in path = Y
WHERE SUBSTR(authPath, LENGTH(authPath), 1) = 'Y'
      ORDER BY all_row_num
--      START: JSON
          WHEN the_level = 1 THEN
--      ITEM: Indent levels
|| RPAD(' ',(the_level - 1) * 2)
--      ITEM: Comma separator
     || CASE
           WHEN item_row_num != 1 THEN
--      ITEM: Item details
     || '{'
     ||   '"id":"'   || list_entry_id || '"'
     ||  ',"text":"' || entry_text    || '"'
     ||  ',"href":"' || entry_target  || '"'
--      ITEM: Leaf information */
     || CASE
WHEN isleaf = 0 THEN -- With children
         || ',"children":['
WHEN isleaf != 0 THEN -- With no children
     || -- END: Item
WHEN isleaf != 0 THEN
      -- END: Level
     || CASE
WHEN isleaf != 0 AND
               lead_level < the_level     THEN
RPAD(' ', 1 + ((the_level - lead_level) * 2), ']}')
        -- END: JSON
     || CASE
          WHEN auth_row_num = row_count THEN
FROM menuAuth

This can go anywhere, in an application process, an on-load page process etc, etc. The joy of it is, once you have it working, you can still maintain the hierarchy in the APEX list using the GUI but you can affect it by anything you have in data. You could store ExtJS properties etc in your list entries and carry those through to the tree for example.

This hasn’t been fully tested yet but I will update with any bugs/limitations I come across.


Oracle Application Express 3.2 – The Essentials and More is Published!

I mentioned a while ago that this book was nearing completion but I have just been advised that it has now been published.

You can get the full description and buy a copy here…

Oracle Application Express 3.2 – The Essentials and More

Well done Matthew and Arie!

Thank you Scott…

I was deeply saddened to hear of the loss of Scott Spadafore.

He has done so much for the APEX community and helped so many people. I have learnt alot from his many forum postings and I often find myself looking back to them for guidance. My job is working with a product that he has had so much influence in.

My thoughts are with his family and friends.

Thank you Scott for all of your help.

UKOUG APEX SIG – 31st March 2010

Just a quick note to say that I will be presenting at the next UKOUG APEX SIG at the end of March.

The presentation will demonstrate the creation of an ExtJS based ‘webtop’ in APEX. It won’t be massively technical and will show the process of going from the ExtJS example to something that is generated and controlled by APEX.

The desktop can act as a portal to other APEX applications and also just looks quite cool!

Hope to see you there!

ExtJS and cliffs…

Okay, I haven’t posted in a very long while…

So, apologies for the delay, but such is the result of falling off a cliff!

I have mentioned a few times that I was working on some ExtJSy stuff and some people have asked me when it will be ready. Quick answer is not very soon but I would welcome you to have a look at where I’m at so far.

I took great inspiration from Mark Lancaster (I do hope you’ve seen his site), and Matt Nolan at e-DBA. The route I took was to use a PL/SQL package to dynamically (probably strecthing the use of the word there…) create the ExtJS scripts based on the meta data from the APEX tables and a bit of DBMS_SQL, a few application processes and a couple of standalone JS files in the header of the page template. It’s all pretty clean and generic.

On the home page there’s a little portal kind of thing where the positions of things are based on the region positions, columns etc. If you find the page with some grids on it – it uses the alignment, default sort settings etc. from the APEX data as well. So you just enter the report query and settings as normal in APEX and the PL/SQL package picks all of that up to create the grid.

It’s just a starting point at the moment and something I hope to work on when in finer form.

So sorry for the wait and sorry it’s only just started but here’s the link…

Munky’s Zoo

Please e-mail me with suggestions for what to do next…