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
