I suppose since no one replied to this, that it's not possible to do it. Just wanted to confirm. Thank you...
----- Original Message ---- From: David Champagne <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, January 29, 2007 2:32:00 PM Subject: [sqlite] Memory database to file Hi, I've got an application that creates a database with a large number of records (millions), and the indexation of the tables is taking a long time. Once the database is initially created, it is never modified. No records are added or deleted. So, in the case where a user has sufficient memory, I want to offer the option to create the entire database in memory, including the indexes, and then serialize that to disk. I am not sure if this is really going to work or if it will be more effcient. I am using the "ATTACH DATABASE" command to create a copy of my ":memory:" database. I can copy copy the tables (see below), and I can get a list of the indxes (see further below), but I don't know how to copy the indexes. Also, does anyone know if this method is really going to be faster (memory db -> disk db) than doing everything with a disk db? Would the indexes really be copied or just re-created? //get the list of tables sql_cmd = "SELECT name FROM SQLITE_MASTER WHERE type = 'table'"; rc = sqlite3_prepare( m_db, sql_cmd.c_str(),(int)sql_cmd.length(), &stmt, &tail ); std::vector<string> tables; rc = sqlite3_step(stmt); while ( rc != SQLITE_DONE && rc != SQLITE_ERROR ) { tables.push_back((const char*) sqlite3_column_text ( stmt,0 )); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); //get the list of indexes sql_cmd = "SELECT name, tbl_name FROM SQLITE_MASTER WHERE type = 'index'"; rc = sqlite3_prepare( m_db, sql_cmd.c_str(),(int)sql_cmd.length(), &stmt, &tail ); std::vector<std::pair<string, string>> indexes; rc = sqlite3_step(stmt); while ( rc != SQLITE_DONE && rc != SQLITE_ERROR ) { indexes.push_back(std::pair<string, string>((const char*) sqlite3_column_text ( stmt,0 ), (const char*) sqlite3_column_text ( stmt,1 ))); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); rc = sqlite3_exec(m_db,"begin;", callback, 0, &zErrMsg); //copy the tables for (vector<string>::const_iterator it = tables.begin(); it != tables.end(); it++) { string sTable = *it; sql_cmd = "CREATE TABLE dbdisk." + sTable + " AS SELECT * FROM " + sTable + ";"; rc = sqlite3_exec(m_db,sql_cmd.c_str(), callback, 0, &zErrMsg); } //copy the indexes for (vector<std::pair<string, string>>::const_iterator it = indexes.begin(); it != indexes.end(); it++) { string sIndex = (*it).first; string sTable = (*it).second; //to be filled in } rc = sqlite3_exec(m_db,"commit;", callback, 0, &zErrMsg); ____________________________________________________________________________________ Don't get soaked. Take a quick peak at the forecast with the Yahoo! Search weather shortcut. http://tools.search.yahoo.com/shortcuts/#loc_weather ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] ----------------------------------------------------------------------------- ____________________________________________________________________________________ Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------