When programming API based utilities for Portal 3.0.x it is very
frequent to need to enable users to pick a folder within any Portal Content
Area. One simple solution for this is to create a List of Value component
in Portal 3.0. But a folder List of Value is more complex than the average
LOV for two reasons. First, we are doing a hierarchical type of LOV. Second,
have to dig in the internals of Portal because we need to present a list
of Portal folders.
Step 1: The SQL part
The table wwv_corners is owned by the Portal30 schema in the database, unless the person who installed Portal on your server used a different schema. Wwv_corners contains all folder information for all folders defined in Portal, including the internal folders that come with the product. Folders constitute a hierarchy: a folder may contain other folders that in turn may contain others, etc.
There are a few tricks to the table wwv_corners. It also contains content
area information. A content area is really a top level folder. All content
area folders have their ID set to 1 and the parent folder ID set to 0.
Yes, ID is NOT the primary key. All content areas, i.e. top level folder
have the same ID in wwv_corners: 1. Try it: run this query:
select id, title,
parentid
from wwv_corners
where id =1
The PK is actually composed of three columns: (ID, SiteID, Language).
Incidentally you will find that there is no folder record with ID equal to 0 so you could not have a foreign key from parentid to id. Each content area corresponds to one and only one site referred to by the column siteid. Site information can be found in table wwsbr_sites$.
So if you want to produce a list of values showing folders, you should
not hope much from a query like this:
select id, title, parentid
from wwv_corners
where siteid >10 -- only user
defined folders
start with parentid = 0
connect by parentid = prior id
because there are a number of root folders that have the same id. Instead
you must write this:
select id, title, parentid
from wwv_corners
where siteid >10 -- only user
defined folders
start with parentid = 0
connect by parentid = prior id and
siteid = prior siteid and
language = prior language
The operator prior designates the parent record in a hierarchical SQL query.
The database first looks for the root level record(s) by applying the 'start
with'
clause, then for each of the root record it looks for children records
by applying the 'connect by' clause. 'connect by parentid = prior id' is
like writing 'where child.parent_id = parent.id". Then for each child records
the database engine looks for grand children and keeps going down the hierarchy
until it gets to the bottom level. There is a good description of this
process in the Oracle8i SQL Reference, Chapter: "Expressions, Conditions,
and Queries", under the sub-title: "Hierarchical Queries".
Step 2: The Portal part
If you need a tree looking layout, try this:
select id, LPAD(title,length(title)+(level-1)*2,'+'),
parentid
from wwv_corners
where siteid >10 -- only user
defined folders
start with parentid = 0
connect by parentid = prior id and
siteid = prior siteid and
language = prior language
In Portal, to create such an you must first create or edit an application
and add a component 'List of Values'. Change the order of the column selected
like this:
select LPAD(title,length(title)+(level-1)*6,'.'),
id
from wwv_corners
where siteid >10 -- only user
defined folders
start with parentid = 0
connect by parentid = prior id and
siteid = prior siteid and
language = prior language
Set the default format to Popup, et voila. Ready for use in any other component.
The resulting list of value is illustrated in Figure 1.