Need If column exist option in SQLite
Unfortunately, there is no "IF NOT EXISTS for ADD COLUMN".
If it is available, Then I could simply do such as ALTER TABLE MYTABLE ADD COLUMN IF NOT EXISTS NEWCOL String.
Can you please help me with work around for this? I am beginner for SQLite, so kindly help me with detailed answer. [SQLite Code]
While this would be technically possible in SQL it's a bad idea. If your program is changing your schema on the fly it's probably doing the wrong thing. This not only covers adding new columns to existing tables but also adding new tables. The general idea is to get the schema right when you're designing your software and to change it only when a programmer (a human who can examine the schema) has made that decision.
SELECT newcol FROM mytable LIMIT 1
and see if you get
One could argue about "DROP VIEW IF EXIST", "CREATE TABLE IF NOT EXISTS", etc. the same.
It's a very useful construct and I vote for supporting it. Btw, Postgres has it too ;)
A SELECT statement
- requires a semic-colon
- does not return SQLite_OK.
SELECT newcol FROM mytable LIMIT 1;
will fail if newcol does not exist.
If you're using an C API call such as sqlite3_prepare(), the semi-colon is not required. Further, if newcol exists, the C function will return SQLITE_OK. If it does not exist, it will return an error. In both cases the actual statement is not executed.
Nor, of course, do you need the "LIMIT 1".
If you prepare the statement
select newcol from newtable then the return code from the prepare will be SQLITE_OK if BOTH the table newtable exists AND the column newcol exists in that table.
If the column newcol does not exist in the table newtable (which exists), OR, the table newtable does not exist, you will get an SQLITE_ERROR result, and you can examine the error message text to see what the problem was.
In the event that SQLITE_OK was returned because the table existed and it contained the desired column, then you still have to execute the statement if you want to execute it. However, since you do not need to execute the statement, you could simply dispose of it right then and there by calling sqlite3_finalize on the statement handle.
There is no need to execute the statement.
It would be much easier to just put the schema type indicator and version number in the database somewhere and if when you check they are nonsense use that to "fire the customer" (that is, put in your warranty that there are no "user serviceable parts inside" and that if the customer attempts "user servicing" which busts the database, then they will have to pay to have it fixed at your "fix user bustage" hourly rate (I used to claim this was $10,000.00 per hour, which was extremely effective at stopping end-user fiddling)).
SQLITE_ERROR result, and you can examine the error message text
What error message text?
So far as I can see, sqlite3_prepare and sqlite3_prepare_v2 simply emit a return code and NO error message.
If I get SQLITE_ERROR i.e. 1, how does that tell me whether it is the table that does not exist, or that it is the column that does not exist, or that neither exist?
I'm glad I asked else I would not have known as it is no obvious.
(4) By Andreas Kupries (andreas-kupries) on 2021-01-19 13:01:50 in reply to 1 [link] [source]
This kind of thing I have usually seen handled in the application (logic) instead of via SQL statements.
The basic idea was to have a fixed table
appschema or the like which has a single row listing the version of the application's schema found in the database.
When the application starts and accesses the database it compares the version from that table with the version it itself supports. Then
If the database is ahead of the application you can only abort.
If, on the other hand, the application is ahead of the database then it is expected to have code to update the old database schema to the new schema. This kind of code is usually written as a series of functions, each of which will incrementally update the schema from one version to the next. The application then simply invokes the functions for the "(database schema).next" up to "latest", in order, stepping the schema forward in easy increments. After that completes successfully it can then use the database as normal.
You don't need a table per-se, SQLite has built-in facilities for that use-case, i.e.
pragma application_id = ...where
...is a 4-byte integer usually called a magic cookie, and which is fixed and specific to your schema.
pragma user_version = ...where
...is a version integer representing your current schema version, that you increment explicitly on schema changes.
- and optionally, keep track of when upgrades where made in an ad-hoc table too, by whom, where, etc.. if you have the need for that.
When opening the DB, check the
application_id matches your expectation,
user_version to know if you need to upgrade the schema, and also
if it is newer than what the code expects.
You can also do deep schema introspection, if you do not want to trust the two built-in integers above, but that's more complex, and I won't explain how to do that now.
(7.1) By Andreas Kupries (andreas-kupries) on 2021-01-19 13:59:13 edited from 7.0 in reply to 5 [link] [source]
Right. The idea I was talking about I had seen in code which allowed different engines as backend. It uses Mysql in the default config, and could be switched to use a Postgres backend. So that idea is generally applicable. Good if you don't know what database will be used, or know that things have to be portable across several engines.
deep schema introspection Yes, sqlite has a few pragmas which help there to figure out tables, columns, indices, foreign keys, default values, etc. IIRC I have seen usage of these in the Datasette (python) code.