diff options
Diffstat (limited to 'SQLiteStudio3/coreSQLiteStudio/selectresolver.cpp')
| -rw-r--r-- | SQLiteStudio3/coreSQLiteStudio/selectresolver.cpp | 296 |
1 files changed, 179 insertions, 117 deletions
diff --git a/SQLiteStudio3/coreSQLiteStudio/selectresolver.cpp b/SQLiteStudio3/coreSQLiteStudio/selectresolver.cpp index 3db029d..8a3efa8 100644 --- a/SQLiteStudio3/coreSQLiteStudio/selectresolver.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/selectresolver.cpp @@ -1,9 +1,7 @@ #include "selectresolver.h" #include "parser/token.h" -#include "parser/lexer.h" #include "parser/keywords.h" #include "schemaresolver.h" -#include "parser/ast/sqlitecreateview.h" #include "common/global.h" #include <QDebug> #include <QHash> @@ -55,8 +53,8 @@ QList<QList<SelectResolver::Column>> SelectResolver::resolve(SqliteSelect *selec { errors.clear(); extractCte(select); - QList<QList<SelectResolver::Column> > results; - for (SqliteSelect::Core* core : select->coreSelects) + QList<QList<SelectResolver::Column>> results; + for (SqliteSelect::Core*& core : select->coreSelects) { results << resolveCore(core); currentCoreResults.clear(); @@ -77,17 +75,39 @@ QList<QList<SelectResolver::Column> > SelectResolver::resolveAvailableColumns(Sq errors.clear(); extractCte(select); QList<QList<SelectResolver::Column> > results; - for (SqliteSelect::Core* core : select->coreSelects) + for (SqliteSelect::Core*& core : select->coreSelects) results << resolveAvailableCoreColumns(core); return results; } +QList<SelectResolver::Column> SelectResolver::resolveAvailableColumns(SqliteSelect::Core::JoinSource* joinSrc) +{ + errors.clear(); + return resolveJoinSource(joinSrc); +} + QSet<SelectResolver::Table> SelectResolver::resolveTables(SqliteSelect::Core *selectCore) { - QSet<Table> tables; extractCte(selectCore); - QList<Column> columns = resolveAvailableColumns(selectCore); + return resolveTablesFromCore(selectCore); +} + +QList<QSet<SelectResolver::Table>> SelectResolver::resolveTables(SqliteSelect *select) +{ + extractCte(select); + QList<QSet<Table>> results; + for (SqliteSelect::Core*& core : select->coreSelects) + results << resolveTablesFromCore(core); + + return results; +} + +QSet<SelectResolver::Table> SelectResolver::resolveTables(SqliteSelect::Core::JoinSource* joinSrc) +{ + errors.clear(); + QSet<Table> tables; + QList<Column> columns = resolveAvailableColumns(joinSrc); for (const Column& col : columns) { if (col.type != Column::Type::COLUMN) @@ -95,30 +115,21 @@ QSet<SelectResolver::Table> SelectResolver::resolveTables(SqliteSelect::Core *se tables << col.getTable(); } - return tables; } -QList<QSet<SelectResolver::Table> > SelectResolver::resolveTables(SqliteSelect *select) +QSet<SelectResolver::Table> SelectResolver::resolveTablesFromCore(SqliteSelect::Core *selectCore) { - QList<QSet<Table> > results; - extractCte(select); - QList<QList<Column> > columnLists = resolveAvailableColumns(select); - for (const QList<Column>& columns : columnLists) + QSet<Table> tables; + QList<Column> columns = resolveAvailableColumns(selectCore); + for (const Column& col : columns) { - QSet<Table> tables; - for (const Column& col : columns) - { - if (col.type != Column::Type::COLUMN) - continue; - - tables << col.getTable(); - } + if (col.type != Column::Type::COLUMN) + continue; - results << tables; + tables << col.getTable(); } - - return results; + return tables; } QList<SelectResolver::Column> SelectResolver::translateToColumns(SqliteSelect* select, const TokenList& columnTokens) @@ -148,25 +159,21 @@ const QStringList& SelectResolver::getErrors() const return errors; } +QList<SelectResolver::Column> SelectResolver::sqliteResolveColumns(const QString& query) +{ + return sqliteResolveColumns(db, query, dbNameToAttach); +} + QList<SelectResolver::Column> SelectResolver::resolveCore(SqliteSelect::Core* selectCore) { if (selectCore->from) currentCoreSourceColumns = resolveJoinSource(selectCore->from); - for (SqliteSelect::Core::ResultColumn* resCol : selectCore->resultColumns) + for (SqliteSelect::Core::ResultColumn*& resCol : selectCore->resultColumns) resolve(resCol); - if (selectCore->distinctKw) - markDistinctColumns(); - - if (selectCore->groupBy.size() > 0) - markGroupedColumns(); - fixColumnNames(); - - SqliteSelect* select = dynamic_cast<SqliteSelect*>(selectCore->parentStatement()); - if (select && select->coreSelects.size() > 1) - markCompoundColumns(); + markFlagsBySelect(selectCore, currentCoreResults); return currentCoreResults; } @@ -177,15 +184,31 @@ QList<SelectResolver::Column> SelectResolver::resolveAvailableCoreColumns(Sqlite if (selectCore->from) columns = resolveJoinSource(selectCore->from); + markFlagsBySelect(selectCore, columns); return columns; } +void SelectResolver::markFlagsBySelect(SqliteSelect::Core* core, QList<Column>& columns) +{ + if (core->distinctKw) + markDistinctColumns(&columns); + + if (core->groupBy.size() > 0) + markGroupedColumns(&columns); + + SqliteSelect* select = dynamic_cast<SqliteSelect*>(core->parentStatement()); + if (select && select->coreSelects.size() > 1) + markCompoundColumns(&columns); +} + SelectResolver::Column SelectResolver::translateTokenToColumn(SqliteSelect* select, TokenPtr token) { + QString strippedColName = stripObjName(token->value); + // Default result Column notTranslatedColumn; notTranslatedColumn.type = Column::OTHER; - notTranslatedColumn.column = token->value; + notTranslatedColumn.column = strippedColName; // Find containing statement SqliteStatement* parentStmt = select->findStatementWithToken(token); @@ -214,9 +237,9 @@ SelectResolver::Column SelectResolver::translateTokenToColumn(SqliteSelect* sele } // Search through available columns - for (const Column& availableColumn : resolveAvailableColumns(core)) + for (Column& availableColumn : resolveAvailableColumns(core)) { - if (availableColumn.type == Column::COLUMN && availableColumn.column.compare(token->value, Qt::CaseInsensitive) == 0) + if (availableColumn.type == Column::COLUMN && availableColumn.column.compare(strippedColName, Qt::CaseInsensitive) == 0) return availableColumn; } @@ -227,19 +250,19 @@ SelectResolver::Column SelectResolver::translateTokenToColumn(SqliteSelect* sele return notTranslatedColumn; } -void SelectResolver::markDistinctColumns() +void SelectResolver::markDistinctColumns(QList<Column>* columnList) { - markCurrentColumnsWithFlag(FROM_DISTINCT_SELECT); + markCurrentColumnsWithFlag(FROM_DISTINCT_SELECT, columnList); } -void SelectResolver::markCompoundColumns() +void SelectResolver::markCompoundColumns(QList<Column>* columnList) { - markCurrentColumnsWithFlag(FROM_COMPOUND_SELECT); + markCurrentColumnsWithFlag(FROM_COMPOUND_SELECT, columnList); } -void SelectResolver::markGroupedColumns() +void SelectResolver::markGroupedColumns(QList<Column>* columnList) { - markCurrentColumnsWithFlag(FROM_GROUPED_SELECT); + markCurrentColumnsWithFlag(FROM_GROUPED_SELECT, columnList); } void SelectResolver::fixColumnNames() @@ -296,7 +319,7 @@ void SelectResolver::resolve(SqliteSelect::Core::ResultColumn *resCol) void SelectResolver::resolveStar(SqliteSelect::Core::ResultColumn *resCol) { bool foundAtLeastOne = false; - for (SelectResolver::Column column : currentCoreSourceColumns) + for (SelectResolver::Column column : qAsConst(currentCoreSourceColumns)) { if (!resCol->table.isNull()) { @@ -338,7 +361,6 @@ void SelectResolver::resolveStar(SqliteSelect::Core::ResultColumn *resCol) else column.displayName = column.column; - column.originalColumn = resCol; currentCoreResults << column; foundAtLeastOne = true; } @@ -355,7 +377,6 @@ void SelectResolver::resolveExpr(SqliteSelect::Core::ResultColumn *resCol) // Not a simple column, but some expression SelectResolver::Column column; column.alias = resCol->alias; - column.originalColumn = resCol; column.column = getResColTokensWithoutAlias(resCol).detokenize().trimmed(); column.displayName = !resCol->alias.isNull() ? column.alias : column.column; @@ -378,7 +399,6 @@ void SelectResolver::resolveDbAndTable(SqliteSelect::Core::ResultColumn *resCol) Column col; col.alias = resCol->alias; col.column = expr->column; - col.originalColumn = resCol; col.type = Column::COLUMN; // Display name @@ -399,7 +419,7 @@ void SelectResolver::resolveDbAndTable(SqliteSelect::Core::ResultColumn *resCol) matched = resolveExplicitColumn(expr->column); - if (!matched.table.isNull()) + if (!matched.table.isNull() || !matched.tableAlias.isNull()) { col.database = matched.database; col.originalDatabase = resolveDatabase(matched.database); @@ -425,7 +445,7 @@ void SelectResolver::resolveDbAndTable(SqliteSelect::Core::ResultColumn *resCol) SelectResolver::Column SelectResolver::resolveRowIdColumn(SqliteExpr *expr) { // Looking for first source that can provide ROWID. - for (const Column& column : currentCoreSourceColumns) + for (Column& column : currentCoreSourceColumns) { if (column.table.isNull()) continue; // ROWID cannot be related to source with no table @@ -438,7 +458,7 @@ SelectResolver::Column SelectResolver::resolveRowIdColumn(SqliteExpr *expr) SelectResolver::Column SelectResolver::resolveExplicitColumn(const QString &columnName) { - for (const Column& column : currentCoreSourceColumns) + for (Column& column : currentCoreSourceColumns) { if (columnName.compare(column.column, Qt::CaseInsensitive) != 0 && columnName.compare(column.alias, Qt::CaseInsensitive) != 0) continue; @@ -450,7 +470,7 @@ SelectResolver::Column SelectResolver::resolveExplicitColumn(const QString &colu SelectResolver::Column SelectResolver::resolveExplicitColumn(const QString &table, const QString &columnName) { - for (const Column& column : currentCoreSourceColumns) + for (Column& column : currentCoreSourceColumns) { if (columnName.compare(column.column, Qt::CaseInsensitive) != 0 && columnName.compare(column.alias, Qt::CaseInsensitive) != 0) continue; @@ -465,7 +485,7 @@ SelectResolver::Column SelectResolver::resolveExplicitColumn(const QString &tabl SelectResolver::Column SelectResolver::resolveExplicitColumn(const QString &database, const QString &table, const QString &columnName) { - for (const Column& column : currentCoreSourceColumns) + for (Column& column : currentCoreSourceColumns) { if (columnName.compare(column.column, Qt::CaseInsensitive) != 0 && columnName.compare(column.alias, Qt::CaseInsensitive) != 0) continue; @@ -529,7 +549,7 @@ void SelectResolver::extractCte(SqliteSelect *select) if (!select->with) return; - for (SqliteWith::CommonTableExpression* cte : select->with->cteList) + for (SqliteWith::CommonTableExpression*& cte : select->with->cteList) cteList[cte->table] = cte; } @@ -545,7 +565,7 @@ QList<SelectResolver::Column> SelectResolver::resolveJoinSource(SqliteSelect::Co { QList<SelectResolver::Column> columnSources; columnSources += resolveSingleSource(joinSrc->singleSource); - for (SqliteSelect::Core::JoinSourceOther* otherSrc : joinSrc->otherSources) + for (SqliteSelect::Core::JoinSourceOther*& otherSrc : joinSrc->otherSources) columnSources += resolveOtherSource(otherSrc); return columnSources; @@ -566,16 +586,16 @@ QList<SelectResolver::Column> SelectResolver::resolveSingleSource(SqliteSelect:: return resolveTableFunctionColumns(joinSrc); if (isView(joinSrc->database, joinSrc->table)) - return resolveView(joinSrc->database, joinSrc->table, joinSrc->alias); + return resolveView(joinSrc); - if (joinSrc->database.isNull() && cteList.contains(joinSrc->table)) + if (joinSrc->database.isNull() && cteList.contains(joinSrc->table, Qt::CaseInsensitive)) return resolveCteColumns(joinSrc); QList<Column> columnSources; QStringList columns = getTableColumns(joinSrc->database, joinSrc->table, joinSrc->alias); Column column; column.type = Column::COLUMN; - column.table = joinSrc->table;; + column.table = joinSrc->table; column.database = joinSrc->database; column.originalDatabase = resolveDatabase(joinSrc->database); if (!joinSrc->alias.isNull()) @@ -592,40 +612,25 @@ QList<SelectResolver::Column> SelectResolver::resolveSingleSource(SqliteSelect:: QList<SelectResolver::Column> SelectResolver::resolveCteColumns(SqliteSelect::Core::SingleSource* joinSrc) { - SqliteWith::CommonTableExpression* cte = cteList[joinSrc->table]; - - Column column; - column.type = Column::COLUMN; - column.flags |= FROM_CTE_SELECT; - column.tableAlias = cte->table; - - QList<Column> columnSources; - static_qstring(cteSelectTpl, "WITH %1 SELECT * FROM %2"); + + SqliteWith::CommonTableExpression* cte = cteList.value(joinSrc->table, Qt::CaseInsensitive); QString selectQuery = cte->detokenize(); - QString query = cteSelectTpl.arg(selectQuery, cte->table); - QList<AliasedColumn> queryColumns = db->columnsForQuery(query); - if (queryColumns.isEmpty()) - { - qWarning() << "Could not detect query columns. Probably due to db error:" << db->getErrorText(); - return columnSources; - } + QString theQuery = cteSelectTpl.arg(selectQuery, cte->table); + QList<Column> columnSources = sqliteResolveColumns(theQuery); - for (const AliasedColumn& queryColumn : queryColumns) + for (Column& column : columnSources) { - if (!queryColumn.getDatabase().isNull()) - column.database = resolveDatabase(queryColumn.getDatabase()); - else - column.database = queryColumn.getDatabase(); - - column.table = queryColumn.getTable(); + column.flags |= FROM_CTE_SELECT; + column.tableAlias = cte->table; // From CTE perspective, however the column is received as "result column name" from SQLite API // is what we report back to user of the CTE as available column. No matter if it's actual alias, // or simply name of a column. - column.column = queryColumn.getAlias(); - column.displayName = queryColumn.getAlias(); - columnSources << column; + column.column = column.displayName; + + // Column aliases inside of CTE are disregarded, because they do not matter outside of CTE + column.alias = QString(); } return columnSources; @@ -680,16 +685,41 @@ QList<SelectResolver::Column> SelectResolver::resolveOtherSource(SqliteSelect::C QList<SelectResolver::Column> SelectResolver::resolveSubSelect(SqliteSelect *select) { - QList<Column> columnSources; Q_ASSERT(select->coreSelects.size() > 0); bool compound = (select->coreSelects.size() > 1); if (compound && !resolveMultiCore) - return columnSources; + return QList<Column>(); + + // SQLite API used for fully correct & precise resolution of source table, column and displayName. + QString coreQuery = select->detokenize(); + QList<Column> columnSources = sqliteResolveColumns(coreQuery); + // Internal, recursive SelectResolver used for fine tuning the tableAlias and oldTableAliases of columns. SelectResolver internalResolver(db, query); - columnSources += internalResolver.resolve(select->coreSelects[0]); + QList<Column> columnSourcesFromInternal = internalResolver.resolve(select->coreSelects[0]); + + if (columnSourcesFromInternal.size() == columnSources.size()) + { + // Copy tableAlias and oldTableAliases from internal resolver. + QMutableListIterator<Column> it(columnSources); + QMutableListIterator<Column> intIt(columnSourcesFromInternal); + while (it.hasNext() && intIt.hasNext()) + { + Column& col = it.next(); + Column& intCol = intIt.next(); + col.tableAlias = intCol.tableAlias; + col.oldTableAliases = intCol.oldTableAliases; + col.flags = intCol.flags; + } + } + else + { + qCritical() << "Number of columns resolved by internal SchemaResolver is different than resolved by SQLite API:" + << columnSourcesFromInternal.size() << "!=" << columnSources.size() + << ", therefore table alias may be identified incorrectly (from resolver, but not by SQLite API)"; + } if (compound) { @@ -701,27 +731,51 @@ QList<SelectResolver::Column> SelectResolver::resolveSubSelect(SqliteSelect *sel return columnSources; } -QList<SelectResolver::Column> SelectResolver::resolveView(const QString& database, const QString& name, const QString& alias) +QList<SelectResolver::Column> SelectResolver::resolveView(SqliteSelect::Core::SingleSource *joinSrc) { - QList<Column> results; - SqliteQueryPtr query = schemaResolver->getParsedObject(database, name, SchemaResolver::VIEW); - if (!query) - { - qDebug() << "Could not get parsed CREATE VIEW in SelectResolver::resolveView()."; - return results; - } + static_qstring(columnSqlTpl, "SELECT * FROM %1 LIMIT 0"); + QList<Column> results = sqliteResolveColumns(columnSqlTpl.arg(joinSrc->detokenize())); + applySubSelectAlias(results, (!joinSrc->alias.isNull() ? joinSrc->alias : joinSrc->table)); + for (Column& column : results) + column.flags |= FROM_VIEW; - SqliteCreateViewPtr createView = query.dynamicCast<SqliteCreateView>(); - if (!createView) + return results; +} + +QList<SelectResolver::Column> SelectResolver::sqliteResolveColumns(Db* db, const QString& query, const BiStrHash& dbNameToAttach) +{ + QList<Column> columnSources; + QList<AliasedColumn> queryColumns = db->columnsForQuery(query); + if (queryColumns.isEmpty()) + qWarning() << "Could not detect query columns. Probably due to db error:" << db->getErrorText(); + + Column column; + for (const AliasedColumn& queryColumn : queryColumns) { - qDebug() << "Parsed object not a CREATE VIEW as expected, but instead it's:" << sqliteQueryTypeToString(query->queryType); - return results; - } + if (!queryColumn.getDatabase().isNull()) + column.database = dbNameToAttach.valueByRight(queryColumn.getDatabase(), queryColumn.getDatabase(), Qt::CaseInsensitive); + else + column.database = QString(); - results = resolveSubSelect(createView->select); - applySubSelectAlias(results, (!alias.isNull() ? alias : name)); + column.displayName = queryColumn.getAlias(); + if (queryColumn.getTable().isNull()) + { + column.type = Column::OTHER; + column.table = QString(); + column.column = wrapObjIfNeeded(queryColumn.getAlias()); + column.alias = queryColumn.getAlias(); + } + else + { + column.type = Column::COLUMN; + column.table = queryColumn.getTable(); + column.column = queryColumn.getColumn(); + column.alias = (queryColumn.getColumn() != queryColumn.getAlias()) ? queryColumn.getAlias() : QString(); + } + columnSources << column; + } - return results; + return columnSources; } bool SelectResolver::isView(const QString& database, const QString& name) @@ -773,10 +827,7 @@ void SelectResolver::applySubSelectAlias(QList<SelectResolver::Column>& columns, QString SelectResolver::resolveDatabase(const QString& database) { - if (dbNameToAttach.containsRight(database, Qt::CaseInsensitive)) - return dbNameToAttach.valueByRight(database, Qt::CaseInsensitive); - - return database; + return dbNameToAttach.valueByRight(database, database, Qt::CaseInsensitive); } bool SelectResolver::parseOriginalQuery() @@ -802,16 +853,6 @@ bool SelectResolver::parseOriginalQuery() return true; } -SelectResolver::Table::Table() -{ -} - -SelectResolver::Table::Table(const SelectResolver::Table &other) : - database(other.database), originalDatabase(other.originalDatabase), table(other.table), - tableAlias(other.tableAlias), oldTableAliases(other.oldTableAliases), flags(other.flags) -{ -} - int SelectResolver::Table::operator ==(const SelectResolver::Table &other) { return ::operator==(*this, other); @@ -863,3 +904,24 @@ uint qHash(const SelectResolver::Column &column) return qHash(column.database.toLower() + "." + column.table.toLower() + "." + column.column.toLower() + "/" + column.tableAlias.toLower() + "/" + column.oldTableAliases.join(",")); } + +QDebug operator<<(QDebug debug, const SelectResolver::Column &c) +{ + QDebugStateSaver saver(debug); + debug.nospace() << "Column " << c.displayName << "(" + << c.column << " = " << c.alias << ", " + << c.table << " = " << c.tableAlias << ", " + << c.database << " = " << c.originalDatabase + << ")"; + return debug; +} + +QDebug operator<<(QDebug debug, const SelectResolver::Table &c) +{ + QDebugStateSaver saver(debug); + debug.nospace() << "Table (" + << c.table << " = " << c.tableAlias << ", " + << c.database << " = " << c.originalDatabase + << ")"; + return debug; +} |