Re: [sqlite] ALTER TABLE ADD COLUMN

[x]

Graham, as you probably realise from reading David & Simon’s replies, it’s 
really about avoiding those type code bytes altogether. You can use ADD COLUMN 
to add 10 columns to a billion row table in microseconds so obviously all 
that’s changed is the header. SQLITE_ENABLE_NULL_TRIM answers my question 
although Simon’s point about SQLITE_ENABLE_DEFAULT_TRIM is valid. I see that 
enabling it might trigger some obscure bug w.r.t. blobs and that it may be 
enabled by default in future versions.








From: sqlite-users  on behalf of 
Graham Holden 
Sent: Wednesday, October 23, 2019 2:09:19 PM
To: SQLite mailing list 
Subject: Re: [sqlite] ALTER TABLE ADD COLUMN

Wednesday, October 23, 2019, 1:53:10 PM, x  wrote:

> From the documentation

> “A record might have fewer values than the number of columns in the 
> corresponding table. This can happen, for example, after an ALTER TABLE ... 
> ADD COLUMN SQL statement has increased the number of
> columns in the table schema without modifying preexisting rows in the table. 
> Missing values at the end of the record are filled in using the default value 
> for the corresponding columns defined in
> the table schema.”

> Suppose you have a table with say 5 columns that are almost always
> the default value (probably zero or null). Does the above suggest
> you should make them the last 5 columns in the table as the last
> n columns that are the default value won’t take up space? Or does
> this state just exist after ADD COLUMN but any rows added thereafter
> use the space?

I believe it can only happen after an ADD COLUMN, however, zero or
NULL values will, essentially, take zero space whereever they are in a
row. If you look in-and-around:

   https://www.sqlite.org/fileformat.html#record_format,

you will see that the "type code" used for each column in a row has
specific values for "NULL" and zero (0 and 8, respectively). This
means that where those NULL/zero occurs, no extra space is used to
hold the value.

Graham


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE ADD COLUMN

[David Raymond]

On disk a record basically looks like:
Here are 5 values: value1, value2, value3, value4, value5

If your query is looking for the 6th, 7th or 8th field and the record on the 
disk only has 5, then it goes " I guess they should be the default 
values for the missing fields." What that means is that when you add a new 
field it doesn't have to re-write the table because it handles the "missing on 
disk" fields just fine.

There's actually a compile option for SQLite to intentionally do this all the 
time and leave out as many trailing NULL fields as it can to save space. I 
think it's SQLITE_ENABLE_NULL_TRIM, which is disabled by default.


-Original Message-
From: sqlite-users  On Behalf Of x
Sent: Wednesday, October 23, 2019 8:53 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] ALTER TABLE ADD COLUMN

From the documentation

“A record might have fewer values than the number of columns in the 
corresponding table. This can happen, for example, after an ALTER TABLE ... ADD 
COLUMN SQL statement has increased the number of columns in the table schema 
without modifying preexisting rows in the table. Missing values at the end of 
the record are filled in using the default value for the corresponding columns 
defined in the table schema.”

Suppose you have a table with say 5 columns that are almost always the default 
value (probably zero or null). Does the above suggest you should make them the 
last 5 columns in the table as the last n columns that are the default value 
won’t take up space? Or does this state just exist after ADD COLUMN but any 
rows added thereafter use the space?
*Assume the 5 columns are little used so it doesn’t matter that they are the 
last named columns.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE ADD COLUMN

[Simon Slavin]

On 23 Oct 2019, at 1:53pm, x  wrote:

> Suppose you have a table with say 5 columns that are almost always the 
> default value (probably zero or null). Does the above suggest you should make 
> them the last 5 columns in the table as the last n columns that are the 
> default value won’t take up space?

If you make up your own file in SQLite format and do this, you get the right 
results: missing fields at the end of a row return the correct DEFAULT value.  
I seem to remember testing this long ago using a hex editor.

However, I don't think the SQLite API checks for this or implements it.  I 
believe that even if a row ends in NULL NULL NULL those three values get 
written to the row.  However they take up one octet each, so it will have 
minimal effect on filesize.

I note with interest the SQLITE_ENABLE_NULL_TRIM option mentioned by 
david.raym...@tomtom.com .  If this was to work correctly according to the text 
of your question it might more properly be called SQLITE_ENABLE_DEFAULT_TRIM .

It might be interesting to find out what it does if the default value for a 
column isn't NULL.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE ADD COLUMN

[Graham Holden]

Wednesday, October 23, 2019, 1:53:10 PM, x  wrote:

> From the documentation

> “A record might have fewer values than the number of columns in the 
> corresponding table. This can happen, for example, after an ALTER TABLE ... 
> ADD COLUMN SQL statement has increased the number of
> columns in the table schema without modifying preexisting rows in the table. 
> Missing values at the end of the record are filled in using the default value 
> for the corresponding columns defined in
> the table schema.”

> Suppose you have a table with say 5 columns that are almost always
> the default value (probably zero or null). Does the above suggest
> you should make them the last 5 columns in the table as the last
> n columns that are the default value won’t take up space? Or does
> this state just exist after ADD COLUMN but any rows added thereafter
> use the space?  

I believe it can only happen after an ADD COLUMN, however, zero or
NULL values will, essentially, take zero space whereever they are in a
row. If you look in-and-around:

   https://www.sqlite.org/fileformat.html#record_format,
 
you will see that the "type code" used for each column in a row has
specific values for "NULL" and zero (0 and 8, respectively). This
means that where those NULL/zero occurs, no extra space is used to
hold the value.

Graham


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table

[Manuel Rigger]

Great, thanks a lot, Dan!

Best,
Manuel

On Mon, May 6, 2019 at 6:18 PM Dan Kennedy  wrote:

>
> On 6/5/62 16:42, Manuel Rigger wrote:
> > Hi everyone,
> >
> > the following example fails with an error "no such column: c0":
> >
> > CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID;
> > ALTER TABLE t0 RENAME COLUMN c0 TO c1;
>
> Thanks again for the bug reports. This one is now fixed here:
>
> https://sqlite.org/src/info/91f701d39852ef1ddb29
>
> Dan.
>
>
>
> >
> > However, specifying c0 as the PRIMARY KEY in the column definition rather
> > than in a table constraint seems to work:
> >
> > CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID;
> > ALTER TABLE t0 RENAME COLUMN c0 TO c1;
> >
> > Best,
> > Manuel
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE fails when renaming an INTEGER PRIMARY KEY column in a WITHOUT ROWID table

[Dan Kennedy]

On 6/5/62 16:42, Manuel Rigger wrote:

Hi everyone,

the following example fails with an error "no such column: c0":

CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY (c0)) WITHOUT ROWID;
ALTER TABLE t0 RENAME COLUMN c0 TO c1;


Thanks again for the bug reports. This one is now fixed here:

https://sqlite.org/src/info/91f701d39852ef1ddb29

Dan.





However, specifying c0 as the PRIMARY KEY in the column definition rather
than in a table constraint seems to work:

CREATE TABLE t0 (c0 INTEGER PRIMARY KEY) WITHOUT ROWID;
ALTER TABLE t0 RENAME COLUMN c0 TO c1;

Best,
Manuel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

[Mark Johnson]

Am Do., 27. Dez. 2018 um 02:53 Uhr schrieb Mark Johnson <
mj10...@googlemail.com>:

> (summery of the last messages that were sent as email)
>
> >> Please add the list of column names after the view name:
> >> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ...
> >> I have a note to improve the documentation about this point.
>
> So would the following be true:
>
> To insure that a constant, proper column resolvement, VIEWs should be
> defined in a similar way as an INSERT command where a sub-set of columns
> with values is done:
>
> INSERT INTO gcp_master
> (name, longitude,latitude)
>  SELECT
>   name, longitude,latitude
>  FROM populated_places
>  WHERE name LIKE "roma,%";
>
>
> CREATE VIEW gcp_master_view
> (name, longitude,latitude) AS
>  SELECT
>   name, longitude,latitude
>  FROM populated_places
>  WHERE name LIKE "roma,%";
>
>
> --- Final note:
>
> When creating a VIEW with a list of defined column names, a COLUMN rename
> on the underlining TABLE:
>
> ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO
> "position_x";
> ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO
> "position_y";
>
Correction:

ALTER TABLE "main"."populated_places" RENAME COLUMN "longitude" TO
"position_x";
ALTER TABLE "main"."populated_places" RENAME COLUMN "latitude" TO
"position_y";

>
> will result will be:
> - rename of the COLUMN of the TABLE
> - rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs
> - will NOT rename the COLUMN definition of the VIEW
>
> The final CREATE command will then look like this:
>
> CREATE VIEW gcp_master_view
> (name, longitude,latitude) AS
>  SELECT
>   name, "position_x","position_y"
>  FROM populated_places
>  WHERE name LIKE "roma,%";
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

[Mark Johnson]

(summery of the last messages that were sent as email)

>> Please add the list of column names after the view name:
>> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ...
>> I have a note to improve the documentation about this point.

So would the following be true:

To insure that a constant, proper column resolvement, VIEWs should be
defined in a similar way as an INSERT command where a sub-set of columns
with values is done:

INSERT INTO gcp_master
(name, longitude,latitude)
 SELECT
  name, longitude,latitude
 FROM populated_places
 WHERE name LIKE "roma,%";


