github
| html_url | issue_url | id | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
|---|---|---|---|---|---|---|---|---|---|---|---|
| https://github.com/dogsheep/dogsheep-beta/issues/10#issuecomment-686238498 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/10 | 686238498 | MDEyOklzc3VlQ29tbWVudDY4NjIzODQ5OA== | 9599 | 2020-09-03T04:05:05Z | 2020-09-03T04:05:05Z | MEMBER | Since the first two categories are `created` and `saved` this one should be called `received`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691557547 | |
| https://github.com/dogsheep/dogsheep-beta/issues/11#issuecomment-686618669 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/11 | 686618669 | MDEyOklzc3VlQ29tbWVudDY4NjYxODY2OQ== | 9599 | 2020-09-03T16:47:34Z | 2020-09-03T16:53:25Z | MEMBER | I think a `is_public` integer column which defaults to 0 would be good here. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 692125110 | |
| https://github.com/dogsheep/dogsheep-beta/issues/13#issuecomment-686774592 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/13 | 686774592 | MDEyOklzc3VlQ29tbWVudDY4Njc3NDU5Mg== | 9599 | 2020-09-03T21:30:21Z | 2020-09-03T21:30:21Z | MEMBER | This is partially supported: the custom search SQL we run doesn't escape them, but the `?_search` used to calculate facet counts does. So this is a bug. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 692386625 | |
| https://github.com/dogsheep/dogsheep-beta/issues/15#issuecomment-695124698 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/15 | 695124698 | MDEyOklzc3VlQ29tbWVudDY5NTEyNDY5OA== | 9599 | 2020-09-18T23:17:38Z | 2020-09-18T23:17:38Z | MEMBER | This can be part of the demo instance in #6. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 694136490 | |
| https://github.com/dogsheep/dogsheep-beta/issues/16#issuecomment-694548909 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/16 | 694548909 | MDEyOklzc3VlQ29tbWVudDY5NDU0ODkwOQ== | 9599 | 2020-09-17T23:15:09Z | 2020-09-17T23:15:09Z | MEMBER | I have sort by date now, #21. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 694493566 | |
| https://github.com/dogsheep/dogsheep-beta/issues/16#issuecomment-695851036 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/16 | 695851036 | MDEyOklzc3VlQ29tbWVudDY5NTg1MTAzNg== | 9599 | 2020-09-20T23:34:57Z | 2020-09-20T23:34:57Z | MEMBER | Really basic starting point is to add facet by date. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 694493566 | |
| https://github.com/dogsheep/dogsheep-beta/issues/16#issuecomment-695877627 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/16 | 695877627 | MDEyOklzc3VlQ29tbWVudDY5NTg3NzYyNw== | 9599 | 2020-09-21T02:42:29Z | 2020-09-21T02:42:29Z | MEMBER | Fun twist: assuming `timestamp` is always stored as UTC, I need the interface to be timezone aware so I can see e.g. everything from 4th July 2020 in the San Francisco timezone definition of 4th July 2020. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 694493566 | |
| https://github.com/dogsheep/dogsheep-beta/issues/17#issuecomment-687880459 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/17 | 687880459 | MDEyOklzc3VlQ29tbWVudDY4Nzg4MDQ1OQ== | 9599 | 2020-09-06T19:36:32Z | 2020-09-06T19:36:32Z | MEMBER | At some point I may even want to support search types which are indexed from (and inflated from) more than one database file. I'm going to ignore that for the moment though. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 694500679 | |
| https://github.com/dogsheep/dogsheep-beta/issues/17#issuecomment-689226390 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/17 | 689226390 | MDEyOklzc3VlQ29tbWVudDY4OTIyNjM5MA== | 9599 | 2020-09-09T00:36:07Z | 2020-09-09T00:36:07Z | MEMBER | Alternative names: - type - record_type - doctype I think `type` is right. It matches what Elasticsearch used to call their equivalent of this (before they removed the feature!). https://www.elastic.co/guide/en/elasticsearch/reference/current/removal-of-types.html | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 694500679 | |
| https://github.com/dogsheep/dogsheep-beta/issues/18#issuecomment-688622995 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/18 | 688622995 | MDEyOklzc3VlQ29tbWVudDY4ODYyMjk5NQ== | 9599 | 2020-09-08T05:15:21Z | 2020-09-08T05:15:21Z | MEMBER | Alternatively it could run as it does now but add a `DELETE FROM index1.search_index WHERE key not in (select key from ...)`. I'm not sure which would be more efficient. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 695553522 | |
| https://github.com/dogsheep/dogsheep-beta/issues/18#issuecomment-688623097 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/18 | 688623097 | MDEyOklzc3VlQ29tbWVudDY4ODYyMzA5Nw== | 9599 | 2020-09-08T05:15:51Z | 2020-09-08T05:15:51Z | MEMBER | I'm inclined to go with the first, simpler option. I have longer term plans for efficient incremental index updates based on clever trickery with triggers. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 695553522 | |
| https://github.com/dogsheep/dogsheep-beta/issues/19#issuecomment-688625430 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/19 | 688625430 | MDEyOklzc3VlQ29tbWVudDY4ODYyNTQzMA== | 9599 | 2020-09-08T05:24:50Z | 2020-09-08T05:24:50Z | MEMBER | I thought about allowing tables to define a incremental indexing SQL query - maybe something that can return just records touched in the past hour, or records since a recorded "last indexed record" value. The problem with this is deletes - if you delete a record, how does the indexer know to remove it? See #18 - that's already caused problems. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 695556681 | |
| https://github.com/dogsheep/dogsheep-beta/issues/19#issuecomment-688626037 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/19 | 688626037 | MDEyOklzc3VlQ29tbWVudDY4ODYyNjAzNw== | 9599 | 2020-09-08T05:27:07Z | 2020-09-08T05:27:07Z | MEMBER | A really clever way to do this would be with triggers. The indexer script would add triggers to each of the database tables that it is indexing - each in their own database. Those triggers would then maintain a `_index_queue_` table. This table would record the primary key of rows that are added, modified or deleted. The indexer could then work by reading through the `_index_queue_` table, re-indexing (or deleting) just the primary keys listed there, and then emptying the queue once it has finished. This would add a small amount of overhead to insert/update/delete queries run against the table. My hunch is that the overhead would be miniscule, but I could still allow people to opt-out for tables that are so high traffic that this would matter. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 695556681 | |
| https://github.com/dogsheep/dogsheep-beta/issues/2#issuecomment-685115519 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/2 | 685115519 | MDEyOklzc3VlQ29tbWVudDY4NTExNTUxOQ== | 9599 | 2020-09-01T20:31:57Z | 2020-09-01T20:31:57Z | MEMBER | Actually this doesn't work: you can't turn on stemming for specific tables, because all of the content goes into a single `search_index` table which is configured the same way. So stemming needs to be a global option. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 689809225 | |
| https://github.com/dogsheep/dogsheep-beta/issues/2#issuecomment-685121074 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/2 | 685121074 | MDEyOklzc3VlQ29tbWVudDY4NTEyMTA3NA== | 9599 | 2020-09-01T20:42:00Z | 2020-09-01T20:42:00Z | MEMBER | Documentation at the bottom of the Usage section here: https://github.com/dogsheep/dogsheep-beta/blob/0.2/README.md#usage | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 689809225 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-694551406 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 694551406 | MDEyOklzc3VlQ29tbWVudDY5NDU1MTQwNg== | 9599 | 2020-09-17T23:22:07Z | 2020-09-17T23:22:07Z | MEMBER | Neat, I can debug this with the new `--pdb` option: datasette . --get '/-/beta?q=pycon&sort=oldest' --pdb | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-694551646 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 694551646 | MDEyOklzc3VlQ29tbWVudDY5NDU1MTY0Ng== | 9599 | 2020-09-17T23:22:48Z | 2020-09-17T23:22:48Z | MEMBER | Looks like its happening in a Jinja fragment template for one of the results: ``` /Users/simon/Dropbox/Development/dogsheep-beta/dogsheep_beta/__init__.py(169)process_results() -> output = compiled.render({**result, **{"json": json}}) /Users/simon/.local/share/virtualenvs/dogsheep-beta-u_po4Rpj/lib/python3.8/site-packages/jinja2/asyncsupport.py(71)render() -> return original_render(self, *args, **kwargs) /Users/simon/.local/share/virtualenvs/dogsheep-beta-u_po4Rpj/lib/python3.8/site-packages/jinja2/environment.py(1090)render() -> self.environment.handle_exception() /Users/simon/.local/share/virtualenvs/dogsheep-beta-u_po4Rpj/lib/python3.8/site-packages/jinja2/environment.py(832)handle_exception() -> reraise(*rewrite_traceback_stack(source=source)) /Users/simon/.local/share/virtualenvs/dogsheep-beta-u_po4Rpj/lib/python3.8/site-packages/jinja2/_compat.py(28)reraise() -> raise value.with_traceback(tb) <template>(5)top-level template code() > /usr/local/opt/python@3.8/Frameworks/Python.framework/Versions/3.8/lib/python3.8/json/__init__.py(341)loads() -> raise TypeError(f'the JSON object must be str, bytes or bytearray, ' ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-694552393 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 694552393 | MDEyOklzc3VlQ29tbWVudDY5NDU1MjM5Mw== | 9599 | 2020-09-17T23:25:01Z | 2020-09-17T23:25:17Z | MEMBER | Ran `locals()` In the debugger: `{'range': <class 'range'>, 'dict': <class 'dict'>, 'lipsum': <function generate_lorem_ipsum at 0x10aeff430>, 'cycler': <class 'jinja2.utils.Cycler'>, 'joiner': <class 'jinja2.utils.Joiner'>, 'namespace': <class 'jinja2.utils.Namespace'>, 'rank': -9.383801886431414, 'rowid': 14297, 'type': 'twitter.db/tweets', 'key': '312658917933076480', 'title': 'Tweet by @chrisstreeter', 'category': 2, 'timestamp': '2013-03-15T20:17:49+00:00', 'search_1': '@simonw are you at pycon? Would love to meet you.', 'display': {'avatar_url': 'https://pbs.twimg.com/profile_images/806275088597204993/38yLHfJi_normal.jpg', 'user_name': 'Chris Streeter', 'screen_name': 'chrisstreeter', 'followers_count': 280, 'tweet_id': 312658917933076480, 'created_at': '2013-03-15T20:17:49+00:00', 'full_text': '@simonw are you at pycon? Would love to meet you.', 'media_urls_2': '[]', 'media_urls': '[]'}, 'json': <module 'json' from '/usr/local/opt/python@3.8/Frameworks/Python.framework/Versions/3.8/lib/python3.8/json/__init__.py'>}` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-694552681 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 694552681 | MDEyOklzc3VlQ29tbWVudDY5NDU1MjY4MQ== | 9599 | 2020-09-17T23:25:54Z | 2020-09-17T23:25:54Z | MEMBER | This is the template fragment it's rendering: ```html+jinja <div style="overflow: hidden;"> <p>Tweet by <a href="https://twitter.com/{{ display.screen_name }}">@{{ display.screen_name }}</a> ({{ display.user_name }}, {{ "{:,}".format(display.followers_count or 0) }} followers) on <a href="https://twitter.com/{{ display.screen_name }}/status/{{ display.tweet_id }}">{{ display.created_at }}</a></p> </p> <blockquote>{{ display.full_text }}</blockquote> {% if display.media_urls and json.loads(display.media_urls) %} {% for url in json.loads(display.media_urls) %} <img src="{{ url }}" style="height: 200px;"> {% endfor %} {% endif %} </div> ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-694553579 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 694553579 | MDEyOklzc3VlQ29tbWVudDY5NDU1MzU3OQ== | 9599 | 2020-09-17T23:28:37Z | 2020-09-17T23:28:37Z | MEMBER | More investigation in pdb: ``` (dogsheep-beta) dogsheep-beta % datasette . --get '/-/beta?q=pycon&sort=oldest' --pdb > /usr/local/opt/python@3.8/Frameworks/Python.framework/Versions/3.8/lib/python3.8/json/__init__.py(341)loads() -> raise TypeError(f'the JSON object must be str, bytes or bytearray, ' (Pdb) list 336 if s.startswith('\ufeff'): 337 raise JSONDecodeError("Unexpected UTF-8 BOM (decode using utf-8-sig)", 338 s, 0) 339 else: 340 if not isinstance(s, (bytes, bytearray)): 341 -> raise TypeError(f'the JSON object must be str, bytes or bytearray, ' 342 f'not {s.__class__.__name__}') 343 s = s.decode(detect_encoding(s), 'surrogatepass') 344 345 if "encoding" in kw: 346 import warnings (Pdb) bytes <class 'bytes'> (Pdb) locals()['s'] Undefined (Pdb) type(locals()['s']) <class 'jinja2.runtime.Undefined'> ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-694554584 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 694554584 | MDEyOklzc3VlQ29tbWVudDY5NDU1NDU4NA== | 9599 | 2020-09-17T23:31:25Z | 2020-09-17T23:31:25Z | MEMBER | I'd prefer it if errors in these template fragments were displayed as errors inline where the fragment should have been inserted, rather than 500ing the whole page - especially since the template fragments are user-provided and could have all kinds of odd errors in them which should be as easy to debug as possible. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-694557425 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 694557425 | MDEyOklzc3VlQ29tbWVudDY5NDU1NzQyNQ== | 9599 | 2020-09-17T23:41:01Z | 2020-09-17T23:41:01Z | MEMBER | I removed all of the `json.loads()` calls and I'm still getting that `Undefined` error. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/24#issuecomment-695113871 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/24 | 695113871 | MDEyOklzc3VlQ29tbWVudDY5NTExMzg3MQ== | 9599 | 2020-09-18T22:30:17Z | 2020-09-18T22:30:17Z | MEMBER | I think I know what's going on here: https://github.com/dogsheep/dogsheep-beta/blob/0f1b951c5131d16f3c8559a8e4d79ed5c559e3cb/dogsheep_beta/__init__.py#L166-L171 This is a logic bug - the `compiled` variable could be the template from the previous loop! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 703970814 | |
| https://github.com/dogsheep/dogsheep-beta/issues/25#issuecomment-695108895 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/25 | 695108895 | MDEyOklzc3VlQ29tbWVudDY5NTEwODg5NQ== | 9599 | 2020-09-18T22:11:32Z | 2020-09-18T22:11:32Z | MEMBER | I'm going to make this a new plugin configuration setting, `template_debug`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 704685890 | |
| https://github.com/dogsheep/dogsheep-beta/issues/25#issuecomment-695109140 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/25 | 695109140 | MDEyOklzc3VlQ29tbWVudDY5NTEwOTE0MA== | 9599 | 2020-09-18T22:12:20Z | 2020-09-18T22:12:20Z | MEMBER | Documented here: https://github.com/dogsheep/dogsheep-beta/blob/534fc9689227eba70e69a45da0cee5820bbda9e1/README.md#datasette-plugin | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 704685890 | |
| https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695855646 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 | 695855646 | MDEyOklzc3VlQ29tbWVudDY5NTg1NTY0Ng== | 9599 | 2020-09-21T00:16:11Z | 2020-09-21T00:16:11Z | MEMBER | Should I do this with offset/limit or should I do proper keyset pagination? I think keyset because then it will work well for the full search interface with no filters or search string. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 705215230 | |
| https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695855723 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 | 695855723 | MDEyOklzc3VlQ29tbWVudDY5NTg1NTcyMw== | 9599 | 2020-09-21T00:16:52Z | 2020-09-21T00:17:53Z | MEMBER | It feels a bit weird to implement keyset pagination against results sorted by `rank` because the ranks could change substantially if the search index gets updated while the user is paginating. I may just ignore that though. If you want reliable pagination you can get it by sorting by date. Maybe it doesn't even make sense to offer pagination if you sort by relevance? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 705215230 | |
| https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695856398 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 | 695856398 | MDEyOklzc3VlQ29tbWVudDY5NTg1NjM5OA== | 9599 | 2020-09-21T00:22:20Z | 2020-09-21T00:22:20Z | MEMBER | I'm going to try for keyset pagination sorted by relevance just as a learning exercise. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 705215230 | |
| https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695856967 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 | 695856967 | MDEyOklzc3VlQ29tbWVudDY5NTg1Njk2Nw== | 9599 | 2020-09-21T00:26:59Z | 2020-09-21T00:26:59Z | MEMBER | It's a shame Datasette doesn't currently have an easy way to implement sorted-by-rank keyset-paginated using a TableView or QueryView. I'll have to do this using the custom SQL query constructed in the plugin: https://github.com/dogsheep/dogsheep-beta/blob/bed9df2b3ef68189e2e445427721a28f4e9b4887/dogsheep_beta/__init__.py#L8-L43 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 705215230 | |
| https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695875274 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 | 695875274 | MDEyOklzc3VlQ29tbWVudDY5NTg3NTI3NA== | 9599 | 2020-09-21T02:28:58Z | 2020-09-21T02:28:58Z | MEMBER | Datasette's implementation is complex because it has to support compound primary keys: https://github.com/simonw/datasette/blob/a258339a935d8d29a95940ef1db01e98bb85ae63/datasette/utils/__init__.py#L88-L114 - but that's not something that's needed for dogsheep-beta. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 705215230 | |
| https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695879237 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 | 695879237 | MDEyOklzc3VlQ29tbWVudDY5NTg3OTIzNw== | 9599 | 2020-09-21T02:53:29Z | 2020-09-21T02:53:29Z | MEMBER | If previous page ended at `2018-02-11T16:32:53+00:00`: ```sql select search_index.rowid, search_index.type, search_index.key, search_index.title, search_index.category, search_index.timestamp, search_index.search_1 from search_index where date("timestamp") = '2018-02-11' and timestamp < '2018-02-11T16:32:53+00:00' order by search_index.timestamp desc, rowid limit 41 ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 705215230 | |
| https://github.com/dogsheep/dogsheep-beta/issues/26#issuecomment-695879531 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/26 | 695879531 | MDEyOklzc3VlQ29tbWVudDY5NTg3OTUzMQ== | 9599 | 2020-09-21T02:55:28Z | 2020-09-21T02:55:54Z | MEMBER | Actually for the tie-breaker it should be something like https://latest.datasette.io/fixtures?sql=select+pk%2C+created%2C+planet_int%2C+on_earth%2C+state%2C+city_id%2C+neighborhood%2C+tags%2C+complex_array%2C+distinct_some_null+from+facetable+where+%28created+%3E+%3Ap1+or+%28created+%3D+%3Ap1+and+%28%28pk+%3E+%3Ap0%29%29%29%29+order+by+created%2C+pk+limit+11&p0=10&p1=2019-01-16+08%3A00%3A00 ```sql where ( created > :p1 or ( created = :p1 and ((pk > :p0)) ) ) ``` But with `rowid` and `timestamp` in place of `pk` and `created`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 705215230 | |
| https://github.com/dogsheep/dogsheep-beta/issues/28#issuecomment-711089647 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/28 | 711089647 | MDEyOklzc3VlQ29tbWVudDcxMTA4OTY0Nw== | 9599 | 2020-10-17T22:43:13Z | 2020-10-17T22:43:13Z | MEMBER | Since my personal Dogsheep uses Datasette authentication, I'm going to need to pass through cookies. https://github.com/simonw/datasette/issues/1020 will solve that in the future but for now I need to solve it explicitly. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 723861683 | |
| https://github.com/dogsheep/dogsheep-beta/issues/29#issuecomment-712266834 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/29 | 712266834 | MDEyOklzc3VlQ29tbWVudDcxMjI2NjgzNA== | 9599 | 2020-10-19T16:01:23Z | 2020-10-19T16:01:23Z | MEMBER | Might just be a documented pattern for how to configure this in YAML templates. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 724759588 | |
| https://github.com/dogsheep/dogsheep-beta/issues/29#issuecomment-747029636 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/29 | 747029636 | MDEyOklzc3VlQ29tbWVudDc0NzAyOTYzNg== | 9599 | 2020-12-16T21:14:03Z | 2020-12-16T21:14:03Z | MEMBER | I think I can do this as a cunning trick in `display_sql`. Consider this example query: https://til.simonwillison.net/tils?sql=select%0D%0A++path%2C%0D%0A++snippet%28til_fts%2C+-1%2C+%27b4de2a49c8%27%2C+%278c94a2ed4b%27%2C+%27...%27%2C+60%29+as+snippet%0D%0Afrom%0D%0A++til%0D%0A++join+til_fts+on+til.rowid+%3D+til_fts.rowid%0D%0Awhere%0D%0A++til_fts+match+escape_fts%28%3Aq%29%0D%0A++and+path+%3D+%27asgi_lifespan-test-httpx.md%27%0D%0A&q=pytest ```sql select path, snippet(til_fts, -1, 'b4de2a49c8', '8c94a2ed4b', '...', 60) as snippet from til join til_fts on til.rowid = til_fts.rowid where til_fts match escape_fts(:q) and path = 'asgi_lifespan-test-httpx.md' ``` The `and path = 'asgi_lifespan-test-httpx.md'` bit means we only get back a specific document - but the snippet highlighting is applied to it. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 724759588 | |
| https://github.com/dogsheep/dogsheep-beta/issues/29#issuecomment-747030964 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/29 | 747030964 | MDEyOklzc3VlQ29tbWVudDc0NzAzMDk2NA== | 9599 | 2020-12-16T21:14:54Z | 2020-12-16T21:14:54Z | MEMBER | To do this I'll need the search term to be passed to the `display_sql` SQL query: https://github.com/dogsheep/dogsheep-beta/blob/4890ec87b5e2ec48940f32c9ad1f5aae25c75a4d/dogsheep_beta/__init__.py#L164-L171 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 724759588 | |
| https://github.com/dogsheep/dogsheep-beta/issues/29#issuecomment-747031608 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/29 | 747031608 | MDEyOklzc3VlQ29tbWVudDc0NzAzMTYwOA== | 9599 | 2020-12-16T21:15:18Z | 2020-12-16T21:15:18Z | MEMBER | Should I pass any other details to the `display_sql` here as well? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 724759588 | |
| https://github.com/dogsheep/dogsheep-beta/issues/29#issuecomment-747034481 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/29 | 747034481 | MDEyOklzc3VlQ29tbWVudDc0NzAzNDQ4MQ== | 9599 | 2020-12-16T21:17:05Z | 2020-12-16T21:17:05Z | MEMBER | I'm just going to add `q` for the moment. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 724759588 | |
| https://github.com/dogsheep/dogsheep-beta/issues/3#issuecomment-684250044 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/3 | 684250044 | MDEyOklzc3VlQ29tbWVudDY4NDI1MDA0NA== | 9599 | 2020-09-01T05:01:09Z | 2020-09-01T05:01:23Z | MEMBER | Maybe this starts out as a custom templated canned query. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 689810340 | |
| https://github.com/dogsheep/dogsheep-beta/issues/3#issuecomment-685961809 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/3 | 685961809 | MDEyOklzc3VlQ29tbWVudDY4NTk2MTgwOQ== | 9599 | 2020-09-02T19:54:24Z | 2020-09-02T19:54:24Z | MEMBER | This should implement search highlighting too, as seen on https://til.simonwillison.net/til/search?q=cloud <img width="1029" alt="TIL_search__cloud" src="https://user-images.githubusercontent.com/9599/92029959-32c6a300-ed1b-11ea-8b5e-971950980c38.png"> | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 689810340 | |
| https://github.com/dogsheep/dogsheep-beta/issues/3#issuecomment-686689612 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/3 | 686689612 | MDEyOklzc3VlQ29tbWVudDY4NjY4OTYxMg== | 9599 | 2020-09-03T18:44:20Z | 2020-09-03T18:44:20Z | MEMBER | Facets are now displayed but selecting them doesn't work yet. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 689810340 | |
| https://github.com/dogsheep/dogsheep-beta/issues/31#issuecomment-748426501 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/31 | 748426501 | MDEyOklzc3VlQ29tbWVudDc0ODQyNjUwMQ== | 9599 | 2020-12-19T06:12:22Z | 2020-12-19T06:12:22Z | MEMBER | I deliberately added support for advanced FTS in https://github.com/dogsheep/dogsheep-beta/commit/cbb2491b85d7ff416d6d429b60109e6c2d6d50b9 for #13 but that's the cause of this bug. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 771316301 | |
| https://github.com/dogsheep/dogsheep-beta/issues/31#issuecomment-748426581 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/31 | 748426581 | MDEyOklzc3VlQ29tbWVudDc0ODQyNjU4MQ== | 9599 | 2020-12-19T06:13:17Z | 2020-12-19T06:13:17Z | MEMBER | One fix for this could be to try running the raw query, but if it throws an error run it again with the query escaped. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 771316301 | |
| https://github.com/dogsheep/dogsheep-beta/issues/31#issuecomment-748426663 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/31 | 748426663 | MDEyOklzc3VlQ29tbWVudDc0ODQyNjY2Mw== | 9599 | 2020-12-19T06:14:06Z | 2020-12-19T06:14:06Z | MEMBER | Looks like I already do that here: https://github.com/dogsheep/dogsheep-beta/blob/9ba4401017ac24ffa3bc1db38e0910ea49de7616/dogsheep_beta/__init__.py#L141-L146 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 771316301 | |
| https://github.com/dogsheep/dogsheep-beta/issues/31#issuecomment-748426877 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/31 | 748426877 | MDEyOklzc3VlQ29tbWVudDc0ODQyNjg3Nw== | 9599 | 2020-12-19T06:16:11Z | 2020-12-19T06:16:11Z | MEMBER | Here's why: if "fts5" in str(e): But the error being raised here is: sqlite3.OperationalError: no such column: to I'm going to attempt the escaped on on every error. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 771316301 | |
| https://github.com/dogsheep/dogsheep-beta/issues/4#issuecomment-684395444 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/4 | 684395444 | MDEyOklzc3VlQ29tbWVudDY4NDM5NTQ0NA== | 9599 | 2020-09-01T06:00:03Z | 2020-09-01T06:00:03Z | MEMBER | I ran `sqlite-utils optimize beta.db` against my test DB and the size reduced from 183M to 176M - and a 450ms search ran in 359ms. So not a huge improvement but still worthwhile. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 689839399 | |
| https://github.com/dogsheep/dogsheep-beta/issues/5#issuecomment-686689366 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/5 | 686689366 | MDEyOklzc3VlQ29tbWVudDY4NjY4OTM2Ng== | 9599 | 2020-09-03T18:43:50Z | 2020-09-03T18:43:50Z | MEMBER | No longer needed thanks to #9 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 689847361 | |
| https://github.com/dogsheep/dogsheep-beta/issues/7#issuecomment-685895540 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/7 | 685895540 | MDEyOklzc3VlQ29tbWVudDY4NTg5NTU0MA== | 9599 | 2020-09-02T17:46:44Z | 2020-09-02T17:46:44Z | MEMBER | Some opet questions about this: - Should I restrict to two exclusive categories here, or should I have a generic category mechanism that can be expanded to more than two? - Should an item be able to exist in more than one category? Do I want to be able to mark an indexed item as both by-me and liked-by-me for example? This question is more interesting if the number of categories is greater than two. - How should this be modeled? Single column, multiple boolean columns, JSON array, m2m against separate table? - What's the best way to make this performant | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691265198 | |
| https://github.com/dogsheep/dogsheep-beta/issues/7#issuecomment-685962280 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/7 | 685962280 | MDEyOklzc3VlQ29tbWVudDY4NTk2MjI4MA== | 9599 | 2020-09-02T19:55:26Z | 2020-09-02T19:59:58Z | MEMBER | Relevant: https://charlesleifer.com/blog/a-tour-of-tagging-schemas-many-to-many-bitmaps-and-more/ SQLite supports bitwise operators Binary AND (&) and Binary OR (|) - I could try those. Not sure how they interact with indexes though. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691265198 | |
| https://github.com/dogsheep/dogsheep-beta/issues/7#issuecomment-685965516 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/7 | 685965516 | MDEyOklzc3VlQ29tbWVudDY4NTk2NTUxNg== | 9599 | 2020-09-02T20:01:54Z | 2020-09-02T20:01:54Z | MEMBER | Relevant post: https://sqlite.org/forum/forumpost/9f06fedaa5 - drh says: > Indexes are one-to-one. There is one entry in the index for each row in the table. > > You are asking for an index that is many-to-one - multiple index entries for each table row. > > A Full-Text Index is basically a many-to-one index. So if all of your array entries really are words, you could probably get this to work using a Full-Text Index. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691265198 | |
| https://github.com/dogsheep/dogsheep-beta/issues/7#issuecomment-685966361 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/7 | 685966361 | MDEyOklzc3VlQ29tbWVudDY4NTk2NjM2MQ== | 9599 | 2020-09-02T20:03:29Z | 2020-09-02T20:03:41Z | MEMBER | I'm going to implement the first version of this as an indexed integer `category` column which has 1 for "about me" and 2 for "liked by me" - and space for other category numerals in the future, albeit a row can only belong to one category. I'll think about a full tagging system separately. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691265198 | |
| https://github.com/dogsheep/dogsheep-beta/issues/7#issuecomment-685966707 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/7 | 685966707 | MDEyOklzc3VlQ29tbWVudDY4NTk2NjcwNw== | 9599 | 2020-09-02T20:04:08Z | 2020-09-02T20:04:08Z | MEMBER | I'll make `category` a foreign key to a `categories` table so Datasette can automatically show the `name` column. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691265198 | |
| https://github.com/dogsheep/dogsheep-beta/issues/7#issuecomment-685970384 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/7 | 685970384 | MDEyOklzc3VlQ29tbWVudDY4NTk3MDM4NA== | 9599 | 2020-09-02T20:11:41Z | 2020-09-02T20:11:59Z | MEMBER | Default categories: - 1 = created - 2 = saved | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691265198 | |
| https://github.com/dogsheep/dogsheep-beta/issues/8#issuecomment-685960072 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/8 | 685960072 | MDEyOklzc3VlQ29tbWVudDY4NTk2MDA3Mg== | 9599 | 2020-09-02T19:50:47Z | 2020-09-02T19:50:47Z | MEMBER | This doesn't actually help, because the Datasette table view page doesn't then support adding the `where search_index_fts match :query` bit. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691369691 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686153967 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686153967 | MDEyOklzc3VlQ29tbWVudDY4NjE1Mzk2Nw== | 9599 | 2020-09-03T00:17:16Z | 2020-09-03T00:17:55Z | MEMBER | Maybe I can take advantage of https://sqlite.org/np1queryprob.html here - I could define a SQL query for fetching the "display" version of each item, and include a Jinja template fragment in the configuration as well. Maybe something like this: ```yaml photos.db: photos_with_apple_metadata: sql: |- select sha256 as key, 'Photo in ' || coalesce(place_city, 'unknown') as title, ( select group_concat(normalized_string, ' ') from labels where labels.uuid = photos_with_apple_metadata.uuid ) as search_1, date as timestamp, 1 as category from photos_with_apple_metadata display_sql: |- select sha256, place_city, date from photos_with_apple_metadata where sha256 = :key display: |- <img src="https://photos.simonwillison.net/i/{{ display.sha256 }}.jpeg?w=600"> <p>Taken in {{ display.place_city }} on {{ display.date }}</p> ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686154486 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686154486 | MDEyOklzc3VlQ29tbWVudDY4NjE1NDQ4Ng== | 9599 | 2020-09-03T00:18:54Z | 2020-09-03T00:18:54Z | MEMBER | `display_sql` could be optional. If it's not defined, a `row` object is passed to the template which is the row that's stored in `search_index`. If `display_sql` IS defined then it's executed and the result is made available as a `display` object in addition to the `row` object. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686154627 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686154627 | MDEyOklzc3VlQ29tbWVudDY4NjE1NDYyNw== | 9599 | 2020-09-03T00:19:22Z | 2020-09-03T00:19:22Z | MEMBER | If this performs well enough (100 displayed items will be 100 extra `display_sql` calls) then I'll go with this as the design for the feature. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686158454 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686158454 | MDEyOklzc3VlQ29tbWVudDY4NjE1ODQ1NA== | 9599 | 2020-09-03T00:32:42Z | 2020-09-03T00:32:42Z | MEMBER | If this turns out to be too inefficient I could add a `display` text column to the `search_index` table which is designed to be populated with arbitrary JSON by the indexing query, which can then be used to render the template fragment. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686163754 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686163754 | MDEyOklzc3VlQ29tbWVudDY4NjE2Mzc1NA== | 9599 | 2020-09-03T00:46:21Z | 2020-09-03T00:46:21Z | MEMBER | Challenge: the `dogsheep-beta.yml` configuration file that is passed to the `dogsheep-beta index` command needs to also be made available to Datasette itself, so that it can read the configuration. Let's say it can either be duplicated in the `plugins` configuration block of the `metadata.yml` OR you can do this in `metadata.yml`: ```yaml plugins: dogsheep-beta: config_file: dogsheep-beta.yml ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686688963 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686688963 | MDEyOklzc3VlQ29tbWVudDY4NjY4ODk2Mw== | 9599 | 2020-09-03T18:42:59Z | 2020-09-03T18:42:59Z | MEMBER | I'm pleased with how this works now. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686689122 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686689122 | MDEyOklzc3VlQ29tbWVudDY4NjY4OTEyMg== | 9599 | 2020-09-03T18:43:20Z | 2020-09-03T18:43:20Z | MEMBER | Needs documentation. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-beta/issues/9#issuecomment-686767208 | https://api.github.com/repos/dogsheep/dogsheep-beta/issues/9 | 686767208 | MDEyOklzc3VlQ29tbWVudDY4Njc2NzIwOA== | 9599 | 2020-09-03T21:12:14Z | 2020-09-03T21:12:14Z | MEMBER | Documentation: https://github.com/dogsheep/dogsheep-beta/blob/0.4/README.md#custom-results-display | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 691521965 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623193947 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623193947 | MDEyOklzc3VlQ29tbWVudDYyMzE5Mzk0Nw== | 9599 | 2020-05-03T22:36:17Z | 2020-05-03T22:36:17Z | MEMBER | I'm going to use [osxphotos](https://github.com/RhetTbull/osxphotos) for this. Since I've already got code to upload photos and insert them into a table based on their `sha256` hash, my first go at this will be to import data using the tool and foreign-key it to the `sha256` hash in the existing table. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623195197 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623195197 | MDEyOklzc3VlQ29tbWVudDYyMzE5NTE5Nw== | 9599 | 2020-05-03T22:44:33Z | 2020-05-03T22:44:33Z | MEMBER | Command will be this: $ photos-to-sqlite apple-photos photos.db This will populate a `apple_photos` table with the data imported by the `osxphotos` library, plus the calculated sha256. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623198653 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623198653 | MDEyOklzc3VlQ29tbWVudDYyMzE5ODY1Mw== | 9599 | 2020-05-03T23:09:57Z | 2020-05-03T23:09:57Z | MEMBER | For locations: I'll add `place_x` columns for all of these: ``` (Pdb) photo.place.address._asdict() {'street': None, 'sub_locality': None, 'city': 'Loreto', 'sub_administrative_area': 'Loreto', 'state_province': 'BCS', 'postal_code': None, 'country': 'Mexico', 'iso_country_code': 'MX'} ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623198986 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623198986 | MDEyOklzc3VlQ29tbWVudDYyMzE5ODk4Ng== | 9599 | 2020-05-03T23:12:31Z | 2020-05-03T23:12:46Z | MEMBER | To get the taken date in UTC: ``` from datetime import timezone (Pdb) photo.date.astimezone(timezone.utc).isoformat() '2018-02-13T20:21:31.620000+00:00' (Pdb) photo.date.astimezone(timezone.utc).isoformat().split(".") ['2018-02-13T20:21:31', '620000+00:00'] (Pdb) photo.date.astimezone(timezone.utc).isoformat().split(".")[0] '2018-02-13T20:21:31' (Pdb) photo.date.astimezone(timezone.utc).isoformat().split(".")[0] + "+00:00" '2018-02-13T20:21:31+00:00' ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623199214 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623199214 | MDEyOklzc3VlQ29tbWVudDYyMzE5OTIxNA== | 9599 | 2020-05-03T23:14:08Z | 2020-05-03T23:14:08Z | MEMBER | Albums have UUIDs: ``` (Pdb) photo.album_info[0].__dict__ {'_uuid': '17816791-ABF3-447B-942C-9FA8065EEBBA', '_db': osxphotos.PhotosDB(dbfile='/Users/simon/Pictures/Photos Library.photoslibrary/database/photos.db'), '_title': 'Geotaggable Photos geotagged'} ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623199701 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623199701 | MDEyOklzc3VlQ29tbWVudDYyMzE5OTcwMQ== | 9599 | 2020-05-03T23:17:38Z | 2020-05-03T23:17:38Z | MEMBER | Record burst_uuid as a column: ``` (Pdb) with_bursts[0]._info["burstUUID"] '703FAA23-57BF-40B4-8A33-D9CEB143391B' ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623199750 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623199750 | MDEyOklzc3VlQ29tbWVudDYyMzE5OTc1MA== | 9599 | 2020-05-03T23:17:58Z | 2020-05-03T23:17:58Z | MEMBER | Reading this source code is really useful for figuring out how to store a photo in a DB table: https://github.com/RhetTbull/osxphotos/blob/7444b6d173918a3ad2a07aefce5ecf054786c787/osxphotos/photoinfo.py | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/1#issuecomment-623232984 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/1 | 623232984 | MDEyOklzc3VlQ29tbWVudDYyMzIzMjk4NA== | 9599 | 2020-05-04T02:41:32Z | 2020-05-04T02:41:32Z | MEMBER | Needs documentation. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 602533300 | |
| https://github.com/dogsheep/dogsheep-photos/issues/12#issuecomment-618796564 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/12 | 618796564 | MDEyOklzc3VlQ29tbWVudDYxODc5NjU2NA== | 9599 | 2020-04-24T04:35:25Z | 2020-04-24T04:35:25Z | MEMBER | Code: https://github.com/dogsheep/photos-to-sqlite/blob/a388cf1f1b6b67752d669466cda8b171b6582171/photos_to_sqlite/cli.py#L109-L114 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 606033104 | |
| https://github.com/dogsheep/dogsheep-photos/issues/13#issuecomment-620273692 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/13 | 620273692 | MDEyOklzc3VlQ29tbWVudDYyMDI3MzY5Mg== | 9599 | 2020-04-27T22:42:50Z | 2020-04-27T22:42:50Z | MEMBER | ``` >>> def ext_counts(directory): ... counts = {} ... for path in pathlib.Path(directory).glob("**/*"): ... ext = path.suffix ... counts[ext] = counts.get(ext, 0) + 1 ... return counts ... >>> >>> ext_counts("/Users/simon/Pictures/Photos Library.photoslibrary/originals") {'': 16, '.heic': 15478, '.jpeg': 21691, '.mov': 946, '.png': 2262, '.gif': 38, '.mp4': 116, '.aae': 2} ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 607888367 | |
| https://github.com/dogsheep/dogsheep-photos/issues/13#issuecomment-620309185 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/13 | 620309185 | MDEyOklzc3VlQ29tbWVudDYyMDMwOTE4NQ== | 9599 | 2020-04-28T00:39:45Z | 2020-04-28T00:39:45Z | MEMBER | I'm going to leave this until I have the mechanism for associating a live photo video with the photo. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 607888367 | |
| https://github.com/dogsheep/dogsheep-photos/issues/14#issuecomment-620769348 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/14 | 620769348 | MDEyOklzc3VlQ29tbWVudDYyMDc2OTM0OA== | 9599 | 2020-04-28T18:09:21Z | 2020-04-28T18:09:21Z | MEMBER | Pricing is pretty good: free for first 1,000 calls per month, then $1.50 per thousand after that. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 608512747 | |
| https://github.com/dogsheep/dogsheep-photos/issues/14#issuecomment-620771067 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/14 | 620771067 | MDEyOklzc3VlQ29tbWVudDYyMDc3MTA2Nw== | 9599 | 2020-04-28T18:12:34Z | 2020-04-28T18:15:38Z | MEMBER | Python library docs: https://googleapis.dev/python/vision/latest/index.html I'm creating a new project for this called simonwillison-photos: https://console.cloud.google.com/projectcreate https://console.cloud.google.com/home/dashboard?project=simonwillison-photos Then I enabled the Vision API. The direct link to https://console.cloud.google.com/flows/enableapi?apiid=vision-json.googleapis.com which they provided in the docs didn't work - it gave me a "You don't have sufficient permissions to use the requested API" error - but starting at the "Enable APIs" page and searching for it worked fine. I created a new service account as an "owner" of that project: https://console.cloud.google.com/apis/credentials/serviceaccountkey (and complained about it on Twitter and through their feedback form) `pip install google-cloud-vision` ```python from google.cloud import vision client = vision.ImageAnnotatorClient.from_service_account_file("simonwillison-photos-18c570b301fe.json") # Photo of a lemur response = client.annotate_image( { "image": { "source": { "image_uri": "https://photos.simonwillison.net/i/1b3414ee9ade67ce04ade9042e6d4b433d1e523c9a16af17f490e2c0a619755b.jpeg" } }, "features": [ {"type": vision.enums.Feature.Type.IMAGE_PROPERTIES}, {"type": vision.enums.Feature.Type.OBJECT_LOCALIZATION}, {"type": vision.enums.Feature.Type.LABEL_DETECTION}, ], } ) response ``` Output is: ``` label_annotations { mid: "/m/09686" description: "Vertebrate" score: 0.9851104021072388 topicality: 0.9851104021072388 } label_annotations { mid: "/m/04rky" description: "Mammal" score: 0.975814163684845 topicality: 0.975814163684845 } label_annotations { mid: "/m/01280g" description: "Wildlife" score: 0.8973650336265564 topicality: 0.8973650336265564 } label_annotations { mid: "/m/02f9pk" description: "Lemur" score: 0.8270352482795715 topicality: 0.8270352482795715 } label_annotations { mid: "/m/0fbf1m" description: "Terrestrial animal" score: 0.7443860769271851 topicality: 0.7443860769271851 } label_annotations { mid: "/m/06z_nw" description: "Tail" score: 0.6934166550636292 topicality: 0.6934166550636292 } label_annotations { mid: "/m/0b5gs" description: "Branch" score: 0.6203985214233398 topicality: 0.6203985214233398 } label_annotations { mid: "/m/05s2s" description: "Plant" score: 0.585474967956543 topicality: 0.585474967956543 } label_annotations { mid: "/m/089v3" description: "Zoo" score: 0.5488107800483704 topicality: 0.5488107800483704 } label_annotations { mid: "/m/02tcwp" description: "Trunk" score: 0.5200017690658569 topicality: 0.5200017690658569 } image_properties_annotation { dominant_colors { colors { color { red: 172.0 green: 146.0 blue: 116.0 } score: 0.24523821473121643 pixel_fraction: 0.027533333748579025 } colors { color { red: 54.0 green: 50.0 blue: 42.0 } score: 0.10449723154306412 pixel_fraction: 0.12893334031105042 } colors { color { red: 141.0 green: 121.0 blue: 97.0 } score: 0.1391485631465912 pixel_fraction: 0.039133332669734955 } colors { color { red: 28.0 green: 25.0 blue: 20.0 } score: 0.08589499443769455 pixel_fraction: 0.11506666988134384 } colors { color { red: 87.0 green: 82.0 blue: 74.0 } score: 0.0845794677734375 pixel_fraction: 0.16113333404064178 } colors { color { red: 121.0 green: 117.0 blue: 108.0 } score: 0.05901569500565529 pixel_fraction: 0.13379999995231628 } colors { color { red: 94.0 green: 83.0 blue: 66.0 } score: 0.049011144787073135 pixel_fraction: 0.03946666792035103 } colors { color { red: 155.0 green: 117.0 blue: 90.0 } score: 0.04164913296699524 pixel_fraction: 0.0023333332501351833 } colors { color { red: 178.0 green: 143.0 blue: 102.0 } score: 0.02993861958384514 pixel_fraction: 0.0012666666880249977 } colors { color { red: 61.0 green: 51.0 blue: 35.0 } score: 0.027391711249947548 pixel_fraction: 0.01953333243727684 } } } crop_hints_annotation { crop_hints { bounding_poly { vertices { x: 2073 } vertices { x: 4008 } vertices { x: 4008 y: 3455 } vertices { x: 2073 y: 3455 } } confidence: 0.65625 importance_fraction: 0.746666669845581 } } localized_object_annotations { mid: "/m/0jbk" name: "Animal" score: 0.7008256912231445 bounding_poly { normalized_vertices { x: 0.0390297956764698 y: 0.26235100626945496 } normalized_vertices { x: 0.8466796875 y: 0.26235100626945496 } normalized_vertices { x: 0.8466796875 y: 0.9386426210403442 } normalized_vertices { x: 0.0390297956764698 y: 0.9386426210403442 } } } ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 608512747 | |
| https://github.com/dogsheep/dogsheep-photos/issues/14#issuecomment-620771698 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/14 | 620771698 | MDEyOklzc3VlQ29tbWVudDYyMDc3MTY5OA== | 9599 | 2020-04-28T18:13:48Z | 2020-04-28T18:13:48Z | MEMBER | For face detection: ``` {"type": vision.enums.Feature.Type.Type.FACE_DETECTION} ``` For OCR: ``` {"type": vision.enums.Feature.Type.DOCUMENT_TEXT_DETECTION} ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 608512747 | |
| https://github.com/dogsheep/dogsheep-photos/issues/14#issuecomment-620772190 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/14 | 620772190 | MDEyOklzc3VlQ29tbWVudDYyMDc3MjE5MA== | 9599 | 2020-04-28T18:14:43Z | 2020-04-28T18:14:43Z | MEMBER | Database schema for this will require some thought. Just dumping the output into a JSON column isn't going to be flexible enough - I want to be able to FTS against labels and OCR text, and potentially query against other characteristics too. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 608512747 | |
| https://github.com/dogsheep/dogsheep-photos/issues/14#issuecomment-620774507 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/14 | 620774507 | MDEyOklzc3VlQ29tbWVudDYyMDc3NDUwNw== | 9599 | 2020-04-28T18:19:06Z | 2020-04-28T18:19:06Z | MEMBER | The default timeout is a bit aggressive and sometimes failed for me if my resizing proxy took too long to fetch and resize the image. `client.annotate_image(..., timeout=3.0)` may be worth trying. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 608512747 | |
| https://github.com/dogsheep/dogsheep-photos/issues/15#issuecomment-623723026 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/15 | 623723026 | MDEyOklzc3VlQ29tbWVudDYyMzcyMzAyNg== | 9599 | 2020-05-04T21:41:30Z | 2020-05-04T21:41:30Z | MEMBER | I'm going to put these in a table called `apple_photos_scores` - I'll also pull in the following columns from the `ZGENERICASSET` table: * `ZOVERALLAESTHETICSCORE` * `ZCURATIONSCORE` * `ZHIGHLIGHTVISIBILITYSCORE` * `ZPROMOTIONSCORE` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612151767 | |
| https://github.com/dogsheep/dogsheep-photos/issues/15#issuecomment-623723687 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/15 | 623723687 | MDEyOklzc3VlQ29tbWVudDYyMzcyMzY4Nw== | 9599 | 2020-05-04T21:43:06Z | 2020-05-04T21:43:06Z | MEMBER | It looks like I can map the photos I'm importing to these tables using the `ZUUID` column on `ZGENERICASSET` to get a `Z_PK` which then maps to the rows in `ZGENERICASSET`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612151767 | |
| https://github.com/dogsheep/dogsheep-photos/issues/15#issuecomment-623730934 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/15 | 623730934 | MDEyOklzc3VlQ29tbWVudDYyMzczMDkzNA== | 9599 | 2020-05-04T22:00:38Z | 2020-05-04T22:00:48Z | MEMBER | Here's the query to create the new table: ```sql create table apple_photos_scores as select ZGENERICASSET.ZUUID, ZGENERICASSET.ZOVERALLAESTHETICSCORE, ZGENERICASSET.ZCURATIONSCORE, ZGENERICASSET.ZPROMOTIONSCORE, ZGENERICASSET.ZHIGHLIGHTVISIBILITYSCORE, ZCOMPUTEDASSETATTRIBUTES.ZBEHAVIORALSCORE, ZCOMPUTEDASSETATTRIBUTES.ZFAILURESCORE, ZCOMPUTEDASSETATTRIBUTES.ZHARMONIOUSCOLORSCORE, ZCOMPUTEDASSETATTRIBUTES.ZIMMERSIVENESSSCORE, ZCOMPUTEDASSETATTRIBUTES.ZINTERACTIONSCORE, ZCOMPUTEDASSETATTRIBUTES.ZINTERESTINGSUBJECTSCORE, ZCOMPUTEDASSETATTRIBUTES.ZINTRUSIVEOBJECTPRESENCESCORE, ZCOMPUTEDASSETATTRIBUTES.ZLIVELYCOLORSCORE, ZCOMPUTEDASSETATTRIBUTES.ZLOWLIGHT, ZCOMPUTEDASSETATTRIBUTES.ZNOISESCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTCAMERATILTSCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTCOMPOSITIONSCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTLIGHTINGSCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTPATTERNSCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTPERSPECTIVESCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTPOSTPROCESSINGSCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTREFLECTIONSSCORE, ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTSYMMETRYSCORE, ZCOMPUTEDASSETATTRIBUTES.ZSHARPLYFOCUSEDSUBJECTSCORE, ZCOMPUTEDASSETATTRIBUTES.ZTASTEFULLYBLURREDSCORE, ZCOMPUTEDASSETATTRIBUTES.ZWELLCHOSENSUBJECTSCORE, ZCOMPUTEDASSETATTRIBUTES.ZWELLFRAMEDSUBJECTSCORE, ZCOMPUTEDASSETATTRIBUTES.ZWELLTIMEDSHOTSCORE from attached.ZGENERICASSET join attached.ZCOMPUTEDASSETATTRIBUTES on attached.ZGENERICASSET.Z_PK = attached.ZCOMPUTEDASSETATTRIBUTES.Z_PK; ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612151767 | |
| https://github.com/dogsheep/dogsheep-photos/issues/15#issuecomment-623739934 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/15 | 623739934 | MDEyOklzc3VlQ29tbWVudDYyMzczOTkzNA== | 9599 | 2020-05-04T22:24:26Z | 2020-05-04T22:24:26Z | MEMBER | Twitter thread with some examples of photos that are coming up from queries against these scores: https://twitter.com/simonw/status/1257434670750408705 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612151767 | |
| https://github.com/dogsheep/dogsheep-photos/issues/15#issuecomment-748436115 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/15 | 748436115 | MDEyOklzc3VlQ29tbWVudDc0ODQzNjExNQ== | 8573886 | 2020-12-19T07:43:38Z | 2020-12-19T07:47:36Z | NONE | Hey Simon! I really enjoy datasette so far, just started trying it out today following your iPhone photos [example](https://simonwillison.net/2020/May/21/dogsheep-photos/). I am not sure if you had run into this or not, but it seems like they might have changed one of the column names from `ZGENERICASSET` to `ZASSET`. Should I open a PR? Would change: - [here](https://github.com/dogsheep/dogsheep-photos/blob/master/dogsheep_photos/cli.py#L209-L213) - [here](https://github.com/dogsheep/dogsheep-photos/blob/master/dogsheep_photos/cli.py#L238) - [here](https://github.com/dogsheep/dogsheep-photos/blob/master/dogsheep_photos/cli.py#L240) | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612151767 | |
| https://github.com/dogsheep/dogsheep-photos/issues/15#issuecomment-748436779 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/15 | 748436779 | MDEyOklzc3VlQ29tbWVudDc0ODQzNjc3OQ== | 41546558 | 2020-12-19T07:49:00Z | 2020-12-19T07:49:00Z | CONTRIBUTOR | @nickvazz ZGENERICASSET changed to ZASSET in Big Sur. Here's a list of other changes to the schema in Big Sur: https://github.com/RhetTbull/osxphotos/wiki/Changes-in-Photos-6---Big-Sur | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612151767 | |
| https://github.com/dogsheep/dogsheep-photos/issues/15#issuecomment-748562288 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/15 | 748562288 | MDEyOklzc3VlQ29tbWVudDc0ODU2MjI4OA== | 41546558 | 2020-12-20T04:44:22Z | 2020-12-20T04:44:22Z | CONTRIBUTOR | @nickvazz @simonw I opened a [PR](https://github.com/dogsheep/dogsheep-photos/pull/31) that replaces the SQL for `ZCOMPUTEDASSETATTRIBUTES` to use osxphotos which now exposes all this data and has been updated for Big Sur. I did regression tests to confirm the extracted data is identical, with one exception which should not affect operation: the old code pulled data from `ZCOMPUTEDASSETATTRIBUTES` for missing photos while the main loop ignores missing photos and does not add them to `apple_photos`. The new code does not add rows to the `apple_photos_scores` table for missing photos. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612151767 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623805823 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623805823 | MDEyOklzc3VlQ29tbWVudDYyMzgwNTgyMw== | 9599 | 2020-05-05T02:45:56Z | 2020-05-05T02:45:56Z | MEMBER | I filed an issue with `osxphotos` about this here: https://github.com/RhetTbull/osxphotos/issues/121 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623806085 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623806085 | MDEyOklzc3VlQ29tbWVudDYyMzgwNjA4NQ== | 9599 | 2020-05-05T02:47:18Z | 2020-05-05T02:47:18Z | MEMBER | In https://github.com/RhetTbull/osxphotos/issues/121#issuecomment-623249263 Rhet Turnbull spotted a table called `ZSCENEIDENTIFIER` which looked like it might have the right data, but the columns in it aren't particularly helpful: ``` Z_PK,Z_ENT,Z_OPT,ZSCENEIDENTIFIER,ZASSETATTRIBUTES,ZCONFIDENCE 8,49,1,731,5,0.11834716796875 9,49,1,684,6,0.0233648251742125 10,49,1,1702,1,0.026153564453125 ``` I love the look of those confidence scores, but what do the numbers mean? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623806533 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623806533 | MDEyOklzc3VlQ29tbWVudDYyMzgwNjUzMw== | 9599 | 2020-05-05T02:50:16Z | 2020-05-05T02:50:16Z | MEMBER | I figured there must be a separate database that Photos uses to store the text of the identified labels. I used "Open Files and Ports" in Activity Monitor against the Photos app to try and spot candidates... and found `/Users/simon/Pictures/Photos Library.photoslibrary/database/search/psi.sqlite` - a 53MB SQLite database file. <img width="1365" alt="Item-0_and_Item-0_and_Item-0_and_Item-0" src="https://user-images.githubusercontent.com/9599/81031213-61ea0800-8e40-11ea-8237-cfce4a5128e0.png"> Here's the schema of that file: ``` $ sqlite3 psi.sqlite .schema CREATE TABLE word_embedding(word TEXT, extended_word TEXT, score DOUBLE); CREATE INDEX word_embedding_index ON word_embedding(word); CREATE VIRTUAL TABLE word_embedding_prefix USING fts5(extended_word) /* word_embedding_prefix(extended_word) */; CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_data'(id INTEGER PRIMARY KEY, block BLOB); CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_content'(id INTEGER PRIMARY KEY, c0); CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB); CREATE TABLE IF NOT EXISTS 'word_embedding_prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID; CREATE TABLE groups(category INT2, owning_groupid INT, content_string TEXT, normalized_string TEXT, lookup_identifier TEXT, token_ranges_0 INT8, token_ranges_1 INT8, UNIQUE(category, owning_groupid, content_string, lookup_identifier, token_ranges_0, token_ranges_1)); CREATE TABLE assets(uuid_0 INT, uuid_1 INT, creationDate INT, UNIQUE(uuid_0, uuid_1)); CREATE TABLE ga(groupid INT, assetid INT, PRIMARY KEY(groupid, assetid)); CREATE TABLE collections(uuid_0 INT, uuid_1 INT, startDate INT, endDate INT, title TEXT, subtitle TEXT, keyAssetUUID_0 INT, keyAssetUUID_1 INT, typeAndNumberOfAssets INT32, sortDate DOUBLE, UNIQUE(uuid_0, uuid_1)); CREATE TABLE gc(groupid INT, collectionid INT, PRIMARY KEY(groupid, collectionid)); CREATE VIRTUAL TABLE prefix USING fts5(content='groups', normalized_string, category UNINDEXED, tokenize = 'PSITokenizer'); CREATE TABLE IF NOT EXISTS 'prefix_data'(id INTEGER PRIMARY KEY, block BLOB); CREATE TABLE IF NOT EXISTS 'prefix_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID; CREATE TABLE IF NOT EXISTS 'prefix_docsize'(id INTEGER PRIMARY KEY, sz BLOB); CREATE TABLE IF NOT EXISTS 'prefix_config'(k PRIMARY KEY, v) WITHOUT ROWID; CREATE TABLE lookup(identifier TEXT PRIMARY KEY, category INT2); CREATE TRIGGER trigger_groups_insert AFTER INSERT ON groups BEGIN INSERT INTO prefix(rowid, normalized_string, category) VALUES (new.rowid, new.normalized_string, new.category); END; CREATE TRIGGER trigger_groups_delete AFTER DELETE ON groups BEGIN INSERT INTO prefix(prefix, rowid, normalized_string, category) VALUES('delete', old.rowid, old.normalized_string, old.category); END; CREATE INDEX group_pk ON groups(category, content_string, normalized_string, lookup_identifier); CREATE INDEX asset_pk ON assets(uuid_0, uuid_1); CREATE INDEX ga_assetid ON ga(assetid, groupid); CREATE INDEX collection_pk ON collections(uuid_0, uuid_1); CREATE INDEX gc_collectionid ON gc(collectionid); ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623806687 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623806687 | MDEyOklzc3VlQ29tbWVudDYyMzgwNjY4Nw== | 9599 | 2020-05-05T02:51:16Z | 2020-05-05T02:51:16Z | MEMBER | Running datasette against it directly doesn't work: ``` simon@Simons-MacBook-Pro search % datasette psi.sqlite Serve! files=('psi.sqlite',) (immutables=()) on port 8001 Usage: datasette serve [OPTIONS] [FILES]... Error: Connection to psi.sqlite failed check: no such tokenizer: PSITokenizer ``` Instead, I created a new SQLite database with a copy of some of the key tables, like this: ``` sqlite-utils rows psi.sqlite groups | sqlite-utils insert /tmp/search.db groups - sqlite-utils rows psi.sqlite assets | sqlite-utils insert /tmp/search.db assets - sqlite-utils rows psi.sqlite ga | sqlite-utils insert /tmp/search.db ga - sqlite-utils rows psi.sqlite collections | sqlite-utils insert /tmp/search.db collections - sqlite-utils rows psi.sqlite gc | sqlite-utils insert /tmp/search.db gc - sqlite-utils rows psi.sqlite lookup | sqlite-utils insert /tmp/search.db lookup - ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623807568 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623807568 | MDEyOklzc3VlQ29tbWVudDYyMzgwNzU2OA== | 9599 | 2020-05-05T02:56:06Z | 2020-05-05T02:56:06Z | MEMBER | I'm pretty sure this is what I'm after. The `groups` table has what looks like identified labels in the rows with category = 2025: <img width="1122" alt="words__groups__2_528_rows_where_where_category___2025" src="https://user-images.githubusercontent.com/9599/81031361-e0df4080-8e40-11ea-9060-6d850aa52140.png"> Then there's a `ga` table that maps groups to assets: <img width="304" alt="words__ga__633_653_rows" src="https://user-images.githubusercontent.com/9599/81031387-f48aa700-8e40-11ea-9a3d-da23903be928.png"> And an `assets` table which looks like it has one row for every one of my photos: <img width="645" alt="words__assets__40_419_rows" src="https://user-images.githubusercontent.com/9599/81031402-04a28680-8e41-11ea-8047-e9199d068563.png"> One major challenge: these UUIDs are split into two integer numbers, `uuid_0` and `uuid_1` - but the main photos database uses regular UUIDs like this:  I need to figure out how to match up these two different UUID representations. I asked on Twitter if anyone has any ideas: https://twitter.com/simonw/status/1257500689019703296 | {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623811131 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623811131 | MDEyOklzc3VlQ29tbWVudDYyMzgxMTEzMQ== | 9599 | 2020-05-05T03:16:18Z | 2020-05-05T03:16:18Z | MEMBER | Here's how to convert two integers unto a UUID using Java. Not sure if it's the solution I need though (or how to do the same thing in Python): https://repl.it/repls/EuphoricSomberClasslibrary <img width="1494" alt="Repl_it_-_EuphoricSomberClasslibrary" src="https://user-images.githubusercontent.com/9599/81032267-0d488c00-8e44-11ea-9be7-680eaccd1611.png"> ```java import java.util.UUID; class Main { public static void main(String[] args) { java.util.UUID uuid = new java.util.UUID( 2544182952487526660L, -3640314103732024685L ); System.out.println( uuid ); } } ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623845014 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623845014 | MDEyOklzc3VlQ29tbWVudDYyMzg0NTAxNA== | 41546558 | 2020-05-05T03:55:14Z | 2020-05-05T03:56:24Z | CONTRIBUTOR | I'm traveling w/o access to my Mac so can't help with any code right now. I suspected ZSCENEIDENTIFIER was a foreign key into one of these psi.sqlite tables. But looks like you're on to something connecting groups to assets. As for the UUID, I think there's two ints because each is 64-bits but UUIDs are 128-bits. Thus they need to be combined to get the 128 bit UUID. You might be able to use Apple's [NSUUID](https://developer.apple.com/documentation/foundation/nsuuid?language=objc), for example, by wrapping with pyObjC. Here's one [example](https://github.com/ronaldoussoren/pyobjc/blob/881c82a7ba90f193934b52b44143360c80dce5e5/pyobjc-framework-Cocoa/PyObjCTest/test_nsuuid.py) of using this in PyObjC's test suite. Interesting it's stored this way instead of a UUIDString as in Photos.sqlite. Perhaps it for faster indexing. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623846880 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623846880 | MDEyOklzc3VlQ29tbWVudDYyMzg0Njg4MA== | 9599 | 2020-05-05T04:06:08Z | 2020-05-05T04:06:08Z | MEMBER | This function seems to convert them into UUIDs that match my photos: ```python def to_uuid(uuid_0, uuid_1): b = uuid_0.to_bytes(8, 'little', signed=True) + uuid_1.to_bytes(8, 'little', signed=True) return str(uuid.UUID(bytes=b)).upper() ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623855841 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623855841 | MDEyOklzc3VlQ29tbWVudDYyMzg1NTg0MQ== | 9599 | 2020-05-05T04:54:28Z | 2020-05-05T04:54:28Z | MEMBER | Things were not matching up for me correctly: <img width="1143" alt="search__select_json_object__img_src____https___photos_simonwillison_net_i______photos_sha256___________photos_ext______w_400___as_photo__groups_content_string__assets_uuid_0__assets_uuid_1__to_uuid_assets_uuid_0__assets_uuid_1__as_uuid__pho" src="https://user-images.githubusercontent.com/9599/81035923-ca8db080-8e51-11ea-95a7-6ee60bae7502.png"> I think that's because my import script didn't correctly import the existing `rowid` values. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623855885 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623855885 | MDEyOklzc3VlQ29tbWVudDYyMzg1NTg4NQ== | 9599 | 2020-05-05T04:54:39Z | 2020-05-05T04:54:53Z | MEMBER | Trying this import mechanism instead: `sqlite3 /Users/simon/Pictures/Photos\ Library.photoslibrary/database/search/psi.sqlite .dump | grep -v 'CREATE INDEX' | grep -v 'CREATE TRIGGER' | grep -v 'CREATE VIRTUAL TABLE' | sqlite3 search.db` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623857417 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623857417 | MDEyOklzc3VlQ29tbWVudDYyMzg1NzQxNw== | 9599 | 2020-05-05T05:01:47Z | 2020-05-05T05:01:47Z | MEMBER | Even that didn't work - it didn't copy across the rowid values. I'm pretty sure that's what's wrong here: ``` sqlite3 /Users/simon/Pictures/Photos\ Library.photoslibrary/database/search/psi.sqlite 'select rowid, uuid_0, uuid_1 from assets limit 10' 1619605|-9205353363298198838|4814875488794983828 1641378|-9205348195631362269|390804289838822030 1634974|-9205331524553603243|-3834026796261633148 1619083|-9205326176986145401|7563404215614709654 22131|-9205315724827218763|8370531509591906734 1645633|-9205247376092758131|-1311540150497601346 ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623863902 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623863902 | MDEyOklzc3VlQ29tbWVudDYyMzg2MzkwMg== | 9599 | 2020-05-05T05:31:53Z | 2020-05-05T05:31:53Z | MEMBER | Yes! Turning those `rowid` values into `id` with this script did the job: ```python import sqlite3 import sqlite_utils conn = sqlite3.connect( "/Users/simon/Pictures/Photos Library.photoslibrary/database/search/psi.sqlite" ) def all_rows(table): result = conn.execute("select rowid as id, * from {}".format(table)) cols = [c[0] for c in result.description] for row in result.fetchall(): yield dict(zip(cols, row)) if __name__ == "__main__": db = sqlite_utils.Database("psi_copy.db") for table in ("assets", "collections", "ga", "gc", "groups"): db[table].upsert_all(all_rows(table), pk="id", alter=True) ``` Then I ran this query: ```sql select json_object('img_src', 'https://photos.simonwillison.net/i/' || photos.sha256 || '.' || photos.ext || '?w=400') as photo, group_concat(strip_null_chars(groups.content_string), ' ') as words, assets.uuid_0, assets.uuid_1, to_uuid(assets.uuid_0, assets.uuid_1) as uuid from assets join ga on assets.id = ga.assetid join groups on ga.groupid = groups.id join photos on photos.uuid = to_uuid(assets.uuid_0, assets.uuid_1) where groups.category = 2024 group by assets.id order by random() limit 10 ``` And got these results! <img width="1054" alt="psi_copy__select_json_object__img_src____https___photos_simonwillison_net_i______photos_sha256___________photos_ext______w_400___as_photo__group_concat_strip_null_chars_groups_content_string________as_words__assets_uuid_0__assets_uuid_1__to" src="https://user-images.githubusercontent.com/9599/81037264-f1021a80-8e56-11ea-9924-6f9f55a0fb4b.png"> | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/16#issuecomment-623865250 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/16 | 623865250 | MDEyOklzc3VlQ29tbWVudDYyMzg2NTI1MA== | 9599 | 2020-05-05T05:38:16Z | 2020-05-05T05:38:16Z | MEMBER | It looks like `groups.content_string` often has a null byte in it. I should clean this up as part of the import. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612287234 | |
| https://github.com/dogsheep/dogsheep-photos/issues/17#issuecomment-624278090 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/17 | 624278090 | MDEyOklzc3VlQ29tbWVudDYyNDI3ODA5MA== | 9599 | 2020-05-05T20:06:01Z | 2020-05-05T20:06:01Z | MEMBER | https://www.python.org/dev/peps/pep-0508/#environment-markers I think I want `sys_platform` of `darwin`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612860531 | |
| https://github.com/dogsheep/dogsheep-photos/issues/17#issuecomment-624278714 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/17 | 624278714 | MDEyOklzc3VlQ29tbWVudDYyNDI3ODcxNA== | 9599 | 2020-05-05T20:07:19Z | 2020-05-05T20:07:19Z | MEMBER | From https://hynek.me/articles/conditional-python-dependencies/ I think this will look like: ```python setup( # ... install_requires=[ # ... "osxphotos>=0.28.13 ; sys_platform=='darwin'", ] ) ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612860531 | |
| https://github.com/dogsheep/dogsheep-photos/issues/17#issuecomment-624284539 | https://api.github.com/repos/dogsheep/dogsheep-photos/issues/17 | 624284539 | MDEyOklzc3VlQ29tbWVudDYyNDI4NDUzOQ== | 41546558 | 2020-05-05T20:20:05Z | 2020-05-05T20:20:05Z | CONTRIBUTOR | FYI, I've got an [issue](https://github.com/RhetTbull/osxphotos/issues/25) to make osxphotos cross-platform but it's low on my priority list. About 90% of the functionality could be done cross-platform but right now the MacOS specific stuff is embedded throughout and would take some work. Though I try to minimize it, there's sprinklings of ObjC & Applescript throughout osxphotos. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 612860531 |