async def company_upload(request):
field_name = request.match_info['field']
assert field_name in {'image', 'logo'}, field_name # double check
content = await request_image(
request, expected_size=None if field_name == 'image' else LOGO_SIZE)
co_id = request['company_id']
co_slug, old_image = await request['conn'].fetchrow_b(
'SELECT slug, :image_field FROM companies WHERE id=:id',
image_field=V(field_name),
id=co_id)
upload_path = Path(co_slug) / 'co' / field_name
method = upload_background if field_name == 'image' else upload_logo
image_url = await method(content,
upload_path=upload_path,
settings=request.app['settings'])
await request['conn'].execute_b('UPDATE companies SET :set WHERE id=:id',
set=V(field_name) == image_url,
id=co_id)
if old_image:
await delete_image(old_image, request.app['settings'])
return json_response(status='success')
def where(self):
logic = V('c.company') == self.request['company_id']
session = self.request['session']
user_role = session['user_role']
if user_role != 'admin':
logic &= V('e.host') == session['user_id']
return Where(logic)
class UserBread(Bread):
class Model(BaseModel):
status: str
first_name: str
last_name: str
role: str # TODO enum
email: EmailStr
browse_enabled = True
retrieve_enabled = True
model = Model
table = 'users'
browse_order_by_fields = V('active_ts').desc(),
browse_fields = ('id', V('first_name').cat(RawDangerous("' '")).cat(
V('last_name')).as_('name'), V('role').as_('role_type'), 'email')
retrieve_fields = browse_fields + (
'phone_number',
'created_ts',
'active_ts',
)
async def check_permissions(self, method):
await check_session(self.request, 'admin')
def where(self):
return Where(V('company') == self.request['company_id'])
def where(self):
logic = V('cat.company') == self.request['company_id']
session = self.request['session']
if session['role'] != 'admin':
logic &= V('e.host') == session['user_id']
if self.method == Method.edit:
logic &= V('e.start_ts') > funcs.now()
return Where(logic)
def select(self) -> Select:
if self.method == Method.retrieve:
event_link = Func('event_link', V('cat.slug'), V('e.slug'),
V('e.public'),
funcs.cast(self.settings.auth_key,
'TEXT')).as_('link')
return Select(self.retrieve_fields + (event_link, ))
return super().select()
async def test_manual_logic(conn):
query, params = render(
'SELECT :select FROM users WHERE :where ORDER BY :order_by',
select=select_fields('first_name'),
where=V('created') > datetime(2021, 1, 1),
order_by=V('last_name'),
)
v = await conn.fetch(query, *params)
assert ['Fred', 'Joe'] == [r[0] for r in v]
def join(self):
joins = Join(
V('categories').as_('cat').on(V('cat.id') == V('e.category'))
) + Join(V('companies').as_('co').on(V('co.id') == V('cat.company')))
if self.method == Method.retrieve:
joins += Join(V('users').as_('uh').on(V('uh.id') == V('e.host')))
return joins
async def test_pool_fetch():
async with asyncpg.create_pool_b(
f'postgresql://postgres@localhost/{DB_NAME}') as pool:
v = await pool.fetchval_b('SELECT :v FROM users ORDER BY id LIMIT 1',
v=funcs.right(V('first_name'), 3))
assert v == 'red'
async def test_cursor(conn):
results = []
q = 'SELECT :s FROM users ORDER BY :o'
async for r in conn.cursor_b(q,
s=select_fields('first_name'),
o=V('first_name').asc()):
results.append(tuple(r))
if len(results) == 2:
break
assert results == [('Franks', ), ('Fred', )]
async def test_values_executemany_default(conn):
co_id = await conn.fetchval('SELECT id FROM companies')
v = [
Values(company=co_id,
first_name='anne',
last_name=None,
value=V('DEFAULT')),
Values(company=co_id,
first_name='ben',
last_name='better',
value=V('DEFAULT')),
Values(company=co_id,
first_name='charlie',
last_name='cat',
value=V('DEFAULT')),
]
await conn.executemany_b(
'INSERT INTO users (:values__names) VALUES :values', v)
assert 6 == await conn.fetchval('SELECT COUNT(*) FROM users')
class UserSelfBread(Bread):
class Model(BaseModel):
first_name: str = None
last_name: str = None
email: EmailStr
phone_number: str = None
receive_emails: bool = None
allow_marketing: bool = None
retrieve_enabled = True
edit_enabled = True
model = Model
table = 'users'
browse_order_by_fields = (V('active_ts').desc(), )
retrieve_fields = (
Func('full_name', V('first_name'), V('last_name'),
V('email')).as_('name'),
'email',
V('role').as_('role_type'),
'status',
'phone_number',
'created_ts',
'receive_emails',
'allow_marketing',
'first_name',
'last_name',
)
async def check_permissions(self, method):
await check_session(self.request, 'host', 'admin')
if int(self.request.match_info['pk']
) != self.request['session']['user_id']:
raise JsonErrors.HTTPForbidden(message='wrong user')
async def edit_execute(self, pk, **data):
await super().edit_execute(pk, **data)
await record_action(self.request,
self.request['session']['user_id'],
ActionTypes.edit_profile,
changes=data)
await self.app['donorfy_actor'].update_user(
pk, update_marketing='allow_marketing' in data)
class DonationOptionBread(Bread):
class Model(BaseModel):
category: int
name: constr(max_length=255)
amount: condecimal(ge=1, max_digits=6, decimal_places=2)
live: bool = True
short_description: str
long_description: str
sort_index: int = None
browse_enabled = True
retrieve_enabled = True
edit_enabled = True
add_enabled = True
delete_enabled = True
model = Model
table = 'donation_options'
table_as = 'opt'
browse_order_by_fields = 'opt.category', 'opt.sort_index', 'opt.amount'
browse_fields = (
'opt.id',
'opt.name',
V('cat.name').as_('category_name'),
'opt.live',
'opt.amount',
)
retrieve_fields = browse_fields + (
'opt.category',
'opt.sort_index',
'opt.short_description',
'opt.long_description',
'opt.image',
)
async def check_permissions(self, method):
await check_session(self.request, 'admin')
def where(self):
return Where(V('cat.company') == self.request['company_id'])
def join(self):
return Join(
V('categories').as_('cat').on(V('cat.id') == V('opt.category')))
async def test_multiple_values_execute(conn):
co_id = await conn.fetchval('SELECT id FROM companies')
v = MultipleValues(
Values(company=co_id,
first_name='anne',
last_name=None,
value=3,
created=datetime(2032, 1, 1)),
Values(company=co_id,
first_name='ben',
last_name='better',
value=5,
created=datetime(2032, 1, 2)),
Values(company=co_id,
first_name='charlie',
last_name='cat',
value=5,
created=V('DEFAULT')),
)
await conn.execute_b('INSERT INTO users (:values__names) VALUES :values',
values=v)
assert 6 == await conn.fetchval('SELECT COUNT(*) FROM users')
def test_args():
query, params = render('WHERE :a', a=V('a') == True) # noqa: E712
assert query == 'WHERE a = $1'
assert params == [True]
assert params[0] is True
def test_falsey_values(value, expected):
query, params = render('WHERE :a', a=V('a') == value)
assert query == 'WHERE a = $1'
assert params == [expected]
assert type(params[0]) == type(expected)
def test_where():
query, params = render(':v',
v=clauses.Where((V('x') == 4)
& (V('y').like('xxx'))))
assert 'WHERE x = $1 AND y LIKE $2' == query
assert [4, 'xxx'] == params
import pytest
from buildpg import V, clauses, render
@pytest.mark.parametrize(
'block,expected_query,expected_params',
[
(lambda: clauses.Select(['foo', 'bar']), 'SELECT foo, bar', []),
(lambda: clauses.Select(['foo', 'bar']), 'SELECT foo, bar', []),
(lambda: clauses.Select(V('foo').comma(V('bar'))), 'SELECT foo, bar',
[]),
(lambda: clauses.Select([V('foo').as_(
'x'), V('bar').as_('y')]), 'SELECT foo AS x, bar AS y', []),
(lambda: clauses.From('foobar'), 'FROM foobar', []),
(lambda: clauses.From('foo', 'bar'), 'FROM foo, bar', []),
(lambda: clauses.From('foo', V('bar')), 'FROM foo, bar', []),
(lambda: clauses.Join('foobar',
V('x.id') == V('y.id')),
'JOIN foobar ON x.id = y.id', []),
(lambda: clauses.CrossJoin('xxx'), 'CROSS JOIN xxx', []),
(lambda: clauses.From('a') + clauses.Join('b') + clauses.Join('c'),
'FROM a\nJOIN b\nJOIN c', []),
(lambda: clauses.OrderBy('apple',
V('pear').desc()),
'ORDER BY apple, pear DESC', []),
(lambda: clauses.Limit(20), 'LIMIT $1', [20]),
(lambda: clauses.Offset(20), 'OFFSET $1', [20]),
(lambda: clauses.Join('foobar',
V('x.value') == 0),
'JOIN foobar ON x.value = $1', [0]),
class EventBread(Bread):
class Model(BaseModel):
name: constr(max_length=63)
category: int
public: bool = True
class DateModel(BaseModel):
dt: datetime
dur: Optional[int]
date: DateModel
class LocationModel(BaseModel):
lat: float
lng: float
name: constr(max_length=63)
location: LocationModel
ticket_limit: int = None
long_description: str
browse_enabled = True
retrieve_enabled = True
add_enabled = True
edit_enabled = True
model = Model
table = 'events'
table_as = 'e'
browse_fields = (
'e.id',
'e.name',
V('c.name').as_('category'),
'e.highlight',
'e.start_ts',
funcs.extract(V('epoch').from_(V('e.duration'))).cast('int').as_('duration'),
)
retrieve_fields = browse_fields + (
'e.slug',
V('c.slug').as_('cat_slug'),
'e.public',
'e.status',
'e.ticket_limit',
'e.location_name',
'e.location_lat',
'e.location_lng',
'e.long_description',
)
async def check_permissions(self, method):
await check_session(self.request, 'admin', 'host')
def join(self):
return Join(V('categories').as_('c').on(V('c.id') == V('e.category')))
def where(self):
logic = V('c.company') == self.request['company_id']
session = self.request['session']
user_role = session['user_role']
if user_role != 'admin':
logic &= V('e.host') == session['user_id']
return Where(logic)
def prepare(self, data):
date = data.pop('date', None)
if date:
dt: datetime = date['dt']
duration: Optional[int] = date['dur']
data.update(
start_ts=datetime(dt.year, dt.month, dt.day) if duration is None else dt.replace(tzinfo=None),
duration=duration and timedelta(seconds=duration),
)
loc = data.pop('location', None)
if loc:
data.update(
location_name=loc['name'],
location_lat=loc['lat'],
location_lng=loc['lng'],
)
long_desc = data.get('long_description')
if long_desc is not None:
data['short_description'] = shorten(long_desc, width=140, placeholder='…')
return data
def prepare_add_data(self, data):
data = self.prepare(data)
data.update(
slug=slugify(data['name']),
host=self.request['session'].get('user_id'),
)
return data
def prepare_edit_data(self, data):
return self.prepare(data)
def join(self):
return Join(V('companies').as_('co').on(V('u.company') == V('co.id')))
def join(self):
return Join(V('categories').as_('cat').on(V('cat.id') == V('opt.category')))
class EventBread(Bread):
class Model(BaseModel):
name: constr(max_length=150)
category: int
public: bool = True
timezone: TzInfo
date: DateModel
mode: EventMode = EventMode.tickets
class LocationModel(BaseModel):
lat: float
lng: float
name: constr(max_length=63)
location: LocationModel = None
ticket_limit: PositiveInt = None
price: condecimal(ge=1, max_digits=6, decimal_places=2) = None
suggested_donation: condecimal(ge=1, max_digits=6,
decimal_places=2) = None
donation_target: condecimal(ge=0, max_digits=9,
decimal_places=2) = None
long_description: str
short_description: str = None
youtube_video_id: str = None
description_image: str = None
description_intro: str = None
external_ticket_url: HttpUrl = None
external_donation_url: HttpUrl = None
@validator('public', pre=True)
def none_bool(cls, v):
return v or False
browse_enabled = True
retrieve_enabled = True
add_enabled = True
edit_enabled = True
delete_enabled = True
model = Model
table = 'events'
table_as = 'e'
browse_fields = (
'e.id',
'e.name',
V('cat.name').as_('category'),
'e.status',
'e.highlight',
Func('as_time_zone', V('e.start_ts'), V('e.timezone')).as_('start_ts'),
funcs.extract(V('epoch').from_(
V('e.duration'))).cast('int').as_('duration'),
)
browse_order_by_fields = (V('e.start_ts').desc(), )
retrieve_fields = browse_fields + (
V('cat.id').as_('cat_id'),
'e.public',
'e.allow_tickets',
'e.allow_donations',
'e.image',
'e.secondary_image',
'e.ticket_limit',
'e.donation_target',
'e.location_name',
'e.location_lat',
'e.location_lng',
'e.youtube_video_id',
'e.short_description',
'e.long_description',
'e.description_image',
'e.description_intro',
'e.external_ticket_url',
'e.external_donation_url',
'e.host',
'e.timezone',
Func('full_name', V('uh.first_name'), V('uh.last_name'),
V('uh.email')).as_('host_name'),
)
async def check_permissions(self, method):
if method == Method.delete:
await check_session(self.request, 'admin')
else:
await check_session(self.request, 'admin', 'host')
def select(self) -> Select:
if self.method == Method.retrieve:
event_link = Func('event_link', V('cat.slug'), V('e.slug'),
V('e.public'),
funcs.cast(self.settings.auth_key,
'TEXT')).as_('link')
return Select(self.retrieve_fields + (event_link, ))
return super().select()
def join(self):
joins = Join(
V('categories').as_('cat').on(V('cat.id') == V('e.category'))
) + Join(V('companies').as_('co').on(V('co.id') == V('cat.company')))
if self.method == Method.retrieve:
joins += Join(V('users').as_('uh').on(V('uh.id') == V('e.host')))
return joins
def where(self):
logic = V('cat.company') == self.request['company_id']
session = self.request['session']
if session['role'] != 'admin':
logic &= V('e.host') == session['user_id']
if self.method == Method.edit:
logic &= V('e.start_ts') > funcs.now()
return Where(logic)
def prepare(self, data):
if self.request['session']['role'] != 'admin':
if data.get('external_ticket_url'):
raise JsonErrors.HTTPForbidden(
message='external_ticket_url may only be set by admins')
elif data.get('external_donation_url'):
raise JsonErrors.HTTPForbidden(
message='external_donation_url may only be set by admins')
timezone: TzInfo = data.pop('timezone', None)
if timezone:
data['timezone'] = str(timezone)
date = data.pop('date', None)
if date:
dt, duration = prepare_event_start(date['dt'], date['dur'],
timezone)
data.update(
start_ts=dt,
duration=duration,
)
loc = data.pop('location', None)
if loc:
data.update(
location_name=loc['name'],
location_lat=loc['lat'],
location_lng=loc['lng'],
)
return data
async def prepare_add_data(self, data):
data = self.prepare(data)
session = self.request['session']
mode: EventMode = data.pop('mode', EventMode.tickets)
data.update(
slug=slugify(data['name'], 63),
short_description=shorten(clean_markdown(data['long_description']),
width=140,
placeholder='…'),
host=session['user_id'],
allow_tickets=mode in (EventMode.tickets, EventMode.both),
allow_donations=mode in (EventMode.donations, EventMode.both),
)
q = 'SELECT status FROM users WHERE id=$1'
if session['role'] == 'admin' or 'active' == await self.conn.fetchval(
q, session['user_id']):
data['status'] = 'published'
return data
async def prepare_edit_data(self, pk, data):
timezone: TzInfo = data.get('timezone')
if not timezone and 'date' in data:
# timezone is needed when date is being updated
tz = await self.conn.fetchval(
'SELECT timezone FROM events WHERE id=$1', pk)
data['timezone'] = pytz.timezone(tz)
data = self.prepare(data)
if timezone and 'start_ts' not in data:
# timezone has changed but not start_ts, need to update start_ts to account for timezone change
dt = await self.conn.fetchval(
"SELECT start_ts AT TIME ZONE timezone FROM events WHERE id=$1",
pk)
data['start_ts'] = timezone.localize(dt)
mode: EventMode = data.pop('mode', None)
if mode is not None:
data.update(
allow_tickets=mode in (EventMode.tickets, EventMode.both),
allow_donations=mode in (EventMode.donations, EventMode.both),
)
return data
add_sql = """
INSERT INTO :table (:values__names) VALUES :values
ON CONFLICT (category, slug) DO NOTHING
RETURNING :pk_field
"""
async def add_execute(self, *, slug, **data):
price = data.pop('price', None)
# we always create a suggested donation and the amount cannot be blank
suggested_donation = data.pop('suggested_donation', price or 10)
async with self.conn.transaction():
pk = await super().add_execute(slug=slug, **data)
while pk is None:
# event with this slug already exists
pk = await super().add_execute(slug=slug + '-' +
pseudo_random_str(4),
**data)
# always create both a ticket type and a suggested donation in case the mode of the event changes in future
await self.conn.execute_b(
'INSERT INTO ticket_types (:values__names) VALUES :values',
values=MultipleValues(
Values(event=pk,
name='Standard',
price=price,
mode='ticket',
custom_amount=False),
Values(event=pk,
name='Standard',
price=suggested_donation,
mode='donation',
custom_amount=False),
Values(event=pk,
name='Custom Amount',
price=None,
mode='donation',
custom_amount=True),
),
)
action_id = await record_action_id(
self.request,
self.request['session']['user_id'],
ActionTypes.create_event,
event_id=pk)
await self.app['email_actor'].send_event_created(action_id)
await self.app['donorfy_actor'].event_created(pk)
return pk
async def edit_execute(self, pk, **data):
try:
await super().edit_execute(pk, **data)
except CheckViolationError as exc:
if exc.constraint_name != 'ticket_limit_check': # pragma: no branch
raise # pragma: no cover
raise JsonErrors.HTTPBadRequest(
message='Invalid Ticket Limit',
details=[{
'loc': ['ticket_limit'],
'msg':
f'May not be less than the number of tickets already booked.',
'type': 'value_error.too_low',
}],
)
else:
await record_action(
self.request,
self.request['session']['user_id'],
ActionTypes.edit_event,
event_id=pk,
subtype='edit-event',
)
await self.app['email_actor'].send_tickets_available(pk)
def where(self):
return Where(V('company') == self.request['company_id'])
def join(self):
return Join(V('categories').as_('c').on(V('c.id') == V('e.category')))
{
'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
'var': lambda: funcs.AND(V('foo') > 2, funcs.OR(S('x') / 7 > 3, V('another') ** 4 == 42)),
'expected_query': 'complex AND OR: foo > $1 AND ($2 / $3 > $4 OR another ^ $5 = $6)',
'expected_params': [2, 'x', 7, 3, 4, 42],
class UserBread(Bread):
class Model(BaseModel):
first_name: str = None
last_name: str = None
email: EmailStr
role_type: UserRoles
phone_number: str = None
receive_emails: bool = True
allow_marketing: bool = False
browse_enabled = True
retrieve_enabled = True
edit_enabled = True
add_enabled = True
model = Model
table = 'users'
table_as = 'u'
browse_order_by_fields = V('u.active_ts').desc(), V('id').asc()
browse_fields = (
'u.id',
Func('full_name', V('u.first_name'), V('u.last_name'),
V('u.email')).as_('name'),
V('u.role').as_('role_type'),
'u.status',
'u.email',
Func('as_time_zone', V('u.active_ts'),
V('co.display_timezone')).as_('active_ts'),
)
retrieve_fields = browse_fields + (
'u.status',
'u.phone_number',
Func('as_time_zone', V('u.created_ts'),
V('co.display_timezone')).as_('created_ts'),
'u.active_ts',
'u.receive_emails',
'u.allow_marketing',
'u.first_name',
'u.last_name',
)
async def check_permissions(self, method):
await check_session(self.request, 'admin')
def where(self):
return Where(V('company') == self.request['company_id'])
def join(self):
return Join(V('companies').as_('co').on(V('u.company') == V('co.id')))
async def prepare_add_data(self, data):
role_type = data.pop('role_type')
if role_type not in {UserRoles.host, UserRoles.admin}:
raise JsonErrors.HTTPBadRequest(
message='role must be either "host" or "admin".')
data.update(role=role_type, company=self.request['company_id'])
return data
async def prepare_edit_data(self, pk, data):
role_type = data.pop('role_type', None)
if role_type:
data['role'] = role_type
return data
async def add_execute(self, **data):
pk = await super().add_execute(**data)
await self.app['email_actor'].send_account_created(
pk, created_by_admin=True)
return pk
async def edit_execute(self, pk, **data):
await super().edit_execute(pk, **data)
await self.app['donorfy_actor'].update_user(
pk, update_marketing='allow_marketing' in data)