Infinite LiveCode – The First Stretch Goal – SQLite Library

by Mark Waddingham on May 20, 2016 3 comments

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.

Mark WaddinghamInfinite LiveCode – The First Stretch Goal – SQLite Library

Related Posts

Take a look at these posts

3 comments

Join the conversation
  • Mark Smith - May 20, 2016 reply

    I’m hoping we get to the 2nd stretch goal and I hope it’s wrapping a native field object. Let’s go!!!

  • Quentin Brown - May 21, 2016 reply

    This library is essential for the platform as a whole and I hope the stretch goal is exceeded, but wasn’t it already funded as part of the open source stretch goals already? (Albeit not in this specific form). I’ve been waiting for this since I donated to the Kickstarter campaign for Livecode open source and haven’t even used my license which has run for years now whilst waiting. Once builder is complete and has this library integrated (and hopefully support for using as much as possible the same commands to agnostically access other Databases like MySQL, PostgreSQL etc.)
    Also I think openDatastores() is a poor choice for function naming and too easily confused with openDatastore() using two very different applications of the word open. It would be much clearer to use something like listactiveDatastores() or listopenDatastores.

    Quentin Brown - May 21, 2016 reply

    Lost my trail of thought in the middle there…
    Once builder 2.0 is complete, well documented and database access is built in (a new list box module with built in database integration would also be a major leg up) then I can start thinking about learning it and wrapping some API’s I would need to use in the suite of desktop and mobile apps I have been planning for ages.
    A local to/from cloud database syncing library would also be very helpful – such a core feature of many multi platform apps why should everyone have to code their own one?

Join the conversation

*