C++ QueryBuilder类代码示例

/**
 * @brief Repository::updateByIdQB Constructs a query to update a row identified by its ID in the
 * child's Repository associated table.
 * @param id Id of the row to update.
 * @param cols Columns to update.
 */
QueryBuilder Repository::updateByIdQB(int id, QList<QString> cols) {
    QueryBuilder qb;
    qb.update(getTableName(), QStringList(cols));
    qb.where("id = " + QString::number(id));

    return qb;
}

    void findAction() 
    {
        if (path.size() == 0 || args.size() == 0) {
            usageAction();
            return;
        }
        
        QueryBuilder builder;
        Query * q = new Query();
        builder.setQuery(q);

        for (int i = 0; i < args.size(); i++) {
            
            bool parsed = builder.parse(args[i]);
            
            if (!parsed) {
                delete q;
                std::cerr << "Error de sintaxis" << std::endl;
                return;
            }
        }
        
        KDtree * tree = new KDtree(DIMENSIONS, new FileBlocks(path.c_str(), BLOCKSIZE));
        std::vector< Record * > result = tree->find(q);

        std::cout << "Se encontraron " << result.size() << " registros." << std::endl;
        
        for (int i = 0; i < result.size(); i++) {
            result[i]->dump();
            std::cout << std::endl;
        }
        
    };

QueryBuilder Repository::deleteByIdQb(int id) {
    QueryBuilder qb;
    qb.deleteFrom(getTableName())
     ->where("id = " + QString::number(id));

    return qb;
}

/**
 * @brief Repository::findByIdQB Constructs a query to find one row by its id from the child's
 * repository associated table.
 * @param id Id of the row to find.
 * @return QueryBuilder
 */
QueryBuilder Repository::findByIdQB(int id) {
    QueryBuilder qb;

    qb.select("*")
    ->from(getTableName())
    ->where("id = " + QString::number(id));

    return qb;
}

void Catalog::homePage()
{
    QueryBuilder builder;
    const auto query =  builder.catalog();
    auto replay = query.exec(m_manager);

    connect(replay, &QNetworkReply::finished,
            this, &Catalog::onRequestHanderl);
}

int Repository::insertIntoDefault(DBItem *item) {
    QueryBuilder qb;
    qb.insertIntoDefault(getTableName());
    qb.withAsSelect(qb, "id");
    QSqlQuery query = qb.getQuery();

    int id = Database::instance()->pushWithId(query);
    item->setId(id);

    return id;
}

/**
 * @brief Repository::isEmpty Verifies if the table is empty.
 * @return Return whether the table associated with the inherithing Repository is empty or not.
 */
bool Repository::isEmpty() {
    QueryBuilder qb;
    qb.select("count(*) AS rows")
      ->from(getTableName());

    DBItem dbItem = Database::instance()->pullFirst(qb.getQuery());

    int rowCount = dbItem.getHashMap().value("rows").toInt();

    return (rowCount == 0);
}

/**
 * @brief Repository::insert Inserts a row in the database with the query given by the QueryBuilder
 * (qb) and the given bind values (pBindValues). Retrieves the id of the inserted row and sets it on
 * the DBItem.
 * @param item Item associated with the inserted row in the database which will be given an id once
 * the insertion is successful.
 * @param qb QueryBuilder holding the query which will be executed.
 * @param pBindValues Holds the values to be bound with the request.
 * @param db Database
 * @return Id of the inserted row.
 */
int Repository::insert(DBItem *item, QueryBuilder qb, QHash<QString, QVariant> pBindValues)
{
    qb.withAsSelect(qb, "id");
    QSqlQuery query = qb.getPreparedQuery();
    bindValues(&query, pBindValues);

    int id = Database::instance()->pushPreparedWithId(query);
    item->setId(id);

    return id;
}

void PlayFilesModel::setFilm(const QVariant& films)
{
    if(films.canConvert<Film>())
    {
        const auto film = films.value<Film>();
        QueryBuilder builder;
        const auto query =  builder.filles(film.id());
        auto replay = query.exec(m_manager);

        connect(replay, &QNetworkReply::finished,
                this, &PlayFilesModel::onRequestHanderl);
    }
}

void K3bExporter::exportAlbum( const QString &artist, const QString &album, int openmode )
{
    QString albumId = QString::number( CollectionDB::instance()->albumID( album, false, false, true ) );
    QString artistId;      
    if( !artist.isNull() )
        artistId = QString::number( CollectionDB::instance()->artistID( artist, false, false, true ) );

    QueryBuilder qb;
    qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );
    qb.addMatch( QueryBuilder::tabSong, QueryBuilder::valAlbumID, albumId );
    if( !artist.isNull() )
        qb.addMatch( QueryBuilder::tabSong, QueryBuilder::valArtistID, artistId );
    qb.sortBy( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
    qb.sortBy( QueryBuilder::tabSong, QueryBuilder::valTrack );

    QStringList values( qb.run() );

    if( !values.isEmpty() )
    {
        KURL::List urls;

        foreach( values )
            urls << KURL( *it );

        exportTracks( urls, openmode );
    }
}

