Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic <philipk@posteo.net>]

Lynn Winebarger <owinebar@gmail.com> writes:

> On Tue, Mar 21, 2023 at 7:08 AM Philip Kaludercic <philipk@posteo.net> wrote:
>> 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))
>
> That's a pretty limited notion of interpolating data into code.  Using
> metadata stored in tables and systematically generating queries from
> that metadata is a pretty standard technique even among SQL
> programmers that aren't otherwise inclined to writing recursive macros
> to implement DSLs.

I cannot say, for my intents this has always been enough.

>> > * 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.
>
> Are lisp macros written in terms of string interpolation?  If there
> are no other types of data than strings, fine, but that's not really
> the case - machine instructions have different operations for
> integers/floats/pointers, a good programming abstraction will reflect
> that.  If the underlying machine used strings to represent numbers and
> arithmetic operations took two numeric strings and produced another
> numeric string, maybe there'd be a case to be made (although the first
> point above still mitigates against it).

I really have no idea what you are getting at.

>> 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?
>
> It's just another data structure at that point, so why wouldn't I want
> to be able to include it in my pdmp file?  Why would I want to make my
> internal data structure available as a separate file, or manage
> creating and tracking those files?

My bad, I did not understand that you were talking about dumping in
terms of what temacs does.  Perhaps you could be more clear if you have
a specific example of what you think a in-memory database could be used
for when dumped along with Emacs?

> Maybe having a separate primitive type for a "table" with named
> columns that happens to be represented with a sqlite_statement would
> make the abstraction clearer?