|
<< Click to Display Table of Contents >> Navigation: Apollo SQL > Using Apollo SQL > Inline Table Definitions |
Inline Table Definitions give developers control over how individual tables are processed by the SQL engine. Since xBase tables and indexes are individual files and are not part of a single database file, extra care is required in managing these data files. Also, since Apollo supports multiple data formats and table types, users must define certain table information such as extra indexes, password info etc.
The Apollo SQL engine automatically parses the tables referenced by in SQL statement if you are accessing compound FoxPro or HiPer-SIx data tables only. In other words, tables that have a single auto-open index file associated with it are managed automatically. If you are accessing Clipper tables with NTX or old Fox tables with IDX indexes, then you will need to specify the indexes to use in the ExtraIndexes property.
In addition to automatic table parsing, Inline Table Definitions allow you to set various settings for each table being accessed such as TableType, OEMTranslate, Password, ExtraIndexes, OptimizeJoin and OptimizeWhere. Inline Table Definition support allows additional table information to be set along side SQL statements surrounded by [* *] and multiple table definitions can be separated by the "|" operator.
Several Inline Table Definition syntaxes are supported:
Single Table: Minimum settings required:
[* TableName: <tablename>, Alias: <alias> *]
Optimization Only (Both Params are optional – use either one or both):
[*OptimizeJoin:True|False, OptimizeWhere: True|False *]
Single Table: Params and Optimization in one [* *] section
[* TableName:<tablename>, Alias: <alias>, TableType: <type>,
OEMTranslate: True|False, Password: <password>,
ExtraIndexes: <indexList>,
OptimizeJoin:True|False, OptimizeWhere: True|False *]
Single Table: Params and Optimization in separate [* *] sections
[* TableName:<tablename>, Alias: <alias>, TableType: <type>,
OEMTranslate: True|False, Password: <password>,
ExtraIndexes: <indexList> *]
[* OptimizeJoin:True|False, OptimizeWhere: True|False *]
Multiple Tables: Params and Optimization all in one [* *] section with Tables separated by pipe "|" symbol
[* TableName:<tablename1>, Alias: <alias1>, TableType: <type>,
OEMTranslate: True|False, Password: <password>,
ExtraIndexes: <indexList> |
TableName:<tablename2>, Alias: <alias2>, TableType: <type>,
OEMTranslate: True|False, Password: <password>,
ExtraIndexes: <indexList> *]
[* OptimizeJoin:True|False, OptimizeWhere: True|False *]
Multiple Tables: Params and Optimization in separate [* *] sections
[* TableName:<tablename1>, Alias: <alias1>, TableType: <type>,
OEMTranslate: True|False, Password: <password>,
ExtraIndexes: <indexList> *]
[* TableName:<tablename2>, Alias: <alias2>, TableType: <type>,
OEMTranslate: True|False, Password: <password>,
ExtraIndexes: <indexList> *]
[* OptimizeJoin:True|False, OptimizeWhere: True|False *]
Select * From MYDATA Where MYDATA.StateCode ='"CA'
[* TableName: MyData.dbf, Alias: MYDATA *]
Select * From MYDATA Where MYDATA.StateCode = 'CA'
[* TableName: MyData.dbf, Alias: MYDATA , OptimizeWhere:True *]
Select * From MYDATA Where MYDATA.StateCode = 'CA'
[* TableName: MyData.dbf, Alias: MYDATA *]
Select * From City, State Where City.StateCode = State.StateCode
[* TableName: CITY.dbf, Alias: CITY, TableType: ttsxNTX,
OEMTranslate: False, Password: "",
ExtraIndexes: CITYNM.NTX, CITYST.NTX |
TableName: STATE.dbf, Alias: STATE, TableType: ttsxNTX,
OEMTranslate: False, Password: "",
ExtraIndexes: STATCODE.NTX *]
Select * From City, State Where City.StateCode = State.StateCode
[* TableName: CITY.dbf, Alias: CITY, TableType: ttsxNTX,
OEMTranslate: False, Password: "",
ExtraIndexes: CITYNM.NTX, CITYST.NTX *]
[* TableName: STATE.dbf, Alias: STATE, TableType: ttsxNTX,
OEMTranslate: False, Password: "",
ExtraIndexes: STATCODE.NTX *]
[*OptimizeJoin:True, OptimizeWhere:True *]
Parameters accepted between the [* *] are:
TableName |
Name of the physical table, use the file extension. Not Optional. |
Alias |
Name of the physical table, use the file extension. Not Optional. |
TableType |
ttsxFOX, ttsxNTX, ttsxNSX or ttsxNSX_DBT or FOXPRO, CLIPPER, HIPER-SIX, and APOLLO both syntax styles are accepted. |
OEMTranslate |
True or False without quotes |
Password |
Password used to decrypt the table if it is encrypted. |
ExtraIndexes |
A list of file-based indexes such as NTX or IDX files. No quotes. Example: ExtraIndexes: TEST1.NTX, TEST2.NTX
|
OptimizeJoin |
True or False without quotes (Default: True) When True, the engine will try to find an appropriate index for the JOINed field(s). If an index is found, a high-speed call to SetScope on the physical index file is used to filter the matching records of the correct table. If this fails, the JOIN will be calculated in memory. |
OptimizeWhere |
True or False without quotes (Default: True) When True, the engine will try to use a low-level xBase style Query operation on the WHERE clause. |
Sometimes setting both OptimizeJoin and OptimizeWhere to True could cause slower performance than by setting other combinations (e.g. True, False). These two properties are provided to let the developer fine-tune SQL statements. Any combination may be used: True/True, True/False, False/True or False/False.
Tip:
Select * from Sales Join Orders On (Sales.ID = Order.ID)
Where Orders.Date >= '01/01/2001'
How the engine works:
First, the Where clause makes the SQL engine set a high-speed filter on "Date >= 01/01/2001" on the Orders table. Then it traverses the Sales table and for each row it sets a high-speed filter on the ID field back to the Orders table. The default OptimizeWhere=True value would cause the SQL engine to try and set yet another filter on the Orders table when it already has a filter set, resulting in double the work. Setting OptimizeWhere to False in this case would prevent this filtering and instead would let the SQL engine do a quick in-memory evaluation. Also, having indexes on all the fields involved will speed up processing.
Basically, the problem to avoid is when you have a Join, and then a Where clause for the "right" table in the Join, you should set OptimizeWhere to False and keep OptimizeJoin set to True.
Rules
•Not case sensitive
•Quotes are not allowed.
•Separate each pair by commas.
•At least the TableName and Alias must be provided. The other parameters are optional.
Referencing Sub-Directories
Apollo SQL supports accessing data in full path directories sub-directories:
Update Test Set First = 'Homer'
[* TableName:MySubdir1\Test.DBF, Alias:Test *]
Update Test Set First = 'Homer'
[* TableName:c:\Data\MySubdir1\Test.DBF, Alias:Test *]
Syntax
[* TableName:Test.dbf, Alias:Test, TableType:FoxPro,
OEMTranslate:True, Password:shazam,
ExtraIndexes: Test1.NTX, Test2.NTX*]
For example, no additional parameters are needed if you are accessing a FoxPro table that:
- Is not password protected
- Uses default Windows characters for sorting
- Does not require OEMTranslate
Then, the following code run without specifying parameters:
Select * From Test
2. If the Test.dbf table has a password, then you would use:
Select * From Test
[* TableName:Test.dbf, Alias:Test, Password:shazam *]
Alternatively, you can use the following:
Select * From City, State Where City.StateCode = State.StateCode
[* TableName:City.dbf, Alias: City | TableName:State.dbf, Alias:State *]
Where the "|" (pipe operator) can be used to list multiple tables.