SQLite Database Interface
The SQLite interface is a DLL with callable routines which enables Planimate to store and access large amounts of data in database files using Structured Query Language (SQL. It features high speed, mapping between Planimate and SQL table columns, support for Planimate dates, flexible handling of labels and support for text data as well as numeric.
Function Call Notation
At the time of writing, DLLs are called in Planimate routines using the CallDLL routine operation, eg:
r.result = CallDLL("PLSQLite.dll","OpenDatabase","mydatabase.sqlite",1,r.dbhandle)
In the descriptions below, the CallDLL part is omitted in anticipation that a future version of Planimate will present the DLL calls in a more streamlined way.
In other words, the DLL functions are described as if they are stand-alone functions. when in fact you need to use CallDLL with the DLL name and function name as the first two parameters, as in the example above.
Return code
Most of the functions in the PLSQLite DLL return a status value which is zero on success or a _dllerrors label value otherwise.
The codes are as defined in the PLDLL.HPP API and are currently:
PLDLL_OK = 0
PLDLL_ERROR = 1
PLDLL_BADFUNCTION = 2
PLDLL_BADPARAMS = 3
PLDLL_MEMORY = 4
PLDLL_BADROUTINECONFIG = 5
PLDLL_BADPARAMCONFIG = 6
These codes are generic codes used by the Planimate DLL framework and typically users of the SQLite DLL will have to deal with errors in their queries etc. These are all returned as PLDLL_ERROR, for which case the modeller should call GetErrorDetail() which is described below.
Data Types
The functions below use the following names to indicate what they expect for parameters. This summarises the corresponding Planimate data types you can use for each.
| Type Name | Planimate Data Example |
|---|---|
| VALUE |
Constant number (IN only) (123) |
| STRING |
Quoted text ("SELECT * FROM") |
| TABLE |
Reference to an entire Planimate table (t.tablename) |
Function Reference
The following are the callable functions/routines that the PLSQlite DLL provides to modellers. The TestDB demo provides excellent examples of the use of these functions from which useful routine code can be copied and pasted into your own model.
OpenDatabase
status = OpenDatabase(IN STRING databaseFilename, IN VALUE allowCreateNew, OUT VALUE databaseHandle)
Opens a sqlite database file for subsequent use. On success it sets databaseHandle (used in most other calls) and returns zero. Failure codes include:
PLDLL_MEMORY - too many databases open or memory allocation failure
PLDLL_ERROR - sqlite error (detail available via GetErrorDetail).
Up to 16 database files may be opened simultaneously. Ensure you do not open the same database file more than once.
CloseDatabase
status = CloseDatabase(IN VALUE databaseHandle)
Closes a previously opened database. If the database handle was not opened this returns PLDLL_BADPARAMS. If a value of 0 is passed, ALL open databases are closed, invalidating all the handles.
GetErrorDetail
sqliteerrorcode = GetErrorDetail()
This returns the sqlite error code which is set in cases where status returned from a just-previous call was PLDLL_ERROR. Do not rely on this value otherwise. This value may indicate a problem in SQL parsing or the preparation of the parameters (eg: bad Planimate table row number or row count return SQLITE_RANGE). These are defined in sqlite.h and part of the sqlite API.
The testsql demo model has a SQLiteErrors label list you may wish to import into your own model.
SQLITE_ERROR 1
SQLITE_INTERNAL 2
SQLITE_PERM 3
SQLITE_ABORT 4
SQLITE_BUSY 5
SQLITE_LOCKED 6
SQLITE_NOMEM 7
SQLITE_READONLY 8
SQLITE_INTERRUPT 9
SQLITE_IOERR 10
SQLITE_CORRUPT 11
SQLITE_NOTFOUND 12
SQLITE_FULL 13
SQLITE_CANTOPEN 14
SQLITE_PROTOCOL 15
SQLITE_EMPTY 16
SQLITE_SCHEMA 17
SQLITE_TOOBIG 18
SQLITE_CONSTRAINT 19
SQLITE_MISMATCH 20
SQLITE_MISUSE 21
SQLITE_NOLFS 22
SQLITE_AUTH 23
SQLITE_FORMAT 24
SQLITE_RANGE 25
SQLITE_NOTADB 26
SQLITE_NOTICE 27
SQLITE_WARNING 28
BeginTransaction and CommitTransaction
status = BeginTransaction(IN VALUE databaseHandle) status = CommitTransaction(IN VALUE databaseHandle)
These enable combining of a number of operations as a single transactional unit. This guarantees an all-or-nothing operation. Transactions also greatly speed up performance of multiple inserts but will use a correspondingly larger amount of memory as more occurs between a begin and commit.
It is recommended that InsertRows() be placed in between BeginTransaction() and CommitTransaction().
GetTableStructure
status = GetTableStructure(IN VALUE databasehandle, IN STRING tablename, OUT TABLE resulttable)
This can be used to retrieve the column names and types for a table in the database.
"resulttable" has rows set to contain a row for every column in the named table. Three columns must be present in "resulttable" before use, with arrangement and type as follows:
Column 1: Text format, will be set to the name of the sqlite table's column.
Column 2: Text format, will be set to the SQL definition type for the column.
Column 3: Value, will be set to the data type of the first row (if any).
SQLite data can differ from the declared column type, which is only a hint. This is transparent to the use of the database in Planimate but will be important to know if you interface to the database directly.
Data types in sqlite are defined in sqlite.h and in the SQLTypes label list in the demo model, as follows:
SQLITE_INTEGER 1
SQLITE_FLOAT 2
SQLITE_TEXT 3
SQLITE_BLOB 4
SQLITE_NULL 5
RunQuery
status = RunQuery(IN VALUE databaseHandle IN STRING querytext, OUT VALUE returnvalue)
Runs a query and is useful for building the database's structure, inserting from manually built queries, getting a count or other operations where at most a single outpui value will suffice.
"returnvalue" may be set to s.NullAttribut if you don't need or care about a return value.
QueryIntoTable
status = QueryIntoTable(IN VALUE databasehandle, IN STRING querytext, OUT TABLE resulttable)
Runs a query which reads data into the provided planimate table.
Columns in the result set are matched by name to columns in the Planimate table, so the order/presence of the columns in the Planimate table does not matter. The "AS" SQL clause may be used to associate a given column with a differently named column in the Planimate table. If no column names match, no data is returned.
SELECT * FROM testData -- matches columns by name SELECT Time AS MyTime, LocationID AS MyLocation, Quantity AS MyQuantity, Notes AS MyNotes, ColorI AS Color FROM testData ORDER BY Time
You can also create new columns which match Planimate columns:
SELECT *,(Quantity * 100) AS UsagePC FROM testData ORDER BY Time
Rows are appeneded and reallocation in resulttable when required. As this is done in stages, memory fragmentation will be a problem if very large tables are read without preallocating the Planimate table.
In some cases it may be worth using RunQuery to execute the query to retrieve the size of the result set first, then reallocate the Planimate table using AllocateTableRowMemory(), then run the query again to retrieve the actual data, eg:
SELECT COUNT(*) FROM testData
QueryIntoRows
status = QueryIntoRows(IN VALUE databasehandle, IN STRING querytext, IN VALUE startRow IN VALUE rowCount OUT TABLE resulttable, OUT VALUE rowsRead)
Runs a query which reads data into the provided Planimate table, like QueryIntoTable. However this does not allocate/append rows, it operates over an existing start row / row count range. "rowsRead" returns the actual number of rows retrieved.
This operation is useful for reading small record sets (eg: from/to data) or "paging in" data from logs for graphing.
Column name matching works the same as QueryIntoTable.
and row limit after which the operation returns.
InsertRows
status = InsertRows(IN VALUE databasehandle, IN STRING querytext, IN VALUE startRow, IN VALUE rowCount, IN TABLE datatable)
Performs repeated inserts over a range of rows. The query should specify an insert operation, table name and the columns to be inserted, for example:
INSERT INTO testData VALUES (?,?,?,?,?,?) INSERT INTO testData (Time,LocationID,Quantity,Notes,Color,ColorI) VALUES ($MyTime,$MyLocation,$MyQuantity,$MyNotes,$Color,@Color)
The first example assumes Planimate and SQL table columns match.
The second example enables Planimate columns (MyTime,MyLocation etc) to be mapped to SQL column names. When naming a Planimate coluimn, if $ prefixes the name, label list columns are processed as text whereas if @ prefixes the name, label list columns are processed as numbers (the label index).
When inserting many rows, there is significant speed benefit to preceeding this operation with a call to BeginTransaction and following it with a call to CommitTransaction.
How Planimate Column Formats are handled
The format of Planimate columns is used to determine how data is transferred by QueryIntoTable,QueryIntoRows and InsertRows.
Dates and Times
Planimate columns which are a date/datetime are rounded to the nearest millisecond, truncated to seconds, then converted to Unix time which counts seconds from 1-Jan-1970. They are then stored in the database as 64 bit integers. To achieve this conversion, the DLL uses Planimate's active Run Start Date since during run that determines the date epoch.
If you are quoting dates directly in SQL statements (including constructing statements with attribute values) you must use strftime() around the quoted date - eg:
SELECT * WHERE OrderDate >= strftime('%s','2015-10-21')
This is an SQL function not Planimate and must appear in the SQL text.
Text
For text columns, the data is handled as text (UTF-8 ASCII).
Label Lists
For label list columns, either text or index may be used as follows
Query: If the column was declared with a type starting with "INT" eg: INTEGER, labels are treated as integer numbers (their label index) otherwise they are treated as text.
Insert: Labels are treated as text unless the SQL placeholder name is the Planimate column name preceeded by @.
Hence both the above must be observed for labels to be inserted and retrieved as integer/index values.
Values, times and ofther format
For all other Planimate formats (times, money etc) floating point numbers are used to write and read from the database.
The declared type of a column is the SQL type used in creating a table. Whilst sqlite does not enforce this type (unlike most databases), as noted above it is used by the interface DLL to determine when to treat labels as index values. In such a case you might use SQL as follows in creating the table:
CREATE TABLE testData (Time INTEGER PRIMARY KEY, LocationID INT NOT NULL, Quantity REAL NOT NULL, Color CHAR[64], -- InsertRows stores labels as text ColorI INTEGER, -- InsertRows stores labels as index Notes CHAR(64))
Note that PRIMARY KEY adds search speed but does not support duplicates. A PRIMARY KEY can also be the combination of columns which might be useful for a FROM/TO pair.