The suffixes listed are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the SQLite3 reader is SQLITE3.
DATASET
This is the file name of the SQLite3 Database.
A logical SQLite3 dataset consists of one or more files in the same folder. Files can have any extension but the following file extensions are commonly used:
- .sqlite
- .sqlite3
- .db
- .sl3
Note that .db and .sl3 are equivalent; however, the .db file extension is more commonly used. These file extensions are not exclusive to the SQLite 3 format.
This extension is added to the basename of SQLite3 files.
Required/Optional
Required
Example
SQLITE3_DATASET c:/data/citySource.db
Workbench Parameter
Source SQLite3 Database File(s)
DEF
Required/Optional: Required
The syntax of the definition is:
SQLITE3_DEF <tableName> \ [sqlite3_sql_statement <sqlQuery>] \ [sqlite3_where_clause <whereClause>] \ [<fieldName> <fieldType>] +
OR
SQLITE3_DEF <queryName> \ [sqlite3_sql_statement <sqlQuery>] \
The <tableName> must match the name of an existing SQLite3 table in the database. This will be used as the feature type of all the features read from the table. The exception to this rule is when using the sqlite3_sql_statement keyword. In this case, the DEF name may be any valid alphabetic identifier; it does not have to be an existing table name – rather, it is an identifier for the custom SQL query. The feature type of all the features returned from the SQL query are given the query name.
The <fieldType> of each field must be given, but it is not verified against the database definition for the field. In effect, it is ignored.
The definition allows specification of separate search parameters for each table. If any of the per table configuration parameters are given, they will override, for that table, whatever global values have been specified by the reader keywords such as the WHERE_CLAUSE. If any of these parameters is not specified, the global values will be used.
The following table summarizes the definition line configuration parameters:
|
Parameter |
Contents |
|
sqlite3_where_clause |
This specifies the SQL WHERE clause applied to the attributes of the layer’s features to limit the set of features returned. If this is not specified, then all the rows are returned. This keyword will be ignored if the sql3_sql_statement is present. |
|
sqlite3_sql_statement |
This specifies an SQL SELECT query to be used as the source for the results. If this is specified, the SQLite3 reader will execute the query, and use the resulting rows as the features instead of reading from the table <queryName>. All returned features will have a feature type of <queryName>, and attributes for all columns selected by the query. The sqlite3_where_clause is ignored if sqlite3_sql_statement is supplied. This form allows the results of complex joins to be returned to FME. |
If no <whereClause> is specified, all rows in the table will be read and returned as individual features. If a <whereClause> is specified, only those rows that are selected by the clause will be read. Note that the <whereClause> does not include the word WHERE.
The SQLite3 reader allows one to use the sqlite3_sql_statement parameter to specify an arbitrary SQL SELECT query on the DEF line. If this is specified, FME will execute the query, and use each row of data returned from the query to define at least one feature. Each of these features will be given the feature type named in the DEF line, and will contain attributes for every column returned by the SELECT. In this case, all DEF line parameters regarding a WHERE clause or spatial querying are ignored, as it is possible to embed this information directly in the text of the <sqlQuery>.
In the following example, all the records whose ID is less than 5 will be read from the supplier table:
SQLITE3_DEF supplier \ sqlite3_where_clause "id < 5" \ ID integer \ NAME text \ CITY text
In this example, the results of joining the employee and city tables are returned. All attributes from the two tables will be present on each returned feature. The feature type will be set to complex.
SQLITE3_DEF complex \
sqlite3_sql_statement \
"SELECT * FROM EMPLOYEE, CITY WHERE EMPLOYEE.CITY = CITY.NAME"
IDs
Required/Optional: Optional
This optional specification is used to limit the available and defined database tables that will be read. If no IDs are specified, then all tables are read. The syntax of the IDs keyword is:
SQLITE3_IDs <featureType1> \ <featureType2> … \ <featureTypeN>
The feature types must match those used in DEF lines.
The example below selects only the HISTORY table for input during a translation:
SQLITE3_IDs HISTORY
RETRIEVE_ALL_SCHEMAS
This parameter is applicable only when generating a mapping file, generating a workspace, or when retrieving schemas in an FME Objects application.
When set to yes, schemas for all of the tables and feature classes in the database are returned.
When set to no (or missing), and if RETRIEVE_ALL_TABLE_NAMES is similarly set to no (or missing), only schemas requested by the IDs parameter are returned.
Required/Optional
Optional
Values
YES | NO (default)
NO: The reader will return the schemas for the feature types specified in the IDs. If no features are specified in IDs, then FME returns the schema features for all the tables.
If this value is not specified, then it is assumed to be No.
YES: Indicates to the reader to return all the schemas of the tables in the database.
Mapping File Syntax
Not applicable.
FME Objects applications would include RETRIEVE_ALL_SCHEMAS followed by “YES” in the parameters array passed to IFMEUniversalReader::open().
Workbench Parameter
Not applicable
SEARCH_ENVELOPE
Using the minimum and maximum x and y parameters, define a bounding box that will be used to filter the input features. Only features that interact with the bounding box are returned.
If all four coordinates of the search envelope are specified as 0, the search envelope will be disabled.
Mapping File Syntax
<ReaderKeyword>_SEARCH_ENVELOPE <minX> <minY> <maxX> <maxY>
Note: If all four coordinates of the search envelope are specified as zero, the search envelope will be disabled.
Required/Optional
Optional
Workbench Parameter
Minimum X, Minimum Y, Maximum X, Maximum Y
SEARCH_ENVELOPE_COORDINATE_SYSTEM
This directive specifies the coordinate system of the search envelope if it is different than the coordinate system of the data.
The COORDINATE_SYSTEM directive, which specifies the coordinate system associated with the data to be read, must always be set if the SEARCH_ENVELOPE_COORDINATE_SYSTEM directive is set.
If this directive is set, the minimum and maximum points of the search envelope are reprojected from the SEARCH_ENVELOPE_COORDINATE_SYSTEM to the reader COORDINATE_SYSTEM prior to applying the envelope.
Required/Optional
Optional
Mapping File Syntax
<ReaderKeyword>_SEARCH_ENVELOPE_COORDINATE_SYSTEM <coordinate system>
Workbench Parameter
Search Envelope Coordinate System
CLIP_TO_ENVELOPE
This directive specifies whether or not FME should clip features to the envelope specified in the SEARCH_ENVELOPE directive.
Values
YES | NO (default)
Mapping File Syntax
<ReaderKeyword>_CLIP_TO_ENVELOPE [yes | no]
Workbench Parameter
Clip To Envelope
EXPOSED_ATTRS
This directive allows the selection of format attributes to be explicitly added to the reader feature type.
This is similar to exposing format attributes on a reader feature type once it has been generated; however, it is even more powerful because it enables schema-driven applications other than Workbench to access and leverage these attributes as if they were explicitly on the schema as user attributes.
The result of picking a list of attributes is a comma-separated list of attribute names and types that will be added to the schema features. Currently all reader feature types will receive the same set of additional schema attributes for a given instance of the reader.
Required/Optional
Optional
Mapping File Syntax
Not applicable.
While it is possible for FME Objects applications to invoke this directive, the required format is not documented.
This directive is intended for use in our GUI applications (for example, Workbench) only.
Workbench Parameter
Additional Attributes to Expose