> I think
> SQLite implementations should probably adhere to a core spec but I recognize
> this as my bias, not dogma.
Probably this is my personal opinion but why should SQLite comply with
specification of Pick Multi-dimensional databases if it never claimed
to be multi-dimensional? SQLite is a relational database, it complies
with standard for relational databases and relational language - SQL.
And SQL standard doesn't define which type should be assigned to
columns in the table created by CREATE ... AS SELECT. But even if it
defined that special treatment of INTEGER PRIMARY KEY column as
equivalent to rowid is definitely not in that standard - it's SQLite's
special feature. So if you rely on that feature then your application
doesn't conform SQL standard, so you should change your application...
But I digress and don't mean any offence. I just want to say that
SQLite complies with SQL standard and all other features should be
either taken by you as is or shouldn't be used at all.
Pavel
On Tue, Jun 29, 2010 at 10:58 AM, Tim Romano <tim.romano...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
