SQLite Forum

Feature Request: ATTACH DB readonly?
Login

Feature Request: ATTACH DB readonly?

(1) By midijohnny on 2021-11-24 17:30:35 [link] [source]

Apologies if this is wrong place to request features (if so: is there somewhere else to do that?).

Could we have a extension to the ATTACH DATABASE command to allow a secondary DB to be attached as readonly?

I have a use-case where I want to access information from another DB, but want to avoid making any changes by accident; I also want to signal to others that the DB should be opened-up as readonly (or 'reference' etc).

(I am aware that you can make the file readonly : but this would also make the same data readonly for all other access to the same file; which isn't what I want to do).

(2) By ddevienne on 2021-11-24 17:44:59 in reply to 1 [link] [source]

Are you aware of URIs in SQLite?
I think this allows to do what you want.

(7) By midijohnny on 2021-11-25 13:39:16 in reply to 2 [link] [source]

That works - thanks !

sqlite> .version
SQLite 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5
zlib version 1.2.11
gcc-5.2.0
sqlite> attach database "file:reference.db?mode=ro" as reference;
sqlite> PRAGMA database_list;
0|main|
2|reference|C:\Users\[...]\reference.db
sqlite> .tables
reference.x
sqlite> drop table reference.x;
Error: attempt to write a readonly database

(9) By Anuj Kumar Garg (anujrkgit) on 2022-08-09 09:49:33 in reply to 2 [link] [source]

Seems like using URIs is disabled in Android. Is there any other way to achieve this in Android?

(10) By Sunny Saini (SunnySaini_com) on 2022-08-09 11:42:56 in reply to 9 [link] [source]

Android SQLite is compiled mainly to be used by Android OS for internal use. It is not compiled with all features. Also it can't be upgraded as it is part of the OS. Third party Apps like aSQLiteManager make use of SQLite that is inbuilt into Android. You can issue "Select sqlite_version() version;" to know your phone's SQLite version. "Pragma compile_options" generally gives no result.

(3) By Larry Brasfield (larrybr) on 2021-11-24 17:45:09 in reply to 1 [link] [source]

See URI query parameters, then use the URI file specification syntax for your ATTACH. (A compile-time option to enable URIs may be necessary, depending on where you get SQLite bits.)

(8) By midijohnny on 2021-11-25 13:39:57 in reply to 3 [link] [source]

Thanks !

(4) By Keith Medcalf (kmedcalf) on 2021-11-24 21:54:55 in reply to 1 [link] [source]

(5) By anonymous on 2021-11-24 22:09:53 in reply to 4 [source]

That applies globally to the main, temp, and all attached databases.

(6) By Keith Medcalf (kmedcalf) on 2021-11-24 22:37:02 in reply to 5 [link] [source]

Ooops. You are correct. It applies to the entire connection (it does not recognize the <schema> prefix.)