You can construct a SQL query to retrieve content and metadata from columns in a database. Each row of data represents a document. Each document requires a unique identifier (a document reference) and content (body text). Optionally, each document can have a title and other metadata.
Each database document store can have only one SQL query. A single SQL query can import one or all of your database documents into a document store, provided that the documents are all in a single database and that no authentication or authorization constraints requires multiple queries. If you must specify more than one user name and password or more than one host, more than one SQL query is required, and you must create a database document store for each SQL query. Documents in separate databases require their own database document stores.
When constructing an import SQL statement, the following column names (or column aliases) have specific meaning:
DOC_REF – a unique token by which the document can be referred to for updates and deletions. A primary key column is most suitable for this.
Sybase Search supports these SQL types:
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT
DOUBLE
CHAR
VARCHAR – you can define the maximum length of VARCHAR.
DOC_CONTENT – the text used as the body of the document. The text can be the content from TEXT or VARCHAR fields.
DOC_CONTENT_TYPE – the content type (or MIME type) of the document. When content is contained in the database in a binary format, DOC_CONTENT_TYPE provides the additional information required to decode it. For example, if the document content is UTF-8 encoded, plain text, then the content type is “text/plain; charset=UTF-8”. Similarly, if the content field contains PDF bytes, the content type is “application/pdf”. When the document content is binary and no content type is specified, Sybase Search attempts to decode it as plain text using the JRE default character set. DOC_CONTENT_TYPE is not always required, as Sybase Search can automatically detect most common content types. The parameter is not mandatory, and is not required for all TEXT and VARCHAR fields.
DOC_LINK – a link to an external document on a file system. The link must be an absolute path, visible to Sybase Search. The document properties (where present) are extracted as metadata and Sybase Search uses the text as the document’s body text.
Sybase Search treats all other column names and aliases as metadata and saves the information with the document as its metadata. If the metadata is to be indexed, its name and type must be known by the metadata manager. You are not required to supply metadata; however, as a best practice, supply a document TITLE, which is shown on the document search results page.
This SQL query shows how a recruitment agency might import the resumes of current candidates:
SELECT ID AS DOC_REF, /*INT*/ PROFILE AS DOC_CONTENT, /*VARCHAR*/ CV AS DOC_CONTENT_2 /*BLOB*/ CV_MIME AS DOC_CONTENT_TYPE_2, FIRST_NAME + ' ' + LAST_NAME AS TITLE, PREF_SALARY /*FLOAT*/ FROM CANDIDATES WHERE LIVE=1
The primary key column ID is used as a document reference, and the document content (body text) is composed from both VARCHAR text and document bytes in a BLOB column. The MIME type of each document is stored in the database. A title is constructed from the first and last name of the candidate, and the preferred salary is saved as metadata.
The next example shows how the content-type detector allows the database import SQL statement to index all the binary data, and makes all these common document formats accessible for searching. For example, if you have MS-Word documents in your table, use:
< … MY_COL AS DOC_CONTENT, ‘application/msword’ AS DOC_CONTENT_TYPE … >
This example demonstrates how the DOC_CONTENT_TYPE column can be used to define explicitly the content type for a DOC_CONTENT column.
