Exemplo n.º 1
0
def verify_domain(server, db_name, schema_id, domain_name):
    """
    This function get the oid & name of the domain
    :param server: server details
    :type server: dict
    :param db_name: db name
    :type db_name: str
    :param schema_id: schema id
    :type schema_id: int
    :param domain_name: domain name
    :type domain_name: str
    :return:
    """
    connection = utils.get_db_connection(db_name,
                                         server['username'],
                                         server['db_password'],
                                         server['host'],
                                         server['port'])
    pg_cursor = connection.cursor()
    pg_cursor.execute("SELECT d.oid, d.typname FROM pg_type d WHERE"
                      " d.typname='%s' AND d.typnamespace='%s'" %
                      (domain_name, schema_id))
    domains = pg_cursor.fetchone()
    connection.close()
    return domains
Exemplo n.º 2
0
def create_trigger_function_with_trigger(server, db_name, schema_name,
                                         func_name):
    """This function add the trigger function to schema"""
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        query = "CREATE FUNCTION " + schema_name + "." + func_name + \
                "()" \
                " RETURNS trigger LANGUAGE 'plpgsql' STABLE LEAKPROOF" \
                " SECURITY DEFINER SET enable_sort=true AS $BODY$ BEGIN" \
                " NULL; END; $BODY$"
        pg_cursor.execute(query)
        connection.commit()
        # Get 'oid' from newly created function
        pg_cursor.execute("SELECT pro.oid, pro.proname FROM"
                          " pg_proc pro WHERE pro.proname='%s'" %
                          func_name)
        functions = pg_cursor.fetchone()
        connection.close()
        return functions
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 3
0
def create_domain(server, db_name, schema_name, schema_id, domain_name):
    """
    This function is used to add the domain to existing schema
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param schema_id: schema id
    :type schema_id: int
    :param domain_name: domain name
    :type domain_name: str
    :return: None
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'])
        pg_cursor = connection.cursor()
        query = 'CREATE DOMAIN ' + schema_name + '.' + domain_name + \
                ' AS character(10) DEFAULT 1'
        pg_cursor.execute(query)
        connection.commit()
        # Get 'oid' from newly created domain
        pg_cursor.execute("SELECT d.oid, d.typname FROM pg_type d WHERE"
                          " d.typname='%s' AND d.typnamespace='%s'" %
                          (domain_name, schema_id))
        domains = pg_cursor.fetchone()
        connection.close()
        return domains
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 4
0
def drop_extension(server, db_name, extension_name):
    """
    This function used to drop the extension.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param extension_name: extension name
    :type extension_name: str
    :return: None
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "SELECT * FROM pg_extension WHERE extname='%s'"
            % extension_name)
        if pg_cursor.fetchall():
            pg_cursor.execute(
                "DROP EXTENSION %s CASCADE" % extension_name)
            connection.commit()
        connection.close()
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 5
0
def verify_table(server, db_name, table_id):
    """
    This function verifies table exist in database or not.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param table_id: schema name
    :type table_id: int
    :return table: table record from database
    :rtype: tuple
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("SELECT * FROM pg_class tb WHERE tb.oid=%s" %
                          table_id)
        table = pg_cursor.fetchone()
        connection.close()
        return table
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 6
0
def verify_rule(server, db_name, rule_name):
    """
    This function verifies rule exist in database or not.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param rule_name: rule name
    :type rule_name: str
    :return rule: rule record from database
    :rtype: tuple
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("select * from pg_rewrite where rulename='%s'" %
                          rule_name)
        rule = pg_cursor.fetchone()
        connection.close()
        return rule
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 7
0
def verify_fdw(server, db_name, fdw_name):
    """
    This function will verify current foreign data wrapper.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param fdw_name: FDW name
    :type fdw_name: str
    :return fdw: fdw details
    :rtype: tuple
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname = '%s'"
            % fdw_name)
        fdw = pg_cursor.fetchone()
        connection.close()
        return fdw
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 8
0
def create_pgagent_job(self, name):
    """
    This function create the pgAgent job.
    """
    try:
        connection = utils.get_db_connection(
            self.server['db'],
            self.server['username'],
            self.server['db_password'],
            self.server['host'],
            self.server['port'],
            self.server['sslmode']
        )
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            """
            INSERT INTO pgagent.pga_job(
                jobjclid, jobname, jobdesc, jobhostagent, jobenabled
            ) VALUES (
                1::integer, '{0}'::text, ''::text, ''::text, true
            ) RETURNING jobid;
            """.format(name)
        )
        job_id = pg_cursor.fetchone()
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        connection.close()
        return job_id[0]
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 9
0
def verify_sequence(server, db_name, sequence_name):
    """
    This function verifies the sequence in database
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param sequence_name: sequence name
    :type sequence_name: str
    :return sequence: sequence record from database
    :rtype: tuple
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("select * from pg_class where relname='%s'" %
                          sequence_name)
        sequence = pg_cursor.fetchone()
        connection.close()
        return sequence
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 10
0
def create_fts_template(server, db_name, schema_name, fts_temp_name):
    """This function will add the fts_template under test schema. """

    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        pg_cursor = connection.cursor()

        query = "DROP TEXT SEARCH TEMPLATE IF EXISTS " + schema_name + "." + \
                fts_temp_name
        pg_cursor.execute(query)

        query = "CREATE TEXT SEARCH TEMPLATE " + schema_name + "." + \
                fts_temp_name + \
                "(INIT=dispell_init, LEXIZE=dispell_lexize)"
        pg_cursor.execute(query)
        connection.commit()

        # Get 'oid' from newly created template
        pg_cursor.execute("select oid from pg_catalog.pg_ts_template where "
                          "tmplname = '%s' order by oid ASC limit 1" %
                          fts_temp_name)

        oid = pg_cursor.fetchone()
        fts_temp_id = ''
        if oid:
            fts_temp_id = oid[0]
        connection.close()
        return fts_temp_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 11
