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)

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)

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)

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)

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)

 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)

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)

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)

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)

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)

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)

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)

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

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

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)

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)

    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)

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

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)

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)