Hi Richard, Thank you for replying personally. Apologies in advance if I use the wrong terminology. I haven't looked at SQLite since the previous discussion in this thread, as I've been waiting for this needed "feature".
I just have to say you treat ROWID like it's your quasimodo. A thing you need to cover up and pretend doesn't exist. Well, it's there. As you say, you're supporting it for at least three more decades. Why not document it properly? It deserves more than a highly obtuse mention on your Datatypes page. Why not make a PRAGMA statement to allow tools to introspect the thing? Please. The code is already there for you. The alternative to supporting table_ipk pragma is to support this practically undocumented method, which I'll paste again in full. What you, Richard Hipp, are saying to me, is that you propose to keep these opcodes outputs from these EXPLAIN statements identical for 30 years: sqlite> create table rowidPK ...> ( ...> x integer, ...> y text, ...> primary key (x) ...> ); sqlite> create table separatePK ...> ( ...> x integer primary key desc, ...> y text ...> ); sqlite> pragma table_info(rowidPK); cid|name|type|notnull|dflt_value|pk 0|x|integer|0||1 1|y|text|0||0 sqlite> pragma table_info(separatePK); cid|name|type|notnull|dflt_value|pk 0|x|integer|0||1 1|y|text|0||0 sqlite> explain select x from rowidPK not indexed; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 00 Start at 8 1 OpenRead 0 3 0 0 00 root=3 iDb=0; rowidPK 2 Explain 0 0 0 SCAN TABLE rowidPK 00 3 Rewind 0 7 0 00 4 Rowid 0 1 0 00 r[1]=rowid 5 ResultRow 1 1 0 00 output=r[1] 6 Next 0 4 0 01 7 Halt 0 0 0 00 8 Transaction 0 0 10 0 01 usesStmtJournal=0 9 Goto 0 1 0 00 sqlite> explain select x from separatePK not indexed; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 00 Start at 8 1 OpenRead 0 4 0 1 00 root=4 iDb=0; separatePK 2 Explain 0 0 0 SCAN TABLE separatePK 00 3 Rewind 0 7 0 00 4 Column 0 0 1 00 r[1]=separatePK.x 5 ResultRow 1 1 0 00 output=r[1] 6 Next 0 4 0 01 7 Halt 0 0 0 00 8 Transaction 0 0 10 0 01 usesStmtJournal=0 9 Goto 0 1 0 00 sqlite> Because there is no other reliable way to access this information. There's currently no way to reliably access IPK information. Tools that are built around Sqlite need to have access to the specific information about what its columns contain. Existing tools and libraries try very hard to support SQLite but they are buggy and error prone. Why? Simply because of the lack of any way to reasonably introspect. The advice I was give in this thread was to write my own SQL parser -- I was literally given that advice here. Yes. I know you've done that, but it doesn't make sense for everyone else to too. table_ipk pragma is not designed as some new fancy add-on feature like JSON storage or 4D coordinate data; the feature is precisely to support the database and its existing design choices. It's because you're supporting the legacy of SQLite that tool developers need to be able to introspect whether a table has an IPK column. Parsing explain statements just doesn't make sense. You want to support the exact output of those EXPLAIN statements for 30 years? Not having a table_ipk pragma would only make sense if you were planning a complete redesign which had different datatype behaviors. As you clearly planning NOT to do that (and fairly so), it's frankly insane that you wouldn't consider adding a way to introspect the existence of an IPK column. All I'm asking is you embrace the decisions of the past and make them visible to users and tool makers that need to introspect databases. (And seriously, please including IPK as an actual type on https://www.sqlite.org/datatype3.html. Embrace IPK. Let it out into the light. Please. If you're going to have a fancy table showing how INT has an affinity for INTEGER, state the giant hunchback of an exception somewhere in the same table or at least directly under it.) Please just make the datatypes of your fields programmatically accessible. I want to keep using SQLite. I understand it has a large legacy to support. But you can do that without fucking over tool developers and all the users who use or wish to use those tools such as myself. Thanks for listening. Peter Halasz. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
