Re: [sqlite] Why can't SQLite support ALTER TABLE commands that rewrite the table?

[Nico Williams]

On Fri, Oct 19, 2012 at 4:14 PM, Yves Goergen
 wrote:
> Since my last question was not answered but instead another statement
> was made which I don't understand, I feel the need to reformulate my
> question to this: Why is it that SQLite can only support ALTER TABLE
> statements "that can be accomplished without having to rewrite the
> entire table"? [1] This includes statements to drop single columns out
> of a table. I understand that doing this might cause a considerable
> delay during which the database is not available, but the same applies
> to the VACUUM statement and that is very well supported. Could somebody
> maybe enlighten me?

SQLite3 could have a way of recording the existence of dropped columns
so as to allow column dropping while keeping the data as-is.  That is,
the column would not be dropped in actuality, just marked as so, with
all new rows getting NULL for it..  (Note that even that's not really
feasible if the column being dropped was part of a primary key or
index of any kind.)  But re-writing tables is not likely to be
terribly satisfactory: you might run into ENOSPC in the process, for
example, or it might just take too long.  Simply marking a column as
dropped is much simpler, I'm sure.  Also, to properly handle data
re-write for schema changes would require being able to process an
entire transaction of schema changes because a change to one table
might make no sense except in conjunction with a corresponding change
to another (think of foreign keys).  In other words: this feature is
difficult to implement and likely to leave a lot to be desired, so why
bother.

> If the developers deciding what SQLite can do and what not would state
> that they do not want, for personal reasons they don't want to disclose,
> to implement that particular feature, that's fine. But if that's the
> case, I'd be happy to hear that. Also, if there's a technical limitation
> preventing this, I'd like to understand it. Having no answer at all is a
> bit frustrating...

The answer I've seen given to this sort of question before is that
SQLite3 is supposed to be *lite*.  Since everyone knows to rewrite
their tables themselves when making schema changes that require it...
Or put it another way: the SQLite developers almost certainly have
lots of things they'd rather work on than this feature.   Frankly, I'd
rather they focus on SQLite4 than this feature.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why can't SQLite support ALTER TABLE commands that rewrite the table?

[Yves Goergen]

Since my last question was not answered but instead another statement
was made which I don't understand, I feel the need to reformulate my
question to this: Why is it that SQLite can only support ALTER TABLE
statements "that can be accomplished without having to rewrite the
entire table"? [1] This includes statements to drop single columns out
of a table. I understand that doing this might cause a considerable
delay during which the database is not available, but the same applies
to the VACUUM statement and that is very well supported. Could somebody
maybe enlighten me?

If the developers deciding what SQLite can do and what not would state
that they do not want, for personal reasons they don't want to disclose,
to implement that particular feature, that's fine. But if that's the
case, I'd be happy to hear that. Also, if there's a technical limitation
preventing this, I'd like to understand it. Having no answer at all is a
bit frustrating...

[1] Richard Hipp, 2012-10-08 22:12 +0200, on this mailing list

-- 
Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users