Re: [sqlite] Recommend server for Windows?
Hi Gilles, On 6/20/07, Gilles Ganault [EMAIL PROTECTED] wrote: At 16:49 19/06/2007 -0700, Medi Montaseri wrote: The context is that, until now, our apps were almost used on stand-alone hosts with only a few customers hosting the (small) SQLite database file on a shared drive on the LAN, so performance was just fine. Now, we have a customer whose DB file is about 50MB... and using a 10Mbps LAN, and it takes about 8 seconds for an INSERT. So we have to find a solution ASAP, with minimal changes to our app, at least until we get around to rewriting the DB part so that it uses a location-independent connector. I would suggest that you develop a small application-specific Sqlite server (specific to your application requirements - specialized), put it on a machine and do ALL communication with it using straight TCP-IP because Sqlite is an embedded database and I think your are having problems because you're sharing it on a LAN. I think embedding your database in an application-specific server and making your client applications communicate with that server through TCP/IP (instead of sharing on a LAN) would do the job. -- Best regards, Asif Instead of putting an Sqlite database on a shared drive on a LAN, why don't you - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache invalidation after insert statements.
HI all. Thanks for everyones help the problem is now solved. The memory drive worked like a bomb. Basically the problem on that server was that the insanely high IO prevented the OS from caching the file which slowed down the performance. After installing a mem drive ( using mfs ) and reducing the cache size, multiple connections are now flying. Im getting insane speeds. SQLite FTW! Regards. Werner pompomJuice wrote: Hello there. I need some insight into how SQLite's caching works. I have a database that is quite large (5Gb) sitting on a production server that's IO is severely taxed. This causes my SQLite db to perform very poorly. Most of the time my application just sits there and uses about 10% of a CPU where it would use a 100% on test systems with idle IO. Effectively what the application does is constantly doing lookups as fast as it can. To counteract this I increased the page size to 8192 (Unix server with advfs having 8K block sizes) and increased the MAX_PAGES value in sqliteInt.h to 512000. This worked. My application starts at low memory usage and as it gradually gains more memory. As it gains more memory it uses more CPU and reaches a point where it finally uses 100% CPU and 5Gb of ram. Every now and then the lookup table is udpated. As soon as the application does this however the performance goes back to a crawl and slowly builds up again as described in the previous paragraph. The memory usage stays at 5Gb. All that I can think of is that the update invalidates the cache. The update is not very big, say 20 rows in a table that has about 45 million rows. What exactly is happening here? Regards. -- View this message in context: http://www.nabble.com/Cache-invalidation-after-insert-statements.-tf3944881.html#a11190285 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - -- View this message in context: http://www.nabble.com/Cache-invalidation-after-insert-statements.-tf3944908.html#a11248815 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Trigger on Attached Database
Try to create a TEMPorary trigger after the ATTACH command. Regards Marc Is it possible to do this: Open DB1 Attatch DB2 In DB1 have a trigger that does Insert into DB2. ? Theoretically it seems possible but we couldn't get it to work. Before I investigate further just want to know if it is possible Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache invalidation after insert statements.
At 11:20 22/06/2007, you wrote: HI all. Thanks for everyones help the problem is now solved. The memory drive worked like a bomb. Basically the problem on that server was that the insanely high IO prevented the OS from caching the file which slowed down the performance. After installing a mem drive ( using mfs ) and reducing the cache size, multiple connections are now flying. Im getting insane speeds. SQLite FTW! It's better to write a custom ram drive with sqlite than sqlite with a ram drive. This way the ram drive can lock to the database and make a copy of ramdrive to disk. -- General error, hit any user to continue. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Pragma busy
Hi all, The following errors are generated in a multi threaded program where each thread has its own independent Connection to sqlite. The command sql=[PRAGMA synchronous=FULL] Results in a rc=[5] and errorr msg=[database is locked] How can this can be avoided? I've tried starting a transaction begin exclusive but that results in: rc=[1] msg=[Safety level may not be changed inside a transaction] Thanks.
Re: [sqlite] Re: How to sort not binary?
On 12.05.2007 22:57 CE(S)T, Ingo Koch wrote:
Yves Goergen wrote:
I guess that doesn't work when I'm accessing the database through the
System.Data.SQLite interface in .NET?
Fortunately your guess is wrong. ;-) System.Data.SQLite supports
user defined collation sequences. See TestCases.cs of the source
distribution for samples how to implement them.
Thank you for the reply. I managed to try it out now and got it working
really fast! Here's my solution, tightly adapted from TestCases.cs:
/// summary
/// User-defined collating sequence which does natural sorting.
/// /summary
[SQLiteFunction(Name = NATSORT, FuncType = FunctionType.Collation)]
class NaturalSorting : SQLiteFunction
{
public override int Compare(string param1, string param2)
{
Match m1 = Regex.Match(param1, ^([0-9]+));
if (m1.Success)
{
Match m2 = Regex.Match(param2, ^([0-9]+));
if (m2.Success)
{
int cmpNum = int.Parse(m1.Groups[1].Value) -
int.Parse(m2.Groups[1].Value);
if (cmpNum != 0) return cmpNum;
}
}
return String.Compare(param1, param2, true);
}
}
Then, without anything else, do a query like:
SELECT * FROM table1 ORDER BY column1 COLLATE NATSORT;
You can even debug the user-defined function from VS 2005. I don't have
the impression that it runs considerably slower with my 5000 records
sorting on 3 solumns (where in many cases the first already decides).
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Unicode collation
Hello, I wonder whether there are any plans to include internally proper Unicode comparisons? Don't get me wrong, I think that it's great that SQLite supports custom collations, there's absolutely no problem to handle it in internally for my database, but problem is that if I define UNICODE collation, no other application knows about it and so users can't open it in any SQLite DB editor. Nowadays applications without Unicode support slowly become rare, as I see, I'm not the first one asking for this kind of support in SQLite. Is there any technical reason why not to include UNICODE and e.g. IUNICODE (for case-insensitive comparisons) collations in SQLite? Is it because of some systems that don't have (full) Unicode support? In such a case, I guess that it could be a compile-time option. Thanks, Jiri - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Capturing output from SQLlite with variables in a BASH script
Hi, I'm wondering how to write a BASH script that will capture my SQLite output. I can do it for a single line with something like this: somevar=`sqlite3 dbfilename SELECT name FROM tablename WHERE name='smith' LIMIT 1;` However, if I want to do anything with multiple lines, I haven't figured out a good way. So far, I'm using a workaround by outputting to a file, then reading it after I exit the SQLite commandline, but that slows down the script significantly. e.g. sqlite3 dbfilename EOF .output temp1 select id from tablename where name = bush; .output temp2 select id from tablename where name = osama; .quit EOF read id1 temp1 read id2 temp2 What's the better way to do this without actually writing to a file? Thanks! -- View this message in context: http://www.nabble.com/Capturing-output-from-SQLlite-with-variables-in-a-BASH-script-tf3966729.html#a11259171 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] blob data streaming
On 4/10/07, Teg [EMAIL PROTECTED] wrote: Hello Andrew, Tuesday, April 10, 2007, 3:25:29 PM, you wrote: AF Using sqlite3 (3.3.15 or later), is there a method to retrieve portions of a AF blob rather than the whole thing? AF If not, would others find it useful and handy? Store the file in chunks and retrieve them a chunk at a time. Basically create your own random access method. Following up, as of SQLite 3.4.0 there is a partial read and write of blob interface from SQLite. cf: http://www.sqlite.org/capi3ref.html#sqlite3_blob Thank you much, drh company!
Re: [sqlite] Capturing output from SQLlite with variables in a BASH script
[In the message [sqlite] Capturing output from SQLlite with variables in a BASH script on Jun 22, 13:02, litenoob writes:] Hi, I'm wondering how to write a BASH script that will capture my SQLite output. I can do it for a single line with something like this: somevar=`sqlite3 dbfilename SELECT name FROM tablename WHERE name='smith' LIMIT 1;` However, if I want to do anything with multiple lines, I haven't figured out a good way. So far, I'm using a workaround by outputting to a file, then reading it after I exit the SQLite commandline, but that slows down the script significantly. e.g. sqlite3 dbfilename EOF .output temp1 select id from tablename where name = bush; .output temp2 select id from tablename where name = osama; .quit EOF read id1 temp1 read id2 temp2 What's the better way to do this without actually writing to a file? Well, you can combine the two: id=`sqlite3 dbfilename EOF select id from tablename where name = 'bush'; select id from tablename where name = 'osama'; EOF ` It works for me in ksh and should probably work even in /bin/sh Keep in mind that you will have to parse up the resulting variable with cut, sed, awk and similar if you get too many results. Brush up on those guys too :-) . Thanks, Nikola - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Capturing output from SQLlite with variables in a BASH script
litenoob wrote: Hi, I'm wondering how to write a BASH script that will capture my SQLite output. I can do it for a single line with something like this: somevar=`sqlite3 dbfilename SELECT name FROM tablename WHERE name='smith' LIMIT 1;` However, if I want to do anything with multiple lines, I haven't figured out a good way. So far, I'm using a workaround by outputting to a file, then reading it after I exit the SQLite commandline, but that slows down the script significantly. e.g. sqlite3 dbfilename EOF .output temp1 select id from tablename where name = bush; .output temp2 select id from tablename where name = osama; .quit EOF read id1 temp1 read id2 temp2 What's the better way to do this without actually writing to a file? Thanks! How about re-writing your query to generate shell script to set shell (or environment?) variables and using those variables in your script? Could you make the SQLite output look like shell script and execute or source that? Or, use Tcl? ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Capturing output from SQLlite with variables in a BASH script
- Original Message From: Martin Jenkins [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Friday, June 22, 2007 2:00:45 PM Subject: Re: [sqlite] Capturing output from SQLlite with variables in a BASH script litenoob wrote: Hi, I'm wondering how to write a BASH script that will capture my SQLite output. I can do it for a single line with something like this: somevar=`sqlite3 dbfilename SELECT name FROM tablename WHERE name='smith' LIMIT 1;` However, if I want to do anything with multiple lines, I haven't figured out a good way. So far, I'm using a workaround by outputting to a file, then reading it after I exit the SQLite commandline, but that slows down the script significantly. e.g. sqlite3 dbfilename EOF .output temp1 select id from tablename where name = bush; .output temp2 select id from tablename where name = osama; .quit EOF read id1 temp1 read id2 temp2 What's the better way to do this without actually writing to a file? Thanks! If you're using bash you can simply do something like: sqlite3 dbfilename 'SELECT name FROM tablename WHERE name=smith' | ( while read name ; do echo -- $name ; done ) You can actually put whatever you want within parenthesis (even more parenthised goodness). That, or use a scripting language like perl or python :) Nicolas
Re: [sqlite] Re: How to sort not binary?
On 22.06.2007 17:48 CE(S)T, Yves Goergen wrote:
Match m1 = Regex.Match(param1, ^([0-9]+));
if (m1.Success)
{
Match m2 = Regex.Match(param2, ^([0-9]+));
if (m2.Success)
{
int cmpNum = int.Parse(m1.Groups[1].Value) -
int.Parse(m2.Groups[1].Value);
if (cmpNum != 0) return cmpNum;
}
}
return String.Compare(param1, param2, true);
Oh, well, I just realised that this will only sort numbers naturally
at the beginning of strings, but not in the middle or at the end. It
will be a bit more complex to do that. Maybe I find a sort/compare
algorithm for it. But at least I know now that it's easy to use. :)
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] What about with LAST_INSERT_ROWID() ?
Hmm.. don't know sed or awk, but I suppose this weekend would be a good time to learn! Alright, going along with this, is there a better way to get the last row id inserted? This is my (completely inefficient) workaround: - - - - - sqlite3 $dbfilename EOF INSERT INTO tablename (col1, col2) VALUES (foovaluoo, 3.14159265); .output t_rowid.tmp SELECT LAST_INSERT_ROWID() FROM $db_table LIMIT 1; .quit EOF read tehrowish t_rowid.tmp rm t_rowid.tmp - - - - - Basically, I want to insert something into the DB, then get the row id of that insertion. I know I have to do it in one session, otherwise LAST_INSERT_ROWID() returns 0. -- View this message in context: http://www.nabble.com/Capturing-output-from-SQLlite-with-variables-in-a-BASH-script-tf3966729.html#a11260834 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] What about with LAST_INSERT_ROWID() ?
[In the message [sqlite] What about with LAST_INSERT_ROWID() ? on Jun 22, 15:09, litenoob writes:] Hmm.. don't know sed or awk, but I suppose this weekend would be a good time to learn! Alright, going along with this, is there a better way to get the last row id inserted? This is my (completely inefficient) workaround: - - - - - sqlite3 $dbfilename EOF INSERT INTO tablename (col1, col2) VALUES (foovaluoo, 3.14159265); .output t_rowid.tmp SELECT LAST_INSERT_ROWID() FROM $db_table LIMIT 1; .quit EOF read tehrowish t_rowid.tmp rm t_rowid.tmp - - - - - Basically, I want to insert something into the DB, then get the row id of that insertion. I know I have to do it in one session, otherwise LAST_INSERT_ROWID() returns 0. Well cut, sed, awk would be usefull if you are to push this idea further. For your needs I just tested the following code: -- #/bin/sh ROW_ID=`sqlite3 test.db END insert into t values(1,2); select last_insert_rowid() from t limit 1; END ` echo ROW_ID=$ROW_ID -- And it worked just fine. I am sure bash is compatible with Bourne shel. Thanks, Nikola - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Worked perfectly!
-- #/bin/sh ROW_ID=`sqlite3 test.db END insert into t values(1,2); select last_insert_rowid() from t limit 1; END ` echo ROW_ID=$ROW_ID -- ^ that worked perfectly. Thank you Nikola! -- View this message in context: http://www.nabble.com/Capturing-output-from-SQLlite-with-variables-in-a-BASH-script-tf3966729.html#a11262060 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tomcat crashes with SQLite
Hi I just tried this driver: http://www.zentus.com/sqlitejdbc/ The result is ten times slower or even more. Maybe I am wrong using this new driver. So for the moment I am still using this one: http://www.ch-werner.de/javasqlite/ BUT I HAVE STILL THE CRASH TROUBLES: An unexpected exception has been detected in native code outside the VM. Unexpected Signal : 11 occurred at PC=0x34D8493F Function=sqlite3VdbeExec+0x10B Library=/usr/local/lib/libsqlite3.so.8 ANY IDEAR ??? Thanks for your attention. Cheers Frederic On 6/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: My project is working with Tomcat, SQLite and javasqlite. http://www.ch-werner.de/javasqlite/ http://www.ysalaya.org Since a few weeks Tomcat server crashes very often: error 505 and I need to restart it manually. It is installed on FreeBSD 5.4. Please see the Tomcat log file bellow. It seems to be an error in SQLite outsite the Java Virtual Machine. ANY IDEAR ? I see 2 possible explanations: 1. You use the same connection in different threads at the same time. 2. There are bugs in JDBC driver. If this is the case, try driver from http://www.zentus.com/sqlitejdbc. It for sure has bugs, but may be different and you won't even notice them. You may use pure java version - it will be probably slower than JNI based, but should never crash VM. -- Wicie, rozumicie Zobacz http://link.interia.pl/f1a74 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tomcat crashes with SQLite
If you did not compile sqlite as multi-threaded this is exactly what would happen. On Jun 22, 2007, at 9:06 PM, Frederic de la Goublaye wrote: Hi I just tried this driver: http://www.zentus.com/sqlitejdbc/ The result is ten times slower or even more. Maybe I am wrong using this new driver. So for the moment I am still using this one: http://www.ch-werner.de/javasqlite/ BUT I HAVE STILL THE CRASH TROUBLES: An unexpected exception has been detected in native code outside the VM. Unexpected Signal : 11 occurred at PC=0x34D8493F Function=sqlite3VdbeExec+0x10B Library=/usr/local/lib/libsqlite3.so.8 ANY IDEAR ??? Thanks for your attention. Cheers Frederic On 6/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: My project is working with Tomcat, SQLite and javasqlite. http://www.ch-werner.de/javasqlite/ http://www.ysalaya.org Since a few weeks Tomcat server crashes very often: error 505 and I need to restart it manually. It is installed on FreeBSD 5.4. Please see the Tomcat log file bellow. It seems to be an error in SQLite outsite the Java Virtual Machine. ANY IDEAR ? I see 2 possible explanations: 1. You use the same connection in different threads at the same time. 2. There are bugs in JDBC driver. If this is the case, try driver from http://www.zentus.com/sqlitejdbc. It for sure has bugs, but may be different and you won't even notice them. You may use pure java version - it will be probably slower than JNI based, but should never crash VM. -- Wicie, rozumicie Zobacz http://link.interia.pl/f1a74 -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
