def where(self, _where: str, *items: Any):
if len(items) == 0 or '?' in _where:
try:
ctx = Ctx(self.paramOffset, items)
sql = self.validator.validate_where(_where, ctx)
except ValidationError as err:
raise UserWarning(f'invalid WHERE: {err}') from None
value = {'sql': sql, 'params': ctx.args}
elif len(items) <= 2:
if len(items) == 1:
if isinstance(items[0], list):
operator = 'IN'
else:
operator = '='
val = items[0]
if val is None:
operator = 'IS'
else:
operator = items[0]
val = items[1]
_where += f' {operator}'
if val is None:
_where += ' NULL'
params = []
else:
jsonbTest = '->' in _where
if operator == 'IN' or operator == 'NOT IN':
params = []
vs = []
for v in val:
processed, _ = process_value(v)
if processed == '?' or jsonbTest:
params.append(v)
vs.append('?')
else:
vs.append(str(processed))
_where += ' (' + ', '.join(vs) + ')'
else:
params = []
processed, _ = process_value(val)
if processed == '?' or jsonbTest:
params.append(val)
_where += ' ?'
else:
_where += f' {processed}'
try:
ctx = Ctx(self.paramOffset, params)
sql = self.validator.validate_where(_where, ctx)
except ValidationError as err:
raise UserWarning(f'invalid WHERE: {err}') from None
value = {'sql': sql, 'params': ctx.args}
else:
raise UserWarning(f"Invalid WHERE: {_where} {items}")
self.paramOffset += len(value['params'])
self.append(WhereToken(value))
def validate_value_list(self, ctx: Ctx, values: List[Any]) -> str:
replaces = {}
transformedValues = []
args = []
for val in values:
if isinstance(val, datetime.datetime) or isinstance(
val, datetime.date):
val, _ = process_value(str(val.astimezone(UTC)))
replaces[val] = f'{val}::timestamptz'
elif isinstance(val, asyncpg.pgproto.pgproto.UUID):
val = quote_literal(str(val))
replaces[val] = f'{val}::uuid'
else:
val, p = process_value(val)
if p is not None:
args.append(p)
val = str(val)
transformedValues.append(val)
nestedCtx = Ctx(ctx.param_offset + len(ctx.args), args)
result = _value_list.parse(','.join(transformedValues), nestedCtx)
for rep in replaces:
result = result.replace(rep, replaces[rep])
ctx.args.extend(args)
return result
def rrule(self, name: str, columns: List[str], values: List[Any]):
try:
name = self.validator.validate_identifier(name)
sqlColumns = self.validator.validate_rrule_columns(columns)
sqlValues = []
args = []
for row in values:
ctx = Ctx(self.paramOffset, [])
occurrences = slice(100000)
rrulesetVal = row[0]
sliceVal = row[1]
afterVal = row[2]
beforeVal = row[3]
betweenVal = row[4]
if sliceVal is not None:
occurrences = sliceVal
del row[0] # del rruleset
del row[0] # del rrule_slice
del row[0] # del rrule_after
del row[0] # del rrule_before
del row[0] # del rrule_between
sqlVal = self.validator.validate_rrule_values(
ctx, row, rrulesetVal, occurrences, afterVal, beforeVal, betweenVal)
if sqlVal:
sqlValues.append(sqlVal)
self.paramOffset += len(ctx.args)
args += ctx.args
except ValidationError as err:
raise UserWarning(f'invalid RRULE: {err}') from None
self.append(RruleToken(
{'name': name, 'columns': sqlColumns, 'values': ', '.join(sqlValues), 'params': args}))
def limit(self, size: int):
try:
ctx = Ctx(self.paramOffset, [size])
sql = self.validator.validate_limit(str(size), ctx)
except ValidationError as err:
raise UserWarning(f'invalid LIMIT: {err}') from None
self.paramOffset += 1
self.append(LimitToken({'sql': sql, 'params': ctx.args}))
def update(self, sql: str, *params: Any):
try:
ctx = Ctx(self.paramOffset, params)
sql = self.validator.validate_update(sql, ctx)
self.paramOffset += len(ctx.args)
except ValidationError as err:
raise UserWarning(f'invalid UPDATE: {err}') from None
value = {'sql': sql, 'params': ctx.args}
self.append(UpdateToken(value))
self.add_start(StartUpdateToken())
def on_conflict(self, *items):
try:
ctx = Ctx(self.paramOffset, items[2:])
target = self.validator.validate_conflict_target(items[0])
action = self.validator.validate_conflict_action(items[1], ctx)
self.paramOffset += len(ctx.args)
params = ctx.args
except ValidationError as err:
raise UserWarning(f'invalid ON CONFLICT: {err}') from None
self.append(
OnConflictToken({
'target': target,
'action': action,
'params': params
}))
def with_values(self, name: str, columns: List[str], values: List[Any]):
try:
name = self.validator.validate_identifier(name)
sqlColumns = self.validator.validate_with_columns(columns)
sqlValues = []
args = []
for row in values:
ctx = Ctx(self.paramOffset, [])
sqlVal = self.validator.validate_with_values(ctx, row)
if sqlVal:
sqlValues.append(sqlVal)
self.paramOffset += len(ctx.args)
args += ctx.args
except ValidationError as err:
raise UserWarning(f'invalid WITH VALUES: {err}') from None
self.append(WithValuesToken(
{'name': name, 'columns': sqlColumns, 'values': ', '.join(sqlValues), 'params': args}))
def validate_rrule_values(self, ctx: Ctx, values: List[Any],
rrulesetVal: rrule.rruleset,
occurrences: slice) -> str:
row = [None] # slot for rrule timestamp
args = []
for val in values:
val, p = process_value(val)
if p is not None:
args.append(p)
row.append(str(val))
results = []
# set a limit in case the rrule is unbound
for tm in rrulesetVal[occurrences]:
row[0], _ = process_value(str(tm.astimezone(UTC)))
nestedCtx = Ctx(ctx.param_offset + len(ctx.args), args)
rowTmpl = '(' + _value_list.parse(','.join(row), nestedCtx) + ')'
results.append(rowTmpl.replace(row[0], f'{row[0]}::timestamptz'))
ctx.args.extend(args)
return ', '.join(results)
def join(self, tbl: str, *cond: Any):
if len(cond) == 0:
raise UserWarning(f'JOIN cannot be empty')
if len(cond) == 1 or '?' in cond[0]:
_join = cond[0]
params = cond[1:]
elif len(cond) == 3:
_join = f'{cond[0]} {cond[1]} {cond[2]}'
params = []
else:
raise UserWarning(f"Invalid JOIN: {tbl} {cond}")
try:
ctx = Ctx(self.paramOffset, params)
sql = self.validator.validate_join(tbl, _join, ctx)
except ValidationError as err:
raise UserWarning(f'invalid JOIN: {err}') from None
value = {'sql': sql, 'params': ctx.args}
self.paramOffset += len(value['params'])
self.append(JoinToken(value))
def insert(self, columns: List[str], values: List[Any]):
try:
sqlColumns = self.validator.validate_insert_columns(columns)
sqlValues = []
args = []
for row in values:
ctx = Ctx(self.paramOffset, [])
sqlValues.append(
self.validator.validate_insert_values(row, ctx))
self.paramOffset += len(ctx.args)
args += ctx.args
except ValidationError as err:
raise UserWarning(f'invalid INSERT: {err}') from None
columns.sort()
key = ','.join(columns)
self.append(
InsertToken({
'columns': sqlColumns,
'values': ', '.join(sqlValues),
'params': args,
'key': key
}))
self.add_start(StartInsertToken())
def validate_schema(self, s: str) -> str:
return _schema.parse(s, Ctx())
def validate_group_by(self, s: str) -> str:
return _field.parse(s, Ctx())
def validate_order_by(self, s: str) -> str:
return _order_by.parse(s, Ctx())
def validate_select(self, s: str) -> str:
return _select.parse(s, Ctx())
def validate_tablename(self, s: str) -> str:
return _table.parse(s, Ctx())
def validate_create(self, s: str) -> str:
return _create.parse(s, Ctx())
def validate_alter(self, s: str) -> str:
return _alter.parse(s, Ctx())
def validate_conflict_target(self, s: str) -> str:
return _conflict_target.parse(s, Ctx())
