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