15
RESOURCE_VIEW and PATH_VIEW

This chapter describes the SQL-based mechanisms, RESOURCE_VIEW and PATH_VIEW, used to access Oracle XML DB Repository data. It discusses the SQL operators UNDER_PATH and EQUALS_PATH used to query resources based on their path names and the SQL operators PATH and DEPTH that return the resource path names and depth.

This chapter contains the following sections:

• Oracle XML DB RESOURCE_VIEW and PATH_VIEW
• Resource_View, Path_View API
• UNDER_PATH
• EQUALS_PATH
• PATH
• DEPTH
• Using the Resource View and Path View API
• Working with Multiple Oracle XML DB Resources Simultaneously
• Tuning XML DB to Obtain Faster Queries
• Searching for Resources Using Oracle Text

Oracle XML DB RESOURCE_VIEW and PATH_VIEW

Figure 15-1 shows how Oracle XML DB RESOURCE_VIEW and PATH_VIEW provide a mechanism for using SQL to access data stored in Oracle XML DB Repository. Data stored in Oracle XML DB Repository through protocols like FTP, WebDAV, or programming API, can be accessed in SQL using RESOURCE_VIEWs and PATH_VIEWs, and vice versa.

RESOURCE_VIEW and PATH_VIEW together, along with PL/SQL package, DBMS_XDB, provide all query-based access to Oracle XML DB and DML functionality that is available through the programming API.

The base table for RESOURCE_VIEW is XDB.XDB$RESOURCE and should only be accessed through RESOURCE_VIEW or the DBMS_XDB API.

Figure 15-1 Accessing Repository Resources Using RESOURCE_VIEW and PATH_VIEW

RESOURCE_VIEW Definition and Structure

The RESOURCE_VIEW contains one row for each resource in the Repository. The following describes its structure:

Column      Datatype     Description 
------      --------     -------------------------------------------------------
RES         XMLTYPE      A resource in Oracle XML Repository 
ANY_PATH    VARCHAR2     A path that can be used to access the resource in the
                         Repository 

PATH_VIEW Definition and Structure

The PATH_VIEW contains one row for each unique path to access a resource in the Repository. The following describes its structure:

Column      Datatype     Description 
------      --------     -----------------------------
PATH        VARCHAR2     Path name of a resource 
RES         XMLTYPE      The resource referred by PATH 
LINK        XMLTYPE      Link property 

Figure 15-2 illustrates the structure of Resource and PATH_VIEWs.

The path in the RESOURCE_VIEW is an arbitrary one and one of the accessible paths that can be used to access that resource. Oracle XML DB provides operator UNDER_PATH that enables applications to search for resources contained (recursively) within a particular folder, get the resource depth, and so on. Each row in the PATH_VIEW and RESOURCE_VIEW columns is of XMLType. DML on Oracle XML DB Repository views can be used to insert, rename, delete, and update resource properties and contents. Programmatic APIs must be used for some operations, such as creating links to existing resources.

Figure 15-2 RESOURCE_VIEW and PATH_VIEW Structure

Understanding the Difference Between RESOURCE_VIEW and PATH_VIEW

The major difference between the RESOURCE_VIEW and PATH_VIEW is:

• PATH_VIEW displays all the path names to a particular resource whereas RESOURCE_VIEW displays one of the possible path names to the resource
• PATH_VIEW also displays the properties of the link

Figure 15-3 illustrates the difference between Resource and PATH_VIEW.

Since many internet applications only need one URL to access a resource, RESOURCE_VIEW is widely applicable.

PATH_VIEW contains the link properties as well as resource properties, whereas the RESOURCE_VIEW only contains resource properties.

The RESOURCE_VIEW benefit is generally optimization, if the database knows that only one path is needed, the index does not have to do as much work to determine all the possible paths.

Figure 15-3 RESOURCE_VIEW and PATH_VIEW Explained

Operations You Can Perform Using UNDER_PATH and EQUALS_PATH

You can perform the following operations using UNDER_PATH and EQUALS_PATH:

• Given a path name:
  • Get a resource
  • List the directory given by the path name
  • Create a resource
  • Delete a resource
  • Update a resource
• Given a condition, containing UNDER_PATH operator or other SQL operators:
  • Update resources
  • Delete resources
  • Get resources

See the "Using the Resource View and Path View API" and EQUALS_PATH.

Resource_View, Path_View API

This section describes the RESOURCE_VIEW and PATH_VIEW operators:

UNDER_PATH

The UNDER_PATH operator uses the Oracle XML DB Repository hierarchical index to return the paths under a particular path. The hierarchical index is designed to speed access walking down a path name (the normal usage).

If the other parts of the query predicate are very selective, however, a functional implementation of UNDER_PATH can be chosen that walks back up the Repository. This can be more efficient, since a much smaller number of links may need to be traversed. Figure 15-4 shows the UNDER_PATH syntax.

Figure 15-4 UNDER_PATH Syntax

Table 15-1 describes the UNDER_PATH syntax.

Table 15-1 RESOURCE_VIEW and PATH_VIEW API Syntax: UNDER_PATH  

EQUALS_PATH

The EQUALS_PATH operator is used to find the resource with the specified path name. It is functionally equivalent to UNDER_PATH with a depth restriction of zero. The EQUALS_PATH syntax is describe here and in Figure 15-5.

EQUALS_PATH INTEGER EQUALS_PATH( resource_column,pathname);

Figure 15-5 EQUALS_PATH Syntax

where:

• resource_column is the column name or column alias of the 'resource' column in the path_view or resource_view.
• pathname is the path name to resolve.

PATH

PATH is an ancillary operator that returns the relative path name of the resource under the specified pathname argument. Note that the path column in the RESOURCE_VIEW always contains the absolute path of the resource. The PATH syntax is:

PATH VARCHAR2 PATH( correlation);

where:

• correlation is an integer that can be used to correlate the UNDER_PATH operator (a primary operator) with ancillary operators (PATH and DEPTH).

  Note: If a path is not under the specified pathname argument, a NULL value is returned as the output of the current path.

Here are some examples of a RESOURCE_VIEW that include resources specified by paths:

'/a/b/c'
'/a/b/c/d'
'/a/e/c'
'/a/e/c/d' 

Example 15-1 Determining Paths Under the Specified Pathname Argument

SELECT path(1) FROM resource_view 
   WHERE UNDER_PATH(res, '/a/b', 1) = 1;

Returns the following:

PATH(1)
-------
c
c/d
2 rows returned

Example 15-2 Determining Paths Not Under the Specified Pathname Argument

SELECT path(1) FROM resource_view 
   WHERE UNDER_PATH(res, '/a/b', 1)!=1

Returns the following:

PATH(1)
-------


2 rows returned

SELECT ANY_PATH
FROM resource_view 
WHERE UNDER_PATH(res, '/a/b')=1;

ANY_PATH
--------
/a/e/c
/a/e/c/d
2 rows returned

Example 15-3 Determining Paths Using Multiple Correlations

SELECT ANY_PATH, path(1), path(2) 
FROM resource_view
WHERE UNDER_PATH(res, '/a/b', 1) = 1 or UNDER_PATH(res, '/a/e', 2) = 1;

This returns the following:

ANY_PATH           PATH(1)                  PATH(2)
----------------------------------------------------
/a/b/c             c
/a/b/c/d           c/d
/a/e/c                                      c
/a/e/c/d                                    c/d
4 rows returned

DEPTH

DEPTH is an ancillary operator that returns the folder depth of the resource under the specified starting path.

DEPTH  INTEGER DEPTH( correlation);

where:

correlation is an integer that can be used to correlate the UNDER_PATH operator (a primary operator) with ancillary operators (PATH and DEPTH).

Using the Resource View and Path View API

The following RESOURCE_VIEW and PATH_VIEW examples use operators UNDER_PATH, EQUALS_PATH, PATH, and DEPTH.

