Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic <philipk@posteo.net>]

Lynn Winebarger <owinebar@gmail.com> writes:

> On Tue, Mar 21, 2023 at 3:22 AM Jean Louis <bugs@gnu.support> wrote:
>> * Philip Kaludercic <philipk@posteo.net> [2023-03-14 19:17]:
>> > Jonas Bernoulli <jonas@bernoul.li> writes:
>> >
>> > >> Do you have a link to the package you are talking about?
>> > >
>> > > Ups, here you go: https://github.com/pekingduck/emacs-sqlite3-api
>> >
>> > Would you happen to know if there is some rx-like, s-expression based
>> > language for constructing SQL queries.  I am not looking for anything
>> > generic, just a way to avoid writing long strings.
>>
>> While such packages exists, for me I do not find them usable as then I
>> have to forget about the SQL and learn about the new Emacs Lisp
>> structure that is to correspond to SQL. I see personally no benefit in
>> that.
>
> There are a couple of good reasons to use an sexpr-based query language:
> * Avoiding sql injection issues by putting all the boilerplate for
> interpolating data into queries into a macro expander

To be fair, this is not a concern because SQLite supports parameterised
queries:

  (sqlite-execute db "insert into foo values (?, ?)" '("bar" 2))

> * Treating code as data and vice-versa is a powerful programming technique

Not sure about this.... Strings are data too, but neither the SQL
statements or the regular expressions are (Elisp) code.  To me the
advantage of something like `rx' is that I can insert comments and make
use of regular indentation.  Then again, it would also be possible to
provide specialised SQLite wrappers (sqlite-insert, sqlite-update, ...)
instead of taking a `rx' like approach to generating strings.

> The real power of embedding sqlite in elisp will come when sqlite data
> structures can be used as efficient representations of sets and
> relations in lisp code.  Eventually, I would also expect to see
> mutually recursive code enabled, with "virtual table" modules for
> emacs data structures so they can be transparently used in sql code,
> along with sql functions written in lisp.  For example, you might
> create a table from lisp data using a select statement rather than
> executing a large number of insert statements.  In-memory databases
> would not be unusual, and should be dumpable objects.  

What is the point of using a in-memory database if you want to dump it?

>                                                        At that point,
> you could expect to see such objects frequently used, e.g. for tag
> tables, user configuration, abstract interpretation of lisp code, etc.