FreetdsSqlUtil.qm.dox.h

// -*- mode: c++; indent-tabs-mode: nil -*-
 
/*  FreetdsSqlUtil.qm Copyright (C) 2015 - 2022 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 SqlUtil module
 
// requires the Util 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 FreetdsSqlUtil {
     FreetdsTable get_table(AbstractDatasource nds, string nname, *hash<auto> opts);
 
 
     FreetdsDatabase get_database(AbstractDatasource nds, *hash<auto> opts);
 
 
    parse_schema_name(string nname, reference<string> schema, reference<string> name, string default_schema = 'dbo');
 
 
class FreetdsSavepointHelper : public AbstractSavepointHelper {
 
public:
 
        constructor(AbstractDatasource ds, *string savepoint = 'x' + get_random_string(31))
 ;
 
 
protected:
         createSavepointImpl();
public:
 
 
protected:
         rollbackImpl();
public:
 
 
protected:
         deleteSavepointImpl();
public:
 
    };
 
class FreetdsNumberDataType : public SqlUtil::AbstractNumericDataType {
 
public:
        constructor(string native_type, bool nullable, *hash<auto> options)
 ;
 
    };
 
class FreetdsIndex : public SqlUtil::AbstractIndex {
 
public:
            *string filegroup;
 
        constructor(string n_name, bool n_unique, hash n_cols, *string n_filegroup)
 ;
 
 
        string getCreateSql(string table_name, *hash opt);
 
 
protected:
         bool equalImpl(AbstractIndex ix);
public:
 
 
        string getRenameSql(string table_name, string new_name);
 
    };
 
class FreetdsForeignConstraint : public SqlUtil::AbstractForeignConstraint {
 
public:
        constructor(string n, Columns c, ForeignConstraintTarget t) ;
 
 
        string getCreateSql(string table_name, *hash opt);
 
 
        softlist getRenameSql(string table_name, string new_name);
 
 
        string getCreateSql(string name, string table_name, *hash opt);
 
    };
 
class FreetdsCheckConstraint : public SqlUtil::AbstractCheckConstraint {
 
public:
        constructor(string n, string n_src) ;
 
 
        string getCreateSql(string table_name, *hash opt);
 
 
        list getRenameSql(string table_name, string new_name);
 
 
        string getCreateSql(string name, string table_name, *hash opt);
 
    };
 
class FreetdsColumn : public SqlUtil::AbstractColumn {
 
public:
            int byte_size;
 
protected:
         constructor();
public:
 
 
        constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string cm, softint bs, *int scale)
 ;
 
 
 
        softlist getAddColumnSql(AbstractTable t);
 
 
 
        list getModifySqlImpl(AbstractTable t, AbstractColumn col, *hash opt);
 
 
 
        string getRenameSql(AbstractTable t, string new_name);
 
 
 
        string getDdlName(string name);
 
 
protected:
         bool equalImpl(AbstractColumn c);
public:
 
    };
 
class FreetdsNumericColumn : public FreetdsColumn {
 
public:
        constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string cm, softint bs,
                softint scale) ;
 
    };
 
class FreetdsUniqueConstraintCommon : public SqlUtil::AbstractUniqueConstraint {
 
public:
protected:
            *string filegroup;
 
public:
 
protected:
         constructor();
public:
 
 
        constructor(*string ts);
 
 
        *string getTablespace();
 
 
        setTablespace(*string ts);
 
 
        bool setIndexBase(string ix);
 
 
        clearIndex();
 
 
        getIndexSql(reference<string> sql, string name, *hash opts);
 
    };
 
class FreetdsUniqueConstraint : public SqlUtil::AbstractUniqueConstraint,public  FreetdsUniqueConstraintCommon {
 
public:
protected:
            bool enabled;
 
public:
 
 
        constructor(string n, hash n_cols, bool e = True, *string ts) ;
 
 
 
        FreetdsColumn memberGate(string k);
 
 
        string getCreateSql(string table_name, *hash opts);
 
 
        list getRenameSql(string table_name, string new_name);
 
 
        string getCreateSql(string name, string table_name, *hash opts);
 
 
        bool isEnabled();
 
    };
 
class FreetdsPrimaryKey : public SqlUtil::AbstractPrimaryKey,public  FreetdsUniqueConstraintCommon {
 
public:
        constructor();
 
 
 
        constructor(string n, *hash c, *string ts) ;
 
 
 
        FreetdsColumn memberGate(string k);
 
 
        string getCreateSql(string table_name, *hash opts);
 
 
        softlist getRenameSql(string table_name, string new_name);
 
    };
 
class FreetdsSequence : public SqlUtil::AbstractSequence {
 
public:
        constructor(string n_name, number n_start = 1, number n_increment = 1, *softnumber n_end) ;
 
 
        string getCreateSql(*hash opt);
 
 
 
        softlist getRenameSql(string new_name, *hash opt);
 
    };
 
class FreetdsView : public SqlUtil::AbstractView {
 
public:
        constructor(string n_name, string n_src, *string n_schemaname) ;
 
 
        string getCreateSql(*hash opt);
 
 
 
        softlist getRenameSql(string new_name, *hash opt);
 
    };
 
class FreetdsTrigger : public SqlUtil::AbstractTrigger {
 
public:
        constructor(string n, string n_src) ;
 
 
        softlist getCreateSql(string table_name, *hash opt);
 
 
        softlist getDropSql(string table_name);
 
 
protected:
         bool equalImpl(AbstractFunctionBase t);
public:
 
 
        softlist getRenameSql(string table_name, string new_name);
 
    };
 
class FreetdsFunction : public SqlUtil::AbstractFunction {
 
public:
            *string args;
 
        constructor(string n, string n_src, *string a) ;
 
 
        softlist getCreateSql(*hash opt);
 
 
 
        string getDropSql(*hash opt);
 
 
protected:
         bool equalImpl(AbstractFunctionBase t);
public:
 
 
 
        softlist getRenameSql(string new_name, *hash opt);
 
 
        setName(string new_name);
 
    };
 
class FreetdsDatabase : public SqlUtil::AbstractDatabase {
 
public:
            bool is_sybase;
 
            string default_schema = "dbo";
 
            const FreetdsSchemaDescriptionOptions = AbstractDatabase::SchemaDescriptionOptions;
 
            const FreeTDS_TempSavepoint = "qore_freetds_tmp_savepoint";
 
            const FreetdsReclaimSpaceOptions = ReclaimSpaceOptions;
 
            const FreetdsReservedWords = ...;
 
 
            const MaxPrecision = 38;
 
        constructor(AbstractDatasource nds, *hash opts) ;
 
 
 
        bool requiresScale();
 
 
 
        int getMaximumPrecision();
 
 
protected:
         list<string> featuresImpl();
public:
 
 
protected:
         FreetdsSequence makeSequenceImpl(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
public:
 
 
protected:
         *AbstractSequence getSequenceImpl(string name);
public:
 
 
protected:
         *AbstractView getViewImpl(string name);
public:
 
 
protected:
         FreetdsFunction makeFunctionImpl(string name, string src, *hash opts);
public:
 
 
protected:
         FreetdsFunction makeProcedureImpl(string name, string src, *hash opts);
public:
 
 
protected:
         *AbstractFunction getFunctionImpl(string name);
public:
 
 
protected:
         AbstractFunction getProcedureImpl(string name);
public:
 
 
protected:
         list getDropSchemaSqlImpl(hash schema_hash, *hash opt);
public:
 
 
protected:
         list getAlignSqlImpl(hash schema_hash, *hash opt);
public:
 
 
protected:
         softlist<string> listTablesImpl();
public:
 
 
 
protected:
         list<string> listFunctionsImpl();
public:
 
 
 
protected:
         list<string> listProceduresImpl();
public:
 
 
protected:
         list<string> listSequencesImpl();
public:
 
 
protected:
         list<string> listViewsImpl();
public:
 
 
protected:
         string getCreateSqlImpl(list l);
public:
 
 
          static bool isSybase(AbstractDatasource ds);
 
          static string getCreateSql(list l);
 
protected:
         hash getSchemaDescriptionOptions();
public:
 
 
protected:
         hash getReclaimSpaceOptions();
public:
 
 
protected:
         softint getNextSequenceValueImpl(string name);
public:
 
 
protected:
         softint getCurrentSequenceValueImpl(string name);
public:
 
 
protected:
         bool supportsSequencesImpl();
public:
 
 
protected:
         bool supportsTypesImpl();
public:
 
 
protected:
         bool supportsPackagesImpl();
public:
 
 
protected:
         bool rebuildIndexImpl(string name, *hash options);
public:
 
 
protected:
         computeStatisticsImpl(*hash options);
public:
 
 
protected:
         reclaimSpaceImpl(*hash options);
public:
 
 
protected:
         int getPhysicalSizeImpl();
public:
 
 
protected:
         auto tryExecArgsImpl(string sql, *softlist<auto> args);
public:
 
 
protected:
         auto tryExecRawImpl(string sql, *softlist<auto> args);
public:
 
 
          static auto tryExecArgs(AbstractDatasource ds, string sql, *softlist<auto> args);
 
          static auto tryExecRaw(AbstractDatasource ds, string sql);
    };
 
class FreetdsTable : public SqlUtil::AbstractTable {
 
public:
            bool is_sybase;
 
            bool ss_has_merge = False;
 
            bool ss_has_sequences = False;
 
            const FreetdsTypeMap = ...;
 
 
            const QoreTypeMap = ...;
 
 
            const MsQoreTypeMap = QoreTypeMap + {
                SqlUtil::BLOB: "varbinary(max)",
                "date": "datetime2",
            };
 
            const SybaseQoreTypeMap = QoreTypeMap + {
                "date": "datetime",
            };
 
            const FreetdsTableDescriptionHashOptions = AbstractTable::TableDescriptionHashOptions;
 
            const FreetdsColumnDescOptions = AbstractTable::ColumnDescOptions;
 
            const FreetdsIndexOptions = AbstractTable::IndexOptions;
 
            const FreetdsConstraintOptions = AbstractTable::ConstraintOptions + FreetdsIndexOptions;
 
            const FreetdsTableCreationOptions = AbstractTable::TableCreationOptions + FreetdsConstraintOptions;
 
            const FreetdsAlignTableOptions = AbstractTable::AlignTableOptions + FreetdsTableCreationOptions;
 
            const FreetdsSelectOptions = ...;
 
 
            const MsSqlServerTableHints = ...;
 
 
            const FreetdsExpressionMap = DefaultExpressionMap + {
                OP_SUBSTR: DefaultExpressionMap{OP_SUBSTR} + {
                    "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,%s,32767)", exp0, exp1);
                        }
                        return sprintf("substring(%s,%s,%s)", exp0, exp1,
                            info.table.getExpressionArg(\info, role, args[2], AbstractDataProviderTypeMap."int"));
                    },
                },
                COP_PREPEND: DefaultExpressionMap{COP_PREPEND} + {
                    "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("concat(%s, %s)", exp1, exp0);
                    },
                },
                COP_APPEND: DefaultExpressionMap{COP_APPEND} + {
                    "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("concat(%s, %s)", exp0, exp1);
                    },
                },
            };
 
            const FreetdsOpMap = DefaultOpMap + {
                OP_SUBSTR: {
                    "code": string sub (reference<hash<QueryInfo>> info, string cn, softlist arg) {
                        info.args += arg[0]; // start
                        if (!exists arg[1]) {
                            info.args += arg[2]; # text
                            return sprintf("substring(%s,%v,32767) = %v", cn); # NOTE: length arg is required
                        }
                        info.args += arg[1]; # count
                        info.args += arg[2]; # text
                        return sprintf("substring(%s,%v,%v) = %v", cn);
                    },
                },
            };
 
            const FreetdsCopMap = ...;
 
 
            /*  Impl. note here. There is shorter workaround for this, but it does not work with seconds
                and with dates older than 1.1.1970. (minutes will overflow later too etc.).
                So no usage for: dateadd(ss, datediff(ss, 0, %s), 0) for example
             */
            const FreetdsTruncDate = ...;
 
 
            const FreetdsIopMap = ...;
 
 
            const FreetdsUopMap = ...;
 
 
