Table of Contents

Version and Contacts

Glossary

Overview

Table Structure

Linking Tables

Data Searches

Appendix

Figures

This manual documents the Vanilla software.

Present software version: Vanilla 3.10 (6/16/00)

All examples used in this manual are from the Mars Global Surveyor Thermal Emission Spectrometer [complete listing of sample data is contained in the appendix]. In addition, all headers and labels are in PDS compliant format. Vanilla was developed at Arizona State University by Noel Gorelick, Saadat Anwar, Kamran Qazi, and Michael Weiss-Malik (the programming gurus). This manual was written by Kelly Bender (a Vanilla user, but not a programmer) and Noel Gorelick. Questions regarding Vanilla can be directed to vanilla@tes.asu.edu.

ASCII

DATASET

Fixed-length record

Fixed-length array

Fragment

Key

Key block

PDS

Table

Vanilla

Variable-length record

Vanilla is a database query tool that allows for easy storage and retrieval of fixed and variable length data. It uses a table format and allows connection between tables via a primary key. The Vanilla search function is run in a command line format and outputs in ASCII. Vanilla is easy to learn, user friendly, and easily applied to large datasets.

Prior to using the Vanilla software, the user needs to carefully assess the data that will be put into the Vanilla database format. Many decisions have to be made regarding the segregation of the data into different tables, the assignment of column headers, and the identification of fundamental data links between the various tables that will be used. Once these issues have been laid out, this manual will help in the actual setup of the database in Vanilla format. Changes are readily accommodated by Vanilla. It is always possible to add additional tables as they become necessary (or useful), so the results of the initial database layout activity can be modified over time.

Using the MGS-TES as an example, the instrument outputs a data stream that contains radiance data, bolometric data, instrument telemetry, a record of the instrument settings used for each observation, and a time stamp for every piece of data collected. So just from the raw instrument output the following tables were devised: radiance data (rad), bolometric data (bol), telemetry (tlm), and observation parameters (obs). Time is stored in each of the tables and provides the link among them. In addition to the raw data tables, other information was needed for use in analysis of the data. In the case of MGS-TES, geometric information was provided by the project for use in calculating such parameters as footprint latitude and longitude. The calculated geometry data was put into a table (geo). Science analysis results and data quality flags are examples of later additions to the MGS-TES Vanilla database.

A fragment is the smallest unit of data that Vanilla considers a "file" and therefore has a filename and contains a header at the start of the fragment prior to the data. There is no limit to the number of rows or columns contained in a fragment. Fragments with no data records are bad and will cause the Vanilla program to have problems.

A table is a collection of one or more related fragments. All fragments in a table must be sorted in ascending order based on the PRIMARY_KEY (discussed in the key section below). Vanilla assumes all files with the same name to be fragments of a single table sorted lexicographically on filename. It also assumes that all fragments in a single table contain identical keys/columns.

Each fragment is prefixed with an ASCII header in PDS 3.0 format. The format of this header consists of sets of keyword=value pairs, followed by the keyword END. A sample header is given below: [lines are numbered for easy reference — numbering is NOT part of the PDS format]

The above header consists of three primary parts: a description of the whole fragment, a pointer to the binary data, and a set of nested PDS objects that identify the contents and layout of the fragment.

Line 1 identifies the PDS format version for the entire header. Line 2 is the fragment filename. Fragment (or file) names are comprised of three parts: the table name, the fragment number and an extension. Table names are defined in the OBJECT=TABLE section of the header (lines 24). The table name can be any combination of letters, is case insensitive, and can be any length (TES uses three letters). Fragment numbers must be a numeric string. There is no limit on the length of the fragment numbers. The extension will be .DAT or .VAR depending on the data type. The fixed-length records are stored in files with a .DAT extension. The variable length records that are referenced by an individual .DAT file can be found in a file with the same name, but with a .VAR extension. The .DAT extension can take the form .[Dd][Aa][Tt] or .[Tt][Aa][Bb].

Lines 3 - 6 of the header describe the overall structure of the fragment and in this case indicate that it consists of 1,245 fixed-length records, 39 bytes in length. The entire ASCII header is padded with white space to occupy an integral number of records of the 39 byte length. The header record number is reported on line 6.

The keyword ^TABLE is a pointer to the start of the binary data (line 7). The number given with this keyword is the record number of the start of the data. In this case the record number is 36, which starts at byte 1404 counting from byte zero (35 records * 39 bytes/record).

Lines 8 through 22 are PDS required entries used for archival and data distribution purposes.

Nested object items (lines 23 — 31) identifies the table that the fragment is part of (line 24), the data format (line 25), the key element used for connection among the various tables (line 26), the start and stop values of the key element in the fragment (lines 27 and 28), and the number of rows of data contained in the fragment (29). The keyword ^STRUCTURE (line 30) points to a separate file which details the individual column descriptions. The OBJECT=COLUMN listings are kept in a separate format file (extension .FMT) that the Vanilla program will access in order to read the data. By separating the .FMT files for the different tables of data, the user is provided with an easy reference to the contents of each table. Knowledge of the individual column names is necessary for utilizing the search capabilities of Vanilla.