CREATE VIEW gcp_master_view
(name, longitude,latitude) AS
 SELECT
  name, longitude,latitude
 FROM populated_places
 WHERE name LIKE "roma,%";


--- Final note:

When creating a VIEW with a list of defined column names, a COLUMN rename
on the underlining TABLE:

ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO
"position_x";
ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO
"position_y";

will result will be:
- rename of the COLUMN of the TABLE
- rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs
- will NOT rename the COLUMN definition of the VIEW

The final CREATE command will then look like this:

CREATE VIEW gcp_master_view
(name, longitude,latitude) AS
 SELECT
  name, "position_x","position_y"
 FROM populated_places
 WHERE name LIKE "roma,%";
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

[Richard Hipp]

On 12/26/18, Mark Johnson  wrote:
> Am Do., 20. Dez. 2018 um 16:34 Uhr schrieb Mark Johnson <
> mj10...@googlemail.com>:
>
>> Based on ticket
>>
>> https://www.sqlite.org/src/tktview?name=43ddc85a63
>>
>>
>>
>> However, the column count is not correct.
>> In my case 2 columns are missing: which should be 19.
>>
> After a fresh look at this today, I realized that this error was caused by
> a faulty VIEW, where the 2 columns were not defined in the VIEW but being
> used in the TRIGGER.
>
> After correcting the VIEW, the error is different:
>
> ALTER TABLE "main"."middle_earth_admin" RENAME COLUMN "admin_type" TO
> "admin_level";

I closed the original ticket (which I neglected to do originally) with
an explanation of why it is not a bug.

In order for us to investigate this new problem you are having, please
supply us with the VIEW, CREATE TRIGGER, and ALTER TABLE statements
that are giving you trouble.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

[Mark Johnson]

Am Do., 20. Dez. 2018 um 16:34 Uhr schrieb Mark Johnson <
mj10...@googlemail.com>:

> Based on ticket
>
> https://www.sqlite.org/src/tktview?name=43ddc85a63
>
>
>
> However, the column count is not correct.
> In my case 2 columns are missing: which should be 19.
>
After a fresh look at this today, I realized that this error was caused by
a faulty VIEW, where the 2 columns were not defined in the VIEW but being
used in the TRIGGER.

After correcting the VIEW, the error is different:

ALTER TABLE "main"."middle_earth_admin" RENAME COLUMN "admin_type" TO
"admin_level";

The lookupName parameter 'zCol' contains the new column name 'admin_level',
but is searching for the old column name admin_type, which is not being
found.

-I-> lookupName -200a- looking_for[NEW.admin_type] pParse->eTriggerOp[116]
pTriggerTab->zName[middle_earth_admin_general] -I-> lookupName -201a-
looking_for[NEW.admin_type] op[116 !=TK_DELETE[117]]
pTriggerTab->zName[middle_earth_admin_general]

-I-> lookupName -202a loop - pCol->zName[admin_type]==zCol[admin_level]
 iCol[3]

Error: near line 14: error in trigger vw_ins_middle_earth_admin_general
after rename: no such column: NEW.admin_type


>
>
> Mark Johnson
> mj10...@googlemail.com
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE, modifying columns

[Thomas Kurz]

> I never would have allowed the recent
> enhancements to ALTER TABLE that broke it.

The enhancements made have been way overdue. Personally, I appreciate them very 
much and they are worth the "trouble". And I hope that the small problem does 
not prevent you from taking MODIFY COLUMN and DROP COLUMN into account. I think 
it should solve most problems as the quite complex procedure (which obviously 
seems to be improperly implemented quite often, not only from me *g*) would 
then be obsolete. And, in addition, SQLite could possibly even use a more 
efficient way for the modifications than just copying all the data. (In fact, a 
13th step "VACUUM" may be required in many cases, as dropping the table in step 
6 can lead to a large "hole" in the database.)

As far as the example is concerned, I will try to reproduce tomorrow.

Kind regards,
Thomas

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE, modifying columns

[Richard Hipp]

On 12/12/18, Thomas Kurz  wrote:
> This doesn't work either. The error now occurs in the "ALTER TABLE" line,
> which is correct as the table "x" being refered to doesn't exist that
> moment. Tested with both 3.25.2 and 3.26.

Can you please post a script showing us exactly what you are trying to
do when you get the error?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE, modifying columns

[Richard Hipp]

On 12/12/18, Thomas Kurz  wrote:
>
> Btw, has the "correct vs. incorrect" table that you've cited already been
> there before release 3.25?

The procedure description is unchanged for many years.  I added the
"Caution:" section recently, because a lot of people have been having
the same problem you are currently having.  Had I known in advance
that so many people were doing the "incorrect" procedure for making
schema modifications, I never would have allowed the recent
enhancements to ALTER TABLE that broke it.  But I didn't know.  And
that is now water under the bridge.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE, modifying columns

[Thomas Kurz]

This doesn't work either. The error now occurs in the "ALTER TABLE" line, which 
is correct as the table "x" being refered to doesn't exist that moment. Tested 
with both 3.25.2 and 3.26.

Btw, has the "correct vs. incorrect" table that you've cited already been there 
before release 3.25?


- Original Message - 
From: Shawn Wagner 
To: SQLite mailing list 
Sent: Wednesday, December 12, 2018, 18:02:54
Subject: [sqlite] ALTER TABLE, modifying columns

You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz  Dear all,

> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):

> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:

> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
>  more to do here ...
> COMMIT
> PRAGMA foreign_keys=1

> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".

> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.

> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.

> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))

> Kind regards,
> Thomas

> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE, modifying columns

[Shawn Wagner]

You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz  Dear all,
>
> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):
>
> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:
>
> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
>  more to do here ...
> COMMIT
> PRAGMA foreign_keys=1
>
> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".
>
> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.
>
> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.
>
> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))
>
> Kind regards,
> Thomas
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table, other alter category, fails in presence of trigger on 3.25.2

[Thierry Henrio]

Hello Keith,

On Thu, Oct 4, 2018 at 1:16 AM Keith Medcalf  wrote:

>
> Unrelated, but are you sure that you want the albums "id int primary key"
> and did not happen to misspell "integer" so that the declaration should be
> "id integer primary key".  In the former case, id is an integer that just
> happens to be unique (ie, "id int primary key" is the same as "id integer
> unique") and not an explicitly named alias for the rowid (which requires
> correct spelling of the phrase "integer primary key").
>

I though "int" was the same as "integer" (
https://www.sqlite.org/datatype3.html).
Thanks.


> Second unrelated, do you not want an affinity for the album_id column in
> rates?  Should not you have declared it as "album_id integer references
> albums(id) on delete cascade"?
>

Correct.

Third unrelated, do not forget to create an index on the foreign key (as in
> "CREATE INDEX idxRates_album_id on rates (album_id)" for example).
>

Sure.

Fourth unrelated, do you want the title and comment_text to be case
> sensitive or should they have COLLATE NOCASE?
>

No.


> As to the issue with the updated table rename, you can either use a
> version of sqlite3 that does not have the alter table rename updates, or
> for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to avoid
> using the new "change the table names in triggers etc" features added in
> 3.25.0 so that you can continue to use the old method of just "substituting
> tables".
>

Indeed, use the pragma, thanks! https://www.sqlite.org/pragma.html#toc

using pragma, the following script output 5, which is expected result.

drop table if exists albums;
drop table if exists rates;
--
create table albums (id integer primary key, title text, score int);
create table rates (album_id integer references albums(id) on delete
cascade, score int);
create trigger test after insert on rates begin update albums set
score=new.score where id=new.album_id; end;
--
insert into albums (id, title) values (1, 'Cheap Thrills');
--
begin;
pragma legacy_alter_table=ON;
drop table if exists new_albums;
create table new_albums (id int primary key, title text not null, score
int);
insert into new_albums (id, title, score) select id, title, score from
albums;
drop table albums;
alter table new_albums rename to albums;
pragma legacy_alter_table=OFF;
end;
--
insert into rates values (1, 5);
select score from albums;

When I comment the pragma, I have

Error: near line 16: error in trigger test: no such table: main.albums
Error: near line 20: no such table: main.albums
Error: near line 21: no such table: albums

It is a resolution for the problem I faced : add a constraint to colum of a
table referenced in a trigger.

Do you believe ?

a) https://www.sqlite.org/lang_altertable.html#otheralter could be updated.

b) rename A to B should not fail in the face of a trigger referencing B.

?
, Thierry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table, other alter category, fails in presence of trigger on 3.25.2

[Keith Medcalf]

Unrelated, but are you sure that you want the albums "id int primary key" and 
did not happen to misspell "integer" so that the declaration should be "id 
integer primary key".  In the former case, id is an integer that just happens 
to be unique (ie, "id int primary key" is the same as "id integer unique") and 
not an explicitly named alias for the rowid (which requires correct spelling of 
the phrase "integer primary key").

Second unrelated, do you not want an affinity for the album_id column in rates? 
 Should not you have declared it as "album_id integer references albums(id) on 
delete cascade"?

Third unrelated, do not forget to create an index on the foreign key (as in 
"CREATE INDEX idxRates_album_id on rates (album_id)" for example).

Fourth unrelated, do you want the title and comment_text to be case sensitive 
or should they have COLLATE NOCASE?

