On Sat, Apr 04, 2009 at 05:25:38PM +1100, BareFeet scratched on the wall:
> Hi Jay,
>
> Thanks for the reply.
>
> > Asking SQLite for the insert statement doesn't tell you what the
> > type in the database is
>
> Shouldn't it?
In my opinion, no. For one thing, depending on the format of a literal
representation is a very poor indicator of type. Raw SQL is not
meant to express type-- traditionally that is what the tables are
for. The only way SQLite gets away with it is that SQLite has relatively
few storage classes.
But if you've got date types, time types, timestamps, monetary types,
different string types, etc., etc., like most "real" RDBMS engines,
there is an assumption that nearly any value expressed as a literal
(usually a string) will be modified for storage.
> It doesn't seem that any output mode in the sqlite3
> command will distinguish types.
Perhaps, although that's no different than any other database I've
worked with. The API most definitely does provide all the type
information you need, and that's what really counts.
If you really care about what the type is, you can just ask.
> CSV sometimes quotes text, sometimes doesn't.
CSV is a horrid format. Despite what everyone seems to think, CSV is
about as standardized as a pile of rocks. It works well enough,
until you add commas or new-lines to values. Then you need to
quote. Then you need to escape a quote. Then you need to escape and
escape. Pretty soon everyone has a different idea of the "right" way
to do CSV.
And even if you can agree on a CSV format, it was never meant to
convey any kind of type information.
> And now it seems insert doesn't quote text and not numbers.
Yeah, and in this case I would agree that this seems broken.
> > If the column is declared to have a TEXT affinity, any numbers will
> > be converted to text strings and stored as text. That's true of
> > your INSERT statements as well as the automatically generated
> > statements. The end result in both cases is numbers being stored as
> > strings.
>
> OK, let's change the test to not specify affinity for the column:
> > the code is smart enough to know that it can coerce the number-as-
> > text to a number, since it will be coerced back into a text value
> > upon INSERT.
>
> In this case, that logic doesn't seem to hold. Reinjecting the
> generated 456 insert statement above would insert an integer instead
> of the original text. So the SQLite produced insert statement is
> "wrong" in the sense that running it would give a different value
> (type) than the original.
Yeah. Apparently the code is not smart enough, it was just lucky
enough the first time.
Here's an interesting point, however... "dump" does the right thing.
$ sqlite3
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> create table t ( c );
sqlite> insert into t values ( 123 );
sqlite> insert into t values ('456' );
sqlite> insert into t values ('789z' );
sqlite> .dump t
BEGIN TRANSACTION;
CREATE TABLE t ( c );
INSERT INTO "t" VALUES(123);
INSERT INTO "t" VALUES('456');
INSERT INTO "t" VALUES('789z');
COMMIT;
sqlite> .mode insert
sqlite> select * from t;
INSERT INTO table VALUES(123);
INSERT INTO table VALUES(456);
INSERT INTO table VALUES('789z');
Which brings us back to some interesting ground. If dump did it
incorrectly, I'd clearly say that's a bug. But if dump does it
right, and this is limited to just the mode insert, things are less
clear. Most output modes do not mark or preserve type information.
For example, the default mode list does not mark strings as such, the
value of the rows are simply displayed. From that standpoint, I can
see an both sides of the argument as to if this behavior is
"correct" or not. Given the way all the other normal output modes
work, I'd say there is a strong case it is correct.
> As I mentioned, there therefore doesn't appear to be any way to get
> the command line to show the data in a table with implied type.
Not as an output format, but as I did in my last post, you can simply
ask SQLite to display the types in a different output column:
select MyData, typeof(MyData) from MyTable;
That will produce a two column output: first column with values,
second column with types. The API, as well, provides hard and fast
type information.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
