(fix):db: pragma foreign keys to work with sqlite3 #2018
Conversation
This commit is follow-up for PR org-roam#2009 should fix issues org-roam#1927 & org-roam#1910. Currently, `[:pragma (= foreign_keys ON)]` is present only in function `org-roam-db--init`. This means the `foreign_keys` is turned on only when the db file is created for the first time. Subsequent Emacs sessions won't turn it on as the db file is already present and does not evaluate `org-roam-db--init`. This PRAGMA needs to be turned on each database connection at runtime according to sqlite's documentation (https://sqlite.org/foreignkeys.html#fk_enable) ``` Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection ``` I have observed that on Windows only the Emacs session that initially creates the Org-roam db file works correctly with the `sqlite3` option. Subsequent Emacs sessions have the same "Unique constraint failed" error messages. I have tested this patch on Windows and Ubuntu; both seem to work in the first and subsequent Emacs sessions. I am not 100% sure if the location of the PRAGMA is the best; use it as a proof-of-cocenpt for the fix. Thank you.
|
@nobiot thanks for investigating. I found it strange from a design perspective, but I found a forum post that describes a little on how this particular pragma came to be a per connection rather than per db file setting: https://sqlite.org/forum/info/c5dc50f61b88c587 Your fix is correct. We would then want to remove the other Line 246 in ee9a8d4 Then this should be good to merge. |
|
@jethrokuan |
When `org-roam-database-connector` is not `sqlite`, it outputs an unnecessary error when Org-roam starts up as `defconst` evaluates `(emacsql-sqlite-ensure-binary)`. ``` Org-roam initialization: (error "No EmacSQL SQLite binary available, aborting") ``` For other database connectors, this is not relevant.
|
Started using
|
No, it can be removed |
As per our conversation, org-roam--sqlite-available-p not needed.
|
done :) |
This commit is follow-up for PR #2009 should fix issues #1927 & #1910.
Currently,
[:pragma (= foreign_keys ON)]is present only in functionorg-roam-db--init. This means theforeign_keysis turned on only when the dbfile is created for the first time. Subsequent Emacs sessions won't turn it on
as the db file is already present and does not evaluate
org-roam-db--init.This PRAGMA needs to be turned on each database connection at runtime according
to sqlite's documentation (https://sqlite.org/foreignkeys.html#fk_enable)
I have observed that on Windows only the Emacs session that initially creates
the Org-roam db file works correctly with the
sqlite3option. Subsequent Emacssessions have the same "Unique constraint failed" error messages.
I have tested this patch on Windows and Ubuntu; both seem to work in the first
and subsequent Emacs sessions.
I am not 100% sure if the location of the PRAGMA is the best; use it as a
proof-of-cocenpt for the fix. Thank you.
Motivation for this change