As to the issue with the updated table rename, you can either use a version of 
sqlite3 that does not have the alter table rename updates, or for version 
3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to avoid using the new 
"change the table names in triggers etc" features added in 3.25.0 so that you 
can continue to use the old method of just "substituting tables".

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thierry Henrio
>Sent: Wednesday, 3 October, 2018 16:43
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] alter table, other alter category, fails in
>presence of trigger on 3.25.2
>
>Hello,
>
>I want to report a bug, I checked
>https://www.sqlite.org/src/rptview?rn=8
>and did not find the same?
>
>Let schema be:
>
>sqlite> .schema
>CREATE TABLE albums (id int primary key, title text, score int);
>CREATE TABLE rates (album_id references albums(id) on delete cascade,
>comment text, score int);
>CREATE TRIGGER test after insert on rates begin update albums set
>score=new.score where id=new.album_id; end;
>
>And I want to make albums.title not null.
>This falls into
>https://www.sqlite.org/lang_altertable.html#otheralter.
>
>sqlite> BEGIN;
>sqlite> CREATE TABLE new_albums (id int primary key, title text not
>null,
>score int);
>sqlite> INSERT INTO new_albums (id, title, score) SELECT id, title,
>score
>FROM albums;
>sqlite> DROP TABLE albums;
>sqlite> ALTER TABLE new_albums RENAME TO albums;
>Error: error in trigger test: no such table: main.albums
>
>Expected behavior is last alter is ok.
>
>This is in 3.25.2
>
>sqlite> select sqlite_version();
>3.25.2
>
>Cheers, Thierry
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Donald Griggs]

David, that's a useful reminder than trigger and index NAMES are not
automatically changed when the referenced table is renamed.

Maybe another is that while index and trigger creation statements
automatically have the referenced table name changed (at least the
non-action trigger commands) by ALTER TABLE renames, the FOREIGN KEY table
references are changed ONLY if foreign keys are turned on at the time of
the ALTER TABLE.  (PRAGMA foreign_keys=ON;)

From:  http://sqlite.org/lang_altertable.html

===

... This command cannot be used to move a table between attached databases,
only to rename a table within the same database.

If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed. However, if there are any
view definitions, or statements executed by triggers that refer to the
table being renamed, these are not automatically modified to use the new
table name. If this is required, the triggers or view definitions must be
dropped and recreated to use the new table name by hand.

*Important Note:* The 'ALTER TABLE ... RENAME TO ...' command does not
update action statements within triggers or SELECT statements within views.
If the table being renamed is referenced from within triggers or views,
then those triggers and views must be dropped and recreated separately by
the application.

If foreign key constraints  are enabled
 when a table is
renamed, then any REFERENCES clauses
 in any table (either the
table being renamed or some other table) that refer to the table being
renamed are modified to refer to the renamed table by its new name.
===
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[David Raymond]

Small note on this: Don't forget any indexes, triggers, etc. They will carry 
over when the rename table is done, but they'll have their original names. So 
if you have a table foo with index foo_idx, and do "alter table foo rename to 
bar", then you'll have index foo_idx on table bar. Which means some droping and 
renaming of indexes, triggers, foreign keys etc as well.


sqlite> create table foo (id integer primary key, foo text);

sqlite> create index foo_idx on foo (foo);

sqlite> create trigger foo_trg before delete on foo begin select raise(abort, 
'Not allowing deletes from foo'); end;

sqlite> create table bar(id integer primary key, foo_id int references foo);

sqlite> select * from sqlite_master;
type  name  tbl_namerootpagesql

    --  --  


table foo   foo 2   CREATE TABLE foo (id integer 
primary key, foo text)

index foo_idx   foo 3   CREATE INDEX foo_idx on foo (foo)

trigger   foo_trg   foo 0   CREATE TRIGGER foo_trg before 
delete on foo begin select raise(abort, 'Not a
llowing deletes from foo'); end
table bar   bar 4   CREATE TABLE bar(id integer primary 
key, foo_id int references foo)


sqlite> alter table foo rename to foobar;

sqlite> select * from sqlite_master;
type  name  tbl_namerootpagesql

    --  --  


table foobarfoobar  2   CREATE TABLE "foobar" (id integer 
primary key, foo text)

index foo_idx   foobar  3   CREATE INDEX foo_idx on "foobar" 
(foo)

trigger   foo_trg   foobar  0   CREATE TRIGGER foo_trg before 
delete on "foobar" begin select raise(abort, '
Not allowing deletes from foo'); end
table bar   bar 4   CREATE TABLE bar(id integer primary 
key, foo_id int references "foobar")


sqlite> create table foo (id integer primary key, something_new text);

sqlite> create index foo_idx on foo (something_new);
Error: index foo_idx already exists

sqlite>



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Charles Leifer
Sent: Tuesday, May 22, 2018 5:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] ALTER TABLE

SQLite supports renaming tables, so in my experience you move the old table
out of the way, and create the new table with the desired schema and the
original name.

On Tue, May 22, 2018 at 2:34 PM, Igor Korot <ikoro...@gmail.com> wrote:

> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer <colei...@gmail.com>
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz <sqlite.2...@t-net.ruhr>
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
&

Re: [sqlite] ALTER TABLE

[Charles Leifer]

SQLite supports renaming tables, so in my experience you move the old table
out of the way, and create the new table with the desired schema and the
original name.

On Tue, May 22, 2018 at 2:34 PM, Igor Korot  wrote:

> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer 
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz 
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Thomas Kurz]

Thanks for all explanations. The background of my question wasn't about speed, 
but about easier handling. It would be perfectly ok if Sqlite did more or less 
the same as when currently manually recreating/copying the table. I just would 
appreciate having an intuitive (and easy-to-read) SQL statement for that 
operation.


- Original Message - 
From: David Raymond <david.raym...@tomtom.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Sent: Tuesday, May 22, 2018, 22:32:39
Subject: [sqlite] ALTER TABLE

Some of these things can get taken care of by simply messing with the 
sqlite_master table contents. Renaming a field for example would "just" be 
basically doing some replacing in the many places field names are used. So the 
table entry itself, any indexes, views that use that field, triggers, etc. It's 
a lot of managing, but since our databases work then we know that the ability 
to parse out what's what in the schema text has already been done.

As far as dropping a field, you'd have to do a re-write of the whole table even 
if there were no dependency issues. The actual data storage has no info about 
what name each field is, it just stores the data in the order of the schema, so 
if you drop field #3, then you actually have to go in and remove field #3 from 
the data pages, or else it won't realize the old field #4 is now field #3. I 
originally thought you might be able to get away with dropping the final field, 
as all the front fields would still be in the right order. But then if you were 
to issue the "alter table add column" then your new field would suddenly have 
all the data from the dropped field, so you'd need to re-write it then anyway. 
You'd have to introduce a "sqlite file format 5" or something similar to be 
able to handle it without the re-write.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Stephen Chrzanowski
Sent: Tuesday, May 22, 2018 3:58 PM
To: SQLite mailing list
Subject: Re: [sqlite] ALTER TABLE

Thinking off the cuff, there'd be a seven step process for this;

Begin Transaction
Turn off the PK/FK relationship PRAGMA constraint checks
Rename old table to a temp table via whatever means are available
Create the new table
INSERT INTO the new table
Turn on the PK/FK relationship PRAGMA constraint check
End transaction.

I've never manually run the SQLite commands to perform a rename, as I use
SQLite expert, and "F2" allows me to rename if needed (So it does all the
legwork) so I don't know if its an alter table command or you have to go
the drop/create route.  On top of that, my databases typically only contain
about a meg or two of textual data, so, how quick my F2 would work on a
larger dataset, I don't know.

SQLite does not hold a unique permanent "link" from one table to another.
So the act of renaming, and then re-provisioning the table will not change
your code or how the other tables view the new table.  Its concern is that
at the time of the SQL execution, the tables and fields exist as required.
In other words, if you do rename a table, every other table in the database
is oblivious to the change, meaning its not aware.



On Tue, May 22, 2018 at 3:34 PM, Igor Korot <ikoro...@gmail.com> wrote:

> Hi, Charles,

> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer <colei...@gmail.com>
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.

> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...

> Thank you.

> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz <sqlite.2...@t-net.ruhr>
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
>

Re: [sqlite] ALTER TABLE

[David Raymond]

Some of these things can get taken care of by simply messing with the 
sqlite_master table contents. Renaming a field for example would "just" be 
basically doing some replacing in the many places field names are used. So the 
table entry itself, any indexes, views that use that field, triggers, etc. It's 
a lot of managing, but since our databases work then we know that the ability 
to parse out what's what in the schema text has already been done.

As far as dropping a field, you'd have to do a re-write of the whole table even 
if there were no dependency issues. The actual data storage has no info about 
what name each field is, it just stores the data in the order of the schema, so 
if you drop field #3, then you actually have to go in and remove field #3 from 
the data pages, or else it won't realize the old field #4 is now field #3. I 
originally thought you might be able to get away with dropping the final field, 
as all the front fields would still be in the right order. But then if you were 
to issue the "alter table add column" then your new field would suddenly have 
all the data from the dropped field, so you'd need to re-write it then anyway. 
You'd have to introduce a "sqlite file format 5" or something similar to be 
able to handle it without the re-write.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Stephen Chrzanowski
Sent: Tuesday, May 22, 2018 3:58 PM
To: SQLite mailing list
Subject: Re: [sqlite] ALTER TABLE

Thinking off the cuff, there'd be a seven step process for this;

