The directives listed below are processed by the SQLite3 writer. The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the SQLite3 writer is SQLITE3.
DATASET
Required/Optional: Required
The DATASET directive operates in the same manner as it does for the SQLite3 reader.
Workbench Parameter: Destination SQLite3 Database File
DEF
Required/Optional: Required
Each SQLite3 table must be defined before it can be written. The general form of a SQLite3 definition statement is:
SQLITE3_DEF <tableName> \ [sqlite3_update_key_columns <keyColumns>] \ [sqlite3_drop_table (yes|no)] \ [sqlite3_truncate_table (yes|no)] \ [sqlite3_table_writer_mode (inherit_from_writer|insert| update|delete)] \ [<fieldName> <fieldType>[,<indexType>]]+
When the destination table does not exist, the table definition specifies the table that will be created. When the destination table exists and is not being dropped, the fields and types listed should match those of the existing table.
Fields in the table definition that do not match any existing columns will be ignored by the writer. Where <fieldType> is specified, it should be a field type supported by the target database.
The configuration parameters present on the definition line are described in the following table:
|
Parameter |
Contents |
|
tableName |
The name of the table to be written. If a table with the specified name exists, it will be overwritten if the sqlite3_drop_table DEF line parameter is set to YES, or it will be truncated if the sqlite3_truncate_table DEF line parameter is set to YES. Otherwise the table will be appended. Valid values for table names include any character string devoid of SQL-offensive characters(the “ is the only SQL-offensive character in SQLite) and less than 255 characters in length. |
|
sqlite3_table_writer_ |
The default operation mode of the feature type in terms of the types of SQL statements sent to the database.Valid values are INSERT, UPDATE, DELETE and INHERIT_FROM_WRITER. Note that INSERT mode allows for only INSERT operations where as UPDATE and DELETE can be overwritten at the feature levels. INHERIT_FROM_WRITER simply indicates to take this value from the writer level and not to override it at the feature type level. Default:INHERIT_FROM_WRITER |
|
sqlite3_update_key_ |
This is a comma-separated list of the columns which are matched against the corresponding FME attributes’ values to specify which rows are to be updated or deleted when the writer mode is either UPDATE or INSERT. For example: sqlite3_update_key_columns ID would instruct the writer to ensure that the ID attribute is always matched against the column with the same name. Also, the target table is always the feature type specified in the DEF line. Each column listed with the sqlite3_update_key_columns keyword must be defined with a type on the DEF line, in addition to the columns whose values will be updated by the operation. |
|
sqlite3_drop_table |
This specifies that if the table exists by this name, it should be dropped and replaced with a table specified by this definition. Default: NO |
|
sqlite3_truncate_table |
This specifies that if the table exists by this name, it should be cleared prior to writing. Default: NO |
|
fieldName |
The name of the field to be written. Valid values for field name include any character string devoid of SQL-offensive characters (the “ is the only SQL-offensive character in SQLite) and less than 255 characters in length. |
|
fieldType |
The type of a column in a table. The valid values for the field type are listed below: blob float integer real(width, decimal) text varchar(width) |
|
indexType |
The type of index to create for the column. If the table does not previously exist, then upon table creation, a database index of the specified type is created. The database index contains only the one column. The valid values for the column type are listed below: indexed: An index without constraints. unique: An index with a unique constraint. |
START_TRANSACTION
Required/Optional: Optional
This statement tells the SQLite3 writer module when to start actually writing features into the database. The SQLite3 writer does not write any features until the feature is reached that belongs to <last successful transaction> + 1. Specifying a value of zero causes every feature to be output. Normally, the value specified is zero – a non-zero value is only specified when a data load operation is being resumed after failing partway through.
|
Parameter |
Contents |
|
<last successful transaction> |
The transaction number of the last successful transaction. When loading data for the first time, set this value to 0. Default: 0 |
Example:
SQLITE3_START_TRANSACTION 0
Workbench Parameter: Start transaction at
TRANSACTION_INTERVAL
Required/Optional: Optional
This statement informs the FME about the number of features to be placed in each transaction before a transaction is committed to the database.
If the SQLITE3_TRANSACTION_INTERVAL statement is not specified, then a value of 500 is used as the transaction interval.
|
Parameter |
Contents |
|
<transaction_interval> |
The number of features in a single transaction. Default: 500 |
If the SQLITE3_TRANSACTION_INTERVAL is set to zero, then feature based transactions are used. As each feature is processed by the writer, they are checked for an attribute called fme_db_transaction. The value of this attribute specifies whether the writer should commit or rollback the current transaction. The value of the attribute can be one of COMMIT_BEFORE, COMMIT_AFTER, ROLLBACK_AFTER or IGNORE. If the fme_db_transaction attribute is not set in any features, then the entire write operation occurs in a single transaction.
Example:
SQLITE3_TRANSACTION_INTERVAL 5000
Workbench Parameter: Transaction interval
WRITER_MODE
Required/Optional: Optional
Note: For more information on this directive, see the chapter Database Writer Mode.
This directive informs the SQLite3 writer which SQL operations will be performed by default by this writer. This operation can be set to INSERT, UPDATE or DELETE. The default writer level value for this operation can be overwritten at the feature type or table level. The corresponding feature type DEF parameter name is called sqlite3_table_writer_mode. It has the same valid options as the writer level mode and additionally the value INHERIT_FROM_WRITER which causes the writer level mode to be inherited by the feature type as the default for features contained in that table.
The operation can be set specifically for individual features as well, using the fme_db_operation attribute. Note that when the writer mode is set to INSERT this prevents the mode from being interpreted from individual features and all features are inserted unless otherwise marked as UPDATE or DELETE features. These are skipped.
If the SQLITE3_WRITER_MODE statement is not specified, then a value of INSERT is given.
|
Parameter |
Contents |
|
<writer_mode> |
The type of SQL operation that should be performed by the writer. The valid list of values are below: INSERT UPDATE DELETE Default: INSERT |
Example:
SQLITE3_WRITER_MODE INSERT
Workbench Parameter: Writer Mode
BEGIN_SQL{n}
Sometimes, you must execute some SQL statements prior to opening a table. For example, it may be necessary to ensure that a view exists before attempting to read from it.
Upon opening a connection to read from a database, the reader looks for the directive <ReaderKeyword>_BEGIN_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.
Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.
For example:
FME_SQL_DELIMITER ; DELETE FROM instructors; DELETE FROM people WHERE LastName='Doe' AND FirstName='John'
Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.
Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.
Required/Optional
Optional
Workbench Parameter
Begin SQL
END_SQL{n}
Sometimes you must execute some SQL statements after a set of tables has been read. For example, it may be necessary to clean up a temporary view after creating it in BEGIN_SQL.
Just before closing a connection on a database, the reader looks for the directive <ReaderKeyword>_END_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.
Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.
For example:
FME_SQL_DELIMITER ; DELETE FROM instructors; DELETE FROM people WHERE LastName='Doe' AND FirstName='John'
Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.
Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.
Required/Optional
Optional
Workbench Parameter
End SQL
INIT_TABLES
Required/Optional: Optional
This directive informs the SQLite3 writer when each table should be initialized. Initialization encompasses the actions of dropping or truncating existing tables, and creating new tables as necessary.
When INIT_TABLES is set to IMMEDIATELY, the SQLite3 writer will initialize all tables immediately after parsing the DEF lines and opening the database file. In this mode, all tables will be initialized, even if the SQLite3 writer receives no features for a given table.
When INIT_TABLES is set to FIRSTFEATURE, the SQLite3 writer will only initialize a table once the first feature destined for that table is received. In this mode, if the SQLite3 writer does not receive any features for a given table, the table will never be initialized.
Workbench Parameter: Initialize Tables