An environment is a collection of one or more databases that will be connected to as a group using Omnidex. While an environment frequently contains only one database for an application, it is possible to create environments containing multiple databases. These databases can be of different types as well. This allows SQL statements to span multiple databases, joining tables from one database type to another. This can be especially valuable when supplementing a relational database with traditional flatfiles or a secondary database.
name is the logical name of the environment. The logical name uniquely identifies this environment within Omnidex. This logical name is not the same as the physical filename; instead it is a name given to the environment that will be used within Omnidex.
The primary use of the logical name is to qualify the names of databases when connected to multiple environment files containing databases that share the same logical name. This is not a frequent occurrence, so the environment logical name winds up being infrequently used.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
masterEnvironmentHost is used in scenarios where a database is distributed across nodes of a grid. masterEnvironmentHost specifies the name of the host containing the master environment file. This information cannot be updated as it is managed by Omnidex.
masterEnvironmentFilename is used in scenarios where a database is distributed across nodes of a grid. masterEnvironmentFilename specifies the filename of the master environment file. This information cannot be updated as it is managed by Omnidex.
globalSettings is a collection of settings that apply to all connections to the environment, and therefore apply to all databases and all statements. Some settings may be overridden at other layers, such as the database or table layer within the environment, or using options within the statements.
centuryThreshold determines the default century to use when referencing certain date datatypes. Some date formats do not specify the century, such as those in the format YYMMDD. If if it is not specified, it must be known to Omnidex. The centuryThreshold provides a threshold between 00 and 99 controlling whether to presume the 20th century (19nn) or the 21st century (20nn). If the YY portion of the date is greater or equal to the centuryThreshold, Omnidex will presume the 20st century; otherwise; Omnidex will presume the 21th century.
dataCache is a setting that determines how much data to temporarily cache within this Omnidex connection. In certain queries, the Omnidex SQL engine may elect to cache data in memory to speed performance. By default, each Omnidex process is allowed to cache up to 32 megabytes of data. This setting can be adjusted to control the size of this cache.
dataCache can be set to any number between 0 and 2048. Since this is a "per-connection" setting, care should be taken to insure that this setting does not lead to overconsumption of memory.
metadataCache is a setting that determines how much metadata to cache within this Omnidex process. Omnidex caches metadata to optimize performance; however, on large environments, Omnidex will flush unused metadata to preserve memory. By default, each Omnidex process is allowed to cache up to 2 megabytes of metadata.
metadataCache can be set to any number between 0 and 32. Since this is a "per-connection" setting, care should be taken to insure that this setting does not lead to overconsumption of memory. Normally, this setting does not need to be adjusted.
application is a section allowing the Omnidex administrator to record custom metadata for this environment. For more information on the specific metadata allowed, see Application Settings.
documentation is a section allowing the Omnidex administrator to record custom documentation for this environment. For more information on the specific documentation allowed, see Documentation Settings.
databases is a collection of databases declared in this environment. Omnidex allows multiple databases to be declared in the environment file to allow queries to span multiple databases.
number is the internally assigned number for this environment. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
numDatabases is the number of databases declared in this environment. This information cannot be updated as it is managed by Omnidex.
database contains the information describing a database, including table, column, index and grid definitions. Primarily, a database is a collection of one or more tables that can be accessed in queries. Databases may be contained on one server or may be distributed across a grid of servers to improve performance. In the case of relational databases, a database includes instructions for connecting to the database. In the case of a database of flat files, a database simply groups all the tables into one entity.
name is the logical name of the database. The logical name uniquely identifies this database within Omnidex. In the case of relational databases, this logical name may differ from the database name within the RDBMS.
The primary use of the logical name is to qualify tables that share the same name across databases within an environment, or across multiple environments. This is not a frequent occurrence, so the database logical name winds up being infrequently used.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
application is a section allowing the Omnidex administrator to record custom metadata for this database. For more information on the specific metadata allowed, see Application Settings.
documentation is a section allowing the Omnidex administrator to record custom documentation for this database. For more information on the specific documentation allowed, see Documentation Settings.
nodes is a collection of node declarations for this database. All databases have at least one node, and the node describes the connection information for the database and Omnidex indexes. Databases that are partitioned across a grid of servers contain multiple nodes, each with their own connection information.
tables is a collection of table declarations for this database. Each table consists of columns, constraints and indexes. In the case of a flatfile database, a table may be comprised of one file or multiple files that are combined into one source of data.
Partitioning of data can improve performance by allowing queries to be directed to only the partitions that are relevant to the query. There are two approaches available in Omnidex to partition data. The simplest approach allows a single table per database to be partitioned within the database itself. This is useful when there is one large table that dominates the database. For more complex database environments, the database can be distributed across a grid of servers.
number is the internally assigned number for this database. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
numNodes is the number of node declarations for this database. This number cannot be updated as it is managed by Omnidex.
node contains the information specific to this node of the database. All databases have at least one node, and the node describes the connection information for the database and Omnidex indexes. Databases that are partitioned across a grid of servers contain multiple nodes, each with their own connection information.
name is the logical name that uniquely identifies the node. This name does not need to be correlated with anything else, such as a host name or the database name. It can be set to something meaningful to the administrator.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
server is the name of the server hosting this node. If the node is on the local server, then set the server name to "localhost" or the server name itself.
server is a character string containing up to 32 bytes.
indexPrefix establishes the naming convention used for naming the Omnidex index files. Omnidex will use this prefix, followed by suffixes such as a four-digit number or a word like STATS. This prefix may also include path information, and the path may either be relative or absolute. The prefix may also include the use of environment variables. Examples of index prefixes are show in the table below:
| Example | Description |
| star | Omnidex index files will be placed in the current working directory and will begin with "star". The current working directory is the same directory as the environment file. |
| idx/star | Omnidex index files will be placed in the subdirectory "idx" off the current working directory and will begin with "star". The current working directory is the same directory as the environment file. |
| /applications/star/idx/star | Omnidex index files will be placed in the path "/applications/star/idx" and will begin with "star". This approach is appropriate for specifying fully-qualified paths on UNIX. |
| $STAR_HOME/idx/star | Omnidex index files will be placed in the subdirectory "idx" off the directory determined by the environment variable $STAR_HOME and will begin with "star". This environment variable syntax is appropriate for UNIX. |
| d:\applications\star\idx\star | Omnidex index files will be placed in the path "d:\applications\star\idx" and will begin with "star". This approach is appropriate for specifying fully-qualified paths on Microsoft Windows. |
| %STAR_HOME%\idx\star | Omnidex index files will be placed in the subdirectory "idx" off the directory determined by the environment variable %STAR_HOME% and will begin with "star". This environment variable syntax is appropriate for Microsoft Windows. |
indexPrefix is a character string containing up to 250 bytes.
indexMaintenance is a setting that determines how updates to the indexes are handled. Some applications will use Omnidex to update both the database and the indexes using ODBC, JDBC or Omnidex's API. Other applications will update the database and use triggers to update the Omnidex indexes. This setting establishes the default for the entire database, though it can be set individually for each table as well. Use one of the following settings accordingly:
| Setting | Description |
| API | The Omnidex indexes should be updated when the database is updated using ODBC, JDBC or Omnidex's API. This is the default setting. |
| DBMS | The Omnidex indexes should be not be updated when the database is updated using ODBC, JDBC or Omnidex's API. Instead the indexes will be updated using triggers in conjunction with the OdxAIM utility. |
dbmsPhysical is the physical connection information for the database. This information varies for each database as shown below:
| Database | Setting |
| FLATFILE | No connection information is required. |
| ORACLE | The service name if using SQL*Net; otherwise blank. |
| SQLSERVER | For system datasources, use "DSN=name". For file datasources, use "FILEDSN="filename". |
| ODBC | For system datasources, use "DSN=name". For file datasources, use "FILEDSN="filename". |
dbmsPhysical is a character string containing up to 255 bytes.
dbmsUser is the username that should be used when connecting to the database. This setting is only necessary when connecting to a relational database.
dbmsUser is a character string containing up to 32 bytes.
dbmsPassword is the password that should be used when connecting to the database. This setting is only necessary when connecting to a relational database.
dbmsPassword is a character string containing up to 32 bytes.
partitionBy allows criteria to be specified that restricts the data found on this node. Omnidex will consider this criteria when optimizing queries and only process nodes relevant to the query. partitionBy is a character string containing up to 255 bytes.
statistics is a collection of statistics that are tracked about the node. These statistics are updated by issuing the UPDATE STATISTICS statement or by using Omnidex Administrator's maintenance tools. These settings cannot be updated as they are managed by Omnidex.
dateIndexesInstalled is the date that Omnidex indexes were last installed.
application is a section allowing the Omnidex administrator to record custom metadata for this connection. For more information on the specific metadata allowed, see Application Settings.
documentation is a section allowing the Omnidex administrator to record custom documentation for this environment. For more information on the specific documentation allowed, see Documentation Settings.
number is the internally assigned number for this node. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
type describes whether the node is local or remote. Nodes that are local will be optimized to avoid using network services to transfer data. The choices for the connection type are as follows:
| Setting | Description |
| LOCAL | The node is on the same server as the environment file. |
| REMOTE | The node is on a different server than the environment file. |
dbmsType is the type of database. The supported types of databases are:
| Setting | Description |
| FLATFILE | A collection of flat files. Files may be delimited using tabs, commas or other characters, or may be of fixed length and layout. |
| ORACLE | An Oracle relational database provided by Oracle Corporation. |
| SQLSERVER | A SQL Server relational database provided by Microsoft Corporation. |
| ODBC | Any database accessed through a standard ODBC Driver. |
dbmsSubtype is the subtype of the database when the database type is ODBC. If the underlying database matches one of these subtypes, then Omnidex will use ODBC extensions appropriate to that subtype. If the subtype is unspecified, Omnidex will use standard ODBC calls. The supported subtypes of databases are:
| Setting | Description |
| ORACLE | An Oracle relational database provided by Oracle Corporation. |
| SQLSERVER | A SQL Server relational database provided by Microsoft Corporation. |
| MYSQL | A MySQL relational database provided by Sun Corporation. |
| ACCESS | A Microsoft Access relational database provided by Microsoft Corporation. |
dbmsVersion is the version of database. If the database type appears in the table below, then use the corresponding version; otherwise, leave the version unspecified.
| Database | Version | Description |
| Oracle | 8 or SQLNET8 | Any of the version 8 series of Oracle. Put SQLNET in front of the version if using SQL*Net. |
| 9 or SQLNET9 | Any of the version 9 series of Oracle. Put SQLNET in front of the version if using SQL*Net. | |
| 10 or SQLNET10 | Any of the version 10 series of Oracle. Put SQLNET in front of the version if using SQL*Net. | |
| 11 or SQLNET11 | Any of the version 11 series of Oracle. Put SQLNET in front of the version if using SQL*Net. | |
| SQL Server | 8 | Any of the version 8 series of SQL Server. |
| 9 | Any of the version 9 series of SQL Server. |
numTables is the number of table declarations for this database. This number cannot be updated as it is managed by Omnidex.
table contains the information describing a table in the database, including column layout, constraints and indexing.
name is the logical name that uniquely identifies the table. This name does not need to be correlated with anything else, such as a table name in the underlying database. It can be set to something meaningful to the administrator.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
physical contains the information needed to connect to the table in the underlying database. For relational databases, physical is the name of the table or view in the database itself. If physical is not specified, then it is presumed to be the same as the name. For flatfile databases, physical is the name of the file, or the group of files, comprising the table. In this situation, physical may contain multiple files separated by commas or spaces, and wildcard filesets. Files may be contain path information as well. Files may also reference environment variables as needed. The following are examples of physicals for tables:
| Database | Example | Description |
| Relational | star.customers | Relational databases often use a convention of "owner.table". Though the owner is not required, it may be used if needed. The content of physical will be directly used in the SQL statements sent to the underlying database when referencing this table. |
| Flatfile | star.dat | For flatfile databases, the direct filename can be used. |
| star1.dat, star2.dat | In flatfile databases, multiple filenames can be used. The files will be accessed in the order they are specified and will be treated as one large logical file. | |
| star*.dat | In flatfile databases, wildcards can be used. The files will be accessed in the order they are specified and will be treated as one large logical file. | |
| /applications/star/dat/star*.dat | In flatfile databases, filenames can include path information. This approach is appropriate for specifying fully-qualified paths on UNIX. | |
| $STAR_HOME/dat/star*.dat | In flatfile databases, filenames can include environment variables. This approach is appropriate for specifying fully-qualified paths on UNIX. | |
| d:/applications/star/dat/star*.dat | In flatfile databases, filenames can include path information. This approach is appropriate for specifying fully-qualified paths on Microsoft Windows. | |
| %STAR_HOME%/dat/star*.dat | In flatfile databases, filenames can include environment variables. This approach is appropriate for specifying fully-qualified paths on Microsoft Windows. |
physical is a character string of unlimited size, allowing for very large sequences of filenames.
options is a string of options that apply to this table. Options are only required in certain scenarios as described below: TODO - FIXED/TDF/OST
| Scenario | Option | Description | ||||||||||||||||||||
| Delimited Files | COLUMN='delimiter' | When a table is of type TDF, meaning that it is a tab-delimited
file, Omnidex will expect columns to be delimited by a tab character.
Omnidex supports other combinations of delimiters by specifying them
in the options string. The delimiter may be a single
character or it may be a sequence to any two characters. To specify
unprintable characters, use one of the following tokens:
|
||||||||||||||||||||
| RECORD='delimiter' | When a table is of type TDF, meaning that it is a tab-delimited
file, Omnidex will expect records to be delimited by a linefeed
character on UNIX, or a combination of a carriage return and
linefeed character on Microsoft Windows. Omnidex supports other
combinations of delimiters by specifying them n the options
string. The delimiter may be a single character or it may be a
sequence to any two characters. To specify unprintable characters,
use one of the following tokens:
|
|||||||||||||||||||||
| QUOTES | Delimited files may or may not quote character data. This is often
done when the delimiter may appear in the data itself. For instance,
when a comma is used as a separator, it is not uncommon to also have
commas in the data itself. Quoting the data allows applications
to recognize that the comma is not a delimiter. Not all applications
use the same approach to quoting, and this option allows several
different approaches as described below:
|
|||||||||||||||||||||
| ESCAPE=c | Normally, a backslash can be used to escape a character, meaning to specify that this character is part of the data itself rather than part of the structure of the file. The escape character can be overridden using this option. | |||||||||||||||||||||
| HEADER_ROW | Delimited files may use the first row of the file to provide column names. This option indicates that the first row is a header row so that it is not interpreted as data. |
options is a character string of up to 255 characters.
indexMaintenance is a setting that determines how updates to the indexes are handled. Some applications will use Omnidex to update both the database and the indexes using ODBC, JDBC or Omnidex's API. Other applications will update the database and use triggers to update the Omnidex indexes. This setting can be set globally for a database; however, it is can be set individually for each table as well. Use one of the following settings accordingly:
| Setting | Description |
| API | The Omnidex indexes should be updated when the database is updated using ODBC, JDBC or Omnidex's API. This is the default setting. |
| DBMS | The Omnidex indexes should be not be updated when the database is updated using ODBC, JDBC or Omnidex's API. Instead the indexes will be updated using triggers in conjunction with the OdxAIM utility. |
dataCaching is not used at this time.
autoFilter is a string of criteria using SQL syntax that will be applied every time this table is queried.
as is a SQL statement that unions partitioned tables together to form a dynamic view. This setting is only applicable when the table type is DYNAMIC VIEW. This setting cannot be updated as it is managed by Omnidex.
statistics is a collection of statistics that are tracked about the table. These statistics are updated by issuing the UPDATE STATISTICS statement or by using Omnidex Administrator's maintenance tools. These settings cannot be updated as they are managed by Omnidex.
cardinality is the cardinality of the table, meaning the number of rows in the table.
dateModified is the date that the table was last modified.
application is a section allowing the Omnidex administrator to record custom metadata for this table. For more information on the specific metadata allowed, see Application Settings.
documentation is a section allowing the Omnidex administrator to record custom documentation for this table. For more information on the specific documentation allowed, see Documentation Settings.
prejoinedTables TODO.
numPrejoinedTables TODO.
prejoinedTable TODO.
name TODO.
userRequested TODO.
indexes is a collection of indexes created on this table. This collection includes native indexes from the relational database as well as Omnidex indexes.
constraints is a collection of constraints that are declared on this table. Constraints include primary keys, foreign keys and unique keys.
partitions is a collection of partitions for this table. Partitioning a table can improve performance by breaking the table into multiple parts, allowing queries to be directed only to the relevant parts.
columns is a collection of columns that exist in this table.
nativeRecordLength is the length of the record as it is received from the underlying database. This can be different than the length of the record as returned by Omnidex due to features such as pseudocolumns.
number is the internally assigned number for this table. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
omnidexTableType describes how Omnidex indexes were installed on this table.
| Type | Description |
| NONE | Omnidex indexing is not installed on this table. |
| IDSI | Omnidex uses the primary directly as the index pointer. This is only possible when the primary key is a 4-byte ascending number. |
| TR | Omnidex logically represents the primary key as the index pointer through the use of a cross-reference file. |
| TRR | Omnidex logically represents the primary key and the unique key as the index pointer through the use of a cross-reference file. |
| DR | Omnidex logically represents the unique key as the index pointer through the use of a cross-reference file. |
| LINKED CHILD | Omnidex logically represents the primary key and the foreign key as the index pointer to form a pre-joined index between a parent table and a child table. |
| HYBRID IDSI | Omnidex logically represents the primary key and the foreign key as the index pointer to form a pre-joined index between a parent table and a child table. |
| HYBRID TR | Omnidex views this table as a hybrid of TR and LINKED CHILD |
| HYBRID TRR | Omnidex views this table as a hybrid of TRR and LINKED CHILD |
| HYBRID DR | Omnidex views this table as a hybrid of DR and LINKED CHILD |
| ASK | Omnidex only uses ASK indexes on this table. |
recordLength is the length of the record as it is returned by Omnidex. This includes the length of columns that do not exist in the database, such as pseudocolumns. This information cannot be updated as it is managed by Omnidex.
temporary is a flag that describes whether this table is a temporary table or a table declared in the environment. Temporary tables may be created as part of Omnidex processing. This information cannot be updated as it is managed by Omnidex.
type is the type of table. The available types are shown below:
| Setting | Description |
| TABLE | Indicates that this is a normal table. |
| PARTITIONED TABLE | Indicates that this is a view table that represents a collection of partitioned tables. See partitions for more information on table partitioning. |
| ROLLUP TABLE | Indicates that this is a rollup table. Rollup tables are pre-aggregated tables that improve the performance of count and aggregation options. See [to be written] for more information on rollup tables. |
numConstraints is the number of constraints that are declared for this table. This number cannot be updated as it is managed by Omnidex.
constraint contains the information that describes a constraint for this table. Constraints include primary keys, foreign keys and unique keys.
name is the logical name that uniquely identifies the constraint. This name does not need to be correlated with anything else, such as a constraint name in the underlying database. It can be set to something meaningful to the administrator.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
columns is a collection of information describing the columns in this table.
numConstraintColumns is the number of columns that exist in this constraint. This number cannot be updated as it is managed by Omnidex.
constraintColumn is contains the information that describes a column within this constraint.
name is the logical name for the column within this constraint. This column must be a column within this table.
name is a character string containing up to 32 characters, and is case-insensitive.
number is the internally assigned number for this column within this constraint. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
referenceConstraintName is the logical name of the reference constraint for this constraint. Reference constraints occur with foreign keys, allowing the foreign key to point to a primary key in another table.
referenceTableName is the logical name of the table containing the reference constraint. This table must be a table in the same database.
number is the internally assigned number for this reference constraint. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
type is the type of constraint. The different types of constraints are described below:
| Type | Description |
| PRIMARY | A primary key constraint states that the columns within the constraint may only occur once within the table. Primary constraints may also act as reference constraints for a foreign constraint. Primary key constraints determine that this table can be a parent table. |
| FOREIGN | A foreign key constraint states that the columns within the constraint must match the primary key of a row in a parent table. Foreign key constraints determine that this is a child table. Foreign key constraints also require that the child rows be deleted before the parent row is deleted. Omnidex relies on the underlying database to enforce constraints and imposes no enforcement of its own. |
| UNIQUE | A unique key states that the columns within the constraint may only occurs onces in the table. This is similar to a primary key except that unique keys do not have a relationship with foreign keys. Unique keys are used by Omnidex as the fastest method of accessing individual rows in the database. On some databases, the unique key will be the tables rowid. |
numPartitions is the number of partitions that exist for this table. This number cannot be updated as it is managed by Omnidex.
partition contains all the information for an individual partition for this table.
name is the logical name that uniquely identifies the partition. This name does not need to be correlated with anything else, such as a table name in the underlying database. It can be set to something meaningful to the administrator.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
physical contains the information needed to connect to the partition in the underlying database. The rules for partition physicals are the same as those for table physicals.
partitionBy allows criteria to be specified that restricts the data found on this partition. Omnidex will consider this criteria when optimizing queries and only process nodes relevant to the query.
partitionBy is a character string containing up to 255 bytes.
partition.application is a section allowing the Omnidex administrator to record custom metadata for this partition. For more information on the specific metadata allowed, see Application Settings.
partition.documentation is a section allowing the Omnidex administrator to record custom documentation for this partition. For more information on the specific documentation allowed, see Documentation Settings.
statistics is a collection of statistics that are tracked about the partition. These statistics are updated by issuing the UPDATE STATISTICS statement or by using Omnidex Administrator's maintenance tools. These settings cannot be updated as they are managed by Omnidex.
cardinality is the cardinality of the partition, meaning the number of rows in the partition.
dateModified is the date that the partition was last modified.
number is the internally assigned number for this partition. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
numColumns is the number of columns that exist for this table. This number cannot be updated as it is managed by Omnidex.
column contains all the information for an individual column in this table.
name is the logical name that uniquely identifies the column within the table. This name does not need to be correlated with anything else, such a column name in the underlying database. It can be set to something meaningful to the administrator.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
physical is the physical name to use in SQL statements to the underlying relational database. This can be a qualified column name but it may not include SQL expressions or functions. Use pseudocolumns to have columns with SQL expressions or functions. physical has no meaning within flatfiles.
datatype is the datatype of the column. Generally this datatype matches the datatype of the underlying database, but for relational database, it is sometimes possible to use a different datatype. This depends on whether the underlying database will automatically perform the conversion. The following datatypes are allowed:
| Datatype | Description |
| UNKNOWN | TODO DESCRIPTION |
| CHARACTER | A standard character datatype that contains up to 4095 characters. This datatype is not null-terminated, such as a C STRING or a VARCHAR datatype. Instead the value is space padded to the end of the field. |
| C STRING | A null-terminated string datatype character datatype that in most situations contains up to 65,535 bytes. The last declared column in a table can be declared as a C STRING with a size up to 16 MB. |
| TINYINT | A standard 8-bit signed integer datatype that may contain a whole number between -128 and 128. |
| SMALLINT | A standard 16-bit signed integer datatype that may contain a whole number between -32,768 and 32,767. |
| INTEGER | A standard 32-bit signed integer datatype that may contain a whole number between -2,147,483,648 and 2,147,483,647. |
| BIGINT | A standard 64-bit signed integer datatype that may contain a whole number between -(2 ^63) and (2^63 - 1). |
| UNSIGNED TINYINT | A standard 8-bit unsigned integer datatype that may contain a whole number between 0 and 255. |
| UNSIGNED SMALLINT | A standard 16-bit unsigned integer datatype that may contain a whole number between 0 and 65,535. |
| UNSIGNED INTEGER | A standard 32-bit unsigned integer datatype that may contain a whole number between 0 and 4,294,967,295. |
| UNSIGNED BIGINT | A standard 64-bit unsigned integer datatype that may contain a whole number between 0 and (2^64 - 1). |
| FLOAT | A single-precision floating point datatype. |
| DOUBLE | A double-precision floating point datatype. |
| ANSI DATE | An ANSI-compliant date datatype. |
| ODBC DATE | An ODBC date datatype. |
| ASCII DATE | An ASCII date datatype. If the length is 6 bytes, then the format must be YYMMDD. If the length is 8 bytes, then the format is YYYYMMDD. |
| OMNIDEX DATE | An Omnidex proprietary date datatype. |
| ANSI TIME | An ANSI-compliant time datatype. |
| ODBC TIME | An ODBC time datatype. |
| OMNIDEX TIME | An Omnidex proprietary time datatype. |
| ANSI DATETIME | An ANSI-compliant datetime timestamp datatype. |
| ORACLE DATETIME | An Oracle datetime timestamp datatype. |
| C DATETIME | A C-language compliant datetime timestamp usable in time() functions. |
| ODBC DATETIME | An ODBC datetime timestamp datatype. |
| OMNIDEX DATETIME | An Omnidex proprietary datetime timestamp datatype |
| VARCHAR | A variable-length character string up to 4095 bytes that uses a length variable to determine size rather than using a terminator. |
| CLOB | A variable-length character string up to 65,535 bytes that uses a length variable to determine size rather than using a terminator. The last declared column in a table can be declared as a CLOB with a size up to 16 MB. |
| BLOB | TODO DESCRIPTION |
| OMNIDEX VARCHAR | TODO DESCRIPTION |
| OMNIDEX CLOB | TODO DESCRIPTION |
| OMNIDEX BLOB | TODO DESCRIPTION |
| NATIONAL CHARACTER | A 16-bit character datatype that contains up to 4095 characters.
This datatype is often called Unicode or Wide Characters and is
appropriate for international languages. Omnidex has very limited support for this datatype at this time. Omnidex can query and retrieve from these datatypes; however, Omnidex cannot fully index these datatypes. Omnidex will only index the lower 256 bits of these datatypes. This means that Omnidex is not suited to index columns that use the full implementation of these datatypes. |
| NATIONAL C STRING | TODO DESCRIPTION |
| NATIONAL VARCHAR | A 16-bit, variable-length character datatype up to 4095 bytes that
uses a length variable to determine size rather than using a
terminator. This datatype is often called Unicode or Wide
Characters and is appropriate for international languages. Omnidex has very limited support for this datatype at this time. Omnidex can query and retrieve from these datatypes; however, Omnidex cannot fully index these datatypes. Omnidex will only index the lower 256 bits of these datatypes. This means that Omnidex is not suited to index columns that use the full implementation of these datatypes. |
| NATIONAL CLOB | A 16-bit, variable-length character datatype up to 65,535 bytes that
uses a length variable to determine size rather than using a
terminator. The last declared column in a table can be declared
as a NATIONAL CLOB with a size up to 16 MB. This datatype is often
called Unicode or Wide Characters and is appropriate for
international languages. Omnidex has very limited support for this datatype at this time. Omnidex can query and retrieve from these datatypes; however, Omnidex cannot fully index these datatypes. Omnidex will only index the lower 256 bits of these datatypes. This means that Omnidex is not suited to index columns that use the full implementation of these datatypes. |
length is the length of the column, measured in bytes. See datatypes for more information on the lengths allowed for each datatype.
usage describe how the datatype is used, as opposed to the datatype in the database. For instance latitude and longtitude may be stored in a character format, but their usage is LATITUDE and LONGITUDE respectively. At present, LATITUDE and LONGITUDE are the only usages supported. TODO AUTOID, ADDRESS, CITY, STATE, COUNTY, COUNTRY, ZIPCODE
format is used to change the granularity of indexing for date datatypes. Any contiguous combination of the following may be used: YYYYMMDDHHNNSSFF. For example, YYYYMMDD may be used, as well as YYMMDD and DDHH.
pseudoColumn allows a column to be in Omnidex that does not exist in the underlying database. This column may be a simple SQL expression valid as a select item in an Omnidex SQL statement.
default is the default value for the column if it is not specified on an insert.
statistics is a collection of statistics that are tracked about the column. These statistics are updated by issuing the UPDATE STATISTICS statement or by using Omnidex Administrator's maintenance tools. These settings cannot be updated as they are managed by Omnidex.
cardinality is the cardinality of the column, meaning the number of unique values in the column within the table.
column.application is a section allowing the Omnidex administrator to record custom metadata for this environment. For more information on the specific metadata allowed, see Application Settings.
column.documentation is a section allowing the Omnidex administrator to record custom documentation for this environment. For more information on the specific documentation allowed, see Documentation Settings.
number is the internally assigned number for this column. This number may vary between processes and cannot be relied upon as static. This information cannot be updated as it is managed by Omnidex.
type is TODO.
notNull indicates that the column is required on an insert, unless a default value has been specified. See default.
numIndexes is the number of indexes that exist for this table. This number cannot be updated as it is managed by Omnidex.
index contains all the information for an individual index on this table.
name is the logical name that uniquely identifies the index. This name does not need to be correlated with anything else, such as an index name within the database. It can be set to something meaningful to the administrator.
name is a character string containing up to 32 characters, and is case-insensitive. If name contains any reserved words or allowed special characters, it will have to be enclosed in single or double quotes. The special characters allowed are: -!@#$%^_.
physical is the name of the index in the underlying database. This is only relevant for indexes on relational databases.
omnidexSettings contains all of the Omnidex settings for the index. This is only relevant for Omnidex indexes.
ownerTableName is the name of the table that is pre-joined to this index. Pre-joined indexes improve the performance of joins between tables. Pre-joined indexes require more overhead to maintain; however they can provide significant performance gains in joined scenarios.
indexSize is size of this index, measured in megabytes. This information is only available for aggregation indexes.
indexFilename is filename containing this index. This information is only available for aggregation indexes.
options are the options used for this index.
| Option | Description |
| KEYWORDING | Keyword Indexing - This option indexes each keyword in the column so that they can be searched independently. Keyword Indexing is Case Insensitive by default. |
| EXCLUDED_WORDS | Excluded Words - This option avoids indexing excluded words named in the excluded words list. By default, all keywords are indexed; however, this option will automatically avoid indexing spaces and zeros. Furthermore, if an excluded words list is specified during indexing, then these words will be avoided as well. |
| PHONETIC | Phonetic - This option indexes keywords twice, once in their original form and once with their Soundex equivalent. This option greatly improves the performance of phonetic searches. |
| PROXIMITY | Proximity Indexing - This option indexes keywords along with their position in the text. Keyword Indexing indexes keywords, but without their position in the text. Proximity Indexing allows phrase searches, use of the NEAR and BEFORE operators, relevancy scoring and context displays. This option requires more indexing overhead, but greatly expands the capabilities of text searches. |
| RECORD_SPECIFIC | Record Complex Indexing - TODO. |
| RECORD_COMPLEX | Record Complex Indexing - This option causes an index in a child table to be indexed as though the column was in the parent table. This option is allowed when using prejoined indexes and can improve the performance of cross-table queries. Because this option only indexes a child row with the parent's primary key, this option should not be combined with other child indexes in the same query. |
| BITMAP | Bitmap Indexing - This option uses bitmap technology to store and retrieve from the index. This provides optimal performance for columns with 32 or fewer distinct values. Bitmap indexes are only updated during batch reindexes. |
| STANDALONE | Standalone Indexing - This option uses causes the index to be placed in a separate file. This is necessary for non-text columns greater than 32 characters long. |
| AGGREGATION | Aggregations - This option causes the index to be an aggregation index, supporting grouped and ungrouped aggregations, as well as ordering of data retrieval. Aggregation indexes usually consist of multiple columns, beginning with Group By or Order By columns, and then optionally containing the aggregated columns. By default, an Aggregation index is case sensitive. |
groupNumber is the internally assigned number for this group. An index group causes the data from multiple columns to be indexed as though it came from one column. This information cannot be updated as it is managed by Omnidex.
groupMasterTableName is the name of the table containing the master index within this group. An index group causes the data from multiple columns to be indexed as though it came from one column. Members of an index group will share the same groupMasterTableName.
groupMasterIndexName is the name of the master index within this group. An index group causes the data from multiple columns to be indexed as though it came from one column. Members of an index group will share the same groupMasterTableName.
caseInsensitive is a flag that describes whether this index is case-insensitive.
statistics is a collection of statistics that are tracked about the index. These statistics are updated by issuing the UPDATE STATISTICS statement or by using Omnidex Administrator's maintenance tools. These settings cannot be updated as they are managed by Omnidex.
cardinality is the cardinality of the index, meaning the number of unique values in the index.
totalEntries is the total number of values within the index. This number can be much higher or lower than the number of rows in the table. The Excluded Words option may cause spaces, zeroes or other words to be excluded from the index, reducing the number of entries. Conversely, textual indexing may cause multiple keywords per row, increasing the number of entries.
numSearches is the number of times this index has been searched since the last time an UPDATE STATISTICS command was issued. This statistic can be valuable when determining whether statistics are worth retaining.
dateBuilt is the date that an index was last built.
state is the state of the index. This information cannot be updated as it is managed by Omnidex. The following states are supported:
| Index State | Description |
| AWAITING_BUILD | This index needs to be built. |
| BUILT | This index does not need to be built. |
| UNKNOWN | The state of this index is not known. |
index.application is a section allowing the Omnidex administrator to record custom metadata for this index. For more information on the specific metadata allowed, see Application Settings.
index.documentation is a section allowing the Omnidex administrator to record custom documentation for this index. For more information on the specific documentation allowed, see Documentation Settings.
indexComponents is a collection of index components for this index. An index component consists of a table and column, optionally with an offset and length.
indexComponents contains all of the components of the index. An index component consists of a table and column, optionally with an offset and length.
dataclass is the datatype class of the index. This information cannot be updated as it is managed by Omnidex.