Maybe one option would be to add a layer to affect that explicitly, so that instead of the problem being that the existing rows can't be reordered without re-writing the entire table, the problem is to just change the schema to indicate where the columns should appear in "SELECT *" statements.
Basically, you might have: CREATE TABLE x (id INTEGER PRIMARY KEY, v TEXT); then say: ALTER TABLE x ADD COLUMN k TEXT UNIQUE AFTER id; which converts the table to: CREATE TABLE x (id INTEGER PRIMARY KEY, v TEXT, k TEXT UNIQUE AFTER id); This could be extended to delete columns by instead annotating the schema to indicate that a column is hidden or ignored. Perhaps the annotations could be constrained to only the end of the statement (so that series of placements and deletions can be comprehended). All that would be left would be renaming a column, which is already plausible (just rename the column in the schema). You could already implement this by renaming the table and replacing it with a view plus a trigger. But specifying that would be somewhat involved and brittle. WRT handling things like view and trigger and foreign key references, I think that would be reasonable to just expose manually. Replace the views and triggers in the same transaction. Provide a means to replace column definitions so that foreign key references can be handled in a similar fashion. [Personally my position is that "SELECT * FROM x" is not appropriate for production code. Otherwise you're implicitly relying on implementation details.] -scott On Tue, Jan 5, 2016 at 5:23 AM, gunnar <gharms at hiqinvest.nl> wrote: > Indeed those two possibilities! And perhaps also to MODIFY a column. We > alter tables all the time :) > > The possibility to specify at which place an added column should appear > when I do a "SELECT * ..." would be a nice extra, in MySQL it is done like: > ALTER TABLE table_name ADD COLUMN col_name1 [_AFTER_ col_name2 | FIRST] > > > > > Gr., > Gunnar > > > > > On 12/24/2015 05:14 PM, Simon Slavin wrote: > >> On 24 Dec 2015, at 3:12pm, John McKown <john.archie.mckown at gmail.com> >> wrote: >> >> ?I'm curious as to why. Doing so would, most likely, require rewriting the >>> entire table. If you want a SELECT * to get the columns in a particular >>> order, just create a VIEW with the columns in the order in which you want >>> them. >>> >> I think Gunnar just wants forms such as >> >> ALTER TABLE CREATE COLUMN ... >> ALTER TABLE DROP COLUMN ... >> >> to aid with making small changes to the schema. These are available in >> most SQL engines but the way SQLite3 is written makes it difficult or >> inefficient to implement them. >> >> I have a database where one table takes up more than 30 Gigabytes of >> space. While developing the software I needed several times to change a >> column definition and since SQLite lacks these facilities I had to move 30 >> Gig of data around every time I did it. Annoying. But it's not normally >> that much of a problem for me. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