Begin Transaction
Turn off the PK/FK relationship PRAGMA constraint checks
Rename old table to a temp table via whatever means are available
Create the new table
INSERT INTO the new table
Turn on the PK/FK relationship PRAGMA constraint check
End transaction.

I've never manually run the SQLite commands to perform a rename, as I use
SQLite expert, and "F2" allows me to rename if needed (So it does all the
legwork) so I don't know if its an alter table command or you have to go
the drop/create route.  On top of that, my databases typically only contain
about a meg or two of textual data, so, how quick my F2 would work on a
larger dataset, I don't know.

SQLite does not hold a unique permanent "link" from one table to another.
So the act of renaming, and then re-provisioning the table will not change
your code or how the other tables view the new table.  Its concern is that
at the time of the SQL execution, the tables and fields exist as required.
In other words, if you do rename a table, every other table in the database
is oblivious to the change, meaning its not aware.



On Tue, May 22, 2018 at 3:34 PM, Igor Korot <ikoro...@gmail.com> wrote:

> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer <colei...@gmail.com>
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz <sqlite.2...@t-net.ruhr>
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> h

Re: [sqlite] ALTER TABLE

[Paul Sanderson]

To modify column names if you want to live dangerously you could try
something like this

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> create table test (c1, c2, c3);
sqlite> insert into test values(1, 2, 3);
sqlite> pragma writable_schema = yes;
sqlite> update sqlite_master set sql = 'create table test (d1, d2, d3)'
where name = 'test';
sqlite> .quit

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> select * from test;
d1|d2|d3
1|2|3

You could potentially do the same to modify the type of a column and
SQLites column affinity rules could/should help display the data correctly.
I have not tested this for different column types

sqlite> create table test2 (c1 int, c2 int, c3 int);
sqlite> insert into test2 values(1, 2, 3);
sqlite> pragma writable_schema = yes;
sqlite> update sqlite_master set sql = 'create table test2 (d1 text, d2
text, d3 text)' where name = 'test2';
sqlite> .quit

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> select * from test2;
d1|d2|d3
1|2|3

The same idea should also work to add/remove a foreign key -  but you would
of course need to understand any implications and ensure that the existing
data does not cause a constraint conflict.

The only suggestion I have a problem with is dropping a column. Every
record in the b-tree would need to be modified to remove the now redundant
data. The on;ly exception I can see to this is if you are dropping the last
column from a table

sqlite> pragma writable_schema = yes;
sqlite> update sqlite_master set sql = 'create table test2 (e1 text, e2
text)' where name = 'test2';
sqlite> .quit

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> select * from test2;
e1|e2
1|2
sqlite> pragma integrity_check;
integrity_check
ok

I have not done any thorough testing as this sort of thing is outside my
main area of interest, but it might give you some ideas. It goes without
saying that messing with the sqlite_schema is dangerous territory.





Paul
www.sandersonforensics.com
SQLite Forensics Book 

On 22 May 2018 at 20:34, Igor Korot  wrote:

> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer 
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz 
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Stephen Chrzanowski]

Thinking off the cuff, there'd be a seven step process for this;

Begin Transaction
Turn off the PK/FK relationship PRAGMA constraint checks
Rename old table to a temp table via whatever means are available
Create the new table
INSERT INTO the new table
Turn on the PK/FK relationship PRAGMA constraint check
End transaction.

I've never manually run the SQLite commands to perform a rename, as I use
SQLite expert, and "F2" allows me to rename if needed (So it does all the
legwork) so I don't know if its an alter table command or you have to go
the drop/create route.  On top of that, my databases typically only contain
about a meg or two of textual data, so, how quick my F2 would work on a
larger dataset, I don't know.

SQLite does not hold a unique permanent "link" from one table to another.
So the act of renaming, and then re-provisioning the table will not change
your code or how the other tables view the new table.  Its concern is that
at the time of the SQL execution, the tables and fields exist as required.
In other words, if you do rename a table, every other table in the database
is oblivious to the change, meaning its not aware.



On Tue, May 22, 2018 at 3:34 PM, Igor Korot  wrote:

> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer 
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz 
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Simon Slavin]

Just to explain to everyone why these commands are harder than they appear at 
first, consider ALTER TABLE DROP COLUMN .

The problem is not in "deleting" the column of data .  All you have to do for 
that is to rename the column something that can't be typed, and remove any 
constraints built into the column definition.  It's easy.  If the programmer 
wants to save the space the data takes up they can do a VACUUM.

But before you do this, you need to make sure that dropping that column isn't 
going to mess anything up.  To do that you have to look for anything in the 
schema that uses that column name.  It might be part of a constraint on that 
table.   It might be part of a trigger or foreign key on any table.  In most 
DBMSes, this isn't too difficult.  Database schema are held in a structured 
manner.  The column concerned will have an ID number, and all you do is search 
for that ID.  But in SQLite, the schema is held only in text.  So you have to 
parse the schema, with all its blanking and comments, looking for that column.  
And handling all the places it might occur and ways it might be phrased is 
difficult.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Igor Korot]

Hi, Charles,

On Tue, May 22, 2018 at 2:30 PM, Charles Leifer  wrote:
> As a workaround, you can always rename the existing table, create the new
> table with desired attributes, and do a INSERT INTO ... SELECT FROM
> old_table. Then you can safely drop the old table.

But the table_name will be different.
Also the data in the old table might be referencing some other table.
So this process is not really very
straightforward...

Thank you.

>
> On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz  wrote:
>
>> > ALTER TABLE ADD COLUMN has existed for a long time.
>>
>> Yes, sorry, I mixed things up.
>>
>> The order of importance is imho:
>> 1. RENAME COLUMN (shouldn't be too hard)
>> 2. DROP COLUMN (should be a bit more comlicated but feasible)
>> 3. MODIFY COLUMN
>>
>> > What kind of MODIFY COLUMN changes do you have in mind?
>>
>> I understand this can be difficult as there are many possible operations
>> that might be incompatible with the data already stored in that column.
>> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE CASCADE
>> action of a foreign key column. Also adding/removing a foreign key would be
>> useful.
>>
>> Kind regards,
>> Thomas
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Charles Leifer]

As a workaround, you can always rename the existing table, create the new
table with desired attributes, and do a INSERT INTO ... SELECT FROM
old_table. Then you can safely drop the old table.

On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz  wrote:

> > ALTER TABLE ADD COLUMN has existed for a long time.
>
> Yes, sorry, I mixed things up.
>
> The order of importance is imho:
> 1. RENAME COLUMN (shouldn't be too hard)
> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> 3. MODIFY COLUMN
>
> > What kind of MODIFY COLUMN changes do you have in mind?
>
> I understand this can be difficult as there are many possible operations
> that might be incompatible with the data already stored in that column.
> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE CASCADE
> action of a foreign key column. Also adding/removing a foreign key would be
> useful.
>
> Kind regards,
> Thomas
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Thomas Kurz]

> ALTER TABLE ADD COLUMN has existed for a long time.

Yes, sorry, I mixed things up.

The order of importance is imho:
1. RENAME COLUMN (shouldn't be too hard)
2. DROP COLUMN (should be a bit more comlicated but feasible)
3. MODIFY COLUMN

> What kind of MODIFY COLUMN changes do you have in mind?

I understand this can be difficult as there are many possible operations that 
might be incompatible with the data already stored in that column. Last time I 
needed MODIFY, I wanted to change the ON UPDATE/DELETE CASCADE action of a 
foreign key column. Also adding/removing a foreign key would be useful.

Kind regards,
Thomas

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Richard Hipp]

On 5/22/18, Thomas Kurz  wrote:
> I'd like to ask whether there is hope for a more complete support of ALTER
> TABLE in the near future, i.e. ADD COLUMN, MODIFY COLUMN, RENAME COLUMN and
> DROP COLUMN.

ALTER TABLE ADD COLUMN has existed for a long time.

What kind of MODIFY COLUMN changes do you have in mind?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table and .schema output

[R Smith]

On 2016/05/30 12:39 PM, Luca Ferrari wrote:

On Mon, May 30, 2016 at 12:24 PM, Clemens Ladisch  wrote:

This is normal.  The ALTER TABLE adds the new column(s) immediately
behind the actual column definition.  Inserting a comma before the
comment and the rest of the new column definition in the next line would
be too complex; and in any case the DB cannot know whether the comment
belongs to the column or the table.

Thanks, I was suspecting it.
Is there a "correct" way to annotate SQL schema? Other databases
provides special commands (e.g., PostgreSQL ADD COMMENT), but I don't
see nothing in SQLIte3 syntax except the SQL '--' one.


There is no more-correct way in SQL terms, but there is an option that 
works for most of us - simply use /* ... */ style commenting and add it 
before the comma, like so:

CREATE TABLE xxx(
...
  Col3 INT NOT NULL /* Comment here */,
  Col4 TEXT /* Comment two */ NOT NULL,
...etc.
);

Some DB managers even read these as metadata, such as this documentation 
example produced by SQLitespeed. You can see the schema SQL that 
prompted the detail in there, and if you look at the Numbers table, you 
can see the same effect achieved for other objects, like triggers and 
Indices:

http://www.sqlc.rifin.co.za/SchemaDocExample2.html

or if you prefer a more contrasting version:
http://www.sqlc.rifin.co.za/SchemaDocExample1.html

Hope that helps!
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table and .schema output

