Jump to content
dfay

SQLite query object for workflows

Recommended Posts

Could we get native support for querying a SQLite database?  It seems this is already being done in a lot of workflows (e.g. with Zotero, Mail, Ulysses) and/or is needed for updates (Notes).  I am faced with the prospect of reverse-engineering one of these to try to write a workflow that would query a MaxQDA coding scheme.  But the use of SQLite seems so widespread it might be helpful to include it natively in Alfred.  Or if not in Alfred itself, in one of the workflow libraries (my vote is for python since @deanishe has probably done 90% of the work required already....).

 

 

Share this post


Link to post
Share on other sites
7 minutes ago, dfay said:

Could we get native support for querying a SQLite database?

 

I don't think that could offer a whole lot more than directly using the sqlite3 command-line tool (which outputs CSV). Support for JSON, perhaps?

 

You really want native support in the language you're using, so you can use native strings, numbers, date objects etc.

 

20 minutes ago, dfay said:

in one of the workflow libraries

 

I'm not sure there's a whole lot of broadly-useful middle ground between Python's native sqlite3 library and a full-blown ORM like peewee or SQLAlchemy.


peewee is tiny compared to SQLAlchemy (presumably where the name comes from), but it's still a big library and importing it noticeably slows down Script Filters.

 

SQL typically starts to get hairy when you're dealing with relationships between tables, especially many-to-many ones. If a library can't model relationships (and generate corresponding SQL for you), you're likely to be spending most of your time writing the SQL queries, anyway.

 

There are also SQL query builders for generating SQL queries, but not models. Personally, I find these are more work than just using SQL, at least when trying to read somebody else's database, as you end up first writing the query in SQL while you're testing it out in a GUI client, and then you have to translate it to the builder API.

 

Share this post


Link to post
Share on other sites
40 minutes ago, nikivi said:

Here is one workflow that queries SQLite in Go

 

It's not a good idea to use SQLite for something like that (in Go). It adds a large, non-native dependency (sqlite, which is C), and it isn't going to be meaningfully faster than storing the repos as JSON unless you have several thousand repos.

 

Share this post


Link to post
Share on other sites
17 minutes ago, deanishe said:

unless you have several thousand repos

I actually do have several thousand repos. 🙂 

 

And this workflow is insanely fast. All it lacks is fuzzy searching. But I will add a PR for that soon. I hope it is possible.

Edited by nikivi

Share this post


Link to post
Share on other sites
25 minutes ago, deanishe said:

No, you have 30 repos.

 

I meant it in the context of the GitHub Jump workflow which searches repos users starred. In my case its a bit over 4,000 thus SQLite DB is really noticeable.

 

I have it run the `update` script every evening so it always keeps the index up-to-date and it's pretty amazing. Here is the macro that updates the index:

 

5a58bbcf4d548_2018-01-12at14_44.png.e865ef4c83fc280947d3529fcc062e3a.png

 

This one workflow means I have my entire GitHub world with less than 1 second access to open issues, view repo, make releases to my own repos, clone to a directory or view PR's. The author of the workflow didn't merge my PR that adds my bindings so I added the modified workflow to Small Workflows giving credit to original author.

 

I plan to add fuzzy search to it though so I can do queries like `face reactjs` to show me Reactjs repo because currently I have to type the entire name in full if there are many conflicting entries:

 

5a58bc90e1a79_2018-01-12at14_47.png.c2ba2667f6c29a017498fff28ba70cfd.png

 

Fuzzy searching would solve this. I was thinking of wrapping the workflow around AwGo to take use of what AwGo does with fuzzy searching but not sure how to combine it with the original workflow. Will try look at it again. Maybe I can extract the AwGo portion that does the fuzzy searching and apply it on the results somehow.

 

I also plan to do a similar workflow for GitHub users a person follows. To quickly jump to their profiles, search their repos, search their stars even (if API allows) and more.

 

 

 

Edited by nikivi

Share this post


Link to post
Share on other sites

Ah, right. My bad. I hadn't realised it also indexes your starred repos (the README just says "your repos").

 

In that case, then yeah, there is a decent reason to use SQLite.

 

But you'll lose all the performance of SQLite if you try to apply AwGo's fuzzy search to it.

Share this post


Link to post
Share on other sites
4 minutes ago, deanishe said:

But you'll lose all the performance of SQLite if you try to apply AwGo's fuzzy search to it

 

Yeah I thought that'd be the case. Oh well. The workflow is good as it is.

Share this post


Link to post
Share on other sites

You can also simulate a fuzzy search with SQLite by interspersing your query with %. I'm pretty sure this is how Alfred's fuzzy search for applications and preference panes works.


Alfred keeps its cache of apps and pref panes in ~/Library/Application Support/Alfred 3/Databases/filecache.alfdb, which is a normal SQLite3 database. When I enter kotor as my query, Alfred finds Knights of the Old Republic.app and I'm pretty sure the query it's actually running to find this is:

SELECT * FROM `files` WHERE `files`.`nameSplit` LIKE '%k%o%t%o%r%'

The problem with doing this vs AwGo's fuzzy search is that the results aren't ranked in any way. But if Alfred's knowledge is turned on, it will do the ranking for you.

Edited by deanishe

Share this post


Link to post
Share on other sites
10 hours ago, deanishe said:

But you'll lose all the performance of SQLite if you try to apply AwGo's fuzzy search to it.

 

Forget this comment. It's completely wrong. This one was right:

 

15 hours ago, deanishe said:

It's not a good idea to use SQLite for something like that

 

I thought the workflow looked interesting, so I had a closer look at the source code.

 

It doesn't actually use SQLite for searching, just as a "dumb" datastore. It still loads every repo into memory on each run and filters it with go-alfred's MatchesTerms function.

 

This kills the main performance advantage of using SQLite (databases get much of their speed from not loading unwanted stuff into memory), so the only speed tradeoff would be AwGo's fuzzy search vs go-alfred's simpler "contains" search (which is still significant, but shouldn't be super-noticeable for ~4000 items).


I'm 99% certain that using an SQL query similar to what I posted above would be the fastest of all, but you'd have to change the database schema to include the full name of each repo, as that is what you want to search against, and it currently isn't stored in the database.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×