Der Magic Software Germany Blog

Montag, 12. Dezember 2011

Bauen Sie Apps schnell mit uniPaaS und dem SQLite-Gateway

Die Applikationsplattform uniPaaS von Magic Software umfasst ein SQLite-Gateway zur Unterstützung der SQLite-Datenbank. So auch uniPaaS Jet, die kostenlose Einzelnutzerversion der Applikationsplattform uniPaaS von Magic Software.

Wer sich bereits mit SQLite auskennt wird wissen, dass es sich um eine Single-User und  in sich geschlossene Transaktions-SQL-Datenbank-Engine handelt,  die  keinen Server, keine Installation oder Konfiguration benötigt.

Das SQLite-Gateway ist in uniPaaS integriert und in jeder Installation von uniPaaS verfügbar. SQLite kann man sich als eine Softwarebibliothek, die eine in sich geschlossene, serverlose, konfigurationslose, transaktionale SQL-Datenbank-Engine beinhaltet, vorstellen. SQLite gehört zu der am weitesten verbreiteten SQL-Datenbank-Engines  in der Welt und der Quellcode für SQLite ist in der Public Domain.

uniPaaS Jet unterstützt ausschließlich die SQLite Datenbank. Diese Single-User DBMS wird zusammen mit uniPaaS Jet installiert.

SQLite ist eine Single-User-Datenbank und ist nicht für die Handhabung und Bearbeitung von Daten mehrerer gleichzeitiger Benutzer gedacht. Kommerzielle Versionen von uniPaaS unterstützen Multi-User-Datenbanken wie Microsoft SQL, Oracle, IBM DB2, und andere Datenquellen über ODBC.

Bei der Benutzung von SQLite sollten Sie die folgenden Punkte beachten:

There are two other limitations of SQLite worth noting for the uniPaaS developer. You cannot use theASCIIChr 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.

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 are not supported and will be disregarded.
Isolation level
Isolation level cannot be changed. Any changes in the DBMS will be disregarded.
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.
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.
Using the Automatic Program Generator (APG) in DSQL SELECT statements will result in the Null Allowed column property being set to Yes.
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.

SQLite ist nur eines der großartigen Zusatzprogramme, die uniPaaS seit diesem Jahr bereichern.