[Clemens Ladisch]

Luca Ferrari wrote:
> Is there a "correct" way to annotate SQL schema? Other databases
> provides special commands (e.g., PostgreSQL ADD COMMENT), but I don't
> see nothing in SQLIte3 syntax except the SQL '--' one.

If those annotations are to be queried, put them into a table.

Otherwise, if you want it to look good even after an ALTER TABLE, put
the comment before the comma:

CREATE TABLE ... (
...
Col /* hi! */,
...
);

To ensure that there is a comma after the last column, always use
a table constraint (e.g., PRIMARY KEY, or a dummy CHECK(1)).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table and .schema output

[Luca Ferrari]

On Mon, May 30, 2016 at 12:24 PM, Clemens Ladisch  wrote:
> This is normal.  The ALTER TABLE adds the new column(s) immediately
> behind the actual column definition.  Inserting a comma before the
> comment and the rest of the new column definition in the next line would
> be too complex; and in any case the DB cannot know whether the comment
> belongs to the column or the table.

Thanks, I was suspecting it.
Is there a "correct" way to annotate SQL schema? Other databases
provides special commands (e.g., PostgreSQL ADD COMMENT), but I don't
see nothing in SQLIte3 syntax except the SQL '--' one.

Thanks,
Luca
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table and .schema output

[Clemens Ladisch]

Luca Ferrari wrote:
> CREATE TABLE pratica_protocollo(
> ...
> note varchar( 2048 ) -- note per l'integrazione
> );
>
> ALTER TABLE pratica_protocollo ADD COLUMN  cage_attribuzione_anno
>integer;
> ALTER TABLE pratica_protocollo ADD COLUMN  cage_attribuzione_numero
>integer;
>
> and the .schema on the table provides now:
>
> CREATE TABLE pratica_protocollo(
> ...
>
> note varchar( 2048 ), cage_attribuzione_anno integer,
> cage_attribuzione_numero   integer, -- note per l'integrazione
> )
>
> As you can see, the columns have been added before the SQL comment on
> the last column of the table.

This is normal.  The ALTER TABLE adds the new column(s) immediately
behind the actual column definition.  Inserting a comma before the
comment and the rest of the new column definition in the next line would
be too complex; and in any case the DB cannot know whether the comment
belongs to the column or the table.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE .. ADD COLUMN .. IF NOT EXISTS ..?

[Nico Williams]

On Tue, Dec 16, 2014 at 11:40:22PM +, Simon Slavin wrote:
> If 'ALTER TABLE ... ADD COLUMN ...' fails it fails harmlessly, with

But it doesn't fail so harmlessly:

$ sqlite3 db 'alter table toy add column foo text; select 5;' || echo fail
SQL Error: duplicate column name: foo
fail
$ 

Note that:

 - the second statement was not executed
 - the noise on stderr
 - the non-zero exit status for the sqlite3 shell (which means that one
   of sqlite3_prepare_v2() or sqlite3_step() failed)

Yes, I can work around this.

Compare to a CREATE TABLE .. IF NOT EXISTS, which is silent and does not
cause the shell to exit with a non-zero exit status (because it doesn't
cause the sqlite3_exec() nor sqlite3_step() to return an error), and
does not stop evaluation of remaining input to the shell.

Ideally I could just have schema SQL in a file, doing DROP .. IF EXISTS
for some schema elements, CREATE .. IF NOT EXISTS for all of them, and
ALTER TABLE .. IF NOT EXISTS to upgrade schemas by just evaluating this
one file.

Executing a schema setup/upgrade file this via the shell is extremely
convenient.

(I do that all the time, but not with ALTER TABLE.)

> its work already having been done.  The ALTER command came along a
> long time after original SQL.  By that time software could handle
> cases where a single SQL command failed without the software having to
> crash at that point.

Yes, there are workarounds, I'm well aware.

> I would value far more the ability to do
> 
> ALTER TABLE ... DROP COLUMN ...
> 
> in SQLite, difficult though it would be to implement in SQLite3.

I would like this too, yes.

It wouldn't be that difficult: all that's needed is to arrange for the
dropped column to remain on-disk but otherwise be ignored (hidden, but
really well hidden), but still be added (with null value) for INSERTs
and UPDATEs.  For SQLite3 that would mean something like extending the
sqlite_master table to list the on-disk columns, with dropped columns
marked-up as such.  One would have to vaccuum to have them truly
disappear.

(For extra credit fail if triggers/FKs retain dangling references to the
dropped column, and even better, defer this check to commit time, since
subsequent statements might remediate this.)

Some things are easier than others.  ALTER .. IF NOT EXISTS surely would
be easier to add than DROP COLUMN.  Whether that's enough to recommend
it is a different story; I leave it to the SQLite3 team to decide that.

Even better, I'd like a normalized form of the schema stored in sqlite_*
tables, so that I could create/alter/drop schema elements with normal
CREATE/UPDATE/DELETE statements with WHERE clauses (so that I could
express conditional schema changes in SQL).  It'd be better than any
pragmas like table_info(table_name).  Much of the schema manipulation
statement logic could later be re-implemented by mapping those to DMLs
and then executing them, with many constraints (e.g., new columns must
allow NULL or otherwise have a default value, ...) implemented as
triggers.

Today I'm just asking for IF NOT EXISTS.  If it's not adopted, no big
deal.  I think it has a couple of things to recommend it (utility,
relative ease of implementation), but I'm not paying for it.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE .. ADD COLUMN .. IF NOT EXISTS ..?

[Simon Slavin]

On 16 Dec 2014, at 10:40pm, Nico Williams  wrote:

> I have a habit of putting schema definitions in a file that's always
> safe to read and execute against a DB connection.  This means that I
> DROP some things IF EXISTS and CREATE all things IF NOT EXISTS.
> 
> But if I have to ALTER TABLE... there's no IF NOT EXISTS .. equivalent
> for ALTER TABLE.
> 
> Funny that, or that I only just noticed this absence.
> 
> Looking at other SQL databases I see that this is actually a common
> question/request, and it seems that where this is implemented it looks
> like this:
> 
>  ALTER TABLE [IF EXISTS]  ADD COLUMN  [IF NOT EXISTS] ..;

If 'ALTER TABLE ... ADD COLUMN ...' fails it fails harmlessly, with its work 
already having been done.  The ALTER command came along a long time after 
original SQL.  By that time software could handle cases where a single SQL 
command failed without the software having to crash at that point.

In other words a programmer could execute the ALTER command, and if if failed 
carry on regardless, or use that failure to skip over more code which set up 
initial values in the new column.

I would value far more the ability to do

ALTER TABLE ... DROP COLUMN ...

in SQLite, difficult though it would be to implement in SQLite3.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE ... RENAME and indices.

[Richard Hipp]

On Mon, Aug 12, 2013 at 4:34 PM, Scott Hess  wrote:

> I had been attempting to write some code like this:
>
> CREATE TABLE t (x);
> CREATE INDEX t_x ON t(x);
> -- bunch of operations over a long period.
> -- now we want to run an expiration pass:
> BEGIN;
> ALTER TABLE t RENAME TO t_old;
> CREATE TABLE t (x);
> CREATE INDEX t_x ON t(x);  -- (*)
> -- Bunch of code like:
> INSERT INTO t (x) SELECT x FROM t_old WHERE (complicated);
> DROP TABLE t_old;
> COMMIT;
>
> Unfortunately, at (*) this throws:
>   Error: index t_x already exists
>
> Is there something really obvious I'm missing, here?
>

Does the index really have to be named "t_x".  Can you make up some
nonsense name containing a lot of randomness - a name that is very unlikely
to exist in the current schema?  Like say:
"t_x_b27289506cc846621a648dc9e95bba85".

SQLite doesn't care what the name of the index is, unless you use the
"INDEXED BY" clause on a query.  Are you doing that?


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Alter table constraint question

[Peter Aronson]

As pointed out, there are products out there that will add or drop 
constraints (by doing all the tedious table creation/rename/drop under 
the covers) for SQLite.  The other approach is to do what SpatiaLite 
does in general -- use triggers instead of check constraints but for the 
same purpose (see the triggers created by AddGreometryColumn()).  SQLite 
can drop and add triggers, and unlike check constraints, they can't be 
turned off by a pragma.  They may be less efficient, though (it would be 
an interesting thing to test).


Peter

On 9/9/2012 2:19 AM, Andrea Peri wrote:

Hi,

I'm an user of sqlite with the spatial extension "spatialite".

I see the sqlite db allow to define a constraints when are in the creating
phase of the table.
But is not possible to add or drop a constraint after the table is created.

In the GIS data the availability of the constraints is a really useful
think,
but if not so smart to have they locked to the creation phase.

So it is a continuous drop and recreate the table.

This is a really time consuming work.

So I like to understand if the not availability of the
both

Alter table add constraint ... foreign key (..)
Alter table drop constraint ...

is a technology choose.
and how much complex could be to add both to the sqlite sql.

Thx,

Andrea




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Alter table constraint question

[Peter Haworth]

Hi Andrea,
There are several commercial products that will provide this capability
along with many other schema maintenance functions not available in sqlite
itself.  I have one such available, SQLiteAdmin, at www.lcsql.com.
Pete
lcSQL Software 



On Sun, Sep 9, 2012 at 9:00 AM,  wrote:

