void SQLitePersistedObjectSet::initDB() {
SQLiteDBPtr db = SQLite::getSingleton().open(mDBFilename);
sqlite3_busy_timeout(db->db(), 1000);
// Create the table for this object if it doesn't exist yet
String table_create = "CREATE TABLE IF NOT EXISTS ";
table_create += "\"" TABLE_NAME "\"";
table_create += "(object TEXT PRIMARY KEY, script_type TEXT, script_args TEXT, script_contents TEXT)";
int rc;
char* remain;
sqlite3_stmt* table_create_stmt;
mDB = db;
bool success = true;
rc = sqlite3_prepare_v2(db->db(), table_create.c_str(), -1, &table_create_stmt, (const char**)&remain);
success = success && !checkSQLiteError(rc, "Error preparing table create statement");
rc = sqlite3_step(table_create_stmt);
success = success && !checkSQLiteError(rc, "Error executing table create statement");
rc = sqlite3_finalize(table_create_stmt);
success = success && !checkSQLiteError(rc, "Error finalizing table create statement");
if (!success)
mDB.reset();
}
template <class WriteSet> SQLiteObjectStorage::Error SQLiteObjectStorage::applyWriteSet(const SQLiteDBPtr& db, const WriteSet& ws, int retries) {
int num_writes=ws.writes_size();
for (int ws_it=0;ws_it<num_writes;++ws_it) {
String object_hex = getTableName(ws.writes(ws_it));
String key_name = getKeyName(ws.writes(ws_it));
int rc;
char* remain;
// Insert or replace the value
String value_insert = "INSERT OR REPLACE INTO ";
if (!ws.writes(ws_it).has_data()) {
value_insert = "DELETE FROM ";
}
value_insert += "\"" TABLE_NAME "\"";
if (!ws.writes(ws_it).has_data()) {
value_insert+= " WHERE object = x\'" + object_hex + "\' AND key = ?";
}else {
//if (ws.writes(ws_it).has_data()) {
value_insert += " (object, key, value) VALUES(x\'" + object_hex + "\', ?, ?)";
//}
}
sqlite3_stmt* value_insert_stmt;
rc = sqlite3_prepare_v2(db->db(), value_insert.c_str(), -1, &value_insert_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing value insert statement");
rc = sqlite3_bind_text(value_insert_stmt, 1, key_name.data(), (int)key_name.size(), SQLITE_TRANSIENT);
SQLite::check_sql_error(db->db(), rc, NULL, "Error binding key name to value insert statement");
if (rc==SQLITE_OK) {
if (ws.writes(ws_it).has_data()) {
rc = sqlite3_bind_blob(value_insert_stmt, 2, ws.writes(ws_it).data().data(), (int)ws.writes(ws_it).data().size(), SQLITE_TRANSIENT);
SQLite::check_sql_error(db->db(), rc, NULL, "Error binding value to value insert statement");
}
}
int step_rc = sqlite3_step(value_insert_stmt);
if (step_rc != SQLITE_OK && step_rc != SQLITE_DONE)
sqlite3_reset(value_insert_stmt); // allow this to be cleaned up
rc = sqlite3_finalize(value_insert_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing value insert statement");
if (step_rc == SQLITE_BUSY || step_rc == SQLITE_LOCKED)
return DatabaseLocked;
}
return None;
}
void SQLiteObjectFactory::generate() {
SQLiteDBPtr db = SQLite::getSingleton().open(mDBFilename);
sqlite3_busy_timeout(db->db(), 1000);
String value_query = "SELECT object, script_type, script_args, script_contents FROM ";
value_query += "\"" TABLE_NAME "\"";
int rc;
char* remain;
sqlite3_stmt* value_query_stmt;
rc = sqlite3_prepare_v2(db->db(), value_query.c_str(), -1, &value_query_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing value query statement");
if (rc==SQLITE_OK) {
int step_rc = sqlite3_step(value_query_stmt);
while(step_rc == SQLITE_ROW) {
String object_str(
(const char*)sqlite3_column_text(value_query_stmt, 0),
sqlite3_column_bytes(value_query_stmt, 0)
);
String script_type(
(const char*)sqlite3_column_text(value_query_stmt, 1),
sqlite3_column_bytes(value_query_stmt, 1)
);
String script_args(
(const char*)sqlite3_column_text(value_query_stmt, 2),
sqlite3_column_bytes(value_query_stmt, 2)
);
String script_contents(
(const char*)sqlite3_column_text(value_query_stmt, 3),
sqlite3_column_bytes(value_query_stmt, 3)
);
HostedObjectPtr obj = mOH->createObject(
UUID(object_str, UUID::HexString()),
script_type, script_args, script_contents
);
step_rc = sqlite3_step(value_query_stmt);
}
if (step_rc != SQLITE_DONE) {
// reset the statement so it'll clean up properly
rc = sqlite3_reset(value_query_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing value query statement");
}
}
rc = sqlite3_finalize(value_query_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing value query statement");
}
void SQLiteObjectStorage::rollbackTransaction(const SQLiteDBPtr& db) {
String rollback = "ROLLBACK TRANSACTION";
int rc;
char* remain;
sqlite3_stmt* rollback_stmt;
rc = sqlite3_prepare_v2(db->db(), rollback.c_str(), -1, &rollback_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing rollback transaction statement");
rc = sqlite3_step(rollback_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error executing rollback statement");
rc = sqlite3_finalize(rollback_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing rollback statement");
}
/*
void SQLiteObjectStorage::handleResult(const EventPtr& evt) const {
SQLiteObjectStorageResultEventPtr revt = DowncastEvent<SQLiteObjectStorageResultEvent>(evt);
revt->callback()(revt->error());
}
*/
void SQLiteObjectStorage::beginTransaction(const SQLiteDBPtr& db) {
String begin = "BEGIN DEFERRED TRANSACTION";
int rc;
char* remain;
sqlite3_stmt* begin_stmt;
rc = sqlite3_prepare_v2(db->db(), begin.c_str(), -1, &begin_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing begin transaction statement");
rc = sqlite3_step(begin_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error executing begin statement");
rc = sqlite3_finalize(begin_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing begin statement");
}
SQLiteObjectStorage::SQLiteObjectStorage(bool transactional, const String& pl)
: mTransactional(transactional),
mDBName(),
mRetries(5),
mBusyTimeout(1000)
{
OptionValue*databaseFile;
OptionValue*workQueueInstance;
unsigned char * epoch=NULL;
static AtomicValue<int> counter(0);
int handle_offset=counter++;
InitializeClassOptions("sqlite",epoch+handle_offset,
databaseFile=new OptionValue("databasefile","",OptionValueType<String>(),"Sets the database to be used for storage"),
workQueueInstance=new OptionValue("workqueue","0",OptionValueType<void*>(),"Sets the work queue to be used for disk reads to a common work queue"),NULL);
(mOptions=OptionSet::getOptions("sqlite",epoch+handle_offset))->parse(pl);
mDiskWorkQueue=(Task::WorkQueue*)workQueueInstance->as<void*>();
mWorkQueueThread=NULL;
if(mDiskWorkQueue==NULL) {
mDiskWorkQueue=&_mLocalWorkQueue;
mWorkQueueThread=mDiskWorkQueue->createWorkerThreads(1);
}
mDBName = databaseFile->as<String>();
assert( !mDBName.empty() );
SQLiteDBPtr db = SQLite::getSingleton().open(mDBName);
sqlite3_busy_timeout(db->db(), mBusyTimeout);
// Create the table for this object if it doesn't exist yet
String table_create = "CREATE TABLE IF NOT EXISTS ";
table_create += "\"" TABLE_NAME "\"";
table_create += "(object TEXT, key TEXT, value TEXT, PRIMARY KEY(object, key))";
int rc;
char* remain;
sqlite3_stmt* table_create_stmt;
rc = sqlite3_prepare_v2(db->db(), table_create.c_str(), -1, &table_create_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing table create statement");
rc = sqlite3_step(table_create_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error executing table create statement");
rc = sqlite3_finalize(table_create_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing table create statement");
mDB = db;
}
bool SQLiteObjectStorage::commitTransaction(const SQLiteDBPtr& db) {
String commit = "COMMIT TRANSACTION";
int rc;
char* remain;
sqlite3_stmt* commit_stmt;
rc = sqlite3_prepare_v2(db->db(), commit.c_str(), -1, &commit_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing commit transaction statement");
rc = sqlite3_step(commit_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error executing commit statement");
rc = sqlite3_finalize(commit_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing commit statement");
return true;
}
template <class CompareSet> SQLiteObjectStorage::Error SQLiteObjectStorage::checkCompareSet(const SQLiteDBPtr& db, const CompareSet& cs) {
int num_compares=cs.compares_size();
for (int cs_it=0;cs_it<num_compares;++cs_it) {
String object_hex = getTableName(cs.compares(cs_it));
String key_name = getKeyName(cs.compares(cs_it));
String value_query = "SELECT value FROM ";
value_query += "\"" TABLE_NAME "\"";
value_query += " WHERE object == x\'" + object_hex + "\' AND key == ?";
int rc;
char* remain;
sqlite3_stmt* value_query_stmt;
rc = sqlite3_prepare_v2(db->db(), value_query.c_str(), -1, &value_query_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing value query statement");
rc = sqlite3_bind_text(value_query_stmt, 1, key_name.data(), (int)key_name.size(), SQLITE_TRANSIENT);
SQLite::check_sql_error(db->db(), rc, NULL, "Error binding key name to value query statement");
int step_rc = sqlite3_step(value_query_stmt);
bool passed_test = true;
if (step_rc == SQLITE_ROW) {
const char *data=(const char*)sqlite3_column_text(value_query_stmt, 0);
size_t size=sqlite3_column_bytes(value_query_stmt, 0);
if(cs.compares(cs_it).has_data()==false) {
passed_test=false;
} else if (cs.compares(cs_it).has_comparator()==false) {
if (cs.compares(cs_it).data().length() != size) passed_test = false;
else if (0!=memcmp(cs.compares(cs_it).data().data(), data, size)) passed_test = false;
}else {
switch (cs.compares(cs_it).comparator()) {
case Protocol::CompareElement::EQUAL:
if (cs.compares(cs_it).data().length() != size) passed_test = false;
else if (0!=memcmp(cs.compares(cs_it).data().data(), data, size)) passed_test = false;
break;
case Protocol::CompareElement::NEQUAL:
if (cs.compares(cs_it).data().length() == size
&& 0==memcmp(cs.compares(cs_it).data().data(), data, size))
passed_test = false;
break;
default:
passed_test=false;
break;
}
}
}
else {
sqlite3_reset(value_query_stmt); // allow proper clean up
}
rc = sqlite3_finalize(value_query_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing value query statement");
if (step_rc == SQLITE_BUSY || step_rc == SQLITE_LOCKED)
return DatabaseLocked;
if (step_rc == SQLITE_DONE)
return KeyMissing;
if (!passed_test)
return ComparisonFailed;
}
return None;
}
template <class ReadSet> SQLiteObjectStorage::Error SQLiteObjectStorage::applyReadSet(const SQLiteDBPtr& db, const ReadSet& rs, Protocol::Response&retval) {
int num_reads=rs.reads_size();
retval.clear_reads();
while (retval.reads_size()<num_reads)
retval.add_reads();
SQLiteObjectStorage::Error databaseError=None;
for (int rs_it=0;rs_it<num_reads;++rs_it) {
String object_hex = getTableName(rs.reads(rs_it));
String key_name = getKeyName(rs.reads(rs_it));
String value_query = "SELECT value FROM ";
value_query += "\"" TABLE_NAME "\"";
value_query += " WHERE object == x\'" + object_hex + "\' AND key == ?";
int rc;
char* remain;
sqlite3_stmt* value_query_stmt;
bool newStep=true;
bool locked=false;
rc = sqlite3_prepare_v2(db->db(), value_query.c_str(), -1, &value_query_stmt, (const char**)&remain);
SQLite::check_sql_error(db->db(), rc, NULL, "Error preparing value query statement");
if (rc==SQLITE_OK) {
rc = sqlite3_bind_text(value_query_stmt, 1, key_name.data(), (int)key_name.size(), SQLITE_TRANSIENT);
SQLite::check_sql_error(db->db(), rc, NULL, "Error binding key name to value query statement");
if (rc==SQLITE_OK) {
int step_rc = sqlite3_step(value_query_stmt);
while(step_rc == SQLITE_ROW) {
newStep=false;
retval.reads(rs_it).set_data((const char*)sqlite3_column_text(value_query_stmt, 0),sqlite3_column_bytes(value_query_stmt, 0));
step_rc = sqlite3_step(value_query_stmt);
}
if (step_rc != SQLITE_DONE) {
// reset the statement so it'll clean up properly
rc = sqlite3_reset(value_query_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing value query statement");
if (rc==SQLITE_LOCKED||rc==SQLITE_BUSY)
locked=true;
}
}
}
rc = sqlite3_finalize(value_query_stmt);
SQLite::check_sql_error(db->db(), rc, NULL, "Error finalizing value query statement");
if (locked||rc == SQLITE_LOCKED||rc==SQLITE_BUSY) {
retval.clear_reads();
return DatabaseLocked;
}
if (newStep) {
retval.reads(rs_it).clear_data();
retval.reads(rs_it).set_return_status(Protocol::StorageElement::KEY_MISSING);
}
if(rs.reads(rs_it).has_index()) {
retval.reads(rs_it).set_index(rs.reads(rs_it).index());
}
}
if (rs.has_options()&&(rs.options()&Protocol::ReadWriteSet::RETURN_READ_NAMES)!=0) {
// make sure read set is clear before each attempt
mergeKeysFromStorageSet( retval, rs );
}
return databaseError;
}
