To modify column names if you want to live dangerously you could try something like this
PS C:\sqlite> sqlite3 writ.db SQLite version 3.23.1 2018-04-10 17:39:29 Enter ".help" for usage hints. sqlite> create table test (c1, c2, c3); sqlite> insert into test values(1, 2, 3); sqlite> pragma writable_schema = yes; sqlite> update sqlite_master set sql = 'create table test (d1, d2, d3)' where name = 'test'; sqlite> .quit PS C:\sqlite> sqlite3 writ.db SQLite version 3.23.1 2018-04-10 17:39:29 Enter ".help" for usage hints. sqlite> .headers on sqlite> select * from test; d1|d2|d3 1|2|3 You could potentially do the same to modify the type of a column and SQLites column affinity rules could/should help display the data correctly. I have not tested this for different column types sqlite> create table test2 (c1 int, c2 int, c3 int); sqlite> insert into test2 values(1, 2, 3); sqlite> pragma writable_schema = yes; sqlite> update sqlite_master set sql = 'create table test2 (d1 text, d2 text, d3 text)' where name = 'test2'; sqlite> .quit PS C:\sqlite> sqlite3 writ.db SQLite version 3.23.1 2018-04-10 17:39:29 Enter ".help" for usage hints. sqlite> .headers on sqlite> select * from test2; d1|d2|d3 1|2|3 The same idea should also work to add/remove a foreign key - but you would of course need to understand any implications and ensure that the existing data does not cause a constraint conflict. The only suggestion I have a problem with is dropping a column. Every record in the b-tree would need to be modified to remove the now redundant data. The on;ly exception I can see to this is if you are dropping the last column from a table sqlite> pragma writable_schema = yes; sqlite> update sqlite_master set sql = 'create table test2 (e1 text, e2 text)' where name = 'test2'; sqlite> .quit PS C:\sqlite> sqlite3 writ.db SQLite version 3.23.1 2018-04-10 17:39:29 Enter ".help" for usage hints. sqlite> .headers on sqlite> select * from test2; e1|e2 1|2 sqlite> pragma integrity_check; integrity_check ok I have not done any thorough testing as this sort of thing is outside my main area of interest, but it might give you some ideas. It goes without saying that messing with the sqlite_schema is dangerous territory. Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 22 May 2018 at 20:34, 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
