SqlUtil.qm.dox.h

// -*- mode: c++; indent-tabs-mode: nil -*-
 
/*  SqlUtil.qm Copyright (C) 2013 - 2023 Qore Technologies, s.r.o.
 
    Permission is hereby granted, free of charge, to any person obtaining a
    copy of this software and associated documentation files (the "Software"),
    to deal in the Software without restriction, including without limitation
    the rights to use, copy, modify, merge, publish, distribute, sublicense,
    and/or sell copies of the Software, and to permit persons to whom the
    Software is furnished to do so, subject to the following conditions:
 
    The above copyright notice and this permission notice shall be included in
    all copies or substantial portions of the Software.
 
    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
    FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
    DEALINGS IN THE SOFTWARE.
*/
 
// minimum required Qore version
 
// requires the Util module
 
// requires the DataProvider module
 
// don't use "$" signs for variables and class members, assume local variable scope
 
// require type definitions everywhere
 
// enable all warnings
 
 
// version history is included below in the docs
 
namespace SqlUtil {
    public hashdecl GenericColumnInfo {
        string qore_type;
        string native_type;
        softint size;
        softint scale;
        auto default_value;
        bool default_value_native = False;
        *string comment;
        bool notnull = False;
        hash<string, hash> driver;
        *bool auto_increment;
    };
 
    public hashdecl OperatorInfo {
        string op;   
        auto arg;     
    };
 
    public hashdecl ColumnOperatorInfo {
        string cop;  
        auto column;  
        auto arg;     
    };
 
    public hashdecl InsertOperatorInfo {
        string _iop;  
        any arg;      
    };
 
    public hashdecl UpdateOperatorInfo {
        string uop;  
        auto arg;     
        *hash nest;  
    };
 
    public hashdecl JoinOperatorInfo {
        string jop;           
        auto table;           
        *string alias;        
        *hash jcols;          
        *hash cond;           
        *string ta;           
        *hash<auto> opt;      
    };
 
    public hashdecl SqlUtilDataTypeOptionInfo {
        *string date_format;
 
        *TimeZone data_timezone;
 
        *TimeZone db_timezone;
 
        *string number_format;
 
        *softbool mandatory;
 
 
        *softint maxlen;
    };
 
 
    public hashdecl SqlCommandInfo {
        string sql;
 
        list<auto> args;
    };
 
 
    const DB_FUNCTIONS = "functions";
    const DB_MVIEWS = "materialized views";
    const DB_PACKAGES = "packages";
    const DB_PROCEDURES = "procedures";
    const DB_SEQUENCES = "sequences";
    const DB_TABLES = "tables";
    const DB_TYPES = "named types";
    const DB_VIEWS = "views";
    const DB_SYNONYMS = "synonyms";
 
    /* @defgroup SqlTypeConstants SQL Type Constants
        These constants can be used for the \c "qore_type" values when creating columns to specify additional SQL column types
    */
    const VARCHAR = "string";
 
    const NUMERIC = "number";
 
    const CHAR = "char";
 
    const BLOB = "blob";
 
    const CLOB = "clob";
 
    const SZ_NONE = 0;
 
    const SZ_MAND = 1;
 
    const SZ_OPT = 2;
 
    const SZ_NUM = 3;
 
 
    const COP_AS = "as";
 
 
    const COP_CAST = "cast";
 
 
    const COP_PREPEND = "prepend";
 
 
    const COP_APPEND = "append";
 
 
    const COP_VALUE = "value";
 
 
    const COP_UPPER = "upper";
 
 
    const COP_LOWER = "lower";
 
 
    const COP_DISTINCT = "distinct";
 
 
    const COP_MIN = "min";
 
 
    const COP_MAX = "max";
 
 
    const COP_AVG = "avg";
 
 
    const COP_SUM = "sum";
 
 
    const COP_COUNT = "count";
 
 
    const COP_OVER = "over";
 
 
    const COP_MINUS = "minus";
 
 
    const COP_PLUS = "plus";
 
 
    const COP_DIVIDE = "divide";
 
 
    const COP_MULTIPLY = "multiply";
 
 
    const COP_YEAR = "year";
 
 
    const COP_YEAR_MONTH = "year_month";
 
 
    const COP_YEAR_DAY = "year_day";
 
 
    const COP_YEAR_HOUR = "year_hour";
 
 
    const COP_SEQ = "seq";
 
 
    const COP_SEQ_CURRVAL = "seq_currval";
 
 
    const COP_COALESCE = "coalesce";
 
 
    const COP_SUBSTR = "substr";
 
 
    const COP_LENGTH = "length";
 
 
    const COP_TRUNC_DATE = "truncate_date";
 
 
    const COP_CUME_DIST = "cume_dist";
 
 
    const COP_DENSE_RANK = "dense_rank";
 
 
    const COP_FIRST_VALUE = "first_value";
 
 
    const COP_LAST_VALUE = "last_value";
 
 
    const COP_NTILE = "ntile";
 
 
    const COP_PERCENT_RANK = "percent_rank";
 
 
    const COP_RANK = "rank";
 
 
    const COP_ROW_NUMBER = "row_number";
 
    public hashdecl QueryInfo {
        object table;
 
        *hash<auto> query_hash;
 
        *hash<auto> query_options;
 
        *hash<string, AbstractTable> join_map;
 
        *hash<string, bool> subquery_column_map;
 
        *hash<auto> pseudo_column_map;
 
        *hash<auto> expression_map;
 
        *hash<auto> where_operator_map;
 
        *hash<auto> column_operator_map;
 
        list<auto> args;
    };
 
    const DefaultCopMap = ...;
 
 
 
     hash<ColumnOperatorInfo> make_cop(string cop, auto column, auto arg);
 
 
 
     hash<ColumnOperatorInfo> cop_as(auto column, string arg);
 
 
 
     hash<ColumnOperatorInfo> cop_cast(auto column, string arg, auto arg1, auto arg2);
 
 
 
     hash<ColumnOperatorInfo> cop_prepend(auto column, string arg);
 
 
 
