Puneet,
I am simply pointing out a potential pitfall. Putting up a highway sign
that says "Soft Shoulder" is one way to go about things. Widening the
shoulder and perhaps paving it is another.
Regards
Tim Romano
Swarthmore PA
On Tue, Jun 29, 2010 at 11:56 AM, P Kishor <punk.k...@gmail.com> wrote:
> On Tue, Jun 29, 2010 at 10:46 AM, Tim Romano <tim.romano...@gmail.com>
> wrote:
> > Puneet,
> > I am not trying to give Adobe any sort of primacy; but I wouldn't call
> them
> > unimportant either.
> >
> > The core concern, at least as I see it, is the undesirable effects of
> > sharing data between implementations that do not handle INT and INTEGER
> > primary keys compatibly.
> >
>
> There is no incompatibility. CREATE TABLE t AS SELECT ... is not meant
> to clone a table. Period. Done.
>
> Don't depend on it, don't expect it, don't promote it.
>
>
>
>
>
> > I don't use and won't use the "create table as select ... " syntax, but
> > others might, hence the advisory.
> >
> > Here's a little story: years ago, back in the days of dumb terminals,
> > oncology patients were dying in abnormally high numbers not long after
> > receiving their radiation treatment. Turned out that the software that
> > controlled the radiation dosage was written to be used with a
> dumb-terminal
> > that did not have cursor-positioning keys. It might have been a VT100. I
> > don't recall. You had to hit RETURN to move from field to field. But the
> > hospital had installed a "compatible" terminal that did have these
> > cursor-arrow keys. The hospital technician, ignorant of the
> consequences,
> > were in the habit of using the arrow keys to move the cursor around the
> > radiation dosage and timing screen, and then would key in their values
> and
> > execute the program. The numbers they keyed in were actually hitting the
> > wrong input fields. The users' eyes told them they were on the dosage
> field
> > but they were actually keying in the value for the time, or vice-versa,
> and
> > the patients were being given lethal doses of radiation.
> >
> > I saw this on 60 minutes or 20/20 or some show like that -- I didn't
> write
> > that program or install the compatible terminal. But since then, whenever
> I
> > see the opportunity for things going FUBAR, I will say something.
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> >
> >
> >
> >
> >
> >
> > On Tue, Jun 29, 2010 at 11:10 AM, P Kishor <punk.k...@gmail.com> wrote:
> >
> >> On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano <tim.romano...@gmail.com>
> >> wrote:
> >> > But there may be an argument for making the cloning more precise.
> >>
> >>
> >> The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
> >> table. Not too long ago I encountered the same issue (search the mail
> >> archives). Igor, as usual, explained it succinctly and effectively --
> >>
> >> sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> >> sqlite> INSERT INTO t (b) VALUES ('foo');
> >> sqlite> INSERT INTO t (b) VALUES ('bar');
> >> sqlite> INSERT INTO t (b) VALUES ('baz');
> >> sqlite> SELECT * FROM t;
> >> a b
> >> ---------- ----------
> >> 1 foo
> >> 2 bar
> >> 3 baz
> >> sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
> >> sqlite> SELECT * FROM u;
> >> a b
> >> ---------- ----------
> >> 1.5 foo
> >> 2.5 bar
> >> 3.5 baz
> >> sqlite> .s
> >> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
> >> CREATE TABLE u(a,b TEXT);
> >> sqlite> SELECT Typeof(a) FROM u;
> >> Typeof(a)
> >> ----------
> >> real
> >> real
> >> real
> >> sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
> >> sqlite> INSERT INTO v SELECT * FROM t;
> >> sqlite> SELECT * FROM v;
> >> a b
> >> ---------- ----------
> >> 1 foo
> >> 2 bar
> >> 3 baz
> >> sqlite> DELETE FROM v;
> >> sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
> >> Error: datatype mismatch
> >> sqlite>
> >>
> >>
> >> In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
> >> Instead, create the new table the way you want to, and then use INSERT
> >> to populate it with data from the old table.
> >>
> >>
> >> > 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.
> >> >
> >>
> >>
> >> Right. But, the issue here may be that the sqlite world is much, much
> >> bigger than either the Adobe or the Google worlds. While those two
> >> companies may be a part of the sqlite consortium, sqlite itself runs
> >> on way more platforms and in configurations than Adobe+Google support.
> >>
> >> In any case, I am not even sure if the sql standard calls for CREATE
> >> TABLE t AS SELECT... to produce a clone of a table.
> >>
> >>
> >>
> >>
> >> >
> >> > 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
> >> >
> >>
> >>
> >>
> >> --
> >> Puneet Kishor http://www.punkish.org
> >> Carbon Model http://carbonmodel.org
> >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> >> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> >> -----------------------------------------------------------------------
> >> Assertions are politics; backing up assertions with evidence is science
> >> =======================================================================
> >> _______________________________________________
> >> 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
