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] bug/problem with detach
On Feb 28, 2005, at 10:05 PM, Charles Mills wrote: This patch doesn't have the memory leak the other one has and it is formatted correctly. Sorry about that :) whoops. -Charlie
Re: [sqlite] bug/problem with detach
This patch doesn't have the memory leak the other one has and it is formatted correctly. Sorry about that :) -Charlie
RE: [sqlite] new API for query column sources (was Re: ticket 1147)
> -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 10:29 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] new API for query column sources (was Re: > ticket 1147) > > Here are some alternate API naming suggestions, that I have > thought through at length and believe will work. > > Since the proposed new functions are all related and talk > about the source table or view columns for the query, they > should all have the word 'source' in their names. Here are > my suggestions for new functions (and we keep the old ones as > they are): > >sqlite3_column_source() or sqlite3_column_source_column() >sqlite3_column_source_table() >sqlite3_column_source_database() > > All 3 of the above functions return null values for a > calculated field, and non-null values for a non-calculated > field. The first function gives the source table or view > field/column name, and is the same as many databases return > when you say "select *". The second function gives the name > of the table or view; since another name for a "view" is a > "viewed table" (see SQL:2003), that name isn't inappropriate > when the source is a view. And the third function is the > database containing the source table. If desired, pair each > one with a second version for UTF16. > I think your proposed functions are fine. However, I don't know if returning the view name for a column from a view is useful. I think always returning the source table is the way to go, since one of the driving reasons for these functions is the ability to update the source table from the result set, and views are read-only. Tim
Re: [sqlite] bug/problem with detach
I downloaded sqlite-3.1.3 and made the fix. The patch is attached. Also created a ticket here: http://www.sqlite.org/cvstrac/tktview?tn=1151 -Charlie On Feb 16, 2005, at 2:15 PM, Charles Mills wrote: open the database "test.db" then execute the following sql commands ATTACH 'test.db' AS hey PRAGMA database_list # [0, "main", "/Users/boson/workspace/test.db"] # [2, "hey", "/Users/boson/workspace/test.db"] DETACH [hey] # error: no such database: [hey] DETACH hey # success # also note: ATTACH 'test.db' AS [hey] PRAGMA database_list # [0, "main", "/Users/boson/workspace/test.db"], # [2, "hey", "/Users/boson/workspace/test.db"]] So it seems that the [database name] syntax is not understood by DETACH. This seems inconsistent. In attach.c the function void sqlite3Detach(Parse *pParse, Token *pDbname) sqlite3NameFromToken() is not called on the Dbname token. I am using sqlite 3.0.8, haven't had chance to grab the latest release yet. (So maybe this is already fixed?) -Charlie
[sqlite] new API for query column sources (was Re: ticket 1147)
Here are some alternate API naming suggestions, that I have thought through at length and believe will work. Since the proposed new functions are all related and talk about the source table or view columns for the query, they should all have the word 'source' in their names. Here are my suggestions for new functions (and we keep the old ones as they are): sqlite3_column_source() or sqlite3_column_source_column() sqlite3_column_source_table() sqlite3_column_source_database() All 3 of the above functions return null values for a calculated field, and non-null values for a non-calculated field. The first function gives the source table or view field/column name, and is the same as many databases return when you say "select *". The second function gives the name of the table or view; since another name for a "view" is a "viewed table" (see SQL:2003), that name isn't inappropriate when the source is a view. And the third function is the database containing the source table. If desired, pair each one with a second version for UTF16. At 5:33 PM -0700 2/28/05, Dennis Cote wrote: D. Richard Hipp wrote: 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(). I disagree with your assessment about the existing use of sqlite3_column_name(), and think that function should stay the way it is. My reason is that the common thing between all the "sqlite3_column" functions is that they refer to a RESULT column for a select query. Since the QUERY is the primary subject under discussion, it makes sense that sqlite3_column_name() refers to the name of the result column, which is determined either by the AS clause or other default rules. Calling this sqlite3_column_heading() is inappropriate when you consider that most of the time this value is used as a primary identifier for a query result column, for example being used as a hash key. Anyone agree or disagree with my suggestions? -- Darren Duncan
[sqlite] Does SQLite.NET not support AUTOINCREMENT
I used this SQL: CREATE TABLE NewEmployees(EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT, LastName TEXT, FirstName TEXT); and using SQLite3.exe did this SQLite3 employees.db .read create.sql .exit which created my database but no tools nor the SQLite.NET provider can read it, I get the error "malformed database schema near AUTOINCREMENT?
RE: [sqlite] Good Graphical Tool for 3.x
Thanx, I tried that would last night, unfortunately the trial version is so crippled its worthless. > > The SqlitePlus Database Manager is pretty good. Its very similar to > Microsoft > SQL Query Analyzer. Here is the URL: > > www.sqliteplus.com > > > Quoting Tim McDaniel <[EMAIL PROTECTED]>: > >> >> > -Original Message- >> > From: [EMAIL PROTECTED] >> > [mailto:[EMAIL PROTECTED] >> > Sent: Monday, February 28, 2005 12:43 PM >> > To: sqlite-users@sqlite.org >> > Subject: [sqlite] Good Graphical Tool for 3.x >> > >> > Is there a good enterprise manager like tool for SQLite 3.0? >> > I'm a windows guy and command line impaired, what I'm really >> > after is a database with a column in it that use the new >> > AUTOINCREMENT keyword, we're adding support for SQLite 3.x in >> > MyGeneration, we'll also be releasing an instance of our >> > dOOdads .NET architecture (C# and VB.NET) for SQLite in about a week. >> > >> > Anyway, can anybody help me ? Graphical Tool or db with >> > AUTOINCREMENT column in it. >> > >> > Mike Griffin >> > MyGeneration Software >> > http://www.mygenerationsoftware.com >> > >> >> Here's the best three that I found, though still not terribly >> exciting... >> >> http://www.dbtools.com.br/EN/index.php >> >> http://sqlite.org/contrib/download/sqlite3Explorer.zip?get=5 >> >> SQLiteCC (Can't find where I got it from, maybe Googling will turn it >> up) >> >> > > > > > > This message was sent using IMP, the Internet Messaging Program. > > >
Re: [sqlite] Any Plan for Supporting sqlite3_exec16 in near future
> > Regarding the sqlite3_exec() should no longer be used for sqlite3 for > > Unicode application. I am doing the "Begin Transaction" "Commit > > Transaction"... with the sqlite3_exec(), since this function not > > supporting Unicode, do you show me how to do a transaction with > > sqlit3. What are the functions should I use? > > Could anyone answer: What is the best way to run the Transaction (Begin, > Commit, Rollback...) with sqlite3 functions for Unicode application? > sqlite3_prepare16(), sqlite3_step(), sqlite3_finalize(). __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail
Re: [sqlite] Any Plan for Supporting sqlite3_exec16 in near future
[EMAIL PROTECTED] wrote: D. Richard Hipp wrote: On Mon, 2005-02-28 at 19:14 -0400, [EMAIL PROTECTED] wrote: How about the sqlite3_mprintf and sqlite3_vmprintf functions? are they for the legacy users only? It is unclear how to add UTF-16 support to those functions. But they are used internally so they are unlikely to go away. Regarding the sqlite3_exec() should no longer be used for sqlite3 for Unicode application. I am doing the "Begin Transaction" "Commit Transaction"... with the sqlite3_exec(), since this function not supporting Unicode, do you show me how to do a transaction with sqlit3. What are the functions should I use? Thanks, Ming Hi All, Could anyone answer: What is the best way to run the Transaction (Begin, Commit, Rollback...) with sqlite3 functions for Unicode application? Thanks, Ming
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] Any Plan for Supporting sqlite3_exec16 in near future
D. Richard Hipp wrote: On Mon, 2005-02-28 at 19:14 -0400, [EMAIL PROTECTED] wrote: How about the sqlite3_mprintf and sqlite3_vmprintf functions? are they for the legacy users only? It is unclear how to add UTF-16 support to those functions. But they are used internally so they are unlikely to go away. Regarding the sqlite3_exec() should no longer be used for sqlite3 for Unicode application. I am doing the "Begin Transaction" "Commit Transaction"... with the sqlite3_exec(), since this function not supporting Unicode, do you show me how to do a transaction with sqlit3. What are the functions should I use? Thanks, Ming
[sqlite] is this a bug?
I type very fast and my fingers added the semicolon on the end because they're used to doing it: sqlite> .dump people_event; BEGIN TRANSACTION; COMMIT; sqlite> .dump people_event BEGIN TRANSACTION; CREATE TABLE People_Event ( PeopleINTEGER NOT NULL, Event INTEGER NOT NULL ); INSERT INTO "People_Event" VALUES(1, 2); INSERT INTO "People_Event" VALUES(2, 3); COMMIT; __ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250
Re: [sqlite] Any Plan for Supporting sqlite3_exec16 in near future
On Mon, 2005-02-28 at 19:14 -0400, [EMAIL PROTECTED] wrote: > > > How about the sqlite3_mprintf and sqlite3_vmprintf functions? are they > for the legacy users only? > It is unclear how to add UTF-16 support to those functions. But they are used internally so they are unlikely to go away. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Good Graphical Tool for 3.x
The SqlitePlus Database Manager is pretty good. Its very similar to Microsoft SQL Query Analyzer. Here is the URL: www.sqliteplus.com Quoting Tim McDaniel <[EMAIL PROTECTED]>: > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] > > Sent: Monday, February 28, 2005 12:43 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Good Graphical Tool for 3.x > > > > Is there a good enterprise manager like tool for SQLite 3.0? > > I'm a windows guy and command line impaired, what I'm really > > after is a database with a column in it that use the new > > AUTOINCREMENT keyword, we're adding support for SQLite 3.x in > > MyGeneration, we'll also be releasing an instance of our > > dOOdads .NET architecture (C# and VB.NET) for SQLite in about a week. > > > > Anyway, can anybody help me ? Graphical Tool or db with > > AUTOINCREMENT column in it. > > > > Mike Griffin > > MyGeneration Software > > http://www.mygenerationsoftware.com > > > > Here's the best three that I found, though still not terribly > exciting... > > http://www.dbtools.com.br/EN/index.php > > http://sqlite.org/contrib/download/sqlite3Explorer.zip?get=5 > > SQLiteCC (Can't find where I got it from, maybe Googling will turn it > up) > > This message was sent using IMP, the Internet Messaging Program.
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] Good Graphical Tool for 3.x
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Monday, February 28, 2005 12:43 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Good Graphical Tool for 3.x > > Is there a good enterprise manager like tool for SQLite 3.0? > I'm a windows guy and command line impaired, what I'm really > after is a database with a column in it that use the new > AUTOINCREMENT keyword, we're adding support for SQLite 3.x in > MyGeneration, we'll also be releasing an instance of our > dOOdads .NET architecture (C# and VB.NET) for SQLite in about a week. > > Anyway, can anybody help me ? Graphical Tool or db with > AUTOINCREMENT column in it. > > Mike Griffin > MyGeneration Software > http://www.mygenerationsoftware.com > Here's the best three that I found, though still not terribly exciting... http://www.dbtools.com.br/EN/index.php http://sqlite.org/contrib/download/sqlite3Explorer.zip?get=5 SQLiteCC (Can't find where I got it from, maybe Googling will turn it up)
[sqlite] accessing Tables on a different Server
SQLiters: does anybody have a need to access SQLite Tables on a different server over the same LAN? how do you access such Tables? Regards, Uriel_Carrasquilla
Re: [sqlite] Mem-Structure
On Mon, 2005-02-28 at 20:03 +0100, Bernhard DÃbler wrote:
> Hello,
>
> vdbemem.c (2004 May 26) reads at about line 650:
>
> if( pMem->enc==SQLITE_UTF8 && (flags & MEM_Term) ){
> assert( strlen(pMem->z)<=pMem->n );
> assert( pMem->z[pMem->n]==0 );
> }
>
> First is tested if the length of the passed string is shorter or equal to
> the passed number of bytes it's maximal allowed to be long. Secondly is
> tested if the n-th byte of the string is a NULL-char
>
> I don't recognize the actual need of the second assert.
Strictly speaking, assert() is never needed. These two asserts
were probably put in at different times. The second assert is
the more restrictive of the two. If either were eliminated it
would be the first.
Note that for production builds, all assert() operators become
no-ops. So leaving an extra assert in the code is harmless.
--
D. Richard Hipp <[EMAIL PROTECTED]>
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
[sqlite] dis-ambiguating compound queries (was RE: ticket 1147)
At 12:29 PM -0500 2/28/05, D. Richard Hipp wrote: 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? Since this was brought up, I'll answer it as a separate thread. The short answer is that the person writing the SQL needs to do a bit more work, and explicitly define an extra result column whose value differs for each member of the compound query, so one knows which said member the row came from. For example: SELECT 'first' AS member, foo, bar, baz FROM table_one UNION SELECT 'second' AS member, foo, bar, baz FROM table_two As for natural joins ... by definition a natural join combines columns that have the same name and equal values in every row; because of this, it is known that each returned value returns to both source columns. -- Darren Duncan
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 to clients.) A negative value indicates the data type is variable-length. PQbinaryTuples Returns 1 if the PGresult contains binary data and 0 if it contains
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
[sqlite] Mem-Structure
Hello,
vdbemem.c (2004 May 26) reads at about line 650:
if( pMem->enc==SQLITE_UTF8 && (flags & MEM_Term) ){
assert( strlen(pMem->z)<=pMem->n );
assert( pMem->z[pMem->n]==0 );
}
First is tested if the length of the passed string is shorter or equal to
the passed number of bytes it's maximal allowed to be long. Secondly is
tested if the n-th byte of the string is a NULL-char
I don't recognize the actual need of the second assert.
if strlen(pMem->z) equals pMem->n then pMem->z[pMem->n] is of course equals
NULL
if strlen(pMem->z) was smaller than pMem->n then pMem->z[pMem->n] is not 0
but there obviously must be a NULL char which instructs strlen to stop
counting characters.
The comment to the code reads, the check is done because of possible
erroneus databases.
I wonder what exactly is written into the database.
Are n bytes from pMem->z on written to the db? Is n written to the db too?
Is the actual length of the string at time of insertion written to the db?
Best,
Bernhard
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
[sqlite] sqlite_exec_printf
I didn't find anything in the archives, but is there a reason that the sql_exec_printf wasn't carried over from sqlite2 to sqlite3? I figure I can add the functions back in, but is there a better way intended to implement the same functionality, without adding the functions? Thanks for the help. -- Robert Scussel 1024D/BAF70959/0036 B19E 86CE 181D 0912 5FCC 92D8 1EA1 BAF7 0959
[sqlite] Good Graphical Tool for 3.x
Is there a good enterprise manager like tool for SQLite 3.0? I'm a windows guy and command line impaired, what I'm really after is a database with a column in it that use the new AUTOINCREMENT keyword, we're adding support for SQLite 3.x in MyGeneration, we'll also be releasing an instance of our dOOdads .NET architecture (C# and VB.NET) for SQLite in about a week. Anyway, can anybody help me ? Graphical Tool or db with AUTOINCREMENT column in it. Mike Griffin MyGeneration Software http://www.mygenerationsoftware.com
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 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?
[sqlite] Remote Access of SQLite Tables
Hello SQLiters! I am trying to figure out "best practices" for accessing SQL Tables from other servers in the same LAN with bandwidth of 1 GB. There are several purposes for such access: 1) to query tables from one of the servers running HTTP-Daemon (apache) using perl. 2) to read entire table once a day and summarize the information for trends. Files are of significant size and the summarized information is small. 3) to back up the entire tables on a daily basis. I figured that the solution to (1) is to use SQLRelay. I have never used it but found in the list of SQLite products. I am thinking of creating a Store Procedure for (2). Then, using SQLRelay, invoke it and save the results in the local server for historical trends. My thinking of (3) is along the lines of creating a backup file using the .dump command, followed by a remote copy (rsync, scp, rcp) to the server that handles the backups. We actually don't install SQLite in our servers and use it only within perl. So will have to figure out how to invoke .dump from perl. Background: We have 20+ Unix Servers (large SUN and AIX servers with 71 CPU's or so) and a Mainframe runing both zOS and zLinux in multiple partitions (LPAR's). These servers are all running perl daemons capturing performance information using "ps, vmstat, sar, and acctcom". Until recently I had been using csv to capture the information and then via NFS, I would read all the data into one single server for daily processing into a historical performance database. My HTTP server would via inet and sockets connect to the servers and pull data for daily monitoring. My backups are done on the MF using zOS/SMS/HSM for archival. This entire solution works but I am duplicating data across servers and we are talking BIG datasets. The nastiest problem with this approach: keeping records layout in sync across all servers. Then, I started using SQLite and realized that I could re-write all my daemons to create the Tables at source when the data is captured. This way, if the record layout changes, I would not have to modify anything else down the path but once at source. My next challenge is accessing the tables real time. I thought of writing my own inet/perl scripts to receive requests. I started checking around if anybody had done something along these lines and came across SQLRelay (http://sqlrelay.sourceforge.net/). Regards, Uriel_Carrasquilla
[sqlite] Storing XML
Has anyone used SQLite to store XML fragments or documents? Which approach have you taken or what approach would you take? I am currently evaluating the possibilities of storing arbitrary XML fragments using SQLite. The fragments may or may not have schema information.
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] Version 3.1.3 is a headache
On Sat, 28 Feb 2004 11:52:03 +0100, Jakub Adamek <[EMAIL PROTECTED]> wrote: > Is it really so that some database server returns a result set with two > same column names? Seems very strange. And the lovely SQLite 3.0.8 > didn't do such things ... PostgreSQL, which holds closer to the SQL spec than any other DB I'm aware of, refuses this syntax: SELECT * FROM a INNER JOIN b; You are depending on implicit join syntax that I believe is illegal in SQL. The fact that it happens to work doesn't make that a good idea. If you are explicit in the join: petrilli=# SELECT * FROM a, b WHERE a.id = b.id; id | x | id | y +---++--- 1 | 1 | 1 | 2 As you'll notice, it returns both id columns, because you selected ALL columns. This is the correct behavior. In this case, you've simply chosen the wrong behavior. As several other people have commented, you should, in all join cases, explictely call out all columns that you are interested in: petrilli=# SELECT a.id, x, y FROM a, b WHERE a.id = b.id; id | x | y +---+--- 1 | 1 | 2 Good luck. Chris -- | Christopher Petrilli | [EMAIL PROTECTED]
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]>
[sqlite] ODBC driver
Hi,
I've been trying to modify the ODBC driver
(http://www.ch-werner.de/sqliteodbc/) to be able to work with BLOBs. The current
0.65 ODBC driver can only handle CLOBs (i.e. null terminated strings) inspite
of it being built on sqlite3 .
I've managed to get BLOB writing to work by using sqlite3_bind_blob(...) at
write time. This is a bit problematic though as it requires
the entire BLOB to be available at binding time.
Lets say a 10MB file is to be stored in the database, usual procedure would be
to divide it up in chunks and do SQLPutData on each chunk. With the current
SQLite API that is not possible.
I'd propose an added API called sqlite3_bind_chunk(...) that has the same
function prototype (as _bind_blob) but with the added function that calling
sqlite3_step() returns SQLITE_OK until the data size parameter to
sqlite3_bind_chunk(...) is zero, like:
sqlite3_prepare(db, "INSERT INTO T_TABLE (DATA) VALUES(?) WHERE ID=42", -1,
, );
while (true)
{
int chunkLength = min(dataLength, CHUNK_LENGTH);
sqlite3_bind_chunk(stmt, 0, dataPtr, chunkLength, SQLITE_TRANSIENT);
dataLength -= chunkLength;
rc = sqlite3_step();
if (rc == SQLITE_DONE)
{
break;
}
}
I.e. bind the blob in several consecutive chunks instead of one. Make sense? Or
is it RTFM?? ;)
However, getting the data back is a bit problematic as the SQLite statement
scope is only within SQLExecute. When SQLGetData is called, there no longer is
a statement to use for sqlite3_column_blob(...). The 0.65 driver solves this by
copying the rowset as returned in SQLExecute (by sqlite3_get_table)
into a char** structure which is then used in SQLGetData to pass data on to the
calling application. However, I'd prefer not to have this
behavior as it means more data copy than needed.
I'll try not to sqlite3_finalize the statement until SQLFreeStmt is called
(from the application), so that the actual data pointers
are available in SQLGetData during BLOB fetch. Does anybody see any hazard in
doing this?
/Rob
[sqlite] ticket 1147
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...
