[NonGNU ELPA] New package: sqlite3

[NonGNU ELPA] New package: sqlite3

[Jonas Bernoulli]

Sqlite3 provides a module for SQLite.  It comes with the Elisp library
necessary for the metadata and to compile the module on first use.

The new SQLite support in Emacs 29 was inspired by this module.

I have contacted the author and he agrees with adding his package here.

The motivation for adding this package now is that the next EmacSQL
release is going to depend on it.  EmacSQL is already on NonGNU-devel
ELPA, but the upcoming 4.0.0 will be the first release available from
the stable channel.

This EmacSQL release adds two new back-ends, one using the new builtin
SQLite support, and the other using this module.  The back-end using
builtin support is preferred but requires Emacs 29, compiled with SQLite
support.  If that is not available, the module back-end should be used.
The old back-end, which uses a custom SQLite binary, remains but should
only be used if all other options are exhausted.

If there are no objections or questions, I will add this in a few days.

     Cheers,
     Jonas

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

Jonas Bernoulli <jonas@bernoul.li> writes:

> Sqlite3 provides a module for SQLite.  It comes with the Elisp library
> necessary for the metadata and to compile the module on first use.
>
> The new SQLite support in Emacs 29 was inspired by this module.
>
> I have contacted the author and he agrees with adding his package here.
>
> The motivation for adding this package now is that the next EmacSQL
> release is going to depend on it.  EmacSQL is already on NonGNU-devel
> ELPA, but the upcoming 4.0.0 will be the first release available from
> the stable channel.
>
> This EmacSQL release adds two new back-ends, one using the new builtin
> SQLite support, and the other using this module.  The back-end using
> builtin support is preferred but requires Emacs 29, compiled with SQLite
> support.  If that is not available, the module back-end should be used.
> The old back-end, which uses a custom SQLite binary, remains but should
> only be used if all other options are exhausted.
>
> If there are no objections or questions, I will add this in a few days.

Do you have a link to the package you are talking about?

>      Cheers,
>      Jonas

-- 
Philip Kaludercic

Re: [NonGNU ELPA] New package: sqlite3

[Richard Stallman]

