On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano <tim.romano...@gmail.com> wrote:
> But there may be an argument for making the cloning more precise.
The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
table. Not too long ago I encountered the same issue (search the mail
archives). Igor, as usual, explained it succinctly and effectively --
sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
sqlite> INSERT INTO t (b) VALUES ('foo');
sqlite> INSERT INTO t (b) VALUES ('bar');
sqlite> INSERT INTO t (b) VALUES ('baz');
sqlite> SELECT * FROM t;
a b
---------- ----------
1 foo
2 bar
3 baz
sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
sqlite> SELECT * FROM u;
a b
---------- ----------
1.5 foo
2.5 bar
3.5 baz
sqlite> .s
CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
CREATE TABLE u(a,b TEXT);
sqlite> SELECT Typeof(a) FROM u;
Typeof(a)
----------
real
real
real
sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
sqlite> INSERT INTO v SELECT * FROM t;
sqlite> SELECT * FROM v;
a b
---------- ----------
1 foo
2 bar
3 baz
sqlite> DELETE FROM v;
sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
Error: datatype mismatch
sqlite>
In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
Instead, create the new table the way you want to, and then use INSERT
to populate it with data from the old table.
> It's a
> bit of a mess, or at least it seems so to me because my first ten years of
> database work was done with PICK, a database that was developed by PICK
> Systems but licensed to many companies and marketed under different brands
> with subtle functionality differences, yet applications that adhered to the
> core PICK spec were completely portable across all implementations. I think
> SQLite implementations should probably adhere to a core spec but I recognize
> this as my bias, not dogma.
>
> Adobe (and possibly Google and some others who are involved in coordinating
> their SQLite implementations --I'm not fully "up" on the details of who all
> are involved in that cooperative effort, or the extent to which they have
> reconciled their implementations) treats INT primary keys as aliases for
> the RowId, whereas SQLite does so only with INTEGER primary keys.
>
> Should one ever share a SQLite database that happens to contain tables
> defined via the "CREATE TABLE X as SELECT..." cloning approach with someone
> who is using an Adobe-based tool, the query results returned by the
> Adobe-based tool will not jibe with the results returned by the SQlite
> command-line utility. On the table with INT primary key, Adobe will be
> fetching the row by row-id.
>
> If a table has foreign-key value of 10, and is joined to a table with an
> INT primary key, Adobe will bring over the 10th row in the table even though
> the value in the primary key column of that table may or may not contain the
> number 10.
>
> In Adobe:
>
> CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
> insert into FOO(1,'Groucho')
> insert into FOO(2,'Chico')
> Insert into FOO(999, 'Harpo')
>
> the row id of Harpo's row is 3 (it's PK value is 999) and Harpo will be
> returned by a join when the foreign key = 3 not when the foreign key = 999.
>
Right. But, the issue here may be that the sqlite world is much, much
bigger than either the Adobe or the Google worlds. While those two
companies may be a part of the sqlite consortium, sqlite itself runs
on way more platforms and in configurations than Adobe+Google support.
In any case, I am not even sure if the sql standard calls for CREATE
TABLE t AS SELECT... to produce a clone of a table.
>
> Regards
> Tim Romano
> --
> Most people assume one is wearing underwear. Not so for intelligence.
>
>
> On Tue, Jun 29, 2010 at 9:46 AM, Jay A. Kreibich <j...@kreibi.ch> wrote:
>
>> On Tue, Jun 29, 2010 at 06:59:18AM -0400, Tim Romano scratched on the wall:
>> > CREATE TABLE "main"."proto" ("id" INTEGER PRIMARY KEY AUTOINCREMENT
>> NOT
>> > NULL , "name" TEXT)
>> > CREATE TABLE "main"."clone" as select * from PROTO
>> >
>> > The primary key of table CLONE is defined as "INT" not "INTEGER".
>>
>> The only information preserved by CREATE TABLE ... AS SELECT is the
>> column affinity (not "type"). These are equivalent, and both
>> translate to an INTEGER affinity.
>>
>>
>>
>> ...which actually surprises me, since I was under the impression
>> CREATE TABLE ... AS SELECT always produced NONE affinities. Is this
>> a semi-recent (last year) change?
>>
>> -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
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
