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 test_insert_returning_functions(self):
query = PostgreSQLQuery.into(self.table_abc).insert(1).returning(
Cast(self.table_abc.f1, "int"))
self.assertEqual(
'INSERT INTO "abc" VALUES (1) RETURNING CAST("f1" AS INT)',
str(query))
def inside_operation(a, b):
path = "{{{}}}".format(selector.replace(LOOKUP_SEP, ','))
a = a.get_path_text_value(path)
type_b = type(b)
if type_b in AsyncpgFilter.TYPE_MAP:
a = Cast(a, AsyncpgFilter.TYPE_MAP[type_b])
return opr(a, b)
async def createOrder(self, order: Order.CreateInfo) -> int:
if len(order.products) == 0: raise Exception('No products')
orderSql = Query.into(orders).columns(
orders.address, orders.name, orders.surname, orders.user_id,
orders.status, orders.shipping_price).insert(
order.address, order.name, order.surname, order.userId,
Cast('NEW', 'ORDER_STATUS'),
order.shippingPrice).returning(orders.id)
productsSql = Query.into(order_product_link).columns(
order_product_link.order_id, order_product_link.product_id,
order_product_link.amount)
async with databaseClient.transaction() as t:
orderId = (await t.query(orderSql.get_sql()))[0][0]
for prodId, amount in order.products.items():
productsSql = productsSql.insert(orderId, prodId, amount)
await t.execute(productsSql.get_sql())
return orderId
def insensitive_exact(field: Term, value: str) -> Criterion:
return Upper(Cast(field, SqlTypes.VARCHAR)).eq(Upper(f"{value}"))
def ends_with(field: Term, value: str) -> Criterion:
return Cast(field, SqlTypes.VARCHAR).like(f"%{value}")
def starts_with(field: Term, value: str) -> Criterion:
return Cast(field, SqlTypes.VARCHAR).like(f"{value}%")
def contains(field: Term, value: str) -> Criterion:
return Cast(field, SqlTypes.VARCHAR).like(f"%{value}%")
def insensitive_ends_with(field: Term, value: str) -> Criterion:
return Upper(Cast(field, SqlTypes.VARCHAR)).like(Upper(f"%{value}"))
def parse_sources(self, config_name: str,
sources: List[Dict]) -> List[Dict]:
sql_sources = []
for source in sources:
if source.get("raw", None) is not None:
sql_sources.append(source["raw"])
else:
# sql query builder
table = Table(source["table"]["name"]).as_(source["table"].get(
"alias", source["table"]["name"]))
fields = []
for field in source["fields"]:
function = field.get("function", None)
if function is None:
value = Field(
field["name"].split(".")[1],
table=Table(field["name"].split(".")[0]),
).as_(field.get("alias", field["name"]))
if cast_is_required(field):
value = Cast(value, field.get("cast", None))\
.as_(field.get("alias", field["name"]))
fields.append(value)
else:
f = Field(
field["name"].split(".")[1],
table=Table(field["name"].split(".")[0]),
).as_(field.get("alias", field["name"]))
func = SQL_FUNCTION_MAPPING[function](f).as_(
field.get("alias", field["name"])).as_(
field.get("alias", field["name"]))
if cast_is_required(field):
func = Cast(func, field.get("cast", None))\
.as_(field.get("alias", field["name"]))
fields.append(func)
query = Query.from_(table)
# parse tables to join
for join_table_config in source.get("join", []):
join_table = Table(join_table_config["table"]["name"]).as_(
join_table_config["table"].get(
"alias", join_table_config["table"]["name"]))
# parse fields to join the table on
join_on_fields = []
for field in join_table_config["on"]:
table_name, field_name = field.split(".")
if table_name == join_table.get_table_name():
join_on_fields.append(
Field(field_name, table=join_table))
elif table_name == table.get_table_name():
join_on_fields.append(
Field(field_name, table=table))
else:
raise ExportConfigError(
f'ON field name "{field}" in configuration for "{config_name}" '
f'does not recognize table "{table_name}".')
if join_table_config[
"cond"] in COMPARISON_OPERATORS_MAPPING.keys():
on_cond = COMPARISON_OPERATORS_MAPPING[
join_table_config["cond"]](join_on_fields[0],
join_on_fields[1])
else:
on_cond = SQL_SPATIAL_JOIN_MAPPING[
join_table_config["cond"]](join_on_fields[0],
join_on_fields[1])
query = query.join(
join_table, JoinType[
join_table_config["type"].lower()]).on(on_cond)
# parse GROUP BY parameters
for group_by_field in source.get("group_by", []):
query = query.groupby(Field(group_by_field))
query = query.select(*fields)
# add RAW statements provided by a user
for raw_statement in [
source.get("filter", ""),
source.get("having", ""),
]:
if raw_statement:
query = str(query) + " " + raw_statement
# append SQL query string to the configuration sources
sql_sources.append(str(query))
return sql_sources
def _mysql_json_is_null(left: Term, is_null: bool):
if is_null:
return operator.eq(left, Cast("null", "JSON"))
else:
return not_equal(left, Cast("null", "JSON"))
def insensitive_ends_with(field: Term, value: str) -> Criterion:
return Like(Upper(Cast(field, SqlTypes.VARCHAR)),
field.wrap_constant(Upper(f"%{escape_like(value)}")))
def ends_with(field: Term, value: str) -> Criterion:
return Like(Cast(field, SqlTypes.VARCHAR),
field.wrap_constant(f"%{escape_like(value)}"))
def read_account_points(self, rv: int,
id_component_type: int) -> List[AccountPoint]:
"""
Выполняет чтение всех точек учета
:return: массив точек учета
"""
a = Table('AccountPoint', alias='a')
t = Table('Tag', alias='t')
n = Table('Node', alias='n')
n2 = Table('Node', alias='n2')
n3 = Table('Node', alias='n3')
n4 = Table('Node', alias='n4')
p = Table('ObjPassport', alias='p')
c = Table('ComponentType', alias='c')
s = Table('DataString', alias='c')
sub_query = (
Query.from_(a).join(
t, how=JoinType.inner).on(a.Id == t.AccountId).join(
n, how=JoinType.inner).on(a.DriverId == n.Id).join(
c,
how=JoinType.inner).on(n.IdComponentType == c.Id).join(
n2, how=JoinType.inner).on(a.Id == n2.Id).join(
n3, how=JoinType.inner).
on((n2.IdOwn == n3.IdOwn)
& (n3.BrowseName == 'Diagnostic')).join(
n4,
how=JoinType.inner).on((n3.Id == n4.IdOwn)
& (n4.BrowseName == 'SerialNumber')).
where((a.Del == self._FALSE_) & (t.TagCategory == 'CounterAplus')
& (t.TagName == 'Day1') & (a.Rv > 0)))
if self.__needRvCast:
sub_query = sub_query.select(a.Id, a.DisplayName, t.IdTagDef,
a.DriverId, c.Guid,
Cast(a.Rv, 'BIGINT',
'Rv'), n3.BrowseName,
n4.Id.as_('serial_num'))
else:
sub_query = sub_query.select(a.Id, a.DisplayName, t.IdTagDef,
a.DriverId, c.Guid,
a.Rv, n3.BrowseName,
n4.Id.as_('serial_num'))
ap = AliasedQuery("ap")
query = (Query.select(
ap.Id, ap.DisplayName, ap.IdTagDef, ap.Rv, p.PostIndex, p.FlatS,
p.Flat, p.Region, p.Country, ap.serial_num,
Case().when(
ap.Guid == 'est.by:Bus.GranDrvClientImpl', 'СС-301').when(
ap.Guid == 'est.by:Bus.Gran101DrvClientImpl',
'СС-101').when(
ap.Guid == 'est.by:Bus.EmeraDrvClientImpl',
'СЕ-102').else_("UNKNOWN").as_('Driver')).with_(
sub_query, "ap").from_(ap).join(n,
how=JoinType.left).
on((n.IdOwn == ap.Id)
& (n.IdComponentType == id_component_type)).join(
p, how=JoinType.left).on(n.Id == p.Id))
sql = query.get_sql()
ret_val: List[AccountPoint] = []
try:
self._logger.debug(f'SQL: {sql}')
result = self._engine_main.execute(sql)
for row in result:
data = AccountPoint(row['Id'], row['DisplayName'],
row['IdTagDef'], row['PostIndex'],
row['FlatS'], row['Flat'], row['Region'],
row['Country'], row['serial_num'],
row['Driver'], row['Rv'])
ret_val.append(data)
except Exception as e:
self._logger.error(e)
return ret_val
def insensitive_contains(field: Term, value: str) -> Criterion:
return Upper(Cast(field, SqlTypes.VARCHAR)).like(Upper(f"%{value}%"))
def convert_date(self, dt: datetime) -> Function:
return Cast(dt, 'datetimeoffset')
def index_loans(request: Request,
id: int = 0,
after_time: int = 0,
before_time: int = 0,
borrower_id: int = 0,
lender_id: int = 0,
includes_user_id: int = 0,
borrower_name: str = '',
lender_name: str = '',
includes_user_name: str = '',
principal_cents: int = 0,
principal_repayment_cents: int = -1,
unpaid: int = -1,
repaid: int = -1,
format: int = 2,
limit: int = 10):
id = _zero_to_none(id)
after_time = _zero_to_none(after_time)
before_time = _zero_to_none(before_time)
borrower_id = _zero_to_none(borrower_id)
lender_id = _zero_to_none(lender_id)
includes_user_id = _zero_to_none(includes_user_id)
borrower_name = _blank_to_none(borrower_name)
lender_name = _blank_to_none(lender_name)
includes_user_name = _blank_to_none(includes_user_name)
principal_cents = _zero_to_none(principal_cents)
principal_repayment_cents = _neg1_to_none(principal_repayment_cents)
unpaid = _neg1_to_none(unpaid)
repaid = _neg1_to_none(repaid)
limit = _zero_to_none(limit)
attempt_request_cost = 5
headers = {'x-request-cost': str(attempt_request_cost)}
with LazyItgs() as itgs:
auth = find_bearer_token(request)
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, auth, ratelimit_helper.RATELIMIT_PERMISSIONS)
resp = try_handle_deprecated_call(itgs, request, SLUG, user_id=user_id)
if resp is not None:
return resp
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
attempt_request_cost):
return JSONResponse(content=RATELIMIT_RESPONSE.dict(),
status_code=429,
headers=headers)
if limit is not None and (limit < 0 or limit >= 1000):
return JSONResponse(content=PHPErrorResponse(errors=[
PHPError(
error_type='INVALID_PARAMETER',
error_message=(
'Limit must be 0 or a positive integer less than 1000'
))
]).dict(),
status_code=400)
if limit is None and user_id is None:
headers[
'x-limit-warning'] = 'unauthed requests limit=0 replaced with limit=100'
limit = 100
if format not in (0, 1, 2, 3):
return JSONResponse(content=PHPErrorResponse(errors=[
PHPError(error_type='INVALID_PARAMETER',
error_message=('Format must be 0, 1, 2, or 3'))
]).dict(),
status_code=400)
loans = Table('loans')
if limit is None:
real_request_cost = 100
else:
real_request_cost = min(100, limit)
if format == 0:
real_request_cost = math.ceil(math.log(real_request_cost + 1))
elif format < 3:
# Cost needs to be greater than loans show
real_request_cost = 25 + real_request_cost * 2
else:
# We need to ensure the cost is greater than using the /users show
# endpoint for getting usernames
real_request_cost = 25 + math.ceil(real_request_cost * 4.1)
headers['x-request-cost'] = str(attempt_request_cost +
real_request_cost)
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
real_request_cost):
return JSONResponse(content=RATELIMIT_RESPONSE.dict(),
status_code=429,
headers=headers)
moneys = Table('moneys')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
usrs = Table('users')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
query = (Query.from_(loans).where(loans.deleted_at.isnull()).orderby(
loans.id, order=Order.desc))
params = []
joins = set()
def _add_param(val):
params.append(val)
return Parameter(f'${len(params)}')
def _ensure_principals():
nonlocal query
if 'principals' in joins:
return
joins.add('principals')
query = (query.join(principals).on(
principals.id == loans.principal_id))
def _ensure_principal_repayments():
nonlocal query
if 'principal_repayments' in joins:
return
joins.add('principal_repayments')
query = (query.join(principal_repayments).on(
principal_repayments.id == loans.principal_repayment_id))
def _ensure_lenders():
nonlocal query
if 'lenders' in joins:
return
joins.add('lenders')
query = (query.join(lenders).on(lenders.id == loans.lender_id))
def _ensure_borrowers():
nonlocal query
if 'borrowers' in joins:
return
joins.add('borrowers')
query = (query.join(borrowers).on(
borrowers.id == loans.borrower_id))
if id is not None:
query = query.where(loans.id == _add_param(id))
if after_time is not None:
query = (query.where(loans.created_at > _add_param(
datetime.fromtimestamp(after_time / 1000.0))))
if before_time is not None:
query = (query.where(loans.created_at < _add_param(
datetime.fromtimestamp(before_time / 1000.0))))
if principal_cents is not None:
_ensure_principals()
query = (query.where(
principals.amount_usd_cents == _add_param(principal_cents)))
if principal_repayment_cents is not None:
_ensure_principal_repayments()
query = (query.where(principal_repayments.amount_usd_cents ==
_add_param(principal_repayment_cents)))
if borrower_id is not None:
query = (query.where(loans.borrower_id == _add_param(borrower_id)))
if lender_id is not None:
query = (query.where(loans.lender_id == _add_param(lender_id)))
if includes_user_id is not None:
prm = _add_param(includes_user_id)
query = (query.where((loans.borrower_id == prm)
| (loans.lender_id == prm)))
if borrower_name is not None:
_ensure_borrowers()
query = (query.where(
borrowers.username == _add_param(borrower_name.lower())))
if lender_name is not None:
_ensure_lenders()
query = (query.where(
lenders.username == _add_param(lender_name.lower())))
if includes_user_name is not None:
_ensure_lenders()
_ensure_borrowers()
prm = _add_param(includes_user_name)
query = (query.where((lenders.username == prm)
| (borrowers.username == prm)))
if unpaid is not None:
if unpaid:
query = query.where(loans.unpaid_at.notnull())
else:
query = query.where(loans.unpaid_at.isnull())
if repaid is not None:
if repaid:
query = query.where(loans.repaid_at.notnull())
else:
query = query.where(loans.repaid_at.isnull())
if limit is not None:
query = query.limit(limit)
query = query.select(loans.id)
if format > 0:
_ensure_principals()
_ensure_principal_repayments()
event_tables = (Table('loan_repayment_events'),
Table('loan_unpaid_events'),
Table('loan_admin_events'))
latest_events = Table('latest_events')
query = (query.with_(
Query.from_(loans).select(
loans.id.as_('loan_id'),
Greatest(loans.created_at,
*(tbl.created_at for tbl in event_tables
)).as_('latest_event_at')).groupby(loans.id),
'latest_events').left_join(latest_events).on(
latest_events.loan_id == loans.id).select(
loans.lender_id, loans.borrower_id,
principals.amount_usd_cents,
principal_repayments.amount_usd_cents, (Case().when(
loans.unpaid_at.isnull(), 'false').else_('true')),
Cast(
Extract('epoch', loans.created_at) * 1000,
'bigint'),
Cast(
Extract('epoch', latest_events.latest_event_at) *
1000, 'bigint')))
if format == 3:
creation_infos = Table('loan_creation_infos')
_ensure_borrowers()
_ensure_lenders()
query = (query.join(creation_infos).on(
creation_infos.loan_id == loans.id).select(
Function('SUBSTRING', creation_infos.parent_fullname,
4),
Function('SUBSTRING', creation_infos.comment_fullname,
4), lenders.username, borrowers.username))
sql, args = convert_numbered_args(query.get_sql(), params)
headers['Cache-Control'] = 'public, max-age=600'
if format == 0:
return _UltraCompactResponse((sql, args), headers,
'LOANS_ULTRACOMPACT')
elif format == 1:
return _CompactResponse((sql, args), headers, 'LOANS_COMPACT')
elif format == 2:
return _StandardResponse((sql, args), headers, 'LOANS_STANDARD')
else:
return _ExtendedResponse((sql, args), headers, 'LOANS_EXTENDED')
