$darkmode
Qore SqlUtil Module Reference 1.9.1
SqlUtil.qm.dox.h
1 // -*- mode: c++; indent-tabs-mode: nil -*-
3 
4 /* SqlUtil.qm Copyright (C) 2013 - 2023 Qore Technologies, s.r.o.
5 
6  Permission is hereby granted, free of charge, to any person obtaining a
7  copy of this software and associated documentation files (the "Software"),
8  to deal in the Software without restriction, including without limitation
9  the rights to use, copy, modify, merge, publish, distribute, sublicense,
10  and/or sell copies of the Software, and to permit persons to whom the
11  Software is furnished to do so, subject to the following conditions:
12 
13  The above copyright notice and this permission notice shall be included in
14  all copies or substantial portions of the Software.
15 
16  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
17  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
18  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
19  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
20  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
21  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
22  DEALINGS IN THE SOFTWARE.
23 */
24 
25 // minimum required Qore version
26 
27 // requires the Util module
28 
29 // requires the DataProvider module
30 
31 // don't use "$" signs for variables and class members, assume local variable scope
32 
33 // require type definitions everywhere
34 
35 // enable all warnings
36 
37 
38 // version history is included below in the docs
39 
2323 namespace SqlUtil {
2325  public struct GenericColumnInfo {
2327  string qore_type;
2329  string native_type;
2331  softint size;
2333  softint scale;
2339  *string comment;
2341  bool notnull = False;
2343  hash<string, hash> driver;
2346  };
2347 
2349  public struct OperatorInfo {
2350  string op;
2351  auto arg;
2352  };
2353 
2355  public struct ColumnOperatorInfo {
2356  string cop;
2357  auto column;
2358  auto arg;
2359  };
2360 
2362  public struct InsertOperatorInfo {
2363  string _iop;
2364  any arg;
2365  };
2366 
2368  public struct UpdateOperatorInfo {
2369  string uop;
2370  auto arg;
2372  };
2373 
2375  public struct JoinOperatorInfo {
2376  string jop;
2377  auto table;
2378  *string alias;
2381  *string ta;
2382  *hash<auto> opt;
2383  };
2384 
2388  *string date_format;
2389 
2391  *TimeZone data_timezone;
2392 
2394  *TimeZone db_timezone;
2395 
2397  *string number_format;
2398 
2400  *softbool mandatory;
2401 
2403 
2405  *softint maxlen;
2406  };
2407 
2409 
2411  public struct SqlCommandInfo {
2413  string sql;
2414 
2416  list<auto> args;
2417  };
2418 
2424 
2426  const DB_FUNCTIONS = "functions";
2428  const DB_MVIEWS = "materialized views";
2430  const DB_PACKAGES = "packages";
2432  const DB_PROCEDURES = "procedures";
2434  const DB_SEQUENCES = "sequences";
2436  const DB_TABLES = "tables";
2438  const DB_TYPES = "named types";
2440  const DB_VIEWS = "views";
2442  const DB_SYNONYMS = "synonyms";
2444 
2445  /* @defgroup SqlTypeConstants SQL Type Constants
2446  These constants can be used for the \c "qore_type" values when creating columns to specify additional SQL column types
2447  */
2450  const VARCHAR = "string";
2451 
2453  const NUMERIC = "number";
2454 
2456  const CHAR = "char";
2457 
2459  const BLOB = "blob";
2460 
2462  const CLOB = "clob";
2464 
2470  const SZ_NONE = 0;
2471 
2473  const SZ_MAND = 1;
2474 
2476  const SZ_OPT = 2;
2477 
2479  const SZ_NUM = 3;
2481 
2487 
2489  const COP_AS = "as";
2490 
2492 
2494  const COP_CAST = "cast";
2495 
2497 
2499  const COP_PREPEND = "prepend";
2500 
2502 
2504  const COP_APPEND = "append";
2505 
2507 
2509  const COP_VALUE = "value";
2510 
2512 
2514  const COP_UPPER = "upper";
2515 
2517 
2519  const COP_LOWER = "lower";
2520 
2522 
2524  const COP_DISTINCT = "distinct";
2525 
2527 
2529  const COP_MIN = "min";
2530 
2532 
2534  const COP_MAX = "max";
2535 
2537 
2539  const COP_AVG = "avg";
2540 
2542 
2544  const COP_SUM = "sum";
2545 
2547 
2549  const COP_COUNT = "count";
2550 
2552 
2554  const COP_OVER = "over";
2555 
2557 
2559  const COP_MINUS = "minus";
2560 
2562 
2564  const COP_PLUS = "plus";
2565 
2567 
2569  const COP_DIVIDE = "divide";
2570 
2572 
2574  const COP_MULTIPLY = "multiply";
2575 
2577 
2579  const COP_YEAR = "year";
2580 
2582 
2584  const COP_YEAR_MONTH = "year_month";
2585 
2587 
2589  const COP_YEAR_DAY = "year_day";
2590 
2592 
2594  const COP_YEAR_HOUR = "year_hour";
2595 
2597 
2599  const COP_SEQ = "seq";
2600 
2602 
2604  const COP_SEQ_CURRVAL = "seq_currval";
2605 
2607 
2609  const COP_COALESCE = "coalesce";
2610 
2612 
2614  const COP_SUBSTR = "substr";
2615 
2617 
2621  const COP_LENGTH = "length";
2622 
2624 
2630  const COP_TRUNC_DATE = "truncate_date";
2631 
2633 
2637  const COP_CUME_DIST = "cume_dist";
2638 
2640 
2644  const COP_DENSE_RANK = "dense_rank";
2645 
2647 
2651  const COP_FIRST_VALUE = "first_value";
2652 
2654 
2658  const COP_LAST_VALUE = "last_value";
2659 
2661 
2665  const COP_NTILE = "ntile";
2666 
2668 
2672  const COP_PERCENT_RANK = "percent_rank";
2673 
2675 
2679  const COP_RANK = "rank";
2680 
2682 
2686  const COP_ROW_NUMBER = "row_number";
2687 
2689  public struct QueryInfo {
2691  object table;
2692 
2694  *hash<auto> query_hash;
2695 
2697  *hash<auto> query_options;
2698 
2700  *hash<string, AbstractTable> join_map;
2701 
2703  *hash<string, bool> subquery_column_map;
2704 
2706  *hash<auto> pseudo_column_map;
2707 
2709  *hash<auto> expression_map;
2710 
2712  *hash<auto> where_operator_map;
2713 
2715  *hash<auto> column_operator_map;
2716 
2718  list<auto> args;
2719  };
2720 
2722  const DefaultCopMap = ...;
2723 
2725 
2768 
2776  hash<ColumnOperatorInfo> make_cop(string cop, auto column, auto arg);
2777 
2778 
2780 
2792  hash<ColumnOperatorInfo> cop_as(auto column, string arg);
2793 
2794 
2796 
2810  hash<ColumnOperatorInfo> cop_cast(auto column, string arg, auto arg1, auto arg2);
2811 
2812 
2814 
2824  hash<ColumnOperatorInfo> cop_prepend(auto column, string arg);
2825 
2826 
2828 
2838  hash<ColumnOperatorInfo> cop_append(auto column, string arg);
2839 
2840 
2842 
2970  hash<ColumnOperatorInfo> cop_value(auto arg);
2971 
2972 
2974 
2983  hash<ColumnOperatorInfo> cop_upper(auto column);
2984 
2985 
2987 
2996  hash<ColumnOperatorInfo> cop_lower(auto column);
2997 
2998 
3000 
3009  hash<ColumnOperatorInfo> cop_distinct(auto column);
3010 
3011 
3013 
3022  hash<ColumnOperatorInfo> cop_min(auto column);
3023 
3024 
3026 
3035  hash<ColumnOperatorInfo> cop_max(auto column);
3036 
3037 
3039 
3048  hash<ColumnOperatorInfo> cop_avg(auto column);
3049 
3050 
3052 
3061  hash<ColumnOperatorInfo> cop_sum(auto column);
3062 
3063 
3065 
3072  hash<ColumnOperatorInfo> cop_count(auto column = '');
3073 
3074 
3076 
3083  hash<ColumnOperatorInfo> cop_over(auto column, *string partitionby, *string orderby);
3084 
3085 
3087 
3097  hash<ColumnOperatorInfo> cop_minus(auto column1, auto column2);
3098 
3099 
3101 
3111  hash<ColumnOperatorInfo> cop_plus(auto column1, auto column2);
3112 
3113 
3115 
3125  hash<ColumnOperatorInfo> cop_divide(auto column1, auto column2);
3126 
3127 
3129 
3139  hash<ColumnOperatorInfo> cop_multiply(auto column1, auto column2);
3140 
3141 
3143 
3152  hash<ColumnOperatorInfo> cop_year(auto column);
3153 
3154 
3156 
3165  hash<ColumnOperatorInfo> cop_year_month(auto column);
3166 
3167 
3169 
3178  hash<ColumnOperatorInfo> cop_year_day(auto column);
3179 
3180 
3182 
3191  hash<ColumnOperatorInfo> cop_year_hour(auto column);
3192 
3193 
3195 
3205  hash<ColumnOperatorInfo> cop_seq(string seq, *string as);
3206 
3207 
3209 
3219  hash<ColumnOperatorInfo> cop_seq_currval(string seq, *string as);
3220 
3221 
3223 
3235  hash<ColumnOperatorInfo> cop_coalesce(auto col1, auto col2);
3236 
3237 
3239 
3250  hash<ColumnOperatorInfo> cop_substr(auto column, int start, *int count);
3251 
3252 
3254 
3265  hash<ColumnOperatorInfo> cop_length(auto column);
3266 
3267 
3269 
3283  hash<ColumnOperatorInfo> cop_trunc_date(auto column, string mask);
3284 
3285 
3286 
3288 
3316  hash<ColumnOperatorInfo> cop_cume_dist();
3317 
3318 
3320 
3348  hash<ColumnOperatorInfo> cop_dense_rank();
3349 
3350 
3352 
3380  hash<ColumnOperatorInfo> cop_first_value(any column);
3381 
3382 
3384 
3412  hash<ColumnOperatorInfo> cop_last_value(any column);
3413 
3414 
3416 
3446  hash<ColumnOperatorInfo> cop_ntile(int value);
3447 
3448 
3450 
3478  hash<ColumnOperatorInfo> cop_percent_rank();
3479 
3480 
3482 
3510  hash<ColumnOperatorInfo> cop_rank();
3511 
3512 
3514 
3542  hash<ColumnOperatorInfo> cop_row_number();
3543 
3544 
3546 
3598  const DT_YEAR = "Y";
3599 
3601  const DT_MONTH = "M";
3602 
3604  const DT_DAY = "D";
3605 
3607  const DT_HOUR = "H";
3608 
3610  const DT_MINUTE = "m";
3611 
3613  const DT_SECOND = "S";
3614 
3615  // let's simulate and enum here'
3616  const DT_ALL_VALUES = ( DT_YEAR, DT_MONTH, DT_DAY, DT_HOUR, DT_MINUTE, DT_SECOND );
3618 
3624  const DefaultUopMap = ...;
3625 
3627 
3650 
3658  hash<UpdateOperatorInfo> make_uop(string uop, auto arg, *hash<UpdateOperatorInfo> nest);
3659 
3660 
3662 
3672  hash<UpdateOperatorInfo> uop_prepend(string arg, *hash<UpdateOperatorInfo> nest);
3673 
3674 
3676 
3686  hash<UpdateOperatorInfo> uop_append(string arg, *hash<UpdateOperatorInfo> nest);
3687 
3688 
3690 
3699  hash<UpdateOperatorInfo> uop_upper(*hash<UpdateOperatorInfo> nest);
3700 
3701 
3703 
3712  hash<UpdateOperatorInfo> uop_lower(*hash<UpdateOperatorInfo> nest);
3713 
3714 
3716 
3727  hash<UpdateOperatorInfo> uop_substr(int start, *int count, *hash<UpdateOperatorInfo> nest);
3728 
3729 
3731 
3741  hash<UpdateOperatorInfo> uop_plus(auto arg, *hash<UpdateOperatorInfo> nest);
3742 
3743 
3745 
3755  hash<UpdateOperatorInfo> uop_minus(auto arg, *hash<UpdateOperatorInfo> nest);
3756 
3757 
3759 
3769  hash<UpdateOperatorInfo> uop_multiply(auto arg, *hash<UpdateOperatorInfo> nest);
3770 
3771 
3773 
3783  hash<UpdateOperatorInfo> uop_divide(auto arg, *hash<UpdateOperatorInfo> nest);
3784 
3785 
3787 
3796  hash<UpdateOperatorInfo> uop_seq(string seq);
3797 
3798 
3800 
3809  hash<UpdateOperatorInfo> uop_seq_currval(string seq);
3810 
3812 
3820 
3822  const JOP_INNER = "inner";
3823 
3825 
3827  const JOP_LEFT = "left";
3828 
3830 
3832  const JOP_RIGHT = "right";
3833 
3835  const JopMap = {
3836  JOP_INNER: "inner",
3837  JOP_LEFT: "left outer",
3838  JOP_RIGHT: "right outer",
3839  };
3841 
3852 
3855  hash<string, hash<JoinOperatorInfo>> make_jop(string jop, AbstractTable table, *string alias, *hash jcols, *hash cond, *string ta, *hash<auto> opt);
3856 
3857 
3859 
3863  hash<string, hash<JoinOperatorInfo>> make_jop(string jop, string table_name, *string alias, *hash jcols, *hash cond, *string ta, *hash<auto> opt);
3864 
3865 
3867 
3886  hash<string, hash<JoinOperatorInfo>> join_inner(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
3887 
3888 
3890 
3909  hash<string, hash<JoinOperatorInfo>> join_inner(Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
3910 
3911 
3913 
3932  hash<string, hash<JoinOperatorInfo>> join_inner(string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
3933 
3934 
3936 
3958  hash<string, hash<JoinOperatorInfo>> join_inner(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
3959 
3960 
3962 
3982  hash<string, hash<JoinOperatorInfo>> join_inner(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
3983 
3984 
3986 
4008  hash<string, hash<JoinOperatorInfo>> join_inner_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4009 
4010 
4012 
4031  hash<string, hash<JoinOperatorInfo>> join_left(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4032 
4033 
4035 
4054  hash<string, hash<JoinOperatorInfo>> join_left(Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4055 
4056 
4058 
4079  hash<string, hash<JoinOperatorInfo>> join_left(string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4080 
4081 
4083 
4103  hash<string, hash<JoinOperatorInfo>> join_left(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4104 
4105 
4107 
4127  hash<string, hash<JoinOperatorInfo>> join_left(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4128 
4129 
4131 
4153  hash<string, hash<JoinOperatorInfo>> join_left_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4154 
4155 
4157 
4176  hash<string, hash<JoinOperatorInfo>> join_right(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4177 
4178 
4180 
4199  hash<string, hash<JoinOperatorInfo>> join_right(Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4200 
4201 
4203 
4224  hash<string, hash<JoinOperatorInfo>> join_right(string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4225 
4226 
4228 
4248  hash<string, hash<JoinOperatorInfo>> join_right(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4249 
4250 
4252 
4272  hash<string, hash<JoinOperatorInfo>> join_right(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4273 
4274 
4276 
4298  hash<string, hash<JoinOperatorInfo>> join_right_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
4299 
4301 
4307 
4309  const OP_LIKE = "like";
4310 
4312 
4314  const OP_LT = "<";
4315 
4317 
4319  const OP_LE = "<=";
4320 
4322 
4324  const OP_GT = ">";
4325 
4327 
4329  const OP_GE = ">=";
4330 
4332 
4334  const OP_NE = "!=";
4335 
4337 
4339  const OP_EQ = "=";
4340 
4342 
4344  const OP_CLT = "C<";
4345 
4347 
4349  const OP_CLE = "C<=";
4350 
4352 
4354  const OP_CGT = "C>";
4355 
4357 
4359  const OP_CGE = "C>=";
4360 
4362 
4364  const OP_CNE = "C!=";
4365 
4367 
4369  const OP_CEQ = "C=";
4370 
4372 
4374  const OP_BETWEEN = "between";
4375 
4377 
4379  const OP_IN = "in";
4380 
4382 
4384  const OP_NOT = "not";
4385 
4387 
4389  const OP_SUBSTR = "substr";
4390 
4392 
4394  const OP_OR = "or";
4395 
4397 
4399  const OP_IN_SELECT = "in_select";
4400 
4403  // first the "AND" and "OR" "operators"
4404  DP_OP_AND: {
4405  "exp": AbstractDataProvider::GenericExpressions{DP_OP_AND},
4406  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4407  return "("
4408  + (foldl $1 + " and " + $2,
4409  (map info.table.getExpressionArg(\info, role, $1, AbstractDataProviderTypeMap."bool"), args))
4410  + ")";
4411  },
4412  },
4413  DP_OP_OR: {
4414  "exp": AbstractDataProvider::GenericExpressions{DP_OP_OR},
4415  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4416  return "("
4417  + (foldl $1 + " or " + $2,
4418  (map info.table.getExpressionArg(\info, role, $1, AbstractDataProviderTypeMap."bool"), args))
4419  + ")";
4420  },
4421  },
4422  OP_LIKE: {
4423  "exp": <DataProviderExpressionInfo>{
4424  "type": DET_Operator,
4425  "label": "like",
4426  "name": "like",
4427  "desc": "The value to bind as the 'like' value (ex: '%some string%')",
4428  "symbol": "like",
4429  "args": (
4430  DataProviderSignatureStringType,
4431  DataProviderSignatureStringValueType,
4432  ),
4433  "return_type": AbstractDataProviderTypeMap."bool",
4434  },
4435  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4436  return sprintf("%s like %s",
4437  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string"),
4438  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."string"));
4439  },
4440  },
4441  OP_LT: {
4442  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LT},
4443  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4444  return sprintf("%s < %s",
4445  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
4446  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
4447  },
4448  },
4449  OP_LE: {
4450  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LE},
4451  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4452  return sprintf("%s <= %s",
4453  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
4454  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
4455  },
4456  },
4457  OP_GT: {
4458  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GT},
4459  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4460  return sprintf("%s > %s",
4461  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
4462  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
4463  },
4464  },
4465  OP_GE: {
4466  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GE},
4467  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4468  return sprintf("%s >= %s",
4469  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
4470  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
4471  },
4472  },
4473  OP_NE: {
4474  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NE},
4475  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4476  bool null0 = (args[0] === NULL || !exists args[0]);
4477  bool null1 = (args[1] === NULL || !exists args[1]);
4478  if (null0 && null1) {
4479  return "1 = 0";
4480  }
4481  if (null0) {
4482  return sprintf("%s is not null",
4483  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
4484  }
4485  if (null1) {
4486  return sprintf("%s is not null",
4487  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"));
4488  }
4489  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4490  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
4491  return sprintf("%s != %s", exp0, exp1);
4492  },
4493  },
4494  OP_EQ: {
4495  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_EQ},
4496  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4497  bool null0 = (args[0] === NULL || !exists args[0]);
4498  bool null1 = (args[1] === NULL || !exists args[1]);
4499  if (null0 && null1) {
4500  return "1 = 1";
4501  }
4502  if (null0) {
4503  return sprintf("%s is null",
4504  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
4505  }
4506  if (null1) {
4507  return sprintf("%s is null",
4508  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"));
4509  }
4510  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4511  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
4512  return sprintf("%s = %s", exp0, exp1);
4513  },
4514  },
4515  OP_BETWEEN: {
4516  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_BETWEEN},
4517  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4518  return sprintf("%s between %s and %s",
4519  info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
4520  info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"),
4521  info.table.getExpressionArg(\info, role, args[2], AbstractDataProviderTypeMap."any"));
4522  },
4523  },
4524  OP_IN: {
4525  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_IN},
4526  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4527  string exp0 = info.table.getExpressionArg(\info, role, shift args, AbstractDataProviderTypeMap."any");
4528  *string ins = (foldl $1 + "," + $2, (map info.table.getExpressionArg(\info, role, $1,
4529  AbstractDataProviderTypeMap."any"), args));
4530  return exists ins ? sprintf("%s in (%s)", exp0, ins) : "1 != 1";
4531  },
4532  },
4533  OP_NOT: {
4534  "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NOT},
4535  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4536  return sprintf("not (%s)", info.table.getExpressionArg(\info, role, args[0],
4537  AbstractDataProviderTypeMap."bool"));
4538  },
4539  },
4540  OP_SUBSTR: {
4541  "exp": <DataProviderExpressionInfo>{
4542  "type": DET_Operator,
4543  "label": "substr",
4544  "name": "substr",
4545  "desc": "returns a substring from a string; args: string, int start char, [int length]",
4546  "symbol": "substr",
4547  "args": (
4548  DataProviderSignatureStringType,
4549  DataProviderSignatureIntValueType,
4550  DataProviderSignatureOptionalIntValueType,
4551  ),
4552  "return_type": AbstractDataProviderTypeMap."string",
4553  },
4554  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4555  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string");
4556  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."int");
4557  if (!exists args[2]) {
4558  return sprintf("substring(%s from %s)", exp0, exp1);
4559  }
4560  return sprintf("substring(%s from %s for %s)", exp0, exp1,
4561  info.table.getExpressionArg(\info, role, args[2], AbstractDataProviderTypeMap."int"));
4562  },
4563  },
4564  OP_IN_SELECT: {
4565  "exp": <DataProviderExpressionInfo>{
4566  "type": DET_Operator,
4567  "label": "in_select",
4568  "name": "in_select",
4569  "desc": "check if a value is in a sub-select; args: value, table name for the subselect, subselect "
4570  "query hash",
4571  "symbol": "in_select",
4572  "args": (
4573  DataProviderSignatureAnyType, # any value, expression, or field reference
4574  DataProviderSignatureStringValueType, # table name
4575  DataProviderSignatureHashValueType, # query hash
4576  ),
4577  "return_type": AbstractDataProviderTypeMap."bool",
4578  },
4579  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4580  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4581  object subtable = info.table.getSubtableFromString(args[1], info.query_options);
4582  list<auto> subargs;
4583  string subsql = subtable.getSelectSql(args[2], \subargs);
4584  args += subargs;
4585  return sprintf("%s in (%s)", exp0, subsql);
4586  },
4587  },
4588  COP_AS: {
4589  "exp": <DataProviderExpressionInfo>{
4590  "type": DET_Operator,
4591  "label": "as",
4592  "name": "as",
4593  "desc": "aliases a result; args: expression or value, string label",
4594  "symbol": "as",
4595  "role": ER_Field,
4596  "args": (
4597  DataProviderSignatureAnyType,
4598  DataProviderSignatureStringValueType,
4599  ),
4600  "return_type": AbstractDataProviderTypeMap."any",
4601  },
4602  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4603  string exp = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4604  string arg = args[1];
4605  info.pseudo_column_map{arg} = exp;
4606  return sprintf("%s as %s", exp, info.table.getColumnSqlName(arg));
4607  },
4608  },
4609  COP_PREPEND: {
4610  "exp": <DataProviderExpressionInfo>{
4611  "type": DET_Operator,
4612  "label": COP_PREPEND,
4613  "name": COP_PREPEND,
4614  "desc": "prepends a string to another string; args: string to be prepended, string to prepend to the "
4615  "first string",
4616  "symbol": COP_PREPEND,
4617  "args": (
4618  DataProviderSignatureStringType,
4619  DataProviderSignatureStringType,
4620  ),
4621  "return_type": AbstractDataProviderTypeMap."string",
4622  },
4623  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4624  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string");
4625  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."string");
4626  return sprintf("%s || %s", exp1, exp0);
4627  },
4628  },
4629  COP_APPEND: {
4630  "exp": <DataProviderExpressionInfo>{
4631  "type": DET_Operator,
4632  "label": COP_APPEND,
4633  "name": COP_APPEND,
4634  "desc": "appends or catenates a string to another string; args: string to be appended, string to "
4635  "append or concatenate to the first string",
4636  "symbol": COP_APPEND,
4637  "args": (
4638  DataProviderSignatureStringType,
4639  DataProviderSignatureStringType,
4640  ),
4641  "return_type": AbstractDataProviderTypeMap."string",
4642  },
4643  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4644  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string");
4645  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."string");
4646  return sprintf("%s || %s", exp0, exp1);
4647  },
4648  },
4649  COP_UPPER: {
4650  "exp": <DataProviderExpressionInfo>{
4651  "type": DET_Operator,
4652  "label": COP_UPPER,
4653  "name": COP_UPPER,
4654  "desc": "converts a string to all upper case; arg: string",
4655  "symbol": COP_UPPER,
4656  "args": (
4657  DataProviderSignatureStringType,
4658  ),
4659  "return_type": AbstractDataProviderTypeMap."string",
4660  },
4661  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4662  return sprintf("upper(%s)", info.table.getExpressionArg(\info, role, args[0],
4663  AbstractDataProviderTypeMap."string"));
4664  },
4665  },
4666  COP_LOWER: {
4667  "exp": <DataProviderExpressionInfo>{
4668  "type": DET_Operator,
4669  "label": COP_LOWER,
4670  "name": COP_LOWER,
4671  "desc": "converts a string to all lower case; arg: string",
4672  "symbol": COP_LOWER,
4673  "args": (
4674  DataProviderSignatureStringType,
4675  ),
4676  "return_type": AbstractDataProviderTypeMap."string",
4677  },
4678  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4679  return sprintf("lower(%s)", info.table.getExpressionArg(\info, role, args[0],
4680  AbstractDataProviderTypeMap."string"));
4681  },
4682  },
4683  COP_DISTINCT: {
4684  "exp": <DataProviderExpressionInfo>{
4685  "type": DET_Operator,
4686  "label": COP_DISTINCT,
4687  "name": COP_DISTINCT,
4688  "desc": "indicates that only records with distinct values of the given field should be returned",
4689  "symbol": COP_DISTINCT,
4690  "role": ER_Field,
4691  "args": (
4692  DataProviderSignatureAnyType,
4693  ),
4694  "return_type": AbstractDataProviderTypeMap."any",
4695  },
4696  "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
4697  return sprintf("distinct %s", info.table.getExpressionArg(\info, role, args[0],
4698  AbstractDataProviderTypeMap."any"));
4699  },
4700  },
4701  COP_MIN: {
4702  "exp": <DataProviderExpressionInfo>{
4703  "type": DET_Operator,
4704  "label": COP_MIN,
4705  "name": COP_MIN,
4706  "desc": "Returns the minimum value in the group",
4707  "symbol": COP_MIN,
4708  "role": ER_Field,
4709  "args": (
4710  DataProviderSignatureAnyType,
4711  ),
4712  "return_type": AbstractDataProviderTypeMap."any",
4713  },
4714  "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
4715  return sprintf("min(%s)", info.table.getExpressionArg(\info, role, args[0],
4716  AbstractDataProviderTypeMap."any"));
4717  },
4718  "group": True,
4719  },
4720  COP_MAX: {
4721  "exp": <DataProviderExpressionInfo>{
4722  "type": DET_Operator,
4723  "label": COP_MAX,
4724  "name": COP_MAX,
4725  "desc": "Returns the maximum value in the group",
4726  "symbol": COP_MAX,
4727  "role": ER_Field,
4728  "args": (
4729  DataProviderSignatureAnyType,
4730  ),
4731  "return_type": AbstractDataProviderTypeMap."any",
4732  },
4733  "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
4734  return sprintf("max(%s)", info.table.getExpressionArg(\info, role, args[0],
4735  AbstractDataProviderTypeMap."any"));
4736  },
4737  "group": True,
4738  },
4739  COP_AVG: {
4740  "exp": <DataProviderExpressionInfo>{
4741  "type": DET_Operator,
4742  "label": COP_AVG,
4743  "name": COP_AVG,
4744  "desc": "Returns the average of the given values in the group",
4745  "symbol": COP_AVG,
4746  "role": ER_Field,
4747  "args": (
4748  DataProviderSignatureAnyType,
4749  ),
4750  "return_type": AbstractDataProviderTypeMap."any",
4751  },
4752  "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
4753  return sprintf("avg(%s)", info.table.getExpressionArg(\info, role, args[0],
4754  AbstractDataProviderTypeMap."any"));
4755  },
4756  "group": True,
4757  },
4758  COP_SUM: {
4759  "exp": <DataProviderExpressionInfo>{
4760  "type": DET_Operator,
4761  "label": COP_SUM,
4762  "name": COP_SUM,
4763  "desc": "Returns the sum of the given values",
4764  "symbol": COP_SUM,
4765  "role": ER_Field,
4766  "args": (
4767  DataProviderSignatureAnyType,
4768  ),
4769  "return_type": AbstractDataProviderTypeMap."any",
4770  },
4771  "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
4772  return sprintf("sum(%s)", info.table.getExpressionArg(\info, role, args[0],
4773  AbstractDataProviderTypeMap."any"));
4774  },
4775  "group": True,
4776  },
4777  COP_COUNT: {
4778  "exp": <DataProviderExpressionInfo>{
4779  "type": DET_Operator,
4780  "label": COP_COUNT,
4781  "name": COP_COUNT,
4782  "desc": "Count the number of rows in the group",
4783  "symbol": COP_COUNT,
4784  "role": ER_Field,
4785  "args": (
4786  DataProviderSignatureAnyType,
4787  ),
4788  "return_type": AbstractDataProviderTypeMap."int",
4789  },
4790  "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
4791  if (exists args[0] && args[0] != NULL) {
4792  return sprintf("count(%s)", info.table.getExpressionArg(\info, role, args[0],
4793  AbstractDataProviderTypeMap."any"));
4794  }
4795  return "count(1)";
4796  },
4797  },
4798  COP_MINUS: {
4799  "exp": <DataProviderExpressionInfo>{
4800  "type": DET_Operator,
4801  "label": COP_MINUS,
4802  "name": COP_MINUS,
4803  "desc": "Subtracts one number from another",
4804  "symbol": COP_MINUS,
4805  "args": (
4806  DataProviderSignatureAnyType,
4807  DataProviderSignatureAnyType,
4808  ),
4809  "return_type": AbstractDataProviderTypeMap."any",
4810  },
4811  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4812  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4813  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
4814  return sprintf("%s - %s", exp0, exp1);
4815  },
4816  },
4817  COP_PLUS: {
4818  "exp": <DataProviderExpressionInfo>{
4819  "type": DET_Operator,
4820  "label": COP_PLUS,
4821  "name": COP_PLUS,
4822  "desc": "Adds two numbers",
4823  "symbol": COP_PLUS,
4824  "args": (
4825  DataProviderSignatureAnyType,
4826  DataProviderSignatureAnyType,
4827  ),
4828  "return_type": AbstractDataProviderTypeMap."any",
4829  },
4830  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4831  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4832  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
4833  return sprintf("%s + %s", exp0, exp1);
4834  },
4835  },
4836  COP_DIVIDE: {
4837  "exp": <DataProviderExpressionInfo>{
4838  "type": DET_Operator,
4839  "label": COP_DIVIDE,
4840  "name": COP_DIVIDE,
4841  "desc": "Divides one number by another",
4842  "symbol": COP_DIVIDE,
4843  "args": (
4844  DataProviderSignatureAnyType,
4845  DataProviderSignatureAnyType,
4846  ),
4847  "return_type": AbstractDataProviderTypeMap."any",
4848  },
4849  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4850  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4851  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
4852  return sprintf("%s / %s", exp0, exp1);
4853  },
4854  },
4855  COP_MULTIPLY: {
4856  "exp": <DataProviderExpressionInfo>{
4857  "type": DET_Operator,
4858  "label": COP_MULTIPLY,
4859  "name": COP_MULTIPLY,
4860  "desc": "Multiplies two numbers",
4861  "symbol": COP_MULTIPLY,
4862  "args": (
4863  DataProviderSignatureAnyType,
4864  DataProviderSignatureAnyType,
4865  ),
4866  "return_type": AbstractDataProviderTypeMap."any",
4867  },
4868  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4869  string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
4870  string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
4871  return sprintf("%s * %s", exp0, exp1);
4872  },
4873  },
4874  COP_COALESCE: {
4875  "exp": <DataProviderExpressionInfo>{
4876  "type": DET_Operator,
4877  "label": COP_COALESCE,
4878  "name": COP_COALESCE,
4879  "desc": "Returns the value of the first expression with a value",
4880  "symbol": COP_COALESCE,
4881  "args": (
4882  DataProviderSignatureAnyType,
4883  ),
4884  "varargs": True,
4885  "return_type": AbstractDataProviderTypeMap."any",
4886  },
4887  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4888  return sprintf("coalesce(%s)", (foldl $1 + "," + $2,
4889  (map info.table.getExpressionArg(\info, role, $1, AbstractDataProviderTypeMap."any"), args)));
4890  }
4891  },
4892  COP_LENGTH: {
4893  "exp": <DataProviderExpressionInfo>{
4894  "type": DET_Operator,
4895  "label": COP_LENGTH,
4896  "name": COP_LENGTH,
4897  "desc": "Returns the length of the argument",
4898  "symbol": COP_LENGTH,
4899  "args": (
4900  DataProviderSignatureAnyType,
4901  ),
4902  "return_type": AbstractDataProviderTypeMap."int",
4903  },
4904  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4905  return sprintf("length(%s)", info.table.getExpressionArg(\info, role, args[0],
4906  AbstractDataProviderTypeMap."any"));
4907  },
4908  },
4909  COP_SEQ: {
4910  "exp": <DataProviderExpressionInfo>{
4911  "type": DET_Operator,
4912  "label": COP_SEQ,
4913  "name": COP_SEQ,
4914  "desc": "returns the next value in the given sequence; args: sequence name",
4915  "symbol": COP_SEQ,
4916  "args": (
4917  DataProviderSignatureStringValueType,
4918  ),
4919  "return_type": AbstractDataProviderTypeMap."int",
4920  },
4921  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4922  throw "SEQUENCE-ERROR", sprintf("cannot select sequence %y because this database does not support "
4923  "sequences", args[0]);
4924  }
4925  },
4926  COP_SEQ_CURRVAL: {
4927  "exp": <DataProviderExpressionInfo>{
4928  "type": DET_Operator,
4929  "label": COP_SEQ_CURRVAL,
4930  "name": COP_SEQ_CURRVAL,
4931  "desc": "returns the current value in the given sequence; args: sequence name",
4932  "symbol": COP_SEQ_CURRVAL,
4933  "args": (
4934  DataProviderSignatureStringValueType,
4935  ),
4936  "return_type": AbstractDataProviderTypeMap."int",
4937  },
4938  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4939  throw "SEQUENCE-ERROR", sprintf("cannot select the current value of sequence %y because this "
4940  "database does not support sequences", args[0]);
4941  }
4942  },
4943  COP_OVER: {
4944  "exp": <DataProviderExpressionInfo>{
4945  "type": DET_Operator,
4946  "label": COP_OVER,
4947  "name": COP_OVER,
4948  "desc": "returns the output of the SQL window given by the arguments; args: *string partiion by, "
4949  "*string order by",
4950  "symbol": COP_OVER,
4951  "args": (
4952  DataProviderSignatureFieldType,
4953  DataProviderSignatureOptionalStringValueType,
4954  DataProviderSignatureOptionalStringValueType,
4955  ),
4956  "role": ER_Field,
4957  "return_type": AbstractDataProviderTypeMap."any",
4958  },
4959  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
4960  string column_name = args[0];
4961  *string partitionby = args[1];
4962  *string orderby = args[2];
4963  if (!exists partitionby && exists orderby) {
4964  throw "OVER-ERROR", "Argument 2 (partition by) must be given in case of usage of argument 3 "
4965  "(order by)";
4966  }
4967  string sql = column_name + " over (";
4968  Columns cols;
4969  if (exists partitionby) {
4970  if (!info.pseudo_column_map{partitionby}) {
4971  cols = info.table.describe();
4972  if (!exists cols{partitionby}) {
4973  throw "OVER-ERROR", sprintf("Argument 2 (partition by) %y is not an alias or an "
4974  "accessible column", partitionby);
4975  }
4976  }
4977  sql += sprintf("partition by %s", partitionby);
4978  }
4979  if (exists orderby) {
4980  if (!info.pseudo_column_map{orderby}) {
4981  if (!cols) {
4982  cols = info.table.describe();
4983  if (!exists cols{orderby}) {
4984  throw "OVER-ERROR", sprintf("Argument 3 (order by) %y is not an alias or an "
4985  "accessible column", orderby);
4986  }
4987  }
4988  }
4989  sql += sprintf(" order by %s", orderby);
4990  }
4991  sql += ")";
4992  return sql;
4993  }
4994  },
4995  COP_DENSE_RANK: (
4996  "exp": <DataProviderExpressionInfo>{
4997  "type": DET_Operator,
4998  "label": COP_DENSE_RANK,
4999  "name": COP_DENSE_RANK,
5000  "desc": "Returns the rank of a row in an ordered group of rows as a number",
5001  "symbol": COP_DENSE_RANK,
5002  "args": (),
5003  "role": ER_Field,
5004  "return_type": AbstractDataProviderTypeMap."any",
5005  },
5006  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
5007  return "dense_rank()";
5008  },
5009  ),
5010  COP_FIRST_VALUE: (
5011  "exp": <DataProviderExpressionInfo>{
5012  "type": DET_Operator,
5013  "label": COP_FIRST_VALUE,
5014  "name": COP_FIRST_VALUE,
5015  "desc": "Returns the first value in an ordered group of rows",
5016  "symbol": COP_FIRST_VALUE,
5017  "args": (
5018  DataProviderSignatureFieldType,
5019  ),
5020  "role": ER_Field,
5021  "return_type": AbstractDataProviderTypeMap."any",
5022  },
5023  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
5024  return sprintf("first_value(%s)", args[0]);
5025  },
5026  ),
5027  COP_LAST_VALUE: (
5028  "exp": <DataProviderExpressionInfo>{
5029  "type": DET_Operator,
5030  "label": COP_LAST_VALUE,
5031  "name": COP_LAST_VALUE,
5032  "desc": "Returns the last value in an ordered group of rows",
5033  "symbol": COP_LAST_VALUE,
5034  "args": (
5035  DataProviderSignatureFieldType,
5036  ),
5037  "role": ER_Field,
5038  "return_type": AbstractDataProviderTypeMap."any",
5039  },
5040  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
5041  return sprintf("last_value(%s)", args[0]);
5042  },
5043  ),
5044  COP_NTILE: (
5045  "exp": <DataProviderExpressionInfo>{
5046  "type": DET_Operator,
5047  "label": COP_NTILE,
5048  "name": COP_NTILE,
5049  "desc": "Returns the group number of an ordered group of rows",
5050  "symbol": COP_NTILE,
5051  "args": (
5052  DataProviderSignatureIntValueType,
5053  ),
5054  "role": ER_Field,
5055  "return_type": AbstractDataProviderTypeMap."int",
5056  },
5057  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
5058  return sprintf("ntile(%d)", args[0]);
5059  },
5060  ),
5061  COP_PERCENT_RANK: (
5062  "exp": <DataProviderExpressionInfo>{
5063  "type": DET_Operator,
5064  "label": COP_PERCENT_RANK,
5065  "name": COP_PERCENT_RANK,
5066  "desc": "Returns the percent rank of a row in an ordered group of rows",
5067  "symbol": COP_PERCENT_RANK,
5068  "args": (),
5069  "role": ER_Field,
5070  "return_type": AbstractDataProviderTypeMap."int",
5071  },
5072  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
5073  return "percent_rank()";
5074  },
5075  ),
5076  COP_RANK: (
5077  "exp": <DataProviderExpressionInfo>{
5078  "type": DET_Operator,
5079  "label": COP_PERCENT_RANK,
5080  "name": COP_PERCENT_RANK,
5081  "desc": "Returns the row number of a row in an ordered group of rows",
5082  "symbol": COP_PERCENT_RANK,
5083  "args": (),
5084  "role": ER_Field,
5085  "return_type": AbstractDataProviderTypeMap."int",
5086  },
5087  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
5088  return "rank()";
5089  },
5090  ),
5091  COP_ROW_NUMBER: (
5092  "exp": <DataProviderExpressionInfo>{
5093  "type": DET_Operator,
5094  "label": COP_ROW_NUMBER,
5095  "name": COP_ROW_NUMBER,
5096  "desc": "Returns the row number of a result set",
5097  "symbol": COP_ROW_NUMBER,
5098  "args": (),
5099  "role": ER_Field,
5100  "return_type": AbstractDataProviderTypeMap."int",
5101  },
5102  "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
5103  return "row_number()";
5104  },
5105  ),
5106  };
5107 
5109  const DefaultOpMap = {
5110  OP_LIKE: {
5111  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5112  info.args += arg;
5113  return sprintf("%s like %v", cn);
5114  },
5115  "name": "like",
5116  "args": (DataProviderSignatureStringType, DataProviderSignatureStringValueType),
5117  "return_type": AbstractDataProviderTypeMap."bool",
5118  "desc": "The value to bind as the 'like' value (ex: '%some string%')",
5119  },
5120  OP_LT: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LT} + (
5121  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5122  info.args += arg;
5123  return sprintf("%s < %v", cn);
5124  },
5125  ),
5126  OP_LE: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LE} + (
5127  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5128  info.args += arg;
5129  return sprintf("%s <= %v", cn);
5130  },
5131  ),
5132  OP_GT: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GT} + (
5133  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5134  info.args += arg;
5135  return sprintf("%s > %v", cn);
5136  },
5137  ),
5138  OP_GE: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GE} + (
5139  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5140  info.args += arg;
5141  return sprintf("%s >= %v", cn);
5142  },
5143  ),
5144  OP_NE: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NE} + (
5145  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5146  if (arg === NULL || !exists arg)
5147  return sprintf("%s is not null", cn);
5148  info.args += arg;
5149  return sprintf("(%s != %v or %s is null)", cn, cn);
5150  },
5151  ),
5152  OP_EQ: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_EQ} + (
5153  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5154  if (arg === NULL || !exists arg)
5155  return sprintf("%s is null", cn);
5156  info.args += arg;
5157  return sprintf("%s = %v", cn);
5158  },
5159  ),
5160  OP_BETWEEN: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_BETWEEN} + (
5161  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5162  info.args += arg[0];
5163  info.args += arg[1];
5164  return sprintf("%s between %v and %v", cn);
5165  },
5166  ),
5167  OP_IN: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_IN} + (
5168  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5169  *string ins = (foldl $1 + "," + $2, (map info.table.getSqlValue($1), arg));
5170  return exists ins ? sprintf("%s in (%s)", cn, ins) : "1 != 1";
5171  },
5172  ),
5173  OP_IN_SELECT: {
5174  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg,
5175  *hash<auto> opt) {
5176  object subtable;
5177  if (arg.table.typeCode() == NT_STRING) {
5178  subtable = info.table.getSubtableFromString(arg.table, opt);
5179  } else {
5180  subtable = arg.table;
5181  }
5182 
5183  list<auto> subargs;
5184  string subsql = subtable.getSelectSql(arg.select_hash, \subargs);
5185  info.args += subargs;
5186  return sprintf("%s in (%s)", cn, subsql);
5187  },
5188  "withopt": True,
5189  },
5190  OP_NOT: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NOT} + (
5191  "recursive": True,
5192  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5193  return sprintf("not (%s)", cn);
5194  },
5195  ),
5196  OP_CLT: (
5197  "argcolumn": True,
5198  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5199  return sprintf("%s < %s", cn, arg);
5200  },
5201  "name": "column <",
5202  "type": AbstractDataProviderType::get(StringType),
5203  "desc": "a column name for less than comparisons; if the field value is less than the column argument's "
5204  "value, then the operation returns true; the other column must have a compatible type",
5205  ),
5206  OP_CLE: (
5207  "argcolumn": True,
5208  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5209  return sprintf("%s <= %s", cn, arg);
5210  },
5211  "name": "column <=",
5212  "type": AbstractDataProviderType::get(StringType),
5213  "desc": "a column name for less than or equals comparisons; if the field value is less than or equal to "
5214  "the column argument's, then the operation returns true; the other column must have a compatible type",
5215  ),
5216  OP_CGT: (
5217  "argcolumn": True,
5218  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5219  return sprintf("%s > %s", cn, arg);
5220  },
5221  "name": "column >",
5222  "type": AbstractDataProviderType::get(StringType),
5223  "desc": "a column name for less than comparisons; if the field value is less than the column argument's, "
5224  "then the operation returns true; the other column must have a compatible type",
5225  ),
5226  OP_CGE: (
5227  "argcolumn": True,
5228  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5229  return sprintf("%s >= %s", cn, arg);
5230  },
5231  "name": "column >=",
5232  "type": AbstractDataProviderType::get(StringType),
5233  "desc": "a column name for greater than or equals comparisons; if the field value is greater than or "
5234  "equal to the column argument's, then the operation returns true; the other column must have a "
5235  "compatible type",
5236  ),
5237  OP_CNE: (
5238  "argcolumn": True,
5239  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5240  return sprintf("%s != %s", cn, arg);
5241  },
5242  "name": "column !=",
5243  "type": AbstractDataProviderType::get(StringType),
5244  "desc": "a column name for not=equals comparisons; the other column must have a compatible type",
5245  ),
5246  OP_CEQ: (
5247  "argcolumn": True,
5248  "code": string sub (reference<hash<QueryInfo>> info, string cn, string arg) {
5249  return sprintf("%s = %s", cn, arg);
5250  },
5251  "name": "column =",
5252  "type": AbstractDataProviderType::get(StringType),
5253  "desc": "a value for equality comparisons; the other column must have a compatible type",
5254  ),
5255  OP_SUBSTR: (
5256  "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
5257  info.args += arg[0]; // start
5258  if (!exists arg[1]) {
5259  info.args += arg[2]; # text
5260  return sprintf("substring(%s from %v) = %v", cn);
5261  }
5262  info.args += arg[1]; # count
5263  info.args += arg[2]; # text
5264  return sprintf("substring(%s from %v for %v) = %v", cn);
5265  },
5266  ),
5267  OP_OR: (
5268  "code": string sub (reference<hash<QueryInfo>> info, string cn, list<auto> arg) {
5269  return info.table.getOrClause(\info, arg);
5270  },
5271  ),
5272  };
5274 
5299  hash<OperatorInfo> make_op(string op, auto arg);
5300 
5301 
5303 
5312  hash<OperatorInfo> op_like(string str);
5313 
5314 
5316 
5327  hash<OperatorInfo> op_lt(auto arg);
5328 
5329 
5331 
5342  hash<OperatorInfo> op_le(auto arg);
5343 
5344 
5346 
5357  hash<OperatorInfo> op_gt(auto arg);
5358 
5359 
5361 
5372  hash<OperatorInfo> op_ge(auto arg);
5373 
5374 
5376 
5389  hash<OperatorInfo> op_ne(auto arg);
5390 
5391 
5393 
5406  hash<OperatorInfo> op_eq(auto arg);
5407 
5408 
5410 
5422  hash<OperatorInfo> op_between(auto l, auto r);
5423 
5424 
5426 
5435  hash<OperatorInfo> op_in();
5436 
5437 
5439 
5446  hash<OperatorInfo> op_in_select(string table, hash<auto> select_hash);
5447 
5448 
5450 
5457  hash<OperatorInfo> op_in_select(AbstractTable table, hash<auto> select_hash);
5458 
5459 
5461 
5468  hash<OperatorInfo> op_in_select(Table table, hash<auto> select_hash);
5469 
5470 
5472 
5483  hash<OperatorInfo> op_in(list<auto> args);
5484 
5485 
5487 
5494  hash<OperatorInfo> op_not(hash arg);
5495 
5496 
5498 
5509  hash<OperatorInfo> op_clt(string arg);
5510 
5511 
5513 
5524  hash<OperatorInfo> op_cle(string arg);
5525 
5526 
5528 
5539  hash<OperatorInfo> op_cgt(string arg);
5540 
5541 
5543 
5554  hash<OperatorInfo> op_cge(string arg);
5555 
5556 
5558 
5569  hash<OperatorInfo> op_cne(string arg);
5570 
5571 
5573 
5584  hash<OperatorInfo> op_ceq(string arg);
5585 
5586 
5588 
5599  hash<OperatorInfo> op_substr(int start, *int count, string text);
5600 
5601 
5603 
5613  hash<OperatorInfo> op_substr(int start, string text);
5614 
5615 
5617 
5634  hash<string, hash<OperatorInfo>> wop_or(hash h1, hash h2);
5635 
5637 
5645 
5647  const IOP_SEQ = "seq";
5648 
5650 
5652  const IOP_SEQ_CURRVAL = "seq_currval";
5653 
5655  const DefaultIopMap = {};
5657 
5664 
5671  hash<InsertOperatorInfo> make_iop(string iop, auto arg);
5672 
5673 
5675 
5684  hash<InsertOperatorInfo> iop_seq(string arg);
5685 
5686 
5688 
5697  hash<InsertOperatorInfo> iop_seq_currval(string arg);
5698 
5700 
5702  const SqlUtilDrivers = ...;
5703 
5704 
5705  // private variable to store the closure used to deserialize datasources (if any)
5706  extern code ds_get;
5707 
5708  // private variable to store the closure used to serialize datasources (if any)
5709  extern code ds_do;
5710 
5713 
5714 
5717 
5718 
5719  // private function used to deserialize datasources
5720  AbstractDatasource sqlutil_get_ds(string type, string config);
5721 
5722 
5723  // private function used to serialize datasources
5724  hash<auto> sqlutil_ds(AbstractDatasource ds);
5725 
5726 
5729 
5730 public:
5731 
5732 
5733 protected:
5736 
5737 public:
5738 
5741 
5742 
5745 
5746 
5749 
5750 
5752 
5767  auto memberGate(string k);
5768 
5769 
5771 
5778 
5779 
5781  abstract auto take(string k);
5782 
5784  renameKey(string old_name, string new_name);
5785 
5786 
5788  *hash<auto> getHash();
5789 
5790 
5792 
5801  bool matchKeys(hash h1);
5802 
5803 
5805 
5814  bool matchKeys(list<auto> l);
5815 
5816 
5818 
5828 
5829 
5831 
5841 
5842 
5844 
5853  bool partialMatchKeys(list<auto> l);
5854 
5855 
5857 
5867 
5868 
5870 
5879  bool val();
5880 
5881 
5883 
5890  list<string> keys();
5891 
5892 
5894 
5901  list<auto> values();
5902 
5903 
5905 
5913 
5914 
5916 
5924 
5925 
5927 
5935 
5936 
5938  bool empty();
5939 
5940 
5942 
5949  int size();
5950 
5951 
5953 
5962  bool hasKey(string k);
5963 
5964 
5966 
5975  bool hasKeyValue(string k);
5976 
5977 
5979 
5988  *string firstKey();
5989 
5990 
5992 
6001  *string lastKey();
6002 
6003 
6005  abstract string getElementName();
6006  };
6007 
6010 
6011 public:
6012 
6013 
6014 protected:
6015  softlist l;
6016 
6017 public:
6018 
6020  constructor(softlist nl);
6021 
6022 
6024 
6035  abstract auto get(softint i);
6036 
6038  add(auto val);
6039 
6040 
6042  auto take(int i);
6043 
6044 
6046  list<auto> getList();
6047 
6048 
6050 
6059  bool val();
6060 
6061 
6063 
6071 
6072 
6074  bool empty();
6075 
6076 
6078 
6085  int size();
6086 
6087 
6089  abstract string getElementName();
6090 
6091 protected:
6092  checkIndex(int i);
6093 public:
6094 
6095  };
6096 
6099 
6100 public:
6103 
6104 
6106  constructor(AbstractDatasource ds, hash tables, *hash<auto> opt);
6107 
6108 
6110  constructor(AbstractDatasource ds);
6111 
6112 
6114  add(string k, Table val);
6115 
6116 
6118  add(string k, AbstractTable val);
6119 
6120 
6123 
6124 
6127 
6128 
6130  AbstractTable take(string k);
6131 
6132 
6134  populate(AbstractDatasource ds, hash<auto> tables, *hash<auto> opt);
6135 
6136 
6138  populate(AbstractDatasource ds);
6139 
6140 
6142 
6158  *list<auto> getDropAllForeignConstraintsOnTableSql(string name, *hash<auto> opt);
6159 
6160 
6162 
6178 
6179 
6181  string getElementName();
6182 
6183 
6185  *AbstractTable getIfExists(AbstractDatasource ds, string name);
6186 
6187 
6189  AbstractTable get(AbstractDatasource ds, string name);
6190 
6191 
6193 
6208  *string getRenameTableIfExistsSql(string old_name, string new_name, *hash<auto> opts);
6209 
6210 
6212 
6223  bool tableRenamed(string old_name, string new_name, string old_sql_name);
6224 
6225 
6226 protected:
6227  tableRenamedIntern(string old_name, string new_name, string oldsn);
6228 public:
6229 
6230 
6232 
6247  *string getDropConstraintIfExistsSql(string tname, string cname, *hash<auto> opts);
6248 
6249 
6250  list<auto> getCreateList();
6251 
6252 
6253  Qore::AbstractIterator createIterator();
6254 
6255 
6257 
6265  list<auto> getDropList();
6266 
6267 
6269 
6277 
6278 
6279 protected:
6280  getDependencies(reference<hash> tdh, reference<hash> sdh, *reference<hash> th);
6281 public:
6282 
6283  };
6284 
6287 
6288 public:
6291 
6292 
6295 
6296 
6298  add(string k, AbstractColumn val);
6299 
6300 
6303 
6304 
6306 
6322 
6323 
6325  Columns subset(softlist l);
6326 
6327 
6329  string getElementName();
6330 
6331 
6333  bool equal(Columns cols);
6334 
6335 
6337  AbstractIterator getSqlColumnNameIterator();
6338 
6339  };
6340 
6343 
6344 public:
6346  string name;
6347 
6349  string ddl_name;
6350 
6352 protected:
6354 public:
6355 
6356 
6359 
6360 
6362 
6364  abstract string getDdlName(string name);
6365  };
6366 
6369 
6370 public:
6372  string native_type;
6373 
6375  *string qore_type;
6376 
6378  int size;
6379 
6381  bool nullable;
6382 
6384  *string def_val;
6385 
6387  *string comment;
6388 
6390  *int scale;
6391 
6394 
6396 protected:
6398 public:
6399 
6400 
6401  constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string c, *int scale) ;
6402 
6403 
6405 
6409  hash<GenericColumnInfo> getDescriptionHash();
6410 
6411 
6413 
6416 
6417 
6420 
6421 
6423 
6432  abstract list<auto> getAddColumnSql(AbstractTable t);
6433 
6435  string getDropSql(string table_name);
6436 
6437 
6439 
6452  list<auto> getModifySql(AbstractTable t, AbstractColumn c, *hash<auto> opt);
6453 
6454 
6456 
6466  abstract string getRenameSql(AbstractTable t, string new_name);
6467 
6470 
6471 
6473 protected:
6474  abstract bool equalImpl(AbstractColumn c);
6475 public:
6476 
6478 
6494 protected:
6495  abstract list<auto> getModifySqlImpl(AbstractTable t, AbstractColumn c, *hash<auto> opt);
6496 public:
6497  };
6498 
6501 
6502 public:
6503  constructor(*hash c) ;
6504 
6505 
6507  add(string k, AbstractIndex val);
6508 
6509 
6512 
6513 
6515  AbstractIndex take(string k);
6516 
6517 
6520 
6521 
6523 
6539 
6540 
6541  string getElementName();
6542 
6543  };
6544 
6547 
6548 public:
6550  string name;
6551 
6553  bool unique;
6554 
6557 
6558 protected:
6561 
6564 
6565 public:
6566 
6568  constructor(string n, bool u, hash c);
6569 
6570 
6572  string getName();
6573 
6574 
6576  bool hasColumn(string cname);
6577 
6578 
6580  abstract string getCreateSql(string table_name, *hash<auto> opt);
6581 
6583  string getDropSql(string table_name);
6584 
6585 
6588 
6589 
6592 
6593 
6595  abstract bool equalImpl(AbstractIndex ix);
6596 
6598  abstract string getRenameSql(string table_name, string new_name);
6599 
6602 
6603 
6606 
6607 
6610 
6611 
6614 
6615 
6618 
6619 
6622 
6623 
6625  *list<AbstractColumnConstraint> getAllSupportingConstraints();
6626 
6627 
6629  list<auto> getRecreateSql(AbstractDatasource ds, string table_name, *hash<auto> opt);
6630 
6631  };
6632 
6635 
6636 public:
6637  constructor(*hash c) ;
6638 
6639 
6642 
6643 
6646 
6647 
6650 
6651 
6653 
6669 
6670 
6671  string getElementName();
6672 
6673  };
6674 
6677 
6678 public:
6679 
6680 
6681 protected:
6683  string name;
6684 
6685 public:
6686 
6688  constructor(string n);
6689 
6690 
6692  string getName();
6693 
6694 
6696  rename(string n);
6697 
6698 
6700  abstract string getCreateSql(string table_name, *hash<auto> opt);
6701 
6703  string getDropSql(string table_name);
6704 
6705 
6707  abstract list<auto> getRenameSql(string table_name, string new_name);
6708 
6710  string getDisableSql(string table_name);
6711 
6712 
6714  string getEnableSql(string table_name, *hash<auto> opt);
6715 
6716 
6719 
6720 
6722 protected:
6723  abstract bool equalImpl(AbstractConstraint c);
6724 public:
6725 
6727  abstract bool setIndexBase(string ix);
6728 
6730  abstract clearIndex();
6731 
6733  bool hasColumn(string cname);
6734 
6735  };
6736 
6739 
6740 public:
6742  string src;
6743 
6745  constructor(string n, string n_src) ;
6746 
6747 
6749 protected:
6751 public:
6752 
6753 
6755  bool setIndexBase(string ix);
6756 
6757 
6760 
6761  };
6762 
6765 
6766 public:
6767 protected:
6769  *string index;
6770 
6771 public:
6772 
6774  constructor(string name, *hash<auto> cols, *string index) ;
6775 
6776 
6778  constructor(string name, Columns cols, *string index) ;
6779 
6780 
6782  bool setIndexBase(string ix);
6783 
6784 
6787 
6788  };
6789 
6792 
6793 public:
6794 protected:
6796  *hash<string, hash<string, AbstractForeignConstraint>> sourceConstraints;
6797 
6798 public:
6799 
6801  constructor(string name, *hash<auto> cols, *string index) ;
6802 
6803 
6805  constructor(string name, Columns cols, *string index) ;
6806 
6807 
6809 
6814 
6815 
6817  hash<auto> getDisableReenableSql(AbstractDatasource ds, string table_name, *hash<auto> opts);
6818 
6819 
6822 
6823 
6825 
6836 
6837 
6839  removeSourceConstraint(string tname, list<auto> cols);
6840 
6841 
6843  renameSourceConstraintTable(string old_name, string new_name);
6844 
6845 
6847  bool hasColumn(string cname);
6848 
6849 
6851  *string getIndex();
6852 
6853 
6855 protected:
6857 public:
6858 
6859 
6861  abstract string getCreateSql(string table_name, *hash<auto> opts);
6862  };
6863 
6866 
6867 public:
6869  constructor(string n, *hash<auto> c, *string n_index) ;
6870 
6871  };
6872 
6875 
6876 public:
6877  constructor() ;
6878 
6879 
6880  constructor(string n, *hash<auto> c) ;
6881 
6882  };
6883 
6886 
6887 public:
6888  constructor(*hash<auto> c) ;
6889 
6890 
6893 
6894 
6897 
6898 
6901 
6902 
6904 
6920 
6921 
6923  *hash<auto> findConstraintOn(string table, softlist<auto> cols);
6924 
6925 
6927  string getElementName();
6928 
6929  };
6930 
6933 
6934 public:
6936  string table;
6937 
6940 
6942  constructor(string t, Columns c);
6943 
6944 
6947 
6948  };
6949 
6952 
6953 public:
6956 
6957  constructor(string name, Columns cols, ForeignConstraintTarget t) ;
6958 
6959 
6961 protected:
6963 public:
6964 
6965  };
6966 
6969 
6970 public:
6972  string name;
6973 
6976 
6979 
6982 
6984  constructor(string n_name, number n_start = 1, number n_increment = 1, *softnumber n_max);
6985 
6986 
6988  abstract string getCreateSql(*hash<auto> opt);
6989 
6991 
6993  string getDropSql(*hash<auto> opt);
6994 
6995 
6997 
7000  abstract softlist<auto> getRenameSql(string new_name, *hash<auto> opt);
7001  };
7002 
7005 
7006 public:
7007  // ! potential object schema
7008  *string schema;
7009 
7011  string name;
7012 
7014  string src;
7015 
7018 
7020  constructor(string n_name, string n_src);
7021 
7022 
7024  abstract string getCreateSql(*hash<auto> opt);
7025 
7027 
7029  string getDropSql(*hash<auto> opt);
7030 
7031 
7033 
7036  abstract softlist<auto> getRenameSql(string new_name, *hash<auto> opt);
7037  };
7038 
7041 
7042 public:
7044  string name;
7045 
7047  string type;
7048 
7050  string src;
7051 
7053 
7057  constructor(string n, string n_type, string n_src);
7058 
7059 
7061  string getType();
7062 
7063 
7065 
7067  string getDropSql(*hash<auto> opt);
7068 
7069 
7072 
7073 
7075  string getNormalizedSource(string src);
7076 
7077 
7079 protected:
7081 public:
7082  };
7083 
7086 
7087 public:
7089 
7093  constructor(string n, string n_type, string n_src) ;
7094 
7095 
7097  abstract list<auto> getCreateSql(*hash<auto> opt);
7098 
7100 
7103  abstract softlist<auto> getRenameSql(string new_name, *hash<auto> opt);
7104 
7106  setName(string new_name);
7107 
7108  };
7109 
7112 
7113 public:
7114  constructor(*hash c) ;
7115 
7116 
7119 
7120 
7123 
7124 
7126 
7142 
7143 
7144  string getElementName();
7145 
7146  };
7147 
7150 
7151 public:
7153  constructor(string n, string n_src) ;
7154 
7155 
7157  abstract list<auto> getCreateSql(string table_name, *hash<auto> opt);
7158 
7160  abstract softlist<auto> getRenameSql(string table_name, string new_name);
7161 
7163  abstract list<auto> getDropSql(string table_name);
7164  };
7165 
7168 
7169 public:
7170  constructor(*hash c) ;
7171 
7172 
7175 
7176 
7179 
7180 
7182 
7198 
7199 
7200  string getElementName();
7201 
7202  };
7203 };
abstract class for check constraints
Definition: SqlUtil.qm.dox.h:6738
string src
the source of the check clause
Definition: SqlUtil.qm.dox.h:6742
bool equalImpl(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
constructor(string n, string n_src)
creates the object and sets its name and the check clause source
bool setIndexBase(string ix)
returns True if the object supports an index property and is set, False if not
clearIndex()
clears any index base for the constraint
the API for a constraint with columns
Definition: SqlUtil.qm.dox.h:6764
*string index
the index supporting the constraint
Definition: SqlUtil.qm.dox.h:6769
constructor(string name, Columns cols, *string index)
creates the object from the name and a Columns object
clearIndex()
clears any index base for the constraint
constructor(string name, *hash< auto > cols, *string index)
creates the object from the name and a hash of column information
bool setIndexBase(string ix)
returns True if the object supports an index property and is set, False if not
the base class for column information
Definition: SqlUtil.qm.dox.h:6368
abstract list< auto > getAddColumnSql(AbstractTable t)
returns a list of sql strings that can be used to add the column to an existing table
abstract bool equalImpl(AbstractColumn c)
returns True if the argument is equal to the current object, False if not
abstract string getRenameSql(AbstractTable t, string new_name)
returns a string that can be used to rename the column
string native_type
the native type name of the column
Definition: SqlUtil.qm.dox.h:6372
*string comment
comment on the column
Definition: SqlUtil.qm.dox.h:6387
*string def_val
default value for column
Definition: SqlUtil.qm.dox.h:6384
list< auto > getModifySql(AbstractTable t, AbstractColumn c, *hash< auto > opt)
returns a list of sql strings that can be used to modify the column to the new definition; if the col...
constructor()
empty constructor for subclasses
hash< GenericColumnInfo > getDescriptionHash()
Returns a description hash of the column.
abstract list< auto > getModifySqlImpl(AbstractTable t, AbstractColumn c, *hash< auto > opt)
returns a list of sql strings that can be used to modify the column to the new definition
*string qore_type
the equivalent qore type name of the column if known
Definition: SqlUtil.qm.dox.h:6375
string getCreateSql(AbstractTable t)
returns an sql string that can be used to add the column to a table
bool nullable
True if the column can hold a NULL value, False if not
Definition: SqlUtil.qm.dox.h:6381
bool equal(AbstractColumn c)
returns True if the argument is equal to the current object, False if not
const DefaultVarcharSize
Default VARCHAR column size.
Definition: SqlUtil.qm.dox.h:6393
string getNativeTypeString()
returns the string describing the native type that can be used in SQL
int size
the size of the column
Definition: SqlUtil.qm.dox.h:6378
*int scale
the scale for numeric columns
Definition: SqlUtil.qm.dox.h:6390
string getDropSql(string table_name)
returns a string that can be used to drop the column from the table
the API for a constraint with columns
Definition: SqlUtil.qm.dox.h:6791
Qore::AbstractIterator getSourceConstraintIterator()
returns an iterator through all known source foreign constraints on the current table
addSourceConstraint(string tname, AbstractForeignConstraint fk)
adds a foreign constraint source to the unique constraint
*hash< string, hash< string, AbstractForeignConstraint > > sourceConstraints
a hash of ForeignConstraintSources, keyed by table name, the value is a hash of foreign constraints k...
Definition: SqlUtil.qm.dox.h:6796
constructor(string name, Columns cols, *string index)
creates the object from the name and a Columns object
renameSourceConstraintTable(string old_name, string new_name)
renames a table in a source constraint
abstract string getCreateSql(string table_name, *hash< auto > opts)
returns a string that can be used to create the constraint in the database
findMatchingIndex(*Indexes indexes)
find an index that matches the constraint and marks both objects as related
*string getIndex()
returns the name of the associated index, if any
hash< auto > getDisableReenableSql(AbstractDatasource ds, string table_name, *hash< auto > opts)
returns lists of SQL strings to disable this constraint plus any dependent constraints and another li...
removeSourceConstraint(string tname, list< auto > cols)
removes a source constraint
bool equalImpl(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
constructor(string name, *hash< auto > cols, *string index)
creates the object from the name and a hash of column information
bool hasColumn(string cname)
returns True if the constraint references the named column
abstract base class for constraints
Definition: SqlUtil.qm.dox.h:6676
abstract bool setIndexBase(string ix)
returns True if the object supports an index property and is set, False if not
string getDisableSql(string table_name)
returns a string that can be used to temporarily disable the constraint from the database; if disabli...
string getName()
returns the constraint name
constructor(string n)
creates the object and sets its name
abstract list< auto > getRenameSql(string table_name, string new_name)
returns a list of SQL strings that can be used to rename the constraint in the database
bool hasColumn(string cname)
returns True if the constraint references the named column
rename(string n)
renames the constraint
abstract clearIndex()
clears any index base for the constraint
string getDropSql(string table_name)
returns a string that can be used to drop the constraint from the database
string name
the name of the constraint
Definition: SqlUtil.qm.dox.h:6683
abstract bool equalImpl(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
bool equal(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
string getEnableSql(string table_name, *hash< auto > opt)
returns a string that can be used to enable the constraint in the database; if disabling constraints ...
abstract string getCreateSql(string table_name, *hash< auto > opt)
returns a string that can be used to create the constraint in the database
the base class for named objects
Definition: SqlUtil.qm.dox.h:6342
constructor()
empty constructor for subclasses
abstract string getDdlName(string name)
returns the column name with quoting in case the column name is a reserved word
string ddl_name
the name of the object for DDL (in case it's a reserved word)
Definition: SqlUtil.qm.dox.h:6349
string name
the name of the object
Definition: SqlUtil.qm.dox.h:6346
constructor(string name)
creates the object from the name
the base class for foreign key constraint information
Definition: SqlUtil.qm.dox.h:6951
bool equalImpl(AbstractConstraint con)
returns True if the argument is equal to the current object, False if not
ForeignConstraintTarget target
a ForeignConstraintTarget object to describe the target table and columns
Definition: SqlUtil.qm.dox.h:6955
base class for function or objects with code
Definition: SqlUtil.qm.dox.h:7040
string getNormalizedSource(string src)
returns normalized source for comparisons
string name
the name of the object
Definition: SqlUtil.qm.dox.h:7044
abstract bool equalImpl(AbstractFunctionBase t)
returns True if the argument is equal to the current object, False if not
constructor(string n, string n_type, string n_src)
creates the object from the arguments passed
string getDropSql(*hash< auto > opt)
returns a string that can be used to drop the function from the database
bool equal(AbstractFunctionBase t)
returns True if the argument is equal to the current object, False if not
string src
the source of the object
Definition: SqlUtil.qm.dox.h:7050
string type
the type of object
Definition: SqlUtil.qm.dox.h:7047
string getType()
returns the type of object
base class for functions
Definition: SqlUtil.qm.dox.h:7085
abstract softlist< auto > getRenameSql(string new_name, *hash< auto > opt)
returns a list of strings that can be used to rename the function in the database
setName(string new_name)
sets the new name of the object
abstract list< auto > getCreateSql(*hash< auto > opt)
returns a list of SQL strings that can be used to create the function in the database
constructor(string n, string n_type, string n_src)
creates the object from the arguments passed
abstract container class that throws an exception if an unknown key is accessed
Definition: SqlUtil.qm.dox.h:5728
list< auto > values()
Returns a list of values of the contained hash.
bool partialMatchKeys(hash h1)
returns True if the hash argument has at least the same keys (in any order, can have more keys),...
Qore::AbstractIterator pairIterator()
Returns a HashPairIterator object for the contained hash.
bool hasKeyValue(string k)
Returns True if the key exists in the contained hash and is assigned a value, False if not.
abstract auto take(string k)
removes the given key from the contained hash and returns the value
list< string > keys()
Returns a list of key names of the contained hash.
bool partialMatchKeys(list< auto > l)
returns True if the list<auto> argument has at least the same keys (in any order, can have more keys)...
*string firstKey()
Returns the first key name in the contained hash or NOTHING if the contained hash has no keys.
bool partialMatchKeys(AbstractHashContainer c)
returns True if the container argument has at least the same keys (in any order, can have more keys),...
Qore::AbstractIterator iterator()
Returns a HashIterator object for the contained hash.
auto memberGate(string k)
returns the value of the given key in the contained hash if it exists, otherwise throws a KEY-ERROR e...
bool hasKey(string k)
Returns True if the key exists in the contained hash (may or may not be assigned a value),...
*hash< auto > getHash()
returns the hash contained by this object
renameKey(string old_name, string new_name)
renames the given key; maintains the key order
*string lastKey()
Returns the last key name in the contained hash or NOTHING if the contained hash has no keys.
clear()
purges the contained data
bool matchKeys(hash h1)
returns True if the hash argument has the same keys (in any order), False if not
*hash h
the data to be contained
Definition: SqlUtil.qm.dox.h:5735
abstract string getElementName()
must return the name of the contained element
Qore::AbstractIterator keyIterator()
Returns a HashKeyIterator object for the contained hash.
int size()
Returns the number of keys in the contained hash.
constructor(*hash nh)
creates the object with the hash argument passed
bool matchKeys(list< auto > l)
returns True if the list<auto> argument has the same list of key strings as the keys in the object (i...
constructor(AbstractHashContainer old)
creates a copy of the object
bool empty()
returns True if the container is empty, False if not
bool matchKeys(AbstractHashContainer c)
returns True if the container argument has the same keys (in any order), False if not
bool val()
Returns False if the contained hash has no keys, True if it does.
copy(AbstractHashContainer old)
creates a "deep copy" of the object
the abstract base class for index information
Definition: SqlUtil.qm.dox.h:6546
bool equalExceptName(AbstractIndex ix)
returns True if the argument is equal to the current index with the exception of the name,...
bool hasColumn(string cname)
returns True if the constraint references the named column
*AbstractForeignConstraint foreign_constraint
Any foreign constraint that this index supports.
Definition: SqlUtil.qm.dox.h:6563
abstract bool equalImpl(AbstractIndex ix)
returns True if the argument is equal to the current index, False if not
setForeignConstraint()
clears the supporting constraint
*AbstractColumnSupportingConstraint getSupportingConstraint()
returns the supporting constraint, if any
*list< AbstractColumnConstraint > getAllSupportingConstraints()
returns all supporting constraints, if any
abstract string getCreateSql(string table_name, *hash< auto > opt)
returns a string that can be used to create the index in the database
string name
the name of the index
Definition: SqlUtil.qm.dox.h:6550
*AbstractForeignConstraint getForeignConstraint()
returns the supporting constraint, if any
constructor(string n, bool u, hash c)
creates the object from the name, a unique flag, and a hash of column information
setSupportingConstraint(AbstractForeignConstraint c)
tags the index with a column supporting constraint (unique or fk constraint, etc) that the index supp...
setSupportingConstraint(AbstractColumnSupportingConstraint c)
tags the index with a column supporting constraint (unique or fk constraint, etc) that the index supp...
bool unique
True if the index is a unique index, False if not
Definition: SqlUtil.qm.dox.h:6553
*AbstractColumnSupportingConstraint constraint
the AbstractColumnSupportingConstraint that this index supports, if any
Definition: SqlUtil.qm.dox.h:6560
string getName()
returns the index name
setSupportingConstraint()
clears the supporting constraint
bool equal(AbstractIndex ix)
returns True if the argument is equal to the current index, False if not
string getDropSql(string table_name)
returns a string that can be used to drop the index from the database
abstract string getRenameSql(string table_name, string new_name)
returns a string that can be used to rename the index in the database
list< auto > getRecreateSql(AbstractDatasource ds, string table_name, *hash< auto > opt)
returns a list of strings to drop and recreate the current index; if there are dependent constraints,...
Columns columns
an object of class Columns representing the columns in the index
Definition: SqlUtil.qm.dox.h:6556
abstract container class that throws an exception if an unknown key is accessed
Definition: SqlUtil.qm.dox.h:6009
bool empty()
returns True if the container is empty, False if not
list< auto > getList()
returns the list contained by this object
abstract auto get(softint i)
returns the value of the given element in the contained list if it exists, otherwise throws an ELEMEN...
auto take(int i)
removes the given element from the contained list and returns the value
add(auto val)
adds the given value to the list
bool val()
Returns False if the contained list is empty, True if not.
abstract string getElementName()
must return the name of the contained element
constructor(softlist nl)
creates the object with the list<auto> argument passed
Qore::ListIterator iterator()
Returns a ListIterator object for the contained list.
int size()
Returns the number of elements in the contained list.
represents a primary key
Definition: SqlUtil.qm.dox.h:6874
base class for sequences
Definition: SqlUtil.qm.dox.h:6968
*number max
the ending number
Definition: SqlUtil.qm.dox.h:6981
string name
the name of the sequence
Definition: SqlUtil.qm.dox.h:6972
string getDropSql(*hash< auto > opt)
returns a string that can be used to drop the sequence from the database
abstract softlist< auto > getRenameSql(string new_name, *hash< auto > opt)
returns a list of strings that can be used to rename the sequence in the database
abstract string getCreateSql(*hash< auto > opt)
returns a string that can be used to create the sequence in the database
number start
the starting number
Definition: SqlUtil.qm.dox.h:6975
number increment
the increment
Definition: SqlUtil.qm.dox.h:6978
constructor(string n_name, number n_start=1, number n_increment=1, *softnumber n_max)
creates the object from the arguments
the base abstract class for the table implementation
Definition: AbstractTable.qc.dox.h:30
the base class for triggers
Definition: SqlUtil.qm.dox.h:7149
constructor(string n, string n_src)
creates the object and sets its name and the trigger source
abstract list< auto > getCreateSql(string table_name, *hash< auto > opt)
returns a string that can be used to create the trigger in the database
abstract softlist< auto > getRenameSql(string table_name, string new_name)
returns a string that can be used to rename the trigger in the database
abstract list< auto > getDropSql(string table_name)
returns a string that can be used to drop the trigger in the database
represents a unique column constraint
Definition: SqlUtil.qm.dox.h:6865
constructor(string n, *hash< auto > c, *string n_index)
creates the object from the name an a hash of column information
base class for views
Definition: SqlUtil.qm.dox.h:7004
abstract softlist< auto > getRenameSql(string new_name, *hash< auto > opt)
returns a list with command(s) that can be used to rename the view in the database
bool updatable
Flag showing if is the view updatable with DML commands.
Definition: SqlUtil.qm.dox.h:7017
string name
the name of the sequence
Definition: SqlUtil.qm.dox.h:7011
constructor(string n_name, string n_src)
creates the object from the arguments
string src
the source code
Definition: SqlUtil.qm.dox.h:7014
string getDropSql(*hash< auto > opt)
returns a string that can be used to drop the view from the database
abstract string getCreateSql(*hash< auto > opt)
returns a string that can be used to create the view in the database
column container class that throws an exception if an unknown column is accessed
Definition: SqlUtil.qm.dox.h:6286
bool equal(Columns cols)
returns True if the argument has the same columns in the same order as the current object,...
add(string k, AbstractColumn val)
adds the given value to the hash with the given key name
Columns subset(softlist l)
returns a subset of the current columns according to the list<auto> argument
string getElementName()
returns "column" since this object stores column objects
AbstractIterator getSqlColumnNameIterator()
returns an iterator for column SQL names
AbstractColumn memberGate(string k)
returns the AbstractColumn object corresponding to the key given or throws a KEY-ERROR exception
AbstractColumn take(string k)
removes the given key from the contained hash and returns the value
constructor(Columns old)
creates a copy of the object
constructor(*hash c)
creates the object from the argument
constraint container class that throws an exception if an unknown constraint is accessed
Definition: SqlUtil.qm.dox.h:6634
AbstractConstraint memberGate(string k)
returns the AbstractConstraint object corresponding to the key given or throws a KEY-ERROR exception
AbstractConstraint take(string k)
removes the given key from the contained hash and returns the value
*AbstractUniqueConstraint findEqualUniqueConstraint(AbstractUniqueConstraint uk)
finds a unique constraint with the same columns as the unique constraint passed
add(string k, AbstractConstraint val)
adds the given value to the hash with the given key name
string getElementName()
must return the name of the contained element
a class describing a foreign constraint target
Definition: SqlUtil.qm.dox.h:6932
constructor(string t, Columns c)
creates the object and sets the target table name and the target columns
bool equal(ForeignConstraintTarget targ)
returns True if the argument is equal to the current object, False if not
Columns columns
columns in the target table
Definition: SqlUtil.qm.dox.h:6939
string table
the name of the target table
Definition: SqlUtil.qm.dox.h:6936
foreign constraint container class that throws an exception if an unknown constraint is accessed
Definition: SqlUtil.qm.dox.h:6885
AbstractForeignConstraint take(string k)
removes the given key from the contained hash and returns the value
*AbstractForeignConstraint findEqual(AbstractForeignConstraint fk)
find an index with columns equal to the index passed
*hash< auto > findConstraintOn(string table, softlist< auto > cols)
returns either a hash of AbstractColumn information or NOTHING if no foreign constraint can be found ...
add(string k, AbstractForeignConstraint val)
adds the given value to the hash with the given key name
string getElementName()
returns "foreign constraint" for the type of object encapsulated
AbstractForeignConstraint memberGate(string k)
returns the AbstractForeignConstraint object corresponding to the key given or throws a KEY-ERROR exc...
function container class that throws an exception if an unknown function is accessed
Definition: SqlUtil.qm.dox.h:7111
add(string k, AbstractFunction val)
adds the given value to the hash with the given key name
AbstractFunction take(string k)
removes the given key from the contained hash and returns the value
string getElementName()
must return the name of the contained element
AbstractFunction memberGate(string k)
returns the AbstractFunction object corresponding to the key given or throws a KEY-ERROR exception
index container class that throws an exception if an unknown index is accessed
Definition: SqlUtil.qm.dox.h:6500
string getElementName()
must return the name of the contained element
AbstractIndex take(string k)
removes the given key from the contained hash and returns the value
*AbstractIndex tryTake(string k)
tries to remove the given key from the contained hash and returns the value if it exists
*AbstractIndex findEqual(AbstractIndex ix)
find an index with columns equal to the index passed
add(string k, AbstractIndex val)
adds the given value to the hash with the given key name
AbstractIndex memberGate(string k)
returns the AbstractIndex object corresponding to the key given or throws a KEY-ERROR exception
represents a database table; this class embeds an AbstractTable object that is created automatically ...
Definition: Table.qc.dox.h:44
the table container class stores a collection of tables in a schema
Definition: SqlUtil.qm.dox.h:6098
AbstractTable memberGate(string k)
returns the AbstractTable object corresponding to the key given or throws a KEY-ERROR exception
Qore::AbstractIterator dropIterator()
returns an iterator for a list of cached table names in the order that can be used to drop the tables...
constructor(AbstractDatasource ds, hash tables, *hash< auto > opt)
creates and populates the object from a hash<auto> description
populate(AbstractDatasource ds, hash< auto > tables, *hash< auto > opt)
populates the object from a hash<auto> description
bool tableRenamed(string old_name, string new_name, string old_sql_name)
updates table names and internal references for renamed tables
add(AbstractTable val)
adds the given value to the hash with the given key name
list< auto > getDropList()
returns a list of cached table names in the order that can be used to drop the tables,...
add(string k, Table val)
adds the given value to the hash with the given key name
AbstractTable take(string k)
removes the given key from the contained hash and returns the value
*AbstractTable getIfExists(AbstractDatasource ds, string name)
gets a table from the database or from the cache if already cached; if the table does not exist,...
populate(AbstractDatasource ds)
populates the object from tables in the database
constructor(AbstractDatasource ds)
creates and populates the object from tables in the database
add(string k, AbstractTable val)
adds the given value to the hash with the given key name
*string getDropConstraintIfExistsSql(string tname, string cname, *hash< auto > opts)
returns an SQL string that can be used to drop an existing constraint on a table, if the table is not...
*list< auto > getDropAllForeignConstraintsOnTableSql(string name, *hash< auto > opt)
returns a list of SQL strings that can be used to drop all the foreign constraints on a particular ta...
add(Table val)
adds the given value to the hash with the given key name
*string getRenameTableIfExistsSql(string old_name, string new_name, *hash< auto > opts)
returns an SQL string that can be used to rename the given table if it exists and the target does not...
string getElementName()
returns "table" since this object stores AbstractTable objects
constructor()
creates an empty object
AbstractTable get(AbstractDatasource ds, string name)
gets a table from the database or from the cache if already cached
trigger container class that throws an exception if an unknown trigger is accessed
Definition: SqlUtil.qm.dox.h:7167
AbstractTrigger memberGate(string k)
returns the AbstractTrigger object corresponding to the key given or throws a KEY-ERROR exception
AbstractTrigger take(string k)
removes the given key from the contained hash and returns the value
add(string k, AbstractTrigger val)
adds the given value to the hash with the given key name
string getElementName()
must return the name of the contained element
const SZ_NONE
Definition: SqlUtil.qm.dox.h:2470
const SZ_NUM
the data type is numeric so takes an optional precision and scale
Definition: SqlUtil.qm.dox.h:2479
const SZ_MAND
the data type takes a mandatory size parameter
Definition: SqlUtil.qm.dox.h:2473
const SZ_OPT
the data type takes an optional size parameter
Definition: SqlUtil.qm.dox.h:2476
const CLOB
specifies a large variable-length character column (ie CLOB or TEXT, etc)
Definition: SqlUtil.qm.dox.h:2462
const DB_SYNONYMS
Feature: synonyms.
Definition: SqlUtil.qm.dox.h:2442
const DB_PACKAGES
Feature: packages.
Definition: SqlUtil.qm.dox.h:2430
const VARCHAR
Definition: SqlUtil.qm.dox.h:2450
const DB_MVIEWS
Feature: materialized views / snapshots.
Definition: SqlUtil.qm.dox.h:2428
const BLOB
specifies a large variable-length binary column (ie BLOB or BYTEA, etc)
Definition: SqlUtil.qm.dox.h:2459
const DB_PROCEDURES
Feature: procedures.
Definition: SqlUtil.qm.dox.h:2432
const DB_TABLES
Feature: tables.
Definition: SqlUtil.qm.dox.h:2436
const DB_VIEWS
Feature: views.
Definition: SqlUtil.qm.dox.h:2440
const CHAR
specifies a CHAR column
Definition: SqlUtil.qm.dox.h:2456
const NUMERIC
specifies a numeric column (equivalent to Qore::Type::Number)
Definition: SqlUtil.qm.dox.h:2453
const DB_FUNCTIONS
Feature: functions.
Definition: SqlUtil.qm.dox.h:2426
const DB_SEQUENCES
Feature: sequences.
Definition: SqlUtil.qm.dox.h:2434
const DB_TYPES
Feature: named types.
Definition: SqlUtil.qm.dox.h:2438
const True
const False
number exp(number n)
bool exists(...)
const NULL
hash< ColumnOperatorInfo > cop_year_hour(auto column)
returns a ColumnOperatorInfo hash for the "year_hour" operator with the given argument
hash< ColumnOperatorInfo > cop_seq_currval(string seq, *string as)
returns a ColumnOperatorInfo hash for the "seq_currval" operator with the given argument giving the s...
hash< ColumnOperatorInfo > cop_coalesce(auto col1, auto col2)
returns a ColumnOperatorInfo hash for the "coalesce" operator with the given column arguments; the fi...
hash< ColumnOperatorInfo > cop_percent_rank()
Analytic/window function: relative rank of the current row.
hash< ColumnOperatorInfo > cop_dense_rank()
Analytic/window function: rank of the current row without gaps.
hash< ColumnOperatorInfo > cop_prepend(auto column, string arg)
returns a ColumnOperatorInfo hash for the "prepend" operator with the given argument
hash< ColumnOperatorInfo > cop_ntile(int value)
Analytic/window function: integer ranging from 1 to the argument value, dividing the partition as equ...
hash< ColumnOperatorInfo > cop_avg(auto column)
returns a ColumnOperatorInfo hash for the "avg" operator; returns average column values
hash< ColumnOperatorInfo > cop_over(auto column, *string partitionby, *string orderby)
returns a ColumnOperatorInfo hash for the "over" clause
hash< ColumnOperatorInfo > cop_sum(auto column)
returns a ColumnOperatorInfo hash for the "sum" operator; returns the total sum of a numeric column.
hash< ColumnOperatorInfo > cop_value(auto arg)
returns a ColumnOperatorInfo hash for the "value" (literal) operator with the given argument
hash< ColumnOperatorInfo > cop_rank()
Analytic/window function: rank of the current row with gaps.
hash< ColumnOperatorInfo > cop_year_month(auto column)
returns a ColumnOperatorInfo hash for the "year_month" operator with the given argument
hash< ColumnOperatorInfo > cop_upper(auto column)
returns a ColumnOperatorInfo hash for the "upper" operator with the given argument; returns a column ...
hash< ColumnOperatorInfo > cop_trunc_date(auto column, string mask)
Truncates a date column or value regarding the given mask. The resulting value remains Qore::date (no...
hash< ColumnOperatorInfo > cop_plus(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "+" operator with the given arguments
hash< ColumnOperatorInfo > cop_append(auto column, string arg)
returns a ColumnOperatorInfo hash for the "append" operator with the given argument
hash< ColumnOperatorInfo > cop_as(auto column, string arg)
returns a ColumnOperatorInfo hash for the "as" operator with the given argument
hash< ColumnOperatorInfo > cop_last_value(any column)
Analytic/window function: value evaluated at the row that is the last row of the window frame.
hash< ColumnOperatorInfo > cop_lower(auto column)
returns a ColumnOperatorInfo hash for the "lower" operator with the given argument; returns a column ...
hash< ColumnOperatorInfo > cop_cume_dist()
Analytic/window function: relative rank of the current row.
hash< ColumnOperatorInfo > cop_year(auto column)
returns a ColumnOperatorInfo hash for the "year" operator with the given argument
hash< ColumnOperatorInfo > cop_divide(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "/" operator with the given arguments
hash< ColumnOperatorInfo > cop_distinct(auto column)
returns a ColumnOperatorInfo hash for the "distinct" operator with the given argument; returns distin...
hash< ColumnOperatorInfo > make_cop(string cop, auto column, auto arg)
hash< ColumnOperatorInfo > cop_multiply(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "*" operator with the given arguments
hash< ColumnOperatorInfo > cop_length(auto column)
returns a ColumnOperatorInfo hash for the "len" operator with the given argument; returns the length ...
hash< ColumnOperatorInfo > cop_count(auto column='')
returns a ColumnOperatorInfo hash for the "count" operator; returns row counts
hash< ColumnOperatorInfo > cop_max(auto column)
returns a ColumnOperatorInfo hash for the "max" operator; returns maximum column values
hash< ColumnOperatorInfo > cop_substr(auto column, int start, *int count)
returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a subst...
hash< ColumnOperatorInfo > cop_year_day(auto column)
returns a ColumnOperatorInfo hash for the "year_day" operator with the given argument
hash< ColumnOperatorInfo > cop_row_number()
Analytic/window function: number of the current row within its partition, counting from 1.
hash< ColumnOperatorInfo > cop_seq(string seq, *string as)
returns a ColumnOperatorInfo hash for the "seq" operator with the given argument giving the sequence ...
hash< ColumnOperatorInfo > cop_min(auto column)
returns a ColumnOperatorInfo hash for the "min" operator; returns minimum column values
hash< ColumnOperatorInfo > cop_cast(auto column, string arg, auto arg1, auto arg2)
returns a ColumnOperatorInfo hash for the "cast" operator with the given argument(s)
hash< ColumnOperatorInfo > cop_minus(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "-" operator with the given arguments
hash< ColumnOperatorInfo > cop_first_value(any column)
Analytic/window function: value evaluated at the row that is the first row of the window frame.
const DT_YEAR
Definition: SqlUtil.qm.dox.h:3598
const DT_MINUTE
Format unit: minute.
Definition: SqlUtil.qm.dox.h:3610
const DT_SECOND
Format unit: hour.
Definition: SqlUtil.qm.dox.h:3613
const DT_MONTH
Format unit: month.
Definition: SqlUtil.qm.dox.h:3601
const DT_HOUR
Format unit: hour.
Definition: SqlUtil.qm.dox.h:3607
const DT_DAY
Format unit: day.
Definition: SqlUtil.qm.dox.h:3604
const COP_MAX
to return the maximum value
Definition: SqlUtil.qm.dox.h:2534
const COP_OVER
the SQL "over" clause
Definition: SqlUtil.qm.dox.h:2554
const COP_SEQ_CURRVAL
to return the last value of a sequence issued in the same session
Definition: SqlUtil.qm.dox.h:2604
const COP_LENGTH
to get the length of a text field
Definition: SqlUtil.qm.dox.h:2621
const COP_RANK
Analytic (window) function: RANK.
Definition: SqlUtil.qm.dox.h:2679
const COP_VALUE
to append a constant value (SQL Literal) to use as an output column value
Definition: SqlUtil.qm.dox.h:2509
const COP_COALESCE
to return the first non-null argument in the list
Definition: SqlUtil.qm.dox.h:2609
const COP_MIN
to return the minimum value
Definition: SqlUtil.qm.dox.h:2529
const COP_YEAR_HOUR
to return a date value with year to hextern information
Definition: SqlUtil.qm.dox.h:2594
const COP_DIVIDE
the SQL "divide" operator
Definition: SqlUtil.qm.dox.h:2569
const COP_YEAR
to return a date value with year information only
Definition: SqlUtil.qm.dox.h:2579
const COP_COUNT
to return the row count
Definition: SqlUtil.qm.dox.h:2549
const COP_MULTIPLY
the SQL "multiply" operator
Definition: SqlUtil.qm.dox.h:2574
const COP_AVG
to return the average value
Definition: SqlUtil.qm.dox.h:2539
const COP_PLUS
the SQL "plus" operator
Definition: SqlUtil.qm.dox.h:2564
const COP_CUME_DIST
Analytic (window) function: CUME_DIST.
Definition: SqlUtil.qm.dox.h:2637
const COP_SUBSTR
to extract a substring from a column
Definition: SqlUtil.qm.dox.h:2614
const COP_SEQ
to return the next value of a sequence
Definition: SqlUtil.qm.dox.h:2599
const COP_APPEND
to append a string to a column on output
Definition: SqlUtil.qm.dox.h:2504
const COP_PERCENT_RANK
Analytic (window) function: PERCENT_RANK.
Definition: SqlUtil.qm.dox.h:2672
const COP_NTILE
Analytic (window) function: NTILE.
Definition: SqlUtil.qm.dox.h:2665
const COP_YEAR_MONTH
to return a date value with year to month information
Definition: SqlUtil.qm.dox.h:2584
const COP_PREPEND
to prepend a string to a column on output
Definition: SqlUtil.qm.dox.h:2499
const COP_LOWER
to return column value in lower case
Definition: SqlUtil.qm.dox.h:2519
const COP_AS
Definition: SqlUtil.qm.dox.h:2489
const COP_CAST
to convert column value into another datatype
Definition: SqlUtil.qm.dox.h:2494
const COP_ROW_NUMBER
Analytic (window) function: ROW_NUMBER.
Definition: SqlUtil.qm.dox.h:2686
const COP_YEAR_DAY
to return a date value with year to day information
Definition: SqlUtil.qm.dox.h:2589
const COP_UPPER
to return column value in upper case
Definition: SqlUtil.qm.dox.h:2514
const COP_DENSE_RANK
Analytic (window) function: DENSE_RANK.
Definition: SqlUtil.qm.dox.h:2644
const COP_TRUNC_DATE
to return the date with truncated value
Definition: SqlUtil.qm.dox.h:2630
const COP_MINUS
the SQL "minus" operator
Definition: SqlUtil.qm.dox.h:2559
const COP_LAST_VALUE
Analytic (window) function: LAST_VALUE.
Definition: SqlUtil.qm.dox.h:2658
const COP_FIRST_VALUE
Analytic (window) function: FIRST_VALUE.
Definition: SqlUtil.qm.dox.h:2651
const DefaultCopMap
a hash of default column operator descriptions
Definition: SqlUtil.qm.dox.h:2722
const COP_DISTINCT
to return distinct values
Definition: SqlUtil.qm.dox.h:2524
const COP_SUM
to return the sum value
Definition: SqlUtil.qm.dox.h:2544
hash< InsertOperatorInfo > iop_seq_currval(string arg)
returns an InsertOperatorInfo hash for retrieving the current value of the given sequence in insert q...
hash< InsertOperatorInfo > make_iop(string iop, auto arg)
hash< InsertOperatorInfo > iop_seq(string arg)
returns an InsertOperatorInfo hash for retrieving the value of the given sequence in insert queries
const IOP_SEQ
Definition: SqlUtil.qm.dox.h:5647
const DefaultIopMap
a hash of default insert operator descriptions (currently empty, all operators are driver-dependent)
Definition: SqlUtil.qm.dox.h:5655
const IOP_SEQ_CURRVAL
for using the last value of a sequence issued in the current session
Definition: SqlUtil.qm.dox.h:5652
hash< string, hash< JoinOperatorInfo > > join_right_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash< auto > opt)
returns a hash for right outer joins with the given arguments for use when joining with a table other...
hash< string, hash< JoinOperatorInfo > > join_left_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash< auto > opt)
returns a hash for left outer joins with the given arguments for use when joining with a table other ...
hash< string, hash< JoinOperatorInfo > > join_inner_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash< auto > opt)
returns a hash for standard inner joins with the given arguments for use when joining with a table ot...
hash< string, hash< JoinOperatorInfo > > join_inner(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash< auto > opt)
returns a hash for standard inner joins with the given arguments
hash< string, hash< JoinOperatorInfo > > join_left(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash< auto > opt)
returns a hash for left outer joins with the given arguments
hash< string, hash< JoinOperatorInfo > > join_right(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash< auto > opt)
returns a hash for right outer joins with the given arguments
hash< string, hash< JoinOperatorInfo > > make_jop(string jop, AbstractTable table, *string alias, *hash jcols, *hash cond, *string ta, *hash< auto > opt)
const JopMap
a hash of valid join operators
Definition: SqlUtil.qm.dox.h:3835
const JOP_INNER
Definition: SqlUtil.qm.dox.h:3822
const JOP_RIGHT
for right outer joins
Definition: SqlUtil.qm.dox.h:3832
const JOP_LEFT
for left outer joins
Definition: SqlUtil.qm.dox.h:3827
hash< OperatorInfo > op_ge(auto arg)
returns an OperatorInfo hash for the ">=" operator with the given argument for use in where clauses w...
hash< OperatorInfo > op_substr(int start, *int count, string text)
returns an OperatorInfo hash for the "substr" operator with the given arguments; for use in where cla...
hash< OperatorInfo > op_cle(string arg)
returns an OperatorInfo hash for the "<=" operator with the given argument for use in where clauses w...
hash< OperatorInfo > op_ne(auto arg)
returns an OperatorInfo hash for the "!=" or "<>" operator with the given argument for use in where c...
hash< OperatorInfo > op_eq(auto arg)
returns an OperatorInfo hash for the "=" operator with the given argument for use in where clauses wh...
hash< OperatorInfo > op_ceq(string arg)
returns an OperatorInfo hash for the "=" operator with the given argument for use in where clauses wh...
hash< OperatorInfo > op_in()
returns an OperatorInfo hash for the "in" operator with all arguments passed to the function; for use...
hash< OperatorInfo > op_like(string str)
returns an OperatorInfo hash for the "like" operator with the given argument for use in where clauses
hash< OperatorInfo > op_in_select(string table, hash< auto > select_hash)
returns an OperatorInfo hash for the "in" operator with a subquery as the argument; for use in where ...
hash< OperatorInfo > make_op(string op, auto arg)
hash< OperatorInfo > op_cne(string arg)
returns an OperatorInfo hash for the "!=" or "<>" operator with the given argument for use in where c...
hash< string, hash< OperatorInfo > > wop_or(hash h1, hash h2)
returns an OperatorInfo hash with a fake "_OR_" column name; the list of arguments to the function is...
hash< OperatorInfo > op_between(auto l, auto r)
returns an OperatorInfo hash for the "between" operator with the given arguments, neither of which ca...
hash< OperatorInfo > op_cge(string arg)
returns an OperatorInfo hash for the ">=" operator with the given argument for use in where clauses w...
hash< OperatorInfo > op_clt(string arg)
returns an OperatorInfo hash for the "<" operator with the given argument for use in where clauses wh...
hash< OperatorInfo > op_lt(auto arg)
returns an OperatorInfo hash for the "<" operator with the given argument for use in where clauses wh...
hash< OperatorInfo > op_cgt(string arg)
returns an OperatorInfo hash for the ">" operator with the given argument for use in where clauses wh...
hash< OperatorInfo > op_gt(auto arg)
returns an OperatorInfo hash for the ">" operator with the given argument for use in where clauses wh...
hash< OperatorInfo > op_not(hash arg)
returns an OperatorInfo hash for the "not" operator; for use in where clauses
hash< OperatorInfo > op_le(auto arg)
returns an OperatorInfo hash for the "<=" operator with the given argument for use in where clauses w...
const OP_GE
the SQL greater than or equals operator (>=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4329
const OP_NE
the SQL not equals operator (!= or <>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4334
const OP_LE
the SQL less than or equals (<=) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4319
const OP_EQ
the SQL equals operator (=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4339
const OP_IN
the SQL "in" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4379
const OP_NOT
the SQL "not" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4384
const DefaultExpressionMap
Default expressions to be supported by all SqlUtil modules.
Definition: SqlUtil.qm.dox.h:4402
const DefaultOpMap
a hash of valid operators for use in Where Clauses
Definition: SqlUtil.qm.dox.h:5109
const OP_SUBSTR
the SQL "substr" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4389
const OP_CGE
the SQL greater than or equals operator (>=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4359
const OP_LT
the SQL less than (<) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4314
const OP_CLE
the SQL less than or equals (<=) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4349
const OP_BETWEEN
the SQL "between" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4374
const OP_GT
the SQL greater than operator (>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4324
const OP_CNE
the SQL not equals operator (!= or <>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4364
const OP_CLT
the SQL less than (<) operator for use in Where Clauses when comparing two columns
Definition: SqlUtil.qm.dox.h:4344
const OP_OR
to combine SQL expressions with "or" for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4394
const OP_IN_SELECT
the SQL "in" operator with a select query for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4399
const OP_CGT
the SQL greater than operator (>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4354
const OP_CEQ
the SQL equals operator (=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4369
const OP_LIKE
Definition: SqlUtil.qm.dox.h:4309
hash< UpdateOperatorInfo > uop_seq(string seq)
returns an UpdateOperatorInfo hash for the "seq" operator with the given argument giving the sequence...
hash< UpdateOperatorInfo > uop_minus(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "-" operator with the given arguments
hash< UpdateOperatorInfo > uop_plus(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "+" operator with the given arguments
hash< UpdateOperatorInfo > uop_seq_currval(string seq)
returns an UpdateOperatorInfo hash for the "seq" operator with the given argument giving the sequence...
hash< UpdateOperatorInfo > uop_divide(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "/" operator with the given arguments
hash< UpdateOperatorInfo > uop_upper(*hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "upper" operator with the given argument; returns a column...
hash< UpdateOperatorInfo > uop_prepend(string arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "prepend" operator with the given argument
hash< UpdateOperatorInfo > make_uop(string uop, auto arg, *hash< UpdateOperatorInfo > nest)
hash< UpdateOperatorInfo > uop_lower(*hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "lower" operator with the given argument; returns a column...
hash< UpdateOperatorInfo > uop_multiply(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "*" operator with the given arguments
hash< UpdateOperatorInfo > uop_substr(int start, *int count, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "substr" operator with the given arguments; returns a subs...
hash< UpdateOperatorInfo > uop_append(string arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "append" or concatenate operator with the given argument
const DefaultUopMap
Definition: SqlUtil.qm.dox.h:3624
string sprintf(string fmt,...)
const NT_STRING
hash< auto > hash(object obj)
string type(auto arg)
number number(softnumber n)
Qore AbstractDatabase class definition.
Definition: AbstractDatabase.qc.dox.h:26
sqlutil_register_ds_deserializer(*code new_ds_get)
registers a closure or call reference taking a string type and string datasource configuration that w...
sqlutil_register_ds_serializer(*code new_ds_do)
registers a closure or call reference taking a string type and string datasource configuration that w...
const SqlUtilDrivers
known drivers
Definition: SqlUtil.qm.dox.h:5702
column operator info hash as returned by all column operator functions
Definition: SqlUtil.qm.dox.h:2355
string cop
the column operator string code
Definition: SqlUtil.qm.dox.h:2356
auto arg
optional argument
Definition: SqlUtil.qm.dox.h:2358
auto column
column sopecifier, may be a string or a complex hash
Definition: SqlUtil.qm.dox.h:2357
generic column description hash in schema descriptions
Definition: SqlUtil.qm.dox.h:2325
bool notnull
if the column should have a "not null" constraint on it; if missing the default value is False
Definition: SqlUtil.qm.dox.h:2341
hash< string, hash > driver
this key can optionally contain a hash keyed by driver name which contains a hash of values that will...
Definition: SqlUtil.qm.dox.h:2343
auto default_value
the default value for the column
Definition: SqlUtil.qm.dox.h:2335
softint scale
for numeric data types, this value gives the scale
Definition: SqlUtil.qm.dox.h:2333
softint size
for data types requiring a size component, the size; for numeric columns this represents the precisio...
Definition: SqlUtil.qm.dox.h:2331
bool default_value_native
a boolean flag to say if a default_value should be validated against table column type (False) or use...
Definition: SqlUtil.qm.dox.h:2337
*string comment
an optional comment for the column
Definition: SqlUtil.qm.dox.h:2339
string qore_type
a qore type string that will be converted to a native DB type with some default conversion
Definition: SqlUtil.qm.dox.h:2327
string native_type
the native database column type; if both native_type and qore_type are given then native_type is used
Definition: SqlUtil.qm.dox.h:2329
*bool auto_increment
True for DBs that support an auto-increment column
Definition: SqlUtil.qm.dox.h:2345
insert operator info hash as returned by all insert operator functions
Definition: SqlUtil.qm.dox.h:2362
string _iop
the insert operator string code
Definition: SqlUtil.qm.dox.h:2363
any arg
optional argument
Definition: SqlUtil.qm.dox.h:2364
join operator info hash as returned by all join operator functions
Definition: SqlUtil.qm.dox.h:2375
*hash cond
additional conditions for the join clause for the table argument; see Where Clauses for more informat...
Definition: SqlUtil.qm.dox.h:2380
*string alias
optional alias for table in the query
Definition: SqlUtil.qm.dox.h:2378
*string ta
optional table name or alias of the other table to join with when not joining with the primary table
Definition: SqlUtil.qm.dox.h:2381
auto table
the table to join with (either an AbstractTable object or a string table name)
Definition: SqlUtil.qm.dox.h:2377
*hash jcols
the columns to use for the join, the keys will be columns in the source table and the values are colu...
Definition: SqlUtil.qm.dox.h:2379
*hash< auto > opt
optional join options (for example, to specify a partition for the join if supported)
Definition: SqlUtil.qm.dox.h:2382
string jop
the join operator string code
Definition: SqlUtil.qm.dox.h:2376
SQL operator info hash as returned by all operator functions.
Definition: SqlUtil.qm.dox.h:2349
auto arg
optional argument
Definition: SqlUtil.qm.dox.h:2351
string op
the operator string code
Definition: SqlUtil.qm.dox.h:2350
Query information.
Definition: SqlUtil.qm.dox.h:2689
*hash< auto > expression_map
The expression map.
Definition: SqlUtil.qm.dox.h:2709
*hash< string, AbstractTable > join_map
Tables in this join; table alias -> table.
Definition: SqlUtil.qm.dox.h:2700
*hash< auto > pseudo_column_map
Any valid pseudocolumns or aliases generated in th query.
Definition: SqlUtil.qm.dox.h:2706
*hash< auto > where_operator_map
The old-style backwards-compatible "where operator" map.
Definition: SqlUtil.qm.dox.h:2712
object table
The primary table object.
Definition: SqlUtil.qm.dox.h:2691
*hash< auto > query_hash
The original query hash.
Definition: SqlUtil.qm.dox.h:2694
*hash< auto > column_operator_map
Any custom column operator map.
Definition: SqlUtil.qm.dox.h:2715
list< auto > args
arguments to bind in the query generated
Definition: SqlUtil.qm.dox.h:2718
*hash< auto > query_options
Any query options.
Definition: SqlUtil.qm.dox.h:2697
*hash< string, bool > subquery_column_map
When executing a superquery, we can only reference colums in the subquery.
Definition: SqlUtil.qm.dox.h:2703
A hash describing SQL and arguments for an SQL DML command.
Definition: SqlUtil.qm.dox.h:2411
string sql
the SQL string for the update
Definition: SqlUtil.qm.dox.h:2413
list< auto > args
the arguments for the string
Definition: SqlUtil.qm.dox.h:2416
column data type options
Definition: SqlUtil.qm.dox.h:2386
*string number_format
optional format string for converting strings to numeric / decimal / number columns
Definition: SqlUtil.qm.dox.h:2397
*TimeZone data_timezone
the timezone when converting dates from external data to the DB's date
Definition: SqlUtil.qm.dox.h:2391
*softint maxlen
optional additional limit to the maximum length of the data
Definition: SqlUtil.qm.dox.h:2405
*TimeZone db_timezone
the timezone to use when sending date/time values to the DB
Definition: SqlUtil.qm.dox.h:2394
*softbool mandatory
optional flag to overrude any nullable attribute and require data for the column
Definition: SqlUtil.qm.dox.h:2400
*string date_format
optional format string for converting strings to dates for date / timestamp columns
Definition: SqlUtil.qm.dox.h:2388
update operator info hash as returned by all update operator functions
Definition: SqlUtil.qm.dox.h:2368
*hash nest
option nested operation hash
Definition: SqlUtil.qm.dox.h:2371
auto arg
optional argument
Definition: SqlUtil.qm.dox.h:2370
string uop
the update operator string code
Definition: SqlUtil.qm.dox.h:2369