def compile_currval(compile, expr, state):
"""Compile a currval.
This is a bit involved because we have to get escaping right. Here
are a few cases to keep in mind::
currval('thetable_thecolumn_seq')
currval('theschema.thetable_thecolumn_seq')
currval('"the schema".thetable_thecolumn_seq')
currval('theschema."the table_thecolumn_seq"')
currval('theschema."thetable_the column_seq"')
currval('"thetable_the column_seq"')
currval('"the schema"."the table_the column_seq"')
"""
state.push("context", COLUMN_PREFIX)
table = compile(expr.column.table, state, token=True)
state.pop()
column_name = compile(expr.column.name, state, token=True)
if table.endswith('"'):
table = table[:-1]
if column_name.endswith('"'):
column_name = column_name[1:-1]
return "currval('%s_%s_seq\"')" % (table, column_name)
elif column_name.endswith('"'):
column_name = column_name[1:-1]
if "." in table:
schema, table = table.rsplit(".", 1)
return "currval('%s.\"%s_%s_seq\"')" % (schema, table, column_name)
else:
return "currval('\"%s_%s_seq\"')" % (table, column_name)
else:
return "currval('%s_%s_seq')" % (table, column_name)
def compile_values(compile, expr, state):
col_names, col_types = zip(*expr.cols)
first_row = ", ".join(
"%s::%s" % (compile(value, state), type)
for value, type in zip(expr.values[0], col_types))
rows = [first_row] + [compile(value, state) for value in expr.values[1:]]
return "(VALUES (%s)) AS %s(%s)" % (
"), (".join(rows), expr.name, ', '.join(col_names))
def compile_returning(compile, expr, state):
state.push("context", COLUMN)
columns = compile(expr.insert.primary_columns, state)
state.pop()
state.push("precedence", 0)
insert = compile(expr.insert, state)
state.pop()
return "%s RETURNING %s" % (insert, columns)
def compile_returning(compile, expr, state):
state.push("context", COLUMN)
columns = expr.columns or expr.expr.primary_columns
columns = compile(columns, state)
state.pop()
state.push("precedence", 0)
expr = compile(expr.expr, state)
state.pop()
return "%s RETURNING %s" % (expr, columns)
def compile_type(compile, expr, state):
table = getattr(expr, "__storm_table__", None)
if table is None:
raise CompileError("Don't know how to compile %r" % expr)
if state.context is TABLE and issubclass(expr, ClassAlias):
cls_info = get_cls_info(expr)
return "%s AS %s" % (compile(cls_info.cls, state), table)
if isinstance(table, basestring):
return table
return compile(table, state)
def compile_concat(compile, concat, state):
left = compile(concat.left, state)
right = compile(concat.right, state)
if concat.db in ('sqlite', 'postgres'):
statement = "%s||%s"
elif concat.db == 'mysql':
statement = "CONCAT(%s, %s)"
else:
raise NotSupportedError("Unspported database (%s)" % concat.db)
return statement % (left, right)
def test_nested_classes(self):
"""Convoluted case checking that the model is right."""
class Class1(object):
__storm_table__ = "class1"
id = Property(primary=True)
class Class2(object):
__storm_table__ = Class1
id = Property(primary=True)
statement = compile(Class2)
self.assertEquals(statement, "class1")
alias = ClassAlias(Class2, "alias")
statement = compile(Select(alias.id))
self.assertEquals(statement, "SELECT alias.id FROM class1 AS alias")
def test_compile_in_select_with_reserved_keyword(self):
Alias = ClassAlias(self.Class, "select")
expr = Select(Alias.prop1, Alias.prop1 == 1, Alias)
statement = compile(expr)
self.assertEquals(statement,
'SELECT "select".column1 FROM "table" AS "select" '
'WHERE "select".column1 = ?')
def execute(self, async_conn):
"""Executes a query within an asyncronous psycopg2 connection
"""
if self.status == self.STATUS_CANCELLED:
return
self.status = self.STATUS_EXECUTING
# Async variant of Connection.execute() in storm/database.py
state = State()
statement = compile(self.expr, state)
stmt = convert_param_marks(statement, "?", "%s")
self._async_cursor = async_conn.cursor()
self._async_conn = async_conn
# This is postgres specific, see storm/databases/postgres.py
self._statement = stmt.encode('utf-8')
self._parameters = tuple(Connection.to_database(state.parameters))
trace("connection_raw_execute", self._conn,
self._async_cursor, self._statement, self._parameters)
self._async_cursor.execute(self._statement,
self._parameters)
# This can happen if another thread cancelled this while the cursor was
# executing. In that case, it is not interested in the retval anymore
if self.status == self.STATUS_CANCELLED:
return
self.status = self.STATUS_FINISHED
glib.idle_add(self._on_finish)
def test_compile_in_select(self):
expr = Select(self.ClassAlias.prop1, self.ClassAlias.prop1 == 1,
self.ClassAlias)
statement = compile(expr)
self.assertEquals(statement,
'SELECT alias.column1 FROM "table" AS alias '
'WHERE alias.column1 = ?')
def test_whereExpressions__asc(self):
"""For ascending sort order, whereExpressions() returns the
WHERE clause expression > memo.
"""
resultset = self.makeStormResultSet()
range_factory = StormRangeFactory(resultset, self.logError)
[where_clause] = range_factory.whereExpressions([Person.id], [1])
self.assertEquals('(Person.id) > (1)', compile(where_clause))
def compile_proxy(compile, proxy, state):
# Inject the join between the table of the class holding the proxy
# and the table of the class which is the target of the reference.
left_join = LeftJoin(proxy._reference._relation.local_cls,
proxy._remote_prop.table,
proxy._reference._relation.get_where_for_join())
state.auto_tables.append(left_join)
# And compile the remote property normally.
return compile(proxy._remote_prop, state)
def test_whereExpressions__two_sort_columns_asc_desc(self):
"""If the ascending sort column c1, the descending sort column
c2 and the memo values m1, m2 are specified, whereExpressions()
returns two expressions where the first expression is
c1 == m1 AND c2 < m2
and the second expression is
c1 > m1
"""
resultset = self.makeStormResultSet()
range_factory = StormRangeFactory(resultset, self.logError)
[where_clause_1, where_clause_2] = range_factory.whereExpressions(
[Person.id, Desc(Person.name)], [1, 'foo'])
self.assertEquals(
"Person.id = ? AND ((Person.name) < ('foo'))",
compile(where_clause_1))
self.assertEquals('(Person.id) > (1)', compile(where_clause_2))
def compile_list_variable(compile, list_variable, state):
elements = []
variables = list_variable.get(to_db=True)
if variables is None:
return "NULL"
if not variables:
return "'{}'"
for variable in variables:
elements.append(compile(variable, state))
return "ARRAY[%s]" % ",".join(elements)
def compile_bulkupdate(compile, update, state):
pairs = update.map.items()
state.push("context", COLUMN_NAME)
col_names = [compile(col, state, token=True) for col, val in pairs]
state.context = EXPR
col_values = [compile(val, state) for col, val in pairs]
sets = ["%s=%s" % (col, val) for col, val in zip(col_names, col_values)]
state.context = TABLE
tokens = ["UPDATE ", compile(update.table, state, token=True), " SET ",
", ".join(sets), " FROM "]
state.context = EXPR
# We don't want the values expression wrapped in parenthesis.
state.precedence = 0
tokens.append(compile(update.values, state, raw=True))
if update.where is not Undef:
tokens.append(" WHERE ")
tokens.append(compile(update.where, state, raw=True))
state.pop()
return "".join(tokens)
def test_lessThanOrGreaterThanExpression__desc(self):
# beforeOrAfterExpression() returns an expression
# (col1, col2,..) < (memo1, memo2...) for descending sort order.
resultset = self.makeStormResultSet()
range_factory = StormRangeFactory(resultset, self.logError)
expressions = [Desc(Person.id), Desc(Person.name)]
limits = [1, 'foo']
limit_expression = range_factory.lessThanOrGreaterThanExpression(
expressions, limits)
self.assertEqual(
"(Person.id, Person.name) < (1, 'foo')",
compile(limit_expression))
def raise_none_error(column):
if not column:
raise NoneError("None isn't acceptable as a value")
else:
from storm.expr import compile, CompileError
name = column.name
if column.table is not Undef:
try:
table = compile(column.table)
name = "%s.%s" % (table, name)
except CompileError:
pass
raise NoneError("None isn't acceptable as a value for %s" % name)
def test_whereExpressions__two_sort_columns_desc_desc(self):
"""If the descending sort columns c1, c2 and the memo values
m1, m2 are specified, whereExpressions() returns a WHERE
expressions comparing the tuple (c1, c2) with the memo tuple
(m1, m2):
(c1, c2) < (m1, m2)
"""
resultset = self.makeStormResultSet()
range_factory = StormRangeFactory(resultset, self.logError)
[where_clause] = range_factory.whereExpressions(
[Desc(Person.id), Desc(Person.name)], [1, 'foo'])
self.assertEquals(
"(Person.id, Person.name) < (1, 'foo')", compile(where_clause))
def compile_proxy(compile, proxy, state):
# References build the relation lazily so that they don't immediately
# try to resolve string properties. Unfortunately we have to check that
# here as well and make sure that at this point it's actually there.
# Maybe this should use the same trick as _remote_prop on Proxy
if proxy._reference._relation is None:
proxy._reference._build_relation()
# Inject the join between the table of the class holding the proxy
# and the table of the class which is the target of the reference.
left_join = LeftJoin(proxy._remote_prop.table, proxy._reference._relation.get_where_for_join())
state.auto_tables.append(left_join)
# And compile the remote property normally.
return compile(proxy._remote_prop, state)
def execute(self, async_conn):
"""Executes a query within an asyncronous psycopg2 connection
"""
# Async variant of Connection.execute() in storm/database.py
state = State()
statement = compile(self.expr, state)
stmt = convert_param_marks(statement, "?", "%s")
self._async_cursor = async_conn.cursor()
self._async_conn = async_conn
# This is postgres specific, see storm/databases/postgres.py
self._statement = stmt.encode("utf-8")
self._parameters = tuple(Connection.to_database(state.parameters))
trace("connection_raw_execute", self._conn, self._async_cursor, self._statement, self._parameters)
self._async_cursor.execute(self._statement, self._parameters)
def test_comparable_expr_subclass(self):
prop1 = self.SubClass.prop1
prop2 = self.SubClass.prop2
prop3 = self.SubClass.prop3
expr = Select(SQLRaw("*"), (prop1 == "value1") &
(prop2 == "value2") &
(prop3 == "value3"))
state = State()
statement = compile(expr, state)
self.assertEquals(statement, "SELECT * FROM mysubtable WHERE "
"mysubtable.column1 = ? AND "
"mysubtable.prop2 = ? AND "
"mysubtable.column3 = ?")
self.assertVariablesEqual(
state.parameters,
[CustomVariable("value1"),
CustomVariable("value2"),
CustomVariable("value3")])
def compile_set_expr_postgres(compile, expr, state):
if expr.order_by is not Undef:
# The following statement breaks in postgres:
# SELECT 1 AS id UNION SELECT 1 ORDER BY id+1
# With the error:
# ORDER BY on a UNION/INTERSECT/EXCEPT result must
# be on one of the result columns
# So we transform it into something close to:
# SELECT * FROM (SELECT 1 AS id UNION SELECT 1) AS a ORDER BY id+1
# Build new set expression without arguments (order_by, etc).
new_expr = expr.__class__()
new_expr.exprs = expr.exprs
new_expr.all = expr.all
# Make sure that state.aliases isn't None, since we want them to
# compile our order_by statement below.
no_aliases = state.aliases is None
if no_aliases:
state.push("aliases", {})
# Build set expression, collecting aliases.
set_stmt = SQLRaw("(%s)" % compile_set_expr(compile, new_expr, state))
# Build order_by statement, using aliases.
state.push("context", COLUMN_NAME)
order_by_stmt = SQLRaw(compile(expr.order_by, state))
state.pop()
# Discard aliases, if they were not being collected previously.
if no_aliases:
state.pop()
# Build wrapping select statement.
select = Select(SQLRaw("*"), tables=Alias(set_stmt), limit=expr.limit,
offset=expr.offset, order_by=order_by_stmt)
return compile_select(compile, select, state)
else:
return compile_set_expr(compile, expr, state)
def compile_count(compile, cast, state):
state.push("context", EXPR)
column = compile(cast.column, state)
state.pop()
return "CAST(%s AS %s)" % (column, cast.type)
def compile_type(compile, expr, state):
cls_info = get_cls_info(expr)
table = compile(cls_info.table, state)
if state.context is TABLE and issubclass(expr, ClassAlias):
return "%s AS %s" % (compile(cls_info.cls, state), table)
return table
def compile_columnselect(compile, expr, state):
state.push("context", EXPR)
select = compile(expr.select)
state.pop()
return "(%s)" % select
def compile_countdistinct(compile, countselect, state):
state.push("context", EXPR)
col = compile(countselect.columns)
state.pop()
return "count(distinct(%s))" % col
def test_compile(self):
statement = compile(self.ClassAlias)
self.assertEquals(statement, "alias")
def test_cls_alias_compile(Class):
Alias = ClassAlias(Class, "alias")
statement = compile(Alias)
assert statement == "alias"
def test_cls_alias_compile_with_reserved_keyword(Class):
Alias = ClassAlias(Class, "select")
statement = compile(Alias)
assert statement == '"select"'
def compile_array(compile, array, state):
state.push("context", EXPR)
args = compile(array.args, state)
state.pop()
return "ARRAY[%s]" % args
def test_cls_alias_compile_in_select_with_reserved_keyword(Class):
Alias = ClassAlias(Class, "select")
expr = Select(Alias.prop1, Alias.prop1 == 1, Alias)
statement = compile(expr)
assert statement == ('SELECT "select".column1 FROM "table" AS "select" '
'WHERE "select".column1 = ?')
def test_compile_with_reserved_keyword(self):
Alias = ClassAlias(self.Class, "select")
statement = compile(Alias)
self.assertEquals(statement, '"select"')
def test_compile(self):
statement = compile(self.ClassAlias)
self.assertEquals(statement, "alias")
def compile_count(compile, cast, state):
state.push("context", EXPR)
column = compile(cast.column, state)
state.pop()
return "CAST(%s AS %s)" % (column, cast.type)
def compile_array(compile, array, state):
state.push("context", EXPR)
args = compile(array.args, state)
state.pop()
return "ARRAY[%s]" % args
def compile_currval(compile, expr, state):
return "currval('%s_%s_seq')" % (compile(expr.column.table,
state), expr.column.name)
def test_compile_with_reserved_keyword(self):
Alias = ClassAlias(self.Class, "select")
statement = compile(Alias)
self.assertEquals(statement, '"select"')
def compile_type(compile, expr, state):
cls_info = get_cls_info(expr)
table = compile(cls_info.table, state)
if state.context is TABLE and issubclass(expr, ClassAlias):
return "%s AS %s" % (compile(cls_info.cls, state), table)
return table
def compile_columnselect(compile, expr, state):
state.push("context", EXPR)
select = compile(expr.select)
state.pop()
return "(%s)" % select
def test_cls_alias_compile_in_select(Class):
Alias = ClassAlias(Class, "alias")
expr = Select(Alias.prop1, Alias.prop1 == 1, Alias)
statement = compile(expr)
assert statement == ('SELECT alias.column1 FROM "table" AS alias '
'WHERE alias.column1 = ?')
def compile_countdistinct(compile, countselect, state):
state.push("context", EXPR)
col = compile(countselect.columns)
state.pop()
return "count(distinct(%s))" % col
def compile_currval(compile, expr, state):
return "currval('%s_%s_seq')" % (compile(expr.column.table, state),
expr.column.name)