C++ QSqlDatabase::commit方法代码示例

void SitesDatabaseInterface::deleteSite(int id)
{
    qDebug() << "db: " << this->dbPath;
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(this->dbPath);

    if(false == db.open())
    {
        qDebug() << "can not open database";
    }

    QSqlQuery query;
    query.prepare("DELETE FROM sites WHERE ID = :id;");
    query.bindValue(":id", id);
    if(false == query.exec())
    {
       qDebug() << "SQL ERROR : " << query.lastError();
    }
    db.commit();

    //We have also to delete the associated weather data to this site
    query.prepare("DELETE FROM meteo WHERE id_site = :id;");
    query.bindValue(":id", id);
    if(false == query.exec())
    {
       qDebug() << "SQL ERROR : " << query.lastError();
    }

    db.commit();
    db.close();
}

QModelIndex ListModel::moveItemHorizontal(const QModelIndex& index, int direction)
{
    ListItem* item = itemFromIndex(index);
    if (!item)
        return index;

    ListItem* parent = item->parent();
    int row = item->row();

    if (direction == App::Left) { // reparent as child of parent's parent
        if (!parent || parent->isRoot()) // already top level item
            return index;

        ListItem* newParent = parent->parent();
        int newRow = parent->row() + 1;

        QSqlDatabase db = QSqlDatabase::database();
        db.transaction();
        if (parent->takeChildDb(row) &&
            item->setParentDb(newParent, newRow)) {
            db.commit();
            if (beginMoveRows(indexFromItem(parent), row, row, indexFromItem(newParent), newRow)) {
                newParent->insertChild(newRow, parent->takeChild(row));
                endMoveRows();
            }
            return indexFromItem(item);
        } else {
            db.rollback();
            return index;
        }
    } else { // move as child of previous sibling
        ListItem* newParent = parent->child(row - 1);
        if (!newParent)
            return index;

        QSqlDatabase db = QSqlDatabase::database();
        db.transaction();
        if (parent->takeChildDb(row) &&
            item->setParentDb(newParent, newParent->childCount())) {
            db.commit();
            if (beginMoveRows(indexFromItem(parent), row, row, indexFromItem(newParent), newParent->childCount())) {
                newParent->appendChild(parent->takeChild(row));
                endMoveRows();
            }
            newParent->setExpanded(true);
            return indexFromItem(item);
        } else {
            db.rollback();
            return index;
        }
    }
}

bool Migration::run(bool forward)
{
    QSqlDatabase db = QSqlDatabase::database();
    qDebug() << db.databaseName();
    try {
        db.transaction();
        if (forward)
        {
            qDebug() << "DB::Migration [" << name() << "] run up";
            up();
        }
        else
        {
            qDebug() << "DB::Migration [" << name() << "] run down";
            down();
        }
        qDebug() << "commit " << name() << " " << db.lastError().text();
        db.commit();
        return true;
    } catch (...) {
        qDebug() << "DB::Migration [" << name() << "] failed: " << db.lastError().text();
        db.rollback();
        throw(QObject::tr("Can't update your database !"));
        return false;
    }
}

int main(int argc, char *argv[])
{
	QCoreApplication app(argc, argv);

	QSqlDatabase db1();

	{
		QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connection_name");
		db.setDatabaseName(":memory:");
		db.open();
		db.transaction();
		QSqlQuery q("CREATE TABLE t (i INTEGER)", db);
		q.prepare("INSERT INTO t (i) VALUES(?)");
		q.addBindValue(42);
		q.exec();
		db.commit();

		db.transaction();
		q.setForwardOnly(true);
		q.setForwardOnly(false);
		q.exec("SELECT * FROM t");
		q.first();
		int value = q.value(0).toInt();
		Q_ASSERT(value == 42);
		q.last();
		q.finish();
	}
	QSqlDatabase::removeDatabase("connection_name");

	QTimer::singleShot(500, qApp, SLOT(quit()));
	return app.exec();
}

