How to open an SQLite database readonly in Julia?

Question

I'd like to read my Safari history database from a Julia script (Mac OS X).

I have a command line script that works:

sqlite3 -readonly ~/Library/Safari/History.db 'SELECT v.title, i.url FROM history_items i, history_visits v WHERE i.url LIKE "%en.wikipedia.org%" AND i.id=v.history_item AND v.title LIKE "%- Wikipedia%" GROUP BY v.title ORDER BY v.visit_time'

... but trying it in Julia (in Juno / Atom) gives me a permission error

db = SQLite.DB("/Users/grimxn/Library/Safari/History.db")
sql = """
    SELECT v.title, i.url, v.visit_time
    FROM history_items i, history_visits v
    WHERE i.url LIKE "%en.wikipedia.org%"
        AND i.id=v.history_item
        AND v.title LIKE "%- Wikipedia%"
    GROUP BY v.title
    ORDER BY v.visit_time
"""
result = DBInterface.execute(db, sql) |> DataFrame
(rows, cols) = size(result)
println("Result has $(rows) rows")
println("Earliest: $(result[1,1])")
println("Latest: $(result[rows,1])")

ERROR: LoadError: SQLite.SQLiteException("unable to open database file")

Now, when I copy the database to my home directory, and swap

db = SQLite.DB("/Users/grimxn/Library/Safari/History.db")

to

db = SQLite.DB("/Users/grimxn/History.db")

everything works, so I guess it is that the Julia / Juno process has only got read permissions, but is accessing the db read/write.

How do I attach to the database as readonly in Julia?

user_600142 · Answer

Theoretically, use a URI connection string: file:foo.db?mode=ro.

This is documented in the SQLite manual.

Practically, it appears the current version of the SQLite.jl package does not support URIs, and neither does it support flags that could be passed along to sqlite3_open_v2().

Leaving this answer for reference just in case the Julia package fixes this some day.

How to open an SQLite database readonly in Julia?

1 Answers