C++ QSqlDriver类代码示例

	QVariant RelationalProxyModel::getRelationData(QModelIndex index) const {
		int column = index.column();
		if (not m_relations.contains(column))
			return QVariant();

		const QSqlRelation relation = m_relations[column];

		QVariant related_id = sourceModel()->data(mapToSource(index));

		QSqlDriver *driver = QSqlDatabase::database().driver();

		QSqlRecord search_record;
		search_record.append(QSqlField(relation.indexColumn(), QVariant::Int));
		search_record.setValue(relation.indexColumn(), QVariant(related_id.toInt()));

		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, relation.tableName(), search_record, false);
		QString select_statement = driver->sqlStatement(QSqlDriver::SelectStatement, relation.tableName(), QSqlDatabase::database().record(relation.tableName()), false);
		QSqlQuery query(QString("%1 %2").arg(select_statement, where_statement));

		query.next();
		if (not query.isValid())
			return QVariant();

		return query.record().value(relation.displayColumn());
	}

QString TSqlQuery::escapeIdentifier(const QString &identifier, QSqlDriver::IdentifierType type)
{
    QString ret = identifier;
    QSqlDriver *driver = TActionContext::currentDatabase().driver();
    if (!driver->isIdentifierEscaped(identifier, type)) {
        ret = driver->escapeIdentifier(identifier, type);
    }
    return ret;
}

QString TSqlQuery::escapeIdentifier(const QString &identifier, QSqlDriver::IdentifierType type, const QSqlDatabase &database)
{
    QString ret = identifier;
    QSqlDriver *driver = database.driver();
    if (!driver->isIdentifierEscaped(identifier, type)) {
        ret = driver->escapeIdentifier(identifier, type);
    }
    return ret;
}

void SqlTransactionTests::testSqLiteDriverRequirements()
{
    const char DriverName[] = "QSQLITE";
    QVERIFY( QSqlDatabase::isDriverAvailable( DriverName ) );
    QSqlDatabase db = QSqlDatabase::addDatabase( DriverName, "test-sqlite.charm.kdab.com" );
    QSqlDriver* driver  = db.driver();
    QVERIFY( driver->hasFeature( QSqlDriver::Transactions ) );
}

	QString formatSqlValue(QVariant value) {
		QSqlDatabase db = QSqlDatabase::database();
		QSqlDriver *driver = db.driver();

		QSqlField field("value", value.type());
		field.setValue(value);

		return driver->formatValue(field, true);
	}

	void deleteRecord(QString table, QSqlRecord record) {
		QSqlDriver *driver = QSqlDatabase::database().driver();
		QString delete_statement = driver->sqlStatement(QSqlDriver::DeleteStatement, table, QSqlRecord(), false);

		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, table, record, false);
		QSqlQuery query(QString("%1 %2").arg(delete_statement, where_statement));
		if (query.lastError().isValid())
			throw new SqlQueryError(query);
	}

bool receiptsEngine::insertIntoAccount(const QHash<int,QVariant> &hashValues, const QString &userUuid)
{
    // fetch all the account model
    /*while (m_mpmodel->canFetchMore(QModelIndex())) {
        if (WarnDebugMessage)
              qDebug() << __FILE__ << QString::number(__LINE__)<< " while ";
        m_mpmodel->QAbstractItemModel::fetchMore(QModelIndex());
    }*/

    QSqlDatabase db = QSqlDatabase::database(Constants::DB_ACCOUNTANCY);
    QSqlDriver *driver = db.driver();
    if (driver->hasFeature(QSqlDriver::QuerySize)) {
        if (WarnDebugMessage)
              qDebug() << __FILE__ << QString::number(__LINE__) << "driver has feature";
    }
    QString filterUser = QString("%1='%2'").arg("USER_UID",userUuid);
    m_mpmodel->AccountModel::setFilter(filterUser);
    int rowBefore = m_mpmodel->AccountModel::rowCount(QModelIndex());
    if (WarnDebugMessage)
              qDebug() << __FILE__ << QString::number(__LINE__) << " rowBefore = " << QString::number(rowBefore);
    if (m_mpmodel->insertRows(rowBefore,1,QModelIndex()))
    {
          qWarning() << __FILE__ << QString::number(__LINE__) << "Row inserted !" ;
        }
    bool ret = true;
    QVariant data;

    for(int i = 1 ; i < ACCOUNT_MaxParam ; i ++){
         data = hashValues.value(i);
         if (WarnDebugMessage)
              qDebug() << __FILE__ << QString::number(__LINE__) << " data + i =" << data.toString()+" "+QString::number(i);
         if (i == ACCOUNT_PATIENT_NAME)
         {
            QString dataString = data.toString();
            dataString.replace("'","''");
            data = QVariant(dataString);
             }
             if (WarnDebugMessage)
             qDebug() << __FILE__ << QString::number(__LINE__) << " data after =" << data.toString()+" "+QString::number(i);
         if (!m_mpmodel-> setData(m_mpmodel->index(rowBefore,i), data ,Qt::EditRole))
            {
                qWarning() << __FILE__ << QString::number(__LINE__) << " model account error = "
                                                                    << m_mpmodel->lastError().text() ;
                }
        }
        if (!m_mpmodel->AccountModel::submit())
        {
              qWarning() << __FILE__ << QString::number(__LINE__) << " submit error = "
                                     << m_mpmodel->lastError().text() ;
              ret = false;
            }

    return ret;
}

