Re: [sqlite] Why can't SQLite drop columns?

On 9 Oct 2012, at 6:54pm, Adam DeVita wrote: > The why seems to be something about the necessity of rewriting the > table, which is 'slow'. I expect that users accept that Alter table > can be expensive.Is there more to it? Some sort of ACID breakdown? There are

Re: [sqlite] Why can't SQLite drop columns?

There are some applications where using a 3rd party utility is inherently awkward and time consuming, but using your application that uses the sqlite api is not. (For example, you can change your code via an automatic update mechanism, but perhaps can't execute a 3rd party tool that likely isn't

Re: [sqlite] Why can't SQLite drop columns?

Pete lcSQL Software You're probably already aware of this but there are third party tools available that will do this for you, plus many other schema maintenance functions that aren't provided in sqlite. One such is my SQLiteAdmin program, available at www.lcsql.com. On

Re: [sqlite] Why can't SQLite drop columns?

On 10/8/12, Petite Abeille wrote: > How do they deal with constraints? triggers? indexes? others? > If this was as straightforward as a 'create table bar as select a, b, c from > foo; drop table foo; alter table bar rename to foo;', we would most likely > not having this

Re: [sqlite] Why can't SQLite drop columns?

You can do it, but you need to parse the existing table schema. 1. Get the existing schema for the table, along with associated triggers and indexes: select group_concat(SQL, x'0A' || ';' || x'0A') from SQLite_Master where tbl_name = 'My Table'. 2. Edit the create table schema to remove the

Re: [sqlite] Why can't SQLite drop columns?

Jay Kreibich wrote: On Oct 8, 2012, at 3:36 PM, Darren Duncan wrote: Of course, if that is done, then in order to have predictable performance we'd also want to add some other statement one can invoke when they want to reclaim disk space later, which actually goes

Re: [sqlite] Why can't SQLite drop columns?

On Oct 8, 2012, at 10:47 PM, gregorinator wrote: > (they do it by copying the table, but it's transparent to > you). How do they deal with constraints? triggers? indexes? others? If this was as straightforward as a 'create table bar as select a, b, c from foo; drop

Re: [sqlite] Why can't SQLite drop columns?

Some freeware SQLite tools, such as SQLiteStudio, allow you to drop columns (they do it by copying the table, but it's transparent to you). If you don't need to drop the column programmatically, as part of a bigger automated process, it works and you don't have to code anything. gs

Re: [sqlite] Why can't SQLite drop columns?

On Oct 8, 2012, at 10:36 PM, Darren Duncan wrote: > Petite Abeille wrote: >> On Oct 8, 2012, at 10:12 PM, Richard Hipp wrote: >>> ALTER TABLE DROP COLUMN requires writing every row of the table. >> Could be a 'logical delete' instead. In other words, a

Re: [sqlite] Why can't SQLite drop columns?

On 08.10.2012 22:12 CE(S)T, Richard Hipp wrote: > The only ALTER TABLE functions that SQLite supports are those that can be > accomplished without having to rewrite the entire table. Oh, and what about the VACUUM command? That certainly needs to recreate just about everything, and yet it's here.

Re: [sqlite] Why can't SQLite drop columns?

On 08.10.2012 22:12 CE(S)T, Richard Hipp wrote: > ALTER TABLE DROP COLUMN requires writing every row of the table. Actually, I wouldn't care what the engine needs to do to get the job done. If it's documented that a certain command requires more time, it would be perfectly fine. SELECT queries

Re: [sqlite] Why can't SQLite drop columns?

On Oct 8, 2012, at 10:12 PM, Richard Hipp wrote: > the only ALTER TABLE functions supported are those that can be > done in O(1) time. Out of curiosity, does 'add column not null default' modify the data or just the table definition? For example: create table foo( x, y );

Re: [sqlite] Why can't SQLite drop columns?

On Oct 8, 2012, at 3:36 PM, Darren Duncan wrote: > Petite Abeille wrote: >> On Oct 8, 2012, at 10:12 PM, Richard Hipp wrote: >>> ALTER TABLE DROP COLUMN requires writing every row of the table. >> Could be a 'logical delete' instead. In other words, a

Re: [sqlite] Why can't SQLite drop columns?

Petite Abeille wrote: On Oct 8, 2012, at 10:12 PM, Richard Hipp wrote: ALTER TABLE DROP COLUMN requires writing every row of the table. Could be a 'logical delete' instead. In other words, a dictionary operation instead of a data one.

Re: [sqlite] Why can't SQLite drop columns?

On Oct 8, 2012, at 10:12 PM, Richard Hipp wrote: > ALTER TABLE DROP COLUMN requires writing every row of the table. Could be a 'logical delete' instead. In other words, a dictionary operation instead of a data one.

Re: [sqlite] Why can't SQLite drop columns?

On Mon, Oct 8, 2012 at 3:57 PM, Yves Goergen wrote: > > Why does SQLite still not support dropping columns > through SQL after all these years? > The only ALTER TABLE functions that SQLite supports are those that can be accomplished without having to rewrite the