Ejemplo n.º 1
0
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
Ejemplo n.º 2
0
    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))
Ejemplo n.º 3
0
        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)
Ejemplo n.º 4
0
    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
Ejemplo n.º 5
0
def insensitive_exact(field: Term, value: str) -> Criterion:
    return Upper(Cast(field, SqlTypes.VARCHAR)).eq(Upper(f"{value}"))
Ejemplo n.º 6
0
def ends_with(field: Term, value: str) -> Criterion:
    return Cast(field, SqlTypes.VARCHAR).like(f"%{value}")
Ejemplo n.º 7
0
def starts_with(field: Term, value: str) -> Criterion:
    return Cast(field, SqlTypes.VARCHAR).like(f"{value}%")
Ejemplo n.º 8
0
def contains(field: Term, value: str) -> Criterion:
    return Cast(field, SqlTypes.VARCHAR).like(f"%{value}%")
Ejemplo n.º 9
0
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
Ejemplo n.º 11
0
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"))
Ejemplo n.º 12
0
def insensitive_ends_with(field: Term, value: str) -> Criterion:
    return Like(Upper(Cast(field, SqlTypes.VARCHAR)),
                field.wrap_constant(Upper(f"%{escape_like(value)}")))
Ejemplo n.º 13
0
def ends_with(field: Term, value: str) -> Criterion:
    return Like(Cast(field, SqlTypes.VARCHAR),
                field.wrap_constant(f"%{escape_like(value)}"))
Ejemplo n.º 14
0
    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
Ejemplo n.º 15
0
def insensitive_contains(field: Term, value: str) -> Criterion:
    return Upper(Cast(field, SqlTypes.VARCHAR)).like(Upper(f"%{value}%"))
Ejemplo n.º 16
0
 def convert_date(self, dt: datetime) -> Function:
     return Cast(dt, 'datetimeoffset')
Ejemplo n.º 17
0
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')