To increase efficiency of processing SQL queries against the file system, emissary RT: Files makes use of an in-memory caching system. This cache (if enabled in the Data Source options) monitors the file system and is updated in real-time should files be changed. This cache is initially built when the ODBC connection is established, and is maintained for the lifetime of the connection. If real-time updates are disabled, emissary RT: Files will rescan and update its cache (if necessary) when it executes a SQL query. Due to being more resource intensive, image and audio information is only cached/updated if the SQL query involves those tables, respectively.

When making use of emissary RT: Files in custom applications, as the cache is built at connection time and is maintained for the lifetime of the connection, it is important to reuse the ODBC connection when possible. This can be more challenging in a web application environment, and may require changes to both the web server configuration and API used. An example includes PHP's odbc_pconnect function and a compatible Apache configuration (non-CGI mode), which creates a persistent connection across each request (for the session lifetime).

When executing queries containing related tables (either via JOIN or appropriate WHERE clauses), emissary RT: Files is optimized for predicates comparing the equality of Image.ID and/or Audio.ID against Standard.ID. E.g. "SELECT * FROM Standard INNER JOIN Audio ON Standard.ID = Audio.ID", "SELECT * FROM Image, Standard WHERE Image.ID = Standard.ID", "SELECT * FROM Standard, Image, Audio WHERE Standard.ID = Image.ID AND Standard.ID = Audio.ID", etc. Comparisons of Image.ID directly to Audio.ID are not optimized. Predicates may contain additional expressions, as long as OR operators do not allow for potential additional matches in the join. Any non-optimized predicate with valid syntax may be used, but performance will degrade significantly, as the system must internally perform a full cross join.

Additionally, all tables are indexed against their ID column, and will perform significantly faster with WHERE clauses that select for specific IDs, via inline values and/or parameters. As with related table optimization above, WHERE clauses optimized for ID indices may contain additional expressions, as long as OR operators do not allow for potenital additional matches.

The execution time involved for a SQL query is dependent on the size of the file system (especially if recursion is enabled), the speed of the underlying hardware in querying the file system, if image or audio data is being queried, etc. Because it may be desired to execute a query that may take a significant time to process, emissary RT: Files provides an interactive mode that shows both a progress meter, and allows the cancelation of a query. Note - canceling an INSERT, UPDATE or DELETE query is not ACID compliant - changes are made to the file system in real-time, and are not automatically rolled back. Please construct a corresponding SELECT query for testing before executing any potentially destructive INSERT, UPDATE or DELETE queries.

SELECT Statement
 

Note: SELECT statements used with a FROM command will retrieve data from the table(s) specified in the table_expression (see below). Usage without a FROM command will return a single row, executing any specified expressions in the select_expression (see below). At least one select_expression is required.

UPDATE Statement
 

Note: UPDATE statements used with an ORDER BY command will control the order in which file operations are performed. This can be useful if the order of updating filenames may otherwise cause a name collision with pre-existing files. If the SET command includes any expressions with column names, the value of the field in the currently updating row will be used. LIMIT will constraint which files are updated from the total UPDATE rowset.

INSERT Statement
 

Note: INSERT statements may use either syntax shown above. If the first syntax is used without specifying column names, the number of VALUES/VALUE expressions specified must equal the number of columns in the table. For read only fields, the value specified is ignored.

DELETE Statement
 

Note: DELETE statements used with an ORDER BY command will control the order in which file operations are performed. LIMIT will constraint which files are deleted from the total DELETE rowset.

Select Expressions
 

Note: Table and column names may be delineated using the ` character.

Table Expressions
 

Note: Table and column names may be delineated using the ` character. Comma separated tables specified after the first table in a table_expression before JOIN commands will be treated as CROSS JOINed tables.

General Expression Operators and Functions
 

SQL Types

CHAR, VARCHAR, LONG VARCHAR, BINARY, SMALLINT, INTEGER, FLOAT, DOUBLE, TIMESTAMP

Miscellaneous ODBC Support

"{d '1995-01-15'}" style date literals, unnamed parameters via '?' literals, single prepare/multiple execution model with parameter updating, thread-safety. Contact Synthetic Dreams regarding any further ODBC support questions.

Usage Notes

1. INSERT and DELETE statements may not be run against the 'Errors' table
2. The 'Errors' table is automatically purged prior to executing UPDATE, INSERT, or DELETE statements. The table should be checked for error details immediately after an unsuccessful query fails to execute

Usage Notes

1. INSERT and DELETE statements may not be run against the 'Settings' table
2. Initial values of the 'Setting' field will reflect data source settings as configured from the ODBC manager
3. Changes made to 'Setting' field will immediately update data source settings for the duration of the connection (settings will revert to permanent values upon disconnect). Changes to Base Path or Recursion will cause emissary RT: Files to rebuild all caches