void NotificationPresenterImpl::maybeStorePermanentPermissions()
{
// If the permanent permissions haven't been modified, there's no need to
// save them to the DB. (If we haven't even loaded them, writing them now
// would overwrite the stored permissions with the empty set.)
SQLiteDatabase database;
if (!openDatabase(&database))
return;
SQLiteTransaction transaction(database);
// The number of entries should be small enough that it's not worth trying
// to perform a diff. Simply clear the table and repopulate it.
if (!database.executeCommand("DELETE FROM NotifyPermissions")) {
database.close();
return;
}
PermissionsMap::const_iterator end = s_notificationPermissions.end();
for (PermissionsMap::const_iterator iter = s_notificationPermissions.begin(); iter != end; ++iter) {
SQLiteStatement statement(database, "INSERT INTO NotifyPermissions (origin, allow) VALUES (?, ?)");
if (statement.prepare() != SQLResultOk)
continue;
statement.bindText(1, iter->first);
statement.bindInt64(2, iter->second);
statement.executeCommand();
}
transaction.commit();
database.close();
}
void GeolocationPositionCache::writeToDatabaseImpl()
{
SQLiteDatabase database;
{
MutexLocker lock(m_databaseFileMutex);
if (!database.open(m_databaseFile))
return;
}
RefPtr<Geoposition> cachedPosition;
{
MutexLocker lock(m_cachedPositionMutex);
if (m_cachedPosition)
cachedPosition = m_cachedPosition->threadSafeCopy();
}
SQLiteTransaction transaction(database);
if (!database.executeCommand("DELETE FROM CachedPosition"))
return;
SQLiteStatement statement(database, "INSERT INTO CachedPosition ("
"latitude, "
"longitude, "
"altitude, "
"accuracy, "
"altitudeAccuracy, "
"heading, "
"speed, "
"timestamp) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
if (statement.prepare() != SQLResultOk)
return;
statement.bindDouble(1, cachedPosition->coords()->latitude());
statement.bindDouble(2, cachedPosition->coords()->longitude());
if (cachedPosition->coords()->canProvideAltitude())
statement.bindDouble(3, cachedPosition->coords()->altitude());
else
statement.bindNull(3);
statement.bindDouble(4, cachedPosition->coords()->accuracy());
if (cachedPosition->coords()->canProvideAltitudeAccuracy())
statement.bindDouble(5, cachedPosition->coords()->altitudeAccuracy());
else
statement.bindNull(5);
if (cachedPosition->coords()->canProvideHeading())
statement.bindDouble(6, cachedPosition->coords()->heading());
else
statement.bindNull(6);
if (cachedPosition->coords()->canProvideSpeed())
statement.bindDouble(7, cachedPosition->coords()->speed());
else
statement.bindNull(7);
statement.bindInt64(8, cachedPosition->timestamp());
if (!statement.executeCommand())
return;
transaction.commit();
}
void GeolocationPermissions::maybeLoadPermanentPermissions()
{
if (s_permanentPermissionsLoaded)
return;
s_permanentPermissionsLoaded = true;
SQLiteDatabase database;
if (!openDatabase(&database))
return;
// Create the table here, such that even if we've just created the DB, the
// commands below should succeed.
if (!database.executeCommand("CREATE TABLE IF NOT EXISTS Permissions (origin TEXT UNIQUE NOT NULL, allow INTEGER NOT NULL)")) {
database.close();
return;
}
SQLiteStatement statement(database, "SELECT * FROM Permissions");
if (statement.prepare() != SQLResultOk) {
database.close();
return;
}
ASSERT(s_permanentPermissions.size() == 0);
while (statement.step() == SQLResultRow)
s_permanentPermissions.set(statement.getColumnText(0), statement.getColumnInt64(1));
database.close();
}
// deleteDatabaseFile has to release locks between looking up the list of databases to close and closing them. While this is in progress, the caller
// is responsible for making sure no new databases are opened in the file to be deleted.
bool DatabaseTracker::deleteDatabaseFile(SecurityOrigin* origin, const String& name, DeletionMode deletionMode)
{
String fullPath = fullPathForDatabase(origin, name, false);
if (fullPath.isEmpty())
return true;
#ifndef NDEBUG
{
LockHolder lockDatabase(m_databaseGuard);
ASSERT(isDeletingDatabaseOrOriginFor(origin, name));
}
#endif
Vector<RefPtr<Database>> deletedDatabases;
// Make sure not to hold the any locks when calling
// Database::markAsDeletedAndClose(), since that can cause a deadlock
// during the synchronous DatabaseThread call it triggers.
{
LockHolder openDatabaseMapLock(m_openDatabaseMapGuard);
if (m_openDatabaseMap) {
// There are some open databases, lets check if they are for this origin.
DatabaseNameMap* nameMap = m_openDatabaseMap->get(origin);
if (nameMap && nameMap->size()) {
// There are some open databases for this origin, let's check
// if they are this database by name.
DatabaseSet* databaseSet = nameMap->get(name);
if (databaseSet && databaseSet->size()) {
// We have some database open with this name. Mark them as deleted.
for (auto& database : *databaseSet)
deletedDatabases.append(database);
}
}
}
}
for (auto& database : deletedDatabases)
database->markAsDeletedAndClose();
#if PLATFORM(IOS)
if (deletionMode == DeletionMode::Deferred) {
// On the phone, other background processes may still be accessing this database. Deleting the database directly
// would nuke the POSIX file locks, potentially causing Safari/WebApp to corrupt the new db if it's running in the background.
// We'll instead truncate the database file to 0 bytes. If another process is operating on this same database file after
// the truncation, it should get an error since the database file is no longer valid. When Safari is launched
// next time, it'll go through the database files and clean up any zero-bytes ones.
SQLiteDatabase database;
if (!database.open(fullPath))
return false;
return SQLiteFileSystem::truncateDatabaseFile(database.sqlite3Handle());
}
#else
UNUSED_PARAM(deletionMode);
#endif
return SQLiteFileSystem::deleteDatabaseFile(fullPath);
}
std::string SQLiteException::buildCause(const std::string &reason, SQLiteDatabase &database)
{
int errorCode = database.getErrorCode();
std::string errorMessage = database.getErrorMessage();
std::ostringstream cause;
cause << reason << "; SQLite error: " << errorCode << "; " << errorMessage;
return cause.str();
}
void GeolocationPositionCache::writeToDB(const Geoposition* position)
{
ASSERT(position);
SQLiteDatabase database;
if (!s_databaseFile || !database.open(*s_databaseFile))
return;
SQLiteTransaction transaction(database);
if (!database.executeCommand("DELETE FROM CachedPosition"))
return;
SQLiteStatement statement(database, "INSERT INTO CachedPosition ("
"latitude, "
"longitude, "
"altitude, "
"accuracy, "
"altitudeAccuracy, "
"heading, "
"speed, "
"timestamp) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
if (statement.prepare() != SQLResultOk)
return;
statement.bindDouble(1, position->coords()->latitude());
statement.bindDouble(2, position->coords()->longitude());
if (position->coords()->canProvideAltitude())
statement.bindDouble(3, position->coords()->altitude());
else
statement.bindNull(3);
statement.bindDouble(4, position->coords()->accuracy());
if (position->coords()->canProvideAltitudeAccuracy())
statement.bindDouble(5, position->coords()->altitudeAccuracy());
else
statement.bindNull(5);
if (position->coords()->canProvideHeading())
statement.bindDouble(6, position->coords()->heading());
else
statement.bindNull(6);
if (position->coords()->canProvideSpeed())
statement.bindDouble(7, position->coords()->speed());
else
statement.bindNull(7);
statement.bindInt64(8, position->timestamp());
if (!statement.executeCommand())
return;
transaction.commit();
}
void GeolocationPositionCache::readFromDatabaseImpl()
{
SQLiteDatabase database;
{
MutexLocker lock(m_databaseFileMutex);
if (!database.open(m_databaseFile))
return;
}
// Create the table here, such that even if we've just created the
// DB, the commands below should succeed.
if (!database.executeCommand("CREATE TABLE IF NOT EXISTS CachedPosition ("
"latitude REAL NOT NULL, "
"longitude REAL NOT NULL, "
"altitude REAL, "
"accuracy REAL NOT NULL, "
"altitudeAccuracy REAL, "
"heading REAL, "
"speed REAL, "
"timestamp INTEGER NOT NULL)"))
return;
SQLiteStatement statement(database, "SELECT * FROM CachedPosition");
if (statement.prepare() != SQLResultOk)
return;
if (statement.step() != SQLResultRow)
return;
bool providesAltitude = statement.getColumnValue(2).type() != SQLValue::NullValue;
bool providesAltitudeAccuracy = statement.getColumnValue(4).type() != SQLValue::NullValue;
bool providesHeading = statement.getColumnValue(5).type() != SQLValue::NullValue;
bool providesSpeed = statement.getColumnValue(6).type() != SQLValue::NullValue;
RefPtr<Coordinates> coordinates = Coordinates::create(statement.getColumnDouble(0), // latitude
statement.getColumnDouble(1), // longitude
providesAltitude, statement.getColumnDouble(2), // altitude
statement.getColumnDouble(3), // accuracy
providesAltitudeAccuracy, statement.getColumnDouble(4), // altitudeAccuracy
providesHeading, statement.getColumnDouble(5), // heading
providesSpeed, statement.getColumnDouble(6)); // speed
DOMTimeStamp timestamp = statement.getColumnInt64(7); // timestamp
// A position may have been set since we called triggerReadFromDatabase().
MutexLocker lock(m_cachedPositionMutex);
if (m_cachedPosition)
return;
m_cachedPosition = Geoposition::create(coordinates.release(), timestamp);
}
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 setMetaData(SQLiteDatabase& sqliteDatabase, const String& name, const String& version, int64_t& rowId)
{
ASSERT(!name.isNull());
ASSERT(!version.isNull());
String sql = rowId != IDBDatabaseBackendImpl::InvalidId ? "UPDATE Databases SET name = ?, version = ? WHERE id = ?"
: "INSERT INTO Databases (name, description, version) VALUES (?, '', ?)";
SQLiteStatement query(sqliteDatabase, sql);
if (query.prepare() != SQLResultOk) {
ASSERT_NOT_REACHED();
return false;
}
query.bindText(1, name);
query.bindText(2, version);
if (rowId != IDBDatabaseBackendImpl::InvalidId)
query.bindInt64(3, rowId);
if (query.step() != SQLResultDone)
return false;
if (rowId == IDBDatabaseBackendImpl::InvalidId)
rowId = sqliteDatabase.lastInsertRowID();
return true;
}
void NotificationPresenterImpl::deleteDatabase()
{
LOGV("NotificationPresenterImpl::clearAll");
SQLiteDatabase database;
if (!openDatabase(&database))
return;
SQLiteTransaction transaction(database);
if (!database.executeCommand("DELETE FROM NotifyPermissions")) {
database.close();
return;
}
transaction.commit();
database.close();
}
void SQLiteResultSet::_closeNeedNoticeDb(bool flag)
{
if (_pStmt && _db)
{
SQLiteDatabase* db = _db;
_db = NULL;
sqlite3_reset(_pStmt);
if (!_cached)
{
sqlite3_finalize(_pStmt);
_pStmt = NULL;
}
if (flag)
{
db->resultSetDidfinalize(this);
}
}
}
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;
}
PassRefPtr<Geoposition> GeolocationPositionCache::readFromDB()
{
SQLiteDatabase database;
if (!s_databaseFile || !database.open(*s_databaseFile))
return 0;
// Create the table here, such that even if we've just created the
// DB, the commands below should succeed.
if (!database.executeCommand("CREATE TABLE IF NOT EXISTS CachedPosition ("
"latitude REAL NOT NULL, "
"longitude REAL NOT NULL, "
"altitude REAL, "
"accuracy REAL NOT NULL, "
"altitudeAccuracy REAL, "
"heading REAL, "
"speed REAL, "
"timestamp INTEGER NOT NULL)"))
return 0;
SQLiteStatement statement(database, "SELECT * FROM CachedPosition");
if (statement.prepare() != SQLResultOk)
return 0;
if (statement.step() != SQLResultRow)
return 0;
bool providesAltitude = statement.getColumnValue(2).type() != SQLValue::NullValue;
bool providesAltitudeAccuracy = statement.getColumnValue(4).type() != SQLValue::NullValue;
bool providesHeading = statement.getColumnValue(5).type() != SQLValue::NullValue;
bool providesSpeed = statement.getColumnValue(6).type() != SQLValue::NullValue;
RefPtr<Coordinates> coordinates = Coordinates::create(statement.getColumnDouble(0), // latitude
statement.getColumnDouble(1), // longitude
providesAltitude, statement.getColumnDouble(2), // altitude
statement.getColumnDouble(3), // accuracy
providesAltitudeAccuracy, statement.getColumnDouble(4), // altitudeAccuracy
providesHeading, statement.getColumnDouble(5), // heading
providesSpeed, statement.getColumnDouble(6)); // speed
return Geoposition::create(coordinates.release(), statement.getColumnInt64(7)); // timestamp
}
static bool runCommands(SQLiteDatabase& sqliteDatabase, const char** commands, size_t numberOfCommands)
{
SQLiteTransaction transaction(sqliteDatabase, false);
transaction.begin();
for (size_t i = 0; i < numberOfCommands; ++i) {
if (!sqliteDatabase.executeCommand(commands[i])) {
LOG_ERROR("Failed to run the following command for IndexedDB: %s", commands[i]);
return false;
}
}
transaction.commit();
return true;
}
bool DatabaseTracker::deleteDatabaseFileIfEmpty(const String& path)
{
if (!isZeroByteFile(path))
return false;
SQLiteDatabase database;
if (!database.open(path))
return false;
// Specify that we want the exclusive locking mode, so after the next read,
// we'll be holding the lock to this database file.
SQLiteStatement lockStatement(database, "PRAGMA locking_mode=EXCLUSIVE;");
if (lockStatement.prepare() != SQLITE_OK)
return false;
int result = lockStatement.step();
if (result != SQLITE_ROW && result != SQLITE_DONE)
return false;
lockStatement.finalize();
// Every sqlite database has a sqlite_master table that contains the schema for the database.
// http://www.sqlite.org/faq.html#q7
SQLiteStatement readStatement(database, "SELECT * FROM sqlite_master LIMIT 1;");
if (readStatement.prepare() != SQLITE_OK)
return false;
// We shouldn't expect any result.
if (readStatement.step() != SQLITE_DONE)
return false;
readStatement.finalize();
// At this point, we hold the exclusive lock to this file. Double-check again to make sure
// it's still zero bytes.
if (!isZeroByteFile(path))
return false;
return SQLiteFileSystem::deleteDatabaseFile(path);
}
static bool putObjectStoreData(SQLiteDatabase& db, IDBKey* key, SerializedScriptValue* value, int64_t objectStoreId, int64_t& dataRowId)
{
String sql = dataRowId != IDBObjectStoreBackendImpl::InvalidId ? "UPDATE ObjectStoreData SET keyString = ?, keyDate = ?, keyNumber = ?, value = ? WHERE id = ?"
: "INSERT INTO ObjectStoreData (keyString, keyDate, keyNumber, value, objectStoreId) VALUES (?, ?, ?, ?, ?)";
SQLiteStatement query(db, sql);
if (query.prepare() != SQLResultOk)
return false;
key->bindWithNulls(query, 1);
query.bindText(4, value->toWireString());
if (dataRowId != IDBDatabaseBackendImpl::InvalidId)
query.bindInt64(5, dataRowId);
else
query.bindInt64(5, objectStoreId);
if (query.step() != SQLResultDone)
return false;
if (dataRowId == IDBDatabaseBackendImpl::InvalidId)
dataRowId = db.lastInsertRowID();
return true;
}
bool SQLStatementBackend::execute(DatabaseBackend* db)
{
ASSERT(!m_resultSet->isValid());
// If we're re-running this statement after a quota violation, we need to clear that error now
clearFailureDueToQuota();
// This transaction might have been marked bad while it was being set up on the main thread,
// so if there is still an error, return false.
if (m_error)
return false;
db->setAuthorizerPermissions(m_permissions);
SQLiteDatabase* database = &db->sqliteDatabase();
SQLiteStatement statement(*database, m_statement);
int result = statement.prepare();
if (result != SQLResultOk) {
WTF_LOG(StorageAPI, "Unable to verify correctness of statement %s - error %i (%s)", m_statement.ascii().data(), result, database->lastErrorMsg());
if (result == SQLResultInterrupt)
m_error = SQLErrorData::create(SQLError::DATABASE_ERR, "could not prepare statement", result, "interrupted");
else
m_error = SQLErrorData::create(SQLError::SYNTAX_ERR, "could not prepare statement", result, database->lastErrorMsg());
db->reportExecuteStatementResult(1, m_error->code(), result);
return false;
}
// FIXME: If the statement uses the ?### syntax supported by sqlite, the bind parameter count is very likely off from the number of question marks.
// If this is the case, they might be trying to do something fishy or malicious
if (statement.bindParameterCount() != m_arguments.size()) {
WTF_LOG(StorageAPI, "Bind parameter count doesn't match number of question marks");
m_error = SQLErrorData::create(db->isInterrupted() ? SQLError::DATABASE_ERR : SQLError::SYNTAX_ERR, "number of '?'s in statement string does not match argument count");
db->reportExecuteStatementResult(2, m_error->code(), 0);
return false;
}
for (unsigned i = 0; i < m_arguments.size(); ++i) {
result = statement.bindValue(i + 1, m_arguments[i]);
if (result == SQLResultFull) {
setFailureDueToQuota(db);
return false;
}
if (result != SQLResultOk) {
WTF_LOG(StorageAPI, "Failed to bind value index %i to statement for query '%s'", i + 1, m_statement.ascii().data());
db->reportExecuteStatementResult(3, SQLError::DATABASE_ERR, result);
m_error = SQLErrorData::create(SQLError::DATABASE_ERR, "could not bind value", result, database->lastErrorMsg());
return false;
}
}
// Step so we can fetch the column names.
result = statement.step();
if (result == SQLResultRow) {
int columnCount = statement.columnCount();
SQLResultSetRowList* rows = m_resultSet->rows();
for (int i = 0; i < columnCount; i++)
rows->addColumn(statement.getColumnName(i));
do {
for (int i = 0; i < columnCount; i++)
rows->addResult(statement.getColumnValue(i));
result = statement.step();
} while (result == SQLResultRow);
if (result != SQLResultDone) {
db->reportExecuteStatementResult(4, SQLError::DATABASE_ERR, result);
m_error = SQLErrorData::create(SQLError::DATABASE_ERR, "could not iterate results", result, database->lastErrorMsg());
return false;
}
} else if (result == SQLResultDone) {
// Didn't find anything, or was an insert
if (db->lastActionWasInsert())
m_resultSet->setInsertId(database->lastInsertRowID());
} else if (result == SQLResultFull) {
// Return the Quota error - the delegate will be asked for more space and this statement might be re-run
setFailureDueToQuota(db);
return false;
} else if (result == SQLResultConstraint) {
db->reportExecuteStatementResult(6, SQLError::CONSTRAINT_ERR, result);
m_error = SQLErrorData::create(SQLError::CONSTRAINT_ERR, "could not execute statement due to a constaint failure", result, database->lastErrorMsg());
return false;
} else {
db->reportExecuteStatementResult(5, SQLError::DATABASE_ERR, result);
m_error = SQLErrorData::create(SQLError::DATABASE_ERR, "could not execute statement", result, database->lastErrorMsg());
return false;
}
// FIXME: If the spec allows triggers, and we want to be "accurate" in a different way, we'd use
// sqlite3_total_changes() here instead of sqlite3_changed, because that includes rows modified from within a trigger
// For now, this seems sufficient
m_resultSet->setRowsAffected(database->lastChanges());
db->reportExecuteStatementResult(0, -1, 0); // OK
return true;
}
// A test function.
void SQLiteDatabase::test()
{
// Sample usage code follows...
std::string strFileName = "sqlite3.txt";
SQLiteDatabase db;
try {
Verbose::out(1, "*");
Verbose::out(1, "Create/Open database: sqlite3.txt");
db.open(strFileName);
SQLiteRecordset rset(db);
Verbose::out(1, "create table Test1 (Id int primary key, Name varchar(255))");
db.execute("create table Test1 (Id int primary key, Name varchar(255))");
Verbose::out(2, "create table Test1 (Id int primary key, Name varchar(255))");
db.execute("create table Test2 (Id int primary key, Name varchar(255))");
Verbose::out(1, "Begin transaction...");
db.beginTransaction();
for (int iIndex = 1; (iIndex < 10); iIndex++)
{
std::string strSQL = "insert into Test1 values (" + ::getInt(iIndex) + ", 'Test1-" + ::getInt(iIndex) + "')";
Verbose::out(1, "\t" + strSQL);
db.execute(strSQL);
}
for (int iIndex = 1; (iIndex < 10); iIndex++)
{
std::string strSQL = "insert into Test2 values (" + ::getInt(iIndex) + ", 'Test2-" + ::getInt(iIndex) + "')";
Verbose::out(1, "\t" + strSQL);
db.execute(strSQL);
}
Verbose::out(1, "Commit transaction...");
db.commitTransaction();
Verbose::out(1, "select Test1.id, Test1.Name, Test2.Name from Test1, Test2 where Test1.Id = Test2.id");
rset.open("select Test1.id, Test1.Name, Test2.Name from Test1, Test2 where Test1.Id = Test2.id");
while (rset.fetch())
{
int iID = rset.getInteger(0);
std::string strName = rset.getString(1);
std::string strName2 = rset.getString(2);
Verbose::out(1, "\tfetched: " + ::getInt(iID) + ", " + strName + ", " + strName2);
}
rset.close();
Verbose::out(1, "drop table Test1");
db.execute("drop table Test1");
Verbose::out(1, "drop table Test2");
db.execute("drop table Test2");
Verbose::out(1, "Closing SQLite database");
db.close();
Verbose::out(1, "*");
} catch (SQLiteException& e) {db.rollbackTransaction(); db.close(); Verbose::out(1, e.getMessage());}
// End sample usage code
}
PassRefPtrWillBeRawPtr<SQLResultSet> SQLStatementSync::execute(DatabaseSync* db, ExceptionState& exceptionState)
{
db->setAuthorizerPermissions(m_permissions);
SQLiteDatabase* database = &db->sqliteDatabase();
SQLiteStatement statement(*database, m_statement);
int result = statement.prepare();
if (result != SQLResultOk) {
if (result == SQLResultInterrupt)
exceptionState.throwDOMException(SQLDatabaseError, "Connection to the database interrupted.");
else
exceptionState.throwDOMException(SyntaxError, "Could not prepare statement.");
db->setLastErrorMessage("could not prepare statement", result, database->lastErrorMsg());
return nullptr;
}
if (statement.bindParameterCount() != m_arguments.size()) {
if (db->isInterrupted())
exceptionState.throwDOMException(SQLDatabaseError, "Connection to the database interrupted.");
else
exceptionState.throwDOMException(SyntaxError, "Number of '?'s in statement string (" + String::number(statement.bindParameterCount()) + ") does not match the arguments provided (" + String::number(m_arguments.size()) + ").");
db->setLastErrorMessage("number of '?'s in statement string does not match argument count");
return nullptr;
}
for (unsigned i = 0; i < m_arguments.size(); ++i) {
result = statement.bindValue(i + 1, m_arguments[i]);
if (result == SQLResultFull) {
exceptionState.throwDOMException(QuotaExceededError, SQLError::quotaExceededErrorMessage);
db->setLastErrorMessage("there was not enough remaining storage space");
return nullptr;
}
if (result != SQLResultOk) {
exceptionState.throwDOMException(SQLDatabaseError, "Could not bind value.");
db->setLastErrorMessage("could not bind value", result, database->lastErrorMsg());
return nullptr;
}
}
RefPtrWillBeRawPtr<SQLResultSet> resultSet = SQLResultSet::create();
// Step so we can fetch the column names.
result = statement.step();
if (result == SQLResultRow) {
int columnCount = statement.columnCount();
SQLResultSetRowList* rows = resultSet->rows();
for (int i = 0; i < columnCount; i++)
rows->addColumn(statement.getColumnName(i));
do {
for (int i = 0; i < columnCount; i++)
rows->addResult(statement.getColumnValue(i));
result = statement.step();
} while (result == SQLResultRow);
if (result != SQLResultDone) {
exceptionState.throwDOMException(SQLDatabaseError, "Could not iterate results.");
db->setLastErrorMessage("could not iterate results", result, database->lastErrorMsg());
return nullptr;
}
} else if (result == SQLResultDone) {
// Didn't find anything, or was an insert.
if (db->lastActionWasInsert())
resultSet->setInsertId(database->lastInsertRowID());
} else if (result == SQLResultFull) {
// Quota error, the delegate will be asked for more space and this statement might be re-run.
exceptionState.throwDOMException(QuotaExceededError, SQLError::quotaExceededErrorMessage);
db->setLastErrorMessage("there was not enough remaining storage space");
return nullptr;
} else if (result == SQLResultConstraint) {
exceptionState.throwDOMException(ConstraintError, "A constraint was violated.");
db->setLastErrorMessage("statement failed due to a constraint failure");
return nullptr;
} else {
exceptionState.throwDOMException(SQLDatabaseError, "Could not execute statement.");
db->setLastErrorMessage("could not execute statement", result, database->lastErrorMsg());
return nullptr;
}
resultSet->setRowsAffected(database->lastChanges());
return resultSet.release();
}
static void truncateDatabaseFile(SQLiteDatabase& database)
{
sqlite3_file_control(database.sqlite3Handle(), 0, SQLITE_TRUNCATE_DATABASE, 0);
}
PassRefPtr<SQLResultSet> SQLStatementSync::execute(DatabaseSync* db, ExceptionCode& ec)
{
db->setAuthorizerPermissions(m_permissions);
SQLiteDatabase* database = &db->sqliteDatabase();
SQLiteStatement statement(*database, m_statement);
int result = statement.prepare();
if (result != SQLResultOk) {
ec = (result == SQLResultInterrupt ? SQLException::DATABASE_ERR : SQLException::SYNTAX_ERR);
db->setLastErrorMessage("could not prepare statement", result, database->lastErrorMsg());
return 0;
}
if (statement.bindParameterCount() != m_arguments.size()) {
ec = (db->isInterrupted()? SQLException::DATABASE_ERR : SQLException::SYNTAX_ERR);
db->setLastErrorMessage("number of '?'s in statement string does not match argument count");
return 0;
}
for (unsigned i = 0; i < m_arguments.size(); ++i) {
result = statement.bindValue(i + 1, m_arguments[i]);
if (result == SQLResultFull) {
ec = SQLException::QUOTA_ERR;
db->setLastErrorMessage("there was not enough remaining storage space");
return 0;
}
if (result != SQLResultOk) {
ec = SQLException::DATABASE_ERR;
db->setLastErrorMessage("could not bind value", result, database->lastErrorMsg());
return 0;
}
}
RefPtr<SQLResultSet> resultSet = SQLResultSet::create();
// Step so we can fetch the column names.
result = statement.step();
if (result == SQLResultRow) {
int columnCount = statement.columnCount();
SQLResultSetRowList* rows = resultSet->rows();
for (int i = 0; i < columnCount; i++)
rows->addColumn(statement.getColumnName(i));
do {
for (int i = 0; i < columnCount; i++)
rows->addResult(statement.getColumnValue(i));
result = statement.step();
} while (result == SQLResultRow);
if (result != SQLResultDone) {
ec = SQLException::DATABASE_ERR;
db->setLastErrorMessage("could not iterate results", result, database->lastErrorMsg());
return 0;
}
} else if (result == SQLResultDone) {
// Didn't find anything, or was an insert.
if (db->lastActionWasInsert())
resultSet->setInsertId(database->lastInsertRowID());
} else if (result == SQLResultFull) {
// Quota error, the delegate will be asked for more space and this statement might be re-run.
ec = SQLException::QUOTA_ERR;
db->setLastErrorMessage("there was not enough remaining storage space");
return 0;
} else if (result == SQLResultConstraint) {
ec = SQLException::CONSTRAINT_ERR;
db->setLastErrorMessage("statement failed due to a constraint failure");
return 0;
} else {
ec = SQLException::DATABASE_ERR;
db->setLastErrorMessage("could not execute statement", result, database->lastErrorMsg());
return 0;
}
resultSet->setRowsAffected(database->lastChanges());
return resultSet.release();
}
int main()
{
SQLiteDatabase* db = new SQLiteDatabase(10,"aocemu.db");
db->start();
SQLiteQuery* q = new SQLiteQuery(Query::WORKER_THREAD,Query::HAS_RESULT);
q->setQueryText("SELECT * FROM auth");
q->setCallbackFunction(boost::bind(&dummyFunction, q));
SQLiteQuery* q2 = new SQLiteQuery(boost::bind(&dummyFunction, q2),Query::WORKER_THREAD,Query::HAS_RESULT);
q2->setQueryText("SELECT * FROM auth");
q2->setCallbackFunction(boost::bind(&dummyFunction, q2));
SQLiteQuery* q3 = new SQLiteQuery(boost::bind(&secondDummyFunction, q3),Query::MAIN_THREAD,Query::HAS_RESULT);
q3->setQueryText("SELECT * FROM auth");
q3->setCallbackFunction(boost::bind(&dummyFunction, q3));
SQLiteQuery* q4 = new SQLiteQuery(boost::bind(&secondDummyFunction, q4),Query::MAIN_THREAD,Query::HAS_RESULT);
q4->setQueryText("SELECT * FROM auth");
q4->setCallbackFunction(boost::bind(&dummyFunction, q4));
SQLiteQuery* q5 = new SQLiteQuery(boost::bind(&secondDummyFunction, q5),Query::MAIN_THREAD,Query::HAS_RESULT);
q5->setQueryText("SELECT * FROM auth");
q5->setCallbackFunction(boost::bind(&dummyFunction, q5));
SQLiteQuery* q6 = new SQLiteQuery(boost::bind(&dummyFunction, q6),Query::WORKER_THREAD,Query::HAS_RESULT);
q6->setQueryText("SELECT * FROM auth");
q6->setCallbackFunction(boost::bind(&dummyFunction, q6));
SQLiteQuery* q7 = new SQLiteQuery(boost::bind(&dummyFunction, q7),Query::WORKER_THREAD,Query::HAS_RESULT);
q7->setQueryText("SELECT * FROM auth");
q7->setCallbackFunction(boost::bind(&dummyFunction, q7));
SQLiteQuery* q8 = new SQLiteQuery(boost::bind(&dummyFunction, q8),Query::WORKER_THREAD,Query::HAS_RESULT);
q8->setQueryText("SELECT * FROM auth");
q8->setCallbackFunction(boost::bind(&dummyFunction, q8));
db->enqueueQuery(q);
db->enqueueQuery(q2);
db->enqueueQuery(q3);
db->enqueueQuery(q4);
db->enqueueQuery(q5);
db->enqueueQuery(q6);
db->enqueueQuery(q7);
db->enqueueQuery(q8);
std::cout << "available connection : " << db->availableDBConnection() << std::endl;
db->runFinishedQueryCallback();
boost::xtime sleepA;
boost::xtime_get(&sleepA, boost::TIME_UTC);
sleepA.sec += 1;
boost::thread::sleep(sleepA);
db->runFinishedQueryCallback();
std::cout << "available connection : " << db->availableDBConnection() << std::endl;
db->shutdown();
delete db;
return 0;
}
