2

There are multiple questions with "sqlite" in the title, but they all want to run SQL queries interactively, which sql-mode does.

What I want is the following:

I have a pre-existing SQLite database whose format I know. This database is maintained and updated by another program, so I cannot just convert it to a different format and work with that.

I now want to SELECT some stuff from a table in that database and get the result into a variable in elisp (specifically, this will be used to build a helm source).

emacsql-sqlite almost does this, but it requires all entries in the database to be valid sexpressions, which I cannot enforce in this database.

Is there a package without this limitation? I would rather avoid calling sqlite3 and parsing the output myself, because that will definitely break things in the future.

Drew
  • 75,699
  • 9
  • 109
  • 225
Betta George
  • 57
  • 4
  • One option would be to modify the library that almost does what you want and add whatever features are needed. – lawlist Dec 10 '22 at 19:10
  • https://emacs.stackexchange.com/tags/elisp/info – Drew Dec 10 '22 at 22:15
  • @lawlist That is an option, but I do not consider myself proficient enough to do that. I was hoping that there is some tool or library out there that I missed. If there is no simple solution, I guess I will have to fall back to using `sqlite3` to convert the table to JSON and then parse it into Emacs. – Betta George Dec 10 '22 at 23:05

3 Answers3

3

Soon... (maybe early 2023)

Starting from version 29, native SQLite support will be included in Emacs. I just compiled it, and I took a look at sqlite-mode.el to see what kinds of functions it offered.

(declare-function sqlite-execute "sqlite.c")
(declare-function sqlite-more-p "sqlite.c")
(declare-function sqlite-next "sqlite.c")
(declare-function sqlite-columns "sqlite.c")
(declare-function sqlite-finalize "sqlite.c")
(declare-function sqlite-select "sqlite.c")
(declare-function sqlite-open "sqlite.c")

I then opened up M-x ielm to try it out as well.

ELISP> (setq x/file "/my/db")
"/my/db"
ELISP> (setq x/db (sqlite-open x/file))
#<sqlite db=0x55b423800028 name=/my/db>
ELISP> (sqlite-select x/db "SELECT * FROM forum")
((0 nil "Forum")
 (1 0 "States & Cities")
 (39 1 "California")
 (477 39 "Los Angeles")
 (483 39 "Orange County")
 (491 39 "San Diego")
 (496 39 "Santa Barbara"))

I think this is the functionality you're looking for.

Performance

I tried selecting from a post table with 100k+ rows in it. First I tried it in Emacs.

ELISP> (sqlite-select x/db "SELECT count(*) FROM post")
((103604))

ELISP> (benchmark-run-compiled (sqlite-select x/db "SELECT * FROM post"))
(2.654469372 1 0.34534770399999815)

Then I tried it from the command line sqlite3 client.

sqlite> .timer on
sqlite> SELECT count(*) FROM post;
103604
Run Time: real 0.001 user 0.000378 sys 0.000897

sqlite> SELECT * FROM post;
-- result omitted
Run Time: real 1.143 user 0.261333 sys 0.534172
g-gundam
  • 1,096
  • 1
  • 3
  • 13
  • This is great! How performant is it? If it works fast, I can wait a few months until it goes upstream. – Betta George Dec 12 '22 at 11:39
  • @BettaGeorge - I updated the answer with a performance comparison between Elisp sqlite functions from emacs29 and the command line sqlite3 client. – g-gundam Dec 12 '22 at 17:42
2

You might want to investigate Org Babel, Org mode's source evaluation facility. There is an ob-sqlite backend that you can use for straight sqlite3 queries that return the results in various forms, in particular as a list that can be used to initialize an Emacs Lisp variable. Here's a simple example from an existing music database:

#+name: titles
#+begin_src sqlite :db ~/.config/banshee-1/banshee.db :results list
select albumid, title from corealbums;
#+end_src

#+begin_src elisp :var x=titles
(setq dbtitles x)
#+end_src

The second source block evaluates the first source block assigning the result to a variable x, which in turn is used to set the variable dbtitles. Use C-h v dbtitles to see the value. The format of the result is like this:

((145 "Unknown Album")
 (500 "The 3 Symphonies and the Poem of Ecstasy")
 (514 "Sacrificium")
 (515 "Songs")
 ...
)

a list of lists, each of which consists of the selected fields in the query.

You can execute source blocks non-interactively too, by using the org-sbe macro - do C-h f org-sbe for details. The whole thing might feel a bit rickety, but I believe it can be made to work. However, you are going to have to make sure that there aren't too many rows returned: Org mode has a limit of 999 rows in a table; you can make that larger, but the evaluation of the result might be too slow (that's part of the reason for the 999 limit).

NickD
  • 27,023
  • 3
  • 23
  • 42
0

The sqlite library on MELPA looks to do what you want.

db48x
  • 15,741
  • 1
  • 19
  • 23
  • It does indeed do exactly what I want, but it is so slow (compared to using the `sqlite3` CLI and then parsing the dump myself) that I consider it unusable. – Betta George Dec 10 '22 at 22:55