KDb

KDbFunctionExpression.cpp
1/* This file is part of the KDE project
2 Copyright (C) 2003-2016 Jarosław Staniek <staniek@kde.org>
3
4 Based on nexp.cpp : Parser module of Python-like language
5 (C) 2001 Jarosław Staniek, MIMUW (www.mimuw.edu.pl)
6
7 This library is free software; you can redistribute it and/or
8 modify it under the terms of the GNU Library General Public
9 License as published by the Free Software Foundation; either
10 version 2 of the License, or (at your option) any later version.
11
12 This library is distributed in the hope that it will be useful,
13 but WITHOUT ANY WARRANTY; without even the implied warranty of
14 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 Library General Public License for more details.
16
17 You should have received a copy of the GNU Library General Public License
18 along with this library; see the file COPYING.LIB. If not, write to
19 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
20 * Boston, MA 02110-1301, USA.
21 */
22
23#include "KDbExpression.h"
24#include "KDb.h"
25#include "KDbQuerySchema.h"
26#include "KDbDriver.h"
27#include "KDbParser.h"
28#include "KDbParser_p.h"
29#include "kdb_debug.h"
30
31#include <QSet>
32
33#include <vector>
34#include <algorithm>
35
36// Enable to add SQLite-specific functions
37//#define KDB_ENABLE_SQLITE_SPECIFIC_FUNCTIONS
38
39//! A set of names of aggregation SQL functions
40class BuiltInAggregates
41{
42public:
43 BuiltInAggregates()
44 : data({ QStringLiteral("SUM"),
45 QStringLiteral("MIN"),
46 QStringLiteral("MAX"),
47 QStringLiteral("AVG"),
48 QStringLiteral("COUNT"),
49 QStringLiteral("STD"),
50 QStringLiteral("STDDEV"),
51 QStringLiteral("VARIANCE")
52 })
53 {
54 }
55 const QSet<QString> data;
56};
57
58Q_GLOBAL_STATIC(BuiltInAggregates, _builtInAggregates)
59
60//! Type of a single function argument, used with KDbField::Type values.
61//! Used to indicate that multiple types are allowed.
62enum BuiltInFunctionArgumentType
63{
64 AnyText = KDbField::LastType + 1,
65 AnyInt,
66 AnyFloat,
67 AnyNumber,
68 Any
69};
70
71//! @return any concrete type matching rule @a argType
72static KDbField::Type anyMatchingType(int argType)
73{
74 if (argType == AnyText || argType == Any) {
75 return KDbField::Text;
76 }
77 else if (argType == AnyInt || argType == AnyNumber) {
78 return KDbField::Integer;
79 }
80 else if (argType == AnyFloat) {
81 return KDbField::Double;
82 }
84}
85
86//! Declaration of a single built-in function. It can offer multiple signatures.
87class BuiltInFunctionDeclaration
88{
89public:
90 inline BuiltInFunctionDeclaration()
91 : defaultReturnType(KDbField::InvalidType), copyReturnTypeFromArg(-1)
92 {
93 }
94 virtual ~BuiltInFunctionDeclaration() {}
95 virtual KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const {
96 Q_UNUSED(parseInfo);
97 const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
98 if (argsData->containsNullArgument()) {
99 return KDbField::Null;
100 }
101 if (copyReturnTypeFromArg >= 0 && copyReturnTypeFromArg < argsData->children.count()) {
102 KDbQueryParameterExpressionData *queryParameterExpressionData
103 = argsData->children.at(copyReturnTypeFromArg)
105 if (queryParameterExpressionData) {
106 // Set query parameter type (if there are any) to deduced result type
107 //! @todo Most likely but can be also other type
108 for (size_t i = 0; i < signatures.size(); ++i) {
109 int** signature = signatures[i];
110 const KDbField::Type t = anyMatchingType(signature[copyReturnTypeFromArg][0]);
111 if (t != KDbField::InvalidType) {
112 queryParameterExpressionData->m_type = t;
113 return t;
114 }
115 }
116 }
117 return argsData->children.at(copyReturnTypeFromArg)->type();
118 }
119 return defaultReturnType;
120 }
121 std::vector<int**> signatures;
122protected:
123 KDbField::Type defaultReturnType;
124 int copyReturnTypeFromArg;
125 friend class BuiltInFunctions;
126private:
127 Q_DISABLE_COPY(BuiltInFunctionDeclaration)
128};
129
130//! Declaration of a single built-in function COALESCE() and similar ones.
131class CoalesceFunctionDeclaration : public BuiltInFunctionDeclaration
132{
133public:
134 CoalesceFunctionDeclaration() {}
135 KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
136 Q_UNUSED(parseInfo);
137 // Find type
138 //! @todo Most likely but can be also other type
140 const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
141 foreach(const ExplicitlySharedExpressionDataPointer &expr, argsData->children) {
142 KDbQueryParameterExpressionData *queryParameterExpressionData = expr->convert<KDbQueryParameterExpressionData>();
143 const KDbField::Type currentType = expr->type();
144 if (!queryParameterExpressionData && currentType != KDbField::Null) {
145 t = currentType;
146 break;
147 }
148 }
149 foreach(const ExplicitlySharedExpressionDataPointer &expr, argsData->children) {
150 KDbQueryParameterExpressionData *queryParameterExpressionData = expr->convert<KDbQueryParameterExpressionData>();
151 if (queryParameterExpressionData) {
152 // Set query parameter type (if there are any) to deduced result type
153 queryParameterExpressionData->m_type = t;
154 }
155 }
156 return t;
157 }
158private:
159 Q_DISABLE_COPY(CoalesceFunctionDeclaration)
160};
161
162//! Declaration of a single built-in function MIN(), MAX() and similar ones.
163//! Its return type is:
164//! - NULL if any argument is NULL
165//! - valid type if types of all arguments are compatible (e.g. text, numeric, date...)
166//! - InvalidType if types of any two are incompatible
167class MinMaxFunctionDeclaration : public BuiltInFunctionDeclaration
168{
169 Q_DECLARE_TR_FUNCTIONS(MinMaxFunctionDeclaration)
170public:
171 MinMaxFunctionDeclaration() {}
172 KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
173 const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
174 if (argsData->children.isEmpty()) {
175 return KDbField::Null;
176 }
177 const KDbField::Type type0 = argsData->children.at(0)->type(); // cache: evaluating type of expressions can be expensive
178 if (nullOrInvalid(type0)) {
179 return type0;
180 }
181 KDbField::TypeGroup prevTg = KDbField::typeGroup(type0); // use typegroup for simplicity
182 bool prevTgIsAny = argsData->children.at(0)->convertConst<KDbQueryParameterExpressionData>();
183 for(int i = 1; i < argsData->children.count(); ++i) {
184 const ExplicitlySharedExpressionDataPointer expr = argsData->children.at(i);
185 const KDbField::Type t = expr->type();
186 if (nullOrInvalid(t)) {
187 return t;
188 }
190 const bool tgIsAny = argsData->children.at(i)->convertConst<KDbQueryParameterExpressionData>();
191 if (prevTgIsAny) {
192 if (!tgIsAny) { // no longer "Any" (query parameter)
193 prevTgIsAny = false;
194 prevTg = tg;
195 }
196 continue;
197 } else if (tgIsAny) {
198 continue; // use previously found concrete type
199 }
200 if ((prevTg == KDbField::IntegerGroup || prevTg == KDbField::FloatGroup)
201 && (tg == KDbField::IntegerGroup || tg == KDbField::FloatGroup))
202 {
203 if (prevTg == KDbField::IntegerGroup && tg == KDbField::FloatGroup) {
204 prevTg = KDbField::FloatGroup; // int -> float
205 }
206 continue;
207 }
208 if (prevTg == tg) {
209 continue;
210 }
211 if (parseInfo) {
212 parseInfo->setErrorMessage(
213 tr("Incompatible types in %1() function").arg(f->name));
214 parseInfo->setErrorDescription(
215 tr("Argument #%1 of type \"%2\" in function %3() is not "
216 "compatible with previous arguments of type \"%4\".")
217 .arg(i+1)
218 .arg(KDbField::typeName(simpleTypeForGroup(tg)),
219 f->name,
220 KDbField::typeName(simpleTypeForGroup(prevTg))));
221 }
223 }
224 if (prevTgIsAny) {
225 //! @todo Most likely Integer but can be also Float/Double/Text/Date...
226 return KDbField::Integer;
227 }
228 const KDbField::Type resultType = safeTypeForGroup(prevTg);
229 // Set query parameter types (if there are any) to deduced result type
230 for(ExplicitlySharedExpressionDataPointer expr : argsData->children) {
231 KDbQueryParameterExpressionData *queryParameterExpressionData = expr->convert<KDbQueryParameterExpressionData>();
232 if (queryParameterExpressionData) {
233 queryParameterExpressionData->m_type = resultType;
234 }
235 }
236 return resultType;
237 }
238private:
239 static bool nullOrInvalid(KDbField::Type type) {
241 }
242 //! @return safe default type for type group @a tg (too big sizes better than too small)
243 static KDbField::Type safeTypeForGroup(KDbField::TypeGroup tg) {
244 switch (tg) {
245 case KDbField::TextGroup: return KDbField::LongText;
246 case KDbField::IntegerGroup: return KDbField::BigInteger;
247 case KDbField::FloatGroup: return KDbField::Double;
248 case KDbField::BooleanGroup: return KDbField::Boolean;
249 case KDbField::DateTimeGroup: return KDbField::DateTime;
250 case KDbField::BLOBGroup: return KDbField::BLOB;
251 default: break;
252 }
254 }
255 //! @return resonable default type for type group @a tg (used for displaying in error message)
256 static KDbField::Type simpleTypeForGroup(KDbField::TypeGroup tg) {
257 switch (tg) {
258 case KDbField::TextGroup: return KDbField::Text;
259 case KDbField::IntegerGroup: return KDbField::Integer;
260 case KDbField::FloatGroup: return KDbField::Double;
261 case KDbField::BooleanGroup: return KDbField::Boolean;
262 case KDbField::DateTimeGroup: return KDbField::DateTime;
263 case KDbField::BLOBGroup: return KDbField::BLOB;
264 default: break;
265 }
267 }
268 Q_DISABLE_COPY(MinMaxFunctionDeclaration)
269};
270
271//! Declaration of a single built-in function RANDOM() and RANDOM(X,Y).
272//! Its return type is:
273//! - Double when number of arguments is zero
274//! - integer if there are two integer arguments (see KDb::maximumForIntegerFieldTypes())
275//! - InvalidType for other number of arguments
276class RandomFunctionDeclaration : public BuiltInFunctionDeclaration
277{
278 Q_DECLARE_TR_FUNCTIONS(RandomFunctionDeclaration)
279public:
280 RandomFunctionDeclaration() {}
281 KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
282 const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
283 if (argsData->children.isEmpty()) {
284 return KDbField::Double;
285 }
286 if (argsData->children.count() == 2) {
287 const KDbConstExpressionData *const0 = argsData->children.at(0)->convertConst<KDbConstExpressionData>();
288 const KDbConstExpressionData *const1 = argsData->children.at(1)->convertConst<KDbConstExpressionData>();
289 if (const0 && const1) {
290 bool ok0;
291 const qlonglong val0 = const0->value.toLongLong(&ok0);
292 bool ok1;
293 const qlonglong val1 = const1->value.toLongLong(&ok1);
294 if (ok0 && ok1) {
295 if (val0 >= val1) {
296 if (parseInfo) {
297 parseInfo->setErrorMessage(
298 tr("Invalid arguments of %1() function").arg(f->name));
299 parseInfo->setErrorDescription(
300 tr("Value of the first argument should be less than "
301 "value of the second argument."));
302 }
304 }
305 }
306 }
309 // deduce query parameter types
310 KDbQueryParameterExpressionData *queryParameterExpressionData0
311 = argsData->children.at(0)->convert<KDbQueryParameterExpressionData>();
312 KDbQueryParameterExpressionData *queryParameterExpressionData1
313 = argsData->children.at(1)->convert<KDbQueryParameterExpressionData>();
314 if (queryParameterExpressionData0 && queryParameterExpressionData1) {
315 queryParameterExpressionData0->m_type = KDbField::Integer;
316 queryParameterExpressionData1->m_type = KDbField::Integer;
319 } else if (queryParameterExpressionData0 && !queryParameterExpressionData1) {
320 queryParameterExpressionData0->m_type = KDbField::Integer;
321 t0 = queryParameterExpressionData0->m_type;
322 t1 = argsData->children.at(1)->type();
323 } else if (!queryParameterExpressionData0 && queryParameterExpressionData1) {
324 queryParameterExpressionData1->m_type = KDbField::Integer;
325 t0 = argsData->children.at(0)->type();
326 t1 = queryParameterExpressionData1->m_type;
327 } else {
328 t0 = argsData->children.at(0)->type();
329 t1 = argsData->children.at(1)->type();
330 }
332 }
334 }
335private:
336 Q_DISABLE_COPY(RandomFunctionDeclaration)
337};
338
339//! Declaration of a single built-in function CEILING(X) and FLOOR(X).
340//! Its return type is:
341//! - integer if there are two integer arguments (see KDb::maximumForIntegerFieldTypes())
342//! - InvalidType for other number of arguments
343class CeilingFloorFunctionDeclaration : public BuiltInFunctionDeclaration
344{
345public:
346 CeilingFloorFunctionDeclaration() {}
347 KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
348 Q_UNUSED(parseInfo);
349 const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
350 if (argsData->children.count() == 1) {
351 KDbQueryParameterExpressionData *queryParameterExpressionData
352 = argsData->children.at(0)->convert<KDbQueryParameterExpressionData>();
353 if (queryParameterExpressionData) {
354 // Set query parameter type (if there are any) to deduced result type
355 //! @todo Most likely but can be also other type
356 queryParameterExpressionData->m_type = KDbField::Double;
358 }
359 const KDbField::Type type = argsData->children.at(0)->type(); // cache: evaluating type of expressions can be expensive
360 if (KDbField::isFPNumericType(type)) {
362 }
363 switch (type) {
367 case KDbField::Null: return KDbField::Null;
369 default:;
370 }
371 }
373 }
374private:
375 Q_DISABLE_COPY(CeilingFloorFunctionDeclaration)
376};
377
378//! A map of built-in SQL functions
379//! See https://community.kde.org/Kexi/Plugins/Queries/SQL_Functions for the status.
380class BuiltInFunctions
381{
382public:
383 BuiltInFunctions();
384 ~BuiltInFunctions()
385 {
386 qDeleteAll(m_functions);
387 }
388
389 //! @return function declaration's structure for name @a name
390 //! If @a name is alias of the function, e.g. "MIN" for "LEAST", the original
391 //! function's declaration is returned.
392 BuiltInFunctionDeclaration* value(const QString &name) const;
393
394 //! @return a list of function aliases.
395 QStringList aliases() const;
396
397 static int multipleArgs[];
398private:
401 Q_DISABLE_COPY(BuiltInFunctions)
402};
403
404int BuiltInFunctions::multipleArgs[] = { 0 };
405
406BuiltInFunctions::BuiltInFunctions()
407{
408 BuiltInFunctionDeclaration *decl;
409#define _TYPES(name, ...) static int name[] = { __VA_ARGS__, KDbField::InvalidType }
410 _TYPES(argAnyTextOrNull, AnyText, KDbField::Null);
411 _TYPES(argAnyIntOrNull, AnyInt, KDbField::Null);
412 _TYPES(argAnyNumberOrNull, AnyNumber, KDbField::Null);
413 _TYPES(argAnyFloatOrNull, AnyFloat, KDbField::Null);
414 Q_UNUSED(argAnyFloatOrNull);
415 _TYPES(argAnyOrNull, Any, KDbField::Null);
416 _TYPES(argBLOBOrNull, KDbField::BLOB, KDbField::Null);
417 Q_UNUSED(argBLOBOrNull);
418 _TYPES(argAnyTextBLOBOrNull, AnyText, KDbField::BLOB, KDbField::Null);
419#undef _TYPES
420
421//! Adds a signature named @a name with specified arguments to declaration decl
422#define _SIG(name, ...) \
423 static int* name[] = { __VA_ARGS__, nullptr }; \
424 decl->signatures.push_back(name)
425
426//! Adds a signature with no arguments to declaration decl
427#define _SIG0 \
428 decl->signatures.push_back(sig0)
429
430 static int* sig0[] = { nullptr };
431
432 m_functions.insert(QLatin1String("ABS"), decl = new BuiltInFunctionDeclaration);
433 // From https://www.sqlite.org/lang_corefunc.html
434 /* The abs(X) function returns the absolute value of the numeric argument X.
435 Abs(X) returns NULL if X is NULL. Abs(X) returns 0.0 if X is a string or blob that
436 cannot be converted to a numeric value. If X is the integer -9223372036854775808
437 then abs(X) throws an integer overflow error since there is no equivalent positive
438 64-bit two complement value. */
439 // example: SELECT ABS(-27), ABS(-3.1415), ABS(NULL + 1)
440 // result: 27, 3.1415, NULL
441 decl->copyReturnTypeFromArg = 0;
442 _SIG(abs_1, argAnyNumberOrNull);
443
444 m_functions.insert(QLatin1String("CEILING"), decl = new CeilingFloorFunctionDeclaration);
445 /* ceiling(X) returns the largest integer value not less than X. */
446 // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_ceiling
447 // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE
448 // SQLite has no equivalent of ceiling() so this is used:
449 // (CASE WHEN X = CAST(X AS INT) THEN CAST(X AS INT) WHEN X >= 0 THEN CAST(X AS INT) + 1 ELSE CAST(X AS INT) END)
450 //! @todo add a custom function to SQLite to optimize/simplify things
451 // example: SELECT CEILING(3.14), CEILING(-99.001)
452 // result: 4, -99
453 _SIG(ceiling, argAnyNumberOrNull);
454
455 m_functions.insert(QLatin1String("CHAR"), decl = new BuiltInFunctionDeclaration);
456 // From https://www.sqlite.org/lang_corefunc.html
457 /* The char(X1,X2,...,XN) function returns a string composed of characters having
458 the unicode code point values of integers X1 through XN, respectively. */
459 // example: SELECT CHAR(75,69,88,73), CHAR()
460 // result: "KEXI" ""
461 decl->defaultReturnType = KDbField::LongText;
462 static int char_min_args[] = { 0 };
463 _SIG(char_N, argAnyIntOrNull, multipleArgs, char_min_args);
464
465 m_functions.insert(QLatin1String("COALESCE"), decl = new CoalesceFunctionDeclaration);
466 // From https://www.sqlite.org/lang_corefunc.html
467 /* The coalesce() function returns a copy of its first non-NULL argument, or NULL if
468 all arguments are NULL. Coalesce() must have at least 2 arguments. */
469 // example: SELECT COALESCE(NULL, 17, NULL, "A")
470 // result: 17
471 static int coalesce_min_args[] = { 2 };
472 _SIG(coalesce_N, argAnyOrNull, multipleArgs, coalesce_min_args);
473
474 m_functions.insert(QLatin1String("FLOOR"), decl = new CeilingFloorFunctionDeclaration);
475 /* floor(X) returns the largest integer value not greater than X. */
476 // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_floor
477 // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE
478 // SQLite has no equivalent of floor() so this is used:
479 // (CASE WHEN X >= 0 OR X = CAST(X AS INT) THEN CAST(X AS INT) ELSE CAST(X AS INT) - 1 END)
480 //! @todo add a custom function to SQLite to optimize/simplify things
481 // example: SELECT FLOOR(3.14), FLOOR(-99.001)
482 // result: 3, -100
483 _SIG(floor, argAnyNumberOrNull);
484
485 m_functions.insert(QLatin1String("GREATEST"), decl = new MinMaxFunctionDeclaration);
486 m_aliases.insert(QLatin1String("MAX"), decl);
487 // From https://www.sqlite.org/lang_corefunc.html
488 // For SQLite MAX() is used.
489 // If arguments are of text type, to each argument default (unicode) collation
490 // is assigned that is configured for SQLite by KDb.
491 // Example: SELECT MAX('ą' COLLATE '', 'z' COLLATE '').
492 // Example: SELECT MAX('ą' COLLATE '', 'z' COLLATE '').
493 /* The multi-argument max() function returns the argument with the maximum value, or
494 return NULL if any argument is NULL. The multi-argument max() function searches its
495 arguments from left to right for an argument that defines a collating function and
496 uses that collating function for all string comparisons. If none of the arguments to
497 max() define a collating function, then the BINARY collating function is used. Note
498 that max() is a simple function when it has 2 or more arguments but operates as an
499 aggregate function if given only a single argument. */
500 // For pgsql GREATEST() function ignores NULL values, it only returns NULL
501 // if all the expressions evaluate to NULL. So this is used for MAX(v0,..,vN):
502 // (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE GREATEST(v0,..,vN) END)
503 // See also https://www.postgresql.org/docs/9.5/static/functions-conditional.html#FUNCTIONS-GREATEST-LEAST
504 //! @todo for pgsql CREATE FUNCTION can be used to speed up and simplify things
505 // For mysql GREATEST() is used.
506 // See https://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_greatest
507 // Note: Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL
508 // (like pgsql). As of 5.0.13, it returns NULL if any argument is NULL (like sqlite's MAX()).
509 // See also https://bugs.mysql.com/bug.php?id=15610
510 //! @todo MySQL: check for server version and don't use the pgsql's approach for ver >= 5.0.13
511 //! We cannot do that now because we only have access to driver, not the connection.
512 // example: SELECT GREATEST("Z", "ą", "AA"), MAX(0.1, 7.1, 7), GREATEST(9, NULL, -1)
513 // result: "Z", 7.1, NULL
514 static int greatest_min_args[] = { 2 };
515 _SIG(greatest_N, argAnyOrNull, multipleArgs, greatest_min_args);
516
517 m_functions.insert(QLatin1String("HEX"), decl = new BuiltInFunctionDeclaration);
518 // From https://www.sqlite.org/lang_corefunc.html
519 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_hex
520 /* The hex() function interprets its argument as a BLOB and returns a string which is
521 the upper-case hexadecimal rendering of the content of that blob. */
522 /* For pgsql UPPER(ENCODE(val, 'hex')) is used,
523 See https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-OTHER */
524 // example: SELECT HEX(X'BEEF'), HEX('DEAD')
525 // result: "BEEF", "44454144"
526 //! @todo HEX(int) for SQLite is not the same as HEX(int) for MySQL so we disable it
527 //! -- maybe can be wrapped?
528 decl->defaultReturnType = KDbField::LongText;
529 _SIG(hex_1, argAnyTextBLOBOrNull);
530
531 m_functions.insert(QLatin1String("IFNULL"), decl = new CoalesceFunctionDeclaration);
532 // From https://www.sqlite.org/lang_corefunc.html
533 /* The ifnull() function returns a copy of its first non-NULL argument, or NULL if
534 both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function
535 is equivalent to coalesce() with two arguments. */
536 // For postgresql coalesce() is used.
537 // example: SELECT IFNULL(NULL, 17), IFNULL(NULL, NULL)
538 // result: 17, NULL
539 _SIG(ifnull_2, argAnyOrNull, argAnyOrNull);
540
541 m_functions.insert(QLatin1String("INSTR"), decl = new BuiltInFunctionDeclaration);
542 // From https://www.sqlite.org/lang_corefunc.html
543 /* The instr(X,Y) function finds the first occurrence of string Y within string X and
544 returns the number of prior characters plus 1, or 0 if Y is nowhere found within X.
545 If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then both are
546 interpreted as strings. If either X or Y are NULL in instr(X,Y) then the result
547 is NULL. */
548 //! @todo PostgreSQL does not have instr() but CREATE FUNCTION can be used,
549 //! see https://www.postgresql.org/docs/9.5/static/plpgsql-porting.html
550 //! @todo support (BLOB, BLOB)?
551 /* From the same docs:
552 Or, if X and Y are both BLOBs, then instr(X,Y) returns one more than the number bytes
553 prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X. */
554 // example: SELECT INSTR("KEXI", "X"), INSTR("KEXI", "ZZ")
555 // result: 3, 0
556 decl->defaultReturnType = KDbField::Integer;
557 _SIG(instr_2, argAnyTextOrNull, argAnyTextOrNull);
558
559 m_functions.insert(QLatin1String("LEAST"), decl = new MinMaxFunctionDeclaration);
560 m_aliases.insert(QLatin1String("MIN"), decl);
561 // From https://www.sqlite.org/lang_corefunc.html
562 // For SQLite uses MIN().
563 /* The multi-argument min() function returns the argument with the minimum value, or
564 return NULL if any argument is NULL. The multi-argument min() function searches its
565 arguments from left to right for an argument that defines a collating function and
566 uses that collating function for all string comparisons. If none of the arguments to
567 max() define a collating function, then the BINARY collating function is used. Note
568 that max() is a simple function when it has 2 or more arguments but operates as an
569 aggregate function if given only a single argument. */
570 // For pgsql LEAST() function ignores NULL values, it only returns NULL
571 // if all the expressions evaluate to NULL. So this is used for MAX(v0,..,vN):
572 // (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE LEAST(v0,..,vN) END)
573 // See also https://www.postgresql.org/docs/9.5/static/functions-conditional.html#FUNCTIONS-GREATEST-LEAST
574 //! @todo for pgsql CREATE FUNCTION can be used to speed up and simplify things
575 // For mysql LEAST() is used.
576 // See https://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_least
577 // Note: Before MySQL 5.0.13, LEAST() returns NULL only if all arguments are NULL
578 // (like pgsql). As of 5.0.13, it returns NULL if any argument is NULL (like sqlite's MIN()).
579 //! @todo MySQL: check for server version and don't use the pgsql's approach for ver >= 5.0.13
580 //! We cannot do that now because we only have access to driver, not the connection.
581 // See also https://bugs.mysql.com/bug.php?id=15610
582 // example: SELECT LEAST("Z", "ą", "AA"), MIN(0.1, 7.1, 7), LEAST(9, NULL, -1)
583 // result: "ą", 0.1, NULL
584 static int least_min_args[] = { 2 };
585 _SIG(least_N, argAnyOrNull, multipleArgs, least_min_args);
586
587 m_functions.insert(QLatin1String("LENGTH"), decl = new BuiltInFunctionDeclaration);
588 // From https://www.sqlite.org/lang_corefunc.html
589 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length
590 /* For a string value X, the length(X) function returns the number of characters (not
591 bytes) in X prior to the first NUL character. Since SQLite strings do not normally
592 contain NUL characters, the length(X) function will usually return the total number
593 of characters in the string X. For a blob value X, length(X) returns the number of
594 bytes in the blob. If X is NULL then length(X) is NULL. If X is numeric then
595 length(X) returns the length of a string representation of X. */
596 /* For postgres octet_length(val) is used if val is a of BLOB type.
597 length(val) for BLOB cannot be used because it returns number of bits. */
598 /* For mysql char_length(val) is used.
599 This is because length(val) in mysql returns number of bytes, what is not right for
600 multibyte (unicode) encodings. */
601 // example: SELECT LENGTH('Straße'), LENGTH(X'12FE')
602 // result: 6, 2
603 decl->defaultReturnType = KDbField::Integer;
604 _SIG(length_1, argAnyTextBLOBOrNull);
605
606 m_functions.insert(QLatin1String("LOWER"), decl = new BuiltInFunctionDeclaration);
607 // From https://www.sqlite.org/lang_corefunc.html
608 /* The lower(X) function returns a copy of string X with all characters converted
609 to lower case. */
610 // Note: SQLite such as 3.8 without ICU extension does not convert non-latin1 characters
611 // too well; Kexi uses ICU extension by default so the results are very good.
612 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_lower
613 // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
614 // example: SELECT LOWER("MEGSZENTSÉGTELENÍTHETETLENSÉGESKEDÉSEITEKÉRT")
615 // result: "megszentségteleníthetetlenségeskedéseitekért"
616 decl->defaultReturnType = KDbField::LongText;
617 _SIG(lower_1, argAnyTextOrNull);
618
619 m_functions.insert(QLatin1String("LTRIM"), decl = new BuiltInFunctionDeclaration);
620 // From https://www.sqlite.org/lang_corefunc.html
621 /* The ltrim(X,Y) function returns a string formed by removing any and all characters
622 that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X)
623 removes spaces from the left side of X.*/
624 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_ltrim
625 //! @todo MySQL's LTRIM only supports one arg. TRIM() does not work too
626 //! https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
627 // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
628 // example: SELECT LTRIM(" John Smith")
629 // result: "John Smith"
630 // example: SELECT LTRIM("a b or c", "ab ")
631 // result: "or c"
632 decl->defaultReturnType = KDbField::LongText;
633 _SIG(ltrim_1, argAnyTextOrNull);
634 _SIG(ltrim_2, argAnyTextOrNull, argAnyTextOrNull);
635
636 m_functions.insert(QLatin1String("NULLIF"), decl = new BuiltInFunctionDeclaration);
637 // From https://www.sqlite.org/lang_corefunc.html
638 /* The nullif(X,Y) function returns its first argument if the arguments are different
639 and NULL if the arguments are the same. The nullif(X,Y) function searches its
640 arguments from left to right for an argument that defines a collating function and
641 uses that collating function for all string comparisons. If neither argument to
642 nullif() defines a collating function then the BINARY is used. */
643 // See also https://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_nullif
644 // See also https://www.postgresql.org/docs/9.5/static/functions-conditional.html#FUNCTIONS-NULLIF
645 // example: SELECT NULLIF("John", "Smith"), NULLIF(177, 177)
646 // result: "John", NULL
647 decl->copyReturnTypeFromArg = 0;
648 _SIG(nullif_2, argAnyOrNull, argAnyOrNull);
649
650 m_functions.insert(QLatin1String("RANDOM"), decl = new RandomFunctionDeclaration);
651 /* RANDOM() returns a random floating-point value v in the range 0 <= v < 1.0.
652 RANDOM(X,Y) - returns returns a random integer that is equal or greater than X
653 and less than Y. */
654 // For MySQL RANDOM() is equal to RAND().
655 // For MySQL RANDOM(X,Y) is equal to (X + FLOOR(RAND() * (Y - X))
656 // For PostreSQL RANDOM() is equal to RANDOM().
657 // For PostreSQL RANDOM(X,Y) is equal to (X + FLOOR(RANDOM() * (Y - X))
658 // Because SQLite returns integer between -9223372036854775808 and +9223372036854775807,
659 // so RANDOM() for SQLite is equal to (RANDOM()+9223372036854775807)/18446744073709551615.
660 // Similarly, RANDOM(X,Y) for SQLite is equal
661 // to (X + CAST((Y - X) * (RANDOM()+9223372036854775807)/18446744073709551615 AS INT)).
662 // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_rand
663 // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-RANDOM-TABLE
664 //! @note rand(X) (where X is a seed value to set) isn't portable between MySQL and PostgreSQL,
665 //! and does not exist in SQLite, so we don't support it.
666 // example: SELECT RANDOM(), RANDOM(2, 5)
667 // result: (some random floating-point value v where 0 <= v < 1.0)
668 // example: SELECT RANDOM(2, 5)
669 // result: (some random integer value v where 2 <= v < 5)
670 decl->defaultReturnType = KDbField::Double;
671 _SIG0;
672 _SIG(random_2, argAnyIntOrNull, argAnyIntOrNull);
673
674 m_functions.insert(QLatin1String("ROUND"), decl = new BuiltInFunctionDeclaration);
675 // From https://www.sqlite.org/lang_corefunc.html
676 /* The round(X,Y) function returns a floating-point value X rounded to Y digits to the
677 right of the decimal point. If the Y argument is omitted, it is assumed to be 0. */
678 // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round
679 // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE
680 //! @note round(X,Y) where Y < 0 is supported only by MySQL so we ignore this case
681 // example: SELECT ROUND(-1.13), ROUND(-5.51), ROUND(5.51), ROUND(1.298, 1), ROUND(1.298, 0), ROUND(7)
682 // result: -1, -6, 6, 1.3, 1, 7
683 decl->copyReturnTypeFromArg = 0;
684 _SIG(round_1, argAnyNumberOrNull);
685 _SIG(round_2, argAnyNumberOrNull, argAnyIntOrNull);
686
687 m_functions.insert(QLatin1String("RTRIM"), decl = new BuiltInFunctionDeclaration);
688 // From https://www.sqlite.org/lang_corefunc.html
689 /* The rtrim(X,Y) function returns a string formed by removing any and all characters
690 that appear in Y from the right side of X. If the Y argument is omitted, rtrim(X)
691 removes spaces from the right side of X. */
692 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_ltrim
693 //! @todo MySQL's RTRIM only supports one arg. TRIM() does not work too
694 //! https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
695 // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
696 // example: SELECT RTRIM("John Smith ")
697 // result: "John Smith"
698 // example: SELECT RTRIM("a b or c", "orc ")
699 // result: "a b"
700 decl->defaultReturnType = KDbField::LongText;
701 _SIG(rtrim_1, argAnyTextOrNull);
702 _SIG(rtrim_2, argAnyTextOrNull, argAnyTextOrNull);
703
704 m_functions.insert(QLatin1String("SOUNDEX"), decl = new BuiltInFunctionDeclaration);
705 // From https://www.sqlite.org/lang_corefunc.html
706 /* The soundex(X) function returns a string that is the soundex encoding of the string
707 X. The string "?000" is returned if the argument is NULL or contains non-ASCII
708 alphabetic characters. */
709 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_soundex
710 // See also https://www.postgresql.org/docs/9.5/static/fuzzystrmatch.html#AEN165853
711 //! @todo we call drv_executeSql("CREATE EXTENSION IF NOT EXISTS fuzzystrmatch") on connection,
712 //! do that on first use of SOUNDEX()
713 // example: SELECT SOUNDEX("John")
714 // result: "J500"
715 decl->defaultReturnType = KDbField::Text;
716 _SIG(soundex, argAnyTextOrNull);
717
718 m_functions.insert(QLatin1String("SUBSTR"), decl = new BuiltInFunctionDeclaration);
719 // From https://www.sqlite.org/lang_corefunc.html
720 /* The substr(X,Y) returns all characters through the end of the string X beginning with
721 the Y-th. The left-most character of X is number 1. If Y is negative then the
722 first character of the substring is found by counting from the right rather than
723 the left. If Z is negative then the abs(Z) characters preceding the Y-th
724 character are returned. If X is a string then characters indices refer to actual
725 UTF-8 characters. If X is a BLOB then the indices refer to bytes. */
726 _SIG(substr_2, argAnyTextOrNull, argAnyIntOrNull);
727 /* The substr(X,Y,Z) function returns a substring of input string X that begins
728 with the Y-th character and which is Z characters long. */
729 _SIG(substr_3, argAnyTextOrNull, argAnyIntOrNull, argAnyIntOrNull);
730 decl->copyReturnTypeFromArg = 0;
731
732 m_functions.insert(QLatin1String("TRIM"), decl = new BuiltInFunctionDeclaration);
733 // From https://www.sqlite.org/lang_corefunc.html
734 /* The trim(X,Y) function returns a string formed by removing any and all characters
735 that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes
736 spaces from both ends of X. */
737 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
738 //! @todo MySQL's TRIM only supports one arg. TRIM() does not work too
739 //! https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
740 // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
741 // example: SELECT TRIM(" John Smith ")
742 // result: "John Smith"
743 // example: SELECT TRIM("a b or c", "orca ")
744 // result: "b"
745 decl->defaultReturnType = KDbField::LongText;
746 _SIG(trim_1, argAnyTextOrNull);
747 _SIG(trim_2, argAnyTextOrNull, argAnyTextOrNull);
748
749 m_functions.insert(QLatin1String("UNICODE"), decl = new BuiltInFunctionDeclaration);
750 // From https://www.sqlite.org/lang_corefunc.html
751 /* The unicode(X) function returns the numeric unicode code point corresponding to
752 the first character of the string X. If the argument to unicode(X) is not a string
753 then the result is undefined. */
754 // For MySQL ORD(CONVERT(X USING UTF16)) is used (ORD(X) returns a UTF-16 number)
755 // For PostreSQL ASCII(X) is used.
756 // example: SELECT UNICODE('A'), UNICODE('ą'), UNICODE('Δ'), UNICODE('葉')
757 // result: 65, 261, 916, 33865
758 decl->defaultReturnType = KDbField::Integer;
759 _SIG(unicode_1, argAnyTextOrNull);
760
761 m_functions.insert(QLatin1String("UPPER"), decl = new BuiltInFunctionDeclaration);
762 // From https://www.sqlite.org/lang_corefunc.html
763 /* The upper(X) function returns a copy of string X with all characters converted
764 to upper case. */
765 // Note: SQLite such as 3.8 without ICU extension does not convert non-latin1 characters
766 // too well; Kexi uses ICU extension by default so the results are very good.
767 // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_upper
768 // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
769 // example: SELECT UPPER("megszentségteleníthetetlenségeskedéseitekért")
770 // result: "MEGSZENTSÉGTELENÍTHETETLENSÉGESKEDÉSEITEKÉRT"
771 decl->defaultReturnType = KDbField::LongText;
772 _SIG(upper_1, argAnyTextOrNull);
773
774#ifdef KDB_ENABLE_SQLITE_SPECIFIC_FUNCTIONS
775 m_functions.insert(QLatin1String("GLOB"), decl = new BuiltInFunctionDeclaration);
776 //! @todo GLOB(X,Y) is SQLite-specific and is not present in MySQL so we don't expose it; use GLOB operator instead.
777 //! We may want to address it in raw SQL generation time.
778 // From https://www.sqlite.org/lang_corefunc.html
779 /* The glob(X,Y) function is equivalent to the expression "Y GLOB X". Note that the
780 X and Y arguments are reversed in the glob() function relative to the infix GLOB
781 operator. */
782 // example: SELECT GLOB("Foo*", "FooBar"), GLOB("Foo*", "foobar")
783 // result: TRUE, FALSE
784 decl->defaultReturnType = KDbField::Boolean;
785 _SIG(glob_2, argAnyTextOrNull, argAnyOrNull /* will be casted to text */);
786
787 m_functions.insert(QLatin1String("LIKE"), decl = new BuiltInFunctionDeclaration);
788 //! @todo LIKE(X,Y,[Z]) not present in MySQL so we don't expose it; use LIKE operator instead.
789 //! We may want to address it in raw SQL generation time.
790 // From https://www.sqlite.org/lang_corefunc.html
791 /* The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the
792 optional ESCAPE clause is present, then the like() function is invoked with three
793 arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y
794 parameters are reversed in the like() function relative to the infix LIKE operator.*/
795 decl->defaultReturnType = KDbField::Boolean;
796 _SIG(like_2, argAnyTextOrNull, argAnyTextOrNull);
797 _SIG(like_3, argAnyTextOrNull, argAnyTextOrNull, argAnyTextOrNull);
798#endif
799}
800
801BuiltInFunctionDeclaration* BuiltInFunctions::value(const QString &name) const
802{
803 BuiltInFunctionDeclaration* f = m_functions.value(name);
804 if (!f) {
805 f = m_aliases.value(name);
806 }
807 return f;
808}
809
810QStringList BuiltInFunctions::aliases() const
811{
812 return m_aliases.keys();
813}
814
815Q_GLOBAL_STATIC(BuiltInFunctions, _builtInFunctions)
816
817//=========================================
818
819KDbFunctionExpressionData::KDbFunctionExpressionData()
821{
822 ExpressionDebug << "FunctionExpressionData" << ref;
824}
825
826KDbFunctionExpressionData::KDbFunctionExpressionData(const QString& aName,
829 , name(aName)
830{
831 setArguments(arguments);
832 ExpressionDebug << "FunctionExpressionData" << ref << *args;
833}
834
835KDbFunctionExpressionData::~KDbFunctionExpressionData()
836{
837 ExpressionDebug << "~FunctionExpressionData" << ref;
838}
839
840KDbFunctionExpressionData* KDbFunctionExpressionData::clone()
841{
842 ExpressionDebug << "FunctionExpressionData::clone" << *this;
844 ExpressionDebug << "FunctionExpressionData::clone" << *cloned;
845 cloned->args = args->clone();
846 return cloned;
847}
848
850{
851 dbg.nospace() << "FunctionExp(" << name;
852 if (args.data()) {
853 dbg.nospace() << ',';
854 args.data()->debug(dbg, callStack);
855 }
857}
858
859static QByteArray greatestOrLeastName(const QByteArray &name)
860{
861 if (name == "MAX") {
862 return "GREATEST";
863 }
864 if (name == "MIN") {
865 return "LEAST";
866 }
867 return name;
868}
869
870KDbEscapedString KDbFunctionExpressionData::toStringInternal(
871 const KDbDriver *driver,
873 KDb::ExpressionCallStack* callStack) const
874{
875 KDbNArgExpressionData *argsData = args->convert<KDbNArgExpressionData>();
876 if (name == QLatin1String("HEX")) {
877 if (driver) {
878 return driver->hexFunctionToString(KDbNArgExpression(args), params, callStack);
879 }
880 }
881 else if (name == QLatin1String("IFNULL")) {
882 if (driver) {
883 return driver->ifnullFunctionToString(KDbNArgExpression(args), params, callStack);
884 }
885 }
886 else if (name == QLatin1String("LENGTH")) {
887 if (driver) {
888 return driver->lengthFunctionToString(KDbNArgExpression(args), params, callStack);
889 }
890 }
891 else if (name == QLatin1String("GREATEST") || name == QLatin1String("MAX")
892 || name == QLatin1String("LEAST") || name == QLatin1String("MIN"))
893 {
894 if (driver) {
895 return driver->greatestOrLeastFunctionToString(
896 QString::fromLatin1(greatestOrLeastName(name.toLatin1())), KDbNArgExpression(args), params, callStack);
897 }
898 // else: don't change MIN/MAX
899 }
900 else if (name == QLatin1String("RANDOM")) {
901 if (driver) {
902 return driver->randomFunctionToString(KDbNArgExpression(args), params, callStack);
903 }
904 }
905 else if (name == QLatin1String("CEILING") || name == QLatin1String("FLOOR")) {
906 if (driver) {
907 return driver->ceilingOrFloorFunctionToString(name, KDbNArgExpression(args), params, callStack);
908 }
909 }
910 else if (name == QLatin1String("UNICODE")) {
911 if (driver) {
912 return driver->unicodeFunctionToString(KDbNArgExpression(args), params, callStack);
913 }
914 }
915 return KDbFunctionExpressionData::toString(name, driver, argsData, params, callStack);
916}
917
918void KDbFunctionExpressionData::getQueryParameters(QList<KDbQuerySchemaParameter>* params)
919{
920 Q_ASSERT(params);
921 args->getQueryParameters(params);
922}
923
925{
926 Q_UNUSED(callStack);
927 const BuiltInFunctionDeclaration *decl = _builtInFunctions->value(name);
928 if (decl) {
929 return decl->returnType(this, nullptr);
930 }
931 //! @todo
933}
934
935static void setIncorrectNumberOfArgumentsErrorMessage(KDbParseInfo *parseInfo, int count,
936 const std::vector<int> &argCounts,
937 const QString &name)
938{
939 parseInfo->setErrorMessage(
940 KDbFunctionExpressionData::tr("Incorrect number of arguments (%1)").arg(count));
941 const int maxArgCount = argCounts[argCounts.size() - 1];
942 const int minArgCount = argCounts[0];
943 QString firstSentence;
944 if (count > maxArgCount) {
945 firstSentence = KDbFunctionExpressionData::tr("Too many arguments.%1", "don't use space before %1")
946 .arg(QLatin1String(" "));
947 }
948 if (count < minArgCount) {
949 firstSentence = KDbFunctionExpressionData::tr("Too few arguments.%1", "don't use space before %1")
950 .arg(QLatin1String(" "));
951 }
952 if (argCounts.size() == 1) {
953 const int c = argCounts[0];
954 if (c == 0) {
955 parseInfo->setErrorDescription(
956 KDbFunctionExpressionData::tr("%1%2() function does not accept any arguments.")
957 .arg(firstSentence, name));
958 }
959 else if (c == 1) {
960 parseInfo->setErrorDescription(
961 KDbFunctionExpressionData::tr("%1%2() function requires 1 argument.")
962 .arg(firstSentence, name));
963 }
964 else {
965 //~ singular %1%2() function requires %3 argument.
966 //~ plural %1%2() function requires %3 arguments.
967 parseInfo->setErrorDescription(
968 KDbFunctionExpressionData::tr("%1%2() function requires %3 argument(s).", "", c)
969 .arg(firstSentence, name).arg(c));
970 }
971 }
972 else if (argCounts.size() == 2) {
973 const int c1 = argCounts[0];
974 const int c2 = argCounts[1];
975 if (c2 == 1) {
976 parseInfo->setErrorDescription(
977 KDbFunctionExpressionData::tr("%1%2() function requires 0 or 1 argument.",
978 "the function requires zero or one argument")
979 .arg(firstSentence, name));
980 }
981 else {
982 //~ singular %1%2() function requires %3 or %4 argument.
983 //~ plural %1%2() function requires %3 or %4 arguments.
984 parseInfo->setErrorDescription(
985 KDbFunctionExpressionData::tr("%1%2() function requires %3 or %4 argument(s).", "", c2)
986 .arg(firstSentence, name).arg(c1).arg(c2));
987 }
988 }
989 else if (argCounts.size() == 3) {
990 //~ singular %1%2() function requires %3 or %4 or %5 argument.
991 //~ plural %1%2() function requires %3 or %4 or %5 arguments.
992 parseInfo->setErrorDescription(
993 KDbFunctionExpressionData::tr("%1%2() function requires %3 or %4 or %5 argument(s).", "", argCounts[2])
994 .arg(firstSentence, name).arg(argCounts[0])
995 .arg(argCounts[1]).arg(argCounts[2]));
996 }
997 else {
998 QString listCounts;
999 for(std::vector<int>::const_iterator it(argCounts.begin()); it != argCounts.end(); ++it) {
1000 if (listCounts.isEmpty()) {
1001 listCounts += QString::number(*it);
1002 } else {
1003 listCounts = KDbFunctionExpressionData::tr("%1 or %2").arg(listCounts).arg(*it);
1004 }
1005 }
1006 parseInfo->setErrorDescription(
1007 KDbFunctionExpressionData::tr("%1%2() function requires %3 argument(s).", "",
1008 argCounts[argCounts.size() - 1])
1009 .arg(firstSentence, name, listCounts));
1010 }
1011}
1012
1013static void setIncorrectTypeOfArgumentsErrorMessage(KDbParseInfo *parseInfo, int argNum,
1014 KDbField::Type type,
1015 int *argTypes, const QString &name)
1016{
1017 QString listTypes;
1018 int *argType = argTypes;
1019 while(*argType != KDbField::InvalidType) {
1020 if (!listTypes.isEmpty()) {
1021 listTypes += KDbFunctionExpressionData::tr(" or ");
1022 }
1023 const KDbField::Type realFieldType = KDb::intToFieldType(*argType);
1024 if (realFieldType != KDbField::InvalidType) {
1025 listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1026 .arg(KDbField::typeName(realFieldType));
1027 }
1028 else if (*argType == KDbField::Null) {
1029 listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1031 }
1032 else if (*argType == AnyText) {
1033 listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1035 }
1036 else if (*argType == AnyInt) {
1037 listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1039 }
1040 else if (*argType == AnyFloat) {
1041 listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1042 .arg(KDbField::typeGroupName(KDbField::FloatGroup));
1043 // better than typeName() in this case
1044 }
1045 else if (*argType == AnyNumber) {
1046 listTypes += KDbFunctionExpressionData::tr("\"Number\"");
1047 }
1048 else if (*argType == Any) {
1049 listTypes += KDbFunctionExpressionData::tr("\"Any\"", "Any data type");
1050 }
1051 ++argType;
1052 }
1053 parseInfo->setErrorMessage(KDbFunctionExpressionData::tr("Incorrect type of argument"));
1054 QString lastSentence
1055 = KDbFunctionExpressionData::tr("Specified argument is of type \"%1\".")
1056 .arg(KDbField::typeName(type));
1057 if (argNum == 0) {
1058 parseInfo->setErrorDescription(
1059 KDbFunctionExpressionData::tr("%1() function's first argument should be of type %2. %3")
1060 .arg(name, listTypes, lastSentence));
1061 }
1062 else if (argNum == 1) {
1063 parseInfo->setErrorDescription(
1064 KDbFunctionExpressionData::tr("%1() function's second argument should be of type %2. %3")
1065 .arg(name, listTypes, lastSentence));
1066 }
1067 else if (argNum == 2) {
1068 parseInfo->setErrorDescription(
1069 KDbFunctionExpressionData::tr("%1() function's third argument should be of type %2. %3")
1070 .arg(name, listTypes, lastSentence));
1071 }
1072 else if (argNum == 3) {
1073 parseInfo->setErrorDescription(
1074 KDbFunctionExpressionData::tr("%1() function's fourth argument should be of type %2. %3")
1075 .arg(name, listTypes, lastSentence));
1076 }
1077 else if (argNum == 4) {
1078 parseInfo->setErrorDescription(
1079 KDbFunctionExpressionData::tr("%1() function's fifth argument should be of type %2. %3")
1080 .arg(name, listTypes, lastSentence));
1081 }
1082 else {
1083 parseInfo->setErrorDescription(
1084 KDbFunctionExpressionData::tr("%1() function's %2 argument should be of type %3. %4")
1085 .arg(name).arg(argNum + 1).arg(listTypes, lastSentence));
1086 }
1087}
1088
1089//! @return true if type rule @a argType matches concrete type @a actualType
1090static bool typeMatches(int argType, KDbField::Type actualType)
1091{
1092 if (argType == AnyText) {
1093 if (KDbField::isTextType(actualType)) {
1094 return true;
1095 }
1096 }
1097 else if (argType == AnyInt) {
1098 if (KDbField::isIntegerType(actualType)) {
1099 return true;
1100 }
1101 }
1102 else if (argType == AnyFloat) {
1103 if (KDbField::isFPNumericType(actualType)) {
1104 return true;
1105 }
1106 }
1107 else if (argType == AnyNumber) {
1108 if (KDbField::isNumericType(actualType)) {
1109 return true;
1110 }
1111 }
1112 else if (argType == Any) {
1113 return true;
1114 }
1115 else {
1116 if (argType == actualType) {
1117 return true;
1118 }
1119 }
1120 return false;
1121}
1122
1123static int findMatchingType(int *argTypePtr, KDbField::Type actualType)
1124{
1125 for (; *argTypePtr != KDbField::InvalidType; ++argTypePtr) {
1126 if (typeMatches(*argTypePtr, actualType)) {
1127 break;
1128 }
1129 }
1130 return *argTypePtr;
1131}
1132
1134 KDb::ExpressionCallStack* callStack)
1135{
1136 if (!args->validate(parseInfo, callStack)) {
1137 return false;
1138 }
1139 if (args->token != ',') { // arguments required: NArgExpr with token ','
1140 return false;
1141 }
1142 if (args->children.count() > KDB_MAX_FUNCTION_ARGS) {
1143 parseInfo->setErrorMessage(
1144 tr("Too many arguments for function."));
1145 parseInfo->setErrorDescription(
1146 tr("Maximum number of arguments for function %1() is %2.")
1147 .arg(args->children.count()).arg(KDB_MAX_FUNCTION_ARGS));
1148 return false;
1149 }
1150 if (!args->validate(parseInfo)) {
1151 return false;
1152 }
1153 if (name.isEmpty()) {
1154 return false;
1155 }
1156 const BuiltInFunctionDeclaration *decl = _builtInFunctions->value(name);
1157 if (!decl) {
1158 return false;
1159 }
1160 const KDbNArgExpressionData *argsData = args->convertConst<KDbNArgExpressionData>();
1161 if (argsData->containsInvalidArgument()) {
1162 return false;
1163 }
1164
1165 // Find matching signature
1166 int **signature = nullptr;
1167 bool multipleArgs = false; // special case, e.g. for CHARS(v1, ... vN)
1168 {
1169 const int count = args->children.count();
1170 bool properArgCount = false;
1171 std::vector<int> argCounts;
1172 int i = 0;
1173 argCounts.resize(decl->signatures.size());
1174 for(std::vector<int**>::const_iterator it(decl->signatures.begin());
1175 it != decl->signatures.end(); ++it, ++i)
1176 {
1177 signature = *it;
1178 int **arg = signature;
1179 int expectedCount = 0;
1180 while(*arg && *arg != BuiltInFunctions::multipleArgs) {
1181 ++arg;
1182 ++expectedCount;
1183 }
1184 multipleArgs = *arg == BuiltInFunctions::multipleArgs;
1185 if (multipleArgs) {
1186 ++arg;
1187 const int minArgs = arg[0][0];
1188 properArgCount = count >= minArgs;
1189 if (!properArgCount) {
1190 parseInfo->setErrorMessage(
1191 tr("Incorrect number of arguments (%1)").arg(count));
1192 if (minArgs == 1) {
1193 parseInfo->setErrorDescription(
1194 tr("Too few arguments. %1() function requires "
1195 "at least one argument.").arg(name));
1196 }
1197 else if (minArgs == 2) {
1198 parseInfo->setErrorDescription(
1199 tr("Too few arguments. %1() function requires "
1200 "at least two arguments.").arg(name));
1201 }
1202 else if (minArgs == 3) {
1203 parseInfo->setErrorDescription(
1204 tr("Too few arguments. %1() function requires "
1205 "at least three arguments.").arg(name));
1206 }
1207 else {
1208 parseInfo->setErrorDescription(
1209 tr("Too few arguments. %1() function requires "
1210 "at least %2 arguments.").arg(name).arg(minArgs));
1211 }
1212 return false;
1213 }
1214 break;
1215 }
1216 else if (count == expectedCount) { // arg # matches
1217 properArgCount = true;
1218 break;
1219 }
1220 else {
1221 argCounts[i] = expectedCount;
1222 }
1223 }
1224 if (!properArgCount) {
1225 const std::vector<int>::iterator last = std::unique(argCounts.begin(), argCounts.end());
1226 argCounts.erase(last, argCounts.end());
1227 std::sort(argCounts.begin(), argCounts.end()); // sort so we can easier check the case
1228 setIncorrectNumberOfArgumentsErrorMessage(parseInfo, count, argCounts, name);
1229 return false;
1230 }
1231 }
1232
1233 // Verify types
1234 if (multipleArgs) { // special signature: {typesForAllArgs, [multipleArgs-token], MIN, 0}
1235 int **arg = signature;
1236 int *typesForAllArgs = arg[0];
1237 int i = 0;
1238 foreach(const ExplicitlySharedExpressionDataPointer &expr, args->children) {
1239 const KDbField::Type exprType = expr->type(); // cache: evaluating type of expressions can be expensive
1240 const bool isQueryParameter = expr->convertConst<KDbQueryParameterExpressionData>();
1241 if (!isQueryParameter) { // (query parameter always matches)
1242 const int matchingType = findMatchingType(typesForAllArgs, exprType);
1243 if (matchingType == KDbField::InvalidType) {
1244 setIncorrectTypeOfArgumentsErrorMessage(parseInfo, i, exprType, typesForAllArgs, name);
1245 return false;
1246 }
1247 }
1248 ++i;
1249 }
1250 }
1251 else { // typical signature: array of type-lists
1252 int **arg = signature;
1253 int i=0;
1254 foreach(const ExplicitlySharedExpressionDataPointer &expr, args->children) {
1255 const KDbField::Type exprType = expr->type(); // cache: evaluating type of expressions can be expensive
1256 const bool isQueryParameter = expr->convertConst<KDbQueryParameterExpressionData>();
1257 if (!isQueryParameter) { // (query parameter always matches)
1258 const int matchingType = findMatchingType(arg[0], exprType);
1259 if (matchingType == KDbField::InvalidType) {
1260 setIncorrectTypeOfArgumentsErrorMessage(parseInfo, i, exprType, arg[0], name);
1261 return false;
1262 }
1263 }
1264 ++arg;
1265 ++i;
1266 }
1267 }
1268
1269 // Check type just now. If we checked earlier, possible error message would be less informative.
1270 if (decl->returnType(this, parseInfo) == KDbField::InvalidType) {
1271 return false;
1272 }
1273 return true;
1274}
1275
1276void KDbFunctionExpressionData::setArguments(ExplicitlySharedExpressionDataPointer arguments)
1277{
1278 args = (arguments && arguments->convert<KDbNArgExpressionData>())
1280 children.append(args);
1281 args->parent = this;
1282 args->token = ',';
1283 args->expressionClass = KDb::ArgumentListExpression;
1284}
1285
1286//static
1287KDbEscapedString KDbFunctionExpressionData::toString(
1288 const QString &name,
1289 const KDbDriver *driver,
1290 const KDbNArgExpressionData *args,
1292 KDb::ExpressionCallStack* callStack)
1293{
1294 return KDbEscapedString(name + QLatin1Char('('))
1295 + args->toString(driver, params, callStack)
1296 + KDbEscapedString(')');
1297}
1298
1299//=========================================
1300
1301inline KDb::ExpressionClass classForFunctionName(const QString& name)
1302{
1303 if (KDbFunctionExpression::isBuiltInAggregate(name))
1304 return KDb::AggregationExpression;
1305 else
1306 return KDb::FunctionExpression;
1307}
1308
1311{
1312 ExpressionDebug << "KDbFunctionExpression() ctor" << *this;
1313}
1314
1317 classForFunctionName(name), KDbToken()/*undefined*/)
1318{
1319}
1320
1322 const KDbNArgExpression& arguments)
1323 : KDbExpression(new KDbFunctionExpressionData(name.toUpper(), arguments.d),
1324 classForFunctionName(name), KDbToken()/*undefined*/)
1325{
1326}
1327
1332
1334 : KDbExpression(data)
1335{
1336 ExpressionDebug << "KDbFunctionExpression ctor (KDbExpressionData*)" << *this;
1337}
1338
1340 : KDbExpression(ptr)
1341{
1342}
1343
1344KDbFunctionExpression::~KDbFunctionExpression()
1345{
1346}
1347
1348// static
1349bool KDbFunctionExpression::isBuiltInAggregate(const QString& function)
1350{
1351 return _builtInAggregates->data.contains(function.toUpper());
1352}
1353
1354// static
1355QStringList KDbFunctionExpression::builtInAggregates()
1356{
1357 return _builtInAggregates->data.values();
1358}
1359
1360//static
1362 const QString &name,
1363 const KDbDriver *driver,
1364 const KDbNArgExpression& args,
1366 KDb::ExpressionCallStack* callStack)
1367{
1368 const KDbNArgExpressionData *argsData = args.d.constData()->convertConst<KDbNArgExpressionData>();
1369 return KDbFunctionExpressionData::toString(name, driver, argsData, params, callStack);
1370}
1371
1373{
1374 return d->convert<KDbFunctionExpressionData>()->name;
1375}
1376
1378{
1379 d->convert<KDbFunctionExpressionData>()->name = name;
1380}
1381
1386
1391
1392// static
1394 const QString &name,
1395 const KDbDriver *driver,
1396 const KDbNArgExpression &args,
1398 KDb::ExpressionCallStack* callStack)
1399{
1400 // (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE F(v0,..,vN) END)
1401 if (args.argCount() >= 2) {
1402 KDbEscapedString whenSql;
1403 whenSql.reserve(256);
1404 foreach(const ExplicitlySharedExpressionDataPointer &child, args.d.constData()->children) {
1405 if (!whenSql.isEmpty()) {
1406 whenSql += " OR ";
1407 }
1408 whenSql += QLatin1Char('(') + child->toString(driver, params, callStack)
1409 + QLatin1String(") IS NULL");
1410 }
1411 return KDbEscapedString("(CASE WHEN (") + whenSql
1412 + QLatin1String(") THEN NULL ELSE (")
1413 + KDbFunctionExpression::toString(name, driver, args, params, callStack)
1414 + QLatin1String(") END)");
1415 }
1416 return KDbFunctionExpression::toString(name, driver, args, params, callStack);
1417}
Internal data class used to implement implicitly shared class KDbConstExpression.
Database driver's abstraction.
Definition KDbDriver.h:50
virtual KDbEscapedString greatestOrLeastFunctionToString(const QString &name, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) GREATEST() and LEAST() function calls.
static QString defaultSqlTypeName(KDbField::Type type)
virtual KDbEscapedString ifnullFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) IFNULL() function call.
virtual KDbEscapedString randomFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) RANDOM() and RANDOM(X,Y) function calls.
virtual KDbEscapedString lengthFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) LENGTH() function call.
virtual KDbEscapedString hexFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) HEX() function call.
virtual KDbEscapedString ceilingOrFloorFunctionToString(const QString &name, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) CEILING() and FLOOR() function calls.
virtual KDbEscapedString unicodeFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) UNICODE() function call.
Specialized string for escaping.
Internal data class used to implement implicitly shared class KDbExpression.
KDbField::Type type() const
The KDbExpression class represents a base class for all expressions.
ExplicitlySharedExpressionDataPointer d
Meta-data for a field.
Definition KDbField.h:72
bool isNumericType() const
Definition KDbField.h:317
bool isTextType() const
Definition KDbField.h:353
QString typeName() const
Definition KDbField.h:377
@ Integer
Definition KDbField.h:90
@ Boolean
Definition KDbField.h:92
@ ShortInteger
Definition KDbField.h:89
@ InvalidType
Definition KDbField.h:86
@ BigInteger
Definition KDbField.h:91
@ LongText
Definition KDbField.h:99
bool isFPNumericType() const
Definition KDbField.h:335
bool isIntegerType() const
Definition KDbField.h:326
QString typeGroupName() const
Definition KDbField.h:387
TypeGroup typeGroup() const
Definition KDbField.h:382
Internal data class used to implement implicitly shared class KDbFunctionExpression.
bool validateInternal(KDbParseInfo *parseInfo, KDb::ExpressionCallStack *callStack) override
KDbField::Type typeInternal(KDb::ExpressionCallStack *callStack) const override
void debugInternal(QDebug dbg, KDb::ExpressionCallStack *callStack) const override
Sends information about this expression to debug output dbg (internal).
The KDbFunctionExpression class represents expression that use functional notation F(x,...
static KDbEscapedString greatestOrLeastFunctionUsingCaseToString(const QString &name, const KDbDriver *driver, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack)
static KDbEscapedString toString(const QString &name, const KDbDriver *driver, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack)
void setArguments(const KDbNArgExpression &arguments)
Sets the list of arguments to arguments.
void setName(const QString &name)
Sets name of the function to name.
Internal data class used to implement implicitly shared class KDbNArgExpression.
The KDbNArgExpression class represents a base class N-argument expression.
Internal data class used to implement implicitly shared class KDbQueryParameterExpression.
An iterator for a list of values of query schema parameters Allows to iterate over parameters and ret...
A type-safe KDbSQL token It can be used in KDb expressions.
Definition KDbToken.h:37
Type type(const QSqlDatabase &db)
KDB_EXPORT KDbField::Type maximumForIntegerFieldTypes(KDbField::Type t1, KDbField::Type t2)
KDB_EXPORT KDbField::Type intToFieldType(int type)
Definition KDb.cpp:670
ExpressionClass
Classes of expressions.
QString name(StandardAction id)
QDebug & nospace()
const T * constData() const const
iterator insert(const Key &key, const T &value)
QList< Key > keys() const const
T value(const Key &key) const const
pointer data()
QString arg(Args &&... args) const const
QString fromLatin1(QByteArrayView str)
bool isEmpty() const const
QString number(double n, char format, int precision)
QByteArray toLatin1() const const
QString toUpper() const const
qlonglong toLongLong(bool *ok) const const
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Mon Nov 18 2024 12:19:07 by doxygen 1.12.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.