Thanks for all explanations. The background of my question wasn't about speed, but about easier handling. It would be perfectly ok if Sqlite did more or less the same as when currently manually recreating/copying the table. I just would appreciate having an intuitive (and easy-to-read) SQL statement for that operation.
----- Original Message ----- From: David Raymond <david.raym...@tomtom.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Sent: Tuesday, May 22, 2018, 22:32:39 Subject: [sqlite] ALTER TABLE Some of these things can get taken care of by simply messing with the sqlite_master table contents. Renaming a field for example would "just" be basically doing some replacing in the many places field names are used. So the table entry itself, any indexes, views that use that field, triggers, etc. It's a lot of managing, but since our databases work then we know that the ability to parse out what's what in the schema text has already been done. As far as dropping a field, you'd have to do a re-write of the whole table even if there were no dependency issues. The actual data storage has no info about what name each field is, it just stores the data in the order of the schema, so if you drop field #3, then you actually have to go in and remove field #3 from the data pages, or else it won't realize the old field #4 is now field #3. I originally thought you might be able to get away with dropping the final field, as all the front fields would still be in the right order. But then if you were to issue the "alter table add column" then your new field would suddenly have all the data from the dropped field, so you'd need to re-write it then anyway. You'd have to introduce a "sqlite file format 5" or something similar to be able to handle it without the re-write. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Tuesday, May 22, 2018 3:58 PM To: SQLite mailing list Subject: Re: [sqlite] ALTER TABLE Thinking off the cuff, there'd be a seven step process for this; Begin Transaction Turn off the PK/FK relationship PRAGMA constraint checks Rename old table to a temp table via whatever means are available Create the new table INSERT INTO the new table Turn on the PK/FK relationship PRAGMA constraint check End transaction. I've never manually run the SQLite commands to perform a rename, as I use SQLite expert, and "F2" allows me to rename if needed (So it does all the legwork) so I don't know if its an alter table command or you have to go the drop/create route. On top of that, my databases typically only contain about a meg or two of textual data, so, how quick my F2 would work on a larger dataset, I don't know. SQLite does not hold a unique permanent "link" from one table to another. So the act of renaming, and then re-provisioning the table will not change your code or how the other tables view the new table. Its concern is that at the time of the SQL execution, the tables and fields exist as required. In other words, if you do rename a table, every other table in the database is oblivious to the change, meaning its not aware. On Tue, May 22, 2018 at 3:34 PM, Igor Korot <ikoro...@gmail.com> wrote: > Hi, Charles, > On Tue, May 22, 2018 at 2:30 PM, Charles Leifer <colei...@gmail.com> > wrote: > > As a workaround, you can always rename the existing table, create the new > > table with desired attributes, and do a INSERT INTO ... SELECT FROM > > old_table. Then you can safely drop the old table. > But the table_name will be different. > Also the data in the old table might be referencing some other table. > So this process is not really very > straightforward... > Thank you. > > > > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz <sqlite.2...@t-net.ruhr> > wrote: > > > >> > ALTER TABLE ADD COLUMN has existed for a long time. > >> > >> Yes, sorry, I mixed things up. > >> > >> The order of importance is imho: > >> 1. RENAME COLUMN (shouldn't be too hard) > >> 2. DROP COLUMN (should be a bit more comlicated but feasible) > >> 3. MODIFY COLUMN > >> > >> > What kind of MODIFY COLUMN changes do you have in mind? > >> > >> I understand this can be difficult as there are many possible operations > >> that might be incompatible with the data already stored in that column. > >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE > CASCADE > >> action of a foreign key column. Also adding/removing a foreign key > would be > >> useful. > >> > >> Kind regards, > >> Thomas > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ 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
