repos: 190950781
This data as json
| id | node_id | name | full_name | private | owner | html_url | description | fork | created_at | updated_at | pushed_at | homepage | size | stargazers_count | watchers_count | language | has_issues | has_projects | has_downloads | has_wiki | has_pages | forks_count | archived | disabled | open_issues_count | license | topics | forks | open_issues | watchers | default_branch | permissions | temp_clone_token | organization | network_count | subscribers_count | readme | readme_html | allow_forking | visibility | is_template | template_repository | web_commit_signoff_required | has_discussions |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 190950781 | MDEwOlJlcG9zaXRvcnkxOTA5NTA3ODE= | datasette-bplist | simonw/datasette-bplist | 0 | 9599 | https://github.com/simonw/datasette-bplist | Datasette plugin for working with Apple's binary plist format | 0 | 2019-06-09T01:15:01Z | 2021-06-07T18:05:00Z | 2019-06-09T01:17:19Z | 7 | 9 | 9 | Python | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | apache-2.0 | ["bplist", "datasette", "datasette-plugin", "datasette-io"] | 0 | 1 | 9 | master | {"admin": false, "push": false, "pull": false} | 0 | 0 | # datasette-bplist [](https://pypi.org/project/datasette-bplist/) [](https://circleci.com/gh/simonw/datasette-bplist) [](https://github.com/simonw/datasette-bplist/blob/master/LICENSE) Datasette plugin for working with Apple's [binary plist](https://en.wikipedia.org/wiki/Property_list) format. This plugin adds two features: a display hook and a SQL function. The display hook will detect any database values that are encoded using the binary plist format. It will decode them, convert them into JSON and display them pretty-printed in the Datasette UI. The SQL function `bplist_to_json(value)` can be used inside a SQL query to convert a binary plist value into a JSON string. This can then be used with SQLite's `json_extract()` function or with the [datasette-jq](https://github.com/simonw/datasette-jq) plugin to further analyze that data as part of a SQL query. Install this plugin in the same environment as Datasette to enable this new functionality: pip install datasette-bplist ## Trying it out If you use a Mac you already have plenty of SQLite databases that contain binary plist data. One example is the database that powers the Apple Photos app. This database tends to be locked, so you will need to create a copy of the database in order to run queries against it: cp ~/Pictures/Photos\ Library.photoslibrary/database/photos.db /tmp/photos.db The database also makes use of custom SQLite extensions which prevent it from opening in Datasette. You can work around this by exporting the data that you want to experiment with into a new SQLite file. I recommend trying this plugin against the `RKMaster_dataNote` table, which contains plist-encoded EXIF metadata about the photos you have taken. You can export that table into a fresh database like so: sqlite3 /tmp/photos.db ".dump RKMaster_dataNote" | sqlite3 /tmp/exif.db Now run `datasette /tmp/exif.db` and you can start trying out the plugin. ## Using the bplist_to_json() SQL function Once you have the `exif.db` demo working, you can try the `bplist_to_json()` SQL function. Here's a query that shows the camera lenses you have used the most often to take photos: select json_extract( bplist_to_json(value), "$.{Exif}.LensModel" ) as lens, count(*) as n from RKMaster_dataNote group by lens order by n desc; If you have a large number of photos this query can take a long time to execute, so you may need to increase the SQL time limit enforced by Datasette like so: $ datasette /tmp/exif.db \ --config sql_time_limit_ms:10000 Here's another query, showing the time at which you took every photo in your library which is classified as as screenshot: select attachedToId, json_extract( bplist_to_json(value), "$.{Exif}.DateTimeOriginal" ) from RKMaster_dataNote where json_extract( bplist_to_json(value), "$.{Exif}.UserComment" ) = "Screenshot" And if you install the [datasette-cluster-map](https://github.com/simonw/datasette-cluster-map) plugin, this query will show you a map of your most recent 1000 photos: select *, json_extract( bplist_to_json(value), "$.{GPS}.Latitude" ) as latitude, -json_extract( bplist_to_json(value), "$.{GPS}.Longitude" ) as longitude, json_extract( bplist_to_json(value), "$.{Exif}.DateTimeOriginal" ) as datetime from RKMaster_dataNote where latitude is not null order by attachedToId desc | <div id="readme" class="md" data-path="README.md"><article class="markdown-body entry-content container-lg" itemprop="text"><h1><a id="user-content-datasette-bplist" class="anchor" aria-hidden="true" href="#user-content-datasette-bplist"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>datasette-bplist</h1> <p><a href="https://pypi.org/project/datasette-bplist/" rel="nofollow"><img src="https://camo.githubusercontent.com/a6a0093ab5212f3444dc00643b1dddd80bba4eea970443b0cbab79157ebab0ea/68747470733a2f2f696d672e736869656c64732e696f2f707970692f762f6461746173657474652d62706c6973742e737667" alt="PyPI" data-canonical-src="https://img.shields.io/pypi/v/datasette-bplist.svg" style="max-width:100%;"></a> <a href="https://circleci.com/gh/simonw/datasette-bplist" rel="nofollow"><img src="https://camo.githubusercontent.com/b4666b829d10d25169928fd8de374c652be7345117ec655c69ba8e6d8b9d2421/68747470733a2f2f636972636c6563692e636f6d2f67682f73696d6f6e772f6461746173657474652d62706c6973742e7376673f7374796c653d737667" alt="CircleCI" data-canonical-src="https://circleci.com/gh/simonw/datasette-bplist.svg?style=svg" style="max-width:100%;"></a> <a href="https://github.com/simonw/datasette-bplist/blob/master/LICENSE"><img src="https://camo.githubusercontent.com/1698104e976c681143eb0841f9675c6f802bb7aa832afc0c7a4e719b1f3cf955/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d417061636865253230322e302d626c75652e737667" alt="License" data-canonical-src="https://img.shields.io/badge/license-Apache%202.0-blue.svg" style="max-width:100%;"></a></p> <p>Datasette plugin for working with Apple's <a href="https://en.wikipedia.org/wiki/Property_list" rel="nofollow">binary plist</a> format.</p> <p>This plugin adds two features: a display hook and a SQL function.</p> <p>The display hook will detect any database values that are encoded using the binary plist format. It will decode them, convert them into JSON and display them pretty-printed in the Datasette UI.</p> <p>The SQL function <code>bplist_to_json(value)</code> can be used inside a SQL query to convert a binary plist value into a JSON string. This can then be used with SQLite's <code>json_extract()</code> function or with the <a href="https://github.com/simonw/datasette-jq">datasette-jq</a> plugin to further analyze that data as part of a SQL query.</p> <p>Install this plugin in the same environment as Datasette to enable this new functionality:</p> <div class="snippet-clipboard-content position-relative" data-snippet-clipboard-copy-content="pip install datasette-bplist "><pre><code>pip install datasette-bplist </code></pre></div> <h2><a id="user-content-trying-it-out" class="anchor" aria-hidden="true" href="#user-content-trying-it-out"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Trying it out</h2> <p>If you use a Mac you already have plenty of SQLite databases that contain binary plist data.</p> <p>One example is the database that powers the Apple Photos app.</p> <p>This database tends to be locked, so you will need to create a copy of the database in order to run queries against it:</p> <div class="snippet-clipboard-content position-relative" data-snippet-clipboard-copy-content="cp ~/Pictures/Photos\ Library.photoslibrary/database/photos.db /tmp/photos.db "><pre><code>cp ~/Pictures/Photos\ Library.photoslibrary/database/photos.db /tmp/photos.db </code></pre></div> <p>The database also makes use of custom SQLite extensions which prevent it from opening in Datasette.</p> <p>You can work around this by exporting the data that you want to experiment with into a new SQLite file.</p> <p>I recommend trying this plugin against the <code>RKMaster_dataNote</code> table, which contains plist-encoded EXIF metadata about the photos you have taken.</p> <p>You can export that table into a fresh database like so:</p> <div class="snippet-clipboard-content position-relative" data-snippet-clipboard-copy-content="sqlite3 /tmp/photos.db ".dump RKMaster_dataNote" | sqlite3 /tmp/exif.db "><pre><code>sqlite3 /tmp/photos.db ".dump RKMaster_dataNote" | sqlite3 /tmp/exif.db </code></pre></div> <p>Now run <code>datasette /tmp/exif.db</code> and you can start trying out the plugin.</p> <h2><a id="user-content-using-the-bplist_to_json-sql-function" class="anchor" aria-hidden="true" href="#user-content-using-the-bplist_to_json-sql-function"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Using the bplist_to_json() SQL function</h2> <p>Once you have the <code>exif.db</code> demo working, you can try the <code>bplist_to_json()</code> SQL function.</p> <p>Here's a query that shows the camera lenses you have used the most often to take photos:</p> <div class="snippet-clipboard-content position-relative" data-snippet-clipboard-copy-content="select json_extract( bplist_to_json(value), "$.{Exif}.LensModel" ) as lens, count(*) as n from RKMaster_dataNote group by lens order by n desc; "><pre><code>select json_extract( bplist_to_json(value), "$.{Exif}.LensModel" ) as lens, count(*) as n from RKMaster_dataNote group by lens order by n desc; </code></pre></div> <p>If you have a large number of photos this query can take a long time to execute, so you may need to increase the SQL time limit enforced by Datasette like so:</p> <div class="snippet-clipboard-content position-relative" data-snippet-clipboard-copy-content="$ datasette /tmp/exif.db \ --config sql_time_limit_ms:10000 "><pre><code>$ datasette /tmp/exif.db \ --config sql_time_limit_ms:10000 </code></pre></div> <p>Here's another query, showing the time at which you took every photo in your library which is classified as as screenshot:</p> <div class="snippet-clipboard-content position-relative" data-snippet-clipboard-copy-content="select attachedToId, json_extract( bplist_to_json(value), "$.{Exif}.DateTimeOriginal" ) from RKMaster_dataNote where json_extract( bplist_to_json(value), "$.{Exif}.UserComment" ) = "Screenshot" "><pre><code>select attachedToId, json_extract( bplist_to_json(value), "$.{Exif}.DateTimeOriginal" ) from RKMaster_dataNote where json_extract( bplist_to_json(value), "$.{Exif}.UserComment" ) = "Screenshot" </code></pre></div> <p>And if you install the <a href="https://github.com/simonw/datasette-cluster-map">datasette-cluster-map</a> plugin, this query will show you a map of your most recent 1000 photos:</p> <div class="snippet-clipboard-content position-relative" data-snippet-clipboard-copy-content="select *, json_extract( bplist_to_json(value), "$.{GPS}.Latitude" ) as latitude, -json_extract( bplist_to_json(value), "$.{GPS}.Longitude" ) as longitude, json_extract( bplist_to_json(value), "$.{Exif}.DateTimeOriginal" ) as datetime from RKMaster_dataNote where latitude is not null order by attachedToId desc "><pre><code>select *, json_extract( bplist_to_json(value), "$.{GPS}.Latitude" ) as latitude, -json_extract( bplist_to_json(value), "$.{GPS}.Longitude" ) as longitude, json_extract( bplist_to_json(value), "$.{Exif}.DateTimeOriginal" ) as datetime from RKMaster_dataNote where latitude is not null order by attachedToId desc </code></pre></div> </article></div> |
Links from other tables
- 1 row from repo in releases