void QueryBuilderTest::testQueryBuilder_data()
{
    qRegisterMetaType<QVector<QVariant> >();
    mBuilders.clear();
    QTest::addColumn<int>("qbId");
    QTest::addColumn<QString>("sql");
    QTest::addColumn<QVector<QVariant> >("bindValues");

    QueryBuilder qb("table", QueryBuilder::Select);
    qb.addColumn("col1");
    mBuilders << qb;
    QTest::newRow("simple select") << mBuilders.count() << QString("SELECT col1 FROM table") << QVector<QVariant>();

    qb.addColumn("col2");
    mBuilders << qb;
    QTest::newRow("simple select 2") << mBuilders.count() << QString("SELECT col1, col2 FROM table") << QVector<QVariant>();

    qb.addValueCondition("col1", Query::Equals, QVariant(5));
    QVector<QVariant> bindVals;
    bindVals << QVariant(5);
    mBuilders << qb;
    QTest::newRow("single where") << mBuilders.count() << QString("SELECT col1, col2 FROM table WHERE ( col1 = :0 )") << bindVals;

    qb.addColumnCondition("col1", Query::LessOrEqual, "col2");
    mBuilders << qb;
    QTest::newRow("flat where") << mBuilders.count() << QString("SELECT col1, col2 FROM table WHERE ( col1 = :0 AND col1 <= col2 )") << bindVals;

    qb.setSubQueryMode(Query::Or);
    mBuilders << qb;
    QTest::newRow("flat where 2") << mBuilders.count() << QString("SELECT col1, col2 FROM table WHERE ( col1 = :0 OR col1 <= col2 )") << bindVals;

    Condition subCon;
    subCon.addColumnCondition("col1", Query::Greater, "col2");
    subCon.addValueCondition("col1", Query::NotEquals, QVariant());
    qb.addCondition(subCon);
    mBuilders << qb;
    QTest::newRow("hierarchical where") << mBuilders.count() << QString("SELECT col1, col2 FROM table WHERE ( col1 = :0 OR col1 <= col2 OR ( col1 > col2 AND col1 <> NULL ) )") << bindVals;

    qb = QueryBuilder("table");
    qb.addAggregation("col1", "count");
    mBuilders << qb;
    QTest::newRow("single aggregation") << mBuilders.count() << QString("SELECT count(col1) FROM table") << QVector<QVariant>();

    qb = QueryBuilder("table");
    qb.addColumn("col1");
    qb.addSortColumn("col1");
    mBuilders << qb;
    QTest::newRow("single order by") << mBuilders.count() << QString("SELECT col1 FROM table ORDER BY col1 ASC") << QVector<QVariant>();

    qb.addSortColumn("col2", Query::Descending);
    mBuilders << qb;
    QTest::newRow("multiple order by") << mBuilders.count() << QString("SELECT col1 FROM table ORDER BY col1 ASC, col2 DESC") << QVector<QVariant>();

    qb = QueryBuilder("table");
    qb.addColumn("col1");
    QStringList vals;
    vals << "a" << "b" << "c";
    qb.addValueCondition("col1", Query::In, vals);
    bindVals.clear();
    bindVals << QString("a") << QString("b") << QString("c");
    mBuilders << qb;
    QTest::newRow("where in") << mBuilders.count() << QString("SELECT col1 FROM table WHERE ( col1 IN ( :0, :1, :2 ) )") << bindVals;

    qb = QueryBuilder("table", QueryBuilder::Select);
    qb.setDatabaseType(DbType::MySQL);
    qb.addColumn("col1");
    qb.setLimit(1);
    mBuilders << qb;
    QTest::newRow("SELECT with LIMIT") << mBuilders.count() << QString("SELECT col1 FROM table LIMIT 1") << QVector<QVariant>();

    qb = QueryBuilder("table", QueryBuilder::Update);
    qb.setColumnValue("col1", QString("bla"));
    bindVals.clear();
    bindVals << QString("bla");
    mBuilders << qb;
    QTest::newRow("update") << mBuilders.count() << QString("UPDATE table SET col1 = :0") << bindVals;

    qb = QueryBuilder("table1", QueryBuilder::Update);
    qb.setDatabaseType(DbType::MySQL);
    qb.addJoin(QueryBuilder::InnerJoin, "table2", "table1.id", "table2.id");
    qb.addJoin(QueryBuilder::InnerJoin, "table3", "table1.id", "table3.id");
    qb.setColumnValue("col1", QString("bla"));
    bindVals.clear();
    bindVals << QString("bla");
    mBuilders << qb;
    QTest::newRow("update multi table MYSQL") << mBuilders.count() << QString("UPDATE table1, table2, table3 SET col1 = :0 WHERE ( ( table1.id = table2.id ) AND ( table1.id = table3.id ) )")
                                              << bindVals;

    qb = QueryBuilder("table1", QueryBuilder::Update);
    qb.setDatabaseType(DbType::PostgreSQL);
    qb.addJoin(QueryBuilder::InnerJoin, "table2", "table1.id", "table2.id");
    qb.addJoin(QueryBuilder::InnerJoin, "table3", "table1.id", "table3.id");
    qb.setColumnValue("col1", QString("bla"));
    mBuilders << qb;
    QTest::newRow("update multi table PSQL") << mBuilders.count() << QString("UPDATE table1 SET col1 = :0 FROM table2 JOIN table3 WHERE ( ( table1.id = table2.id ) AND ( table1.id = table3.id ) )")
                                             << bindVals;
    ///TODO: test for subquery in SQLite case

    qb = QueryBuilder("table", QueryBuilder::Insert);
    qb.setColumnValue("col1", QString("bla"));
//.........这里部分代码省略.........

    void reportAction()
    {
    	/*
    	1. Todos los trenes que tienen cierta falla.
		2. Todos los trenes que tienen cierto accidente.
		3. Todas las fallas de una formacion determinada.
		4. Todos los accidentes de una formacion determinada.
		5. Todas las fallas.
		6. Todas las formaciones.
		7. Todas las lineas.
		8. Todos los accidentes
		*/
		
		std::map< std::string, unsigned > available_reports;
		
		available_reports["trenesxfalla"] = 1;
		available_reports["trenesxaccidente"] = 2;
		available_reports["fallasxformacion"] = 3;
		available_reports["accidentesxformacion"] = 4;
		available_reports["fallas"] = 5;
		available_reports["formaciones"] = 6;
		available_reports["lineas"] = 7;
		available_reports["accidentes"] = 8;
		
		if (args.size() == 0) {
			std::cerr << "No se especificó el tipo de reporte" << std::endl;
			return;
		}
		
		if (available_reports.find(args[0]) == available_reports.end()) {
			std::cerr << "No se encontró el reporte especificado" << std::endl;
			return;
		}
		
		Query * q = new Query();
		QueryBuilder builder;
		builder.setQuery(q);
		std::vector< std::string > headers;
		std::vector< unsigned > fields;
		
		switch (available_reports[args[0]]) {
			case 1:
				if (args.size() < 2) {
					std::cerr << "No se especificó la falla" << std::endl;
					delete q;
					return;				
				}
				
				builder.parse("falla=" + args[1]);
				
				headers.push_back("Línea");
				headers.push_back("Formación");
				
				fields.push_back(LINEA);
				fields.push_back(FORMACION);
				
				break;
			case 2:
				if (args.size() < 2) {
					std::cerr << "No se especificó el accidente" << std::endl;
					delete q;
					return;				
				}
				
				builder.parse("accidente=" + args[1]);
				
				headers.push_back("Línea");
				headers.push_back("Formación");
				
				fields.push_back(LINEA);
				fields.push_back(FORMACION);
				
				break;

			case 3:
				if (args.size() < 2) {
					std::cerr << "No se especificó la línea ni la formación" << std::endl;
					delete q;
					return;				
				}
				
				if (args.size() < 3) {
					std::cerr << "No se especificó la formación" << std::endl;
					delete q;
					return;				
				}
				
				builder.parse("linea=" + args[1]);
				builder.parse("formacion=" + args[2]);
				
				headers.push_back("Línea");
				headers.push_back("Formación");
				headers.push_back("Falla");
				
				fields.push_back(LINEA);
				fields.push_back(FORMACION);
				fields.push_back(FALLA);
				
				break;

//.........这里部分代码省略.........

/**
 * @brief Repository::insertQB Constructs a query to insert a row in the child's Repository
 * associated table.
 * @param cols Columns for which values will be inserted.
 * @return QueryBuilder
 */
QueryBuilder Repository::insertQB(QList<QString> cols) {
    QueryBuilder qb;
    qb.insertInto(getTableName(), QStringList(cols));

    return qb;
}

/**
 * @brief Repository::findAllQB Constructs a query to retrieve all the rows from the child's
 * Repository associated table.
 * @return QueryBuilder
 */
QueryBuilder Repository::findAllQB() {
    QueryBuilder qb;
    qb.select("*")->from(getTableName());

    return qb;
}

bool Query::exec(const QueryBuilder &query_builder, bool throw_exc)
{
	return exec(query_builder.toString(), throw_exc);
}