Re: [sqlite] multiple writers for in-memory datastore
interesting. thanks for the tip. I"ll give it some consideration. -James On Apr 21, 2008, at 1:07 :50PM, Scott Hess wrote: > If you create a file on disk and set PRAGMA synchronous = OFF, you > should get pretty close to the performance of a shared in-memory > database on most modern desktop operating systems - maybe close enough > that you won't care to do anything beyond that. If you further look > at the recent discussion/patch to disable journaling entirely, you > should get even closer. Going this route means you won't have to > worry so much about the case where someone accidentally pumps 4 gig of > data into your database and sucks up all RAM. > > Keep in mind that if you do these things, then it is quite trivial to > generate corrupt database files if your app or OS crashes. So you > need to arrange to delete database files on app start-up to reset your > state (an in-memory database wouldn't have that problem!). On a > Unix-based system, you may be able to open the database then delete > the underlying path, but that may not work for however you are sharing > things. > > -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
If you create a file on disk and set PRAGMA synchronous = OFF, you should get pretty close to the performance of a shared in-memory database on most modern desktop operating systems - maybe close enough that you won't care to do anything beyond that. If you further look at the recent discussion/patch to disable journaling entirely, you should get even closer. Going this route means you won't have to worry so much about the case where someone accidentally pumps 4 gig of data into your database and sucks up all RAM. Keep in mind that if you do these things, then it is quite trivial to generate corrupt database files if your app or OS crashes. So you need to arrange to delete database files on app start-up to reset your state (an in-memory database wouldn't have that problem!). On a Unix-based system, you may be able to open the database then delete the underlying path, but that may not work for however you are sharing things. -scott On Fri, Apr 18, 2008 at 11:25 AM, James Gregurich <[EMAIL PROTECTED]> wrote: > > > I'm working on a commercial, boxed, desktop product. I can't be > creating new mounted disks on a customer's system every time he uses > my application. > > > > How about this... > > > suppose I create a temporary db file on disk. Each task ( a thread) > opens a connection to the temp file and attaches an in-memory db to > it. The task then writes to tables in the attached in-memory db. When > the task is done, the tables in the in-memory db are merged into the > disk file and the attached in-memory db is closed. reader connections > would only read from the disk file. > > Will such a design give me full concurrency on my writer tasks until > they are ready to flush their results to the disk file? As I > understand it, the attached db won't be locked by reading done on the > disk file. > > > thanks, > James > > > > On Apr 18, 2008, at 10:33 :39AM, Dennis Cote wrote: > >> James Gregurich wrote: >>> If the sqlite statement had a temporary storage area so that I could >>> load up a bunch of rows and then commit them in one shot so that the >>> lock on the db was not held very long by a single transaction, that >>> would probably work. >>> >> >> Using a RAM disk you could insert rows into one database as they are >> generated. This would be your batch. >> >> Then periodically attach that database to the main database and copy >> all >> the new rows to the main DB table in one in a auto transaction. >> >> attach "batch.db" as batch; >> begin; >> insert into main.tbl select * from batch.tbl; >> delete from batch.tbl; >> commit; >> detach batch; >> >> This will only lock the main database for a short period while it is >> updated. >> >>> However, my reading of the documentation leads me to believe that >>> using the bind functions in a loop with an insert statement will lock >>> the entire in-memory DB until the bulk insert is donewhich >>> means I >>> would get no benefit from concurrency. >>> >>> Is this correct? >> >> Readers are blocked by a writer until the write transaction is >> committed. >> >>> >>> BTW: does the question I posed on modifying the library to add a >>> feature to attach an in-memory data stores to another one via the C >>> API belong on the sqlite-dev list? >>> >> >> That list doesn't get much traffic. Your question was fine here. >> >> It would be fairly involved to change the handling of in memory >> databases. They don't have names to use with the attach command, and >> they don't do any locking since they can only be accessed from a >> single >> connection currently. The locking in SQLite is done with POSIX file >> locks which can't be used for in memory databases since they aren't >> files. You're welcome to try of course, but it seems like a lot of >> work >> for little return when there are other ways to do what you want. >> >> HTH >> Dennis Cote >> >> >> ___ >> 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] multiple writers for in-memory datastore
yes. However, CoreData queues up modified managed objects in a managed object context and then commits them all in one shot making sure the serialization is done on the back side. So, it does basically what someone here recommended earlier. I just don't have to write the mechanism myself. Actually, CoreData is what I intended to use at first. However, I have explored the possibility of directly using SQLite instead to keep my document readers and their data management cross-platform. On Apr 20, 2008, at 8:31 AM, Dennis Cote wrote: > James Gregurich wrote: >> I think I will go with CoreData on MacOSX and figure out something >> else to do on Windows later. >> >> >> > You do know that CoreData uses SQLite for its persistant storage. > > Dennis Cote > ___ > 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] multiple writers for in-memory datastore
James Gregurich wrote: > I think I will go with CoreData on MacOSX and figure out something > else to do on Windows later. > > > You do know that CoreData uses SQLite for its persistant storage. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
On Apr 20, 2008, at 12:29 AM, James Gregurich wrote: > > oh good! That isn't the version that ships with Leopard, but I can > live with deploying my own version as part of my app. > > Will l get the writer parallelism I'm after as long as each thread > writes exclusively into its own attached db? > > > in other wordstwo bulk insert operations going on simultaneously > on the same connection but each insert operation going into a > different attached in-memory db. Probably not. Each sqlite3* handle has a single mutex that it uses to serialize operations. Dan. > > > On Apr 19, 2008, at 9:20 AM, Dan wrote: > >> >> On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: >> >>> >>> I'll ask this question. The answer is probably "no," but I'll ask it >>> for the sake of completeness. >>> >>> >>> Suppose I created an in-memory db. I use the attach command to >>> associate an additional in-memory db. Suppose I assign the main >>> db to >>> thread 1 and the associated db to thread 2. Can I share the >>> connection >>> across the 2 threads if each thread works exclusively in its own db? >>> >>> I am aware that the connection is generally not threadsafe, but will >>> it work if the two threads don't operate on the same db at the same >>> time? >> >> As of 3.5, sqlite connections are threadsafe by default. With >> earlier versions, this trick will not work. >> >> Dan. >> >> >> ___ >> 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] multiple writers for in-memory datastore
for those who may be interested: I ran a test with SQLite version: 3.5.8 I tried the scheme described earlier with each thread sharing a connection but writing into its own attached in-memory db on that connection. Didn't work. all but the first writer thread failed with a SQLITE_ERROR oh well. I think I will go with CoreData on MacOSX and figure out something else to do on Windows later. my thanks to all who attempted to provide a solution. -James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
ok. I think I see what you are suggesting. You want to provide a mechanism to create memory files that SQLite thinks are disk filesthat way you could create multiple connections to them. an interesting idea. However, that may be more effort that I can justify at this point. I'll think about it. yes. I am fluent in STL. On Apr 19, 2008, at 1:19 PM, Virgilio Alexandre Fornazin wrote: > Imagine the following cenario (I assume you know c++ stdlib) > > A map of strings (filenames) to in-memory file handlers (the objects > that > will handle the shared memory or heap files). > > These files handlers will exists until the process exists and do not > receive > a delelefile() vfs call. > > File handlers can synchronize RW-Locks using internal mutex/criticat > sections/semaphores/spin locks, etc. > > When you create a new file in vfs, a new handler is created and > assigned to > that filename and registered in this map. > > > > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of James Gregurich > Sent: sábado, 19 de abril de 2008 17:02 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] multiple writers for in-memory datastore > > > I don't immediately see how that would solve the problem. > > The limitation of interest here (based on my perhaps limited > understanding) is that locking has file-level granularity. I don't > immediately see how a VST implementation would allow for changing the > locking granularity of the overall system. > > -James > > On Apr 19, 2008, at 12:03 PM, Virgilio Fornazin wrote: > >> what about creating a VFS for such task ? Can be accomplished in >> many ways, >> using heap memory, shared memory... not so easy to do, but not much >> complicated too... locking can be provided by multiple-readers >> single-writers locks strategies, etc... >> >> On Sat, Apr 19, 2008 at 2:29 PM, James Gregurich >> <[EMAIL PROTECTED]> >> wrote: >> >>> >>> oh good! That isn't the version that ships with Leopard, but I can >>> live with deploying my own version as part of my app. >>> >>> Will l get the writer parallelism I'm after as long as each thread >>> writes exclusively into its own attached db? >>> >>> >>> in other wordstwo bulk insert operations going on simultaneously >>> on the same connection but each insert operation going into a >>> different attached in-memory db. >>> >>> >>> On Apr 19, 2008, at 9:20 AM, Dan wrote: >>> >>>> >>>> On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: >>>> >>>>> >>>>> I'll ask this question. The answer is probably "no," but I'll ask >>>>> it >>>>> for the sake of completeness. >>>>> >>>>> >>>>> Suppose I created an in-memory db. I use the attach command to >>>>> associate an additional in-memory db. Suppose I assign the main >>>>> db to >>>>> thread 1 and the associated db to thread 2. Can I share the >>>>> connection >>>>> across the 2 threads if each thread works exclusively in its own >>>>> db? >>>>> >>>>> I am aware that the connection is generally not threadsafe, but >>>>> will >>>>> it work if the two threads don't operate on the same db at the >>>>> same >>>>> time? >>>> >>>> As of 3.5, sqlite connections are threadsafe by default. With >>>> earlier versions, this trick will not work. >>>> >>>> Dan. >>>> >>>> >>>> ___ >>>> 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-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] multiple writers for in-memory datastore
Imagine the following cenario (I assume you know c++ stdlib) A map of strings (filenames) to in-memory file handlers (the objects that will handle the shared memory or heap files). These files handlers will exists until the process exists and do not receive a delelefile() vfs call. File handlers can synchronize RW-Locks using internal mutex/criticat sections/semaphores/spin locks, etc. When you create a new file in vfs, a new handler is created and assigned to that filename and registered in this map. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of James Gregurich Sent: sábado, 19 de abril de 2008 17:02 To: General Discussion of SQLite Database Subject: Re: [sqlite] multiple writers for in-memory datastore I don't immediately see how that would solve the problem. The limitation of interest here (based on my perhaps limited understanding) is that locking has file-level granularity. I don't immediately see how a VST implementation would allow for changing the locking granularity of the overall system. -James On Apr 19, 2008, at 12:03 PM, Virgilio Fornazin wrote: > what about creating a VFS for such task ? Can be accomplished in > many ways, > using heap memory, shared memory... not so easy to do, but not much > complicated too... locking can be provided by multiple-readers > single-writers locks strategies, etc... > > On Sat, Apr 19, 2008 at 2:29 PM, James Gregurich <[EMAIL PROTECTED]> > wrote: > >> >> oh good! That isn't the version that ships with Leopard, but I can >> live with deploying my own version as part of my app. >> >> Will l get the writer parallelism I'm after as long as each thread >> writes exclusively into its own attached db? >> >> >> in other wordstwo bulk insert operations going on simultaneously >> on the same connection but each insert operation going into a >> different attached in-memory db. >> >> >> On Apr 19, 2008, at 9:20 AM, Dan wrote: >> >>> >>> On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: >>> >>>> >>>> I'll ask this question. The answer is probably "no," but I'll ask >>>> it >>>> for the sake of completeness. >>>> >>>> >>>> Suppose I created an in-memory db. I use the attach command to >>>> associate an additional in-memory db. Suppose I assign the main >>>> db to >>>> thread 1 and the associated db to thread 2. Can I share the >>>> connection >>>> across the 2 threads if each thread works exclusively in its own >>>> db? >>>> >>>> I am aware that the connection is generally not threadsafe, but >>>> will >>>> it work if the two threads don't operate on the same db at the same >>>> time? >>> >>> As of 3.5, sqlite connections are threadsafe by default. With >>> earlier versions, this trick will not work. >>> >>> Dan. >>> >>> >>> ___ >>> 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
I don't immediately see how that would solve the problem. The limitation of interest here (based on my perhaps limited understanding) is that locking has file-level granularity. I don't immediately see how a VST implementation would allow for changing the locking granularity of the overall system. -James On Apr 19, 2008, at 12:03 PM, Virgilio Fornazin wrote: > what about creating a VFS for such task ? Can be accomplished in > many ways, > using heap memory, shared memory... not so easy to do, but not much > complicated too... locking can be provided by multiple-readers > single-writers locks strategies, etc... > > On Sat, Apr 19, 2008 at 2:29 PM, James Gregurich <[EMAIL PROTECTED]> > wrote: > >> >> oh good! That isn't the version that ships with Leopard, but I can >> live with deploying my own version as part of my app. >> >> Will l get the writer parallelism I'm after as long as each thread >> writes exclusively into its own attached db? >> >> >> in other wordstwo bulk insert operations going on simultaneously >> on the same connection but each insert operation going into a >> different attached in-memory db. >> >> >> On Apr 19, 2008, at 9:20 AM, Dan wrote: >> >>> >>> On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: >>> I'll ask this question. The answer is probably "no," but I'll ask it for the sake of completeness. Suppose I created an in-memory db. I use the attach command to associate an additional in-memory db. Suppose I assign the main db to thread 1 and the associated db to thread 2. Can I share the connection across the 2 threads if each thread works exclusively in its own db? I am aware that the connection is generally not threadsafe, but will it work if the two threads don't operate on the same db at the same time? >>> >>> As of 3.5, sqlite connections are threadsafe by default. With >>> earlier versions, this trick will not work. >>> >>> Dan. >>> >>> >>> ___ >>> 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
Re: [sqlite] multiple writers for in-memory datastore
what about creating a VFS for such task ? Can be accomplished in many ways, using heap memory, shared memory... not so easy to do, but not much complicated too... locking can be provided by multiple-readers single-writers locks strategies, etc... On Sat, Apr 19, 2008 at 2:29 PM, James Gregurich <[EMAIL PROTECTED]> wrote: > > oh good! That isn't the version that ships with Leopard, but I can > live with deploying my own version as part of my app. > > Will l get the writer parallelism I'm after as long as each thread > writes exclusively into its own attached db? > > > in other wordstwo bulk insert operations going on simultaneously > on the same connection but each insert operation going into a > different attached in-memory db. > > > On Apr 19, 2008, at 9:20 AM, Dan wrote: > > > > > On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: > > > >> > >> I'll ask this question. The answer is probably "no," but I'll ask it > >> for the sake of completeness. > >> > >> > >> Suppose I created an in-memory db. I use the attach command to > >> associate an additional in-memory db. Suppose I assign the main db to > >> thread 1 and the associated db to thread 2. Can I share the > >> connection > >> across the 2 threads if each thread works exclusively in its own db? > >> > >> I am aware that the connection is generally not threadsafe, but will > >> it work if the two threads don't operate on the same db at the same > >> time? > > > > As of 3.5, sqlite connections are threadsafe by default. With > > earlier versions, this trick will not work. > > > > Dan. > > > > > > ___ > > 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] multiple writers for in-memory datastore
oh good! That isn't the version that ships with Leopard, but I can live with deploying my own version as part of my app. Will l get the writer parallelism I'm after as long as each thread writes exclusively into its own attached db? in other wordstwo bulk insert operations going on simultaneously on the same connection but each insert operation going into a different attached in-memory db. On Apr 19, 2008, at 9:20 AM, Dan wrote: > > On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: > >> >> I'll ask this question. The answer is probably "no," but I'll ask it >> for the sake of completeness. >> >> >> Suppose I created an in-memory db. I use the attach command to >> associate an additional in-memory db. Suppose I assign the main db to >> thread 1 and the associated db to thread 2. Can I share the >> connection >> across the 2 threads if each thread works exclusively in its own db? >> >> I am aware that the connection is generally not threadsafe, but will >> it work if the two threads don't operate on the same db at the same >> time? > > As of 3.5, sqlite connections are threadsafe by default. With > earlier versions, this trick will not work. > > Dan. > > > ___ > 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] multiple writers for in-memory datastore
On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: > > I'll ask this question. The answer is probably "no," but I'll ask it > for the sake of completeness. > > > Suppose I created an in-memory db. I use the attach command to > associate an additional in-memory db. Suppose I assign the main db to > thread 1 and the associated db to thread 2. Can I share the connection > across the 2 threads if each thread works exclusively in its own db? > > I am aware that the connection is generally not threadsafe, but will > it work if the two threads don't operate on the same db at the same > time? As of 3.5, sqlite connections are threadsafe by default. With earlier versions, this trick will not work. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
I'll ask this question. The answer is probably "no," but I'll ask it for the sake of completeness. Suppose I created an in-memory db. I use the attach command to associate an additional in-memory db. Suppose I assign the main db to thread 1 and the associated db to thread 2. Can I share the connection across the 2 threads if each thread works exclusively in its own db? I am aware that the connection is generally not threadsafe, but will it work if the two threads don't operate on the same db at the same time? thanks, James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
How about this instead. Read your records, parse and format into some known format by your application. Write the data to disk in a file. Then put a single entry into a sqlite table. specifing the on disk file name. Sqlite may only have one write operation running concurrently. There are no concurrent writes. You can have of course concurrent reads. does that help? Ken James Gregurich <[EMAIL PROTECTED]> wrote: On Apr 18, 2008, at 2:33 :32PM, Dennis Cote wrote: > > To share an attached database the threads must be able to name it, and > this is only possible with a file database. you could change the open() function to be able to assign a name to an in-memory db and then keep a mapping of all the names internally. You could also provide an API call that takes an existing connection to an in-memory store and attaches its db to another pre-existing db on another connection. Seems like the underlying foundation is already there to do it. But, I admit, I have no knowledge of the implementation details of SQLite. > > Perhaps you can replace the proprietary file format with a permanent > SQLite database file (and then again maybe not). We don't control those formats. they are controlled by certain large, well-known software companies. we just reverse-engineer their formats. > You could implement a server thread that accesses a single memory > database which accepts commands from, and passes the results back to, > your other threads as John suggested. You will have to provide some > form > of resource management for the shared resource, whether it is a shared > memory database, file, or something else. unless I misunderstand the way the SQLite API works, that isn't really practical. my task is to read a chunk of data, parse it and insert a record into table ( a number of records in a loop ofcourse). To do that, I have to prepare a statement and then bind data values to the to the statement in a loop. Once I begin the transaction and prepare the statement, the entire db is locked up for the duration of the bulk insert. If that is true, then I'll lose all opportunity for parallelism. If I have to write my own temporary storage containers to hold data while it waits to be committed by a datastore thread, then I might as well just write my own containers and be done with the task rather than going to the expense of using a SQL data store. One reason to use SQLite is that it would take care of the synchronization of multiple writers and readers for me. If I have to write all that myself, then why bother with SQLite? On of my options is to use CoreData on the macintosh. That will do what I want as it caches record inserts and does one big commitand it handles the synchronization. However, what I do do with the lovable Windows platform? oh well. I"ll figure it all out some how. ___ 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] multiple writers for in-memory datastore
On Apr 18, 2008, at 2:33 :32PM, Dennis Cote wrote: > > To share an attached database the threads must be able to name it, and > this is only possible with a file database. you could change the open() function to be able to assign a name to an in-memory db and then keep a mapping of all the names internally. You could also provide an API call that takes an existing connection to an in-memory store and attaches its db to another pre-existing db on another connection. Seems like the underlying foundation is already there to do it. But, I admit, I have no knowledge of the implementation details of SQLite. > > Perhaps you can replace the proprietary file format with a permanent > SQLite database file (and then again maybe not). We don't control those formats. they are controlled by certain large, well-known software companies. we just reverse-engineer their formats. > You could implement a server thread that accesses a single memory > database which accepts commands from, and passes the results back to, > your other threads as John suggested. You will have to provide some > form > of resource management for the shared resource, whether it is a shared > memory database, file, or something else. unless I misunderstand the way the SQLite API works, that isn't really practical. my task is to read a chunk of data, parse it and insert a record into table ( a number of records in a loop ofcourse). To do that, I have to prepare a statement and then bind data values to the to the statement in a loop. Once I begin the transaction and prepare the statement, the entire db is locked up for the duration of the bulk insert. If that is true, then I'll lose all opportunity for parallelism. If I have to write my own temporary storage containers to hold data while it waits to be committed by a datastore thread, then I might as well just write my own containers and be done with the task rather than going to the expense of using a SQL data store. One reason to use SQLite is that it would take care of the synchronization of multiple writers and readers for me. If I have to write all that myself, then why bother with SQLite? On of my options is to use CoreData on the macintosh. That will do what I want as it caches record inserts and does one big commitand it handles the synchronization. However, what I do do with the lovable Windows platform? oh well. I"ll figure it all out some how. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
James Gregurich wrote: >> You will have to open the memory database and attach the db file since >> SQLite can't attach to a memory database. > > is this information wrong? > http://www.blitzbasic.com/Community/posts.php?topic=60981 > No it's not. I was mistaken. SQLite can attach a memory database to an open connection, even another memory database. Each such memory database is independent, and there is no way to use an existing memory database in an attach command. Attaching to a memory database always creates a new empty in memory database. To share an attached database the threads must be able to name it, and this is only possible with a file database. > yes. the nature of the application is such that the data is loaded > from a proprietary file format, processed, and presented to the user. > There is no need to store the data back on disk. One of the best uses of SQlite is as an application's file format. See http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite for more info. Perhaps you can replace the proprietary file format with a permanent SQLite database file (and then again maybe not). > I'm interested in a flexible, convenient, in-memory datastore. I > thought it was going to work but was stopped dead in my tracks when I > realized I couldn't open multiple connections on an in-memory db. You could implement a server thread that accesses a single memory database which accepts commands from, and passes the results back to, your other threads as John suggested. You will have to provide some form of resource management for the shared resource, whether it is a shared memory database, file, or something else. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
On Apr 18, 2008, at 1:25 :36PM, Dennis Cote wrote: > James Gregurich wrote: >> >> suppose I create a temporary db file on disk. Each task ( a thread) >> opens a connection to the temp file and attaches an in-memory db to >> it. > > You will have to open the memory database and attach the db file since > SQLite can't attach to a memory database. is this information wrong? http://www.blitzbasic.com/Community/posts.php?topic=60981 > > > Why have you proposed to use a temporary database file? Can this > data be > destroyed between executions? yes. the nature of the application is such that the data is loaded from a proprietary file format, processed, and presented to the user. There is no need to store the data back on disk. I'm interested in a flexible, convenient, in-memory datastore. I thought it was going to work but was stopped dead in my tracks when I realized I couldn't open multiple connections on an in-memory db. > > The way I have suggested, the readers only open the database file. > They > can read as long as no update is in progress. The updates will be > batched into the independent memory database. During an update the > database file will be locked, so readers will have to wait. great. that will get me concurrency at the expense of unnecessary disk I/O. I suppose I will have to decide if using sqlite is worth the price. I may just fall back to defining a table as a std::vector<> of std::map<> entries and skip the whole idea of using an embedded db engine. I'll have to think about all of this. thanks, James ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
James Gregurich wrote: > > suppose I create a temporary db file on disk. Each task ( a thread) > opens a connection to the temp file and attaches an in-memory db to > it. You will have to open the memory database and attach the db file since SQLite can't attach to a memory database. I would operate it in much the same fashion as I described before, with the memory database used as the batch database, and the file database is the main table storage. Attach to the file, perform the update transaction, and then detach the file. Readers would read open and read from the main database file. Why have you proposed to use a temporary database file? Can this data be destroyed between executions? > > Will such a design give me full concurrency on my writer tasks until > they are ready to flush their results to the disk file? As I > understand it, the attached db won't be locked by reading done on the > disk file. > The way I have suggested, the readers only open the database file. They can read as long as no update is in progress. The updates will be batched into the independent memory database. During an update the database file will be locked, so readers will have to wait. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
I'm working on a commercial, boxed, desktop product. I can't be creating new mounted disks on a customer's system every time he uses my application. How about this... suppose I create a temporary db file on disk. Each task ( a thread) opens a connection to the temp file and attaches an in-memory db to it. The task then writes to tables in the attached in-memory db. When the task is done, the tables in the in-memory db are merged into the disk file and the attached in-memory db is closed. reader connections would only read from the disk file. Will such a design give me full concurrency on my writer tasks until they are ready to flush their results to the disk file? As I understand it, the attached db won't be locked by reading done on the disk file. thanks, James On Apr 18, 2008, at 10:33 :39AM, Dennis Cote wrote: > James Gregurich wrote: >> If the sqlite statement had a temporary storage area so that I could >> load up a bunch of rows and then commit them in one shot so that the >> lock on the db was not held very long by a single transaction, that >> would probably work. >> > > Using a RAM disk you could insert rows into one database as they are > generated. This would be your batch. > > Then periodically attach that database to the main database and copy > all > the new rows to the main DB table in one in a auto transaction. > > attach "batch.db" as batch; > begin; > insert into main.tbl select * from batch.tbl; > delete from batch.tbl; > commit; > detach batch; > > This will only lock the main database for a short period while it is > updated. > >> However, my reading of the documentation leads me to believe that >> using the bind functions in a loop with an insert statement will lock >> the entire in-memory DB until the bulk insert is donewhich >> means I >> would get no benefit from concurrency. >> >> Is this correct? > > Readers are blocked by a writer until the write transaction is > committed. > >> >> BTW: does the question I posed on modifying the library to add a >> feature to attach an in-memory data stores to another one via the C >> API belong on the sqlite-dev list? >> > > That list doesn't get much traffic. Your question was fine here. > > It would be fairly involved to change the handling of in memory > databases. They don't have names to use with the attach command, and > they don't do any locking since they can only be accessed from a > single > connection currently. The locking in SQLite is done with POSIX file > locks which can't be used for in memory databases since they aren't > files. You're welcome to try of course, but it seems like a lot of > work > for little return when there are other ways to do what you want. > > HTH > Dennis Cote > > > ___ > 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] multiple writers for in-memory datastore
James Gregurich wrote: > If the sqlite statement had a temporary storage area so that I could > load up a bunch of rows and then commit them in one shot so that the > lock on the db was not held very long by a single transaction, that > would probably work. > Using a RAM disk you could insert rows into one database as they are generated. This would be your batch. Then periodically attach that database to the main database and copy all the new rows to the main DB table in one in a auto transaction. attach "batch.db" as batch; begin; insert into main.tbl select * from batch.tbl; delete from batch.tbl; commit; detach batch; This will only lock the main database for a short period while it is updated. > However, my reading of the documentation leads me to believe that > using the bind functions in a loop with an insert statement will lock > the entire in-memory DB until the bulk insert is donewhich means I > would get no benefit from concurrency. > > Is this correct? Readers are blocked by a writer until the write transaction is committed. > > BTW: does the question I posed on modifying the library to add a > feature to attach an in-memory data stores to another one via the C > API belong on the sqlite-dev list? > That list doesn't get much traffic. Your question was fine here. It would be fairly involved to change the handling of in memory databases. They don't have names to use with the attach command, and they don't do any locking since they can only be accessed from a single connection currently. The locking in SQLite is done with POSIX file locks which can't be used for in memory databases since they aren't files. You're welcome to try of course, but it seems like a lot of work for little return when there are other ways to do what you want. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
If the sqlite statement had a temporary storage area so that I could load up a bunch of rows and then commit them in one shot so that the lock on the db was not held very long by a single transaction, that would probably work. However, my reading of the documentation leads me to believe that using the bind functions in a loop with an insert statement will lock the entire in-memory DB until the bulk insert is donewhich means I would get no benefit from concurrency. Is this correct? BTW: does the question I posed on modifying the library to add a feature to attach an in-memory data stores to another one via the C API belong on the sqlite-dev list? thanks, James On Apr 18, 2008, at 9:43 :22AM, John Stanton wrote: > Just use a thread as a DB handler. Queue transactions to it using > some > IPC mechanism like a message queue or named pipe. Another way would > be > to synchronize access to the DB handle using a mutex. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
Just use a thread as a DB handler. Queue transactions to it using some IPC mechanism like a message queue or named pipe. Another way would be to synchronize access to the DB handle using a mutex. James Gregurich wrote: > hi! > > > I need to set up multiple writers to an in-memory datastore. I just > discovered that you can't have more than one connection to an in- > memory store. > > I can give each task its own independent datastore if there is a way I > can merge the contents of each store into a central store. Is there a > way to attach an existing in-memory store to another in-memory store? > > If not, how hard would it be to modify the sqlite source to allow such > an attachment to be made given the two connection pointers to two > independent stores? > > > thanks, > James Gregurich > ___ > 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] multiple writers for in-memory datastore
James Gregurich wrote: > > I need to set up multiple writers to an in-memory datastore. I just > discovered that you can't have more than one connection to an in- > memory store. > > I can give each task its own independent datastore if there is a way I > can merge the contents of each store into a central store. Is there a > way to attach an existing in-memory store to another in-memory store? > No, you can't do that in SQLite. You can get the same effect by storing a database file on a RAM disk. Now you can have multiple connections and SQLite will manage the locking for you. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
