Adding emacsql to NonGNU ELPA?

Adding emacsql to NonGNU ELPA?

[Stefan Kangas]

Hi emacs-devel,

Any objections to adding emacsql to NonGNU ELPA?

    https://github.com/skeeto/emacsql

The reason I am asking is that it duplicates some functionality of our
now built-in sqlite support.  It also adds support for PostgreSQL and
MySQL.  In the future, I'd expect that package to base itself on the
built-in support, but I don't know what their plans are.

It is required by org-roam, another very useful and popular package that
I would like to add.  The org-roam developers are already discussing
using the built-in sqlite support in Emacs 29 and later.

Re: Adding emacsql to NonGNU ELPA?

[Teemu Likonen]

[-- Attachment #1: Type: text/plain, Size: 753 bytes --]

* 2022-01-02 11:16:01-0500, Stefan Kangas wrote:

> Any objections to adding emacsql to NonGNU ELPA?
>
>     https://github.com/skeeto/emacsql

No objections from me, just some notes:

> It also adds support for PostgreSQL and MySQL.

The PostgreSQL support is not very good, though. One of its backend is
external package "pg" which implements PostgreSQL protocol version 1
which is not supported by the current server software anymore. Other
backend is "psql" which uses pipes to interact with command-line client
program "psql".

It seems that MySQL support is similar: no server protocal, just a
command-line tool.

-- 
/// Teemu Likonen - .-.. https://www.iki.fi/tlikonen/
// OpenPGP: 6965F03973F0D4CA22B9410F0F2CAE0E07608462

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 434 bytes --]

Re: Adding emacsql to NonGNU ELPA?

[Lars Ingebrigtsen]

Stefan Kangas <stefan@marxist.se> writes:

> Any objections to adding emacsql to NonGNU ELPA?
>
>     https://github.com/skeeto/emacsql

Sure, seems like a good idea to me.  

---
(emacsql db [:select [name id]
             :from people
             :where (> salary 62000)])
---

We should probably add something like this to Emacs core, though.  I
mean the DSL for writing SQL without strings.  I'm not sure this is
exactly the language we should go for, though, but it generally looks
sound.

-- 
(domestic pets only, the antidote for overdose, milk.)
   bloggy blog: http://lars.ingebrigtsen.no

Re: Adding emacsql to NonGNU ELPA?

[Tim Cross]

Lars Ingebrigtsen <larsi@gnus.org> writes:

> Stefan Kangas <stefan@marxist.se> writes:
>
>> Any objections to adding emacsql to NonGNU ELPA?
>>
>>     https://github.com/skeeto/emacsql
>
> Sure, seems like a good idea to me.  
>
> ---
> (emacsql db [:select [name id]
>              :from people
>              :where (> salary 62000)])
> ---
>
> We should probably add something like this to Emacs core, though.  I
> mean the DSL for writing SQL without strings.  I'm not sure this is
> exactly the language we should go for, though, but it generally looks
> sound.

My only concern is the treatment of 1:1 mapping between elisp nil and
sql null. I'm not sure this is a good idea. In database terms, null is
different from nil (or false). In database terms, null essentially means
'unknown' - it isn't false, it isn't true, it is unknown. Most databases
also have a boolean value - perhaps for elisp, false would be a better
mapping for nil than null?

Regardless, I do agree a good DSL for writing SQL which is not simply a
concatenation of strings would be good.

Another approach which I think works well is the HugSQL model where SQL
is parsed into functions. I quite like this approach as it allows you to
write native SQL and then use the HugSQL library to create functions you
can then just call from your Clojure code. Something similar would
probably work well for elisp as well. One reason I like it is that all
other 'high level' mapping of language to SQL or ORM tool has always
imposed too many limits. Many of them work well with very simple basic
data models, but don't scale well once things become more complex. When
using HugSQL, I did not run into this limitation (or at least, ran into
it much later and less frequently :-). At some point, SQL remains the
best way to write database queries in a RDMS).  

RE: [External] : Re: Adding emacsql to NonGNU ELPA?

[Drew Adams]

> concern is the treatment of 1:1 mapping between elisp nil and
> sql null. I'm not sure this is a good idea. In database terms, null is
> different from nil (or false). In database terms, null essentially means
> 'unknown' - it isn't false, it isn't true, it is unknown. Most databases
> also have a boolean value - perhaps for elisp, false would be a better
> mapping for nil than null?

+1.

