On May 29, 2004, at 9:35 AM, Will Leshner wrote:
On May 29, 2004, at 7:31 AM, Tito Ciuro wrote:
SQLite stores the original SQL statement that created the table. This statement can be retrieved, as you well suggested, querying sqlite_master. The data is there, but SQLite doesn't do its homework when PRAGMA show_datatypes = ON is set. Instead of reporting in the result set that x is a INTEGER PRIMARY KEY, it returns INTEGER and chops out the rest.
Personally, I don't think of PRIMARY KEY as part of the type. I think of it as a constraint. I'm not sure I want to have to distinguish between INTEGER and INTEGER PRIMARY KEY as types. They are both INTEGER. I think if SQLite reported an INTEGER column as INTEGER PRIMARY KEY, then I would have more work to do to recover the actual type.
My two bits follow --
except, if you know something is INTERGER PRIMARY KEY, you know two things for certain -- that it is of type INTEGER, and that it is a PRIMARY KEY. The information is complete, albeit, more than what _you_ want. Otoh, if Tito sees something as INTEGER, he knows it is of type INTEGER, but he has no idea whether it is a PRIMARY KEY or not. The information is not complete as far as he is concerned.
It is a matter of debate whether PRIMARY KEY is a constraint or not. I would agree with you that it is a constraint, but then, arguably INTEGER is also a constraint because it constrains what kind of data can go into that column.
Since the CREATE statements allows one to specify a column type as INTEGER PRIMARY KEY, by context I would be more likely to consider it a column type than a column type + a constraint. I would treat a INTEGER DEFAULT (5) also as the complete information to figure out the default type.
I believe that folks designing applications of any sort (and, it would seem that most users of SQLite on this list are application designers rather than just managers of data that happen to reside in SQLite) have a continuing and real need to easily determine the column types. If these types happen to include "constraints" and "default values," great, we want to know those also. While the info in sqlite_master does give the "schema," it is arguably a tad inconvenient to get at. Now I have learned that various PRAGMAs allow me to do just that. So, they should do it completely.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