> Message: 9
> Date: Sun, 9 Sep 2012 11:19:21 +0200
> From: Andrea Peri 
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Alter table constraint question
> Message-ID:
> 

Re: [sqlite] Alter table: number size

[Richard Hipp]

On Tue, Feb 28, 2012 at 7:14 AM, Marco Turco wrote:

> Hi all,
>
> I need to alter a field from smallint(1) to smallint(2),
>

No you don't; not unless your application or the wrapper you are using are
reading the schema or datatypes separately.  SQLite itself makes no
distinction between smallint(1) and smallint(2).  Both operate exactly the
same.


>
> is there to do this using ALTER TABLE ?
>
>
>
> Thanks in advance
>
>
>
> Marco
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Roger Binns]

-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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Nico Williams]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Tim Streater]

On 06 Feb 2012 at 19:31, Ryan Johnson  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Ryan Johnson]

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  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Bill McCormick]

Nico Williams wrote, On 2/6/2012 12:44 PM:

On Mon, 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.

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Nico Williams]

On Mon, 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.

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Black, Michael (IS)]

Hmmmcould .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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Gerry Snyder [mesmerizer...@gmail.com]
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Gerry Snyder]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Bill McCormick]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Gerry Snyder]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Puneet Kishor]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table add column

[Simon Slavin]

On 31 May 2011, at 5:09pm, Fabio Spadaro wrote:

> Step 1: alter table pippo rename to fabio -> ok
> step 2: insert into fabio (field1) values ​​('1 ') -> ko
> OperationalError: no such table main.pippo

How does step 2 know the name 'pippo' ?  You don't seem to supply it in the 
command.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table add column

[Stephan Beal]

On Tue, May 31, 2011 at 6:09 PM, Fabio Spadaro wrote:

> To recap:
> Step 1: alter table pippo rename to fabio -> ok
> step 2: insert into fabio (field1) values ('1 ') -> ko
> OperationalError: no such table main.pippo
> Step 3: alter table add column fabio field2 integer null -> ok
> result:
>  empty table
> Question: Why does my insert referring to the old table?
>

Because you typed it that way? If the insert is part of a trigger (you
didn't mention a trigger, but it sounds like you're using one), see:

http://www.sqlite.org/lang_altertable.html

and read the 3rd paragraph. If it is not part of a trigger, then simply
correct the spelling in your program/SQL script.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table add column

[Fabio Spadaro]

Hi

2011/5/31 Stephan Beal 

> On Tue, May 31, 2011 at 5:11 PM, Fabio Spadaro  >wrote:
>
> > "Alter table add column" command drop data from table.
> > Can you keep the data or should I store the data before the alter and
> then
> > put
> > them in the table?
> >
>
> http://www.sqlite.org/lang_altertable.html
>
> says:
>
> "The execution time of the ALTER TABLE command is independent of the amount
> of data in the table. The ALTER TABLE command runs as quickly on a table
> with 10 million rows as it does on a table with 1 row."
>
> Implicit in that statement is that ALTER TABLE does not modify/delete any
> table data. If it did, the runtime would probably be O(N) or worse, not
> O(1)
> (as described above).
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
>  ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Ok I have been deceived by the anomaly that I found in my application. In
practice I have renamed the table (with alter table), I inserted a row and
then I inserted a new column (with alter table add column) and to my
surprise I saw the empty table. The problem is that my insert is not
successful and what you do not understand why try to make the insert taking the
old name of the table.

To recap:
Step 1: alter table pippo rename to fabio -> ok
step 2: insert into fabio (field1) values ​​('1 ') -> ko
 OperationalError: no such table main.pippo
Step 3: alter table add column fabio field2 integer null -> ok
result:
  empty table
Question: Why does my insert referring to the old table?

use python sqlite vers. 2.5.9


-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table add column

[Stephan Beal]

On Tue, May 31, 2011 at 5:11 PM, Fabio Spadaro wrote:

> "Alter table add column" command drop data from table.
> Can you keep the data or should I store the data before the alter and then
> put
> them in the table?
>

http://www.sqlite.org/lang_altertable.html

says:

"The execution time of the ALTER TABLE command is independent of the amount
of data in the table. The ALTER TABLE command runs as quickly on a table
with 10 million rows as it does on a table with 1 row."

Implicit in that statement is that ALTER TABLE does not modify/delete any
table data. If it did, the runtime would probably be O(N) or worse, not O(1)
(as described above).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] alter table add column

[Mr. Puneet Kishor]

On May 31, 2011, at 10:11 AM, Fabio Spadaro wrote:

> "Alter table add column" command drop data from table.
> Can you keep the data or should I store the data before the alter and then put
> them in the table?

ALTER TABLE ADD COLUMN does not drop data from the table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Scott Hess]

I'd love to do fts2_1, because it implies fts1_1, but, really, 2_1
implies that the data would be backward-compatible, and maybe there's
just a new feature exposed or something.

-scott


On 8/14/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:
>
> +1 for fts3 or fts2_1 :-)
>
> ---
> We're Hiring! Seeking a passionate developer to join our team building
> products. Position is in the Washington D.C. metro area. If interested
> contact [EMAIL PROTECTED]
>
> -Original Message-
> From: Scott Hess [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 14, 2007 8:22 PM
> To: [EMAIL PROTECTED]
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
>
> On 8/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > > to add one last bit, to upgrade older tables.
> > >
> > > Unfortunately, code of the form:
> > >
> > >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> > >
> > > is documented as not supported.
> > > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > > this means that there is no option to do a cheap schema upgrade to get
> > > the correct semantics.  Am I missing a trick?
> >
> > It appears that you can set
> >
> >PRAGMA writable_schema=ON;
> >
> > Then do a manual UPDATE of the sqlite_master table to insert
> > an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> > I tried it and it seems to work.  But it is dangerous.  If you
> > mess up, you corrupt the database file.
>
> Ooh, I think that tips me away from fixing fts2, because it's scary
> and Google Gears disables PRAGMA.
>
> At least Joe threw in a vote for just versioning things to fts3 -
> anyone want to vote against?
>
> -scott
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Samuel R. Neff]

+1 for fts3 or fts2_1 :-) 

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 14, 2007 8:22 PM
To: [EMAIL PROTECTED]
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

On 8/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > to add one last bit, to upgrade older tables.
> >
> > Unfortunately, code of the form:
> >
> >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> >
> > is documented as not supported.
> > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > this means that there is no option to do a cheap schema upgrade to get
> > the correct semantics.  Am I missing a trick?
>
> It appears that you can set
>
>PRAGMA writable_schema=ON;
>
> Then do a manual UPDATE of the sqlite_master table to insert
> an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> I tried it and it seems to work.  But it is dangerous.  If you
> mess up, you corrupt the database file.

Ooh, I think that tips me away from fixing fts2, because it's scary
and Google Gears disables PRAGMA.

At least Joe threw in a vote for just versioning things to fts3 -
anyone want to vote against?

-scott


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[drh]

"Scott Hess" <[EMAIL PROTECTED]> wrote:
> >
> > It appears that you can set
> >
> >PRAGMA writable_schema=ON;
> >
> > Then do a manual UPDATE of the sqlite_master table to insert
> > an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> > I tried it and it seems to work.  But it is dangerous.  If you
> > mess up, you corrupt the database file.
> 
> Ooh, I think that tips me away from fixing fts2, because it's scary
> and Google Gears disables PRAGMA.
> 
> At least Joe threw in a vote for just versioning things to fts3 -
> anyone want to vote against?
> 

+1 in favor of fts3.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Scott Hess]

On 8/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > to add one last bit, to upgrade older tables.
> >
> > Unfortunately, code of the form:
> >
> >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> >
> > is documented as not supported.
> > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > this means that there is no option to do a cheap schema upgrade to get
> > the correct semantics.  Am I missing a trick?
>
> It appears that you can set
>
>PRAGMA writable_schema=ON;
>
> Then do a manual UPDATE of the sqlite_master table to insert
> an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> I tried it and it seems to work.  But it is dangerous.  If you
> mess up, you corrupt the database file.

Ooh, I think that tips me away from fixing fts2, because it's scary
and Google Gears disables PRAGMA.

At least Joe threw in a vote for just versioning things to fts3 -
anyone want to vote against?

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> > to add one last bit, to upgrade older tables.
> > 
> > Unfortunately, code of the form:
> > 
> >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> > 
> > is documented as not supported.
> > http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> > this means that there is no option to do a cheap schema upgrade to get
> > the correct semantics.  Am I missing a trick?
> 
> It appears that you can set
> 
>PRAGMA writable_schema=ON;
> 
> Then do a manual UPDATE of the sqlite_master table to insert
> an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
> I tried it and it seems to work.  But it is dangerous.  If you
> mess up, you corrupt the database file.

As long as we're on the topic of writable_schema = ON hacks, it 
seems you can have many tables/indexes point to the same underlying 
pages of another table/index.

Of course it won't survive a VACUUM, and you'll have problems with
pragma integrity_check, but what the heck...

$ ./sqlite3 hack.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> CREATE TABLE abc(a, b, c);
sqlite> CREATE INDEX abc_i on abc(c, a);
sqlite> insert into abc values(4,5,6);
sqlite> insert into abc values(1,2,3);
sqlite> insert into abc values(9,8,7);
sqlite> pragma writable_schema=on;
sqlite> .header on
sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|abc|abc|2|CREATE TABLE abc(a, b, c)
index|abc_i|abc|3|CREATE INDEX abc_i on abc(c, a)

