Hi SQLite users,
Questions:
1. What is the preferred way to copy/flush new memory db to disk db ? (all
tables, indices, records from memoryDB to diskDB)
2. Should I create indices first in memory db or directly in disk db ?
3. How to maintain exclusive access to disk db file till creation/copying
from memory db is finished ?
My requirement is (Win32 App, Windows 98/NT/2000/XP) :
- create a new SQLite database with all necessary tables, records and
indices
- initial database must contain necessary tables, records and indices
- or database file must be removed from disk if any error while
creating/copying tables, records or indices
- other application or other instance of same app must not be able to access
the database, till database is not ready with necessary minimum tables and
records.
Intial database will have about 30+ tables, very few records in each of
these tables, one or two indices on some tables.
/ My sample c-program is given below /
I truly appreciate any help, ideas, alternatives...
Thanks
Rohit
What I am doing is:
1. create a new database in memory
2. create necessary tables in memory db and insert necessary records
3. attach disk db to the memory db
4. copy tables and records from memory db to disk db using CREATE TABLE ...
AS SELECT
5. create necessary indices in disk db
6. detach disk db from memory db
/ My sample c-program is given below /
/ Error checking is omitted for brevity /
#include "sqlite3.h"
int main(int argc, char* argv[])
{
/* create a new database in memory */
sqlite3 *db1;
sqlite3_open(":memory:", &db1);
/* create necessary tables in memory db and insert necessary records */
sqlite3_exec(db1,"CREATE TABLE t1(a TEXT);", 0, 0, 0);
sqlite3_exec(db1,"INSERT INTO t1 VALUES('aa');", 0, 0, 0);
sqlite3_exec(db1,"INSERT INTO t1 VALUES('bb');", 0, 0, 0);
sqlite3_exec(db1,"CREATE TABLE t2(a TEXT, b REAL);", 0, 0, 0);
sqlite3_exec(db1,"INSERT INTO t2 VALUES('cc', 11.11);", 0, 0, 0);
sqlite3_exec(db1,"INSERT INTO t2 VALUES('dd', 22.22);", 0, 0, 0);
/* attach disk db (disk.db3) to the memory db */
sqlite3_exec(db1, "ATTACH 'disk.db3' AS disk_db;" , 0, 0, 0);
/* copy tables and records from memory db to disk db using CREATE TABLE
... AS SELECT */
sqlite3_exec(db1, "CREATE TABLE disk_db.t1 AS SELECT * FROM t1;" , 0, 0,
0);
sqlite3_exec(db1, "CREATE TABLE disk_db.t2 AS SELECT * FROM t2;" , 0, 0,
0);
/* create necessary indices in disk db */
/* detach disk db from memory db */
sqlite3_exec(db1, "DETACH 'disk.db3' AS disk_db;" , 0, 0, 0);
sqlite3_close(db1);
/* if any errors while creating/copying tables or records */
/* then delete disk.db3 */
/* loop for processing user comands */
return 0;
}
--
View this message in context:
http://www.nabble.com/Preferred-way-to-copy-flush-new-memory-db-to-disk-db---t1843573.html#a5032300
Sent from the SQLite forum at Nabble.com.
