PSQLQueryTool Glossary

Description

In order to make the most of the MSFF PSQL Mars database, you must be familiar with the schema and table layout of the database. The examples, provided here allow you to access the descriptions of the tables and columns available for you to use in your queries.

There are two sources for this information in the reference schema:

1. reference.pubmars_glossary

   This is a VIEW based on joining several of the postgres control tables which define the actual Mars data tables; this includes tables in the public schema. Field descriptions will be null, if the table author did not populate a descriptive comment about the column.

   Suggested selection-list fields: schema_name, table_name, column_name, datatype_name, description

2. reference.glossary

   This is a hand-updated table describing all of the tables under the control of THEMIS Mission-Operations; the descriptions provided here may be more verbose than those in the pubmars_glossary

   Suggested selection-list fields: table_name, field_name, value_type, description

   Suggested constraints: schema_name='thmpub'

The examples provided below are intended to get you started; however, you should refine the constraints-list and the order-by-list according to your needs.

Procedure

• Use the following commands in each of the following examples

1. Write the query; for more information see Writing PSQL-DB Queries
2. Access the PSQLQueryTool from Davinci; see the function page for a complete description of all available options
3. Read in the results using one of the two options shown here:

   "read_lines" is better for queries that return fewer lines

   "copy" is better for queries that return hundreds of lines or verbose fields

dv>query="select table_name, field_name, description from glossary where schema_name='thmpub' order by table_name, field_name;"

dv>url=themis3db(query,psql=1,header=1)
 Using PSQL password from: /themis/lib/dav_lib/dbpass/THM_DB_USER_davinci
 Read TEXT file: 1 lines
 make this a link! http...

dv>gloss=read_lines(url)
 Downloading.done.
 Read TEXT file: 0 lines
 Text Buffer with 0 lines of text
--OR--
dv>copy(url,$TMPDIR)
 Downloading.done.
 [path to file repeated to screen] 

• Example-1: Get a list of available tables

dv> commands here
dv results ...

• Example-2: Explore the columns available in a single table

dv> commands here
dv results ... 

• Example-3: Search for available columns related to the concept of "temperature"

dv> commands here
dv results ...