But there may be an argument for making the cloning more precise. 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.
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
