Re: [sqlite] What is the easiest way of changing the field type?
How about trying to write .output to the same file you are .read ing? I would expect it to explode. Further, I would expect it quite likely that it would explode without being able to tell you about the explosion. The sequence should be: .output somefile .dump sometable .output stdout ... carry on swimmingly --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Monday, 03 December, 2012 13:42 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] What is the easiest way of changing the field type? > > > On 3 Dec 2012, at 7:29pm, Igor Korot wrote: > > > I didn't get errors when I did that with my table. Other than that its all > > the same. > > You didn't get any time shown when the .read command finished. This suggests > that something crashed the .read command. Anything done after that can > malfunction. > > But I don't understand what would have crashed the .read command and you now > need someone who knows more than I do about how that program is written. > > Simon. > ___ > 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] What is the easiest way of changing the field type?
On 3 December 2012 19:29, Igor Korot wrote: > Simon, > > On Mon, Dec 3, 2012 at 11:03 AM, Simon Slavin wrote: >> >> On 3 Dec 2012, at 6:53pm, Igor Korot wrote: >> > > Here is the session: > > sqlite> CREATE TABLE test(id1 integer, id2 double); > sqlite> INSERT INTO test( 1, 1.0 ); > Error: near "1": syntax error > sqlite> INSERT INTO test VALUES( 1, 1.0 ); > sqlite> INSERT INTO test VALUES( 2, 2.0 ); > sqlite> INSERT INTO test VALUES( 3, 3.0 ); > sqlite> .timer ON > sqlite> SELECT * FROM test; > 1|1.0 > 2|2.0 > 3|3.0 > CPU Time: user 0.00 sys 0.00 > .output testdump.log > .dump test > sqlite> SELECT * FROM test; > CPU Time: user 0.00 sys 0.00 > sqlite> DROP TABLE test; > CPU Time: user 0.00 sys 0.00 > sqlite> .read testdump.log > > Error: incomplete SQL: 1|1.0 > 2|2.0 > 3|3.0 > sqlite> SELECT * FROM test; > CPU Time: user 0.00 sys 0.00 > sqlite> > > I didn't get errors when I did that with my table. Other than that its all > the same. > > > Thank you. > >> After .dump test, add .output stdout, and try again. Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
On 3 Dec 2012, at 7:29pm, Igor Korot wrote: > I didn't get errors when I did that with my table. Other than that its all > the same. You didn't get any time shown when the .read command finished. This suggests that something crashed the .read command. Anything done after that can malfunction. But I don't understand what would have crashed the .read command and you now need someone who knows more than I do about how that program is written. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
Simon, On Mon, Dec 3, 2012 at 11:03 AM, Simon Slavin wrote: > > On 3 Dec 2012, at 6:53pm, Igor Korot wrote: > >> Yes, using standard sqlite3 shell from command prompt in Windows 7 64-bit. >> After .dump/.read no queries to the table in question produced output. >> >> When restarted the shell everything worked. > > I've not come across that before. Try > > .timer ON > SELECT command > .dump command > SELECT command > .read command > SELECT command > > Do you get timings for all the commands including the last SELECT ? Here is the session: sqlite> CREATE TABLE test(id1 integer, id2 double); sqlite> INSERT INTO test( 1, 1.0 ); Error: near "1": syntax error sqlite> INSERT INTO test VALUES( 1, 1.0 ); sqlite> INSERT INTO test VALUES( 2, 2.0 ); sqlite> INSERT INTO test VALUES( 3, 3.0 ); sqlite> .timer ON sqlite> SELECT * FROM test; 1|1.0 2|2.0 3|3.0 CPU Time: user 0.00 sys 0.00 .output testdump.log .dump test sqlite> SELECT * FROM test; CPU Time: user 0.00 sys 0.00 sqlite> DROP TABLE test; CPU Time: user 0.00 sys 0.00 sqlite> .read testdump.log CPU Time: user 0.00 sys 0.00 CPU Time: user 0.00 sys 0.00 CPU Time: user 0.00 sys 0.015600 CPU Time: user 0.00 sys 0.00 CPU Time: user 0.00 sys 0.00 CPU Time: user 0.00 sys 0.00 CPU Time: user 0.00 sys 0.015600 Error: incomplete SQL: 1|1.0 2|2.0 3|3.0 sqlite> SELECT * FROM test; CPU Time: user 0.00 sys 0.00 sqlite> I didn't get errors when I did that with my table. Other than that its all the same. > > Might also be interesting to type yourself a short text file with just one > INSERT or DELETE command in and see whether that problem happens for all > files or is related to the kind of file produced by .dump . That's will be the next step. Lets' first analyze the output above. Thank you. > > Simon. > ___ > 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] What is the easiest way of changing the field type?
On 3 Dec 2012, at 6:53pm, Igor Korot wrote: > Yes, using standard sqlite3 shell from command prompt in Windows 7 64-bit. > After .dump/.read no queries to the table in question produced output. > > When restarted the shell everything worked. I've not come across that before. Try .timer ON SELECT command .dump command SELECT command .read command SELECT command Do you get timings for all the commands including the last SELECT ? Might also be interesting to type yourself a short text file with just one INSERT or DELETE command in and see whether that problem happens for all files or is related to the kind of file produced by .dump . Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
Simon, On Mon, Dec 3, 2012 at 9:39 AM, Simon Slavin wrote: > > On 3 Dec 2012, at 5:38pm, Igor Korot wrote: > >> Simon, >> Yes, thank you. > > Great. > >> ALL, >> After recreating the table this way I had to restart sqlite console as >> otherwise >> all queries to the table were dead. >> >> Is this normal? > > Hmm. No, if you're using the standard sqlite3 shell tool you should be able > to do .read and then carry on with the new data you read in. Yes, using standard sqlite3 shell from command prompt in Windows 7 64-bit. After .dump/.read no queries to the table in question produced output. When restarted the shell everything worked. SQLite version 3.7.14 2012-09-03 15:42:36 Enter ".help" for instructions Enter SQL statements terminated with a ";" Thank you. > > Simon. > ___ > 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] What is the easiest way of changing the field type?
On 3 Dec 2012, at 5:38pm, Igor Korot wrote: > Simon, > Yes, thank you. Great. > ALL, > After recreating the table this way I had to restart sqlite console as > otherwise > all queries to the table were dead. > > Is this normal? Hmm. No, if you're using the standard sqlite3 shell tool you should be able to do .read and then carry on with the new data you read in. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
Simon, Yes, thank you. ALL, After recreating the table this way I had to restart sqlite console as otherwise all queries to the table were dead. Is this normal? Thank you. On Mon, Dec 3, 2012 at 1:42 AM, Simon Slavin wrote: > > On 3 Dec 2012, at 1:08am, Igor Korot wrote: > >> I thought that simply using ".backup/.restore" will give me what I want, >> but it gives me a file in internal format. >> What I am trying to do is do make a text file which gives me all SQL command >> that was issued (CREATE TABLE and INSERT) than fix the particular CREATE >> TABLEu. > > Ah. You're using the wrong commands. You want .dump/.read. > > Simon. > ___ > 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] What is the easiest way of changing the field type?
On 3 Dec 2012, at 1:08am, Igor Korot wrote: > I thought that simply using ".backup/.restore" will give me what I want, > but it gives me a file in internal format. > What I am trying to do is do make a text file which gives me all SQL command > that was issued (CREATE TABLE and INSERT) than fix the particular CREATE > TABLEu. Ah. You're using the wrong commands. You want .dump/.read. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
Regarding: > > > > Note that you only need to .dump/.read the table(s) you want to alter. > > *Well, I don't think it's true. > 1. When I execute "CREATE TABLE" with the table name that already exists > wouldn't I get an error message? > 2. If I drop this table first I will get an error about the key constraint > as the table holds primary key for couple of tables. > * > > > I believe that simply turning off foreign key constraints will prevent the error messages. http://sqlite.org/pragma.html#pragma_foreign_keys But perhaps there's a foreign key relationship with a rowid that requires you to re-create the entire database to ensure integrity. If not, maybe the pragma is all you need. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
Donald, On Sun, Dec 2, 2012 at 7:22 PM, Donald Griggs wrote: > Regarding: > > *How do I do that? > I thought that simply using ".backup/.restore" will give me what I want, > but it gives me a file in internal format. > What I am trying to do is do make a text file which gives me all SQL command > that was issued (CREATE TABLE and INSERT) than fix the particular CREATE > TABLEu. > > and then load this file back into the db. > All this will be done in the console (command prompt) window. > > Do I need to use ".dump/.load" commands for that?* > > It does seem the ".dump" command in the sqlite command shell program does > just what you're asking. The ".load" command, though, loads program > extensions -- the ".read" command is the proper inverse of the .dump. OK. > > Note that you only need to .dump/.read the table(s) you want to alter. Well, I don't think it's true. 1. When I execute "CREATE TABLE" with the table name that already exists wouldn't I get an error message? 2. If I drop this table first I will get an error about the key constraint as the table holds primary key for couple of tables. Am I right? Thank you. > > http://sqlite.org/sqlite.html > ___ > 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] What is the easiest way of changing the field type?
Regarding: *How do I do that? I thought that simply using ".backup/.restore" will give me what I want, but it gives me a file in internal format. What I am trying to do is do make a text file which gives me all SQL command that was issued (CREATE TABLE and INSERT) than fix the particular CREATE TABLEu. and then load this file back into the db. All this will be done in the console (command prompt) window. Do I need to use ".dump/.load" commands for that?* It does seem the ".dump" command in the sqlite command shell program does just what you're asking. The ".load" command, though, loads program extensions -- the ".read" command is the proper inverse of the .dump. Note that you only need to .dump/.read the table(s) you want to alter. http://sqlite.org/sqlite.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
Jay, On Sun, Dec 2, 2012 at 1:31 PM, Jay A. Kreibich wrote: > On Sun, Dec 02, 2012 at 12:52:33PM -0800, Igor Korot scratched on the wall: >> Jay, >> >> On Sun, Dec 2, 2012 at 12:16 PM, Jay A. Kreibich wrote: >> > On Sun, Dec 02, 2012 at 11:58:54AM -0800, Igor Korot scratched on the wall: >> >> Hi, ALL, >> >> ALTER TABLE command does not support changing the field type. >> >> >> >> What is the easiest and fastest way to change the field type from >> >> integer to double? >> > >> > The easy, fast, and dangerous method is to edit sqlite_master directly. >> >> ;-) >> I thought that the word "safest" is explicit. Guess not... > > Life is short. Agreed. > >> So, does this mean that I need to drop the DB in the text file, edit >> it and then re-create it from this file? > > That's the safest, but it might not be the fastest. Without the FK, > it would normally be a simple matter of creating the new table, > copying the data with a INSERT...SELECT (which should auto adjust the > types), then swapping the tables. I'm not sure of the FK will allow > you to do that or not. Worth a shot before you dump the whole > database... you might need to update both tables at the same time > within a transaction. How do I do that? I thought that simply using ".backup/.restore" will give me what I want, but it gives me a file in internal format. What I am trying to do is do make a text file which gives me all SQL command that was issued (CREATE TABLE and INSERT) than fix the particular CREATE TABLEu. and then load this file back into the db. All this will be done in the console (command prompt) window. Do I need to use ".dump/.load" commands for that? Thank you. > >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > 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] What is the easiest way of changing the field type?
On Sun, Dec 02, 2012 at 12:52:33PM -0800, Igor Korot scratched on the wall: > Jay, > > On Sun, Dec 2, 2012 at 12:16 PM, Jay A. Kreibich wrote: > > On Sun, Dec 02, 2012 at 11:58:54AM -0800, Igor Korot scratched on the wall: > >> Hi, ALL, > >> ALTER TABLE command does not support changing the field type. > >> > >> What is the easiest and fastest way to change the field type from > >> integer to double? > > > > The easy, fast, and dangerous method is to edit sqlite_master directly. > > ;-) > I thought that the word "safest" is explicit. Guess not... Life is short. > So, does this mean that I need to drop the DB in the text file, edit > it and then re-create it from this file? That's the safest, but it might not be the fastest. Without the FK, it would normally be a simple matter of creating the new table, copying the data with a INSERT...SELECT (which should auto adjust the types), then swapping the tables. I'm not sure of the FK will allow you to do that or not. Worth a shot before you dump the whole database... you might need to update both tables at the same time within a transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
On 12/2/2012 1:52 PM, Igor Korot wrote: So, does this mean that I need to drop the DB in the text file, edit it and then re-create it from this file? How to make alterations to a table that can not be done with the ALTER TABLE command is outlined in topic 11 of the FAQ: http://sqlite.org/faq.html#q11 You just have to make a change to a column definition rather than add or drop columns, but the idea is the same. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
Jay, On Sun, Dec 2, 2012 at 12:16 PM, Jay A. Kreibich wrote: > On Sun, Dec 02, 2012 at 11:58:54AM -0800, Igor Korot scratched on the wall: >> Hi, ALL, >> ALTER TABLE command does not support changing the field type. >> >> What is the easiest and fastest way to change the field type from >> integer to double? > > The easy, fast, and dangerous method is to edit sqlite_master directly. ;-) I thought that the word "safest" is explicit. Guess not... So, does this mean that I need to drop the DB in the text file, edit it and then re-create it from this file? Thank you. > >> I know it is all saved as text > > No, it isn't. That was true of SQLite 2, but SQLite 3 stores types > in their native format. > >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > 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] What is the easiest way of changing the field type?
On Sun, Dec 02, 2012 at 11:58:54AM -0800, Igor Korot scratched on the wall: > Hi, ALL, > ALTER TABLE command does not support changing the field type. > > What is the easiest and fastest way to change the field type from > integer to double? The easy, fast, and dangerous method is to edit sqlite_master directly. > I know it is all saved as text No, it isn't. That was true of SQLite 2, but SQLite 3 stores types in their native format. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is the easiest way of changing the field type?
Hi, ALL, ALTER TABLE command does not support changing the field type. What is the easiest and fastest way to change the field type from integer to double? I know it is all saved as text but for me when I look at the db structure I know what kind of variable I will use and what calculation needs to be performed. Also the database uses foreign keys. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
