IO using SQLite

9.4

The SQLite database engine has been embedded in the Sunbelt products to enhance the overall operations for the PLB Language. By developing a schema database, end-user record layouts can be defined in SQL tables.

Definitions

SQLite Database

A SQLite Database is a data file with a '.db' extension that has been created using the SQLite database engine. The SQLite database engine has been embedded into the Sunbelt products to allow dynamic creation and access to various tables created in a SQLite database file.

Schema Database

A Schema Database as implemented into the Sunbelt runtimes is a SQLite database file that contains the SQL table names 'sun_views', 'sun_columns', and 'sun_databases'. A Schema Database can be referenced by a Schema Name.

Default Schema Database

The Default Schema Database is opened/created for a Sunbelt runtime or Data Manager during the initial startup phase. The PLB_SCHEMA or DM_SCHEMA keywords specifies a specific SQLite Database file that contains the SQL table names 'sun_views', 'sun_columns', and 'sun_databases'. If the PLB_SCHEMA or DM_SCHEMA keywords do not exist, a default Schema Database is opened/created as 'sunschema.db' for the runtime or Data Manager.

Overview

A schema database must contain internal tables named 'sun_views', 'sun_columns', and 'sun_databases'. The data inserted into these tables is used by the Sunbelt products to provide enhanced functionality that includes PLB IO using column names and record filtering. A basic description of these tables is as follows:

Schema Table 'sun_views'

An SQL table that is created/opened in a SQLite database to which the runtime has access. The intent of this table is to contain data that gives an identification and name for record layout views. The view names specified in this table are not case sensitive. A PLB program can specify a view name in an OPEN or PREPARE instruction. The file variable is then associated with the view data layout to perform file IO operations. The following column names are defined in the 'sun_views' table

Schema Table 'sun_columns'

The 'sun_columns' is a SQL table that is created/opened in a SQLite database to which a runtime has access. The intent of this table is to contain data that defines the column information to access/use a field in a PLB data file record. The view 'id' from the 'sun_views' table uniquely ties each column data row to a view name that a user program is using. The following column names are defined in the 'sun_columns' table.

Format Masks (9.5B)

Columns schema definitions for VIEWS support a 'format_mask' definition.

1. The SQL_DATE 'format_mask' must include the following special characters to identify the mask format:

 

Mask	Meaning
zz or yy or yyyy	2 year or 4 digit year
mm	2 digit month
dd	2 digit day

 

The 'zz' year is a two character mask field that has the year encoded to represent a four digit number value for a year. The high order (first) character of the 'zz' mask can be a numeric character zero to nine or it can be an uppercase alpha character 'A' to 'Z'. The low order (second) character of the 'zz' mask can only be a numeric character zero to nine. Both of the 'zz' characters are used to calculate a four digit year.

 

Example of data for 'zz' encoded character mask:

 

Encoded Data	Calculated Year
00	1900
...
99	1999
A0	2000
...
A9	2009
B0	2010

 

The default SQL_DATE format_mask is 'mm-dd-yy'

2. The SQL_TIME 'format_mask' must include the following special characters to identify the mask format:

 

Mask	Meaning
hh	2 digit hours
mm	2 digit minutes
ss	2 digit seconds

 

The default SQL_TIME format_mask is 'hh:mm:ss'

3. The SQL_DATETIME 'format_mask' must include the following special characters to identify the mask format:

 

Mask	Meaning
yy or yyyy	2 year or 4 digit year
mm	2 digit month
dd	2 digit day
hh	2 digit hours
nn	2 digit minutes
ss	2 digit seconds

 

The default SQL_DATETIME format_mask is 'yyyymmddhhnnss'

 

Example:

 

<sql_type>9</sql_type>

<format_mask>mm/dd/yy</format_mask>

4. For a VIEW and the 'sql_type', only the date, time, and datetime SQL data types are supported at this time. All other data type values are ignored at this time.

5. Standard SQL formats are defined as follows:

 

Mask	Meaning
Date	yyyy-mm-dd
Time	hh:mm:ss
DateTime	yyyy-mm-dd hh:mm:ss

 

These formats are the required andexpected formats that are used to perform actual SQL operations. When the runtime performs SQL operations for the date, time, and datetime data types, appropriate conversions are performed using the 'format_mask' to insure proper operations.

