Memory DB: Load from file

[Dave Gierok]

I am developing a game on Xbox360 using Sqlite for a lot of our data
manipulation. We use the ':memory:' functionality to create our DB in
RAM, because we need our DB manipulations to be very fast - we can't
afford for our queries to cause seeks/loads on the DVD.



This works very well for us with one exception: It takes a long time to
load the DB. Since we need the DB to be in memory, we create an empty
':memory:' DB, then load up a file which contains all the SQL (CREATE
TABLE, INSERT INTO) we need to create our tables and execute them on the
memory-DB. This process currently takes 10 seconds (all running the SQL
statements to create the tables), which is not acceptable for our game.



Is there a way to load a Sqlite file based DB and then specify we want
that to go into memory? Or is there a more efficient way to create our
memory DB?



Thank you very much for your help,

Dave Gierok

[Andrew Piskorski]

Post by Dave Gierok
Is there a way to load a Sqlite file based DB and then specify we want
that to go into memory? Or is there a more efficient way to create our
memory DB?

You could use attach to copy from an on-disk SQLite database:

http://www.sqlite.org/google-talk-slides/page-024.html
http://www.sqlite.org/google-talk-slides/page-025.html

[d***@public.gmane.org]

Post by Andrew Piskorski

Post by Dave Gierok
Is there a way to load a Sqlite file based DB and then specify we want
that to go into memory? Or is there a more efficient way to create our
memory DB?

http://www.sqlite.org/google-talk-slides/page-024.html
http://www.sqlite.org/google-talk-slides/page-025.html

Andrews suggestion above is better than the sqlite3_raw_write()
routine of my previous post because Andrew's idea is portable.
Use this idea if it is fast enough for you. The raw_write()
thing will be a little bit faster, but as I said, it is subject
to break without warning.
--
D. Richard Hipp <***@hwaci.com>

[Dave Gierok]

Thank you for the help Andrew and D. Richard Hipp. But how would I do
this (page 24 & 25 of the slides) using the C/C++ interface?

{
sqlite3* pFileDB;
sqlite3* pMemoryDB;

sqlite3_open(fileName, &pFileDB);
sqlite3_open(":memory:", &pMemoryDB);

//****************************************
//WHAT DO I DO NOW?
//****************************************
}

Thanks,
Dave Gierok

-----Original Message-----
From: drh-***@public.gmane.org [mailto:drh-***@public.gmane.org]
Sent: Monday, June 05, 2006 12:49 PM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: Re: [sqlite] Memory DB: Load from file

Post by Andrew Piskorski

Post by Dave Gierok
Is there a way to load a Sqlite file based DB and then specify we want
that to go into memory? Or is there a more efficient way to create our
memory DB?

http://www.sqlite.org/google-talk-slides/page-024.html
http://www.sqlite.org/google-talk-slides/page-025.html

Andrews suggestion above is better than the sqlite3_raw_write()
routine of my previous post because Andrew's idea is portable.
Use this idea if it is fast enough for you. The raw_write()
thing will be a little bit faster, but as I said, it is subject
to break without warning.
--
D. Richard Hipp <drh-***@public.gmane.org>

[Dennis Cote]

Thank you for the help Andrew and D. Richard Hipp. But how would I do
this (page 24 & 25 of the slides) using the C/C++ interface?
{
sqlite3* pFileDB;
sqlite3* pMemoryDB;
sqlite3_open(fileName, &pFileDB);
sqlite3_open(":memory:", &pMemoryDB);
//****************************************
//WHAT DO I DO NOW?
//****************************************
}

Dave,


You simply execute the SQL commands against your memory database like this:


{
sqlite3* pMemoryDB;

sqlite3_open(":memory:", &pMemoryDB);

//****************************************
//WHAT DO I DO NOW?
//****************************************


char* sql = "ATTACH DATABASE backup.db AS backup;"
"BEGIN;"
"DELETE FROM backup.attachment;"
"INSERT INTO backup.attachment SELECT * FROM main.attachment;"
"COMMIT;"
"DETACH DATABASE backup;";

sqlite3_exec(pMemoryDB, sql, NULL, NULL, NULL):

//****************************************
//continue working with memory database
//****************************************

}