Create an "alias" table and index sharing the data of 
the other table via the same rootpage...

sqlite> insert into sqlite_master values('table','xyz','xyz',2,'CREATE TABLE 
xyz(x, y, z, id
INTEGER PRIMARY KEY)');
sqlite> insert into sqlite_master values('index','xyz_i','xyz',3,'CREATE INDEX 
xyz_i on xyz(z,
x)');
sqlite> select * from abc;
a|b|c
4|5|6
1|2|3
9|8|7
sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|abc|abc|2|CREATE TABLE abc(a, b, c)
index|abc_i|abc|3|CREATE INDEX abc_i on abc(c, a)
table|xyz|xyz|2|CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY)
index|xyz_i|xyz|3|CREATE INDEX xyz_i on xyz(z, x)
sqlite> .q

# is there another way to force a reload on the schema from 
# the sqlite3 shell?

$ ./sqlite3 hack.db
SQLite version 3.4.2
Enter ".help" for instructions

Notice the same data in the "aliased" table xyz...

sqlite> select * from xyz;
4|5|6|1
1|2|3|2
9|8|7|3
sqlite> select * from abc;
4|5|6
1|2|3
9|8|7
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE abc(a, b, c);
INSERT INTO "abc" VALUES(4,5,6);
INSERT INTO "abc" VALUES(1,2,3);
INSERT INTO "abc" VALUES(9,8,7);
CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY);
INSERT INTO "xyz" VALUES(4,5,6,1);
INSERT INTO "xyz" VALUES(1,2,3,2);
INSERT INTO "xyz" VALUES(9,8,7,3);
CREATE INDEX abc_i on abc(c, a);
CREATE INDEX xyz_i on xyz(z, x);
COMMIT;

Notice that changing one table affects the other, since they
are sharing underlying data...

sqlite> insert into abc values(2,3,4);
sqlite> delete from xyz where x=1;
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE abc(a, b, c);
INSERT INTO "abc" VALUES(4,5,6);
INSERT INTO "abc" VALUES(9,8,7);
INSERT INTO "abc" VALUES(2,3,4);
CREATE TABLE xyz(x, y, z, id INTEGER PRIMARY KEY);
INSERT INTO "xyz" VALUES(4,5,6,1);
INSERT INTO "xyz" VALUES(9,8,7,3);
INSERT INTO "xyz" VALUES(2,3,4,4);
CREATE INDEX abc_i on abc(c, a);
CREATE INDEX xyz_i on xyz(z, x);
COMMIT;
sqlite> select * from abc order by c, a;
2|3|4
4|5|6
9|8|7
sqlite> select * from xyz order by z, x;
2|3|4|4
4|5|6|1
9|8|7|3

Oh well, it was good while it lasted...

sqlite> pragma integrity_check;
*** in database main ***
List of tree roots: 2nd reference to page 3
List of tree roots: 2nd reference to page 2



   

Pinpoint customers who are looking for what you sell. 
http://searchmarketing.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Joe Wilson]

--- Scott Hess <[EMAIL PROTECTED]> wrote:
> This may mean that I'll need to branch fts2 to fts3 and deprecate
> fts1/2 as being not safe for use.  If the code is going to have to
> create new tables and populate them, then there's not a lot of gain
> versus just having the developer do that.

Is it a good thing to still call the upgraded module "fts2" if
its schema is not backwards compatible with older versions of 
sqlite/fts2?  This is similar in spirit to the sqlite 3.x file 
format change that was later reverted.

Just playing the devil's advocate - I don't use fts.



   

Looking for a deal? Find great prices on flights and hotels with Yahoo! 
FareChase.
http://farechase.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[drh]

"Scott Hess" <[EMAIL PROTECTED]> wrote:
> I was getting ready to checkin the rowid-versus-fts2 fix, and wanted
> to add one last bit, to upgrade older tables.
> 
> Unfortunately, code of the form:
> 
>ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY;
> 
> is documented as not supported.
> http://www.sqlite.org/lang_altertable.html .  As far as I can tell,
> this means that there is no option to do a cheap schema upgrade to get
> the correct semantics.  Am I missing a trick?
> 

It appears that you can set

   PRAGMA writable_schema=ON;

Then do a manual UPDATE of the sqlite_master table to insert
an "id INTEGER PRIMARY KEY" into the SQL for the table definition.
I tried it and it seems to work.  But it is dangerous.  If you
mess up, you corrupt the database file.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ALTER TABLE ADD COLUMN - strange behaviour....Mac OSX

[Nuno Lucas]

On 6/3/07, Mark Gilbert <[EMAIL PROTECTED]> wrote:

Anyone have *any* idea what is happening ?


I don't know nothing about MacOS, but you may want to check the result
of sqlite3_close. It's possible it's not closing the database [1].

Regards,
~Nuno Lucas

[1] http://www.sqlite.org/capi3ref.html#sqlite3_close


Cheers

mark


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ALTER table command

[Clark Christensen]

Assuming a schema like:

create table t1 (a,b);

Add another column, "c"

alter table t1 add column c;

 -Clark


- Original Message 
From: Anish Enos Mathew <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, June 19, 2006 12:42:46 AM
Subject: [sqlite] ALTER table command


Hi all,
Any body knows  how to use ADD [COLUMN] in alter table command? I want
to add a new field to my table. Can "modify " be used with alter command
as in SQL?



The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of the company. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused, directly or 
indirectly, by any virus transmitted in this email.

www.aztecsoft.com

Re: [sqlite] ALTER table command

[John Newby]

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

Hi, SQLite FAQ recommends creating temp tables and copying the data from the
original table into it then deleting the old table then recreating the old
table (with the desired new column) then copying the data back and deleting
the temp table.

On 19/06/06, Anish Enos Mathew <[EMAIL PROTECTED]> wrote:



Hi all,
Any body knows  how to use ADD [COLUMN] in alter table command? I want
to add a new field to my table. Can "modify " be used with alter command
as in SQL?



The information contained in, or attached to, this e-mail, contains
confidential information and is intended solely for the use of the
individual or entity to whom they are addressed and is subject to legal
privilege. If you have received this e-mail in error you should notify the
sender immediately by reply e-mail, delete the message from your system and
notify your system manager. Please do not copy it for any purpose, or
disclose its contents to any other person. The views or opinions presented
in this e-mail are solely those of the author and do not necessarily
represent those of the company. The recipient should check this e-mail and
any attachments for the presence of viruses. The company accepts no
liability for any damage caused, directly or indirectly, by any virus
transmitted in this email.

www.aztecsoft.com

Re: [sqlite] Alter table to add a variable named column

[Pam Greene]

The workaround would be to build the statement some other way
(sqlite3_mprintf(), for example) for each individual ALTER TABLE command.
At that point you may want to use sqlite3_exec() instead of
sqlite3_prepare(), depending on how you'll be using the statement.  You'll
also have to be more careful about SQL injection, if the variable column
name comes from any sort of user input.

- Pam

On 5/23/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


Kevin Piciulo wrote:
>  Can I add a column using a variable for the column name?  Below is
> the prepare statement, which is returning an error.
>
> sqlite3_prepare(m_dbDataBase, "ALTER TABLE users ADD COLUMN ?
> varchar;", -1, , NULL);
>
>  I'm pretty sure my syntax is correct which leads me to believe you
> cannot do this.  If that's the case is there some sort of work around?
>
Kevin,

You are correct, this is illegal. You can only use a parameter where an
"expression" is allowed in the SQL syntax. Parameters do not do string
substitution in the SQL. You can check if your SQL still makes sense by
substituting a simple sum expression for your parameter. In your case,
the following does not make sense.

  ALTER TABLE users ADD COLUMN 5+2 varchar;

HTH
Dennis Cote

Re: [sqlite] Alter table to add a variable named column

[Dennis Cote]

Kevin Piciulo wrote:
 Can I add a column using a variable for the column name?  Below is 
the prepare statement, which is returning an error.


sqlite3_prepare(m_dbDataBase, "ALTER TABLE users ADD COLUMN ? 
varchar;", -1, , NULL);


 I'm pretty sure my syntax is correct which leads me to believe you 
cannot do this.  If that's the case is there some sort of work around?



Kevin,

You are correct, this is illegal. You can only use a parameter where an 
"expression" is allowed in the SQL syntax. Parameters do not do string 
substitution in the SQL. You can check if your SQL still makes sense by 
substituting a simple sum expression for your parameter. In your case, 
the following does not make sense.


 ALTER TABLE users ADD COLUMN 5+2 varchar;

HTH
Dennis Cote

Re: [sqlite] alter table syntax ?

[Kurt Welgehausen]

"Doug Fajardo" <[EMAIL PROTECTED]> wrote:

> Help!
> I keep getting a syntax error from the 'alter table' sql command, when
> used to add a column to a table. Can someone help with this error? Below
> is an example of one attempt, and its results:
>
> [tuna]$ sqlite test2.db
> SQLite version 2.8.16
> Enter ".help" for instructions
> sqlite> create table x1 ( name );
> sqlite> alter table x1 add column ( phone );
> SQL error: near "alter": syntax error
> sqlite>

There's no  statement in Sqlite v2.x;
switch to v3 if you need it.

Regards

Re: [sqlite] ALTER TABLE: Confusing documentation

[Tito Ciuro]

On 27/07/2005, at 13:04, Kurt Welgehausen wrote:


The docs are correct; you just have to read carefully.


I have :-)


They say that you can "rename, or add a new column to,
an existing table".


No, it doesn't.

It states that you can "rename or add a new column to an existing  
table."


Regards,

-- Tito

Re: [sqlite] ALTER TABLE: Confusing documentation

[Kurt Welgehausen]

The docs are correct; you just have to read carefully.
They say that you can "rename, or add a new column to,
an existing table".

Regards

Re: [sqlite] alter table rename column

[Will Leshner]

On May 7, 2005, at 3:14 PM, Kurt Welgehausen wrote:
  SQLite's version of the ALTER TABLE command allows the user to
  rename, or add a new column to, an existing table.

Aha. Ok. I get it now :)

