Re: [sqlite] Multi-threading Common Problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/23/2011 09:12 PM, John Deal wrote: > I guess I am lost on how to obtain a many reader or one writer mutex in > SQLite. You are confusing locks on the database and locks in the library on a sqlite3 pointer. The latter is what the mutex alloc function you reference is about and there there is no reader/writer mechanism. Access has to be serialized. > You are correct in the locking article referenced I want a mutex that can > have the lock states of shared, pending, and exclusive. Those are locks on the database which you get through regular operations and transactions. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp =rXCY -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [PATCH] Won't compile on archaic OS
See also
https://rt.cpan.org/Ticket/Display.html?id=68396
Using gcc-3.4.6 on HP-UX 10.20:
> make test
gcc -c -I. -I/opt/perl/lib/site_perl/5.14.0/PA-RISC2.0/auto/DBI -mpa-
risc-1-1 -DPERL_DONT_CREATE_GVSV -D_HPUX_SOURCE -fno-strict-aliasing -
pipe -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -DVERSION=
\"1.31\" -DXS_VERSION=\"1.31\" -fPIC "-I/opt/perl/lib/5.14.0/PA-RISC2.0/
CORE" -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -
DSQLITE_ENABLE_COLUMN_METADATA -DNDEBUG=1 -DHAVE_USLEEP=1 -
DTHREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION sqlite3.c
sqlite3.c: In function `unixShmMap':
sqlite3.c:26184: error: `MAP_FAILED' undeclared (first use in this
function)
sqlite3.c:26184: error: (Each undeclared identifier is reported only
once
sqlite3.c:26184: error: for each function it appears in.)
After applying this patch:
--8<---
--- sqlite3.c.org 2011-05-23 15:27:35 +0200
+++ sqlite3.c 2011-05-23 15:29:55 +0200
@@ -22661,6 +22661,10 @@ struct unixFile {
*/
#define SQLITE_WHOLE_FILE_LOCKING 0x0001 /* Use whole-file locking */
+#ifndef MAP_FAILED
+#define MAP_FAILED ((void *) -1)
+#endif
+
/*
** Include code that is common to all os_*.c files
*/
-->8---
> make test
:
All tests successful.
Files=59, Tests=1308, 87 wallclock secs ( 4.61 usr 0.48 sys + 53.42
cusr 3.96 csys = 62.47 CPU)
Result: PASS
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help sqlite database corruption
Hello, Resending with some more finding any help would be appreciated. One of the tables in the database is getting corrupted. 1. Sqlite>select count(*) from $table_name .. 7981508|35001|284 7981509|35002|284 7981510|35003|284 7981511|35004|284 SQL error: database disk image is malformed sqlite> 2. When integrity check is performed following is the error: *** in database main *** Page 703104: sqlite3BtreeInitPage() returns error code 11 On tree page 680368 cell 75: Child page depth differs Page 703365: sqlite3BtreeInitPage() returns error code 11 Page 703624: sqlite3BtreeInitPage() returns error code 11 Page 703884: sqlite3BtreeInitPage() returns error code 11 Note: 1. $table_name has 2 unique indexes and 3 columns 2. Using Sqlite version 3.5.6 3. We are able to .dump to sql file and get the db back Thanks and Regards, Manpreet Singh CA Subject Matter Expert SQL Certified (Oracle 9i) MCP (SQL 2000 Administration) MS Certified Technology Specialist - SQL Server 2005 From: Singh, Manpreet Sent: Wednesday, May 18, 2011 12:04 PM To: 'sqlite-...@sqlite.org' Subject: Help sqlite database corruption Hello, Looking for help, sqlite database getting corrupted multiple times we need to use .dump to recover and then back to database. As per our observations index gets corrupted for one of the tables (table goes more than 7900k records) and when queried it give "malformed disk image" for that database. Thanks and Regards, Manpreet Singh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help sqlite database corruption
On Tue, May 24, 2011 at 5:39 AM, Singh, Manpreet wrote: > Hello, > > > > Resending with some more finding any help would be appreciated. One of > the tables in the database is getting corrupted. > http://www.sqlite.org/howtocorrupt.html http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/atomiccommit.html#sect_9_0 2. Using Sqlite version 3.5.6 > SQLite version 3.7.6.3 is faster and has fewer bugs. Recommend that you upgrade. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-threading Common Problem
Hello Roger, Sorry to be so brain-dead but I am still confused. I have multiple threads, each with their own DB connection. I want to allow multiple readers accessing the DB at the same time since nothing is changing. However, if a writes is to take place, I want all readers to finish their reads and give the writer exclusive access. Once the writer is done, the readers can come back in. I have all writes in transactions. If I deactivate my pthread_rwlock() that enforce the above, several writes fail with a "database locked" error (I assume it is returning SQLITE_BUSY). With my pthread_rwlock(), I have multiple threads reading the DB and my writes get the exclusive access they need. Now I could loop on the write until it gets in but that seems very wasteful. So how do I implement the equivalent of a pthread_rwlock() using SQLite mechinisms? Thanks, John --- On Tue, 5/24/11, Roger Binns wrote: > From: Roger Binns > Subject: Re: [sqlite] Multi-threading Common Problem > To: "General Discussion of SQLite Database" > Date: Tuesday, May 24, 2011, 3:10 AM > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/23/2011 09:12 PM, John Deal wrote: > > I guess I am lost on how to obtain a many reader or > one writer mutex in SQLite. > > You are confusing locks on the database and locks in the > library on a > sqlite3 pointer. The latter is what the mutex alloc > function you reference > is about and there there is no reader/writer > mechanism. Access has to be > serialized. > > > You are correct in the locking article referenced I > want a mutex that can have the lock states of shared, > pending, and exclusive. > > Those are locks on the database which you get through > regular operations and > transactions. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk > VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp > =rXCY > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-threading Common Problem
On 24 May 2011, at 12:43pm, John Deal wrote: > Sorry to be so brain-dead but I am still confused. I have multiple threads, > each with their own DB connection. Read http://www.sqlite.org/threadsafe.html If it's not clear to you please ask specific questions about what's on that page, since your questions will help us to work out how to improve it. > I want to allow multiple readers accessing the DB at the same time since > nothing is changing. However, if a writes is to take place, I want all > readers to finish their reads and give the writer exclusive access. Once the > writer is done, the readers can come back in. > > I have all writes in transactions. If I deactivate my pthread_rwlock() that > enforce the above, several writes fail with a "database locked" error (I > assume it is returning SQLITE_BUSY). The documentation on how to handle this is a little lacking, but if your app has no principles of its own, just loop until the problem goes away. > With my pthread_rwlock(), I have multiple threads reading the DB and my > writes get the exclusive access they need. Now I could loop on the write > until it gets in but that seems very wasteful. > > So how do I implement the equivalent of a pthread_rwlock() using SQLite > mechinisms? You use transactions correctly. Pay especial attention to the differences between BEGIN BEGIN IMMEDIATE BEGIN EXCLUSIVE BEGIN DEFERRED See the following page http://www.sqlite.org/lang_transaction.html However, you may have done something to defeat SQLite's built in mechanism. Have you done any of the following: Have you set any of SQLite's compiler directives in your compilation ? I'm thinking especially of SQLITE_THREADSAFE. I'm not completely familiar with SQLite's model, but it seems to me as if you should be using '-DSQLITE_THREADSAFE=2'. The explanation of this is on the first web page I pointed to above. Have you set any PRAGMAs which are related to threading ? Especially 'PRAGMA temp_store_directory' and 'PRAGMA read_uncommitted' ? If so, try removing your PRAGMAs and see if this fixes your problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Better way to get records by IDs
On 23/05/2011, at 11:13 PM, jose isaias cabrera wrote: > SharedDB file is about 600 megs shared over a network drive and it's getting > slow, but if I get the specific record ID only with the select that I want, > it's a lot faster than getting the select with all the items in one shot. > SQLite probably does something in the back ground to get things faster when > addressed specifically to an ID. 600MB and slow seems like a good candidate for better structuring your database. If you post your full schema, we can suggest a better way to structure it for optimal speed and efficiency. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-threading Common Problem
> I have all writes in transactions. If I deactivate my pthread_rwlock() that > enforce the above, several writes fail with a "database locked" error (I > assume it is returning SQLITE_BUSY). > > So how do I implement the equivalent of a pthread_rwlock() using SQLite > mechinisms? When SQLITE_BUSY in a reader transaction is returned just wait a little bit and try again. Also you can benefit from sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html). Another question is why do you want to get read of pthread_rwlock if it works for you? Pavel On Tue, May 24, 2011 at 7:43 AM, John Deal wrote: > Hello Roger, > > Sorry to be so brain-dead but I am still confused. I have multiple threads, > each with their own DB connection. I want to allow multiple readers > accessing the DB at the same time since nothing is changing. However, if a > writes is to take place, I want all readers to finish their reads and give > the writer exclusive access. Once the writer is done, the readers can come > back in. > > I have all writes in transactions. If I deactivate my pthread_rwlock() that > enforce the above, several writes fail with a "database locked" error (I > assume it is returning SQLITE_BUSY). With my pthread_rwlock(), I have > multiple threads reading the DB and my writes get the exclusive access they > need. Now I could loop on the write until it gets in but that seems very > wasteful. > > So how do I implement the equivalent of a pthread_rwlock() using SQLite > mechinisms? > > Thanks, > > John > > --- On Tue, 5/24/11, Roger Binns wrote: > >> From: Roger Binns >> Subject: Re: [sqlite] Multi-threading Common Problem >> To: "General Discussion of SQLite Database" >> Date: Tuesday, May 24, 2011, 3:10 AM >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> On 05/23/2011 09:12 PM, John Deal wrote: >> > I guess I am lost on how to obtain a many reader or >> one writer mutex in SQLite. >> >> You are confusing locks on the database and locks in the >> library on a >> sqlite3 pointer. The latter is what the mutex alloc >> function you reference >> is about and there there is no reader/writer >> mechanism. Access has to be >> serialized. >> >> > You are correct in the locking article referenced I >> want a mutex that can have the lock states of shared, >> pending, and exclusive. >> >> Those are locks on the database which you get through >> regular operations and >> transactions. >> >> Roger >> -BEGIN PGP SIGNATURE- >> Version: GnuPG v1.4.11 (GNU/Linux) >> >> iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk >> VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp >> =rXCY >> -END PGP SIGNATURE- >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-threading Common Problem
Hello Pavel, I don't want per-say to remove my pthread_rwlock() but the main point of this discussion is I should not have to use pthread_rwlock(). Others have mentioned I should be using SQLite-specific mechanisms to achieve the same results. I am just trying to understand how to do that. Pthread_rwlock() works fine. Thanks, John --- On Tue, 5/24/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Multi-threading Common Problem > To: "General Discussion of SQLite Database" > Date: Tuesday, May 24, 2011, 9:51 AM > > I have all writes in > transactions. If I deactivate my pthread_rwlock() that > enforce the above, several writes fail with a "database > locked" error (I assume it is returning SQLITE_BUSY). > > > > So how do I implement the equivalent of a > pthread_rwlock() using SQLite mechinisms? > > When SQLITE_BUSY in a reader transaction is returned just > wait a > little bit and try again. Also you can benefit from > sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html). > > Another question is why do you want to get read of > pthread_rwlock if > it works for you? > > > Pavel > > > On Tue, May 24, 2011 at 7:43 AM, John Deal > wrote: > > Hello Roger, > > > > Sorry to be so brain-dead but I am still confused. I > have multiple threads, each with their own DB connection. > I want to allow multiple readers accessing the DB at the > same time since nothing is changing. However, if a writes > is to take place, I want all readers to finish their reads > and give the writer exclusive access. Once the writer is > done, the readers can come back in. > > > > I have all writes in transactions. If I deactivate > my pthread_rwlock() that enforce the above, several writes > fail with a "database locked" error (I assume it is > returning SQLITE_BUSY). With my pthread_rwlock(), I have > multiple threads reading the DB and my writes get the > exclusive access they need. Now I could loop on the write > until it gets in but that seems very wasteful. > > > > So how do I implement the equivalent of a > pthread_rwlock() using SQLite mechinisms? > > > > Thanks, > > > > John > > > > --- On Tue, 5/24/11, Roger Binns > wrote: > > > >> From: Roger Binns > >> Subject: Re: [sqlite] Multi-threading Common > Problem > >> To: "General Discussion of SQLite Database" > >> Date: Tuesday, May 24, 2011, 3:10 AM > >> -BEGIN PGP SIGNED MESSAGE- > >> Hash: SHA1 > >> > >> On 05/23/2011 09:12 PM, John Deal wrote: > >> > I guess I am lost on how to obtain a many > reader or > >> one writer mutex in SQLite. > >> > >> You are confusing locks on the database and locks > in the > >> library on a > >> sqlite3 pointer. The latter is what the mutex > alloc > >> function you reference > >> is about and there there is no reader/writer > >> mechanism. Access has to be > >> serialized. > >> > >> > You are correct in the locking article > referenced I > >> want a mutex that can have the lock states of > shared, > >> pending, and exclusive. > >> > >> Those are locks on the database which you get > through > >> regular operations and > >> transactions. > >> > >> Roger > >> -BEGIN PGP SIGNATURE- > >> Version: GnuPG v1.4.11 (GNU/Linux) > >> > >> > iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk > >> VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp > >> =rXCY > >> -END PGP SIGNATURE- > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database Locked
I'm using sqlite v3 as database backend for an very small web app (some of 20 concurrent users -really serial access users-).. in Ms. Win xp (my test/develop env.) it work perfectly...my web app can handle that load(and even more by My JMeter Tests)... But on my production environment (Linux CentOS)... with 3 concurrent access (CTRL+F5 on diferent Chrome Tabs/3 times)... my web app hang with message "Sqlite Blocked.." I'm using exactly same versions on both environments(for Java, Sqlite JDBC, Resin, App. Pooling, etc..) I know that jdbc pooling is trivial on sqlite (just one user at time can update the DB) but in Win XP. it's running fine Why ? On My Test Page.. i'm doing some just SELECT statement... (no insert, no delete, no update, no ddl) Sorry for my Bad English ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using sqlite.net with Linq to create database
Folks, Has anyone successfully used system.data.linq and system.data.sqlite together with the linq CreateDatabase function? When I try, I get an exception "syntax error near "DATABASE"". If I try to query database existence using Linq's "DatabaseExists" I get a NotImplemented exception in the sqlite ChangeDatabase function - which doesn't make sense to me. Regards, Ruth -- Software Manager& Engineer Tel: 01223 414180 Blog: http://www.ivimey.org/blog LinkedIn: http://uk.linkedin.com/in/ruthivimeycook/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-threading Common Problem
> I don't want per-say to remove my pthread_rwlock() but the main point of this > discussion is I should not have to use pthread_rwlock(). I'd say pthread_rwlock and SQLite-specific mechanisms work completely differently and you should choose depending on what you want to do. As you saw to use SQLite's mechanisms you should write additional code waiting when write lock is released. As you said it's not effective and prone to starvation. But it works across process boundaries when pthread_rwlock works only inside your process although it does all waiting very effectively on a kernel level. So if you will ever want to connect to your database with sqlite3 command line tool for example while your application is running, and you will do some manipulations with the database, then your pthread_rwlock won't work and you will still get SQLITE_BUSY. And now you decide what mechanism is better for you. Pavel On Tue, May 24, 2011 at 10:11 AM, John Deal wrote: > Hello Pavel, > > I don't want per-say to remove my pthread_rwlock() but the main point of this > discussion is I should not have to use pthread_rwlock(). Others have > mentioned I should be using SQLite-specific mechanisms to achieve the same > results. I am just trying to understand how to do that. Pthread_rwlock() > works fine. > > Thanks, > > John > > --- On Tue, 5/24/11, Pavel Ivanov wrote: > >> From: Pavel Ivanov >> Subject: Re: [sqlite] Multi-threading Common Problem >> To: "General Discussion of SQLite Database" >> Date: Tuesday, May 24, 2011, 9:51 AM >> > I have all writes in >> transactions. If I deactivate my pthread_rwlock() that >> enforce the above, several writes fail with a "database >> locked" error (I assume it is returning SQLITE_BUSY). >> > >> > So how do I implement the equivalent of a >> pthread_rwlock() using SQLite mechinisms? >> >> When SQLITE_BUSY in a reader transaction is returned just >> wait a >> little bit and try again. Also you can benefit from >> sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html). >> >> Another question is why do you want to get read of >> pthread_rwlock if >> it works for you? >> >> >> Pavel >> >> >> On Tue, May 24, 2011 at 7:43 AM, John Deal >> wrote: >> > Hello Roger, >> > >> > Sorry to be so brain-dead but I am still confused. I >> have multiple threads, each with their own DB connection. >> I want to allow multiple readers accessing the DB at the >> same time since nothing is changing. However, if a writes >> is to take place, I want all readers to finish their reads >> and give the writer exclusive access. Once the writer is >> done, the readers can come back in. >> > >> > I have all writes in transactions. If I deactivate >> my pthread_rwlock() that enforce the above, several writes >> fail with a "database locked" error (I assume it is >> returning SQLITE_BUSY). With my pthread_rwlock(), I have >> multiple threads reading the DB and my writes get the >> exclusive access they need. Now I could loop on the write >> until it gets in but that seems very wasteful. >> > >> > So how do I implement the equivalent of a >> pthread_rwlock() using SQLite mechinisms? >> > >> > Thanks, >> > >> > John >> > >> > --- On Tue, 5/24/11, Roger Binns >> wrote: >> > >> >> From: Roger Binns >> >> Subject: Re: [sqlite] Multi-threading Common >> Problem >> >> To: "General Discussion of SQLite Database" >> >> Date: Tuesday, May 24, 2011, 3:10 AM >> >> -BEGIN PGP SIGNED MESSAGE- >> >> Hash: SHA1 >> >> >> >> On 05/23/2011 09:12 PM, John Deal wrote: >> >> > I guess I am lost on how to obtain a many >> reader or >> >> one writer mutex in SQLite. >> >> >> >> You are confusing locks on the database and locks >> in the >> >> library on a >> >> sqlite3 pointer. The latter is what the mutex >> alloc >> >> function you reference >> >> is about and there there is no reader/writer >> >> mechanism. Access has to be >> >> serialized. >> >> >> >> > You are correct in the locking article >> referenced I >> >> want a mutex that can have the lock states of >> shared, >> >> pending, and exclusive. >> >> >> >> Those are locks on the database which you get >> through >> >> regular operations and >> >> transactions. >> >> >> >> Roger >> >> -BEGIN PGP SIGNATURE- >> >> Version: GnuPG v1.4.11 (GNU/Linux) >> >> >> >> >> iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk >> >> VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp >> >> =rXCY >> >> -END PGP SIGNATURE- >> >> ___ >> >> sqlite-users mailing list >> >> sqlite-users@sqlite.org >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sql
Re: [sqlite] Using sqlite.net with Linq to create database
> If I try to query database existence using Linq's "DatabaseExists" I get > a NotImplemented exception in the sqlite ChangeDatabase function - which > doesn't make sense to me. SQLite doesn't have a notion of server containing several databases. That's why it makes perfect sense that SQLite doesn't implement DatabaseExists or ChangeDatabase methods. If you want to check database existence just check if file exists in the file system. If you want to use new database just open new connection to it. Also if you want to create a new database just open connection to it, it will be created automatically. Sorry I can't say what syntax for all those things it is in Linq. Pavel On Tue, May 24, 2011 at 10:28 AM, Ruth Ivimey-Cook wrote: > Folks, > > Has anyone successfully used system.data.linq and system.data.sqlite > together with the linq CreateDatabase function? > > When I try, I get an exception "syntax error near "DATABASE"". > > If I try to query database existence using Linq's "DatabaseExists" I get > a NotImplemented exception in the sqlite ChangeDatabase function - which > doesn't make sense to me. > > Regards, > Ruth > > -- > Software Manager& Engineer > Tel: 01223 414180 > Blog: http://www.ivimey.org/blog > LinkedIn: http://uk.linkedin.com/in/ruthivimeycook/ > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-threading Common Problem
Hello Pavel, Thanks. This is the conclusion I was arriving at. I do use the Sqlite3 utility but only for looking at test results when the server is in an inactive state. Thanks, John --- On Tue, 5/24/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Multi-threading Common Problem > To: "General Discussion of SQLite Database" > Date: Tuesday, May 24, 2011, 10:32 AM > > I don't want per-say to remove > my pthread_rwlock() but the main point of this discussion is > I should not have to use pthread_rwlock(). > > I'd say pthread_rwlock and SQLite-specific mechanisms work > completely > differently and you should choose depending on what you > want to do. As > you saw to use SQLite's mechanisms you should write > additional code > waiting when write lock is released. As you said it's not > effective > and prone to starvation. But it works across process > boundaries when > pthread_rwlock works only inside your process although it > does all > waiting very effectively on a kernel level. > So if you will ever want to connect to your database with > sqlite3 > command line tool for example while your application is > running, and > you will do some manipulations with the database, then > your > pthread_rwlock won't work and you will still get > SQLITE_BUSY. And now > you decide what mechanism is better for you. > > > Pavel > > > On Tue, May 24, 2011 at 10:11 AM, John Deal > wrote: > > Hello Pavel, > > > > I don't want per-say to remove my pthread_rwlock() but > the main point of this discussion is I should not have to > use pthread_rwlock(). Others have mentioned I should be > using SQLite-specific mechanisms to achieve the same > results. I am just trying to understand how to do that. > Pthread_rwlock() works fine. > > > > Thanks, > > > > John > > > > --- On Tue, 5/24/11, Pavel Ivanov > wrote: > > > >> From: Pavel Ivanov > >> Subject: Re: [sqlite] Multi-threading Common > Problem > >> To: "General Discussion of SQLite Database" > >> Date: Tuesday, May 24, 2011, 9:51 AM > >> > I have all writes in > >> transactions. If I deactivate my > pthread_rwlock() that > >> enforce the above, several writes fail with a > "database > >> locked" error (I assume it is returning > SQLITE_BUSY). > >> > > >> > So how do I implement the equivalent of a > >> pthread_rwlock() using SQLite mechinisms? > >> > >> When SQLITE_BUSY in a reader transaction is > returned just > >> wait a > >> little bit and try again. Also you can benefit > from > >> sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html). > >> > >> Another question is why do you want to get read > of > >> pthread_rwlock if > >> it works for you? > >> > >> > >> Pavel > >> > >> > >> On Tue, May 24, 2011 at 7:43 AM, John Deal > >> wrote: > >> > Hello Roger, > >> > > >> > Sorry to be so brain-dead but I am still > confused. I > >> have multiple threads, each with their own DB > connection. > >> I want to allow multiple readers accessing the > DB at the > >> same time since nothing is changing. However, if > a writes > >> is to take place, I want all readers to finish > their reads > >> and give the writer exclusive access. Once the > writer is > >> done, the readers can come back in. > >> > > >> > I have all writes in transactions. If I > deactivate > >> my pthread_rwlock() that enforce the above, > several writes > >> fail with a "database locked" error (I assume it > is > >> returning SQLITE_BUSY). With my > pthread_rwlock(), I have > >> multiple threads reading the DB and my writes get > the > >> exclusive access they need. Now I could loop on > the write > >> until it gets in but that seems very wasteful. > >> > > >> > So how do I implement the equivalent of a > >> pthread_rwlock() using SQLite mechinisms? > >> > > >> > Thanks, > >> > > >> > John > >> > > >> > --- On Tue, 5/24/11, Roger Binns > >> wrote: > >> > > >> >> From: Roger Binns > >> >> Subject: Re: [sqlite] Multi-threading > Common > >> Problem > >> >> To: "General Discussion of SQLite > Database" > >> >> Date: Tuesday, May 24, 2011, 3:10 AM > >> >> -BEGIN PGP SIGNED MESSAGE- > >> >> Hash: SHA1 > >> >> > >> >> On 05/23/2011 09:12 PM, John Deal wrote: > >> >> > I guess I am lost on how to obtain a > many > >> reader or > >> >> one writer mutex in SQLite. > >> >> > >> >> You are confusing locks on the database > and locks > >> in the > >> >> library on a > >> >> sqlite3 pointer. The latter is what the > mutex > >> alloc > >> >> function you reference > >> >> is about and there there is no > reader/writer > >> >> mechanism. Access has to be > >> >> serialized. > >> >> > >> >> > You are correct in the locking > article > >> referenced I > >> >> want a mutex that can have the lock > states of > >> shared, > >> >> pending, and exclusive. > >> >> > >> >> Those are locks on the database which you > get > >> through > >> >> regular operations and > >> >> transactions. > >> >> > >> >> Roger > >> >> -BEGIN PGP SIGNATURE- > >> >> Version: GnuP
Re: [sqlite] Using sqlite.net with Linq to create database
Pavel Ivanov wrote: >> If I try to query database existence using Linq's "DatabaseExists" I get >> a NotImplemented exception in the sqlite ChangeDatabase function - which >> doesn't make sense to me. > SQLite doesn't have a notion of server containing several databases. > That's why it makes perfect sense that SQLite doesn't implement > DatabaseExists or ChangeDatabase methods. If you want to check > database existence just check if file exists in the file system. If > you want to use new database just open new connection to it. Also if > you want to create a new database just open connection to it, it will > be created automatically. I wanted to distinguish between a file being present and that file actually being parsable as an sqlite database. I also wanted Linq to create the tables required in the file... i.e. moving from "no file" to s3db with tables". In Linq the CreateDatabase call both creates the database (i.e. in sqlite terms, make an s3db file) and populates that database with tables, according to the metadata and class information provided in the code. Surely, the fact that SQLite doesn't permit one file having several databases should just mean that "CREATE DATABASE ;" is a really simple operation, not that it throws an exception and/or fails. If the s3db file format includes a database name (distinct from the name of the file) then that name can be used for - otherwise, the should be ignored IMO. Regards Ruth -- Software Manager& Engineer Tel: 01223 414180 Blog: http://www.ivimey.org/blog LinkedIn: http://uk.linkedin.com/in/ruthivimeycook/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite.net with Linq to create database
> Surely, the fact that SQLite doesn't permit one file having several > databases should just mean that "CREATE DATABASE ;" is a really > simple operation, not that it throws an exception and/or fails. There's no such command in SQLite. So if Linq can't live without issuing this command then it can't be used with SQLite. Pavel On Tue, May 24, 2011 at 11:05 AM, Ruth Ivimey-Cook wrote: > Pavel Ivanov wrote: >>> If I try to query database existence using Linq's "DatabaseExists" I get >>> a NotImplemented exception in the sqlite ChangeDatabase function - which >>> doesn't make sense to me. >> SQLite doesn't have a notion of server containing several databases. >> That's why it makes perfect sense that SQLite doesn't implement >> DatabaseExists or ChangeDatabase methods. If you want to check >> database existence just check if file exists in the file system. If >> you want to use new database just open new connection to it. Also if >> you want to create a new database just open connection to it, it will >> be created automatically. > I wanted to distinguish between a file being present and that file > actually being parsable as an sqlite database. > > I also wanted Linq to create the tables required in the file... i.e. > moving from "no file" to s3db with tables". > > In Linq the CreateDatabase call both creates the database (i.e. in > sqlite terms, make an s3db file) and populates that database with > tables, according to the metadata and class information provided in the > code. > > Surely, the fact that SQLite doesn't permit one file having several > databases should just mean that "CREATE DATABASE ;" is a really > simple operation, not that it throws an exception and/or fails. > > If the s3db file format includes a database name (distinct from the name > of the file) then that name can be used for - otherwise, the > should be ignored IMO. > > Regards > Ruth > > -- > Software Manager& Engineer > Tel: 01223 414180 > Blog: http://www.ivimey.org/blog > LinkedIn: http://uk.linkedin.com/in/ruthivimeycook/ > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Better way to get records by IDs
"BareFeetWare", on Tuesday, May 24, 2011 8:31 AM wrote... On 23/05/2011, at 11:13 PM, jose isaias cabrera wrote: SharedDB file is about 600 megs shared over a network drive and it's getting slow, but if I get the specific record ID only with the select that I want, it's a lot faster than getting the select with all the items in one shot. SQLite probably does something in the back ground to get things faster when addressed specifically to an ID. 600MB and slow seems like a good candidate for better structuring your database. If you post your full schema, we can suggest a better way to structure it for optimal speed and efficiency. Tom BareFeetWare I know that I have to re-structure the whole thing, and I know I have to do it soon, but, this is now live data and I have over 100K lines of code, which about 10K has to do with sqlite, and I am scared to break anything. When I started this, I was very new to the SQL environment and so I thought of SQL as a huge data as a worksheet, and that is how I created the databases. Now I know better, so I have to break the various tables to normalize them, but it is a lot of data. Some of our fellow SQLiters (P Kishor, yourself, Kees Nuyt, Igor, plus others) have been very good to me. I need to read and learn how to normalize, plus more. But, anyway, for the fun of it I have attached the current schema for your laughing pleasure. :-) By the way, if you feel like suggesting anything, go ahead and make my day some more. .-) thanks for all the help. josé 11:22:20.86>sqlite3 "K:\Data\OpenJobsTool\AllOpenProjs.db" SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema CREATE TABLE "Alignment" ( keyName primary key, data ); CREATE TABLE Contact ( myKey primary key ); CREATE TABLE "Create-SRC" ( keyName primary key, data ); CREATE TABLE CustDivision ( keyName primary key, data ); CREATE TABLE CustomerInfo (Program PRIMARY KEY, Contact, Terminologist, TechPM,PM, PMBkup, BizType, BizNature, UKPM, Margin, UKXchange, CanXchange, Xtra0, Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9); CREATE TABLE "DOC-Trans" ( keyName primary key, data ); CREATE TABLE "DOC-Valid" ( keyName primary key, data ); CREATE TABLE "DTP" ( keyName primary key, data ); CREATE TABLE DTPSoftware ( keyName primary key, data ); CREATE TABLE "Delivery" ( keyName primary key, data ); CREATE TABLE "File-Proc" ( keyName primary key, data ); CREATE TABLE "GraphEditing" ( keyName primary key, data ); CREATE TABLE "In-Context-Proof" ( keyName primary key, data ); CREATE TABLE "Interpretation" ( keyName primary key, data ); CREATE TABLE JobTranslationWordCount (JobID integer primary key, SubProjID integer, ProjID integer, " Context TM ", " Repetitions ", " 100% ", " 95% - 99% ", "85% - 94% ", " 75% - 84% ", " 50% - 74% ", " No Match ", " Total "); CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, subProjID integer, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir, Delivery Dir, paid, paidDate, notes, status, pages, ta, fromLang, techPM, termPM, Xtra0,Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9, XtraA, XtraB, XtraC, XtraD, XtraE, XtraF ); CREATE TABLE LSOpenProjects ( id integer primary key, ProjID integer, subProjID integer, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir, Delivery Dir, paid, paidDate, notes, status, pages, ta, fromLang, techPM, termPM, Xtra0,Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9, XtraA, XtraB, XtraC, XtraD, XtraE, XtraF ); CREATE TABLE LSOpenSubProjects ( id integer primary key, ProjID integer, subProjID integer, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir, Delivery Dir, paid, paidDate, notes, status, pages, ta, fromLang, techPM, termPM, Xtra0,Xtra1, Xtra2, Xtra3, Xtra4, Xtra5, Xtra6, Xtra7, Xtra8, Xtra9, XtraA, XtraB, XtraC, XtraD, XtraE, XtraF ); CREATE TABLE LogAnalysis ( id integer primary key, subProjID integer, ProjID integer, filename, " Context TM ", " Repetitions ", " 100% ", " 95% - 99% ", " 85% - 94% ", " 75% - 84% ", " 50% - 74% ", " No Match ", Xtra0, Xtra1, Xtra2, Xtra3, Xtra4 ); CREATE TABLE "PM" ( keyName primary key, data ); CRE
[sqlite] [ANN] cubeSQL
Viadana, Italy - SQLabs announced cubeSQL a fully featured and high performance relational database management system built on top of the sqlite database engine. It is the ideal database server for both developers who want to convert a single user database solution to a multiuser project and for companies looking for an affordable, easy to use and easy to maintain database management system. cubeSQL runs on Windows, Mac, Linux and it can be embedded into any iOS and Cocoa application. cubeSQL is incredibly fast, has a small footprint, is highly reliable and it offers some unique features. It can be easily accessed with any JSON client, with PHP, with the native C SDK and with an highly optimized REAL Studio plugin. Some features includes: - Multi-core and multiprocessor aware. - Strong AES encryption (128, 192 and 256 bit). - Supports unlimited connections. - Full ACID (Atomic, Consistent, Isolated, Durable) compliant. - Platform independent storage engine. - Full support of triggers and transactions. - Journal engine for crash recovery. - Supports databases of 2 terabytes. - Supports sqlite 3 databases. - Automatic logging. - Automatic compression. - Multiversion concurrency control (MVCC). - Plugins for extending the SQL language and the custom commands supported by the server. - Restore and backup support. - Mac OS X, Windows and Linux support. - Native 32bit and 64bit supports. ... and much more Minimum requirements: * MacOS X 10.5 or higher * Windows NT/XP/Vista/7/Server or higher * Linux kernel 2.6.2 or higher Pricing and Availability: cubeSQL Developer Edition is completely free of charge and enables developers to create an application based on cubeSQL without paying any fees until they are ready to deploy their application. Commercial license starts at $299 USD. For more information, please visit the SQLabs website: http://www.sqlabs.com -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SEE encryption lib and odbc
Hi all, We are successfully using the SEE module in Sqlite. All runs well but we need to provide a ODBC connection to a limited number of users. Question: is there a Sqlite odbc driver SEE compatible ? Thanks. Regards, Marco Turco IT Business Devl Manager Software XP LLP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SEE encryption lib and odbc
On Tue, May 24, 2011 at 1:36 PM, Marco Turco wrote: > Hi all, > We are successfully using the SEE module in Sqlite. > All runs well but we need to provide a ODBC connection to a limited number > of users. > > Question: is there a Sqlite odbc driver SEE compatible ? > http://www.ch-werner.de/sqliteodbc/ You'll have to recompile it yourself using the SEE sqlite3.c rather than use the provided precompiled binary, of course. Both other than that, everthing should work fine. > Thanks. > > Regards, > > Marco Turco > IT Business Devl Manager > Software XP LLP > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite.net with Linq to create database
On 2011-05-24 09:28, Ruth Ivimey-Cook wrote: > Folks, > > Has anyone successfully used system.data.linq and system.data.sqlite > together with the linq CreateDatabase function? > > When I try, I get an exception "syntax error near "DATABASE"". > > If I try to query database existence using Linq's "DatabaseExists" I get > a NotImplemented exception in the sqlite ChangeDatabase function - which > doesn't make sense to me. I have never in my life messed with Linq, but I did poke around a bit getting the testlinq program to work. Does it work for you? You'll probably have to fix the connection strings in the App.Config for it. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite.net with Linq to create database
Joe Thanks for the work helping make the linq side happen! > Ruth Ivimey-Cook wrote: >> If I try to query database existence using Linq's "DatabaseExists" I get >> a NotImplemented exception in the sqlite ChangeDatabase function - which >> doesn't make sense to me. > I have never in my life messed with Linq, but I did poke around a bit > getting the testlinq program to work. All the examples I've seen for sqlite.net have been using direct calls to the api. I am new to Linq too but it seemed appropriate to the matter at hand. > Does it work for you? You'll probably have to fix the connection > strings in the App.Config for it. I've done the app.config thing to register the linq provider, though in fact I couldn't make it work when I tried; I ended up passing an explicit sqlite connection into linq (which also means I can specify a file at runtime). So far the create database thing is my bugbear. It doesn't seem hard or bad practice to implement "create database" as a no-op (or better, an expunge of the current file so that the app sees an empty database). I guess a workaround could be to modify the wrapper to search/replace create database out of existence... that really does seem like a hack! Sadly Linq as implemented in .net 3.5 doesn't allow for database-less databases :-) Regards Ruth -- Software Manager& Engineer Tel: 01223 414180 Blog: http://www.ivimey.org/blog LinkedIn: http://uk.linkedin.com/in/ruthivimeycook/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite.net with Linq to create database
On 2011-05-24 18:37, Ruth Ivimey-Cook wrote: > So far the create database thing is my bugbear. It doesn't seem hard or > bad practice to implement "create database" as a no-op (or better, an > expunge of the current file so that the app sees an empty database). > > I guess a workaround could be to modify the wrapper to search/replace > create database out of existence... that really does seem like a hack! According to this: http://msdn.microsoft.com/en-us/library/bb399375%28v=VS.90%29.aspx you need to create a class for your database that inherits from DataContext. CreateDatabase is a DataContext method. So it looks to me like you need to override that method in your derived database class. This is just from poking around on MSDN. I am no doubt terribly wrong, and will now be mocked. Look at the testlinq program and see how it connects. Maybe that will give you some pointers. Joe D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