     hash<ColumnOperatorInfo> cop_append(auto column, string arg);
 
 
 
     hash<ColumnOperatorInfo> cop_value(auto arg);
 
 
 
     hash<ColumnOperatorInfo> cop_upper(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_lower(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_distinct(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_min(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_max(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_avg(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_sum(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_count(auto column = '');
 
 
 
     hash<ColumnOperatorInfo> cop_over(auto column, *string partitionby, *string orderby);
 
 
 
     hash<ColumnOperatorInfo> cop_minus(auto column1, auto column2);
 
 
 
     hash<ColumnOperatorInfo> cop_plus(auto column1, auto column2);
 
 
 
     hash<ColumnOperatorInfo> cop_divide(auto column1, auto column2);
 
 
 
     hash<ColumnOperatorInfo> cop_multiply(auto column1, auto column2);
 
 
 
     hash<ColumnOperatorInfo> cop_year(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_year_month(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_year_day(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_year_hour(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_seq(string seq, *string as);
 
 
 
     hash<ColumnOperatorInfo> cop_seq_currval(string seq, *string as);
 
 
 
     hash<ColumnOperatorInfo> cop_coalesce(auto col1, auto col2);
 
 
 
     hash<ColumnOperatorInfo> cop_substr(auto column, int start, *int count);
 
 
 
     hash<ColumnOperatorInfo> cop_length(auto column);
 
 
 
     hash<ColumnOperatorInfo> cop_trunc_date(auto column, string mask);
 
 
 
 
     hash<ColumnOperatorInfo> cop_cume_dist();
 
 
 
     hash<ColumnOperatorInfo> cop_dense_rank();
 
 
 
     hash<ColumnOperatorInfo> cop_first_value(any column);
 
 
 
     hash<ColumnOperatorInfo> cop_last_value(any column);
 
 
 
     hash<ColumnOperatorInfo> cop_ntile(int value);
 
 
 
     hash<ColumnOperatorInfo> cop_percent_rank();
 
 
 
     hash<ColumnOperatorInfo> cop_rank();
 
 
 
     hash<ColumnOperatorInfo> cop_row_number();
 
 
 
    const DT_YEAR = "Y";
 
    const DT_MONTH = "M";
 
    const DT_DAY = "D";
 
    const DT_HOUR = "H";
 
    const DT_MINUTE = "m";
 
    const DT_SECOND = "S";
 
    // let's simulate and enum here'
    const DT_ALL_VALUES = ( DT_YEAR, DT_MONTH, DT_DAY, DT_HOUR, DT_MINUTE, DT_SECOND );
 
    const DefaultUopMap = ...;
 
 
 
     hash<UpdateOperatorInfo> make_uop(string uop, auto arg, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_prepend(string arg, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_append(string arg, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_upper(*hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_lower(*hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_substr(int start, *int count, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_plus(auto arg, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_minus(auto arg, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_multiply(auto arg, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_divide(auto arg, *hash<UpdateOperatorInfo> nest);
 
 
 
     hash<UpdateOperatorInfo> uop_seq(string seq);
 
 
 
     hash<UpdateOperatorInfo> uop_seq_currval(string seq);
 
 
 
    const JOP_INNER = "inner";
 
 
    const JOP_LEFT = "left";
 
 
    const JOP_RIGHT = "right";
 
    const JopMap = {
        JOP_INNER: "inner",
        JOP_LEFT: "left outer",
        JOP_RIGHT: "right outer",
    };
 
 
     hash<string, hash<JoinOperatorInfo>> make_jop(string jop, AbstractTable table, *string alias, *hash jcols, *hash cond, *string ta, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> make_jop(string jop, string table_name, *string alias, *hash jcols, *hash cond, *string ta, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_inner(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_inner(Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_inner(string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_inner(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_inner(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_inner_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_left(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_left(Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_left(string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_left(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_left(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_left_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_right(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_right(Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_right(string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_right(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_right(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
     hash<string, hash<JoinOperatorInfo>> join_right_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash<auto> opt);
 
 
 
    const OP_LIKE = "like";
 
 
    const OP_LT = "<";
 
 
    const OP_LE = "<=";
 
 
    const OP_GT = ">";
 
 
    const OP_GE = ">=";
 
 
    const OP_NE = "!=";
 
 
    const OP_EQ = "=";
 
 
    const OP_CLT = "C<";
 
 
    const OP_CLE = "C<=";
 
 
    const OP_CGT = "C>";
 
 
    const OP_CGE = "C>=";
 
 
    const OP_CNE = "C!=";
 
 
    const OP_CEQ = "C=";
 
 
    const OP_BETWEEN = "between";
 
 
    const OP_IN = "in";
 
 
    const OP_NOT = "not";
 
 
    const OP_SUBSTR = "substr";
 
 
    const OP_OR = "or";
 
 
    const OP_IN_SELECT = "in_select";
 
    const DefaultExpressionMap = {
        // first the "AND" and "OR" "operators"
        DP_OP_AND: {
            "exp": AbstractDataProvider::GenericExpressions{DP_OP_AND},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return "("
                    + (foldl $1 + " and " + $2,
                        (map info.table.getExpressionArg(\info, role, $1, AbstractDataProviderTypeMap."bool"), args))
                    + ")";
            },
        },
        DP_OP_OR: {
            "exp": AbstractDataProvider::GenericExpressions{DP_OP_OR},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return "("
                    + (foldl $1 + " or " + $2,
                        (map info.table.getExpressionArg(\info, role, $1, AbstractDataProviderTypeMap."bool"), args))
                    + ")";
            },
        },
        OP_LIKE: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": "like",
                "name": "like",
                "desc": "The value to bind as the 'like' value (ex: '%some string%')",
                "symbol": "like",
                "args": (
                    DataProviderSignatureStringType,
                    DataProviderSignatureStringValueType,
                ),
                "return_type": AbstractDataProviderTypeMap."bool",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("%s like %s",
                    info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string"),
                    info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."string"));
            },
        },
        OP_LT: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LT},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("%s < %s",
                    info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
                    info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
            },
        },
        OP_LE: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LE},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("%s <= %s",
                    info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
                    info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
            },
        },
        OP_GT: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GT},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("%s > %s",
                    info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
                    info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
            },
        },
        OP_GE: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GE},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("%s >= %s",
                    info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
                    info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
            },
        },
        OP_NE: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NE},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                bool null0 = (args[0] === NULL || !exists args[0]);
                bool null1 = (args[1] === NULL || !exists args[1]);
                if (null0 && null1) {
                    return "1 = 0";
                }
                if (null0) {
                    return sprintf("%s is not null",
                        info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
                }
                if (null1) {
                    return sprintf("%s is not null",
                        info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"));
                }
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
                return sprintf("%s != %s", exp0, exp1);
            },
        },
        OP_EQ: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_EQ},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                bool null0 = (args[0] === NULL || !exists args[0]);
                bool null1 = (args[1] === NULL || !exists args[1]);
                if (null0 && null1) {
                    return "1 = 1";
                }
                if (null0) {
                    return sprintf("%s is null",
                        info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"));
                }
                if (null1) {
                    return sprintf("%s is null",
                        info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"));
                }
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
                return sprintf("%s = %s", exp0, exp1);
            },
        },
        OP_BETWEEN: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_BETWEEN},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("%s between %s and %s",
                    info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any"),
                    info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any"),
                    info.table.getExpressionArg(\info, role, args[2], AbstractDataProviderTypeMap."any"));
            },
        },
        OP_IN: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_IN},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, shift args, AbstractDataProviderTypeMap."any");
                *string ins = (foldl $1 + "," + $2, (map info.table.getExpressionArg(\info, role, $1,
                    AbstractDataProviderTypeMap."any"), args));
                return exists ins ? sprintf("%s in (%s)", exp0, ins) : "1 != 1";
            },
        },
        OP_NOT: {
            "exp": AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NOT},
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("not (%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."bool"));
            },
        },
        OP_SUBSTR: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": "substr",
                "name": "substr",
                "desc": "returns a substring from a string; args: string, int start char, [int length]",
                "symbol": "substr",
                "args": (
                    DataProviderSignatureStringType,
                    DataProviderSignatureIntValueType,
                    DataProviderSignatureOptionalIntValueType,
                ),
                "return_type": AbstractDataProviderTypeMap."string",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."int");
                if (!exists args[2]) {
                    return sprintf("substring(%s from %s)", exp0, exp1);
                }
                return sprintf("substring(%s from %s for %s)", exp0, exp1,
                    info.table.getExpressionArg(\info, role, args[2], AbstractDataProviderTypeMap."int"));
            },
        },
        OP_IN_SELECT: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": "in_select",
                "name": "in_select",
                "desc": "check if a value is in a sub-select; args: value, table name for the subselect, subselect "
                    "query hash",
                "symbol": "in_select",
                "args": (
                    DataProviderSignatureAnyType,         # any value, expression, or field reference
                    DataProviderSignatureStringValueType, # table name
                    DataProviderSignatureHashValueType,   # query hash
                ),
                "return_type": AbstractDataProviderTypeMap."bool",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                object subtable = info.table.getSubtableFromString(args[1], info.query_options);
                list<auto> subargs;
                string subsql = subtable.getSelectSql(args[2], \subargs);
                args += subargs;
                return sprintf("%s in (%s)", exp0, subsql);
            },
        },
        COP_AS: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": "as",
                "name": "as",
                "desc": "aliases a result; args: expression or value, string label",
                "symbol": "as",
                "role": ER_Field,
                "args": (
                    DataProviderSignatureAnyType,
                    DataProviderSignatureStringValueType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                string arg = args[1];
                info.pseudo_column_map{arg} = exp;
                return sprintf("%s as %s", exp, info.table.getColumnSqlName(arg));
            },
        },
        COP_PREPEND: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_PREPEND,
                "name": COP_PREPEND,
                "desc": "prepends a string to another string; args: string to be prepended, string to prepend to the "
                    "first string",
                "symbol": COP_PREPEND,
                "args": (
                    DataProviderSignatureStringType,
                    DataProviderSignatureStringType,
                ),
                "return_type": AbstractDataProviderTypeMap."string",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."string");
                return sprintf("%s || %s", exp1, exp0);
            },
        },
        COP_APPEND: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_APPEND,
                "name": COP_APPEND,
                "desc": "appends or catenates a string to another string; args: string to be appended, string to "
                    "append or concatenate to the first string",
                "symbol": COP_APPEND,
                "args": (
                    DataProviderSignatureStringType,
                    DataProviderSignatureStringType,
                ),
                "return_type": AbstractDataProviderTypeMap."string",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."string");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."string");
                return sprintf("%s || %s", exp0, exp1);
            },
        },
        COP_UPPER: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_UPPER,
                "name": COP_UPPER,
                "desc": "converts a string to all upper case; arg: string",
                "symbol": COP_UPPER,
                "args": (
                    DataProviderSignatureStringType,
                ),
                "return_type": AbstractDataProviderTypeMap."string",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("upper(%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."string"));
            },
        },
        COP_LOWER: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_LOWER,
                "name": COP_LOWER,
                "desc": "converts a string to all lower case; arg: string",
                "symbol": COP_LOWER,
                "args": (
                    DataProviderSignatureStringType,
                ),
                "return_type": AbstractDataProviderTypeMap."string",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("lower(%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."string"));
            },
        },
        COP_DISTINCT: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_DISTINCT,
                "name": COP_DISTINCT,
                "desc": "indicates that only records with distinct values of the given field should be returned",
                "symbol": COP_DISTINCT,
                "role": ER_Field,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
                return sprintf("distinct %s", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."any"));
            },
        },
        COP_MIN: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_MIN,
                "name": COP_MIN,
                "desc": "Returns the minimum value in the group",
                "symbol": COP_MIN,
                "role": ER_Field,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
                return sprintf("min(%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."any"));
            },
            "group": True,
        },
        COP_MAX: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_MAX,
                "name": COP_MAX,
                "desc": "Returns the maximum value in the group",
                "symbol": COP_MAX,
                "role": ER_Field,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
                return sprintf("max(%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."any"));
            },
            "group": True,
        },
        COP_AVG: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_AVG,
                "name": COP_AVG,
                "desc": "Returns the average of the given values in the group",
                "symbol": COP_AVG,
                "role": ER_Field,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
                return sprintf("avg(%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."any"));
            },
            "group": True,
        },
        COP_SUM: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_SUM,
                "name": COP_SUM,
                "desc": "Returns the sum of the given values",
                "symbol": COP_SUM,
                "role": ER_Field,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
                return sprintf("sum(%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."any"));
            },
            "group": True,
        },
        COP_COUNT: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_COUNT,
                "name": COP_COUNT,
                "desc": "Count the number of rows in the group",
                "symbol": COP_COUNT,
                "role": ER_Field,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, *list<auto> args) {
                if (exists args[0] && args[0] != NULL) {
                    return sprintf("count(%s)", info.table.getExpressionArg(\info, role, args[0],
                        AbstractDataProviderTypeMap."any"));
                }
                return "count(1)";
            },
        },
        COP_MINUS: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_MINUS,
                "name": COP_MINUS,
                "desc": "Subtracts one number from another",
                "symbol": COP_MINUS,
                "args": (
                    DataProviderSignatureAnyType,
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
                return sprintf("%s - %s", exp0, exp1);
            },
        },
        COP_PLUS: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_PLUS,
                "name": COP_PLUS,
                "desc": "Adds two numbers",
                "symbol": COP_PLUS,
                "args": (
                    DataProviderSignatureAnyType,
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
                return sprintf("%s + %s", exp0, exp1);
            },
        },
        COP_DIVIDE: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_DIVIDE,
                "name": COP_DIVIDE,
                "desc": "Divides one number by another",
                "symbol": COP_DIVIDE,
                "args": (
                    DataProviderSignatureAnyType,
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
                return sprintf("%s / %s", exp0, exp1);
            },
        },
        COP_MULTIPLY: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_MULTIPLY,
                "name": COP_MULTIPLY,
                "desc": "Multiplies two numbers",
                "symbol": COP_MULTIPLY,
                "args": (
                    DataProviderSignatureAnyType,
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string exp0 = info.table.getExpressionArg(\info, role, args[0], AbstractDataProviderTypeMap."any");
                string exp1 = info.table.getExpressionArg(\info, role, args[1], AbstractDataProviderTypeMap."any");
                return sprintf("%s * %s", exp0, exp1);
            },
        },
        COP_COALESCE: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_COALESCE,
                "name": COP_COALESCE,
                "desc": "Returns the value of the first expression with a value",
                "symbol": COP_COALESCE,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "varargs": True,
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("coalesce(%s)", (foldl $1 + "," + $2,
                    (map info.table.getExpressionArg(\info, role, $1, AbstractDataProviderTypeMap."any"), args)));
            }
        },
        COP_LENGTH: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_LENGTH,
                "name": COP_LENGTH,
                "desc": "Returns the length of the argument",
                "symbol": COP_LENGTH,
                "args": (
                    DataProviderSignatureAnyType,
                ),
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("length(%s)", info.table.getExpressionArg(\info, role, args[0],
                    AbstractDataProviderTypeMap."any"));
            },
        },
        COP_SEQ: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_SEQ,
                "name": COP_SEQ,
                "desc": "returns the next value in the given sequence; args: sequence name",
                "symbol": COP_SEQ,
                "args": (
                    DataProviderSignatureStringValueType,
                ),
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                throw "SEQUENCE-ERROR", sprintf("cannot select sequence %y because this database does not support "
                    "sequences", args[0]);
            }
        },
        COP_SEQ_CURRVAL: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_SEQ_CURRVAL,
                "name": COP_SEQ_CURRVAL,
                "desc": "returns the current value in the given sequence; args: sequence name",
                "symbol": COP_SEQ_CURRVAL,
                "args": (
                    DataProviderSignatureStringValueType,
                ),
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                throw "SEQUENCE-ERROR", sprintf("cannot select the current value of sequence %y because this "
                    "database does not support sequences", args[0]);
            }
        },
        COP_OVER: {
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_OVER,
                "name": COP_OVER,
                "desc": "returns the output of the SQL window given by the arguments; args: *string partiion by, "
                    "*string order by",
                "symbol": COP_OVER,
                "args": (
                    DataProviderSignatureFieldType,
                    DataProviderSignatureOptionalStringValueType,
                    DataProviderSignatureOptionalStringValueType,
                ),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                string column_name = args[0];
                *string partitionby = args[1];
                *string orderby = args[2];
                if (!exists partitionby && exists orderby) {
                    throw "OVER-ERROR", "Argument 2 (partition by) must be given in case of usage of argument 3 "
                        "(order by)";
                }
                string sql = column_name + " over (";
                Columns cols;
                if (exists partitionby) {
                    if (!info.pseudo_column_map{partitionby}) {
                        cols = info.table.describe();
                        if (!exists cols{partitionby}) {
                            throw "OVER-ERROR", sprintf("Argument 2 (partition by) %y is not an alias or an "
                                "accessible column", partitionby);
                        }
                    }
                    sql += sprintf("partition by %s", partitionby);
                }
                if (exists orderby) {
                    if (!info.pseudo_column_map{orderby}) {
                        if (!cols) {
                            cols = info.table.describe();
                            if (!exists cols{orderby}) {
                                throw "OVER-ERROR", sprintf("Argument 3 (order by) %y is not an alias or an "
                                    "accessible column", orderby);
                            }
                        }
                    }
                    sql += sprintf(" order by %s", orderby);
                }
                sql += ")";
                return sql;
            }
        },
        COP_DENSE_RANK: (
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_DENSE_RANK,
                "name": COP_DENSE_RANK,
                "desc": "Returns the rank of a row in an ordered group of rows as a number",
                "symbol": COP_DENSE_RANK,
                "args": (),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return "dense_rank()";
            },
        ),
        COP_FIRST_VALUE: (
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_FIRST_VALUE,
                "name": COP_FIRST_VALUE,
                "desc": "Returns the first value in an ordered group of rows",
                "symbol": COP_FIRST_VALUE,
                "args": (
                    DataProviderSignatureFieldType,
                ),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("first_value(%s)", args[0]);
            },
        ),
        COP_LAST_VALUE: (
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_LAST_VALUE,
                "name": COP_LAST_VALUE,
                "desc": "Returns the last value in an ordered group of rows",
                "symbol": COP_LAST_VALUE,
                "args": (
                    DataProviderSignatureFieldType,
                ),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."any",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("last_value(%s)", args[0]);
            },
        ),
        COP_NTILE: (
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_NTILE,
                "name": COP_NTILE,
                "desc": "Returns the group number of an ordered group of rows",
                "symbol": COP_NTILE,
                "args": (
                    DataProviderSignatureIntValueType,
                ),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return sprintf("ntile(%d)", args[0]);
            },
        ),
        COP_PERCENT_RANK: (
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_PERCENT_RANK,
                "name": COP_PERCENT_RANK,
                "desc": "Returns the percent rank of a row in an ordered group of rows",
                "symbol": COP_PERCENT_RANK,
                "args": (),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return "percent_rank()";
            },
        ),
        COP_RANK: (
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_PERCENT_RANK,
                "name": COP_PERCENT_RANK,
                "desc": "Returns the row number of a row in an ordered group of rows",
                "symbol": COP_PERCENT_RANK,
                "args": (),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return "rank()";
            },
        ),
        COP_ROW_NUMBER: (
            "exp": <DataProviderExpressionInfo>{
                "type": DET_Operator,
                "label": COP_ROW_NUMBER,
                "name": COP_ROW_NUMBER,
                "desc": "Returns the row number of a result set",
                "symbol": COP_ROW_NUMBER,
                "args": (),
                "role": ER_Field,
                "return_type": AbstractDataProviderTypeMap."int",
            },
            "code": string sub (reference<hash<QueryInfo>> info, int role, list<auto> args) {
                return "row_number()";
            },
        ),
    };
 
    const DefaultOpMap = {
        OP_LIKE: {
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                info.args += arg;
                return sprintf("%s like %v", cn);
            },
            "name": "like",
            "args": (DataProviderSignatureStringType, DataProviderSignatureStringValueType),
            "return_type": AbstractDataProviderTypeMap."bool",
            "desc": "The value to bind as the 'like' value (ex: '%some string%')",
        },
        OP_LT: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LT} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                info.args += arg;
                return sprintf("%s < %v", cn);
            },
        ),
        OP_LE: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_LE} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                info.args += arg;
                return sprintf("%s <= %v", cn);
            },
        ),
        OP_GT: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GT} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                info.args += arg;
                return sprintf("%s > %v", cn);
            },
        ),
        OP_GE: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_GE} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                info.args += arg;
                return sprintf("%s >= %v", cn);
            },
        ),
        OP_NE: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NE} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                if (arg === NULL || !exists arg)
                    return sprintf("%s is not null", cn);
                info.args += arg;
                return sprintf("(%s != %v or %s is null)", cn, cn);
            },
        ),
        OP_EQ: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_EQ} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                if (arg === NULL || !exists arg)
                    return sprintf("%s is null", cn);
                info.args += arg;
                return sprintf("%s = %v", cn);
            },
        ),
        OP_BETWEEN: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_BETWEEN} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                info.args += arg[0];
                info.args += arg[1];
                return sprintf("%s between %v and %v", cn);
            },
        ),
        OP_IN: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_IN} + (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                *string ins = (foldl $1 + "," + $2, (map info.table.getSqlValue($1), arg));
                return exists ins ? sprintf("%s in (%s)", cn, ins) : "1 != 1";
            },
        ),
        OP_IN_SELECT: {
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg,
                    *hash<auto> opt) {
                object subtable;
                if (arg.table.typeCode() == NT_STRING) {
                    subtable = info.table.getSubtableFromString(arg.table, opt);
                } else {
                    subtable = arg.table;
                }
 
                list<auto> subargs;
                string subsql = subtable.getSelectSql(arg.select_hash, \subargs);
                info.args += subargs;
                return sprintf("%s in (%s)", cn, subsql);
            },
            "withopt": True,
        },
        OP_NOT: AbstractDataProvider::GenericExpressions{DP_SEARCH_OP_NOT} + (
            "recursive": True,
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                return sprintf("not (%s)", cn);
            },
        ),
        OP_CLT: (
            "argcolumn": True,
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                return sprintf("%s < %s", cn, arg);
            },
            "name": "column <",
            "type": AbstractDataProviderType::get(StringType),
            "desc": "a column name for less than comparisons; if the field value is less than the column argument's "
                "value, then the operation returns true; the other column must have a compatible type",
        ),
        OP_CLE: (
            "argcolumn": True,
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                return sprintf("%s <= %s", cn, arg);
            },
            "name": "column <=",
            "type": AbstractDataProviderType::get(StringType),
            "desc": "a column name for less than or equals comparisons; if the field value is less than or equal to "
                "the column argument's, then the operation returns true; the other column must have a compatible type",
        ),
        OP_CGT: (
            "argcolumn": True,
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                return sprintf("%s > %s", cn, arg);
            },
            "name": "column >",
            "type": AbstractDataProviderType::get(StringType),
            "desc": "a column name for less than comparisons; if the field value is less than the column argument's, "
                "then the operation returns true; the other column must have a compatible type",
        ),
        OP_CGE: (
            "argcolumn": True,
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                return sprintf("%s >= %s", cn, arg);
            },
            "name": "column >=",
            "type": AbstractDataProviderType::get(StringType),
            "desc": "a column name for greater than or equals comparisons; if the field value is greater than or "
                "equal to the column argument's, then the operation returns true; the other column must have a "
                "compatible type",
        ),
        OP_CNE: (
            "argcolumn": True,
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                return sprintf("%s != %s", cn, arg);
            },
            "name": "column !=",
            "type": AbstractDataProviderType::get(StringType),
            "desc": "a column name for not=equals comparisons; the other column must have a compatible type",
        ),
        OP_CEQ: (
            "argcolumn": True,
            "code": string sub (reference<hash<QueryInfo>> info, string cn, string arg) {
                return sprintf("%s = %s", cn, arg);
            },
            "name": "column =",
            "type": AbstractDataProviderType::get(StringType),
            "desc": "a value for equality comparisons; the other column must have a compatible type",
        ),
        OP_SUBSTR: (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, auto arg) {
                info.args += arg[0]; // start
                if (!exists arg[1]) {
                    info.args += arg[2]; # text
                    return sprintf("substring(%s from %v) = %v", cn);
                }
                info.args += arg[1]; # count
                info.args += arg[2]; # text
                return sprintf("substring(%s from %v for %v) = %v", cn);
            },
        ),
        OP_OR: (
            "code": string sub (reference<hash<QueryInfo>> info, string cn, list<auto> arg) {
                return info.table.getOrClause(\info, arg);
            },
        ),
    };
 
     hash<OperatorInfo> make_op(string op, auto arg);
 
 
 
     hash<OperatorInfo> op_like(string str);
 
 
 
     hash<OperatorInfo> op_lt(auto arg);
 
 
 
     hash<OperatorInfo> op_le(auto arg);
 
 
 
     hash<OperatorInfo> op_gt(auto arg);
 
 
 
     hash<OperatorInfo> op_ge(auto arg);
 
 
 
     hash<OperatorInfo> op_ne(auto arg);
 
 
 
     hash<OperatorInfo> op_eq(auto arg);
 
 
 
     hash<OperatorInfo> op_between(auto l, auto r);
 
 
 
     hash<OperatorInfo> op_in();
 
 
 
     hash<OperatorInfo> op_in_select(string table, hash<auto> select_hash);
 
 
 
     hash<OperatorInfo> op_in_select(AbstractTable table, hash<auto> select_hash);
 
 
 
     hash<OperatorInfo> op_in_select(Table table, hash<auto> select_hash);
 
 
 
     hash<OperatorInfo> op_in(list<auto> args);
 
 
 
     hash<OperatorInfo> op_not(hash arg);
 
 
 
     hash<OperatorInfo> op_clt(string arg);
 
 
 
     hash<OperatorInfo> op_cle(string arg);
 
 
 
     hash<OperatorInfo> op_cgt(string arg);
 
 
 
     hash<OperatorInfo> op_cge(string arg);
 
 
 
     hash<OperatorInfo> op_cne(string arg);
 
 
 
     hash<OperatorInfo> op_ceq(string arg);
 
 
 
     hash<OperatorInfo> op_substr(int start, *int count, string text);
 
 
 
     hash<OperatorInfo> op_substr(int start, string text);
 
 
 
     hash<string, hash<OperatorInfo>> wop_or(hash h1, hash h2);
 
 
 
    const IOP_SEQ = "seq";
 
 
    const IOP_SEQ_CURRVAL = "seq_currval";
 
    const DefaultIopMap = {};
 
 
     hash<InsertOperatorInfo> make_iop(string iop, auto arg);
 
 
 
     hash<InsertOperatorInfo> iop_seq(string arg);
 
 
 
     hash<InsertOperatorInfo> iop_seq_currval(string arg);
 
 
    const SqlUtilDrivers = ...;
 
 
    // private variable to store the closure used to deserialize datasources (if any)
    extern code ds_get;
 
    // private variable to store the closure used to serialize datasources (if any)
    extern code ds_do;
 
     sqlutil_register_ds_deserializer(*code new_ds_get);
 
 
     sqlutil_register_ds_serializer(*code new_ds_do);
 
 
    // private function used to deserialize datasources
    AbstractDatasource sqlutil_get_ds(string type, string config);
 
 
    // private function used to serialize datasources
    hash<auto> sqlutil_ds(AbstractDatasource ds);
 
 
