Magic Software’s uniPaaS Application Platformincludes a SQLlite gateway to support the SQLite database. So does uniPaaS Jet, the free distribution single user version of Magic Software’s uniPaaS application platform.
Those already familiar with SQLite will know that it is a single user, self-contained transactional SQL database engine that does not require a server or any installation or configuration.
The SQLite gateway is integrated in uniPaaS and available with every installation of uniPaaS. SQLite can be thought of as a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite claims to be the most widely deployed SQL database engine in the world and the source code for SQLite is in the public domain.
Those already familiar with SQLite will know that it is a single user, self-contained transactional SQL database engine that does not require a server or any installation or configuration.
The SQLite gateway is integrated in uniPaaS and available with every installation of uniPaaS. SQLite can be thought of as a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite claims to be the most widely deployed SQL database engine in the world and the source code for SQLite is in the public domain.
uniPaaS Jet supports the SQLite DBMS exclusively. The SQLite database, a single-user database embedded in uniPaaS Jet, comes bundled with the platform, eliminating the need to install it separately.
Once again: SQLite is a single user database and is not intended for handling and manipulating data from multiple, concurrent users. Commercial releases of uniPaaS support multi-user databases such as Microsoft SQL, Oracle, IBM DB2, and any other data source through ODBC.
When working with SQLite in uniPaaS, keep the following in mind:
| Table Position | SQLite supports rowid as the table position. The default position is ROWID. Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using a special case-independent name: "rowid". The data for each table in SQLite is stored as a B-Tree structure containing an entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. To be clear, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions to occur rapidly and efficiently. |
| Get Definition | For SQLite, if the primary key is a single column that auto-increments, the Get Definition utility will not bring the key into the table structure. This is because there is not a separate index using the column. The index internally refers to rowid. |
| Hints | Hints are not supported and will be disregarded. |
| Isolation level | Isolation level cannot be changed. Any changes in the DBMS will be disregarded. |
| Joins | A join is used to combine rows from multiple tables. Inner Joins are the most common type of join. Inner joins return all rows from multiple tables where the join condition is met. Outer Join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (the join condition is met).Inner and Outer joins should be implemented using the same syntax as in the SQL Server. |
| Transactions | A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer. In uniPaaS, SQLite physical /deferred transactions are supported. |
| Array fetching | SQLite does not support array fetching. Therefore, the array size setting will be disregarded and the records will be fetched one record at a time. |
| Parallel execution | A single uniPaaS engine, or multiple engines on the same machine, may access SQLite via parallel programs. In such a case, only one thread/EXE can open a physical transaction, since the SQLite file is exclusively locked. |
| APG | Using the Automatic Program Generator (APG) in DSQL SELECT statements will result in the Null Allowed column property being set to Yes. |
| Identity | Identity should be defined as Type INTEGER PRIMARY KEY. SQLite does not allow more than one INTEGER PRIMARY KEY fields. Identity fields are stored as INT64 data types in SQLite; therefore, you should create a Numeric field to hold 8 bytes of data. |
There are two other limitations of SQLite worth noting for the uniPaaS developer. You cannot use the ASCIIChr SQL function which converts a number to a corresponding character in the ASCII character set. You also cannot use the InStr function, which returns a number that represents the first position of a sub-string within an Alpha string or an Alpha expression, or at least you cannot use from within a SQL Where range.
SQLite is just another great addition this year to the Magic of uniPaaS.
