[sqlite] Bug in SQLite 3.8.2
Hi, The bug affects 3.8.2 and 3.8.3.1, I haven't tested other versions. *Preconditions:* - 2 databases: A and B. - database A has table "test": CREATE TABLE test (id integer PRIMARY KEY, val text) WITHOUT ROWID - database B has table "test2": CREATE TABLE test2 (EID INTEGER, node1 INTEGER, node2 INTEGER) *To reproduce bug:* - open database A and attach database B: ATTACH 'database_b.db' AS 'attached'; - execute query: select test.*, t2.ROWID from attached.test2 t2, test SQLite says: *no such column: t2.ROWID* Weird thing is that when you switch test2 and test table positions with each other, the same query will work: select test.*, t2.ROWID from test, attached.test2 t2 *^^^ this works just fine.* Problem occurres only if following conditions are met: - table in local database is WITHOUT ROWID - table in attached database is a regular table with ROWID - query selects ROWID from the regular table - both tables must be mentioned in the FROM clause - the WITHOUT ROWID table must be mentioned as the second one Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Some of PRAGMAs deprecated in 3.7.4
Hi, I noticed that some pragmas are marked as deprecated. I also read some other thread about it, but it was about foreign_keys_list. I'm personally concerned about full_column_names and short_column_names, because I use them a lot in SQLiteStudio. They are very helpful when executing queries from Tcl level. I think there are many other people that use them and they're not aware that these pragmas are about to disappear. Is it really necessary to remove them? I also think that other deprecated pragmas are used (and inreplaceable) by some people. Pragmas are really widely used by management tools. Just something to reconsider. Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building sqlite 3.7.3 with Tcl binding
Hi, How do I compile sqlite 3.7.3 with Tcl bindings? I don't see any tcl-related options in configure script (the amalgamation distribution) and default compilation doesn't probide Tcl bindings. Thanks for help! Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl bindins problem, once again
Hi, I mentioned the problem before but it seems to be ignored for now. I'd really appreciate some response, at least confirmation or denial that I'm right about the problem. Quoting from previous thread: --- QUOTE --- select a1.txt, a2.txt from a a1 join a a2 using (ref) where a1.lang = 'french' and a2.lang = 'english'; --- END OF QUOTE --- Note, that the "SELECT" is not known to application - it's custom select typed by end-user, so I don't deciede about "AS" aliases for columns. --- QUOTE --- We can either use: db eval $query arr { parray arr } But then we would have arr(*) = a.txt a.txt but only one array entry: arr(a.txt). Other way is to use: set results [db eval $query] foreach cellValue $results { puts $cellValue } But then we lose informations about column names in results. One of possible solutions would be to add some prefix or suffix to array indexes. Other one would be to provide column names in results of [db eval $query], using for example flag, like: [db eval $query -withColumnNames] or something like that. The results would be: [list $columnList $tableDataList] --- END OF QUOTE --- Currently I implemented workaround with 2 calls mixed. First is "db eval $query arr {...}" to collect list of result columns and second "set results [db eval $query]" to get all values. It works, but it causes additional query execution. It's a workaround, not a solution. Shouldn't it be fixed? Regards, Googie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.
> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the > uniqueness constraint... You're right, of course. Shame on me for missing it :( Shame on me! -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No error on UPDATE setting duplicated value on UNIQUE column.
Hi, My SQLite is 3.7.2. I have a table like this: CREATE TABLE [newsd] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT, [date] INTEGER NOT NULL, [title] TEXT NOT NULL, [yhfgdfhd] NONE, CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE ) so column [yhfgdfhd] is UNIQUE, and [title] is NOT NULL. Now follow the log of 'sqlite3' calls: $ sqlite3 "data.db" SQLite version 3.7.2 Enter ".help" for instructions sqlite> select * from newsd where ROWID = 16; 16|12.2.12||e sqlite> select * from newsd where ROWID = 21; 21|||x sqlite> UPDATE [newsd] SET [yhfgdfhd] = 'e' WHERE ROWID = 21; sqlite> select * from newsd where ROWID = 21; 21|||x sqlite> UPDATE [newsd] SET [title] = NULL WHERE ROWID = 21; SQL error: newsd.title may not be NULL So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't raise error, thus my application doesn't call ROLLBACK for that case. Last call at the end is to ensure that other constraint violation raises error correctly. Same behaviour takes place for tclsqlite extension. Regards, -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl bindings are a little problematic.
Hi, I have a feeling that thread "Tcl bindings are a little problematic." is forgotten, but yet not concluded. I want to catch your eye again on it. Please see response below. Regards, Pawel From: Paweł Salawa Subject: Re: Tcl bindings are a little problematic. Newsgroups: gmane.comp.db.sqlite.general Date: 2010-09-05 10:19:35 GMT (2 days, 19 hours and 47 minutes ago) > The column names on queries that do not have AS clauses on the columns are > undefined and are subject to change. To get specific column names, use AS: > > select a1.txt AS a1, a2.txt AS a2 > > Then your arr() will contain entries a(*), a(a1), and a(a2). True, but in case of custom SQL typed by end-user (here by user of SQLiteStudio) both result columns can be named exactly same by the user. For now it can be handled by double call (in case of SELECT), first using arr() and second with results returned in list, but a disadventage is obvious. Dnia czwartek, 2 września 2010, napisałeś: > On Wed, Sep 1, 2010 at 6:11 PM, Paweł Salawa bitrock.com> wrote: > > > Hi, > > > > It's pretty hard to make a SELECT from same table, but using two different > > aliases for the table and at the same time also getting column names in > > results - using Tcl bindings. > > > > The SQL query would be: > > > > select a1.txt, a2.txt > > from a a1 > > join a a2 > > using (ref) > > where a1.lang = 'french' > > and > > a2.lang = 'english'; > > > > We can either use: > > db eval $query arr { > > parray arr > > } > > > > But then we would have arr(*) = a.txt a.txt > > but only one entry: arr(a.txt) = something > > which can fail (see thread on forum I mentioned below). > > > > The column names on queries that do not have AS clauses on the columns are > undefined and are subject to change. To get specific column names, use AS: > > select a1.txt AS a1, a2.txt AS a2 > > Then your arr() will contain entries a(*), a(a1), and a(a2). > > > > > > Other way is to use: > > set results [db eval $query] > > foreach cellValue $results { > > puts $cellValue > > } > > > > But then we lose informations about column names in results. > > > > The problem was discussed here (escpecially first and last post in the > > thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4&t=3596 > > > > One of possible solutions would be to add some prefix or suffix to array > > indexes. > > > > Other one would be to provide column names in results of [db eval $query], > > using for example flag, like: [db eval $query -withColumnNames] or > > something > > like that. The results would be: [list $columnList $tableDataList] > > > > I hope I explained it clearly enough. In case of any questions don't > > hesistate > > to ask. > > > > Regards, > > -- > > Paweł Salawa > > paul bitrock.com > > _______ > > sqlite-users mailing list > > sqlite-users sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > drh sqlite.org > -- Paweł Salawa paul bitrock.com ___ sqlite-users mailing list sqlite-users sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl bindings are a little problematic.
> The column names on queries that do not have AS clauses on the columns are > undefined and are subject to change. To get specific column names, use AS: > > select a1.txt AS a1, a2.txt AS a2 > > Then your arr() will contain entries a(*), a(a1), and a(a2). True, but in case of custom SQL typed by end-user (here by user of SQLiteStudio) both result columns can be named exactly same by the user. For now it can be handled by double call (in case of SELECT), first using arr() and second with results returned in list, but a disadventage is obvious. Dnia czwartek, 2 września 2010, napisałeś: > On Wed, Sep 1, 2010 at 6:11 PM, Paweł Salawa wrote: > > > Hi, > > > > It's pretty hard to make a SELECT from same table, but using two different > > aliases for the table and at the same time also getting column names in > > results - using Tcl bindings. > > > > The SQL query would be: > > > > select a1.txt, a2.txt > > from a a1 > > join a a2 > > using (ref) > > where a1.lang = 'french' > > and > > a2.lang = 'english'; > > > > We can either use: > > db eval $query arr { > > parray arr > > } > > > > But then we would have arr(*) = a.txt a.txt > > but only one entry: arr(a.txt) = something > > which can fail (see thread on forum I mentioned below). > > > > The column names on queries that do not have AS clauses on the columns are > undefined and are subject to change. To get specific column names, use AS: > > select a1.txt AS a1, a2.txt AS a2 > > Then your arr() will contain entries a(*), a(a1), and a(a2). > > > > > > Other way is to use: > > set results [db eval $query] > > foreach cellValue $results { > > puts $cellValue > > } > > > > But then we lose informations about column names in results. > > > > The problem was discussed here (escpecially first and last post in the > > thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4&t=3596 > > > > One of possible solutions would be to add some prefix or suffix to array > > indexes. > > > > Other one would be to provide column names in results of [db eval $query], > > using for example flag, like: [db eval $query -withColumnNames] or > > something > > like that. The results would be: [list $columnList $tableDataList] > > > > I hope I explained it clearly enough. In case of any questions don't > > hesistate > > to ask. > > > > Regards, > > -- > > Paweł Salawa > > p...@bitrock.com > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > -- Paweł Salawa p...@bitrock.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl bindings are a little problematic.
Hi, It's pretty hard to make a SELECT from same table, but using two different aliases for the table and at the same time also getting column names in results - using Tcl bindings. The SQL query would be: select a1.txt, a2.txt from a a1 join a a2 using (ref) where a1.lang = 'french' and a2.lang = 'english'; We can either use: db eval $query arr { parray arr } But then we would have arr(*) = a.txt a.txt but only one entry: arr(a.txt) = something which can fail (see thread on forum I mentioned below). Other way is to use: set results [db eval $query] foreach cellValue $results { puts $cellValue } But then we lose informations about column names in results. The problem was discussed here (escpecially first and last post in the thread): http://forum.sqlitestudio.one.pl/viewtopic.php?f=4&t=3596 One of possible solutions would be to add some prefix or suffix to array indexes. Other one would be to provide column names in results of [db eval $query], using for example flag, like: [db eval $query -withColumnNames] or something like that. The results would be: [list $columnList $tableDataList] I hope I explained it clearly enough. In case of any questions don't hesistate to ask. Regards, -- Paweł Salawa p...@bitrock.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users