TableInfoModel* MigraineMainWindow::buildTreeModel(QSqlDatabase db)
{
	QList<TableInfo*> data;
	QSqlDriver *driver = db.driver();
	QStringList tables = db.tables(QSql::Tables);
	
	for(int i = 0; i < tables.count(); i++)
	{
		data << new TableInfo(tables.at(i), driver->record(tables.at(i)));
	}
	
    return new TableInfoModel(data);
}

DatabaseCache::DatabaseCache(const Ice::CommunicatorPtr& communicator, 
                             const string& type,
                             const string& name,
                             const string& host,
                             int port,
                             const string& user,
                             const string& password,
                             bool requiresBlob)
{
    Ice::PropertiesPtr properties = communicator->getProperties();
    
    //
    // File lock to prevent multiple process open the same db env.
    //
    if(type == "QSQLITE")
    {
        _fileLock = new IceUtilInternal::FileLock(name + ".lock");
    }

    _connection = QSqlDatabase::addDatabase(type.c_str(), IceUtil::generateUUID().c_str());
    _connection.setDatabaseName(name.c_str());
    _connection.setHostName(host.c_str());
    if(port != 0)
    {
        _connection.setPort(port);
    }
    _connection.setUserName(user.c_str());
    _connection.setPassword(password.c_str());

    DatabaseConnectionPtr connection = DatabaseConnectionPtr::dynamicCast(getConnection());
    QSqlDriver* driver = connection->sqlConnection().driver();
    if(!driver->hasFeature(QSqlDriver::Transactions))
    {
        throw Ice::InitializationException(__FILE__, __LINE__, "SQL database driver requires transaction support");
    }
    if(!driver->hasFeature(QSqlDriver::Unicode))
    {
        throw Ice::InitializationException(__FILE__, __LINE__, "SQL database driver requires unicode support");
    }
    if(requiresBlob && connection->sqlConnection().driverName() != "QODBC" && !driver->hasFeature(QSqlDriver::BLOB))
    {
        throw Ice::InitializationException(__FILE__, __LINE__, "SQL database driver requires blob support");
    }
}

	QList<QSqlRecord> selectQuery(QString table, QSqlRecord values, QSqlRecord where) {
		QList<QSqlRecord> records;

		QSqlDriver *driver = QSqlDatabase::database().driver();


		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, table, where, false);
		QString select_statement = driver->sqlStatement(QSqlDriver::SelectStatement, table, values, false);

		QSqlQuery query(QString("%1 %2").arg(select_statement, where_statement));

		if (query.lastError().isValid())
			throw new SqlQueryError(query);

		while (query.next())
			records.append(query.record());

		return records;
	}

	QSqlDatabase Database::add(QString p_path, QString database_name) {
		setPath(p_path);
		m_database_name = database_name;

		if (path().isEmpty())
			throw new NoDatabaseNameGiven;

		QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

		// Check if driver has all required features
		QSqlDriver *driver = db.driver();
		if (not driver->hasFeature(QSqlDriver::LastInsertId)) {
			throw new RequiredFeatuersMissing;
		}

		db.setDatabaseName(path());

		return db;
	}

	QSqlRecord findOne(QString table, QSqlRecord values, QSqlRecord where) {

		QSqlDriver *driver = QSqlDatabase::database().driver();

		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, table, where, false);
		QString select_statement = driver->sqlStatement(QSqlDriver::SelectStatement, table, values, false);

		QSqlQuery query(QString("%1 %2").arg(select_statement, where_statement));

		qDebug() << "Query:" << query.lastQuery();

		if (query.lastError().isValid())
			throw new SqlQueryError(query);

		if (query.next())
			return query.record();
		else
			return QSqlRecord();
	}

	QList<RelationalObjectRef> findMany(RelationalObjectRef type, QSqlRecord values, QSqlRecord where) {
		QList<RelationalObjectRef> results;

		QSqlDriver *driver = QSqlDatabase::database().driver();

		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, type.table(), where, false);
		QString select_statement = driver->sqlStatement(QSqlDriver::SelectStatement, type.table(), values, false);

		QSqlQuery query(QString("%1 %2").arg(select_statement, where_statement));

		if (query.lastError().isValid())
			throw new SqlQueryError(query);


		while (query.next()) {
			QSqlRecord record = query.record();
			results << RelationalObjectRef(type.table(),type.fieldName(), record.field("id").value().toInt());
		}

		return results;
	}

