On Mon, May 16, 2011 at 1:18 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Mon, May 16, 2011 at 12:49:14PM -0500, Nico Williams scratched on the wall: >> Nit: that's almost certainly the reason that SQLite3 doesn't support >> column rename, > > I think the bigger issue is that column rename requires full > understanding of the SQL statements stored in sqlite_master. To > safely do a ALTER TABLE...RENAME COLUMN you have to understand the > context and parts of the CREATE TABLE statement (and triggers, and > indexes, and FKs of other tables). This requires a close link between > the modification function, the parser, and the tokenizer. You have to > scan the SQL statement and understand the different parts, and then > back-trace them to a specific character in the original statement, > so you know which substring to change. While this is feasible, most > tokenizers and parsers are specifically designed to be abstract layers.
Of course that's the problem for column rename, and a problem for column drop. If I were implementing this I'd consider generating new CREATE statements while parsing old ones. I'd either create a separate .y file just for this, or I'd add logic to the existing code invoked from parse.y to detect re-write context and re-write instead of generate Vdbe code. The former seems likely to be much easier, and yet it'd still require much more code than the DB trigger patch I just posted -- "column rename is hard". I'd rather punt, not least because I don't need this feature :( >> The row data encoding is also the reason that new columns can only >> be added and appear at the end of the new CREATE TABLE statement, > > While I'm sure this is the major factor in that decision, the parsing > issue still exists. Adding a column def to the end of a CREATE TABLE > statement is fairly easy... find the last ')' character in the whole > statement and insert "',' || <new column>" right before. Even if the > encoding issue could be solved, inserting a column def anywhere else > requires context-aware parsing and understanding of the CREATE TABLE > statement, so it can be understood where the modifications have to be > made. Agreed. I just think that the row encoding issues are more significant still than the CREATE TABLE statement re-write issues, because overcoming the row encoding issue would require adding annotations in sqlite_master, and to drop rows would require more than that still: it'd require keeping historical schema metadata. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