(Caveat: I'm not following this thread.)

Good default relations between Lisp nil and SQL NULL,
and good user control over those relations, are very
important.

[The same is true for relations between JSON null and
SQL NULL, BTW.  Sometimes you want to treat them
similarly; sometimes (typically) you don't.  And when
you don't, you want to be able to specify just what
the relation (handling/transformation) is to be.]

Re: Adding emacsql to NonGNU ELPA?

[Tim Landscheidt]

Lars Ingebrigtsen <larsi@gnus.org> wrote:

>> Any objections to adding emacsql to NonGNU ELPA?

>>     https://github.com/skeeto/emacsql

> Sure, seems like a good idea to me.

> ---
> (emacsql db [:select [name id]
>              :from people
>              :where (> salary 62000)])
> ---

> We should probably add something like this to Emacs core, though.  I
> mean the DSL for writing SQL without strings.  I'm not sure this is
> exactly the language we should go for, though, but it generally looks
> sound.

There isn't anything similar for other languages, so why for
SQL?  This DSL would need to be /very/ complicated for it to
be able to express non-trivial SQL statements.

What I dearly yearn for is an equivalent of
shell-quote-argument (or prin1 & Co.) for SQL (and maybe
other languages), e. g. in Emacs Lisp generate a Perl
script, embedding strings that represent data gathered by
Emacs Lisp.

Tim

Re: Adding emacsql to NonGNU ELPA?

[Stefan Monnier]

> There isn't anything similar for other languages, so why for SQL?

Probably because it's arguably much more common to programmatically
generate SQL code, than it is for most other languages (except for HTML,
I guess).


        Stefan

Re: Adding emacsql to NonGNU ELPA?

[Tim Landscheidt]

Stefan Monnier <monnier@iro.umontreal.ca> wrote:

>> There isn't anything similar for other languages, so why for SQL?

> Probably because it's arguably much more common to programmatically
> generate SQL code, than it is for most other languages (except for HTML,
> I guess).

Genuine question: Is it?  My assumption is that SQL code is
generated mostly in ORM libraries, and they often use a syn-
tax that bears no resemblance to SQL.

Tim

Re: Adding emacsql to NonGNU ELPA?

[Stefan Monnier]

Tim Landscheidt [2022-01-27 14:54:07] wrote:
> Stefan Monnier <monnier@iro.umontreal.ca> wrote:
>>> There isn't anything similar for other languages, so why for SQL?
>> Probably because it's arguably much more common to programmatically
>> generate SQL code, than it is for most other languages (except for HTML,
>> I guess).
> Genuine question: Is it?  My assumption is that SQL code is
> generated mostly in ORM libraries, and they often use a syntax that
> bears no resemblance to SQL.

But how do those ORM libraries generate their SQL code?  I would be
surprised if most of them don't use some internal AST-style
representation of SQL as an intermediate step before generating the
actual string to pass to the SQL engine (and I suspect a good proportion
of those that don't harbor nasty security holes).


        Stefan

Re: Adding emacsql to NonGNU ELPA?

[Tomas Hlavaty]

On Thu 27 Jan 2022 at 04:19, Tim Landscheidt <tim@tim-landscheidt.de> wrote:
> There isn't anything similar for other languages, so why for
> SQL?

Of course there is.  Lisp is full of stuff like this.  For example,
there is a cons tree language for regular expressions in Emacs:

   (info "(elisp) Rx Notation")

There are also cons tree bases representations of xml, html, svg etc.

One can conveniently represent almost anything using cons trees.
It is great.

In Lisp, the first thing to do is to escape the concat string
inconvenient and insecure hell and use cons tree based representation
which is very easy to work with.

> This DSL would need to be /very/ complicated for it to
> be able to express non-trivial SQL statements.

On the contrary, it is very simple and allows writing arbitrary SQL
statements.

> What I dearly yearn for is an equivalent of
> shell-quote-argument (or prin1 & Co.) for SQL (and maybe
> other languages), e. g. in Emacs Lisp generate a Perl
> script, embedding strings that represent data gathered by
> Emacs Lisp.

If you need to generate a Perl script, you can create your own cons tree
based DSL which would ensure proper escaping is used in the right
places.  Simply recursively traverse the cons tree and output what you
want in a way you want.

For inspiration, here is what I do to generate PDF:
https://logand.com/sw/emacs-pdf/file/emacs-pdf.el.html#l118

Re: Adding emacsql to NonGNU ELPA?

[Tomas Hlavaty]

On Fri 28 Jan 2022 at 02:22, Tim Landscheidt <tim@tim-landscheidt.de> wrote:

> Tomas Hlavaty <tom@logand.com> wrote:
>
>>> There isn't anything similar for other languages, so why for
>>> SQL?
>
>> Of course there is.  Lisp is full of stuff like this.  For example,
>> there is a cons tree language for regular expressions in Emacs:
>
>>    (info "(elisp) Rx Notation")
>
>> There are also cons tree bases representations of xml, html, svg etc.
>
>> One can conveniently represent almost anything using cons trees.
>> It is great.
>
>> In Lisp, the first thing to do is to escape the concat string
>> inconvenient and insecure hell and use cons tree based representation
>> which is very easy to work with.
>
>> […]
>
> These work for those languages because they have very few
> base elements, much like Lisp; one can probably fit the EBNF
> for XML on a single page, and the transformation will be
> reasonably predictable.
>
> SQL on the other hand almost has a different syntax for ev-
> ery command.  So one would either have to have different
> mappings for "CREATE INDEX", "CREATE UNIQUE INDEX", "CREATE
> INDEX IF NOT EXISTS", "CREATE UNIQUE INDEX IF NOT EXISTS",
> etc., or come up with a generalized version where the pro-
> grammer would probably have to test each time if the SQL ge-
> nerator generated the SQL that the programmer was expecting.
> I find it much more straightforward in that case to write
> down the SQL code itself, and use (ideally placeholders à la
> DBI or) sql-quote-string to only transform what needs to be
> transformed from Emacs Lisp to SQL.

We are talking about cons tree based "meta-syntax".
Similar to how Lisp programs are written using cons tree based
symbolic expression "meta-syntax".
For such "meta-syntax", it is trivial to have a rule for example,
that :create-index is output as CREATE INDEX or
:create-unique-index is output as CREATE UNIQUE INDEX etc.

In addition, you want the syntax to be validated.
That is a different issue.
It can be built on top of the cons tree based "meta-syntax".
In this case there is no simple way to validate arbitrary
SQL upfront except encoding the complex SQL rules explicitly
somehow.  And do it for each SQL dialect.
Cons tree based "meta-syntax" does not solve validation
in itself, but helps here significatly,
because one can use backquote and comma to build the tree
programatically and validate the structure in a recursive function.
This is much more convenient than concatenating strings.
Moreover, it is possible to make it extendable,
similar to what Lisp does with macros.

It is similar to what Lisp does.  Write symbolic expressions
and the interpreter/compiler does the validation/transformation.
Write a function which takes cons tree and outputs what you want.
The more validation you want, the more complex such function will be.