def show(id: int, authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
request_cost = 1
with LazyItgs() as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization,
(helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
request_cost):
return Response(status_code=429,
headers={'x-request-cost': str(request_cost)})
if user_id is None:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms
can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms
auth_methods = Table('password_authentications')
query = (Query.from_(auth_methods).select(
auth_methods.human,
auth_methods.deleted).where(auth_methods.id == Parameter('%s')))
args = [id]
if not can_view_others_auth_methods:
query = query.where(auth_methods.user_id == Parameter('%s'))
args.append(user_id)
if not can_view_deleted_auth_methods:
query = query.where(auth_methods.deleted.eq(False))
itgs.read_cursor.execute(query.get_sql(), args)
row = itgs.read_cursor.fetchone()
if row is None:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
(main, deleted) = row
authtokens = Table('authtokens')
itgs.read_cursor.execute(
Query.from_(authtokens).select(Count(
Star())).where(authtokens.expires_at < Now()).where(
authtokens.source_type == Parameter('%s')).where(
authtokens.source_id == Parameter('%s')).get_sql(),
('password_authentication', id))
(active_grants, ) = itgs.read_cursor.fetchone()
return JSONResponse(status_code=200,
content=models.AuthMethod(
main=main,
deleted=deleted,
active_grants=active_grants).dict(),
headers={'x-request-cost': str(request_cost)})
def init(self):
self.meta = self.model_class._meta
self.pika_table = Table(self.meta.table)
column_names = []
columns = []
self.columns = columns
self.column_names = column_names
for name, field in self.meta.fields_map.items():
column_names.append(name)
columns.append(field)
self.filters = PikaTableFilters(self.pika_table, self.meta.filters)
self.insert_all_sql = str(
PostgreSQLQuery.into(
self.pika_table).columns(*column_names).insert(
*[self.parameter(i)
for i in range(len(column_names))]).get_sql())
self.delete_sql = str(
PostgreSQLQuery.from_(self.pika_table).where(
self.pika_table[self.meta.db_pk_field] == self.parameter(
0)).delete().get_sql())
self.delete_table_sql = str(
PostgreSQLQuery.from_(self.pika_table).delete().get_sql())
self.drop_table_sql = str(f"DROP TABLE IF EXISTS {self.meta.table};")
self.update_cache = {}
def test_create(self):
with helper.clear_tables(self.conn, self.cursor, ['responses', 'response_histories']),\
helper.user_with_token(self.conn, self.cursor, ['responses']) as (user_id, token):
r = requests.post(f'{HOST}/responses',
headers={'authorization': f'bearer {token}'},
json={
'name': 'foobar',
'body': 'my body',
'desc': 'my desc'
})
r.raise_for_status()
self.assertEqual(r.status_code, 200)
responses = Table('responses')
self.cursor.execute(
Query.from_(responses).select(
responses.id, responses.response_body,
responses.description).where(
responses.name == Parameter('%s')).get_sql(),
('foobar', ))
row = self.cursor.fetchone()
self.assertIsNotNone(row)
(respid, body, desc) = row
self.assertEqual(body, 'my body')
self.assertEqual(desc, 'my desc')
resp_hists = Table('response_histories')
self.cursor.execute(
Query.from_(resp_hists).select(1).where(
resp_hists.response_id == Parameter('%s')).limit(
1).get_sql(), (respid, ))
row = self.cursor.fetchone()
self.assertIsNotNone(row)
def execute_get_missing_initial_alerts(itgs):
endpoint_users = Table('endpoint_users')
endpoint_alerts = Table('endpoint_alerts')
users = Table('users')
usage_after_filters = Table('usage_after_filters')
query = (Query.with_(
Query.from_(endpoint_users).where(
Not(
Exists(
Query.from_(endpoint_alerts).where(
endpoint_alerts.endpoint_id ==
endpoint_users.endpoint_id).where(
endpoint_alerts.user_id == endpoint_users.user_id))
)).select(endpoint_users.endpoint_id.as_('endpoint_id'),
endpoint_users.user_id.as_('user_id'),
Min(endpoint_users.created_at).as_('first_usage'),
Max(endpoint_users.created_at).as_('last_usage'),
Count(endpoint_users.id).as_('count_usage')).groupby(
endpoint_users.endpoint_id, endpoint_users.user_id),
'usage_after_filters').from_(usage_after_filters).join(users).on(
users.id == usage_after_filters.user_id).select(
usage_after_filters.user_id, users.username,
usage_after_filters.endpoint_id,
usage_after_filters.first_usage,
usage_after_filters.last_usage,
usage_after_filters.count_usage).orderby(
usage_after_filters.user_id))
sql = query.get_sql()
itgs.read_cursor.execute(sql)
def delete_all_sessions(id: int, authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
request_cost = 5
with LazyItgs(no_read_only=True) as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization,
(helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM,
helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
request_cost):
return Response(status_code=429,
headers={'x-request-cost': str(request_cost)})
if user_id is None:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms
can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms
can_modify_others_auth_methods = (
helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM in perms)
auth_methods = Table('password_authentications')
itgs.read_cursor.execute(
Query.from_(auth_methods).select(
auth_methods.deleted, auth_methods.user_id).where(
auth_methods.id == Parameter('%s')).get_sql(), (id, ))
row = itgs.read_cursor.fetchone()
if row is None:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
(deleted, auth_method_user_id) = row
if deleted and not can_view_deleted_auth_methods:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
if auth_method_user_id != user_id and not can_view_others_auth_methods:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
if auth_method_user_id != user_id and not can_modify_others_auth_methods:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
authtokens = Table('authtokens')
itgs.write_cursor.execute(
Query.from_(authtokens).delete().where(
authtokens.source_type == Parameter('%s')).where(
authtokens.source_id == Parameter('%s')).get_sql(),
('password_authentication', id))
itgs.write_conn.commit()
return Response(status_code=200,
headers={'x-request-cost': str(request_cost)})
def update_response(name: str,
change: models.ResponseEditArgs,
authorization: str = Header(None)):
if len(change.edit_reason) < 5:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['body', 'edit_reason']
},
'msg': 'minimum 5 characters',
'type': 'too_short'
})
with LazyItgs(no_read_only=True) as itgs:
authed, user_id = users_helper.check_permissions_from_header(
itgs, authorization, 'responses')
if not authed:
return Response(status_code=403)
users = Table('users')
itgs.write_cursor.execute(
Query.from_(users).select(
users.id).where(users.id == Parameter('%s')).get_sql() +
' FOR SHARE', (user_id, ))
row = itgs.write_cursor.fetchone()
if row is None:
itgs.write_conn.rollback()
return Response(status_code=403)
responses = Table('responses')
itgs.write_cursor.execute(
Query.from_(responses).select(
responses.id, responses.response_body,
responses.description).where(
responses.name == Parameter('%s')).get_sql() +
' FOR UPDATE', (name, ))
row = itgs.write_cursor.fetchone()
if row is None:
itgs.write_conn.rollback()
return Response(status_code=404)
(resp_id, old_body, old_desc) = row
resp_hists = Table('response_histories')
itgs.write_cursor.execute(
Query.into(resp_hists).columns(
resp_hists.response_id, resp_hists.user_id, resp_hists.old_raw,
resp_hists.new_raw, resp_hists.reason, resp_hists.old_desc,
resp_hists.new_desc).insert(
*[Parameter('%s') for _ in range(7)]).get_sql(),
(resp_id, user_id, old_body, change.body, change.edit_reason,
old_desc, change.desc))
itgs.write_cursor.execute(
Query.update(responses).set(
responses.response_body, Parameter('%s')).set(
responses.description, Parameter('%s')).set(
responses.updated_at, ppfns.Now()).where(
responses.id == Parameter('%s')).get_sql(),
(change.body, change.desc, resp_id))
itgs.write_conn.commit()
return Response(status_code=200)
async def update_links_from_html(ob, *contents):
storage = get_storage()
if storage is None:
return
if hasattr(ob, "context"):
uuid = ob.context.uuid
else:
uuid = ob.uuid
links = set()
for content in contents:
dom = html.fromstring(content)
for node in dom.xpath("//a") + dom.xpath("//img"):
url = node.get("href", node.get("src", ""))
if "resolveuid/" not in url:
continue
_, _, uid = url.partition("resolveuid/")
uid = uid.split("/")[0].split("?")[0]
links.add(_safe_uid(uid))
if len(links) == 0:
# delete existing if there are any
async with storage.pool.acquire() as conn:
await conn.execute(
str(
Query.from_(links_table).where(
links_table.source_id == _safe_uid(uuid)).delete()))
return
async with storage.pool.acquire() as conn:
# make sure to filter out bad links
existing_oids = set()
query = str(
Query.from_(objects_table).select("zoid").where(
objects_table.zoid == "$1") # noqa
).replace("'$1'", "any($1)")
results = await conn.fetch(query, list(links))
for record in results:
existing_oids.add(record["zoid"])
# first delete all existing ones
await conn.execute(
str(
Query.from_(links_table).where(
links_table.source_id == _safe_uid(uuid)).delete()))
# then, readd
links = links & existing_oids
if len(links) > 0:
query = Query.into(links_table).columns("source_id", "target_id")
for link in links:
query = query.insert(_safe_uid(str(uuid)), _safe_uid(link))
await conn.execute(str(query))
async def remove_user(user_id=None, username=None):
if user_id is not None:
query = Query.from_(users_table).where(users_table.id == user_id)
else:
query = Query.from_(users_table).where(
users_table.username == username)
db = await get_db()
async with db.acquire() as conn:
await conn.execute(str(query.delete()))
await notify(UserRemovedEvent(user_id, username))
def count_sql(self, estimate=True):
if estimate:
stats_table = Table("pg_stat_user_tables")
return Query.from_(stats_table).select(
stats_table.n_live_tup
).where(stats_table.relname == self.initial_table_name).where(
stats_table.schemaname == self.initial_schema_name).get_sql()
self.current_query = Query.from_(self.current_dataset)
return self.current_query.select(
pypika_fn.Count(self.current_dataset.star)).get_sql()
def revoke_permissions(itgs: 'LazyItgs',
user_id: int,
reason: str,
passwd_auth_id: int,
perm_ids_to_revoke: list,
commit=False):
"""Revokes all the given permissions from the given password authentication
id. The password authentication must have all of the permissions. This will
record the event in the audit table. This will log the user out.
Arguments:
- `itgs (LazyItgs)`: The integrations to connect to third part services on
- `user_id (int)`: The user the password authentication belong sto.
- `reason (str)`: The reason for revoking permissions
- `passwd_auth_id (int)`: The password authentication to revoke permissions
on.
- `perm_ids_to_revoke (list[int])`: The ids of the permissions to revoke
- `commit (bool)`: True to commit the transaction immediately, false not
to.
"""
passwd_auth_perms = Table('password_auth_permissions')
itgs.write_cursor.execute(
Query.from_(passwd_auth_perms).delete().where(
passwd_auth_perms.password_authentication_id == Parameter(
'%s')).where(
passwd_auth_perms.permission_id.isin(
tuple(Parameter('%s')
for _ in perm_ids_to_revoke))).get_sql(),
(passwd_auth_id, *perm_ids_to_revoke))
passwd_auth_events = Table('password_authentication_events')
loansbot_user_id = get_loansbot_user_id(itgs)
args = []
for perm_id in perm_ids_to_revoke:
args.append(passwd_auth_id)
args.append('permission-revoked')
args.append(reason)
args.append(loansbot_user_id)
args.append(perm_id)
itgs.write_cursor.execute(
Query.into(passwd_auth_events).columns(
passwd_auth_events.password_authentication_id,
passwd_auth_events.type, passwd_auth_events.reason,
passwd_auth_events.user_id,
passwd_auth_events.permission_id).insert(*(tuple(
Parameter('%s')
for _ in range(5)) for _ in perm_ids_to_revoke)).get_sql(),
args)
authtokens = Table('authtokens')
itgs.write_cursor.execute(
Query.from_(authtokens).delete().where(
authtokens.user_id == Parameter('%s')).get_sql(), (user_id, ))
if commit:
itgs.write_conn.commit()
def create_server_trust_comment(itgs, comment, user_id=None, username=None):
"""Create an autogenerated comment on the given users trustworthiness,
where the user is specified either via id or username. This does not
commit the comment. This will create the user if specified via username
and they do not exist.
Arguments:
- `itgs (LazyIntegrations)`: The lazy integrations to use
- `comment (str)`: The comment to post
- `user_id (int, None)`: The id of the user to post the comment on or
None if the user is specified via the username.
- `username (str, None)`: The username of the user to post the comment on
or None if the user is specified via the user id.
"""
assert (user_id is None) != (username is None), f'user_id={user_id}, username={username}'
usrs = Table('users')
if user_id is None:
itgs.read_cursor.execute(
Query.from_(usrs).select(usrs.id)
.where(usrs.username == Parameter('%s'))
.get_sql(),
(username.lower(),)
)
row = itgs.read_cursor.fetchone()
if row is None:
user_id = users.helper.create_new_user(itgs, username.lower())
else:
(user_id,) = row
itgs.read_cursor.execute(
Query.from_(usrs).select(usrs.id)
.where(usrs.username == Parameter('%s'))
.get_sql(),
('loansbot',)
)
row = itgs.read_cursor.fetchone()
if row is None:
loansbot_user_id = users.helper.create_new_user(itgs, 'loansbot')
else:
(loansbot_user_id,) = row
trust_comments = Table('trust_comments')
itgs.write_cursor.execute(
Query.into(trust_comments).columns(
trust_comments.author_id,
trust_comments.target_id,
trust_comments.comment
).insert(*[Parameter('%s') for _ in range(3)])
.get_sql(),
(loansbot_user_id, user_id, comment)
)
def load_game(self, chat_id: int) -> Optional[GameState]:
if not self.use_db:
return None
games, players = Tables('games', 'players')
logging.info(f"Loading game {chat_id} from DB")
load_game_query = PostgreSQLQuery.from_(games) \
.select(games.player1,
games.player2,
games.size_x,
games.size_y,
games.state,
games.turn_color,
games.turn_player,
games.last_stone,
games.last_capt_stone,
games.player_passed) \
.where(games.chat_id == chat_id)
self._cur.execute(load_game_query.get_sql())
rows = self._cur.fetchall()
if not rows:
logging.info(f"Game {chat_id} not fround in DB")
return None
row = rows[0]
get_player1_query = PostgreSQLQuery.from_(players) \
.select(players.name) \
.where(players.id == row[0])
self._cur.execute(get_player1_query.get_sql())
player1_name = self._cur.fetchall()[0][0]
get_player2_query = PostgreSQLQuery.from_(players) \
.select(players.name) \
.where(players.id == row[1])
self._cur.execute(get_player2_query.get_sql())
player2_name = self._cur.fetchall()[0][0]
return GameState(
player_ids=(row[0], row[1]),
size_x=row[2],
size_y=row[3],
board=row[4],
turn_color=row[5],
turn_player=row[6],
last_stone=row[7],
last_capt_stone=row[8],
player_passed=row[9],
player1_name=player1_name,
player2_name=player2_name
)
def get_id_device_elements(vl_url: str, filters: List[str], inhibit_filters: List[str]) -> List:
re_user_filters = []
re_inhibit_user_filter = []
result_filter: List[str]
result_inhibit_filter: List[str]
gg = vl_url.replace('/', '\\\\')
for inhibit in inhibit_filters:
result_inhibit_filter = inhibit.split('\r\n')
re_inhibit_user_filter = [w.replace('\\', '\\\\') for w in result_inhibit_filter]
re_inhibit_user_filter = [w.replace('*', "%") for w in re_inhibit_user_filter]
for filter_user in filters:
result_filter = filter_user.split('\r\n')
re_user_filters = [w.replace('\\', '\\\\') for w in result_filter]
re_user_filters = [w.replace('*', "%") for w in re_user_filters]
logging.debug('Получение таблицы балансов')
p = Table('askue_rs_point', alias='p')
q = (PostgreSQLQuery.from_(p)
.select(p.id, p.object_name)
.where(p.object_name == gg)
)
filter_sql = db_work.get_query_by_user_filter(q, result_filter, result_inhibit_filter)
sql = filter_sql.get_sql()
re_user_filters.extend(re_inhibit_user_filter)
result_elements = []
logging.debug(f'SQL: {sql}')
try:
result = engine.execute(sql, re_user_filters)
for row in result:
result_elements.append(row[0])
except Exception as e:
logging.error("Error is ", e)
logging.debug("get_id_device_elements() OK...")
return result_elements
def get_result_day_by_day_elements(list_id: int, start: str, end: str) -> List:
logging.debug('Получение таблицы балансов')
start_utc = datetime.strptime(start, '%Y-%m-%d').astimezone(pytz.UTC).replace(tzinfo=None)
end_utc = datetime.strptime(end, '%Y-%m-%d').astimezone(pytz.UTC).replace(tzinfo=None)
p = Table('calc_balance', alias='p')
try:
q = (PostgreSQLQuery.from_(p)
.select(p.start_period, p.time_start_write)
.where(
(p.time_start_write[start_utc:end_utc])
& (p.id_tu == list_id[0])
).orderby(p.time_start_write, order=Order.asc))
sql = q.get_sql()
except Exception as e:
logging.error("Error is ", e)
result_elements = []
logging.debug(f'SQL: {sql}')
try:
result = engine.execute(sql)
for row in result:
result_elements.append(row)
except Exception as e:
logging.error("Error is ", e)
logging.debug("get_result_day_by_day_elements() OK...")
return result_elements
def test_delete_returning(self):
q1 = PostgreSQLQuery.from_(self.table_abc).where(
self.table_abc.foo == self.table_abc.bar).delete().returning(
self.table_abc.id)
self.assertEqual('DELETE FROM "abc" WHERE "foo"="bar" RETURNING id',
str(q1))
def show(name: str, authorization: str = Header(None)):
with LazyItgs() as itgs:
if not users_helper.check_permissions_from_header(
itgs, authorization, 'responses')[0]:
return Response(status_code=403)
responses = Table('responses')
itgs.read_cursor.execute(
Query.from_(responses).select(
responses.id, responses.name, responses.response_body,
responses.description, responses.created_at,
responses.updated_at).where(
responses.name == Parameter('%s')).get_sql(), (name, ))
row = itgs.read_cursor.fetchone()
if row is None:
return Response(status_code=404)
return JSONResponse(status_code=200,
content=models.ResponseShow(
id=row[0],
name=row[1],
body=row[2],
desc=row[3],
created_at=int(row[4].timestamp()),
updated_at=int(row[5].timestamp())).dict(),
headers={
'Cache-Control':
'public, max-age=86400, stale-if-error=2629746'
})
def get_elements_for_light(list_id: List, start: str, end: str) -> List:
logging.debug('Получение данных для таблицы УО')
start_utc = datetime.strptime(str(start), '%Y-%m-%d').astimezone(
pytz.UTC).replace(tzinfo=None)
end_utc = datetime.strptime(str(end), '%Y-%m-%d').astimezone(
pytz.UTC).replace(tzinfo=None)
p = Table('calc_balance', alias='p')
pp = Table('calc_balance', alias='pp')
try:
q = (PostgreSQLQuery.from_(p).join(pp).on(p.id_tu == pp.id_tu).select(
p.id_tu, p.dtp, p.locality, p.name_of_accounting_point, p.str_ra,
p.pxx, p.loss_xx, p.ktt, p.head_of_counter, p.start_period,
pp.start_period.as_("end_period"), p.q_slim, p.time_start_write,
p.country, p.driver).where((p.time_start_write == start_utc)
& (pp.time_start_write == end_utc)
& (p.id_tu.isin(list_id))))
sql = q.get_sql()
except Exception as e:
logging.error("Error is ", e)
result_elements = []
logging.debug(f'SQL: {sql}')
try:
result = engine.execute(sql)
for row in result:
result_elements.append(row)
except Exception as e:
logging.error("Error is ", e)
logging.debug("get_elements_for_light() OK...")
return result_elements
def check_if_pkey_exists(cursor, tblname):
pg_indices = Table('pg_index')
cursor.execute(
Query.from_(pg_indices).select(1).where(
pg_indices.indrelid == Cast(Parameter('%s'), 'regclass')).where(
pg_indices.indisprimary).get_sql(), (tblname, ))
return cursor.fetchone() is not None
def _get_query_sql(self, queryset):
values = []
table = self.pika_table
query = PostgreSQLQuery.from_(table).select(*self.column_names)
i = 0
criterion, where_values = self._expressions_to_criterion(
queryset._expressions, i)
query = query.where(criterion)
values.extend(where_values)
i += len(where_values)
if queryset._distinct:
query = query.distinct()
if queryset._limit:
query = query.limit(queryset._limit)
if queryset._orderings:
for field_name, order in queryset._orderings:
query = query.orderby(getattr(table, field_name), order=order)
if queryset._offset:
query = query.offset(queryset._offset)
sql = str(query.get_sql())
return sql, values
def test_existing(self):
resps = Table('responses')
with LazyIntegrations() as itgs:
itgs.write_cursor.execute(
Query.into(resps).columns(
resps.name, resps.response_body, resps.description).insert(
*[Parameter('%s')
for _ in range(3)]).returning(resps.id).get_sql(),
('my_response', 'I like to {foo} the {bar}', 'Testing desc'))
(respid, ) = itgs.write_cursor.fetchone()
try:
itgs.write_conn.commit()
res: str = responses.get_response(itgs,
'my_response',
foo='open',
bar='door')
self.assertEqual(res, 'I like to open the door')
res: str = responses.get_response(itgs,
'my_response',
foo='eat',
buzz='bear')
self.assertIsInstance(res, str)
self.assertTrue(res.startswith('I like to eat the '), res)
# it's not important how we choose to format the error, but it
# needs the missing key or debugging will be a pain
self.assertIn('bar', res)
finally:
itgs.write_conn.rollback()
itgs.write_cursor.execute(
Query.from_(resps).delete().where(
resps.id == Parameter('%s')).get_sql(), (respid, ))
itgs.write_conn.commit()
def index_permissions(id: int, authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
request_cost = 50
with LazyItgs() as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization,
(helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
request_cost):
return Response(status_code=429,
headers={'x-request-cost': str(request_cost)})
if user_id is None:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms
can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms
auth_methods = Table('password_authentications')
auth_perms = Table('password_auth_permissions')
permissions = Table('permissions')
query = (Query.from_(auth_methods).select(
permissions.name).join(auth_perms).on(
auth_perms.password_authentication_id == auth_methods.id).join(
permissions).on(
permissions.id == auth_perms.permission_id).where(
auth_methods.deleted.eq(False)).where(
auth_methods.id == Parameter('%s')))
args = [id]
if not can_view_others_auth_methods:
query = query.where(auth_methods.user_id == Parameter('%s'))
args.append(user_id)
if not can_view_deleted_auth_methods:
query = query.where(auth_methods.deleted.eq(False))
itgs.read_cursor.execute(query.get_sql(), args)
result = []
row = itgs.read_cursor.fetchone()
while row is not None:
result.append(row[0])
row = itgs.read_cursor.fetchone()
return JSONResponse(
status_code=200,
headers={
'x-request-cost': str(request_cost),
'Cache-Control':
'private, max-age=60, stale-while-revalidate=540'
},
content=models.AuthMethodPermissions(granted=result).dict())
def applications(authorization: str = Header(None)):
"""Returns application ids mapped to the corresponding application
names."""
with LazyItgs() as itgs:
if not users.helper.check_permissions_from_header(
itgs, authorization, 'logs')[0]:
return Response(status_code=403)
apps = Table('log_applications')
itgs.read_cursor.execute(
Query.from_(apps).select(apps.id, apps.name).get_sql())
result = {}
while True:
row = itgs.read_cursor.fetchone()
if row is None:
break
result[row[0]] = models.LogApplicationResponse(name=row[1])
return JSONResponse(
status_code=200,
content=models.LogApplicationsResponse(applications=result).dict(),
headers={
'Cache-Control':
'public, max-age=86400, stale-if-error=2419200'
})
def calculate_etag(itgs, loan_id) -> str:
"""Calculates a valid etag for the loan with the given id. If no such loan
exists this returns None.
"""
loans = Table('loans')
event_tables = [
Table(t) for t in
['loan_admin_events', 'loan_repayment_events', 'loan_unpaid_events']
]
q = (Query.from_(loans).select(
Greatest(loans.created_at, loans.unpaid_at, loans.deleted_at,
*[tbl.created_at for tbl in event_tables])))
for tbl in event_tables:
q = q.left_join(tbl).on(loans.id == tbl.loan_id)
q = q.where(loans.id == Parameter('%s'))
itgs.read_cursor.execute(q.get_sql(), (loan_id, ))
row = itgs.read_cursor.fetchone()
if row is None:
return None
(updated_at, ) = row
raw_str = f'{loan_id}-{updated_at.timestamp()}'
return hashlib.sha256(raw_str.encode('ASCII')).hexdigest()
def get_result_device_day_elements(list_id: List, start: str) -> float:
logging.debug('Получение таблицы балансов')
start_utc = datetime.strptime(start, '%Y-%m-%d').astimezone(pytz.UTC).replace(tzinfo=None)
p = Table('calc_balance', alias='p')
try:
q = (PostgreSQLQuery.from_(p)
.select(p.start_period)
.where(
(p.time_start_write == start_utc)
& (p.id_tu.isin(list_id)))
.orderby(p.dtp, order=Order.asc)
)
sql = q.get_sql()
except Exception as e:
logging.error("Error is ", e)
result_elements = 0
logging.debug(f'SQL: {sql}')
try:
result = engine.execute(sql)
for row in result:
result_elements = row[0]
except Exception as e:
logging.error("Error is ", e)
logging.debug("get_result_device_day_elements() OK...")
return result_elements
def get_points_for_uo(filters: List[str], inhibit_filters: List[str]) -> List:
re_user_filters = []
re_inhibit_user_filter = []
result_filter: List[str]
result_inhibit_filter: List[str]
for inhibit in inhibit_filters:
result_inhibit_filter = inhibit.split('\r\n')
re_inhibit_user_filter = [
w.replace('\\', '\\\\') for w in result_inhibit_filter
]
re_inhibit_user_filter = [
w.replace('*', "%") for w in re_inhibit_user_filter
]
for filter_user in filters:
result_filter = filter_user.split('\r\n')
re_user_filters = [w.replace('\\', '\\\\') for w in result_filter]
re_user_filters = [w.replace('*', "%") for w in re_user_filters]
logging.debug('Получение подстанций, присоединений для таблицы балансов')
p = Table('askue_rs_point', alias='p')
q = (PostgreSQLQuery.from_(p).select(p.object_name))
filter_sql = db_work.get_query_by_user_filter(q, result_filter,
result_inhibit_filter)
sql = filter_sql.get_sql()
result_elements = []
re_user_filters.extend(re_inhibit_user_filter)
logging.debug(f'SQL: {sql}')
try:
result = engine.execute(sql, re_user_filters)
for row in result:
if row[0].rfind('УО') != -1:
result_elements.append(row)
except Exception as e:
logging.error("Error is ", e)
logging.debug("get_points_for_uo() OK...")
return result_elements
def main():
conn = setup_connection()
cursor = conn.cursor()
tbls = Schema('information_schema').tables
cursor.execute(
Query.from_(tbls).where(tbls.table_type == 'BASE TABLE').where(
tbls.table_schema == 'public').select(tbls.table_name).get_sql(), )
rows = cursor.fetchall()
rows = map(lambda r: r[0], rows)
cursor.close()
conn.close()
cfg = settings.load_settings()
db_host = cfg['DATABASE_HOST']
db_port = int(cfg['DATABASE_PORT'])
db_user = cfg['DATABASE_USER']
db_pass = cfg['DATABASE_PASSWORD']
db_name = cfg['DATABASE_DBNAME']
old_pg_pass = os.environ.get('PGPASSWORD')
os.environ['PGPASSWORD'] = db_pass
for tbl in rows:
print('=' * 50)
print(f'DESCRIBE {tbl}')
os.system(f'psql -d {db_name} -h {db_host} -p {db_port} '
f'-U {db_user} -c "\\d+ {tbl}"')
print()
if old_pg_pass is not None:
os.environ['PGPASSWORD'] = old_pg_pass
else:
del os.environ['PGPASSWORD']
def test_postgres_query_uses_double_quote_chars(self):
q = PostgreSQLQuery.from_(self.table_abc).select(
self.table_abc.foo).groupby(
self.table_abc.foo).having(self.table_abc.buz == 'fiz')
self.assertEqual(
"SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'",
str(q))
def test_postgres_query_uses_double_quote_chars(self):
q = (PostgreSQLQuery.from_(self.table_abc).select(
self.table_abc.foo).groupby(
self.table_abc.foo).having(self.table_abc.buz == "fiz"))
self.assertEqual(
'SELECT "foo" FROM "abc" GROUP BY "foo" HAVING "buz"=\'fiz\'',
str(q))
def __init__(self, model):
super().__init__()
self.fields = model._meta.db_fields
self.model = model
if not hasattr(model._meta.db, 'query_class'):
# use PostgreSQLQuery if model doesn't have query_class
self.query = Query.from_(model._meta.table)
else:
self.query = model._meta.db.query_class.from_(model._meta.table)
self._prefetch_map = {} # type: Dict[str, Set[str]]
self._prefetch_queries = {} # type: Dict[str, QuerySet]
self._single = False # type: bool
self._get = False # type: bool
self._count = False # type: bool
self._db = None # type: Optional[BaseDBAsyncClient]
self._limit = None # type: Optional[int]
self._offset = None # type: Optional[int]
self._filter_kwargs = {} # type: Dict[str, Any]
self._orderings = [] # type: List[str]
self._q_objects_for_resolve = [] # type: List[Q]
self._distinct = False # type: bool
self._annotations = {} # type: Dict[str, Aggregate]
self._having = {} # type: Dict[str, Any]
self._available_custom_filters = {} # type: Dict[str, dict]
def store_letter_message(itgs: 'LazyItgs', user_id: int, letter_name: str, commit=False):
"""This function is responsible for storing that we sent an onboarding
letter message to the given user, where the subject and body were
fetched as if by `lbshared.responses.get_letter_response`
Arguments:
- `itgs (LazyItgs)`: The integrations to use to connect to networked
components.
- `user_id (int)`: The id of the user who we sent the message to
- `letter_name (str)`: The base part of the response for both the title and
subject. The title response name is formed by appending `_title` and the
subject is formed by appending `_body`
- `commit (bool)`: True to commit the change immediately, false not to
"""
(body_name, title_name) = (f'{letter_name}_body', f'{letter_name}_title')
responses = Table('responses')
itgs.read_cursor.execute(
Query.from_(responses)
.select(responses.id, responses.name)
.where(responses.name.isin((Parameter('%s'), Parameter('%s'))))
.get_sql(),
(body_name, title_name)
)
rows = itgs.read_cursor.fetchall()
if len(rows) != 2:
raise Exception(f'expected 2 rows for letter base {letter_name}, got {len(rows)}')
(body_id, title_id) = [r[0] for r in sorted(rows, key=lambda x: x[1])]
store_letter_message_with_id_and_names(
itgs, user_id, title_id, title_name,
body_id, body_name, commit=commit
)
def test_postgres_query_uses_double_quote_chars(self):
q = PostgreSQLQuery.from_(self.table_abc).select(
self.table_abc.foo
).groupby(
self.table_abc.foo
).having(
self.table_abc.buz == 'fiz'
)
self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
def test_delete_returning_str(self):
q1 = PostgreSQLQuery.from_(self.table_abc).where(
self.table_abc.foo == self.table_abc.bar
).delete().returning('id')
self.assertEqual('DELETE FROM "abc" WHERE "foo"="bar" RETURNING id', str(q1))
def test_postgres_query_uses_double_quote_chars(self):
q = PostgreSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_postgresql_query_uses_double_quote_chars(self):
q = PostgreSQLQuery.from_('abc').select('foo', 'bar')
self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