protected:
            *string filegroup;
 
            string schema;
 
            Functions triggerFunctions;
 
            string default_schema = "dbo";
 
public:
 
        constructor(AbstractDatasource nds, string nname, *hash opts) ;
 
 
        string getSchemaName();
 
 
        *string getTablespaceName();
 
 
 
        code getUpsertClosure(hash<auto> row, int upsert_strategy = UpsertAuto, *hash opt);
 
 
        bool hasArrayBind();
 
 
        string getSqlName();
 
 
        hash<auto> getExpressionMap();
 
 
        hash<auto> getWhereOperatorMap();
 
 
protected:
         string getFromIntern(string from, *hash qh);
public:
 
 
protected:
         hash getTableCreationOptions();
public:
 
 
protected:
         hash getTableDescriptionHashOptions();
public:
 
 
protected:
         hash getColumnDescOptions();
public:
 
 
protected:
         hash getIndexOptions();
public:
 
 
protected:
         hash getConstraintOptions();
public:
 
 
protected:
         hash getAlignTableOptions();
public:
 
 
protected:
         hash getColumnOperatorMapImpl();
public:
 
 
protected:
         hash getInsertOperatorMap();
public:
 
 
protected:
         hash getRawUpdateOperatorMap();
public:
 
 
protected:
         hash getSelectOptions();
