uint connection::authenticate_user(std::string const& name, bool& authenticated)
{
authenticated = true;
// Insert or ignore.
sqlite3_mutex_enter(sqlite3_db_mutex(db_connection_m));
sqlite3_exec(db_connection_m, "BEGIN;", 0, 0, 0);
int error = SQLITE_OK;
sqlite3_stmt* prepared_stmt;
char const* insert = "INSERT OR IGNORE INTO user (name) VALUES (?);";
error = sqlite3_prepare_v2(db_connection_m, insert, -1, &prepared_stmt, 0);
if (error != SQLITE_OK)
{
throw std::runtime_error(std::string("Could not prepare a statement: ") + sqlite3_errmsg(db_connection_m));
}
sqlite3_bind_text(prepared_stmt, 1, name.data(), -1, SQLITE_TRANSIENT);
if (sqlite3_step(prepared_stmt) != SQLITE_DONE)
{
throw std::runtime_error(std::string("Could not execute a statement: ") + sqlite3_errmsg(db_connection_m));
}
sqlite3_finalize(prepared_stmt);
sqlite3_exec(db_connection_m, "COMMIT;", 0, 0, 0);
sqlite3_mutex_leave(sqlite3_db_mutex(db_connection_m));
// Select.
char const* select = "SELECT (user.id) FROM user WHERE (user.name = ?);";
error = sqlite3_prepare_v2(db_connection_m, select, -1, &prepared_stmt, 0);
if (error != SQLITE_OK)
{
throw std::runtime_error(std::string("Could not prepare a statement: ") + sqlite3_errmsg(db_connection_m));
}
sqlite3_bind_text(prepared_stmt, 1, name.data(), -1, SQLITE_TRANSIENT);
uint result = 0;
if (sqlite3_step(prepared_stmt) != SQLITE_ROW)
{
authenticated = false;
throw std::runtime_error(std::string("Could not execute a statement: ") + sqlite3_errmsg(db_connection_m));
}
else
{
result = static_cast<uint>(sqlite3_column_int(prepared_stmt, 0));
}
sqlite3_finalize(prepared_stmt);
return result;
}
void connection::create_tables()
{
sqlite3_mutex_enter(sqlite3_db_mutex(db_connection_m));
sqlite3_exec(db_connection_m, "BEGIN;", 0, 0, 0);
char const* game =
"CREATE TABLE IF NOT EXISTS game "
"( id INTEGER PRIMARY KEY "
", name TEXT "
", UNIQUE(name) "
");";
char const* game_session =
"CREATE TABLE IF NOT EXISTS game_session "
"( id INTEGER PRIMARY KEY "
", game_id INTEGER "
", data TEXT " // Can differ game from game, but it is usually replay data in JSON.
"); ";
char const* game_session_participant =
"CREATE TABLE IF NOT EXISTS game_session_participant "
"( user_id INTEGER "
", game_session_id INTEGER "
", result INTEGER " // Can differ game from game, but it is usually one of (defeat, draw, victory).
", type INTEGER " // Can differ game from game, for connect this is either (0 == white) or (1 == black).
");";
char const* user =
"******"
"( id INTEGER PRIMARY KEY "
", name TEXT "
", UNIQUE(name) "
");";
sqlite3_exec(db_connection_m, game, 0, 0, 0);
sqlite3_exec(db_connection_m, user, 0, 0, 0);
sqlite3_exec(db_connection_m, game_session, 0, 0, 0);
sqlite3_exec(db_connection_m, game_session_participant, 0, 0, 0);
sqlite3_exec(db_connection_m, "COMMIT;", 0, 0, 0);
sqlite3_mutex_leave(sqlite3_db_mutex(db_connection_m));
}
void bgg_client::data::database::unlock()
{
sqlite3_mutex_leave(sqlite3_db_mutex(m_db));
}
void bgg_client::data::database::lock()
{
sqlite3_mutex_enter(sqlite3_db_mutex(m_db));
}
int bwl_init_database(const char *szDatabaseFilePath)
{
if (SQLITE_OK != sqlite3_config(SQLITE_CONFIG_SERIALIZED))
{
LOG("Configure Sqlite3 error:%s.", sqlite3_errmsg(db));
return BWLIST_ERROR;
}
if (SQLITE_OK != sqlite3_open(szDatabaseFilePath, &db))
{
LOG("Can't open database:%s.", sqlite3_errmsg(db));
sqlite3_close(db);
return BWLIST_ERROR;
}
sqlite3_busy_timeout(db, 1);
//sqlite3_update_hook(db, db_change_hook, NULL);
if (NULL == (db_mutex = sqlite3_db_mutex(db)))
{
LOG("SQLITE IS NOT IN SERIALIZED MOD");
sqlite3_close(db);
return BWLIST_ERROR;
}
sqlite3_mutex_enter(db_mutex);
if (SQLITE_OK != sqlite3_exec(db, "PRAGMA page_size=4096;", 0, 0, NULL))
{
LOG("Can't set page_size:%s.", sqlite3_errmsg(db));
sqlite3_mutex_leave(db_mutex);
sqlite3_close(db);
return BWLIST_ERROR;
}
if (SQLITE_OK != sqlite3_exec(db, "PRAGMA cache_size=8000;", 0, 0, NULL))
{
LOG("Can't set cache_size:%s.", sqlite3_errmsg(db));
sqlite3_mutex_leave(db_mutex);
sqlite3_close(db);
return BWLIST_ERROR;
}
// if blacklist not exists, create it
char *sSqlCreateBlacklist = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %Q"
"(PlateNumber TEXT NOT NULL PRIMARY KEY, PlateType INTEGER, Comment TEXT);", szBlackListTable);
int rc_bl = sqlite3_exec(db, sSqlCreateBlacklist, 0, 0, NULL);
sqlite3_free(sSqlCreateBlacklist);
if (SQLITE_OK != rc_bl)
{
LOG("Create BlackList Error:%s", sqlite3_errmsg(db));
sqlite3_mutex_leave(db_mutex);
sqlite3_close(db);
return BWLIST_ERROR;
}
// if whitelist not exists, create it
char *sSqlCreateWhitelist = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %Q"
"(PlateNumber TEXT NOT NULL PRIMARY KEY, PlateType INTEGER, Comment TEXT);", szWhiteListTable);
int rc_wl = sqlite3_exec(db, sSqlCreateWhitelist, 0, 0, NULL);
sqlite3_free(sSqlCreateWhitelist);
if (SQLITE_OK != rc_wl)
{
LOG("Create WhiteList error:%s.", sqlite3_errmsg(db));
sqlite3_mutex_leave(db_mutex);
sqlite3_close(db);
return BWLIST_ERROR;
}
sqlite3_mutex_leave(db_mutex);
if (0 != pthread_create(&query_tid, NULL, query_thread, NULL))
{
LOG("%s", strerror(errno));
sqlite3_close(db);
return BWLIST_ERROR;
}
return BWLIST_OK;
}
nsresult
Connection::initialize(nsIFile *aDatabaseFile)
{
NS_ASSERTION (!mDBConn, "Initialize called on already opened database!");
int srv;
nsresult rv;
mDatabaseFile = aDatabaseFile;
if (aDatabaseFile) {
nsAutoString path;
rv = aDatabaseFile->GetPath(path);
NS_ENSURE_SUCCESS(rv, rv);
srv = ::sqlite3_open_v2(NS_ConvertUTF16toUTF8(path).get(), &mDBConn, mFlags,
NULL);
}
else {
// in memory database requested, sqlite uses a magic file name
srv = ::sqlite3_open_v2(":memory:", &mDBConn, mFlags, NULL);
}
if (srv != SQLITE_OK) {
mDBConn = nsnull;
return convertResultCode(srv);
}
// Properly wrap the database handle's mutex.
sharedDBMutex.initWithMutex(sqlite3_db_mutex(mDBConn));
#ifdef PR_LOGGING
if (!gStorageLog)
gStorageLog = ::PR_NewLogModule("mozStorage");
::sqlite3_trace(mDBConn, tracefunc, this);
nsCAutoString leafName(":memory");
if (aDatabaseFile)
(void)aDatabaseFile->GetNativeLeafName(leafName);
PR_LOG(gStorageLog, PR_LOG_NOTICE, ("Opening connection to '%s' (%p)",
leafName.get(), this));
#endif
// Switch db to preferred page size in case the user vacuums.
sqlite3_stmt *stmt;
srv = prepareStmt(mDBConn, NS_LITERAL_CSTRING("PRAGMA page_size = 32768"),
&stmt);
if (srv == SQLITE_OK) {
(void)stepStmt(stmt);
(void)::sqlite3_finalize(stmt);
}
// Register our built-in SQL functions.
srv = registerFunctions(mDBConn);
if (srv != SQLITE_OK) {
::sqlite3_close(mDBConn);
mDBConn = nsnull;
return convertResultCode(srv);
}
// Register our built-in SQL collating sequences.
srv = registerCollations(mDBConn, mStorageService);
if (srv != SQLITE_OK) {
::sqlite3_close(mDBConn);
mDBConn = nsnull;
return convertResultCode(srv);
}
// Execute a dummy statement to force the db open, and to verify if it is
// valid or not.
srv = prepareStmt(mDBConn, NS_LITERAL_CSTRING("SELECT * FROM sqlite_master"),
&stmt);
if (srv == SQLITE_OK) {
srv = stepStmt(stmt);
if (srv == SQLITE_DONE || srv == SQLITE_ROW)
srv = SQLITE_OK;
::sqlite3_finalize(stmt);
}
if (srv != SQLITE_OK) {
::sqlite3_close(mDBConn);
mDBConn = nsnull;
return convertResultCode(srv);
}
// Set the synchronous PRAGMA, according to the pref
nsCOMPtr<nsIPrefBranch> pref(do_GetService(NS_PREFSERVICE_CONTRACTID));
PRInt32 synchronous = 1; // Default to NORMAL if pref not set
if (pref)
(void)pref->GetIntPref(PREF_TS_SYNCHRONOUS, &synchronous);
switch (synchronous) {
case 2:
(void)ExecuteSimpleSQL(NS_LITERAL_CSTRING(
"PRAGMA synchronous = FULL;"));
break;
case 0:
(void)ExecuteSimpleSQL(NS_LITERAL_CSTRING(
"PRAGMA synchronous = OFF;"));
break;
case 1:
default:
(void)ExecuteSimpleSQL(NS_LITERAL_CSTRING(
"PRAGMA synchronous = NORMAL;"));
break;
}
return NS_OK;
}
