SQLite User Forum

conditional ATTACH
Login
Chronological

conditional ATTACH

(1) By anonymous on 2022-10-05 09:36:22 [link] [source] 

Help needed:
I cannot manage to avoid an error message when attaching an already attached database
Having checked the syntx at: https://sqlite.org/lang_attach.html

I tried (using the v3.39.3)
   attach <database> raise (IGNORE) as test;

i.e. expr = raise-function with option IGNORE
and receive a syntax error.

Are there ways to suppress the "database test is already in use" message? What did I wrong here?
btw. I am aware of keeping track of attached databases and avoid redundant attach cmds.

Best regards and thanks in advance.

(2) By Ryan Smith (cuz) on 2022-10-05 11:28:17 in reply to 1 [source]

Not sure what you are trying to do, but a DB NAME can only ever be attached once, even though a physical DB file can be attached multiple times.

I imagine it's one of two options:

  1. You are trying to re-attach the DB with the same name
  2. You are trying to attach the same DB to use it from a different name

In case 1, you have first DETACH the previous DB with that name.

In case 2 you have to specify a New NAME not used before.

To Demonstrate, imagine I have a file named "somedb.db" for simplicity sake, I can do:

ATTACH 'somedb.db' AS DB_1;
ATTACH 'somedb.db' AS DB_2;
ATTACH 'somedb.db' AS DB_3;

without a problem, and then do queries in between DB_1 and DB_2, etc.

or you can do:
ATTACH 'somedb.db' AS DB_1;

... then later ...
DETACH DB_1;
ATTACH 'someotherdb.db' AS DB_1;

The point to remember is that the Name/identifier (DB_1 etc.) used inside SQLite has to be unique for the connection.

(3) By anonymous on 2022-10-05 15:03:50 in reply to 2 [link] [source]

Thanks a lot and sorry for being unclear. I am looking for whether or not there is a way to suppress the error message only, if possible - without changing something else. When reading the documentation I thought there were some means, but i cound not manage to find something working.

Like in "create table IF NOT EXISTS ..." or in pseudo SQL something like: "attach if not attached 'somedb.db' as something".

In other words: is there an option to the attach cmd so that attaching an already attached database behaves like a NO OPERATION (i.e. no error message)?

Best regards

(4) By Ryan Smith (cuz) on 2022-10-05 15:41:06 in reply to 3 [link] [source]

Ah, well in that case the answer is quite easy: No.

In the case of CREATE TABLE, the thing you are trying to achieve is a SCHEMA change and it is likely that alterations to a schema may have been effected by a previous connection or such, so there is good reason to support IF NOT EXISTS.

In the case of opening (or attaching) a new connection, this is not something that can ever happily fail without consequence, nor something that I've seen requested before. A connection must always know which attached connections were made and which not. Are you attaching files randomly?

Programmatically this is a piece of cake. You can query the Alias schema and see if you get any returns, or use this pragma that will list all the databases (main, temp, + the attached ones) in a bit of code to figure out if a file or DB alias is already attached or not, but I do not think there is any way to do that with DDL alone.

(5) By anonymous on 2022-10-05 16:03:17 in reply to 4 [link] [source]

Thanks a lot. Thats all I wanted to know, if there is no way then its OK for me and i dont have to look further.

Best regards.

(6) By Anton Dyachenko (ahtoh_) on 2023-11-22 05:17:47 in reply to 4 [link] [source]

There is a perfect use case for attach if not attached:

  • I need to attach a db that located on removable USB device
  • I shall NOT create a db file on that device if no writes goes to the db during user session

Why I should create a db conditionally? Because user may use many different USB drives and only some of them (maybe 0) are related to my application, so the requirement is to not polute unrelated drives with the empty db (and whole directory branch) without a need. Why I need to write to the external drives db in first place? Because my application creates a copy of some portion of data located on the internal db of main PC/Laptop that user can use on another embedded device.

So a natural solution would be to add the following attach query as prelude to every INSERT/UPDATE query.

ATTACH IF NOT EXISTED MY_OWN_FUNCTION_CREATE_ALL_DIRS(path) AS db1; INSERT INTO db1.table DEFAULT VALUES;

So now if the above is not possible, I think to go with a shim VFS that creates db file (and all parent dir branch) lazily at first write to the db and attach db unconditionally for all connected removable drives.