Re: [sqlite] Trouble with ALTER TABLE/ADD

[Isaac Raway]

On 10/25/06, Christian Smith <[EMAIL PROTECTED]> wrote:

A better solution would be to transfer the contents of the table being
updated to a temporary table, then recreate the original tables sans the
surplus columnn:

It may not be quick for large tables, but how often are you going to be
updating the table definition? If often, then you probably have a more
fundamental problem on your hands.


I considered this kind of solution briefly, but I'm afraid that users
might have too much data for this to be effecient at all. It wouldn't
happen a lot, but that rebuilding will have to happen between
user interactions. The user is basically allowed to create a form
template attached to an object. This template has an ID and a table
devoted to that type (user_data_nn).

A row in a user_tables:

ID  name   fields
01 "Person"  "name,email,site"

Then user_data_01 is created with 3 generic columns. If the total
field count ever drops below 3, it's simply ignored.

So what's really 

Re: [sqlite] Trouble with ALTER TABLE/ADD

[Christian Smith]

Isaac Raway uttered:


Thank, I will look at that (away from my dev machine for the day).

One other related question, are there plans to expand the functionality of
ALTER TABLE? I am working on a feature that could benefit greatly from
REMOVE/RENAME COLUMN. As it stands, I am going to have to simulate this by
using generic column names and mapping them to a list of the "actual" names.
It would be *very* nice to see these features added to sqlite before I
finish this feature, but I imagine this has been requested before...



A better solution would be to transfer the contents of the table being 
updated to a temporary table, then recreate the original tables sans the 
surplus columnn:


  BEGIN;
  CREATE TABLE temp_topic AS SELECT  FROM topic;
  DROP TABLE topic;
  CREATE TABLE topic ...;
  INSERT INTO topic SELECT * FROM temp_topic;
  DROP TABLE temp_topic;
  COMMIT;

It may not be quick for large tables, but how often are you going to be 
updating the table definition? If often, then you probably have a more 
fundamental problem on your hands.


If your data format is by definition user defined, then you might be best 
off with an  table instead, and use views to map 
instances into a virtual table.





Isaac




Christian




On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Isaac Raway" <[EMAIL PROTECTED]> wrote:
>
> ALTER TABLE topic ADD COLUMN type_id integer;
>
> This works fine when I run it on the sqlite3 command line, but fails in
the
> Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE
from
> the Delphi bindings?
>

Perhaps the delphi code is statically linked against an
older version of SQLite.  ADD COLUMN was added in version
3.2.0.  You can find what version delphi uses by executing

   SELECT sqlite_version();

--
D. Richard Hipp  <[EMAIL PROTECTED]>




-
To unsubscribe, send email to [EMAIL PROTECTED]


-








--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trouble with ALTER TABLE/ADD

[Isaac Raway]

Thank, I will look at that (away from my dev machine for the day).

One other related question, are there plans to expand the functionality of
ALTER TABLE? I am working on a feature that could benefit greatly from
REMOVE/RENAME COLUMN. As it stands, I am going to have to simulate this by
using generic column names and mapping them to a list of the "actual" names.
It would be *very* nice to see these features added to sqlite before I
finish this feature, but I imagine this has been requested before...

Isaac


On 10/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Isaac Raway" <[EMAIL PROTECTED]> wrote:
>
> ALTER TABLE topic ADD COLUMN type_id integer;
>
> This works fine when I run it on the sqlite3 command line, but fails in
the
> Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE
from
> the Delphi bindings?
>

Perhaps the delphi code is statically linked against an
older version of SQLite.  ADD COLUMN was added in version
3.2.0.  You can find what version delphi uses by executing

   SELECT sqlite_version();

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.

http://blueapples.org - blog
http://stonenotes.com - personal knowledge management

Re: [sqlite] Trouble with ALTER TABLE/ADD

[drh]

"Isaac Raway" <[EMAIL PROTECTED]> wrote:
> 
> ALTER TABLE topic ADD COLUMN type_id integer;
> 
> This works fine when I run it on the sqlite3 command line, but fails in the
> Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE from
> the Delphi bindings?
> 

Perhaps the delphi code is statically linked against an
older version of SQLite.  ADD COLUMN was added in version
3.2.0.  You can find what version delphi uses by executing

   SELECT sqlite_version();

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Trouble with ALTER TABLE/ADD

[Isaac Raway]

Hi, this is my first question on this list. First, a brief introduction:
I've been using sqlite for about the past year or so and so far I'm very
happy with it. As far as databases, I have experience with MySQL, SQL Server
and of course Access. My language skills include Delphi, PHP, Tcl, and VB
(top 4).

Okay, my question...I have this table in the first version of an application
I have created:

CREATE TABLE topic(id integer primary key, title string, namespace integer,
content blob, meta blob, x integer, y integer, w integer, h integer);

In order to upgrade existing databases I run various commands to add the
needed tables and store the current version of the database

ALTER TABLE topic ADD COLUMN type_id integer;

This works fine when I run it on the sqlite3 command line, but fails in the
Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE from
the Delphi bindings?

--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.


--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.

http://blueapples.org - blog
http://stonenotes.com - personal knowledge management

[sqlite] Trouble with ALTER TABLE/ADD

[Isaac Raway]

Hi, this is my first question on this list. First, a brief introduction:
I've been using sqlite for about the past year or so and so far I'm very
happy with it. As far as databases, I have experience with MySQL, SQL Server
and of course Access. My language skills include Delphi, PHP, Tcl, and VB
(top 4).

Okay, my question...I have this table in the first version of an application
I have created:

CREATE TABLE topic(id integer primary key, title string, namespace integer,
content blob, meta blob, x integer, y integer, w integer, h integer);

In order to upgrade existing databases I run various commands to add the
needed tables and store the current version of the database

ALTER TABLE topic ADD COLUMN type_id integer;

This works fine when I run it on the sqlite3 command line, but fails in the
Delphi units. Any thoughts? Has anyone ahd trouble running ALTER TABLE from
the Delphi bindings?

--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.