class AbstractHashContainer : public Qore::Serializable {
 
public:
        
 
protected:
            *hash h;
 
public:
 
        constructor(*hash nh);
 
 
        constructor(AbstractHashContainer old);
 
 
        copy(AbstractHashContainer old);
 
 
 
        auto memberGate(string k);
 
 
 
        clear();
 
 
        abstract auto take(string k);
 
        renameKey(string old_name, string new_name);
 
 
        *hash<auto> getHash();
 
 
 
        bool matchKeys(hash h1);
 
 
 
        bool matchKeys(list<auto> l);
 
 
 
        bool matchKeys(AbstractHashContainer c);
 
 
 
        bool partialMatchKeys(hash h1);
 
 
 
        bool partialMatchKeys(list<auto> l);
 
 
 
        bool partialMatchKeys(AbstractHashContainer c);
 
 
 
        bool val();
 
 
 
        list<string> keys();
 
 
 
        list<auto> values();
 
 
 
        Qore::AbstractIterator iterator();
 
 
 
        Qore::AbstractIterator keyIterator();
 
 
 
        Qore::AbstractIterator pairIterator();
 
 
        bool empty();
 
 
 
        int size();
 
 
 
        bool hasKey(string k);
 
 
 
        bool hasKeyValue(string k);
 
 
 
