static bool createTables(SQLiteDatabase& sqliteDatabase)
{
if (sqliteDatabase.tableExists("Databases"))
return true;
static const char* commands[] = {
"CREATE TABLE Databases (id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT NOT NULL, version TEXT NOT NULL)",
"CREATE UNIQUE INDEX Databases_name ON Databases(name)",
"CREATE TABLE ObjectStores (id INTEGER PRIMARY KEY, name TEXT NOT NULL, keyPath TEXT, doAutoIncrement INTEGER NOT NULL, databaseId INTEGER NOT NULL REFERENCES Databases(id))",
"CREATE UNIQUE INDEX ObjectStores_composit ON ObjectStores(databaseId, name)",
"CREATE TABLE Indexes (id INTEGER PRIMARY KEY, objectStoreId INTEGER NOT NULL REFERENCES ObjectStore(id), name TEXT NOT NULL, keyPath TEXT, isUnique INTEGER NOT NULL)",
"CREATE UNIQUE INDEX Indexes_composit ON Indexes(objectStoreId, name)",
"CREATE TABLE ObjectStoreData (id INTEGER PRIMARY KEY, objectStoreId INTEGER NOT NULL REFERENCES ObjectStore(id), keyString TEXT, keyDate INTEGER, keyNumber INTEGER, value TEXT NOT NULL)",
"CREATE UNIQUE INDEX ObjectStoreData_composit ON ObjectStoreData(keyString, keyDate, keyNumber, objectStoreId)",
"CREATE TABLE IndexData (id INTEGER PRIMARY KEY, indexId INTEGER NOT NULL REFERENCES Indexes(id), keyString TEXT, keyDate INTEGER, keyNumber INTEGER, objectStoreDataId INTEGER NOT NULL REFERENCES ObjectStoreData(id))",
"CREATE INDEX IndexData_composit ON IndexData(keyString, keyDate, keyNumber, indexId)",
"CREATE INDEX IndexData_objectStoreDataId ON IndexData(objectStoreDataId)",
"CREATE INDEX IndexData_indexId ON IndexData(indexId)",
};
return runCommands(sqliteDatabase, commands, sizeof(commands) / sizeof(commands[0]));
}
static bool migrateDatabase(SQLiteDatabase& sqliteDatabase)
{
if (!sqliteDatabase.tableExists("MetaData")) {
if (!createMetaDataTable(sqliteDatabase))
return false;
}
int databaseVersion;
if (!getDatabaseVersion(sqliteDatabase, &databaseVersion))
return false;
if (databaseVersion == 1) {
static const char* commands[] = {
"DROP TABLE IF EXISTS ObjectStoreData2",
"CREATE TABLE ObjectStoreData2 (id INTEGER PRIMARY KEY, objectStoreId INTEGER NOT NULL REFERENCES ObjectStore(id), keyString TEXT, keyDate REAL, keyNumber REAL, value TEXT NOT NULL)",
"INSERT INTO ObjectStoreData2 SELECT * FROM ObjectStoreData",
"DROP TABLE ObjectStoreData", // This depends on SQLite not enforcing referential consistency.
"ALTER TABLE ObjectStoreData2 RENAME TO ObjectStoreData",
"CREATE UNIQUE INDEX ObjectStoreData_composit ON ObjectStoreData(keyString, keyDate, keyNumber, objectStoreId)",
"DROP TABLE IF EXISTS IndexData2", // This depends on SQLite not enforcing referential consistency.
"CREATE TABLE IndexData2 (id INTEGER PRIMARY KEY, indexId INTEGER NOT NULL REFERENCES Indexes(id), keyString TEXT, keyDate REAL, keyNumber REAL, objectStoreDataId INTEGER NOT NULL REFERENCES ObjectStoreData(id))",
"INSERT INTO IndexData2 SELECT * FROM IndexData",
"DROP TABLE IndexData",
"ALTER TABLE IndexData2 RENAME TO IndexData",
"CREATE INDEX IndexData_composit ON IndexData(keyString, keyDate, keyNumber, indexId)",
"CREATE INDEX IndexData_objectStoreDataId ON IndexData(objectStoreDataId)",
"CREATE INDEX IndexData_indexId ON IndexData(indexId)",
"UPDATE MetaData SET value = 2 WHERE name = 'version'",
};
if (!runCommands(sqliteDatabase, commands, sizeof(commands) / sizeof(commands[0])))
return false;
databaseVersion = 2;
}
if (databaseVersion == 2) {
// We need to make the ObjectStoreData.value be a BLOB instead of TEXT.
static const char* commands[] = {
"DROP TABLE IF EXISTS ObjectStoreData", // This drops associated indices.
"CREATE TABLE ObjectStoreData (id INTEGER PRIMARY KEY, objectStoreId INTEGER NOT NULL REFERENCES ObjectStore(id), keyString TEXT, keyDate REAL, keyNumber REAL, value BLOB NOT NULL)",
"CREATE UNIQUE INDEX ObjectStoreData_composit ON ObjectStoreData(keyString, keyDate, keyNumber, objectStoreId)",
"UPDATE MetaData SET value = 3 WHERE name = 'version'",
};
if (!runCommands(sqliteDatabase, commands, sizeof(commands) / sizeof(commands[0])))
return false;
databaseVersion = 3;
}
return true;
}