RE: [sqlite] Memory DB: Load from file

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

Andrew Piskorski <[EMAIL PROTECTED]> 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   <[EMAIL PROTECTED]>