Serialize an in-memory database

classic Classic list List threaded Threaded
32 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Serialize an in-memory database

Philip Bennefall
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Andreas Kupries-2
On Wed, Jun 5, 2013 at 11:55 AM, Philip Bennefall <[hidden email]> 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
[hidden email]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall
----- Original Message -----
From: "Andreas Kupries" <[hidden email]>
To: <[hidden email]>; "General Discussion of SQLite Database"
<[hidden email]>
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 <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Petite Abeille-2

On Jun 5, 2013, at 9:10 PM, Philip Bennefall <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall

----- Original Message -----
From: "Petite Abeille" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Stephan Beal-3
On Wed, Jun 5, 2013 at 9:25 PM, Philip Bennefall <[hidden email]>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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Roman Fleysher
In reply to this post by Petite Abeille-2

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: [hidden email] [[hidden email]] on behalf of Petite Abeille [[hidden email]]
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Andreas Kupries-2
In reply to this post by Philip Bennefall
On Wed, Jun 5, 2013 at 12:25 PM, Philip Bennefall <[hidden email]> wrote:
> ----- Original Message ----- From: "Petite Abeille"
> On Jun 5, 2013, at 9:10 PM, Philip Bennefall <[hidden email]> 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
[hidden email]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall
In reply to this post by Roman Fleysher

----- Original Message -----
From: "Roman Fleysher" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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: [hidden email] [[hidden email]] on
behalf of Petite Abeille [[hidden email]]
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 <[hidden email]> 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
[hidden email]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Petite Abeille-2
In reply to this post by Philip Bennefall

On Jun 5, 2013, at 9:25 PM, Philip Bennefall <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall

----- Original Message -----
From: "Petite Abeille" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Petite Abeille-2

On Jun 5, 2013, at 9:38 PM, Philip Bennefall <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall

----- Original Message -----
From: "Petite Abeille" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Petite Abeille-2

On Jun 5, 2013, at 9:44 PM, Philip Bennefall <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Jay Kreibich
In reply to this post by Petite Abeille-2
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall
In reply to this post by Petite Abeille-2

----- Original Message -----
From: "Petite Abeille" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall
In reply to this post by Jay Kreibich

----- Original Message -----
From: "Jay A. Kreibich" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Petite Abeille-2

On Jun 5, 2013, at 10:02 PM, Philip Bennefall <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Philip Bennefall

----- Original Message -----
From: "Petite Abeille" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Serialize an in-memory database

Simon Slavin-3
In reply to this post by Philip Bennefall

On 5 Jun 2013, at 8:38pm, Philip Bennefall <[hidden email]> wrote:

> On 5 Jun 2013, at 8:32pm, Petite Abeille <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
12