Imported Upstream version 3.1.0upstream/3.1.0

1 files changed, 275 insertions, 0 deletions

diff --git a/SQLiteStudio3/coreSQLiteStudio/querygenerator.cpp b/SQLiteStudio3/coreSQLiteStudio/querygenerator.cpp
new file mode 100644
index 0000000..4e2d9b6
--- /dev/null
+++ b/SQLiteStudio3/coreSQLiteStudio/querygenerator.cpp
@@ -0,0 +1,275 @@
+#include "querygenerator.h"
+#include "common/global.h"
+#include "common/utils_sql.h"
+#include "db/db.h"
+
+QueryGenerator::QueryGenerator()
+{
+
+}
+
+QString QueryGenerator::generateSelectFromTable(Db* db, const QString& table, const StrHash<QVariantList> values)
+{
+    return generateSelectFromTable(db, QString(), table, values);
+}
+
+QString QueryGenerator::generateSelectFromTable(Db* db, const QString& database, const QString& table, const StrHash<QVariantList> values)
+{
+    SchemaResolver resolver(db);
+    QStringList columns = resolver.getTableColumns(database, table);
+    return generateSelectFromTableOrView(db, database, table, columns, values);
+}
+
+QString QueryGenerator::generateInsertToTable(Db* db, const QString& table, const StrHash<QVariantList> values)
+{
+    return generateInsertToTable(db, QString(), table, values);
+}
+
+QString QueryGenerator::generateInsertToTable(Db* db, const QString& database, const QString& table, StrHash<QVariantList> values)
+{
+    static_qstring(tpl, "INSERT INTO %1 (%2) VALUES %3");
+    static_qstring(rowTpl, "(%1)");
+
+    Dialect dialect = db->getDialect();
+    QString target = toFullObjectName(database, table, dialect);
+
+    // Get all table's columns
+    SchemaResolver resolver(db);
+    QStringList tableCols = resolver.getTableColumns(database, table);
+
+    // If no values were given, then column names will serve as values for insertion
+    if (values.isEmpty())
+    {
+        QStringList valueList = wrapStrings(tableCols);
+        QList<QString> wrappedCols = wrapObjNamesIfNeeded(tableCols, dialect);
+        return tpl.arg(target, wrappedCols.join(", "), rowTpl.arg(valueList.join(", ")));
+    }
+
+    // Values were given. Sort given columns in order they are defined in table
+    QStringList valueCols = values.keys();
+    sortWithReferenceList(valueCols, tableCols);
+
+    // Group values into rows
+    QStringList valueSets = toValueSets(valueCols, values, dialect);
+    QString valueStr = rowTpl.arg(valueSets.join("), ("));
+
+    // Wrap given column names
+    QList<QString> wrappedCols = wrapObjNamesIfNeeded(valueCols, dialect);
+
+    return tpl.arg(target, wrappedCols.join(", "), valueStr);
+}
+
+QString QueryGenerator::generateUpdateOfTable(Db* db, const QString& table, const StrHash<QVariantList> values)
+{
+    return generateUpdateOfTable(db, QString(), table, values);
+}
+
+QString QueryGenerator::generateUpdateOfTable(Db* db, const QString& database, const QString& table, StrHash<QVariantList> values)
+{
+    static_qstring(tpl, "UPDATE %1 SET %2%3");
+    static_qstring(tplWithWhere, "UPDATE %1 SET %2 WHERE %3");
+    static_qstring(updateColTpl, "%1 = %2");
+
+    Dialect dialect = db->getDialect();
+    QString target = toFullObjectName(database, table, dialect);
+
+    // Get all columns of the table
+    SchemaResolver resolver(db);
+    QStringList tableCols = resolver.getTableColumns(database, table);
+
+    // Create list of "column = 'column'"
+    QStringList commonUpdateCols;
+    for (const QString& col : tableCols)
+        commonUpdateCols << updateColTpl.arg(wrapObjIfNeeded(col, dialect), wrapString(col));
+
+    // Put it to comma spearated string
+    QString commonColumnStr = commonUpdateCols.join(", ");
+
+    // If no values were given, we simply use column names as values everywhere
+    if (values.isEmpty())
+        return tplWithWhere.arg(target, commonColumnStr, commonColumnStr);
+
+    // If values were given, then they will be used in WHERE clause
+    QStringList valueCols = values.keys();
+    sortWithReferenceList(valueCols, tableCols);
+
+    // Conditions for WHERE clause
+    QString conditionStr = valuesToConditionStr(values, dialect);
+
+    return tpl.arg(target, commonColumnStr, conditionStr);
+}
+
+QString QueryGenerator::generateDeleteFromTable(Db* db, const QString& table, const StrHash<QVariantList> values)
+{
+    return generateDeleteFromTable(db, QString(), table, values);
+}
+
+QString QueryGenerator::generateDeleteFromTable(Db* db, const QString& database, const QString& table, StrHash<QVariantList> values)
+{
+    static_qstring(tpl, "DELETE FROM %1%2");
+    static_qstring(tplWithWhere, "DELETE FROM %1 WHERE %2");
+    static_qstring(conditionColTpl, "%1 = %2");
+
+    Dialect dialect = db->getDialect();
+    QString target = toFullObjectName(database, table, dialect);
+
+    // Get all columns of the table
+    SchemaResolver resolver(db);
+    QStringList tableCols = resolver.getTableColumns(database, table);
+
+    // If no values were given, we simply use column names as values everywhere
+    if (values.isEmpty())
+    {
+        // Create list of "column = 'column'"
+        QStringList conditionCols;
+        for (const QString& col : tableCols)
+            conditionCols << conditionColTpl.arg(wrapObjIfNeeded(col, dialect), wrapString(col));
+
+        // Put it to comma spearated string
+        QString conditionStr = conditionCols.join(", ");
+
+        return tplWithWhere.arg(target, conditionStr);
+    }
+
+    // If values were given, then they will be used in WHERE clause
+    QStringList valueCols = values.keys();
+    sortWithReferenceList(valueCols, tableCols);
+
+    // Conditions for WHERE clause
+    QString conditionStr = valuesToConditionStr(values, dialect);
+
+    return tpl.arg(target, conditionStr);
+}
+
+QString QueryGenerator::generateSelectFromView(Db* db, const QString& view, const StrHash<QVariantList> values)
+{
+    return generateSelectFromView(db, QString(), view, values);
+}
+
+QString QueryGenerator::generateSelectFromView(Db* db, const QString& database, const QString& view, const StrHash<QVariantList> values)
+{
+    SchemaResolver resolver(db);
+    QStringList columns = resolver.getViewColumns(database, view);
+    return generateSelectFromTableOrView(db, database, view, columns, values);
+}
+
+QString QueryGenerator::generateSelectFromSelect(Db* db, const QString& initialSelect, const StrHash<QVariantList> values, const BiStrHash& dbNameToAttach)
+{
+    static_qstring(tpl, "SELECT %1 FROM (%2)%3");
+
+    Dialect dialect = db->getDialect();
+
+    // Resolve all columns of the select
+    SelectResolver resolver(db, initialSelect, dbNameToAttach);
+    QList<SelectResolver::Column> columns = resolver.resolveColumnsFromFirstCore();
+
+    // Generate result columns
+    QStringList resCols;
+    for (const SelectResolver::Column& col : columns)
+        resCols << toResultColumnString(col, dialect);
+
+    // Generate conditions for WHERE clause
+    QString conditionStr = valuesToConditionStr(values, dialect);
+
+    return tpl.arg(resCols.join(", "), initialSelect, conditionStr);
+}
+
+QString QueryGenerator::generateSelectFromTableOrView(Db* db, const QString& database, const QString& tableOrView, const QStringList& columns, const StrHash<QVariantList> values)
+{
+    static_qstring(tpl, "SELECT %1 FROM %2%3");
+
+    Dialect dialect = db->getDialect();
+
+    QString target = toFullObjectName(database, tableOrView, dialect);
+    QString conditionStr = valuesToConditionStr(values, dialect);
+
+    return tpl.arg(columns.join(", "), target, conditionStr);
+}
+
+QString QueryGenerator::getAlias(const QString& name, QSet<QString>& usedAliases)
+{
+    static_qstring(tpl, "%2%1");
+
+    QString letter;
+    if (name.length() == 0)
+        letter = "t";
+    else
+        letter = name[0];
+
+    QString alias = letter + "1";
+    int i = 2;
+    while (usedAliases.contains(alias))
+        alias = tpl.arg(i++).arg(letter);
+
+    usedAliases << alias;
+    return alias;
+}
+
+QStringList QueryGenerator::valuesToConditionList(const StrHash<QVariantList>& values, Dialect dialect)
+{
+    static_qstring(conditionTpl1, "%1 = %2");
+    static_qstring(conditionTpl2, "%1 IN (%2)");
+
+    QStringList conditions;
+    QStringList conditionValues;
+    for (const QString& col : values.keys())
+    {
+        conditionValues = valueListToSqlList(values[col], dialect);
+        conditionValues.removeDuplicates();
+        if (conditionValues.size() == 1)
+            conditions << conditionTpl1.arg(wrapObjIfNeeded(col, dialect), conditionValues.first());
+        else
+            conditions << conditionTpl2.arg(wrapObjIfNeeded(col, dialect), conditionValues.join(", "));
+    }
+    return conditions;
+}
+
+QString QueryGenerator::valuesToConditionStr(const StrHash<QVariantList>& values, Dialect dialect)
+{
+    static_qstring(condTpl, " WHERE %1");
+
+    QStringList conditions = valuesToConditionList(values, dialect);
+    QString conditionStr = "";
+    if (conditions.size() > 0)
+        conditionStr = condTpl.arg(conditions.join(" AND "));
+
+    return conditionStr;
+}
+
+QString QueryGenerator::toResultColumnString(const SelectResolver::Column& column, Dialect dialect)
+{
+    return wrapObjIfNeeded(column.displayName, dialect);
+}
+
+QString QueryGenerator::toFullObjectName(const QString& database, const QString& object, Dialect dialect)
+{
+    static_qstring(tpl, "%1%2");
+
+    QString dbName = "";
+    if (!database.isEmpty() && dbName.toLower() != "main")
+        dbName = wrapObjIfNeeded(database, dialect);
+
+    if (!dbName.isEmpty())
+        dbName.append(".");
+
+    return tpl.arg(dbName, wrapObjIfNeeded(object, dialect));
+}
+
+QStringList QueryGenerator::toValueSets(const QStringList& columns, const StrHash<QVariantList> values, Dialect dialect)
+{
+    QStringList rows;
+    QVariantList rowValues;
+    QStringList valueList;
+
+    for (int total = values.values().first().size(), i = 0; i < total; i++)
+    {
+        rowValues.clear();
+        for (const QString& col : columns)
+            rowValues << values[col][i];
+
+        valueList = valueListToSqlList(rowValues, dialect);
+        rows << valueList.join(", ");
+    }
+
+    return rows;
+}