github

Custom SQL query returning 101 rows (hide)

This data as json, CSV

html_urlissue_urlidnode_idusercreated_atupdated_atauthor_associationbodyreactionsissueperformed_via_github_app
https://github.com/dogsheep/apple-notes-to-sqlite/issues/1#issuecomment-1461226402 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/1 1461226402 IC_kwDOJHON9s5XGIui 9599 2023-03-09T03:47:11Z 2023-03-09T03:47:11Z MEMBER Me: > Write applescript to loop through all of my Apple Notes and output their contents ChatGPT: ```applescript tell application "Notes" repeat with eachNote in every note set noteTitle to the name of eachNote set noteBody to the body of eachNote display dialog noteTitle & return & return & noteBody end repeat end tell ``` Me: > How can I turn that into a zsh script which will output the results to my terminal ChatGPT: ```zsh #!/usr/bin/zsh osascript -e 'tell application "Notes" repeat with eachNote in every note set noteTitle to the name of eachNote set noteBody to the body of eachNote set output to noteTitle & "\n" & noteBody & "\n" display dialog output log output end repeat end tell' ``` I had to change that first line to `#!/bin/zsh` - then when I ran it it spammed my screen with dialog boxes. Me: > I want to log the notes contents to the terminal, not display it in a dialog ChatGPT: ```zsh #!/bin/zsh osascript -e 'tell application "Notes" repeat with eachNote in every note set noteTitle to the name of eachNote set noteBody to the body of eachNote set output to noteTitle & "\n" & noteBody & "\n" log output end repeat end tell' ``` This really helps! {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616347574  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/1#issuecomment-1461230197 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/1 1461230197 IC_kwDOJHON9s5XGJp1 9599 2023-03-09T03:51:36Z 2023-03-09T03:51:36Z MEMBER After a few more rounds I got to this script, which outputs them to a `/tmp/notes.txt` file: ```zsh #!/bin/zsh osascript -e ' set notesFile to "/tmp/notes.txt" set fileRef to open for access notesFile with write permission tell application "Notes" repeat with eachNote in every note set noteId to the id of eachNote set noteTitle to the name of eachNote set noteBody to the body of eachNote write "------------------------" & "\n" to fileRef write noteId & "\n" to fileRef write noteTitle & "\n\n" to fileRef write noteBody & "\n" to fileRef end repeat end tell close access fileRef' ``` Then I wrote this little Python script to load them into a database: ```python import sqlite_utils split = b"------------------------\n" s = open("/tmp/notes.txt", "rb").read() notes = [n.decode("mac_roman") for n in s.split(split) if n] cleaned_notes = [{ "id": n.split("\n")[0], "title": n.split("\n")[1], "body": "\n".join(n.split("\n")[2:]).strip() } for n in notes] db = sqlite_utils.Database("/tmp/notes.db") db["notes"].insert_all(cleaned_notes) ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616347574  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/1#issuecomment-1461230436 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/1 1461230436 IC_kwDOJHON9s5XGJtk 9599 2023-03-09T03:51:52Z 2023-03-09T03:51:52Z MEMBER This did the job! Next step is to turn that into a Python script. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616347574  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/11#issuecomment-1462962682 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/11 1462962682 IC_kwDOJHON9s5XMwn6 9599 2023-03-09T23:20:35Z 2023-03-09T23:22:41Z MEMBER Here's a query that returns all notes in folder 1, including notes in descendant folders: ```sql with recursive nested_folders(folder_id, descendant_folder_id) as ( -- base case: select all immediate children of the root folder select id, id from folders where parent is null union all -- recursive case: select all children of the previous level of nested folders select nf.folder_id, f.id from nested_folders nf join folders f on nf.descendant_folder_id = f.parent ) -- Find notes within all descendants of folder 1 select * from notes where folder in ( select descendant_folder_id from nested_folders where folder_id = 1 ); ``` With assistance from ChatGPT. Prompts were: ``` SQLite schema: CREATE TABLE [folders] ( [id] INTEGER PRIMARY KEY, [long_id] TEXT, [name] TEXT, [parent] INTEGER, FOREIGN KEY([parent]) REFERENCES [folders]([id]) ); Write a recursive CTE that returns the following: folder_id | descendant_folder_id With a row for every nested child of every folder - so the top level folder has lots of rows ``` Then I tweaked it a bit, then ran this: ``` WITH RECURSIVE nested_folders(folder_id, descendant_folder_id) AS ( -- base case: select all immediate children of the root folder SELECT id, id FROM folders WHERE parent IS NULL UNION ALL -- recursive case: select all children of the previous level of nested folders SELECT nf.folder_id, f.id FROM nested_folders nf JOIN folders f ON nf.descendant_folder_id = f.parent ) -- select all rows from the recursive CTE SELECT * from notes where folder in (select descendant_folder_id FROM nested_folders where folder_id = 1) Convert all SQL keywords to lower case, and re-indent ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1618130434  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/11#issuecomment-1462965256 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/11 1462965256 IC_kwDOJHON9s5XMxQI 9599 2023-03-09T23:22:12Z 2023-03-09T23:22:12Z MEMBER Here's what the CTE from that looks like: <img width="469" alt="image" src="https://user-images.githubusercontent.com/9599/224182888-50f315eb-417f-4fb1-b624-151e1740ffed.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1618130434  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/11#issuecomment-1462968053 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/11 1462968053 IC_kwDOJHON9s5XMx71 9599 2023-03-09T23:24:01Z 2023-03-09T23:24:01Z MEMBER I improved the readability by removing some unnecessary table aliases: ```sql with recursive nested_folders(folder_id, descendant_folder_id) as ( -- base case: select all immediate children of the root folder select id, id from folders where parent is null union all -- recursive case: select all children of the previous level of nested folders select nested_folders.folder_id, folders.id from nested_folders join folders on nested_folders.descendant_folder_id = folders.parent ) -- Find notes within all descendants of folder 1 select * from notes where folder in ( select descendant_folder_id from nested_folders where folder_id = 1 ); ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1618130434  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/2#issuecomment-1461232709 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/2 1461232709 IC_kwDOJHON9s5XGKRF 9599 2023-03-09T03:54:28Z 2023-03-09T03:54:28Z MEMBER I think the AppleScript I want to pass to `osascript` looks like this: ```applescript tell application "Notes" repeat with eachNote in every note set noteId to the id of eachNote set noteTitle to the name of eachNote set noteBody to the body of eachNote log "------------------------" & "\n" log noteId & "\n" log noteTitle & "\n\n" log noteBody & "\n" end repeat end tell ``` But there are a few more properties I'd like to get - created and updated date for example. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616354999  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/2#issuecomment-1461234311 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/2 1461234311 IC_kwDOJHON9s5XGKqH 9599 2023-03-09T03:56:24Z 2023-03-09T03:56:24Z MEMBER I opened the "Script Editor" app on my computer, used Window -> Library to open the Library panel, then clicked on the Notes app there. I got this: <img width="1240" alt="image" src="https://user-images.githubusercontent.com/9599/223912876-09e8660e-f59a-4f04-8992-668e09c8d7d4.png"> So the notes object has these properties: - name (text) : the name of the note (normally the first line of the body) - id (text, r/o) : the unique identifier of the note - container ([folder](applewebdata://621FA8D9-C995-4081-B3B3-149B0EA04C7F#Notes-Suite.folder), r/o) : the folder of the note - body (text) : the HTML content of the note - plaintext (text, r/o) : the plaintext content of the note - creation date (date, r/o) : the creation date of the note - modification date (date, r/o) : the modification date of the note - password protected (boolean, r/o) : Is the note password protected? - shared (boolean, r/o) : Is the note shared? I'm going to ignore the concept of attachments for the moment. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616354999  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/2#issuecomment-1461234591 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/2 1461234591 IC_kwDOJHON9s5XGKuf 9599 2023-03-09T03:56:45Z 2023-03-09T03:56:45Z MEMBER My prototype showed that images embedded in notes come out in the HTML export as bas64 image URLs, which is neat. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616354999  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/2#issuecomment-1461259490 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/2 1461259490 IC_kwDOJHON9s5XGQzi 9599 2023-03-09T04:24:27Z 2023-03-09T04:24:27Z MEMBER Converting AppleScript date strings to ISO format is hard! https://forum.latenightsw.com/t/formatting-dates/841 has a recipe I'll try: set todayISO to (todayDate as «class isot» as string) Not clear to me how timezones work here. I'm going to ignore them for the moment. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616354999  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/2#issuecomment-1461260978 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/2 1461260978 IC_kwDOJHON9s5XGRKy 9599 2023-03-09T04:27:18Z 2023-03-09T04:27:18Z MEMBER Before that conversion: Monday, March 6, 2023 at 11:55:15 AM After: 2023-03-06T11:55:15 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616354999  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/2#issuecomment-1461262577 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/2 1461262577 IC_kwDOJHON9s5XGRjx 9599 2023-03-09T04:30:00Z 2023-03-09T04:30:00Z MEMBER It doesn't have tests yet. I guess I'll need to mock `subprocess` to test this. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616354999  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/2#issuecomment-1461285545 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/2 1461285545 IC_kwDOJHON9s5XGXKp 9599 2023-03-09T05:06:24Z 2023-03-09T05:06:24Z MEMBER OK, this works! {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616354999  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/4#issuecomment-1462554175 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/4 1462554175 IC_kwDOJHON9s5XLM4_ 9599 2023-03-09T18:19:34Z 2023-03-09T18:19:34Z MEMBER It looks like the iteration order is most-recently-modified-first - I tried editing a note a bit further back in my notes app and it was the first one output by `apple-notes-to-sqlite --dump`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616429236  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/4#issuecomment-1462556829 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/4 1462556829 IC_kwDOJHON9s5XLNid 9599 2023-03-09T18:20:56Z 2023-03-09T18:20:56Z MEMBER In terms of the UI: I'm tempted to say that the default behaviour is for it to run until it sees a note that it already knows about AND that has matching update/created dates, and then stop. You can do a full import again ignoring that logic with `apple-notes-to-sqlite notes.db --full`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1616429236  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/6#issuecomment-1493442956 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/6 1493442956 IC_kwDOJHON9s5ZBCGM 14314871 2023-04-02T21:20:43Z 2023-04-02T21:25:37Z NONE I'm experiencing something similar. My apostrophes (') turn into (‚Äô) and the output is truncated. Hoping to debug next weekend <img width="814" alt="Screenshot 2023-04-02 at 4 24 10 PM" src="https://user-images.githubusercontent.com/14314871/229379762-f622205a-7bba-4598-b0da-a6b86e5c0a44.png"> {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617602868  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/6#issuecomment-1508784533 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/6 1508784533 IC_kwDOJHON9s5Z7jmV 579727 2023-04-14T15:22:09Z 2023-04-14T15:22:09Z NONE Just changing the encoding in `extract_notes` to `utf8` seems to fix it for my titles that were messed up. ![Screen Shot 2023-04-14 at 5 14 18 PM](https://user-images.githubusercontent.com/579727/232086062-e7edc4d1-0880-417a-925b-fd6c65b05155.png) {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617602868  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/7#issuecomment-1462562735 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/7 1462562735 IC_kwDOJHON9s5XLO-v 9599 2023-03-09T18:23:56Z 2023-03-09T18:25:22Z MEMBER From the Script Editor library docs: A note has a: > - `container` (folder), r/o) : the folder of the note Here's what a folder looks like: > folder n : a folder containing notes > elements: > > - contains folders, notes; contained by application, accounts, folders. > > properties: > > - `name` (text) : the name of the folder > - `id` (text, r/o) : the unique identifier of the folder > - `shared` (boolean, r/o) : Is the folder shared? > - `container` (account or folder, r/o) : the container of the folder {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617769847  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/7#issuecomment-1462564717 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/7 1462564717 IC_kwDOJHON9s5XLPdt 9599 2023-03-09T18:25:39Z 2023-03-09T18:25:39Z MEMBER So it looks like folders can be hierarchical? {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617769847  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/7#issuecomment-1462570187 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/7 1462570187 IC_kwDOJHON9s5XLQzL 9599 2023-03-09T18:30:24Z 2023-03-09T18:30:24Z MEMBER I used ChatGPT to write this: ``` osascript -e 'tell application "Notes" set allFolders to folders repeat with aFolder in allFolders set folderId to id of aFolder set folderName to name of aFolder set folderContainer to container of aFolder set folderContainerName to name of folderContainer log "Folder ID: " & folderId log "Folder Name: " & folderName log "Folder Container: " & folderContainerName log " " --check for nested folders if count of folders of aFolder > 0 then set nestedFolders to folders of aFolder repeat with aNestedFolder in nestedFolders set nestedFolderId to id of aNestedFolder set nestedFolderName to name of aNestedFolder set nestedFolderContainer to container of aNestedFolder set nestedFolderContainerName to name of nestedFolderContainer log " Nested Folder ID: " & nestedFolderId log " Nested Folder Name: " & nestedFolderName log " Nested Folder Container: " & nestedFolderContainerName log " " end repeat end if end repeat end tell ' ``` Which for my account output this: ``` Folder ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p6113 Folder Name: Blog posts Folder Container: iCloud Nested Folder ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p7995 Nested Folder Name: Nested inside blog posts Nested Folder Container: Blog posts Folder ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p698 Folder Name: JSK Folder Container: iCloud Folder ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p7995 Folder Name: Nested inside blog posts Folder Container: Blog posts Folder ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p3526 Folder Name: New Folder Folder Container: iCloud Folder ID: x-coreda… {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617769847  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/7#issuecomment-1462682795 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/7 1462682795 IC_kwDOJHON9s5XLsSr 9599 2023-03-09T19:52:20Z 2023-03-09T19:52:44Z MEMBER Created through several rounds with ChatGPT (including hints like "rewrite that using setdefault()"): ```python def topological_sort(nodes): children = {} for node in nodes: parent_id = node["parent"] if parent_id is not None: children.setdefault(parent_id, []).append(node) def traverse(node, result): result.append(node) if node["id"] in children: for child in children[node["id"]]: traverse(child, result) sorted_data = [] for node in nodes: if node["parent"] is None: traverse(node, sorted_data) return sorted_data ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617769847  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/7#issuecomment-1462691466 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/7 1462691466 IC_kwDOJHON9s5XLuaK 9599 2023-03-09T19:59:52Z 2023-03-09T19:59:52Z MEMBER Improved script: ```zsh osascript -e 'tell application "Notes" set allFolders to folders repeat with aFolder in allFolders set folderId to id of aFolder set folderName to name of aFolder set folderContainer to container of aFolder if class of folderContainer is folder then set folderContainerId to id of folderContainer else set folderContainerId to "" end if log "ID: " & folderId log "Name: " & folderName log "Container: " & folderContainerId log " " end repeat end tell ' ``` ``` ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p6113 Name: Blog posts Container: ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p698 Name: JSK Container: ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p7995 Name: Nested inside blog posts Container: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p6113 ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p3526 Name: New Folder Container: ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p3839 Name: New Folder 1 Container: ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p2 Name: Notes Container: ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p6059 Name: Quick Notes Container: ID: x-coredata://D2D50498-BBD1-4097-B122-D15ABD32BDEC/ICFolder/p7283 Name: UK Christmas 2022 Container: ``` I filtered out things where the parent was an account and not a folder using `if class of folderContainer is folder then`. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617769847  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/7#issuecomment-1462693867 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/7 1462693867 IC_kwDOJHON9s5XLu_r 9599 2023-03-09T20:01:39Z 2023-03-09T20:02:11Z MEMBER My `folders` table will have: - `id` - rowid - `long_id` - that long unique string ID - `name` - the name - `parent` - foreign key to `id` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617769847  
https://github.com/dogsheep/apple-notes-to-sqlite/issues/8#issuecomment-1468898285 https://api.github.com/repos/dogsheep/apple-notes-to-sqlite/issues/8 1468898285 IC_kwDOJHON9s5XjZvt 41546558 2023-03-14T22:00:21Z 2023-03-14T22:00:21Z NONE Well that's embarrassing. I made a fork using macnotesapp and it's actually slower. This is because the Scripting Bridge sometimes fails to return the folder and thus macnotesapp resorts to AppleScript in this situation. The repeated AppleScript calls on a large library are slower than your "slurp it all in" approach. I've got some ideas about how to improve this--will make another attempt if I can fix the issues. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1617823309  
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/37#issuecomment-1646950438 https://api.github.com/repos/dogsheep/dogsheep-beta/issues/37 1646950438 IC_kwDOC8SPRc5iKngm 10352819 2023-07-23T20:18:26Z 2023-07-23T20:18:26Z NONE My bad, although I could not find how to use `url`s, I was using a function call syntax for the filters instead of jinja's pipe syntax; closing this issue. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} 1817281557  
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 … {"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