Add Column with "If Not Exists"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Add Column with "If Not Exists"

dcipher
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.

--
Regards,
Simon White
dCipher Computing
705-500-0191

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

Tim Streater-3
On 01 Aug 2018, at 14:34, Simon White <[hidden email]> wrote:

> 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.


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

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.

On Wed, Aug 1, 2018 at 9:13 AM, Tim Streater <[hidden email]> wrote:

> On 01 Aug 2018, at 14:34, Simon White <[hidden email]>
> wrote:
>
> > 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.
>
>
> --
> Cheers  --  Tim
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

R Smith-2
On 2018/08/01 5:29 PM, Charles Leifer wrote:
> 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.

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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

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?

On Wed, Aug 1, 2018 at 10:46 AM R Smith <[hidden email]> wrote:

> On 2018/08/01 5:29 PM, Charles Leifer wrote:
> > 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.
>
> 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
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

Warren Young
In reply to this post by dcipher
On Aug 1, 2018, at 7:34 AM, Simon White <[hidden email]> wrote:
>
> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

Tim Streater-3
On 01 Aug 2018, at 20:40, Warren Young <[hidden email]> wrote:

> On Aug 1, 2018, at 7:34 AM, Simon White <[hidden email]>
> wrote:
>>
>>  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]

> Now the neat bit: SQLite already supports doing this with its user_version
> feature:
>
>     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.


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

Warren Young
On Aug 1, 2018, at 1:52 PM, Tim Streater <[hidden email]> wrote:
>
> 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.

> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

Tim Streater-3
On 01 Aug 2018, at 21:06, Warren Young <[hidden email]> wrote:

> On Aug 1, 2018, at 1:52 PM, Tim Streater <[hidden email]> wrote:
>>
>> 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.





--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

Warren Young
On Aug 1, 2018, at 3:57 PM, Tim Streater <[hidden email]> wrote:
>
> On Aug 1, 2018, at 1:52 PM, Tim Streater <[hidden email]> wrote:
>>>
>>> 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 );
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Add Column with "If Not Exists"

Richard Hipp-3
In reply to this post by Charles Leifer
On 8/1/18, Charles Leifer <[hidden email]> wrote:
> 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.

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

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users