no such table error

[fonnesbeck]

Each time I try to search for a note or tag, I get the following error:

no such table: ZSFNOTE

I am running the release version of alfred-bear and version 1.3.1 of bear on macOS 10.13.1

[chrisbro]

@fonnesbeck I'm having a hard time reproducing on two machines with a similar setup - someone did reach out to me on Twitter however and said that it resolved itself after a reboot - strange! Give that a shot and let me know if it does anything, in the meantime I'll see if I can't try to figure out what it might be.

[chrisbro]

I'm reaching out to the Bear support channel to see what's causing this. If I can figure out what the deal is I'll try to code around it!

[fonnesbeck]

Thanks. I've tried the restart, but that does not clear up the issue. Let me know if there is anything else I should try. I assumed it was the new version of Bear that broke it. Alfred was also updated a few days ago.

[chrisbro]

@fonnesbeck If you're feeling adventurous and have some SQL-ish skills, you can see about opening the SQLite database with the sqlite3 command and seeing if you can pull out the list of tables, so that at least I know what it got renamed to. Database location is here: https://github.com/chrisbro/alfred-bear/blob/master/queries.py#L14

I can write up more specific instructions next week - about to head out of town for the weekend, unfortunately.

[chrisbro]

@fonnesbeck The developers suggest that it's a problem with the database not being where I expect it to be at all, specifically the behavior with this Stackoverflow thread. Could you see if there are files where we expect them to be in either of these two places, and that the filesizes make sense?

~/Library/Containers/net.shinyfrog.bear/Data/Library/Application Support/net.shinyfrog.bear/database.sqlite
~/Library/Containers/net.shinyfrog.bear/Data/Documents/Application Data/database.sqlite

[fonnesbeck]

Here are the files on my machine:

$ ls -al ~/Library/Containers/net.shinyfrog.bear/Data/Library/Application\ Support/net.shinyfrog.bear
total 0
drwxr-xr-x@   4 fonnescj  staff   128 Oct 19 12:13 .
drwx------    6 fonnescj  staff   192 Mar  2  2017 ..
drwxr-xr-x@ 101 fonnescj  staff  3232 Oct 15 14:01 Thumbs
-rw-r--r--    1 fonnescj  staff     0 Oct 19 12:13 database.sqlite
(dev) fonnescj on Ott.local in ~/Repos/dask-pydata-chi-2016(424d1h20m|master*)
$ ls -al ~/Library/Containers/net.shinyfrog.bear/Data/Documents/Application\ Data
total 14048
drwxr-xr-x@ 5 fonnescj  staff      160 Oct 23 19:23 .
drwx------  4 fonnescj  staff      128 Jul 19 09:11 ..
drwxr-xr-x@ 4 fonnescj  staff      128 Jun 16 15:40 Local Files
-rw-r--r--@ 1 fonnescj  staff  3139738 Oct 23 19:23 cloudkit.plist
-rw-r--r--@ 1 fonnescj  staff  3112960 Oct 23 19:23 database.sqlite

[chrisbro]

@fonnesbeck Huh. Thought I was onto something given the zero-size sqlite database in that first/"old" directory and that it was trying to pull from there instead, but I have the same situation on my machine and don't see this behavior. Mind attaching the Alfred log that leads up to this error? Apologies, and thanks for the patience!

[fonnesbeck]

If I run the workflow in debug mode, I get the following:

Starting debug for 'Bear'

[2017-10-24 08:55:22][ERROR: input.scriptfilter] Code 1: 08:55:22 workflow.py:2225 DEBUG    Workflow version : 0.2.0
08:55:22 workflow.py:1628 DEBUG    Reading settings from `/Users/fonnescj/Library/Application Support/Alfred 3/Workflow Data/com.chrisbro.bear/settings.json` ...
08:55:22 workflow.py:2514 DEBUG    Update check not due
08:55:22 workflow.py:1859 DEBUG    Loading cached data from : /Users/fonnescj/Library/Caches/com.runningwithcrayons.Alfred-3/Workflow Data/com.chrisbro.bear/__workflow_update_status.cpickle
08:55:22 workflow.py:2444 DEBUG    update_data : {u'available': True, u'version': u'0.3.1', u'download_url': u'https://github.com/chrisbro/alfred-bear/releases/download/0.3.1/alfred-bear.alfredworkflow'}
08:55:22 search.py:37 DEBUG    Started search workflow
08:55:22 search.py:60 DEBUG    [u'f']
08:55:22 search.py:42 DEBUG    Searching notes for f
08:55:22 search.py:97 DEBUG    Searching notes
08:55:22 workflow.py:1748 DEBUG    Data `db_path` stored in `cpickle` format
08:55:22 workflow.py:1763 DEBUG    Stored data loaded from : /Users/fonnescj/Library/Application Support/Alfred 3/Workflow Data/com.chrisbro.bear/db_path.cpickle
08:55:22 queries.py:87 DEBUG    /Users/fonnescj/Library/Containers/net.shinyfrog.bear/Data/Library/Application Support/net.shinyfrog.bear/database.sqlite
08:55:22 queries.py:91 DEBUG    SELECT DISTINCT   ZUNIQUEIDENTIFIER, ZTITLE FROM    ZSFNOTE WHERE    ZARCHIVED=0    AND ZTRASHED=0    AND lower(ZTITLE) LIKE lower('%f%')
08:55:22 workflow.py:2243 ERROR    no such table: ZSFNOTE
Traceback (most recent call last):
  File "/Users/fonnescj/Library/Application Support/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.7BF3C156-8E64-48F7-BBC7-4A936C843A61/workflow/workflow.py", line 2236, in run
    func(self)
  File "search.py", line 43, in main
    execute_search_query(args)
  File "search.py", line 98, in execute_search_query
    results = queries.search_notes_by_title(WORKFLOW, LOGGER, query)
  File "/Users/fonnescj/Library/Application Support/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.7BF3C156-8E64-48F7-BBC7-4A936C843A61/queries.py", line 56, in search_notes_by_title
    return run_query(workflow, log, sql_query)
  File "/Users/fonnescj/Library/Application Support/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.7BF3C156-8E64-48F7-BBC7-4A936C843A61/queries.py", line 92, in run_query
    cursor.execute(sql)
