C++ SQLiteDatabase::open方法代码示例

void GeolocationPermissions::maybeLoadPermanentPermissions()
{
    if (s_permanentPermissionsLoaded)
        return;
    s_permanentPermissionsLoaded = true;

    SQLiteDatabase database;
    if (!database.open(s_databasePath + databaseName))
        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();
}

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

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

// 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
}

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

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
}

void GeolocationPermissions::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.)
    if (!s_permanentPermissionsModified)
        return;

    SQLiteDatabase database;
    if (!database.open(s_databasePath + databaseName))
        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 Permissions")) {
        database.close();
        return;
    }

    PermissionsMap::const_iterator end = s_permanentPermissions.end();
    for (PermissionsMap::const_iterator iter = s_permanentPermissions.begin(); iter != end; ++iter) {
         SQLiteStatement statement(database, "INSERT INTO Permissions (origin, allow) VALUES (?, ?)");
         if (statement.prepare() != SQLResultOk)
             continue;
         statement.bindText(1, iter->first);
         statement.bindInt64(2, iter->second);
         statement.executeCommand();
    }

    transaction.commit();
    database.close();

    s_permanentPermissionsModified = false;
}

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