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)