[[[ To any NSA and FBI agents reading my email: please consider    ]]]
[[[ whether defending the US Constitution against all enemies,     ]]]
[[[ foreign or domestic, requires you to follow Snowden's example. ]]]


  > Sqlite3 provides a module for SQLite.  It comes with the Elisp library
  > necessary for the metadata and to compile the module on first use.

  > The new SQLite support in Emacs 29 was inspired by this module.

  > I have contacted the author and he agrees with adding his package here.

Did the developer agree to maintain it so that it continues to follow
the NonGNU ELPA rules?

There are two ways to put a package into NonGNU ELPA.  One updates
automatically from the developer's repo -- that we can use if the
developer agrees to maintain it in accord with the NonGNU ELPA rules.

The other requires us to look at each new version and manually copy it
into NonGNU ELPA.  That doesn't require any specific cooperation from
the developer, but the downside is that it requires extra work for us.

-- 
Dr Richard Stallman (https://stallman.org)
Chief GNUisance of the GNU Project (https://gnu.org)
Founder, Free Software Foundation (https://fsf.org)
Internet Hall-of-Famer (https://internethalloffame.org)

Re: [NonGNU ELPA] New package: sqlite3

[Jonas Bernoulli]

> Do you have a link to the package you are talking about?

Ups, here you go: https://github.com/pekingduck/emacs-sqlite3-api

Re: [NonGNU ELPA] New package: sqlite3

[Jonas Bernoulli]

Richard Stallman <rms@gnu.org> writes:

>   > Sqlite3 provides a module for SQLite.  It comes with the Elisp library
>   > necessary for the metadata and to compile the module on first use.
>
>   > The new SQLite support in Emacs 29 was inspired by this module.
>
>   > I have contacted the author and he agrees with adding his package here.
>
> Did the developer agree to maintain it so that it continues to follow
> the NonGNU ELPA rules?

They have done that now.

> There are two ways to put a package into NonGNU ELPA.  One updates
> automatically from the developer's repo -- that we can use if the
> developer agrees to maintain it in accord with the NonGNU ELPA rules.

So I am using that approach.

> The other requires us to look at each new version and manually copy it
> into NonGNU ELPA.  That doesn't require any specific cooperation from
> the developer, but the downside is that it requires extra work for us.

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

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.

Re: [NonGNU ELPA] New package: sqlite3

[Jonas Bernoulli]

Philip Kaludercic <philipk@posteo.net> writes:

> 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.

Does the mentioned EmacSQL not fit bill?

https://github.com/magit/emacsql

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

Jonas Bernoulli <jonas@bernoul.li> writes:

> Philip Kaludercic <philipk@posteo.net> writes:
>
>> 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.
>
> Does the mentioned EmacSQL not fit bill?
>
> https://github.com/magit/emacsql

Sorry, I missed that.

Re: [NonGNU ELPA] New package: sqlite3

[Jean Louis]

* 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.

Often, I tend to do 2 steps SQL, one for preliminary query, where I
fetch only the list of items as in this example:

(defun cf-people-by-last-name (&optional last-name)
  "Return list of people by last name."
  (interactive)
  (let* ((last-name (or last-name (rcd-ask-get "Last name: ")))
	 (id-list (rcd-sql-list "SELECT people_id FROM people WHERE people_name ~* $1" cf-db last-name))
	 (title (format "People by last name `%s'" last-name)))
    (cf-people-by-id-list id-list title)))

And then the other one, more complex which is used for the final
report, and from which I only cut this one part:

(sql (format "SELECT DISTINCT ON (people_id) 
                                           people_id, get_full_contacts_name(people_id), 
                                           coalesce((SELECT get_contacts_name(peoplerelations_people2) 
                                                       FROM peoplerelations 
                                                      WHERE peoplerelations_people1 = people_id 
                                                   ORDER BY peoplerelations_default, peoplerelations_id LIMIT 1),
                                                     '>>>UNKNOWN<<<') 
                                       FROM people 
                                      WHERE people_id IN (%s) 
                                   ORDER BY people_id DESC"
                                           sql-id-list))

By using the two step approach, first selecting IDs necessary, and
then running the main report using those ID numbers, I avoid writing
larger single functions.

In general SQL is so much simpler and logically understandable as
compared to Emacs Lisp, writing SQL is beneficial as then you get the
habit and can run queries in any other programming language, or
directly on command line or in programs, first making sure you are
doing it right.

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/

Re: [NonGNU ELPA] New package: sqlite3

[Lynn Winebarger]

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
* Treating code as data and vice-versa is a powerful programming technique

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.  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.

Lynn

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

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.

Re: [NonGNU ELPA] New package: sqlite3

[Lynn Winebarger]

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.


>
> > * 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).

> 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?

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?

Lynn

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

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?

Re: [NonGNU ELPA] New package: sqlite3

[Lynn Winebarger]

On Tue, Mar 21, 2023 at 8:18 AM Philip Kaludercic <philipk@posteo.net> wrote:
> Lynn Winebarger <owinebar@gmail.com> writes:
>
> >> > * 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.

The reason for not trying to construct SQL from strings (in macros or
other programmatic ways) is the same reason lisp and other dynamically
typed languages don't just treat every value as a string.   The lisp
macro expander doesn't create a string to pass to eval - why would you
want to turn everything into a string for it to be parsed again, and
possibly introduce errors along the way?  If I have a double that
happens to be expressible as an integer in text, will the system
ensure the generated query uses and returns values as doubles?

If the machine only had string data types (I don't know how that would
work, it would be radically different from any architecture I'm
familiar with), then there would be an argument for only having
strings as primitive values, even in general purpose languages like
lisp.

>
> >> 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.
Also for redumping with dump-emacs-portable.

> [...]  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?

*  Anywhere a large association list or hash table is currently used
*  Caching library locations, checksums, and modification times for
more efficient loading
*  Tracking customization variables, dependencies, etc for generating
the correct sequence of initialization commands at startup
(particularly after redumping)

I'm sure there's more, but we won't know until the programming idiom
is readily available and easy to use.

Lynn

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

Lynn Winebarger <owinebar@gmail.com> writes:

> On Tue, Mar 21, 2023 at 8:18 AM Philip Kaludercic <philipk@posteo.net> wrote:
>> Lynn Winebarger <owinebar@gmail.com> writes:
>>
>> >> > * 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.
>
> The reason for not trying to construct SQL from strings (in macros or
> other programmatic ways) is the same reason lisp and other dynamically
> typed languages don't just treat every value as a string.   The lisp
> macro expander doesn't create a string to pass to eval - why would you
> want to turn everything into a string for it to be parsed again, and
> possibly introduce errors along the way?  If I have a double that
> happens to be expressible as an integer in text, will the system
> ensure the generated query uses and returns values as doubles?
>
> If the machine only had string data types (I don't know how that would
> work, it would be radically different from any architecture I'm
> familiar with), then there would be an argument for only having
> strings as primitive values, even in general purpose languages like
> lisp.

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?

>> >> 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.
> Also for redumping with dump-emacs-portable.
>
>> [...]  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?
>
> *  Anywhere a large association list or hash table is currently used
> *  Caching library locations, checksums, and modification times for
> more efficient loading
> *  Tracking customization variables, dependencies, etc for generating
> the correct sequence of initialization commands at startup
> (particularly after redumping)

Are we sure that a database is more efficient than a hash-table (which
can already be printed and read)?  Or are we talking about unusually
extreme values, like in your other message where you were loading 2000+
packages?

> I'm sure there's more, but we won't know until the programming idiom
> is readily available and easy to use.

Are there any other languages that support this kind of interaction,
where we could learn some lessons about the advantages and limits of
these ideas?

> Lynn

-- 
Philip Kaludercic

Re: [NonGNU ELPA] New package: sqlite3

[Tomas Hlavaty]

On Tue 21 Mar 2023 at 09:51, Jean Louis <bugs@gnu.support> wrote:
> 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.
[...]
> (sql (format "SELECT DISTINCT ON (people_id) 
        ^
        sql injection danger right there

Using strings is a bad idea.  You have to manually ensure every such use
is properly escaped.  If you used sexp and let elisp do the conversion,
every such usage could be automatically properly escaped.

Also using sexp does not mean you cannot use plain sql:

   (sql `(SELECT DISTINCT ON (people_id)

Re: [NonGNU ELPA] New package: sqlite3

[Tomas Hlavaty]

On Tue 21 Mar 2023 at 11:08, Philip Kaludercic <philipk@posteo.net> wrote:
> To be fair, this is not a concern because SQLite supports parameterised
> queries:
>
>   (sqlite-execute db "insert into foo values (?, ?)" '("bar" 2))

Not sure about sqlite but I know that for example in postgresql, not
everything is parametrizeable this way.

> To me the
> advantage of something like `rx' is that I can insert comments and make
> use of regular indentation.

Those are cosmetic advantages.
There are more profound advatages.

Re: [NonGNU ELPA] New package: sqlite3

[Tomas Hlavaty]

On Tue 21 Mar 2023 at 16:53, 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?

The idea is that one should not concatenate strings by hand but one
should write the query as sexp (likely build that cons tree using quote
or backquote).  That cons tree should then be converted to string by a
lisp function.  Only after that should the string be passed to sqlite.

   sexp (cons tree) -> string -> sqlite

Re: [NonGNU ELPA] New package: sqlite3

[Tomas Hlavaty]

On Tue 21 Mar 2023 at 21:12, Philip Kaludercic <philipk@posteo.net> wrote:
>>> To me the
>>> advantage of something like `rx' is that I can insert comments and make
>>> use of regular indentation.
>>
>> Those are cosmetic advantages.
>> There are more profound advatages.
>
> In what way profound?

For example, the Lisp environment provides many tools that understand
and help with lisp code (jumping, help, autodoc, compilation, warnings,
errors debugging etc).  With strings, one cannot take advantage of any
those.  Another: making sure that things have the right structure, make
sense to some extent and output is properly escaped.  It is also much
easier to process or transform such cons tree than process or transform
a string with some kind of syntax.

> Tomas Hlavaty <tom@logand.com> writes:
>> On Tue 21 Mar 2023 at 16:53, 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?
>>
>> The idea is that one should not concatenate strings by hand but one
>> should write the query as sexp (likely build that cons tree using quote
>> or backquote).  That cons tree should then be converted to string by a
>> lisp function.  Only after that should the string be passed to sqlite.
>>
>>    sexp (cons tree) -> string -> sqlite
>
> I was under the impression that Lynn was advocating for avoiding the
> usage any strings.  In general this is nice and I'd use it if built-in,
> but seeing as SQL is more readable than regular expressions I am not
> under the impression that there is the same need for it.

The point is that it is better to use an sexp based syntax than string
based syntax in both cases, for sql and for regular expressions.  This
is a general idea, one can apply it to html, css, xml, pdf, docx, xlsx,
ooxml etc.  anywhere where one needs to output some kind of arbitrary
(non-sexp based) syntax.

The fact that in the end a string is passed to sqlite is not
interesting.  Interesting is that one writes sql not by concatenating
strings but by building cons trees, similar to what one does when
writing lisp code.

Re: [NonGNU ELPA] New package: sqlite3

[Lynn Winebarger]

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.

You're correct that strings are used in representing the queries, but
the values that are stored and returned via the C api are strongly
dynamically typed.  BTW, I am not advocating avoiding the use of any
strings in interacting with sqlite.  That's impossible, since queries
are ultimately presented to the sqlite engine as text (possibly with
additional explicit values).  But in lisp, identifiers and keywords
are just values, too.  I don't think "? ? table values ( 1.0, 'Foo' )"
can be supplied with 'insert and 'into as parameters.

> Are we sure that a database is more efficient than a hash-table (which
> can already be printed and read)?  Or are we talking about unusually
> extreme values, like in your other message where you were loading 2000+
> packages?

Who determines what is extreme?  Tasks that aren't done today because
they are difficult to code efficiently?  Tasks that seem extreme when
you write the code in direct style may become much less extreme once a
well-crafted table/query facility is available.  I don't think simply
*installing* 2000+ packages is all that extreme in itself.  Even
loading all those packages, particularly when using redumping, is not
particularly extreme in terms of resource consumption on modern
desktop hardware.

Hash tables only index a single key of a data set.  And they don't
address tasks like efficiently joining tables.

My personal interests run to using relational programming for problems
like abstract interpretation and compiler implementation.  I'm sure
there are many applications for problems Emacs is used to solve, e.g.
tracking cross-references, tag tables, etc.

>
> > I'm sure there's more, but we won't know until the programming idiom
> > is readily available and easy to use.
>
> Are there any other languages that support this kind of interaction,
> where we could learn some lessons about the advantages and limits of
> these ideas?

You might consider the LINQ sublanguage of C# and other .NET-based
languages as an example of a useful query DSL.


Lynn

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

Tomas Hlavaty <tom@logand.com> writes:

> On Tue 21 Mar 2023 at 21:12, Philip Kaludercic <philipk@posteo.net> wrote:
>>>> To me the
>>>> advantage of something like `rx' is that I can insert comments and make
>>>> use of regular indentation.
>>>
>>> Those are cosmetic advantages.
>>> There are more profound advatages.
>>
>> In what way profound?
>
> For example, the Lisp environment provides many tools that understand
> and help with lisp code (jumping, help, autodoc, compilation, warnings,
> errors debugging etc).  With strings, one cannot take advantage of any
> those.  Another: making sure that things have the right structure, make
> sense to some extent and output is properly escaped.  It is also much
> easier to process or transform such cons tree than process or transform
> a string with some kind of syntax.

It might be easier, but it certainly possible to have the tools that
take advantage of lisp code also understand specific strings.  This is
not what I would call "profound".

>> Tomas Hlavaty <tom@logand.com> writes:
>>> On Tue 21 Mar 2023 at 16:53, 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?
>>>
>>> The idea is that one should not concatenate strings by hand but one
>>> should write the query as sexp (likely build that cons tree using quote
>>> or backquote).  That cons tree should then be converted to string by a
>>> lisp function.  Only after that should the string be passed to sqlite.
>>>
>>>    sexp (cons tree) -> string -> sqlite
>>
>> I was under the impression that Lynn was advocating for avoiding the
>> usage any strings.  In general this is nice and I'd use it if built-in,
>> but seeing as SQL is more readable than regular expressions I am not
>> under the impression that there is the same need for it.
>
> The point is that it is better to use an sexp based syntax than string
> based syntax in both cases, for sql and for regular expressions.  This
> is a general idea, one can apply it to html, css, xml, pdf, docx, xlsx,
> ooxml etc.  anywhere where one needs to output some kind of arbitrary
> (non-sexp based) syntax.
>
> The fact that in the end a string is passed to sqlite is not
> interesting.  Interesting is that one writes sql not by concatenating
> strings but by building cons trees, similar to what one does when
> writing lisp code.

I agree that we should strings ought not to be concatenated.  My issue
is that I am under the impression that constructing SQL statements from
s-expressions is more complicated than it is to construct regular
expressions, and I don't know if it is worth the effort or if
parameterised queries suffice?

Re: [NonGNU ELPA] New package: sqlite3

[Philip Kaludercic]

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.

>> Are we sure that a database is more efficient than a hash-table (which
>> can already be printed and read)?  Or are we talking about unusually
>> extreme values, like in your other message where you were loading 2000+
>> packages?
>
> Who determines what is extreme?  

Experience and convention?  There is no algorithm to determine this, but
before 2000 the highest number of Emacs packages I heard someone was
using was maybe 300-400 (which I also think is an absurd number).

>                                  Tasks that aren't done today because
> they are difficult to code efficiently?  Tasks that seem extreme when
> you write the code in direct style may become much less extreme once a
> well-crafted table/query facility is available.  I don't think simply
> *installing* 2000+ packages is all that extreme in itself.  Even
> loading all those packages, particularly when using redumping, is not
> particularly extreme in terms of resource consumption on modern
> desktop hardware.
>
> Hash tables only index a single key of a data set.  And they don't
> address tasks like efficiently joining tables.
>
> My personal interests run to using relational programming for problems
> like abstract interpretation and compiler implementation.  

In Elisp?

>                                                            I'm sure
> there are many applications for problems Emacs is used to solve, e.g.
> tracking cross-references, tag tables, etc.
>
>> > I'm sure there's more, but we won't know until the programming idiom
>> > is readily available and easy to use.
>>
>> Are there any other languages that support this kind of interaction,
>> where we could learn some lessons about the advantages and limits of
>> these ideas?
>
> You might consider the LINQ sublanguage of C# and other .NET-based
> languages as an example of a useful query DSL.

As far as I understand (I have no experience with .NET-based languages),
this is only syntax sugar?  Or how does this relate to the point of
dumping an in-memory database.

Re: [NonGNU ELPA] New package: sqlite3

[Lynn Winebarger]

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

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.

>> Are we sure that a database is more efficient than a hash-table (which
> >> can already be printed and read)?  Or are we talking about unusually
> >> extreme values, like in your other message where you were loading 2000+
> >> packages?
> >
> > Who determines what is extreme?
>
> Experience and convention?  There is no algorithm to determine this, but
> before 2000 the highest number of Emacs packages I heard someone was
> using was maybe 300-400 (which I also think is an absurd number).
>

I don't know why it's absurd.  There are ~300 packages in gnu elpa, ~200 in
nongnu elpa, and over 5000 in melpa.
The vast majority are single files.

My experiments have shown that a substantial part of the pain of adding
packages is simply due to the cost of extending the load path.  I really
question how much of the effort in these configuration management systems
and specialized configs like doomemacs is prompted by the inordinate cost
of the extending the load path just to add a one-file package.

I can report from experience that most packages can be simultaneously
loaded and work fine as long as conflicting modes are not simultaneously in
effect.


> >                                  Tasks that aren't done today because
> > they are difficult to code efficiently?  Tasks that seem extreme when
> > you write the code in direct style may become much less extreme once a
> > well-crafted table/query facility is available.  I don't think simply
> > *installing* 2000+ packages is all that extreme in itself.  Even
> > loading all those packages, particularly when using redumping, is not
> > particularly extreme in terms of resource consumption on modern
> > desktop hardware.
> >
> > Hash tables only index a single key of a data set.  And they don't
> > address tasks like efficiently joining tables.
> >
> > My personal interests run to using relational programming for problems
> > like abstract interpretation and compiler implementation.
>
> In Elisp?
>

Eventually, sure - for elisp itself, anyway.    It's a longer term project
for me, though.

> You might consider the LINQ sublanguage of C# and other .NET-based
> > languages as an example of a useful query DSL.
>
> As far as I understand (I have no experience with .NET-based languages),
> this is only syntax sugar?


"Syntactic sugar" should be reserved for syntax that the compiler
immediately transforms into a simpler syntactic structure.  Otherwise any
DSL can be shrugged off as syntactic sugar.  I don't know the
implementation details, but it's an example of a query DSL integrated into
an otherwise imperative/OO paradigm language.


Or how does this relate to the point of
> dumping an in-memory database.
>

I just think it's a powerful paradigm that will eventually be utilized in
core tasks involved in managing emacs itself, like reasoning about
customization variables and relationships between them.  Things that aren't
done now because doing them without a relational language to take care of
the details is painful as error prone.
Once those kinds of tables are in use at startup, why wouldn't you want to
include them in pdmp file?  Particularly if you record pointers to objects
as integers in the database - the dumper/loader will be needed to ensure
those remain consistent.

Lynn

[-- Attachment #2: Type: text/html, Size: 6990 bytes --]

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

Re: [NonGNU ELPA] New package: sqlite3

[Jean Louis]

* Tomas Hlavaty <tom@logand.com> [2023-03-22 00:03]:
> The idea is that one should not concatenate strings by hand but one
> should write the query as sexp (likely build that cons tree using quote
> or backquote).  That cons tree should then be converted to string by a
> lisp function.  Only after that should the string be passed to sqlite.
> 
>    sexp (cons tree) -> string -> sqlite

I have got 2027 SELECT statements, 463 INSERT statements, and 81
UPDATE statements in Emacs Lisp, mostly for PostgreSQL, little less
for SQLite3.

There are many string concatenations, and I never had a visual problem
with it. Maybe it depends on various styles of programming.

Example:
--------

(defun rcd-db-words-list-translations (&optional id)
  "List translations of the table `words'"
  (interactive)
  (when-tabulated-id "words"
      (let ((sql (format "SELECT translations_id, languages_name, words_value, translations_translation FROM translations, languages, words WHERE words_id = translations_words AND languages_id = translations_languages AND translations_words = %s" id)))
	(rcd-db-sql-report "Translations" sql [("ID" 4 t) ("Language" 30 t) ("Words" 30 t) ("Translations" 30 t)] "translations" nil 'rcd-db-words-list-translations))))

In the above case there is main function `rcd-db-sql-report' which
only accepts SQL. I spare writing code by using single function to
handle reports. If I would make that function accept "carefully" the
parameters, that would mean that for every of thousands of SELECT
statements I would need to make little different function, and would
waste terribly my time. 

I do not find writing SQL queries as s-exp practically useful, as that
causes programmer NOT to have the SQL statement at hand to verify or
debug the SQL query. In that case I would need to wait for program to
construct SQL, to show me somewhere, that I can try it out.

Otherwise, I use SQL first, then enter it in the Emacs Lisp. Isn't
that more practical to try SQL first until satisifed and then use it?

Back to above function, the macro `when-tabulated-id "words"' passes
the ID only as number. It is checking if user is in the table "words"
and then ID can be taken only from tabulated list ID, and due to
program style in general, there is no other ID but number ID. Nothing
can happen.

Of course, user could modify the buffer of Emacs and "inject" the
dangerous ID there, and thus dangerous SQL possibly. While one can say
those are Emacs weaknesses, for me those are features. That user is
able to modify everything within Emacs, including the code, this fact
alone cannot make SQL queries "safer" by using exlusively s-exp for
SQL. Then I can also modify that SQL and inject anyway dangerous SQL,
analogous typographic and computer errors can take place with s-exp or
without.

IMHO, examples discussed are way too hypothetical without single
real world problem reported so far.

The difference is that with SQL as strings, one can see the SQL
practically, and construct it and place in Emacs Lisp freely, without
double work to re-structure it into s-exp.

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/

Re: [NonGNU ELPA] New package: sqlite3

[Jean Louis]

* Tomas Hlavaty <tom@logand.com> [2023-03-21 23:36]:
> On Tue 21 Mar 2023 at 09:51, Jean Louis <bugs@gnu.support> wrote:
> > 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.
> [...]
> > (sql (format "SELECT DISTINCT ON (people_id) 
>         ^
>         sql injection danger right there
> 
> Using strings is a bad idea.  You have to manually ensure every such use
> is properly escaped.  If you used sexp and let elisp do the conversion,
> every such usage could be automatically properly escaped.
> 
> Also using sexp does not mean you cannot use plain sql:
> 
>    (sql `(SELECT DISTINCT ON (people_id)

"SQL injection right there", booh, scare, terror...

You are exaggerating using single fact that "SQL injections are
security issue", while forgetting the other fact that "Emacs security
is weak by default".

If we would speak of web server programming for public where input
information is restricted to HTTP channel, then I would say that SQL
injections are security issue and programming style shall change.

If we speak of web server programming with team of trained and trusted
staff members, then SQL injection is not an issue, as they could
"inject" much more dangerous harm into the group than the singe
SQL. It matters not. Such people could destroy computers, hard disks,
and anything so much more. And if any of them is really a hidden
psychopath that does "only" less harmful SQL injections in the group
that feeds him, then this will remain in the logs and will be found
who was it.

If we speak of Emacs interface instead of "public input through HTTP"
interface, then talking about SQL injections is pointless, as user can
anyway inject Emacs Lisp, just anything through Emacs interface.

If you wish to keep scaring people on this mailing list, explain them
all the factors for informed decision making.

If you wish to say how you learn about SQL injection, great, but then
remember to tell people that such securit warning applies rather for
web (HTTP) or similar interfaces where program is exposed to public.

It doesn't apply for Emacs text editor interface, because you can
fricking inject anything you want with M-: and by talking how `format'
is dangerous with the SQL query, and "please avoid SQL injections" you
are only introducing unreasonable fears, and will make some people
believe that "using strings to construct SQL is wrong", which is can't
be said so decisively.

In addition, there are many cases where neither PostgreSQL or SQLite3
will allow native interpolation of attributes.

For example I do not believe that following could be replaced with
native interpolation:

(defun rcd-db-table-oid (table pg &optional schema)
  "Return the TABLE OID for database handle PG.

Use optional SCHEMA."
  (let* ((schema (or schema "public"))
	 (sql (format "SELECT '%s.%s'::regclass::oid" schema table)))
    (rcd-sql-first sql pg)))

Neither this one:

(defun rcd-db-table-update-comment (table comment pg)
  "Update TABLE with COMMENT for handle PG."
  (rcd-sql (format "COMMENT ON TABLE %s IS %s" table (sql-escape-string comment)) pg))

This one for sure cannot be replaced with native interpolation as it
requires all the tables to have TABLE_id column as dictated by GeDaFe design.

GeDaFe - PostgreSQL Generic Database Interface:
http://gedafe.github.io/doc/gedafe-sql.en.html 

(defun rcd-db-table-last-id (table pg)
  "Return the last entry ID for the TABLE with database handle PG."
  (rcd-sql-first (format "SELECT %s_id FROM %s ORDER BY %s_id DESC LIMIT 1" table table table) pg))

The design then let me get any value from any column just by using
following, for example to get last name or name of company, I can use
following:

(rcd-db-get-entry "people" "people_name" 1 cf-db)

which uses string interpolation in background:

(defun rcd-db-get-entry (table column id pg)
  "Return value for the COLUMN from the RCD Database by using the entrxy ID.
Argument TABLE is database table."
  (if id
      (let* ((sql (format "SELECT %s FROM %s WHERE %s_id = %s" column table table id))
	     (value (rcd-sql-first sql pg)))
      value)
    (error "Function `rcd-db-get-entry': ID not conveyed")))

Summary is that no programmer who uses Emacs Lisp need to worry about
the SQL injection, but about the people who are using the program.

More about SQL injection:
https://en.wikipedia.org/wiki/SQL_injection

Programmers who use Emacs Lisp to accept public input, from unknown
people through Internet web HTTP interface, should worry about SQL
injection and in that case try their best to verify the input. Even in
that case there is no need to blame using strings or `format' when
constructing SQL, but programmer who doesn't understand the input, who
neglected to verify that input is what it is supposed to be.


Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/