        *string firstKey();
 
 
 
        *string lastKey();
 
 
        abstract string getElementName();
    };
 
class AbstractListContainer : public Qore::Serializable {
 
public:
        
 
protected:
            softlist l;
 
public:
 
        constructor(softlist nl);
 
 
 
        abstract auto get(softint i);
 
        add(auto val);
 
 
        auto take(int i);
 
 
        list<auto> getList();
 
 
 
        bool val();
 
 
 
        Qore::ListIterator iterator();
 
 
        bool empty();
 
 
 
        int size();
 
 
        abstract string getElementName();
 
protected:
         checkIndex(int i);
public:
 
    };
 
class Tables : public AbstractHashContainer {
 
public:
        constructor();
 
 
        constructor(AbstractDatasource ds, hash tables, *hash<auto> opt);
 
 
        constructor(AbstractDatasource ds);
 
 
        add(string k, Table val);
 
 
        add(string k, AbstractTable val);
 
 
        add(Table val);
 
 
        add(AbstractTable val);
 
 
        AbstractTable take(string k);
 
 
        populate(AbstractDatasource ds, hash<auto> tables, *hash<auto> opt);
 
 
        populate(AbstractDatasource ds);
 
 
 
        *list<auto> getDropAllForeignConstraintsOnTableSql(string name, *hash<auto> opt);
 
 
 
