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

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

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

[D. Richard Hipp]

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

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

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

[Andy Lutomirski]

I can crash sqlite3 like this:
% cat test.sql
create table a (id INTEGER PRIMARY KEY);
alter table a add column f1 TEXT;
alter table a add column f2 TEXT;
alter table a add column f3 TEXT;
alter table a add column f4 TEXT;
alter table a add column f5 TEXT;
alter table a add column f6 TEXT;
alter table a add column f7 TEXT;
alter table a add column f8 TEXT;
alter table a add column f9 TEXT;
alter table a add column f10 TEXT;
alter table a add column f11 TEXT;
alter table a add column f12 TEXT;
alter table a add column f13 TEXT;
% sqlite3 foo2.db < test.sql
*** glibc detected *** double free or corruption (out): 
0x005136f0 ***
Aborted

The backtrace is:
#0  0x003aa212ec49 in raise () from /lib/tls/libc.so.6
#1  0x003aa213014e in abort () from /lib/tls/libc.so.6
#2  0x003aa2167b84 in malloc_usable_size () from /lib/tls/libc.so.6
#3  0x003aa2168646 in free () from /lib/tls/libc.so.6
#4  0x2ab0c55e in sqlite3FreeX (p=0x5146f0) at ./src/util.c:287
#5  0x2aae3097 in sqliteResetColumnNames (pTable=0x512750) at 
./src/build.c:397
#6  0x2aae3174 in sqlite3DeleteTable (db=0x50b580, 
pTable=0x512750) at ./src/build.c:450
#7  0x2ab097ab in sqlite3RunParser (pParse=0x7fffdaa0, 
zSql=0x512720 "alter table a add column f9 TEXT;",
pzErrMsg=0x7fffda98) at ./src/tokenize.c:425
#8  0x2aaf508e in sqlite3_prepare (db=0x50b580, zSql=0x512720 
"alter table a add column f9 TEXT;", nBytes=-1,
ppStmt=0x7fffdc20, pzTail=0x7fffdc28) at ./src/main.c:1056
#9  0x2ab1e0c6 in sqlite3_exec (db=0x50b580, zSql=0x512720 
"alter table a add column f9 TEXT;",
xCallback=0x402311 , pArg=0x7fffdd10, 
pzErrMsg=0x7fffdc90) at ./src/legacy.c:56
#10 0x00405b16 in process_input (p=0x7fffdd10, 
in=0x3aa231fb60) at ./src/shell.c:1503
#11 0x0040657f in main (argc=2, argv=0x7328) at 
./src/shell.c:1795

This is sqlite 3.2.0, running on Gentoo amd64.
Should I submit a bug report?
Thanks,
Andy