Infinite LiveCode – The First Stretch Goal – SQLite Library

SQLite databases are used ubiquitously in modern applications. Whether they be used as caches for downloaded data (so apps can run offline), as a file format for document editing apps or just for persistent data storage. Indeed, we feel that they are so important that they deserve their own clean, modern, high-level set of functions in LiveCode Script to access directly – rather than going through revDB.

For this Infinite LiveCode stretch goal we propose to:

• Provide gyp-based descriptions of building the SQLite native code library.
• Wrap the SQLite API using Infinite LiveCode to allow it to be used directly from Builder
• Write a Builder module which allows usage of SQLite databases easily from Builder
• Write a Builder library module which exposes a high-level API for manipulating SQLite databases directly from Script

This work will mean that not only will you be able to use an improved set of functions to manipulate SQLite databases from LiveCode Script, but you will also be able to access them from LiveCode Builder – allowing widgets and libraries to be written which use SQLite ‘behind the scenes’.

Further to the actual code, we will also make this feature ‘fully worked’ – we’ll provide associated documentation and training materials explaining how it all works so that you can start learning how to implement similar things yourselves. You will be able to take the principles explained in this example and apply them to wrap other C libraries using Infinite LiveCode.

Finally, remember, that this will all be open source code, built and included in the community edition – you will be able to take it, adapt it and help improve it!

Here is an idea of the functions we’ll add at the LiveCode Script level that you’ll be able to use directly in your applications.

Datastore Lifecycle Management

openDatastore <dbFilename>, [ <tag> ]

Open an sqlite database present in the specified file – you can optionally name the datastore for referencing via the other datastore functions (otherwise the filename will be the name).

closeDatastore <datastore>

Close the specified SQLite database

openDatastores()

Return a list of the names of all the currently open datastores.

SQL Statement Execution

doInDatastore <datastore>, <sql>, ...
doInDatastoreIndirect <datastore>, <sql>, <parameterArray> 

Compile an SQL statement, with optional parameterized bindings and execute in the specified datastore. The first form will lookup placeholders using indices into the passed parameters, the second form will lookup placeholders from the passed array.

SQL Query Execution

fetchFromDatastore(<datastore>, <sql>, ....)
fetchFromDatastoreIndirect(<datastore>, <sql>, <parameterArray>)

Perform a query on the given datastore, reading all the results and returning a numerically keyed array of array – one element for each returned row. Each row will be returned as an array mapping column names to data.

e.g.

fetchFromDatastore(tDatastore, "SELECT foo, bar FROM foobartable")

might return

[1]["foo"] = "hello"
[1]["bar"] = "world"
[2]["foo"] = "goodbye"
[2]["bar"] = "world"

Iterated SQL Query Execution

startQueryInDatastore <datastore>, <sql>, ...
startQueryInDatastoreIndirect <datastore>, <sql>, <parameterArray> 
continueQueryInDatastore(<datastore>)
currentRowForQueryInDatastore(<datastore>)
finishQueryInDatastore <datastore>

These functions allow iterated access over a SQL query rather than consuming the entire set of results all at once. They are designed to be used in a loop like:

startQueryInDatastore tDatastore, tQuery, ...
repeat while continueQueryInDatastore(tDatastore)
	get currentRowForQueryInDatastore(tDatastore)
	... here it will be an array mapping column names to values ...
end repeat
finishQueryInDatastore tDatastore

This is a much cleaner, simpler API than we currently have via revDB, and by taking advantage of Builder features – such as being able to directly manipulate LiveCode Script arrays, strings and data we can ensure there’s no more need to pass names of variables, or use binary data prefixes, or any of the other annoyances you need to know about to work with revDB!

This is going to be a good implementation. And yet if you find you need something we didn’t include, it should just be a case of editing the LCB file to add it.

We only need $7k more to ensure this stretch goal is met. Pledge for infinite LiveCode today.