def _select_fields(self):
tz = self.get_dt_tz()
date_func = self.get_date_func()
fields = [
Var('m.id'),
Func(date_func, Var('send_ts'), tz).as_('send_ts'),
Func(date_func, Var('update_ts'), tz).as_('update_ts'),
'external_id',
'method',
'subject',
'status',
'to_first_name',
'to_last_name',
'to_user_link',
'to_address',
'm.company_id',
'tags',
'from_name',
'from_name',
'cost',
'extra',
]
if self.sms_method:
fields.append('body')
return fields
async def edit_execute(self, pk, **data):
await self.conn.execute_b(
self.edit_sql,
table=Var(self.table),
values=SetValues(**data),
where=Where(Var(self.pk_field) == pk),
print_=self.print_queries,
)
async def add_execute(self, **data):
return await self.conn.fetchval_b(
self.add_sql,
table=Var(self.table),
values=Values(**data),
pk_field=Var(self.pk_field),
print_=self.print_queries,
)
async def delete_execute(self, pk):
logger.info('%s %s=%d deleted by user %s', self.table, self.pk_field,
pk, self.request['session']['user_id'])
await self.conn.execute_b(
self.delete_sql,
table=Var(self.table),
where=Where(Var(self.pk_field) == pk),
print_=self.print_queries,
)
async def call(self, request):
# TODO allow more filtering here, filter to last X days.
where = Var('method') == self.request.match_info['method']
async with self.app['pg'].acquire() as conn:
if self.session.company != '__all__':
where &= Var('company_id') == await get_company_id(conn, self.session.company)
data = await conn.fetchval_b(agg_sql, where=where)
return PreResponse(text=data, content_type='application/json')
async def query(self, *, message_id=None, tags=None, query=None):
where = Var('method') == self.request.match_info['method']
pg_pool: BuildPgPool = self.app['pg']
if self.session.company != '__all__':
company_id = await get_create_company_id(pg_pool, self.session.company)
where &= Var('company_id') == company_id
if message_id:
where &= Var('id') == message_id
elif tags:
where &= Var('tags').contains(tags)
elif query:
return await self.query_general(where, query)
# count is limited to 10,000 as it speeds up the query massively
count, items = await asyncio.gather(
pg_pool.fetchval_b(
"""
select count(*)
from (
select 1
from messages
where :where
limit 10000
) as t
""",
where=where,
),
pg_pool.fetch_b(
"""
:select
from messages m
join message_groups j on m.group_id = j.id
where m.id in (
select id from messages
where :where
order by id desc
limit 100
offset :offset
)
order by m.id desc
""",
select=Select(self._select_fields()),
where=where,
offset=self.get_arg_int('from', 0) if self.offset else 0,
),
)
return {'count': count, 'items': [dict(r) for r in items]}
async def browse(self) -> web.Response:
json_str = await self.conn.fetchval_b(
self.browse_sql,
items_query=await self.browse_items_query(),
count_query=await self.browse_count_query(),
pagination=Var(str(self.browse_limit_value)),
print_=self.print_queries,
)
return raw_json_response(json_str)
async def call(self, request):
method = self.request.match_info['method']
where = (Var('m.method') == method) & (Var('m.id') == int(request.match_info['id']))
if self.session.company != '__all__':
where &= Var('c.code') == self.session.company
async with self.app['pg'].acquire() as conn:
data = await conn.fetchrow_b(
"""
select from_name, to_last_name, to_address, status, body, extra
from messages m
join message_groups j on m.group_id = j.id
join companies c on m.company_id = c.id
where :where
""",
where=where,
)
if not data:
raise JsonErrors.HTTPNotFound('message not found')
data = dict(data)
body = data['body']
# Remove links from preview
body = re.sub('(href=").*?"', r'\1#"', body, flags=re.S | re.I)
extra = json.loads(data['extra']) if data.get('extra') else {}
if method.startswith('sms'):
# need to render the sms so it makes sense to users
return {
'from': data['from_name'],
'to': data['to_last_name'] or data['to_address'],
'status': data['status'],
'message': body,
'extra': extra,
}
else:
return {'raw': body}
async def check_item_permissions_query(self, pk):
yield Select([self.pk_ref()])
yield self.from_()
yield self.join()
yield self.where_pk(pk)
yield Limit(Var('1'))
async def retrieve_query(self, pk):
yield self.select()
yield self.from_()
yield self.join()
yield self.where_pk(pk)
yield Limit(Var('1'))
def browse_limit(self) -> Optional[Limit]:
if self.browse_limit_value:
return Limit(Var(str(self.browse_limit_value)))
def pk_ref(self) -> Var:
if self.table_as:
return Var(self.table_as + '.' + self.pk_field)
else:
return Var(self.pk_field)
def from_(self) -> From:
v = Var(self.table)
if self.table_as:
v = v.as_(self.table_as)
return From(v)
{'template': 'eq: :var', 'var': lambda: S(1) > 2, 'expected_query': 'eq: $1 > $2', 'expected_params': [1, 2]},
{'template': 'and: :var', 'var': lambda: S(1) & 2, 'expected_query': 'and: $1 AND $2', 'expected_params': [1, 2]},
{'template': 'or: :var', 'var': lambda: S(1) | 2, 'expected_query': 'or: $1 OR $2', 'expected_params': [1, 2]},
{
'template': 'inv: :var',
'var': lambda: ~(S(1) % 2),
'expected_query': 'inv: not($1 % $2)',
'expected_params': [1, 2],
},
{
'template': 'double inv: :var',
'var': lambda: ~~(S(1) % 2),
'expected_query': 'double inv: $1 % $2',
'expected_params': [1, 2],
},
{'template': 'eq: :var', 'var': lambda: Var('foo') > 2, 'expected_query': 'eq: foo > $1', 'expected_params': [2]},
{
'template': 'chain: :var',
'var': lambda: V('x') + 4 + 2 + 1,
'expected_query': 'chain: x + $1 + $2 + $3',
'expected_params': [4, 2, 1],
},
{
'template': 'complex: :var',
'var': lambda: (Var('foo') > 2) & ((SqlBlock('x') / 7 > 3) | (Var('another') ** 4 == 42)),
'expected_query': 'complex: foo > $1 AND ($2 / $3 > $4 OR another ^ $5 = $6)',
'expected_params': [2, 'x', 7, 3, 4, 42],
},
{
'template': 'complex AND OR: :var',
# as above but using the AND and OR functions for clear usage
async def delete_execute(self, pk):
await self.conn.execute_b(self.delete_sql,
table=Var(self.table),
where=Where(Var(self.pk_field) == pk),
print_=self.print_queries)