void ListModel::removeItem(const QModelIndex& index)
{
    if (!index.isValid())
        return;

    ListItem* item = itemFromIndex(index);
    if (!item)
        return;

    bool isProject = item->isProject();

    ListItem* parent = item->parent();

    // disable removing the last child
    if (parent == root() && root()->childCount() == 1)
        return;

    int row = item->row();

    beginRemoveRows(indexFromItem(parent), row, row);
    QSqlDatabase db = QSqlDatabase::database();
    db.transaction();
    if (_removeItem(item)) {
        db.commit();
        if (isProject)
            emit projectRemoved();
    } else {
        db.rollback();
    }
    endRemoveRows();
}

void NetworkQueue::appendToLibrary(Scraper::ScraperData *data)
{
    qCDebug(phxLibrary) << "Game received: " << data->title << " On Platform: " << data->platform << " And artwork: " << data->front_boxart << " and: " << data->back_boxart;
    counter++;
    if (counter == 1) {
        emit progress(0.0);
        emit label("Attaching Artwork");
    }

    QSqlDatabase database = db_manager.handle();
    database.transaction();
    QSqlQuery q(database);

    q.prepare("UPDATE " + LibraryDbManager::table_games + " SET artwork = ? WHERE id = ?");

    q.addBindValue(data->front_boxart);
    q.addBindValue(data->libraryId);

    q.exec();

    delete data;

    emit ((qreal)counter / m_request_count * 100.0);

    if (counter == m_request_count) {
        database.commit();
        QMetaObject::invokeMethod(m_game_model, "select");

        emit label("");
        emit finished();
        m_request_count = 0;
        counter = 0;
    }

}

/**
 * Constructeur qui initialise le client par rapport au info contenu dans la BDD
 * @param identifiant, identifiant du client au sein de la BDD
 */
Customer::Customer(int identifiant){

    QSqlDatabase base = QSqlDatabase::database();
    QSqlQuery query;
    query.prepare("SELECT * FROM customer WHERE idCustomer=:id");
    query.bindValue(":id",identifiant);
    query.exec();

    query.next();
    QSqlRecord rec = query.record();

    m_name=rec.value("name").toString();
    m_adress=rec.value("adress").toString();
    m_adress2=rec.value("adress2").toString();
    m_city=rec.value("city").toString();
    m_country=rec.value("country").toString();
    m_email=rec.value("email").toString();
    m_phone=rec.value("phone").toString();
    m_postalCode=rec.value("postalCode").toInt();
    this->id=identifiant;

    query.finish();
    base.commit();

}

bool ContentCategory::removeCategory(QSqlDatabase &database,
                                     ContentCategory &category)
{
    // Use transaction.
    database.transaction();

    // Step1: Remove from category_category table.
    QSqlQuery query(database);
    query.prepare( "delete from category_category where "
                   "child_category_id = ?" );
    query.addBindValue(category.id());
    query.exec();

    query.prepare( "delete from category_category where "
                   "parent_category_id = ?" );
    query.addBindValue(category.id());
    query.exec();

    // Step2: Remove from content_category table
    query.prepare( "delete from content_category where "
                   "category_id = ?" );
    query.addBindValue(category.id());
    query.exec();

    // Step3: Remove from category table
    query.prepare( "delete from category where "
                   "id = ?" );
    query.addBindValue(category.id());
    query.exec();

    database.commit();
    category.clear();
    return true;
}

static bool prepareDatabase(QSqlDatabase &database)
{
    if (!database.transaction())
        return false;

    bool error = false;
    for (int i = 0; i < db_schema_count; ++i) {
        QSqlQuery query(database);

        if (!query.exec(QLatin1String(db_schema[i]))) {
            qWarning() << "Table creation failed";
            qWarning() << query.lastError();
            qWarning() << db_schema[i];
            error = true;
            break;
        }
    }

    if (error) {
        database.rollback();
        return false;
    } else {
        return database.commit();
    }
}

