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

[Simon Slavin]

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 problems with ALTER TABLE, but most of them involve very thorough 
checks that the column you're dropping isn't mentioned anywhere: indices, 
foreign keys, constraints, and probably a bunch of things I've forgotten.  The 
way SQLite works means that trying to spot all the places that a column is 
mentioned is very difficult and involves a lot of parsing.  I'm betting that 
it's that which makes it so difficult.  Once you're sure the column isn't 
mentioned, actually dropping it is probably fairly simple: marking pages in the 
data file as unused, rewriting the CREATE TABLE command, and updating the 
schema version number.

It would be nice to be able to drop columns in SQLite4.  Don't bet on it 
appearing before then, or even then.

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

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

[Adam DeVita]

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 on the user's system anyway.)

"Use some 3rd party utility" isn't really an answer to "Why can't I
use this command?".   The OP didn't ask how can they alter the table,
they know how.

The lack of ALTER TABLE means that your application would have to do
it  This is simple enough to do, but means you have to write your own
implementation of alter table, to some degree of complication or
duplication, and of course with more lines of code comes an increased
risk of writing a new bug.

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?

regards,
Adam DeVita


On Tue, Oct 9, 2012 at 12:45 PM, Peter Haworth <p...@lcsql.com> wrote:
> Pete
> lcSQL Software <http://www.lcsql.com>
> 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 Tue, Oct 9, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:
>
>> Message: 11
>> Date: Mon, 08 Oct 2012 21:57:21 +0200
>> From: Yves Goergen <nospam.l...@unclassified.de>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Subject: [sqlite] Why can't SQLite drop columns?
>> Message-ID: <50733021.8020...@unclassified.de>
>> Content-Type: text/plain; charset=UTF-8
>>
>> Hello,
>>
>> I know that SQLite doesn't support ALTER TABLE DROP COLUMN, unlike
>> probably every other SQL database system. But every time I come across
>> this, I feel the pain of having to write huge amounts of code to
>> automatically remove single columns in a table. When doing that in code,
>> it's usually working non-interactively on some generic table schema and
>> cannot use hard-coded column names. So I really have to collect all
>> relevant data including foreign keys and all column attributes and then
>> generate the right SQL code to copy everything right except the dropped
>> column.
>>
>> I very much believe that it would save a lot of developers' resources if
>> SQLite supported that directly. After all, the DBMS has all the data it
>> needs in its readily readable data structures. It would possibly be less
>> work for SQLite than for anybody using it.
>>
>> So I am asking: Why does SQLite still not support dropping columns
>> through SQL after all these years? Do the SQLite developers have strong
>> arguments against it, and which? Are there technical limitations (I
>> can't believe that)? Is there some kind of religion behind it?
>>
>> --
>> 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



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Haworth]

Pete
lcSQL Software <http://www.lcsql.com>
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 Tue, Oct 9, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 11
> Date: Mon, 08 Oct 2012 21:57:21 +0200
> From: Yves Goergen <nospam.l...@unclassified.de>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: [sqlite] Why can't SQLite drop columns?
> Message-ID: <50733021.8020...@unclassified.de>
> Content-Type: text/plain; charset=UTF-8
>
> Hello,
>
> I know that SQLite doesn't support ALTER TABLE DROP COLUMN, unlike
> probably every other SQL database system. But every time I come across
> this, I feel the pain of having to write huge amounts of code to
> automatically remove single columns in a table. When doing that in code,
> it's usually working non-interactively on some generic table schema and
> cannot use hard-coded column names. So I really have to collect all
> relevant data including foreign keys and all column attributes and then
> generate the right SQL code to copy everything right except the dropped
> column.
>
> I very much believe that it would save a lot of developers' resources if
> SQLite supported that directly. After all, the DBMS has all the data it
> needs in its readily readable data structures. It would possibly be less
> work for SQLite than for anybody using it.
>
> So I am asking: Why does SQLite still not support dropping columns
> through SQL after all these years? Do the SQLite developers have strong
> arguments against it, and which? Are there technical limitations (I
> can't believe that)? Is there some kind of religion behind it?
>
> --
> 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

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

[gregorinator]

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 conversation :)

I'm sorry for not being more comprehensive in my answer.  Also, as I
pointed out, I can only speak for SQLiteStudio -- there are other
tools; you need to investigate for yourself.

When dropping a column from a table, SQLiteStudio preserves the
original table's primary key, foreign keys, null constraints,
defaults, check constraints, unique constraints, and indexes on
columns other than the one being deleted.  At _least_ that much.  I
confess I've never done an exhaustive exploration of what it preserves
-- I only look for the things I need preserved in my applications.  I
haven't come across anything I need that it doesn't preserve, but I
confess I haven't tried it with triggers or that really powerful SQL92
"others" functionality that you asked about. :)

Honestly, I wouldn't even have recommended SQLiteStudio or tools like
it if my experience was that all it did was "create table bar select
from foo", but I can see it would have made my earlier post more clear
if I had mentioned this from the start.  And, as I said, SQLiteStudio
isn't the only SQLite db manager out there; I suggest that you take
them each for a spin and come to your own conclusions.  But my point
was that there are third party tools that might be able to give you a
robust DROP COLUMN capability -- and maybe other functionality that
SQLite can't do itself -- without your having to reinvent the wheel.
Might.

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

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

[BareFeetWare]

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 column you no longer want.

3. Execute the following, with the edited schema inserted as shown:

begin immediate
;
pragma foreign_keys = NO
;
pragma triggers = NO
;
create temp table "Cache" as select * from "My Table"
;
drop table "My Table"
;

;
insert into "My Table" () select  
from temp.Cache
;
drop table temp.Cache
;
pragma foreign_keys = YES
;

4. If any of that generates an error, then issue a rollback. If it works fine, 
then execute commit.

Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

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

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

[Darren Duncan]

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 and rewrites the table, rather than this just happening 
"automatically" (though it could also be configured to happen automatically.


That would be VACUUM, which already rewrites the tables, and would have to be 
run to reclaim the space anyways.


Yes, that's what I meant, I didn't recall if SQLite already had such 
functionality (I knew of it in other DBMSs). -- Darren Duncan

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

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

[Petite Abeille]

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 table foo; alter table bar rename to foo;', we would most likely not 
having this conversation :)



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

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

[gregorinator]

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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

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 dictionary operation 
>> instead of a data one.
>> http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete
> 
> I don't think you want to follow the syntax of that Oracle example, the 
> "ALTER TABLE foo SET UNUSED (bar, baz)", especially since that is reversible 
> and it could mess up other issues such as effectively invalidating 
> constraints.

Yes, I would not suggest using the Oracle syntax either. This was more for 
illustration purpose.

> *All* SQL actions are /supposed/ to be logical, meaning that DROP COLUMN *is* 
> a "logical delete", no matter if column data is physically removed or just 
> made inaccessible.

"In theory there is no difference between theory and practice. In practice 
there is."
-- Yogi Berra


> SQLite can add the DROP COLUMN syntax but just implement it so that tables 
> don't have to be rewritten, but just that the then-superfluous table data is 
> ignored, and so you still get O(1).

Precisely.

> 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 and rewrites the table, rather 
> than this just happening "automatically" (though it could also be configured 
> to happen automatically).

vacuum?


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

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

[Yves Goergen]

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. (Thankfully!)

-- 
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

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

[Yves Goergen]

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 also don't always return in a
constant time, each of them requires different work to be done.

So, to update my question: Why doesn't SQLite support DDL commands that
internally need recreating the table or may not return in O(1) time?

Of course, when the engine recreates the table, I'd expect it to
maintain all foreign key references, if possible. That's probably even
more work from the outside as a normal database user. (You know, the
engine can trust the things it does itself (at least I hope so), but it
doesn't necessarily trust the things a user does.)

-- 
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

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

[Petite Abeille]

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 );
insert into foo values( 1, null );
alter table foo add column z not null default 'z' ;
select * from foo;
> x|y|z
> 1||z


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

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

[Jay Kreibich]

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 dictionary operation 
>> instead of a data one.
>> http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete
> 
> I don't think you want to follow the syntax of that Oracle example, the 
> "ALTER TABLE foo SET UNUSED (bar, baz)", especially since that is reversible 
> and it could mess up other issues such as effectively invalidating 
> constraints.
> 
> *All* SQL actions are /supposed/ to be logical, meaning that DROP COLUMN *is* 
> a "logical delete", no matter if column data is physically removed or just 
> made inaccessible.
> 
> SQLite can add the DROP COLUMN syntax but just implement it so that tables 
> don't have to be rewritten, but just that the then-superfluous table data is 
> ignored, and so you still get O(1).
> 
> 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 and rewrites the table, rather 
> than this just happening "automatically" (though it could also be configured 
> to happen automatically.

That would be VACUUM, which already rewrites the tables, and would have to be 
run to reclaim the space anyways.

  -j




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

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

[Darren Duncan]

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.

http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete


I don't think you want to follow the syntax of that Oracle example, the "ALTER 
TABLE foo SET UNUSED (bar, baz)", especially since that is reversible and it 
could mess up other issues such as effectively invalidating constraints.


*All* SQL actions are /supposed/ to be logical, meaning that DROP COLUMN *is* a 
"logical delete", no matter if column data is physically removed or just made 
inaccessible.


SQLite can add the DROP COLUMN syntax but just implement it so that tables don't 
have to be rewritten, but just that the then-superfluous table data is ignored, 
and so you still get O(1).


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 and rewrites the table, rather than this 
just happening "automatically" (though it could also be configured to happen 
automatically).


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

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

[Petite Abeille]

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.

http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete

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

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

[Richard Hipp]

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 entire table.  Or, to put it
another way, the only ALTER TABLE functions supported are those that can be
done in O(1) time.

ALTER TABLE DROP COLUMN requires writing every row of the table.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why can't SQLite drop columns?

[Yves Goergen]

Hello,

I know that SQLite doesn't support ALTER TABLE DROP COLUMN, unlike
probably every other SQL database system. But every time I come across
this, I feel the pain of having to write huge amounts of code to
automatically remove single columns in a table. When doing that in code,
it's usually working non-interactively on some generic table schema and
cannot use hard-coded column names. So I really have to collect all
relevant data including foreign keys and all column attributes and then
generate the right SQL code to copy everything right except the dropped
column.

I very much believe that it would save a lot of developers' resources if
SQLite supported that directly. After all, the DBMS has all the data it
needs in its readily readable data structures. It would possibly be less
work for SQLite than for anybody using it.

So I am asking: Why does SQLite still not support dropping columns
through SQL after all these years? Do the SQLite developers have strong
arguments against it, and which? Are there technical limitations (I
can't believe that)? Is there some kind of religion behind it?

-- 
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