class SQLiteBuilder(SQLBuilder):
dialect = 'SQLite'
def SELECT_FOR_UPDATE(builder, nowait, *sections):
assert not builder.indent and not nowait
return builder.SELECT(*sections)
def INSERT(builder, table_name, columns, values, returning=None):
if not values: return 'INSERT INTO %s DEFAULT VALUES' % builder.quote_name(table_name)
return SQLBuilder.INSERT(builder, table_name, columns, values, returning)
def TODAY(builder):
return "date('now', 'localtime')"
def NOW(builder):
return "datetime('now', 'localtime')"
def YEAR(builder, expr):
return 'cast(substr(', builder(expr), ', 1, 4) as integer)'
def MONTH(builder, expr):
return 'cast(substr(', builder(expr), ', 6, 2) as integer)'
def DAY(builder, expr):
return 'cast(substr(', builder(expr), ', 9, 2) as integer)'
def HOUR(builder, expr):
return 'cast(substr(', builder(expr), ', 12, 2) as integer)'
def MINUTE(builder, expr):
return 'cast(substr(', builder(expr), ', 15, 2) as integer)'
def SECOND(builder, expr):
return 'cast(substr(', builder(expr), ', 18, 2) as integer)'
def datetime_add(builder, funcname, expr, td):
assert isinstance(td, timedelta)
modifiers = []
seconds = td.seconds + td.days * 24 * 3600
sign = '+' if seconds > 0 else '-'
seconds = abs(seconds)
if seconds >= (24 * 3600):
days = seconds // (24 * 3600)
modifiers.append(", '%s%d days'" % (sign, days))
seconds -= days * 24 * 3600
if seconds >= 3600:
hours = seconds // 3600
modifiers.append(", '%s%d hours'" % (sign, hours))
seconds -= hours * 3600
if seconds >= 60:
minutes = seconds // 60
modifiers.append(", '%s%d minutes'" % (sign, minutes))
seconds -= minutes * 60
if seconds:
modifiers.append(", '%s%d seconds'" % (sign, seconds))
if not modifiers: return builder(expr)
return funcname, '(', builder(expr), modifiers, ')'
def DATE_ADD(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('date', expr, delta)
return 'datetime(julianday(', builder(expr), ') + ', builder(delta), ')'
def DATE_SUB(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('date', expr, -delta)
return 'datetime(julianday(', builder(expr), ') - ', builder(delta), ')'
def DATETIME_ADD(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('datetime', expr, delta)
return 'datetime(julianday(', builder(expr), ') + ', builder(delta), ')'
def DATETIME_SUB(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('datetime', expr, -delta)
return 'datetime(julianday(', builder(expr), ') - ', builder(delta), ')'
def MIN(builder, *args):
if len(args) == 0: assert False # pragma: no cover
elif len(args) == 1: fname = 'MIN'
else: fname = 'min'
return fname, '(', join(', ', imap(builder, args)), ')'
def MAX(builder, *args):
if len(args) == 0: assert False # pragma: no cover
elif len(args) == 1: fname = 'MAX'
else: fname = 'max'
return fname, '(', join(', ', imap(builder, args)), ')'
def RANDOM(builder):
return 'rand()' # return '(random() / 9223372036854775807.0 + 1.0) / 2.0'
PY_UPPER = make_unary_func('py_upper')
PY_LOWER = make_unary_func('py_lower')
class SQLiteBuilder(SQLBuilder):
dialect = 'SQLite'
def __init__(builder, provider, ast):
builder.json1_available = provider.json1_available
SQLBuilder.__init__(builder, provider, ast)
def SELECT_FOR_UPDATE(builder, nowait, *sections):
assert not builder.indent and not nowait
return builder.SELECT(*sections)
def INSERT(builder, table_name, columns, values, returning=None):
if not values:
return 'INSERT INTO %s DEFAULT VALUES' % builder.quote_name(
table_name)
return SQLBuilder.INSERT(builder, table_name, columns, values,
returning)
def TODAY(builder):
return "date('now', 'localtime')"
def NOW(builder):
return "datetime('now', 'localtime')"
def YEAR(builder, expr):
return 'cast(substr(', builder(expr), ', 1, 4) as integer)'
def MONTH(builder, expr):
return 'cast(substr(', builder(expr), ', 6, 2) as integer)'
def DAY(builder, expr):
return 'cast(substr(', builder(expr), ', 9, 2) as integer)'
def HOUR(builder, expr):
return 'cast(substr(', builder(expr), ', 12, 2) as integer)'
def MINUTE(builder, expr):
return 'cast(substr(', builder(expr), ', 15, 2) as integer)'
def SECOND(builder, expr):
return 'cast(substr(', builder(expr), ', 18, 2) as integer)'
def datetime_add(builder, funcname, expr, td):
assert isinstance(td, timedelta)
modifiers = []
seconds = td.seconds + td.days * 24 * 3600
sign = '+' if seconds > 0 else '-'
seconds = abs(seconds)
if seconds >= (24 * 3600):
days = seconds // (24 * 3600)
modifiers.append(", '%s%d days'" % (sign, days))
seconds -= days * 24 * 3600
if seconds >= 3600:
hours = seconds // 3600
modifiers.append(", '%s%d hours'" % (sign, hours))
seconds -= hours * 3600
if seconds >= 60:
minutes = seconds // 60
modifiers.append(", '%s%d minutes'" % (sign, minutes))
seconds -= minutes * 60
if seconds:
modifiers.append(", '%s%d seconds'" % (sign, seconds))
if not modifiers: return builder(expr)
return funcname, '(', builder(expr), modifiers, ')'
def DATE_ADD(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('date', expr, delta)
return 'datetime(julianday(', builder(expr), ') + ', builder(
delta), ')'
def DATE_SUB(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('date', expr, -delta)
return 'datetime(julianday(', builder(expr), ') - ', builder(
delta), ')'
def DATETIME_ADD(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('datetime', expr, delta)
return 'datetime(julianday(', builder(expr), ') + ', builder(
delta), ')'
def DATETIME_SUB(builder, expr, delta):
if isinstance(delta, timedelta):
return builder.datetime_add('datetime', expr, -delta)
return 'datetime(julianday(', builder(expr), ') - ', builder(
delta), ')'
def MIN(builder, *args):
if len(args) == 0: assert False # pragma: no cover
elif len(args) == 1: fname = 'MIN'
else: fname = 'min'
return fname, '(', join(', ', imap(builder, args)), ')'
def MAX(builder, *args):
if len(args) == 0: assert False # pragma: no cover
elif len(args) == 1: fname = 'MAX'
else: fname = 'max'
return fname, '(', join(', ', imap(builder, args)), ')'
def RANDOM(builder):
return 'rand()' # return '(random() / 9223372036854775807.0 + 1.0) / 2.0'
PY_UPPER = make_unary_func('py_upper')
PY_LOWER = make_unary_func('py_lower')
def FLOAT_EQ(builder, a, b):
a, b = builder(a), builder(b)
return 'abs(', a, ' - ', b, ') / coalesce(nullif(max(abs(', a, '), abs(', b, ')), 0), 1) <= 1e-14'
def FLOAT_NE(builder, a, b):
a, b = builder(a), builder(b)
return 'abs(', a, ' - ', b, ') / coalesce(nullif(max(abs(', a, '), abs(', b, ')), 0), 1) > 1e-14'
def JSON_QUERY(builder, expr, path):
fname = 'json_extract' if builder.json1_available else 'py_json_extract'
path_sql, has_params, has_wildcards = builder.build_json_path(path)
return 'py_json_unwrap(', fname, '(', builder(
expr), ', null, ', path_sql, '))'
# json_value_type_mapping = {unicode: 'text', bool: 'boolean', int: 'integer', float: 'real', Json: None}
def JSON_VALUE(builder, expr, path, type):
func_name = 'json_extract' if builder.json1_available else 'py_json_extract'
path_sql, has_params, has_wildcards = builder.build_json_path(path)
return func_name, '(', builder(expr), ', ', path_sql, ')'
def JSON_NONZERO(builder, expr):
return builder(
expr), ''' NOT IN ('null', 'false', '0', '""', '[]', '{}')'''
def JSON_ARRAY_LENGTH(builder, value):
func_name = 'json_array_length' if builder.json1_available else 'py_json_array_length'
return func_name, '(', builder(value), ')'
def JSON_CONTAINS(builder, expr, path, key):
path_sql, has_params, has_wildcards = builder.build_json_path(path)
return 'py_json_contains(', builder(
expr), ', ', path_sql, ', ', builder(key), ')'
class SQLiteBuilder(SQLBuilder):
dialect = 'SQLite'
least_func_name = 'min'
greatest_func_name = 'max'
value_class = SQLiteValue
def __init__(builder, provider, ast):
builder.json1_available = provider.json1_available
SQLBuilder.__init__(builder, provider, ast)
def SELECT_FOR_UPDATE(builder, nowait, skip_locked, *sections):
assert not builder.indent
return builder.SELECT(*sections)
def INSERT(builder, table_name, columns, values, returning=None):
if not values:
return 'INSERT INTO %s DEFAULT VALUES' % builder.quote_name(
table_name)
return SQLBuilder.INSERT(builder, table_name, columns, values,
returning)
def STRING_SLICE(builder, expr, start, stop):
if start is None:
start = ['VALUE', None]
if stop is None:
stop = ['VALUE', None]
return "py_string_slice(", builder(expr), ', ', builder(
start), ', ', builder(stop), ")"
def IN(builder, expr1, x):
if not x:
return '0 = 1'
if len(x) >= 1 and x[0] == 'SELECT':
return builder(expr1), ' IN ', builder(x)
op = ' IN (VALUES ' if expr1[0] == 'ROW' else ' IN ('
expr_list = [builder(expr) for expr in x]
return builder(expr1), op, join(', ', expr_list), ')'
def NOT_IN(builder, expr1, x):
if not x:
return '1 = 1'
if len(x) >= 1 and x[0] == 'SELECT':
return builder(expr1), ' NOT IN ', builder(x)
op = ' NOT IN (VALUES ' if expr1[0] == 'ROW' else ' NOT IN ('
expr_list = [builder(expr) for expr in x]
return builder(expr1), op, join(', ', expr_list), ')'
def TODAY(builder):
return "date('now', 'localtime')"
def NOW(builder):
return "datetime('now', 'localtime')"
def YEAR(builder, expr):
return 'cast(substr(', builder(expr), ', 1, 4) as integer)'
def MONTH(builder, expr):
return 'cast(substr(', builder(expr), ', 6, 2) as integer)'
def DAY(builder, expr):
return 'cast(substr(', builder(expr), ', 9, 2) as integer)'
def HOUR(builder, expr):
return 'cast(substr(', builder(expr), ', 12, 2) as integer)'
def MINUTE(builder, expr):
return 'cast(substr(', builder(expr), ', 15, 2) as integer)'
def SECOND(builder, expr):
return 'cast(substr(', builder(expr), ', 18, 2) as integer)'
def datetime_add(builder, funcname, expr, td):
assert isinstance(td, timedelta)
modifiers = []
seconds = td.seconds + td.days * 24 * 3600
sign = '+' if seconds > 0 else '-'
seconds = abs(seconds)
if seconds >= (24 * 3600):
days = seconds // (24 * 3600)
modifiers.append(", '%s%d days'" % (sign, days))
seconds -= days * 24 * 3600
if seconds >= 3600:
hours = seconds // 3600
modifiers.append(", '%s%d hours'" % (sign, hours))
seconds -= hours * 3600
if seconds >= 60:
minutes = seconds // 60
modifiers.append(", '%s%d minutes'" % (sign, minutes))
seconds -= minutes * 60
if seconds:
modifiers.append(", '%s%d seconds'" % (sign, seconds))
if not modifiers: return builder(expr)
return funcname, '(', builder(expr), modifiers, ')'
def DATE_ADD(builder, expr, delta):
if delta[0] == 'VALUE' and isinstance(delta[1], timedelta):
return builder.datetime_add('date', expr, delta[1])
return 'datetime(julianday(', builder(expr), ') + ', builder(
delta), ')'
def DATE_SUB(builder, expr, delta):
if delta[0] == 'VALUE' and isinstance(delta[1], timedelta):
return builder.datetime_add('date', expr, -delta[1])
return 'datetime(julianday(', builder(expr), ') - ', builder(
delta), ')'
def DATE_DIFF(builder, expr1, expr2):
return 'julianday(', builder(expr1), ') - julianday(', builder(
expr2), ')'
def DATETIME_ADD(builder, expr, delta):
if delta[0] == 'VALUE' and isinstance(delta[1], timedelta):
return builder.datetime_add('datetime', expr, delta[1])
return 'datetime(julianday(', builder(expr), ') + ', builder(
delta), ')'
def DATETIME_SUB(builder, expr, delta):
if delta[0] == 'VALUE' and isinstance(delta[1], timedelta):
return builder.datetime_add('datetime', expr, -delta[1])
return 'datetime(julianday(', builder(expr), ') - ', builder(
delta), ')'
def DATETIME_DIFF(builder, expr1, expr2):
return 'julianday(', builder(expr1), ') - julianday(', builder(
expr2), ')'
def RANDOM(builder):
return 'rand()' # return '(random() / 9223372036854775807.0 + 1.0) / 2.0'
PY_UPPER = make_unary_func('py_upper')
PY_LOWER = make_unary_func('py_lower')
def FLOAT_EQ(builder, a, b):
a, b = builder(a), builder(b)
return 'abs(', a, ' - ', b, ') / coalesce(nullif(max(abs(', a, '), abs(', b, ')), 0), 1) <= 1e-14'
def FLOAT_NE(builder, a, b):
a, b = builder(a), builder(b)
return 'abs(', a, ' - ', b, ') / coalesce(nullif(max(abs(', a, '), abs(', b, ')), 0), 1) > 1e-14'
def JSON_QUERY(builder, expr, path):
fname = 'json_extract' if builder.json1_available else 'py_json_extract'
path_sql, has_params, has_wildcards = builder.build_json_path(path)
return 'py_json_unwrap(', fname, '(', builder(
expr), ', null, ', path_sql, '))'
json_value_type_mapping = {
unicode: 'text',
bool: 'integer',
int: 'integer',
float: 'real'
}
def JSON_VALUE(builder, expr, path, type):
func_name = 'json_extract' if builder.json1_available else 'py_json_extract'
path_sql, has_params, has_wildcards = builder.build_json_path(path)
type_name = builder.json_value_type_mapping.get(type)
result = func_name, '(', builder(expr), ', ', path_sql, ')'
if type_name is not None:
result = 'CAST(', result, ' as ', type_name, ')'
return result
def JSON_NONZERO(builder, expr):
return builder(
expr), ''' NOT IN ('null', 'false', '0', '""', '[]', '{}')'''
def JSON_ARRAY_LENGTH(builder, value):
func_name = 'json_array_length' if builder.json1_available else 'py_json_array_length'
return func_name, '(', builder(value), ')'
def JSON_CONTAINS(builder, expr, path, key):
path_sql, has_params, has_wildcards = builder.build_json_path(path)
return 'py_json_contains(', builder(
expr), ', ', path_sql, ', ', builder(key), ')'
def ARRAY_INDEX(builder, col, index):
return 'py_array_index(', builder(col), ', ', builder(index), ')'
def ARRAY_CONTAINS(builder, key, not_in, col):
return ('NOT ' if not_in else ''
), 'py_array_contains(', builder(col), ', ', builder(key), ')'
def ARRAY_SUBSET(builder, array1, not_in, array2):
return ('NOT ' if not_in else ''), 'py_array_subset(', builder(
array2), ', ', builder(array1), ')'
def ARRAY_LENGTH(builder, array):
return 'py_array_length(', builder(array), ')'
def ARRAY_SLICE(builder, array, start, stop):
return 'py_array_slice(', builder(array), ', ', \
builder(start) if start else 'null', ',',\
builder(stop) if stop else 'null', ')'
def MAKE_ARRAY(builder, *items):
return 'py_make_array(', join(', ',
(builder(item) for item in items)), ')'
