This is an old revision of the document!
The Omnidex Environment Catalog is the starting point for constructing an Omnidex application. It is the primary source of information used by Omnidex to access a database and the Omnidex indexes.
An Environment Catalog is a collection of information, objects that include descriptions of databases, tables, columns and indexes, as well as Omnidex specific information like optimization options and configuration details. Omnidex uses this information to determine how to access the databases. It can also store supplemental information about the databases, that can enhance Omnidex's ability to optimize queries.
The Environment Catalog is both a superset and subset of information about the database. It is a superset of database information because a single Environment Catalog can contain information about multiple databases, including databases from different database management systems. However, it contains only a subset of the information tracked in the database management system about each of those databases, limited to the few details required by Omnidex.
Omnidex is aware only of the objects defined in the Environment Catalog. Any databases, tables, columns, etc… not defined in the Environment Catalog do not exist, as far as Omnidex is concerned, providing a fundamental level of security.
In the case of flat-file databases, the Environment Catalog plays a special role. Since plain flat-files rarely have meta-data stored in them, Omnidex acts as the database management system, using the Environment Catalog to describe the tables and columns and their relationships to each other, there by allowing standard SQL, ODBC and JDBC access to the data.
An Omnidex Environment begins as an environment source file, a text file containing all the environment definitions, then is compiled into a binary file, the Environment Catalog used by Omnidex.
The best way to create an environment source file is to use the Omnidex utility OAHELPER. OAHELPER, can automatically generate an environment source file for most RDBMSs including Oracle, Informix, DB2 and SQL Server. Alternatively, you can create an environment source file by hand using any text editor.
If you have an existing Omnidex Environment Catalog, you can use the Omnidex utility OADECOMP, to decompile the binary file into an editable source file.
When the environment source file is complete, you must use the Omnidex utility OACOMP to compile the environment source file into a binary file, the Environment Catalog.
An environment file is divided into several statements beginning with the ENVIRONMENT statement. The general environment layout is as follows:
The ENVIRONMENT declaration contains overall configuration settings and is followed by one or more DATABASE statements. Each DATABASE declaration describes the basic database characteristics and access instructions, followed by a series of TABLE statements. Each TABLE declaration describes the table including its physical location, key constraints and a series of COLUMN statements. Each COLUMN declaration describes the database column, including data type and length. INDEX statements describing the native indexes in the database, follow the TABLE declarations. This sequence is then repeated, in this order, for each database being described in this environment.
The following is a simple example Environment source file with a single database and a single table:
Environment start_env Database start Type flatfile table sales_facts column odx_seq_ID datatype INTEGER column acct datatype CHAR(10) column amount datatype INTEGER
Statements in an environment catalog must follow a certain hierarchy. For example, COLUMN definition statements must follow the TABLE definition statement that defines the table to which the columns belong.
See the individual statement topics for specific details.
Syntax Convention [ ] Optional syntax
{ } Required, choose one from the set.
option1 | option2 - Choose option1 or option2
option1 / option2 - option1 and option2 have the same meaning, both work.
ENVIRONMENT environment_name
[VERSION "string"]
[TITLE "string"]
[SET
[WILDCARDS any_char, single_char, single_digit]
[OMNIDEX SENTINEL character]
[OPTIMIZATION "options"]
[ESCAPE character]
[SQL_SYNTAX {OMNIDEX | OMNIDEX_PLUS}]
[CENTURY_THRESHOLD n]
]>
Optional. This is a string of up to 32 characters that defines a version number for the Environment file. VERSION is used for informational purposes only. It in no way affects the application or how Omnidex behaves against the environment.
Optional. This is a string of up to 80 characters that describes the Environment file. TITLE is used for informational purposes only. It in no way affects the application or how Omnidex behaves against the environment.
Optional. The keyword SET is a section identifier for the SET section of the ENVIRONMENT definition. The following Environment settings are established through SET:
Must contain three different wildcard characters, separated by commas, from the following set: *!#%@^~{}'|?[]\/
Defaults * (asterisk) any string
? (question mark) any character
# (pound sign) any ASCII digit
Default % (percent sign).
specifies the default type of optimization performed by Omnidex throughout an application connected to this environment. These options can be toggled on (optimization_setting) or off (NO_optimization_setting). Several optimization settings can be specified in a single SET command, separated by commas or spaces. Example:
SET OPTIMIZATION=“AGGREGATION, SERIAL”
Default \ (backslash). Only a single escape character can be defined.
Specifies the year cutoff for determining the century in 2 digit years. For example, if the century threshold is set to 60, a 2 digit year of 45 would be translated to 2045 and a 2 digit year of 61 would be translated to 1961. This setting can be explicitly over-ridden for a specific column in the column definition. Environment name is a required string of up to 32 characters. Environment name can contain numbers and any of the allowed special characters. However, it MUST BEGIN WITH A LETTER, A-Z (case insensitive).
[DATABASE database_name
TYPE database_type_spec
[SUBTYPE database_subtype_spec]
[VERSION "version"]
[PHYSICAL "filespec"]
[OPTIONS "options"]
[INDEXPREFIX "filespec"]
[USERCLASS "DEFAULT" [USER "user"] [PASSWORD "password"]
[AUTOPSEUDOCOLUMNS]
[INDEXMAINTENANCE {DBMS | API}]
table_definitions for this database
index_specs for this database
[environment_name.]database_name
Required. database_name represents a non-ambiguous database.
Database_name MUST BEGIN WITH A LETTER, A-Z (case insensitive) and may contain up to 32 characters including letters, numbers and allowed special characters.
If database_name contains any reserved words or allowed special characters, it must be enclosed in single or double quotes.
Required string that declares the type of the underlying database. It can be any one of these values:
ORACLE FLATFILE ODBC DB2 UNION INFORMIX
Optional. Specifies the subtype of the database. If database type (above) is ODBC, indicate here what the actual underlying database type is. This will allow Omnidex to provide some additional functionality not available in strictly ODBC applications, depending on the database type. It can be any one of these values:
ORACLE INFORMIX DB2 SQLSERVER ACCESS
Required in ORACLE and optional in other databases. This is a quoted string of up to 255 characters that declares the version of the DBMS. It is used primarily with relational databases.
Optional. Physical declares the name and location of the database's root file or system dictionary and is used primarily with relational databases and ODBC.
string is a quoted string that may vary according to database type. See the appropriate database type for details on the PHYSICAL clause.
Optional.
Optional. The Indexprefix declares the root name and location of the Omnidex index files. filespec is a quoted string up to 251 characters in length and may contain any valid filename as well as appropriate path information and password information.
DBINSTAL appends 4 characters (0001, 0002, …) to the end of each index file name, therefore the filespec is limited to 251 characters.
Indexprefix is not used with TurboIMAGE databases, as the database name determines the index prefix and the group and account in the physicals indicates the index location.
A Required clause for ORACLE and TurboIMAGE (if the TurboIMAGE database is password secured) and optional for INFORMIX. It declares either or both the name and password of a valid database. All connections to the database will connect with this option. username and password can be filespecs where the contents of the file contain the username and password.
Automatically constructs pseudocolumns from MDK composite keys.
Optional. Default is API. Specify how Omnidex indexes will be updated.
API - The indexes will be updated by Omnidex automatically as the database data is updated.
DBMS - The indexes will be updated by some other means, ODXAIM for example, in a separated process.
This setting applies to the entire index installation on this database. It can be over-ridden on a table-by-table basis by explicitly defining the indexmaintenance setting on an individual table.
TABLE < tablename | "tablename" >
[ TYPE < ROLLUP | > ]
[ PHYSICAL "filespec" ]
[ PARTITION BY "partition_spec"]
[OPTIONS "options"]
[CARDINALITY n]
[INDEXMAINTENANCE {DBMS | API}]
[ AS "select_statment" ]
[{PARENT / PRIMARY} [{KEY / CONSTRAINT}] column_spec
[{CHILD / FOREIGN} [{KEY / CONSTRAINT}] column_spec
REFERENCES table[(column_spec)]]
< COLUMN [ FROM ]column_spec [ COLUMN column_spec ... > ]
table_name
table_name represents a non-ambiguous table or view name. table_name must be unique within the database statement but can be ambiguous within the environment. However, any reference to an ambiguous table_name in an application will have to be qualified by the database name: database_name.table_name
table_name MUST BEGIN WITH A LETTER, A-Z (case insensitive), and may contain up to 32 characters including letters, numbers and allowed special characters.
If table_name contains any reserved words or allowed special characters, it must be enclosed in single or double quotes.
TYPE is set according to the DATABASE TYPE declared.
One of the following:
[{CACHED | DYNAMIC}] VIEW OST (Flat Files) TDF (Flat Files) RELATIONAL (SQL-Class databases only)
Optional. filespec is a quoted string, unlimited in length, and can contain an unlimited number of comma separated file specifications, including file names and appropriate path and password information as well as any operating system command that outputs a valid physical file name entry. However, each individual file specification is limited to 255 characters in length. See File Name Handling for more details.
Options apply to TDF (tab-delimited file) tables only. Multiple options are space separated. The entire option string is enclosed in quotes. Valid options are:
COLUMN='string' - Defines the column delimiter.
RECORD='string' - Defines the record delimiter.
QUOTES - Instructs OA to support quotes in a record. If omitted, quotes are not supported. Note that this option does not cause OA to create TDF files with quotes. It is strictly to support quotes in existing files.
ESCAPE='character' - Defines an escape character used to escape delimiters and quotes.
options “column='\t' record='\r\n'”
The cardinality of a table is the number of records in the table. This number should be near the actual table cardinality. Omnidex uses this number to better optimize some queries.
Optional. Default is API. Specify how Omnidex indexes will be updated.
API - The indexes will be updated by Omnidex automatically as the database data is updated.
DBMS - The indexes will be updated by some other means, ODXAIM for example, in a separated process.
This setting overrides the indexmaintenance definition in the database section.
[{CHILD / FOREIGN} [{KEY / CONSTRAINT}] column_spec] Optional. The column_spec is the column name or a column specification qualified by a table name. keytype declares any of the following values:
PARENT or PRIMARY CHILD or FOREIGN
Foreign keys use a REFERENCES clause to relate the child to a parent table by a previously declared primary constraint. Example:
FOREIGN CUSTOMER_NO REFERENCES CUSTOMERS(CUSTOMER_NO)
- or -
CHILD KEY CUSTOMER_NO REFERENCES CUSTOMERS(CUSTOMER_NO)
Primary keys and foreign keys can be declared for the same table. Multiple column constraints are not presently supported. Omnidex does not enforce constraints. Constraint enforcement is left up to the DBMS. Therefore, constraints on flat file databases are not enforced.
AS defines a table as a UNION VIEW. Data from other physical tables in the database is unioned together using SQL select statements. The other physical tables must also be defined in the environment file.
The column definitions for the view must match the original physical tables exactly. Therefore, the COLUMNS FROM “table” column definition should be used, as opposed to explicitly defining each column in the view.
COLUMN column-name DATATYPE datatype-spec
[PROXIMITY “options”]
[SYNONYMS 'options']
[SPELLCHECK 'options']
[MISSPELLINGS 'options']
[FORMS 'options']
[STOPWORDS 'options']
[PHONETIC 'options']
[INCLUDED_TAGS 'options']
[EXCLUDED_TAGS 'options']
{AS < columnspec | "$retrieve_file(FILENAME)" > ]
COLUMN column_name
[INTERNAL]
DATATYPE datatype_spec
LENGTH n
[SCALE n]
[PRECISION n]
[FORMAT format_spec]
[PHYSICAL "native_column_name"]
[USAGE usage_spec]
[CENTURY THRESHOLD n]
[OMNIDEX
[DATATYPE datatype_spec]
[LENGTH n]
[FORMAT format_spec]
]
[AS select_item]
[SQL SELECT "string"]
[PROMPT "string"]
[HEADING "string"]
[LABEL "string"]
[CARDINALITY n]
[[[environment_name.]database_name.]table_name.]column_name[(start, length)]
column_name represents a non-ambiguous column or portion of a column.
column_name MUST BEGIN WITH A LETTER, A-Z (case insensitive) and may contain up to 32 characters including letters, numbers and allowed special characters.
If column_name contains any reserved words or allowed special characters, it must be enclosed in single or double quotes.
start is the starting byte within column relative to one.
length is the number of characters after the starting byte to include.
The INTERNAL section is Required, however the keyword INTERNAL is optional. It is a section identifier for the INTERNAL section of the COLUMN definition. INTERNAL refers to the actual data as it is stored in the DBMS.
Use of the keyword when also using an Omnidex section improves the readability of the Environment Catalog source code.
Required. Declares the data type format of the defined column. The DATATYPE keyword is required. See Supported Datatypes for a list of supported datatypes.
Required. Declares the storage length (as n) of the column in bytes.
C String data is terminated by a null character. Therefore, length must be increased by 1 to accommodate the null character. For example, a two character State field defined as a C String will have a length of 3: two for the abbreviated state and one for the null character.
Optional. Sets the magnitude of numeric values. OmniAccess does not format data based on SCALE. SCALE is reported in the Environment file and the System Catalog. SCALE is used by the Omnidex ODBC and JDBC drivers as well as for meta-data purposes.
Optional. Specifies the number of decimal places in numeric values. OmniAccess does not format data based on PRECISION. PRECISION is reported in the Environment file and the System Catalog. PRECISION is used by the Omnidex ODBC and JDBC drivers as well as for meta-data purposes.
Optional. Applies to Omnidex date class data types only. This means this setting will only apply to flat file databases, since a flat file database is the only database that will contain an Omnidex date class data type. Refers to the granularity of the date data stored in the database.
This setting DOES NOT affect the appearance of returned data.
Optional. This is a quoted string of up to 255 characters that maps the logical column name to the native column's name. This refers to native columns. Expressions and functions are not allowed.
USAGE usage_spec USAGE [ASCENDING] [AUTO] ROWID
Optional. Defines a column as the id column. Use AUTO for columns where the id value is automatically assigned by the DBMS. Use ASCENDING only when the id value is 100% guaranteed to be sequentially ascending from 1.
CENTURY THRESHOLD n Optional. Specifies the year cutoff for determining the century in 2 digit years. For example, if the century threshold is set to 60, a 2 digit year of 45 would be translated to 2045 and a 2 digit year of 61 would be translated to 1961. This setting over-rides the century threshold setting in the environment section.
OMNIDEX Optional. The OMNIDEX section is used to index data in a format other than its native format. The keyword OMNIDEX is a section identifier for the OMNIDEX section of the COLUMN statement and is required if an OMNIDEX section is declared. Definitions in this section apply to the Omnidex indexes only.
This section is used ONLY if you want to override the way the indexes are stored by default. Most applications will not have an OMNIDEX section.
DATATYPE datatype_spec Optional. Declares the data type format of the defined column. The DATATYPE keyword is required. See Supported Datatypes for a list of supported datatypes.
LENGTH n Required. Declares the storage length (as n) of the column in bytes.
C String data is terminated by a null character. Therefore, length must be increased by 1 to accommodate the null character. For example, a two character State field defined as a C String will have a length of 3: two for the abbreviated state and one for the null character.
FORMAT format_spec Optional. Applies to date class data types only. Refers to the granularity of the date data stored in the index files. NOTE: This setting DOES NOT affect the appearance of returned data.
AS “select_item” Optional. Generates Pseudo-columns using SQL standard expressions and functions, with limitations. Pseudo-columns allows columns to be included in the environment file that do not exist in the native database.
select_item can be an expression or function. The pseudo-column is read/write.
SQL SELECT “string” Optional. Generates Pseudo-columns using native database functions and expressions. Values are read-only.
Unless the column data can only be generated through native database functions that are not supported by Omnidex, DISC highly recommends that the AS select_item (above) clause be used instead of the SQL SELECT string clause.
SQL SELECT clauses support any of the following tokens that represent the current DATABASE, TABLE or COLUMN.
%d represents the DATABASE wherever it appears in the string. %t represents the TABLE wherever it appears in the string. %c represents the COLUMN wherever it appears in the string. To pass double quotes ( “ ) in a rule to the native database, enclose the string in single quotes ( ' ). The converse is true for single quotes. Mixing double quotes and single quotes within strings is not supported. The following illustrates a few valid examples:
SQL SELECT “to_char(%t.%c)”
SQL SELECT ”%t.%c + %t.AMOUNT“
SQL SELECT ”%t.%c * 1.0725“
PROMPT “string” Optional. This is a string of up to 32 characters used when setting a prompt for a value for the field. Prompt is for meta-data purposes only. It is not used by Omnidex in any way but is included as meta-data with some info calls.
HEADING “string” Optional. This is a string of up to 32 characters used when setting a heading or name for a field appearing above the column being reported. Heading is for meta-data purposes only. It is not used by Omnidex in any way but is included as meta-data with some info calls.
Optional. This is a string of up to 32 characters used when setting a label or name for a field, appearing to the left of the column being reported. Label is for meta-data purposes only. It is not used by Omnidex in any way but is included as meta-data with some info calls.
The cardinality of a column is the number of distinct values in the column. This number should be near the actual column cardinality. Omnidex uses this number to better optimize some queries.
INDEX index_name
TYPE index_type_spec
AS column_spec [, column_spec...]
[PHYSICAL "native_index"]
[[environment_name.]database_name.]index_name
index_name represents a non-ambiguous index or view.
index_name MUST BEGIN WITH A LETTER, A-Z (case insensitive) and may contain up to 32 characters including letters, numbers and allowed special characters.
If index_name contains any reserved words or allowed special characters, it must be enclosed in single or double quotes.
If the underlying database contains native indexes for any of the tables defined in the Omnidex Environment Catalog, you must declare these indexes in this section. If not declared, data retrievals will either be performed by serial read or return incorrect data.
Required when declaring an INDEX index and can declare any one of these values:
INVERTED HASHED SORTED LINKED LIST
Required when declaring an INDEX index. This string, up to 32 characters long, uniquely identifies the column defined within the Environment to which the native index corresponds.
Optional. This is a quoted string of up to 255 characters which references the underlying database's native name for the index.
NODE nodename
DATABASE dbname FILEDSN "dsn"
[ LOCAL ]
[ CACHE ]
CLUSTER table
[PARTITION BY criteria_expression
The following sample Environment Catalog Source is the minimum set of statements that will compile into an Omnidex Compiled Environment.
ENVIRONMENT tinyenv DATABASE tinydb TYPE flatfile TABLE table1 COLUMN myseq DATATYPE INTEGER COLUMN mytext DATATYPE CHAR(60)
This creates an Omnidex Environment named “tinyenv” with a single Database named “tinydb” with one table named “table1”. Because the DATABASE TYPE is “flatfile”, the name of the underlying operating system file for this table is “table1”. Typically a filespec location would be used. Copy and save this environment as “tiny.src”.
os> oacomp tiny.src tiny.env
os> odxsql tiny.env
or
os> odxsql > connect tiny.env
> create file table1
> insert into table1 values (1)
> select * from table1
C:\dev\odx\tiny>more tiny.src
ENVIRONMENT tinyenv
DATABASE tinydb
TYPE flatfile
TABLE table1
COLUMN mysq DATATYPE INTEGER
COLUMN mytext DATATYPE CHAR(60)
C:\dev\odx\tiny>oacomp tiny.src tiny.env
OACOMP - Omnidex Environment Compiler Mon Nov 30 21:41:50 2009
(c) Dynamic Information Systems Corp. 1981-2009 5.0 Build 1H
Microsoft Windows XP Professional SP 3 (2600) TDO830/TDO
** Trial Copy **
Parsed; Validated; Compiled; Done
** Environment Compiled Successfully! **
C:\dev\odx\tiny>odxsql tiny.env
ODXSQL - Omnidex SQL Mon Nov 30 21:41:59 2009
(c) Dynamic Information Systems Corp. 1981-2009 5.0 Build 1H
Microsoft Windows XP Professional SP 3 (2600) TDO830/TDO
** Trial Copy **
Connected to C:\dev\odx\tiny\tiny.env
> create file table1
File table1 created
> insert into values (1, 'lions, tigers, and bears')
1 row inserted into TABLE1
> insert into values (2, 'lions, tigers, kittys, cougars')
1 row inserted into TABLE1
> insert into valu (3, 'The teddy bears are having a picnic')
1 row inserted into TABLE1
> select * from table1
MYSEQ MYTEXT
----------- ------------------------------------------------------------
1 lions, tigers, and bears
2 lions, tigers, kittys, cougars
3 The teddy bears are having a picnic
1 row returned
>
C:\dev\odx\tiny>dbinstal DBINSTAL - Omnidex Database Indexing Utility Tue Dec 01 07:30:37 2009 (c) Dynamic Information Systems Corp. 1981-2009 5.0 Build 1H Microsoft Windows XP Professional SP 3 (2600) TDO830/TDO ** Trial Copy ** Database or Environment: tiny.env Cmd: install ** OMNIDEX MultiDimensional/Keyword (MDK) Index Installation ** Enter Tables and Columns to be indexed To create an index comprised of all or parts of one or more columns, enter an Index Name followed by '!' at the Column prompt Enter '?' for a list of Tables or Columns, '/' to exit the current prompt Table name? table1 To index this table in any parent domains, enter the column linking it to its parent or 'P=' followed by the parent name Link column 1: / Table will be indexed in its own domain Column name: mytext;kw Column name: / Table name? / Specification of MDK indexes is complete ** OMNIDEX Aggregation/Sorted_Key (ASK) Index Installation ** Table name? / Proceed with installation? [Y] Use the Build command to load the following indexes: TABLE1 (Omnidex MDK) Cmd: build Table [;key] table1 Unloading keywords TABLE1: 3 records (15 keywords) processed 15 keywords unloaded 0 (0MB), 1 (1MB), 0 (0MB) Loading keywords 15 keywords loaded 2 blocks used, 2 levels Indexing complete. CPU time: 0:00:00 Elapsed: 0:00:00 Cmd: quit C:\dev\odx\tiny>
After creating an Omnidex Environment and adding Omnidex indexes via DBINSTAL, applications can be developed using the DISC provided ODBC and JDBC drivers.
Create an ODBC or JDBC datasource using the Windows Client DataSource Editor (DSEDIT).
Name the file as “tiny.dsn”.
Or modify the following text file and save as tiny.dsn. Change the following lines to the correct path and filenames.
Change these three lines with:
Cut and paste this text as “tiny.dsn” with the modifications and save in the same directory as the “tiny.env” Omnidex Environment file.
Host=tdo830 ODBCDSNFILE=C:\dev\odx\tiny\tiny.dsn FileSpec=c:\dev\odx\tiny\tiny.env
ODBC] DRIVER=DISC OMNIDEX OdxNet Driver ODBCDSNFILE=C:\dev\odx\tiny\tiny.dsn ODBCDSNNAME=tinydsn [DataSources] tinydsn=DataSource Edit Wizard Generated [DataSource tinydsn] Dictionary=tinydsn DisplayWindow=NONE [Dictionaries] tinydsn=DataSource Edit Wizard Generated [Dictionary tinydsn] Server=Server1 NetworkServices=OdxNet Type=OmniAccess FileSpec=c:\dev\odx\tiny\tiny.env HostOAConnectOptions= Password=!~ AccessOptions=Write [Servers] Server1=DataSource Edit Wizard Generated [Server Server1] Host=tdo830 Port=7555
Start a new command window as odxnet will run and await connections in the new command window.
os> odxnet
Go back to the original command window and do the following:
os> odxsql tiny.dsn -odbc
or run an ODXNET listener from within OdexSQL using the -odxnet command line option.
os> odxsql -odxnet
> connect tiny.dsn using odbc
> select * from table1 where mytext = 'bears' MYSEQ MYTEXT ----------- ------------------------------------------------------------ 1 lions, tigers, and bears 3 The teddy bears are having a picnic
Note that Omnidex will do a quick text (Keyword) retreival automatically
> insert into table1 values (4, 'Da Bears') 1 row inserted into TABLE1
> select * from table1 where mytext = 'bears' MYSEQ MYTEXT ----------- ------------------------------------------------------------ 1 lions, tigers, and bears 3 The teddy bears are having a picnic 4 Da Bears