The database definition file
The database definition file is an ASCII file created during the database creation procedure that contains a detailed description of the database. The name of the definition file is the database name with a .ddb extension, for example gold.ddb.
The .ddb extension signifies a geological database definition, as opposed to an .sdb extension for a survey database.
The following table shows a complete description of the database definition file including the keywords that are allowed, the type of argument and a description of the argument and, where appropriate, the valid values which you can use for the argument.
There are two argument types used by the database definition file:
- identifier - A name or identifier. Consists of one or more characters. No spaces or special characters are permitted with the exception of the underscore '_' character. Identifiers should be no greater than 18 characters in length.
- string - A string of characters surrounded by " (double quote) characters. The string of characters can be no longer than 1024 characters in length.
| Keyword | Argument Type | Argument Description |
| DATABASE | identifier | Name of the database |
| TYPE | string | Type of database engine for the database. Available types are:
|
| DB_SPECIFIC | string |
Defines some database engine specific features or mapping to the physical database name. "name=explore;" would indicate that the database as it exists in a driver is actually called explore. "~connect_string=<ODBC connect string>~; sql_extra=<SQL statement>; oracle_set_role=<SQL statement>; prompt_username=<on/off>; systables=<yes/no>; tables=<yes/no>; views=<yes/no>; synonyms=<yes/no>; table_mask=<mask for tables>; user_mask=<mask for table owners>; include_tables=<exclusive list of tables>; exclude_tables=<tables to exclude from the database>;" Notice that tilde characters surround the connect string option because it may contain the semicolon separator character. The connect string option must precede all other options. The connect string may contain any ODBC driver specific option that is valid for the particular driver you are using. It must at least contain the option DSN=name where name identifies the data source being connected to. An example is ~connect_string=DSN=ssidb;locking=none~. sql_extra may be equal to any valid SQL statement that you wish to execute after the initial ODBC connection has been made. For example, you may want to set security or set a particular environment for your database. If you were connecting to an Oracle database you may want to set a user role. You could do this by sql_extra=set role roleid. oracle_set_role is a specific option for setting roles in an Oracle database. It can be set to an sql statement that returns one column which is the role name. This name is then used to set the role in Oracle. An example could be oracle_set_role=select rolename from roletable; prompt_username=on/off can be specified to allow you to have a database logon banner appear when you connect to the ODBC data source. This will avoid the need to have the connect string clauses UID and PWD hard coded into the ddb file. User names and passwords are only required if your database management system supports and enforces them i.e. Oracle. The various on/off style options are used to control what objects are visible from the actual data source. The example following describes the default behaviour. systable=off;tables=on;views=on;synonyms=off. Table_mask and user_mask, are used to restrict the number of tables that the software will consider as part of the database. These options may contain the wildcard characters * and ?. An example usage would be table_mask=ssi* meaning only consider tables whose name begins with ssi. Include_tables and exclude_tables are comma separated lists of table names that are also used to restrict the tables that the software will consider to be part of the database. The include_tables list is an include list meaning only consider the tables listed. The exclude_tables list is a list of tables which are not to be considered, or include all tables except those in the exclude_table list. An example could be include_tables=collar,survey, assay1, assay2. In the case where a table name has been mapped using "DB_SPECIFIC name=tablename" the mapped name should be used in the include_tables and exclude_tables lists, not the physical table name. For an Access database, the following options are valid sql_extra=<any valid sql statement> Note that the pwd=<optional password> specifies a password to use to connect to the Access database. If the password is left blank, you are prompted for the password when you try to connect to the database. An example where the For an INFORMIX database type the following clause is also valid. For an ORACLE database type the following clause is also valid. The advantage in using the table filter clause is that you can filter out extraneous database tables which have no relevance to the Surpac database currently in use. |
| TABLE | identifier | Defines a table in the database. Other keywords which can be used to describe a table are DB_SPECIFIC. |
| DB_SPECIFIC | string | For ODBC databases
DB_SPECIFIC "name=<table>;include_fields=<field list>;exclude_fields=<fields list>" where name is the actual database table name, include_fields is an inclusive comma separated list of field names, and exclude_list is a comma separated list of fields to exclude from the table definition. If you use an include_fields specifier then only the fields in the list will be considered. When using an exclude_fields specifier, all fields other than those in the list will be considered as part of the table definition. For example name=basinfo; include_fields=hole_no, depth,x_coord, y_coord,z_ For SSI or ISAM databases, this qualifier will specify the actual table file name e.g. DB_SPECIFIC "name=goldco;" For other database types e.g. ORACLE, it may specify the physical table name in an existing ORACLE database e.g. TABLE collar |
| FIELD | identifier | Defines a field in a table. Other keywords which can be used to describe a field are TYPE, DB_SPECIFIC and VALIDATION. |
| TYPE | identifier | When describing a FIELD in a table the TYPE identifier describes the data type of a field and also the size and precision (decimal places).
Valid values are:
w = field width |
| DB_SPECIFIC | string | This can be used to map field names to the physical field name in an existing database e.g.
FIELD hole_id |
| Keyword | Argument Type | Argument Description |
| VALIDATION | string |
A string of characters which describe the validation characteristics for validation of data when editing, inserting or loading rows from a text file. The contents of the validation string are one or more edit descriptors delimited by the `~' character. The edit descriptors have already been defined in documentation relating to the macro system. For details see the section on Macros in the CMS Users Reference. A validation feature unique to the database is the usage of the `SSI_set=@fname' where fname equals the field name in the translation table in which the permissible values are stored. This will cause validation of character fields to be performed by using the translation table field name 'fname' as a lookup table to ensure that the value entered is a member of a defined set of values. |
| NULLS | identifier | Defines that NULL values are permitted in the field. |
| INDEX | identifier | Defines the name of the index. This is particularly important for third party database engines. This keyword must be further qualified by the keywords UNIQUE or DUPLICATE to define if duplicate values are permitted. Furthermore, one or more fields must be identified as the index fields with the keywords ASC or DESC to indicate ascending or descending order. |
| UNIQUE | identifier | Defines that the index values must always be unique. |
| DUPLICATE | identifier | Defines that duplicate index values may exist. |
| ASC | identifier | Defines a field in the table which will be used in the index and that it will be sorted into ascending order e.g.,
ASC hole_id |
| DESC | identifier |
Defines a field in the table which will be used in the index and that it will be sorted into descending order e.g., DESC hole_id |
| CALCULATED | function | Defines a field to be a calculated or virtual field. A VIRTUAL field is one whose value is only determined when data is retrieved from the field. This is in contrast to a PHYSICAL field which is a field whose value is explicitly stored in the database. A description of the functions which can be used by the CALCULATED keyword are described in a table below. |
Notes: 
technical detailsBelow is a description of the functions which may be used for CALCULATED fields. All functions take the form of
function_name(argument1, argument2, argument3, ...)
The number of arguments, the type of arguments and permissible values for the various arguments in a function are defined by the function itself.
| Function | Number of Arguments | Description |
| y | 2 |
Determine the Y coordinate of the drill hole trace at some depth down the hole. arg1 = name of hole id field in the table e.g. |
| x | 2 |
Determine the X coordinate of the drill hole trace at some depth down the hole. arg1 = name of hole id field in the table e.g. |
| z | 2 |
Determine the Z coordinate of the drill hole trace at some depth down the hole. arg1 = name of hole id field in the table e.g. |
| translate | 6,9,12 or 15 | Provides a translation service for converting some stored numeric value into a textual representation for special conditions. The classic case is to convert a value stored as -1in a field to `NS' on extraction to indicate that no samples are present. This will be used as an example. |
| See below for details on constant calculated fields. |
The table below describes the arguments for the translate function.
| Argument # | Description |
| 1 | Name of the table which contains the fields to be used in translating the stored value to the extracted value. Typically this will be `translation' as this is the table in which the translation codes are stored. |
| 2 | Name of the field in the translation table from which a value will be returned if the translation criteria are met. Typically the `code' field of the translation table is used here. |
| 3 | Name of the field in the current table containing the values which are to be translated. This can be any physical field in the table that contains the virtual field being defined by the translate function. In this example the gold field is being used. |
| 4 | A logical operator. Valid values are = - values must be equal < - one value must be less than the other > - one value must be greater than the other <> - values must not be equal |
| 5 | Name of a field in the translation table which is to be used as the first argument to the logical operator. Typically the num_equiv field is used here as it defines the field constraint for the various codes which are used. |
| 6 | The second argument to the logical operator. This can either be the name of a field in the current table or a string of characters surrounded by double quotes. Typically the same field name as the third argument is used as it is the values from this field which are to be translated. |
Note:The content and intent of the 4th, 5th and 6th arguments may be repeated as many times as required to create the desired effect. The example below is also explained in simple english in an attempt to resolve any confusion which may arise.
|
English Equivalent of Translate Function
To determine the value for the tgold field the following steps are performed
- Get the value from the gold field for this row in the table
- Query the translation table for a row where the following selection criteria are met
(a) The value in the num_equiv field of the translation table is equal to the value in the gold field of the assay table
and
(b) The value in the field_name field of the translation table is equal to the text `gold'
and
(c) The value in the table_name field of the translation table is equal to the text `assay'
- If the criteria in 2 above are met then return the value in the code field of the translation table as the value of the tgold field in the assay table.
- If the criteria in 2 above are not met the return the value from the gold column in the assay table as the value of the tgold field in the assay table.
Thus, some textual representation is returned if the selection criteria are met otherwise the actual numeric value which is stored in the gold field is returned.
Calculated constant fields
The constant specifier can also be used with the calculated keyword as in the example below:
FIELD calc_field
CALCULATED constant(1)
This will create a virtual field called calc_field with each record in the table have a value of 1 for this field. Further examples are:
FIELD calc_field
|
For the numeric field calc_field, use a value of 1 |
FIELD calc_field
|
For the character field calc_field, use the string LINEAR |
FIELD calc_field
|
Use the same value for the calc_field as the hole_path field for eachrecord. Note that the type of both fields must be the same. |
Below is an example of a database definition file for a simple database. You may find this useful when referring to the above tables to see examples of the usage of the various keywords.
|