Re: [sqlite] ALTER TABLE ... ADD COLUMN crashes with too many columns

[D. Richard Hipp]

On Tue, 2005-03-29 at 03:13 -0800, Andy Lutomirski wrote:
> I can crash sqlite3 like this:
> 
> % cat test.sql
> create table a (id INTEGER PRIMARY KEY);
> alter table a add column f1 TEXT;
> alter table a add column f2 TEXT;
> alter table a add column f3 TEXT;
> alter table a add column f4 TEXT;
> alter table a add column f5 TEXT;
> alter table a add column f6 TEXT;
> alter table a add column f7 TEXT;
> alter table a add column f8 TEXT;
> alter table a add column f9 TEXT;
> alter table a add column f10 TEXT;
> alter table a add column f11 TEXT;
> alter table a add column f12 TEXT;
> alter table a add column f13 TEXT;
> % sqlite3 foo2.db < test.sql
> *** glibc detected *** double free or corruption (out): 
> 0x005136f0 ***
> Aborted
> 

Ticket #1183 has already been fixed.  Version 3.2.1 will be out
in a day or so.  Or you can grab the latest from CVS.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] ALTER TABLE substitute?

[Tito Ciuro]

On 6 ene 2005, at 18:01, aleks ponjavic wrote:
What I want to do is drop and add columns, couldn't find something 
appropriate with sqlite, atleast it doesn't seem as ALTER TABLE works, 
how can I do it instead?
Maybe it isn't possible?
Please check the archives. It's been discussed already:
http://www.mail-archive.com/sqlite-users@sqlite.org
-- Tito

RE: [sqlite] ALTER TABLE statement?

[Griggs, Donald]

Richard,

When you wrote, 
   "...as my boss feels that he can't easily alter the database with a GUI
tool"

Is there any chance you meant to type "withOUT a GUI tool" instead?

If so, and if you're on a PC, then the nice utility:
   SqliteExplorer  http://www.sqlite.org/contrib

has the ability to alter sqlite tables.   Just right-click on the table and
choose DESIGN TABLE.   It actually generates the SQL needed to effect your
design changes, but then you're only an EXECUTE button away from
implementing them.

Don't forget to refresh the schema using F5 to see the results.

Most folks, though, would be more than happy to keep their boss from
schema-ing behind their backs.


Donald Griggs
Desk: 803-735-7834

Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.



-Original Message-
From: Richard Boehme [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 28, 2004 1:52 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] ALTER TABLE statement?


Does anyone know if there are any plans for an ALTER TABLE statement? 
Not having it is a major issue in possibly adopting SQLite, as my boss 
feels that he can't easily alter the database with a GUI tool (the ones 
I've seen for SQLite don't handle it

Thanks.

Richard Boehme

Re: [sqlite] ALTER TABLE statement?

[Mateusz Łoskot]

User Richard Boehme wrote::
Does anyone know if there are any plans for an ALTER TABLE statement? 
Not having it is a major issue in possibly adopting SQLite, as my boss 
feels that he can't easily alter the database with a GUI tool (the ones 
I've seen for SQLite don't handle it
Read sqlite docs and its WIKI pages:
http://www.sqlite.org/cvstrac/tktview?tn=236,8
Q 13
http://www.sqlite.org/faq.html
Greets
--
Mateusz Łoskot, mateusz (at) loskot (dot) net
Registered Linux User #220771, Debian (Sarge)

Re: [sqlite] ALTER TABLE statement?

[Paolo Vernazza]

This sample is wrong.
You forgot indexes and triggers
Altering a table (if there are indexes or triggers associated to it) 
cannot be done using only SQL...

Perhaps you search these examples ???
-Inserire una nuova colonna nel database (esempio completo).
ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE
sqlite prova
#Crea una tabella
sqlite> create table t1(a text, b text);
#Inizia la transazione
sqlite> BEGIN TRANSACTION;
#Crea una tabella temporanea uguale all'altra tabella
sqlite> CREATE TEMPORARY TABLE t1_backup(a,b);
#Fa un copia ed incolla dalla prima tabella alla tabella temporanea
sqlite> INSERT INTO t1_backup SELECT a,b FROM t1;
#Cancella la prima tabella
sqlite> DROP TABLE t1;
#Crea la nuova tabella con la nuova colonna
sqlite> CREATE TABLE t1(a text,b text,c text);
#Fa una copia dalla tabella temporanea alla nuova tabella
sqlite> INSERT INTO t1 (a,b) select * FROM t1_backup;
#Cancella la tabella temporanea
sqlite> DROP TABLE t1_backup;
#Finisce la transazione.
sqlite> COMMIT;
#Controllo nomi colonne
sqlite> .schema
-Rinominare una colonna (esempio completo).
ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE
sqlite prova
#Crea una tabella
sqlite> create table t1(a text, b text);
#Inizia la transazione
sqlite> BEGIN TRANSACTION;
#Crea una tabella temporanea uguale all'altra tabella
sqlite> CREATE TEMPORARY TABLE t1_backup(a,b);
#Fa un copia ed incolla dalla prima tabella alla tabella temporanea
sqlite> INSERT INTO t1_backup SELECT a,b FROM t1;
#Cancella la prima tabella
sqlite> DROP TABLE t1;
#Crea la nuova tabella con il nuovo nome della colonna
sqlite> CREATE TABLE t1(a text,botte text);
#Fa una copia dalla tabella temporanea alla nuova tabella
sqlite> INSERT INTO t1 (a,botte) select * FROM t1_backup;
#Cancella la tabella temporanea
sqlite> DROP TABLE t1_backup;
#Finisce la transazione.
sqlite> COMMIT;
#Controllo nomi colonne
sqlite> .schema

Re: [sqlite] ALTER TABLE statement?

[Cristiano Macaluso]

Perhaps you search these examples ???
-Inserire una nuova colonna nel database (esempio completo).
ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE
sqlite prova
#Crea una tabella
sqlite> create table t1(a text, b text);
#Inizia la transazione
sqlite> BEGIN TRANSACTION;
#Crea una tabella temporanea uguale all'altra tabella
sqlite> CREATE TEMPORARY TABLE t1_backup(a,b);
#Fa un copia ed incolla dalla prima tabella alla tabella temporanea
sqlite> INSERT INTO t1_backup SELECT a,b FROM t1;
#Cancella la prima tabella
sqlite> DROP TABLE t1;
#Crea la nuova tabella con la nuova colonna
sqlite> CREATE TABLE t1(a text,b text,c text);
#Fa una copia dalla tabella temporanea alla nuova tabella
sqlite> INSERT INTO t1 (a,b) select * FROM t1_backup;
#Cancella la tabella temporanea
sqlite> DROP TABLE t1_backup;
#Finisce la transazione.
sqlite> COMMIT;
#Controllo nomi colonne
sqlite> .schema
-Rinominare una colonna (esempio completo).
ATTENZIONE FARE PRIMA UNA COPIA DEL FILE-DATABASE
sqlite prova
#Crea una tabella
sqlite> create table t1(a text, b text);
#Inizia la transazione
sqlite> BEGIN TRANSACTION;
#Crea una tabella temporanea uguale all'altra tabella
sqlite> CREATE TEMPORARY TABLE t1_backup(a,b);
#Fa un copia ed incolla dalla prima tabella alla tabella temporanea
sqlite> INSERT INTO t1_backup SELECT a,b FROM t1;
#Cancella la prima tabella
sqlite> DROP TABLE t1;
#Crea la nuova tabella con il nuovo nome della colonna
sqlite> CREATE TABLE t1(a text,botte text);
#Fa una copia dalla tabella temporanea alla nuova tabella
sqlite> INSERT INTO t1 (a,botte) select * FROM t1_backup;
#Cancella la tabella temporanea
sqlite> DROP TABLE t1_backup;
#Finisce la transazione.
sqlite> COMMIT;
#Controllo nomi colonne
sqlite> .schema

Re: [sqlite] Alter Table?!

[andr3a]

I've implemented this sintax with few options on my PHP Class, if you're
interested in syntax like this:
 ALTER TABLE tbl_name ADD column_name alter_specification [FIRST | AFTER
column_name]
or this:
 ALTER TABLE tbl_name DROP [COLUMN] col_name
tell me.

andr3a

- Original Message - 
From: "Danny Reinhold" <[EMAIL PROTECTED]>
> Hi!
>
> Some months ago (9th of May) Jim Lyon wrote:
>
> > PS: I'm working on adding ALTER TABLE as a background project
> I would like to know about the current status of this background
> project.
> Will you add this useful extension to the standard SQLite sources?
>
>   - Danny



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]