[sqlite] Column types "safe" changes
As I recall sqlite probably doesn't case about string types included in the CREATE TABLE field list, so TEXT, VARCHAR, VARCHAR(200) are identical from its point of view. Today I needed to increase the number of chars in VARCHAR statement since the library that uses sqlite actually does care about the value. So I did it with writable_schema pragma temporary change. Everything worked but just wondering, what kind of changes are safe if it is absolutely necessary? I suppose the order of fields and the presence of every one of them is absolutely necessary, but what about types changes like moving from INTEGER to TEXT? Also is it possible to add to sqlite a limited ALTER TABLE .. MODIFY support that could wrap all known "safe" changes and invoking errors on all "unsafe" ones? Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column types "safe" changes
Max Vlasov wrote: > As I recall sqlite probably doesn't case about string types included > in the CREATE TABLE field list, so TEXT, VARCHAR, VARCHAR(200) are > identical from its point of view. Today I needed to increase the > number of chars in VARCHAR statement since the library that uses > sqlite actually does care about the value. So I did it with > writable_schema pragma temporary change. Everything worked but just > wondering, what kind of changes are safe if it is absolutely > necessary? Basically, any changes that don't affect the interpretation of bits already present in the database file. > I suppose the order of fields and the presence of every > one of them is absolutely necessary, but what about types changes > like moving from INTEGER to TEXT? Should be safe, unless there is an index involving the column. Column affinity affects comparisons, so the existing sorting order may be wrong for the new affinity. And, of course, you'll have a table where some rows (old ones) contain integers and others (new ones) contain text. SQLite is fine with this, but your client software might not be prepared to deal with it. Some comparisons might behave in surprising ways. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column types "safe" changes
>And, of course, you'll have a table where some rows (old ones) contain >integers and others (new ones) contain text. SQLite is fine with this, >but your client software might not be prepared to deal with it. Some >comparisons might behave in surprising ways. I imagine that in such case, the best bet is to run a "refresh all" procedure, updating each row identically, just to have the new affinity take effect. Beside the obvious update with every changed column mentionned, update tbl set col1 = (select col1 from tbl y where y.rowid = tbl.rowid), ... coln = (select coln from tbl y where y.rowid = tbl.rowid); do you think of a easier way? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column types "safe" changes
Jean-Christophe Deschamps wrote: > Beside the obvious update with every changed column mentionned, > > update tbl set col1 = (select col1 from tbl y where y.rowid = > tbl.rowid), >... >coln = (select coln from tbl y where y.rowid = > tbl.rowid); > > do you think of a easier way? Why not just update tbl set col1 = col1; or perhaps update tbl set col1 = cast(col1 as text); I'm not sure the former will actually change anything, but the latter should. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column types "safe" changes
>Why not just > > update tbl set col1 = col1; > >or perhaps > > update tbl set col1 = cast(col1 as text); > >I'm not sure the former will actually change anything, but the latter >should. Yes my untold question was merely if simpler col = col way could be simply ignored. You're right about the cast, it should work, thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
