29 User-Defined Repository Metadata

Overview of Metadata and XML

Data that you use is often associated with additional information that is not part of the content. To process it in different ways, you can use such metadata to group or classify data. For example, you might have a collection of digital photographs, and you might associate metadata with each picture, such as information about the photographic characteristics (color composition, focal length) or context (location, kind of subject: landscape, people).

An Oracle XML DB repository resource is an XML document that contains both metadata and data. The data is the contents of element Contents. All other elements in the resource contain metadata. The data of a resource can be XML, but it need not be.

You can associate resources in the Oracle XML DB repository with metadata that you define. In addition to such user-defined metadata, each repository resource also has associated metadata that Oracle XML DB creates automatically and uses (transparently) to manage the resource. Such system-defined metadata includes properties such as the owner and creation date of each resource.

Except for system-defined metadata, you decide which resource information should be treated as data and which should be treated as metadata. For a photo resource, supplemental information about the photo is normally not considered to be part of the photo data, which is a binary image. For text, however, you sometimes have a choice of whether to include particular information in the resource contents (data) or keep it separate and associate it with the contents as metadata — that choice is often influenced by the applications that use or produce the data.

XML Schemas to Define Resource Metadata

This section first defines the metadata to associate with each photo resource using XML Schema. An XML schema is created and registered for each kind (technique, category) of metadata.

The XML schema in Example 29-1 defines metadata used to describe the technical aspects of a photo image file. It uses PL/SQL procedure DBMS_XMLSCHEMA.registerSchema to register the XML schema. To identify this schema as defining repository resource metadata, it uses ENABLE_HIERARCHY_RESMETADATA as the value for parameter enableHierarchy. Resource contents (data) are defined by using value ENABLE_HIERARCHY_CONTENTS (the default value), instead.

The properties defined in Example 29-1 are the image height, width, color depth, title, and brief description.

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL       => 'imagetechnique.xsd',
    SCHEMADOC       => '<xsd:schema targetNamespace="inamespace"
                                    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                                    xmlns:xdb="http://xmlns.oracle.com/xdb"
                                    xmlns="inamespace">
                          <xsd:element name="ImgTechMetadata"
                                       xdb:defaultTable="IMGTECHMETADATATABLE">
                            <xsd:complexType>
                              <xsd:sequence>
                                <xsd:element name="Height"      type="xsd:float"/>
                                <xsd:element name="Width"       type="xsd:float"/>
                                <xsd:element name="ColorDepth"  type="xsd:integer"/>
                                <xsd:element name="Title"       type="xsd:string"/>
                                <xsd:element name="Description" type="xsd:string"/>
                              </xsd:sequence>
                            </xsd:complexType>
                          </xsd:element>
                        </xsd:schema>',
    enableHierarchy => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_RESMETADATA);
END;
/

The XML schema in Example 29-2 defines metadata used to categorize a photo image file: to describe its content or possible uses. This simple example defines a single, general property for classification, named Category.

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL       => 'imagecategories.xsd',
    SCHEMADOC       => '<xsd:schema targetNamespace="cnamespace"
                                    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                                    xmlns:xdb="http://xmlns.oracle.com/xdb"
                                    xmlns="cnamespace">
                          <xsd:element name="ImgCatMetadata"
                                       xdb:defaultTable="IMGCATMETADATATABLE">
                            <xsd:complexType>
                              <xsd:sequence>
                                <xsd:element name="Categories" 
                                             type="CategoriesType"/>
                              </xsd:sequence>
                            </xsd:complexType>
                          </xsd:element>
                          <xsd:complexType name="CategoriesType">
                            <xsd:sequence>
                              <xsd:element name="Category" type="xsd:string"
                                           maxOccurs="unbounded"/>
                            </xsd:sequence>
                          </xsd:complexType>
                        </xsd:schema>',
    enableHierarchy => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_RESMETADATA);
END;
/

