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 AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION stringToTable ( p_String VARCHAR2, p_Delim VARCHAR2 DEFAULT ':', p_Start VARCHAR2 DEFAULT 'N', p_End VARCHAR2 DEFAULT 'N' ) RETURN t_string_to_table PIPELINED IS l_Char VARCHAR2(1); l_Element VARCHAR2(4000); l_loopStart NUMBER; BEGIN l_Element := NULL; IF p_Start = 'Y' THEN l_loopStart := 2; ELSE l_loopStart := 1; END IF; FOR i IN l_loopStart .. LENGTH(p_String) LOOP 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); ELSE l_Element := l_Element||l_Char; END IF; END LOOP; RETURN; END; /
Now the SQL for the JSON object is as follows (thanks to Kevan for rationalising the last part a bit):
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.