bool RelationFetch::parseStream()
{
Protocol::FetchRelationsCommand cmd(m_command);
SelectQueryBuilder<Relation> relationQuery;
if (cmd.side() > 0) {
Query::Condition c;
c.setSubQueryMode(Query::Or);
c.addValueCondition(Relation::leftIdFullColumnName(), Query::Equals, cmd.side());
c.addValueCondition(Relation::rightIdFullColumnName(), Query::Equals, cmd.side());
relationQuery.addCondition(c);
} else {
if (cmd.left() > 0) {
relationQuery.addValueCondition(Relation::leftIdFullColumnName(), Query::Equals, cmd.left());
}
if (cmd.right() > 0) {
relationQuery.addValueCondition(Relation::rightIdFullColumnName(), Query::Equals, cmd.right());
}
}
if (!cmd.types().isEmpty()) {
relationQuery.addJoin(QueryBuilder::InnerJoin, RelationType::tableName(), Relation::typeIdFullColumnName(), RelationType::idFullColumnName());
QStringList types;
types.reserve(cmd.types().size());
Q_FOREACH (const QByteArray &type, cmd.types()) {
types << QString::fromUtf8(type);
}
relationQuery.addValueCondition(RelationType::nameFullColumnName(), Query::In, types);
}
Query::Condition PartTypeHelper::conditionFromFqName(const QString &fqName)
{
const QPair<QString, QString> p = parseFqName(fqName);
Query::Condition c;
c.setSubQueryMode(Query::And);
c.addValueCondition(PartType::nsFullColumnName(), Query::Equals, p.first);
c.addValueCondition(PartType::nameFullColumnName(), Query::Equals, p.second);
return c;
}
Q_FOREACH (const Flag &flag, flags) {
QSet<PimItem::Id> existing;
if (checkIfExists) {
QueryBuilder qb(PimItemFlagRelation::tableName(), QueryBuilder::Select);
Query::Condition cond;
cond.addValueCondition(PimItemFlagRelation::rightColumn(), Query::Equals, flag.id());
cond.addValueCondition(PimItemFlagRelation::leftColumn(), Query::In, itemsIds);
qb.addColumn(PimItemFlagRelation::leftColumn());
qb.addCondition(cond);
if (!qb.exec()) {
qCDebug(AKONADISERVER_LOG) << "Failed to execute query:" << qb.query().lastError();
return false;
}
QSqlQuery query = qb.query();
if (query.driver()->hasFeature(QSqlDriver::QuerySize)) {
//The query size feature is not suppoerted by the sqllite driver
if (query.size() == items.count()) {
continue;
}
setBoolPtr(flagsChanged, true);
}
while (query.next()) {
existing << query.value(0).value<PimItem::Id>();
}
if (!query.driver()->hasFeature(QSqlDriver::QuerySize)) {
if (existing.size() != items.count()) {
setBoolPtr(flagsChanged, true);
}
}
}
if (!doAppendItemsFlag(items, flag, existing, col, silent)) {
return false;
}
}
bool DataStore::setItemsFlags(const PimItem::List &items, const QVector<Flag> &flags,
bool *flagsChanged, const Collection &col_, bool silent)
{
QSet<QByteArray> removedFlags;
QSet<QByteArray> addedFlags;
QVariantList insIds;
QVariantList insFlags;
Query::Condition delConds(Query::Or);
Collection col = col_;
setBoolPtr(flagsChanged, false);
for (const PimItem &item : items) {
const Flag::List itemFlags = item.flags();
Q_FOREACH (const Flag &flag, itemFlags) {
if (!flags.contains(flag)) {
removedFlags << flag.name().toLatin1();
Query::Condition cond;
cond.addValueCondition(PimItemFlagRelation::leftFullColumnName(), Query::Equals, item.id());
cond.addValueCondition(PimItemFlagRelation::rightFullColumnName(), Query::Equals, flag.id());
delConds.addCondition(cond);
}
}
Q_FOREACH (const Flag &flag, flags) {
if (!itemFlags.contains(flag)) {
addedFlags << flag.name().toLatin1();
insIds << item.id();
insFlags << flag.id();
}
}
if (col.id() == -1) {
col.setId(item.collectionId());
} else if (col.id() != item.collectionId()) {
col.setId(-2);
}
}
if (!removedFlags.empty()) {
QueryBuilder qb(PimItemFlagRelation::tableName(), QueryBuilder::Delete);
qb.addCondition(delConds);
if (!qb.exec()) {
return false;
}
}
if (!addedFlags.empty()) {
QueryBuilder qb2(PimItemFlagRelation::tableName(), QueryBuilder::Insert);
qb2.setColumnValue(PimItemFlagRelation::leftColumn(), insIds);
qb2.setColumnValue(PimItemFlagRelation::rightColumn(), insFlags);
qb2.setIdentificationColumn(QString());
if (!qb2.exec()) {
return false;
}
}
if (!silent && (!addedFlags.isEmpty() || !removedFlags.isEmpty())) {
mNotificationCollector->itemsFlagsChanged(items, addedFlags, removedFlags, col);
}
setBoolPtr(flagsChanged, (addedFlags != removedFlags));
return true;
}
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"));
mBuilders << qb;
QTest::newRow("insert single column") << mBuilders.count() << QString("INSERT INTO table (col1) VALUES (:0)") << bindVals;
qb = QueryBuilder("table", QueryBuilder::Insert);
qb.setColumnValue("col1", QString("bla"));
qb.setColumnValue("col2", 5);
bindVals << 5;
mBuilders << qb;
QTest::newRow("insert multi column") << mBuilders.count() << QString("INSERT INTO table (col1, col2) VALUES (:0, :1)") << bindVals;
qb = QueryBuilder("table", QueryBuilder::Insert);
qb.setDatabaseType(DbType::PostgreSQL);
qb.setColumnValue("col1", QString("bla"));
qb.setColumnValue("col2", 5);
mBuilders << qb;
QTest::newRow("insert multi column PSQL") << mBuilders.count() << QString("INSERT INTO table (col1, col2) VALUES (:0, :1) RETURNING id") << bindVals;
qb.setIdentificationColumn(QString());
mBuilders << qb;
QTest::newRow("insert multi column PSQL without id") << mBuilders.count() << QString("INSERT INTO table (col1, col2) VALUES (:0, :1)") << bindVals;
// test GROUP BY foo
bindVals.clear();
qb = QueryBuilder("table", QueryBuilder::Select);
qb.addColumn("foo");
qb.addGroupColumn("id1");
mBuilders << qb;
QTest::newRow("select group by single column") << mBuilders.count() << QString("SELECT foo FROM table GROUP BY id1") << bindVals;
// test GROUP BY foo, bar
qb.addGroupColumn("id2");
mBuilders << qb;
QTest::newRow("select group by two columns") << mBuilders.count() << QString("SELECT foo FROM table GROUP BY id1, id2") << bindVals;
// test: HAVING .addValueCondition()
qb.addValueCondition("bar", Equals, 1, QueryBuilder::HavingCondition);
mBuilders << qb;
bindVals << 1;
QTest::newRow("select with having valueCond") << mBuilders.count() << QString("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 )") << bindVals;
// test: HAVING .addColumnCondition()
qb.addColumnCondition("asdf", Equals, "yxcv", QueryBuilder::HavingCondition);
mBuilders << qb;
QTest::newRow("select with having columnCond") << mBuilders.count() << QString("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv )") << bindVals;
// test: HAVING .addCondition()
qb.addCondition(subCon, QueryBuilder::HavingCondition);
mBuilders << qb;
QTest::newRow("select with having condition") << mBuilders.count() << QString("SELECT foo FROM table GROUP BY id1, id2 HAVING ( bar = :0 AND asdf = yxcv AND ( col1 > col2 AND col1 <> NULL ) )") << bindVals;
// test: HAVING and WHERE
qb.addValueCondition("bla", Equals, 2, QueryBuilder::WhereCondition);
mBuilders << qb;
bindVals.clear();
bindVals << 2 << 1;
QTest::newRow("select with having and where") << mBuilders.count() << QString("SELECT foo FROM table WHERE ( bla = :0 ) GROUP BY id1, id2 HAVING ( bar = :1 AND asdf = yxcv AND ( col1 > col2 AND col1 <> NULL ) )") << bindVals;
{
/// SELECT with JOINS
QueryBuilder qbTpl = QueryBuilder("table1", QueryBuilder::Select);
qbTpl.setDatabaseType(DbType::MySQL);
qbTpl.addColumn("col");
bindVals.clear();
QueryBuilder qb = qbTpl;
qb.addJoin(QueryBuilder::InnerJoin, "table2", "table2.t1_id", "table1.id");
qb.addJoin(QueryBuilder::LeftJoin, "table3", "table1.id", "table3.t1_id");
mBuilders << qb;
QTest::newRow("select left join and inner join (different tables)") << mBuilders.count()
<< QString("SELECT col FROM table1 INNER JOIN table2 ON ( table2.t1_id = table1.id ) LEFT JOIN table3 ON ( table1.id = table3.t1_id )") << bindVals;
qb = qbTpl;
qb.addJoin(QueryBuilder::InnerJoin, "table2", "table2.t1_id", "table1.id");
qb.addJoin(QueryBuilder::LeftJoin, "table2", "table2.t1_id", "table1.id");
mBuilders << qb;
// join-condition too verbose but should not have any impact on speed
QTest::newRow("select left join and inner join (same table)") << mBuilders.count()
<< QString("SELECT col FROM table1 INNER JOIN table2 ON ( table2.t1_id = table1.id AND ( table2.t1_id = table1.id ) )") << bindVals;
// order of joins in the query should be the same as we add the joins in code
qb = qbTpl;
qb.addJoin(QueryBuilder::InnerJoin, "b_table", "b_table.t1_id", "table1.id");
qb.addJoin(QueryBuilder::InnerJoin, "a_table", "a_table.b_id", "b_table.id");
mBuilders << qb;
QTest::newRow("select join order") << mBuilders.count()
<< QString("SELECT col FROM table1 INNER JOIN b_table ON ( b_table.t1_id = table1.id ) INNER JOIN a_table ON ( a_table.b_id = b_table.id )") << bindVals;
}
{
/// SELECT with CASE
QueryBuilder qbTpl = QueryBuilder("table1", QueryBuilder::Select);
qbTpl.setDatabaseType(DbType::MySQL);
QueryBuilder qb = qbTpl;
qb.addColumn("col");
qb.addColumn(Query::Case("col1", Query::Greater, 42, "1", "0"));
bindVals.clear();
bindVals << 42;
mBuilders << qb;
QTest::newRow("select case simple") << mBuilders.count()
<< QString("SELECT col, CASE WHEN ( col1 > :0 ) THEN 1 ELSE 0 END FROM table1") << bindVals;
qb = qbTpl;
qb.addAggregation("table1.col1", "sum");
qb.addAggregation("table1.col2", "count");
Query::Condition cond(Query::Or);
cond.addValueCondition("table3.col2", Query::Equals, "value1");
cond.addValueCondition("table3.col2", Query::Equals, "value2");
Query::Case caseStmt(cond, "1", "0");
qb.addAggregation(caseStmt, "sum");
qb.addJoin(QueryBuilder::LeftJoin, "table2", "table1.col3", "table2.col1");
qb.addJoin(QueryBuilder::LeftJoin, "table3", "table2.col2", "table3.col1");
bindVals.clear();
bindVals << QString("value1") << QString("value2");
mBuilders <<qb;
QTest::newRow("select case, aggregation and joins") << mBuilders.count()
<< QString("SELECT sum(table1.col1), count(table1.col2), sum(CASE WHEN ( table3.col2 = :0 OR table3.col2 = :1 ) THEN 1 ELSE 0 END) "
"FROM table1 "
"LEFT JOIN table2 ON ( table1.col3 = table2.col1 ) "
"LEFT JOIN table3 ON ( table2.col2 = table3.col1 )")
<< bindVals;
}
{
/// UPDATE with INNER JOIN
QueryBuilder qbTpl = QueryBuilder("table1", QueryBuilder::Update);
qbTpl.setColumnValue("col", 42);
qbTpl.addJoin(QueryBuilder::InnerJoin, "table2", "table2.t1_id", "table1.id");
qbTpl.addValueCondition("table2.answer", NotEquals, "foo");
bindVals.clear();
bindVals << QVariant(42) << QVariant("foo");
qb = qbTpl;
qb.setDatabaseType(DbType::MySQL);
mBuilders << qb;
QTest::newRow("update inner join MySQL") << mBuilders.count()
<< QString("UPDATE table1, table2 SET col = :0 WHERE ( table2.answer <> :1 AND ( table2.t1_id = table1.id ) )") << bindVals;
qb = qbTpl;
qb.setDatabaseType(DbType::PostgreSQL);
mBuilders << qb;
QTest::newRow("update inner join PSQL") << mBuilders.count()
<< QString("UPDATE table1 SET col = :0 FROM table2 WHERE ( table2.answer <> :1 AND ( table2.t1_id = table1.id ) )") << bindVals;
qb = qbTpl;
qb.setDatabaseType(DbType::Sqlite);
mBuilders << qb;
QTest::newRow("update inner join SQLite") << mBuilders.count()
<< QString("UPDATE table1 SET col = :0 WHERE ( ( SELECT table2.answer FROM table2 WHERE ( ( table2.t1_id = table1.id ) ) ) <> :1 )") << bindVals;
qb = qbTpl;
qb.setDatabaseType(DbType::Sqlite);
Query::Condition condition;
condition.addValueCondition("table2.col2", Query::Equals, 666);
condition.addValueCondition("table1.col3", Query::Equals, "text");
qb.addCondition(condition);
qb.addValueCondition("table1.id", Query::Equals, 10);
mBuilders << qb;
bindVals << 666 << "text" << 10;
QTest::newRow("update inner join SQLite with subcondition") << mBuilders.count()
<< QString("UPDATE table1 SET col = :0 WHERE ( ( SELECT table2.answer FROM table2 WHERE "
"( ( table2.t1_id = table1.id ) ) ) <> :1 AND "
"( ( SELECT table2.col2 FROM table2 WHERE ( ( table2.t1_id = table1.id ) ) ) = :2 AND table1.col3 = :3 ) AND "
"table1.id = :4 )") << bindVals;
}
}