bool ValidDocument::createEntry(){
    QSqlDatabase base = QSqlDatabase::database();
    bool retour=false;

    QSqlQuery query;
    query.prepare("INSERT INTO DocumentValide (idCustomer,price,type,payment,date,view,tva) VALUES (:idCustomer,:price,:type,:payment,:date,:view,:tva )");
    query.bindValue(":idCustomer",idCustomer);
    query.bindValue(":price",price);
    query.bindValue(":type",docType);
    query.bindValue(":payment",payment);
    query.bindValue(":date",mdate.toString("yyyy-MM-dd"));
    query.bindValue(":view",view);
    query.bindValue(":tva",tva);

    retour=query.exec();

    if(retour)
        id=query.lastInsertId().toInt();

    query.finish();
    base.commit();


    Document d(idDocument);
    d.remove();


    return retour;

}

bool DlgEditPeoples::sqlUpdateField(
    const QString &sField,
    const T &vUi,
    T &vCp)
{
    if (vCp != vUi)
    {
        QSqlDatabase db = QSqlDatabase::database("mega");
        db.transaction();
        QSqlQuery q(db);
        q.prepare(QString("UPDATE peoples SET %1=? WHERE id=?").arg(sField));
        q.bindValue(1, m_id);
        q.bindValue(0, vUi);
        if (! q.exec())
        {
            QString se = tr("Update query error");
            qDebug() << se << ": " << q.lastError().text();
            QMessageBox::warning(this, se, q.lastError().text());
            db.rollback();
            qDebug() << q.executedQuery();
            q.clear();
            return false;
        }
        vCp = vUi;
        db.commit();
        q.clear();
    }
    return true;
}

bool Library::createUserDatabaseSchema( QSqlDatabase db ) {
    qCDebug( phxLibrary, "Initializing database schema" );
    db.transaction();

    qCDebug( phxLibrary ) << db;

    QSqlQuery q( db );
    q.exec( "CREATE TABLE schema_version (version INTEGER NOT NULL)" );
    q.exec( QStringLiteral( "INSERT INTO schema_version (version) VALUES (0)" ) );
    q.exec( QStringLiteral( "CREATE TABLE games (\n" )
            % QStringLiteral( "   rowIndex INTEGER PRIMARY KEY AUTOINCREMENT,\n" )

            % QStringLiteral( "   \n/* game info */\n" )
            % QStringLiteral( "   title TEXT NOT NULL,\n" )
            % QStringLiteral( "   system TEXT,\n" )
            % QStringLiteral( "   region TEXT,\n" )
            % QStringLiteral( "   goodtoolsCode TEXT,\n" )
            % QStringLiteral( "   timePlayed DATETIME,\n" )
            % QStringLiteral( "   artworkUrl TEXT,\n" )
            % QStringLiteral( "   coreFilePath TEXT,\n" )

            % QStringLiteral( "   /* file info */" )
            % QStringLiteral( "   absolutePath TEXT,\n" )
            % QStringLiteral( "   absoluteFilePath TEXT UNIQUE NOT NULL,\n" )
            % QStringLiteral( "   crc32Checksum TEXT\n" )
            % QStringLiteral( ")" ) );

    q.exec( QStringLiteral( "CREATE INDEX title_index ON games (title)" ) );
    q.exec( QStringLiteral( "CREATE INDEX favorite_index ON games (is_favorite)" ) );


    // Create Collections Mapping Table
    q.exec( QStringLiteral( "CREATE TABLE collections (\n" )
            % QStringLiteral( " collectionID INTEGER PRIMARY KEY AUTOINCREMENT,\n" )
            % QStringLiteral( " collectionName TEXT UNIQUE NOT NULL\n" )
            % QStringLiteral( ")" ) );

    // Create Collections Table
    q.exec( QStringLiteral( "CREATE TABLE collectionMappings (\n" )
            % QStringLiteral( " collectionID INTEGER,\n" )
            % QStringLiteral( " rowIndex INTEGER,\n" )
            % QStringLiteral( " FOREIGN KEY (collectionID) REFERENCES collections " )
            % QStringLiteral( "(collectionID) ON DELETE CASCADE ON UPDATE CASCADE\n" )
            % QStringLiteral( " FOREIGN KEY (rowIndex) REFERENCES games " )
            % QStringLiteral( "(rowIndex) ON DELETE CASCADE ON UPDATE CASCADE\n" )
            % QStringLiteral( ")" ) );

    q.exec( QStringLiteral( "INSERT INTO collections " )
            % QStringLiteral( "(collectionID, collectionName) VALUES (0, 'All')" ) );

    // Create default core table
    q.exec( QStringLiteral( "CREATE TABLE defaultCores (\n" )
            % QStringLiteral( " system TEXT UNIQUE NOT NULL," )
            % QStringLiteral( " defaultCore TEXT" )
            % QStringLiteral( ")" ) );

    db.commit();

    return true;
}

