This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
oaenv:home [2009/12/18 15:45] admin |
oaenv:home [2012/10/26 14:24] (current) |
||
|---|---|---|---|
| Line 332: | Line 332: | ||
| == COLUMN column_name == | == COLUMN column_name == | ||
| - | [[[environment_name.]database_name.]table_name.]column_name[(start, length)] | + | [[[environment_name.]database_name.]table_name.]column_name[(start, length)] |
| column_name represents a non-ambiguous column or portion of a column. | column_name represents a non-ambiguous column or portion of a column. | ||
| Line 368: | Line 368: | ||
| Use of the keyword when also using an Omnidex section improves the readability of the Environment Catalog source code. | Use of the keyword when also using an Omnidex section improves the readability of the Environment Catalog source code. | ||
| - | == DATATYPE datatype_spec == | + | ==== DATATYPE datatype_spec ==== |
| 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 data type format of the defined column. The DATATYPE keyword is required. See Supported Datatypes for a list of supported datatypes. | ||
| - | == LENGTH n == | + | Binary Datatypes |
| + | |||
| + | For integer and floating point datatypes, new datatypes have been added that imply a particular length, eliminating the need to express the number of bytes. The new datatypes that have been added are: | ||
| + | |||
| + | [SIGNED] TINYINT Binary data stored in one byte, from -128 to 127. This is equivalent to a “datatype [SIGNED] INTEGER length 1”. | ||
| + | |||
| + | UNSIGNED TINYINT Binary data stored in one byte, from 0 to 255. This is equivalent to a “datatype UNSIGNED INTEGER length 1”. | ||
| + | |||
| + | [SIGNED] SMALLINT Binary data stored in 2 bytes, from -32768 to 32767. This is equivalent to a “datatype [SIGNED] INTEGER length 2”. | ||
| + | |||
| + | UNSIGNED SMALLINT Binary data stored in 2 bytes, from 0 to 65535. This is equivalent to a “datatype UNSIGNED INTEGER length 2”. | ||
| + | |||
| + | [SIGNED] BIGINT Binary data stored in 8 bytes, from (2^63 * -1) to (2^63 – 1). This is equivalent to a “datatype [SIGNED] INTEGER length 8”. | ||
| + | |||
| + | UNSIGNED BIGINT Binary data stored in 8 bytes, from 0 to (2^64 – 1). This is equivalent to a “datatype UNSIGNED INTEGER length 8”. | ||
| + | |||
| + | DOUBLE Double-precision floating point data stored in 8 bytes. This is equivalent to a “datatype FLOAT length 8”. | ||
| + | |||
| + | <code> | ||
| + | Figure 2 - Examples of Declaring Datatypes Using New and Existing Syntax | ||
| + | New syntax Existing syntax | ||
| + | datatype CHARACTER(10) datatype CHARACTER length 10 | ||
| + | datatype C STRING(10M) datatype C STRING length 1000001 | ||
| + | datatype TINYINT datatype INTEGER length 1 | ||
| + | datatype SMALLINT datatype INTEGER length 2 | ||
| + | datatype INTEGER datatype INTEGER length 4 | ||
| + | datatype BIGINT datatype INTEGER length 8 | ||
| + | datatype FLOAT datatype FLOAT length 4 | ||
| + | datatype DOUBLE datatype FLOAT length 8 | ||
| + | datatype ORACLE DATETIME datatype ORACLE DATETIME length 7 | ||
| + | datatype OMNIDEX DATE(4) format MMDD datatype OMNIDEX DATETIME length 2 format MMDD | ||
| + | |||
| + | Figure 3 - Declaring Omnidex Datatypes in the Environment File | ||
| + | Description Number Value in Parentheses | ||
| + | For New Syntax Value after LENGTH | ||
| + | For Existing Syntax | ||
| + | CHARACTER 100 characters characters | ||
| + | C STRING 201 characters characters + null terminator | ||
| + | VARCHAR 1700 characters characters | ||
| + | CLOB 1800 characters characters | ||
| + | NATIONAL CHARACTER 102 characters characters * 2 | ||
| + | NATIONAL C STRING 202 characters (characters + null terminator) * 2 | ||
| + | NATIONAL VARCHAR 1701 characters characters * 2 | ||
| + | NATIONAL CLOB 1801 characters characters * 2 | ||
| + | OMNIDEX VARCHAR 1799 characters characters + 4 | ||
| + | OMNIDEX CLOB 1899 characters characters + 4 | ||
| + | [SIGNED] TINYINT 301 1 | ||
| + | UNSIGNED TINYINT 401 1 | ||
| + | [SIGNED] SMALLINT 302 2 | ||
| + | UNSIGNED SMALLINT 402 2 | ||
| + | [SIGNED] INTEGER 300 1, 2, 4 or 8 | ||
| + | UNSIGNED INTEGER 400 1, 2, 4 or 8 | ||
| + | [SIGNED] BIGINT 303 8 | ||
| + | UNSIGNED BIGINT 403 8 | ||
| + | FLOAT 600 4 or 8 | ||
| + | DOUBLE 605 8 | ||
| + | DATE 1000 10 | ||
| + | ODBC DATE 1007 6 | ||
| + | DB2 DATE 1009 6 | ||
| + | INFORMIX DATE 1100 4 | ||
| + | ASCII DATE 1101 6 or 8 (optional, default of 8) 6 or 8 | ||
| + | OMNIDEX DATE 1102 2-8 (optional, default of 8) 1-4 | ||
| + | TIME 1199 11 | ||
| + | ODBC TIME 1200 6 | ||
| + | DB2 TIME 1202 6 | ||
| + | OMNIDEX TIME 1207 2-8 (optional, default of 8) 1-4 | ||
| + | DATETIME 1208 22 | ||
| + | ORACLE DATETIME 1206 7 | ||
| + | ODBC DATETIME 1205 6 | ||
| + | DB2 DATETIME 1208 16 | ||
| + | INFORMIX DATETIME 1206 24 | ||
| + | C DATETIME 1205 4 | ||
| + | OMNIDEX DATETIME 1299 2-16 (optional, default of 16) 1-8 | ||
| + | BLOB 1900 bytes bytes | ||
| + | OMNIDEX BLOB 1999 bytes bytes + 4 | ||
| + | </code> | ||
| + | |||
| + | ==== LENGTH n ==== | ||
| Required. Declares the storage length (as n) of the column in bytes. | Required. Declares the storage length (as n) of the column in bytes. | ||
| Line 457: | Line 534: | ||
| 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. | 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. | ||
| + | |||
| + | ===== $RETRIEVE_FILE ===== | ||
| + | |||
| + | <code> | ||
| + | $RETRIEVE_FILE(filename [,’datatype’ [,length [,’options’]]]) | ||
| + | |||
| + | filename A string literal, a column, or an expression containing the filename to retrieve. | ||
| + | |||
| + | datatype The datatype to be used for retrieving the file’s content. Typically a CLOB or C STRING is used to retrieve ASCII data such as text and HTML, and BLOB is used to retrieve binary data such as Microsoft Word and Adobe PDF documents. Alternatively, a CLOB can be used to retrieve the text from Microsoft Word and Adobe PDF documents if the EXTRACT_TEXT option is used. | ||
| + | |||
| + | Datatypes are specified in textual form, and may be used with or without lengths. If no lengths are specified, then they must be included in the length parameter. If no datatype is specified, then CLOB is presumed. | ||
| + | |||
| + | length The length to be used for retrieving the file’s content. Lengths may also be specified in the datatype parameter using the standard Omnidex syntax. If no length is provided in either place, the length defaults to 64KB. | ||
| + | |||
| + | options The options to be applied to retrieving this file. | ||
| + | |||
| + | EXTRACT_TEXT Extract the text from the file, rather than returning the exact contents of the file. | ||
| + | |||
| + | AUTO_EXTENSION If the passed filename does not exist, and if the passed filename does not contain an extension, and if a single file exists with this name plus an extension, the open that file. This option allows filenames to be included without an extension as long as only one file is possible. | ||
| + | STOPWORDS= Use the STOPWORDS list identified by this option. | ||
| + | |||
| + | PARSE Parse the keywords from the text and discard all whitespace and punctuation. | ||
| + | |||
| + | The $RETRIEVE_FILE function returns a buffer using the datatype and length specified in the parameters. If no parameters are specified, then the default datatype and length are returned. | ||
| + | </code> | ||
| + | |||
| + | |||
| ===== INDEX ===== | ===== INDEX ===== | ||