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