static v8::Handle<v8::Value> qmlsqldatabase_changeVersion(const v8::Arguments& args)
{
    if (args.Length() < 2)
        return v8::Undefined();

    QV8SqlDatabaseResource *r = v8_resource_cast<QV8SqlDatabaseResource>(args.This());
    if (!r || r->type != QV8SqlDatabaseResource::Database)
        V8THROW_REFERENCE("Not a SQLDatabase object");

    QV8Engine *engine = r->engine;

    QSqlDatabase db = r->database;
    QString from_version = engine->toString(args[0]);
    QString to_version = engine->toString(args[1]);
    v8::Handle<v8::Value> callback = args[2];

    if (from_version != r->version)
        V8THROW_SQL(SQLEXCEPTION_VERSION_ERR, QQmlEngine::tr("Version mismatch: expected %1, found %2").arg(from_version).arg(r->version));

    v8::Local<v8::Object> instance = databaseData(engine)->queryConstructor->NewInstance();
    QV8SqlDatabaseResource *r2 = new QV8SqlDatabaseResource(engine);
    r2->type = QV8SqlDatabaseResource::Query;
    r2->database = db;
    r2->version = r->version;
    r2->inTransaction = true;
    instance->SetExternalResource(r2);

    bool ok = true;
    if (callback->IsFunction()) {
        ok = false;
        db.transaction();

        v8::TryCatch tc;
        v8::Handle<v8::Value> callbackArgs[] = { instance };
        v8::Handle<v8::Function>::Cast(callback)->Call(engine->global(), 1, callbackArgs);

        if (tc.HasCaught()) {
            db.rollback();
            tc.ReThrow();
            return v8::Handle<v8::Value>();
        } else if (!db.commit()) {
            db.rollback();
            V8THROW_SQL(SQLEXCEPTION_UNKNOWN_ERR,QQmlEngine::tr("SQL transaction failed"));
        } else {
            ok = true;
        }
    }

    r2->inTransaction = false;

    if (ok) {
        r2->version = to_version;
#ifndef QT_NO_SETTINGS
        QSettings ini(qmlsqldatabase_databaseFile(db.connectionName(),engine) + QLatin1String(".ini"), QSettings::IniFormat);
        ini.setValue(QLatin1String("Version"), to_version);
#endif
    }

    return v8::Undefined();
}

QModelIndex ListModel::moveItemVertical(const QModelIndex& index, App::Direction direction)
{
    ListItem* item = itemFromIndex(index);
    if (!item)
        return index;

    int row = item->row();
    if (direction == App::Up && row == 0)
        return index;

    ListItem* parent = item->parent();
    if (direction == App::Down && row == (parent->childCount() - 1))
        return index;

    ListItem* otherItem = parent->child(direction == App::Up ? row - 1 : row + 1);
    int downRow = direction == App::Down ? row : row - 1; // the row that is being moved down

    QSqlDatabase db = QSqlDatabase::database();
    db.transaction();

    if (item->setRow(direction == App::Down ? row + 1 : row - 1) &&
        otherItem->setRow(row)) {
        db.commit();
        if (beginMoveRows(index.parent(), downRow, downRow, index.parent(), downRow + 2)) {
            parent->moveChild(downRow);
            endMoveRows();
            return indexFromItem(item);
        }
    } else
        db.rollback();
    return index;
}

void FileCache::allocFileSpace( int size )
{
    QSqlDatabase database = QSqlDatabase::database( "FileCache" );
    database.transaction();

    bool ok = allocFileSpace( size, database );
    if ( ok )
        ok = database.commit();

    if ( !ok )
        database.rollback();
}