[zz]Save SQLite memory database to file

http://itsystementwicklung.de/pipermail/list-pysqlite/2008-July/000113.html


I would like to do this for similar reasons.  I have a web application where
the application data will be organized in a sqlite database unique to each
user.  The user will upload the database when he begins a session and
download it when he is done.  I want to manage the data entirely in memory
because

    (a) the database size will be relatively small (on the order of a few
megabytes),

    (b) there will be few users simultaneously using the system,

    (c) I want the data to expire when the user's session expires (which may
occur as a timeout), so I intend to store the sqlite database in the user's
server-side session.

    (d) I don't want to have to find a place to store the data on the disk,
ensure that the permissions are correct, and remove files that have expired.

So, to be specific, I don't want to load/dump the in-memory database to/from
a file, but instead to/from a stream.
It appears the API doesn't support what I'm trying to do.  Do you have any
suggestions on how I might go about saving an in-memory database or
initializing from a previously-saved database without touching the file
system?

I understand I could use the aforementioned file-based technique to
accomplish what I need, but that's a clumsy way to get at the raw data, when
it presumably already exists in memory.

评论

5 楼 iunknown 2009-04-28  

http://www.nabble.com/VFS-in-upcoming-3.5.0-td12441170.html

With a built in InMemory-VFS (and its new "Dump-Interface")
we could achieve many nice things in only one roundtrip.
At the serverside we could attach an empty InMemory-DB
(implicitely using the new MemVFS) to an already pooled
SQLite-Connection and perform a bunch of "Insert Into-Queries",
to create a small snapshot of e.g. a midsized "Master-Detail-
Scenario".
Now we could dump not only a "single-query-content" (as
with our Resultsets currently), but could write a complete,
related scenario (containing the prepared "InMemory-Tables")
to a ByteArray and transfer *this* to the client.

Now it would be great, if we could use the new MemVFS-
interface, to create *and* initialize a new InMemory-DB at
the clientside with the received ByteContent.
This way, we could perform related queries (Joins, Filters,
etc.) against the InMemory-DB (containing the midsize
Master-Detail-Set) without doing any extra-roundtrips
over the server.

4 楼 iunknown 2009-04-27  

http://stackoverflow.com/questions/128919/extreme-sharding-one-sqlite-database-per-user

Extreme Sharding: One SQLite Database Per User



I'm working on a web app that is somewhere between an email service and a social network. I feel it has the potential to grow really big in the future, so I'm concerned about scalability.

Instead of using one centralized MySQL/InnoDB database and then partitioning it when that time comes, I've decided to create a separate SQLite database for each active user: one active user per 'shard'.

That way backing up the database would be as easy as copying each user's small database file to a remote location once a day.

Scaling up will be as easy as adding extra hard disks to store the new files.

When the app grows beyond a single server I can link the servers together at the filesystem level using GlusterFS and run the app unchanged, or rig up a simple SQLite proxy system that will allow each server to manipulate sqlite files in adjacent servers.

Concurrency issues will be minimal because each HTTP request will only touch one or two database files at a time, out of thousands, and SQLite only blocks on reads anyway.

I'm betting that this approach will allow my app to scale gracefully and support lots of cool and unique features. Am I betting wrong? Am I missing anything?

UPDATE I decided to go with a less extreme solution, which is working fine so far. I'm using a fixed number of shards - 256 sqlite databases, to be precise. Each user is assigned and bound to a random shard by a simple hash function.

Most features of my app require access to just one or two shards per request, but there is one in particular that requires the execution of a simple query on 10 to 100 different shards out of 256, depending on the user. Tests indicate it would take about 0.02 seconds, or less, if all the data is cached in RAM. I think I can live with that!

UPDATE 2.0 I ported the app to MySQL/InnoDB and was able to get about the same performance for regular requests, but for that one request that requires shard walking, innodb is 4-5 times faster. For this reason, and other reason, I'm dropping this architecture, but I hope someone somewhere finds a use for it...thanks.

3 楼 iunknown 2009-04-25  

eXtremeDB的mco_db_save()函数方便地将内存库保存到磁盘上再通过mco_db_load()随时加载。在系统有 大量初始配置数据需要加载的情形下，mco_db_save()/mco_db_load()特别方便。

        /* abstract write and read streams interfaces; stream handle is a pointer to implementation-specific data */
        typedef int(*mco_stream_write)(void* stream_handle, const void* from, unsigned nbytes);
        typedef int(*mco_stream_read)(void* stream_handle,  /*OUT*/void* to, unsigned max_nbytes);

        /* save db content to output stream */
        MCO_RET mco_db_save(void* stream_handle, mco_stream_write output_stream_writer, mco_db_h db);

        /* create db instance and load content from input stream - instead of mco_db_open() */
        MCO_RET mco_db_load(void* stream_handle, mco_stream_read input_stream_reader, const char* db_name,
                            mco_dictionary_h dict, void* mem_ptr, mco_puint total_size);

2 楼 iunknown 2009-04-25  

http://www.kordelle.de/sqlite/

Followed example shows how to use the patch. It also includes code to read the file back into database. Don't forget to add errorchecking to your code!

     sqlite3  *dbHandle;
     char     *errmsg;
     /* open your memory database */
     sqlite3_open(":memory:", &dbHandle);
     if (access("./file.db", R_OK) != -1)
     {
       /* attach diskfile to current memory database */
       errCode = sqlite3_exec(dbHandle, "ATTACH DATABASE './file.db' AS oldDB", NULL, NULL, &errmsg);
       /* create "MYTABLE" in memory database as fullselect from saved table. 
          content of old table will be copied too. */
       errCode = sqlite3_exec(dbHandle, "CREATE TABLE MYTABLE AS SELECT * FROM oldDB.MYTABLE", NULL, NULL, &errmsg);
       /* close attached database */
       errCode = sqlite3_exec(dbHandle, "DETACH DATABASE oldDB", NULL, NULL, &errmsg);
     }
     else
     { 
       /* first run. create a new empty database */
       errCode = sqlite3_exec(dbHandle, "CREATE TABLE MYTABLE(ID INTEGER, NAME VARCHAR(40))", NULL, NULL, &errmsg);
     }
     Now work with your database.

     /* before closing the database, save it to a file */
     errCode = sqlite3_write_to_file(dbHandle, "./file.db");
     /* close database */
     sqlite3_close(dbHandle);
      

1 楼 iunknown 2009-04-25  

http://thread.gmane.org/gmane.comp.db.sqlite.general/38584/focus=38650

Actually, the reason I asked for this is that I wanted to use SQLite in a
flash memory based file system.
Considering that the flash memory is a bit slower than a normal disk(also
the limitation of writing operations), I wanted to change SQLite in some way
that it can be more efficient and useful upon flash memory systems.
The first solution I thought of was to make a kind of a buffer below the
b-tree, but it was really difficult understanding the internal system of
SQLite. But I'm still working on it.
Second, I wanted SQLite to do all of its work in the memory. Yes this would
be dangerous if there are unexpected power-offs or crashes, but I am
assuming that there won't be such situations like them. Then I visited the
website and found the patch. It sure is too old, but I thought it would be
helpful if I tried it on mine.