{"action":"created","issue":{"url":"https://api.github.com/repos/deanishe/alfred-workflow/issues/58","labels_url":"https://api.github.com/repos/deanishe/alfred-workflow/issues/58/labels{/name}","comments_url":"https://api.github.com/repos/deanishe/alfred-workflow/issues/58/comments","events_url":"https://api.github.com/repos/deanishe/alfred-workflow/issues/58/events","html_url":"https://github.com/deanishe/alfred-workflow/issues/58","id":52951034,"number":58,"title":"FTS filtering","user":{"login":"smargh","id":5077225,"avatar_url":"https://avatars.githubusercontent.com/u/5077225?v=3","gravatar_id":"","url":"https://api.github.com/users/smargh","html_url":"https://github.com/smargh","followers_url":"https://api.github.com/users/smargh/followers","following_url":"https://api.github.com/users/smargh/following{/other_user}","gists_url":"https://api.github.com/users/smargh/gists{/gist_id}","starred_url":"https://api.github.com/users/smargh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smargh/subscriptions","organizations_url":"https://api.github.com/users/smargh/orgs","repos_url":"https://api.github.com/users/smargh/repos","events_url":"https://api.github.com/users/smargh/events{/privacy}","received_events_url":"https://api.github.com/users/smargh/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":43,"created_at":"2014-12-27T07:37:01Z","updated_at":"2015-01-27T03:26:59Z","closed_at":null,"body":"I thought it would be cleaner to discuss this feature and its implementation details in a separate issue.\r\n\r\nThe way I see it, there are a few distinct issues that need to be discussed:\r\n\r\n+ [ ] how does the `filter()` API accommodate both the FTS backend and the current iterating backend?\r\n+ [ ] what is the minimal amount of functionality for the FTS searching for v2 of `Alfred-Workflow`?\r\n+ [ ] what is the FTS searching API?\r\n\r\nOnce these are settled, we can focus solely on making the FTS functionality rock-solid. \r\n\r\nHere are my initial thoughts:\r\n\r\n1. add a `protocol` param which accepts either \"fts\" or \"iter\" (or whatever you want to call the current implementation).\r\n2. I say the current 3 flags (and 4th for the combination) is sufficient. If wf author really wants initial based filtering, he has the other protocol. \r\n3. I'm currently using a class (`FTSDatabase`) to represent the sqlite database. You create the database, its table, its columns, its tokenizer, etc and then search that database by interfacing with this class. I think having this class, but keeping it clean, simple, and low-level is best. Create the various query types in the `filter()` (or some sub-function), structure the data in `filter()`, etc. All `FTSDatabase` does is create an indexed database and allow you to search that data. \r\n\r\nPS. I'm not certain what commit you were looking at, but database creation does use sqlite's default parameter substitution (see [here](https://github.com/smargh/alfred-index-demo/blob/master/src/fts.py#L81)). I did tho just fix the searching so that the [query uses it as well](https://github.com/smargh/alfred-index-demo/blob/master/src/fts.py#L94).\r\n\r\nOthers thoughts?"},"comment":{"url":"https://api.github.com/repos/deanishe/alfred-workflow/issues/comments/71584178","html_url":"https://github.com/deanishe/alfred-workflow/issues/58#issuecomment-71584178","issue_url":"https://api.github.com/repos/deanishe/alfred-workflow/issues/58","id":71584178,"user":{"login":"smargh","id":5077225,"avatar_url":"https://avatars.githubusercontent.com/u/5077225?v=3","gravatar_id":"","url":"https://api.github.com/users/smargh","html_url":"https://github.com/smargh","followers_url":"https://api.github.com/users/smargh/followers","following_url":"https://api.github.com/users/smargh/following{/other_user}","gists_url":"https://api.github.com/users/smargh/gists{/gist_id}","starred_url":"https://api.github.com/users/smargh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smargh/subscriptions","organizations_url":"https://api.github.com/users/smargh/orgs","repos_url":"https://api.github.com/users/smargh/repos","events_url":"https://api.github.com/users/smargh/events{/privacy}","received_events_url":"https://api.github.com/users/smargh/received_events","type":"User","site_admin":false},"created_at":"2015-01-27T03:26:59Z","updated_at":"2015-01-27T03:26:59Z","body":"FWIW, I wanted to detail some of my API thinking.\r\n\r\nFirst, I made `schema` a required init param because [1] it makes things much easier (instead of trying to auto-create it when needed in the most efficient way each time) and [2] makes explicit the most important detail. The workflow author will need to feed consistent data into `Index`, where each `dict` has the exact same keys. Forcing him/her to state the keys will hopefully keep dumb data from getting in (or at least make debugging it easier for him/her).\r\n\r\nSecond, as I state in a comment on line 20, the `sqlite3.Connection` object needs to persist for the entirety of the `Index` object's life so that the various custom functions (like `compress`, `uncompress`, and `rank`) all function properly at their various points. I did extensive testing, and generating new `Connection` objects broke functions.\r\n\r\nThird, I moved away from the `content={table}` FTS4 approach because this did not allow for the data compression. Basically, it seems that using the `content` param allows for implicit triggers that keep data in the specified table search-indexed. However, the trigger is hidden and doesn't seem to respect the `compress` and `uncompress` functions. Again, I tested this. \r\n\r\nFourth, I use the `compress` and `uncompress` functions for the same reason as Kapeli (see the link in my comment above). Basically, I use his trick to allow for suffix and contains searches, but the strings used for searching a big and so large datasets create large files. The compression allows the actual file written to disk to be as small as possible, while keeping the advanced search capabilities working.\r\n\r\nFifth, I split `insert()` and `insertmany()` for the sake of explicitness (trying to embrace and really understand the Zen of Python). No \"magic\" under the covers, just different methods for different (if related) tasks. \r\n\r\nSixth, I allow for `update()`, but within a limited scope. Right now, the searching methods (more on that later) will always return the `score` and the `rowid`. The `rowid` is fixed for each item, so you can use that to update an item's info. Since this is how SQLite would do it, this is how I do it. Again, no \"magic\", but functionality is there if you need it.\r\n\r\nSeventh, I made the `findall()` method because I wanted a Python searching function. Why make people write SQL and pass a string into a search function? And why make people learn the odd conventions for the various search types (substring, suffix, prefix). However, the flexibility of the searching also makes for a more complex API. I tried to make it straight forward, but powerful. I feel good about the power; you can basically create any search using the `findall()` function. But I'm not so sure about its clarity. I'd love to get your feedback on that.\r\n\r\nEighth, since some people do know SQL, and since the `findall()` function does have some limitations, I also made the `sql_search()` method. This takes a single string as input and tries to use it as a SQL query. Nothing more, nothing less.\r\n\r\nNinth, since the `sqlite3.Connection` needs to be open for the duration of the `Index` object's life, user's also need to `close()` the connection. At this stage, I don't see a way around this. Do you? Would the `__del__` magic method be useful?\r\n\r\nTenth, I wanted some flexibility allowed in the column weighting, so I allowed for tuples on initialization as well as an optional param on `sql_search()`. Should there be an optional param on `findall()`? Should I force any ranking specification to happen on initialization, and drop all later overrides?\r\n\r\nThat's where I was coming from and some of my future questions. Like I said, I finally feel good about this, so I'm hoping we're close to done on this particular problem."}}