/**
  * The constructor creates the db and the tables if required. There's a single instance of db for all
  * the ServerDatabase instances.
  */
ServerDatabase::ServerDatabase() {
#ifdef _VERBOSE_DATABASE
    qDebug() << "Creating a new instance of the db connector: " << m_dbref;
#endif
    if (!m_dbref++) {
        // manually load the driver (for some reason I couldn't figure out how to have it loaded automagically)
        QPluginLoader loader("libqsqlmysql.so");
        if (loader.load()) {
#ifdef _VERBOSE_DATABASE
            qDebug() << "Loaded mysql drivers plugin";
#endif
        } else {
            qDebug() << QObject::tr("Failed to load mysql drivers plugin: ") + loader.errorString();
            return;
        }

        QSqlDriverPlugin *sqlPlugin  = qobject_cast<QSqlDriverPlugin *>(loader.instance());
#ifdef _VERBOSE_DATABASE
        qDebug() << "Available sql drivers: " << sqlPlugin->keys();
#endif
        QSqlDriver *sqlDriver = sqlPlugin->create(DB_TYPE);
        if (!sqlDriver) {
            qDebug() << QObject::tr("Failed to instantiate mysql driver");
            return;
        }

        sqlDriver->open(DB_NAME, DB_USR, DB_PWD, DB_HOST);
        if (sqlDriver->isOpenError()) {
            qDebug() << QObject::tr("Failed to connect to (") + DB_TYPE + QObject::tr(") DB ") + DB_NAME + QObject::tr(" on host ") + DB_HOST + QObject::tr(" with usr/pwd '") + DB_USR + "/" + DB_PWD + "'";
            qDebug() << QObject::tr("ERROR: ") + sqlDriver->lastError().text();
            return;
        }
        m_db = QSqlDatabase::addDatabase(sqlDriver);

        // create tables if non existing
        createTables();
    }
}

void SqlTransactionTests::testMySqlTransactionRollback()
{
    MySqlStorage storage = prepareMySqlStorage();
    QVERIFY( storage.database().open() );

    QSqlDriver* driver  = storage.database().driver();
    QVERIFY( driver->hasFeature( QSqlDriver::Transactions ) );

    QList<Task> tasksBefore = storage.getAllTasks();
    QVERIFY( ! tasksBefore.isEmpty() );
    Task first = tasksBefore.first();
    // test a simple transaction that is completed and committed:
    {
        SqlRaiiTransactor transactor( storage.database() );
        QSqlQuery query( storage.database() );
        query.prepare("DELETE from Tasks where id=:id");
        query.bindValue( "id", first.id() );
        QVERIFY( storage.runQuery( query ) );
    } // this transaction was NOT commited
    QList<Task> tasksAfter = storage.getAllTasks();
    QVERIFY( ! tasksAfter.isEmpty() );
    QVERIFY( tasksBefore == tasksAfter );
}