Git commit 03b43ccbfd65c17362003047e7fec8ecef3c3bb7 by Jaroslaw Staniek. Committed on 31/05/2015 at 20:01. Pushed by staniek into branch 'calligra/2.9'. Consistency: use ILIKE/NOT ILIKE operator instead of LIKE for PostgreSQL This fixes the issue "PostgreSQL uses case-sensitive search for LIKE SQL operator, Kexi's default is case-insensitive" The approach isn't generic enough for other expressions (the context of connection could be better) but it's already much better than ignoring differences between servers BUG:348203 REVIEW:123952 FIXED-IN:2.9.5 M +1 -0 kexi/kexidb/drivers/pqxx/pqxxdriver.cpp M +12 -12 kexi/plugins/queries/kexiquerydesignerguieditor.cpp M +1 -1 kexi/widget/tableview/kexicomboboxpopup.cpp M +3 -3 libs/db/connection.cpp M +1 -0 libs/db/driver.cpp M +5 -0 libs/db/driver_p.h M +32 -27 libs/db/expression.cpp M +16 -12 libs/db/expression.h M +2 -2 libs/db/parser/parser_p.cpp http://commits.kde.org/calligra/03b43ccbfd65c17362003047e7fec8ecef3c3bb7 diff --git a/kexi/kexidb/drivers/pqxx/pqxxdriver.cpp b/kexi/kexidb/drivers/= pqxx/pqxxdriver.cpp index 83f655c..f4119bb 100644 --- a/kexi/kexidb/drivers/pqxx/pqxxdriver.cpp +++ b/kexi/kexidb/drivers/pqxx/pqxxdriver.cpp @@ -49,6 +49,7 @@ pqxxSqlDriver::pqxxSqlDriver(QObject *parent, const QVari= antList &args) beh->AUTO_INCREMENT_PK_FIELD_OPTION =3D "PRIMARY KEY"; beh->ALWAYS_AVAILABLE_DATABASE_NAME =3D "template1"; beh->QUOTATION_MARKS_FOR_IDENTIFIER =3D '"'; + beh->LIKE_OPERATOR =3D "ILIKE"; initDriverSpecificKeywords(keywords); = //predefined properties diff --git a/kexi/plugins/queries/kexiquerydesignerguieditor.cpp b/kexi/plu= gins/queries/kexiquerydesignerguieditor.cpp index 2943df6..2c80623 100644 --- a/kexi/plugins/queries/kexiquerydesignerguieditor.cpp +++ b/kexi/plugins/queries/kexiquerydesignerguieditor.cpp @@ -857,15 +857,15 @@ void KexiQueryDesignerGuiEditor::showFieldsOrRelation= sForQueryInternal( if (!eItem) continue; = - kDebug() << eItem->toString(); + kDebug() << eItem->toString(0); KexiDB::BinaryExpr* binary =3D eItem->toBinary(); if (binary && eItem->exprClass() =3D=3D KexiDBExpr_Relational) { KexiDB::Field *leftField =3D 0, *rightField =3D 0; if (eItem->token() =3D=3D '=3D' && binary->left()->toVariable() && binary->right()->toVariable() - && (leftField =3D query->findTableField(binary->left()= ->toString())) - && (rightField =3D query->findTableField(binary->right= ()->toString()))) { + && (leftField =3D query->findTableField(binary->left()= ->toString(0))) + && (rightField =3D query->findTableField(binary->right= ()->toString(0)))) { //! @todo move this check to parser on QuerySchema creation //! or to QuerySchema creation (WHERE expression should be then simp= lified //! by removing joins @@ -921,7 +921,7 @@ void KexiQueryDesignerGuiEditor::showFieldsOrRelationsF= orQueryInternal( columnAlias =3D query->columnAlias(row_num); if (field->isExpression()) { //! @todo ok? perhaps do not allow to omit aliases? - fieldName =3D field->expression()->toString(); + fieldName =3D field->expression()->toString(0); } else { tableName =3D field->table()->name(); @@ -941,9 +941,9 @@ void KexiQueryDesignerGuiEditor::showFieldsOrRelationsF= orQueryInternal( if (criteriaExpr) { //! @todo fix for !INFIX operators if (criteriaExpr->token() =3D=3D '=3D') - criteriaString =3D criteriaArgument->toString(); + criteriaString =3D criteriaArgument->toString(0); else - criteriaString =3D criteriaExpr->tokenToString() + " " + c= riteriaArgument->toString(); + criteriaString =3D criteriaExpr->tokenToString(0) + " " + = criteriaArgument->toString(0); (*newRecord)[COLUMN_ID_CRITERIA] =3D criteriaString; } d->dataTable->dataAwareObject()->insertItem(newRecord, row_num); @@ -955,7 +955,7 @@ void KexiQueryDesignerGuiEditor::showFieldsOrRelationsF= orQueryInternal( set["criteria"].setValue(criteriaString, false); if (field->isExpression()) { if (!d->changeSingleCellValue(*newRecord, COLUMN_ID_COLUMN, - QVariant(columnAlias + ": " + fi= eld->expression()->toString()), &result)) + QVariant(columnAlias + ": " + fi= eld->expression()->toString(0)), &result)) return; //problems with setting column expression } row_num++; @@ -1050,9 +1050,9 @@ void KexiQueryDesignerGuiEditor::showFieldsOrRelation= sForQueryInternal( if (criteriaExpr) { //! @todo fix for !INFIX operators if (criteriaExpr->token() =3D=3D '=3D') - criteriaString =3D criteriaArgument->toString(); + criteriaString =3D criteriaArgument->toString(0); else - criteriaString =3D criteriaExpr->tokenToString() + " " + c= riteriaArgument->toString(); + criteriaString =3D criteriaExpr->tokenToString(0) + " " + = criteriaArgument->toString(0); (*newRecord)[COLUMN_ID_CRITERIA] =3D criteriaString; } d->dataTable->dataAwareObject()->insertItem(newRecord, row_num); @@ -1494,7 +1494,7 @@ void KexiQueryDesignerGuiEditor::slotBeforeColumnCell= Changed(KexiDB::RecordData if ((e =3D parseExpressionString(fieldName, dummyToken, false/*allowRelationalOperator*/))) { - fieldName =3D e->toString(); //print it prettier + fieldName =3D e->toString(0); //print it prettier //this is just checking: destroy expr. object delete e; } @@ -1691,10 +1691,10 @@ void KexiQueryDesignerGuiEditor::slotBeforeCriteria= CellChanged(KexiDB::RecordDat QString tokenStr; if (token !=3D '=3D') { KexiDB::BinaryExpr be(KexiDBExpr_Relational, 0, token, 0); - tokenStr =3D be.tokenToString() + " "; + tokenStr =3D be.tokenToString(0) + " "; } if (set) { - (*set)["criteria"] =3D QString(tokenStr + e->toString()); = //print it prettier + (*set)["criteria"] =3D QString(tokenStr + e->toString(0));= //print it prettier } //this is just checking: destroy expr. object delete e; diff --git a/kexi/widget/tableview/kexicomboboxpopup.cpp b/kexi/widget/tabl= eview/kexicomboboxpopup.cpp index 1a0e0ec..1ff26be 100644 --- a/kexi/widget/tableview/kexicomboboxpopup.cpp +++ b/kexi/widget/tableview/kexicomboboxpopup.cpp @@ -281,7 +281,7 @@ void KexiComboBoxPopup::setData(KexiDB::TableViewColumn= *column, KexiDB::Field * expr =3D fieldExpr; } expr->debug(); - kDebug() << expr->toString(); + kDebug() << expr->toString(0); = KexiDB::Field *f =3D new KexiDB::Field(); f->setExpression(expr); diff --git a/libs/db/connection.cpp b/libs/db/connection.cpp index af245ef..3ffb069 100644 --- a/libs/db/connection.cpp +++ b/libs/db/connection.cpp @@ -1260,7 +1260,7 @@ QString KexiDB::selectStatement(const KexiDB::Driver = *driver, sql +=3D QLatin1Char('*'); } else { if (f->isExpression()) { - sql +=3D f->expression()->toString(); + sql +=3D f->expression()->toString(driver); } else { if (!f->table()) //sanity check return QString(); @@ -1495,9 +1495,9 @@ QString KexiDB::selectStatement(const KexiDB::Driver = *driver, QuerySchemaParameterValueListIterator *paramValuesItPtr =3D params= .isEmpty() ? 0 : ¶mValuesIt; if (wasWhere) { //TODO: () are not always needed - s_where =3D '(' + s_where + ") AND (" + querySchema.whereExpre= ssion()->toString(paramValuesItPtr) + ')'; + s_where =3D '(' + s_where + ") AND (" + querySchema.whereExpre= ssion()->toString(driver, paramValuesItPtr) + ')'; } else { - s_where =3D querySchema.whereExpression()->toString(paramValue= sItPtr); + s_where =3D querySchema.whereExpression()->toString(driver, pa= ramValuesItPtr); } } if (!s_where.isEmpty()) diff --git a/libs/db/driver.cpp b/libs/db/driver.cpp index c1277a8..248d5d7 100644 --- a/libs/db/driver.cpp +++ b/libs/db/driver.cpp @@ -67,6 +67,7 @@ DriverBehaviour::DriverBehaviour() , _1ST_ROW_READ_AHEAD_REQUIRED_TO_KNOW_IF_THE_RESULT_IS_EMPTY(fals= e) , SELECT_1_SUBQUERY_SUPPORTED(false) , TEXT_TYPE_MAX_LENGTH(0) + , LIKE_OPERATOR("LIKE") { } = diff --git a/libs/db/driver_p.h b/libs/db/driver_p.h index 4cdfb5b..d376ae0 100644 --- a/libs/db/driver_p.h +++ b/libs/db/driver_p.h @@ -141,6 +141,11 @@ public: This is the case for MySQL. The default is 0. */ uint TEXT_TYPE_MAX_LENGTH; + + /*! "LIKE" by default, used to construct native expressions "x LIKE y"= and "x NOT LIKE y". + LIKE is case-insensitive for MySQL, SQLite, and often on Sybase/MSSQL + while for PostgreSQL it's case-sensitive. So for PostgreSQL LIKE_OPER= ATOR =3D=3D "ILIKE". */ + QString LIKE_OPERATOR; }; = /*! Private driver's data members. Available for implementation. */ diff --git a/libs/db/expression.cpp b/libs/db/expression.cpp index 794732e..79d3e00 100644 --- a/libs/db/expression.cpp +++ b/libs/db/expression.cpp @@ -22,6 +22,7 @@ = #include "expression.h" #include "utils.h" +#include "driver_p.h" #include "parser/sqlparser.h" #include "parser/parser_p.h" = @@ -126,8 +127,9 @@ QString BaseExpr::tokenToDebugString(int token) return QLatin1String(tokenName(token)); } = -QString BaseExpr::tokenToString() +QString BaseExpr::tokenToString(const Driver *driver) { + Q_UNUSED(driver); if (m_token < 255 && isprint(m_token)) return tokenToDebugString(); return QString(); @@ -232,7 +234,7 @@ bool NArgExpr::containsNullArgument() QString NArgExpr::debugString() { QString s =3D QString("NArgExpr(") - + tokenToString() + ", " + "class=3D" + exprClassName(m_cl= ); + + tokenToString(0) + ", " + "class=3D" + exprClassName(m_c= l); foreach(BaseExpr *expr, list) { s +=3D ", " + expr->debugString(); @@ -241,13 +243,13 @@ QString NArgExpr::debugString() return s; } = -QString NArgExpr::toString(QuerySchemaParameterValueListIterator* params) +QString NArgExpr::toString(const Driver *driver, QuerySchemaParameterValue= ListIterator* params) { if (BaseExpr::token() =3D=3D KEXIDB_TOKEN_BETWEEN_AND && list.count() = =3D=3D 3) { - return list[0]->toString() + " BETWEEN " + list[1]->toString() + "= AND " + list[2]->toString(); + return list[0]->toString(driver) + " BETWEEN " + list[1]->toString= (driver) + " AND " + list[2]->toString(driver); } if (BaseExpr::token() =3D=3D KEXIDB_TOKEN_NOT_BETWEEN_AND && list.coun= t() =3D=3D 3) { - return list[0]->toString() + " NOT BETWEEN " + list[1]->toString()= + " AND " + list[2]->toString(); + return list[0]->toString(driver) + " NOT BETWEEN " + list[1]->toSt= ring(driver) + " AND " + list[2]->toString(driver); } = QString s; @@ -255,7 +257,7 @@ QString NArgExpr::toString(QuerySchemaParameterValueLis= tIterator* params) foreach(BaseExpr* e, list) { if (!s.isEmpty()) s +=3D ", "; - s +=3D e->toString(params); + s +=3D e->toString(driver, params); } return s; } @@ -329,13 +331,13 @@ bool NArgExpr::validate(ParseInfo& parseInfo) return true; } = -QString NArgExpr::tokenToString() +QString NArgExpr::tokenToString(const Driver *driver) { switch (m_token) { case KEXIDB_TOKEN_BETWEEN_AND: return "BETWEEN_AND"; case KEXIDB_TOKEN_NOT_BETWEEN_AND: return "NOT_BETWEEN_AND"; default: { - const QString s =3D BaseExpr::tokenToString(); + const QString s =3D BaseExpr::tokenToString(driver); if (!s.isEmpty()) { return QString("'%1'").arg(s); } @@ -380,19 +382,19 @@ QString UnaryExpr::debugString() + QString(",type=3D%1)").arg(Driver::defaultSQLTypeName(type())= ); } = -QString UnaryExpr::toString(QuerySchemaParameterValueListIterator* params) +QString UnaryExpr::toString(const Driver *driver, QuerySchemaParameterValu= eListIterator* params) { if (m_token =3D=3D '(') //parentheses (special case) - return '(' + (m_arg ? m_arg->toString(params) : "") + ')'; + return '(' + (m_arg ? m_arg->toString(driver, params) : "") = + ')'; if (m_token < 255 && isprint(m_token)) - return tokenToDebugString() + (m_arg ? m_arg->toString(params) : "= "); + return tokenToDebugString() + (m_arg ? m_arg->toString(driver, par= ams) : ""); if (m_token =3D=3D NOT) - return "NOT " + (m_arg ? m_arg->toString(params) : ""); + return "NOT " + (m_arg ? m_arg->toString(driver, params) : "= "); if (m_token =3D=3D SQL_IS_NULL) - return (m_arg ? m_arg->toString(params) : "") + " IS NULL"; + return (m_arg ? m_arg->toString(driver, params) : "") + " IS= NULL"; if (m_token =3D=3D SQL_IS_NOT_NULL) - return (m_arg ? m_arg->toString(params) : "") + " IS NOT NUL= L"; - return QString("{INVALID_OPERATOR#%1} ").arg(m_token) + (m_arg ? m_arg= ->toString(params) : ""); + return (m_arg ? m_arg->toString(driver, params) : "") + " IS= NOT NULL"; + return QString("{INVALID_OPERATOR#%1} ").arg(m_token) + (m_arg ? m_arg= ->toString(driver, params) : ""); } = void UnaryExpr::getQueryParameters(QuerySchemaParameterList& params) @@ -551,7 +553,7 @@ QString BinaryExpr::debugString() + QString::fromLatin1(",type=3D%1)").arg(Driver::defaultSQLType= Name(type())); } = -QString BinaryExpr::tokenToString() +QString BinaryExpr::tokenToString(const Driver *driver) { if (m_token < 255 && isprint(m_token)) return tokenToDebugString(); @@ -564,8 +566,8 @@ QString BinaryExpr::tokenToString() case NOT_EQUAL2: return "!=3D"; case LESS_OR_EQUAL: return "<=3D"; case GREATER_OR_EQUAL: return ">=3D"; - case LIKE: return "LIKE"; - case NOT_LIKE: return "NOT LIKE"; + case LIKE: return driver ? driver->behaviour()->LIKE_OPERATOR : "LIKE"; + case NOT_LIKE: return driver ? (QString::fromLatin1("NOT ") + driver->= behaviour()->LIKE_OPERATOR) : QString::fromLatin1("NOT LIKE"); case SQL_IN: return "IN"; // other logical operations: OR (or ||) AND (or &&) XOR case SIMILAR_TO: return "SIMILAR TO"; @@ -582,11 +584,11 @@ QString BinaryExpr::tokenToString() return QString("{INVALID_BINARY_OPERATOR#%1} ").arg(m_token); } = -QString BinaryExpr::toString(QuerySchemaParameterValueListIterator* params) +QString BinaryExpr::toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params) { #define INFIX(a) \ - (m_larg ? m_larg->toString(params) : "") + ' ' + a + ' ' + (m_ra= rg ? m_rarg->toString(params) : "") - return INFIX(tokenToString()); + (m_larg ? m_larg->toString(driver, params) : "") + ' ' + a + ' '= + (m_rarg ? m_rarg->toString(driver, params) : "") + return INFIX(tokenToString(driver)); } = void BinaryExpr::getQueryParameters(QuerySchemaParameterList& params) @@ -659,12 +661,13 @@ Field::Type ConstExpr::type() = QString ConstExpr::debugString() { - return QString("ConstExpr('") + tokenToDebugString() + "'," + toString= () + return QString("ConstExpr('") + tokenToDebugString() + "'," + toString= (0) + QString(",type=3D%1)").arg(Driver::defaultSQLTypeName(type())= ); } = -QString ConstExpr::toString(QuerySchemaParameterValueListIterator* params) +QString ConstExpr::toString(const Driver *driver, QuerySchemaParameterValu= eListIterator* params) { + Q_UNUSED(driver); Q_UNUSED(params); if (m_token =3D=3D SQL_NULL) return "NULL"; @@ -736,8 +739,9 @@ QString QueryParameterExpr::debugString() + QString("',type=3D%1)").arg(Driver::defaultSQLTypeName(type()= )); } = -QString QueryParameterExpr::toString(QuerySchemaParameterValueListIterator= * params) +QString QueryParameterExpr::toString(const Driver *driver, QuerySchemaPara= meterValueListIterator* params) { + Q_UNUSED(driver); return params ? params->getPreviousValueAsString(type()) : QString::fr= omLatin1("[%2]").arg(value.toString()); } = @@ -790,8 +794,9 @@ QString VariableExpr::debugString() + QString(",type=3D%1)").arg(field ? Driver::defaultSQLTypeName= (type()) : QString("FIELD NOT DEFINED YET")); } = -QString VariableExpr::toString(QuerySchemaParameterValueListIterator* para= ms) +QString VariableExpr::toString(const Driver *driver, QuerySchemaParameterV= alueListIterator* params) { + Q_UNUSED(driver); Q_UNUSED(params); return name; } @@ -1012,9 +1017,9 @@ QString FunctionExpr::debugString() return res; } = -QString FunctionExpr::toString(QuerySchemaParameterValueListIterator* para= ms) +QString FunctionExpr::toString(const Driver *driver, QuerySchemaParameterV= alueListIterator* params) { - return name + '(' + (args ? args->toString(params) : QString()) + ')'; + return name + '(' + (args ? args->toString(driver, params) : QString()= ) + ')'; } = void FunctionExpr::getQueryParameters(QuerySchemaParameterList& params) diff --git a/libs/db/expression.h b/libs/db/expression.h index a7b0410..c3c368d 100644 --- a/libs/db/expression.h +++ b/libs/db/expression.h @@ -125,8 +125,12 @@ public: = /*! \return string as a representation of this expression element by r= unning recursive calls. \a param, if not 0, points to a list item containing value of a query= parameter - (used in QueryParameterExpr). */ - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0) =3D 0; + (used in QueryParameterExpr). + \a driver if present can be used to obtain information useful while g= enerating native statement strings. + For example PostgreSQL uses ILIKE while other drivers use LIKE operat= or. + For generating KEXISQL \a driver is always 0. + @todo connection should be provided here as a context, not driver. */ + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0) =3D 0; = /*! Collects query parameters (messages and types) reculsively and sav= es them to params. The leaf nodes are objects of QueryParameterExpr class. */ @@ -147,7 +151,7 @@ public: static QString tokenToDebugString(int token); = /*! \return string for token, like "<=3D" or ">" */ - virtual QString tokenToString(); + virtual QString tokenToString(const Driver *driver); = int exprClass() const { return m_cl; @@ -187,10 +191,10 @@ public: BaseExpr *arg(int n); int args(); virtual QString debugString(); - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0); + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0); virtual void getQueryParameters(QuerySchemaParameterList& params); virtual bool validate(ParseInfo& parseInfo); - virtual QString tokenToString(); + virtual QString tokenToString(const Driver *driver); = BaseExpr::List list; }; @@ -206,7 +210,7 @@ public: virtual UnaryExpr* copy() const; virtual Field::Type type(); virtual QString debugString(); - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0); + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0); virtual void getQueryParameters(QuerySchemaParameterList& params); BaseExpr *arg() const { return m_arg; @@ -234,7 +238,7 @@ public: virtual BinaryExpr* copy() const; virtual Field::Type type(); virtual QString debugString(); - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0); + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0); virtual void getQueryParameters(QuerySchemaParameterList& params); BaseExpr *left() const { return m_larg; @@ -243,7 +247,7 @@ public: return m_rarg; } virtual bool validate(ParseInfo& parseInfo); - virtual QString tokenToString(); + virtual QString tokenToString(const Driver *driver); = BaseExpr *m_larg; BaseExpr *m_rarg; @@ -262,7 +266,7 @@ public: virtual ConstExpr* copy() const; virtual Field::Type type(); virtual QString debugString(); - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0); + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0); virtual void getQueryParameters(QuerySchemaParameterList& params); virtual bool validate(ParseInfo& parseInfo); QVariant value; @@ -289,7 +293,7 @@ public: */ void setType(Field::Type type); virtual QString debugString(); - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0); + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0); virtual void getQueryParameters(QuerySchemaParameterList& params); virtual bool validate(ParseInfo& parseInfo); protected: @@ -307,7 +311,7 @@ public: virtual VariableExpr* copy() const; virtual Field::Type type(); virtual QString debugString(); - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0); + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0); virtual void getQueryParameters(QuerySchemaParameterList& params); = /*! Validation. Sets field, tablePositionForField @@ -351,7 +355,7 @@ public: virtual FunctionExpr* copy() const; virtual Field::Type type(); virtual QString debugString(); - virtual QString toString(QuerySchemaParameterValueListIterator* params= =3D 0); + virtual QString toString(const Driver *driver, QuerySchemaParameterVal= ueListIterator* params =3D 0); virtual void getQueryParameters(QuerySchemaParameterList& params); virtual bool validate(ParseInfo& parseInfo); = diff --git a/libs/db/parser/parser_p.cpp b/libs/db/parser/parser_p.cpp index 5f11f4e..3d3064a 100644 --- a/libs/db/parser/parser_p.cpp +++ b/libs/db/parser/parser_p.cpp @@ -346,7 +346,7 @@ QuerySchema* buildSelectQuery( aliasVariable =3D e->toBinary()->right()->toVariable(); if (!aliasVariable) { setError(i18n("Invalid alias definition for column \"%= 1\".", - columnExpr->toString())); //ok? + columnExpr->toString(0))); //ok? break; } } @@ -372,7 +372,7 @@ QuerySchema* buildSelectQuery( //take first (left) argument of the special binary expr, w= ill be owned, do not destroy e->toBinary()->m_larg =3D 0; } else { - setError(i18n("Invalid \"%1\" column definition.", e->toSt= ring())); //ok? + setError(i18n("Invalid \"%1\" column definition.", e->toSt= ring(0))); //ok? break; } =20