0
def create_tablespace(server, test_tablespace_name):
    try:
        connection = utils.get_db_connection(server['db'],
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        pg_cursor.execute("CREATE TABLESPACE %s LOCATION '%s'" %
                          (test_tablespace_name, server['tablespace_path']))
        connection.set_isolation_level(old_isolation_level)
        connection.commit()

        # Get 'oid' from newly created tablespace
        pg_cursor.execute(
            "SELECT ts.oid from pg_tablespace ts WHERE ts.spcname='%s'" %
            test_tablespace_name)
        oid = pg_cursor.fetchone()
        tspc_id = ''
        if oid:
            tspc_id = oid[0]
        connection.close()
        return tspc_id
    except Exception as exception:
        raise Exception("Error while creating tablespace. %s" % exception)
Exemplo n.º 12
0
def verify_table_space(server, test_tablespace_name):
    """
    This function calls the GET API for role to verify
    :param server: server info
    :type server: dict
    :param test_tablespace_name: tablespace name
    :type test_tablespace_name: str
    :return tablespace_count: tablespace count
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(server['db'],
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("SELECT * FROM pg_tablespace ts WHERE"
                          " ts.spcname='%s'" % test_tablespace_name)
        tablespace_count = len(pg_cursor.fetchall())
        connection.close()
        return tablespace_count
    except Exception as exception:
        exception = "%s: line:%s %s" % (
            file_name, sys.exc_traceback.tb_lineno, exception)
        print(exception, file=sys.stderr)
Exemplo n.º 13
0
def verify_user_mapping(server, db_name, fsrv_name):
    """
    This function will verify current foreign server.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param fsrv_name: FS name
    :type fsrv_name: str
    :return user_mapping: user mapping record
    :rtype: tuple
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "select umid from pg_user_mappings where srvname = '%s' order by"
            " umid asc limit 1" % fsrv_name)
        user_mapping = pg_cursor.fetchone()
        connection.close()
        return user_mapping
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 14
0
def verify_view(server, db_name, view_name):
    """
    This function verifies view exist in database or not.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param view_name: view name
    :type view_name: str
    :return table: table record from database
    :rtype: tuple
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("select oid from pg_class where relname='%s'" %
                          view_name)
        view = pg_cursor.fetchone()
        connection.close()
        return view
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 15
0
def verify_synonym(server, db_name, synonym_name):
    """
    This function create the synonym on given schema node.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param synonym_name: synonym name
    :type synonym_name: str
    :return synonym: synonym record from database
    :rtype: tuple
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("SELECT * FROM pg_synonym WHERE synname='%s'" %
                          synonym_name)
        synonym = pg_cursor.fetchone()
        connection.close()
        return synonym
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 16
0
def verify_event_trigger(server, db_name, trigger_name):
    """
    This function verifies the event trigger is present in the database
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param trigger_name: trigger name to be verified
    :type trigger_name: str
    :return event_trigger: event trigger's details
    :rtype event_trigger: tuple
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "SELECT oid FROM pg_event_trigger WHERE evtname = '%s'"
            % trigger_name)
        event_trigger = pg_cursor.fetchone()
        connection.close()
        return event_trigger
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 17
0
def verify_role(server, role_name):
    """
    This function calls the GET API for role to verify
    :param server: server details
    :type server: dict
    :param role_name: role name
    :type role_name: str
    :return role: role record from db
    :rtype role: dict
    """
    try:
        connection = utils.get_db_connection(server['db'],
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "SELECT * from pg_catalog.pg_roles pr WHERE pr.rolname='%s'" %
            role_name)
        connection.commit()
        role = pg_cursor.fetchone()
        connection.close()
        return role
    except Exception as exception:
        exception = "Error while getting role: %s: line:%s %s" % (
            file_name, sys.exc_traceback.tb_lineno, exception)
        print(exception, file=sys.stderr)
Exemplo n.º 18
0
    def runTest(self):
        db_con = test_utils.get_db_connection(
            self.encode_db_name,
            self.server['username'],
            self.server['db_password'],
            self.server['host'],
            self.server['port'],
            self.server['sslmode']
        )

        old_isolation_level = db_con.isolation_level
        db_con.set_isolation_level(0)
        db_con.set_client_encoding(self.db_encoding)
        pg_cursor = db_con.cursor()
        query = """INSERT INTO {0} VALUES('{1}')""".format(
            self.table_name, self.test_str)
        pg_cursor.execute(query)
        db_con.set_isolation_level(old_isolation_level)
        db_con.commit()

        query = """SELECT * FROM {0}""".format(self.table_name)
        pg_cursor.execute(query)
        resp = pg_cursor.fetchone()

        if sys.version_info < (3,):
            self.assertEqual(resp[0].encode("raw_unicode_escape"),
                             self.test_str)
        else:
            self.assertEqual(resp[0], self.test_str)
Exemplo n.º 19
0
def create_resource_groups(server, resource_group_name):
    """
    This function create the resource groups into databases.
    :param server: server details
    :type server: dict
    :param resource_group_name: resource group name
    :type resource_group_name: str
    :return resource_group_id: resource group id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(server['db'],
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        pg_cursor.execute("CREATE RESOURCE GROUP %s" % resource_group_name)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get oid of newly created resource group
        pg_cursor.execute("SELECT oid FROM edb_resource_group WHERE "
                          "rgrpname='%s'" % resource_group_name)
        resource_group = pg_cursor.fetchone()
        resource_group_id = resource_group[0]
        connection.close()
        return resource_group_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 20
0
def verify_resource_group(server, resource_group_name):
    """
    This function verifies the resource group exist in database or not.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param resource_group_name: resource group name
    :type resource_group_name: str
    :return:
    """
    try:
        connection = utils.get_db_connection(server['db'],
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("SELECT * FROM edb_resource_group WHERE "
                          "rgrpname='%s'" % resource_group_name)
        resource_group = pg_cursor.fetchone()
        connection.close()
        return resource_group
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 21
0
def verify_extension(server, db_name, extension_name):
    """
    This function will verify current extension.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param extension_name: extension name to be added
    :type extension_name: str
    :return extension: extension detail
    :rtype: tuple
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        pg_cursor = connection.cursor()

        pg_cursor.execute(
            "select * from pg_extension where extname='%s'" % extension_name)
        extension = pg_cursor.fetchone()
        connection.close()
        return extension
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 22
0
def verify_foreignkey(server, db_name, local_table_name):
    """
    This function verifies foreign key constraint exist or not.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param local_table_name: local table name
    :type local_table_name: str
    :return table: table record from database
    :rtype: tuple
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "SELECT oid FROM pg_constraint where conname='%s_id_fkey'" %
            local_table_name)
        fk_record = pg_cursor.fetchone()
        connection.close()
        return fk_record
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 23
0
def delete_pgagent_job(self):
    """
    This function deletes the pgAgent job.
    """
    try:
        connection = utils.get_db_connection(
            self.server['db'],
            self.server['username'],
            self.server['db_password'],
            self.server['host'],
            self.server['port'],
            self.server['sslmode']
        )
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "DELETE FROM pgagent.pga_job "
            "WHERE jobid = '%s'::integer;" % self.job_id
        )
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        connection.close()
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 24
0
def create_role(server, role_name):
    """
    This function create the role.
    :param server:
    :param role_name:
    :return:
    """
    try:
        connection = utils.get_db_connection(server['db'],
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("CREATE ROLE %s LOGIN" % role_name)
        connection.commit()
        # Get 'oid' from newly created tablespace
        pg_cursor.execute(
            "SELECT pr.oid from pg_catalog.pg_roles pr WHERE pr.rolname='%s'" %
            role_name)
        oid = pg_cursor.fetchone()
        role_id = ''
        if oid:
            role_id = oid[0]
        connection.close()
        return role_id
    except Exception as exception:
        exception = "Error while deleting role: %s: line:%s %s" % (
            file_name, sys.exc_traceback.tb_lineno, exception)
        print(exception, file=sys.stderr)
Exemplo n.º 25
0
    def before(self):
        connection = test_utils.get_db_connection(
            self.server['db'],
            self.server['username'],
            self.server['db_password'],
            self.server['host'],
            self.server['port'],
            self.server['sslmode'])

        self.timezone = int(test_utils.get_timezone_without_dst(connection))

        if abs(self.timezone) % 3600 > 0:
            hh_mm = '%H:%M'
        else:
            hh_mm = '%H'

        self.timezone_hh_mm = time.strftime(
            hh_mm, time.gmtime(abs(self.timezone)))

        if self.timezone < 0:
            self.timezone_hh_mm = '-{}'.format(self.timezone_hh_mm)
        else:
            self.timezone_hh_mm = '+{}'.format(self.timezone_hh_mm)

        self.database_version = connection.server_version

        # For this test case we need to set "Insert bracket pairs?"
        # SQL Editor preference to 'false' to avoid codemirror
        # to add matching closing bracket by it self.
        self._update_preferences()

        # close the db connection
        connection.close()
Exemplo n.º 26
0
def verify_fts_parser(server, db_name, fts_parser_name):
    """
    This function will verify current FTS parser.

    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param fts_parser_name: FTS parser name to be added
    :type fts_parser_name: str
    :return fts_temp: FTS parser detail
    :rtype: tuple
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        pg_cursor = connection.cursor()

        pg_cursor.execute(
            "select oid from pg_catalog.pg_ts_parser where "
            "prsname = '%s' order by oid ASC limit 1"
            % fts_parser_name)
        fts_parser = pg_cursor.fetchone()
        connection.close()
        return fts_parser
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 27
0
 def runTest(self):
     """ This function will update added cast."""
     self.server_id = self.database_info["server_id"]
     self.db_id = self.database_info['db_id']
     db_con = database_utils.connect_database(self,
                                              utils.SERVER_GROUP,
                                              self.server_id,
                                              self.db_id)
     if not db_con["info"] == "Database connected.":
         raise Exception("Could not connect to database.")
     connection = utils.get_db_connection(self.server['db'],
                                          self.server['username'],
                                          self.server['db_password'],
                                          self.server['host'],
                                          self.server['port'])
     response = cast_utils.verify_cast(connection, self.source_type,
                                       self.target_type)
     if len(response) == 0:
         raise Exception("Could not find cast.")
     data = {
         "description": "This is cast update comment",
         "id": self.cast_id
     }
     put_response = self.tester.put(
         self.url + str(utils.SERVER_GROUP) + '/' +
         str(self.server_id) + '/' + str(
             self.db_id) +
         '/' + str(self.cast_id),
         data=json.dumps(data),
         follow_redirects=True)
     self.assertEquals(put_response.status_code, 200)
Exemplo n.º 28
0
def verify_check_constraint(server, db_name, check_constraint_name):
    """
    This function verifies check constraint constraint exist or not.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param check_constraint_name: constraint name
    :type check_constraint_name: str
    :return chk_constraint_record: check constraint record from database
    :rtype: tuple
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(
            "SELECT oid FROM pg_constraint where conname='%s'" %
            check_constraint_name)
        chk_constraint_record = pg_cursor.fetchone()
        connection.close()
        return chk_constraint_record
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 29
0
def create_function(server, db_name, schema_name, func_name):
    """This function add the procedure to schema"""
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        query = "CREATE FUNCTION " + schema_name + "." + func_name + \
                "()" \
                " RETURNS integer LANGUAGE 'sql' STABLE" \
                " SECURITY DEFINER AS $$" \
                " SELECT 1; $$;"
        pg_cursor.execute(query)
        connection.commit()
        # Get 'oid' from newly created function
        pg_cursor.execute("SELECT pro.oid, pro.proname FROM"
                          " pg_proc pro WHERE pro.proname='%s'" %
                          func_name)
        functions = pg_cursor.fetchone()
        connection.close()
        return functions
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 30
0
def create_synonym(server, db_name, schema_name, synonym_name, sequence_name):
    """
    This function create the synonym on given schema node.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param synonym_name: synonym name
    :type synonym_name: str
    :param sequence_name: sequence name
    :type sequence_name: str
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'])
        pg_cursor = connection.cursor()
        query = "CREATE OR REPLACE SYNONYM %s.%s FOR %s.%s" % (
            schema_name, synonym_name, schema_name, sequence_name)
        pg_cursor.execute(query)
        connection.commit()
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 31
0
def create_index(server, db_name, schema_name, table_name, index_name,
                 col_name):
    """
    This function will add the new index to existing column.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param table_name: table name
    :type table_name: str
    :param index_name: index name
    :type index_name: str
    :param col_name: column name
    :type col_name: str
    :return table_id: table id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = "CREATE INDEX %s ON %s.%s USING btree (%s ASC NULLS LAST) " \
                "TABLESPACE pg_default" % (index_name, schema_name,
                                           table_name, col_name)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get oid of newly added index
        pg_cursor.execute("select oid from pg_catalog.pg_class "
                          "where relname='%s'" % index_name)
        index_record = pg_cursor.fetchone()
        index_oid = ''
        if index_record:
            index_oid = index_record[0]
        connection.close()
        return index_oid
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 32
0
def create_identity_column(server, db_name, schema_name, table_name,
                           col_name, col_data_type='bigint'):
    """
    This function creates a column under provided table.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param table_name: table name
    :type table_name: str
    :param col_name: column name
    :type col_name: str
    :param col_data_type: column data type
    :type col_data_type: str
    :return table_id: table id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = "ALTER TABLE %s.%s ADD COLUMN %s %s " \
                "GENERATED ALWAYS AS IDENTITY" % \
                (schema_name, table_name, col_name, col_data_type)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get column position of newly added column
        pg_cursor.execute("select attnum from pg_attribute where"
                          " attname='%s'" % col_name)
        col = pg_cursor.fetchone()
        col_pos = ''
        if col:
            col_pos = col[0]
        connection.close()
        return col_pos
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 33
0
def create_procedure(server,
                     db_name,
                     schema_name,
                     func_name,
                     s_type,
                     s_version,
                     with_args=False,
                     args=""):
    """This function add the procedure to schema"""
    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        if s_type == 'pg':
            query = "CREATE PROCEDURE {0}.{1}" \
                    "({2})" \
                    " LANGUAGE 'sql'" \
                    " SECURITY DEFINER AS $$" \
                    " SELECT 1; $$;".format(schema_name, func_name, args)
        else:
            if s_version >= 90500:
                query = "CREATE PROCEDURE {0}.{1}" \
                        "({2})" \
                        " SECURITY DEFINER AS $BODY$ BEGIN" \
                        " NULL; END; $BODY$".format(schema_name, func_name,
                                                    args)
            else:
                query = "CREATE PROCEDURE {0}.{1}" \
                        "({2})" \
                        " AS $BODY$ BEGIN" \
                        " NULL; END; $BODY$".format(schema_name, func_name,
                                                    args)

        pg_cursor.execute(query)
        connection.commit()
        # Get 'oid' from newly created function
        pg_cursor.execute("SELECT pro.oid, pro.proname FROM"
                          " pg_catalog.pg_proc pro WHERE pro.proname='%s'" %
                          func_name)
        functions = pg_cursor.fetchone()
        connection.close()
        return functions
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 34
0
def verify_schemas(server, db_name, schema_name):
    """This function verifies the schema is exists"""
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("SELECT oid,* FROM pg_catalog.pg_namespace sch"
                          " WHERE sch.nspname='%s'" % schema_name)
        schema = pg_cursor.fetchone()
        connection.close()
        return schema
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 35
0
def create_foreign_table(server,
                         db_name,
                         schema_name,
                         fsrv_name,
                         foreign_table_name,
                         sql_query=None):
    """
    This function will create foreign table under the existing
    dummy schema.

    :param server: test_server, test_db, fsrv_name, foreign_table_name
    :return: ft_id
    """

    try:
        query = "CREATE FOREIGN TABLE " + schema_name + "." + \
                foreign_table_name + "(emp_name text NULL) SERVER %s" % \
                fsrv_name

        if sql_query is not None:
            query = eval(sql_query)
        connection = get_db_connection(db_name, server['username'],
                                       server['db_password'], server['host'],
                                       server['port'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()

        # Get 'oid' from newly created foreign table
        pg_cursor.execute(
            "SELECT ftrelid FROM pg_catalog.pg_foreign_table WHERE ftserver = "
            "(SELECT oid FROM pg_catalog.pg_foreign_server "
            "WHERE srvname = '%s') "
            "ORDER BY ftrelid ASC limit 1" % fsrv_name)

        oid = pg_cursor.fetchone()
        ft_id = ''
        if oid:
            ft_id = oid[0]
        connection.close()
        return ft_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 36
0
def verify_collation(server, db_name, coll_name):
    """This function verifies the collation is exist or not"""
    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        # Get 'oid' from newly created database
        pg_cursor.execute("SELECT coll.oid, coll.collname FROM"
                          " pg_collation coll WHERE coll.collname='%s'" %
                          coll_name)
        collation = pg_cursor.fetchone()
        connection.close()
        return collation
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 37
0
    def setUp(self):
        super(CollationAddTestCase, self).setUp()
        self.database_info = parent_node_dict["database"][-1]
        self.db_name = self.database_info["db_name"]
        self.schema_info = parent_node_dict["schema"][-1]
        self.schema_name = self.schema_info["schema_name"]
        self.schema_id = self.schema_info["schema_id"]
        self.server_id = self.schema_info["server_id"]
        self.db_id = self.schema_info["db_id"]

        # Change the db name, so that schema will create in newly created db
        self.schema_name = "schema_get_%s" % str(uuid.uuid4())[1:8]
        connection = utils.get_db_connection(
            self.db_name, self.server['username'], self.server['db_password'],
            self.server['host'], self.server['port'], self.server['sslmode'])
        self.schema_details = schema_utils.create_schema(
            connection, self.schema_name)
    def before(self):
        if self.server['default_binary_paths'] is None:
            self.skipTest(
                "default_binary_paths is not set for the server {0}".format(
                    self.server['name']))
        connection = test_utils.get_db_connection(self.server['db'],
                                                  self.server['username'],
                                                  self.server['db_password'],
                                                  self.server['host'],
                                                  self.server['port'],
                                                  self.server['sslmode'])
        test_utils.drop_database(connection, "pg_utility_test_db")

        test_utils.create_database(self.server, "pg_utility_test_db")
        self.page.add_server(self.server)

        self.wait = WebDriverWait(self.page.driver, 20)
Exemplo n.º 39
0
def create_table(server, db_name, schema_name, table_name, custom_query=None):
    """
    This function creates a table under provided schema.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param table_name: table name
    :type table_name: str
    :return table_id: table id
    :rtype: int
    """
    if custom_query is None:
        query = "CREATE TABLE %s.%s(id serial UNIQUE NOT NULL, name text," \
                " location text)" % \
                (schema_name, table_name)
    else:
        query = eval(custom_query)
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get 'oid' from newly created table
        pg_cursor.execute("select oid from pg_catalog.pg_class "
                          "where relname='%s'" % table_name)
        table = pg_cursor.fetchone()
        table_id = ''
        if table:
            table_id = table[0]
        connection.close()
        return table_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 40
0
def restore_schema(server, db_name, schema_name, sql_path):
    """
    This function is used to restore the schema.
    :param server:
    :param db_name:
    :param schema_name:
    :param sql_path:
    :return:
    """
    schema_id = None
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode']
                                             )

        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()

        with open(sql_path, 'r') as content_file:
            sql = content_file.read()
        pg_cursor.execute(sql)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()

        SQL = """SELECT
        nsp.oid
    FROM
        pg_namespace nsp
        WHERE nsp.nspname = '{0}'""".format(schema_name)

        pg_cursor.execute(SQL)
        schema = pg_cursor.fetchone()
        if schema:
            schema_id = schema[0]
        connection.close()
    except Exception as e:
        print(str(e))
        return False, schema_id

    return True, schema_id
Exemplo n.º 41
0
def create_check_constraint(server, db_name, schema_name, table_name,
                            check_constraint_name):
    """
    This function creates a check constraint under provided table.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param table_name: table name
    :type table_name: str
    :param check_constraint_name: constraint name
    :type check_constraint_name: str
    :return chk_constraint_id: check constraint id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = "ALTER TABLE %s.%s ADD CONSTRAINT %s CHECK ( (id > 0)) " \
                "NOT VALID; COMMENT ON CONSTRAINT %s ON %s.%s IS " \
                "'this is test comment'" % (schema_name, table_name,
                                            check_constraint_name,
                                            check_constraint_name,
                                            schema_name, table_name)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get oid of newly added check constraint
        pg_cursor.execute(
            "SELECT oid FROM pg_constraint where conname='%s'" %
            check_constraint_name)
        chk_constraint_record = pg_cursor.fetchone()
        connection.close()
        chk_constraint_id = chk_constraint_record[0]
        return chk_constraint_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 42
0
def create_trigger(server, db_name, schema_name, table_name, trigger_name,
                   trigger_func_name):
    """
    This function creates a column under provided table.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param table_name: table name
    :type table_name: str
    :param trigger_name: trigger name
    :type trigger_name: str
    :param trigger_func_name: trigger function name
    :type trigger_func_name: str
    :return trigger_id: trigger id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = "CREATE TRIGGER %s BEFORE INSERT ON %s.%s FOR EACH ROW " \
                "EXECUTE PROCEDURE %s.%s()" % (trigger_name, schema_name,
                                               table_name, schema_name,
                                               trigger_func_name)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        pg_cursor.execute("SELECT oid FROM pg_trigger where tgname='%s'" %
                          trigger_name)
        trigger = pg_cursor.fetchone()
        trigger_id = ''
        if trigger:
            trigger_id = trigger[0]
        connection.close()
        return trigger_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 43
0
def create_foreignkey(server, db_name, schema_name, local_table_name,
                      foreign_table_name):
    """
    This function creates a column under provided table.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param local_table_name: local table name
    :type local_table_name: str
    :param foreign_table_name: foreign table name
    :type foreign_table_name: str
    :return table_id: table id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = "ALTER TABLE %s.%s ADD FOREIGN KEY (id) REFERENCES %s.%s " \
                "(id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION" % \
                (
                    schema_name, local_table_name, schema_name,
                    foreign_table_name)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get oid of newly added foreign key
        pg_cursor.execute(
            "SELECT oid FROM pg_constraint where conname='%s_id_fkey'" %
            local_table_name)
        fk_record = pg_cursor.fetchone()
        connection.close()
        fk_id = fk_record[0]
        return fk_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 44
0
def create_sequences(server,
                     db_name,
                     schema_name,
                     sequence_name,
                     positive_seq=True):
    """
    This function used to create sequence in schema provided.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param sequence_name: sequence name
    :type sequence_name: str
    :param positive_seq: True is sequence will be created using positive values
    :type positive_seq: boolean
    :return sequence_id: sequence id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()

        query = "CREATE SEQUENCE %s.%s INCREMENT 5 START 30 " \
                "MINVALUE 10 MAXVALUE 100" % (schema_name, sequence_name)
        if not positive_seq:
            query = "CREATE SEQUENCE %s.%s INCREMENT -5 START -30 " \
                    "MINVALUE -40 MAXVALUE -10" % (schema_name, sequence_name)
        pg_cursor.execute(query)
        connection.commit()
        # Get 'oid' from newly created sequence
        pg_cursor.execute("select oid from pg_catalog.pg_class "
                          "where relname='%s'" % sequence_name)
        sequence = pg_cursor.fetchone()
        sequence_id = ''
        if sequence:
            sequence_id = sequence[0]
        connection.close()
        return sequence_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 45
0
 def after(self):
     try:
         test_utils.delete_table(self.server, self.database_name,
                                 self.table_name)
         self.page.remove_server(self.server)
     except Exception:
         print("PGUtilitiesMaintenanceFeatureTest - "
               "Exception occurred in after method")
     finally:
         connection = test_utils.get_db_connection(
             self.server['db'],
             self.server['username'],
             self.server['db_password'],
             self.server['host'],
             self.server['port'],
             self.server['sslmode']
         )
         test_utils.drop_database(connection, self.database_name)
Exemplo n.º 46
0
def verify_foreign_table(server, db_name, fsrv_name):
    """ This function will verify current foreign table."""

    try:
        connection = get_db_connection(db_name, server['username'],
                                       server['db_password'], server['host'],
                                       server['port'])
        pg_cursor = connection.cursor()

        pg_cursor.execute(
            "SELECT ftrelid FROM pg_foreign_table WHERE ftserver = "
            "(SELECT oid FROM pg_foreign_server WHERE srvname = '%s') "
            "ORDER BY ftrelid ASC limit 1" % fsrv_name)
        fts = pg_cursor.fetchone()
        connection.close()
        return fts
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 47
0
def delete_fdw(server, db_name, fdw_name):
    """
    This function delete FDW.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param fdw_name: fdw name to be deleted
    :type fdw_name: str
    :return: None
    """
    connection = get_db_connection(db_name, server['username'],
                                   server['db_password'], server['host'],
                                   server['port'], server['sslmode'])
    pg_cursor = connection.cursor()
    pg_cursor.execute("DROP FOREIGN DATA WRAPPER %s" % fdw_name)
    connection.commit()
    connection.close()
Exemplo n.º 48
0
def create_subscription(server, db_name, subscription_name):
    """
    This function creates a subscription.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param subscription_name: subscription name
    :type subscription_name: str
    :return subscription_id: subscription id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = """CREATE SUBSCRIPTION "%s" """ \
                """CONNECTION 'host=192.168.1.50 port=5432 user=foo """ \
                """dbname=foodb' """ \
                """PUBLICATION insert_only WITH (create_slot = false, """ \
                """enabled = false, slot_name=NONE, connect=false);""" % (
                    subscription_name)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get role oid of newly added subscription
        pg_cursor.execute("select oid from pg_subscription sub where "
                          "sub.subname='%s'" %
                          subscription_name)
        subscription = pg_cursor.fetchone()
        subscription_id = ''
        if subscription:
            subscription_id = subscription[0]
        connection.close()
        return subscription_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 49
0
def create_user_mapping(server, db_name, fsrv_name):
    """
    This function will create user mapping under the existing
    dummy database.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param fsrv_name: FS name
    :type fsrv_name: str
    :return um_id: user mapping id
    :rtype: int
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = "CREATE USER MAPPING FOR %s SERVER %s OPTIONS" \
                " (user '%s', password '%s')" % (server['username'],
                                                 fsrv_name,
                                                 server['username'],
                                                 server['db_password']
                                                 )
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get 'oid' from newly created user mapping
        pg_cursor.execute(
            "select umid from pg_catalog.pg_user_mappings where "
            "srvname = '%s' order by umid asc limit 1" % fsrv_name)
        oid = pg_cursor.fetchone()
        um_id = ''
        if oid:
            um_id = oid[0]
        connection.close()
        return um_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 50
0
def create_event_trigger(server, db_name, schema_name, func_name,
                         trigger_name):
    """
    This function creates the event trigger into test database.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param func_name: function name
    :type func_name: str
    :param trigger_name: trigger name
    :type trigger_name: str
    :return trigger_id: trigger id
    :rtype: int
    """
    try:
        connection = get_db_connection(db_name,
                                       server['username'],
                                       server['db_password'],
                                       server['host'],
                                       server['port'],
                                       server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        pg_cursor.execute('''CREATE EVENT TRIGGER "%s" ON DDL_COMMAND_END
         EXECUTE PROCEDURE "%s"."%s"()''' % (trigger_name, schema_name,
                                             func_name))
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get 'oid' from newly created event trigger
        pg_cursor.execute(
            "SELECT oid FROM pg_event_trigger WHERE evtname = '%s'"
            % trigger_name)
        oid = pg_cursor.fetchone()
        trigger_id = ''
        if oid:
            trigger_id = oid[0]
        connection.close()
        return trigger_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 51
0
def create_domain(server,
                  db_name,
                  schema_name,
                  schema_id,
                  domain_name,
                  domain_sql=None):
    """
    This function is used to add the domain to existing schema
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param schema_id: schema id
    :type schema_id: int
    :param domain_name: domain name
    :type domain_name: str
    :return: None
    """
    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'])
        pg_cursor = connection.cursor()

        if domain_sql is None:
            query = 'CREATE DOMAIN ' + schema_name + '.' + domain_name + \
                    ' AS numeric(500,4) DEFAULT 1000'
        else:
            query = 'CREATE DOMAIN ' + schema_name + '.' +\
                    domain_name + ' ' + domain_sql

        pg_cursor.execute(query)
        connection.commit()
        # Get 'oid' from newly created domain
        pg_cursor.execute("SELECT d.oid, d.typname FROM pg_type d WHERE"
                          " d.typname='%s' AND d.typnamespace='%s'" %
                          (domain_name, schema_id))
        domains = pg_cursor.fetchone()
        connection.close()
        return domains
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 52
0
    def before(self):
        with test_utils.Database(self.server) as (connection, _):
            if connection.server_version < 90100:
                self.skipTest(
                    "COLLATE is not present in PG versions below v9.1")

        connection = test_utils.get_db_connection(self.server['db'],
                                                  self.server['username'],
                                                  self.server['db_password'],
                                                  self.server['host'],
                                                  self.server['port'])
        test_utils.drop_database(connection, "acceptance_test_db")
        test_utils.create_database(self.server, "acceptance_test_db")

        # Create pre-requisite table
        test_utils.create_table_with_query(self.server, "acceptance_test_db",
                                           CheckForViewDataTest.defaults_query)
        # Initialize an instance of WebDriverWait with timeout of 3 seconds
        self.wait = WebDriverWait(self.driver, 3)
Exemplo n.º 53
0
    def before(self):
        if self.server['default_binary_paths'] is None:
            self.skipTest(
                "default_binary_paths is not set for the server {0}".format(
                    self.server['name']))

        connection = test_utils.get_db_connection(self.server['db'],
                                                  self.server['username'],
                                                  self.server['db_password'],
                                                  self.server['host'],
                                                  self.server['port'],
                                                  self.server['sslmode'])
        test_utils.drop_database(connection, self.database_name)
        test_utils.create_database(self.server, self.database_name)
        test_utils.create_table(self.server, self.database_name,
                                self.table_name)
        self.page.add_server(self.server)
        self.wait = WebDriverWait(self.page.driver, 20)
        test_gui_helper.close_bgprocess_popup(self)
Exemplo n.º 54
0
def verify_fsrv(server, db_name, fsrv_name):
    """ This function will verify current foreign server."""

    try:
        connection = get_db_connection(db_name, server['username'],
                                       server['db_password'], server['host'],
                                       server['port'], server['sslmode'])
        pg_cursor = connection.cursor()

        pg_cursor.execute(
            "SELECT oid FROM pg_foreign_server WHERE srvname = '%s'" %
            fsrv_name)
        fsrvs = pg_cursor.fetchall()
        connection.close()
        return fsrvs
    except Exception as exception:
        exception = "%s: line:%s %s" % (file_name, sys.exc_traceback.tb_lineno,
                                        exception)
        print(exception, file=sys.stderr)
Exemplo n.º 55
0
def get_table_id(server, db_name, table_name):
    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'],
                                             server['sslmode'])
        pg_cursor = connection.cursor()
        pg_cursor.execute("select oid from pg_class where relname='%s'" %
                          table_name)
        table = pg_cursor.fetchone()
        if table:
            table_id = table[0]
        else:
            table_id = None
        connection.close()
        return table_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 56
0
def create_domain_from_sql(server, db_name, sql):
    """
    This function create domain from the reverse engineered sql
    :param server:
    :param db_name:
    :param sql:
    :return:
    """

    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'])
        pg_cursor = connection.cursor()
        pg_cursor.execute(sql)
        connection.commit()
        connection.close()
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 57
0
def delete_pgagent_exception(self, date, time):
    """
    This function deletes the pgAgent exception.
    """
    try:
        connection = utils.get_db_connection(self.server['db'],
                                             self.server['username'],
                                             self.server['db_password'],
                                             self.server['host'],
                                             self.server['port'],
                                             self.server['sslmode'])
        pg_cursor = connection.cursor()
        query = "DELETE FROM pgagent.pga_exception " \
                "WHERE jexdate = to_date('{0}', 'YYYY-MM-DD') AND " \
                "jextime = '{1}'::time without time zone;".format(date, time)
        pg_cursor.execute(query)
        connection.close()
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 58
0
def create_view(server,
                db_name,
                schema_name,
                view_name,
                sql_query=None,
                mview_index=None):
    """
    This function creates a table under provided schema.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param sql_query: sql query to create view
    :type sql_query: str
    :param view_name: view name
    :type view_name: str
    :return view_id: view id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name, server['username'],
                                             server['db_password'],
                                             server['host'], server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = eval(sql_query)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get 'oid' from newly created view
        pg_cursor.execute("select oid from pg_class where relname='%s'" %
                          view_name)
        view = pg_cursor.fetchone()
        view_id = view[0]
        connection.close()
        return view_id
    except Exception:
        traceback.print_exc(file=sys.stderr)
        raise
Exemplo n.º 59
0
def create_index_constraint(server, db_name, schema_name, table_name,
                            key_name, key_type):
    """
    This function creates a index constraint(PK or UK) under provided table.
    :param server: server details
    :type server: dict
    :param db_name: database name
    :type db_name: str
    :param schema_name: schema name
    :type schema_name: str
    :param table_name: table name
    :type table_name: str
    :param key_name: test name for primary or unique key
    :type key_name: str
    :param key_type: key type i.e. primary or unique key
    :type key_type: str
    :return oid: key constraint id
    :rtype: int
    """
    try:
        connection = utils.get_db_connection(db_name,
                                             server['username'],
                                             server['db_password'],
                                             server['host'],
                                             server['port'],
                                             server['sslmode'])
        old_isolation_level = connection.isolation_level
        connection.set_isolation_level(0)
        pg_cursor = connection.cursor()
        query = "ALTER TABLE %s.%s ADD CONSTRAINT %s %s (id)" % \
                (schema_name, table_name, key_name, key_type)
        pg_cursor.execute(query)
        connection.set_isolation_level(old_isolation_level)
        connection.commit()
        # Get oid of newly added index constraint
        pg_cursor.execute("SELECT conindid FROM pg_catalog.pg_constraint "
                          "where conname='%s'" % key_name)
        index_constraint = pg_cursor.fetchone()
        connection.close()
        oid = index_constraint[0]
        return oid
    except Exception:
        traceback.print_exc(file=sys.stderr)
Exemplo n.º 60
0
    def _supported_jit_on_server(self):
        connection = test_utils.get_db_connection(self.server['db'],
                                                  self.server['username'],
                                                  self.server['db_password'],
                                                  self.server['host'],
                                                  self.server['port'],
                                                  self.server['sslmode'])

        pg_cursor = connection.cursor()
        pg_cursor.execute('select version()')
        version_string = pg_cursor.fetchone()

        is_edb = False
        if len(version_string) > 0:
            is_edb = 'EnterpriseDB' in version_string[0]

        connection.close()

        return connection.server_version >= 110000 and not is_edb