All data columns have a name. Occurrences of the same column name in different tables are assumed by Vanilla to be related. In other words, column descriptions must be unique but can be used in different tables. For example, NAME = DETECTOR is used in all TES tables, but the column data type and description are identical everywhere.

The following data types are allowable for use in Vanilla columns:

1. 1, 2, or 4 byte signed or unsigned integer
2. 4 or 8 byte real number
3. fixed length string
4. fixed length array of form A, B, or C
5. 1, 2, or 4 byte bit string of signed or unsigned integer bit fields
6. variable length array of form A, B, or C with maximum array length of 32 kbytes

Vanilla search functions can be used to constrain data extraction in columns with data types A, B, C, D, or E. Variable length arrays (type F) can not be constrained during data extraction. More information about the search capabilities of Vanilla is presented later in this document.

Object listings for all columns in a table are contained in the ^STRUCTURE file referenced by each data fragment. Part of a sample STRUCTURE file (obs.fmt) is shown below. [see Appendix for complete *.fmt examples.] The STRUCTURE file lists the table name, the total number of columns contained in the table/fragment, the number of bytes per row (all rows must be the same), a description of the table, and an OBJECT=COLUMN entry for each column [only one is listed below].

Each OBJECT=COLUMN listing includes the column name, data type, starting position (in bytes), size (in bytes), scaling factors if applicable, and description. A scaling factor is used to convert from the stored value to useful units. A scaling offset may also be included, but if not included, should be assumed to be zero. Scaling factors and offsets should be applied as follows:

scaled_value = (stored_value * scaling_factor) + scaling_offset

Only fixed length data can be scaled and offset. Scaling factors and offsets are applied automatically by Vanilla during data extraction and automatically convert the data type to floating point.

Descriptions are provided for every column. These descriptions are surrounded by quotes and may span several lines.

In some cases the column is a fixed-length array of related, homogeneous values (such as temperatures or voltages). For that case, the column listing also includes the BYTES term to indicate the size of the array, and two fields (ITEMS and ITEM_SIZE) to describe the number and size of a single element in the array. For fixed-length arrays, column "padding" (leaving unused bytes between data bytes) is allowed, as the start bytes are listed in the column information.

The following column listing indicates that the column is a homogeneous array of 6, 2-byte integers.

Variable length data are stored in files separate from the fixed-length data and use .VAR extensions. For every .VAR fragment there will exist a .DAT fragment with the same name. The .DAT fragment will contain all the necessary headers and a "pointer" column. Pointer columns contain the position of the variable length data, in bytes, from the start of the relevent .VAR file. A position value of -1 in a pointer column indicates that there are no variable length data for that record.

Additional keywords in the OBJECT=COLUMN listing are used to identify it as a pointer to a variable length column, and describe the data contained in the variable length records. These keywords are:

The VAR_DATA_TYPE and VAR_ITEM_BYTES keywords are similar to the PDS keywords DATA_TYPE and ITEM_BYTES, but refer to the structure of the variable-length data. The VAR_RECORD_TYPE keyword identifies the overall format of the variable-length record. This keyword has two possible values:

The value VAX_VARIABLE_LENGTH indicates that the variable-length record has the size of the record in bytes, as a 2-byte integer, both before and after the record. This corresponds to the VAX/VMS variable-length record format.

The Q15 format is very similar to the VAX_VARIABLE_LENGTH format; however it is only used to store floating point values in a compact representation. This format is an array of floating point mantissas stored as 2-byte signed integers. These mantissas share a scaling exponent that is stored as the first item in the record as another 2-byte signed integer. All the elements in the array must be scaled by the exponent, by multiplying them by 2 to the power (exp-15). Just like the VAX_VARIABLE_LENGTH records, the Q15 records are also stored with the size of the record in bytes, as a 2-byte integer, both before and after the record.

A sample variable length fragment header is shown below.

See Figure 1 for an illustration of a variable length record and how it related to the column listing. See Figure 2 for a diagram of the Q15 variable length record.

To access the various tables of data, there must exist a fundamental logical element(s) common to the entire dataset. This fundamental element is called a key and must be numeric in value. A key may have more than one element, in which case it is termed a composite key.

Some tables may have just a single element key while others may have composite keys. The key is, in effect, the column heading for the first column (or columns) of data. Each row of data in a fragment/table will have a unique value for the key. Different fragments/tables with the same key are assumed to be related. Hence, keys provide the link between the different tables of data.

All data headers will contain the key (termed PRIMARY_KEY in PDS compliant format) and the start and stop values for the group of data to which the header belongs. For example:

The PRIMARY_KEY for any fragment may contain different key elements from other fragments; however all keys must be a partial subset of the longest composite key and all key elements must occur in the same order as the longest composite key’s elements.