Notice that there is nothing in the XML schema definitions of metadata that restrict that information to being associated with any particular kind of data. You are free to associate any type of metadata with any type of resource. And multiple types of metadata can be associated with the same resource.

Notice, too, that the XML schema does not, by itself, define its associated data as being metadata — it is the schema registration that makes this characterization, through enableHierarchy value ENABLE_HIERARCHY_RESMETADATA. If the same schema were registered instead with enableHierarchy value ENABLE_HIERARCHY_CONTENTS (the default value), then it would define not metadata for resources, but resource contents with the same information. The same XML schema cannot be registered more than once under the same name.

Adding, Updating, and Deleting Resource Metadata

You can add, update, and delete user-defined resource metadata in the following ways:

• Use PL/SQL procedures in package DBMS_XDB:

  • appendResourceMetadata – add metadata to a resource

  • updateResourceMetadata – modify resource metadata

  • deleteResourceMetadata – delete specific metadata from a resource

  • purgeResourceMetadata – delete all metadata from a resource

• Use SQL DML statements INSERT, UPDATE, and DELETE to update the resource directly

• Use WebDAV protocol method PROPPATCH

You use SQL DM statements and WebDAV method PROPPATCH to update or delete metadata in the same way as you add metadata. If you supply a complete Resource element for one of these operations, then keep in mind that each resource metadata property must be a child (not just a descendant) of element Resource — if you want multiple metadata elements of the same kind, you must collect them as children of a single parent metadata element. The order among such top-level user-defined resource metadata properties is unimportant and is not necessarily maintained by Oracle XML DB.

The separate PL/SQL procedures in package DBMS_XDB are similar in their use. Each can be used with either XML schema-based or non-schema-based metadata. Some forms (signatures) of some of the procedures apply only to schema-based metadata. Procedures appendResourceMetadata and deleteResourceMetadata are illustrated here with examples.

DECLARE
  returnbool BOOLEAN;
