Memory DB: Load from file

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

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

Reply | Threaded
Open this post in threaded view
|

Re: Memory DB: Load from file

Andrew Piskorski
On Mon, Jun 05, 2006 at 12:05:52PM -0700, Dave Gierok wrote:

> 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

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/
Reply | Threaded
Open this post in threaded view
|

Re: Memory DB: Load from file

D. Richard Hipp
In reply to this post by Dave Gierok
"Dave Gierok" <[hidden email]> wrote:
> 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

   http://www.sqlite.org/google-talk-slides/logos4.gif

:-)

> 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   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Memory DB: Load from file

D. Richard Hipp
In reply to this post by Andrew Piskorski
Andrew Piskorski <[hidden email]> wrote:

> On Mon, Jun 05, 2006 at 12:05:52PM -0700, Dave Gierok wrote:
>
> > 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
>

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   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: Memory DB: Load from file

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: [hidden email] [mailto:[hidden email]]
Sent: Monday, June 05, 2006 12:49 PM
To: [hidden email]
Subject: Re: [sqlite] Memory DB: Load from file

Andrew Piskorski <[hidden email]> wrote:
> On Mon, Jun 05, 2006 at 12:05:52PM -0700, Dave Gierok wrote:
>
> > 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
>

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   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Memory DB: Load from file

Dennis Cote
Dave Gierok wrote:

> 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


Reply | Threaded
Open this post in threaded view
|

RE: Memory DB: Load from file

Pat Wibbeler
In reply to this post by Dave Gierok
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:[hidden email]]
Sent: Monday, June 05, 2006 5:45 PM
To: [hidden email]
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: [hidden email] [mailto:[hidden email]]
Sent: Monday, June 05, 2006 12:49 PM
To: [hidden email]
Subject: Re: [sqlite] Memory DB: Load from file

Andrew Piskorski <[hidden email]> wrote:
> On Mon, Jun 05, 2006 at 12:05:52PM -0700, Dave Gierok wrote:
>
> > 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
>

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   <[hidden email]>