def get_nodes(self, gid, sid, did, scid, tid):
"""
This function returns all foreign key nodes as a list.
"""
self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
self.conn = self.manager.connection(did=did)
self.template_path = 'foreign_key/sql/#{0}#'.format(
self.manager.version)
# We need parent's name eg table name and schema name
schema, table = fkey_utils.get_parent(self.conn, tid)
self.schema = schema
self.table = table
res = []
SQL = render_template("/".join([self.template_path, 'nodes.sql']),
tid=tid)
status, rset = self.conn.execute_2darray(SQL)
for row in rset['rows']:
if row["convalidated"]:
icon = "icon-foreign_key_no_validate"
valid = False
else:
icon = "icon-foreign_key"
valid = True
res.append(
self.blueprint.generate_browser_node(row['oid'],
tid,
row['name'],
icon=icon,
valid=valid))
return res
def get_node_list(self, gid, sid, did, scid, tid):
"""
This function returns all foreign keys
nodes within that collection as a list.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
fkid: Foreign key constraint ID
Returns:
"""
self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
self.conn = self.manager.connection(did=did)
self.template_path = 'foreign_key/sql/#{0}#'.format(
self.manager.version)
# We need parent's name eg table name and schema name
schema, table = fkey_utils.get_parent(self.conn, tid)
self.schema = schema
self.table = table
SQL = render_template("/".join([self.template_path, 'properties.sql']),
tid=tid)
status, res = self.conn.execute_dict(SQL)
for row in res['rows']:
row['_type'] = self.node_type
return res['rows']
def wrap(*args, **kwargs):
# Here args[0] will hold self & kwargs will hold gid,sid,did
self = args[0]
self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(
kwargs['sid'])
self.conn = self.manager.connection(did=kwargs['did'])
self.datlastsysoid = \
self.manager.db_info[kwargs['did']]['datlastsysoid'] \
if self.manager.db_info is not None and \
kwargs['did'] in self.manager.db_info else 0
self.datistemplate = False
if (self.manager.db_info is not None
and kwargs['did'] in self.manager.db_info and
'datistemplate' in self.manager.db_info[kwargs['did']]):
self.datistemplate = self.manager.db_info[
kwargs['did']]['datistemplate']
self.template_path = self.FOREIGN_KEY_PATH.format(
self.manager.version)
# We need parent's name eg table name and schema name
schema, table = fkey_utils.get_parent(self.conn, kwargs['tid'])
self.schema = schema
self.table = table
return f(*args, **kwargs)
def wrap(*args, **kwargs):
# Here args[0] will hold self & kwargs will hold gid,sid,did
self = args[0]
self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(
kwargs['sid'])
self.conn = self.manager.connection(did=kwargs['did'])
self.template_path = 'foreign_key/sql/#{0}#'.format(
self.manager.version)
# We need parent's name eg table name and schema name
schema, table = fkey_utils.get_parent(self.conn, kwargs['tid'])
self.schema = schema
self.table = table
return f(*args, **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(gettext(FOREIGN_KEY_NOT_FOUND))
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
# Get the parent schema and table.
schema, table = fkey_utils.get_parent(self.conn,
data['columns'][0]['references'])
data['remote_schema'] = schema
data['remote_table'] = 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 create(self, gid, sid, did, scid, tid, fkid=None):
"""
This function will create a 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:
"""
required_args = ['columns']
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 \
(isinstance(data[arg], list) and len(data[arg]) < 1):
return make_json_response(
status=400,
success=0,
errormsg=gettext(
"Could not find required parameter ({}).").format(arg))
data['schema'] = self.schema
data['table'] = self.table
try:
# Get the parent schema and table.
schema, table = fkey_utils.get_parent(
self.conn, data['columns'][0]['references'])
data['remote_schema'] = schema
data['remote_table'] = table
if 'name' not in data or data['name'] == "":
SQL = render_template("/".join(
[self.template_path, 'begin.sql']))
# 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,
conn=self.conn)
status, res = self.conn.execute_scalar(SQL)
if not status:
self.end_transaction()
return internal_server_error(errormsg=res)
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']),
name=data['name'])
status, res = self.conn.execute_dict(sql)
if not status:
self.end_transaction()
return internal_server_error(errormsg=res)
if res['rows'][0]["convalidated"]:
icon = "icon-foreign_key_no_validate"
valid = False
else:
icon = "icon-foreign_key"
valid = True
if data['autoindex']:
sql = render_template("/".join(
[self.template_path, 'create_index.sql']),
data=data,
conn=self.conn)
sql = sql.strip('\n').strip(' ')
if sql != '':
status, idx_res = self.conn.execute_scalar(sql)
if not status:
self.end_transaction()
return internal_server_error(errormsg=idx_res)
return jsonify(node=self.blueprint.generate_browser_node(
res['rows'][0]['oid'],
tid,
data['name'],
valid=valid,
icon=icon))
except Exception as e:
self.end_transaction()
return make_json_response(status=400, success=0, errormsg=e)
def create(self, gid, sid, did, scid, tid, fkid=None):
"""
This function will create a 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:
"""
data = ForeignKeyConstraintView._get_reqes_data()
is_arg_error, errmsg = ForeignKeyConstraintView._check_for_req_data(
data)
if is_arg_error:
return errmsg
data['schema'] = self.schema
data['table'] = self.table
try:
# Get the parent schema and table.
schema, table = fkey_utils.get_parent(
self.conn, data['columns'][0]['references'])
data['remote_schema'] = schema
data['remote_table'] = table
if 'name' not in data or data['name'] == "":
sql = render_template("/".join(
[self.template_path, 'begin.sql']))
# 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, self._CREATE_SQL]),
data=data,
conn=self.conn)
status, res = self.conn.execute_scalar(sql)
if not status:
self.end_transaction()
return internal_server_error(errormsg=res)
elif '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, self._OID_SQL]),
name=data['name'])
status, res = self.conn.execute_dict(sql)
if not status:
self.end_transaction()
return internal_server_error(errormsg=res)
is_error, errmsg, icon, valid = self._create_index(data, res)
if is_error:
return errmsg
return jsonify(node=self.blueprint.generate_browser_node(
res['rows'][0]['oid'],
tid,
data['name'],
valid=valid,
icon=icon))
except Exception as e:
self.end_transaction()
return make_json_response(status=400, success=0, errormsg=e)
def create(self, gid, sid, did, scid):
"""
This function will creates new the table object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema 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']
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 (%s)." % arg))
# Parse privilege data coming from client according to database format
if 'relacl' in data:
data['relacl'] = parse_priv_to_db(data['relacl'], self.acl)
# Parse & format columns
data = column_utils.parse_format_columns(data)
data = TableView.check_and_convert_name_to_string(data)
# 'coll_inherits' is Array but it comes as string from browser
# We will convert it again to list
if 'coll_inherits' in data and \
isinstance(data['coll_inherits'], str):
data['coll_inherits'] = json.loads(data['coll_inherits'],
encoding='utf-8')
if 'foreign_key' in data:
for c in data['foreign_key']:
schema, table = fkey_utils.get_parent(
self.conn, c['columns'][0]['references'])
c['remote_schema'] = schema
c['remote_table'] = table
try:
partitions_sql = ''
if self.is_table_partitioned(data):
data['relkind'] = 'p'
# create partition scheme
data['partition_scheme'] = self.get_partition_scheme(data)
partitions_sql = self.get_partitions_sql(data)
SQL = render_template("/".join(
[self.table_template_path, 'create.sql']),
data=data,
conn=self.conn)
# Append SQL for partitions
SQL += '\n' + partitions_sql
status, res = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
# PostgreSQL truncates the table name to 63 characters.
# Have to truncate the name like PostgreSQL to get the
# proper OID
CONST_MAX_CHAR_COUNT = 63
if len(data['name']) > CONST_MAX_CHAR_COUNT:
data['name'] = data['name'][0:CONST_MAX_CHAR_COUNT]
# Get updated schema oid
SQL = render_template("/".join(
[self.table_template_path, 'get_schema_oid.sql']),
tname=data['name'])
status, scid = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=scid)
# we need oid to to add object in tree at browser
SQL = render_template("/".join(
[self.table_template_path, 'get_oid.sql']),
scid=scid,
data=data)
status, tid = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=tid)
return jsonify(node=self.blueprint.generate_browser_node(
tid,
scid,
data['name'],
icon=self.get_icon_css_class(data),
is_partitioned=self.is_table_partitioned(data)))
except Exception as e:
return internal_server_error(errormsg=str(e))