6. When using the new data types.

1. A WRITE by columname= operation converts data from the standard SQL format into the format specified by the 'format_mask'.

2. A READ by columname= operation causes the PL/B data to be transformed using the 'format_mask' into the standard SQL format.

3. A FILTER statement handles a column marked as a date or time column by transforming the data using the 'format_mask' into the standard SQL format for comparison. A string constant in a FILTER expression can be in either the standard SQL form or the form specified by the 'format_mask'.

 

Example:

FILTER ORDERS_DISK,"ORDERDATE > '1991-04-24'"

FILTER ORDERS_DISK,"ORDERDATE > '04/24/91'"

Schema Table 'sun_databases'

The 'sun_databases' is a SQL table that is created/opened in a SQLite database to which the runtime has access. The intent of this table is to contain data that associates a unique schema database name to a physical SQLite database file which includes the 'sun_views' and 'sun_columns' detail tables used for schemas by the runtime. The PLB program can specify the schema name when using the SCHEMA instruction as well as when a VIEW name is being specified in an OPEN or PREPARE instruction. The runtimes use a schema name to access the 'sun_databases' table to determine the physical SQLite database file and its location to be accessed. Please note that if the PLB program does not specify a database name, the default database for the runtime is used.

PLB Schema Getting Started

Before the PLB file IO using the column name and filtering enhancements can be used in an end-user application, the record layout for fields that includes field names for referencing, field types, and the field locations in the records must be specified in a PLB schema. The schema information is placed into the 'sun_views', 'sun_columns', and the 'sun_databases' tables of a SQLite database. All of the schema information defined in these tables is grouped and referenced in the PLB programs as PLB view names. The data definitions for a PLB view are used by the PLB runtimes to map field name references to the new column name format support in READ, WRITE, and UPDATE instructions in addition to the field name references used in the new FILTER instruction. There are basic areas to be considered when getting started as follows

1. Record/Field Layout

2. Methods for loading PLB Schemas

3. PLB Schema Creation

4. PLB Schema Usage

I. Record/Field Layout

1. Determine all of the PLB data files that are to be included in a PLB schema database.

2. For each file that is to be included in a schema, retrieve the following information for each field in the file records:

    Field name to be referenced. (limited to 63 characters)

    One based offset of field in the record

    Total length of field in the record. (must be > 0)

    Type of field. (0 - DIM, 1 - FORM, 2 - INTEGER)

    Element count. (1 - Single element, >1 - Array count)

3. Using the collected data, the information is arranged into PLB data views that are put into the 'sun_views' and 'sun_columns' SQL tables.

II. Methods for Loading PLB Schemas

There are four basic methods that initialize or load data into a PLB Schema as follows:

1.   The PLB SchemaEditor utility can create, examine and modify a PLB Schema. This utility can create the required SQL tables and insert the PLB schema data into these tables that defines the PLB schema views and field definitions. The utility can also import PLB data definitions.

 

2. Use the PLB DBxxx instructions to access the PLB schema database using the 'SQLITE' database host name. The DBxxx instructions can create the PLB schema tables and insert the PLB schema data as needed. The DB instructions can execute SQL statements to initialize or load the PLB schema data.

 

SQL statement to create 'sun_views'

Create table sun_views (id integer primary key,

     name text not null unique,

     description text);

 

In this case, the 'sun_views' table is created in the schema database with two columns named 'id' and 'name'.

 

SQL statement to insert a view name into 'sun_views' table

Insert into sun_views (name) Values ('product');

 

In this case, the PLB view name 'product' is inserted into the 'sun_views' schema table.

 

SQL statement to retrieve the 'id' number for a view name

Select id from sun_views where upper(name)=upper('product');

 

In this case, the 'id' value is retrieved for the 'product' view name from the 'sun_views' table.

 

SQL statement to create 'sun_columns'

Create table sun_columns (id integer primary key,

view_id integer,

name text not null,

description text,

type integer,

offset integer,

length integer,

scale integer,

element_count integer,

sql_type integer,

key_value integer,

nullable integer,

zero_fill integer,

empty_null integer,

selectivity integer,

format_mask text,

default_value text);

 

In this case, the 'sun_columns' table is created in the schema database with sixteen columns that contain the PLB schema field data.

 

