magit/emacsql

A high-level Emacs Lisp RDBMS front-end

A Git Porcelain inside Emacs

homepage | manual | faq | wiki | mastodon

---

Magit is an interface to the version control system Git, implemented as an Emacs package. Magit aspires to be a complete Git porcelain. While we cannot (yet) claim that Magit wraps and improves upon each and every Git command, it is complete enough to allow even experienced Git users to perform almost all of their daily version control tasks directly from within Emacs. While many fine Git clients exist, only Magit and Git itself deserve to be called porcelains.

---

Keeping its users this excited is a lot of work . If Magit makes you
more productive too, then please consider making a donation.

Thank you! — Jonas Bernoulli

  
  

Some alternative donation methods are available.

---

Getting Started

If you are new to Magit, then either one of the following two articles should help understanding how it differs from other Git clients.

Visual Magit walk-through

If you are completely new to Magit, then this article is a good visual introduction.

Almost everything that you see in Magit can be acted on by pressing some key, but that's not obvious from just seeing how Magit looks. The screenshots and accompanying text of this article explain how to perform a variety of actions on Magit's output.

Magit, the magical Git interface

Magit differs significantly from other Git interfaces, and its advantages are not immediately obvious simply from looking at a few screenshots as presented in the preceding article.

This article discusses Magit's properties in somewhat more abstract terms.

Video introductions

If you prefer video introductions, head over to that page, where find a collection of such introductions and other videos about Magit, by various creators.

---

Support and Contributing

Magit has many users and very few maintainers, so we kindly ask to read the appropriate guidelines before getting in contact. — Thanks!

• 🆘 How to ask for help
• 🪳 How to report a bug
• 💡 How to suggest a feature
• 🏗️ Pull request guidelines
• ℹ️ FAQ
• ℹ️ Manual

TL;DR We now use discussions for feature requests (not issues) and prefer if you ask the community for support instead of the overworked maintainers.

Please also consider to contribute by supporting other users or by making a monetary donation. — Thanks!

---

Acknowledgments

Magit was started by Marius Vollmer, and is now maintained by Jonas Bernoulli and Kyle Meyer. Former maintainers are Nicolas Dudebout, Noam Postavsky, Peter J. Weisberg, Phil Jackson, Rémi Vanicat and Yann Hodique. Many more people have contributed code, suggested features or made monetary contributions.

Thanks to all of you, may (the history of) the source be with you!

---

emacsql's Issues

Parentheses in the expressions are necessary

It seems like a bug in the query compiler. The next code:

(emacsql-compile storage
 [:select *
  :from atable
  :where (and (= field1 1)
          (or (= field2 2)
          (= field3 3)))])

returns the result

"SELECT * FROM atable WHERE field1 = 1 AND field2 = 2 OR field3 = 3;"

while the next statement is expected:

"SELECT * FROM atable WHERE field1 = 1 AND (field2 = 2 OR field3 = 3);"

Actually, there must be parentheses here.

No way to select un(read)able strings in the db

From what I can tell there is no way for me to select strings from my db that can't be read by (read).

I am using mysql and am selecting values that contain the # character, and this errors on read.

My initial solution was to try to select the values as readable strings by concatenating string delimiters around the values but I can't get those queries to build correctly.

I could get around the problem if I could build something like:

SELECT Concat('"', textvalue, '"') FROM mytable;

So that the values are readable, but doing:

(emacsql db [:select [(funcall Concat """ textvalue """)] :from mytable])

yields results like:

"\""value"\""

instead of:

"value"

and when using " instead of """ emacsql tries to use " as a column name.

I've also tried using the Quote function which only uses single quotes, and I get errors when using REPLACE, it seems my REPLACE funcalls maybe aren't built right.

Is there a solution I'm missing or is this not supported?

Perhaps I can go down lower and manually replace #s with #s when the data comes back but that doesn't feel like the best solution.

Thanks.

Issue with value that has embedded periods?

I can work the examples for sqlite in the README.md fine. I have a small existing sqlite3 db that has the following content in a particular table:

sqlite> select id, content, created, filename from nuggets;
1|test0 . This is my first freex nugget. . This is another piece of text. . I link to journal like so|2016-11-30 20:20:24.173502|test0.freex
2|journal . hello, this is my journal . paragraph 1 . page1.freex . journal - test0.freex|2016-12-18 20:58:20.174029|journal.freex
3|page1 . This is part of my journal . page1 - paragraph 1.freex|2016-12-18 21:17:19.464719|page1.freex
4|this is a region to be turned into a nugget . This is edited outside the journal|2016-12-18 21:17:52.696044|page1 - paragraph 1.freex
5|This is some text for the paragraph 1 nugget . Here is a link back to journal|2016-12-18 21:17:52.755955|paragraph 1.freex
6|This is some text point to test0 filename: journal.freex . id: 2 . aliases: 2 . tag-parents: 2 . tag-children: 2 . modtime: 2016-12-18 13:53:41 . |2016-12-18 21:59:17.344417|journal - test0.freex
7|lisp . You can use freex-meta-find to create a new nugget easily . that is how i created the journal nugget|2016-12-18 22:06:12.215085|lisp.freex

If I do

(defvar db (emacsql-sqlite "~/Documents/freex/freex.db"))

(emacsql db [:select [filename]
            :from nuggets])

it works (same for id and created columns), but if I try to get the content column values via

(emacsql db [:select [content]
             :from nuggets])

it fails. Is emacsql perhaps having an issue with the embedded periods, ., in the content column values? The error written to Messages is: Invalid read syntax: ". in wrong context"

