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