SQL statement to insert column data into 'sun_columns'

 

Insert into sun_columns (

view_id, name, type,

offset, length, scale,

element_count, sql_type, key_value,

nullable, zero_fill, empty_null,

selectivity, format_mask, default_value )

Values ( 1, 'SHIPWT', '', 0,

102, 5, 0,

1, 0, 0,

1, 0, 0,

0, '', '');

 

In this case, the column field named 'SHIPWT' is inserted into the 'sun_columns' schema table. The schema data is inserted as follows:

 

Column	Value
view_id	= 1 (value same as view 'id')
name	= 'SHIPWT' (column/field name)
description	='' (Empty)
type	= 0 (DIM type)
offset	= 102 (Offset in record)
length	= 5 (Length of field)
scale	= 0 (Not used for DIM)
element_count	= 1 (Single element)
sql_type	= 0 (Used only for ODBC)
key_value	= 0 (Used only for ODBC)
nullable	= 1 (Used only for ODBC)
zero_fill	= 0 (Used only for ODBC)
empty_null	= 0 (Used only for ODBC)
selectivity	= 0
format_mask	= '' ( Empty )
default_value	= '' ( Empty )

 

3. The PLB Language supports using the instructions for XFILE variables can create an XML data file or data stream that can then be loaded into a PLB Schema database using the SCHEMA IMPORT instruction. The following is a sample of the XML data format used for a PLB Schema:

 

Example PLB Schema XML format:

 

<?xml version="1.0" encoding="UTF-8"?>

<Schema>

  <View>

    <name>Customer</name>

    <Column>

      <name>FieldName1</name>

      <description></description>

      <offset>1</offset>

      <length>5</length>

      <type>0</type>

      <scale>0</scale>

      <element_count>1</element_count>

      <sql_type>0</sql_type>

      <key_value>0</key_value>

      <nullable>0</nullable>

      <zero_fill>1</zero_fill>

      <empty_null>1</empty_null>

      <selectivity>100</selectivity>

      <format_mask></format_mask>

      <default_value></default_value>

    </Column>

    <Column>

      <name>FieldName2</name>

      <description></description>

      <offset>6</offset>

      <length>6</length>

      <type>0</type>

      <scale>0</scale>

      <element_count>1</element_count>

      <sql_type>0</sql_type>

      <key_value>0</key_value>

      <nullable>0</nullable>

      <zero_fill>1</zero_fill>

      <empty_null>1</empty_null>

      <selectivity>100</selectivity>

      <format_mask></format_mask>

      <default_value></default_value>

    </Column>

  </View>

</Schema>

 

Note that more than one view can be defined in the same xml schema data stream. When creating all of the column or field schema data, all of the data schema tag items are required and must be declared.

 

4. The PLB DBExplorer utility can examine and modify a PLB Schema. This utility can create the required SQL tables and insert the PLB schema data into these tables that defines the PLB schema views and field definitions. The DBExplorer can execute SQL statements directly for the PLB Schema database.

III. PLB Schema Creation

A PLB Schema is a SQLite database that includes the 'sun_views' 'sun_columns', and 'sun_database' tables. The information collected in the record and field layout needs to be put into the PLB Schema as follows:

1. Select method of loading PLB Schema data.

2. Select a SQLite database for a PLB Schema.

3. Create PLB Schema tables in the SQLite database.

4. Insert a data row into the 'sun_views' table for each PLB data view to be defined.

5. Insert multiple column/field rows into the 'sun_columns' table for each PLB data view that has been defined.

6. If the end-user has application specific schema databases in a PLB program, insert the PLB Schema database names into the 'sun_databases.'

IV. PLB Schema Usage

After the PLB Schema database has been created with the required SQL tables named 'sun_views', 'sun_columns', and 'sun_databases' and the collected column/field schema data has been inserted, the end-user application can start using the new column name syntax for the READ, WRITE, and UPDATE instructions and start using the new FILTER instruction. A general overview to start using a PLB Schema in a PLB program is as follows:

1. Before PLB data views can be used, they must exist in the in a PLB Schema database. The PLB data views can exist in the default schema for a runtime or in some other database that is specific to a PLB application.

2. Assuming that the default PLB Schema database for a runtime has been loaded with the Schema data, the end-user application must first OPEN or PREPARE a FILE, AFILE, or IFILE with the 'VIEW={viewname}' parameter specified.

 