BEGIN
  returnbool := DBMS_XDB.createResource(
                  '/public/horse_with_pig.jpg',
                  bfilename('MYDIR', 'horse_with_pig.jpg'));
  DBMS_XDB.appendResourceMetadata(
    '/public/horse_with_pig.jpg',
    XMLType('<i:ImgTechMetadata
                 xmlns:i="inamespace"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="inamespace imagetechnique.xsd">
               <Height>1024</Height>
               <Width>768</Width>
               <ColorDepth>24</ColorDepth>
               <Title>Pig Riding Horse</Title>
               <Description>Picture of a pig riding a horse on the beach,
taken outside hotel window.</Description>
             </i:ImgTechMetadata>'));
END;
/

BEGIN
  DBMS_XDB.appendResourceMetadata(
    '/public/horse_with_pig.jpg',
    XMLType('<c:ImgCatMetadata
                 xmlns:c="cnamespace"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="cnamespace imagecategories.xsd">
               <Categories>
                 <Category>Vacation</Category>
                 <Category>Animals</Category>
                 <Category>Humor</Category>
                 <Category>2005</Category>
               </Categories>
             </c:ImgCatMetadata>'));
END;
/

PL/SQL procedure successfully completed.
 
SELECT * FROM imgcatmetadatatable;
 
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<c:ImgCatMetadata xmlns:c="cnamespace" xmlns:xsi="http://www.w3.org/2001/XMLSche
ma-instance" xsi:schemaLocation="cnamespace imagecategories.xsd">
  <Categories>
    <Category>Vacation</Category>
    <Category>Animals</Category>
    <Category>Humor</Category>
    <Category>2005</Category>
  </Categories>
</c:ImgCatMetadata>
 
1 row selected.

BEGIN
  DBMS_XDB.deleteResourceMetadata('/public/horse_with_pig.jpg',
                                  'cnamespace',
                                  'ImgCatMetadata');
END;
/

PL/SQL procedure successfully completed.
 
SELECT * FROM imgcatmetadatatable;
 
no rows selected

UPDATE RESOURCE_VIEW
  SET RES = 
        insertChildXML(
          RES, 
          '/r:Resource', 
          'c:ImgCatMetadata', 
          XMLType('<c:ImgCatMetadata
                       xmlns:c="cnamespace"
                       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                       xsi:schemaLocation="cnamespace imagecategories.xsd">
                     <Categories>
                       <Category>Vacation</Category>
                       <Category>Animals</Category>
                       <Category>Humor</Category>
                       <Category>2005</Category>
                     </Categories>
                   </c:ImgCatMetadata>'),
          'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
           xmlns:c="cnamespace"')
  WHERE equals_path(RES, '/public/horse_with_pig.jpg') = 1;
/

SELECT * FROM imgcatmetadatatable;
 
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<c:ImgCatMetadata xmlns:c="cnamespace" xmlns:xsi="http://www.w3.org/2001/XMLSche
ma-instance" xsi:schemaLocation="cnamespace imagecategories.xsd">
  <Categories>
    <Category>Vacation</Category>
    <Category>Animals</Category>
    <Category>Humor</Category>
    <Category>2005</Category>
  </Categories>
</c:ImgCatMetadata>
 
1 row selected.

SELECT XMLQuery('declare namespace r
                   = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                 declare namespace c
                   = "cnamespace"; (: :)
                 /r:Resource/c:ImgCatMetadata'
                PASSING RES RETURNING CONTENT)
  FROM RESOURCE_VIEW
  WHERE equals_path(RES, '/public/horse_with_pig.jpg') = 1;
 
XMLQUERY('DECLARENAMESPACER="HTTP://XMLNS.ORACLE.COM/XDB/XDBRESOURCE.XSD";(::)DE
--------------------------------------------------------------------------------
<c:ImgCatMetadata xmlns:c="cnamespace" 
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:schemaLocation="cnamespace imagecategories.xsd">
  <Categories>
    <Category>Vacation</Category>
    <Category>Animals</Category>
    <Category>Humor</Category>
    <Category>2005</Category>
  </Categories>
</c:ImgCatMetadata>
 
1 row selected.

PROPPATCH /public/horse_with_pig.jpg HTTP/1.1
Host: www.example.com
Content-Type: text/xml; charset="utf-8"
Content-Length: 609
Authorization: Basic dGRhZHhkYl9tZXRhOnRkYWR4ZGJfbWV0YQ==
Connection: close
 
<?xml version="1.0" encoding="utf-8" ?>
<D:propertyupdate xmlns:D="DAV:" xmlns:Z="http://www.w3.com/standards/z39.50/">
  <D:set>
    <D:prop>
      <c:ImgCatMetadata
          xmlns:c="cnamespace"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="cnamespace imagecategories.xsd">
        <Categories>
          <Category>Vacation</Category>
          <Category>Animals</Category>
          <Category>Humor</Category>
          <Category>2005</Category>
        </Categories>
      </c:ImgCatMetadata>
    </D:prop>
  </D:set>
</D:propertyupdate>

Querying XML Schema-Based Resource Metadata

When you register an XML schema using the enableHierarchy value ENABLE_HIERARCHY_RESMETADATA, an additional column, RESID, is added automatically to the XMLType tables used to store the metadata. This column stores the object identifier (OID) of the resource associated with the metadata. You can use column RESID when querying metadata, to join the metadata with the associated data.

You can query metadata in these ways:

• Query RESOURCE_VIEW for the metadata. For example:

  SELECT count(*) FROM RESOURCE_VIEW
    WHERE
      XMLExists(
        'declare namespace r
           = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
         declare namespace c
           = "cnamespace"; (: :)
         /r:Resource/c:ImgCatMetadata/Categories/Category[text()="Vacation"]'
        PASSING RES);
  
    COUNT(*)
  ----------
           1
   
  1 row selected.
  

• Query the XML schema-based table for the user-defined metadata directly, and join this metadata back to the resource table, identifying which resource to select. Use column RESID of the metadata table to do this. For example:

  SELECT COUNT(*) FROM RESOURCE_VIEW rs, imgcatmetadatatable ct
    WHERE
      XMLExists(
        'declare namespace r
           = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
         declare namespace c
           = "cnamespace"; (: :)
         /r:Resource/c:ImgCatMetadata/Categories/Category'
        PASSING RES)
      AND rs.RESID = ct.RESID;
  
    COUNT(*)
  ----------
           1
   
  1 row selected.
  

Oracle recommends querying for user-defined metadata directly, for performance reasons. Direct queries of the RESOURCE_VIEW alone cannot be optimized using XPath rewrite, because there is no way to determine whether or not target elements like Category are stored in the CLOB value or in an out-of-line table.

To improve performance further, create an index on each metadata column you intend to query.

Example 29-8 queries both kinds of photo resource metadata, retrieving the paths to the resources that are categorized as vacation photos and have the title "Pig Riding Horse".

SELECT ANY_PATH
  FROM RESOURCE_VIEW rs, imgcatmetadatatable ct, imgtechmetadatatable tt
  WHERE XMLExists(
          'declare namespace r
             = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
           declare namespace c
             = "cnamespace"; (: :)
           /r:Resource/c:ImgCatMetadata/Categories/Category[text()="Vacation"]'
          PASSING RES)
    AND XMLExists(
          'declare namespace r
             = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
           declare namespace i
             = "inamespace"; (: :)
           /r:Resource/i:ImgTechMetadata/Title[text()="Pig Riding Horse"]'
          PASSING RES)
    AND rs.RESID = ct.RESID
    AND rs.RESID = tt.RESID;

ANY_PATH
--------------------------
/public/horse_with_pig.jpg
 
1 row selected.

XML Image Metadata from Binary Image Metadata

Previous sections of this chapter use a simple user-defined XML schema that defines technical image metadata, imagetechnique.xsd, to illustrate ways of adding and changing repository resource metadata. That simple XML schema is not intended to be realistic with respect to technical photographic image information.

However, most digital cameras include image metadata as part of the binary image files they produce, and Oracle interMedia, which is part of Oracle Database, provides tools for extracting and converting this binary metadata to XML. Oracle interMedia XML schemas are automatically registered with Oracle XML DB Repository to convert binary image metadata of the followings kinds to XML data:

• EXIF – Exchangeable Image File Format

• IPTC-NAA IIM – International Press Telecommunications Council-Newspaper Association of America Information Interchange Model

• XMP – Extensible Metadata Platform

EXIF is the metadata standard for digital still cameras. EXIF metadata is stored in TIFF and JPEG image files. IPTC and XMP metadata is commonly embedded in image files by desktop image-processing software.

Adding Non-Schema-Based Resource Metadata

You store user-defined resource metadata that is not schema-based as a CLOB instance under the Resource element of the associated resource. The default XML schema for a resource has a top-level element any (declared with maxOccurs= "unbounded"), which admits any valid XML data as part of the resource document. This metadata is stored in a CLOB column of the resource table.

The following skeleton shows the structure and position of non-schema-based resource metadata:

<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"
       <Owner>DESELBY</Owner>
       ... <!-- other system-defined metadata -->
       <!-- contents of the resource>
       <Contents>
         ...
       </Contents>
       <!-- User-defined metadata (appearing within different namespace) -->
       <MyOwnMetadata xmlns="http://www.example.com/custommetadata">
         <MyElement1>value1</MyElement1>
         <MyElement2>value2</MyElement2>
       </MyOwnMetadata>
     </Resource>

You can set and access non-schema-based resource metadata belonging to namespaces other than XDBResource.xsd by using any of the means described previously for accessing XML schema-based resource metadata.

Example 29-9 illustrates this for the case of SQL DML operations, adding user-defined metadata directly to the <RESOURCE> document. It shows how to add non-schema-based metadata to a resource using SQL DML.

DECLARE
  res BOOLEAN;
BEGIN
  res := DBMS_XDB.createResource('/public/NurseryRhyme.txt',
                                 bfilename('MYDIR',
                                           'tdadxdb-xdb_repos_meta-011.txt'),
                                 nls_charset_id('AL32UTF8'));
UPDATE RESOURCE_VIEW
  SET RES = insertChildXML(RES, 
                           '/r:Resource', 
                           'n:NurseryMetadata', 
                           XMLType('<n:NurseryMetadata xmlns:n="nurserynamespace">
                                      <Author>Mother Goose</Author>
                                   <n:NurseryMetadata>'),
                           'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
                            xmlns:n="nurserynamespace"')
  WHERE equals_path(RES, '/public/NurseryRhyme.txt') = 1;
END;
/

PL/SQL procedure successfully completed.
 

SELECT XMLSerialize(DOCUMENT rs.RES AS CLOB) FROM RESOURCE_VIEW rs
  WHERE equals_path(RES, '/public/NurseryRhyme.txt') = 1;
 
XMLSERIALIZE(DOCUMENTRS.RESASCLOB)
--------------------------------------------------------------------------------
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
ef="true">
  <CreationDate>2005-05-24T13:51:48.043234</CreationDate>
  <ModificationDate>2005-05-24T13:51:48.290144</ModificationDate>
  <DisplayName>NurseryRhyme.txt</DisplayName>
  <Language>en-US</Language>
  <CharacterSet>UTF-8</CharacterSet>
  <ContentType>text/plain</ContentType>
  <RefCount>1</RefCount>
  <ACL>
    <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracl
e.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
emaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http:
//xmlns.oracle.com/xdb/acl.xsd" shared="true">
      <ace>
        <principal>PUBLIC</principal>
        <grant>true</grant>
        <privilege>
          <all/>
        </privilege>
      </ace>
    </acl>
  </ACL>
  <Owner>TDADXDB_META</Owner>
  <Creator>TDADXDB_META</Creator>
  <LastModifier>TDADXDB_META</LastModifier>
  <SchemaElement>http://xmlns.oracle.com/xdb/XDBSchema.xsd#text</SchemaElement>
  <Contents>
    <text>Mary had a little lamb
Its fleece was white as snow
and everywhere that Mary went
that lamb was sure to go
</text>
  </Contents>
  <n:NurseryMetadata xmlns:n="nurserynamespace">
    <Author xmlns="">Mother Goose</Author>
  </n:NurseryMetadata>
</Resource>
 
1 row selected.

PL/SQL Procedures Affecting Resource Metadata

The following PL/SQL procedures perform resource metadata operations:

• DBMS_XMLSCHEMA.registerSchema – Register an XML schema. Parameter ENABLEHIERARCHY affects resource metadata.

• DBMS_XDBZ.enable_hierarchy – Enable repository support for an XMLType table or view. Use parameter HIERARCHY_TYPE with a value of DBMS_XDBZ.ENABLE_HIERARCHY_RESMETADATA to enable resource metadata. This adds column RESID to track the resource associated with the metadata.

• DBMS_XDBZ.disable_hierarchy – Disable all repository support for an XMLType table or view.

• DBMS_XDBZ.is_hierarchy_enabled – Tests, using parameter HIERARCHY_TYPE, whether the specified type of hierarchy is currently enabled for the specified XMLType table or view. Value DBMS_XDBZ.IS_ENABLED_RESMETADATA for HIERARCHY_TYPE tests whether resource metadata is enabled.

• DBMS_XDB.appendResourceMetadata – Add metadata to a resource.

• DBMS_XDB.deleteResourceMetadata – Delete specified metadata from a resource.

• DBMS_XDB.purgeResourceMetadata – Delete all user-defined metadata from a resource. For schema-based resources, optional parameter DELETE_OPTION can be used to specify whether or not to delete the metadata information, in addition to unlinking it.

• DBMS_XDB.updateResourceMetadata – Update the metadata for a resource.