ALTER TABLE ADD COLUMN problem
(1) By Tim Streater (Clothears) on 2020-06-13 10:36:23 [link]
I'm looking for a robust way to solve this. I appear to be using 3.29.1. I have an app that I distribute, which uses various SQLite databases. From time to time, as I add new features, I need to add columns here and there to some tables. So far I've been doing that by checking a value stored as a data-version-number in a globals table in a database, and when necessary I bump that number and execute a number of ALTER TABLE ADD COLUMN statements as required. This is all done in the app startup, and normally works without issue. However, recently a user had an unrelated problem and I gave him an unreleased version to test. Unfortunately it seems I broke my update procedure for his set of databases and now he can't update to the next released version. I expect that some column I want to add is, now, already in a table. Once I get hold of him it should be easy enough to diagnose and fix as he's comfortable with Terminal and the sqlite3 CLI. But I want to fix this for the future by being able to detect this specific error (duplicate column) if it happens and ignore it. 1) There is unfortunately no IF NOT EXISTS for ADD COLUMN. Would this be difficult to add, as a feature request? Then I could simply do such as ALTER TABLE MYTABLE ADD COLUMN IF NOT EXISTS NEWCOL INTEGER. This would be the most robust method. 2) As things stand, with "duplicate column" I get error numner 1 (generic error, not too useful) and a message such as "duplicate column name: newcol". I could parse this, but don't want to depend on error message content. 3) I could parse the sqlite_master table but I'd rather not do that either. Have I overlooked a better approach?
(2) By Stephan Beal (stephan) on 2020-06-13 10:44:11 in reply to 1 [link]
You can try to prepare a trivial statement with that column name: select thecol from t. If it prepares, the column exists (simply finalize the statement - you need not execute it). If not, either the column or table are missing.
(3) By Tim Streater (Clothears) on 2020-06-13 10:55:21 in reply to 2 [link]
Stephan - thanks. I'll give that a try.
(4) By Stephan Beal (stephan) on 2020-06-13 10:59:49 in reply to 3 [link]
If you end up needing to differentiate between table/column missing, you can prepare "select 1 from t" to determine whether the table exists.
(5) By Tim Streater (Clothears) on 2020-06-13 11:24:31 in reply to 4 [link]
Unfortunately, it seems that according to the docs for my language (Xojo), the error from the prepare is not reported unless one attempts the SELECT. And I just verified that this is the case. So I may as well just do the SELECT directly. That might be good enough for the moment, but perhaps the PTB will consider adding IF NOT EXISTS.
(6) By Keith Medcalf (kmedcalf) on 2020-06-13 13:23:25 in reply to 1 [link]
Why not retrieve a list of column names in the table, and then add the ones that are not there that you want: ``` select * from pragma_table_xinfo where schema=<schema> and arg=<name>; ``` if the table/view does not exist in the namespace `<schema>` then you will get no rows back. Of course, a `<name>` can be any table-like object including a view, so if you need to know that it is indeed a table, you will need to ask that as well: ``` select type from <schema>.sqlite_master where name == <name>; ``` which will tell you the type (table/view/index/trigger) of the thing called `<name>` in the <schema> namespace.
(7) By Tim Streater (Clothears) on 2020-06-13 13:38:28 in reply to 6 [link]
Is the sqlite_master table and its columns covered by the 2050 SQLite lifetime warranty?
(8) By Keith Medcalf (kmedcalf) on 2020-06-13 14:17:09 in reply to 7
It is documented so I suppose that means that it will, like everything else, stay the same until it changes.