ALTER TABLE

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

ALTER TABLE

Bill McCormick-4
Is there no way to force columns added to a table with alter table to be
added at certain column positions?

Alternatively, if there is some way to save the data in an existing
table; drop the table; re-create the table with the desired schema; and
then reload the data, this would be useful as well. However, I cannot
see how to do this simply.

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

Re: ALTER TABLE

Puneet Kishor-2

On Feb 6, 2012, at 9:36 AM, Bill McCormick wrote:

> Is there no way to force columns added to a table with alter table to be added at certain column positions?
>
> Alternatively, if there is some way to save the data in an existing table; drop the table; re-create the table with the desired schema; and then reload the data, this would be useful as well. However, I cannot see how to do this simply.

just do it exactly as you state above...

dump
drop
recreate
reload

script it and it couldn't be any simpler. Although, ordinarily neither you nor the db should care about the order of the column. The results come out in the order you specify.

CREATE TABLE t (a, b, c);
SELECT b, c, a FROM t WHERE...

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

Re: ALTER TABLE

Gerry Snyder-4
In reply to this post by Bill McCormick-4
On 2/6/2012 8:36 AM, Bill McCormick wrote:
> Is there no way to force columns added to a table with alter table to
> be added at certain column positions?
>
> Alternatively, if there is some way to save the data in an existing
> table; drop the table; re-create the table with the desired schema;
> and then reload the data, this would be useful as well. However, I
> cannot see how to do this simply.
>

A very quick search at the SQLite website (hint, hint) found:

http://www.sqlite.org/faq.html#q11


HTH,

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

Re: ALTER TABLE

Bill McCormick-4
Gerry Snyder wrote, On 2/6/2012 9:48 AM:

> On 2/6/2012 8:36 AM, Bill McCormick wrote:
>> Is there no way to force columns added to a table with alter table to
>> be added at certain column positions?
>>
>> Alternatively, if there is some way to save the data in an existing
>> table; drop the table; re-create the table with the desired schema;
>> and then reload the data, this would be useful as well. However, I
>> cannot see how to do this simply.
>>
>
> A very quick search at the SQLite website (hint, hint) found:
>
> http://www.sqlite.org/faq.html#q11
Sorry, I should have mentioned that I did see that, but it doesn't quite
fit my application. I need a script that doesn't care what the existing
table looks like. In my situation, I may have dozens of databases among
different locations, perhaps not all at the same revision level. The
script I need would be able to bring each up to the current revision.

So, if I had a fist step:

CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1;

and then
DROP TABLE t1;

and then add the table with it's latest schema revision
CREATE TABLE t1( ... );

It seems difficult to get the saved data back in ...
INSERT INTO t1 SELECT * FROM t1_backup;

... without know what the previous schema looks like. It complains like this:
Error: table prod has 27 columns but 25 values were supplied

Any ideas?

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

Re: ALTER TABLE

Gerry Snyder-4
On 2/6/2012 9:22 AM, Bill McCormick wrote:

> Sorry, I should have mentioned that I did see that, but it doesn't
> quite fit my application. I need a script that doesn't care what the
> existing table looks like. In my situation, I may have dozens of
> databases among different locations, perhaps not all at the same
> revision level. The script I need would be able to bring each up to
> the current revision.
>
> So, if I had a fist step:
>
> CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1;
>
> and then
> DROP TABLE t1;
>
> and then add the table with it's latest schema revision
> CREATE TABLE t1( ... );
>
> It seems difficult to get the saved data back in ...
> INSERT INTO t1 SELECT * FROM t1_backup;
>
> ... without know what the previous schema looks like. It complains
> like this:
> Error: table prod has 27 columns but 25 values were supplied

Yes, the INSERT statement has to specify all of the original column
names in the proper order.

I wrote a general ALTER TABLE code in Tcl, and it is one of the largest
functions in the system. It includes moving columns within a table,
since I agree some times a simple spreadsheet-like display is useful.

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

Re: ALTER TABLE

Black, Michael (IS)
Hmmm....could .dump also have the ability to put out the column names for the inserts?

That would solve this problem without having to write a special program to do it yourself.



I suppose somebody might already have made a utility to do this?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems

________________________________
From: [hidden email] [[hidden email]] on behalf of Gerry Snyder [[hidden email]]
Sent: Monday, February 06, 2012 11:03 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] ALTER TABLE

On 2/6/2012 9:22 AM, Bill McCormick wrote:

> Sorry, I should have mentioned that I did see that, but it doesn't
> quite fit my application. I need a script that doesn't care what the
> existing table looks like. In my situation, I may have dozens of
> databases among different locations, perhaps not all at the same
> revision level. The script I need would be able to bring each up to
> the current revision.
>
> So, if I had a fist step:
>
> CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1;
>
> and then
> DROP TABLE t1;
>
> and then add the table with it's latest schema revision
> CREATE TABLE t1( ... );
>
> It seems difficult to get the saved data back in ...
> INSERT INTO t1 SELECT * FROM t1_backup;
>
> ... without know what the previous schema looks like. It complains
> like this:
> Error: table prod has 27 columns but 25 values were supplied

