RE: [sqlite] .import problems
Duuh. The trailing ";" needs a space, otherwise the .import command thinks ";" is part of the table name. Probably not a valid character for a table name anyway, so I guess that goes under the category "unwanted features". Cheers Balthasar T. Indermuehle UNSW Antarctic Astrophysics Group > -Original Message- > From: Balthasar Indermuehle [mailto:[EMAIL PROTECTED] > Sent: Dienstag, 21. März 2006 18:11 > To: sqlite-users@sqlite.org > Subject: [sqlite] .import problems > > Hi all, > > I'm trying to import a rather large file with the .import > command into a version 3 sqlite DB file. I have created the > table named data, all fields are numeric, I type ".import > data.csv data" and I get the sqlite3 error > "Error: No such table: data;" > > Any ideas anyone? > > thanks > > > Balthasar T. Indermuehle > UNSW > Antarctic Astrophysics Group > > >
[sqlite] .import problems
Hi all, I'm trying to import a rather large file with the .import command into a version 3 sqlite DB file. I have created the table named data, all fields are numeric, I type ".import data.csv data" and I get the sqlite3 error "Error: No such table: data;" Any ideas anyone? thanks Balthasar T. Indermuehle UNSW Antarctic Astrophysics Group
Re: [sqlite] Performance of VIEW with UNION
I walked right into that one. Does SQLite have a debug function to dump its parse tree in readable ASCII form? --- [EMAIL PROTECTED] wrote: > We await your patch. > -- > D. Richard Hipp <[EMAIL PROTECTED]> __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Performance of VIEW with UNION
Joe Wilson <[EMAIL PROTECTED]> wrote: > > I would think it would not be too difficult to extend SQLite > to perform this type of transformation on a view. > > i.e., transform: > > SELECT columns0 from ( >SELECT columns1 WHERE condition1 >UNION (ALL) >SELECT columns2 WHERE condition2 > ) > WHERE condition3 > > to > > SELECT columns0 from ( >SELECT columns1 WHERE (condition1) AND (condition3) >UNION (ALL) >SELECT columns2 WHERE (condition2) AND (condition3) > } > > or am I neglecting something? > We await your patch. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Performance of VIEW with UNION
The plain SELECT and the SELECT on a view below are not coded the same way. You are making an optimization in the plain SELECT that SQLite does not currently perform, namely constraining the two SELECTs seperately _before_ the UNION. The code generated by SQLite on the select on a view acts more like this (untested) query: SELECT * FROM ( SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status FROM table1 u, table2 dr, table3 d WHERE dr.ralentid=u.id AND dr.ralentblc=u.blc AND dr.lewisid=d.id AND dr.lewisblc=d.blc UNION ALL SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status FROM table1 u, table4 ugu, table2 dr, table3 d WHERE ugu.userid=u.id AND ugu.userblc=u.blc AND ugu.userGroupid=dr.ralentid AND ugu.userGroupblc=dr.ralentblc AND dr.lewisid=d.id AND dr.lewisblc=d.blc ) WHERE uid=6 and ublc=8193 I would think it would not be too difficult to extend SQLite to perform this type of transformation on a view. i.e., transform: SELECT columns0 from ( SELECT columns1 WHERE condition1 UNION (ALL) SELECT columns2 WHERE condition2 ) WHERE condition3 to SELECT columns0 from ( SELECT columns1 WHERE (condition1) AND (condition3) UNION (ALL) SELECT columns2 WHERE (condition2) AND (condition3) } or am I neglecting something? --- Ole Göbbels <[EMAIL PROTECTED]> wrote: > Hello everybody, > > I encountered some performance problems using a VIEW with UNION ALL > statement. > > This statement works fine: > > SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment, > dr.rightfield1, d.status > FROM table1 u, table2 dr, table3 d > WHERE u.id=6 > AND u.blc=8193 > AND dr.ralentid=u.id > AND dr.ralentblc=u.blc > AND dr.lewisid=d.id > AND dr.lewisblc=d.blc > UNION ALL > SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment, > dr.rightfield1, d.status > FROM table1 u, table4 ugu, table2 dr, table3 d > WHERE u.id=6 > AND u.blc=8193 > AND ugu.userid=u.id > AND ugu.userblc=u.blc > AND ugu.userGroupid=dr.ralentid > AND ugu.userGroupblc=dr.ralentblc > AND dr.lewisid=d.id > AND dr.lewisblc=d.blc; > > When I create a view: > > CREATE VIEW testview AS > SELECT d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status > FROM table1 u, table2 dr, table3 d > WHERE dr.ralentid=u.id > AND dr.ralentblc=u.blc > AND dr.lewisid=d.id > AND dr.lewisblc=d.blc > UNION ALL > SELECT d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status > FROM table1 u, table4 ugu, table2 dr, table3 d > WHERE ugu.userid=u.id > AND ugu.userblc=u.blc > AND ugu.userGroupid=dr.ralentid > AND ugu.userGroupblc=dr.ralentblc > AND dr.lewisid=d.id > AND dr.lewisblc=d.blc; > > and then run a query like "SELECT * FROM testview WHERE uid=6 and > ublc=8193", > the performance is very bad. > > Each select of the UNION without the UNION and without the other select > as a view works fast, on the other hand. > > Is there any error in the way the VIEW is created? Is there anything one > can do to speed it up? > > TIA, > OG > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Do I have to install sqlite odbc driver if I want to use ASP to connect SQLite?
Hi,all Do I have to install sqlite odbc driver if I want to use ASP to connect SQLite? If there are any available samples ,could you give me some? Thank you so much! - 雅虎1G免费邮箱百分百防垃圾信 雅虎助手-搜索、杀毒、防骚扰
Re: [sqlite] SQLite Busy status from DBD::SQLite
use DBI;
$dbh = DBI->connect("dbi:SQLite:dbname=mydb.db","","", {AutoCommit=>0,
RaiseError=>1, ShowErrorStatement=>1});
eval {$dbh->do("insert into mytable (mycolumn) values ('my data')") };
if ($@) {
if ($@ =~ m/database is locked/i) {
print "db is locked\n\n$@";
} else {
print $@;
}
}
# bound to be some errors, but you get the idea :-)
Maybe other Perl guys here have a better solution, but I don't think DBI sets
or returns an error code specific to "locked". So far, my stuff hasn't been
busy enough with writes to generate locked DB errors, so I haven't had to deal
with it.
Have a look at http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm
Here DBI's author talks about a variety of topics including error handling.
It's beem a good reference for me.
-Clark
- Original Message
From: Sripathi Raj <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, March 20, 2006 2:34:06 PM
Subject: [sqlite] SQLite Busy status from DBD::SQLite
Hi,
I'm using DBD::SQlite with Perl. How do I know that the execution of a
statement failed because the database was locked? Should I examine the
$DBI::errstr? Does DBI set an error code or something?
Thanks,
Raj
[sqlite] SQLite Busy status from DBD::SQLite
Hi, I'm using DBD::SQlite with Perl. How do I know that the execution of a statement failed because the database was locked? Should I examine the $DBI::errstr? Does DBI set an error code or something? Thanks, Raj
Re: [sqlite] Enabling Memory Management in 3.3.4
Alexander Roston <[EMAIL PROTECTED]> wrote: > I've got a small memory > leak - each time the server accepts a request and adds > the information to the database, it grabs 3-500 bytes > of memory and doesn't let it go. I'd like to tell > sqlite3_release_memory to drop the appropriate amount > of memory and thus fix the memory leak. > That is not going to fix your memory leak. First off, SQLite is very carefully tested for memory leaks, so any you find are more likely in your own code. sqlite3_release_memory will clearly not help you there. Secondly, even if the leak does turn out to be in SQLite, the sqlite3_release_memory is only going to release memory that SQLite knows about, not memory that has leaked. Your best bet is to find and fix the leak, methinks. Not treat the symptoms. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Enabling Memory Management in 3.3.4
--- [EMAIL PROTECTED] wrote: > You'll need to manually construct your makefile - > the > configure script will not do this. You can either > edit > the Makefile that configure generates or write your > own using Makefile.linux-gcc as a template. > > The SQLITE_ENABLE_MEMORY_MANAGEMENT feature is > intended > for use on embedded devices where a manually created > Makefile is normally required anyway. What exactly > are you trying to do? Do you have some unreasonable > expectation of what sqlite3_release_memory is going > to accomplish? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > Thanks for the quick reply. I'm writing a fairly simple server program, no threads (at least at this point) and I've got a small memory leak - each time the server accepts a request and adds the information to the database, it grabs 3-500 bytes of memory and doesn't let it go. I'd like to tell sqlite3_release_memory to drop the appropriate amount of memory and thus fix the memory leak. I've been using sqlite3_free_table, and while that reduces the memory use, it doesn't completely fix the problem. What specifically do I need to add to the Makefile to enable SQLITE_ENABLE_MEMORY_MANAGEMENT? Thanks, Alex
Re: [sqlite] Enabling Memory Management in 3.3.4
Alexander Roston wrote: > I asked the compiler to show me a list of the library routines and "sqlite3_release_memory" was not present. shouldn't that give you a link error rather than a segfault? Guessing that if your program linked properly the linker found the function somewhere. Maybe it's finding the wrong version? Best Regards, Kervin
Re: [sqlite] Different kind of db locking behaviour on Unix(Linux) vs. Windows?
"Iulian Popescu" <[EMAIL PROTECTED]> wrote: > > I have the following scenario that fails on Linux and executes fine on > Windows XP. Suppose the following SQL statement is executed by calling > sqlite3_exec: > > SELECT mySqlFunction() > > Inside the body of the mySqlFuntion() the following statement is executed > through a call to sqlite3_exec: > > CREATE TEMP TABLE MyTempTable AS SELECT 'foo' > > Followed by the statement: > > DROP TABLE MyTempTable > > The last statement execution fails on Linux (Fedora Core 4) with the > following error "database table is locked" but not on Windows XP. > > Would somebody know why is that happening? > I wrote the code and I do not know why it is doing what you observe. So I'm guessing not many other people know either. I do know that RedHat has fcntl() locking bugs that SQLite has to go to considerable trouble to work around. Perhaps the same bugs exit in Fedora and the work-around code isn't working quite right. Just a guess -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] INSERT multiple times using sqlite_bind_text()
Perfect! It works fine now... :-) Many thanks, -- Tito On 20/03/2006, at 12:23, [EMAIL PROTECTED] wrote: Tito Ciuro <[EMAIL PROTECTED]> wrote: Hello, I'm trying to INSERT many records using sqlite3_bind_text(). This is what I do: // Finish... result = sqlite3_finalize(statement); What am I missing? Use sqlite3_reset() if you intent to reuse the statement. sqlite3_finalize is the destructor. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Different kind of db locking behaviour on Unix(Linux) vs. Windows?
Hello, I have the following scenario that fails on Linux and executes fine on Windows XP. Suppose the following SQL statement is executed by calling sqlite3_exec: SELECT mySqlFunction() Inside the body of the mySqlFuntion() the following statement is executed through a call to sqlite3_exec: CREATE TEMP TABLE MyTempTable AS SELECT 'foo' Followed by the statement: DROP TABLE MyTempTable The last statement execution fails on Linux (Fedora Core 4) with the following error "database table is locked" but not on Windows XP. However the following statement runs fine on both systems: DELETE FROM MyTempTable Would somebody know why is that happening? Thanks, Iulian
Re: [sqlite] INSERT multiple times using sqlite_bind_text()
Tito Ciuro <[EMAIL PROTECTED]> wrote: > Hello, > > I'm trying to INSERT many records using sqlite3_bind_text(). This is > what I do: > > > // Finish... > result = sqlite3_finalize(statement); > > What am I missing? > Use sqlite3_reset() if you intent to reuse the statement. sqlite3_finalize is the destructor. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] INSERT multiple times using sqlite_bind_text()
Hello,
I'm trying to INSERT many records using sqlite3_bind_text(). This is
what I do:
sqlite3_stmt *statement = NULL;
const char *sql = "INSERT INTO mytable(foo, bar) VALUES (?,?);"
int result = sqlite3_prepare(sqliteDatabase, sql, -1, &statement, NULL);
// Do a bunch of binds and execute...
for (i = 0; i < 100; i++)
{
const char *keyString = "one"
const char *valueString = "two"
int resultBindKey = sqlite3_bind_text ( statement, 1, keyString, -1,
SQLITE_STATIC);
int resultBindContent = sqlite3_bind_text ( statement, 2,
valueString, -1, SQLITE_STATIC);
if ((resultBindKey == SQLITE_OK) && (resultBindContent == SQLITE_OK)) {
BOOL waitingForRow = YES;
do {
int result = sqlite3_step(statement);
switch (result) {
case SQLITE_BUSY:
break;
case SQLITE_OK:
case SQLITE_DONE:
waitingForRow = NO;
break;
case SQLITE_ROW:
waitingForRow = NO;
break;
default:
waitingForRow = NO;
break;
}
} while (waitingForRow);
}
// Finish...
result = sqlite3_finalize(statement);
The first iteration works fine, but after that I get a SQLITE_MISUSE
(ID 21) when trying to sqlite3_bind_text(). I thought that I was
supposed to prepare the statement once, then do a bunch of binds and
at the end, finalize.
What am I missing?
Thanks,
-- Tito
Re: [sqlite] Enabling Memory Management in 3.3.4
Alexander Roston <[EMAIL PROTECTED]> wrote: > I'm having trouble with the library routine > "sqlite3_release_memory." When I try to use it, the > program exits with a segmentation fault. > > I asked the compiler to show me a list of the library > routines and "sqlite3_release_memory" was not present. > >From this I concluded that the > SQLITE_ENABLE_MEMORY_MANAGMENT macro has not been > compiled into the library. > > What do I need to do at compile time to enable the > SQLITE_ENABLE_MEMORY_MANAGMENT macro? I've tried, > > "configure > --enable-SQLITE_ENABLE_MEMORY_MANAGMENT=yes" and > other, similar command lines, but I'm still not seeing > the "sqlite3_release_memory." > You'll need to manually construct your makefile - the configure script will not do this. You can either edit the Makefile that configure generates or write your own using Makefile.linux-gcc as a template. The SQLITE_ENABLE_MEMORY_MANAGEMENT feature is intended for use on embedded devices where a manually created Makefile is normally required anyway. What exactly are you trying to do? Do you have some unreasonable expectation of what sqlite3_release_memory is going to accomplish? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Enabling Memory Management in 3.3.4
I'm having trouble with the library routine "sqlite3_release_memory." When I try to use it, the program exits with a segmentation fault. I asked the compiler to show me a list of the library routines and "sqlite3_release_memory" was not present. >From this I concluded that the SQLITE_ENABLE_MEMORY_MANAGMENT macro has not been compiled into the library. What do I need to do at compile time to enable the SQLITE_ENABLE_MEMORY_MANAGMENT macro? I've tried, "configure --enable-SQLITE_ENABLE_MEMORY_MANAGMENT=yes" and other, similar command lines, but I'm still not seeing the "sqlite3_release_memory." Thanks in advance for the help.
Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout
On Monday 20 March 2006 11:47, [EMAIL PROTECTED] wrote: > BTW: Lots of people have multiple processes writing to the same > SQLite database without problems - the SQLite website is a good > example. I do not know what you are doing wrong to get the > locking problems you are experiencing. I don't know how they manage it (unless of course, many of their writes fail and the txns roll back, and they don't notice or care). On Monday 20 March 2006 11:58, Roger wrote: > I am developing a web based application in PHP/Sqlite and i am forever > getting that error. What i normally do is a simple > > service httpd restart. This is no good. I'm creating a daemon-based server application, which is carrying out autonomous tasks. It does not currently run under httpd, and I have no plans to make it do so. I have several processes which are carrying out a fair amount of work inside a transaction - doing several writes, then doing some other time-consuming operations, then providing everything goes OK, committing these transactions. This means that there are some relatively long-lived transactions (several seconds, anyway) in progress. However, with proper locking this should NOT cause a problem - it should simply serialise the transactional operations (or so I thought). As it is, I've actually tried to port this to MySQL (using Mysql5 and InnoDB), but I'm getting some problems there too - I think I'll have to review my use of transactions etc. Regards Mark
Re: [sqlite] sqlite performance with sizeable tables
On Thu, 16 Mar 2006 [EMAIL PROTECTED] wrote: >Sorry it took me some time to get back to this thread. No problem. I missed your reply anyway:) > >- Original Message >From: Christian Smith <[EMAIL PROTECTED]> > >> When your database does not fit in memory, yes, you're right, the OS may >> well get caching wrong, and in the worst way possible. Two things though: >> - SQLite does have known scaling issues when using multi-gigabyte >> databases. >> - Memory is cheap. If it don't fit, spend that few hundred dollars a few >> days of your time is worth and buy another few gigs or RAM. > >The machine has 2 GB of RAM and the table that seems to cause the >problems is less than 700 megs (proven by the fact that if I precache >this database, things get zippy). To me it seems like the problem is >related to the way the reads are done, but I can be wrong: to me it seems >that caches never really get "hot" (and with nothing else running on the >machine, the OS is pretty much caching all reads done by sqlite). With the whole database in OS cache, reads should not be a problem unless locked out by a writer (see below)... > >> >> >Right now, sqlite shows performance that is on par with a simple >> >filesystem structure (except it's much faster to backup because >> >traversing a multimilion file structure takes several hours). I was >> >expecting a few things by moving to sqlite: >> >* getting a better ramp up (cold cache) performance than a dump >> > filesystem structure. > >> Nothing will speed up the physical disks. > >I agree that disk i/o is the bottleneck, but what can be worked around is >the latencies needed to seek in the file and the way operations are send >out to the disk to help the OS cache more useful information and/or have >a better caching mechanism. As SQLite is designed to be simple and embeddable, advanced IO scheduling is unlikely to be a feature. > >> >* having a [much] higher throughput (so that I can combine multiple >> > servers into one), as the records are small and there is no >> > fragmentation of the folders here. >> >* having consistant latencies (filesystems tend to vary a lot). >> > >> >> - Have you tested other databases? What sort of performance did you get >> > from those? >> >I am in the process of setting up mysql with our app, I will keep you >> >posted on the result. >> >> Prepare to be disappointed, IMO. The most important thing when dealing >> with big fast databases is disk IO, and anything (MySQL, PGSQL, Oracle, >> SQLite) will find disks a bottleneck. > >The reason it took me a while to reply to this thread was that I setup a >MySQL server (5.0.19, run on the same machine) and adapted my application >to run with it. > >The results on the exact same operations that I did before: >* importing of the data is faster with MySQL, and performance doesn't > degrade exponentially with the number or rows (insertions at the > beginning and at the end of the import operation are of similar speed). > sqlite and MySQL started at around the same speed, but after a few > million inserts, sqlite becomes slow. >* cached cleared, on my typical run test (read&write combination), MySQL > ran 2 minutes faster than sqlite (6 minutes vs 8 minutes), getting nice > latencies after about 3 minutes (and that's where the difference is, I > think). I think that after 3 minutes, MySQL manage to have most > critical data cached in RAM. >* with precached dbs (file sizes are very similar for sqlite and MySQL, > for the difference that MySQL separates the data and index into 2 > files), MySQL is faster too (1m30 vs 2 minutes). >* latencies stay low (as in, rarely blocks for > 4 seconds, sqlite would > block for > 10 seconds especially for the early queries when there is > no cache). > >Maybe the reason MySQL performs better is simply because they use 2 files >instead of 1. This could simply be because SQLite is designed for small(er) datasets. > >It does make sense: if the index/offsets are all together on disk, then >the OS can very efficiently cache this information for us. Was splitting >the file into 2 ever tried for sqlite? I mean, there is already the >-journal file, so why not having a -index file as well? One philosophy behind SQLite is for zero configuration and a single file database. Having multiple files would make little difference to latencies unless the files were on seperate media and IO scheduled in parallel. > >> > To accomodate a lot of users, I have one main DB that holds all users -> >> > unique ID The other dbs are a partitioned db really, so that later >> > threads conflict only when accessing users within the same range and the >> > dbs don't get out of proportion (because blobs are used I thought a lot >> > of records could become a performance bottleneck). If I precache the big >> > table name -> uid, the import of 650,000 records takes about 2 minutes. >> >If I don't precache it, it takes about 8 minutes. I start/stop >> >transactions e
Re: [sqlite] [SOLVED] Select with left outer join - Am I going mad or just having a bad day?
On 20 Mar 2006, at 16:34, Ian M. Jones wrote: Here's a super stripped down db and test sql scripts that still show the problem: http://www.ianmjones.net/wp-content/stuff/NoOuter.zip Thanks to [EMAIL PROTECTED] the solution has been found. I've updated the zip file with an extra "SOLVED.sql" file that shows the solution (using "and" in the join rather than separate "where"). It's a shame that I can't use a where clause, but it works, so I'm not going to complain. Regards, -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
[sqlite] Performance of VIEW with UNION
Hello everybody, I encountered some performance problems using a VIEW with UNION ALL statement. This statement works fine: SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status FROM table1 u, table2 dr, table3 d WHERE u.id=6 AND u.blc=8193 AND dr.ralentid=u.id AND dr.ralentblc=u.blc AND dr.lewisid=d.id AND dr.lewisblc=d.blc UNION ALL SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status FROM table1 u, table4 ugu, table2 dr, table3 d WHERE u.id=6 AND u.blc=8193 AND ugu.userid=u.id AND ugu.userblc=u.blc AND ugu.userGroupid=dr.ralentid AND ugu.userGroupblc=dr.ralentblc AND dr.lewisid=d.id AND dr.lewisblc=d.blc; When I create a view: CREATE VIEW testview AS SELECT d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status FROM table1 u, table2 dr, table3 d WHERE dr.ralentid=u.id AND dr.ralentblc=u.blc AND dr.lewisid=d.id AND dr.lewisblc=d.blc UNION ALL SELECT d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status FROM table1 u, table4 ugu, table2 dr, table3 d WHERE ugu.userid=u.id AND ugu.userblc=u.blc AND ugu.userGroupid=dr.ralentid AND ugu.userGroupblc=dr.ralentblc AND dr.lewisid=d.id AND dr.lewisblc=d.blc; and then run a query like "SELECT * FROM testview WHERE uid=6 and ublc=8193", the performance is very bad. Each select of the UNION without the UNION and without the other select as a view works fast, on the other hand. Is there any error in the way the VIEW is created? Is there anything one can do to speed it up? TIA, OG
Re: [sqlite] [SOLVED] Select with left outer join - Am I going mad or just having a bad day?
On 3/20/06, Ian M. Jones <[EMAIL PROTECTED]> wrote: > > On 20 Mar 2006, at 16:06, [EMAIL PROTECTED] wrote: > > > > Perhaps you mean something more like this: > > > >select xcat.Category, xc.CaseID as NumCases > >from Cateogry AS xcat > >left outer join Cases as xc on xcat.CategoryID = xc.CategoryID > >where xc.CaseID in (3145) OR xc.CaseID IS NULL; > > > > Or perhaps this (which is really the same thing): > > > >select xcat.Category, xc.CaseID as NumCases > >from Cateogry AS xcat > >left outer join Cases as xc > >on xcat.CategoryID = xc.CategoryID > >and xc.CaseID in (3145); > > > > In your original query, when the right table of the left join > > is NULL, then the WHERE clause always fails, effectively > > converting your LEFT JOIN into a CROSS JOIN. > > > > From what I know, the whole idea of a left outer join is that the > left hand table always returns a result, regardless of whether the > join to the right hand table succeeds or not. > You understand it right, but since your where clause was on XC not on XCAT those results where dropped by the where clause.
Re: [sqlite] [SOLVED] Select with left outer join - Am I going mad or just having a bad day?
On 20 Mar 2006, at 16:06, [EMAIL PROTECTED] wrote: Perhaps you mean something more like this: select xcat.Category, xc.CaseID as NumCases from Cateogry AS xcat left outer join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) OR xc.CaseID IS NULL; Or perhaps this (which is really the same thing): select xcat.Category, xc.CaseID as NumCases from Cateogry AS xcat left outer join Cases as xc on xcat.CategoryID = xc.CategoryID and xc.CaseID in (3145); In your original query, when the right table of the left join is NULL, then the WHERE clause always fails, effectively converting your LEFT JOIN into a CROSS JOIN. From what I know, the whole idea of a left outer join is that the left hand table always returns a result, regardless of whether the join to the right hand table succeeds or not. So, the NULL check in your first example is superfluous, the Category table should always be returning a result for each of it's rows, whether there is a NULL record in the Cases match or not. But, hurrahh! Your second example works! So, now I've re-written the query as such: select xcat.Category, count(xc.CaseID) as NumCases from Category As xcat left join Cases as xc on xcat.CategoryID = xc.CategoryID and xc.CaseID in (3145) group by xcat.Category order by xcat.Category ; And I get: Bug|0 Feature|0 Inquiry|1 Exactly what I wanted! Thank you drh, very much appreciated. -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
Re: [sqlite] Select with left outer join - Am I going mad or just having a bad day?
On 20 Mar 2006, at 15:46, Jay Sprenkle wrote: That's exactly how I did mine. I wrote 'left join' but I don't think that makes any difference. Perhaps if you made your database downloadable we might be able to find out what's going on. Hi Jay, Here's a super stripped down db and test sql scripts that still show the problem: http://www.ianmjones.net/wp-content/stuff/NoOuter.zip It contains the following: /NoOuter NoOuter.sdb test.sql test2.sql test3.sql Hope someone can get this daft little problem worked out, just can't understand why I can't do an outer join! -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
Re: [sqlite] Select with left outer join - Am I going mad or just having a bad day?
"Ian M. Jones" <[EMAIL PROTECTED]> wrote: > > select xcat.Category, xc.CaseID as NumCases > from Category As xcat > left outer join Cases as xc on xcat.CategoryID = xc.CategoryID > where xc.CaseID in (3145) > ; > Perhaps you mean something more like this: select xcat.Category, xc.CaseID as NumCases from Cateogry AS xcat left outer join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) OR xc.CaseID IS NULL; Or perhaps this (which is really the same thing): select xcat.Category, xc.CaseID as NumCases from Cateogry AS xcat left outer join Cases as xc on xcat.CategoryID = xc.CategoryID and xc.CaseID in (3145); In your original query, when the right table of the left join is NULL, then the WHERE clause always fails, effectively converting your LEFT JOIN into a CROSS JOIN. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Select with left outer join - Am I going mad or just having a bad day?
> Even if I strip it down further and take out the "in", I still only > get the one Category back when I'd expect all three: > > select xcat.Category > from Category As xcat > left outer join Cases as xc on xcat.CategoryID = xc.CategoryID > where xc.CaseID = 3145 > ; > > Is there another way of specifying an outer join in SQLite? That's exactly how I did mine. I wrote 'left join' but I don't think that makes any difference. Perhaps if you made your database downloadable we might be able to find out what's going on.
Re: [sqlite] Select with left outer join - Am I going mad or just having a bad day?
On 20 Mar 2006, at 15:17, Jay Sprenkle wrote: On 3/20/06, Ian M. Jones <[EMAIL PROTECTED]> wrote: select xcat.Category, count(xc.CaseID) as NumCases from Category as xcat left join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) group by xcat.Category order by xcat.Category Is your group by summarizing your results away? Nope, don't think so. If I try this even simpler example I still don't get results for the other two Categories... select xcat.Category, xc.CaseID as NumCases from Category As xcat left outer join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) ; Even if I strip it down further and take out the "in", I still only get the one Category back when I'd expect all three: select xcat.Category from Category As xcat left outer join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID = 3145 ; Is there another way of specifying an outer join in SQLite? -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
Re: [sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
On 20 Mar 2006, at 15:14, Igor Tandetnik wrote: Ian M. Jones wrote: I'm trying to get a count of all Cases for each Category, with an outer join to Cases so that I always get a record for each Category regardless of whether there are any Cases with that Category or not. select xcat.Category, count(xc.CaseID) as NumCases from Category as xcat left join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) group by xcat.Category order by xcat.Category ; How many cases with an ID of 3145 do you expect to find? In other words, what's the purpose of the 'where' clause in your query? In the real query there is a need for the where clause, it's a way of determining how many cases exist in each category for proper query (the in clause contains a proper query). In this test example I'm just using CaseID 3145 to make sure that only one category is matched, because I want to make sure the other two categories still come back with a count of 0. But I'm not getting the other two categories coming back at all, let alone with a count of 0. -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
Re: [sqlite] Select with left outer join - Am I going mad or just having a bad day?
On 3/20/06, Ian M. Jones <[EMAIL PROTECTED]> wrote: > select xcat.Category, count(xc.CaseID) as NumCases > from Category as xcat > left join Cases as xc on xcat.CategoryID = xc.CategoryID > where xc.CaseID in (3145) > group by xcat.Category > order by xcat.Category Is your group by summarizing your results away?
[sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
Ian M. Jones wrote: I'm trying to get a count of all Cases for each Category, with an outer join to Cases so that I always get a record for each Category regardless of whether there are any Cases with that Category or not. select xcat.Category, count(xc.CaseID) as NumCases from Category as xcat left join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) group by xcat.Category order by xcat.Category ; How many cases with an ID of 3145 do you expect to find? In other words, what's the purpose of the 'where' clause in your query? Igor Tandetnik
[sqlite] Select with left outer join - Am I going mad or just having a bad day?
Hi guys and gals, I'm either going mad or having a very bad day, but the following isn't doing what I'd expect and could do with some kind soul putting me right. I have a table called Category which has a primary key column called CategoryID, and three values in the Category column, "Inquiry", "Bug" and "Feature". I have a second table called Cases, this table has a column called CaseID which is the primary key and a CategoryID column as a foreign key to Category. I'm trying to get a count of all Cases for each Category, with an outer join to Cases so that I always get a record for each Category regardless of whether there are any Cases with that Category or not. Simple stuff you'd think, but it isn't working. Here's my SQL: select xcat.Category, count(xc.CaseID) as NumCases from Category as xcat left join Cases as xc on xcat.CategoryID = xc.CategoryID where xc.CaseID in (3145) group by xcat.Category order by xcat.Category ; This is a vastly simplified query, I'm using a specific CaseID so that I only get one category joined, the other two should come back with a count of 0. But all I'm getting is the one record that joins. Inquiry|1 What am I doing wrong, why isn't the (left) outer join working? Thanks, -- Ian M. Jones ___ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog)
Re: [sqlite] db timings [was: concers about database size]
> On 3/18/06, Daniel Franke <[EMAIL PROTECTED]> wrote: > > At Thursday I wrote: > > > Since there's so much interest in this, I'll submit a couple of timings > > > as soon as possible =) On Monday 20 March 2006 15:27, Jay Sprenkle wrote: > Thanks for posting those! > Is that good enough performance for you? It should be fine. Those Queries are done before analysing the data. Since that software (again) uses flat files, we export the validated date chunks once. If this takes a minute or two, nobody bothers. The subsequent analysis can easily takes hours or even days ... One query took about 10 minutes. As D. Hipp pointed out, another index will speed that one up as well. My (uninformed) beforehand guess was that queries could take hours to complete. That's not the case and therefore I don't have to switch database backends =) Btw, according to top, memeory usage never exceeded a couple of MB while accessing the db. I assume this associates to the (default) cache size? Daniel
Re: [sqlite] Open->executeSql->close
Hello Roger, Monday, March 20, 2006, 5:19:53 AM, you wrote: RG> Hi, RG> I'm writing an application that writes to SQLite at least once per RG> second. Is it wise to close the connection between each SQL call or is RG> it better to leave the connection open while the program is running? RG> This is a server application that will run for days and hopefully weeks RG> and months... There is another process which reads from this SQLite db. How often does your reader read? I'd leave it open myself but, there's really no reason to write much faster than the reader reads so, I'd probably try batching up the update into a block, say 10 inserts then wait another 10 seconds. The reason I suggest this is because during each insert, you're locking the database so, with your design the database is locked once a second for N seconds depending on how long the insert takes. That might not leave much of a window for the reader to read. I'm not clear on how granular the locking is so, I don't know if writing to a staging table once a second, then executing a transfer from the staging table to the real table every 10 seconds or so, would be better to open up the lock timing on the real table. Maybe write to a separate staging database then combine them every N seconds so, you have the reliability of having the data in the DB without locking the main DB all the time. If you have a crash, the data in the staging DB can still be sent to the main DB on restart. -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] db timings [was: concers about database size]
Thanks for posting those! Is that good enough performance for you? On 3/18/06, Daniel Franke <[EMAIL PROTECTED]> wrote: > > At Thursday I wrote: > > Since there's so much interest in this, I'll submit a couple of timings as > > soon as possible =)
Re: [sqlite] Unrecognized token error
On 3/19/06, Tito Ciuro <[EMAIL PROTECTED]> wrote: > Hi John, > > On 19/03/2006, at 18:23, John Stanton wrote: > > > Tito Ciuro wrote: > >> Hello, > >> I'm trying to store a string and I get the following error: > >> unrecognized token: \"!\"\" > >> This happens with SQLite 3.2.7 > >> Any ideas? > >> Thanks, > >> -- Tito > > What is the string? > > If I escape single quotes (that is, from "'" to "''"), then > everything is fine. I usually do that when I want to query for a > value that contains a quote (i.e. O'Rourke). > > However, why do I have to manipulate the string value to be stored? > Shouldn't SQLite allow me to store any string I want? If you use the functions to bind values instead of constructing sql you don't have to escape the string. http://sqlite.org/capi3ref.html#sqlite3_bind_text
Re: [sqlite] How do I create a DATETIME field?
Don't forget the date functions work on GMT not localtime.
date('now', 'localtime'); gets your local time
Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout
What i normally do in this scenario is just a simple httpd service restart. That normally does the trick because i am building an application also with PHP/Sqlite. On Mon, 2006-03-20 at 06:47 -0500, [EMAIL PROTECTED] wrote: > Mark Robson <[EMAIL PROTECTED]> wrote: > > > > If the answer is "nothing", I'm going straight over to MySQL :) > > > > The advantages of SQLite are that there are no administrative > hassles - there is nothing to set up or configure and the > database is contained in a single disk file that you can copy > to a flash drive or something. Client/server database engines > like MySQL normally default to READ COMMITTED isolation, which > means you never have database locking problems, but at the expense > of considerable setup and configuration complexity. > > It sounds to me like you are more interested in READ COMMITTED > isolation and do not mind the added complexity, in which case > you should be using a client/server database, such as MySQL. > > BTW: Lots of people have multiple processes writing to the same > SQLite database without problems - the SQLite website is a good > example. I do not know what you are doing wrong to get the > locking problems you are experiencing. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout
Mark Robson <[EMAIL PROTECTED]> wrote: > > If the answer is "nothing", I'm going straight over to MySQL :) > The advantages of SQLite are that there are no administrative hassles - there is nothing to set up or configure and the database is contained in a single disk file that you can copy to a flash drive or something. Client/server database engines like MySQL normally default to READ COMMITTED isolation, which means you never have database locking problems, but at the expense of considerable setup and configuration complexity. It sounds to me like you are more interested in READ COMMITTED isolation and do not mind the added complexity, in which case you should be using a client/server database, such as MySQL. BTW: Lots of people have multiple processes writing to the same SQLite database without problems - the SQLite website is a good example. I do not know what you are doing wrong to get the locking problems you are experiencing. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] sqlite 2.8.17 - correlated subquery?
Hi all, hopefully not too long a question... I needs records from 2 tables and in one I need the record with the maximum ID. (to retrieve the record of the most recent version) On other dbms I would do something like this (see below), but I think the correlation is not available unless I have SQlite 3.1. Right? So how can I accomplish this task with 2.8.17? select t1.id, t1.someprops, t2.latestdata, t2.moredata from table1 t1 join table2 t2 on t1.id = t2.id where t2.id = ( select max(t2s.id) as maxid from table2 t2s where t1.id = t2s.id ); If I specify an ID in both where clauses, it works fine, but in this case, I need a list of all records. (and there max matching record in table 2) Can we do a join with no subquery? Where exists? Thanks for any assistance. -- Greg Fischer 1st Byte Solutions http://www.1stbyte.com
Re: [sqlite] Open->executeSql->close
"Roger Gullhaug" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm writing an application that writes to SQLite at least once per > second. Is it wise to close the connection between each SQL call or is > it better to leave the connection open while the program is running? > This is a server application that will run for days and hopefully weeks > and months... There is another process which reads from this SQLite db. It is better to leave it open. Just be sure to call sqlite3_reset() or sqlite3_finalize() on each statement after it runs. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Open->executeSql->close
Roger Gullhaug wrote: Hi, I'm writing an application that writes to SQLite at least once per second. Is it wise to close the connection between each SQL call or is it better to leave the connection open while the program is running? This is a server application that will run for days and hopefully weeks and months... There is another process which reads from this SQLite db. Leave it open, and better still use sqlite3_prepare to compile the SQL just once, then use sqlite3_reset between calls. You then avoid the overhead of opening and closing the database and of compiling the same SQL every second.
Re: [sqlite] How do I create a DATETIME field?
杰 张 wrote:
> Hi,all
> I created a DATETIME field in a table. So how do it automatically INSERT
> INTO datetime data to the DATETIME field? Thank you so much!
>
>
> zhangjie
>
>
> -
> 雅虎1G免费邮箱百分百防垃圾信
If you have a DATETIME field as an ISO date/time, insert it into Sqlite
using the julianday function
INSERT INTO JUNK VALUES ('AAA', julianday('2006-12-12 05:06:00'));
Ypu will then have a floating point number stored. It holds both date
and time.
Re: [sqlite] Open->executeSql->close
Hi, i disagree. As far as i understand sqlite, if you sqlite3_finalize() your statement and close your transaction, there is no reason why the connection can not remain open. It will not hinder any reading processes. Martin Chethana, Rao (IE10) schrieb: I think its better to close, since there is another process reading from this db. -Original Message- From: Roger Gullhaug [mailto:[EMAIL PROTECTED] Sent: Monday, March 20, 2006 2:20 AM To: sqlite-users@sqlite.org Subject: [sqlite] Open->executeSql->close Hi, I'm writing an application that writes to SQLite at least once per second. Is it wise to close the connection between each SQL call or is it better to leave the connection open while the program is running? This is a server application that will run for days and hopefully weeks and months... There is another process which reads from this SQLite db.
RE: [sqlite] Open->executeSql->close
I think its better to close, since there is another process reading from this db. -Original Message- From: Roger Gullhaug [mailto:[EMAIL PROTECTED] Sent: Monday, March 20, 2006 2:20 AM To: sqlite-users@sqlite.org Subject: [sqlite] Open->executeSql->close Hi, I'm writing an application that writes to SQLite at least once per second. Is it wise to close the connection between each SQL call or is it better to leave the connection open while the program is running? This is a server application that will run for days and hopefully weeks and months... There is another process which reads from this SQLite db.
[sqlite] Open->executeSql->close
Hi, I'm writing an application that writes to SQLite at least once per second. Is it wise to close the connection between each SQL call or is it better to leave the connection open while the program is running? This is a server application that will run for days and hopefully weeks and months... There is another process which reads from this SQLite db.
[sqlite] How do I create a DATETIME field?
Hi,all I created a DATETIME field in a table. So how do it automatically INSERT INTO datetime data to the DATETIME field? Thank you so much! zhangjie - 雅虎1G免费邮箱百分百防垃圾信