        AbstractTable memberGate(string k);
 
 
        string getElementName();
 
 
        *AbstractTable getIfExists(AbstractDatasource ds, string name);
 
 
        AbstractTable get(AbstractDatasource ds, string name);
 
 
 
        *string getRenameTableIfExistsSql(string old_name, string new_name, *hash<auto> opts);
 
 
 
        bool tableRenamed(string old_name, string new_name, string old_sql_name);
 
 
protected:
         tableRenamedIntern(string old_name, string new_name, string oldsn);
public:
 
 
 
        *string getDropConstraintIfExistsSql(string tname, string cname, *hash<auto> opts);
 
 
        list<auto> getCreateList();
 
 
        Qore::AbstractIterator createIterator();
 
 
 
        list<auto> getDropList();
 
 
 
        Qore::AbstractIterator dropIterator();
 
 
protected:
         getDependencies(reference<hash> tdh, reference<hash> sdh, *reference<hash> th);
public:
 
    };
 
class Columns : public AbstractHashContainer {
 
public:
        constructor(*hash c) ;
 
 
        constructor(Columns old) ;
 
 
        add(string k, AbstractColumn val);
 
 
        AbstractColumn take(string k);
 
 
 
        AbstractColumn memberGate(string k);
 
 
        Columns subset(softlist l);
 
 
        string getElementName();
 
 
        bool equal(Columns cols);
 
 
        AbstractIterator getSqlColumnNameIterator();
 
    };
 
