Re: [sqlite] ALTER TABLE ADD COLUMN
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
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
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
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 wont 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
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
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
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
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
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
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! -- Regards, Kees Nuyt
[sqlite] ALTER TABLE ADD COLUMN
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
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
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
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
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 ..?
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 ..?
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 ..?
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
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
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
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
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
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
"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
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
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
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
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