def sql(self, gid, sid, did, eid):
"""
This function will generate sql for the sql panel
"""
SQL = render_template("/".join(
[self.template_path, 'properties.sql']
), eid=eid)
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 extension on the server.")
)
result = res['rows'][0]
SQL = render_template("/".join(
[self.template_path, 'create.sql']
),
data=result,
conn=self.conn,
display_comments=True
)
return ajax_response(response=SQL)
def sql(self, gid, sid, did, scid, pkgid, varid=None):
"""
Returns the SQL for the Function object.
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
pkgid: Package Id
varid: variable Id
"""
SQL = render_template(
"/".join([self.sql_template_path, 'properties.sql']),
varid=varid,
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=gettext("Could not find the variables")
)
var = res['rows'][0]
sql = u"-- Package Variable: {}".format(var['name'])
sql += u"\n\n"
sql += u"{} {};".format(var['name'], var['datatype'])
return ajax_response(response=sql)
def nodes(self, gid=None):
"""Return a JSON document listing the server groups for the user"""
nodes = []
if gid is None:
groups = ServerGroup.query.filter_by(user_id=current_user.id)
for group in groups:
nodes.append(
self.blueprint.generate_browser_node(
"%d" % group.id,
None,
group.name,
"icon-%s" % self.node_type,
True,
self.node_type
)
)
else:
group = ServerGroup.query.filter_by(user_id=current_user.id,
id=gid).first()
if not group:
return gone(
errormsg=gettext("Could not find the server group.")
)
nodes = self.blueprint.generate_browser_node(
"%d" % (group.id), None,
group.name,
"icon-%s" % self.node_type,
True,
self.node_type
)
return make_json_response(data=nodes)
def properties(self, gid, sid, did, scid, coid):
"""
This function will show the properties of the selected
catalog objects node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
scid: Schema ID
coid: Catalog object ID
Returns:
JSON of selected catalog objects node
"""
SQL = render_template(
"/".join([self.template_path, 'properties.sql']),
scid=scid, coid=coid
)
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 specified catalog object."""))
return ajax_response(
response=res['rows'][0],
status=200
)
def getSQL(self, gid, sid, data, did, eid=None):
"""
This function will generate sql from model data
"""
required_args = [
'name'
]
if eid is not None:
SQL = render_template("/".join(
[self.template_path, 'properties.sql']
), eid=eid)
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 extension information.")
)
old_data = res['rows'][0]
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)
return SQL, data['name'] if 'name' in data else old_data['name']
else:
SQL = render_template("/".join(
[self.template_path, 'create.sql']
), data=data)
return SQL, data['name']
def sql(self, gid, sid, did, scid, tid):
"""
This function will creates reverse engineered sql for
the table object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
"""
main_sql = []
SQL = render_template(
"/".join([self.table_template_path, 'properties.sql']),
did=did, scid=scid, tid=tid,
datlastsysoid=self.datlastsysoid
)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(gettext("The specified table could not be found."))
data = res['rows'][0]
return BaseTableView.get_reverse_engineered_sql(
self, did, scid, tid, main_sql, data)
def node(self, gid, sid, did, scid, coid):
"""
This function will fetch properties of catalog objects node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
coid: Catalog object ID
Returns:
JSON of given catalog objects child node
"""
SQL = render_template(
"/".join([self.template_path, 'nodes.sql']), coid=coid
)
status, rset = self.conn.execute_2darray(SQL)
if not status:
return internal_server_error(errormsg=rset)
for row in rset['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['oid'],
scid,
row['name'],
icon="icon-catalog_object"
),
status=200
)
return gone(
errormsg=gettext("Could not find the specified catalog object."))
def node(self, gid, sid, did, lid):
"""
This function will fetch properties of the language nodes.
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, result = self.conn.execute_2darray(sql)
if not status:
return internal_server_error(errormsg=result)
for row in result['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['oid'],
did,
row['name'],
icon="icon-language"
),
status=200
)
return gone(gettext("Could not find the specified language."))
def node(self, gid, sid, did, scid, doid):
"""
This function will fetch the properties of the domain node.
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
doid: Domain Id
"""
SQL = render_template("/".join([self.template_path, 'node.sql']),
doid=doid)
status, rset = self.conn.execute_2darray(SQL)
if not status:
return internal_server_error(errormsg=rset)
for row in rset['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['oid'],
scid,
row['name'],
icon="icon-domain"
),
status=200
)
return gone(gettext("Could not find the specified domain."))
def node(self, gid, sid, did):
SQL = render_template(
"/".join([self.template_path, 'nodes.sql']),
did=did, conn=self.conn, last_system_oid=0
)
status, rset = self.conn.execute_2darray(SQL)
if not status:
return internal_server_error(errormsg=rset)
for row in rset['rows']:
db = row['name']
if self.manager.db == db:
connected = True
else:
conn = self.manager.connection(row['name'])
connected = conn.connected()
icon_css_class = "pg-icon-database"
if not connected:
icon_css_class = "icon-database-not-connected"
return make_json_response(
data=self.blueprint.generate_browser_node(
row['did'],
sid,
row['name'],
icon=icon_css_class,
connected=connected,
spcname=row['spcname'],
allowConn=row['datallowconn'],
canCreate=row['cancreate']
),
status=200
)
return gone(errormsg=_("Could not find the database on the server."))
def get_sql(self, gid, sid, data, did=None):
SQL = ''
if did is not None:
# Fetch the name of database for comparison
conn = self.manager.connection()
status, rset = conn.execute_dict(
render_template(
"/".join([self.template_path, 'nodes.sql']),
did=did, conn=conn, last_system_oid=0
)
)
if not status:
return False, internal_server_error(errormsg=rset)
if len(rset['rows']) == 0:
return gone(
_("Could not find the database on the server.")
)
data['old_name'] = (rset['rows'][0])['name']
if 'name' not in data:
data['name'] = data['old_name']
SQL = ''
for action in ["rename_database", "tablespace"]:
SQL += self.get_offline_sql(gid, sid, data, did, action)
SQL += self.get_online_sql(gid, sid, data, did)
else:
SQL += self.get_new_sql(gid, sid, data, did)
return True, SQL
def sql(self, gid, sid, did, scid, syid):
"""
This function will generates reverse engineered sql for synonym object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
syid: Synonym ID
"""
SQL = render_template("/".join([self.template_path,
'properties.sql']),
scid=scid, syid=syid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) > 0:
data = res['rows'][0]
else:
return gone(
gettext('The specified synonym could not be found.')
)
SQL = render_template("/".join([self.template_path,
'create.sql']),
data=data, conn=self.conn, comment=True)
return ajax_response(response=SQL)
def wrapped(self, *args, **kwargs):
self.manager = get_driver(
PG_DEFAULT_DRIVER
).connection_manager(
kwargs['sid']
)
if self.manager is None:
return gone(errormsg="Could not find the server.")
if action and action in ["drop"]:
self.conn = self.manager.connection()
elif 'did' in kwargs:
self.conn = self.manager.connection(did=kwargs['did'])
self.db_allow_connection = True
# If connection to database is not allowed then
# provide generic connection
if kwargs['did'] in self.manager.db_info:
self._db = self.manager.db_info[kwargs['did']]
if self._db['datallowconn'] is False:
self.conn = self.manager.connection()
self.db_allow_connection = False
else:
self.conn = self.manager.connection()
# set template path for sql scripts
self.template_path = 'databases/sql/#{0}#'.format(
self.manager.version
)
return f(self, *args, **kwargs)
def node(self, gid, sid, did, scid, foid):
"""
Returns the Foreign Tables to generate the Nodes.
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
foid: Foreign Table Id
"""
SQL = render_template("/".join([self.template_path,
'node.sql']), foid=foid)
status, rset = self.conn.execute_2darray(SQL)
if not status:
return internal_server_error(errormsg=rset)
for row in rset['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['oid'],
scid,
row['name'],
icon="icon-foreign_table"
),
status=200
)
return gone(gettext(
'Could not find the specified foreign table.'
))
def sql(self, gid, sid, did, scid, tid):
"""
This function will reverse generate sql for sql panel
:param gid: group id
:param sid: server id
:param did: database id
:param scid: schema id
:param tid: fts tempate id
"""
sql = render_template(
"/".join([self.template_path, 'sql.sql']),
tid=tid,
scid=scid,
conn=self.conn
)
status, res = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(
gettext(
"Could not generate reversed engineered query for the "
"FTS Template.\n{0}").format(res)
)
if res is None:
return gone(
gettext(
"Could not generate reversed engineered query for "
"FTS Template node.")
)
return ajax_response(response=res)
def select_sql(self, gid, sid, did, scid, foid):
"""
SELECT script sql for the object
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
foid: Foreign Table Id
Returns:
SELECT Script sql for the object
"""
data = self._fetch_properties(gid, sid, did, scid, foid)
if data is False:
return gone(
gettext("Could not find the foreign table on the server.")
)
columns = []
for c in data['columns']:
columns.append(self.qtIdent(self.conn, c['attname']))
if len(columns) > 0:
columns = ", ".join(columns)
else:
columns = '*'
sql = u"SELECT {0}\n\tFROM {1};".format(
columns,
self.qtIdent(self.conn, data['basensp'], data['name'])
)
return ajax_response(response=sql)
def delete_sql(self, gid, sid, did, scid, foid):
"""
DELETE script sql for the object
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
foid: Foreign Table Id
Returns:
DELETE Script sql for the object
"""
data = self._fetch_properties(gid, sid, did, scid, foid)
if data is False:
return gone(
gettext("Could not find the foreign table on the server.")
)
sql = u"DELETE FROM {0}\n\tWHERE <condition>;".format(
self.qtIdent(self.conn, data['basensp'], data['name'])
)
return ajax_response(response=sql)
def properties(self, gid, sid, jid, jscid):
"""
This function will show the properties of the selected language node.
Args:
gid: Server Group ID
sid: Server ID
jid: Job ID
jscid: JobSchedule ID
"""
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
jscid=jscid, jid=jid
)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(
errormsg=gettext("Could not find the specified job step.")
)
return ajax_response(
response=res['rows'][0],
status=200
)
def node(self, gid, sid, did, scid, cfgid):
"""
Return FTS Configuration node to generate node
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
cfgid: fts Configuration id
"""
sql = render_template(
"/".join([self.template_path, 'nodes.sql']),
cfgid=cfgid
)
status, rset = self.conn.execute_2darray(sql)
if not status:
return internal_server_error(errormsg=rset)
if len(rset['rows']) == 0:
return gone(_("""
Could not find the FTS Configuration node.
"""))
for row in rset['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['oid'],
did,
row['name'],
icon="icon-fts_configuration"
),
status=200
)
def properties(self, gid, sid, did, scid, tid, ptid):
"""
This function will show the properties of the selected table node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
scid: Schema ID
tid: Table ID
ptid: Partition Table ID
Returns:
JSON of selected table node
"""
SQL = render_template("/".join([self.partition_template_path,
'properties.sql']),
did=did, scid=scid, tid=tid,
ptid=ptid, datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(gettext(
"The specified partitioned table could not be found."))
return super(PartitionsView, self).properties(
gid, sid, did, scid, ptid, res)
def node(self, gid, sid, did, fid):
"""
This function will fetch properties of 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']),
conn=self.conn, fid=fid)
status, r_set = self.conn.execute_2darray(sql)
if not status:
return internal_server_error(errormsg=r_set)
for row in r_set['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['fdwoid'],
did,
row['name'],
icon="icon-foreign_data_wrapper"
),
status=200
)
return gone(gettext("Could not find the specified"
" foreign data wrapper."))
def properties(self, gid, sid, did, etid):
"""
This function is used to list all the event trigger
nodes within that collection.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
etid: Event trigger ID
Returns:
"""
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
etid=etid, 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 event trigger information.")
)
result = res['rows'][0]
result = self._formatter(result)
return ajax_response(
response=result,
status=200
)
def properties(self, gid, sid, did, scid, pkgid, edbfnid=None):
"""
Returns the Function properties.
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
pkgid: Package Id
edbfnid: Function Id
"""
SQL = render_template("/".join([self.sql_template_path,
'properties.sql']),
pkgid=pkgid, edbfnid=edbfnid)
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 function in the database.")
)
resp_data = res['rows'][0]
# Get formatted Arguments
frmtd_params, frmtd_proargs = self._format_arguments_from_db(resp_data)
resp_data.update(frmtd_params)
resp_data.update(frmtd_proargs)
return ajax_response(
response=resp_data,
status=200
)
def properties(self, gid, sid, did, cid):
"""
This function will show the properties of the selected cast node
:param gid: group id
:param sid: server id
:param did: database id
:param cid: cast id
:return:
"""
last_system_oid = (self.manager.db_info[did])['datlastsysoid'] if \
self.manager.db_info is not None and \
did in self.manager.db_info else 0
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
cid=cid,
datlastsysoid=last_system_oid,
showsysobj=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 cast information.")
)
return ajax_response(
response=res['rows'][0],
status=200
)
def sql(self, gid, sid, did, cid):
"""
This function will generate sql for sql panel
:param gid: group id
:param sid: server id
:param did: database id
:param cid: cast id
:return:
"""
try:
sql = render_template(
"/".join([self.template_path, 'sql.sql']),
cid=cid,
conn=self.conn
)
status, res = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(
_("Could not generate reversed engineered SQL for the "
"cast.\n\n{0}").format(res)
)
if res is None:
return gone(
_("Could not generate reversed engineered SQL for the "
"cast node.\n")
)
return ajax_response(response=res)
except Exception as e:
return internal_server_error(errormsg=str(e))
def sql(self, gid, sid, did, scid, tid, trid):
"""
This function will generates reverse engineered sql for trigger object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
trid: Trigger ID
"""
SQL = render_template("/".join([self.template_path,
'properties.sql']),
tid=tid, trid=trid,
datlastsysoid=self.datlastsysoid)
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 trigger in the table."""))
data = dict(res['rows'][0])
# Adding parent into data dict, will be using it while creating sql
data['schema'] = self.schema
data['table'] = self.table
data = self.get_trigger_function_schema(data)
if len(data['custom_tgargs']) > 1:
# We know that trigger has more than 1 argument, let's join them
data['tgargs'] = self._format_args(data['custom_tgargs'])
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = self._column_details(tid, columns)
data = self._trigger_definition(data)
SQL, name = self.get_sql(scid, tid, None, data)
sql_header = u"-- Trigger: {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.strip('\n')
# If trigger is disbaled then add sql code for the same
if not data['is_enable_trigger']:
SQL += '\n\n'
SQL += render_template("/".join([self.template_path,
'enable_disable_trigger.sql']),
data=data, conn=self.conn)
return ajax_response(response=SQL)
def properties(self, gid, sid, did, scid, dcid):
"""
Show properties of FTS Dictionary node
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
dcid: fts dictionary id
"""
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
scid=scid,
dcid=dcid
)
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 FTS Dictionary node.
"""))
if res['rows'][0]['options'] is not None:
res['rows'][0]['options'] = self.tokenize_options(res['rows'][0]['options'])
return ajax_response(
response=res['rows'][0],
status=200
)
def node(self, gid, sid, did, etid):
"""
This function will fetch properties of trigger node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
etid: Event trigger ID
Returns:
Json object of trigger node
"""
sql = render_template("/".join([self.template_path, 'nodes.sql']),
etid=etid)
status, res = self.conn.execute_2darray(sql)
if not status:
return internal_server_error(errormsg=res)
for row in res['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['oid'],
did,
row['name'],
icon="icon-%s" % self.node_type
),
status=200
)
return gone(gettext("Could not find the specified event trigger."))
def properties(self, gid, sid, did, scid, doid, coid):
"""
Returns the Domain Constraints property.
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
doid: Domain Id
coid: Domain Constraint Id
"""
SQL = render_template("/".join([self.template_path,
'properties.sql']),
doid=doid, coid=coid)
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 specified domain constraint."
)
)
data = res['rows'][0]
return ajax_response(
response=data,
status=200
)
def node(self, gid, sid, rid):
status, rset = self.conn.execute_2darray(
render_template(
self.sql_path + 'nodes.sql',
rid=rid
)
)
if not status:
return internal_server_error(
_(
"Error fetching role information from the database server.\n{0}"
).format(rset)
)
for row in rset['rows']:
return make_json_response(
data=self.blueprint.generate_browser_node(
row['oid'], sid,
row['rolname'],
'icon-role' if row['rolcanlogin'] else 'icon-group',
can_login=row['rolcanlogin'],
is_superuser=row['rolsuper']
),
status=200
)
return gone(_("Could not find the role information."))
def statistics(self, gid, sid, did, scid, tid, idx=None):
"""
Statistics
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
tid: Table Id
idx: Index Id
Returns the statistics for a particular object if idx is specified
else return all indexes
"""
if idx is not None:
# Individual index
# Check if pgstattuple extension is already created?
# if created then only add extended stats
status, is_pgstattuple = self.conn.execute_scalar("""
SELECT (count(extname) > 0) AS is_pgstattuple
FROM pg_extension
WHERE extname='pgstattuple'
""")
if not status:
return internal_server_error(errormsg=is_pgstattuple)
if is_pgstattuple:
# Fetch index details only if extended stats available
SQL = render_template(
"/".join([self.template_path, self._PROPERTIES_SQL]),
did=did, tid=tid, idx=idx,
datlastsysoid=self.datlastsysoid
)
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 index in the table.""")
)
data = dict(res['rows'][0])
index = data['name']
else:
index = None
status, res = self.conn.execute_dict(
render_template(
"/".join([self.template_path, 'stats.sql']),
conn=self.conn, schema=self.schema,
index=index, idx=idx, is_pgstattuple=is_pgstattuple
)
)
else:
status, res = self.conn.execute_dict(
render_template(
"/".join([self.template_path, 'coll_stats.sql']),
conn=self.conn, schema=self.schema,
table=self.table
)
)
if not status:
return internal_server_error(errormsg=res)
return make_json_response(
data=res,
status=200
)
def sql(self, gid, sid, jid):
"""
This function will generate sql for sql panel
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']),
jid=jid,
conn=self.conn,
last_system_oid=0)
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 object on the server."))
row = res['rows'][0]
status, res = self.conn.execute_dict(
render_template(
"/".join([self.template_path, 'steps.sql']),
jid=jid,
conn=self.conn,
has_connstr=self.manager.db_info['pgAgent']['has_connstr']))
if not status:
return internal_server_error(errormsg=res)
row['jsteps'] = res['rows']
status, res = self.conn.execute_dict(
render_template("/".join([self.template_path, 'schedules.sql']),
jid=jid,
conn=self.conn))
if not status:
return internal_server_error(errormsg=res)
row['jschedules'] = res['rows']
for schedule in row['jschedules']:
schedule['jscexceptions'] = []
if schedule['jexid']:
idx = 0
for exc in schedule['jexid']:
schedule['jscexceptions'].append({
'jexid':
exc,
'jexdate':
schedule['jexdate'][idx],
'jextime':
schedule['jextime'][idx]
})
idx += 1
del schedule['jexid']
del schedule['jexdate']
del schedule['jextime']
return ajax_response(response=render_template(
"/".join([self.template_path, 'create.sql']),
jid=jid,
data=row,
conn=self.conn,
fetch_id=False,
has_connstr=self.manager.db_info['pgAgent']['has_connstr']))
def get_sql(self, gid, sid, did, scid, data, dcid=None):
"""
This function will return SQL for model data
:param gid: group id
:param sid: server id
:param did: database id
:param scid: schema id
:param dcid: fts dictionary id
"""
# Fetch sql for update
if dcid is not None:
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
dcid=dcid,
scid=scid
)
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 FTS Dictionary node."))
old_data = res['rows'][0]
# Handle templates and its schema name properly
if old_data['template_schema'] is not None:
if old_data['template_schema'] != "pg_catalog":
old_data['template'] = self.qtIdent(
self.conn, old_data['template_schema'],
old_data['template']
)
# If user has changed the schema then fetch new schema directly
# using its oid otherwise fetch old schema name using its oid
sql = render_template(
"/".join([self.template_path, 'schema.sql']),
data=data)
status, new_schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=new_schema)
# Replace schema oid with schema name
new_data = data.copy()
if 'schema' in new_data:
new_data['schema'] = new_schema
# Fetch old schema name using old schema oid
sql = render_template(
"/".join([self.template_path, 'schema.sql']),
data=old_data)
status, old_schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=old_schema)
# Replace old schema oid with old schema name
old_data['schema'] = old_schema
sql = render_template(
"/".join([self.template_path, 'update.sql']),
data=new_data, o_data=old_data
)
# Fetch sql query for modified data
return sql.strip('\n'), data['name'] if 'name' in data else old_data['name']
else:
# Fetch schema name from schema oid
sql = render_template("/".join([self.template_path, 'schema.sql']),
data=data)
status, schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=schema)
# Replace schema oid with schema name
new_data = data.copy()
new_data['schema'] = schema
if 'template' in new_data and \
'name' in new_data and \
'schema' in new_data:
sql = render_template("/".join([self.template_path,
'create.sql']),
data=new_data,
conn=self.conn
)
else:
sql = u"-- definition incomplete"
return sql.strip('\n'), 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 = u"""-- Foreign Server: {0}
-- DROP SERVER {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 get_sql(self, gid, sid, data, did, fid, fsid=None):
"""
This function will generate sql from model data.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
data: Contains the data of the selected foreign server node
fid: foreign data wrapper ID
fsid: foreign server ID
"""
if fsid is not None:
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)
elif len(res['rows']) == 0:
return gone(self.not_found_error_msg())
elif res['rows'][0]['fsrvoptions'] is not None:
res['rows'][0]['fsrvoptions'] = tokenize_options(
res['rows'][0]['fsrvoptions'], 'fsrvoption', 'fsrvvalue')
ForeignServerView._get_fsvr_acl(data)
old_data = res['rows'][0]
is_valid_added_options, \
is_valid_changed_options = \
ForeignServerView._validate_fsvr_options(data, old_data)
sql = render_template(
"/".join([self.template_path, self._UPDATE_SQL]),
data=data,
o_data=old_data,
is_valid_added_options=is_valid_added_options,
is_valid_changed_options=is_valid_changed_options,
conn=self.conn)
return sql.strip('\n'), \
data['name'] if 'name' in data else old_data['name']
else:
sql = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
fdwid=fid,
conn=self.conn)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
fdw_data = res['rows'][0]
ForeignServerView._parse_priv(data)
is_valid_options = False
if 'fsrvoptions' in data:
is_valid_options, data['fsrvoptions'] = validate_options(
data['fsrvoptions'], 'fsrvoption', 'fsrvvalue')
sql = render_template("/".join(
[self.template_path, self._CREATE_SQL]),
data=data,
fdwdata=fdw_data,
is_valid_options=is_valid_options,
conn=self.conn)
sql += "\n"
return sql, data['name']
def create(self, gid, sid, did, fid):
"""
This function will create the foreign server node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
fid: foreign data wrapper ID
"""
required_args = ['name']
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
for arg in required_args:
if arg not in data:
return make_json_response(
status=410,
success=0,
errormsg=gettext(
"Could not find the required parameter ({}).").format(
arg))
try:
if 'fsrvacl' in data:
data['fsrvacl'] = parse_priv_to_db(data['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)
if len(res1['rows']) == 0:
return gone(
gettext(
"The specified foreign server could not be found."))
fdw_data = res1['rows'][0]
is_valid_options = False
if 'fsrvoptions' in data:
is_valid_options, data['fsrvoptions'] = validate_options(
data['fsrvoptions'], 'fsrvoption', 'fsrvvalue')
sql = render_template("/".join(
[self.template_path, self._CREATE_SQL]),
data=data,
fdwdata=fdw_data,
is_valid_options=is_valid_options,
conn=self.conn)
status, res = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=res)
sql = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
data=data,
fdwdata=fdw_data,
conn=self.conn)
status, r_set = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=r_set)
return jsonify(node=self.blueprint.generate_browser_node(
r_set['rows'][0]['oid'],
fid,
r_set['rows'][0]['name'],
icon="icon-foreign_server"))
except Exception as e:
return internal_server_error(errormsg=str(e))
def wrapped(self, **kwargs):
self.manager = get_driver(
PG_DEFAULT_DRIVER).connection_manager(kwargs['sid'])
self.conn = self.manager.connection()
driver = get_driver(PG_DEFAULT_DRIVER)
self.qtIdent = driver.qtIdent
if not self.conn.connected():
return precondition_required(
_("Connection to the server has been lost."))
self.sql_path = 'roles/sql/#{0}#'.format(self.manager.version)
self.alterKeys = [
u'rolcanlogin', u'rolsuper', u'rolcreatedb',
u'rolcreaterole', u'rolinherit', u'rolreplication',
u'rolconnlimit', u'rolvaliduntil', u'rolpassword'
] if self.manager.version >= 90200 else [
u'rolcanlogin', u'rolsuper', u'rolcreatedb',
u'rolcreaterole', u'rolinherit', u'rolconnlimit',
u'rolvaliduntil', u'rolpassword'
]
check_permission = False
fetch_name = False
forbidden_msg = None
if action in ['drop', 'update']:
if 'rid' in kwargs:
fetch_name = True
check_permission = True
if action == 'drop':
forbidden_msg = _(
"The current user does not have permission to drop"
" the role.")
else:
forbidden_msg = _(
"The current user does not have permission to "
"update the role.")
elif action == 'create':
check_permission = True
forbidden_msg = _(
"The current user does not have permission to create "
"the role.")
elif action == 'msql' and 'rid' in kwargs:
fetch_name = True
if check_permission:
user = self.manager.user_info
if not user['is_superuser'] and \
not user['can_create_role']:
if action != 'update' or 'rid' in kwargs:
if kwargs['rid'] != -1:
if user['id'] != kwargs['rid']:
return forbidden(forbidden_msg)
if fetch_name:
status, res = self.conn.execute_dict(
render_template(self.sql_path + 'permission.sql',
rid=kwargs['rid'],
conn=self.conn))
if not status:
return internal_server_error(
_("Error retrieving the role information.\n{0}").
format(res))
if len(res['rows']) == 0:
return gone(
_("Could not find the role on the database "
"server."))
row = res['rows'][0]
self.role = row['rolname']
self.rolCanLogin = row['rolcanlogin']
self.rolCatUpdate = row['rolcatupdate']
self.rolSuper = row['rolsuper']
return f(self, **kwargs)
def sql(self, gid, sid, did, scid, tid, fkid=None):
"""
This function generates sql to show in the sql pane for the selected
foreign key.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
fkid: Foreign key constraint ID
Returns:
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']),
tid=tid,
conn=self.conn,
cid=fkid)
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 key."""))
data = res['rows'][0]
data['schema'] = self.schema
data['table'] = self.table
sql = render_template("/".join(
[self.template_path, 'get_constraint_cols.sql']),
tid=tid,
keys=zip(data['confkey'], data['conkey']),
confrelid=data['confrelid'])
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
columns = []
for row in res['rows']:
columns.append({
"local_column": row['conattname'],
"references": data['confrelid'],
"referenced": row['confattname']
})
data['columns'] = columns
SQL = render_template("/".join([self.template_path, 'get_parent.sql']),
tid=data['columns'][0]['references'])
status, res = self.conn.execute_2darray(SQL)
if not status:
return internal_server_error(errormsg=res)
data['remote_schema'] = res['rows'][0]['schema']
data['remote_table'] = res['rows'][0]['table']
SQL = render_template("/".join([self.template_path, 'create.sql']),
data=data)
sql_header = u"-- Constraint: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join(
[self.template_path, 'delete.sql']),
data=data)
sql_header += "\n"
SQL = sql_header + SQL
return ajax_response(response=SQL)
def properties(self, gid, sid, did, scid, tid, fkid=None):
"""
This function is used to list all the foreign key
nodes within that collection.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
fkid: Foreign key constraint ID
Returns:
"""
sql = render_template("/".join([self.template_path, 'properties.sql']),
tid=tid,
cid=fkid)
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 key constraint in the table."""
))
result = res['rows'][0]
sql = render_template("/".join(
[self.template_path, 'get_constraint_cols.sql']),
tid=tid,
keys=zip(result['confkey'], result['conkey']),
confrelid=result['confrelid'])
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
columns = []
cols = []
for row in res['rows']:
columns.append({
"local_column": row['conattname'],
"references": result['confrelid'],
"referenced": row['confattname']
})
cols.append(row['conattname'])
result['columns'] = columns
if fkid:
coveringindex = self.search_coveringindex(tid, cols)
result['coveringindex'] = coveringindex
if coveringindex:
result['autoindex'] = True
result['hasindex'] = True
else:
result['autoindex'] = False
result['hasindex'] = False
return ajax_response(response=result, status=200)
def create(self, gid, sid, did, scid, tid):
"""
This function will creates new the schema object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
"""
data = request.form if request.form else json.loads(
request.data, encoding='utf-8'
)
for k, v in data.items():
try:
# comments should be taken as is because if user enters a
# json comment it is parsed by loads which should not happen
if k in ('description',):
data[k] = v
else:
data[k] = json.loads(v, encoding='utf-8')
except (ValueError, TypeError, KeyError):
data[k] = v
required_args = {
'name': 'Name',
'columns': 'Columns'
}
is_error, err_msg = IndexesView._check_for_error(required_args, data)
if is_error:
return make_json_response(
status=410,
success=0,
errormsg=gettext(err_msg)
)
# Adding parent into data dict, will be using it while creating sql
data['schema'] = self.schema
data['table'] = self.table
if len(data['table']) == 0:
return gone(gettext("The specified table could not be found."))
try:
# Start transaction.
self.conn.execute_scalar("BEGIN;")
SQL = render_template(
"/".join([self.template_path, self._CREATE_SQL]),
data=data, conn=self.conn, mode='create'
)
status, res = self.conn.execute_scalar(SQL)
if not status:
# End transaction.
self.conn.execute_scalar("END;")
return internal_server_error(errormsg=res)
# If user chooses concurrent index then we cannot run it along
# with other alter statements so we will separate alter index part
SQL = render_template(
"/".join([self.template_path, 'alter.sql']),
data=data, conn=self.conn
)
SQL = SQL.strip('\n').strip(' ')
if SQL != '':
status, res = self.conn.execute_scalar(SQL)
if not status:
# End transaction.
self.conn.execute_scalar("END;")
return internal_server_error(errormsg=res)
# we need oid to to add object in tree at browser
SQL = render_template(
"/".join([self.template_path, self._OID_SQL]),
tid=tid, data=data
)
status, idx = self.conn.execute_scalar(SQL)
if not status:
# End transaction.
self.conn.execute_scalar("END;")
return internal_server_error(errormsg=tid)
# End transaction.
self.conn.execute_scalar("END;")
return jsonify(
node=self.blueprint.generate_browser_node(
idx,
tid,
data['name'],
icon="icon-index"
)
)
except Exception as e:
# End transaction.
self.conn.execute_scalar("END;")
return internal_server_error(errormsg=str(e))
def delete(self, gid, sid, did, scid, coid=None, only_sql=False):
"""
This function will delete existing the collation object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
coid: Collation ID
only_sql: Return only sql if True
"""
if coid is None:
data = request.form if request.form else json.loads(
request.data, encoding='utf-8')
else:
data = {'ids': [coid]}
# Below will decide if it's simple drop or drop with cascade call
if self.cmd == 'delete':
# This is a cascade operation
cascade = True
else:
cascade = False
try:
for coid in data['ids']:
SQL = render_template("/".join(
[self.template_path, 'get_name.sql']),
scid=scid,
coid=coid)
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 collation object in the database."
))
data = res['rows'][0]
SQL = render_template("/".join(
[self.template_path, 'delete.sql']),
name=data['name'],
nspname=data['schema'],
cascade=cascade,
conn=self.conn)
# Used for schema diff tool
if only_sql:
return SQL
status, res = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
return make_json_response(success=1,
info=gettext("Collation dropped"))
except Exception as e:
return internal_server_error(errormsg=str(e))
def get_sql(self, data, tid, fkid=None):
"""
This function will generate sql from model data.
Args:
data: Contains the data of the selected foreign key constraint.
tid: Table ID.
fkid: Foreign key constraint ID
Returns:
"""
if fkid is not None:
sql = render_template("/".join(
[self.template_path, 'properties.sql']),
tid=tid,
cid=fkid)
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 key."""))
old_data = res['rows'][0]
required_args = ['name']
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)
if 'autoindex' in data and data['autoindex'] and \
('coveringindex' in data and data['coveringindex'] != ''):
col_sql = render_template(
"/".join([self.template_path, 'get_constraint_cols.sql']),
tid=tid,
keys=zip(old_data['confkey'], old_data['conkey']),
confrelid=old_data['confrelid'])
status, res = self.conn.execute_dict(col_sql)
if not status:
return internal_server_error(errormsg=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(
[self.template_path, 'create_index.sql']),
data=data,
conn=self.conn)
else:
required_args = ['columns']
for arg in required_args:
if arg not in data:
return _('-- definition incomplete')
elif isinstance(data[arg], list) and len(data[arg]) < 1:
return _('-- definition incomplete')
if data['autoindex'] and \
('coveringindex' not in data or
data['coveringindex'] == ''):
return _('-- definition incomplete')
SQL = render_template("/".join(
[self.template_path, 'get_parent.sql']),
tid=data['columns'][0]['references'])
status, rset = self.conn.execute_2darray(SQL)
if not status:
return internal_server_error(errormsg=rset)
data['remote_schema'] = rset['rows'][0]['schema']
data['remote_table'] = rset['rows'][0]['table']
sql = render_template("/".join([self.template_path, 'create.sql']),
data=data,
conn=self.conn)
if data['autoindex']:
sql += render_template("/".join(
[self.template_path, 'create_index.sql']),
data=data,
conn=self.conn)
return sql, data['name'] if 'name' in data else old_data['name']
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, did, scid, data, foid=None):
"""
Genrates the SQL statements to create/update the Foreign Table.
Args:
gid: Server Group Id
sid: Server Id
did: Database Id
scid: Schema Id
foid: Foreign Table Id
"""
try:
if foid is not None:
old_data = self._fetch_properties(gid, sid, did, scid, foid,
inherits=True)
if not old_data:
return gone(gettext("""
Could not find the foreign table in the database.
It may have been removed by another user or
shifted to the another schema.
"""))
# Prepare dict of columns with key = column's attnum
# Will use this in the update template when any column is
# changed, to identify the columns.
col_data = {}
for c in old_data['columns']:
col_data[c['attnum']] = c
old_data['columns'] = col_data
if 'columns' in data and 'added' in data['columns']:
data['columns']['added'] = self._format_columns(
data['columns']['added'])
if 'columns' in data and 'changed' in data['columns']:
data['columns']['changed'] = self._format_columns(
data['columns']['changed'])
# Parse Column Options
for c in data['columns']['changed']:
old_col_options = c['attfdwoptions'] if ('attfdwoptions' in c and c['attfdwoptions']) else []
old_col_frmt_options = {}
for o in old_col_options:
col_opt = o.split("=")
old_col_frmt_options[col_opt[0]] = col_opt[1]
c['coloptions_updated'] = {'added': [],
'changed': [],
'deleted': []}
if 'coloptions' in c and len(c['coloptions']) > 0:
for o in c['coloptions']:
if o['option'] in old_col_frmt_options and o['value'] != old_col_frmt_options[
o['option']]:
c['coloptions_updated']['changed'].append(o)
elif o['option'] not in old_col_frmt_options:
c['coloptions_updated']['added'].append(o)
if o['option'] in old_col_frmt_options:
del old_col_frmt_options[o['option']]
for o in old_col_frmt_options:
c['coloptions_updated']['deleted'].append({'option': o})
# Parse Privileges
if 'acl' in data and 'added' in data['acl']:
data['acl']['added'] = parse_priv_to_db(data['acl']['added'],
["a", "r", "w", "x"])
if 'acl' in data and 'changed' in data['acl']:
data['acl']['changed'] = parse_priv_to_db(
data['acl']['changed'], ["a", "r", "w", "x"])
if 'acl' in data and 'deleted' in data['acl']:
data['acl']['deleted'] = parse_priv_to_db(
data['acl']['deleted'], ["a", "r", "w", "x"])
SQL = render_template(
"/".join([self.template_path, 'update.sql']),
data=data, o_data=old_data
)
else:
data['columns'] = self._format_columns(data['columns'])
# Parse Privileges
if 'acl' in data:
data['acl'] = parse_priv_to_db(data['acl'],
["a", "r", "w", "x"])
SQL = render_template("/".join([self.template_path,
'create.sql']), data=data)
return True, SQL
except Exception as e:
return False, e
def sql(self, gid, sid, did, scid, tid, cid=None):
"""
This function generates sql to show in the sql pane for the selected
primary key.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
cid: Primary key constraint ID
Returns:
"""
SQL = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
did=did,
tid=tid,
conn=self.conn,
cid=cid,
constraint_type=self.constraint_type)
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 {} in the table.""").format(
_("primary key") if self.constraint_type ==
"p" else _("unique key")))
data = res['rows'][0]
data['schema'] = self.schema
data['table'] = self.table
sql = render_template("/".join(
[self.template_path, 'get_constraint_cols.sql']),
cid=cid,
colcnt=data['col_count'])
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
columns = []
for row in res['rows']:
columns.append({"column": row['column'].strip('"')})
data['columns'] = columns
# Add Include details of the index supported for PG-11+
if self.manager.version >= 110000:
sql = render_template("/".join(
[self.template_path, 'get_constraint_include.sql']),
cid=cid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
data['include'] = [col['colname'] for col in res['rows']]
SQL = render_template("/".join([self.template_path, self._CREATE_SQL]),
data=data,
constraint_name=self.constraint_name)
sql_header = u"-- Constraint: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join(
[self.template_path, self._DELETE_SQL]),
data=data)
sql_header += "\n"
SQL = sql_header + SQL
return ajax_response(response=SQL)
def get_sql(self, gid, sid, did, scid, data, cfgid=None):
"""
This function will return SQL for model data
:param gid: group id
:param sid: server id
:param did: database id
:param scid: schema id
:param cfgid: fts Configuration id
"""
# Fetch sql for update
if cfgid is not None:
sql = render_template("/".join(
[self.template_path, 'properties.sql']),
cfgid=cfgid,
scid=scid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
elif len(res['rows']) == 0:
return gone(_("Could not find the FTS Configuration node."))
old_data = res['rows'][0]
if 'schema' not in data:
data['schema'] = old_data['schema']
# If user has changed the schema then fetch new schema directly
# using its oid otherwise fetch old schema name using its oid
sql = render_template("/".join([self.template_path, 'schema.sql']),
data=data)
status, new_schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=new_schema)
new_data = data.copy()
# Replace schema oid with schema name
self._replace_schema_oid_with_schema_name(new_schema, new_data)
# Fetch old schema name using old schema oid
sql = render_template("/".join([self.template_path, 'schema.sql']),
data=old_data)
status, old_schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=old_schema)
# Replace old schema oid with old schema name
old_data['schema'] = old_schema
sql = render_template("/".join([self.template_path, 'update.sql']),
data=new_data,
o_data=old_data)
# Fetch sql query for modified data
if 'name' in data:
return sql.strip('\n'), data['name']
return sql.strip('\n'), old_data['name']
else:
# Fetch schema name from schema oid
sql = render_template("/".join([self.template_path, 'schema.sql']),
data=data)
status, schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=schema)
sql = self._get_sql_for_create(data, schema)
return sql.strip('\n'), data['name']
def create(self, gid, sid, did, scid, tid, cid=None):
"""
This function will create a primary key.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
cid: Check constraint ID
Returns:
"""
required_args = ['consrc']
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
for k, v in data.items():
try:
# comments should be taken as is because if user enters a
# json comment it is parsed by loads which should not happen
if k in ('comment', ):
data[k] = v
else:
data[k] = json.loads(v, encoding='utf-8')
except (ValueError, TypeError, KeyError):
data[k] = v
for arg in required_args:
if arg not in data or data[arg] == '':
return make_json_response(
status=400,
success=0,
errormsg=_("Could not find the required parameter ({})."
).format(arg))
data['schema'] = self.schema
data['table'] = self.table
# Checking whether the table is deleted via query tool
if len(data['table']) == 0:
return gone(_("The specified table could not be found."))
try:
if 'name' not in data or data['name'] == "":
SQL = "BEGIN;"
# Start transaction.
status, res = self.conn.execute_scalar(SQL)
if not status:
self.end_transaction()
return internal_server_error(errormsg=res)
# The below SQL will execute CREATE DDL only
SQL = render_template("/".join([self.template_path, 'create.sql']),
data=data)
status, msg = self.conn.execute_scalar(SQL)
if not status:
self.end_transaction()
return internal_server_error(errormsg=msg)
if 'name' not in data or data['name'] == "":
sql = render_template("/".join(
[self.template_path, 'get_oid_with_transaction.sql'], ),
tid=tid)
status, res = self.conn.execute_dict(sql)
if not status:
self.end_transaction()
return internal_server_error(errormsg=res)
self.end_transaction()
data['name'] = res['rows'][0]['name']
else:
sql = render_template("/".join(
[self.template_path, 'get_oid.sql']),
tid=tid,
name=data['name'])
status, res = self.conn.execute_dict(sql)
if not status:
self.end_transaction()
return internal_server_error(errormsg=res)
if "convalidated" in res['rows'][0] and \
res['rows'][0]["convalidated"]:
icon = "icon-check_constraint_bad"
valid = False
else:
icon = "icon-check_constraint"
valid = True
return jsonify(node=self.blueprint.generate_browser_node(
res['rows'][0]['oid'],
tid,
data['name'],
icon=icon,
valid=valid))
except Exception as e:
self.end_transaction()
return make_json_response(status=400, success=0, errormsg=e)
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]
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 properties(self, gid, sid, did, scid, tid):
"""
This function will show the properties of the selected table node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
scid: Schema ID
tid: Table ID
Returns:
JSON of selected table node
"""
SQL = render_template(
"/".join([self.table_template_path, 'properties.sql']),
did=did, scid=scid, tid=tid,
datlastsysoid=self.datlastsysoid
)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(gettext("The specified table could not be found."))
# We will check the threshold set by user before executing
# the query because that can cause performance issues
# with large result set
pref = Preferences.module('browser')
table_row_count_pref = pref.preference('table_row_count_threshold')
table_row_count_threshold = table_row_count_pref.get()
estimated_row_count = int(res['rows'][0].get('reltuples', 0))
# If estimated rows are greater than threshold then
if estimated_row_count and \
estimated_row_count > table_row_count_threshold:
res['rows'][0]['rows_cnt'] = str(table_row_count_threshold) + '+'
# If estimated rows is lower than threshold then calculate the count
elif estimated_row_count and \
table_row_count_threshold >= estimated_row_count:
SQL = render_template(
"/".join(
[self.table_template_path, 'get_table_row_count.sql']
), data=res['rows'][0]
)
status, count = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=count)
res['rows'][0]['rows_cnt'] = count
# If estimated_row_count is zero then set the row count with same
elif not estimated_row_count:
res['rows'][0]['rows_cnt'] = estimated_row_count
return super(TableView, self).properties(
gid, sid, did, scid, tid, res
)
def sql(self, gid, sid, did, scid, tid, trid):
"""
This function will generates reverse engineered sql for trigger object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
trid: Trigger ID
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=self.datlastsysoid)
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 trigger in the table."""))
data = dict(res['rows'][0])
# Adding parent into data dict, will be using it while creating sql
data['schema'] = self.schema
data['table'] = self.table
data = self.get_trigger_function_schema(data)
if len(data['custom_tgargs']) > 1:
# We know that trigger has more than 1 argument, let's join them
data['tgargs'] = self._format_args(data['custom_tgargs'])
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = self._column_details(tid, columns)
data = self._trigger_definition(data)
SQL, name = self.get_sql(scid, tid, None, data)
sql_header = u"-- Trigger: {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.strip('\n')
# If trigger is disbaled then add sql code for the same
if not data['is_enable_trigger']:
SQL += '\n\n'
SQL += render_template("/".join(
[self.template_path, 'enable_disable_trigger.sql']),
data=data,
conn=self.conn)
return ajax_response(response=SQL)
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 sql(self, gid, sid, did, scid, tid, exid=None):
"""
This function generates sql to show in the sql pane for the selected
Exclusion constraint.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
exid: Exclusion constraint ID
Returns:
"""
try:
SQL = render_template("/".join(
[self.template_path, 'properties.sql']),
did=did,
tid=tid,
conn=self.conn,
cid=exid)
status, result = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=result)
if len(result['rows']) == 0:
return gone(_("Could not find the exclusion constraint."))
data = result['rows'][0]
data['schema'] = self.schema
data['table'] = self.table
sql = render_template("/".join(
[self.template_path, 'get_constraint_cols.sql']),
cid=exid,
colcnt=data['col_count'])
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
columns = []
for row in res['rows']:
if row['options'] & 1:
order = False
nulls_order = True if (row['options'] & 2) else False
else:
order = True
nulls_order = True if (row['options'] & 2) else False
columns.append({
"column": row['coldef'].strip('"'),
"oper_class": row['opcname'],
"order": order,
"nulls_order": nulls_order,
"operator": row['oprname']
})
data['columns'] = columns
# Add Include details of the index supported for PG-11+
if self.manager.version >= 110000:
sql = render_template("/".join(
[self.template_path, 'get_constraint_include.sql']),
cid=exid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
data['include'] = [col['colname'] for col in res['rows']]
if not data['amname'] or data['amname'] == '':
data['amname'] = 'btree'
SQL = render_template("/".join([self.template_path, 'create.sql']),
data=data)
sql_header = u"-- Constraint: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join(
[self.template_path, 'delete.sql']),
data=data)
sql_header += "\n"
SQL = sql_header + SQL
return ajax_response(response=SQL)
except Exception as e:
return internal_server_error(errormsg=str(e))
def enable_disable_trigger(self, gid, sid, did, scid, tid, trid):
"""
This function will enable OR disable the current trigger object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
trid: Trigger ID
"""
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
# Convert str 'true' to boolean type
is_enable_flag = json.loads(data['enable'])
try:
SQL = render_template("/".join(
[self.template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=self.datlastsysoid)
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 trigger in the table."""))
o_data = dict(res['rows'][0])
# If enable is set to true means we need SQL to enable
# current trigger which is disabled already so we need to
# alter the 'is_enable_trigger' flag so that we can render
# correct SQL for operation
o_data['is_enable_trigger'] = is_enable_flag
# Adding parent into data dict, will be using it while creating sql
o_data['schema'] = self.schema
o_data['table'] = self.table
SQL = render_template("/".join(
[self.template_path, 'enable_disable_trigger.sql']),
data=o_data,
conn=self.conn)
status, res = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
return make_json_response(success=1,
info="Trigger updated",
data={
'id': trid,
'tid': tid,
'scid': scid
})
except Exception as e:
return internal_server_error(errormsg=str(e))
def sql(self, gid, sid, did, fid, json_resp=True):
"""
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
json_resp:
"""
sql = render_template("/".join(
[self.template_path, self._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, self._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, self._CREATE_SQL]),
data=res['rows'][0],
conn=self.conn,
is_valid_options=is_valid_options)
sql += "\n"
sql_header = """-- Foreign Data Wrapper: {0}\n\n""".format(
res['rows'][0]['name'])
sql_header += """-- DROP FOREIGN DATA WRAPPER IF EXISTS {0}
""".format(self.qtIdent(self.conn, res['rows'][0]['name']))
sql = sql_header + sql
if not json_resp:
return sql.strip('\n')
return ajax_response(response=sql.strip('\n'))
def update(self, gid, sid, did, scid, tid, trid):
"""
This function will updates existing the trigger object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
trid: Trigger ID
"""
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
try:
data['schema'] = self.schema
data['table'] = self.table
SQL, name = self.get_sql(scid, tid, trid, data)
if not isinstance(SQL, (str, unicode)):
return SQL
SQL = SQL.strip('\n').strip(' ')
status, res = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
# We need oid to add object in browser tree and if user
# update the trigger then new OID is getting generated
# so we need to return new OID of trigger.
SQL = render_template("/".join([self.template_path,
'get_oid.sql']),
tid=tid,
data=data)
status, new_trid = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=new_trid)
# Fetch updated properties
SQL = render_template("/".join(
[self.template_path, 'properties.sql']),
tid=tid,
trid=new_trid,
datlastsysoid=self.datlastsysoid)
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 trigger in the table."""))
# Making copy of output for future use
data = dict(res['rows'][0])
return jsonify(node=self.blueprint.generate_browser_node(
new_trid,
tid,
name,
icon="icon-%s" % self.node_type if data['is_enable_trigger']
else "icon-%s-bad" % self.node_type))
except Exception as e:
return internal_server_error(errormsg=str(e))
def create(self, gid, sid, did, scid, tid):
"""
This function will creates new the trigger object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
"""
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
for k, v in data.items():
try:
# comments should be taken as is because if user enters a
# json comment it is parsed by loads which should not happen
if k in ('description', ):
data[k] = v
else:
data[k] = json.loads(v, encoding='utf-8')
except (ValueError, TypeError, KeyError):
data[k] = v
required_args = {'name': 'Name', 'tfunction': 'Trigger function'}
for arg in required_args:
if arg not in data:
return make_json_response(
status=410,
success=0,
errormsg=gettext(
"Could not find the required parameter ({}).").format(
required_args[arg]))
# Adding parent into data dict, will be using it while creating sql
data['schema'] = self.schema
data['table'] = self.table
if len(data['table']) == 0:
return gone(self.not_found_error_msg())
try:
SQL = render_template("/".join(
[self.template_path, self._CREATE_SQL]),
data=data,
conn=self.conn)
status, res = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
# we need oid to add object in tree at browser
SQL = render_template("/".join([self.template_path,
self._OID_SQL]),
tid=tid,
data=data)
status, trid = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=tid)
return jsonify(node=self.blueprint.generate_browser_node(
trid, tid, data['name'], icon="icon-trigger"))
except Exception as e:
return internal_server_error(errormsg=str(e))
def statistics(self, gid, sid, did, scid, tid, cid):
"""
Statistics
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
cid: Primary key/Unique constraint ID
Returns the statistics for a particular object if cid is specified
"""
# Check if pgstattuple extension is already created?
# if created then only add extended stats
status, is_pgstattuple = self.conn.execute_scalar("""
SELECT (count(extname) > 0) AS is_pgstattuple
FROM pg_extension
WHERE extname='pgstattuple'
""")
if not status:
return internal_server_error(errormsg=is_pgstattuple)
if is_pgstattuple:
# Fetch index details only if extended stats available
sql = render_template(
"/".join([self.template_path, 'properties.sql']),
did=did,
tid=tid,
cid=cid,
constraint_type=self.constraint_type
)
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 {} in the table.""").format(
_("primary key") if self.constraint_type == "p"
else _("unique key")
)
)
result = res['rows'][0]
name = result['name']
else:
name = None
status, res = self.conn.execute_dict(
render_template(
"/".join([self.template_path, 'stats.sql']),
conn=self.conn, schema=self.schema,
name=name, cid=cid, is_pgstattuple=is_pgstattuple
)
)
if not status:
return internal_server_error(errormsg=res)
return make_json_response(
data=res,
status=200
)
def get_children_nodes(self, manager, **kwargs):
"""
Function is used to get the child nodes.
:param manager:
:param kwargs:
:return:
"""
nodes = []
scid = kwargs.get('scid')
tid = kwargs.get('tid')
trid = kwargs.get('trid')
try:
SQL = render_template("/".join(
[self.template_path, 'get_function_oid.sql']),
tid=tid,
trid=trid)
status, rset = self.conn.execute_2darray(SQL)
if not status:
return internal_server_error(errormsg=rset)
if len(rset['rows']) == 0:
return gone(
gettext("Could not find the specified trigger function"))
# For language EDB SPL we should not display any node.
if rset['rows'][0]['lanname'] != 'edbspl':
trigger_function_schema_oid = rset['rows'][0]['tfuncschoid']
sql = render_template("/".join(
[self.trigger_function_template_path, self._NODE_SQL]),
scid=trigger_function_schema_oid,
fnid=rset['rows'][0]['tfuncoid'])
status, res = self.conn.execute_2darray(sql)
if not status:
return internal_server_error(errormsg=rset)
if len(res['rows']) == 0:
return gone(
gettext(
"Could not find the specified trigger function"))
row = res['rows'][0]
func_name = row['name']
# If trigger function is from another schema then we should
# display the name as schema qulified name.
if scid != trigger_function_schema_oid:
func_name = \
rset['rows'][0]['tfuncschema'] + '.' + row['name']
trigger_func = current_app.blueprints['NODE-trigger_function']
nodes.append(
trigger_func.generate_browser_node(
row['oid'],
trigger_function_schema_oid,
gettext(func_name),
icon="icon-trigger_function",
funcowner=row['funcowner'],
language=row['lanname'],
inode=False))
except Exception as e:
return internal_server_error(errormsg=str(e))
return nodes
def get_sql(self, gid, sid, did, scid, data, tid=None):
"""
This function will return SQL for model data
:param gid: group id
:param sid: server id
:param did: database id
:param scid: schema id
:param data: sql data
:param tid: fts tempate id
"""
# Fetch sql for update
if tid is not None:
sql = render_template(
"/".join([self.template_path, self._PROPERTIES_SQL]),
tid=tid,
scid=scid
)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
elif len(res['rows']) == 0:
return gone(self.not_found_error_msg())
old_data = res['rows'][0]
if 'schema' not in data:
data['schema'] = old_data['schema']
# If user has changed the schema then fetch new schema directly
# using its oid otherwise fetch old schema name using
# fts template oid
sql = render_template(
"/".join([self.template_path, self._SCHEMA_SQL]),
data=data)
status, new_schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=new_schema)
# Replace schema oid with schema name
new_data = data.copy()
error, errmsg = self._replace_schema_oid_with_name(new_schema,
old_data,
new_data)
if error:
print('ERROR INSIDE UPDATE:: {0}'.format(errmsg))
return internal_server_error(errormsg=errmsg)
sql = render_template(
"/".join([self.template_path, self._UPDATE_SQL]),
data=new_data, o_data=old_data
)
# Fetch sql query for modified data
if 'name' in data:
return sql.strip('\n'), data['name']
return sql.strip('\n'), old_data['name']
else:
# Fetch schema name from schema oid
sql = render_template("/".join([self.template_path,
self._SCHEMA_SQL]),
data=data)
status, schema = self.conn.execute_scalar(sql)
if not status:
return internal_server_error(errormsg=schema)
sql = self._get_sql_for_create(data, schema)
return sql.strip('\n'), data['name']
