Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

http://sphivedb.googlecode.com/files/spmemvfs-0.2.src.tar.gz

The demo code:

typedef struct spmembuffer_t {
char * data;
int used;
int total;
} spmembuffer_t;

typedef struct spmemvfs_db_t {
sqlite3 * handle;
spmembuffer_t * mem;
} spmemvfs_db_t;

void test( spmemvfs_db_t * db )
{
..
// do whatever you want
errcode = sqlite3_exec( db->handle, .. );
..
}

int readFile( const char * path, spmembuffer_t * mem )
{
  // read file content from path
}

int writeFile( const char * path, spmembuffer_t * mem )
{
  // write buffer into file
}

int main( int argc, char * argv[] )
{
const char * path = "abc.db";

spmemvfs_db_t db;

spmembuffer_t * mem = (spmembuffer_t*)calloc( sizeof(
spmembuffer_t ), 1 );

spmemvfs_env_init();

readFile( path, mem );
spmemvfs_open_db( , path, mem );

assert( db.mem == mem );

test(  );

writeFile( path, db.mem );

spmemvfs_close_db(  );

spmemvfs_env_fini();

return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

> It also sounds like you are operating in an environment that is almost
> entirely reading and with lots of memory.  You could still store one


Yes. The memvfs requires to entirely reading the database.
So this solution is just for something like addressbook application,
the data for one user is small enough, we can accept to entirely read.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

http://spserver.googlecode.com/files/spmemvfs-0.1.src.tar.gz

Changes:
* Modify the save callback, transfer the ownership of the buffer to callback
function
* Add a buffer map to pass buffer between application and sqlite3 vfs

The demo code:

static void * load( void * arg, const char * path, int * len )
{
spmembuffer_map_t * themap = (spmembuffer_map_t*)arg;
char * ret = spmembuffer_map_take( themap, path, len );
return ret;
}

static int save( void * arg, const char * path, char * buffer, int len )
{
spmembuffer_map_t * themap = (spmembuffer_map_t*)arg;
return spmembuffer_map_put( themap, path, buffer, len );
}

int main( int argc, char * argv[] )
{
spmembuffer_map_t * themap = spmembuffer_map_new();
spmemvfs_cb_t cb = { themap, load, save };
const char * path = "abc.db";

spmemvfs_init(  );

// load membuffer from file, put it into themap
{
char * buffer = NULL;
int len = 0;
buffer = read_buffer_from_file( path,  );
spmembuffer_map_put( themap, path, buffer, len );
}

// open sqlite by sqlite3_open_v2
{
sqlite3 * dbHandle = NULL;
char errcode = sqlite3_open_v2( path, ,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, SPMEMVFS_NAME );

// do wahtever you want
..
}

// get membuffer from themap, save it to file
{
int len = 0;
char * buffer = (char*)spmembuffer_map_take( themap, path,  );
save_buffer_to_file( path, buffer, len );
free( buffer );
}

spmembuffer_map_del( themap );

return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

stephen liu wrote:
> For addressbook application, it call select_all_address_for_one_user
> frequency.
> If multipile users' addresses store in one table,  the
> select_all_address_for_one_user
> will cause much I/O operations.

If you have an index on the user column then the index will be used to
control getting entries from the table of all entries.  Admittedly there
will still be some seeking since the various user entries will be
interleaved over time, but it will be the minimum amount necessary.
Without an index, SQLite will have to look at every row.  There are
fairly frequent postings to this list where people detail their schemas
and query patterns and knowledgeable people help with query optimization.

A second approach is to just have one table per user (eg
addressbook_user) with some appropriate scheme for munging the username.

It also sounds like you are operating in an environment that is almost
entirely reading and with lots of memory.  You could still store one
SQLite disk file per user but combine them in memory for fast access
(and not having to worry about I/O latency).  [To do this, create your
table in :memory:, attach each user file, insert into memorytable select
  from userdb.table, detach user file]

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkn3/LMACgkQmOOfHg372QQOHACgiO3NvIzrLkyBABV+NasJuxWA
AXEAoKSZnMrHv+ze2hhDo10AUqdugM/S
=fYN3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

>
>
> > I want to save sqlite database as a entry of the dbm.
>
> If you really have to store the sqlite databases as blobs, why not store
> them in yet another sqlite database as a blob instead of inside dbm?
> SQLite has the drawback of not letting you resize a blob (you have to
> create a new value with the new length) but it doesn't look like the dbm
> api lets you either.  Unless your dbm implementation has proper
> transactions you could also lose data.
>

Some dbm implementations support transaction.
Such as tokyocabinet.


>
> > For example, the user's addressbook save in a sqlite database,
> > and the buffer of the database may be store in a dbm.
>
> That example would definitely be done better using more tables and
> normalization (users don't like addressbooks to be unreliable).  I
> assume there is more to it that you have told us.
>

For addressbook application, it call select_all_address_for_one_user
frequency.
If multipile users' addresses store in one table,  the
select_all_address_for_one_user
will cause much I/O operations. If it use a dbm to store the the
buffer_of_database,
the select_all_address_for_one_user is very fast.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

stephen liu wrote:
> I want to save multipile sqlite database in one file.

Generally that is a bad approach and instead you use more tables and
normalize your data more.  However you know your situation better.

> The backup api is not fix for this requirement.

True.  If your container file is a zip archive or something similar then
you could use the backup api and temporary files.

> I want to save sqlite database as a entry of the dbm.

If you really have to store the sqlite databases as blobs, why not store
them in yet another sqlite database as a blob instead of inside dbm?
SQLite has the drawback of not letting you resize a blob (you have to
create a new value with the new length) but it doesn't look like the dbm
api lets you either.  Unless your dbm implementation has proper
transactions you could also lose data.

> For example, the user's addressbook save in a sqlite database,
> and the buffer of the database may be store in a dbm.

That example would definitely be done better using more tables and
normalization (users don't like addressbooks to be unreliable).  I
assume there is more to it that you have told us.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkn39c4ACgkQmOOfHg372QQR/ACfYbodduOWRUbMhzdEkCo+SuRg
X0UAnAv6HrU+znaEUpDaifHj6JPOFphz
=HUJa
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

I try to implement One SQLite Database Per User.

http://stackoverflow.com/questions/128919/extreme-sharding-one-sqlite-database-per-user
Extreme Sharding: One SQLite Database Per User

2009/4/29 stephen liu 

>
> I want to save multipile sqlite database in one file.
> The backup api is not fix for this requirement.
>
> I want to save sqlite database as a entry of the dbm.
> For example, the user's addressbook save in a sqlite database,
> and the buffer of the database may be store in a dbm.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

I want to save multipile sqlite database in one file.
The backup api is not fix for this requirement.

I want to save sqlite database as a entry of the dbm.
For example, the user's addressbook save in a sqlite database,
and the buffer of the database may be store in a dbm.

2009/4/29 Roger Binns <rog...@rogerbinns.com>

> stephen liu wrote:
> > The attachment is a memvfs implementation for sqlite.
>
> The mailing list strips out attachments.
>
> > With the memvfs, we can loading/saving sqlite database from buffer.
>
> Even simpler, you can use sqlite3_backup API to copy from a disk to
> :memory: and the reverse direction when you are done.
>
> Roger
> ___
> 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] A memvfs for loading/saving database from buffer

[stephen liu]

The mailing list strips out attachments.

Please download from:
http://spserver.googlecode.com/files/spmemvfs.tar.gz

2009/4/29 Virgilio Alexandre Fornazin 

> Where we can get the code ?
>  
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[Virgilio Alexandre Fornazin]

Where we can get the code ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of stephen liu
Sent: terça-feira, 28 de abril de 2009 22:24
To: sqlite-users@sqlite.org
Subject: [sqlite] A memvfs for loading/saving database from buffer

Hi,

The attachment is a memvfs implementation for sqlite.

With the memvfs, we can loading/saving sqlite database from buffer.

There also includes a demo to show how to use it.

Cheers,

Stephen Liu

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[Roger Binns]

stephen liu wrote:
> The attachment is a memvfs implementation for sqlite.

The mailing list strips out attachments.

> With the memvfs, we can loading/saving sqlite database from buffer.

Even simpler, you can use sqlite3_backup API to copy from a disk to
:memory: and the reverse direction when you are done.

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

Hi,

The attachment is a memvfs implementation for sqlite.

With the memvfs, we can loading/saving sqlite database from buffer.

There also includes a demo to show how to use it.

Cheers,

Stephen Liu
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users