Re: [sqlite] Field drop work around

[Simon Slavin]

I just noticed that this is a FAQ:



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

Re: [sqlite] Field drop work around

[Jan Hudec]

On Tue, Aug 02, 2011 at 12:41:55 +, Black, Michael (IS) wrote:
> Since SQLite is type agnostic

Actually no, it's not. The optimizer usually does much better job if the
types are declared (I've seen many cases where it failed to use index when
some column was untyped). Plus there are unique constraints, foreign key
constraints and the very important 'integer primary key'.

> why don't you use generic field names?

You are not serious, are you? That's about the worst thing you could come up
with.

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

Re: [sqlite] Field drop work around

[Jay A. Kreibich]

On Tue, Aug 02, 2011 at 12:03:52PM +0100, Jack Hughes scratched on the wall:
> Hello all,
> 
> Is there a workaround for the lack of support for dropping fields?

  While this doesn't solve your immediate problem, I'm wondering if
  it might be possible to add code that simple marks a column invalid
  or unused, effectively making it "hidden."  Any constraints on that
  column would then be ignored (I know that's harder than it sounds).
  The next VACUUM could then clean up the data, completely deleting the
  column.

  How you deal with FKs, triggers, and indexes that are using those
  columns is left as an exercise for the coder.  8-)Humm... maybe
  that's why we don't have a ALTER TABLE...DROP COLUMN.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jan Hudec]

On Tue, Aug 02, 2011 at 12:03:52 +0100, Jack Hughes wrote:
> Any ideas how I can remove unused fields from the database would be 
> appreciated.

I'd consider creating the new database from scratch and importing the data
from the old one (by attaching it and doing insert ... select). That way
you'd only have one code for creating the schema and it would have
side-effect of defragmenting the database. It would of course take longer,
though.

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

Re: [sqlite] Field drop work around

[Jay A. Kreibich]

On Tue, Aug 02, 2011 at 12:41:55PM +, Black, Michael (IS) scratched on the 
wall:
> Since SQLite is type agnostic why don't you use generic field names?

  Table definitions consist of a lot more than just column names and
  types.  Defining keys and other constraints are an integral part of
  table and database definition.  Many people also use CHECK typeof()
  constraints, making specific columns more strictly typed.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[BareFeetWare]

On 02/08/2011, at 9:03 PM, Jack Hughes wrote:

> Is there a workaround for the lack of support for dropping fields?

As others have said, you can create a new table and insert data from the old to 
new table. Remember to also recreate any needed triggers and indexes. For 
example, I get my SQLite management app to write all the necessary SQL when the 
user changes, adds or deletes a column from a table or view, as shown here:
http://www.barefeetware.com/databare/trace.html

> I have an application and need to evolve the database schema as features are 
> added and removed. Leaving fields sitting inside the database that are no 
> longer used will lead to a schema that is difficult to understand. Especially 
> so when the field is marked as NOT NULL. Years from now I will need to supply 
> a value for a field when inserting a row that has long ago ceased to be used.

If your app has a dedicated purpose, such as a contact database but is not a 
general purpose database management app, then you probably shouldn't be 
redefining your schema columns anyway. Can you give some more specific detail 
on what you're doing here, such as some sample schema changes?

For a dedicated purpose app/database, you should probably be instead adding or 
removing rows or tables as you add or remove features. For instance, if you 
have a contact database that includes Person and a MySpace contact, but later 
decide not to have MySpace but you want Facebook contact, there are three ways 
to do this:

1. Add and remove a column. This is probably the worst way:

old table:

create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
,   "MySpace" text collate nocase
)
;

change:

begin immediate
;
create temp table "Person Cache" as select * from "Person"
;
drop table "Person"
;
create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
,   "Facebook" text collate nocase
)
;
insert into "Person" (ID, "Name First", "Name Last", "Email")
select ID, "Name First", "Name Last", "Email" from "Person Cache"
;
drop table temp."Person Cache"
;
commit
;

2. Or, add and remove rows. Probably the best way, if it fits your needs

schema:

create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
)
;
create table "Service"
(   ID integer primary key not null
,   Name text collate nocase unique not null
)
;
insert into "Service" (Name) values ('MySpace')
;
create table "Person Service"
(   ID integer primary key not null
,   "Person" integer not null references "Person" (ID) on delete cascade
,   "Service" integer not null references "Person" (ID) on delete cascade
,   Name text collate nocase not null
)
;

change:

delete from "Service" where name = 'MySpace'
;
insert into "Service" (Name) values ('Facebook')
;
insert into "Person Service" ("Person", "Service", Name)
(select ID from "Person" where "Email" = 'mic...@disney.com')
,   (select ID from "Service" where Name = 'Facebook')
,   'mickeymouse'
;

3. Or, add and remove related tables. If method 2 doesn't fit your needs.

schema:

create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
)
;
create table "Person MySpace"
(   ID integer primary key not null references "Person" (ID) on delete 
cascade
,   Name text collate nocase not null
)
;

change:

drop table "Person MySpace"
;
create table "Person Facebook"
(   ID integer primary key not null references "Person" (ID) on delete 
cascade
,   Name text collate nocase not null
)
;
insert into "Person Facebook" (ID, Name)
(select ID from "Person" where "Email" = 'mic...@disney.com')
,   'mickeymouse'
;