Example of OPEN/PREPARE with VIEW

 

IFILE	IFILE
CID	DIM	5
ADR	DIM	21
	OPEN	IFILE,"myifile",VIEW="Customer"

 

In this case, the data file 'myifile' is a normal ISI file that is being accessed. The 'Customer' is a PLB data view name that can be found in the 'sun_views' table in the default schema database. The 'Customer' data view column information can be found in the 'sun_columns' table in the default schema database.

3. After a FILE, AFILE, or IFILE has been OPENed or PREPAREd with a view specified, the program can use the new column or field name syntax for the READ, WRITE, and UPDATE instructions. The column or field name reference that is put into the instruction variable list must be defined in the 'sun_columns' schema table being used for the {viewname} that was specified on the OPEN or PREPARE instruction.

 

Example of READ using column/field names from VIEW

 

IFILE	IFILE
CID	DIM	5
ADR	DIM	21
KEY	DIM	5
.
	OPEN	IFILE, "myifile", VIEW="Customer"
	...
	MOVE	"mykey", KEY
	...
	READ	IFILE,KEY; ADDRESS=ADR,CUSTID=CID

 

In this case, the READ instruction retrieves data for two columns or fields that are named 'ADDRESS' and 'CUSTID'. The runtime determines the location of these columns or fields in the file record and the corresponding field data is returned in the program variables. Also notice, that an I85 error occurs if the column or field name syntax is used and the file variable being used does not have a view specified.

4. Again assuming that a file variable has been OPENed or PREPAREd with a view, a PLB program can use the column or field names defined in the 'sun_columns' table for the {viewname} in the FILTER instruction. The FILTER instruction specifies a filter expression that can consists of column or field names, operators, and values. When a filter expression is assigned to a file variable, the runtime applies it to the data before a record is returned to READ instruction. If the record data does not pass the filter expression, the READ operation continues until a record is found that satisfies the filter expression. If there are no records that satisfies the filter expression, the OVER flag is set for the READ. Filter expressions can be set on a file variable any number of times after the file variable has been opened or prepared with a VIEW. If a column or field name is used in a filter expression that is not defined in the file variable PLB VIEW, an I85 error occurs. Filter expressions only affect READ instructions. They do not affect the operation of any other file IO instructions.

 

Example of READ using filter expression

 

IFILE	IFILE
CID	DIM	5
ADR	DIM	21
KEY	DIM	5
.
	OPEN	IFILE,"myifile",VIEW="Customer
	FILTER	IFILE,"POSTALCODE = '75076'"
	...
	MOVE	"mykey",KEY
	...
	READ	IFILE,KEY;ADDRESS=ADR,CUSTID=CID

 

In this case, the ISAM READ instruction executes to find records that have the specific key 'mykey' and the 'POSTALCODE' value is equal to '75076'. The READ only returns record data when both of these conditions are satisfied. Otherwise the READ gives an OVER condition. Also the filter expression can be used on READ instructions that do not use the column or field name syntax for the READ instructions.

5. The Data Manager allows the SQLite database engine to be loaded and used as a driver using keywords from the Sundm CFG file. This feature allows a PLB program to access the SQLite database engine by referencing user defined keywords in the runtime INI and the Data Manager CFG files.

Example 1:

 

PLBWIN INI keyword to access SUNDM:

 

SOMEKEYDM=FILEMAN,127.0.0.1

 

SUNDM CFG keyword to access SQLite database engine:

 

SOMEKEYDM=SQLite

 

PLB Program sample:

 

MYDB	DBFILE
.
	DBCONNECT	MYDB,"SOMEKEYDM;;c:\mypath\mydata.db","",""
.

Example 2:

 

PLBWIN INI keyword to access SUNDM:

 

SOMEKEYDM=FILEMAN,127.0.0.1

 

SUNDM CFG keyword to access SQLite database engine:

 

SOMEKEYDM=SQLite,c:\sundmpath\mydm.db

 

PLB Program sample:

 

MYDB	DBFILE
.
	DBCONNECT	DBCONNECT MYDB, "SOMEKEYDM;;","",""
.

See Also: SQLite, SCHEMA, FILTER, Disk I/O Instructions