[sqlite] alter table

[Thomas Kurz]

I'd just like to kindly ask whether there are any new plans for a full ALTER 
TABLE support?

___
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

[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

[sqlite] ALTER TABLE ADD COLUMN

[x]

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

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

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

[Manuel Rigger]

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;

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

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

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

[Mark Johnson]

Based on ticket

https://www.sqlite.org/src/tktview?name=43ddc85a63

---
During work for spatialite, I have also run into this problem using version
3.25.3.

Today I updated to 3.26.0 and saw that changes were made that (possibly)
makes it easier to pinpoint the problem.

With 3.25.3 lookupName was always being called with pTriggerTab->aCol ==
NULL

-I-> lookupName -200b- looking_for[NEW.id_rowid]
pTriggerTab->zName[middle_earth_general]  *aCol[(nil)] nCol[0]
-I-> lookupName -250b- pTriggerTab->zName[middle_earth_general]  zTab[NEW]
op[116] aCol->zName[NULL] nCol[0]
-I-> lookupName -251b- pTriggerTab->zName[middle_earth_general]  zTab[NEW]
[op != 117] aCol->zName[NULL]
-I-> lookupName -299-  sqlite3SelectAddTypeInfo cnt[0] rc[1]  error[no such
column: NEW.id_rowid]
-I-> sqlite3SelectPrep -103-  sqlite3ResolveSelectNames rc[1]  error[no
such column: NEW.id_rowid]

With 3.26.0 lookupName is being called 2 times, first with NULL and
then again pTriggerTab->aCol
!= NULL.

-I-> lookupName -200b- looking_for[(null).id_rowid] pParse->eTriggerOp[116]
pTriggerTab->zName[middle_earth_general]  *aCol[(nil)] nCol[-1]
-I-> lookupName -200a- looking_for[NEW.id_rowid] pParse->eTriggerOp[116]
pTriggerTab->zName[middle_earth_general]  aCol->zName[id_rowid] nCol[17]

However, the column count is not correct.
In my case 2 columns are missing: which should be 19.

-I-> lookupName -201a- looking_for[NEW.id_belongs_to] op[116
!=TK_DELETE[117]] pTriggerTab->zName[middle_earth_general]
 aCol->zName[id_rowid] nCol[17]
-I-> lookupName -202a loop - iCol[0]
pCol->zName[id_belongs_to]==zCol[id_rowid]
-I-> lookupName -202a loop - iCol[1]
pCol->zName[id_belongs_to]==zCol[id_admin]
-I-> lookupName -202a loop - iCol[2] pCol->zName[id_belongs_to]==zCol[name]
-I-> lookupName -202a loop - iCol[3]
pCol->zName[id_belongs_to]==zCol[admin_type]
* missing id_belongs_to
-I-> lookupName -202a loop - iCol[4]
pCol->zName[id_belongs_to]==zCol[belongs_to_01]
* missing   id_belongs_to_02
-I-> lookupName -202a loop - iCol[5]
pCol->zName[id_belongs_to]==zCol[belongs_to_02]
-I-> lookupName -202a loop - iCol[6]
pCol->zName[id_belongs_to]==zCol[valid_since]
-I-> lookupName -202a loop - iCol[7]
pCol->zName[id_belongs_to]==zCol[valid_until]
-I-> lookupName -202a loop - iCol[8]
pCol->zName[id_belongs_to]==zCol[map_x]
-I-> lookupName -202a loop - iCol[9]
pCol->zName[id_belongs_to]==zCol[map_y]
-I-> lookupName -202a loop - iCol[10]
pCol->zName[id_belongs_to]==zCol[srid]
-I-> lookupName -202a loop - iCol[11]
pCol->zName[id_belongs_to]==zCol[order_selected]
-I-> lookupName -202a loop - iCol[12]
pCol->zName[id_belongs_to]==zCol[rule_type]
-I-> lookupName -202a loop - iCol[13]
pCol->zName[id_belongs_to]==zCol[rule_text]
-I-> lookupName -202a loop - iCol[14]
pCol->zName[id_belongs_to]==zCol[notes]
-I-> lookupName -202a loop - iCol[15]
pCol->zName[id_belongs_to]==zCol[text]
-I-> lookupName -202a loop - iCol[16]
pCol->zName[id_belongs_to]==zCol[eur_point]

resulting in the error: error in trigger vw_ins_middle_earth_general: no
such column: NEW.id_belongs_to.

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

[sqlite] ALTER TABLE, modifying columns

[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

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

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

[Thierry Henrio]

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

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

[sqlite] ALTER TABLE

[Thomas Kurz]

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.

I know about the workaround (alter table rename to, insert, drop table), but 
this is very inconvenient.

___
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

[sqlite] alter table and .schema output

[Luca Ferrari]

Hi all,
I've a doubt about the SQL that .schema provides regarding a single table.
I've a table that has been created (and reported back by .schema) as follows:

CREATE TABLE pratica_protocollo(
...
note varchar( 2048 ) -- note per l'integrazione
);


The I ran the following:

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.
While this does not have any implicit or functional problem, I suspect
it could avoid some tools to work on "annotated" schemas.
Am I missing something on the ALTER TABLE side or this behavior is normal?
I'm running 3.8.11.1.

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

[sqlite] ALTER TABLE ADD COLUMN

[Christoph P.U. Kukulies]

Am 21.05.2015 um 13:44 schrieb Simon Slavin:
> On 21 May 2015, at 7:51am, Christoph P.U. Kukulies  
> wrote:
>
>> Now I wonder why I don't read
>> CREATE TABLE [database] (
>> [database_name] TEXT  NULL,
>> [table_name] TEXT  NULL,
>> [data_type] TEXT  NULL,
>> [data_size] INTEGER  NULL,
>> [column_name] TEXT  NULL,
>> [vendor] TEXT DEFAULT 'SQLBASE' NULL,
>> [real_length] NUMERIC;
> The standard of having a column name in square brackets does not belong to 
> SQLite.  It is not something SQLite does, and it is not something SQLite 
> insists on.  Whoever created the table orignally used square brackets and 
> they worked, but that doesn't mean that everything else has to use square 
> brackets from then on.
>
> If you like things to use square brackets do it yourself, so do
>
> alter table database add column [real_length] numeric;
>
> Simon.
>
That's interesting. Thanks for claryfying.
--
Christoph

[sqlite] ALTER TABLE ADD COLUMN

[Kees Nuyt]

On Thu, 21 May 2015 12:45:41 +0200, "Christoph P.U. Kukulies"
 wrote:

>Am 21.05.2015 um 10:00 schrieb Kees Nuyt:
>> On Thu, 21 May 2015 09:40:53 +0200, "Christoph P.U. Kukulies"
>>  wrote:
>>
>>> Am 21.05.2015 um 09:25 schrieb Hick Gunter:
 alter table [database] add column [real_length] numeric;
>>> Thanks, that did it right. Almost. Still I'm wondering why
>>> the output is
>>> not as "smooth" as the
>>> .fullschema listing before the ALTER ADD COLUMN:
>>> (comma at line start,
>> Alter table just adds ",your-row-spec" in front of ");".
>> If you want it to be more consistent, use comma at line start
>> for all column specs in the original schema.
>>
>>> lower case 'numeric')
>> That can be fixed by using
>> alter table [database] add column [real_length] NUMERIC;
>>
>> You can patch the layout of the CREATE TABLE statement in
>> sqlite_master by using
>> http://my.domain/sqlite.org/pragma.html#pragma_writable_schema

I forgot to edit that link, should be
http://www.sqlite.org/pragma.html#pragma_writable_schema

>> and replacing the contents of the sql column with your preferred
>> layout.
>> It is dangerous, so read the warning, make backups first and
>> test the result thoroughly!
>
>
> Thanks. So you mean to use the pragma at run time in sqlite3, like:
> sqlite> PRAGMA writable_schema = true;
> sqlite>

Yes, that.

>or at compile time and recompile it?

No, no compile time options required, the default sqlite3
command line will do.

> To me it would probably far easier, to rebuild the schema
> from ground up in sqlite3.exe since I'm still in a design phase.

That's much better.
For most of my projects I maintain the schema source in my
project source tree, together with a few scripts with test data.
The database is automaticaly created from that schema when it is
missing (e.g. in  make clean; make). 

-- 
Regards, 

Kees Nuyt

[sqlite] ALTER TABLE ADD COLUMN

[Christoph P.U. Kukulies]

Am 21.05.2015 um 10:00 schrieb Kees Nuyt:
> On Thu, 21 May 2015 09:40:53 +0200, "Christoph P.U. Kukulies"
>  wrote:
>
>> Am 21.05.2015 um 09:25 schrieb Hick Gunter:
>>> alter table [database] add column [real_length] numeric;
>> Thanks, that did it right. Almost. Still I'm wondering why
>> the output is
>> not as "smooth" as the
>> .fullschema listing before the ALTER ADD COLUMN:
>> (comma at line start,
> Alter table just adds ",your-row-spec" in front of ");".
> If you want it to be more consistent, use comma at line start
> for all column specs in the original schema.
>
>> lower case 'numeric')
> That can be fixed by using
> alter table [database] add column [real_length] NUMERIC;
>
> You can patch the layout of the CREATE TABLE statement in
> sqlite_master by using
> http://knuyt.demon.nl/sqlite.org/pragma.html#pragma_writable_schema
> and replacing the contents of the sql column with your preferred
> layout.
> It is dangerous, so read the warning, make backups first and
> test the result thoroughly!


Thanks. So you mean to use the pragma at run time in sqlite3, like:
sqlite> PRAGMA writable_schema = true;
sqlite>

or at compile time and recompile it?

To me it would probably far easier, to rebuild the schema from ground up 
in sqlite3.exe
since I'm still in a design phase.
--
Christoph Kukulies
>

[sqlite] ALTER TABLE ADD COLUMN

[Simon Slavin]

On 21 May 2015, at 7:51am, Christoph P.U. Kukulies  wrote:

> Now I wonder why I don't read
> CREATE TABLE [database] (
> [database_name] TEXT  NULL,
> [table_name] TEXT  NULL,
> [data_type] TEXT  NULL,
> [data_size] INTEGER  NULL,
> [column_name] TEXT  NULL,
> [vendor] TEXT DEFAULT 'SQLBASE' NULL,
> [real_length] NUMERIC;

The standard of having a column name in square brackets does not belong to 
SQLite.  It is not something SQLite does, and it is not something SQLite 
insists on.  Whoever created the table orignally used square brackets and they 
worked, but that doesn't mean that everything else has to use square brackets 
from then on.

If you like things to use square brackets do it yourself, so do

alter table database add column [real_length] numeric;

Simon.

[sqlite] ALTER TABLE ADD COLUMN

[Christoph P.U. Kukulies]

Am 21.05.2015 um 09:08 schrieb Jean-Christophe Deschamps:
> At 08:51 21/05/2015, you wrote:
>
>> I used sqlite3.exe by invoking it on the command line with the name 
>> of a database.
>> Next I typed
>>
>>  alter table database add column real_length numeric;
>>
>> Next I typed .fullschema
>> and I'm getting:
>>
>> sqlite> .fullschema
>> CREATE TABLE [database] (
>> [database_name] TEXT  NULL,
>> [table_name] TEXT  NULL,
>> [data_type] TEXT  NULL,
>> [data_size] INTEGER  NULL,
>> [column_name] TEXT  NULL,
>> [vendor] TEXT DEFAULT 'SQLBASE' NULL
>> , real_length numeric);
>> /* No STAT tables available */
>>
>> Now I wonder why I don't read
>> CREATE TABLE [database] (
>> [database_name] TEXT  NULL,
>> [table_name] TEXT  NULL,
>> [data_type] TEXT  NULL,
>> [data_size] INTEGER  NULL,
>> [column_name] TEXT  NULL,
>> [vendor] TEXT DEFAULT 'SQLBASE' NULL,
>> [real_length] NUMERIC;
>   ^ missing )
Yes, this was my fault when hand editing (copy/paste) the message. 
Thanks for being exact.
--
Christoph Kukulies

[sqlite] ALTER TABLE ADD COLUMN

[Christoph P.U. Kukulies]

Am 21.05.2015 um 09:25 schrieb Hick Gunter:
> alter table [database] add column [real_length] numeric;
Thanks, that did it right. Almost. Still I'm wondering why the output is 
not as "smooth" as the
.fullschema listing before the ALTER ADD COLUMN: (comma at line start, 
lower case 'numeric')

sqlite> .fullschema
CREATE TABLE [database] (
[database_name] TEXT  NULL,
[table_name] TEXT  NULL,
[data_type] TEXT  NULL,
[data_size] INTEGER  NULL,
[column_name] TEXT  NULL,
[vendor] TEXT DEFAULT 'SQLBASE' NULL
);
/* No STAT tables available */
sqlite> alter table [database] add column [real_length] numeric;
sqlite> .fullschema
CREATE TABLE [database] (
[database_name] TEXT  NULL,
[table_name] TEXT  NULL,
[data_type] TEXT  NULL,
[data_size] INTEGER  NULL,
[column_name] TEXT  NULL,
[vendor] TEXT DEFAULT 'SQLBASE' NULL
, [real_length] numeric);
/* No STAT tables available */


--
Christoph Kukulies

[sqlite] ALTER TABLE ADD COLUMN

[Jean-Christophe Deschamps]

At 08:51 21/05/2015, you wrote:

>I used sqlite3.exe by invoking it on the command line with the name of 
>a database.
>Next I typed
>
>  alter table database add column real_length numeric;
>
>Next I typed .fullschema
>and I'm getting:
>
>sqlite> .fullschema
>CREATE TABLE [database] (
>[database_name] TEXT  NULL,
>[table_name] TEXT  NULL,
>[data_type] TEXT  NULL,
>[data_size] INTEGER  NULL,
>[column_name] TEXT  NULL,
>[vendor] TEXT DEFAULT 'SQLBASE' NULL
>, real_length numeric);
>/* No STAT tables available */
>
>Now I wonder why I don't read
>CREATE TABLE [database] (
>[database_name] TEXT  NULL,
>[table_name] TEXT  NULL,
>[data_type] TEXT  NULL,
>[data_size] INTEGER  NULL,
>[column_name] TEXT  NULL,
>[vendor] TEXT DEFAULT 'SQLBASE' NULL,
>[real_length] NUMERIC;
   ^ missing )

Probably because you used a third-party tool (like SQLite Expert) to 
create the table. Such tools often --and I know Expert does-- enclose 
schema names in a given pair of delimiters [...], "..." or `...` while 
the CLI simply issues the SQL command like you input it. 

[sqlite] ALTER TABLE ADD COLUMN

[Christoph P.U. Kukulies]

I used sqlite3.exe by invoking it on the command line with the name of a 
database.
Next I typed

  alter table database add column real_length numeric;

Next I typed .fullschema
and I'm getting:

sqlite> .fullschema
CREATE TABLE [database] (
[database_name] TEXT  NULL,
[table_name] TEXT  NULL,
[data_type] TEXT  NULL,
[data_size] INTEGER  NULL,
[column_name] TEXT  NULL,
[vendor] TEXT DEFAULT 'SQLBASE' NULL
, real_length numeric);
/* No STAT tables available */

Now I wonder why I don't read
CREATE TABLE [database] (
[database_name] TEXT  NULL,
[table_name] TEXT  NULL,
[data_type] TEXT  NULL,
[data_size] INTEGER  NULL,
[column_name] TEXT  NULL,
[vendor] TEXT DEFAULT 'SQLBASE' NULL,
[real_length] NUMERIC;

--
Christoph Kukulies

[sqlite] ALTER TABLE ADD COLUMN

[Hick Gunter]

Try

  alter table [database] add column [real_length] numeric;

-Urspr?ngliche Nachricht-
Von: Christoph P.U. Kukulies [mailto:kuku at kukulies.org]
Gesendet: Donnerstag, 21. Mai 2015 08:51
An: General Discussion of SQLite Database
Betreff: [sqlite] ALTER TABLE ADD COLUMN

I used sqlite3.exe by invoking it on the command line with the name of a 
database.
Next I typed

  alter table database add column real_length numeric;

Next I typed .fullschema
and I'm getting:

sqlite> .fullschema
CREATE TABLE [database] (
[database_name] TEXT  NULL,
[table_name] TEXT  NULL,
[data_type] TEXT  NULL,
[data_size] INTEGER  NULL,
[column_name] TEXT  NULL,
[vendor] TEXT DEFAULT 'SQLBASE' NULL
, real_length numeric);
/* No STAT tables available */

Now I wonder why I don't read
CREATE TABLE [database] (
[database_name] TEXT  NULL,
[table_name] TEXT  NULL,
[data_type] TEXT  NULL,
[data_size] INTEGER  NULL,
[column_name] TEXT  NULL,
[vendor] TEXT DEFAULT 'SQLBASE' NULL,
[real_length] NUMERIC;

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

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

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

[Nico Williams]

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

Nico
-- 
___
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 <http://www.lcsql.com>



On Sun, Sep 9, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

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

[sqlite] Alter table constraint question

[Andrea Peri]

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


-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Alter table: number size

[Marco Turco]

Hi all,

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

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

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

[sqlite] ALTER TABLE

[Bill McCormick]

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


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


Thanks!!
___
sqlite-users mailing list
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

[sqlite] alter table add column

[Fabio Spadaro]

"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?

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

[sqlite] ALTER TABLE and INTEGER PRIMARY KEY.

[Scott Hess]

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?

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.

Thanks,
scott

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

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

[Mark Gilbert]

Folks.

I had been struggling to implement ALTER TABLE ADD COLUMN in my app 
to change the database structure. However I would continuously face a 
locked database error.


After much experimentation I have come to a very very strange conclusion.

In my app if I do this:

-start app
-open database
-read version table
-close database
-CHECK VERSION, then if needed
-open database
-ALTER TABLE ADD COLUMN
-close database

it works FINE>

However if I do this:

-start app
-open database
-read version table
-close database
-CHECK VERSION, then if needed
*** WARN USER WITH ALERT ***   iErr = Alert(alertID, 0);
-open database
-ALTER TABLE ADD COLUMN
-close database

The ALTER TABLE ADD COLUMN stage FAILS with Database is locked.

The only thing I can think is that the Alert function is accessing 
the Application's resource fork, which involves the OS opening the 
resource fork.  Note that the SQLite database is NOT open at the 
time.  it subsequently opens OK, but the ALTER TABLE ADD COLUMN fails.


Anyone have *any* idea what is happening ?

Cheers

mark
--
[EMAIL PROTECTED]
Tel: +44 208 340 5677
fax: +44 870 055 7790
http://www.gallery.co.uk


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

[sqlite] ALTER TABLE

[BardzoTajneKonto]

Is there a reason why ALTER TABLE ADD can add only one column?
I'v changed the parser to allow any number of columns - I'm calling
sqlite3AlterFinishAddColumn() for every column. It seems to work.
Am I missing some problem, or nobody wanted more columns before?

Wiktor Adamski

--
Wkrec znajomych :)>>> http://link.interia.pl/f1a5c 



-
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

[sqlite] ALTER table command

[Anish Enos Mathew]

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

[sqlite] Alter table to add a variable named column

[Kevin Piciulo]

 I asked a similar question to this about accessing columns using a 
variable name, and the answer was you cannot.  Sadly I cannot find the 
email explaining why so I'll ask this similar question:


 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?


 Thanks in advance for any help.
  Kevin Piciulo

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

[sqlite] alter table syntax ?

[Doug Fajardo]

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>

[sqlite] ALTER TABLE Command restrictions

[Robert Foster]

Hi,
I was just playing with ALTER TABLE, and it appears that you can only add 1
column with each command.  Is this correct?  If so, how hard would it be to
enable adding multiple columns with the same command?

Thanks,

Robert Foster
General Manager
Mountain Visions P/L  http://mountainvisions.com.au

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 :)