class AbstractDdlObject : public Qore::Serializable {
 
public:
            string name;
 
            string ddl_name;
 
protected:
         constructor();
public:
 
 
        constructor(string name);
 
 
 
        abstract string getDdlName(string name);
    };
 
class AbstractColumn : public AbstractDdlObject {
 
public:
            string native_type;
 
            *string qore_type;
 
            int size;
 
            bool nullable;
 
            *string def_val;
 
            *string comment;
 
            *int scale;
 
            const DefaultVarcharSize = 80;
 
protected:
         constructor();
public:
 
 
        constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string c, *int scale)
 ;
 
 
 
        hash<GenericColumnInfo> getDescriptionHash();
 
 
 
        string getNativeTypeString();
 
 
        string getCreateSql(AbstractTable t);
 
 
 
        abstract list<auto> getAddColumnSql(AbstractTable t);
 
        string getDropSql(string table_name);
 
 
 
        list<auto> getModifySql(AbstractTable t, AbstractColumn c, *hash<auto> opt);
 
 
 
        abstract string getRenameSql(AbstractTable t, string new_name);
 
        bool equal(AbstractColumn c);
 
 
protected:
         abstract bool equalImpl(AbstractColumn c);
public:
 
 
protected:
         abstract list<auto> getModifySqlImpl(AbstractTable t, AbstractColumn c, *hash<auto> opt);
