C++ SQLiteDatabase类代码示例

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);
}