Yes, the INSERT statement has to specify all of the original column
names in the proper order.

I wrote a general ALTER TABLE code in Tcl, and it is one of the largest
functions in the system. It includes moving columns within a table,
since I agree some times a simple spreadsheet-like display is useful.

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

Re: ALTER TABLE

Nico Williams
In reply to this post by Bill McCormick-4
On Mon, Feb 6, 2012 at 9:36 AM, Bill McCormick <[hidden email]> wrote:
> Is there no way to force columns added to a table with alter table to be
> added at certain column positions?
>
> Alternatively, if there is some way to save the data in an existing table;
> drop the table; re-create the table with the desired schema; and then reload
> the data, this would be useful as well. However, I cannot see how to do this
> simply.

If order of columns is only important for aesthetic reasons you might
just use a ALTER TABLE to add the column at the end of the list and
then a VIEW to provide the view you prefer.

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

Re: ALTER TABLE

Bill McCormick-4
Nico Williams wrote, On 2/6/2012 12:44 PM:

> On Mon, Feb 6, 2012 at 9:36 AM, Bill McCormick<[hidden email]>  wrote:
>> Is there no way to force columns added to a table with alter table to be
>> added at certain column positions?
>>
>> Alternatively, if there is some way to save the data in an existing table;
>> drop the table; re-create the table with the desired schema; and then reload
>> the data, this would be useful as well. However, I cannot see how to do this
>> simply.
> If order of columns is only important for aesthetic reasons you might
> just use a ALTER TABLE to add the column at the end of the list and
> then a VIEW to provide the view you prefer.
>
The order is not important. What is important is that I come up with
some way to manage version updates. I've tried doing something similar
in the past using an "alter tables" script (using a different DB). The
script assumed some base version of schema was present, and then
proceeded adding new schema if it didn't already exist. It probably
seemed like a good idea at the time (to whomever started it), but as
time went on this script grew more and more unmanageable and I dreaded
having to use it.

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

Re: ALTER TABLE

Ryan Johnson-10
On 06/02/2012 1:59 PM, Bill McCormick wrote:

> Nico Williams wrote, On 2/6/2012 12:44 PM:
>> On Mon, Feb 6, 2012 at 9:36 AM, Bill
>> McCormick<[hidden email]>  wrote:
>>> Is there no way to force columns added to a table with alter table
>>> to be
>>> added at certain column positions?
>>>
>>> Alternatively, if there is some way to save the data in an existing
>>> table;
>>> drop the table; re-create the table with the desired schema; and
>>> then reload
>>> the data, this would be useful as well. However, I cannot see how to
>>> do this
>>> simply.
>> If order of columns is only important for aesthetic reasons you might
>> just use a ALTER TABLE to add the column at the end of the list and
>> then a VIEW to provide the view you prefer.
>>
> The order is not important. What is important is that I come up with
> some way to manage version updates. I've tried doing something similar
> in the past using an "alter tables" script (using a different DB). The
> script assumed some base version of schema was present, and then
> proceeded adding new schema if it didn't already exist. It probably
> seemed like a good idea at the time (to whomever started it), but as
> time went on this script grew more and more unmanageable and I dreaded
> having to use it.
You might exploit #pragma user_version to help you track future changes,
though that wouldn't necessarily help with the existing mess.

Ryan

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

Re: ALTER TABLE

Tim Streater-3
In reply to this post by Bill McCormick-4
On 06 Feb 2012 at 19:31, Ryan Johnson <[hidden email]> wrote:

> On 06/02/2012 1:59 PM, Bill McCormick wrote:

>> The order is not important. What is important is that I come up with
>> some way to manage version updates. I've tried doing something similar
>> in the past using an "alter tables" script (using a different DB). The
>> script assumed some base version of schema was present, and then
>> proceeded adding new schema if it didn't already exist. It probably
>> seemed like a good idea at the time (to whomever started it), but as
>> time went on this script grew more and more unmanageable and I dreaded
>> having to use it.

> You might exploit #pragma user_version to help you track future changes,
> though that wouldn't necessarily help with the existing mess.

Can that be relied upon, though? The doc explicitly states: "Specific pragma statements may be removed and others added in future releases of SQLite. There is no guarantee of backwards compatibility".

I keep my own version number in a master table and use that to indicate that a table needs updating.

--
Cheers  --  Tim

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

Re: ALTER TABLE

Nico Williams
In reply to this post by Bill McCormick-4
I'm pretty sure that the user_version pragma is considered stable.

That said, if your application is in full control of the DB then you
could just check the exact create statements logged in sqlite_master
(this is probably less stable, ironically enough).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE

Roger Binns
In reply to this post by Bill McCormick-4
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/02/12 12:50, Tim Streater wrote:
> Can that [pragma user_version] be relied upon, though?

It is used by both Firefox and Android.  The actual value is stored in the
SQLite header.  It would be astonishing and unprecedented for the SQLite
team to remove it, nor is there is any conceivable reason to do so.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8wQMcACgkQmOOfHg372QROzACgqxESS5vKgz1CK5GAxeHFsNPV
pq8An39N2qFS5OnWxCKcQ1dCEXxRehsT
=zsOv
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users