public:
    };
 
class Indexes : public AbstractHashContainer {
 
public:
        constructor(*hash c) ;
 
 
        add(string k, AbstractIndex val);
 
 
        *AbstractIndex findEqual(AbstractIndex ix);
 
 
        AbstractIndex take(string k);
 
 
        *AbstractIndex tryTake(string k);
 
 
 
        AbstractIndex memberGate(string k);
 
 
        string getElementName();
 
    };
 
class AbstractIndex : public Qore::Serializable {
 
public:
            string name;
 
            bool unique;
 
            Columns columns;
 
protected:
            *AbstractColumnSupportingConstraint constraint;
 
            *AbstractForeignConstraint foreign_constraint;
 
public:
 
        constructor(string n, bool u, hash c);
 
 
        string getName();
 
 
        bool hasColumn(string cname);
 
 
        abstract string getCreateSql(string table_name, *hash<auto> opt);
 
        string getDropSql(string table_name);
 
 
        bool equal(AbstractIndex ix);
 
 
        bool equalExceptName(AbstractIndex ix);
 
 
        abstract bool equalImpl(AbstractIndex ix);
 
        abstract string getRenameSql(string table_name, string new_name);
 
        setSupportingConstraint(AbstractColumnSupportingConstraint c);
 
 
        setSupportingConstraint();
 
 
        *AbstractColumnSupportingConstraint getSupportingConstraint();
 
 
        setSupportingConstraint(AbstractForeignConstraint c);
 
 
        setForeignConstraint();
 
 
        *AbstractForeignConstraint getForeignConstraint();
 
 
        *list<AbstractColumnConstraint> getAllSupportingConstraints();
 
 
        list<auto> getRecreateSql(AbstractDatasource ds, string table_name, *hash<auto> opt);
 
    };
 
class Constraints : public AbstractHashContainer {
 
public:
        constructor(*hash c) ;
 
 
        add(string k, AbstractConstraint val);
 
 
        AbstractConstraint take(string k);
 
 
        *AbstractUniqueConstraint findEqualUniqueConstraint(AbstractUniqueConstraint uk);
 
 
 
        AbstractConstraint memberGate(string k);
 
 
        string getElementName();
 
    };
 
class AbstractConstraint : public Qore::Serializable {
 
public:
        
 
protected:
            string name;
 
public:
 
        constructor(string n);
 
 
        string getName();
 
 
        rename(string n);
 
 
        abstract string getCreateSql(string table_name, *hash<auto> opt);
 
        string getDropSql(string table_name);
 
 
        abstract list<auto> getRenameSql(string table_name, string new_name);
 
