PgsqlSqlUtil.qm.dox.h

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

/*  PgsqlSqlUtil.qm Copyright (C) 2013 - 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 PgsqlSqlUtil {
     PgsqlTable get_table(AbstractDatasource nds, string nname, *hash opts);


     PgsqlDatabase get_database(AbstractDatasource nds, *hash opts);


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


    class PgsqlType {

public:
        public :
            string name;

            string src;

public:

        constructor(string n_name, string n_src);


        list getTypeList();


        list getModifySql(PgsqlType old, *hash opt);


        string getCreateSql(*hash opt);


        string getDropSql(*hash opt);


        string getDropSqlForce();


        string getRenameSql(string new_name);


        bool equal(PgsqlType type);

    };

class PgsqlIndex : public SqlUtil::AbstractIndex {

public:
        public :
            *string tablespace;

public:

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


        string getCreateSql(string table_name, *hash opt);


        private bool equalImpl(AbstractIndex ix);


        string getRenameSql(string table_name, string new_name);


    };

class PgsqlForeignConstraint : 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 PgsqlCheckConstraint : 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 PgsqlColumn : 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();



        list getAddColumnSql(AbstractTable t);



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



        string getRenameSql(AbstractTable t, string new_name);


        private bool equalImpl(AbstractColumn c);

    };

class PgsqlNumericColumn : public PgsqlColumn,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 PgsqlUniqueConstraintCommon : public SqlUtil::AbstractUniqueConstraint {

public:
        private :
            *string tablespace;

public:

        constructor(*string ts);


        *string getTablespace();


        setTablespace(*string ts);


        bool setIndexBase(string ix);


        clearIndex();


        abstract AbstractIterator keyIterator();

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

    };

class PgsqlUniqueConstraint : public SqlUtil::AbstractUniqueConstraint,public  PgsqlUniqueConstraintCommon {

public:
        private :
            bool enabled;

public:


        constructor(string n, hash n_cols, bool e = True, *string ts) ;



        PgsqlColumn 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 PgsqlPrimaryKey : public SqlUtil::AbstractPrimaryKey,public  PgsqlUniqueConstraintCommon {

public:
        constructor();



        constructor(string n, *hash c, *string ts) ;



        PgsqlColumn memberGate(string k);


        string getCreateSql(string table_name, *hash opts);


        softlist getRenameSql(string table_name, string new_name);

    };

class PgsqlSequence : 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 PgsqlView : public SqlUtil::AbstractView {

public:

        public :
            *string viewowner;

public:

        constructor(string n_name, string n_src, *string n_schemaname,
                    *string n_viewowner)
 ;


        string getCreateSql(*hash opt);


        softlist getRenameSql(string new_name);

    };

class PgsqlTrigger : 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 PgsqlFunction : 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 PgsqlTriggerFunction : public PgsqlFunction {

public:
        public :
            string trigger;

public:

        constructor(string n, string n_src, string n_trigger) ;

    };

class PgsqlDatabase : public SqlUtil::AbstractDatabase {

public:
       public :
            const PgsqlSchemaDescriptionOptions = AbstractDatabase::SchemaDescriptionOptions + (
                "types": Type::Hash,
                "type_map": Type::Hash,
                );

            const PGSQL_TempSavepoint = "qore_pgsql_tmp_savepoint";

            const PgsqlReclaimSpaceOptions = ReclaimSpaceOptions + (
                "full"              : Type::Boolean,
                "analyze"           : Type::Boolean,
                );

public:

        constructor(AbstractDatasource nds, *hash opts) ;


        private list featuresImpl();


        PgsqlType makeType(string name, string src, *hash opts);


        *PgsqlType getType(string name);


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


        private *AbstractSequence getSequenceImpl(string name);


        private *AbstractView getViewImpl(string name);


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


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


        private *AbstractFunction getFunctionImpl(string name);


        private AbstractFunction getProcedureImpl(string name);


          static *string getFunctionArgs(string err, reference src);

          static PgsqlFunction processFunction(hash row, bool native_case);

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


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


        private list listTablesImpl();



        private list listFunctionsImpl();



        private list listProceduresImpl();


        private list listSequencesImpl();


        private list listViewsImpl();


        list listMaterializedViews();


        ListIterator materializedViewIterator();


        list listTypes();


        ListIterator typeIterator();


        private string getCreateSqlImpl(list l);


          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 PgsqlTable : public SqlUtil::AbstractTable {

public:
        public :
            const PgsqlTypeMap = (
                "abstime": ("qore": Type::Date,),
                "bigint": ("qore": Type::Int,),
                "bigserial": ("qore": Type::Int,),
                "bit": ("qore": Type::Binary, "size": SZ_OPT,),
                "bit varying": ("qore": Type::Binary, "size": SZ_OPT,),
                "bool": ("qore": Type::Boolean,),
                "boolean": ("qore": Type::Boolean,),
                "bytea": ("qore": Type::Binary,),
                "char": ("qore": Type::String, "size": SZ_OPT,),
                "character": ("qore": Type::String, "size": SZ_OPT,),
                "character varying": ("qore": Type::String, "size": SZ_OPT,),
                "cidr": ("qore": Type::String,),
                "date": ("qore": Type::Date,),
                "decimal": ("qore": Type::Number, "size": SZ_NUM,),
                "double precision": ("qore": Type::Float,),
                "float": ("qore": Type::Float,),
                "inet": ("qore": Type::String,),
                "int": ("qore": Type::Int,),
                "integer": ("qore": Type::Int,),
                "interval": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "macaddr": ("qore": Type::String,),
                "money": ("qore": Type::Number,),
                "numeric": ("qore": Type::Number, "size": SZ_NUM,),
                "oid": ("qore": Type::Int,),
                "real": ("qore": Type::Float,),
                "reltime": ("qore": Type::Date,),
                "serial": ("qore": Type::Int,),
                "smallint": ("qore": Type::Int,),
                "smallserial": ("qore": Type::Int,),
                "text": ("qore": Type::String,),
                "time": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "time with time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "time without time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "timetz": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "timestamp": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "timestamp with time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "timestamp without time zone": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "timestamptz": ("qore": Type::Date, "size": SZ_OPT, "size_range": (0, 6), "default_size": 6,),
                "varbit": ("qore": Type::Binary, "size": SZ_OPT,),
                "varchar": ("qore": Type::String, "size": SZ_OPT,),
                );

            const PgsqlNameMap = (
                "bit varying": "varbit",
                "character": "char",
                "character varying": "varchar",
                "char varying": "varchar",
                "double precision": "float",
                "time with time zone": "timetz",
                "time without time zone": "time",
                "timestamp with time zone": "timestamptz",
                "timestamp without time zone": "timestamp",
                );

            const QoreTypeMap = (
                "integer": "bigint",
                "float": "double precision",
                "number": "numeric",
                "string": "varchar",
                "date": "timestamp",
                "binary": "bytea",
                "bool": "boolean",
                SqlUtil::CHAR: "char",
                SqlUtil::CLOB: "text",
                SqlUtil::BLOB: "bytea",
                );

            const PgsqlTableDescriptionHashOptions = AbstractTable::TableDescriptionHashOptions + (
                "functions": Type::Hash,
                );

            const PgsqlColumnDescOptions = AbstractTable::ColumnDescOptions;

            const PgsqlIndexOptions = AbstractTable::IndexOptions;

            const PgsqlConstraintOptions = AbstractTable::ConstraintOptions + PgsqlIndexOptions + (
                "index": Type::String,
                );

            const PgsqlTableCreationOptions = AbstractTable::TableCreationOptions + PgsqlConstraintOptions + (
                "omit_trigger_functions": Type::Boolean,
                );

            const PgsqlAlignTableOptions = AbstractTable::AlignTableOptions + PgsqlTableCreationOptions;

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

                        sql += ")";
                        return sql;
                    },
                ),
                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("to_char(%s, 'YYYY')", arg1);
                    }
                ),
                COP_YEAR_MONTH: (
                    "code": string (string arg1, any arg) {
                        return sprintf("to_char(%s, 'YYYY-MM')", arg1);
                    }
                ),
                COP_YEAR_DAY: (
                    "code": string (string arg1, any arg) {
                        return sprintf("to_char(%s, 'YYYY-MM-DD')", arg1);
                    }
                ),
                COP_YEAR_HOUR: (
                    "code": string (string arg1, any arg) {
                        return sprintf("to_char(%s, 'YYYY-MM-DD HH24')", arg1);
                    }
                ),
                COP_SEQ: (
                    "nocolumn": True,
                    "withalias": True,
                    "code": string (*string cve, hash arg, reference psch) {
                        string sql = sprintf("nextval('%s')", arg.seq);
                        if (arg.as);

                        return sql;
                    }
                ),
                COP_SEQ_CURRVAL: (
                    "nocolumn": True,
                    "withalias": True,
                    "code": string (*string cve, hash arg, reference psch) {
                        string sql = sprintf("currval('%s')", arg.seq);
                        if (arg.as);

                        return sql;
                    }
                ),
                );

            const PgsqlIopMap = DefaultIopMap + (
                IOP_SEQ: (
                    "arg": Type::String,
                    "placeholder": "nextval(%v)",
                ),
                IOP_SEQ_CURRVAL: (
                    "arg": Type::String,
                    "placeholder": "currval(%v)",
                ),
                );

            const PgsqlUopMap = DefaultUopMap + (
                COP_SEQ: (
                    "nocolumn": True,
                    "code": string (*string cve, string arg) {
                        return sprintf("nextval('%s')", arg);
                    }
                ),
                COP_SEQ_CURRVAL: (
                    "nocolumn": True,
                    "withalias": True,
                    "code": string (*string cve, string arg) {
                        return sprintf("currval('%s')", arg);
                    }
                ),
                );

public:

        private :
            *string tablespace;

            string schema;

            Functions triggerFunctions;

public:

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


        string getSchemaName();


        *string getTablespaceName();


        PgsqlFunction addTriggerFunction(string tfname, string src, string trigger);


        bool hasArrayBind();


        private PgsqlFunction addTriggerFunctionUnlocked(string tfname, string src, string trigger);


        string getSqlName();


        private hash getTableCreationOptions();


        private hash getTableDescriptionHashOptions();


        private hash getColumnDescOptions();


        private hash getIndexOptions();


        private hash getConstraintOptions();


        private hash getAlignTableOptions();


        private hash getColumnOperatorMap();


        private hash getInsertOperatorMap();


        private hash getRawUpdateOperatorMap();


        private bool checkExistenceImpl();


        private Columns describeImpl();


        private PgsqlPrimaryKey getPrimaryKeyImpl();


        private Indexes getIndexesImpl();


        private ForeignConstraints getForeignConstraintsImpl(*hash opts);


        private Constraints getConstraintsImpl();


        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 softlist getDropSqlImpl();


        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 list getGroupOrderByListUnlocked(string key, hash qh, *hash jch, *hash ch, *hash psch, list coll);

    };
};