async def get_children(self, parent_id, all=True, group_id='', **kwargs): conn = kwargs['conn'] query_str = ''' SELECT * FROM {table_name} WHERE {left_col}>{left_val} and {right_col}<{right_val} {group_query} {extra} ORDER BY {left_col}; ''' query_dict = { 'left_col': self.left_name, 'right_col': self.right_name, 'table_name': self.table_name } parent_node = await self.get_node(parent_id, conn=conn) left_val = parent_node[self.left_name] right_val = parent_node[self.right_name] query_dict['left_val'] = left_val query_dict['right_val'] = right_val if not all: query_dict['extra'] = " and %s='%s' " % (self.parent_name, str(parent_id)) else: query_dict['extra'] = '' if group_id: query_dict['group_query'] = " and %s='%s' " % (self.group_name, group_id) else: query_dict['group_query'] = '' logger.info(query_str.format(**query_dict)) result = await conn.fetch(query_str.format(**query_dict)) if result: return RecordHelper.record_to_dict(result, normalize=[uuid_serializer]) else: return []
async def _group_check(self, group_id, node_id='', conn=None): if not node_id: if not group_id: raise InvalidArgument("%s is required" % (self.group_name)) else: q_s = '''SELECT * FROM {table} WHERE id='{node_id}';'''.format( table=self.table_name, node_id=node_id) res = await conn.fetch(q_s) res = RecordHelper.record_to_dict(res, normalize=[uuid_serializer]) if res[self.group_name] != group_id: raise InvalidArgument("%s is required" % (self.group_name))
async def filter_by_params(self, params, group_id='', **kwargs): conn = kwargs['conn'] if group_id: params[self.group_name] = group_id where_str = 'where ' + ' and '.join([ "{col}='{val}'".format(col=col, val=val) for col, val in params.items() ]) q_s = 'SELECT * FROM {table} {where};'.format(where=where_str, table=self.table_name) logger.info(q_s) result = await conn.fetch(q_s) return RecordHelper.record_to_dict(result, normalize=[uuid_serializer])
async def get_node(self, id, **kwargs): # id is unique group_id is not needed conn = kwargs['conn'] query_str = ''' SELECT * FROM {table_name} WHERE {where_str}; ''' query_dict = {'table_name': self.table_name, 'where_str': ''} query_dict['where_str'] = "id='%s'" % (str(id)) logger.info(query_str.format(**query_dict)) result = await conn.fetch(query_str.format(**query_dict)) if not result: raise RowNotFound return RecordHelper.record_to_dict(result, normalize=[uuid_serializer])
async def _create_node( self, node_dict, **kwargs ): # don't call it manually, node_dict should contain group_id conn = kwargs['conn'] query_string = 'INSERT INTO {table_name} ({colms}) VALUES ({values}) RETURNING *;' query_dict = {'table_name': self.table_name, 'colms': '', 'values': ''} colms_list, value_list = self.format_col_values_list(node_dict) query_dict['colms'] = ','.join(colms_list) query_dict['values'] = ','.join(value_list) logger.info(query_string.format(**query_dict)) result = await conn.fetch(query_string.format(**query_dict)) if not isinstance(result, list): result = [result] if result: return RecordHelper.record_to_dict(result, normalize=[uuid_serializer]) else: raise RowNotCreated
async def get_all(self, group_id='', limit=sys.maxsize, **kwargs): conn = kwargs['conn'] query_str = '''SELECT * FROM {table_name} {group_only} ORDER BY {left_col} LIMIT {limit};''' if group_id: group_only = " WHERE %s='%s' " % (self.group_name, group_id) else: group_only = '' formatted_qs = query_str.format(table_name=self.table_name, limit=limit, left_col=self.left_name, group_only=group_only) logger.info(formatted_qs) records = await conn.fetch(formatted_qs) if records: return RecordHelper.record_to_dict(records, normalize=[uuid_serializer]) else: return []
async def get_children_by_id( self, parent_id, **kwargs): # parent_id unique group_id not needed conn = kwargs['conn'] query_str = ''' SELECT * FROM {table_name} WHERE {parent_name}='{parent_value}' ORDER BY {left_col}; ''' query_dict = { 'left_col': self.left_name, 'parent_value': str(parent_id), 'table_name': self.table_name, 'parent_name': self.parent_name } logger.info(query_str.format(**query_dict)) result = await conn.fetch(query_str.format(**query_dict)) if result: return RecordHelper.record_to_dict(result, normalize=[uuid_serializer]) else: return []
async def get_leaves(self, group_id='', **kwargs): conn = kwargs['conn'] query_str = '''SELECT * FROM {table_name} WHERE {pk} NOT IN (SELECT {parent_name} FROM {table_name} WHERE {parent_name} IS NOT null {only_group}) {only_group};''' query_dict = { 'table_name': self.table_name, 'pk': 'id', 'parent_name': self.parent_name } if group_id: query_dict['only_group'] = " and %s='%s' " % (self.group_name, group_id) else: query_dict['only_group'] = '' logger.info(query_str.format(**query_dict)) result = await conn.fetch(query_str.format(**query_dict)) if result: return RecordHelper.record_to_dict(result, normalize=[uuid_serializer]) else: return []
async def _update_node(self, node_id, update_dict, **kwargs): #node_id is unique group_id not needed conn = kwargs['conn'] query_str = ''' UPDATE {table_name} SET {set_str} WHERE id='{node_id}' RETURNING *; ''' query_dict = { 'table_name': self.table_name, 'node_id': str(node_id), 'set_str': '' } col, values = self.format_col_values_list(update_dict) query_dict['set_str'] = ','.join( ("%s=%s" % (i, j) for i, j in zip(col, values))) logger.info(query_str.format(**query_dict)) result = await conn.fetch(query_str.format(**query_dict)) if result: return RecordHelper.record_to_dict(result, normalize=[uuid_serializer]) else: return []
async def get_forward_siblings(self, node_dict, group_id='', **kwargs): conn = kwargs['conn'] query_str = ''' SELECT * FROM {table_name} WHERE {parent_name}='{parent_value}' and {left_col}>{right_val} and id!='{id}' ORDER BY {left_col}; ''' query_dict = { 'table_name': self.table_name, 'parent_value': node_dict[self.parent_name], 'left_col': self.left_name, 'right_val': node_dict[self.right_name], 'id': node_dict['id'], 'parent_name': self.parent_name } logger.info(query_str.format(**query_dict)) result = await conn.fetch(query_str.format(**query_dict)) if result: return RecordHelper.record_to_dict(result, normalize=[uuid_serializer]) else: return []