HTH
Dennis Cote

[Pat Wibbeler]

This sounded fun, so I thought I'd give it a try. Here's a sample
pulling schema and data from an on-disk to an in-memory database in c.
I've omitted error handling and debug output to make it shorter.

int process_ddl_row(void * pData, int nColumns,
char **values, char **columns);
int process_dml_row(void *pData, int nColumns,
char **values, char **columns);

void test()
{
sqlite3* memorydb;
sqlite3* budb;

sqlite3_open(":memory:", &memorydb);
// Looks for backup.db in pwd. For testing, you may want to
// initialize the database to a known state.
sqlite3_open("backup.db", &budb);

// Create the in-memory schema from the backup
sqlite3_exec(budb, "BEGIN", NULL, NULL, NULL);
sqlite3_exec(budb, "SELECT sql FROM sqlite_master WHERE sql NOT
NULL",
&process_ddl_row, memorydb, NULL);
sqlite3_exec(budb, "COMMIT", NULL, NULL, NULL);
sqlite3_close(budb);

// Attach the backup to the in memory
sqlite3_exec(memorydb, "ATTACH DATABASE 'backup.db' as backup",
NULL, NULL, NULL);

// Copy the data from the backup to the in memory
sqlite3_exec(memorydb, "BEGIN", NULL, NULL, NULL);
sqlite3_exec(memorydb,
"SELECT name FROM backup.sqlite_master WHERE
type='table'",
&process_dml_row, memorydb, NULL);
sqlite3_exec(memorydb, "COMMIT", NULL, NULL, NULL);

sqlite3_exec(memorydb, "DETACH DATABASE backup", NULL, NULL,
NULL);
sqlite3_close(memorydb);
}

/**
* Exec an sql statement in values[0] against
* the database in pData.
*/
int process_ddl_row(void * pData, int nColumns,
char **values, char **columns)
{
if (nColumns != 1)
return 1; // Error

sqlite3* db = (sqlite3*)pData;
sqlite3_exec(db, values[0], NULL, NULL, NULL);

return 0;
}

/**
* Insert from a table named by backup.{values[0]}
* into main.{values[0]} in database pData.
*/
int process_dml_row(void *pData, int nColumns,
char **values, char **columns)
{
if (nColumns != 1)
return 1; // Error

sqlite3* db = (sqlite3*)pData;

char *stmt = sqlite3_mprintf("insert into main.%q "
"select * from backup.%q", values[0], values[0]);
sqlite3_exec(db, stmt, NULL, NULL, NULL);
sqlite3_free(stmt);

return 0;
}
-----Original Message-----
From: Dave Gierok [mailto:davidgi-***@public.gmane.org]
Sent: Monday, June 05, 2006 5:45 PM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: RE: [sqlite] Memory DB: Load from file

Thank you for the help Andrew and D. Richard Hipp. But how would I do
this (page 24 & 25 of the slides) using the C/C++ interface?

{
sqlite3* pFileDB;
sqlite3* pMemoryDB;

sqlite3_open(fileName, &pFileDB);
sqlite3_open(":memory:", &pMemoryDB);

//****************************************
//WHAT DO I DO NOW?
//****************************************
}

Thanks,
Dave Gierok

-----Original Message-----
From: drh-***@public.gmane.org [mailto:drh-***@public.gmane.org]
Sent: Monday, June 05, 2006 12:49 PM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: Re: [sqlite] Memory DB: Load from file

Post by Andrew Piskorski

Post by Dave Gierok
Is there a way to load a Sqlite file based DB and then specify we want
that to go into memory? Or is there a more efficient way to create our
memory DB?

http://www.sqlite.org/google-talk-slides/page-024.html
http://www.sqlite.org/google-talk-slides/page-025.html