        string getDisableSql(string table_name);
 
 
        string getEnableSql(string table_name, *hash<auto> opt);
 
 
        bool equal(AbstractConstraint c);
 
 
protected:
         abstract bool equalImpl(AbstractConstraint c);
public:
 
        abstract bool setIndexBase(string ix);
 
        abstract clearIndex();
 
        bool hasColumn(string cname);
 
    };
 
class AbstractCheckConstraint : public AbstractConstraint {
 
public:
            string src;
 
        constructor(string n, string n_src) ;
 
 
protected:
         bool equalImpl(AbstractConstraint c);
public:
 
 
        bool setIndexBase(string ix);
 
 
        clearIndex();
 
    };
 
class AbstractColumnConstraint : public AbstractConstraint,public  Columns {
 
public:
protected:
            *string index;
 
public:
 
        constructor(string name, *hash<auto> cols, *string index) ;
 
 
        constructor(string name, Columns cols, *string index) ;
 
 
        bool setIndexBase(string ix);
 
 
        clearIndex();
 
    };
 
class AbstractColumnSupportingConstraint : public AbstractColumnConstraint {
 
public:
protected:
            *hash<string, hash<string, AbstractForeignConstraint>> sourceConstraints;
 
public:
 
        constructor(string name, *hash<auto> cols, *string index) ;
 
 
        constructor(string name, Columns cols, *string index) ;
 
 
 
        Qore::AbstractIterator getSourceConstraintIterator();
 
 
        hash<auto> getDisableReenableSql(AbstractDatasource ds, string table_name, *hash<auto> opts);
 
 
        findMatchingIndex(*Indexes indexes);
 
 
 
        addSourceConstraint(string tname, AbstractForeignConstraint fk);
 
 
        removeSourceConstraint(string tname, list<auto> cols);
 
 
        renameSourceConstraintTable(string old_name, string new_name);
 
 
        bool hasColumn(string cname);
 
 
        *string getIndex();
 
 
protected:
         bool equalImpl(AbstractConstraint c);
public:
 
 
        abstract string getCreateSql(string table_name, *hash<auto> opts);
    };
 
class AbstractUniqueConstraint : public AbstractColumnSupportingConstraint {
 
public:
        constructor(string n, *hash<auto> c, *string n_index) ;
 
    };
 
class AbstractPrimaryKey : public AbstractUniqueConstraint {
 
public:
        constructor() ;
 
 
        constructor(string n, *hash<auto> c) ;
 
    };
 
class ForeignConstraints : public AbstractHashContainer {
 
public:
        constructor(*hash<auto> c) ;
 
 
        add(string k, AbstractForeignConstraint val);
 
 
        AbstractForeignConstraint take(string k);
 
 
        *AbstractForeignConstraint findEqual(AbstractForeignConstraint fk);
 
 
 
        AbstractForeignConstraint memberGate(string k);
 
 
        *hash<auto> findConstraintOn(string table, softlist<auto> cols);
 
 
        string getElementName();
 
    };
 
class ForeignConstraintTarget : public Qore::Serializable {
 
public:
            string table;
 
            Columns columns;
 
        constructor(string t, Columns c);
 
 
        bool equal(ForeignConstraintTarget targ);
 
    };
 
class AbstractForeignConstraint : public AbstractColumnConstraint {
 
public:
            ForeignConstraintTarget target;
 
        constructor(string name, Columns cols, ForeignConstraintTarget t) ;
 
 
protected:
         bool equalImpl(AbstractConstraint con);
public:
 
    };
 
class AbstractSequence : public Qore::Serializable {
 
public:
            string name;
 
            number start;
 
            number increment;
 
            *number max;
 
        constructor(string n_name, number n_start = 1, number n_increment = 1, *softnumber n_max);
 
 
        abstract string getCreateSql(*hash<auto> opt);
 
 
        string getDropSql(*hash<auto> opt);
 
 
 
        abstract softlist<auto> getRenameSql(string new_name, *hash<auto> opt);
    };
 
class AbstractView : public Qore::Serializable {
 
public:
            // ! potential object schema
            *string schema;
 
            string name;
 
            string src;
 
            bool updatable;
 
        constructor(string n_name, string n_src);
 
 
        abstract string getCreateSql(*hash<auto> opt);
 
 
        string getDropSql(*hash<auto> opt);
 
 
 
        abstract softlist<auto> getRenameSql(string new_name, *hash<auto> opt);
    };
 
class AbstractFunctionBase : public Qore::Serializable {
 
public:
            string name;
 
            string type;
 
            string src;
 
 
        constructor(string n, string n_type, string n_src);
 
 
        string getType();
 
 
 
        string getDropSql(*hash<auto> opt);
 
 
        bool equal(AbstractFunctionBase t);
 
 
        string getNormalizedSource(string src);
 
 
protected:
         abstract bool equalImpl(AbstractFunctionBase t);
public:
    };
 
class AbstractFunction : public AbstractFunctionBase {
 
public:
 
        constructor(string n, string n_type, string n_src) ;
 
 
        abstract list<auto> getCreateSql(*hash<auto> opt);
 
 
        abstract softlist<auto> getRenameSql(string new_name, *hash<auto> opt);
 
        setName(string new_name);
 
    };
 
class Functions : public AbstractHashContainer {
 
public:
        constructor(*hash c) ;
 
 
        add(string k, AbstractFunction val);
 
 
        AbstractFunction take(string k);
 
 
 
        AbstractFunction memberGate(string k);
 
 
        string getElementName();
 
    };
 
class AbstractTrigger : public AbstractFunctionBase {
 
public:
        constructor(string n, string n_src) ;
 
 
        abstract list<auto> getCreateSql(string table_name, *hash<auto> opt);
 
        abstract softlist<auto> getRenameSql(string table_name, string new_name);
 
        abstract list<auto> getDropSql(string table_name);
    };
 
class Triggers : public AbstractHashContainer {
 
public:
        constructor(*hash c) ;
 
 
        add(string k, AbstractTrigger val);
 
 
        AbstractTrigger take(string k);
 
 
 
        AbstractTrigger memberGate(string k);
 
 
        string getElementName();
 
    };
};