Home
About Isis
Support
Download

Isis 3

Documentation
Tutorials
Technical Documents

Isis 2

Documentation
Tutorials
Technical Documents

Search
USGS

Isis 3 Application Documentation

pvltodb

Printer Friendly View | TOC | Home

Map a Pvl file to a database table record

Overview Parameters

Description

pvltodb will extract the contents of a Pvl formatted file (FROM) such as a PDS EDR label, an ISIS label or a resulting file from an ISIS application and insert them into a database table.

A database map configuration (DBMAP) file contains the specific of what keywords in the input Pvl file are mapped to individual fields in a database table. The format of this map configuration file is also Pvl.

Access to a database is generalized in an externally managed file in Pvl format. The database configuration (DBCONFIG) file contains access profiles that gives users a flexible framework with which access and application parameters can be specified.

Categories

History

Kris Becker2007-01-24 Original version
Brendan George2007-06-05 Modified to work with iString/StringTools merge
Steven Lambright2007-07-27 Changed category from Utility to System
Kris Becker2007-09-11 Fixed bug when processing more than one table mapped into the database. It needed a persistant database connection. Also changed how data is added to the database - it now uses an update/insert sequence.
Kris Becker2007-10-31 Added the ToFormalCase functor to capitalize first characters of words; added the type of operation (INSERT, UPDATE, SELECT) performed to status report; changed format of verbose to report and write results as a formal group to print file and/or terminal.
Kris Becker2007-11-13 Modified SQL string quoting to properly handle embedded single quotes (excluding Geometry footprints)
Kris Becker2008-02-06 Added the IsEqual(arg1,arg2) functor to compare a value with another, without regard to case. This functor returns TRUE if the strings are equal, FALSE if not.
Kris Becker2008-03-19 Added the Numeric(arg1[,arg2]) functor to validate a numeric value from a PVL input file keyword or expression. By default it returns NULL if it is not a numeric value. The user can provide a second argument (arg2) of their choosing if NULL is not desired (Note that whatever value it is inserts into the database with that value).

Parameter Groups

Files

Name Description
FROM Pvl file to map to a database table
DBMAP File that maps Pvl keywords to database table fields
DBCONFIG Database configuration file to use to access database

Options

Name Description
PROFILE Name of access profile to specify connect options to the database
TABLESSpecifies the name of the database table or tables to insert or update
MISSINGAction to take when a keyword cannot be resolved
MODESpecifies if the result is to be commited to the database or validated for correctness
VERBOSE Optionally generate details on some aspects (results) of the processing
SHOWSQL Allows option printing of SQL INSERT or UPDATE (to be) issued
X

Files: FROM

Description

This is a file that contains Pvl statements. This file could be any Pvl file including PDS EDR/RDR labels, ISIS cubes and output files in Pvl form.

Type filename
File Mode input
Filter *.*
Close Window
X

Files: DBMAP

Description

This file is a Pvl file that contains "key = value" entries. The "key" is a database field name and "value" is of the form "/Object/Group/Keyword". Generally, what ever nested form is supported by ISIS is allowed in the keyword "path". Also provided is an integer index in any element in the path so that the nth occurance of the object, group of value of a keyword can be directly accessed.

Type filename
File Mode input
Internal Default None
Close Window
X

Files: DBCONFIG

Description

The contents of this file contain access information to database such as the IP address or host name, database name, user and password. It can also contain application specific information that may vary for each application. It is really completely up to the application developer what sort of information can exist and its purpose. The sky's the limit, really.

The following is an example of a DBCONFIG file format: 

  Object = Database
    Name = UPC
    Dbname = upc
    Type = PostgreSQL
    Host = "upcdb0.wr.usgs.gov"
    Port = 3309
    Description = "UPC provides GIS-capable image searches"
    AlternateHosts = "upcdb1.wr.usgs.gov"
    DefaultProfile = "upcread"

    Group = Profile
      Name = "upcread"
      User = "upcread"
      Password = "public"
    EndGroup
  EndObject

This can be set up automatically by using the IsisPreferences file. If you have to set up your own, specify it here using, if necessary, the PROFILE parameter to use a named access profile.

Type filename
File Mode input
Internal Default None
Close Window
X

Options: PROFILE

Description

A named profile from the DBCONFIG file that is used to access the database. This can also be specified in the config file using the DefaultProfile keyword in the DBCONFIG file.

Type string
Internal Default None
Close Window
X

Options: TABLES

Description

This option can be used to select one or more tables in the DBMAP file to update as defined in the DBMAP file. The default is to apply an insert or update to all tables defined in the map definition file.

Tables are defined by GROUPs in the DBMAP file. The name of a database table directly corresponds to a named GROUP in the Pvl file. To specify one or more tables, enter them as a string with each table (group name) separated by a comma.

Should the table not exist, an error will be issued and the application will abort with no action taken.

Type string
Internal Default All
Close Window
X

Options: MISSING

Description

If the keyword in the DBMAP cannot be found in the FROM Pvl source, this parameter will determine what type of action to take. In general, this means a value for the field in the database table does not exist. You can require the keyword to exist for every one specified by using the ERROR option. The database field can be NULLed or an ERROR may be signaled as well.

Type string
Default EXCLUDE
Option List:
Option Brief Description
EXCLUDEExclude the field map and keyword value from database operation This option will exclude all database map fields resulting the processing of the DBMAP that do not have a Pvl keyword/value in the FROM file from the INSERT or UPDATE SQL query. Using this option allows processing to continue if even one field parameter resolves to a value from the FROM file Pvl source.
NULLSet the database field to NULL This option will set unresolved database field map keywords to the SQL NULL value. Using this option requires that any database field that is set to NULL must not be qualified with the "NOT NULL" column constraint, otherwise the SQL INSERT or UPDATE will fail. Obviously, this requires thorough knowledge of how tables are created.
ERRORIssue an error and abort the application This option ensures that all database map fields have resolved the keyword parameters as specified in the DBMAP file. If any one of the keywords fails to resolve to a Pvl keyword in the FROM Pvl file, this would result in an error.
Close Window
X

Options: MODE

Description

This parameter can be used to determine the validity of the input files and processing performed on the data. The COMMIT mode will actually attempt to access the database and insert or update the results to the specified database table configuration. One can also VALIDATE the input data without accessing the database (unless there are database references contained within the input configuration files).

Type string
Default COMMIT
Option List:
Option Brief Description
COMMITCommit the processing by inserting result into the database The normal mode of operation is to insert the resulting processing into the database. This MODE indicates to commit the insert to the database if no fatal errors are encountered.
VALIDATERuns a check on the input files for correctness without inserting result into database To ensure the input configuration is correct, this option can be used to check the results for validity. The result of this selected mode will generate and output the actual SQL command that inserts or updates the result of the processing. Users knowledgeable in SQL can verify it's correctness.
Close Window
X

Options: VERBOSE

Description

This option provides user control over the amount of detail that is generated as a result of the processing.

Type string
Default NO
Option List:
Option Brief Description
NO Processing results are not reported, program operates silently This will not produce output of the requested processing with the exception of errors that are encountered.
YESReport aspects of processing This option will report the actual SQL statement resulting from the processing and the number of rows affected by the COMMIT, if so selected.
Close Window
X

Options: SHOWSQL

Description

This option was added to provide an explicit level of control when printing the SQL INSERT or UPDATE query generated by processing.

Type string
Default NO
Option List:
Option Brief Description
NO SQL query results are not reported This will not show the SQL query
YESShow me the query This option will print the actual SQL statement.
Close Window