Re: [NonGNU ELPA] New package: sqlite3

[Lynn Winebarger]

On Wed, Mar 22, 2023 at 11:05 AM Lynn Winebarger <owinebar@gmail.com> wrote:
>
> On Wed, Mar 22, 2023, 4:10 AM Philip Kaludercic <philipk@posteo.net> wrote:
>>
>> Lynn Winebarger <owinebar@gmail.com> writes:
>>
>> > On Tue, Mar 21, 2023 at 12:53 PM Philip Kaludercic <philipk@posteo.net> 
>> > wrote:
>> >> I really, really have no idea what you are getting at.  As in "ok, but
>> >> what is your intent in explaining this?".
>> >>
>> >> Are you trying to propose that Emacs circumvents the SQLite API (that as
>> >> far as I see uses strings) by constructing statement objects manually?
>> >
>> > Not at all.  I don't think I can communicate via email the power of
>> > generative programming techniques, and why basing them on simple
>> > string concatenation is a bad idea, so I'm going to stop trying.
>>
>> I get that, and I am not advocating for string concatenation.  Perhaps
>> that is what is confusing me?
>>
>> > I don't think "? ? table values ( 1.0, 'Foo' )" can be supplied with
>> > 'insert and 'into as parameters.
>>
>> Nor do I, but I doubt the necessity.  SQL is a very brittle language,
>> and replacing one keyword with another will usually require other
>> changes to be made as well.
>
>
> Exactly the point of a DSL that compiles to a query.
>
> Whether emacsql is the best DSL or not, I don't know.  I really haven't used 
> it.  It has the distinct advantage of existing and providing a syntax tree of 
> the query, which are strong points.
>

I gave this some more thought after starting to sketch a sql sexpr ->
string compiler.  SQL is non-compositional (as you put it, "brittle"),
so just encoding SQL syntax trees as sexprs is not particularly
empowering.

A lot of the ugliness (non-compositionality) arises from embedding
properties of the expected instantiation of tables (e.g. keys,
indexing) as part of the query.  To get a compositional querying DSL,
it's probably better to have three orthogonal but mutually recursive
types of expressions, one for specifying the table schema, which may
incorporate queries in its formation, which specify the shape of a
table, and another for specifying "queries" (select - or just "from"
as an operator, join, where, let ["with", as etc], labels ["with
recursive"], etc) that will instantiate the data set in one form or
another, and the third for "commands" that perform side-effects on the
database.  There's probably a set of these operators that can be
cleanly composed in mostly arbitrary ways with clear semantics.  Then
those expressions could be compiled to SQL, letting the compiler
handle optimization as dictated by experience.

There's also the matter of how to store lisp data transparently in
data tables.  Storing pointers directly will mean the garbage
collector will have to know which entries have LispObjects as values
and how to trace them.  And you wouldn't want to do that unless the
database was in memory.

I'm not going to put that before my other projects though, so I may
just be using some fixed string queries to get things going with
unboxed, which has fairly modest needs in terms of table schemas.

Lynn