Andrews suggestion above is better than the sqlite3_raw_write()
routine of my previous post because Andrew's idea is portable.
Use this idea if it is fast enough for you. The raw_write()
thing will be a little bit faster, but as I said, it is subject
to break without warning.
--
D. Richard Hipp <drh-***@public.gmane.org>

[d***@public.gmane.org]

Post by Dave Gierok
I am developing a game on Xbox360 using Sqlite for a lot of our data
manipulation.

That's good to know. I'll be shortly adding a Microsoft logo
to the image of companies and projects using SQLite at

Loading Image...

:-)

Post by Dave Gierok
We use the ':memory:' functionality to create our DB in
RAM, because we need our DB manipulations to be very fast - we can't
afford for our queries to cause seeks/loads on the DVD.
This works very well for us with one exception: It takes a long time to
load the DB. Since we need the DB to be in memory, we create an empty
':memory:' DB, then load up a file which contains all the SQL (CREATE
TABLE, INSERT INTO) we need to create our tables and execute them on the
memory-DB. This process currently takes 10 seconds (all running the SQL
statements to create the tables), which is not acceptable for our game.

The code below should do what you need. First create your :memory:
database. Then open the file that contains the initialization
database. Then read a chunk of the file and write that same chunk
into the :memory: database by calling sqlite3_raw_write().

The code below messes with internal data structures of SQLite and
is not guaranteed to work in future SQLite releases. So beware.


/*
** This routine is called to write data directly into the database image
** (presumably taken from a database file created externally).
**
** The database from which the supplied data is taken must have a page-size
** equal to the value of the SQLITE_DEFAULT_PAGE_SIZE macro this file is
** compiled with.
*/
int sqlite3_raw_write(
sqlite3 *db,
int nData,
int iOffset,
unsigned const char *zData
){
Pager *pPager;
int pageSize; /* Copy of pPager->pageSize */
int psAligned; /* Copy of pPager->psAligned */
int n; /* Remaining bytes to copy into database pages */
unsigned const char *z; /* Bytes to copy into database pages */
int iPage; /* Page number to copy into */
int iOff; /* Offset on iPage to write to */
int rc = SQLITE_OK; /* Offset on iPage to write to */
unsigned char *p1 = 0; /* First page of database */

assert( db );
assert( nData>=0 );
assert( iOffset>=0 );
assert( db->aDb[0].pBt );
assert( zData || nData==0 );

pPager = sqlite3BtreePager(db->aDb[0].pBt);
psAligned = pPager->psAligned;
pageSize = pPager->psAligned;
z = zData;
n = nData;

/* This routine may not be used if any statements or a transaction are
** currently active. If this is not the case and we can proceed, grab a
** reference to page 1 and hold it until the end of this function so that
** the transaction is not rolled back because the page reference count
** reaches zero.
*/
if( pPager->nRef>1 ){
rc = SQLITE_MISUSE;
}else{
rc = sqlite3pager_get(pPager, 1, (void **)&p1);
}

iPage = (iOffset / psAligned) + 1;
iOff = (iOffset % psAligned);
while( n>0 && rc==SQLITE_OK ){
int bytes; /* Number of bytes to write */
unsigned char *p = 0; /* Page iPage */
bytes = pageSize-iOff;
bytes = (bytes<n)?bytes:n;

/* Retrieve the page, set it to writable and copy the data to it. The
** first sqlite3pager_write() call starts a new transaction, which is
** committed at the end of this routine.
*/
if( SQLITE_OK==(rc=sqlite3pager_get(pPager, iPage, (void **)&p)) &&
SQLITE_OK==(rc=sqlite3pager_write(p))
){
memcpy(&p[iOff], z, bytes);
}
if( p ){
sqlite3pager_unref(p);
}

bytes += (psAligned-pageSize);
n -= bytes;
z += bytes;
iOff = 0;
iPage++;
}
if( rc==SQLITE_OK ){
rc = sqlite3pager_commit(pPager);
}
if( p1 ){
sqlite3pager_unref(p1);
}
sqlite3Error(db, rc, 0);
return sqlite3ApiExit(db, rc);
}

--
D. Richard Hipp <***@hwaci.com>