Re: [sqlite] Query on multiple tables
Hi Martin, You should try to use a JOIN instead of the WHERE clause. Like this : SELECT tbl1.ID, tbl1.fld1, tbl1.fld2 ... FROM tbl1 JOIN tbl2 ON tbl2.ParentID = tbl1.ID JOIN tbl3 ON tbl3.ParentID = tbl2.ID WHERE tbl1.ID = 4 Try that and tell me if that work for you. I've got a similar problem and that's how I was able to make it work. Probably it has something to do with the way SQLite parse the request. Regards Marc-Andre Gosselin P.S.: Si tu es du même bureau tu diras salut à Sébastien Rancourt de ma part! Martin Gagnon a écrit : Hi all, Using sqlite3 on QNX 6.3.0. I need to do a select query on 3 tables by binding them by their ID's. Something like: Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID AND tbl2.ID=tbl3.ParentID This returns the expected row instantly but the sqlite3 process takes more that 10 seconds to give back a prompt, taking all the CPU time. Is there a way to accomplish this task better? Thank you, Martin Gagnon
[sqlite] Query on multiple tables
Hi all, Using sqlite3 on QNX 6.3.0. I need to do a select query on 3 tables by binding them by their ID's. Something like: Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID AND tbl2.ID=tbl3.ParentID This returns the expected row instantly but the sqlite3 process takes more that 10 seconds to give back a prompt, taking all the CPU time. Is there a way to accomplish this task better? Thank you, Martin Gagnon
[sqlite] New SQLite 3.x C++ Wrapper
Hello all, I have updated my SQLite 3.x C++ wrapper (at http://dev.int64.org/sqlite.html). The API is a bit different, but I think it's significantly better than the old one: Everything is now in the sqlite3x namespace, so you can include sqlite3.h without errors. Prepared statements are now prepared only once in the sqlite3_command ctor so they should be much faster. Readers are now reference counted so you don't have to manually close() them. The new sqlite3_transaction class allows exception-safe transactions: if they go out of scope before you call the commit() method they will automatically rollback() the database. Relevant classes use boost::noncopyable to make sure you don't copy construct/assign them. if you don't want to use boost it's only 4 lines to change. Tell me what you think! -- Cory Nelson http://www.int64.org
Re: [sqlite] VIEW PROBLEM IN VERSION 3.2.2
Xavier Aguila wrote: Thanks John for your answer, you right this statement 'select "t1.a" from view1;' works fine but the PRAGMA statement "pragma short_column_names = 1" doesn't have any efect, and i need preserve the previous behavior. I've tested with "pragma short_column_names = 1", "pragma short_column_names = 0", "pragma short_column_names = ON", "pragma short_column_names = OFF", with no results. If Somebody have any idea, please let me know. Thanks and Regards Xavier John LeSueur wrote: Xavier Aguila wrote: Hi why this is an error? sqlite> create table table1( a int, b text, primary key (a)); sqlite> create table table2( c int, d text, primary key (c)); sqlite> CREATE VIEW view1 AS SELECT t1.a, t1.b, t2.c, t2.d FROM table1 as t1, table2 as t2; sqlite> insert into table1 values(1, "test1"); sqlite> insert into table1 values(2, "test2"); sqlite> insert into table1 values(3, "test3"); sqlite> insert into table2 values(1, "test4"); . 3|test3|1|test4 3|test3|2|test5 3|test3|3|test6 sqlite> select a from view1; SQL error: no such column: a sqlite> select t1.a from view1; SQL error: no such column: t1.a this works fine in version 3.0.8 Regards Xavier select "t1.a" from view1; should work. You might wish to use pragma short_column_names = 1 to preserve the previous behavior. John LeSueur. At this point, I would suggest you look at this: http://www.sqlite.org/cvstrac/wiki?p=ColumnNames If you find that the latest sqlite doesn't behave as specified on that page, write a ticket. I think there have been other reports that this didn't work as expected, but there was a response that I don't remember. you may want to look at the archives of this mailing list. John LeSueur
[sqlite] Yatt 0.0.7 and Weblite
Hi, I have released a new version (0.0.7) of my freeware bug tracker yatt. Most significant changes are the addition of hook scripts which can be used for various purposes. E.g. - at least on unix systems - a mail can be automatically sent when an entry is added or edited. Additionally, user defined policies can be implemented using the hook script. For download and details see http://www.yatt.de As usually it's available for linux, windows and linux on arm processors. Additionally I uploaded a new program called weblite. This is a very lightweight http server with included sqlite database and a C-like scripting language. It's an alpha version, so it's not announced on the webpage. Download here: http://www.yatt.de/weblite.zip Basically it's a stripped down yatt with added support for cgi scripts. It can be used to make very lightweight web based database applications. It's only one executable containing everything. There is not much documentation, yet, and it's currently only available for windows. If there is interest, I will add documentation and versions for other operating systems. It's freeware. Please tell me, if you think it's useful and/or have suggestions for improvements. Stefan
Re: [sqlite] Thread locking issues on RH9
On Thu, 2005-06-16 at 15:25 -0400, Christopher R. Palmer wrote: > The new misuse tests that were added were very helpful for identifying > this. But, are the current behaviour (and tests) the "right" ones? It is the only behavior that will work given the general brokenness of posix locks and the very specific brokenness of posix locks on RH9. There aren't really any other options here. > That > is, should the FAQ be updated to indicate that only the thread that calls > sqlite3_open is allowed to use the structure? > The documentation needs to be updated, clearly. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Thread locking issues on RH9
My problem (ticket 1272) is that I open multiple sqlite handles in one thread and then pass the handles to other threads to perform the work. That is: db1 = sqlite3_open(); db2 = sqlite3_open(); pthread_create(...,db1); pthread_create(...,db2); While this does not appear to be disallowed by the FAQ: > "Threadsafe" in the previous paragraph means that two or more threads can > run SQLite at the same time on different "sqlite" structures returned > from separate calls to sqlite_open(). It is never safe to use the same > sqlite structure pointer simultaneously in two or more threads. it does appear to (potentially) be invalid (when threads don't override each other). The lock structure depends on the thread that opens the database, not the thread that is currently using it. And, in my case, I end up with 2 threads sharing the same lock structure which is incorrect because they cannot override each other's locks. The new misuse tests that were added were very helpful for identifying this. But, are the current behaviour (and tests) the "right" ones? That is, should the FAQ be updated to indicate that only the thread that calls sqlite3_open is allowed to use the structure? Cheers, Chris.
Re: [sqlite] transient SQLITE_CORRUPT
Kevin Schmeichel wrote: As I reported earlier, after updating to sqlite 3.2.2, I started getting occasional SQLITE_CORRUPT errors. The next query always worked, so the error was only a temporary condition. I decided that if I got an error on a query, I would retry the query after closing and reopening the db. I tried this, but I saw the SQLITE_CORRUPT error repeat itself. Next thought was that maybe sqlite just needed a bit of time to "uncorrupt" itself, so I put in a sleep of 300 ms before retrying the failed query. Seems to work... Did you try: *PRAGMA integrity_check; Perhaps that will tell you if you have integrity problems on the db.. Randall Fox *
Re: [sqlite] Problems compiling threadsafe code from cvs
On Thu, 2005-06-16 at 14:34 -0400, Christopher R. Palmer wrote: > If you compile the current code it only includes -DTHREADSAFE=1 in the > command line options for os_unix.o and os_win.o and not the other library > object files. For example: > I see. The problem is in Makefile.in (which I do not use so am unlikely to ever notice.) Fixed now. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] transient SQLITE_CORRUPT
On Thu, 2005-06-16 at 11:25 -0700, Kevin Schmeichel wrote: > As I reported earlier, after updating to sqlite > 3.2.2, I started getting occasional SQLITE_CORRUPT > errors. The next query always worked, so the error > was only a temporary condition. I decided that if I > got an error on a query, I would retry the query after > closing and reopening the db. I tried this, but I saw > the SQLITE_CORRUPT error repeat itself. Next thought > was that maybe sqlite just needed a bit of time to > "uncorrupt" itself, so I put in a sleep of 300 ms > before retrying the failed query. Seems to work... > There should never be such a thing as a transient SQLITE_CORRUPT error. You should only get an SQLITE_CORRUPT error if the database file is truly and permanently corrupted. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problems compiling threadsafe code from cvs
D. Richard Hipp wrote:
On Thu, 2005-06-16 at 13:24 -0400, Christopher R. Palmer wrote:
In the current cvs, there is a problem compiling the threadsafe code (at
least when threads override each other). The OsFile structure defined in
os_unix.h depends on the definition of THREADSAFE which is not included in
the normal compilation flags (for example, for pager.o). I made the
following change to fix the problem:
I beg to differ. The current CVS code says:
#if defined(THREADSAFE) && THREADSAFE
/* stuff that depends on THREADSAFE */
#endif
If THREADSAFE is undefined, it is assumed to be zero. No changes
are needed to make this work.
Yes, I know that you fixed this problem.
If you compile the current code it only includes -DTHREADSAFE=1 in the
command line options for os_unix.o and os_win.o and not the other library
object files. For example:
./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src
-DNDEBUG -DSQLITE_OMIT_CURSOR -c ./src/pager.c
...
./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src
-DNDEBUG -DSQLITE_OMIT_CURSOR -DTHREADSAFE=1 -c ./src/os_unix.c
And, as I said, the definition of struct OsFile now depends on THREADSAFE,
there is a problem:
GNU gdb Red Hat Linux (5.3post-0.20021129.18rh)
Copyright 2003 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i386-redhat-linux-gnu"...
(gdb) b sqlite3OsOpen
Function "sqlite3OsOpen" not defined.
(gdb) b sqlite3OsOpenReadWrite
Breakpoint 1 at 0x804df8c: file src/os_unix.c, line 515.
(gdb) r /tmp/foo
Starting program: /home/crpalmer/sqlite/sqlite3 /tmp/foo
[New Thread 16384 (LWP 26682)]
[Switching to Thread 16384 (LWP 26682)]
Breakpoint 1, sqlite3OsOpenReadWrite (zFilename=0xbc8d "/tmp/foo",
id=0xbfffe450, pReadonly=0x807e15b) at src/os_unix.c:515
515 id->dirfd = -1;
(gdb) ptype id
type = struct OsFile {
struct Pager *pPager;
struct openCnt *pOpen;
struct lockInfo *pLock;
int h;
unsigned char locktype;
unsigned char isOpen;
unsigned char fullSync;
int dirfd;
pthread_t tid;
} *
(gdb) up
#1 0x0806d585 in sqlite3pager_open (ppPager=0x8088648,
zFilename=0xbc8d "/tmp/foo", nExtra=80, flags=-1073749124) at
src/pager.c:1618
1618rc = sqlite3OsOpenReadWrite(zFullPathname, , );
(gdb) ptype fd
type = struct OsFile {
struct Pager *pPager;
struct openCnt *pOpen;
struct lockInfo *pLock;
int h;
unsigned char locktype;
unsigned char isOpen;
unsigned char fullSync;
int dirfd;
}
(gdb)
Note that the structure as used by pager.o does not contain the tid element.
Cheers,
Chris.
[sqlite] transient SQLITE_CORRUPT
As I reported earlier, after updating to sqlite 3.2.2, I started getting occasional SQLITE_CORRUPT errors. The next query always worked, so the error was only a temporary condition. I decided that if I got an error on a query, I would retry the query after closing and reopening the db. I tried this, but I saw the SQLITE_CORRUPT error repeat itself. Next thought was that maybe sqlite just needed a bit of time to "uncorrupt" itself, so I put in a sleep of 300 ms before retrying the failed query. Seems to work... Kevin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Problems compiling threadsafe code from cvs
On Thu, 2005-06-16 at 13:24 -0400, Christopher R. Palmer wrote: > In the current cvs, there is a problem compiling the threadsafe code (at > least when threads override each other). The OsFile structure defined in > os_unix.h depends on the definition of THREADSAFE which is not included in > the normal compilation flags (for example, for pager.o). I made the > following change to fix the problem: > I beg to differ. The current CVS code says: #if defined(THREADSAFE) && THREADSAFE /* stuff that depends on THREADSAFE */ #endif If THREADSAFE is undefined, it is assumed to be zero. No changes are needed to make this work. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Problems compiling threadsafe code from cvs
In the current cvs, there is a problem compiling the threadsafe code (at least when threads override each other). The OsFile structure defined in os_unix.h depends on the definition of THREADSAFE which is not included in the normal compilation flags (for example, for pager.o). I made the following change to fix the problem: Index: Makefile.in === RCS file: /sqlite/sqlite/Makefile.in,v retrieving revision 1.127 diff -r1.127 Makefile.in 103c103 < LTCOMPILE = $(LIBTOOL) --mode=compile $(TCC) --- > LTCOMPILE = $(LIBTOOL) --mode=compile $(TCC) $(THREADSAFE) 320c320 < $(LTCOMPILE) $(THREADSAFE) -c $(TOP)/src/os_unix.c --- > $(LTCOMPILE) -c $(TOP)/src/os_unix.c 323c323 < $(LTCOMPILE) $(THREADSAFE) -c $(TOP)/src/os_win.c --- > $(LTCOMPILE) -c $(TOP)/src/os_win.c Cheers, Chris.
Re: [sqlite] VIEW PROBLEM IN VERSION 3.2.2
Thanks John for your answer, you right this statement 'select "t1.a" from view1;' works fine but the PRAGMA statement "pragma short_column_names = 1" doesn't have any efect, and i need preserve the previous behavior. I've tested with "pragma short_column_names = 1", "pragma short_column_names = 0", "pragma short_column_names = ON", "pragma short_column_names = OFF", with no results. If Somebody have any idea, please let me know. Thanks and Regards Xavier John LeSueur wrote: Xavier Aguila wrote: Hi why this is an error? sqlite> create table table1( a int, b text, primary key (a)); sqlite> create table table2( c int, d text, primary key (c)); sqlite> CREATE VIEW view1 AS SELECT t1.a, t1.b, t2.c, t2.d FROM table1 as t1, table2 as t2; sqlite> insert into table1 values(1, "test1"); sqlite> insert into table1 values(2, "test2"); sqlite> insert into table1 values(3, "test3"); sqlite> insert into table2 values(1, "test4"); . 3|test3|1|test4 3|test3|2|test5 3|test3|3|test6 sqlite> select a from view1; SQL error: no such column: a sqlite> select t1.a from view1; SQL error: no such column: t1.a this works fine in version 3.0.8 Regards Xavier select "t1.a" from view1; should work. You might wish to use pragma short_column_names = 1 to preserve the previous behavior. John LeSueur.
Re: [sqlite] VIEW PROBLEM IN VERSION 3.2.2
Xavier Aguila wrote: Hi why this is an error? sqlite> create table table1( a int, b text, primary key (a)); sqlite> create table table2( c int, d text, primary key (c)); sqlite> CREATE VIEW view1 AS SELECT t1.a, t1.b, t2.c, t2.d FROM table1 as t1, table2 as t2; sqlite> insert into table1 values(1, "test1"); sqlite> insert into table1 values(2, "test2"); sqlite> insert into table1 values(3, "test3"); sqlite> insert into table2 values(1, "test4"); sqlite> insert into table2 values(2, "test5"); sqlite> insert into table2 values(3, "test6"); sqlite> select * from view1; 1|test1|1|test4 1|test1|2|test5 1|test1|3|test6 2|test2|1|test4 2|test2|2|test5 2|test2|3|test6 3|test3|1|test4 3|test3|2|test5 3|test3|3|test6 sqlite> .header ON sqlite> select * from view1; t1.a|t1.b|t2.c|t2.d 1|test1|1|test4 1|test1|2|test5 1|test1|3|test6 2|test2|1|test4 2|test2|2|test5 2|test2|3|test6 3|test3|1|test4 3|test3|2|test5 3|test3|3|test6 sqlite> select a from view1; SQL error: no such column: a sqlite> select t1.a from view1; SQL error: no such column: t1.a this works fine in version 3.0.8 Regards Xavier select "t1.a" from view1; should work. You might wish to use pragma short_column_names = 1 to preserve the previous behavior. John LeSueur.
[sqlite] Update unique column
Hi,
I discovered a behavior in SQLite 2.8.16 that doesn't conform to the SQL
standard, here's an example :
CREATE TABLE tbUpdateUnique (a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c
VARCHAR(100));
INSERT INTO tbUpdateUnique VALUES('', 1, "Test 1");
INSERT INTO tbUpdateUnique VALUES('', 2, "Test 2");
INSERT INTO tbUpdateUnique VALUES('', 3, "Test 3");
Now when I try the following update, I get a constraint error :
UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
In the SQL Standard and NIST SQL test suite they say than an update
should
be considered atomic, and verify unique constraints only after the operation has
updated all rows. From what I experienced with SQLite, constraints are verified
after each row has been updated, resulting in a constraint error. I also tried
these with no success :
BEGIN TRANSACTION;
UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
COMMIT TRANSACTION;
and
UPDATE tbUpdateUnique SET b = b + 1 WHERE a IN (SELECT a FROM tbUpdateUnique
WHERE b >= 2 ORDER BY b DESC);
This is the content of the NIST test suite file dml027.sql :
START--
-- MODULE DML027
-- SQL Test Suite, V6.0, Interactive SQL, dml027.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION HU
SELECT USER FROM HU.ECCO;
-- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0124 UPDATE UNIQUE column (key = key + 1) interim conflict!
-- setup
UPDATE UPUNIQ
SET NUMKEY = NUMKEY + 1;
-- PASS:0124 If 6 rows updated?
SELECT COUNT(*),SUM(NUMKEY)
FROM UPUNIQ;
-- PASS:0124 If count = 6 and SUM(NUMKEY) = 30?
-- restore
ROLLBACK WORK;
-- END TEST >>> 0124 <<< END TEST
--
-- TEST:0125 UPDATE UNIQUE column (key = key + 1) no interim conflit!
-- setup
UPDATE UPUNIQ
SET NUMKEY = NUMKEY + 1
WHERE NUMKEY >= 4;
-- PASS:0125 If 3 rows are updated?
SELECT COUNT(*),SUM(NUMKEY)
FROM UPUNIQ;
-- PASS:0125 If count = 6 and SUM(NUMKEY) = 27?
-- restore
ROLLBACK WORK;
-- END TEST >>> 0125 <<< END TEST
-- *END-OF-MODULE
END--
I would like to know if this will be corrected or if it's too time
consuming to even bother. I would like to keep my column unique but I can
manage without if I need to.
Best regards,
Marc-Andre Gosselin
[EMAIL PROTECTED]
Re: [sqlite] ANN: Sqlite3Explorer.exe version 1.6
> I have uploaded v 1.6 of sqlite3Explorer (www.singular.gr/sqlite). Thanks! The download page has 16/03/2005 against version 1.6 - should be 16/6/2005. Hugh
Re: ODP: [sqlite] ANN: SQLiteSpy 1.1 Database Manager with REGEXP keyword support
Hello Matt Henley, thanks for the suggestion - I had not considere Lazarus because FreePascal 1 did not support WideStrings which are required for Unicode input and display. As the new FreePascal 2 was just released and claims to support WideStrings, I shall have a look at Lazarus as soon as I find the time. However, I have severe doubts that the source will compile on a platform other than Windows because some critical components are heavily Windows-optimized for performance reasons. Ralf >I take it from the URL that SQLiteSpy was written in Delphi. If thats >the case, there is a chance that it could be ported fairly easily to >Lazarus/Freepascal. They have been working on database support >including SQLite recently. If nothing else... bug reports about what >doesnt work could help find the deficiences in Freepascal. The windows >install is pretty easy. See: > >http:/lazarus.freepascal.org > >Matt > >On Wed, 2005-06-15 at 15:17 +0100, Ralf Junker wrote: >> Hello Jarek, >> >> there are no naive questions, just naive answers ... >> >> >perhaps I'm naive, but are there any chances for versions for *other* OS >> >platforms ? >> >> The development environment of SQLiteSpy is Windows only, so I'm afraid, >> there are no chances for other OS platforms. >> >> I hope this isn't a naive answer ... >> >> Regards, >> >> Ralf >> >> >> a new version of the SQLiteSpy database manager is just released: >> >> >> >> http://www.yunqa.de/delphi/sqlitespy/ >> >> >> >> SQLiteSpy is a fast and memory optimized database manager for >> >> SQLite 3 database files with complete Unicode support for >> >> both input and display. >> >> >> >> All schema items (tables, views, indexes, triggers, >> >> collations, databases) are displayed in a tree view control. >> >> The SQL input is syntax highlighted, the data result are >> >> colored by data types for easy debugging. Tabbed browsing of >> >> multiple result sets is possible. >> >> >> >> The new version 1.1 adds support for the REGEXP keyword with >> >> Perl 5.8 compatible regular expression syntax and updates to >> >> the latest version of SQLite. >> >> >> >> Regards, >> >> >> >> Ralf
