I have been using this version for about half a year and it seems stable. I cannot tell if it is useful to anyone but me but then again, you never know. It offers a distinct set of features not available in prosqlite. Most importantly, it offers bind variables for your SQL queries. You can find SQLite-specific information in the repository.
See the README for a justification for the existence of this library.
You can install it with the package manager:
?- pack_install(swiplite).
This will download it and attempt to compile the foreign code. You need a recent SQLite installed on your system, and a C compiler. Currently, only Linux and MacOS are supported. For MacOS I strongly recommend installing SQLite from Homebrew since MacOS comes with SQLite, but the version is both out-of-date and compiled weirdly (probably for backwards compatibility).
It should be possible to use the SQLite amalgamation for a custom build, please see the README and the code.
Once you install it, you can load it and do a smoke test:
?- use_module(library(sqlite)).
true.
?- sqlite_version(V).
V = '3.53.0'.
Nice. Since you can bind variables (e.g. parametrized sql) I would suggest you run fast_term_serialized/2 and store it as a blob in sqlite. That is much better than stringifying.
Hi, thank you for the interest and the suggestion. I have considered many different possibilities over the last year but I don’t think I considered this in particular. Much appreciated!
The one biggest hurdle has been the lack of another user which basically means that I have had to make uninformed design choices and then also implement them.
Which specific data types do you think should be treated like this?
PS: I seem to remember that very early on I thought I could treat all Prolog terms like this. I do not remember why I changed my mind. Maybe back then I wanted to be able to inspect the database without any Prolog? Hmm… I need to think about it and get back to you.
PPS: the reason for doing any of this was to use relational database features that are not immediately available in SWI-Prolog: disk persistence by default, and more importantly, referential integrity through a (somewhat) normalized schema and foreign keys. This is why the current default for swiplite is to enable foreign keys unless the client code insists otherwise. (Another symptom is how I have differentiated in the interface between “no results”, “one result”, and “many results” queries. I don’t think many programmers would consider this a good design.)
It is certainly possible to do it if the schema doesn’t know about the data but I am guessing that early on I decided this is going to be difficult.
This whole thing ended up being a hobby project and it really shows I shared it publicly for reasons I do not fully understand myself.
It doesn’t seem that term reading and writing is ever going to be the bottleneck in anything I do. In my experience with this hobby library of mine, swiplite, the only bottleneck is writing to disk and the inevitable locks. Doing many inserts with “autocommit”; vs. putting them in a transaction is the only “optimization” that seems to matter.
which basically means that I have had to make uninformed design choices and then also implement them.