[sqlite] Add Column with "If Not Exists"

[Simon White]

Hi

I would like to suggest the addition of the "If not exists" to the Add
Column feature of SQLite.  There are quite common situations where
ensuring a column exists is important so that an update to remote
devices will not fail but it is not so important that deprecated fields
be removed.  This is often the case with backward compatibility.  New
columns will not affect old systems but allows all remote devices
running older software to be updated using the same process as new
devices.  Once the hardware reaches end of life it will be replaced and
the new hardware will use the new columns.  So having the ability to
Alter the table with a series of Add Column commands ensures that the
new records included in the update are added to the table.  These is
especially true for limited remote devices where full database
management is not feasible.  In this scenario all that is required is
that the required columns exist.  So to be able to alter the table with
a standard SQL command is the most efficient method on such devices. 
Developing scripts to drop and re-create and re-load tables on hundreds
of remote devices greatly increases the risk of failures. Having the "if
not exists" would remove all of this potential complexity and allow a
quick and easy method to ensure the column exists in the table.

[Tim Streater]

Post by Simon White
I would like to suggest the addition of the "If not exists" to the Add
Column feature of SQLite.  There are quite common situations where
ensuring a column exists is important so that an update to remote
devices will not fail but it is not so important that deprecated fields
be removed.  This is often the case with backward compatibility.  New
columns will not affect old systems but allows all remote devices
running older software to be updated using the same process as new
devices.  Once the hardware reaches end of life it will be replaced and
the new hardware will use the new columns.  So having the ability to
Alter the table with a series of Add Column commands ensures that the
new records included in the update are added to the table.  These is
especially true for limited remote devices where full database
management is not feasible.  In this scenario all that is required is
that the required columns exist.  So to be able to alter the table with
a standard SQL command is the most efficient method on such devices. 
Developing scripts to drop and re-create and re-load tables on hundreds
of remote devices greatly increases the risk of failures. Having the "if
not exists" would remove all of this potential complexity and allow a
quick and easy method to ensure the column exists in the table.

I'd like to second this and for just the same reasons. Something like:

alter table add column if not exists my_new_col ...;

I would find very helpful.

[Charles Leifer]

You can simply use:

PRAGMA table_info('my_table')

To get a list of columns, which you can check against and then
conditionally add your column.

Post by Tim Streater

Post by Simon White
I would like to suggest the addition of the "If not exists" to the Add
Column feature of SQLite. There are quite common situations where
ensuring a column exists is important so that an update to remote
devices will not fail but it is not so important that deprecated fields
be removed. This is often the case with backward compatibility. New
columns will not affect old systems but allows all remote devices
running older software to be updated using the same process as new
devices. Once the hardware reaches end of life it will be replaced and
the new hardware will use the new columns. So having the ability to
Alter the table with a series of Add Column commands ensures that the
new records included in the update are added to the table. These is
especially true for limited remote devices where full database
management is not feasible. In this scenario all that is required is
that the required columns exist. So to be able to alter the table with
a standard SQL command is the most efficient method on such devices.
Developing scripts to drop and re-create and re-load tables on hundreds
of remote devices greatly increases the risk of failures. Having the "if
not exists" would remove all of this potential complexity and allow a
quick and easy method to ensure the column exists in the table.

alter table add column if not exists my_new_col ...;
I would find very helpful.
--
Cheers -- Tim
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[R Smith]

Post by Charles Leifer
PRAGMA table_info('my_table')
To get a list of columns, which you can check against and then
conditionally add your column.

Aye, but during a script in SQL-only you don't have that luxury. One
could also use a similar pragma to check if a table exists before
creating it, but the SQL for:
CREATE TABLE IF NOT EXISTS... makes it possible to add things without
failing mid-script with no programmatic help (and to be blunt, much
easier and nicer).

That said, I never add columns this way -  but that might only be
precisely because its IF NOT EXISTS does not exist. So...

+1

[Don V Nielsen]

This makes me feel there is a lot of pain coming in the future.

Given an update statement for n dbs of unknown state,
When a db lacks columns necessary to successfully execute the sql
Then add the columns to the db

I'm trying to imagine how to keep n remote dbs in a known state, say z,
when various updates sent to them put result in states a, b, c, d...z. How
do you keep all the db states sync'd when update 1 could create a column
but it fails or was not sent to all n dbs, and update 2 could create a
column but it fails or was not sent to all n dbs? How do you know what
state each remote db is in, or isn't in?

Post by R Smith

Post by Charles Leifer
PRAGMA table_info('my_table')
To get a list of columns, which you can check against and then
conditionally add your column.

Aye, but during a script in SQL-only you don't have that luxury. One
could also use a similar pragma to check if a table exists before
CREATE TABLE IF NOT EXISTS... makes it possible to add things without
failing mid-script with no programmatic help (and to be blunt, much
easier and nicer).
That said, I never add columns this way - but that might only be
precisely because its IF NOT EXISTS does not exist. So...
+1
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

Post by Charles Leifer
PRAGMA table_info('my_table')
To get a list of columns, which you can check against and then
conditionally add your column.