OperationalError: no such table: ZSFNOTE
08:55:22 workflow.py:2267 DEBUG    Workflow finished in 0.059 seconds.
[2017-10-24 08:55:22][ERROR: input.scriptfilter] Code 1: 08:55:22 workflow.py:2225 DEBUG    Workflow version : 0.2.0
08:55:22 workflow.py:1628 DEBUG    Reading settings from `/Users/fonnescj/Library/Application Support/Alfred 3/Workflow Data/com.chrisbro.bear/settings.json` ...
08:55:22 workflow.py:2514 DEBUG    Update check not due
08:55:22 workflow.py:1859 DEBUG    Loading cached data from : /Users/fonnescj/Library/Caches/com.runningwithcrayons.Alfred-3/Workflow Data/com.chrisbro.bear/__workflow_update_status.cpickle
08:55:22 workflow.py:2444 DEBUG    update_data : {u'available': True, u'version': u'0.3.1', u'download_url': u'https://github.com/chrisbro/alfred-bear/releases/download/0.3.1/alfred-bear.alfredworkflow'}
08:55:22 search.py:37 DEBUG    Started search workflow
08:55:22 search.py:60 DEBUG    [u'foo']
08:55:22 search.py:42 DEBUG    Searching notes for foo
08:55:22 search.py:97 DEBUG    Searching notes
08:55:22 workflow.py:1748 DEBUG    Data `db_path` stored in `cpickle` format
08:55:22 workflow.py:1763 DEBUG    Stored data loaded from : /Users/fonnescj/Library/Application Support/Alfred 3/Workflow Data/com.chrisbro.bear/db_path.cpickle
08:55:22 queries.py:87 DEBUG    /Users/fonnescj/Library/Containers/net.shinyfrog.bear/Data/Library/Application Support/net.shinyfrog.bear/database.sqlite
08:55:22 queries.py:91 DEBUG    SELECT DISTINCT   ZUNIQUEIDENTIFIER, ZTITLE FROM    ZSFNOTE WHERE    ZARCHIVED=0    AND ZTRASHED=0    AND lower(ZTITLE) LIKE lower('%foo%')
08:55:22 workflow.py:2243 ERROR    no such table: ZSFNOTE
Traceback (most recent call last):
  File "/Users/fonnescj/Library/Application Support/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.7BF3C156-8E64-48F7-BBC7-4A936C843A61/workflow/workflow.py", line 2236, in run
    func(self)
  File "search.py", line 43, in main
    execute_search_query(args)
  File "search.py", line 98, in execute_search_query
    results = queries.search_notes_by_title(WORKFLOW, LOGGER, query)
  File "/Users/fonnescj/Library/Application Support/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.7BF3C156-8E64-48F7-BBC7-4A936C843A61/queries.py", line 56, in search_notes_by_title
    return run_query(workflow, log, sql_query)
  File "/Users/fonnescj/Library/Application Support/Alfred 3/Alfred.alfredpreferences/workflows/user.workflow.7BF3C156-8E64-48F7-BBC7-4A936C843A61/queries.py", line 92, in run_query
    cursor.execute(sql)
OperationalError: no such table: ZSFNOTE
08:55:22 workflow.py:2267 DEBUG    Workflow finished in 0.029 seconds.

[dmitrym0]

I've got the same issue. Any suggestions?

[solobat]

You need to set the db_path environment variable to make it work. It will probably have the value of: /Users/{your-usernme}/Library/Containers/net.shinyfrog.bear/Data/Documents/Application Data/database.sqlite

[amkirk]

@chrisbro Yeah, it looks like Bear has modified the database that they're using. The old one at
/Library/Containers/net.shinyfrog.bear/Data/Library/Application Support/net.shinyfrog.bear/database.sqlite
still exists but doesn't have anything in it.

The new one is at "/Library/Containers/net.shinyfrog.bear/Data/Documents/Application Data/database.sqlite". I modified queries.py with this address and deleted the stored workflow data and the search worked fine.