def properties(self, gid, sid, did, fid):
"""
This function will show the properties of the selected foreign data wrapper node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
fid: foreign data wrapper ID
"""
sql = render_template("/".join([self.template_path, 'properties.sql']), fid=fid, conn=self.conn)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if res['rows'][0]['fdwoptions'] is not None:
res['rows'][0]['fdwoptions'] = self.tokenize_options(res['rows'][0]['fdwoptions'])
sql = render_template("/".join([self.template_path, 'acl.sql']), fid=fid)
status, fdw_acl_res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=fdw_acl_res)
for row in fdw_acl_res['rows']:
privilege = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(privilege)
else:
res['rows'][0][row['deftype']] = [privilege]
return ajax_response(
response=res['rows'][0],
status=200
)
def properties(self, gid, sid, did, scid, tid):
"""
This function will show the properties of the selected type node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
scid: Schema ID
tid: Type ID
Returns:
JSON of selected type node
"""
SQL = render_template(
"/".join([self.template_path,
'properties.sql']),
scid=scid, tid=tid,
datlastsysoid=self.datlastsysoid,
show_system_objects=self.blueprint.show_system_objects
)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
gettext("""Could not find the type in the database."""))
# Making copy of output for future use
copy_dict = dict(res['rows'][0])
# We need to parse & convert ACL coming from database to json format
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid, tid=tid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
copy_dict['typacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in copy_dict:
copy_dict[row['deftype']].append(priv)
else:
copy_dict[row['deftype']] = [priv]
# Calling function to check and additional properties if available
copy_dict.update(self.additional_properties(copy_dict, tid))
return ajax_response(
response=copy_dict,
status=200
)
def sql(self, gid, sid, did, lid):
"""
This function will generate sql to show in the sql pane for the
selected language node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
lid: Language ID
"""
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
lid=lid
)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
gettext("Could not find the language information.")
)
# Making copy of output for future use
old_data = dict(res['rows'][0])
sql = render_template(
"/".join([self.template_path, 'acl.sql']),
lid=lid
)
status, result = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=result)
for row in result['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in old_data:
old_data[row['deftype']].append(priv)
else:
old_data[row['deftype']] = [priv]
seclabels = []
if 'seclabels' in old_data and old_data['seclabels'] is not None:
import re
for sec in old_data['seclabels']:
sec = re.search(r'([^=]+)=(.*$)', sec)
seclabels.append({
'provider': sec.group(1),
'label': sec.group(2)
})
old_data['seclabels'] = seclabels
sql = render_template(
"/".join([self.template_path, 'sqlpane.sql']),
data=old_data, conn=self.conn
)
return ajax_response(response=sql.strip('\n'))
def properties(self, gid, sid, did, scid, seid):
"""
This function will show the properties of the selected sequence node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
seid: Sequence ID
Returns:
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid, seid=seid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
sec_lbls = []
if 'securities' in res and res['securities'] is not None:
for sec in res['seclabels']:
sec = re.search(r'([^=]+)=(.*$)', sec)
sec_lbls.append({
'provider': sec.group(1),
'label': sec.group(2)
})
res['securities'] = sec_lbls
for row in res['rows']:
SQL = render_template("/".join([self.template_path, 'get_def.sql']), data=row)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=rset1)
row['current_value'] = rset1['rows'][0]['last_value']
row['minimum'] = rset1['rows'][0]['min_value']
row['maximum'] = rset1['rows'][0]['max_value']
row['increment'] = rset1['rows'][0]['increment_by']
row['cache'] = rset1['rows'][0]['cache_value']
row['cycled'] = rset1['rows'][0]['is_cycled']
SQL = render_template("/".join([self.template_path, 'acl.sql']), scid=scid, seid=seid)
status, dataclres = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
for row in dataclres['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(priv)
else:
res['rows'][0][row['deftype']] = [priv]
return ajax_response(
response=res['rows'][0],
status=200
)
def sql(self, gid, sid, did, fid, fsid):
"""
This function will generate sql to show it in sql pane for the selected foreign server node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
fid: Foreign data wrapper ID
fsid: Foreign server ID
"""
sql = render_template("/".join([self.template_path, 'properties.sql']), fsid=fsid, conn=self.conn)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if res['rows'][0]['fsrvoptions'] is not None:
res['rows'][0]['fsrvoptions'] = self.tokenizeOptions(res['rows'][0]['fsrvoptions'])
sql = render_template("/".join([self.template_path, 'acl.sql']), fsid=fsid)
status, fs_rv_acl_res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=fs_rv_acl_res)
for row in fs_rv_acl_res['rows']:
privilege = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(privilege)
else:
res['rows'][0][row['deftype']] = [privilege]
# To format privileges
if 'fsrvacl' in res['rows'][0]:
res['rows'][0]['fsrvacl'] = parse_priv_to_db(res['rows'][0]['fsrvacl'], ['U'])
sql = render_template("/".join([self.template_path, 'properties.sql']), fdwid=fid, conn=self.conn)
status, res1 = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res1)
fdw_data = res1['rows'][0]
sql = ''
sql = render_template("/".join([self.template_path, 'create.sql']), data=res['rows'][0], fdwdata=fdw_data,
conn=self.conn)
sql += "\n"
sql_header = """-- Foreign Server: {0}
-- DROP SERVER {0}
""".format(res['rows'][0]['name'])
sql = sql_header + sql
return ajax_response(response=sql)
def _format_proacl_from_db(self, proacl):
"""
Returns privileges.
Args:
proacl: Privileges Dict
"""
privileges = []
for row in proacl:
priv = parse_priv_from_db(row)
privileges.append(priv)
return {"acl": privileges}
def _formatter(self, data, tsid=None):
"""
Args:
data: dict of query result
tsid: tablespace oid
Returns:
It will return formatted output of collections
"""
# We need to format variables according to client js collection
if 'spcoptions' in data and data['spcoptions'] is not None:
spcoptions = []
for spcoption in data['spcoptions']:
k, v = spcoption.split('=')
spcoptions.append({'name': k, 'value': v})
data['spcoptions'] = spcoptions
# Need to format security labels according to client js collection
if 'seclabels' in data and data['seclabels'] is not None:
seclabels = []
for seclbls in data['seclabels']:
k, v = seclbls.split('=')
seclabels.append({'provider': k, 'label': v})
data['seclabels'] = seclabels
# We need to parse & convert ACL coming from database to json format
SQL = render_template(
"/".join([self.template_path, 'acl.sql']),
tsid=tsid, conn=self.conn
)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
data['spcacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in data:
data[row['deftype']].append(priv)
else:
data[row['deftype']] = [priv]
return data
def properties(self, gid, sid, did, scid, pkgid):
"""
This function will show the properties of the selected package node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
pkgid: Package ID
Returns:
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']),
scid=scid, pkgid=pkgid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
errormsg=_("Could not find the package in the database.")
)
res['rows'][0]['pkgheadsrc'] = self.get_inner(
res['rows'][0]['pkgheadsrc'])
res['rows'][0]['pkgbodysrc'] = self.get_inner(
res['rows'][0]['pkgbodysrc'])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
pkgid=pkgid)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=rset1)
for row in rset1['rows']:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
return ajax_response(
response=res['rows'][0],
status=200
)
def formatdbacl(acl):
"""
Args:
acl: Privileges from ACL query
Returns:
Formatted output required for client side parsing
"""
# Reset any data for that acl if its already present in result set
data = dict()
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in data:
data[row['deftype']].append(priv)
else:
data[row['deftype']] = [priv]
return data
def properties(self, gid, sid, did, lid):
"""
This function will show the properties of the selected language node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
lid: Language ID
"""
sql = render_template("/".join([self.template_path, 'properties.sql']), lid=lid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
sql = render_template("/".join([self.template_path, 'acl.sql']), lid=lid)
status, result = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=result)
# if no acl found then by default add public
if res['rows'][0]['acl'] is None:
res['rows'][0]['lanacl'] = dict()
res['rows'][0]['lanacl']['grantee'] = 'PUBLIC'
res['rows'][0]['lanacl']['grantor'] = res['rows'][0]['lanowner']
res['rows'][0]['lanacl']['privileges'] = [{'privilege_type': 'U', 'privilege': True, 'with_grant': False}]
else:
for row in result['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(priv)
else:
res['rows'][0][row['deftype']] = [priv]
return ajax_response(
response=res['rows'][0],
status=200
)
def _formatter(self, data, scid, seid):
"""
Args:
data: dict of query result
scid: Schema ID
seid: Sequence ID
Returns:
It will return formatted output of sequence
"""
# Need to format security labels according to client js collection
if 'securities' in data and data['securities'] is not None:
seclabels = []
for seclbls in data['securities']:
k, v = seclbls.split('=')
seclabels.append({'provider': k, 'security_label': v})
data['securities'] = seclabels
# We need to parse & convert ACL coming from database to json format
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
seid=seid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
data['relacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
data.setdefault(row['deftype'], []).append(priv)
return data
def _fetch_properties(self, scid, pkgid):
"""
This function is used to fetch the properties of specified object.
:param scid:
:param pkgid:
:return:
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']),
scid=scid,
pkgid=pkgid)
status, res = self.conn.execute_dict(SQL)
if not status:
return False, internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return False, gone(
errormsg=_("Could not find the package in the database."))
res['rows'][0]['pkgheadsrc'] = self.get_inner(
res['rows'][0]['pkgheadsrc'])
res['rows'][0]['pkgbodysrc'] = self.get_inner(
res['rows'][0]['pkgbodysrc'])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
pkgid=pkgid)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return False, internal_server_error(errormsg=rset1)
for row in rset1['rows']:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
return True, res['rows'][0]
def _formatter(self, data, scid, seid):
"""
Args:
data: dict of query result
scid: Schema ID
seid: Sequence ID
Returns:
It will return formatted output of sequence
"""
# Need to format security labels according to client js collection
if 'securities' in data and data['securities'] is not None:
seclabels = []
for seclbls in data['securities']:
k, v = seclbls.split('=')
seclabels.append({'provider': k, 'label': v})
data['securities'] = seclabels
# We need to parse & convert ACL coming from database to json format
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid, seid=seid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
data['relacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
data.setdefault(row['deftype'], []).append(priv)
return data
def sql(self, gid, sid, did, scid, tid):
"""
This function will generates reverse engineered sql for type object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Type ID
"""
SQL = render_template(
"/".join([self.template_path, 'properties.sql']),
scid=scid,
tid=tid,
datlastsysoid=self.datlastsysoid,
show_system_objects=self.blueprint.show_system_objects)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
# Making copy of output for future use
data = dict(res['rows'][0])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
tid=tid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
data['typacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in data:
data[row['deftype']].append(priv)
else:
data[row['deftype']] = [priv]
# Privileges
if 'typacl' in data and data['typacl'] is not None:
data['nspacl'] = parse_priv_to_db(data['typacl'], self.acl)
# Calling function to check and additional properties if available
data.update(self.additional_properties(data, tid))
# We do not want to display table which has '-' value
# setting them to None so that jinja avoid displaying them
for k in data:
if data[k] == '-':
data[k] = None
SQL = self.get_sql(gid, sid, data, scid, tid=None)
# We are appending headers here for sql panel
sql_header = "-- Type: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join(
[self.template_path, 'delete.sql']),
data=data,
conn=self.conn)
SQL = sql_header + '\n\n' + SQL
return ajax_response(response=SQL)
def sql(self, gid, sid, did, fid, fsid):
"""
This function will generate sql to show it in sql pane for the selected foreign server node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
fid: Foreign data wrapper ID
fsid: Foreign server ID
"""
sql = render_template("/".join([self.template_path, 'properties.sql']),
fsid=fsid,
conn=self.conn)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if res['rows'][0]['fsrvoptions'] is not None:
res['rows'][0]['fsrvoptions'] = self.tokenizeOptions(
res['rows'][0]['fsrvoptions'])
sql = render_template("/".join([self.template_path, 'acl.sql']),
fsid=fsid)
status, fs_rv_acl_res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=fs_rv_acl_res)
for row in fs_rv_acl_res['rows']:
privilege = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(privilege)
else:
res['rows'][0][row['deftype']] = [privilege]
# To format privileges
if 'fsrvacl' in res['rows'][0]:
res['rows'][0]['fsrvacl'] = parse_priv_to_db(
res['rows'][0]['fsrvacl'], ['U'])
sql = render_template("/".join([self.template_path, 'properties.sql']),
fdwid=fid,
conn=self.conn)
status, res1 = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res1)
fdw_data = res1['rows'][0]
sql = ''
sql = render_template("/".join([self.template_path, 'create.sql']),
data=res['rows'][0],
fdwdata=fdw_data,
conn=self.conn)
sql += "\n"
sql_header = """-- Foreign Server: {0}
-- DROP SERVER {0}
""".format(res['rows'][0]['name'])
sql = sql_header + sql
return ajax_response(response=sql)
def sql(self, gid, sid, did, scid, pkgid):
"""
This function will generate sql for sql panel
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
pkgid: Package ID
"""
try:
SQL = render_template(
"/".join([self.template_path, 'properties.sql']), scid=scid,
pkgid=pkgid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
errormsg=_("Could not find the package in the database.")
)
res['rows'][0]['pkgheadsrc'] = self.get_inner(
res['rows'][0]['pkgheadsrc'])
res['rows'][0]['pkgbodysrc'] = self.get_inner(
res['rows'][0]['pkgbodysrc'])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
pkgid=pkgid)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=rset1)
for row in rset1['rows']:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
result = res['rows'][0]
sql, name = self.getSQL(gid, sid, did, result, scid, pkgid)
# Most probably this is due to error
if not isinstance(sql, (str, unicode)):
return sql
sql = sql.strip('\n').strip(' ')
sql_header = u"-- Package: {}\n\n-- ".format(
self.qtIdent(self.conn, self.schema, result['name'])
)
sql_header += render_template(
"/".join([self.template_path, 'delete.sql']),
data=result)
sql_header += "\n\n"
sql = sql_header + sql
return ajax_response(response=sql)
except Exception as e:
return internal_server_error(errormsg=str(e))
def get_sql(self, gid, sid, data, scid, tid=None):
"""
This function will genrate sql from model data
"""
if tid is not None:
for key in ['typacl']:
if key in data and data[key] is not None:
if 'added' in data[key]:
data[key]['added'] = parse_priv_to_db(
data[key]['added'], self.acl)
if 'changed' in data[key]:
data[key]['changed'] = parse_priv_to_db(
data[key]['changed'], self.acl)
if 'deleted' in data[key]:
data[key]['deleted'] = parse_priv_to_db(
data[key]['deleted'], self.acl)
SQL = render_template(
"/".join([self.template_path, 'properties.sql']),
scid=scid,
tid=tid,
datlastsysoid=self.datlastsysoid,
show_system_objects=self.blueprint.show_system_objects)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
# Making copy of output for future use
old_data = dict(res['rows'][0])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
tid=tid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
old_data['typacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in old_data:
old_data[row['deftype']].append(priv)
else:
old_data[row['deftype']] = [priv]
# Calling function to check and additional properties if available
old_data.update(self.additional_properties(old_data, tid))
old_data = self._convert_for_sql(old_data)
SQL = render_template("/".join([self.template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=self.conn)
else:
required_args = ['name', 'typtype']
for arg in required_args:
if arg not in data:
return " --definition incomplete"
# Privileges
if 'typacl' in data and data['typacl'] is not None:
data['typacl'] = parse_priv_to_db(data['typacl'], self.acl)
data = self._convert_for_sql(data)
SQL = render_template("/".join([self.template_path, 'create.sql']),
data=data,
conn=self.conn)
return SQL
def formatdbacl(res, dbacl):
for row in dbacl:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
return res
def properties(self, gid, sid, did, lid):
"""
This function will show the properties of the selected language node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
lid: Language ID
"""
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
lid=lid
)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
gettext("Could not find the language information.")
)
sql = render_template(
"/".join([self.template_path, 'acl.sql']),
lid=lid
)
status, result = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=result)
# if no acl found then by default add public
if res['rows'][0]['acl'] is None:
res['rows'][0]['lanacl'] = dict()
res['rows'][0]['lanacl']['grantee'] = 'PUBLIC'
res['rows'][0]['lanacl']['grantor'] = res['rows'][0]['lanowner']
res['rows'][0]['lanacl']['privileges'] = [
{
'privilege_type': 'U',
'privilege': True,
'with_grant': False
}
]
else:
for row in result['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(priv)
else:
res['rows'][0][row['deftype']] = [priv]
seclabels = []
if 'seclabels' in res['rows'][0] and \
res['rows'][0]['seclabels'] is not None:
import re
for sec in res['rows'][0]['seclabels']:
sec = re.search(r'([^=]+)=(.*$)', sec)
seclabels.append({
'provider': sec.group(1),
'label': sec.group(2)
})
res['rows'][0]['seclabels'] = seclabels
return ajax_response(
response=res['rows'][0],
status=200
)
def getSQL(self,
gid,
sid,
did,
data,
scid,
pkgid=None,
sqltab=False,
diff_schema=None):
"""
This function will generate sql from model data.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
pkgid: Package ID
sqltab: True
diff_schema: Target Schema
"""
required_args = [u'name']
if diff_schema:
data['schema'] = diff_schema
else:
data['schema'] = self.schema
if pkgid is not None and not sqltab:
SQL = render_template("/".join(
[self.template_path, 'properties.sql']),
scid=scid,
pkgid=pkgid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
errormsg=_("Could not find the package in the database."))
res['rows'][0]['pkgheadsrc'] = self.get_inner(
res['rows'][0]['pkgheadsrc'])
res['rows'][0]['pkgbodysrc'] = self.get_inner(
res['rows'][0]['pkgbodysrc'])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
pkgid=pkgid)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=rset1)
for row in rset1['rows']:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
# Making copy of output for further processing
old_data = dict(res['rows'][0])
# To format privileges data coming from client
for key in ['pkgacl']:
if key in data and data[key] is not None:
if 'added' in data[key]:
data[key]['added'] = parse_priv_to_db(
data[key]['added'], self.acl)
if 'changed' in data[key]:
data[key]['changed'] = parse_priv_to_db(
data[key]['changed'], self.acl)
if 'deleted' in data[key]:
data[key]['deleted'] = parse_priv_to_db(
data[key]['deleted'], self.acl)
# If name is not present with in update data then copy it
# from old data
for arg in required_args:
if arg not in data:
data[arg] = old_data[arg]
SQL = render_template("/".join([self.template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=self.conn,
is_schema_diff=diff_schema)
return SQL, data['name'] if 'name' in data else old_data['name']
else:
# To format privileges coming from client
if 'pkgacl' in data:
data['pkgacl'] = parse_priv_to_db(data['pkgacl'], self.acl)
SQL = render_template("/".join([self.template_path, 'create.sql']),
data=data,
conn=self.conn)
return SQL, data['name']
def sql(self, gid, sid, did, fid, fsid, json_resp=True):
"""
This function will generate sql to show it in sql pane for the
selected foreign server node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
fid: Foreign data wrapper ID
fsid: Foreign server ID
json_resp:
"""
sql = render_template("/".join([self.template_path,
self._PROPERTIES_SQL]),
fsid=fsid, conn=self.conn)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(self.not_found_error_msg())
if fid is None and 'fdwid' in res['rows'][0]:
fid = res['rows'][0]['fdwid']
is_valid_options = False
if res['rows'][0]['fsrvoptions'] is not None:
res['rows'][0]['fsrvoptions'] = tokenize_options(
res['rows'][0]['fsrvoptions'], 'fsrvoption', 'fsrvvalue'
)
if len(res['rows'][0]['fsrvoptions']) > 0:
is_valid_options = True
sql = render_template("/".join([self.template_path, self._ACL_SQL]),
fsid=fsid)
status, fs_rv_acl_res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=fs_rv_acl_res)
for row in fs_rv_acl_res['rows']:
privilege = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(privilege)
else:
res['rows'][0][row['deftype']] = [privilege]
# To format privileges
if 'fsrvacl' in res['rows'][0]:
res['rows'][0]['fsrvacl'] = parse_priv_to_db(
res['rows'][0]['fsrvacl'],
['U']
)
sql = render_template("/".join([self.template_path,
self._PROPERTIES_SQL]),
fdwid=fid, conn=self.conn)
status, res1 = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res1)
fdw_data = res1['rows'][0]
sql = ''
sql = render_template("/".join([self.template_path,
self._CREATE_SQL]),
data=res['rows'][0], fdwdata=fdw_data,
is_valid_options=is_valid_options,
conn=self.conn)
sql += "\n"
sql_header = """-- Foreign Server: {0}
-- DROP SERVER IF EXISTS {0}
""".format(res['rows'][0]['name'])
sql = sql_header + sql
if not json_resp:
return sql.strip('\n')
return ajax_response(response=sql.strip('\n'))
def _formatter(self, scid, tid, clid, data):
"""
Args:
scid: schema oid
tid: table oid
clid: position of column in table
data: dict of query result
Returns:
It will return formatted output of collections
"""
# To check if column is primary key
if 'attnum' in data and 'indkey' in data:
# Current column
attnum = str(data['attnum'])
# Single/List of primary key column(s)
indkey = str(data['indkey'])
# We will check if column is in primary column(s)
if attnum in indkey.split(" "):
data['is_pk'] = True
else:
data['is_pk'] = False
# Find length & precision of column data type
fulltype = self.get_full_type(data['typnspname'], data['typname'],
data['isdup'], data['attndims'],
data['atttypmod'])
length = False
precision = False
if 'elemoid' in data:
length, precision, typeval = \
self.get_length_precision(data['elemoid'])
# Set length and precision to None
data['attlen'] = None
data['attprecision'] = None
import re
# If we have length & precision both
if length and precision:
matchObj = re.search(r'(\d+),(\d+)', fulltype)
if matchObj:
data['attlen'] = matchObj.group(1)
data['attprecision'] = matchObj.group(2)
elif length:
# If we have length only
matchObj = re.search(r'(\d+)', fulltype)
if matchObj:
data['attlen'] = matchObj.group(1)
data['attprecision'] = None
# We need to fetch inherited tables for each table
SQL = render_template("/".join(
[self.template_path, 'get_inherited_tables.sql']),
tid=tid)
status, inh_res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=inh_res)
for row in inh_res['rows']:
if row['attrname'] == data['name']:
data['is_inherited'] = True
data['tbls_inherited'] = row['inhrelname']
# We need to format variables according to client js collection
if 'attoptions' in data and data['attoptions'] is not None:
spcoptions = []
for spcoption in data['attoptions']:
k, v = spcoption.split('=')
spcoptions.append({'name': k, 'value': v})
data['attoptions'] = spcoptions
# Need to format security labels according to client js collection
if 'seclabels' in data and data['seclabels'] is not None:
seclabels = []
for seclbls in data['seclabels']:
k, v = seclbls.split('=')
seclabels.append({'provider': k, 'label': v})
data['seclabels'] = seclabels
# We need to parse & convert ACL coming from database to json format
SQL = render_template("/".join([self.template_path, 'acl.sql']),
tid=tid,
clid=clid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
data['attacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
data.setdefault(row['deftype'], []).append(priv)
# we are receiving request when in edit mode
# we will send filtered types related to current type
present_type = data['cltype']
type_id = data['atttypid']
SQL = render_template("/".join(
[self.template_path, 'is_referenced.sql']),
tid=tid,
clid=clid)
status, is_reference = self.conn.execute_scalar(SQL)
edit_types_list = list()
# We will need present type in edit mode
if data['typnspname'] == "pg_catalog" or \
data['typnspname'] == "public":
edit_types_list.append(present_type)
else:
t = self.qtTypeIdent(self.conn, data['typnspname'], present_type)
edit_types_list.append(t)
data['cltype'] = t
if int(is_reference) == 0:
SQL = render_template("/".join(
[self.template_path, 'edit_mode_types.sql']),
type_id=type_id)
status, rset = self.conn.execute_2darray(SQL)
for row in rset['rows']:
edit_types_list.append(row['typname'])
else:
edit_types_list.append(present_type)
data['edit_types'] = edit_types_list
data['cltype'] = DataTypeReader.parse_type_name(data['cltype'])
return data
def get_sql(self, gid, sid, data, scid, tid=None):
"""
This function will genrate sql from model data
"""
if tid is not None:
for key in ['typacl']:
if key in data and data[key] is not None:
if 'added' in data[key]:
data[key]['added'] = parse_priv_to_db(data[key]['added'], self.acl)
if 'changed' in data[key]:
data[key]['changed'] = parse_priv_to_db(data[key]['changed'], self.acl)
if 'deleted' in data[key]:
data[key]['deleted'] = parse_priv_to_db(data[key]['deleted'], self.acl)
SQL = render_template("/".join([self.template_path,
'properties.sql']),
scid=scid, tid=tid,
datlastsysoid=self.datlastsysoid,
show_system_objects=self.blueprint.show_system_objects
)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
# Making copy of output for future use
old_data = dict(res['rows'][0])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid, tid=tid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
old_data['typacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in old_data:
old_data[row['deftype']].append(priv)
else:
old_data[row['deftype']] = [priv]
# Calling function to check and additional properties if available
old_data.update(self.additional_properties(old_data, tid))
old_data = self._convert_for_sql(old_data)
SQL = render_template(
"/".join([self.template_path, 'update.sql']),
data=data, o_data=old_data, conn=self.conn
)
else:
required_args = [
'name',
'typtype'
]
for arg in required_args:
if arg not in data:
return " --definition incomplete"
# Privileges
if 'typacl' in data and data['typacl'] is not None:
data['typacl'] = parse_priv_to_db(data['typacl'], self.acl)
data = self._convert_for_sql(data)
SQL = render_template("/".join([self.template_path,
'create.sql']),
data=data, conn=self.conn)
return SQL
def sql(self, gid, sid, did, scid, tid):
"""
This function will generates reverse engineered sql for type object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Type ID
"""
SQL = render_template("/".join([self.template_path,
'properties.sql']),
scid=scid, tid=tid,
datlastsysoid=self.datlastsysoid,
show_system_objects=self.blueprint.show_system_objects
)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
# Making copy of output for future use
data = dict(res['rows'][0])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid, tid=tid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
data['typacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in data:
data[row['deftype']].append(priv)
else:
data[row['deftype']] = [priv]
# Privileges
if 'typacl' in data and data['typacl'] is not None:
data['nspacl'] = parse_priv_to_db(data['typacl'], self.acl)
# Calling function to check and additional properties if available
data.update(self.additional_properties(data, tid))
# We do not want to display table which has '-' value
# setting them to None so that jinja avoid displaying them
for k in data:
if data[k] == '-':
data[k] = None
SQL = self.get_sql(gid, sid, data, scid, tid=None)
# We are appending headers here for sql panel
sql_header = "-- Type: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([self.template_path,
'delete.sql']),
data=data, conn=self.conn)
SQL = sql_header + '\n\n' + SQL
return ajax_response(response=SQL)
def getSQL(self, gid, sid, did, data, scid, pkgid=None):
"""
This function will generate sql from model data.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
pkgid: Package ID
"""
required_args = [
u'name'
]
if pkgid is not None:
data['schema'] = self.schema
SQL = render_template(
"/".join([self.template_path, 'properties.sql']), scid=scid,
pkgid=pkgid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
errormsg=_("Could not find the package in the database.")
)
res['rows'][0]['pkgheadsrc'] = self.get_inner(
res['rows'][0]['pkgheadsrc'])
res['rows'][0]['pkgbodysrc'] = self.get_inner(
res['rows'][0]['pkgbodysrc'])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
pkgid=pkgid)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=rset1)
for row in rset1['rows']:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
# Making copy of output for further processing
old_data = dict(res['rows'][0])
# To format privileges data coming from client
for key in ['pkgacl']:
if key in data and data[key] is not None:
if 'added' in data[key]:
data[key]['added'] = parse_priv_to_db(
data[key]['added'], self.acl)
if 'changed' in data[key]:
data[key]['changed'] = parse_priv_to_db(
data[key]['changed'], self.acl)
if 'deleted' in data[key]:
data[key]['deleted'] = parse_priv_to_db(
data[key]['deleted'], self.acl)
# If name is not present with in update data then copy it
# from old data
for arg in required_args:
if arg not in data:
data[arg] = old_data[arg]
SQL = render_template("/".join([self.template_path, 'update.sql']),
data=data, o_data=old_data, conn=self.conn)
return SQL, data['name'] if 'name' in data else old_data['name']
else:
# To format privileges coming from client
if 'pkgacl' in data:
data['pkgacl'] = parse_priv_to_db(data['pkgacl'], self.acl)
SQL = render_template("/".join([self.template_path, 'create.sql']),
data=data, conn=self.conn)
return SQL, data['name']
def _fetch_properties(self, scid, seid):
"""
This function is used to fetch the properties of the specified object.
:param scid:
:param seid:
:return:
"""
SQL = render_template(
"/".join([self.template_path, 'properties.sql']),
scid=scid, seid=seid
)
status, res = self.conn.execute_dict(SQL)
if not status:
return False, internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return False, gone(
_("Could not find the sequence in the database."))
res['rows'][0]['is_sys_obj'] = (
res['rows'][0]['oid'] <= self.datlastsysoid)
for row in res['rows']:
SQL = render_template(
"/".join([self.template_path, 'get_def.sql']),
data=row
)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return False, internal_server_error(errormsg=rset1)
row['current_value'] = rset1['rows'][0]['last_value']
row['minimum'] = rset1['rows'][0]['min_value']
row['maximum'] = rset1['rows'][0]['max_value']
row['increment'] = rset1['rows'][0]['increment_by']
row['start'] = rset1['rows'][0]['start_value']
row['cache'] = rset1['rows'][0]['cache_value']
row['cycled'] = rset1['rows'][0]['is_cycled']
sec_lbls = []
if 'securities' in row and row['securities'] is not None:
for sec in row['securities']:
import re
sec = re.search(r'([^=]+)=(.*$)', sec)
sec_lbls.append({
'provider': sec.group(1),
'label': sec.group(2)
})
row['securities'] = sec_lbls
SQL = render_template(
"/".join([self.template_path, 'acl.sql']),
scid=scid, seid=seid
)
status, dataclres = self.conn.execute_dict(SQL)
if not status:
return False, internal_server_error(errormsg=res)
for row in dataclres['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(priv)
else:
res['rows'][0][row['deftype']] = [priv]
return True, res['rows'][0]
def column_formatter(conn, tid, clid, data, edit_types_list=None,
fetch_inherited_tables=True, template_path=None):
"""
This function will return formatted output of query result
as per client model format for column node
:param conn: Connection Object
:param tid: Table ID
:param clid: Column ID
:param data: Data
:param edit_types_list:
:param fetch_inherited_tables:
:param template_path: Optional template path
:return:
"""
# To check if column is primary key
_check_primary_column(data)
# Fetch length and precision
data = fetch_length_precision(data)
# We need to fetch inherited tables for each table
is_error, errmsg = _fetch_inherited_tables(
tid, data, fetch_inherited_tables, template_path, conn)
if is_error:
return errmsg
# We need to format variables according to client js collection
if 'attoptions' in data and data['attoptions'] is not None:
spcoptions = []
for spcoption in data['attoptions']:
k, v = spcoption.split('=')
spcoptions.append({'name': k, 'value': v})
data['attoptions'] = spcoptions
# Need to format security labels according to client js collection
if 'seclabels' in data and data['seclabels'] is not None:
seclabels = []
for seclbls in data['seclabels']:
k, v = seclbls.split('=')
seclabels.append({'provider': k, 'label': v})
data['seclabels'] = seclabels
# We need to parse & convert ACL coming from database to json format
SQL = render_template("/".join([template_path, 'acl.sql']),
tid=tid, clid=clid)
status, acl = conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
data['attacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
data.setdefault(row['deftype'], []).append(priv)
# we are receiving request when in edit mode
# we will send filtered types related to current type
type_id = data['atttypid']
if edit_types_list is None:
edit_types_list = []
SQL = render_template("/".join([template_path,
'edit_mode_types.sql']),
type_id=type_id)
status, rset = conn.execute_2darray(SQL)
edit_types_list = [row['typname'] for row in rset['rows']]
# We will need present type in edit mode
edit_types_list.append(data['typname'])
data['edit_types'] = sorted(edit_types_list)
data['cltype'] = DataTypeReader.parse_type_name(data['cltype'])
return data
def sql(self, gid, sid, did, fid):
"""
This function will generate sql to show it in sql pane
for the selected foreign data wrapper node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
fid: Foreign data wrapper ID
"""
sql = render_template("/".join([self.template_path, 'properties.sql']),
fid=fid, conn=self.conn
)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
_("Could not find the foreign data wrapper on the server.")
)
is_valid_options = False
if res['rows'][0]['fdwoptions'] is not None:
res['rows'][0]['fdwoptions'] = tokenize_options(
res['rows'][0]['fdwoptions'], 'fdwoption', 'fdwvalue'
)
if len(res['rows'][0]['fdwoptions']) > 0:
is_valid_options = True
sql = render_template("/".join([self.template_path, 'acl.sql']),
fid=fid)
status, fdw_acl_res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=fdw_acl_res)
for row in fdw_acl_res['rows']:
privilege = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(privilege)
else:
res['rows'][0][row['deftype']] = [privilege]
# To format privileges
if 'fdwacl' in res['rows'][0]:
res['rows'][0]['fdwacl'] = parse_priv_to_db(
res['rows'][0]['fdwacl'],
['U']
)
sql = ''
sql = render_template("/".join([self.template_path, 'create.sql']),
data=res['rows'][0], conn=self.conn,
is_valid_options=is_valid_options
)
sql += "\n"
sql_header = u"""-- Foreign Data Wrapper: {0}
-- DROP FOREIGN DATA WRAPPER {0}
""".format(res['rows'][0]['name'])
sql = sql_header + sql
return ajax_response(response=sql.strip('\n'))
def properties(self, gid, sid, did, scid, seid):
"""
This function will show the properties of the selected sequence node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
seid: Sequence ID
Returns:
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid, seid=seid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(_("""Could not find the sequence in the database."""))
for row in res['rows']:
SQL = render_template("/".join([self.template_path, 'get_def.sql']), data=row)
status, rset1 = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=rset1)
row['current_value'] = rset1['rows'][0]['last_value']
row['minimum'] = rset1['rows'][0]['min_value']
row['maximum'] = rset1['rows'][0]['max_value']
row['increment'] = rset1['rows'][0]['increment_by']
row['start'] = rset1['rows'][0]['start_value']
row['cache'] = rset1['rows'][0]['cache_value']
row['cycled'] = rset1['rows'][0]['is_cycled']
sec_lbls = []
if 'securities' in row and row['securities'] is not None:
for sec in row['securities']:
import re
sec = re.search(r'([^=]+)=(.*$)', sec)
sec_lbls.append({
'provider': sec.group(1),
'label': sec.group(2)
})
row['securities'] = sec_lbls
SQL = render_template("/".join([self.template_path, 'acl.sql']), scid=scid, seid=seid)
status, dataclres = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
for row in dataclres['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(priv)
else:
res['rows'][0][row['deftype']] = [priv]
return ajax_response(
response=res['rows'][0],
status=200
)
def formatdbacl(res, dbacl):
for row in dbacl:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
return res
def sql(self, gid, sid, did, scid, pkgid, **kwargs):
"""
This function will generate sql for sql panel
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
pkgid: Package ID
is_schema_diff:
json_resp: json response or plain text response
"""
is_schema_diff = kwargs.get('is_schema_diff', None)
json_resp = kwargs.get('json_resp', True)
target_schema = kwargs.get('target_schema', None)
try:
sql = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
scid=scid,
pkgid=pkgid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(errormsg=self.not_found_error_msg())
res['rows'][0]['pkgheadsrc'] = self.get_inner(
res['rows'][0]['pkgheadsrc'])
res['rows'][0]['pkgbodysrc'] = self.get_inner(
res['rows'][0]['pkgbodysrc'])
sql = render_template("/".join([self.template_path,
self._ACL_SQL]),
scid=scid,
pkgid=pkgid)
status, rset1 = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=rset1)
for row in rset1['rows']:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
result = res['rows'][0]
if target_schema:
result['schema'] = target_schema
sql, name = self.getSQL(data=result,
scid=scid,
pkgid=pkgid,
sqltab=True,
is_schema_diff=is_schema_diff,
target_schema=target_schema)
# Most probably this is due to error
if not isinstance(sql, str):
return sql
sql = sql.strip('\n').strip(' ')
# Return sql for schema diff
if not json_resp:
return sql
sql_header = "-- Package: {0}.{1}\n\n-- ".format(
self.schema, result['name'])
sql_header += render_template("/".join(
[self.template_path, self._DELETE_SQL]),
data=result)
sql_header += "\n\n"
sql = sql_header + sql
return ajax_response(response=sql)
except Exception as e:
return internal_server_error(errormsg=str(e))
def properties(self, gid, sid, did, lid):
"""
This function will show the properties of the selected language node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
lid: Language ID
"""
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
lid=lid
)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
gettext("Could not find the language information.")
)
sql = render_template(
"/".join([self.template_path, 'acl.sql']),
lid=lid
)
status, result = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=result)
# if no acl found then by default add public
if res['rows'][0]['acl'] is None:
res['rows'][0]['lanacl'] = dict()
res['rows'][0]['lanacl']['grantee'] = 'PUBLIC'
res['rows'][0]['lanacl']['grantor'] = res['rows'][0]['lanowner']
res['rows'][0]['lanacl']['privileges'] = [
{
'privilege_type': 'U',
'privilege': True,
'with_grant': False
}
]
else:
for row in result['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(priv)
else:
res['rows'][0][row['deftype']] = [priv]
seclabels = []
if 'seclabels' in res['rows'][0] and \
res['rows'][0]['seclabels'] is not None:
import re
for sec in res['rows'][0]['seclabels']:
sec = re.search(r'([^=]+)=(.*$)', sec)
seclabels.append({
'provider': sec.group(1),
'label': sec.group(2)
})
res['rows'][0]['seclabels'] = seclabels
return ajax_response(
response=res['rows'][0],
status=200
)
def sql(self, gid, sid, did, lid, json_resp=True):
"""
This function will generate sql to show in the sql pane for the
selected language node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
lid: Language ID
json_resp:
"""
sql = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
lid=lid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(self._NOT_FOUND_LANG_INFORMATION)
# Making copy of output for future use
old_data = dict(res['rows'][0])
sql = render_template("/".join([self.template_path, self._ACL_SQL]),
lid=lid)
status, result = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=result)
for row in result['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in old_data:
old_data[row['deftype']].append(priv)
else:
old_data[row['deftype']] = [priv]
# To format privileges
if 'lanacl' in old_data:
old_data['lanacl'] = parse_priv_to_db(old_data['lanacl'], ['U'])
seclabels = []
if 'seclabels' in old_data and old_data['seclabels'] is not None:
import re
for sec in old_data['seclabels']:
sec = re.search(r'([^=]+)=(.*$)', sec)
seclabels.append({
'provider': sec.group(1),
'label': sec.group(2)
})
old_data['seclabels'] = seclabels
sql = render_template("/".join([self.template_path, 'sqlpane.sql']),
data=old_data,
conn=self.conn)
if not json_resp:
return sql.strip('\n')
return ajax_response(response=sql.strip('\n'))
def get_sql(self, gid, sid, data, scid, tid=None, is_sql=False):
"""
This function will genrate sql from model data
"""
if tid is not None:
for key in ['typacl']:
if key in data and data[key] is not None:
if 'added' in data[key]:
data[key]['added'] = parse_priv_to_db(
data[key]['added'], self.acl)
if 'changed' in data[key]:
data[key]['changed'] = parse_priv_to_db(
data[key]['changed'], self.acl)
if 'deleted' in data[key]:
data[key]['deleted'] = parse_priv_to_db(
data[key]['deleted'], self.acl)
if 'composite' in data and len(data['composite']) > 0:
for key in ['added', 'changed', 'deleted']:
if key in data['composite']:
for each_type in data['composite'][key]:
each_type = self. \
convert_length_precision_to_string(each_type)
if 'type' in each_type:
each_type['cltype'] = self._cltype_formatter(
each_type['type'])
each_type['hasSqrBracket'] = self.hasSqrBracket
SQL = render_template(
"/".join([self.template_path,
'properties.sql']),
scid=scid, tid=tid,
datlastsysoid=self.datlastsysoid,
show_system_objects=self.blueprint.show_system_objects
)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
gettext("Could not find the type in the database.")
)
# Making copy of output for future use
old_data = dict(res['rows'][0])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid, tid=tid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
old_data['typacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in old_data:
old_data[row['deftype']].append(priv)
else:
old_data[row['deftype']] = [priv]
# Calling function to check and additional properties if available
old_data.update(self.additional_properties(old_data, tid))
old_data = self._convert_for_sql(old_data)
SQL = render_template(
"/".join([self.template_path, 'update.sql']),
data=data, o_data=old_data, conn=self.conn
)
else:
required_args = [
'name',
'typtype'
]
for arg in required_args:
if arg not in data:
return "-- definition incomplete"
# Additional checks go here
# If type is composite then check if it has two members
if data and data[arg] == 'c':
if len(data['composite']) < 2:
return "-- definition incomplete"
# If type is range then check if subtype is defined or not
if data and data[arg] == 'r':
if 'typname' not in data or data['typname'] is None:
return "-- definition incomplete"
# If type is external then check if input/output
# conversion function is defined
if data and data[arg] == 'b':
if (
'typinput' not in data or
'typoutput' not in data or
data['typinput'] is None or
data['typoutput'] is None
):
return "-- definition incomplete"
# Privileges
if 'typacl' in data and data['typacl'] is not None:
data['typacl'] = parse_priv_to_db(data['typacl'], self.acl)
data = self._convert_for_sql(data)
if 'composite' in data and len(data['composite']) > 0:
for each_type in data['composite']:
each_type = self.convert_length_precision_to_string(
each_type)
each_type['cltype'] = self._cltype_formatter(
each_type['type'])
each_type['hasSqrBracket'] = self.hasSqrBracket
SQL = render_template("/".join([self.template_path,
'create.sql']),
data=data, conn=self.conn, is_sql=is_sql)
return SQL, data['name'] if 'name' in data else old_data['name']
def sql(self,
gid,
sid,
did,
scid,
pkgid,
diff_schema=None,
json_resp=True):
"""
This function will generate sql for sql panel
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
pkgid: Package ID
diff_schema: Schema diff target schema name
json_resp: json response or plain text response
"""
try:
sql = render_template("/".join(
[self.template_path, 'properties.sql']),
scid=scid,
pkgid=pkgid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
errormsg=_("Could not find the package in the database."))
res['rows'][0]['pkgheadsrc'] = self.get_inner(
res['rows'][0]['pkgheadsrc'])
res['rows'][0]['pkgbodysrc'] = self.get_inner(
res['rows'][0]['pkgbodysrc'])
sql = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
pkgid=pkgid)
status, rset1 = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=rset1)
for row in rset1['rows']:
priv = parse_priv_from_db(row)
res['rows'][0].setdefault(row['deftype'], []).append(priv)
result = res['rows'][0]
sql, name = self.getSQL(gid, sid, did, result, scid, pkgid, True,
diff_schema)
# Most probably this is due to error
if not isinstance(sql, str):
return sql
sql = sql.strip('\n').strip(' ')
# Return sql for schema diff
if not json_resp:
return sql
sql_header = u"-- Package: {0}.{1}\n\n-- ".format(
self.schema, result['name'])
sql_header += render_template("/".join(
[self.template_path, 'delete.sql']),
data=result)
sql_header += "\n\n"
sql = sql_header + sql
return ajax_response(response=sql)
except Exception as e:
return internal_server_error(errormsg=str(e))
def get_sql(self, gid, sid, data, scid, tid=None, is_sql=False):
"""
This function will genrate sql from model data
"""
if tid is not None:
for key in ['typacl']:
if key in data and data[key] is not None:
if 'added' in data[key]:
data[key]['added'] = parse_priv_to_db(
data[key]['added'], self.acl)
if 'changed' in data[key]:
data[key]['changed'] = parse_priv_to_db(
data[key]['changed'], self.acl)
if 'deleted' in data[key]:
data[key]['deleted'] = parse_priv_to_db(
data[key]['deleted'], self.acl)
if 'composite' in data and len(data['composite']) > 0:
for key in ['added', 'changed', 'deleted']:
if key in data['composite']:
for each_type in data['composite'][key]:
each_type = self. \
convert_length_precision_to_string(each_type)
if 'type' in each_type:
each_type['cltype'] = self._cltype_formatter(
each_type['type'])
each_type['hasSqrBracket'] = self.hasSqrBracket
SQL = render_template(
"/".join([self.template_path, 'properties.sql']),
scid=scid,
tid=tid,
datlastsysoid=self.datlastsysoid,
show_system_objects=self.blueprint.show_system_objects)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
gettext("Could not find the type in the database."))
# Making copy of output for future use
old_data = dict(res['rows'][0])
SQL = render_template("/".join([self.template_path, 'acl.sql']),
scid=scid,
tid=tid)
status, acl = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=acl)
# We will set get privileges from acl sql so we don't need
# it from properties sql
old_data['typacl'] = []
for row in acl['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in old_data:
old_data[row['deftype']].append(priv)
else:
old_data[row['deftype']] = [priv]
# Calling function to check and additional properties if available
old_data.update(self.additional_properties(old_data, tid))
old_data = self._convert_for_sql(old_data)
SQL = render_template("/".join([self.template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=self.conn)
else:
required_args = ['name', 'typtype']
for arg in required_args:
if arg not in data:
return "-- definition incomplete"
# Additional checks go here
# If type is composite then check if it has two members
if data and data[arg] == 'c':
if len(data['composite']) < 2:
return "-- definition incomplete"
# If type is range then check if subtype is defined or not
if data and data[arg] == 'r':
if 'typname' not in data or data['typname'] is None:
return "-- definition incomplete"
# If type is external then check if input/output
# conversion function is defined
if data and data[arg] == 'b':
if ('typinput' not in data or 'typoutput' not in data
or data['typinput'] is None
or data['typoutput'] is None):
return "-- definition incomplete"
# Privileges
if 'typacl' in data and data['typacl'] is not None:
data['typacl'] = parse_priv_to_db(data['typacl'], self.acl)
data = self._convert_for_sql(data)
if 'composite' in data and len(data['composite']) > 0:
for each_type in data['composite']:
each_type = self.convert_length_precision_to_string(
each_type)
each_type['cltype'] = self._cltype_formatter(
each_type['type'])
each_type['hasSqrBracket'] = self.hasSqrBracket
SQL = render_template("/".join([self.template_path, 'create.sql']),
data=data,
conn=self.conn,
is_sql=is_sql)
return SQL, data['name'] if 'name' in data else old_data['name']
def sql(self, gid, sid, did, fid):
"""
This function will generate sql to show it in sql pane
for the selected foreign data wrapper node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
fid: Foreign data wrapper ID
"""
sql = render_template("/".join([self.template_path, 'properties.sql']),
fid=fid,
conn=self.conn)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
gettext(
"Could not find the foreign data wrapper on the server."))
is_valid_options = False
if res['rows'][0]['fdwoptions'] is not None:
res['rows'][0]['fdwoptions'] = tokenize_options(
res['rows'][0]['fdwoptions'], 'fdwoption', 'fdwvalue')
if len(res['rows'][0]['fdwoptions']) > 0:
is_valid_options = True
sql = render_template("/".join([self.template_path, 'acl.sql']),
fid=fid)
status, fdw_acl_res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=fdw_acl_res)
for row in fdw_acl_res['rows']:
privilege = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(privilege)
else:
res['rows'][0][row['deftype']] = [privilege]
# To format privileges
if 'fdwacl' in res['rows'][0]:
res['rows'][0]['fdwacl'] = parse_priv_to_db(
res['rows'][0]['fdwacl'], ['U'])
sql = ''
sql = render_template("/".join([self.template_path, 'create.sql']),
data=res['rows'][0],
conn=self.conn,
is_valid_options=is_valid_options)
sql += "\n"
sql_header = u"""-- Foreign Data Wrapper: {0}\n\n""".format(
res['rows'][0]['name'])
sql_header += """-- DROP FOREIGN DATA WRAPPER {0}
""".format(self.qtIdent(self.conn, res['rows'][0]['name']))
sql = sql_header + sql
return ajax_response(response=sql.strip('\n'))
def _fetch_properties(self, did, lid):
"""
This function fetch the properties of the extension.
:param did:
:param lid:
:return:
"""
sql = render_template(
"/".join([self.template_path, self._PROPERTIES_SQL]),
lid=lid
)
status, res = self.conn.execute_dict(sql)
if not status:
return False, internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return False, gone(self._NOT_FOUND_LANG_INFORMATION)
res['rows'][0]['is_sys_obj'] = (
res['rows'][0]['oid'] <= self.datlastsysoid)
sql = render_template(
"/".join([self.template_path, self._ACL_SQL]),
lid=lid
)
status, result = self.conn.execute_dict(sql)
if not status:
return False, internal_server_error(errormsg=result)
# if no acl found then by default add public
if res['rows'][0]['acl'] is None:
res['rows'][0]['lanacl'] = dict()
res['rows'][0]['lanacl']['grantee'] = 'PUBLIC'
res['rows'][0]['lanacl']['grantor'] = res['rows'][0]['lanowner']
res['rows'][0]['lanacl']['privileges'] = [
{
'privilege_type': 'U',
'privilege': True,
'with_grant': False
}
]
else:
for row in result['rows']:
priv = parse_priv_from_db(row)
if row['deftype'] in res['rows'][0]:
res['rows'][0][row['deftype']].append(priv)
else:
res['rows'][0][row['deftype']] = [priv]
seclabels = []
if 'seclabels' in res['rows'][0] and \
res['rows'][0]['seclabels'] is not None:
import re
for sec in res['rows'][0]['seclabels']:
sec = re.search(r'([^=]+)=(.*$)', sec)
seclabels.append({
'provider': sec.group(1),
'label': sec.group(2)
})
res['rows'][0]['seclabels'] = seclabels
return True, res['rows'][0]