This page explains the SQL Adapter in Bridge context. If you were looking for the same information regarding the PAS Designer, refer to SQL Adapter in the Designer guide. |
Using SQLite as DBMS there are two different options that may be used:
The ordinary way would be to reference a database file located in the file system.
Alternatively the database file can be added to the project as a resource using the resource importer.
Holding the database in memory only, without creating a database file, is not possible. The following paragraphs describe when using these options makes sense and how it is done in the component diagram.
Using tagĀ options on the <<SQLAlias>> the following SQLite specific options can be set:
BusyTimeout=<time in milliseconds>
. The default is 60000 milliseconds. Used to avoid some well known problems.
SQLite PRAGMA statements: Used mainly for performance tuning. Details see below.
The SQLite PRAGMA statement is a SQL extension specific to SQLite and used to modify the behavior of the SQLite database. The syntax is sqlite_pragma_<pragma_name>=<pragma_value>. The main use case is performance tuning. For example, the following PRAGMA options speed up inserting data (but also reduce data safety):
sqlite_pragma_synchronous=OFF, sqlite_pragma_temp_store=MEMORY, sqlite_pragma_journal_mode=TRUNCATE |
The tag option is set in the component diagram, for example:
The meaning of these statements is:
sqlite_pragma_synchronous=OFF: Disable wait for writes to complete (may increase performance by factor 50). Potential of database corruption on power failure.
sqlite_pragma_temp_store=MEMORY: Store temporary tables to memory.
sqlite_pragma_journal_mode=TRUNCATE: The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it.
If you want to speed up your persistent state database, please look at option Internal State DB Synch on Persistent State Components. |
The ordinary way of using an SQLite database is the specification of a database file located in the file system (e.g. C:\temp\db.sqlite). The component diagram for the file system deployment option as follows:
Figure: SQLite Deployment Using a Database File in the File System
The SQlite database file (e.g. db.sqlite) can be imported using the resource importer. The import procedure is described in the E2E Builder User Guide, you can select the option "binary file" during the import.
Using this special way, the database itself will be included in the repository during deployment. This has the following implications:
The component diagram for the resource deployment option looks as follows.
Figure: SQLite Deployment Using a File Resource
Using the SQL Adapter with SQLite database, you may get the following error:
[SQLSM][6][Error Message: 5 "database is locked". SQL Statement: ...]
This occurs, if multiple threads or processes want to read/write the SQLite database simultaneously. In case of concurrent writes, one write will fail. The xUML Runtime will retry to execute the write for 60 seconds. After the time-out, the message above is written to the bridgeserver log.
There are two possible approaches to solve this conflict (they may also be combined):
BusyTimeout=<time in milliseconds>
to the tag options of the <<SQLAlias>> . Default is 60000 milliseconds - increase this value.