This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:text:retrieve [2012/01/30 22:57] doc |
admin:indexing:text:retrieve [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 10: | Line 10: | ||
| **[[admin:indexing:text:retrieve|External Files]]** | | **[[admin:indexing:text:retrieve|External Files]]** | | ||
| [[admin:indexing:text:proximity|Proximity Searches]] | | [[admin:indexing:text:proximity|Proximity Searches]] | | ||
| + | [[admin:indexing:text:advanced|Advanced Searches]] | | ||
| [[admin:indexing:text:results|Displaying Results]] | | [[admin:indexing:text:results|Displaying Results]] | | ||
| [[admin:indexing:text:relevancy|Relevancy]] | [[admin:indexing:text:relevancy|Relevancy]] | ||
| Line 17: | Line 18: | ||
| ==== Retrieving External Files ==== | ==== Retrieving External Files ==== | ||
| - | Omnidex allows a table to point to a collection of external text files. One of the columns in the table can contain the filename of an external text file, and other columns can point to starting offsets and lengths within that file. When the table is read, the text from the external file is retrieved and presented as a large text column in the table. In this manner, queries against the table can also include criteria against the contents of the external files. | + | Omnidex allows a table to point to a collection of external text files. One of the columns in the table can contain the filename of an external text file. When the table is read, the text from the external file is retrieved and presented as a large text column in the table. In this manner, queries against the table can also include criteria against the contents of the external files. |
| Omnidex allows this by using an [[admin:features:expressioncols:home|Expression-based Column]]. An expression-based column is a virtual column declared in the table that does not exist in the underlying relational table or raw data file. Instead, the column is fulfilled by a SQL expression each time a row is read. Omnidex provides a [[dev:sql:functions:retrieve_file:home|$RETRIEVE_FILE]] SQL function that can be used in an expression-based column. | Omnidex allows this by using an [[admin:features:expressioncols:home|Expression-based Column]]. An expression-based column is a virtual column declared in the table that does not exist in the underlying relational table or raw data file. Instead, the column is fulfilled by a SQL expression each time a row is read. Omnidex provides a [[dev:sql:functions:retrieve_file:home|$RETRIEVE_FILE]] SQL function that can be used in an expression-based column. | ||
| - | The $RETRIEVE_FILE function references another column in the table that contains the filename. When the function is processed for each row, the file will be open and the contents will be deposited into the expression-based column. The expression-based column can then be indexed and queried as needed. | + | The $RETRIEVE_FILE function references a column in the table that contains the filename. When the function is processed for each row, the respective file is opened and the contents are deposited into the expression-based column. The expression-based column can then be indexed and queried as needed. |
| - | <code sql> | + | === Sample Database === |
| - | create table "LIBRARY" | + | |
| - | physical "dat/library.dat" | + | To demonstrate the $RETRIEVE_FILE function, and to provide examples for this section, we will use the following BOOKS table, which catalogs popular books. |
| + | |||
| + | <code> | ||
| + | create environment | ||
| + | in "books.xml" | ||
| + | with delete; | ||
| + | |||
| + | create database "TEXT" | ||
| + | type file | ||
| + | index_directory "idx" | ||
| + | in "books.xml"; | ||
| + | |||
| + | create table "BOOKS" | ||
| + | options "delimited" | ||
| + | physical "dat/books.txt" | ||
| ( | ( | ||
| - | "DOC_ID" INTEGER omnidex, | + | "DOC_ID" character(8) omnidex, |
| - | "TITLE" CHARACTER(80) fulltext, | + | "CLASS" character(4) omnidex, |
| - | "SUBTITLE" CHARACTER(80) fulltext, | + | "FICTION" character(1) omnidex, |
| - | "AUTHOR_FIRST_NAME" CHARACTER(32) quicktext, | + | "LANGUAGE" character(32) omnidex, |
| - | "AUTHOR_LAST_NAME" CHARACTER(32) quicktext, | + | "TITLE" string(80) fulltext, |
| - | "LANGUAGE" CHARACTER(16) quicktext, | + | "AUTHOR" string(80) fulltext usage "NAME", |
| - | "FILENAME" CHARACTER(256), | + | "SOURCE" string(80) quicktext, |
| - | "CATALOG_DATE" ANSI DATE, | + | "FILENAME" string(255) quicktext, |
| - | "CONTENT" STRING(16MB) fulltext | + | "CONTENT" string(1MB) fulltext usage "TEXT" |
| - | as "$retrieve_file(FILENAME, 'STRING(16MB)')", | + | as "$retrieve_file(FILENAME, 'STRING(1MB)')", |
| + | constraint DOC_ID_PK primary ("DOC_ID"), | ||
| ) | ) | ||
| - | in "library.xml" | + | in "books.xml"; |
| </code> | </code> | ||