Skip to content

Add triggers while enabling FTS#57

Merged
simonw merged 7 commits intosimonw:masterfrom
amjith:fts_trigger
Sep 2, 2019
Merged

Add triggers while enabling FTS#57
simonw merged 7 commits intosimonw:masterfrom
amjith:fts_trigger

Conversation

@amjith
Copy link
Contributor

@amjith amjith commented Sep 2, 2019

This adds the option for a user to set up triggers in the database to keep their FTS table in sync with the parent table.

Ref: https://sqlite.org/fts5.html#external_content_and_contentless_tables

I would prefer to make the creation of triggers the default behavior, but that will break existing usage where people have been calling populate_fts after inserting new rows.

I am happy to make changes to the PR as you see fit.

@simonw
Copy link
Owner

simonw commented Sep 2, 2019

This is brilliant!

One tiny suggestion: I like --create-triggers and create_triggers=True better for this as they are shorter but still capture what it dies - especially since the underlying SQL uses CREATE TRIGGER statements.

@amjith
Copy link
Contributor Author

amjith commented Sep 2, 2019

I have merged the other PR (#56) into this one.

I have incorporated your suggestions. Cheers!

@simonw
Copy link
Owner

simonw commented Sep 2, 2019

This is fantastic, thanks so much.

I spotted a Datasette bug triggered by this: Datasette has its own version of the detect_fts function - at https://github.com/simonw/datasette/blob/d224ee2c98ac39c2c6e21a0ac0c62e5c3e1ccd11/datasette/utils/__init__.py#L466-L479 - which fails to pick up FTS tables created using the new escaping pattern.

It's a bug in Datasette, not sqlite-utils - so I'll open an issue there.

@simonw simonw merged commit 405e092 into simonw:master Sep 2, 2019
@simonw simonw mentioned this pull request Sep 2, 2019
Closed
@amjith amjith deleted the fts_trigger branch September 2, 2019 23:45
@simonw simonw mentioned this pull request Sep 2, 2019
Closed
simonw added a commit that referenced this pull request Sep 3, 2019
@simonw
Documentation for create-triggers, refs #57
3a4ddda
simonw added a commit to simonw/datasette that referenced this pull request Sep 3, 2019
@simonw
detect_fts now works with alternative table escaping (#571)
2dc5c8d 
Fixes #570. See also simonw/sqlite-utils#57
@simonw
Copy link
Owner

simonw commented Sep 3, 2019

Released in 1.11 - thanks again! https://sqlite-utils.readthedocs.io/en/latest/changelog.html#v1-11

simonw added a commit to simonw/datasette that referenced this pull request Nov 11, 2019
@simonw
detect_fts now works with alternative table escaping (#571)
5a43d8e 
Fixes #570. See also simonw/sqlite-utils#57
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@amjith @simonw