Using an administrative table to manage documents

Administrative tables let you extend the information stored about published documents. They aren’t required for general use, but they’re useful to support custom applications for accessing and administering Metadata Services. An administrative table is an additional database table that is joined to the service’s business table.

A Metadata Service stores a record in the database for every document published to it. For each record, columns in the database contain information about the document, such as its name, the geographic extent of the resource it represents, an online link to the data or information about the data, and whether or not it has any related documents. Some of this information is derived from the document during publishing, and some is maintained by the Metadata Service to manage the published documents.

This database schema was designed and developed to work in conjunction with ArcCatalog and Metadata Explorer. For example, Metadata Explorer lets you preview an ArcIMS service associated with a published document. The information about the ArcIMS service is stored in columns in the database and is included directly in the ArcIMS response when the document is found by a search. Therefore, Metadata Explorer can get the information directly from the response and not have to open and look through the contents of the published XML document to find the online link. The database schema allows Metadata Explorer to quickly retrieve the information it needs.

Suppose you want to write your own application that uses a Metadata Service. You might want to store your own information about a document in the database so it can be quickly retrieved by your application. While you can’t add columns to the Metadata Service’s business table, you can extend it by creating your own table in the database to hold the required information. This table is referred to as the administrative table.

You must create the administrative table before starting a Metadata Service. The administrative table is joined to the business table by the document identifier. In the business table, the document identifier is stored in the DocUUID column. One of the columns in your table must be able to contain strings 38 characters long so it can contain the identifiers matching those stored in the DocUUID column. Add other columns to this table as required by your application. This table must be created in the database with a SQL create table statement; it must not be created using ArcCatalog or ArcSDE.

When a document is published, a record is added to the administrative table using the SQL Insert statement defined in the configuration file. Specify the value “%s” to be stored in the Metadata_ID column; ArcIMS substitutes the document’s identifier for this value. If default values weren’t defined for other columns when the table was created, you can provide them in the insert statement as well. Values in other columns can be included in the ArcIMS response when documents are retrieved to support your application. Do this by defining RESPONSE_COLUMNs in the configuration file; be sure to provide default values for these columns—they must not contain null values.

When a document is retrieved from the Metadata Service, an SQL “Where clause” specified in the configuration file is appended to the query sent by ArcIMS to the database; this filters the documents returned based on information in your table. Suppose your organization has strict requirements regarding the content of metadata published to your Metadata Service. The service administrator or librarian will probably want to review the published documents to verify they meet your requirements before letting them be viewed by others. You might validate documents against a metadata standard or simply verify that a value is present to support a predefined search that is available in a customized version of Metadata Explorer. You can use this mechanism to filter out documents that haven’t passed your inspection.

At a minimum, your administrative table would need two columns: one for the document ID, which is set by the Metadata Service when the document is published, and the other for your approval code, such as “yes” or “no”. When a document is published, a record is added to the service’s primary table. The insert statement adds a record to the administrative table and sets the value in the approval column to “no”. The Metadata Service’s filter only retrieves documents with the value “yes” in the approval column. With a custom application, you could review unapproved documents and, once their content has been verified, change the value in the approval column to “yes”. The published document would appear in search results after being approved.

Managing published documents with ArcCatalog

The above example illustrates a simple administrative table with one custom column. You could create a more sophisticated administrative table or set of tables containing many additional columns that are required to support your custom application.

Administrative tables must be created directly in the database—for example, by executing a SQL create table statement. They must not be created using ArcCatalog or the ArcSDE sdetable command. The administrative table may be registered with ArcSDE, but it must not have a row_id column.

The administrative table must exist before a Metadata Service has been created. If the table is created afterward, it won’t contain records corresponding to any documents that have already been published; in particular, the service’s root document won’t exist in the table. To add an administrative table to an existing Metadata Service, you must create the table in the database, create a new configuration file that references it, use the new file to start a new Metadata Service, then republish your documents to the new service.

If you import documents into a Metadata Service using the MDImport command, records representing the imported documents won’t be added to the administrative table. Imported documents have a different collection identifier than documents published directly to your Metadata Service. If you want to manage imported documents in the same way as documents that are native to your Metadata Service, you could use a database trigger to add records to the administrative table when records with a different collection identifier are added to the service’s feature class.

When a document is deleted from a Metadata Service, the corresponding record in the administrative table is not deleted. Information about a published document may be stored in the administrative table that should not be lost even if a document is deleted. For example, a public document might be deleted if the service it describes is unavailable while being moved to a different server. If the document’s record was automatically deleted from the administrative table, notes about suggested edits or other interactions with the document’s publisher might be lost; when the document is republished, it might take longer for the document to be approved for publication.

Your application must manage deletion of records from the administrative table if this is important for your application. ArcIMS maintains a table in the database that keeps track of deleted metadata documents. Your application could use the information in this table to delete records from your administrative table.

Create a table in the database using a SQL CREATE TABLE statement.
Define one column to contain strings 38 characters long. This column will contain the document identifiers.
Define any additional columns required by your application.
Create a copy of your Metadata Service’s configuration file and edit the copy in a text editor.
You can use the MetadataServer.axl file distributed with ArcIMS. On Windows, this file is located by default at C:\ArcIMS\AXL\Metadata.
Remove the comment characters enclosing the ADMIN_TABLE element: “<--” and “-->”.
Specify the appropriate values for tablename, idcolumn, filter, insert, and columnname.
Create a new Metadata Service using the modified configuration file.
When the service is created, a record is added to your administrative table representing the Metadata Service; without this record, the administrative table can’t be properly joined to the service’s business table. The record representing the Metadata Service will only be added to the administrative table the first time a Metadata Service is created; this is why administrative tables can’t be added to existing services. If problems are encountered starting the Metadata Service, delete any objects that were created from the database and check the service’s log file to determine the cause of the problem. Modify the configuration file or recreate the administrative table as appropriate and start the service again.
Publish documents to the new Metadata Service.

For every document published, a corresponding record is added to your administrative table using the insert statement specified in the configuration file.

Using an administrative table with a Metadata Service

tablename—The name of your administrative table. This name is case sensitive with some databases. Match the case used in the database for the table name and column names.

idcolumn—The name of the column in your administrative table that contains the document identifiers.

filter—The SQL WHERE appended to all queries sent to the database by the Metadata Service. This lets you filter out documents that aren’t ready for public consumption using values in your administrative table. For example, a search would find documents updated before April 1 and documents set to approved. Use “filter=” if you don’t want to filter documents, or remove the filter attribute entirely.

insert—The SQL INSERT statement is used to add records to your administrative table when documents are published. The name of the administrative table specified in table name must appear in the INSERT statement between INTO and the first set of parentheses, (). List columns in the administrative table into which default values will be inserted in the first set of parentheses, and list the default values in the second set of parentheses. The value “%s” must be inserted into the column specified in idcolumn.

columnname—The name of the column in the administrative table whose values will be returned in the response when documents are found in a Metadata Service. The column’s name is added as an attribute to the METADATA_DATASET element in the response. You can include more than one RESPONSE_COLUMN element if you want to return the values from more than one column. Only columns containing strings can be response columns, and they must not contain NULL values.

Using an administrative table to manage documents

Related topics