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))