Here is what I wrote to Jay earlier this morning but meant to send to the entire list. I am still in the habit of hitting Reply in Google Mail when I should be hitting a different button that also says Reply.
<sent earlier today to Jay> Jay, I agree with 99.44% of what you say. If in the future ALTER TABLE would allow a constraint to be added to a table, then I would agree somewhat less because the CREATE TABLE x as SELECT ... syntax does indeed create a persistent database object. To call that object a "resultset" and not a "table" is true enough, but perhaps only for the time being. </sent earlier today to Jay> @Pavel: I have documented the problem where Adobe treats INT PK as INTEGER PK and returns the wrong rows in joins. I have encountered that problem in the real world and have been bitten in the ass by it. But the wound is healed (I recreated my tables to use INTEGER PK) and my buttock shows now only the faint marks where the cur's fangs sank in. I agree that CREATE TABLE x as SELECT ... does not create a primary key. I was speaking so sloppily there. But it creates a persistent table without a primary key, changing the prototype's INTEGER to INT in the resulting "tabular object". Inasmuch as ALTER TABLE cannot (today) add a PK constraint to that tabular object, it will remain a table without a primary key. However, I do not know how that table-without-key would fare if the SQLite database file were queried by Adobe AIR. My guess is that one would need a bandage. Regards Tim Romano Swarthmore PA On Thu, Jul 1, 2010 at 9:21 AM, Pavel Ivanov <paiva...@gmail.com> wrote: > > No, I am not asking SQLite to "emulate an error" in Adobe's code. Rather > I > > am suggesting this: if SQLite is going to distinguish in any way between > INT > > and INTEGER on primary key definitions, the CREATE TABLE X as SELECT... > > syntax ought not to produce a table with an INT primary key if the > prototype > > had INTEGER. > > Tim, why do you keep insisting on your claim that came out of thin > air? I first thought that it has some ground under it but as Jay said > and as below log shows you are totally wrong. > > SQLite version 3.6.23.1 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table t (id integer primary key, t text); > sqlite> .s > CREATE TABLE t (id integer primary key, t text); > sqlite> insert into t values (1, '1'); > sqlite> insert into t values (999, '999'); > sqlite> create table t_copy as select * from t; > sqlite> .s > CREATE TABLE t (id integer primary key, t text); > CREATE TABLE t_copy(id INT,t TEXT); > sqlite> > > > You see? There's no INT PRIMARY KEY and there will never be. So Adobe > will never be confused with your copied table because it as well as > SQLite will understand that new table doesn't have primary key at all. > And in this case it doesn't matter whether you have INT or INTEGER. So > what are you fighting for after all? > > > Pavel > > On Thu, Jul 1, 2010 at 7:41 AM, Tim Romano <tim.romano...@gmail.com> > wrote: > > If cross-implementation portability is a stated design goal, Adobe's > > departure from the "authoritative" behavior is indeed a bug. If > > cross-implementation portability is not "officially" supported but is > simply > > something that users might expect and attempt, then Adobe's departure > from > > the authoritative behavior is just a pothole on the lonely highway. > > > > I don't know the particulars of how it happened. I think Adobe may not > have > > envisioned SQLite database files created outside of AIR being used in AIR > > apps. They were probably thinking in terms of "local storage" databases > > instantiated and defined through the AIR API. > > > > No, I am not asking SQLite to "emulate an error" in Adobe's code. Rather > I > > am suggesting this: if SQLite is going to distinguish in any way between > INT > > and INTEGER on primary key definitions, the CREATE TABLE X as SELECT... > > syntax ought not to produce a table with an INT primary key if the > prototype > > had INTEGER. > > > > > > Regards > > Tim Romano > > Swarthmore PA > > > > > > > > On Wed, Jun 30, 2010 at 9:24 AM, David Bicking <dbic...@yahoo.com> > wrote: > > > >> > >> > >> All things considered, I would say this is clearly and 100% a bug in > >> Adobe's code. The given SQL nowhere mentioned rowid, yet they are > >> joining on rowid... that has got to be an error. > >> > >> And how is Adobe Air doing this? Did they write there own sql parser? > >> There own VDBE? (Hopefully I got those initials correct.) Are they > >> calling Sqlite or did they write their own library claiming it is > >> compatible with the Sqlite file format? > >> > >> While I don't necessarily disagree with what you are saying, in this > >> example you appear to be asking the Sqlite developers to emulate an > >> error in someone else's code. > >> > >> David > >> > >> PS. Probably should remove my opinions from this, because the only > >> reason I am sending this is because I am curious what the hell Adobe is > >> doing that is causing this confusion. > >> > >> > >> > >> _______________________________________________ > >> 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