Accessing Paths and Repository Resources: Examples

The following examples illustrate how you can access paths, resources, and link properties in the Repository:

Example 15-4 Using UNDER_PATH: Given a Path Name, List the Directory Given by the Path Name from the RESOURCE_VIEW

select any_path from resource_view where any_path like '/sys%';

Example 15-5 Using UNDER_PATH: Given a Path Name, Get a Resource From the RESOURCE_VIEW

select any_path, extract(res, '/display_name') from resource_view
   where under_path(res, '/sys') = 1; 

Example 15-6 Using RESOURCE_VIEW: Given a Path, Get all Relative Path Names for Resources up to Three Levels

select path(1) from resource_view
   where under_path (res, 3, '/sys',1)=1;

Example 15-7 Using UNDER_PATH: Given a Path Name, Get Path and Depth Under a Specified Path from the PATH_VIEW

select path(1) PATH,depth(1) depth 
  from path_view 
  where under_path(RES, 3,'/sys',1)=1 

Example 15-8 Given a Path Name, Get Paths and Link Properties from PATH_VIEW

select path, extract(link, '/LINK/Name/text()').getstringval(), 
               extract(link, '/LINK/ParentName/text()').getstringval(), 
               extract(link, '/LINK/ChildName/text()').getstringval(), 
               extract(res, '/Resource/DisplayName/text()').getstringval() 
   from path_view
   where path LIKE '/sys%';

Example 15-9 Using UNDER_PATH: Given a Path Name, Find all the Paths up to a Certain Number of Levels, Including Links Under a Specified Path from the PATH_VIEW

select path(1) from path_view 
   where under_path(res, 3,'/sys', 1) > 0 ;

Example 15-10 Using EQUALS_PATH to Locate a Path

select any_path from resource_view
   where equals_path(res, '/sys') > 0;

Inserting Data into a Repository Resource: Examples

The following example illustrates how you can insert data into a resource:

Example 15-11 Creating Resources: Inserting Data Into a Resource

