SQLite

9.4

The SQLite embedded database engine is one of the most widely deployed database engines in the world. It is a self-contained engine that requires minimal external interfaces to be used. By being self-contained, it does not rely on specific OS behaviors or specialize infrastructures. There are no configuration requirements to get it started and it does not require a server. The SQLite database file format allow for cross-platform support. A SQLite database file can be copied between big-endian and little-endian architectures. The SQLite database engine is very popular and has a very rich history with a strong user base that have many third party tools available.

Some of the strong features that has made SQLite a viable enhancement to the Sunbelt products are:

    Public domain source - www.sqlite.org

    No intermediary server process

    Databases are in a single file

    Databases can be memory based

    There is zero configuration

    Implements SQL-92 standard

    Very fast performance

    Embedded in many third party products

    Very popular world wide with a strong user base

    Third party tools available

    Feature rich API set that can minimize development for PLB.

Support for the SQLite database engine has been implemented into the version 9.4 runtimes to enhance the PLB Language functionality. This embedded database can be used directly using the traditional database statements from a PLB program. Also, it may be used to support a PLB schema for enhanced file IO. Over time, the embedded database engine use will be expanded to provide dynamic configurations and operations for the Sunbelt products.

DB Statements

A PLB program can access the embedded SQLite database engine directly using the standard DBxxx instructions. To use the DBCONNECT instruction to logon to the embedded SQLite database engine, the {host} parameter can be specified with the <driver> set to 'SQLITE'. An example {host} parameter string would be

     SQLITE;;mydata.db

After a connection as been established to the embedded SQLite database engine, normal DBxxx instructions execute SQL statements to create and access tables as expected for the database engine. From a user perspective, there is no tedious configurations or setup actions required to use the embedded SQLite database engine. It is simply available to any PLB program on any workstation as needed.

Schema Database

The embedded SQLite database engine has been embedded/implemented into the PLB runtimes to enhance the PLB file IO. To support the file IO enhancements, schema database tables have been developed allowing PLB file IO statements in the runtimes to access schema information that defines the record layouts in PLB data files. A Sunbelt runtime has a default schema database that is named 'sunschema.db'. This default schema database is located in the same directory as the runtime. The PLB_SCHEMA keyword sets the default schema database when a runtime is initially loaded. A schema database must include the special internal SQL tables named 'sun_views', 'sun_columns', and 'sun_databases' required to contain various record layouts that are referred to as 'views'. A new instruction named SCHEMA has been added to support schema database information that is required to support the PLB Language. Also, note that the PLB_SCHEMA keyword using the PLB(Linux) runtime can be put into the UET

Enhanced File IO

By using a VIEW that is defined in a schema database, the PLB file IO instructions have been expanded to allow COLUMN names in the variable list to identify the data fields that are accessed in records. In addition, the READ instructions have been expanded to support record filtering. A new instruction named FILTER has been added to enable or disable filtering for individual file variables. When a filtering expression has been assigned to a file variable, a READ instruction evaluates the filter expression before the data from the record is returned to an end-user READ operation. In order to use either the COLUMN name syntax or READ record filtering, a record layout VIEW must be assigned to a file variable when the file variable is opened or prepared.

See Also: IO using SQLite