From C-code, you can use the sqlite_table_column_metadata() interface
[1] to quickly check for the existance of tables and/or columns. This
happens a lot in Fossil, which has an evolving schema but still needs
to work with older repositories.

[1] https://www.sqlite.org/c3ref/table_column_metadata.html

[Warren Young]

I would like to suggest the addition of the "If not exists" to the Add Column feature of SQLite.

I maintain an application that’s been through dozens of schema changes over its nearly quarter century of life, so let me tell you what works for us: a DB schema serial number.

Any time the DB schema changes, we bump the schema version number and modify a small program we include with the software that upgrades the schema.

Each schema change is contained in a single function within this program, most of which are just a simple CREATE or ALTER TABLE statement. A few are more complex, moving data around or transforming it.

If you upgrade the software on a machine running DB schema 5 to with software that requires DB schema 8, there are 3 steps that, if performed in order, always result in you running DB schema 8. This program simply calls those three functions in sequence based on the old schema number and the current schema number.

We’ve so rarely needed to roll back to older schema versions that we’ve done it by hand. If this happens to you often, you could code an inverse for each upgrade step that lets you roll back each change.

We started out with a simple integer version number, starting with 1, but once we started having multiple major versions in the wild with parallel development on each major version branch, we’d occasionally have to upgrade the DB schema in an older major version in cases where upgrading to the current major version wasn’t possible.

That caused us to modify the DB schema version numbering scheme:

800 # first DB schema for software major version 8
801
802
etc.
900 # software version 9
901
etc.

In this system, we can say that schema 802 and 901 do the same thing for the 800 and 900 series, respectively, so that if a system is upgraded from 802 to 902 in a single step, the upgrade program knows to skip the step done in 901, since it was already done in 802.

That’s as close as we’ve come to ever needing a fully-general tree-structured DB schema versioning system.

We’ve never come close to defining over a hundred schema versions, but if you think you will, then it’s easily handled by adding a digit. If you’re defining over a thousand DB schema versions between major software versions, you probably don’t understand change control. :)

Whenever we upgrade the software, the installer/package for the target runs a post-installation script that runs this DB schema upgrading program. (e.g. The %post script in RPM, InstallFinalize in WIX/MSI, etc.) If the schema upgrading program runs to completion successfully, it updates the schema version number and returns a success code, which tells the calling script that it can restart the software.

Now the neat bit: SQLite already supports doing this with its user_version feature:

https://sqlite.org/pragma.html#pragma_user_version

[Tim Streater]

Post by Warren Young

Post by Simon White
I would like to suggest the addition of the "If not exists" to the Add
Column feature of SQLite.

I maintain an application that’s been through dozens of schema changes over
its nearly quarter century of life, so let me tell you what works for us: a DB
schema serial number.
Any time the DB schema changes, we bump the schema version number and modify a
small program we include with the software that upgrades the schema.
Each schema change is contained in a single function within this program, most
of which are just a simple CREATE or ALTER TABLE statement. A few are more
complex, moving data around or transforming it.
If you upgrade the software on a machine running DB schema 5 to with software
that requires DB schema 8, there are 3 steps that, if performed in order,
always result in you running DB schema 8. This program simply calls those
three functions in sequence based on the old schema number and the current
schema number.

[snip]

Post by Warren Young
https://sqlite.org/pragma.html#pragma_user_version

Yeah, I already also do all of this, although I don't use the pragma since, officially, they are unsupported. But I'd rather have the IF NOT EXISTS.

[Warren Young]

Post by Tim Streater
I don't use the pragma since, officially, they are unsupported.

“Unsupported” how? It’s documented and part of the SQLite file header, which is quite stable.

If you mean this is not standard SQL and thus doesn’t work on non-SQLite DBs, then just do what we do, since we didn’t start out on SQLite: keep the schema version number in a separate file. You probably have some kind of preference file, or INI file, or registry key, or whatever associated with this software. Put it there if you don’t want to give it to SQLite to manage.

Post by Tim Streater
But I'd rather have the IF NOT EXISTS.

That sounds wasteful. 99+% of the time, the column will exist, because it was created the first time the program was run after being upgraded. Imagine how many redundant SQL statements you’ll have to run on program startup a dozen years hence when you have maybe a hundred schema changes.

I’ll stick by my advice: this is an upgrade-time operation only. After the software’s been successfully upgraded, the schema is stable and implicitly trustworthy, always.

[Tim Streater]

Post by Warren Young

Post by Tim Streater
I don't use the pragma since, officially, they are unsupported.

“Unsupported” how? It’s documented and part of the SQLite file header, which
is quite stable.
If you mean this is not standard SQL and thus doesn’t work on non-SQLite DBs,
then just do what we do, ...

No, I mean this, from https://www.sqlite.org/pragma.html:

Specific pragma statements may be removed and others added in future releases of SQLite. There is no guarantee of backwards compatibility.

[Warren Young]

Post by Tim Streater

Post by Tim Streater
I don't use the pragma since, officially, they are unsupported.

Specific pragma statements may be removed and others added in future releases of SQLite. There is no guarantee of backwards compatibility.

CREATE TABLE version ( schema INTEGER NOT NULL );