Re: [sqlite] Serialize an in-memory database
- Original Message - From: Donald Griggs To: phi...@blastbay.com ; General Discussion of SQLite Database Sent: Thursday, June 06, 2013 3:13 PM Subject: Re: [sqlite] Serialize an in-memory database Hi Philip, Maybe neither of these two thoughts are helpful, but fyi: 1. Licensing for existing memory vfs. Regarding this memory vfs implementation referenced earlier: http://article.gmane.org/gmane.comp.db.sqlite.general/46450 http://spserver.googlecode.com/files/spmemvfs-0.1.src.tar.gz Would it not be worth an email to the author ( gmail user stephen.nil ) to see if he might quickly release his code (already open source) to public domain or another acceptable license? 2. Performance of existing solutions. Regarding, "I would like to avoid saving the data out to a temporary disk file... That seems wasteful to me" I can certainly understand why you'd write that, but it's different from saying, "I've tested that solution (or put numerical bounds on its maximum likely performance) and found its performance to be unacceptable for my intended use." Of course, using sqlite at all for your purpose (mainly to avoid writing custom sorts, as I understand) is wasteful in some sense of the word -- but I suspect its nevertheless an entirely appropriate application. One of Donald Knuth's famous quotes was, "Premature optimization is the root of all evil (or at least most of it) in programming." If there's a chance that's applicable here (maybe its not), then since the code to backup to a temp file is already present, would it be worth a try? Respectfully, Donald G. (definitely NOT Donald K!) Hi Donald, You have several good points. Let's see if I can respond to them properly: 1. I can definitely drop a line to the author and ask about the licensing. But one appealing part of the vfs idea is actually sitting down and learning enough to implement it myself. If I write the code, I will also be able to debug it much more easily and will learn something new to boot. I have no urgent need for a solution to this problem, so while I certainly would use an existing memory vfs if it was available in SqLite itself I would also enjoy the challenge of writing my own. 2. Let me change the word wasteful to unnecessary. I certainly have no doubt that writing the temporary file and reading it back in would still be acceptable performance wise in my scenario, and I have no figures to prove otherwise. But it seems unnecessary to do so if an alternative method exists. Copying it from one memory location to another seems a lot more elegant if nothing else. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
Hi Philip, Maybe neither of these two thoughts are helpful, but fyi: 1.* Licensing for existing memory vfs.* Regarding this memory vfs implementation referenced earlier: http://article.gmane.org/gmane.comp.db.sqlite.general/46450 http://spserver.googlecode.com/files/spmemvfs-0.1.src.tar.gz Would it not be worth an email to the author ( gmail user stephen.nil ) to see if he might quickly release his code (already open source) to public domain or another acceptable license? 2. * Performance of existing solutions.* * * Regarding, "*I would like to avoid saving the data out to a temporary disk file... That seems wasteful** to me*" I can certainly understand why you'd write that, but it's different from saying, "I've tested that solution (or put numerical bounds on its maximum likely performance) and found its performance to be unacceptable for my intended use." Of course, using sqlite at all for your purpose (mainly to avoid writing custom sorts, as I understand) is wasteful in some sense of the word -- but I suspect its nevertheless an entirely appropriate application. One of Donald Knuth's famous quotes was, "Premature optimization is the root of all evil (or at least most of it) in programming." If there's a chance that's applicable here (maybe its not), then since the code to backup to a temp file is already present, would it be worth a try? Respectfully, Donald G. (definitely NOT Donald K!) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
If you accept the slowdown of .dump you can directly use the backup option... On Thu, Jun 6, 2013 at 2:10 PM, Philip Bennefall wrote: > > - Original Message - From: "Simon Slavin" > > To: ; "General Discussion of SQLite Database" > > Sent: Thursday, June 06, 2013 1:45 PM > > Subject: Re: [sqlite] Serialize an in-memory database > > > > On 6 Jun 2013, at 10:45am, Philip Bennefall wrote: > >> I have a bunch of data structures in memory that I am looking to replace >> with an SqLite database, primarily for the purpose of avoiding reinventing >> the wheel with various sorts etc. I would then like to serialize the data >> into a memory buffer and do additional processing before finally rendering >> it to disk. The additional processing might include compression, encryption, >> and a few other things specific to my application. > > > Two problems: > > Unlike the SQLite file format, the format SQLite uses when it keeps things > in memory is not published, and changes from version to version. Because > the writers of SQLite expect the in-memory format to be accessed only by > things built into the SQLite API, you have to read the source code to know > what's going on. So any routines you come up will have to just deal with > whatever they find rather than trying to understand its structure. Also > your data will be able to restored only back to versions of SQLite where the > internal data format hasn't changed. > > SQLite does not, by its nature, keep everything in one long block of memory. > It allocates and frees smaller blocks of memory as data is stored or > deleted, and also as it needs to create temporary structures such as indexes > needed to speed up a specific command. So turning a stored database into > one stream of octets takes more than just reading a section of memory. > > Rather than try to mess with the internals of SQLite I suspect you would be > better served by doing the following: > > 1) Using SQLite's existing in-memory databases to keep your data in memory > while your app executes. > > 2) Writing your own routine in your preferred programming language to dump > your data into text or octets in memory or disk in whatever format you want. > One standard way to do this is to generate the SQL commands needed to > reproduce your database. Since these are very repetitive standard ASCII > commands they compress down extremely well and you can do encryption at the > same time using any of a number of standard libraries. Data in this format > has the added advantages that it is human-readable (after decompression) and > can be passed straight to sqlite3_exec() to rebuild the database. However, > you might prefer to invent your own format, perhaps more like CSV, that > makes implicit use of your data structures. > > Simon.= > > Hi Simon, > > Oh I never intended to attempt to rip the data right out of an SqLite memory > database. I realize that it is not at all the same as the disk file that I > could create with, say, the backup API. I am considering two options: > > 1. Writing a memory vfs that I use when I want to save my data, backing up > the existing in-memory database to a new database that uses this memory vfs > and then taking the data from the resulting block where SqLite writes what > it thinks is the database file. > > 2. Doing something like .dump in the shell, but writing the output to memory > and then processing that. This seems to be the simplest approach, but would > waste a lot of space and import/export would be slower as far as I can > judge. This would primarily be the case if I export as SQL, as I would then > not be able to reuse prepared statements with parameters but would have to > use sqlite3_exec. > > The memory vfs seems like the most appealing choice in the longterm, but the > second approach is much more straightforward. > > Kind regards, > > Philip Bennefall > ___ > 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] Serialize an in-memory database
- Original Message - From: "Simon Slavin" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 1:45 PM Subject: Re: [sqlite] Serialize an in-memory database On 6 Jun 2013, at 10:45am, Philip Bennefall wrote: I have a bunch of data structures in memory that I am looking to replace with an SqLite database, primarily for the purpose of avoiding reinventing the wheel with various sorts etc. I would then like to serialize the data into a memory buffer and do additional processing before finally rendering it to disk. The additional processing might include compression, encryption, and a few other things specific to my application. Two problems: Unlike the SQLite file format, the format SQLite uses when it keeps things in memory is not published, and changes from version to version. Because the writers of SQLite expect the in-memory format to be accessed only by things built into the SQLite API, you have to read the source code to know what's going on. So any routines you come up will have to just deal with whatever they find rather than trying to understand its structure. Also your data will be able to restored only back to versions of SQLite where the internal data format hasn't changed. SQLite does not, by its nature, keep everything in one long block of memory. It allocates and frees smaller blocks of memory as data is stored or deleted, and also as it needs to create temporary structures such as indexes needed to speed up a specific command. So turning a stored database into one stream of octets takes more than just reading a section of memory. Rather than try to mess with the internals of SQLite I suspect you would be better served by doing the following: 1) Using SQLite's existing in-memory databases to keep your data in memory while your app executes. 2) Writing your own routine in your preferred programming language to dump your data into text or octets in memory or disk in whatever format you want. One standard way to do this is to generate the SQL commands needed to reproduce your database. Since these are very repetitive standard ASCII commands they compress down extremely well and you can do encryption at the same time using any of a number of standard libraries. Data in this format has the added advantages that it is human-readable (after decompression) and can be passed straight to sqlite3_exec() to rebuild the database. However, you might prefer to invent your own format, perhaps more like CSV, that makes implicit use of your data structures. Simon.= Hi Simon, Oh I never intended to attempt to rip the data right out of an SqLite memory database. I realize that it is not at all the same as the disk file that I could create with, say, the backup API. I am considering two options: 1. Writing a memory vfs that I use when I want to save my data, backing up the existing in-memory database to a new database that uses this memory vfs and then taking the data from the resulting block where SqLite writes what it thinks is the database file. 2. Doing something like .dump in the shell, but writing the output to memory and then processing that. This seems to be the simplest approach, but would waste a lot of space and import/export would be slower as far as I can judge. This would primarily be the case if I export as SQL, as I would then not be able to reuse prepared statements with parameters but would have to use sqlite3_exec. The memory vfs seems like the most appealing choice in the longterm, but the second approach is much more straightforward. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On 6 Jun 2013, at 10:45am, Philip Bennefall wrote: > I have a bunch of data structures in memory that I am looking to replace with > an SqLite database, primarily for the purpose of avoiding reinventing the > wheel with various sorts etc. I would then like to serialize the data into a > memory buffer and do additional processing before finally rendering it to > disk. The additional processing might include compression, encryption, and a > few other things specific to my application. Two problems: Unlike the SQLite file format, the format SQLite uses when it keeps things in memory is not published, and changes from version to version. Because the writers of SQLite expect the in-memory format to be accessed only by things built into the SQLite API, you have to read the source code to know what's going on. So any routines you come up will have to just deal with whatever they find rather than trying to understand its structure. Also your data will be able to restored only back to versions of SQLite where the internal data format hasn't changed. SQLite does not, by its nature, keep everything in one long block of memory. It allocates and frees smaller blocks of memory as data is stored or deleted, and also as it needs to create temporary structures such as indexes needed to speed up a specific command. So turning a stored database into one stream of octets takes more than just reading a section of memory. Rather than try to mess with the internals of SQLite I suspect you would be better served by doing the following: 1) Using SQLite's existing in-memory databases to keep your data in memory while your app executes. 2) Writing your own routine in your preferred programming language to dump your data into text or octets in memory or disk in whatever format you want. One standard way to do this is to generate the SQL commands needed to reproduce your database. Since these are very repetitive standard ASCII commands they compress down extremely well and you can do encryption at the same time using any of a number of standard libraries. Data in this format has the added advantages that it is human-readable (after decompression) and can be passed straight to sqlite3_exec() to rebuild the database. However, you might prefer to invent your own format, perhaps more like CSV, that makes implicit use of your data structures. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Simon Slavin" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 10:51 AM Subject: Re: [sqlite] Serialize an in-memory database On 6 Jun 2013, at 9:01am, Philip Bennefall wrote: Since I don't believe that Windows for example has tmpfs (seems to be a Unix thing), would the idea of constructing a vfs that just reads and writes a huge memory block be doable? Doable ? Yes. Use the code from one of the file-based VFSes and replace all the 'read/write to file offset N" with 'read/write to memory offset N', then sort out locking and a few other problems. Tedious and annoying to do but doable if you write C. Of advantage to many users ? I don't know. I don't see what the advantage of doing this is over SQLite's standard ways of storing data in memory or in a file. Who would use this ? Also, I have a question. How big do you expect to make that block of memory you grab when someone creates a new database ? One of the advantages of SQL databases is that they grow as you get more data. You can't do this if you're going to pre-grab a continuous block of memory. Do you expect to use the C function realloc() a lot ? Simon. Hi Simon, For my own part, I would usually have a database that is no more than a few megabyte in size. A generic solution would be a lot harder than the one I am considering for my own project, where I can cut corners due to the fact that I know the size of my data at least roughly. What I want to achieve is to serialize the data in such a way so that I can do other processing on it before I render it to disk, such as custom compression and/or other things. I am aware that there is an SqLite add-on to do this, but aside from the fact that I cannot afford it I don't need to do this processing on the fly either. I just want to take an in-memory database and put it in a compressed and possibly encrypted file on disk in the end, without having to use a temporary file as an intermediary. I write C, so would have no trouble modifying one of the existing vfs example implementations. Correct me if I am wrong, but do I really need to do any kind of locking if I am not working with disk files? I am not working with shared cache, either. I would have one database connection that would only be accessed from one thread. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On 6 Jun 2013, at 9:01am, Philip Bennefall wrote: > Since I don't believe that Windows for example has tmpfs (seems to be a Unix > thing), would the idea of constructing a vfs that just reads and writes a > huge memory block be doable? Doable ? Yes. Use the code from one of the file-based VFSes and replace all the 'read/write to file offset N" with 'read/write to memory offset N', then sort out locking and a few other problems. Tedious and annoying to do but doable if you write C. Of advantage to many users ? I don't know. I don't see what the advantage of doing this is over SQLite's standard ways of storing data in memory or in a file. Who would use this ? Also, I have a question. How big do you expect to make that block of memory you grab when someone creates a new database ? One of the advantages of SQL databases is that they grow as you get more data. You can't do this if you're going to pre-grab a continuous block of memory. Do you expect to use the C function realloc() a lot ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Paolo Bolzoni" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 10:33 AM Subject: Re: [sqlite] Serialize an in-memory database What is you use case? Why do you need this? I am asking because maybe it helps thinking alternate solutions... Hi Paolo, I have a bunch of data structures in memory that I am looking to replace with an SqLite database, primarily for the purpose of avoiding reinventing the wheel with various sorts etc. I would then like to serialize the data into a memory buffer and do additional processing before finally rendering it to disk. The additional processing might include compression, encryption, and a few other things specific to my application. I would like to avoid saving the data out to a temporary disk file, reading it back in, doing my processing, writing it out into a new file and then finally deleting the temporary file. That seems wasteful to me, and so that's why I am looking into solutions that avoid the temporary files. So far, the memory vfs seems like the best approach. Kind regards, Philip Bennefall P.S. I have looked at the encryption and compression add-ons for SqLite, but I don't need encryption/compression on the fly (just on the entire database in one go), and I don't have the money to purchase the code in the first place. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
What is you use case? Why do you need this? I am asking because maybe it helps thinking alternate solutions... On Thu, Jun 6, 2013 at 10:05 AM, Philip Bennefall wrote: > > - Original Message - From: "Paolo Bolzoni" > > > To: ; "General Discussion of SQLite Database" > > Sent: Thursday, June 06, 2013 10:02 AM > > Subject: Re: [sqlite] Serialize an in-memory database > > >> Sorry I am missing a bit, >> What is the problem of using sqlite3_backup again? >> > > Hi Paolo, > > I would like to avoid using a temporary file, but rather just save and load > the database as a memory block. Serialize to and from memory, in other > words. > > > Kind regards, > > Philip Bennefall > ___ > 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] Serialize an in-memory database
- Original Message - From: "Paolo Bolzoni" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 10:02 AM Subject: Re: [sqlite] Serialize an in-memory database Sorry I am missing a bit, What is the problem of using sqlite3_backup again? Hi Paolo, I would like to avoid using a temporary file, but rather just save and load the database as a memory block. Serialize to and from memory, in other words. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
Sorry I am missing a bit, What is the problem of using sqlite3_backup again? On Thu, Jun 6, 2013 at 10:01 AM, Philip Bennefall wrote: > > - Original Message - From: "Simon Slavin" > To: ; "General Discussion of SQLite Database" > > Sent: Thursday, June 06, 2013 12:15 AM > > Subject: Re: [sqlite] Serialize an in-memory database > > > > On 5 Jun 2013, at 8:38pm, Philip Bennefall wrote: > >> On 5 Jun 2013, at 8:32pm, Petite Abeille wrote: >> >>> write to tmpfs… read the file into byte[]… do what you meant to do… to >>> reload… write byte[] do tmpfs… open db… and be merry… or something along >>> these lines... >> >> >> I don't want it in a file, however. I want it in a memory block. > > > That's why you read from tmpfs (or any other file stored in any other file > system) into byte[]. Once your data is in byte[] you will have entire > SQLite database in one run of memory. > > You can't usefully store a memory database of SQLite because SQLite's data > in memory isn't all in one big run of memory. The data is stored in various > little chunks, some here, some there. If you tried to read the data directly > out of those chunks you would have to read lots of little chunks, not one > big run of continuous memory. And you'd be storing lots of pointers to > various locations in memory. When you 'restore' the data back into memory > you're not going to be allocated the same locations in memory so those > pointers won't mean anything any more. > > A database stored in a file, however, has pointers to locations in that file > instead of pointers to locations in memory. If you save and restore the > whole file in one big run, those pointers will become valid again: the same > bits of data will be at the same offsets of the file. > > Doesn't have to be tmpfs. You can use any file system that SQLite thinks is > file storage rather than memory storage. > > Simon. > > Hi Simon, > > Since I don't believe that Windows for example has tmpfs (seems to be a Unix > thing), would the idea of constructing a vfs that just reads and writes a > huge memory block be doable? If so, how difficult of a task do you estimate > that this might be? I want to reuse as much of the existing vfs code as > possible (e.g. I don't want to reimplement randomness, date etc). Could you > possibly give me some pointers? I read the chapter about the virtual file > systems, but it seems incomplete. > > Kind regards, > > Philip Bennefall > ___ > 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] Serialize an in-memory database
- Original Message - From: "Simon Slavin" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 12:15 AM Subject: Re: [sqlite] Serialize an in-memory database On 5 Jun 2013, at 8:38pm, Philip Bennefall wrote: On 5 Jun 2013, at 8:32pm, Petite Abeille wrote: write to tmpfs… read the file into byte[]… do what you meant to do… to reload… write byte[] do tmpfs… open db… and be merry… or something along these lines... I don't want it in a file, however. I want it in a memory block. That's why you read from tmpfs (or any other file stored in any other file system) into byte[]. Once your data is in byte[] you will have entire SQLite database in one run of memory. You can't usefully store a memory database of SQLite because SQLite's data in memory isn't all in one big run of memory. The data is stored in various little chunks, some here, some there. If you tried to read the data directly out of those chunks you would have to read lots of little chunks, not one big run of continuous memory. And you'd be storing lots of pointers to various locations in memory. When you 'restore' the data back into memory you're not going to be allocated the same locations in memory so those pointers won't mean anything any more. A database stored in a file, however, has pointers to locations in that file instead of pointers to locations in memory. If you save and restore the whole file in one big run, those pointers will become valid again: the same bits of data will be at the same offsets of the file. Doesn't have to be tmpfs. You can use any file system that SQLite thinks is file storage rather than memory storage. Simon. Hi Simon, Since I don't believe that Windows for example has tmpfs (seems to be a Unix thing), would the idea of constructing a vfs that just reads and writes a huge memory block be doable? If so, how difficult of a task do you estimate that this might be? I want to reuse as much of the existing vfs code as possible (e.g. I don't want to reimplement randomness, date etc). Could you possibly give me some pointers? I read the chapter about the virtual file systems, but it seems incomplete. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On 5 Jun 2013, at 8:38pm, Philip Bennefall wrote: > On 5 Jun 2013, at 8:32pm, Petite Abeille wrote: > >> write to tmpfs… read the file into byte[]… do what you meant to do… to >> reload… write byte[] do tmpfs… open db… and be merry… or something along >> these lines... > > I don't want it in a file, however. I want it in a memory block. That's why you read from tmpfs (or any other file stored in any other file system) into byte[]. Once your data is in byte[] you will have entire SQLite database in one run of memory. You can't usefully store a memory database of SQLite because SQLite's data in memory isn't all in one big run of memory. The data is stored in various little chunks, some here, some there. If you tried to read the data directly out of those chunks you would have to read lots of little chunks, not one big run of continuous memory. And you'd be storing lots of pointers to various locations in memory. When you 'restore' the data back into memory you're not going to be allocated the same locations in memory so those pointers won't mean anything any more. A database stored in a file, however, has pointers to locations in that file instead of pointers to locations in memory. If you save and restore the whole file in one big run, those pointers will become valid again: the same bits of data will be at the same offsets of the file. Doesn't have to be tmpfs. You can use any file system that SQLite thinks is file storage rather than memory storage. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Petite Abeille" To: "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 10:04 PM Subject: Re: [sqlite] Serialize an in-memory database On Jun 5, 2013, at 10:02 PM, Philip Bennefall wrote: That is exactly the sort of thing I am looking for. If anything, I think it'd be great to have such a vfs in SqLite if only for completeness/customizability, seeing as how there are so many different allocators for example. It'd be great to have the same level of freedom with the vfs backends. Of course, I want to use the existing methods in the native vfs for randomization, time etc - just override the file I/O methods to operate on a large byte array/blob. memvfs? http://article.gmane.org/gmane.comp.db.sqlite.general/46450 I looked into that one, but the license is unclear. There is a copyright notice in the source but no explicit license. I need something public domain, just like SqLite is. Otherwise I will attempt to roll my own. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Jun 5, 2013, at 10:02 PM, Philip Bennefall wrote: > That is exactly the sort of thing I am looking for. If anything, I think it'd > be great to have such a vfs in SqLite if only for > completeness/customizability, seeing as how there are so many different > allocators for example. It'd be great to have the same level of freedom with > the vfs backends. Of course, I want to use the existing methods in the native > vfs for randomization, time etc - just override the file I/O methods to > operate on a large byte array/blob. memvfs? http://article.gmane.org/gmane.comp.db.sqlite.general/46450 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Jay A. Kreibich" To: "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 9:55 PM Subject: Re: [sqlite] Serialize an in-memory database On Wed, Jun 05, 2013 at 09:15:21PM +0200, Petite Abeille scratched on the wall: On Jun 5, 2013, at 9:10 PM, Philip Bennefall wrote: > Yes, I have seen the backup API. But I would like to avoid the disk > file entirely and just serialize to and from memory. Lateral thinking? write your db to tmpfs? A few months back, wasn't there talk about a VFS that used a giant byte array, rather than a file? If someone actually wrote one of those, you could use the Backup API to blast the DB into a big chunk of memory. If such a VFS does not actually exist, it shouldn't be all that hard to write, and might come in useful for this and other reasons. -j Hi there, That is exactly the sort of thing I am looking for. If anything, I think it'd be great to have such a vfs in SqLite if only for completeness/customizability, seeing as how there are so many different allocators for example. It'd be great to have the same level of freedom with the vfs backends. Of course, I want to use the existing methods in the native vfs for randomization, time etc - just override the file I/O methods to operate on a large byte array/blob. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Petite Abeille" To: "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 9:54 PM Subject: Re: [sqlite] Serialize an in-memory database On Jun 5, 2013, at 9:44 PM, Philip Bennefall wrote: I use Windows. This looks like it is purely for Unix variants? I suspect one call these 'RAM disk/drive' as well... http://en.wikipedia.org/wiki/List_of_RAM_drive_software I need something that operates wherever SqLite does, so can't be system dependent. SQLite runs on pretty much anything. Are you targeting, say, watches? No. All I am saying is that I don't want to limit myself to platforms that implement this particular feature. I would prefer a generic solution that works everywhere, such as a regular malloc:ed chunk that works like a vfs in SqLite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Wed, Jun 05, 2013 at 09:15:21PM +0200, Petite Abeille scratched on the wall: > > On Jun 5, 2013, at 9:10 PM, Philip Bennefall wrote: > > > Yes, I have seen the backup API. But I would like to avoid the disk > > file entirely and just serialize to and from memory. > > Lateral thinking? write your db to tmpfs? A few months back, wasn't there talk about a VFS that used a giant byte array, rather than a file? If someone actually wrote one of those, you could use the Backup API to blast the DB into a big chunk of memory. If such a VFS does not actually exist, it shouldn't be all that hard to write, and might come in useful for this and other reasons. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Jun 5, 2013, at 9:44 PM, Philip Bennefall wrote: > I use Windows. This looks like it is purely for Unix variants? I suspect one call these 'RAM disk/drive' as well... http://en.wikipedia.org/wiki/List_of_RAM_drive_software > I need something that operates wherever SqLite does, so can't be system > dependent. SQLite runs on pretty much anything. Are you targeting, say, watches? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Petite Abeille" To: "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 9:39 PM Subject: Re: [sqlite] Serialize an in-memory database On Jun 5, 2013, at 9:38 PM, Philip Bennefall wrote: I don't want it in a file, however. I want it in a memory block. So tmpfs wouldn't do the trick from what I gather. … tmpfs *is* memory… just looks like a file system… http://en.wikipedia.org/wiki/Tmpfs I use Windows. This looks like it is purely for Unix variants? I need something that operates wherever SqLite does, so can't be system dependent. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Jun 5, 2013, at 9:38 PM, Philip Bennefall wrote: > I don't want it in a file, however. I want it in a memory block. So tmpfs > wouldn't do the trick from what I gather. … tmpfs *is* memory… just looks like a file system… http://en.wikipedia.org/wiki/Tmpfs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Petite Abeille" To: "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 9:32 PM Subject: Re: [sqlite] Serialize an in-memory database On Jun 5, 2013, at 9:25 PM, Philip Bennefall wrote: Doesn't that still create a file, just a temporary one? I need the serialized content in a char* or similar so I can memcpy it etc, and then feed it back to SqLite at a later time. I guess I could make a toy vfs that uses a malloc:ed chunk that pretends to be the disk drive, and backup to/from that to a regular in-memory database. Thoughts? Yes… write to tmpfs… read the file into byte[]… do what you meant to do… to reload… write byte[] do tmpfs… open db… and be merry… or something along these lines... I don't want it in a file, however. I want it in a memory block. So tmpfs wouldn't do the trick from what I gather. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Jun 5, 2013, at 9:25 PM, Philip Bennefall wrote: > Doesn't that still create a file, just a temporary one? I need the serialized > content in a char* or similar so I can memcpy it etc, and then feed it back > to SqLite at a later time. I guess I could make a toy vfs that uses a > malloc:ed chunk that pretends to be the disk drive, and backup to/from that > to a regular in-memory database. Thoughts? Yes… write to tmpfs… read the file into byte[]… do what you meant to do… to reload… write byte[] do tmpfs… open db… and be merry… or something along these lines... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Roman Fleysher" To: "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 9:26 PM Subject: Re: [sqlite] Serialize an in-memory database Read section on URI Filemanes, particularly mode for memory databases: http://www.sqlite.org/c3ref/open.html DB Connection in backup API does not have to point to a file, it can point to in-memory database if URIs are enabled. (I learned it from someone else on the list, i use SQLite for less than a month.) Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Petite Abeille [petite.abei...@gmail.com] Sent: Wednesday, June 05, 2013 3:15 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Serialize an in-memory database On Jun 5, 2013, at 9:10 PM, Philip Bennefall wrote: Yes, I have seen the backup API. But I would like to avoid the disk file entirely and just serialize to and from memory. Lateral thinking… write your db to tmpfs… ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hi Roman, Oh I know that I can copy content from one in-memory database to another. That's trivial with the backup API. My concern is more that I want to copy it to a blob. In short, the contents that would have been fed to the file - I want that in a memory buffer. I also want to be able to feed that back into SqLite at a later time, probably backing it up to a regular in-memory database again. The malloc vfs is the only solution I can come up with, but it seems overkill. I am hoping there is a cleaner way. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Wed, Jun 5, 2013 at 12:25 PM, Philip Bennefall wrote: > - Original Message - From: "Petite Abeille" > On Jun 5, 2013, at 9:10 PM, Philip Bennefall wrote: >> Yes, I have seen the backup API. But I would like to avoid the disk file >> entirely and just serialize to and from memory. > Lateral thinking… write your db to tmpfs… > > Doesn't that still create a file, just a temporary one? Yes. It is a RAM disk fs however AFAIK, i.e. in memory, thus matching the constraints given so far. > I need the > serialized content in a char* or similar so I can memcpy it etc, and then Ok, that is not something even tmpfs provides. > feed it back to SqLite at a later time. I guess I could make a toy vfs that > uses a malloc:ed chunk that pretends to be the disk drive, and backup > to/from that to a regular in-memory database. Thoughts? That might be workable. Take me with a heap of salt here, as I have not done anything with sqlite's vfs API whatsoever so far. -- Andreas Kupries Senior Tcl Developer Code to Cloud: Smarter, Safer, Faster™ F: 778.786.1133 andre...@activestate.com http://www.activestate.com Learn about Stackato for Private PaaS: http://www.activestate.com/stackato Tcl'2013, Sep 23-27, New Orleans, LA, USA @ http://www.tcl.tk/community/tcl2013/ EuroTcl'2013, July 6-7, Munich, GER ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
Read section on URI Filemanes, particularly mode for memory databases: http://www.sqlite.org/c3ref/open.html DB Connection in backup API does not have to point to a file, it can point to in-memory database if URIs are enabled. (I learned it from someone else on the list, i use SQLite for less than a month.) Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Petite Abeille [petite.abei...@gmail.com] Sent: Wednesday, June 05, 2013 3:15 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Serialize an in-memory database On Jun 5, 2013, at 9:10 PM, Philip Bennefall wrote: > Yes, I have seen the backup API. But I would like to avoid the disk file > entirely and just serialize to and from memory. Lateral thinking… write your db to tmpfs… ___ 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] Serialize an in-memory database
On Wed, Jun 5, 2013 at 9:25 PM, Philip Bennefall wrote: > or similar so I can memcpy it etc, and then feed it back to SqLite at a > later time. I guess I could make a toy vfs that uses a malloc:ed chunk that > pretends to be the disk drive, and backup to/from that to a regular > in-memory database. Thoughts? > If you (or anyone else) does implement such a beast, i would be very interested in seeing it posted on the list. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Petite Abeille" To: "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 9:15 PM Subject: Re: [sqlite] Serialize an in-memory database On Jun 5, 2013, at 9:10 PM, Philip Bennefall wrote: Yes, I have seen the backup API. But I would like to avoid the disk file entirely and just serialize to and from memory. Lateral thinking… write your db to tmpfs… Doesn't that still create a file, just a temporary one? I need the serialized content in a char* or similar so I can memcpy it etc, and then feed it back to SqLite at a later time. I guess I could make a toy vfs that uses a malloc:ed chunk that pretends to be the disk drive, and backup to/from that to a regular in-memory database. Thoughts? Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Jun 5, 2013, at 9:10 PM, Philip Bennefall wrote: > Yes, I have seen the backup API. But I would like to avoid the disk file > entirely and just serialize to and from memory. Lateral thinking… write your db to tmpfs… ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Andreas Kupries" To: ; "General Discussion of SQLite Database" Sent: Wednesday, June 05, 2013 9:07 PM Subject: Re: [sqlite] Serialize an in-memory database On Wed, Jun 5, 2013 at 11:55 AM, Philip Bennefall wrote: Hello all, This may be a somewhat strange question, but I can't find an answer to it on the website so I figured I would give it a shot. Is it possible to put the full contents of an SqLite in-memory database into a string/blob in memory? If you can work with a file instead of string the backup/restore APIs should be able to do what you want, i.e. backup of a live database to a file, and back, and the live database can be in-memory, of course. http://www.sqlite.org/backup.html See example 1. http://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupinit Hi Andreas, Yes, I have seen the backup API. But I would like to avoid the disk file entirely and just serialize to and from memory. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On Wed, Jun 5, 2013 at 11:55 AM, Philip Bennefall wrote: > Hello all, > > This may be a somewhat strange question, but I can't find an answer to it on > the website so I figured I would give it a shot. > > Is it possible to put the full contents of an SqLite in-memory database into > a string/blob in memory? If you can work with a file instead of string the backup/restore APIs should be able to do what you want, i.e. backup of a live database to a file, and back, and the live database can be in-memory, of course. http://www.sqlite.org/backup.html See example 1. http://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupinit -- Andreas Kupries Senior Tcl Developer Code to Cloud: Smarter, Safer, Faster™ F: 778.786.1133 andre...@activestate.com http://www.activestate.com Learn about Stackato for Private PaaS: http://www.activestate.com/stackato Tcl'2013, Sep 23-27, New Orleans, LA, USA @ http://www.tcl.tk/community/tcl2013/ EuroTcl'2013, July 6-7, Munich, GER ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Serialize an in-memory database
Hello all, This may be a somewhat strange question, but I can't find an answer to it on the website so I figured I would give it a shot. Is it possible to put the full contents of an SqLite in-memory database into a string/blob in memory? I would then like to take the same string and convert that back into an in-memory database later, if that makes any sense. Serialization of the database, basically. Is there any reasonable, non hack-ish way of doing this? Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
