> -----Original Message----- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 10:30 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] ticket 1147 > > On Mon, 2005-02-28 at 08:48 -0700, Robert Simpson wrote: > > 5. What we do with the schema information or how well we > compute it is > > irrelevant. > > > > No. It is exceedingly relevant if you want any cooperation from > me in addressing the issue. > > There seem to be a lot of people who are emphatic about knowing > which column in which table a value in the result set originated > from. This makes no sense to me. Why do they care? What do > these people do with result set values that originate from > expressions or which are constants? What about the result set > of compound selects or of natural joins where the origin column > is ambiguous? If knowing the original column is so important, > what do people do with those cases? Disallow them? What do > other database engines (PostgreSQL, Oracle, MySQL) do in the way > of revealing the originating column for result set values? Do > they have some mysterious API that I have never seen? > > And why do people care? Can nobody give me a use case where it > is important to know what the originating column for a result > set value is?
1. Calculated fields are not table bound, therefore no schema information is available and the column is read-only for the sake of updateability. 2. For each column of type TK_COLUMN, schema information COULD be extracted if there was some mechanism in place. Compound selects and natural joins in SQLite do not generate columns of type TK_COLUMN -- those columns are computed. Therefore see #1. Here are just a few things I can think of off the top of my head that I cannot do right now for a resultset, but that I *can* do with additional schema information: 1. I cannot determine which (if any) columns in a resultset are primary or foreign keys 2. I cannot determine if a column is autoincrement 3. I can't figure out if any indexes exist on a column 4. I can't determine if the data came out of a view or a table, or neither one, so I can't tell if the data is theoretically updateable. 5. I can't determine what database the query ran against (in the case of ATTACH'd databases) 6. I can't build UPDATE, INSERT or DELETE statements programmatically. (Again, forget the ramifications how hard this may be -- the point is, you can't even TRY because the schema information simply isn't available) 7. I cannot diagram the query to show table usage or hierarchically disassemble views to show table/view usage for a query. Robert
