Re: [sqlite] ticket 1147
On 2/28/05, Dennis Cote <[EMAIL PROTECTED]> wrote: > Yes, it sure would be better to use an API. > > The second would naturally become sqlite3_column_table(), and the fourth > sqlite3_column_database(). > > Unfortunately, the natural name for the third item, > sqlite3_column_name(), is already used. However, rather than the column > name, it returns the usual column heading. This is sometime the column > name alone, and sometimes the table name and the column name separated > by a period, depending upon the type of query, joined or not, and the > column name pragma settings. It's too bad this wasn't called > sqlite3_column_heading(). > > Anyway, to maintain backwards compatibility the new API should probably > be sqlite3_column_short_name() to match the intent of the > short_column_names pragma. It would always return the short name of the > column. > Dennis Cote Has anyone looked into providing this or has a ticket been entered? I've been looking forward to something like this since it seemed like the obvious thing to do, but haven't heard a peep about it since. Wrapper writiers are sure to love this. -- Joel Lucsy "The dinosaurs became extinct because they didn't have a space program." -- Larry Niven
RE: [sqlite] ticket 1147
The user using your wrapper needs to be smart enough to know what he is doing. Your stuff should be merely the conduit not the editor. If I attempted to use a wrapper that "messed" with my SQL code, it would immediately be my ex-wrapper. Fred -Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:43 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] ticket 1147 > I'll third Dr. Hipp's statement. > > I have my own wrappers (in Perl), made for public > consumption, and never had problems with returned column names. > > Simply put, the elegant solution for wrapper authors is to > always use 'as' to explicitly define the column names you > want. You always know how these names map to original table > columns because you explicitly said so. > > Insisting on using default names all the time is for uber-lazy users. > > -- Darren Duncan > As a wrapper writer, I cannot control the SQL that a user of my wrapper is passing in. Tim
RE: [sqlite] ticket 1147
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: 28 February 2005 19:25 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] ticket 1147 > Wouldn't it be better to provide this information with a new > API rather than depend on a column naming convention? That > would avoid ambiguity in cases where users create dodgy > column names that contain characters like space and '.' If > such a new API appears soon, would people (please!) stop > using those short_column_names and long_column_names pragmas? Yes, of course. I'd rather use an API than rely on a pragma. Tim
Re: [sqlite] ticket 1147
> > Simply put, the elegant solution for wrapper authors is to always use > 'as' to explicitly define the column names you want. You always know > how these names map to original table columns because you explicitly > said so. It isn't as simply as that. I.e. within the Delphi wrapper users can apply theire own sql statement and it is binded to data-aware components showing content. To be able to do so, Delphi must assume certain data-types which is extracted from the metadata supplied by the api set. If duplicate names are supplied (like it is possible now) the wrapper must detect and change names, i.e. by adding a suffix _number. The 'as' surely helps a lot, but wrappers cannot always rely on that. I personally do not have any problems with the current sqlite implementation. But I suggest that before publishing the official release a pre-release is published for wrapper writers to allow them to addapt early. I.e. one or two weeks. I don't know about others, but we received a lot of complaints about bugs in our components using the latest sqlite release. It supprised us, needed to investigate, change sources and re-publish. It wasn't a big deal after all, but some of our users where frustrated by the isue. I would welcome an api set for retrieval of sql query metadata. The fact that users can change pragma will not have influence on the working of wrappers any more then. albert drent aducom software
RE: [sqlite] ticket 1147
Sure, an API for returning result set info would be fine, and more convenient than these pragmas, which for one thing are stateful, and thus hell for wrapper writers, which need to assume that only the *wrapper* may set these pragmas. Also, in order to avoid API explosion, I feel that a single api function, named , say, sqlite3_column_origin, could return all relevant information, such as database, table, column names, and as a bonus, primary key and "required" (not NULL) flags. Still, and in order not to lose focus on ticket 1147, I reported a *bug* in the current implementation. The bug *is* there, and very easy to reproduce. I feel that this should either be fixed, or the pragmas removed altogether. From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Mon 2/28/2005 9:24 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] ticket 1147 On Mon, 2005-02-28 at 11:54 -0700, Robert Simpson wrote: > Column Name - The name of the column as specified in the SELECT clause and > what SQLite already generates > Base Table - The base table the column came from or NULL if the column was > computed > Base Column - The base column of the table the column came from or NULL if > the column was computed > Catalog - The database the column came from or NULL if the column was > computed. > OK. This is progress. Now I understand that people need the database, table, and column that resultset values originate from in order to automatically construct an appropriate UPDATE statement. That makes sense. Wouldn't it be better to provide this information with a new API rather than depend on a column naming convention? That would avoid ambiguity in cases where users create dodgy column names that contain characters like space and '.' If such a new API appears soon, would people (please!) stop using those short_column_names and long_column_names pragmas? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] ticket 1147
D. Richard Hipp wrote: On Mon, 2005-02-28 at 11:54 -0700, Robert Simpson wrote: Column Name - The name of the column as specified in the SELECT clause and what SQLite already generates Base Table - The base table the column came from or NULL if the column was computed Base Column - The base column of the table the column came from or NULL if the column was computed Catalog - The database the column came from or NULL if the column was computed. OK. This is progress. Now I understand that people need the database, table, and column that resultset values originate from in order to automatically construct an appropriate UPDATE statement. That makes sense. Wouldn't it be better to provide this information with a new API rather than depend on a column naming convention? That would avoid ambiguity in cases where users create dodgy column names that contain characters like space and '.' Yes, it sure would be better to use an API. The second would naturally become sqlite3_column_table(), and the fourth sqlite3_column_database(). Unfortunately, the natural name for the third item, sqlite3_column_name(), is already used. However, rather than the column name, it returns the usual column heading. This is sometime the column name alone, and sometimes the table name and the column name separated by a period, depending upon the type of query, joined or not, and the column name pragma settings. It's too bad this wasn't called sqlite3_column_heading(). Anyway, to maintain backwards compatibility the new API should probably be sqlite3_column_short_name() to match the intent of the short_column_names pragma. It would always return the short name of the column. I have a question about how column aliases will be handled. Given a table create table t (a, b); what is to be returned by select a as b from t; The column name is obviously 'b' because of the alias, but what should be returned as the base name, 'a' or 'b'? Or do all aliases effectively make their column a computed value so that it is read only, and therefore all the new API functions return a null string? I think this is the issue the proposed real_column_names pragma was intended to address. With the column name and table name the user can get other useful information using pragma table_info(). Note, you need to use "database.table" as the table name for this pragma if the table is a duplicated name in an attached database. So perhaps we need to add an sqlite3_column_full_table_name() API to return this combination as well. ;-) just kidding... If such a new API appears soon, would people (please!) stop using those short_column_names and long_column_names pragmas? For symmetry with the sqlite3_column_short_name() API, you could add an sqlite3_column_full_name() API that always returns the full name of the column including the table name (i.e. what was supposed to be returned as the column name with the full_column_names pragma set). Even without the new API functions, the current sqlite3_column_name() API should probably be fixed so that it obeys the column name pragmas, as they are documented now, for backward compatibility. Dennis Cote
Re: [sqlite] ticket 1147
For me it is not important to know from which table the column comes, but it is a must to have unique column names - because I address all columns by their names. I could also use the column order but this would lead to worse readability and maintainability. Therefore my wrapper protests when it encounters two columns with the same name. I never thought about * as a dangerous thing. The query is shorter and there are less places to modify when I change or add columns. But it is true that VIEWs work with * other way than SELECTs and also because of all this discussion I think I will refrain from using *. The query I posted was a very quick draft of the real query, it was SELECT * FROM BinaryTarget INNER JOIN TargetDescription ON BinaryTarget.FeatureItemId = TargetDescription.FeatureItemId; and I also tried SELECT TargetDescription.*, BinaryTarget.* which did not help. I than thought these are bugs in SQLite and did not try to change the queries. But now I see that even if the parser was modified to return the column names working for me now, it is a very vulnerable part which could easily change in next versions. Jakub D. Richard Hipp wrote: 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?
RE: [sqlite] ticket 1147
> I'll third Dr. Hipp's statement. > > I have my own wrappers (in Perl), made for public > consumption, and never had problems with returned column names. > > Simply put, the elegant solution for wrapper authors is to > always use 'as' to explicitly define the column names you > want. You always know how these names map to original table > columns because you explicitly said so. > > Insisting on using default names all the time is for uber-lazy users. > > -- Darren Duncan > As a wrapper writer, I cannot control the SQL that a user of my wrapper is passing in. Tim
RE: [sqlite] ticket 1147
Robert Simpson wrote: >> -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 > > [snip] >> 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? > > ODBC : SQLColAttributes() or SQLColAttribute() > OLEDB: IColumnsRowset interface > MySQL: mysql_stmt_result_metadata() -- didn't read too much > into this one, but I think it's the right one PostgreSQL for libpq 27.3.2. Retrieving Query Result Information These functions are used to extract information from a PGresult object that represents a successful query result (that is, one that has status PGRES_TUPLES_OK). For objects with other status values they will act as though the result has zero rows and zero columns. PQntuples Returns the number of rows (tuples) in the query result. int PQntuples(const PGresult *res); PQnfields Returns the number of columns (fields) in each row of the query result. int PQnfields(const PGresult *res); PQfname Returns the column name associated with the given column number. Column numbers start at 0. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear. char *PQfname(const PGresult *res, int column_number); NULL is returned if the column number is out of range. PQfnumber Returns the column number associated with the given column name. int PQfnumber(const PGresult *res, const char *column_name); -1 is returned if the given name does not match any column. The given name is treated like an identifier in an SQL command, that is, it is downcased unless double-quoted. For example, given a query result generated from the SQL command select 1 as FOO, 2 as "BAR"; we would have the results: PQfname(res, 0) foo PQfname(res, 1) BAR PQfnumber(res, "FOO")0 PQfnumber(res, "foo")0 PQfnumber(res, "BAR")-1 PQfnumber(res, "\"BAR\"")1 PQftable Returns the OID of the table from which the given column was fetched. Column numbers start at 0. Oid PQftable(const PGresult *res, int column_number); InvalidOid is returned if the column number is out of range, or if the specified column is not a simple reference to a table column, or when using pre-3.0 protocol. You can query the system table pg_class to determine exactly which table is referenced. The type Oid and the constant InvalidOid will be defined when you include the libpq header file. They will both be some integer type. PQftablecol Returns the column number (within its table) of the column making up the specified query result column. Query-result column numbers start at 0, but table columns have nonzero numbers. int PQftablecol(const PGresult *res, int column_number); Zero is returned if the column number is out of range, or if the specified column is not a simple reference to a table column, or when using pre-3.0 protocol. PQfformat Returns the format code indicating the format of the given column. Column numbers start at 0. int PQfformat(const PGresult *res, int column_number); Format code zero indicates textual data representation, while format code one indicates binary representation. (Other codes are reserved for future definition.) PQftype Returns the data type associated with the given column number. The integer returned is the internal OID number of the type. Column numbers start at 0. Oid PQftype(const PGresult *res, int column_number); You can query the system table pg_type to obtain the names and properties of the various data types. The OIDs of the built-in data types are defined in the file src/include/catalog/pg_type.h in the source tree. PQfmod Returns the type modifier of the column associated with the given column number. Column numbers start at 0. int PQfmod(const PGresult *res, int column_number); The interpretation of modifier values is type-specific; they typically indicate precision or size limits. The value -1 is used to indicate "no information available". Most data types do not use modifiers, in which case the value is always -1. PQfsize Returns the size in bytes of the column associated with the given column number. Column numbers start at 0. int PQfsize(const PGresult *res, int column_number); PQfsize returns the space allocated for this column in a database row, in other words the size of the server's internal representation of the data type. (Accordingly, it is not really very useful t
Re: [sqlite] ticket 1147
At 8:32 AM -0500 2/28/05, Clay Dowling wrote: D. Richard Hipp said: On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: I understand that this "column names" issue is becoming a pain for the sqlite authors, but OTOH, it is very important for wrapper authors... Why? Why does anybody care what the column names in the result are? What are the column names used for other than to print a header at the top of a table for human-readable output? I'll second Dr. Hipp's statement. I have my own wrapper that I've written for internal use and seem to be getting along just fine with column names as they are. I do expect the developer (me, in this case) to be smart enough refer to a column by the same name both when setting the SQL and when retrieving the results set. I'm curious just why you feel that you need something different than that. I can't imagine that I'd take too kindly to a wrapper that thought it knew better than me what I wanted to call the columns. I'll third Dr. Hipp's statement. I have my own wrappers (in Perl), made for public consumption, and never had problems with returned column names. Simply put, the elegant solution for wrapper authors is to always use 'as' to explicitly define the column names you want. You always know how these names map to original table columns because you explicitly said so. Insisting on using default names all the time is for uber-lazy users. -- Darren Duncan
RE: [sqlite] ticket 1147
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 12:25 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] ticket 1147 > > On Mon, 2005-02-28 at 11:54 -0700, Robert Simpson wrote: > > Column Name - The name of the column as specified in the > SELECT clause and > > what SQLite already generates > > Base Table - The base table the column came from or NULL if > the column was > > computed > > Base Column - The base column of the table the column came > from or NULL if > > the column was computed > > Catalog - The database the column came from or NULL if the > column was > > computed. > > > > OK. This is progress. Now I understand that people need the > database, > table, and column that resultset values originate from in order to > automatically construct an appropriate UPDATE statement. That makes > sense. > > Wouldn't it be better to provide this information with a new API > rather than depend on a column naming convention? That would avoid > ambiguity in cases where users create dodgy column names that contain > characters like space and '.' If such a new API appears soon, would > people (please!) stop using those short_column_names and > long_column_names pragmas? Yes absolutely, a new API call would be ideal. I'll stop short of dancing naked in the streets, but I will do a jig in my office. :) Robert
RE: [sqlite] ticket 1147
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 12:26 PM > To: sqlite-users@sqlite.org > Cc: sqlite-users@sqlite.org > Subject: RE: [sqlite] ticket 1147 > > > Metadata should be on-demand, and not automatically > returned. As far as a > > standard is concerned, OLEDB and ODBC do it differently and > I'd have to > > look it up. > > Here are the meta data standards: > > OLEDB Scheme Rowsets > http://msdn.microsoft.com/library/default.asp?url=/library/en- > us/oledb/htm/oledbschema_rowsets.asp > > SQL - 92 INFORMATION SCHEMA VIEWS > http://msdn.microsoft.com/library/default.asp?url=/library/en- > us/tsqlref/ts_ia-iz_4pbn.asp > > However, concerning the SQLite.NET provider I recommend putting your > efforts into > > Schemas in ADO.NET 2.0 (We're already playing with them) > http://msdn.microsoft.com/library/en-us/dnvs05/html/adonet2schemas.asp > > Since SQLite has no OLEDB driver forget the schema rowset, you could > create pragma's that match the SQL-92 INFORMATION_SCHEMA Views. That is general metadata information, not metadata information on a specific query. As in my previous post, ODBC does per-query metadata via SQLColAttribute() or the old SQLColAttributes() API, OLEDB uses IColumnsRowset, and other databases implement it in other fashions of which I am currently unaware but would be happy to investigate. Currently, I implement just about all the generic schema functionality in ADO.NET 2.0 for SQLite. The experimental provider can be found at http://www.blackcastlesoft.com/files/sqlitereadme.htm I'm currently in the process of moving the code to Sourceforge. Robert
RE: [sqlite] ticket 1147
> -Original Message- > From: Robert Simpson [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 12:55 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] ticket 1147 > > > -Original Message- > > From: Andrew Piskorski [mailto:[EMAIL PROTECTED] > > Sent: Monday, February 28, 2005 11:34 AM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] ticket 1147 > > > > On Mon, Feb 28, 2005 at 11:16:33AM -0700, Robert Simpson wrote: > > > > > 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: > > > > Do you mean that you would like additional schema > information added to > > the system tables, so that you could separately figure this sort of > > stuff out by querying them? > > No. There is sufficient information there already. > > > Or do you want this metadata returned with each and every resultset? > > Meaning, essentially, have the db (optionally) return a metadata > > resultset along with each normal data resultset. Is there some > > standard precisely specifying what this metadata resultset > should look > > like? > > Metadata should be on-demand, and not automatically returned. > As far as a standard is concerned, OLEDB and ODBC do it > differently and I'd have to look it up. At a minimum the > only columns required to implement this are: > > Column Name - The name of the column as specified in the > SELECT clause and what SQLite already generates Base Table - > The base table the column came from or NULL if the column was > computed Base Column - The base column of the table the > column came from or NULL if the column was computed Catalog - > The database the column came from or NULL if the column was computed. > > Given the above, where base column is not null, one can > retrieve the extended properties of those column(s) and build > the extended metadata for them. > > In a way, it's almost like a deviation from the EXPLAIN > keyword. Call it "METADATA" and it returns a row for each > column in the select clause containing the above information. > > Robert Although I know nothing of the SQLite implementation details, it seems that this could be an extension along the lines of the existing sqlite3_column_decltype() function. sqlite3_column_decltype() returns the type, as a string, of a result column exactly as it was declared in the CREATE statement. Something like sqlite3_column_origname() could return the originating "..". Or it could be split up into 3 functions: sqlite3_column_origdatabase(), sqlite3_column_origtable(), and sqlite3_column_origname(). Tim
RE: [sqlite] ticket 1147
On Mon, 2005-02-28 at 11:54 -0700, Robert Simpson wrote: > Column Name - The name of the column as specified in the SELECT clause and > what SQLite already generates > Base Table - The base table the column came from or NULL if the column was > computed > Base Column - The base column of the table the column came from or NULL if > the column was computed > Catalog - The database the column came from or NULL if the column was > computed. > OK. This is progress. Now I understand that people need the database, table, and column that resultset values originate from in order to automatically construct an appropriate UPDATE statement. That makes sense. Wouldn't it be better to provide this information with a new API rather than depend on a column naming convention? That would avoid ambiguity in cases where users create dodgy column names that contain characters like space and '.' If such a new API appears soon, would people (please!) stop using those short_column_names and long_column_names pragmas? -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] ticket 1147
> Metadata should be on-demand, and not automatically returned. As far as a > standard is concerned, OLEDB and ODBC do it differently and I'd have to > look it up. Here are the meta data standards: OLEDB Scheme Rowsets http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbschema_rowsets.asp SQL - 92 INFORMATION SCHEMA VIEWS http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp However, concerning the SQLite.NET provider I recommend putting your efforts into Schemas in ADO.NET 2.0 (We're already playing with them) http://msdn.microsoft.com/library/en-us/dnvs05/html/adonet2schemas.asp Since SQLite has no OLEDB driver forget the schema rowset, you could create pragma's that match the SQL-92 INFORMATION_SCHEMA Views. Mike Griffin MyGeneration Software http://www.mygenerationsoftware.com
RE: [sqlite] ticket 1147
> -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 [snip] > 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? ODBC : SQLColAttributes() or SQLColAttribute() OLEDB: IColumnsRowset interface MySQL: mysql_stmt_result_metadata() -- didn't read too much into this one, but I think it's the right one
RE: [sqlite] ticket 1147
> -Original Message- > From: Andrew Piskorski [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 11:34 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ticket 1147 > > On Mon, Feb 28, 2005 at 11:16:33AM -0700, Robert Simpson wrote: > > > 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: > > Do you mean that you would like additional schema information added to > the system tables, so that you could separately figure this sort of > stuff out by querying them? No. There is sufficient information there already. > Or do you want this metadata returned with each and every resultset? > Meaning, essentially, have the db (optionally) return a metadata > resultset along with each normal data resultset. Is there some > standard precisely specifying what this metadata resultset should look > like? Metadata should be on-demand, and not automatically returned. As far as a standard is concerned, OLEDB and ODBC do it differently and I'd have to look it up. At a minimum the only columns required to implement this are: Column Name - The name of the column as specified in the SELECT clause and what SQLite already generates Base Table - The base table the column came from or NULL if the column was computed Base Column - The base column of the table the column came from or NULL if the column was computed Catalog - The database the column came from or NULL if the column was computed. Given the above, where base column is not null, one can retrieve the extended properties of those column(s) and build the extended metadata for them. In a way, it's almost like a deviation from the EXPLAIN keyword. Call it "METADATA" and it returns a row for each column in the select clause containing the above information. Robert
RE: [sqlite] ticket 1147
> -Original Message- > From: Andrew Piskorski [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 11:17 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ticket 1147 > > On Mon, Feb 28, 2005 at 09:58:15AM -0800, Tim McDaniel wrote: > > > Given a specific SELECT statement, ADO.NET has the capability to > > automatically build the corresponding INSERT, UPDATE, and DELETE > > statements, so the user can insert/update/delete values/rows in the > > resultset and have those modifications sent back to the > database. But > > > (I wrote the original ADO.NET SQLite wrapper on sourceforge) > > Hm, off topic, but I'm curious: Presumably ADO.NET does not take a > lock out on all those rows and wait around holding it while the human > user goes to lunch. So, when the user changes values and then submits > them, does ADO.NET somehow correctly check that another transaction > has not modified those same rows in the meantime? And what does it do > then, throw a "Someone else has changed your data in the db" > exception? When ADO.NET generates the update statement, it requires that the select clause include the primary key of the table. If no primary key was selected, then an update clause cannot be generated. Furthermore, when it generates the update statement, the where clause includes all the current values as they existed at the time of the query such that if any field has changed between the time of the query and the time of the update, the update will fail. Programmatically generating insert/update/delete statements is just part of the reasoning behind being able to get schema information for a select clause, however. Robert
RE: [sqlite] ticket 1147
Well, I can help here a little, having provided meta data for 11 different databases via our product MyGeneration. We do support SQLite too, including foreignkeys and all the good stuff. However, concerning columns contained results sets from say a select statement that's another story, you're lucky if there is any meta data all about them, and origination is unheard of. You can reverse engineer the language types and the DbType's however easilly enough, as far a computed columns and constant columns there isn't a database on the planet that will give you that concerning a result set, now from a table or a view that's a different story. >> -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 > > >
RE: [sqlite] ticket 1147
> -Original Message- > From: Jay [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 11:08 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? > > > > > > > One example, ADO.NET (Robert S., correct me if I'm wrong here): > > > > Given a specific SELECT statement, ADO.NET has the capability to > > automatically build the corresponding INSERT, UPDATE, and DELETE > > statements, so the user can insert/update/delete values/rows in the > > resultset and have those modifications sent back to the database. > > But > > in order to facilitate this, it must have a direct mapping between > > resultset columns and the originating columns in the database. > > > > Tim McDaniel > > (I wrote the original ADO.NET SQLite wrapper on sourceforge) > > Interesting! > How do they handle calculated columns and constraints and such? > Does it just fail? Any computed column lacking a base table and base column mapping is marked "read only" and is ignored by ADO for the sake of Generating INSERT/UPDATE statements. If there is no column in the select clause that backs to a table, then the statements will fail to generate. If there is no column that has a primary key, then the update statement and delete statements cannot be automatically generated either. Robert
RE: [sqlite] ticket 1147
Jay said: > Interesting! > How do they handle calculated columns and constraints and such? > Does it just fail? Most ADO wrappers cough up a hairball and refuse to proceed. That is, coincidentally, what should be done when you're trying to update a dataset that resulted from a join. Unless I'm completely missing something, actually updating a dataset is restricted to use in cursors, and SQLite notably does not support cursors. What people decide to do with their wrappers is their own business, but I for one would much rather see Dr. Hipp's time devoted to new features such as the previously mentioned true ALTER TABLE syntax than chasing down bugs that allow people to support dodgy SQL. For those of you having the problems with loading data into a hash, feel free to contact me off-list and I'll help you get around the problem. The solution is fairly easy. Clay Dowling -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] ticket 1147
On Mon, Feb 28, 2005 at 11:16:33AM -0700, Robert Simpson wrote: > 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: Do you mean that you would like additional schema information added to the system tables, so that you could separately figure this sort of stuff out by querying them? Or do you want this metadata returned with each and every resultset? Meaning, essentially, have the db (optionally) return a metadata resultset along with each normal data resultset. Is there some standard precisely specifying what this metadata resultset should look like? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] ticket 1147
On 28 Feb 2005, at 07:38, D. Richard Hipp wrote: On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: I understand that this "column names" issue is becoming a pain for the sqlite authors, but OTOH, it is very important for wrapper authors... Why? Why does anybody care what the column names in the result are? What are the column names used for other than to print a header at the top of a table for human-readable output? This seems a bit short sighted. Often the column names are used programatically either for auto-generated APIs, or for a simple key to access the data. I don't mind what you decide on, but please don't break backwards compatibility - you're breaking people's applications when you do that. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] ticket 1147
On Mon, Feb 28, 2005 at 09:58:15AM -0800, Tim McDaniel wrote: > Given a specific SELECT statement, ADO.NET has the capability to > automatically build the corresponding INSERT, UPDATE, and DELETE > statements, so the user can insert/update/delete values/rows in the > resultset and have those modifications sent back to the database. But > (I wrote the original ADO.NET SQLite wrapper on sourceforge) Hm, off topic, but I'm curious: Presumably ADO.NET does not take a lock out on all those rows and wait around holding it while the human user goes to lunch. So, when the user changes values and then submits them, does ADO.NET somehow correctly check that another transaction has not modified those same rows in the meantime? And what does it do then, throw a "Someone else has changed your data in the db" exception? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
RE: [sqlite] ticket 1147
> -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
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? > > > > One example, ADO.NET (Robert S., correct me if I'm wrong here): > > Given a specific SELECT statement, ADO.NET has the capability to > automatically build the corresponding INSERT, UPDATE, and DELETE > statements, so the user can insert/update/delete values/rows in the > resultset and have those modifications sent back to the database. > But > in order to facilitate this, it must have a direct mapping between > resultset columns and the originating columns in the database. > > Tim McDaniel > (I wrote the original ADO.NET SQLite wrapper on sourceforge) Interesting! How do they handle calculated columns and constraints and such? Does it just fail? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] ticket 1147
On Mon, Feb 28, 2005 at 05:38:24PM -, Tim Anderson wrote: > > > SELECT Name, Title, Books.ID, Authors.ID FROM Books inner > > join Authors > > > on Books.AuthorID = Authors.ID ORDER BY Authors.Name, Books.Title; > Not quite. You wanted the column called "Books.ID" so that was > specified. Is it unreasonable to then expect to retrieve it as No, it was not. THIS specifies that you want the column name to be "Books.ID": select Books.ID as "Books.ID" from ... I suspect that is per the SQL standard, although I have not checked. Note that "." is not normally allowed in column names so you have to surround it in double quotes. Btw, I haven't tried this in SQLite but that's how it works in Oracle, e.g.: SQL> select u.username from user_users u; USERNAME -- DDR_DEV SQL> select u.username as "u.username" from user_users u; u.username -- DDR_DEV SQL> select u.username as u.username from user_users u; select u.username as u.username from user_users u * ERROR at line 1: ORA-00923: FROM keyword not found where expected -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
RE: [sqlite] ticket 1147
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 11: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? > One example, ADO.NET (Robert S., correct me if I'm wrong here): Given a specific SELECT statement, ADO.NET has the capability to automatically build the corresponding INSERT, UPDATE, and DELETE statements, so the user can insert/update/delete values/rows in the resultset and have those modifications sent back to the database. But in order to facilitate this, it must have a direct mapping between resultset columns and the originating columns in the database. Tim McDaniel (I wrote the original ADO.NET SQLite wrapper on sourceforge)
Re: [sqlite] ticket 1147
D. Richard Hipp wrote: Can nobody give me a use case where it is important to know what the originating column for a result set value is? Any wrapped or API that loads row values into a hash, and if some columns have exactly the same names then they would overwrite information in the hash. But then again I think solutions to this lies in area of programmer designing tables with unique column names and/or using aliasing. regards, Alex
RE: [sqlite] ticket 1147
> -Original Message- > From: Andrew Piskorski [mailto:[EMAIL PROTECTED] > Sent: 28 February 2005 17:28 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ticket 1147 > > On Mon, Feb 28, 2005 at 05:05:37PM -, Tim Anderson wrote: > > > SELECT Name, Title, Books.ID, Authors.ID FROM Books inner > join Authors > > on Books.AuthorID = Authors.ID ORDER BY Authors.Name, Books.Title; > Well, that looks like correct behavior to me. If you wanted > the column to be called something different you should have > done "... Authors.ID as Authors_ID" or something like that in > your query. So if these various db interface layers need special features > from SQLite in order to rewrite those column names Not quite. You wanted the column called "Books.ID" so that was specified. Is it unreasonable to then expect to retrieve it as "Books.ID"? More important, this used to work, so 3.1.3 broke code. No big deal IMO but annoying. Tim
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? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] ticket 1147
On Mon, Feb 28, 2005 at 05:05:37PM -, Tim Anderson wrote: > SELECT Name, Title, Books.ID, Authors.ID FROM Books inner join Authors > on Books.AuthorID = Authors.ID ORDER BY Authors.Name, Books.Title; > > In this case, the query is unambiguous, but by default Sqlite returns > the column names as: > > Name > Title > ID > ID Well, that looks like correct behavior to me. If you wanted the column to be called something different you should have done "... Authors.ID as Authors_ID" or something like that in your query. So if these various db interface layers need special features from SQLite in order to rewrite those column names, then it must be because the users of those interface layers are writing bad queries, queries that fail to specify the unique column names that the users actually needs or wants. Why is that? Having the user application correctly tack on an "as my_col_name" to the approriate columns in the query is pretty trivial, so why don't these user applications correctly do that? Is it genuinely infeasible for some reason? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] ticket 1147
Edward Macnaghten wrote: I use column names. I have created a wrapper around sqlite3 (and other SQL engines) in a developmeny environment I have written to enable the programmer (or user for that matter) to access an SQL result set using an object where the property names are the column names. Same here -- loading row data into a hash and using column names to access values. However - having duplicate column names (without aliasing them), or using an unqualified "*" when querying a select statement with more than one table in it is really bad practice - and I do not think the ANSI standard specifies how that should be dealt with so I do not think sqlite CAN do it "wrong" in that case. My view is that of trying to avoid having duplicate column names in the first place (when designing schemas), but more importantly I am always wary of using * in the first place unless its throw away SQL in a GUI client that can handle all these things and more importantly where I won't care about exact column name conflicts as I select data for visual purposes. regards Alex
RE: [sqlite] ticket 1147
> -Original Message- > From: Edward Macnaghten [mailto:[EMAIL PROTECTED] > Sent: 28 February 2005 16:47 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ticket 1147 > However - having duplicate column names (without aliasing > them), or using an unqualified "*" when querying a select > statement with more than one table in it is really bad > practice - and I do not think the ANSI standard specifies how > that should be dealt with so I do not think sqlite CAN do it > "wrong" in that case. This isn't the only case at issue though. The reason I had to amend my simple Delphi wrapper was to deal with queries such as this one: SELECT Name, Title, Books.ID, Authors.ID FROM Books inner join Authors on Books.AuthorID = Authors.ID ORDER BY Authors.Name, Books.Title; In this case, the query is unambiguous, but by default Sqlite returns the column names as: Name Title ID ID In may case, all I need to do is to execute: PRAGMA full_column_names = 1; and this fixes the problem, even in 3.1.3 (in my limited testing). Of course you could also do: SELECT Name, Title, Books.ID as BooksID, Authors.ID as AuthorsID FROM Books inner join Authors on Books.AuthorID = Authors.ID ORDER BY Authors.Name, Books.Title; but I don't see why you shouldn't use the first approach if you want to. Tim Behlendorf on open source: http://www.itwriting.com/behlendorf1.php
Re: [sqlite] ticket 1147
I use column names. I have created a wrapper around sqlite3 (and other SQL engines) in a developmeny environment I have written to enable the programmer (or user for that matter) to access an SQL result set using an object where the property names are the column names. However - having duplicate column names (without aliasing them), or using an unqualified "*" when querying a select statement with more than one table in it is really bad practice - and I do not think the ANSI standard specifies how that should be dealt with so I do not think sqlite CAN do it "wrong" in that case. Eddy D. Richard Hipp wrote: On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: I understand that this "column names" issue is becoming a pain for the sqlite authors, but OTOH, it is very important for wrapper authors... Why? Why does anybody care what the column names in the result are? What are the column names used for other than to print a header at the top of a table for human-readable output? Remember that not all wrappers are done by third parties. I ship the TCL wrapper with SQLite and for some reason I have never felt the need to turn on long_names or short_names. The default column names, whatever they might be, have generally been acceptable. So what is the difference? What are all these other wrappers doing that requires specific column names formats?
RE: [sqlite] ticket 1147
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 5:38 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ticket 1147 > > On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: > > I understand that this "column names" issue is becoming a > pain for the > > sqlite authors, but OTOH, it is very important for wrapper > authors... > > > > Why? Why does anybody care what the column names in the result > are? What are the column names used for other than to print a > header at the top of a table for human-readable output? > > Remember that not all wrappers are done by third parties. > I ship the TCL wrapper with SQLite and for some reason I > have never felt the need to turn on long_names or short_names. > The default column names, whatever they might be, have > generally been acceptable. > > So what is the difference? What are all these other > wrappers doing that requires specific column names formats? Here's the deal ... When it comes to enhancing or building infrastructure over the top of existing open source, consistency is the father of invention. The problem with the various column_names pragmas is that their behaviors and intents have either changed or been broken over the last few updates to the core engine. This kind of inconsistency renders the pragmas useless. Additionally, when you change the naming behaviors of the column names, any code that depends on their consistent behavior then fails of course. The crux of the problem for us Schema Dogs is this ... 1. SQLite parses SQL statements. It does a really good job. Logically SQLite will never execute a SQL statement it doesn't understand or cannot parse. 2. SQLite at the time it parses a statement, knows exactly what columns it is going to return and from where those columns came. 3. Schema information can be gleaned from a resultset *if the column names are consistent*. There are three ways to do this: A. Parse the SQL statement yourself to figure out what columns from what tables are being returned and match them in order to SQLite's output. Redundant, error-prone, and not even remotely as good of parsing as what SQLite can do. Chances are that your parsed SQL may not even be close to what actually gets returned from SQLite. B. Let SQLite do the work, but once SQLite has figured out what columns its returning, we need some way of knowing what table id and column id each column in the resultset belongs to (if any). C. Parse and try and figure out the EXPLAIN results. The results from EXPLAIN will give you everything you need, but it requires you emulating all 128 opcodes and trying to make heads or tails of it all. NASTY. Especially since SQLite already did all the work! 4. Schema information is important. Maybe not to some, but it is nonetheless important. Lacking any other method of associating a column in a resultset to a column in a table or view, we are forced into using the column name to build logic around making that association. When the column name is inconsistent and/or pragmas which enhance the column name are broken, it is an impossible task. 5. What we do with the schema information or how well we compute it is irrelevant. One cannot compute the value of Y in the statement 1 + X = Y without additional information. We're not asking for a reason for X or Y's existence or what we intend to do with Y once we figure it out, we're just asking for enough information to be able to complete the statement. Having column names in a consistent format is tantamount to giving us our X. 6. Even if full_column_names was fixed tomorrow, it still requires that we parse the SQL statement ourselves to try and figure out what columns from what tables are being returned so we can match it all up. Which is why I proposed real_column_names to begin with. We will never be able to parse SQL and verify it like SQLite does. Heck, ANY of the following solutions will give us our missing X! (given in order of complexity to implement) 1. PRAGMA real_column_names and about 8 lousy lines of code in generateColumnNames 2. A callback function during generateColumnNames that gives us an opportunity to either set or change the column name, and gives us the column type (TK_COLUMN, etc) with the database id, table id and column id to which the column belongs. 3. Some kind of sqlite3_select_schema() function that takes the SQL text, parses it and returns the column name, database, table/view name and alias name for each column in the select. Any column not bound directly to a table or view would have null for column name and table/view name, leaving only the alias. Robert
RE: [sqlite] ticket 1147
Speaking as a wrapper writer myself (VB Wrapper), I too don't need or make any use of the column names but I can see where they might be being used by other wrappers e.g. ODBC/ADO/OLEDB. It's a convenient way for these guys to pick up field name bindings from select statements i.e. it saves them having to parse the select statement to determine the names of the returned columns. e.g. for ADO set objRecSet objConnection.execute("select * from table1,table2 where table1.id=table2.id") objRecSet.field("table1.id").value If the full_column_name pragma is set, then SQLite gives back unambiguous column names but if it isn't , then the wrapper writer would have to figure out the location of the field by parsing the select statement and creating a column name lookup for the returned columns. I think that's the problem isn't it? Steve -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: 28 February 2005 12:38 To: sqlite-users@sqlite.org Subject: Re: [sqlite] ticket 1147 On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: > I understand that this "column names" issue is becoming a pain for the > sqlite authors, but OTOH, it is very important for wrapper authors... > Why? Why does anybody care what the column names in the result are? What are the column names used for other than to print a header at the top of a table for human-readable output? Remember that not all wrappers are done by third parties. I ship the TCL wrapper with SQLite and for some reason I have never felt the need to turn on long_names or short_names. The default column names, whatever they might be, have generally been acceptable. So what is the difference? What are all these other wrappers doing that requires specific column names formats? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] ticket 1147
D. Richard Hipp said: > On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: >> I understand that this "column names" issue is becoming a pain for the >> sqlite authors, but OTOH, it is very important for wrapper authors... >> > > Why? Why does anybody care what the column names in the result > are? What are the column names used for other than to print a > header at the top of a table for human-readable output? I'll second Dr. Hipp's statement. I have my own wrapper that I've written for internal use and seem to be getting along just fine with column names as they are. I do expect the developer (me, in this case) to be smart enough refer to a column by the same name both when setting the SQL and when retrieving the results set. I'm curious just why you feel that you need something different than that. I can't imagine that I'd take too kindly to a wrapper that thought it knew better than me what I wanted to call the columns. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] ticket 1147
On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: > I understand that this "column names" issue is becoming a pain for the > sqlite authors, but OTOH, it is very important for wrapper authors... > Why? Why does anybody care what the column names in the result are? What are the column names used for other than to print a header at the top of a table for human-readable output? Remember that not all wrappers are done by third parties. I ship the TCL wrapper with SQLite and for some reason I have never felt the need to turn on long_names or short_names. The default column names, whatever they might be, have generally been acceptable. So what is the difference? What are all these other wrappers doing that requires specific column names formats? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] ticket 1147
I'm not sure if this point is off topic regarding this isue, but just to let all know, To get the SQLite Delphi components back on-line i had to define: SQLite3_ExecSQL('PRAGMA full_column_names=off'); SQLite3_ExecSQL('PRAGMA short_column_names=off'); to get things going again. Not using this, caused additional metadata retrieved from the query, i.e. the rowid was reported differently. Perhaps this is a help for other wrapper authors. I remember a similar post by Tim Anderson who is an author of a 'simple' wrapper of Delphi. Albert Drent aducom software Quoting Cariotoglou Mike <[EMAIL PROTECTED]>: > I have opened a ticket (#1147) for the full_column_names issue, which is back > in 3.1.3. pls check it out. > > also, I noticed the following : > > when selecting from a view, and duplicate column names exist, there is an > attempt to de-dupe them, by adding a sequence number, like this: > > ID ID:1 ID:2 etc > > this however does not happen with duplicate columns that are returned from a > query. so, > > the statement: > > select * from master,detail where detail.masterID=master.ID > > and, > > create view v1 as select * from master,detail where > detail.masterID=master.ID; > select * from v1; > > do not have the same result wrt to column names. > > I feel this is inconsistent. you should either de-dupe all result sets (IMHO > a bad idea), or leave all result sets alone, as far as names is concerned (my > suggestion). > > I understand that this "column names" issue is becoming a pain for the sqlite > authors, but OTOH, it is very important for wrapper authors... > > > >