public:
 
 
protected:
         bool checkExistenceImpl();
public:
 
 
protected:
         Columns describeImpl();
public:
 
 
protected:
         Columns describeSybaseImpl();
public:
 
 
protected:
         Columns describeMssqlImpl();
public:
 
 
protected:
         FreetdsPrimaryKey getPrimaryKeyImpl();
public:
 
 
protected:
         FreetdsPrimaryKey getPrimaryKeySybaseImpl();
public:
 
 
protected:
         FreetdsPrimaryKey getPrimaryKeyMssqlImpl();
public:
 
 
protected:
         Indexes getIndexesImpl();
public:
 
 
protected:
         Indexes getIndexesSybaseImpl();
public:
 
 
protected:
         Indexes getIndexesMssqlImpl();
public:
 
 
protected:
         ForeignConstraints getForeignConstraintsImpl(*hash opts);
public:
 
 
protected:
         ForeignConstraints getForeignConstraintsSybaseImpl();
public:
 
 
protected:
         ForeignConstraints getForeignConstraintsMssqlImpl();
public:
 
 
protected:
         Constraints getConstraintsImpl();
public:
 
 
protected:
         Constraints getConstraintsSybaseImpl();
public:
 
 
protected:
         Constraints getConstraintsMssqlImpl();
public:
 
 
protected:
         Triggers getTriggersImpl();
