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 []