def __set_updatable_results_attrs(self, sql_path, table_oid, conn):
# Set template path for sql scripts and the table object id
self.sql_path = sql_path
self.obj_id = table_oid
if conn.connected():
# Fetch the Namespace Name and object Name
query = render_template("/".join([self.sql_path,
'objectname.sql']),
obj_id=self.obj_id)
status, result = conn.execute_dict(query)
if not status:
raise ExecuteError(result)
self.nsp_name = result['rows'][0]['nspname']
self.object_name = result['rows'][0]['relname']
else:
raise InternalServerError(SERVER_CONNECTION_CLOSED)
def get_all_columns_with_order(self, default_conn):
"""
Responsible for fetching columns from given object
Args:
default_conn: Connection object
Returns:
all_sorted_columns: Columns which are already sorted which will
be used to populate the Grid in the dialog
all_columns: List of all the column for given object which will
be used to fill columns options
"""
driver = get_driver(PG_DEFAULT_DRIVER)
if default_conn is None:
manager = driver.connection_manager(self.sid)
conn = manager.connection(did=self.did, conn_id=self.conn_id)
else:
conn = default_conn
all_sorted_columns = []
data_sorting = self.get_data_sorting()
all_columns = []
if conn.connected():
# Fetch the rest of the column names
query = render_template(
"/".join([self.sql_path, 'get_columns.sql']),
obj_id=self.obj_id
)
status, result = conn.execute_dict(query)
if not status:
raise ExecuteError(result)
for row in result['rows']:
all_columns.append(row['attname'])
else:
raise InternalServerError(SERVER_CONNECTION_CLOSED)
# If user has custom data sorting then pass as it as it is
if data_sorting and len(data_sorting) > 0:
all_sorted_columns = data_sorting
return all_sorted_columns, all_columns
def get_sql(conn, data, did, tid, exid=None, template_path=None):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param data: data
:param did: Database ID
:param tid: Table id
:param exid: Exclusion Constraint ID
:param template_path: Template Path
:return:
"""
name = data['name'] if 'name' in data else None
if exid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
did=did,
tid=tid,
cid=exid)
status, res = conn.execute_dict(sql)
if not status:
raise ExecuteError(res)
if len(res['rows']) == 0:
raise ObjectGone(
_('Could not find the exclusion constraint in the table.'))
old_data = res['rows'][0]
if 'name' not in data:
name = data['name'] = old_data['name']
sql = render_template("/".join([template_path, 'update.sql']),
data=data,
o_data=old_data)
else:
if 'columns' not in data or \
(isinstance(data['columns'], list) and
len(data['columns']) < 1):
return _('-- definition incomplete'), name
sql = render_template("/".join([template_path, 'create.sql']),
data=data,
conn=conn)
return sql, name
def get_columns_types(is_query_tool, columns_info, table_oid, conn, has_oids):
nodes_sqlpath = 'columns/sql/#{0}#'.format(conn.manager.version)
query = render_template(
"/".join([nodes_sqlpath, 'nodes.sql']),
tid=table_oid,
has_oids=has_oids
)
colst, rset = conn.execute_2darray(query)
if not colst:
raise ExecuteError(rset)
column_types = dict()
for key, col in enumerate(columns_info):
col_type = dict()
col_type['type_code'] = col['type_code']
col_type['type_name'] = None
col_type['internal_size'] = col['internal_size']
column_types[col['name']] = col_type
if not is_query_tool:
col_type['not_null'] = col['not_null'] = \
rset['rows'][key]['not_null']
col_type['has_default_val'] = \
col['has_default_val'] = \
rset['rows'][key]['has_default_val']
else:
for row in rset['rows']:
if row['oid'] == col['table_column']:
col_type['not_null'] = col['not_null'] = row['not_null']
col_type['has_default_val'] = \
col['has_default_val'] = row['has_default_val']
break
else:
col_type['not_null'] = col['not_null'] = None
col_type['has_default_val'] = col['has_default_val'] = None
return column_types
def _get_primary_keys(sql_path, table_oid, conn):
query = render_template("/".join([sql_path, 'primary_keys.sql']),
obj_id=table_oid)
status, result = conn.execute_dict(query)
if not status:
raise ExecuteError(result)
primary_keys_columns = []
primary_keys = OrderedDict()
pk_names = []
for row in result['rows']:
primary_keys[row['attname']] = row['typname']
primary_keys_columns.append({
'name': row['attname'],
'column_number': row['attnum']
})
pk_names.append(row['attname'])
return primary_keys, primary_keys_columns, pk_names
def get_parent(conn, tid, template_path=None):
"""
This function will return the parent of the given table.
:param conn: Connection Object
:param tid: Table oid
:param template_path:
:return:
"""
SQL = render_template("/".join([template_path, 'get_parent.sql']), tid=tid)
status, rset = conn.execute_2darray(SQL)
if not status:
raise ExecuteError(rset)
schema = ''
table = ''
if 'rows' in rset and len(rset['rows']) > 0:
schema = rset['rows'][0]['schema']
table = rset['rows'][0]['table']
return schema, table
def _check_oids(conn, sql_path, table_oid, columns_info):
# Remove the special behavior of OID columns from
# PostgreSQL 12 onwards, so returning False.
if conn.manager.sversion >= 120000:
return False
# Check that the table has oids
query = render_template("/".join([sql_path, 'has_oids.sql']),
obj_id=table_oid)
status, has_oids = conn.execute_scalar(query)
if not status:
raise ExecuteError(has_oids)
# Check that the oid column is selected in results columns
oid_column_selected = False
for col in columns_info:
if col['table_column'] is None and col['display_name'] == 'oid':
oid_column_selected = True
break
return has_oids and oid_column_selected
def get_primary_keys(self, default_conn=None):
"""
This function is used to fetch the primary key columns.
"""
driver = get_driver(PG_DEFAULT_DRIVER)
if default_conn is None:
manager = driver.connection_manager(self.sid)
conn = manager.connection(did=self.did, conn_id=self.conn_id)
else:
conn = default_conn
pk_names = ''
primary_keys = OrderedDict()
if conn.connected():
# Fetch the primary key column names
query = render_template(
"/".join([self.sql_path, 'primary_keys.sql']),
obj_id=self.obj_id
)
status, result = conn.execute_dict(query)
if not status:
raise ExecuteError(result)
for row in result['rows']:
pk_names += driver.qtIdent(conn, row['attname']) + ','
primary_keys[row['attname']] = row['typname']
if pk_names != '':
# Remove last character from the string
pk_names = pk_names[:-1]
else:
raise InternalServerError(
gettext('Not connected to server or connection with the '
'server has been closed.')
)
return pk_names, primary_keys
def get_reverse_engineered_sql(conn, **kwargs):
"""
This function will return reverse engineered sql for specified trigger.
:param conn: Connection Object
:param kwargs:
:return:
"""
schema = kwargs.get('schema')
table = kwargs.get('table')
tid = kwargs.get('tid')
trid = kwargs.get('trid')
datlastsysoid = kwargs.get('datlastsysoid')
show_system_objects = kwargs.get('show_system_objects')
template_path = kwargs.get('template_path', None)
with_header = kwargs.get('with_header', True)
SQL = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(SQL)
if not status:
raise ExecuteError(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the trigger in the table.'))
data = dict(res['rows'][0])
# Adding parent into data dict, will be using it while creating sql
data['schema'] = schema
data['table'] = table
data = \
get_trigger_function_and_columns(conn, data, tid, show_system_objects)
data = trigger_definition(data)
SQL, name = get_sql(conn,
data=data,
tid=tid,
trid=None,
datlastsysoid=datlastsysoid,
show_system_objects=show_system_objects)
if with_header:
sql_header = "-- Trigger: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([template_path, 'delete.sql']),
data=data,
conn=conn)
SQL = sql_header + '\n\n' + SQL.strip('\n')
else:
SQL = SQL.strip('\n')
# If trigger is disabled then add sql code for the same
if data['is_enable_trigger'] != 'O':
SQL += '\n\n'
SQL += render_template("/".join(
[template_path, 'enable_disable_trigger.sql']),
data=data,
conn=conn)
return SQL
def get_sql(conn, **kwargs):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param kwargs
:return:
"""
data = kwargs.get('data')
tid = kwargs.get('tid')
trid = kwargs.get('trid')
datlastsysoid = kwargs.get('datlastsysoid')
show_system_objects = kwargs.get('show_system_objects')
is_schema_diff = kwargs.get('is_schema_diff', False)
template_path = kwargs.get('template_path', None)
name = data['name'] if 'name' in data else None
if trid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(sql)
if not status:
raise ExecuteError(res)
elif len(res['rows']) == 0:
raise ObjectGone(_('Could not find the trigger in the table.'))
old_data = dict(res['rows'][0])
# If name is not present in data then
# we will fetch it from old data, we also need schema & table name
if 'name' not in data:
name = data['name'] = old_data['name']
drop_sql = _check_schema_diff_sql(is_schema_diff, data, old_data,
template_path, conn)
old_data = get_trigger_function_and_columns(conn, old_data, tid,
show_system_objects)
old_data = trigger_definition(old_data)
sql = render_template("/".join([template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=conn)
if is_schema_diff:
sql = drop_sql + '\n' + sql
else:
required_args = {'name': 'Name', 'tfunction': 'Trigger function'}
for arg in required_args:
if arg not in data:
return _('-- definition incomplete')
# If the request for new object which do not have did
sql = render_template("/".join([template_path, 'create.sql']),
data=data,
conn=conn)
return sql, name
def get_reverse_engineered_sql(conn, **kwargs):
"""
This function will return reverse engineered sql for specified trigger.
:param conn: Connection Object
:param kwargs:
:return:
"""
schema = kwargs.get('schema')
table = kwargs.get('table')
did = kwargs.get('did')
tid = kwargs.get('tid')
idx = kwargs.get('idx')
datlastsysoid = kwargs.get('datlastsysoid')
template_path = kwargs.get('template_path', None)
with_header = kwargs.get('with_header', True)
if_exists_flag = kwargs.get('add_not_exists_clause', False)
SQL = render_template("/".join([template_path, 'properties.sql']),
did=did,
tid=tid,
idx=idx,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(SQL)
if not status:
raise ExecuteError(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the index in the table.'))
data = dict(res['rows'][0])
# Adding parent into data dict, will be using it while creating sql
data['schema'] = schema
data['table'] = table
# Add column details for current index
data = get_column_details(conn, idx, data, 'create')
# Add Include details of the index
if conn.manager.version >= 110000:
data = get_include_details(conn, idx, data)
SQL, name = get_sql(conn,
data=data,
did=did,
tid=tid,
idx=None,
datlastsysoid=datlastsysoid,
if_exists_flag=if_exists_flag)
if with_header:
sql_header = "-- Index: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([template_path, 'delete.sql']),
data=data,
conn=conn)
SQL = sql_header + '\n\n' + SQL
return SQL