public:
 
 
protected:
         string getCreateTableSqlImpl(*hash opt);
public:
 
 
protected:
         *list getCreateMiscSqlImpl(*hash opt, bool cache);
public:
 
 
protected:
         *list getAlignSqlImpl(AbstractTable table, *hash opt);
public:
 
 
protected:
         string getCreateSqlImpl(list l);
public:
 
 
protected:
         string getRenameSqlImpl(string new_name);
public:
 
 
protected:
         AbstractColumn addColumnImpl(string cname, hash opt, bool nullable = True);
public:
 
 
protected:
         AbstractPrimaryKey addPrimaryKeyImpl(string cname, hash ch, *hash opt);
public:
 
 
protected:
         AbstractIndex addIndexImpl(string iname, bool enabled, hash ch, *hash opt);
public:
 
 
        private AbstractForeignConstraint addForeignConstraintImpl(string cname, hash ch, string table, hash tch,
                *hash opt) {
            ForeignConstraintTarget fct(table, new Columns(tch));
            return new FreetdsForeignConstraint(cname, new Columns(ch), fct);
        }
 
protected:
         AbstractCheckConstraint addCheckConstraintImpl(string cname, string src, *hash opt);
public:
 
 
protected:
         AbstractUniqueConstraint addUniqueConstraintImpl(string cname, hash ch, *hash opt);
public:
 
 
protected:
         AbstractTrigger addTriggerImpl(string tname, string src, *hash opt);
public:
 
 
protected:
         bool tryInsertImpl(string sql, hash<auto> row);
public:
 
 
 
protected:
         bool isDuplicateRowErrorImpl(hash<ExceptionInfo> ex);
public:
 
 
protected:
         hash getQoreTypeMapImpl();
public:
 
 
protected:
         hash getTypeMapImpl();
public:
 
 
 
protected:
         *string getSqlValueImpl(auto v);
public:
 
 
        string getColumnSqlName(string col);
 
 
        list getColumnSqlNames(softlist cols);
 
 
protected:
         bool emptyImpl();
public:
 
 
protected:
         clearImpl();
public:
 
 
protected:
         setupTableImpl(hash desc, *hash opt);
public:
 
 
protected:
         bool constraintsLinkedToIndexesImpl();
public:
 
 
protected:
         bool uniqueIndexCreatesConstraintImpl();
public:
 
 
protected:
         bool supportsTablespacesImpl();
public:
 
 
protected:
         doSelectLimitOnlyUnlockedImpl(reference<hash<QueryInfo>> info, reference<string> sql);
public:
 
 
        private doSelectOrderByWithOffsetSqlUnlockedImpl(reference<hash<QueryInfo>> info, reference<string> sql,
                list<auto> coll) {
            if (is_sybase);
 
 
            doSelectOrderBySqlUnlocked(info, \sql, coll);
 
            sql += " offset %v rows";
            info.args += info.query_hash.offset.toInt();
 
            if (info.query_hash.limit);
 
        }
 
protected:
         auto tryExecArgsImpl(string sql, *softlist<auto> args);
public:
 
 
protected:
         auto tryExecRawImpl(string sql, *softlist<auto> args);
public:
 
 
protected:
         copyImpl(AbstractTable old);
public:
 
 
protected:
         *hash<auto> doReturningImpl(hash<auto> opt, reference<string> sql, list<auto> args);
public:
 
 
protected:
         softbool tryUpdate(string sql, hash<auto> row, Columns cols, list updc);
public:
 
 
protected:
         AbstractDataProviderType getNumericTypeImpl(string native_type, bool nullable, *hash<auto> options);
public:
 
 
 
protected:
         AbstractSavepointHelper getSavepointHelperImpl(*string savepoint);
public:
 
    };
};