Isis 3 Application Documentation
Map a Pvl file to a database table record
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 Becker | 2007-01-24 |
Original version
|
Brendan George | 2007-06-05 |
Modified to work with iString/StringTools merge
|
Steven Lambright | 2007-07-27 |
Changed category from Utility to System
|
Kris Becker | 2007-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 Becker | 2007-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 Becker | 2007-11-13 |
Modified SQL string quoting to properly handle embedded single
quotes (excluding Geometry footprints)
|
Kris Becker | 2008-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 Becker | 2008-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
|
TABLES | Specifies the name of the database table or tables to insert or update |
MISSING | Action to take when a keyword cannot be resolved |
MODE | Specifies 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 |
|
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
|
*.*
|
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 |
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 |
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 |
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 |
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 |
EXCLUDE | Exclude 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.
|
NULL | Set 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.
|
ERROR | Issue 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.
|
|
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 |
COMMIT | Commit 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.
|
VALIDATE | Runs 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.
|
|
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.
|
YES | Report 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.
|
|
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
|
YES | Show me the query |
This option will print the actual SQL statement.
|
|