def _get_properties_for_fk_const(tid, fkid, data, template_path, conn):
"""
Get property data for fk constraint.
tid: table Id
fkid: Foreign key constraint ID.
data: Data.
template_path: template path for get sql.
conn: Connection.
"""
name = data['name'] if 'name' in data else None
sql = render_template("/".join([template_path, FKEY_PROPERTIES_SQL]),
tid=tid, cid=fkid)
status, res = conn.execute_dict(sql)
if not status:
raise ExecuteError(res)
if len(res['rows']) == 0:
raise ObjectGone(
_('Could not find the foreign key constraint in the table.'))
old_data = res['rows'][0]
if 'name' not in data:
name = data['name'] = old_data['name']
return old_data, name
def get_reverse_engineered_sql(conn,
schema,
table,
tid,
trid,
datlastsysoid,
template_path=None):
"""
This function will return reverse engineered sql for trigger(s).
:param conn: Connection Object
:param schema: Schema
:param table: Table
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param template_path: Optional template path
:return:
"""
SQL = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(SQL)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(
_('Could not find the compound 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
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = get_column_details(conn, tid, columns)
data = trigger_definition(data)
SQL, name = get_sql(conn, data, tid, None, datlastsysoid)
sql_header = u"-- Compound 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')
# 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_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, data, did, scid, tid, plid, datlastsysoid, schema, table,
template_path=None):
"""
This function will generate sql from model data
"""
if plid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
schema=schema, plid=plid, scid=scid)
status, res = conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the policy in the table.'))
old_data = dict(res['rows'][0])
old_data['schema'] = schema
old_data['table'] = table
sql = render_template(
"/".join([template_path, 'update.sql']),
data=data, o_data=old_data
)
else:
data['schema'] = schema
data['table'] = table
sql = render_template("/".join(
[template_path, 'create.sql']), data=data)
return sql, data['name'] if 'name' in data else old_data['name']
def get_reverse_engineered_sql(conn,
schema,
table,
did,
tid,
idx,
datlastsysoid,
template_path=None,
with_header=True):
"""
This function will return reverse engineered sql for specified trigger.
:param conn: Connection Object
:param schema: Schema
:param table: Table
:param tid: Table ID
:param idx: Index ID
:param datlastsysoid:
:param template_path: Optional template path
:param with_header: Optional parameter to decide whether the SQL will be
returned with header or not
:return:
"""
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 Exception(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, did, tid, None, datlastsysoid)
if with_header:
sql_header = u"-- 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
def get_sql(conn,
data,
tid,
trid,
datlastsysoid,
show_system_objects,
template_path=None):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param data: Data
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param show_system_objects: Show System Object value True or False
:param template_path: Optional template path
:return:
"""
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 Exception(res)
if 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']
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)
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)
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 with_header:
sql_header = u"-- 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
def get_sql(conn, **kwargs):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param kwargs:
:return:
"""
data = kwargs.get('data')
did = kwargs.get('did')
tid = kwargs.get('tid')
idx = kwargs.get('idx')
datlastsysoid = kwargs.get('datlastsysoid')
mode = kwargs.get('mode', None)
template_path = kwargs.get('template_path', None)
if_exists_flag = kwargs.get('if_exists_flag', False)
name = data['name'] if 'name' in data else None
if idx is not None:
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:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the index in the table.'))
old_data = dict(res['rows'][0])
# Remove opening and closing bracket as we already have in jinja
# template.
if 'using' in old_data and old_data['using'] is not None and \
old_data['using'].startswith('(') and \
old_data['using'].endswith(')'):
old_data['using'] = old_data['using'][1:-1]
if 'withcheck' in old_data and old_data['withcheck'] is not None and \
old_data['withcheck'].startswith('(') and \
old_data['withcheck'].endswith(')'):
old_data['withcheck'] = old_data['withcheck'][1:-1]
# 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']
sql = render_template(
"/".join([template_path, 'update.sql']),
data=data, o_data=old_data, conn=conn
)
else:
sql = _get_create_sql(data, template_path, conn, mode, name,
if_exists_flag=if_exists_flag)
return sql, name
def get_reverse_engineered_sql(conn, **kwargs):
"""
This function will return reverse engineered sql for specified trigger.
:param conn:
:param kwargs:
:return:
"""
schema = kwargs.get('schema')
table = kwargs.get('table')
scid = kwargs.get('scid')
plid = kwargs.get('plid')
policy_table_id = kwargs.get('policy_table_id')
datlastsysoid = kwargs.get('datlastsysoid')
template_path = kwargs.get('template_path', None)
with_header = kwargs.get('with_header', True)
SQL = render_template("/".join([template_path, 'properties.sql']),
plid=plid,
scid=scid,
policy_table_id=policy_table_id)
status, res = conn.execute_dict(SQL)
if not status:
raise ExecuteError(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the policy 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
SQL, name = get_sql(conn,
data=data,
scid=scid,
plid=None,
policy_table_id=policy_table_id,
datlastsysoid=datlastsysoid,
schema=schema,
table=table)
if with_header:
sql_header = "-- POLICY: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([template_path, 'delete.sql']),
policy_name=data['name'],
result=data)
SQL = sql_header + '\n\n' + SQL
return SQL
def __init__(self, **kwargs):
"""
This method is used to initialize the class and
create a proper object name which will be used
to fetch the data using namespace name and object name.
Args:
**kwargs : N number of parameters
"""
# Save the server id and database id, namespace id, object id
assert ('sid' in kwargs)
assert ('did' in kwargs)
assert ('obj_id' in kwargs)
self.sid = kwargs['sid']
self.did = kwargs['did']
self.obj_id = kwargs['obj_id']
sql_filter = kwargs.get('sql_filter', None)
self._row_filter = sql_filter if type(sql_filter) is str else None
self._data_sorting = kwargs.get('data_sorting', None)
self._set_sorting_from_filter_dialog = False
manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(self.sid)
conn = manager.connection(did=self.did)
# we will set template path for sql scripts
self.sql_path = 'sqleditor/sql/#{0}#'.format(manager.version)
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 Exception(result)
if len(result['rows']) == 0:
raise ObjectGone(
gettext("The specified object could not be found."))
self.nsp_name = result['rows'][0]['nspname']
self.object_name = result['rows'][0]['relname']
else:
raise Exception(gettext(
'Not connected to server or connection with the server '
'has been closed.')
)
def get_reverse_engineered_sql(conn, schema, table, did, scid, tid, plid,
datlastsysoid,
template_path=None, with_header=True):
"""
This function will return reverse engineered sql for specified trigger.
:param conn: Connection Object
:param schema: Schema
:param table: Table
:param did: DB ID
:param tid: Table ID
:param plid: Policy ID
:param datlastsysoid:
:param template_path: Optional template path
:param with_header: Optional parameter to decide whether the SQL will be
returned with header or not
:return:
"""
SQL = render_template("/".join(
[template_path, 'properties.sql']), plid=plid, scid=scid)
status, res = conn.execute_dict(SQL)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the policy 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
SQL, name = get_sql(conn, data, did, scid, tid, None, datlastsysoid,
schema,
table)
if with_header:
sql_header = u"-- POLICY: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([template_path,
'delete.sql']),
policy_name=data['name'],
result=data
)
SQL = sql_header + '\n\n' + SQL
return SQL
def get_sql(conn, data, did, tid, ctype, cid=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 ctype: Constraint Type
:param cid: index Constraint ID
:param template_path: Template Path
:return:
"""
name = data['name'] if 'name' in data else None
sql = None
if cid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
did=did,
tid=tid,
cid=cid,
constraint_type=ctype)
status, res = conn.execute_dict(sql)
if not status:
raise ExecuteError(res)
elif len(res['rows']) == 0:
raise ObjectGone(_('Could not find the 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:
is_error, errmsg, name = _check_required_args(data, name)
if is_error:
return _(errmsg), name
sql = render_template(
"/".join([template_path, 'create.sql']),
data=data,
conn=conn,
constraint_name='PRIMARY KEY' if ctype == 'p' else 'UNIQUE')
return sql, name
def _get_domain(self, doid):
"""
Returns Domain and Schema name.
Args:
doid: Domain Id
"""
SQL = render_template("/".join([self.template_path, 'get_domain.sql']),
doid=doid)
status, res = self.conn.execute_2darray(SQL)
if not status:
return False, internal_server_error(errormsg=res)
if len(res['rows']) == 0:
raise ObjectGone(self.not_found_error_msg('Domain'))
return res['rows'][0]['schema'], res['rows'][0]['domain']
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 Exception(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_sql(conn, data, did, tid, ctype, cid=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 ctype: Constraint Type
:param cid: index Constraint ID
:param template_path: Template Path
:return:
"""
name = data['name'] if 'name' in data else None
sql = None
if cid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
did=did,
tid=tid,
cid=cid,
constraint_type=ctype)
status, res = conn.execute_dict(sql)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the 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:
required_args = [[u'columns',
u'index'] # Either of one should be there.
]
def is_key_str(key, data):
return isinstance(data[key], (str, unicode)) and data[key] != ""
def is_key_list(key, data):
return isinstance(data[key], list) and len(data[param]) > 0
for arg in required_args:
if isinstance(arg, list):
for param in arg:
if param in data:
if is_key_str(param, data) or is_key_list(param, data):
break
else:
return _('-- definition incomplete'), name
elif arg not in data:
return _('-- definition incomplete'), name
sql = render_template(
"/".join([template_path, 'create.sql']),
data=data,
conn=conn,
constraint_name='PRIMARY KEY' if ctype == 'p' else 'UNIQUE')
return sql, name
def get_reverse_engineered_sql(conn,
schema,
table,
tid,
trid,
datlastsysoid,
show_system_objects,
template_path=None,
with_header=True):
"""
This function will return reverse engineered sql for specified trigger.
:param conn: Connection Object
:param schema: Schema
:param table: Table
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param show_system_objects: Show System Object value True or False
:param template_path: Optional template path
:param with_header: Optional parameter to decide whether the SQL will be
returned with header or not
:return:
"""
SQL = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(SQL)
if not status:
raise Exception(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, tid, None, datlastsysoid,
show_system_objects)
if with_header:
sql_header = u"-- 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_sql(conn,
data,
did,
tid,
idx,
datlastsysoid,
mode=None,
template_path=None):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param data: Data
:param did:
:param tid: Table ID
:param idx: Index ID
:param datlastsysoid:
:param mode:
:param template_path: Optional template path
:return:
"""
name = data['name'] if 'name' in data else None
if idx is not None:
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:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the index in the table.'))
old_data = dict(res['rows'][0])
# Remove opening and closing bracket as we already have in jinja
# template.
if 'using' in old_data and old_data['using'] is not None and \
old_data['using'].startswith('(') and \
old_data['using'].endswith(')'):
old_data['using'] = old_data['using'][1:-1]
if 'withcheck' in old_data and old_data['withcheck'] is not None and \
old_data['withcheck'].startswith('(') and \
old_data['withcheck'].endswith(')'):
old_data['withcheck'] = old_data['withcheck'][1:-1]
# 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']
SQL = render_template("/".join([template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=conn)
else:
required_args = {'name': 'Name', 'columns': 'Columns'}
for arg in required_args:
err = False
if arg == 'columns' and len(data['columns']) < 1:
err = True
if arg not in data:
err = True
# Check if we have at least one column
if err:
return _('-- definition incomplete'), name
# If the request for new object which do not have did
SQL = render_template("/".join([template_path, 'create.sql']),
data=data,
conn=conn,
mode=mode)
SQL += "\n"
SQL += render_template("/".join([template_path, 'alter.sql']),
data=data,
conn=conn)
return SQL, name
def get_sql(conn,
data,
tid,
trid,
datlastsysoid,
show_system_objects,
is_schema_diff=False,
template_path=None):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param data: Data
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param show_system_objects: Show System Object value True or False
:param is_schema_diff:
:param template_path: Optional template path
:return:
"""
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 Exception(res)
if 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 = ''
if is_schema_diff:
if 'table' not in data:
data['table'] = old_data['relname']
if 'schema' not in data:
data['schema'] = old_data['nspname']
# If any of the below key is present in data then we need to drop
# trigger and re-create it.
key_array = [
'prosrc', 'is_row_trigger', 'evnt_insert', 'evnt_delete',
'evnt_update', 'fires', 'tgdeferrable', 'whenclause',
'tfunction', 'tgargs', 'columns', 'is_constraint_trigger',
'tginitdeferred'
]
is_drop_trigger = False
for key in key_array:
if key in data:
is_drop_trigger = True
break
if is_drop_trigger:
tmp_data = dict()
tmp_data['name'] = data['name']
tmp_data['nspname'] = old_data['nspname']
tmp_data['relname'] = old_data['relname']
drop_sql = render_template("/".join(
[template_path, 'delete.sql']),
data=tmp_data,
conn=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 connection(self, **kwargs):
database = kwargs.get('database', None)
conn_id = kwargs.get('conn_id', None)
auto_reconnect = kwargs.get('auto_reconnect', True)
did = kwargs.get('did', None)
async_ = kwargs.get('async_', None)
use_binary_placeholder = kwargs.get('use_binary_placeholder', False)
array_to_string = kwargs.get('array_to_string', False)
if database is not None:
if did is not None and did in self.db_info:
self.db_info[did]['datname'] = database
else:
if did is None:
database = self.db
elif did in self.db_info:
database = self.db_info[did]['datname']
else:
maintenance_db_id = 'DB:{0}'.format(self.db)
if maintenance_db_id in self.connections:
conn = self.connections[maintenance_db_id]
# try to connect maintenance db if not connected
if not conn.connected():
conn.connect()
if conn.connected():
status, res = conn.execute_dict("""
SELECT
db.oid as did, db.datname, db.datallowconn,
pg_catalog.pg_encoding_to_char(db.encoding) AS serverencoding,
pg_catalog.has_database_privilege(db.oid, 'CREATE') as cancreate,
datlastsysoid, datistemplate
FROM
pg_catalog.pg_database db
WHERE db.oid = {0}""".format(did))
if status and len(res['rows']) > 0:
for row in res['rows']:
self.db_info[did] = row
database = self.db_info[did]['datname']
if did not in self.db_info:
raise ObjectGone(
gettext(
"Could not find the specified database."))
if not get_crypt_key()[0]:
# the reason its not connected might be missing key
raise CryptKeyMissing()
if database is None:
# Check SSH Tunnel is alive or not.
if self.use_ssh_tunnel == 1:
self.check_ssh_tunnel_alive()
else:
raise ConnectionLost(self.sid, None, None)
my_id = ('CONN:{0}'.format(conn_id)) if conn_id is not None else \
('DB:{0}'.format(database))
self.pinged = datetime.datetime.now()
if my_id in self.connections:
return self.connections[my_id]
else:
if async_ is None:
async_ = 1 if conn_id is not None else 0
else:
async_ = 1 if async_ is True else 0
self.connections[my_id] = Connection(
self,
my_id,
database,
auto_reconnect=auto_reconnect,
async_=async_,
use_binary_placeholder=use_binary_placeholder,
array_to_string=array_to_string)
return self.connections[my_id]
def get_sql(conn, data, tid, fkid=None, template_path=None):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param data: data
:param tid: Table id
:param fkid: Foreign Key
:param template_path: Template Path
:return:
"""
name = data['name'] if 'name' in data else None
if fkid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
cid=fkid)
status, res = conn.execute_dict(sql)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(
_('Could not find the foreign key 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)
if 'autoindex' in data and data['autoindex'] and \
('coveringindex' in data and data['coveringindex'] != ''):
col_sql = render_template(
"/".join([template_path, 'get_constraint_cols.sql']),
tid=tid,
keys=zip(old_data['confkey'], old_data['conkey']),
confrelid=old_data['confrelid'])
status, res = conn.execute_dict(col_sql)
if not status:
raise Exception(res)
columns = []
for row in res['rows']:
columns.append({
"local_column": row['conattname'],
"references": old_data['confrelid'],
"referenced": row['confattname']
})
data['columns'] = columns
sql += render_template("/".join(
[template_path, 'create_index.sql']),
data=data,
conn=conn)
else:
if 'columns' not in data:
return _('-- definition incomplete'), name
elif isinstance(data['columns'], list) and len(data['columns']) < 1:
return _('-- definition incomplete'), name
if data['autoindex'] and \
('coveringindex' not in data or data['coveringindex'] == ''):
return _('-- definition incomplete'), name
# Get the parent schema and table.
schema, table = get_parent(conn, data['columns'][0]['references'])
data['remote_schema'] = schema
data['remote_table'] = table
sql = render_template("/".join([template_path, 'create.sql']),
data=data,
conn=conn)
if data['autoindex']:
sql += render_template("/".join(
[template_path, 'create_index.sql']),
data=data,
conn=conn)
return sql, name
