Database Translators

Despite the presence of a SQL standard, many database system vary in how they implement or accomplish various tasks. The JDBC driver system tries to hide these differences as much as possible, but unfortunately some differences persist.

The database translator system in Ignition navigates these differences as they apply to the system. It provides a way to define certain key operations that are commonly different between database vendors, such as creating auto-incrementing index columns, and the keywords used for different data types.

Database translators are managed in the gateway under Databases > Drivers > Translators (tab). Ignition comes pre-configured with translators for the major supported databases, but it is possible to edit and remove them, as well as create new translators. It should only be necessary to create a new translator when adding a new JDBC driver for a database that does not share syntax with any of the existing translators.

Each field of the database translator will define a pattern that will be used, with special token markers to indicate places where other values will be placed. For example, the default Create Table entry looks as follows:

In this example, tablename, creationdef, and primarykeydef are all tokens that will be expanded. The token tablename will be replaced directly with the table, but creationdef will be a list of columns, and primarykeydef will be the phrase created by the Primary Key Syntax entry in the translator.

Token	Description
tablename	The name of the table being created.
indexname	The name of the index to create, when adding a column index to the table.
primarykeydef	A clause that will define a primary key for a new table.
creationdef	The list of columns to create in the table
alterdef	A list of columns to add/remove/modify in the table
columnname	The name of a column
type	The data type of a column
limit	The value of the limit clause

Defines where the limit clause should be placed. With Back, the limit will be placed at the end of the query. Front will place it directly after the "SELECT" keyword.

All columns will be created and accessed with the defined quote, which tells the database to use a specific casing, as well as avoiding collisions between the column name and database keywords.

Indicates whether the JDBC driver supports the return of generated keys. If the driver does not support this feature, the Fetch Key Query will be used to retrieve the last key.