issues: 565518772

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
565518772 MDU6SXNzdWU1NjU1MTg3NzI= 673 Mechanism for checking if a SQLite database file is safe to open 9599 closed 0     11 2020-02-14T19:36:04Z 2020-02-14T20:13:59Z 2020-02-14T20:13:59Z OWNER  

Opening a SpatiaLite database file without SpatiaLite will result in errors later on. Same for database files which use custom extensions, like the Apple Photos database.

I've figured out how to tell if a database is safe to open or not: sql select sql from sqlite_master where sql like 'CREATE VIRTUAL TABLE%'; This returns the SQL definitions for virtual tables. The bit after using tells you what they need.

Run this against a SpatiaLite database and you get the following: sql CREATE VIRTUAL TABLE SpatialIndex USING VirtualSpatialIndex() CREATE VIRTUAL TABLE ElementaryGeometries USING VirtualElementary() Run it against an Apple Photos photos.db file (found with find ~/Library | grep photos.db) and you get this (partial list): sql CREATE VIRTUAL TABLE RidList_VirtualReader using RidList_VirtualReaderModule CREATE VIRTUAL TABLE Array_VirtualReader using Array_VirtualReaderModule CREATE VIRTUAL TABLE LiGlobals_VirtualBufferReader using VirtualBufferReaderModule CREATE VIRTUAL TABLE RKPlace_RTree using rtree (modelId,minLongitude,maxLongitude,minLatitude,maxLatitude) For a database with FTS4 you get: sql CREATE VIRTUAL TABLE "docs_fts" USING FTS4 ( [title], [content], content="docs" ) FTS5: sql CREATE VIRTUAL TABLE [FARA_All_Registrants_fts] USING FTS5 ( [Name], [Address_1], [Address_2], content=[FARA_All_Registrants] ) So I can use this to figure out all of the using pieces and then compare them to a list of known support ones.

Originally posted by @simonw in https://github.com/simonw/datasette/pull/672#issuecomment-586441484

 107914493 issue     
{
    "url": "https://api.github.com/repos/simonw/datasette/issues/673/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
   completed

Links from other tables