KDb

KDbFunctionExpression.cpp
1 /* This file is part of the KDE project
2  Copyright (C) 2003-2016 Jarosław Staniek <[email protected]>
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
40 class BuiltInAggregates
41 {
42 public:
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 
58 Q_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.
62 enum 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
72 static 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  }
83  return KDbField::InvalidType;
84 }
85 
86 //! Declaration of a single built-in function. It can offer multiple signatures.
87 class BuiltInFunctionDeclaration
88 {
89 public:
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)
104  ->convert<KDbQueryParameterExpressionData>();
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;
122 protected:
123  KDbField::Type defaultReturnType;
124  int copyReturnTypeFromArg;
125  friend class BuiltInFunctions;
126 private:
127  Q_DISABLE_COPY(BuiltInFunctionDeclaration)
128 };
129 
130 //! Declaration of a single built-in function COALESCE() and similar ones.
131 class CoalesceFunctionDeclaration : public BuiltInFunctionDeclaration
132 {
133 public:
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  }
158 private:
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
167 class MinMaxFunctionDeclaration : public BuiltInFunctionDeclaration
168 {
169  Q_DECLARE_TR_FUNCTIONS(MinMaxFunctionDeclaration)
170 public:
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  }
222  return KDbField::InvalidType;
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  }
238 private:
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  }
253  return KDbField::InvalidType;
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  }
266  return KDbField::InvalidType;
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
276 class RandomFunctionDeclaration : public BuiltInFunctionDeclaration
277 {
278  Q_DECLARE_TR_FUNCTIONS(RandomFunctionDeclaration)
279 public:
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  }
303  return KDbField::InvalidType;
304  }
305  }
306  }
307  KDbField::Type t0;
308  KDbField::Type t1;
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;
317  t0 = KDbField::Integer;
318  t1 = 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  }
331  return KDb::maximumForIntegerFieldTypes(t0, t1);
332  }
333  return KDbField::InvalidType;
334  }
335 private:
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
343 class CeilingFloorFunctionDeclaration : public BuiltInFunctionDeclaration
344 {
345 public:
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;
357  return KDbField::BigInteger;
358  }
359  const KDbField::Type type = argsData->children.at(0)->type(); // cache: evaluating type of expressions can be expensive
360  if (KDbField::isFPNumericType(type)) {
361  return KDbField::BigInteger;
362  }
363  switch (type) {
367  case KDbField::Null: return KDbField::Null;
369  default:;
370  }
371  }
372  return KDbField::InvalidType;
373  }
374 private:
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.
380 class BuiltInFunctions
381 {
382 public:
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[];
398 private:
401  Q_DISABLE_COPY(BuiltInFunctions)
402 };
403 
404 int BuiltInFunctions::multipleArgs[] = { 0 };
405 
406 BuiltInFunctions::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 
801 BuiltInFunctionDeclaration* 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 
810 QStringList BuiltInFunctions::aliases() const
811 {
812  return m_aliases.keys();
813 }
814 
815 Q_GLOBAL_STATIC(BuiltInFunctions, _builtInFunctions)
816 
817 //=========================================
818 
819 KDbFunctionExpressionData::KDbFunctionExpressionData()
821 {
822  ExpressionDebug << "FunctionExpressionData" << ref;
824 }
825 
826 KDbFunctionExpressionData::KDbFunctionExpressionData(const QString& aName,
829  , name(aName)
830 {
831  setArguments(arguments);
832  ExpressionDebug << "FunctionExpressionData" << ref << *args;
833 }
834 
835 KDbFunctionExpressionData::~KDbFunctionExpressionData()
836 {
837  ExpressionDebug << "~FunctionExpressionData" << ref;
838 }
839 
840 KDbFunctionExpressionData* 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 
859 static 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 
870 KDbEscapedString 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 
918 void 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
932  return KDbField::InvalidType;
933 }
934 
935 static 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 
1013 static 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
1090 static 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 
1123 static 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 
1133 bool KDbFunctionExpressionData::validateInternal(KDbParseInfo *parseInfo,
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  const int count = args->children.count();
1167  bool properArgCount = false;
1168  std::vector<int> argCounts;
1169  int i = 0;
1170  argCounts.resize(decl->signatures.size());
1171  int **signature = nullptr;
1172  bool multipleArgs = false; // special case, e.g. for CHARS(v1, ... vN)
1173  for(std::vector<int**>::const_iterator it(decl->signatures.begin());
1174  it != decl->signatures.end(); ++it, ++i)
1175  {
1176  signature = *it;
1177  int **arg = signature;
1178  int expectedCount = 0;
1179  while(*arg && *arg != BuiltInFunctions::multipleArgs) {
1180  ++arg;
1181  ++expectedCount;
1182  }
1183  multipleArgs = *arg == BuiltInFunctions::multipleArgs;
1184  if (multipleArgs) {
1185  ++arg;
1186  const int minArgs = arg[0][0];
1187  properArgCount = count >= minArgs;
1188  if (!properArgCount) {
1189  parseInfo->setErrorMessage(
1190  tr("Incorrect number of arguments (%1)").arg(count));
1191  if (minArgs == 1) {
1192  parseInfo->setErrorDescription(
1193  tr("Too few arguments. %1() function requires "
1194  "at least one argument.").arg(name));
1195  }
1196  else if (minArgs == 2) {
1197  parseInfo->setErrorDescription(
1198  tr("Too few arguments. %1() function requires "
1199  "at least two arguments.").arg(name));
1200  }
1201  else if (minArgs == 3) {
1202  parseInfo->setErrorDescription(
1203  tr("Too few arguments. %1() function requires "
1204  "at least three arguments.").arg(name));
1205  }
1206  else {
1207  parseInfo->setErrorDescription(
1208  tr("Too few arguments. %1() function requires "
1209  "at least %2 arguments.").arg(name).arg(minArgs));
1210  }
1211  return false;
1212  }
1213  break;
1214  }
1215  else if (count == expectedCount) { // arg # matches
1216  properArgCount = true;
1217  break;
1218  }
1219  else {
1220  argCounts[i] = expectedCount;
1221  }
1222  }
1223  if (!properArgCount) {
1224  std::unique(argCounts.begin(), argCounts.end());
1225  std::sort(argCounts.begin(), argCounts.end()); // sort so we can easier check the case
1226  setIncorrectNumberOfArgumentsErrorMessage(parseInfo, count, argCounts, name);
1227  return false;
1228  }
1229 
1230  // Verify types
1231  if (multipleArgs) { // special signature: {typesForAllArgs, [multipleArgs-token], MIN, 0}
1232  int **arg = signature;
1233  int *typesForAllArgs = arg[0];
1234  int i = 0;
1235  foreach(const ExplicitlySharedExpressionDataPointer &expr, args->children) {
1236  const KDbField::Type exprType = expr->type(); // cache: evaluating type of expressions can be expensive
1237  const bool isQueryParameter = expr->convertConst<KDbQueryParameterExpressionData>();
1238  if (!isQueryParameter) { // (query parameter always matches)
1239  const int matchingType = findMatchingType(typesForAllArgs, exprType);
1240  if (matchingType == KDbField::InvalidType) {
1241  setIncorrectTypeOfArgumentsErrorMessage(parseInfo, i, exprType, typesForAllArgs, name);
1242  return false;
1243  }
1244  }
1245  ++i;
1246  }
1247  }
1248  else { // typical signature: array of type-lists
1249  int **arg = signature;
1250  int i=0;
1251  foreach(const ExplicitlySharedExpressionDataPointer &expr, args->children) {
1252  const KDbField::Type exprType = expr->type(); // cache: evaluating type of expressions can be expensive
1253  const bool isQueryParameter = expr->convertConst<KDbQueryParameterExpressionData>();
1254  if (!isQueryParameter) { // (query parameter always matches)
1255  const int matchingType = findMatchingType(arg[0], exprType);
1256  if (matchingType == KDbField::InvalidType) {
1257  setIncorrectTypeOfArgumentsErrorMessage(parseInfo, i, exprType, arg[0], name);
1258  return false;
1259  }
1260  }
1261  ++arg;
1262  ++i;
1263  }
1264  }
1265 
1266  // Check type just now. If we checked earlier, possible error message would be less informative.
1267  if (decl->returnType(this, parseInfo) == KDbField::InvalidType) {
1268  return false;
1269  }
1270  return true;
1271 }
1272 
1273 void KDbFunctionExpressionData::setArguments(ExplicitlySharedExpressionDataPointer arguments)
1274 {
1275  args = (arguments && arguments->convert<KDbNArgExpressionData>())
1277  children.append(args);
1278  args->parent = this;
1279  args->token = ',';
1280  args->expressionClass = KDb::ArgumentListExpression;
1281 }
1282 
1283 //static
1284 KDbEscapedString KDbFunctionExpressionData::toString(
1285  const QString &name,
1286  const KDbDriver *driver,
1287  const KDbNArgExpressionData *args,
1289  KDb::ExpressionCallStack* callStack)
1290 {
1291  return KDbEscapedString(name + QLatin1Char('('))
1292  + args->toString(driver, params, callStack)
1293  + KDbEscapedString(')');
1294 }
1295 
1296 //=========================================
1297 
1298 inline KDb::ExpressionClass classForFunctionName(const QString& name)
1299 {
1300  if (KDbFunctionExpression::isBuiltInAggregate(name))
1301  return KDb::AggregationExpression;
1302  else
1303  return KDb::FunctionExpression;
1304 }
1305 
1308 {
1309  ExpressionDebug << "KDbFunctionExpression() ctor" << *this;
1310 }
1311 
1314  classForFunctionName(name), KDbToken()/*undefined*/)
1315 {
1316 }
1317 
1319  const KDbNArgExpression& arguments)
1320  : KDbExpression(new KDbFunctionExpressionData(name.toUpper(), arguments.d),
1321  classForFunctionName(name), KDbToken()/*undefined*/)
1322 {
1323 }
1324 
1326  : KDbExpression(expr)
1327 {
1328 }
1329 
1331  : KDbExpression(data)
1332 {
1333  ExpressionDebug << "KDbFunctionExpression ctor (KDbExpressionData*)" << *this;
1334 }
1335 
1337  : KDbExpression(ptr)
1338 {
1339 }
1340 
1341 KDbFunctionExpression::~KDbFunctionExpression()
1342 {
1343 }
1344 
1345 // static
1346 bool KDbFunctionExpression::isBuiltInAggregate(const QString& function)
1347 {
1348  return _builtInAggregates->data.contains(function.toUpper());
1349 }
1350 
1351 // static
1352 QStringList KDbFunctionExpression::builtInAggregates()
1353 {
1354  return _builtInAggregates->data.toList();
1355 }
1356 
1357 //static
1359  const QString &name,
1360  const KDbDriver *driver,
1361  const KDbNArgExpression& args,
1363  KDb::ExpressionCallStack* callStack)
1364 {
1365  const KDbNArgExpressionData *argsData = args.d.constData()->convertConst<KDbNArgExpressionData>();
1366  return KDbFunctionExpressionData::toString(name, driver, argsData, params, callStack);
1367 }
1368 
1370 {
1371  return d->convert<KDbFunctionExpressionData>()->name;
1372 }
1373 
1375 {
1376  d->convert<KDbFunctionExpressionData>()->name = name;
1377 }
1378 
1380 {
1381  return KDbNArgExpression(d->convert<KDbFunctionExpressionData>()->args);
1382 }
1383 
1385 {
1387 }
1388 
1389 // static
1391  const QString &name,
1392  const KDbDriver *driver,
1393  const KDbNArgExpression &args,
1395  KDb::ExpressionCallStack* callStack)
1396 {
1397  // (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE F(v0,..,vN) END)
1398  if (args.argCount() >= 2) {
1399  KDbEscapedString whenSql;
1400  whenSql.reserve(256);
1401  foreach(const ExplicitlySharedExpressionDataPointer &child, args.d.constData()->children) {
1402  if (!whenSql.isEmpty()) {
1403  whenSql += " OR ";
1404  }
1405  whenSql += QLatin1Char('(') + child->toString(driver, params, callStack)
1406  + QLatin1String(") IS NULL");
1407  }
1408  return KDbEscapedString("(CASE WHEN (") + whenSql
1409  + QLatin1String(") THEN NULL ELSE (")
1410  + KDbFunctionExpression::toString(name, driver, args, params, callStack)
1411  + QLatin1String(") END)");
1412  }
1413  return KDbFunctionExpression::toString(name, driver, args, params, callStack);
1414 }
KDbField::Type typeInternal(KDb::ExpressionCallStack *callStack) const override
Internal data class used to implement implicitly shared class KDbExpression.
QString number(int n, int base)
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:36
@ InvalidType
Definition: KDbField.h:86
Type type(const QSqlDatabase &db)
@ Text
Definition: KDbField.h:98
void ref()
QDebug & nospace()
KDbField::Type type() const
Specialized string for escaping.
Database driver's abstraction.
Definition: KDbDriver.h:49
QByteArray toLatin1() const const
Internal data class used to implement implicitly shared class KDbQueryParameterExpression.
qlonglong toLongLong(bool *ok) const const
void setName(const QString &name)
Sets name of the function to name.
bool isFPNumericType() const
Definition: KDbField.h:335
bool validateInternal(KDbParseInfo *parseInfo, KDb::ExpressionCallStack *callStack) override
virtual KDbEscapedString ifnullFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) IFNULL() function call.
Definition: KDbDriver.cpp:325
@ LastType
Definition: KDbField.h:102
Q_GLOBAL_STATIC(Internal::StaticControl, s_instance) class ControlPrivate
static QString defaultSqlTypeName(KDbField::Type type)
Definition: KDbDriver.cpp:164
KDB_EXPORT KDbField::Type intToFieldType(int type)
Definition: KDb.cpp:670
QString typeName() const
Definition: KDbField.h:377
virtual KDbEscapedString randomFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) RANDOM() and RANDOM(X,Y) function calls.
Definition: KDbDriver.cpp:350
bool isTextType() const
Definition: KDbField.h:353
bool isIntegerType() const
Definition: KDbField.h:326
@ Integer
Definition: KDbField.h:90
Internal data class used to implement implicitly shared class KDbConstExpression.
bool isEmpty() const const
TypeGroup typeGroup() const
Definition: KDbField.h:382
KDB_EXPORT KDbField::Type maximumForIntegerFieldTypes(KDbField::Type t1, KDbField::Type t2)
Definition: KDb.cpp:1944
ExplicitlySharedExpressionDataPointer d
static KDbEscapedString greatestOrLeastFunctionUsingCaseToString(const QString &name, const KDbDriver *driver, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack)
@ BigInteger
Definition: KDbField.h:91
ExpressionClass
Classes of expressions.
bool isNumericType() const
Definition: KDbField.h:317
virtual KDbEscapedString greatestOrLeastFunctionToString(const QString &name, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) GREATEST() and LEAST() function calls.
Definition: KDbDriver.cpp:341
void setArguments(const KDbNArgExpression &arguments)
Sets the list of arguments to arguments.
Internal data class used to implement implicitly shared class KDbNArgExpression.
@ Double
Definition: KDbField.h:97
@ LongText
Definition: KDbField.h:99
@ Byte
Definition: KDbField.h:87
The KDbNArgExpression class represents a base class N-argument expression.
QString typeGroupName() const
Definition: KDbField.h:387
void debugInternal(QDebug dbg, KDb::ExpressionCallStack *callStack) const override
Sends information about this expression to debug output dbg (internal).
@ Boolean
Definition: KDbField.h:92
virtual KDbEscapedString unicodeFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) UNICODE() function call.
Definition: KDbDriver.cpp:380
QString arg(qlonglong a, int fieldWidth, int base, QChar fillChar) const const
QString fromLatin1(const char *str, int size)
QString name(StandardShortcut id)
static KDbEscapedString toString(const QString &name, const KDbDriver *driver, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack)
virtual KDbEscapedString lengthFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) LENGTH() function call.
Definition: KDbDriver.cpp:333
Meta-data for a field.
Definition: KDbField.h:71
Internal data class used to implement implicitly shared class KDbFunctionExpression.
const T * constData() const const
The KDbExpression class represents a base class for all expressions.
Definition: KDbExpression.h:51
The KDbFunctionExpression class represents expression that use functional notation F(x,...
@ ShortInteger
Definition: KDbField.h:89
virtual KDbEscapedString hexFunctionToString(const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) HEX() function call.
Definition: KDbDriver.cpp:317
virtual KDbEscapedString ceilingOrFloorFunctionToString(const QString &name, const KDbNArgExpression &args, KDbQuerySchemaParameterValueListIterator *params, KDb::ExpressionCallStack *callStack) const
Generates native (driver-specific) CEILING() and FLOOR() function calls.
Definition: KDbDriver.cpp:371
This file is part of the KDE documentation.
Documentation copyright © 1996-2022 The KDE developers.
Generated on Sat Jun 25 2022 06:21:33 by doxygen 1.8.17 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.