Hi Scott, It is not such a big deal for us but since I saw the subject "whish list" I thought to add something that I was surprised that it isn't there in sqlite (since it is so complete in many other ways), but that i use on regular basis. I agree that SELECT * in production code is bad, but I do use it on the command line client now and then. Regarding views, I don't know how sqlite handles them but I know that they can drastically slow down queries in MySQL for example.
gr., Gunnar On 01/05/2016 03:56 PM, Scott Hess wrote: > 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 >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