I hope that helps,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Field drop work around

[Jack Hughes]

I don't think NHibernate would allow me do do that... even if it did it would 
be difficult to understand. Things are hard enough to understand when I name 
the fields as per their intention never mind when there would be a level of 
indirection above it.


>Since SQLite is type agnostic why don't you use generic field names?

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

Re: [sqlite] Field drop work around

[Black, Michael (IS)]

Since SQLite is type agnostic why don't you use generic field names?



Just name your fields 0-NN and keep a set of defines for field names.



Then you just use #define to name the fields.



create table mytable (field1,field2,field3,field4);



#define NAME "field1"

#define ADDR "field2"

#define CITY "field3"

#define ZIP "field4"



All you need to do is put quotes around the field names (note: it's actually 
just separate strings but putting quotes around the field names is the visual 
effect and easier to think about).

char *sql = "select "NAME" from mytable where "ZIP" like '3%'";



sql will then contain:

select field1 from mytable where field4 like '3%'



Dropping fields from your defines will automatically give you syntax errors 
during compilation as long as you don't name your new fields the same as ones 
you just deleted (can you say long-term maintenance?).





This is something you can't do with a strongly-typed database.







Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jack Hughes [jhug...@openxtra.co.uk]
Sent: Tuesday, August 02, 2011 6:03 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Field drop work around

Hello all,

Is there a workaround for the lack of support for dropping fields? I have an 
application and need to evolve the database schema as features are added and 
removed. Leaving fields sitting inside the database that are no longer used 
will lead to a schema that is difficult to understand. Especially so when the 
field is marked as NOT NULL. Years from now I will need to supply a value for a 
field when inserting a row that has long ago ceased to be used.

Any ideas how I can remove unused fields from the database would be appreciated.

Regards,

Jack Hughes

___
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] Field drop work around

[Stephan Beal]

On Tue, Aug 2, 2011 at 1:13 PM, Simon Slavin  wrote:

> copy your data across using INSERT INTO myTable SELECT a,b,c,d FROM
> myOldTable
> DROP the old TABLE
>

or, similarly: CREATE TABLE myTable AS SELECT ... FROM myOldTable;

Though there might be subtle differences between the two which i'm not aware
of.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jack Hughes]

Thank you for your response Simon + Teg.

The absence of the full ALTER table support turns something that would have 
been as simple as writing "Delete.Column("ColumnName").FromTable("TableName");" 
in c# using the fluent migrator project into something that is far from simple 
and probably quite error prone.

I'm sure there are good reasons not to support the full ALTER table syntax but 
in cases like these it does make life a lot more difficult.

Regards,
Jack

>> Any ideas how I can remove unused fields from the database would be 
>> appreciated.

>Only by doing it manually:
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Teg]

Hello Jack,

I just migrate the tables forward.  Generate a new one in my program
and do a bulk insert from the old to the new, then drop the old one.
At other times, I've just re-purposed the unused field.

C

Tuesday, August 2, 2011, 7:03:52 AM, you wrote:

JH> Hello all,

JH> Is there a workaround for the lack of support for dropping
JH> fields? I have an application and need to evolve the database
JH> schema as features are added and removed. Leaving fields sitting
JH> inside the database that are no longer used will lead to a schema
JH> that is difficult to understand. Especially so when the field is
JH> marked as NOT NULL. Years from now I will need to supply a value
JH> for a field when inserting a row that has long ago ceased to be used.

JH> Any ideas how I can remove unused fields from the database would be 
appreciated.

JH> Regards,

JH> Jack Hughes

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



-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Field drop work around

[Simon Slavin]

On 2 Aug 2011, at 12:03pm, Jack Hughes wrote:

> Is there a workaround for the lack of support for dropping fields? I have an 
> application and need to evolve the database schema as features are added and 
> removed. Leaving fields sitting inside the database that are no longer used 
> will lead to a schema that is difficult to understand. Especially so when the 
> field is marked as NOT NULL. Years from now I will need to supply a value for 
> a field when inserting a row that has long ago ceased to be used.
> 
> Any ideas how I can remove unused fields from the database would be 
> appreciated.

Only by doing it manually:

rename your old TABLE using ALTER TABLE ... RENAME TO ...
create a new TABLE with just the columns you want, in the order you want them
copy your data across using INSERT INTO myTable SELECT a,b,c,d FROM myOldTable
DROP the old TABLE
CREATE appropriate INDEXes for the new table

It's just four commands plus one for each index you want, though I know it's 
tedious to have to do it like that rather than all in one operation.

Note that having TRIGGERS or FOREIGN KEYS defined complicates this.  And always 
take a backup before starting anything like this !

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

[sqlite] Field drop work around

[Jack Hughes]

Hello all,

Is there a workaround for the lack of support for dropping fields? I have an 
application and need to evolve the database schema as features are added and 
removed. Leaving fields sitting inside the database that are no longer used 
will lead to a schema that is difficult to understand. Especially so when the 
field is marked as NOT NULL. Years from now I will need to supply a value for a 
field when inserting a row that has long ago ceased to be used.

Any ideas how I can remove unused fields from the database would be appreciated.

Regards,

Jack Hughes

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