FreetdsSqlUtil.qm.dox.h

// -*- mode: c++; indent-tabs-mode: nil -*-
// @file FreetdsSqlUtil.qm Qore user module for working with PostgreSQL SQL data

/*  FreetdsSqlUtil.qm Copyright (C) 2015 - 2016 Qore Technologies, sro

    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 qore version

// requires the SqlUtil 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 opts);


     FreetdsDatabase get_database(AbstractDatasource nds, *hash opts);


    parse_schema_name(string nname, reference schema, reference name);


class FreetdsIndex : public SqlUtil::AbstractIndex {

public:
        public :
            *string filegroup;

public:

        constructor(string n_name, bool n_unique, hash n_cols, *string n_filegroup) ;


        string getCreateSql(string table_name, *hash opt);


        private bool equalImpl(AbstractIndex ix);


        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:
        public :
            int byte_size;

public:

        constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string cm, softint bs) ;


        string getNativeTypeString();



        softlist getAddColumnSql(AbstractTable t);



        list getModifySqlImpl(AbstractTable t, AbstractColumn col, *hash opt);



        string getRenameSql(AbstractTable t, string new_name);


        private bool equalImpl(AbstractColumn c);

    };

class FreetdsNumericColumn : public FreetdsColumn,public  SqlUtil::NumericColumnInfo {

public:
        constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string cm, softint bs, softint n_scale) ;


        string getNativeTypeString();

    };

class FreetdsUniqueConstraintCommon : public SqlUtil::AbstractUniqueConstraint {

public:
        private :
            *string filegroup;

public:

        constructor(*string ts);


        *string getTablespace();


        setTablespace(*string ts);


        bool setIndexBase(string ix);


        clearIndex();


        getIndexSql(reference sql, string name, *hash opts);


        abstract AbstractIterator keyIterator();
    };

class FreetdsUniqueConstraint : public SqlUtil::AbstractUniqueConstraint,public  FreetdsUniqueConstraintCommon {

public:
        private :
            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);


        string getRenameSql(string new_name);

    };

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);

    };

class FreetdsTrigger : public SqlUtil::AbstractTrigger {

public:
        constructor(string n, string n_src) ;


        softlist getCreateSql(string table_name, *hash opt);


        softlist getDropSql(string table_name);


        private bool equalImpl(AbstractFunctionBase t);


        softlist getRenameSql(string table_name, string new_name);

    };

class FreetdsFunction : public SqlUtil::AbstractFunction {

public:
        public :
            *string args;

public:

        constructor(string n, string n_src, *string a) ;


        softlist getCreateSql(*hash opt);


        string getDropSql();


        private bool equalImpl(AbstractFunctionBase t);


        softlist getRenameSql(string new_name);


        setName(string new_name);

    };

class FreetdsDatabase : public SqlUtil::AbstractDatabase {

public:
        public :
            bool is_sybase;

            const FreetdsSchemaDescriptionOptions = AbstractDatabase::SchemaDescriptionOptions;

            const FreeTDS_TempSavepoint = "qore_freetds_tmp_savepoint";

            const FreetdsReclaimSpaceOptions = ReclaimSpaceOptions;

public:

        constructor(AbstractDatasource nds, *hash opts) ;


        private list featuresImpl();


        private FreetdsSequence makeSequenceImpl(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);


        private *AbstractSequence getSequenceImpl(string name);


        private *AbstractView getViewImpl(string name);


        private FreetdsFunction makeFunctionImpl(string name, string src, *hash opts);


        private FreetdsFunction makeProcedureImpl(string name, string src, *hash opts);


        private *AbstractFunction getFunctionImpl(string name);


        private AbstractFunction getProcedureImpl(string name);


        private list getDropSchemaSqlImpl(hash schema_hash, *hash opt);


        private list getAlignSqlImpl(hash schema_hash, *hash opt);


        private softlist listTablesImpl();



        private list listFunctionsImpl();



        private list listProceduresImpl();


        private list listSequencesImpl();


        private list listViewsImpl();


        private string getCreateSqlImpl(list l);


          static bool isSybase(AbstractDatasource ds);

          static string getCreateSql(list l);

        private hash getSchemaDescriptionOptions();


        private hash getReclaimSpaceOptions();


        private softint getNextSequenceValueImpl(string name);


        private softint getCurrentSequenceValueImpl(string name);


        private bool supportsSequencesImpl();


        private bool supportsTypesImpl();


        private bool supportsPackagesImpl();


        private bool rebuildIndexImpl(string name, *hash options);


        private computeStatisticsImpl(*hash options);


        private reclaimSpaceImpl(*hash options);


        private any tryExecArgsImpl(string sql, *softlist args);


        private any tryExecRawImpl(string sql, *softlist args);


          static any tryExecArgs(AbstractDatasource ds, string sql, *softlist args);

          static any tryExecRaw(AbstractDatasource ds, string sql);
    };

class FreetdsTable : public SqlUtil::AbstractTable {

public:
        public :
            bool is_sybase;

            bool ss_has_merge = False;

            const FreetdsTypeMap = (
                "numeric": ("qore": Type::Number, "size": SZ_NUM,),
                "decimal": ("qore": Type::Number, "size": SZ_NUM,),
                "tinyint": ("qore": Type::Int,),
                "smallint": ("qore": Type::Int,),
                "int": ("qore": Type::Int,),
                "bigint": ("qore": Type::Int,),
                "double": ("qore": Type::Float,),
                "float": ("qore": Type::Float,), // NOTE: optional precision
                "real": ("qore": Type::Float,),
                "money": ("qore": Type::Number,),
                "smallmoney": ("qore": Type::Number,),

                "datetime": ("qore": Type::Date,),
                "smalldatetime": ("qore": Type::Date,),
                "date": ("qore": Type::Date,),
                "time": ("qore": Type::Date,),

                // NOTE: "size_range" is different between MS SQL Server and Sybase SQL Anywhere Server
                // MS SQL: range is (1, 8000) or special keyword "max" for varchar types
                // Sybase: range is (1, 32767) and size is mandatory for character-length semantics
                "char": ("qore": Type::String, "size": SZ_OPT),
                "char varying": ("qore": Type::String, "size": SZ_OPT),
                "character varying": ("qore": Type::String, "size": SZ_OPT),
                "varchar": ("qore": Type::String, "size": SZ_OPT),

                // The following uni* types are used in Sybase Adaptive Server
                "unichar": ("qore": Type::String, "size": SZ_MAND,),
                "univarchar": ("qore": Type::String, "size": SZ_MAND,),
                "unitext": ("qore": Type::String,),

                // NOTE: "size_range" is different between MS SQL Server and Sybase SQL Anywhere Server
                // MS SQL: range is (1, 8000) or special keyword "max" for varbinary types
                // Sybase: range is (1, 32767)
                "binary": ("qore": Type::Binary, "size": SZ_OPT),
                "binary varying": ("qore": Type::Binary, "size": SZ_OPT),
                "varbinary": ("qore": Type::Binary, "size": SZ_OPT),

                // The following are used in Sybase, supported & deprecated in MS SQL
                "text": ("qore": Type::String,), // domain, implemented as "long varchar"
                "image": ("qore": Type::Binary,), // domain, implemented as "long binary"
                // The following are Sybase specific
                "long binary": ("qore": Type::Binary,),
                "long varchar": ("qore": Type::String,),
                );

            const QoreTypeMap = (
                "integer": "bigint",
                "float": "float",
                "number": "numeric",
                "string": "varchar",
                "date": "datetime",
                "binary": "varbinary",
                "bool": "tinyint",
                SqlUtil::CHAR: "char",
                SqlUtil::CLOB: "text",
                SqlUtil::BLOB: "varbinary",
                );

            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 = AbstractTable::SelectOptions + (
                "tablehint": "softstringlist",
                );

            const MsSqlServerTableHints = (
                "fastfirstrow": True,
                "holdlock": True,
                "nolock": True,
                "paglock": True,
                "readcommitted": True,
                "readpast": True,
                "readuncommitted": True,
                "repeatableread": True,
                "rowlock": True,
                "serializable": True,
                "tablock": True,
                "tablockx": True,
                "updlock": True,
                "xlock": True,
                );

            const FreetdsOpMap = DefaultOpMap + (
                OP_SUBSTR: (
                    "code": string (object t, string cn, any arg, reference args, *hash jch, bool join = False, *hash ch, *hash psch) {
                        args += arg[0]; // start
                        if (!exists arg[1]);

                        args += arg[1]; // count
                        args += arg[2]; // text
                        return sprintf("substring(%s,%v,%v) = %v", cn);
                    },
                ),
            );

            const FreetdsCopMap = DefaultCopMap + (
                COP_CAST: (
                    "code": string (string cve, list args) {
                        string name = QoreTypeMap{args[0]} ?? args[0];
                        hash desc = FreetdsTypeMap{name};
                        string sql = sprintf ("cast (%s as %s", cve, name);
                        switch (name);

                        sql += ")";
                        return sql;
                    },
                ),
                COP_PREPEND: (
                    "arg": Type::String,
                    "sqlvalue": True,
                    "code": string (string cve, string arg) {
                        return sprintf("%s + %s", arg, cve);
                    },
                ),
                COP_APPEND: (
                    "arg": Type::String,
                    "sqlvalue": True,
                    "code": string (string cve, string arg) {
                        return sprintf("%s + %s", cve, arg);
                    },
                ),
                COP_SUBSTR: (
                    "code": string (string cve, list args) {
                        if (!exists args[1])
                            return sprintf("substring(%s,%d,32767)", cve, args[0]); // NOTE: length arg is required
                        return sprintf("substring(%s,%d,%d)", cve, args[0], args[1]);
                    },
                ),
                COP_OVER: (
                    "argcolumn": True,
                    "argoptional": True,
                    "code": string (*string cve, *string arg) {
                        string sql = cve + " over (";
                        if (arg)
                            sql += sprintf("partition by %s", arg);
                        sql += ")";
                        return sql;
                    },
                ),
                COP_YEAR: (
                    "code": string (string arg1, any arg) {
                        return sprintf("substring(convert(varchar, dt, 120), 1, 4)", arg1);
                    }
                ),
                COP_YEAR_MONTH: (
                    "code": string (string arg1, any arg) {
                        return sprintf("substring(convert(varchar, dt, 120), 1, 7)", arg1);
                    }
                ),
                COP_YEAR_DAY: (
                    "code": string (string arg1, any arg) {
                        return sprintf("substring(convert(varchar, dt, 120), 1, 10)", arg1);
                    }
                ),
                COP_YEAR_HOUR: (
                    "code": string (string arg1, any arg) {
                        return sprintf("substring(convert(varchar, dt, 120), 1, 13)", arg1);
                    }
                ),
                COP_SEQ: (
                    "nocolumn": True,
                    "withalias": True,
                    "code": string (*string cve, hash arg, reference psch) {
                        string sql = sprintf("next value for %s", arg.seq);
                        if (arg.as);

                        return sql;
                    }
                ),
                COP_SEQ_CURRVAL: (
                    "nocolumn": True,
                    "code": string (*string cve, hash arg) {
                        throw "SEQUENCE-ERROR", sprintf("cannot select the current value of sequence %y because this database does not support this operation", arg.seq);
                    }
                ),
                );

            const FreetdsIopMap = DefaultIopMap + (
                IOP_SEQ: (
                    "arg": Type::String,
                    "placeholder": "next value for %v",
                ),
                );

            const FreetdsUopMap = DefaultUopMap + (
                COP_SEQ: (
                    "nocolumn": True,
                    "withalias": True,
                    "code": string (*string cve, string arg) {
                        return sprintf("next value for %s", arg);
                    }
                ),
                COP_SEQ_CURRVAL: (
                    "nocolumn": True,
                    "code": string (*string cve, string arg) {
                        throw "SEQUENCE-ERROR", sprintf("cannot select the current value of sequence %y because this database does not support this operation", arg);
                    }
                ),
                );

public:

        private :
            *string filegroup;

            string schema;

            Functions triggerFunctions;

public:

        constructor(AbstractDatasource nds, string nname, *hash opts) ;


        string getSchemaName();


        *string getTablespaceName();


        code getUpsertClosure(hash row, int upsert_strategy = UpsertAuto, *hash opt);


        bool hasArrayBind();


        string getSqlName();


        private string getFromIntern(string from, *hash qh);


        private hash getTableCreationOptions();


        private hash getTableDescriptionHashOptions();


        private hash getColumnDescOptions();


        private hash getIndexOptions();


        private hash getConstraintOptions();


        private hash getAlignTableOptions();


        private hash getWhereOperatorMap();


        private hash getColumnOperatorMap();


        private hash getInsertOperatorMap();


        private hash getRawUpdateOperatorMap();


        private hash getSelectOptions();


        private bool checkExistenceImpl();


        private Columns describeImpl();


        private Columns describeSybaseImpl();


        private Columns describeMssqlImpl();


        private FreetdsPrimaryKey getPrimaryKeyImpl();


        private FreetdsPrimaryKey getPrimaryKeySybaseImpl();


        private FreetdsPrimaryKey getPrimaryKeyMssqlImpl();


        private Indexes getIndexesImpl();


        private Indexes getIndexesSybaseImpl();


        private Indexes getIndexesMssqlImpl();


        private ForeignConstraints getForeignConstraintsImpl(*hash opts);


        private ForeignConstraints getForeignConstraintsSybaseImpl();


        private ForeignConstraints getForeignConstraintsMssqlImpl();


        private Constraints getConstraintsImpl();


        private Constraints getConstraintsSybaseImpl();


        private Constraints getConstraintsMssqlImpl();


        private Triggers getTriggersImpl();


        private string getCreateTableSqlImpl(*hash opt);


        private *list getCreateMiscSqlImpl(*hash opt, bool cache);


        private *list getAlignSqlImpl(AbstractTable table, *hash opt);


        private string getCreateSqlImpl(list l);


        private string getRenameSqlImpl(string new_name);


        private AbstractColumn addColumnImpl(string cname, hash opt, bool nullable = True);


        private AbstractPrimaryKey addPrimaryKeyImpl(string cname, hash ch, *hash opt);


        private AbstractIndex addIndexImpl(string iname, bool enabled, hash ch, *hash opt);


        private AbstractForeignConstraint addForeignConstraintImpl(string cname, hash ch, string table, hash tch, *hash opt);


        private AbstractCheckConstraint addCheckConstraintImpl(string cname, string src, *hash opt);


        private AbstractUniqueConstraint addUniqueConstraintImpl(string cname, hash ch, *hash opt);


        private AbstractTrigger addTriggerImpl(string tname, string src, *hash opt);


        private bool tryInsertImpl(string sql, hash row);


        private hash getQoreTypeMapImpl();


        private hash getTypeMapImpl();


        private *string getSqlValueImpl(any v);


        private bool emptyImpl();


        private clearImpl();


        private setupTableImpl(hash desc, *hash opt);


        private bool constraintsLinkedToIndexesImpl();


        private bool uniqueIndexCreatesConstraintImpl();


        private bool supportsTablespacesImpl();


        private doSelectLimitOnlyUnlockedImpl(reference sql, reference args, *hash qh);


        private doSelectOrderByWithOffsetSqlUnlockedImpl(reference sql, reference args, *hash qh, *hash jch, *hash ch, *hash psch, list coll);


        private any tryExecArgsImpl(string sql, *softlist args);


        private any tryExecRawImpl(string sql, *softlist args);


        private copyImpl(AbstractTable old);


        private *hash doReturningImpl(hash opt, reference sql, list args);


        private softbool tryUpdate(string sql, hash row, Columns cols, list updc);

    };
};