For any fragment with a composite key, it is possible for a set of rows to contain the same value for the first element of the key and unique values for the second element. For example, using the PRIMARY_KEY above, for any single time value there can exist up to six different rows of data because there are six possible values for detector. Rows of data must be sorted in ascending order based on the PRIMARY_KEY. So, for the above example, the data must start with detector row 1 and end with detector row 6. A group of data rows with the same value for the first key is termed a key block.

An integral number of key blocks are combined to make a fragment. Key blocks cannot span fragment boundaries. The key blocks composing the fragment must be sorted in ascending order based on the PRIMARY_KEY.

For Vanilla to access data it must have a list of the table names and the locations of their fragments. This information is stored in a file named DATASET (case sensitive). The following formats are allowable:

1. One or more fragment filenames for fragments resident in the directory containing the DATASET file (for example obs00001.dat)
2. One or more table names for tables resident in the directory containing the DATASET file (for example obs). Vanilla will automatically assume that all fragments with the same table name are related.
3. One or more path/table name or path/fragment name entries (for example /mapping/data/p1/obs or /mapping/data/p1/obs00001.dat)
4. One or more path listings that direct Vanilla to a location where another DATASET file, with appropriate contents, is in residence (for example /mapping/data/p1 — and p1 contains a DATASET file).

Vanilla uses DATASET to find all available fragments. It then reads the header of the first fragment of each table to find the column definitions (assuming that all fragments with the same table name will contain the same columns). Once Vanilla has accessed this information it is able to search the data as desired by the user. Vanilla can only access tables that are listed in the DATASET file. Nonexistent tables listed in DATASET are ignored by Vanilla.

The real power of Vanilla is its search capabilities. Searches are done using a command line input. Data is output in ASCII format and can be read into other tools for data visualization and analysis.

The usage for Vanilla is:

>vanilla directory —fields "col1 col2 . . ." —select "select1 select2 . . ."

The meaning of each of the arguments is explained below.

directory

This argument is required and must be the absolute or relative path to a directory containing a DATASET file.

-fields "col1 col2 . . ."

This argument is required and identifies the table columns to output. The list of columns must be presented as a single string, and so, must be enclosed in quotes if more than one column is given. The format of a column identifier is:

table.column[index]

Where the ‘table’ and ‘[index]’ portions are optional. Column identifiers are separated by spaces, so no spaces are allowed within a column identifier.

The table prefix is only necessary when multiple tables contain columns with the same name (such as key columns). If the table prefix is not specified, the first table listed in the DATASET file that contains the named column is assumed.

It is possible for some columns in PDS tables to be specified as containing an array of homogeneous data elements. For these array columns, the optional [index] is used to specify which element(s) of the array to extract. The index can be a single number, indicating a single element of the array, or a range of numbers (specified by [low:high]), indicating multiple consecutive elements. If the user specifies the name of an array column and does not specify an index or leaves it blank (eg: column[]), the entire array is output.

Some columns are composed of multiple bit fields. If only the bit column is specified, the entire "bit word" is output as an integer number. In most cases this is probably not desirable. To extract a single bit field, the column should be specified by column:bit_column.

All variable length arrays require at least an empty index (ie: name[]). Leaving off the index extracts the variable length data pointer (the position of the variable length data in its .VAR file).

If columns from multiple tables are specified, an inner join is performed between all the tables involved and only those records that exist in all of the tables specified are output. Searches that use columns in the -fields portion that access tables with no keys in common will return zero records. If the search uses column names that do not exist in the fragments available from the DATASET, then Vanilla returns no records. This is allowable and does not cause any problems.

-select "select1 select2 . . . "

This argument is optional and specifies a selection criteria that a record must meet before it is output. Like the —fields argument, all the selection criteria must be presented to Vanilla as a single string and so must be enclosed in quotes. The column(s) used in the -select portion do not have to be the same ones used in the -fields portion. The format for a selection is as follows:

table.column[index] lowvalue highvalue

Like the —fields options, the ‘table’ and ‘[index]’ potions are optional and carry the same meaning. However, with or without the ‘[index]’ portion, the column identifier must specify only a single data element (eg: the [low:high] fomat for index is not allowed, and omitting the index is not allowed for arrays).

The ‘lowvalue’ and ‘highvalue’ portions of the selection specify a range that the column value must lie within before a record is output. Records that don’t meet all the selection criteria are discarded. The ranges are inclusive; a value must satisfy the following relation for the record to be considered for output:

lowvalue <= column value <= highvalue

If the column contains a character value, the comparison is lexicographical.

This appendix contains the sample data set used as examples in this document. The tables are all contained in a single directory along with the necessary format files — as reflected by the DATASET file.

Figure 1. This figure illustrates the use of variable-length records, and how they relate to the fixed-length records. In this example, the table contains 2 columns, one of which is a pointer to the variable-length records. The table shows 6 rows, but only 5 of the rows actually point to variable-length records. The fourth record contains -1 in the pointer column, indicating that there are no variable-length data for that row. The two column listings are shown below.

Figure 2. A diagram of a complete Q15 variable length record.