Unable to install on Emacs 27.2

When trying to install in Emacs 27.2 I get the error package-untar-buffer: Package does not untar cleanly into directory emacsql-20200714.28/.

emacsql-sqlite returns "middleware out of memory" on Termux (Android)

Calling anything database related, such as:

(emacsql-sqlite "/data/data/com.termux/files/usr/tmp/test.db")

always results in "middleware out of memory" on Termux.

The binary has been compiled with clang version 12.0.0.

Storing null characters

The sqlite back-end at least does not support storing a string that contains a null character. To allow that we would have to test for such characters and then store the value as a blob instead of as text, I believe.

I found out the hard way, implementing a new sqlite back-end (more on that later) that does not error when a parameter contains a null character - only to learn that this results in the text after the null character to be lost.

For the time being I just strip the null character from user values and that's good enough for me, but I am reporting this because its worth knowing that due to this issue Emacsql does not actually support all readable values.

emacsql-foreign-key test fails with newer sqlite

I'm packaging emacsql for Debian and want to build the emacsql-sqlite binary against the system libsqlite3. This seems to be working fine except there is one test failure, as shown below. I think this might be because emacsql needs to be updated in order to be used with newer sqlite?

Test emacsql-foreign-key backtrace:
  signal(ert-test-failed (((should (equal (emacsql db [:select * :from
  ert-fail(((should (equal (emacsql db [:select * :from likes]) '((1 y
  (if (unwind-protect (setq value-29 (apply fn-27 args-28)) (setq form
  (let (form-description-31) (if (unwind-protect (setq value-29 (apply
  (let ((value-29 'ert-form-evaluation-aborted-30)) (let (form-descrip
  (let* ((fn-27 (function equal)) (args-28 (condition-case err (let ((
  (progn (let ((emacsql--conn db)) (let ((emacsql--connection emacsql-
  (unwind-protect (progn (let ((emacsql--conn db)) (let ((emacsql--con
  (progn (unwind-protect (progn (let ((emacsql--conn db)) (let ((emacs
  (unwind-protect (progn (unwind-protect (progn (let ((emacsql--conn d
  (let ((db (funcall (cdr factory)))) (unwind-protect (progn (unwind-p
  (let ((factory (car --dolist-tail--))) (let ((db (funcall (cdr facto
  (while --dolist-tail-- (let ((factory (car --dolist-tail--))) (let (
  (let ((--dolist-tail-- emacsql-tests-connection-factories)) (while -
  (let ((emacsql-global-timeout emacsql-tests-timeout)) (let ((--dolis
  (closure (t) nil (let ((emacsql-global-timeout emacsql-tests-timeout
  ert--run-test-internal(#s(ert--test-execution-info :test #s(ert-test
  ert-run-test(#s(ert-test :name emacsql-foreign-key :documentation "T
  ert-run-or-rerun-test(#s(ert--stats :selector t :tests [#s(ert-test 
  ert-run-tests(t #f(compiled-function (event-type &rest event-args) #
  ert-run-tests-batch(nil)
  ert-run-tests-batch-and-exit()
  eval((ert-run-tests-batch-and-exit))
  command-line-1(("-l" "package" "--eval" "(add-to-list 'package-direc
  command-line()
  normal-top-level()
Test emacsql-foreign-key condition:
    (ert-test-failed
     ((should
       (equal
	(emacsql db
		 [:select * :from likes])
	'...))
      :form
      (equal
       ((0 red)
	(0 yellow)
	(1 yellow))
       ((1 yellow)))
      :value nil :explanation
      (proper-lists-of-different-length 3 1
					((0 red)
					 (0 yellow)
					 (1 yellow))
					((1 yellow))
					first-mismatch-at 0)))
   FAILED  11/23  emacsql-foreign-key

error selecting deleted buffer

With emacs -q I ran this

(require 'package)
(add-to-list 'package-archives '("melpa" . "https://melpa.org/packages/") t)
(package-initialize)
(package-install 'emacsql-sqlite)

(require 'emacsql-sqlite)
(setq db (emacsql-sqlite "/media/dericbytes/Extreme 500/main-photos/photos.db"))

and got this error

Debugger entered--Lisp error: (error "Selecting deleted buffer")
  #f(compiled-function (connection) "Return true if the end of the buffer has a properly-formatted prompt." #<bytecode 0x19b6f9d97cd7d3ab>)(#<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c>)
  apply(#f(compiled-function (connection) "Return true if the end of the buffer has a properly-formatted prompt." #<bytecode 0x19b6f9d97cd7d3ab>) #<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c> nil)
  emacsql-waiting-p(#<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c>)
  #f(compiled-function (connection &optional timeout) "Block until CONNECTION is waiting for further input." #<bytecode -0x1bb371ae8faf3ead>)(#<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c>)
  apply(#f(compiled-function (connection &optional timeout) "Block until CONNECTION is waiting for further input." #<bytecode -0x1bb371ae8faf3ead>) #<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c> nil)
  emacsql-wait(#<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c>)
  #f(compiled-function (#<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c> (:file "/media/dericbytes/Extreme 500/main-photos/photos.d..."))
  apply(#f(compiled-function 
  #f(compiled-function (&rest args) #<bytecode -0xaa0c6eb95f9b1b9>)(#<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c> (:file "/media/dericbytes/Extreme 500/main-photos/photos.d..."))
  apply(#f(compiled-function (&rest args) #<bytecode -0xaa0c6eb95f9b1b9>) #<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c> (:file "/media/dericbytes/Extreme 500/main-photos/photos.d..."))
  initialize-instance(#<emacsql-sqlite-connection emacsql-sqlite-connection-158aaf093b9c> (:file "/media/dericbytes/Extreme 500/main-photos/photos.d..."))
  #f(compiled-function (class &rest slots) "Default constructor for CLASS `eieio-default-superclass'.\nSLOTS are the initialization slots used by `initialize-instance'.\nThis static method is called when an object is constructed.\nIt allocates the vector used to represent an EIEIO object, and then\ncalls `initialize-instance' on that object." #<bytecode 0x1e4a7807358a4b96>)(emacsql-sqlite-connection :file "/media/dericbytes/Extreme 500/main-photos/photos.d...")
  apply(#f(compiled-function (class &rest slots) "Default constructor for CLASS `eieio-default-superclass'.\nSLOTS are the initialization slots used by `initialize-instance'.\nThis static method is called when an object is constructed.\nIt allocates the vector used to represent an EIEIO object, and then\ncalls `initialize-instance' on that object." #<bytecode 0x1e4a7807358a4b96>) emacsql-sqlite-connection (:file "/media/dericbytes/Extreme 500/main-photos/photos.d..."))
  make-instance(emacsql-sqlite-connection :file "/media/dericbytes/Extreme 500/main-photos/photos.d...")
  emacsql-sqlite("/media/dericbytes/Extreme 500/main-photos/photos.d...")
  (setq db (emacsql-sqlite "/media/dericbytes/Extreme 500/main-photos/photos.d..."))
  (progn (setq db (emacsql-sqlite "/media/dericbytes/Extreme 500/main-photos/photos.d...")))
  eval((progn (setq db (emacsql-sqlite "/media/dericbytes/Extreme 500/main-photos/photos.d..."))) t)
  elisp--eval-last-sexp(nil)
  eval-last-sexp(nil)
  funcall-interactively(eval-last-sexp nil)
  call-interactively(eval-last-sexp nil nil)
  command-execute(eval-last-sexp)

I had tried with straight.el previously on my normal setup and got the same message

Linux Slug 4.15.0-115-generic #116-Ubuntu SMP Wed Aug 26 14:04:49 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

GNU Emacs 28.0.50 (build 3, x86_64-pc-linux-gnu, GTK+ Version 3.22.30, cairo version 1.15.10) of 2020-10-04

Relevant full text search (SQLite FTS5 and/or SQLite extensions)

I am developing an application where I would like to use full text search, and moreover full text search that returns results in order by relevance.

Since I am using a SQLite database, my first thought was to use SQLite's FTS5 built in relevance sort, at least as a baseline, but it appears that the SQLite amalgamation emacsql uses predates FTS5.

My second thought was to use a C extension, but I don't see a way to load an SQLite extension in emacsql. I tried to run (emacsql db "select load_extension('/path/to/relevance-extension');") but I got a not authorized error.

I was reading through the issue log and it seems like you don't plan to add any new features, so I wonder if you might accept a PR with a solution to one of the above issues. I am not really familiar with the subject matter, but I might be able put something together.

Do you have any advice?

Select queries does not return column names

There seems to be no way to find the column names in a result.

Ideally, there should be some kind of extra argument to the emacsql function that tells it to return the column names in addition to the actual data.

make sqlite fail?

macOS 10.15
xcode 11

cd ~/.emacs.d/elpa/develop/emacsql-sqlite-20190727.1710/sqlite
make

log: sqlite3.c:20071:24: error: unknown type name ‘time_t’; did you mean ‘size_t’?

mysql is one rude dude!

so I don't even know if I should file this as a bug or anythin' but MAN I am having the damndest of times trying to make a funcall with date_sub.

so here's what I've figured out so far: with the way the s-expression parsing works in emacsql, it winds up translating (operator arg1 arg2) into "arg1 operator arg2." unfortunately, due to mysql being babby's first database, inconsistencies are everywhere, which becomes readily apparent when attempting to funcall date_sub!

here's what I'm trying:

(funcall date_sub (funcall now) :interval 80 :day)

now I may just be a wee little nublet, but here's what I'm expecting:

=> "date_sub(now(), interval 80 day)"

what I actually wind up getting is -close-, but no cigar:

=> "date_sub(now(), ':interval' 80 ':day')"

I thought I was being clever by rewriting it: (funcall date_sub (funcall now) [:interval 80 :day])

which gets SUPER DUPER CLOSE but because mysql is a fucking dingus, it results in a parsing error due to having the interval stuff wrapped in parentheses:

=> "date_sub(now(), (INTERVAL 80 DAY))"

there is a workaround, but it sucks, because it only works for specific intervals, that interval being days-- which totally works for now, but I'd like a functional solution!

your beard's bigger than mine man! what the heck do I do?

Is it possible to ignore sqlite locks and perform read only queries?

apply: Creating pipe: Too many open files

I am looping through a lot of files and emacsql calls (thousands) and I get the error above. That makes me think I am leaking file handles in my loops, maybe from opening and closing the connection?

Any hint on where this might be happening?

edit: Hm. Seems to be directory files that are open.

Inserting strings in MySQL unnecessarily prints double quotes

Here's an example query:

(emacsql sphinx-mysql-connection
         "INSERT INTO org (kind, file, pos, contents, parent)
          VALUES
          ((SELECT id FROM org_kinds WHERE org_kinds.kind = $s1),
           $s2, $s3, $s4,
           (SELECT id FROM org AS org1 WHERE org1.file = $s2 LIMIT 1))"
            kind file begin cnt)

This produces:

INSERT INTO org (kind, file, pos, contents, parent)
                VALUES
                ((SELECT id FROM org_kinds WHERE org_kinds.kind = 'headline'),
                 '"/home/wvxvw/.emacs.d/sphinx-mode/README.org"', 1, '"Setup"',
                 (SELECT id FROM org AS org1 WHERE org1.file = '"/home/wvxvw/.emacs.d/sphinx-mode/README.org"' LIMIT 1));

Here's something quick I could think of, but I'm not sure it doesn't break something else:

(defun emacsql-escape-scalar (value)
  "Escape VALUE for sending to SQLite."
  (let ((print-escape-newlines t))
    (cond ((null value) "NULL")
          ((numberp value) (prin1-to-string value))
          ((stringp value)
           (cl-loop with result = (prin1-to-string value)
                    for char across result
                    for i from 0 do
                    (cl-case char
                      (?\" (aset result i ?'))
                      (?' (aset result i ?\")))
                    finally return result))
          ((emacsql-quote-scalar (prin1-to-string value))))))

Error if the log buffer has been deleted

I found this while using magit-forge. I can refresh a magit status buffer with no problem, but if I then delete the *emacsql-log* buffer and try to refresh I get an error about "Selecting deleted buffer". I'm not familiar with the code, but I think one solution is to have emacsql-live-p check the liveness of the log buffer as well as the process.

I can provide a PR to that effect if you would like and agree that's the best option.

[Feature Request] Support LONGVARCHAR type in SQLite

I try to use emacsql to work with my firefox bookmarks in ff user data sqlite database $HOME/.mozilla/firefox/*/places.sqlite.

However, firefox store bookmark title in LONGVARCHAR type instead of TEXT type in database and emacsql-sqlite program failed to transform SQL query result to Elisp readable text stream.

When I run emacsql-sqlite manually and use this query command

31SELECT title from moz_bookmarks

I found the result was outputed like

(Lorem Ipusm)

but expect

("Lorem Ipsum")

Is there any possible way to get LONGVARCHAR type data in database?

binary not compiling on windows with clang

Hi when I try to compile using clang the function exits with nil and I get the following in the compile-log buffer, any idea what's going wrong?

c:/Program Files/LLVM/bin/clang.exe -Ip:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite -O2 -DSQLITE_THREADSAFE=0 -DSQLITE_DEFAULT_FOREIGN_KEYS=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_JSON1 -DSQLITE_SOUNDEX p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/sqlite3.c p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/emacsql.c -lm -o p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/emacsql-sqlite.exe
p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/emacsql.c:109:22: warning: 'scanf' is deprecated: This function or variable may be unsafe. Consider using scanf_s instead. To disable deprecation, use _CRT_SECURE_NO_WARNINGS. See online help for details. [-Wdeprecated-declarations]
        int result = scanf("%u ", &length);
                     ^
C:\Program Files (x86)\Windows Kits\10\Include\10.0.17763.0\ucrt\stdio.h:1273:20: note: 'scanf' has been explicitly marked deprecated here
    _Check_return_ _CRT_INSECURE_DEPRECATE(scanf_s)
                   ^
C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.16.27023\include\vcruntime.h:255:55: note: expanded from macro '_CRT_INSECURE_DEPRECATE'
        #define _CRT_INSECURE_DEPRECATE(_Replacement) _CRT_DEPRECATE_TEXT(    \
                                                      ^
C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.16.27023\include\vcruntime.h:245:47: note: expanded from macro '_CRT_DEPRECATE_TEXT'
#define _CRT_DEPRECATE_TEXT(_Text) __declspec(deprecated(_Text))
                                              ^
1 warning generated.
LINK : fatal error LNK1181: cannot open input file 'm.lib'
clang: error: linker command failed with exit code 1181 (use -v to see invocation)
c:/Program Files/LLVM/bin/clang.exe -Ip:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite -O2 -DSQLITE_THREADSAFE=0 -DSQLITE_DEFAULT_FOREIGN_KEYS=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_JSON1 -DSQLITE_SOUNDEX p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/sqlite3.c p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/emacsql.c -lm -o p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/emacsql-sqlite.exe
p:/home/.emacs.d/.local/packages/elpa/emacsql-sqlite-20190426.1929/sqlite/emacsql.c:109:22: warning: 'scanf' is deprecated: This function or variable may be unsafe. Consider using scanf_s instead. To disable deprecation, use _CRT_SECURE_NO_WARNINGS. See online help for details. [-Wdeprecated-declarations]
        int result = scanf("%u ", &length);
                     ^
C:\Program Files (x86)\Windows Kits\10\Include\10.0.17763.0\ucrt\stdio.h:1273:20: note: 'scanf' has been explicitly marked deprecated here
    _Check_return_ _CRT_INSECURE_DEPRECATE(scanf_s)
                   ^
C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.16.27023\include\vcruntime.h:255:55: note: expanded from macro '_CRT_INSECURE_DEPRECATE'
        #define _CRT_INSECURE_DEPRECATE(_Replacement) _CRT_DEPRECATE_TEXT(    \
                                                      ^
C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\VC\Tools\MSVC\14.16.27023\include\vcruntime.h:245:47: note: expanded from macro '_CRT_DEPRECATE_TEXT'
#define _CRT_DEPRECATE_TEXT(_Text) __declspec(deprecated(_Text))
                                              ^
1 warning generated.
LINK : fatal error LNK1181: cannot open input file 'm.lib'
clang: error: linker command failed with exit code 1181 (use -v to see invocation)

Split low-level Emacs Lisp front-end for databases

Maybe is it possible to split this package into something like dbi with low lovel api for execution raw sql queries and emacsql for generating those queries from dsl. This will allow to build another tools on top of dbi library.

What do you think?

No alert that we are missing a C compiler

Steps

1. Lack a C compiler
2. Install Forge (and by extension emacsql) for the first time
3. Using Forge, try to list the pull requests (' l p) in a repo

Expected result: Get an alert that emacsql does not work

Actual result: No indication that anything went wrong -- and no pull requests

Thanks to this comment, I learned to do (emacsql-sqlite-compile 2) and then got the information Could not find C compiler, skipping SQLite build, so I installed gcc and it worked. But it was lucky I found this comment, it seems emacsql should emit an error for the user's benefit.

Nesting is lost when combining `and` and `or`, and when combining with `NOTNULL`

I wrote [:where (and (= a "a") (or (= b "b") (= b "B")))
and expected WHERE a = '"a"' AND ( b = '"b"' OR b = '"B"' );
but got WHERE a = '"a"' AND b = '"b"' OR b = '"B"';

---

And once that works, how do I get WHERE a NOTNULL AND ( b = '"b"' OR b = '"B"');?

Using [:where (and a :notnull (or (= b "b") (= b "B")))]
I get WHERE a AND ':notnull' AND b = '"b"' OR b = '"B"';.

I didn't expect that to work, but is there an emacsql-way of writing a NOTNULL, preferably one that works inside (and ...)? I also tried funcall.

Allowance for using DISTINCT keyword in aggregate function for SQLite

I didn't find any obvious way to make the funcall operator work for counting distinct column values so I used :count [:distinct file-name], which doesn't seem to be a documented recommendation in this context.

I'm really liking this emacsql. Thanks for continuing to refine it!

syntax question of update statement.

Hi , i've been trying a couple of hours about update statement and always get syntax error . i don't know where i did wrong .
my code :
(emacsql db [:update test_table :set state=$s1 :where (= name $s2) ] 2 "table_name") or maybe (emacsql db [:update test_table :set (state $s1) :where (=name $s2) ] 2 "table_name")
Could you offer me some update statment examples? thank you.

Why is emacsql-error not declared as an error condition?

It would be useful to be able to handle database errors with condition-case. But the emacsql-error condition is never explicitly declared as an error in emacsql.el. Is this intentional? When I add something like

(define-error 'emacsql-error "SQL Error")

then error-handling seems to work fine.

The sqlite adapter adds some additional conditions, like emacsql-constraint, which can also be declared this way.

Is dumping of sqlite db supported?

If so how and if not could you please implement that?

Currently I resort to closing the db, running sqlite3 db.sqlite .dump > db.sql and opening the db again, but that's not very satisfactory.

Thanks for your help!

Error compiling emacsql-sqlite on Windows when `.emacs.d` is in a path with non-ASCII characters

I know it's a terrible idea, but I have a non-ASCII character in my user folder on Windows 10, and it's breaking emacsql-sqlite-compile when attempting compilation with Mingw-gcc, installed from w64devkit. That is, it will complain about being unable to open various files under elpa/emacsql-sqlite, like sqlite.c.

At first I thought it was because there's a space in my user folder (another terrible idea), but now I think it's because of the non-ASCII character.

*Compile-Log* emits something like this:

c:/opt/w64devkit/bin/cc.exe -Ic:/Users/Silly Üser/AppData/Roaming/.emacs.d/elpa/emacsql-sqlite-20190727.1710/sqlite -O2 -DSQLITE_THREADSAFE=0 -DSQLITE_DEFAULT_FOREIGN_KEYS=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_JSON1 -DSQLITE_SOUNDEX c:/Users/Silly Üser/AppData/Roaming/.emacs.d/elpa/emacsql-sqlite-20190727.1710/sqlite/sqlite3.c c:/Users/Silly Üser/AppData/Roaming/.emacs.d/elpa/emacsql-sqlite-20190727.1710/sqlite/emacsql.c -o c:/Users/Silly Üser/AppData/Roaming/.emacs.d/elpa/emacsql-sqlite-20190727.1710/sqlite/emacsql-sqlite.exe
cc1.exe: fatal error: c:/Users/Silly Üser/AppData/Roaming/.emacs.d/elpa/emacsql-sqlite-20190727.1710/sqlite/sqlite3.c: No such file or directory
compilation terminated.
cc1.exe: fatal error: c:/Users/Silly Üser/AppData/Roaming/.emacs.d/elpa/emacsql-sqlite-20190727.1710/sqlite/emacsql.c: No such file or directory
compilation terminated.

It works if I run it from the Git-Bash-shell, so I'm able to work around this for myself, but I'm not (yet) sure how to best fix this, so I thought I would leave this issue here to make it googlable for others with the same problem.

:select $v1 not possible?

(emacsql db [:select [name class] :from mirror])
(emacsql db [:select $v1 :from mirror] [name class])

The first form works, the second gives me

Debugger entered--Lisp error: (emacsql-error "near \",\": syntax error")
  signal(emacsql-error ("near \",\": syntax error"))
  emacsql-sqlite-connection([object emacsql-sqlite-connection "emacsql-sqlite-connection" #<process emacsql-sqlite> nil "/home/jonas/Repos/emacsmirror/epkg.sqlite"] 1 "near \",\": syntax error")
  apply(emacsql-sqlite-connection ([object emacsql-sqlite-connection "emacsql-sqlite-connection" #<process emacsql-sqlite> nil "/home/jonas/Repos/emacsmirror/epkg.sqlite"] 1 "near \",\": syntax error"))
  eieio-generic-call-primary-only(emacsql-handle ([object emacsql-sqlite-connection "emacsql-sqlite-connection" #<process emacsql-sqlite> nil "/home/jonas/Repos/emacsmirror/epkg.sqlite"] 1 "near \",\": syntax error"))
  emacsql-handle([object emacsql-sqlite-connection "emacsql-sqlite-connection" #<process emacsql-sqlite> nil "/home/jonas/Repos/emacsmirror/epkg.sqlite"] 1 "near \",\": syntax error")
  #[257 "r\301�!q\210eb\210p�\302 \303�\304\"\203�

Support official sqlite3 executable as backend.

I'm using sqlite3 3.29.0 and findswitch --batch will disable readline support, which will be useful in emacsql.

If we can use official sqlite3 executable, it's no need to compile the sqlite client included in emacsql.

Figure out how to work around Readline

Unfortunately Readline on SQLite's side is responding to control characters, making it impossible to transmit these special bytes in any more. It may not be possible to rely on the system's sqlite3 program.

Is emacsql-sqlite-automatic-fetch still relevant?

Trying to investigate magit/forge#84. I don't have clang or gcc installed in Windows and automatic binary fetching doesn't seem to work. The documentation explains about emacsql-sqlite-automatic-fetch but I could not find this variable anywhere in the code.

is inner join syntax possible?

Something like

SELECT * FROM files inner join headlines on files.rowid=headlines.filename_id;

This (emacsql db [:select * :from files :inner :join headlines]) runs.

I tried something like this:
(emacsql db [:select * :from files :inner :join headlines :on (= files.rowid headlines.filename-id)])

but it returns nil.

Please create a new release

I plan to re-announce the Emacsmirror on Monday. I'll then also release epkg.el a tool for browsing metadata extracted from the mirrored packages. That uses emacsql-sqlite and depends on the recent changes.

Could you please create a new minor release so that epkg.el can be installed from Melpa Stable as soon as I announce it. Thanks a lot!

Invalid read syntax ")"

I get Invalid read syntax ")" when datetime('now') is in a column

 (defun pkb-select-table-columns (table-name)
      ""
      (emacsql db
	       [:pragma (funcall table_info $i1)]
	       table-name))

error on running this

 (pkb-select-table-columns 'keymap_keys)

running command directly in sql prompt

sqlite> PRAGMA table_info('keymap_keys');
0|function_name|TEXT|0||1
1|desc|TEXT|0||0
2|key|TEXT|0||0
3|registered|TEXT|0|datetime('now')|0

full text search syntax

Is it possible to get this full text search syntax?

SELECT headline_id, content FROM headline_content WHERE headline_content
MATCH '"alloy" NEAR "neural"';

I got close with

(emacsql org-db [:select [headline-id content] :from
headline-content
:where headline-content :match "alloy" :NEAR "neural"])

but it generates

SELECT headline_id, content FROM headline_content WHERE headline_content
MATCH '"alloy"' NEAR '"neural"';

and the single quotes are not right.

New function `emacsql-async`

Currently all emacsql function are synchorously, but it's waste of time to wait for sql backend write a lot of data into database. So I suggest a function

(emacsql-async DB CALLBACK ERRORBACK &rest ARGS)

Which allow user to execute sql statement asynchorously.

If you think it's resonable and I' ll try to implement this.

emacsql-parse fails on rows with `#` (sharpsign)

Hi:
I'm trying to use emacsql on a table which contains rows #

sqlite> SELECT * FROM searchIndex LIMIT 5;
1|NoMethodError|Class|NoMethodError.html
2|NoMethodError.new|Method|NoMethodError.html#//dash_ref_method%2Dc%2Dnew/Method/new/0
3|NoMethodError.args|Method|NoMethodError.html#//dash_ref_method%2Di%2Dargs/Method/args/0
4|NoMethodError.private_call?|Method|NoMethodError.html#//dash_ref_method%2Di%2Dprivate%5Fcall%2D3F/Method/private_call?/0
5|ClosedQueueError|Class|ClosedQueueError.html
sqlite> 

but when I'm make a query:

(emacsql db [:select * :from searchIndex])

it fails with (invalid-read-syntax "#" 2 48):

backtrace: (invalid-read-syntax "#" 2 48)

Debugger entered--Lisp error: (invalid-read-syntax "#" 2 48)
  read()
  (let* ((standard-input (current-buffer)) (value (read))) (if (eql value 'error) (emacsql-handle connection (read) (read)) (prog1 value (if (eq 'success (read)) nil (emacsql-handle connection (read) (read))))))
  (save-current-buffer (set-buffer (emacsql-buffer connection)) (goto-char (point-min)) (let* ((standard-input (current-buffer)) (value (read))) (if (eql value 'error) (emacsql-handle connection (read) (read)) (prog1 value (if (eq 'success (read)) nil (emacsql-handle connection (read) (read)))))))
  (progn (save-current-buffer (set-buffer (emacsql-buffer connection)) (goto-char (point-min)) (let* ((standard-input (current-buffer)) (value (read))) (if (eql value 'error) (emacsql-handle connection (read) (read)) (prog1 value (if (eq 'success (read)) nil (emacsql-handle connection (read) (read))))))))
  (closure (t) (connection) "Parse well-formed output into an s-expression." (progn (save-current-buffer (set-buffer (emacsql-buffer connection)) (goto-char (point-min)) (let* ((standard-input (current-buffer)) (value (read))) (if (eql value 'error) (emacsql-handle connection (read) (read)) (prog1 value (if ... nil ...)))))))(#<emacsql-sqlite-connection emacsql-sqlite-connection-1fe1bbceff08>)
  apply((closure (t) (connection) "Parse well-formed output into an s-expression." (progn (save-current-buffer (set-buffer (emacsql-buffer connection)) (goto-char (point-min)) (let* ((standard-input (current-buffer)) (value (read))) (if (eql value 'error) (emacsql-handle connection (read) (read)) (prog1 value (if ... nil ...))))))) #<emacsql-sqlite-connection emacsql-sqlite-connection-1fe1bbceff08> nil)
  emacsql-parse(#<emacsql-sqlite-connection emacsql-sqlite-connection-1fe1bbceff08>)
  #f(compiled-function (connection sql &rest args) "Send SQL s-expression to CONNECTION and return the results." #<bytecode 0x8b5ac57cba15ef>)(#<emacsql-sqlite-connection emacsql-sqlite-connection-1fe1bbceff08> [:select * :from searchIndex])
  apply(#f(compiled-function (connection sql &rest args) "Send SQL s-expression to CONNECTION and return the results." #<bytecode 0x8b5ac57cba15ef>) #<emacsql-sqlite-connection emacsql-sqlite-connection-1fe1bbceff08> [:select * :from searchIndex])
  emacsql(#<emacsql-sqlite-connection emacsql-sqlite-connection-1fe1bbceff08> [:select * :from searchIndex])
  (progn (defvar url-http-end-of-headers) (emacsql db [:select * :from searchIndex]))
  elisp--eval-last-sexp(nil)
  eval-last-sexp(nil)
  funcall-interactively(eval-last-sexp nil)
  command-execute(eval-last-sexp)

I think it happens when (read) tries to interpret # as a sharpsign. I think it would also happen with the ; character
https://github.com/skeeto/emacsql/blob/9dca5996168c4963eb67e61c7f17fdcb8228e314/emacsql.el#L211

quoting results?

I have a sqlite database that has entries like "Message from Unknown sender" in a column. But if I do something like

(emacsql db [:select [messageID arrivalTime subject callerIdCallerName]
:from 'Voicemails])

I get results like:
(0:ce02a3fb-6ff0-4d67-b6dd-1dbcd2c8bf1a 1475008083000 Message from Unknown sender (Unknown caller ID))

That list has more than 4 elements in it (it has 7). But this isn't useful since the strings are not quoted. Is there a way to get the results from columns quoted?

SELECT last_insert_rowid()

Is there a convenient way to get the last rowid inserted?

I have a set of tables, eg I insert a row into a headline table, and I need the rowid to put into a tags table.

String values are returned as interned symbols

When performing a select query which returns string values, those strings are returned in the form of symbols. What is worse is that these symbols are interned, which can easily cause performance problems in Emacs since the obarray will be filled up.

A better solution would be to return plain strings instead of symbols.

`defconst emacsql-version`

i think (defvar emacsql-version "3.0.0") should be (defconst emacsql-version "3.0.0") to work with live updates via package-install.

Windows and sqlite3 buffering issues

In Windows, a sqlite3 process has most of its output trapped in some kind of communication buffer, completely losing some of the output. For example,

(let ((buffer (generate-new-buffer "foo")))
  (start-process "sqlite" buffer "sqlite3")
  (pop-to-buffer buffer))

The buffer foo will be empty. Send a few .help commands to push through lots of data and you'll see some parts of the .help command's output.

(process-send-string (get-process "sqlite") ".help\n")

Is this a bug in Emacs? A bug in sqlite3? How do we work around this?

Declare unique column in MySQL

I would expect this to work like this:

(defun sphinx-info-ensure-table ()
  (emacsql sphinx-mysql-connection
           [:create-table :if :not :exists info
                          ([(id integer :primary-key :auto_increment)
                            (node object)
                            (contents object :unique)])]))

But this generates invalid query. I've tried some other variants, but this seems to be impossible with the current version (I found a solution by altering the table and adding the constraint later, but I'd prefer this to be possible in a single query.)

Not able to download sqlite binary for Windows

I installed the emacsql package and started going through the example. Upon doing

ELISP> (defvar db (emacsql-sqlite "~/company.db"))
*** Eval error *** No EmacSQL SQLite binary available, aborting

I agree to download the binary but it doesn't get past displaying
Contacting host: nullprogram.s3.amazonaws.com:80

Is the 2.0.2 binary available?

GROUP BY's on emacsql-sqlite

How do we do group-by's using emacsql?

I use emacsql-sqlite in Org-roam, and it seems that the return result only takes one value from my sqlite database, rather than the grouped result. Here's an example query-result pair:

With group-by:

(pp (emacsql (org-roam-db) [:select [from, to, properties] :from links
                                   :where (= to $s1)
                                   :group-by from]
             "/home/jethro/Dropbox/org/braindump/org/zettelkasten.org"))

"((\"/home/jethro/Dropbox/org/braindump/org/20200213174749.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"If we flip this process in [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]], and make the notes the\\nthinking process rather than /a record of/ of the thinking process.\" :point 493))
 (\"/home/jethro/Dropbox/org/braindump/org/arguments_against_zettelkasten.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"[[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][§zettelkasten]]\" :point 82))
 (\"/home/jethro/Dropbox/org/braindump/org/how_to_take_smart_notes.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"In [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]], the most time-consuming portion is determining /the\\norder/ for the notes in which to write about.\" :point 1330))
 (\"/home/jethro/Dropbox/org/braindump/org/para_method.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"This seems to be in contrast with other techniques like [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]].\\nwhich propose no such organization, instead using links between\\ndifferent information.\" :point 1321))
 (\"/home/jethro/Dropbox/org/braindump/org/roam_research.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"Roam research is a tool for networked-thought, created by [[file:/home/jethro/Dropbox/org/braindump/org/conor_white_sullivan.org][Conor\\nWhite-Sullivan]]. Roam adopts methods from [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]].\" :point 256)))
"

without group-by:

(pp (emacsql (org-roam-db) [:select [from, to, properties] :from links
                                   :where (= to $s1)]
             "/home/jethro/Dropbox/org/braindump/org/zettelkasten.org"))

"((\"/home/jethro/Dropbox/org/braindump/org/20200213174749.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"If we flip this process in [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]], and make the notes the\\nthinking process rather than /a record of/ of the thinking process.\" :point 493))
 (\"/home/jethro/Dropbox/org/braindump/org/arguments_against_zettelkasten.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"[[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][§zettelkasten]]\" :point 82))
 (\"/home/jethro/Dropbox/org/braindump/org/how_to_take_smart_notes.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"Hence, use the [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]] method. To get around this idea of\\nnote-taking, it is important to understand that:\" :point 1021))
 (\"/home/jethro/Dropbox/org/braindump/org/how_to_take_smart_notes.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"In [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]], the most time-consuming portion is determining /the\\norder/ for the notes in which to write about.\" :point 1330))
 (\"/home/jethro/Dropbox/org/braindump/org/para_method.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"Contrasting with [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]]\" :point 1261))
 (\"/home/jethro/Dropbox/org/braindump/org/para_method.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"This seems to be in contrast with other techniques like [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]].\\nwhich propose no such organization, instead using links between\\ndifferent information.\" :point 1321))
 (\"/home/jethro/Dropbox/org/braindump/org/roam_research.org\" \"/home/jethro/Dropbox/org/braindump/org/zettelkasten.org\"
  (:content \"Roam research is a tool for networked-thought, created by [[file:/home/jethro/Dropbox/org/braindump/org/conor_white_sullivan.org][Conor\\nWhite-Sullivan]]. Roam adopts methods from [[file:/home/jethro/Dropbox/org/braindump/org/zettelkasten.org][Zettelkasten]].\" :point 256)))
"

With the group-by, I see that one entry for "how_to_take_smart_notes.org" is missing from the SQL result.

another syntax question

I am trying to get some emacsql code that generates:

SELECT headlines.title AS a1, tags.tag AS a2 FROM

:select [headlines:title] :as a1

generates the first part, but I don't know how to get the second part. None of the efforts at nesting tags.tag have worked,

eg :select [headlines:title tags:tag] :as a2

leads to SELECT headlines.title, tags.tag AS a2

Any hints?

No good way to construct an expression "... like ... escape ..."

I need something like

select url from links where url like '"%%%"' escape '';

However, the construction like

(emacsql-compile storage
 [:select url
  :from links
  :where (like url (escape "%\\%%" "\\"))])

returns

"SELECT url FROM links WHERE url LIKE '"%\%%"' ESCAPE '"\"';"

And the sqlite query parser detects the error: the ESCAPE argument must be a single character, not a string.

install from MELPA without the SQLite shell

I use PG exclusively and would prefer to avoid installing unneedded external binaries when I install Emacs packages.

I'm not sure if this requires splitting the MELPA package or something else, but there doesn't currently seem to be a way to install without the SQLite shell.

Thanks for looking...

extremely slow UPDATE queries

As first mentioned at magit/forge#300 (comment), I'm seeing some very slow UPDATE queries when using emacsql from forge:

emacsql: slow query (0.459997s): UPDATE repository SET apihost = '"api.github.com"' WHERE id = '"Z2l0aHViLmNvbTowMTA6UmVwb3NpdG9yeTMyMjA3MzA0MQ=="';
emacsql: slow query (0.416893s): UPDATE repository SET githost = '"github.com"' WHERE id = '"Z2l0aHViLmNvbTowMTA6UmVwb3NpdG9yeTMyMjA3MzA0MQ=="';
emacsql: slow query (0.204041s): UPDATE repository SET remote = '"origin"' WHERE id = '"Z2l0aHViLmNvbTowMTA6UmVwb3NpdG9yeTMyMjA3MzA0MQ=="';
emacsql: slow query (0.202354s): UPDATE repository SET worktree = '"/home/adam/my/repo/"' WHERE id = '"Z2l0aHViLmNvbTowMTA6UmVwb3NpdG9yeTMyMjA3MzA0MQ=="';
emacsql: slow query (0.225668s): UPDATE repository SET apihost = '"api.github.com"' WHERE id = '"Z2l0aHViLmNvbTowMTA6UmVwb3NpdG9yeTMyMjA3MzA0MQ=="';

Any suggestions where I can start debugging this?

Cannot build emacsql sqlite binary with gcc

I was not able to build sqlite binary with gcc as the compiler, see my issue for org-roam, and after I switched to clang, it seems to be fine. Is it expected behaviour?

How can I write this sql in emacsql-sqlite format?

Hi, emacsql syntax is a bit arcane to me. How can I rewrite this?

 select case substr(path,1,8)
 when 'storage:'
 then '/home/amos/Zotero/storage/'||its.key||'/'||substr(path,9)
 else path
 end
 from items its, itemAttachments a, fulltextWords w, fulltextItemWords iw , fulltextItems i
 where w.wordID = iw.wordID and iw.itemID = i.itemID and a.itemID = i.itemID and its.itemID = a.itemID
 and word in ('database');

regards.