insert into resource_view values(sys.xmltype.createxml(' 
  <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd 
http://xmlns.oracle.com/xdb/XDBResource.xsd"> 
  <Author>John Doe</Author> 
  <DisplayName>example</DisplayName> 
  <Comment>This resource was contrived for resource view demo</Comment> 
  <Language>en</Language> 
  <CharacterSet>ASCII</CharacterSet> 
  <ContentType>text/plain</ContentType> 
  </Resource>'), '/home/SCOTT'); 

Deleting Repository Resources: Examples

The following examples illustrate how you can delete resources or paths:

Example 15-12 Deleting Resources

delete from resource_view where any_path = '/home/SCOTT/example 

If only leaf resources are deleted, you can perform a delete using delete from resource_view where....

Deleting Non-Empty Containers Recursively

If only leaf resources are deleted, you can delete them using "delete from 
resource_view where...". For example, one way to delete leaf node 
'/public/test/doc.xml' is as follows:

delete from resource_view where under_path(res, '/public/test/doc.xml') = 1;

However, if you attempt to delete a non-empty container recursively, the following rules apply:

• Delete on a non-empty container is not allowed
• The order of the paths returned from the where clause predicates is not guaranteed

Therefore you should guarantee that a container is deleted only after its children have been deleted.

Example 15-13 Recursively Deleting Paths

For example, to recursively delete paths under '/public'), you may want to try the following:

delete from
(select 1 from resource_view
 where UNDER_PATH(res, '/public', 1) = 1
 order by depth(1) desc);

Updating Repository Resources: Examples

The following examples illustrate how to update resources and paths:

Example 15-14 Updating Resources

update resource_view set res = sys.xmltype.createxml(' 
  <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd 
http://xmlns.oracle.com/xdb/XDBResource.xsd"> 
  <Author>John Doe</Author> 
  <DisplayName>example</DisplayName> 
  <Comment>Has this got updated or not ? </Comment> 
  <Language>en</Language> 
  <CharacterSet>ASCII</CharacterSet> 
  <ContentType>text/plain</ContentType> 
  </Resource>') 
    where any_path = '/home/SCOTT/example'; 

Example 15-15 Updating a Path in the PATH_VIEW

update path_view set path = '/home/XDB' 
  where path = '/home/SCOTT/example' 

Working with Multiple Oracle XML DB Resources Simultaneously

Operations listed in Table 13-3, Chapter 13, "Oracle XML DB Foldering", typically apply to only one resource at a time. To perform the same operation on multiple Oracle XML DB resources, or to find one or more Oracle XML DB resources that meet a certain set of criteria, use RESOURCE_VIEW and PATH_VIEW in SQL.

For example, you can perform the following operations with these resource_view and PATH_VIEW SQL clauses:

• Updating based on attributes

  UPDATE RESOURCE_VIEW SET ... WHERE extractValue(resource, '/display_name') = 
  'My stuff'
  
  

• Finding recursively in a folder

  SELECT FROM RESOURCE_VIEW WHERE UNDER_PATH(resource, '/sys') ...
  
  

• Copying a set of Oracle XML DB resources

  INSERT INTO PATH_VIEW SELECT .... FROM PATH_VIEW WHERE ...
  
  

Tuning XML DB to Obtain Faster Queries

XML DB uses the xdbconfig file for configuring the system and protocol environment. In Release 2 (9.2.0.2) it includes an element resource-view-cache-size parameter that defines the in-memory size of the RESOURCE_VIEW cache. The default value is 1048576.

Some queries on RESOURCE_VIEW and PATH_VIEW can be sped up by tuning resource-view-cache-size. In general, the bigger the cache size, the faster the query. The default resource-view-cache-size is appropriate for most cases. However you may want to enlarge your resource-view-cache-size element when querying sizable RESOURCE_VIEWs.

Searching for Resources Using Oracle Text

The XDB$RESOURCE table in Oracle XML DB user schema stores in Oracle XML DB the metadata and data corresponding to resources, such as files and folders. You can search for resources containing a specific keyword by using the CONTAINS operator in RESOURCE_VIEW or PATH_VIEW.

Example 15-16 Find All Resources Containing Keywords "Oracle" and "Unix"

select path
   from path_view
   where contains(res, 'Oracle AND Unix') > 0;

Example 15-17 Find All Resources Containing Keyword "Oracle" that are Also Under a Specified Path.

select any_path
   from resource_view
   where contains(res, 'Oracle') > 0
   and under_path(res, '/myDocuments') > 0;

To evaluate such queries, you must create a Context Index on the XDB$RESOURCE table. Depending on the type of documents stored in Oracle XML DB, choose one of the following options for creating your Context Index:

• If Oracle XML DB contains only XML documents, that is, no binary data, a regular Context Index can be created on the XDB$RESOURCE table.

  See Also: Chapter 7, "Searching XML Data with Oracle Text", "XMLType Indexing".

  create index xdb$resource_ctx_i
     on xdb.xdb$resource x (value(x))
     indextype is ctxsys.context;
  
  

• If Oracle XML DB contains binary data, for example WORD documents, a user filter is required to filter such documents prior to indexing. It is recommended that you use the DBMS_XDBT package (dbmsxdbt.sql) to create and configure the Context Index.

  See: Oracle9i XML API Reference - XDK and Oracle XML DB, the chapter on DBMS_XDBT for information on installing and using DBMS_XDBT. Appendix F, "Oracle XML DB XMLType API, PL/SQL and Resource PL/SQL APIs: Quick Reference", DBMS_XDBT.

  Rem Install the package - connected as SYS
  SQL>@dbmsxdbt
  Rem Create the preferences
  SQL>exec dbms_xdbt.createPreferences;
  Rem Create the index
  SQL>exec dbms_xdbt.createIndex;
  
  

DBMS_XDBT package also includes procedures to sync and optimize the index. You can use the configureAutoSync() procedure to configure automatic sync of the index by using job queues.