> PICK is simply an
> example of a database that came in many slightly different versions all of
> which adhered to a core definition, and this compatibility was a good
> thing.
Then I reiterate my point using your wordings: SQL comes in different
versions and flavors (SQLite, Oracle, MS SQL etc.) and if you stick
strictly to SQL standard then your implementation will be compatible
with all those versions. INTEGER PRIMARY KEY is not part of the
standard, as well as INT PRIMARY KEY (which non-standard-compliant
Adobe relies on), or IDENTITY datatype, or CONNECT BY PRIOR query
clause, or lots of other features that you shouldn't use in your
application if you want to stick with core SQL standard (as you
apparently did in case of PICK).
Pavel
On Tue, Jun 29, 2010 at 12:03 PM, Tim Romano <tim.romano...@gmail.com> wrote:
> Pavel,
> As I said, I am biased in favor of the benefits to there being
> core-compatibility among the various implementations of SQLite but I am not
> dogmatic about it, and I have no say in the matter in any case ;-)
>
> But I think you may have misunderstood me: I am not arguing that SQlite
> should be compatible with PICK. They are nothing alike. PICK is quasi-OO,
> sort of like XML, with nesting depth limited to 3. PICK is simply an
> example of a database that came in many slightly different versions all of
> which adhered to a core definition, and this compatibility was a good
> thing.
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
>
> On Tue, Jun 29, 2010 at 11:18 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> > 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
>>
> _______________________________________________
> 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
