def test_like_case(self):
expr = Like("name", "value")
statement = compile(expr)
self.assertEquals(statement, "? LIKE ?")
expr = Like("name", "value", case_sensitive=True)
statement = compile(expr)
self.assertEquals(statement, "? LIKE ?")
expr = Like("name", "value", case_sensitive=False)
statement = compile(expr)
self.assertEquals(statement, "? ILIKE ?")
def test_like_case(self):
expr = Like("name", "value")
statement = compile(expr)
assert statement == "? LIKE ?"
expr = Like("name", "value", case_sensitive=True)
statement = compile(expr)
assert statement == "? LIKE ?"
expr = Like("name", "value", case_sensitive=False)
statement = compile(expr)
assert statement == "? ILIKE ?"
def test_like_case(self):
expr = Like("name", "value")
statement = compile(expr)
self.assertEquals(statement, "? LIKE ?")
expr = Like("name", "value", case_sensitive=True)
statement = compile(expr)
self.assertEquals(statement, "? LIKE ?")
expr = Like("name", "value", case_sensitive=False)
statement = compile(expr)
self.assertEquals(statement, "? ILIKE ?")
def test_array_support_with_empty(self):
try:
self.connection.execute("DROP TABLE array_test")
self.connection.commit()
except:
self.connection.rollback()
self.connection.execute("CREATE TABLE array_test "
"(id SERIAL PRIMARY KEY, a INT[])")
variable = ListVariable(IntVariable)
variable.set([])
state = State()
statement = compile(variable, state)
self.connection.execute("INSERT INTO array_test VALUES (1, %s)"
% statement, state.parameters)
result = self.connection.execute("SELECT a FROM array_test WHERE id=1")
array = result.get_one()[0]
self.assertTrue(isinstance(array, list))
variable = ListVariable(IntVariable)
result.set_variable(variable, array)
self.assertEquals(variable.get(), [])
def test_expressions_in_union_order_by(self):
# 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
column = SQLRaw("1")
Alias.auto_counter = 0
alias = Alias(column, "id")
expr = Union(Select(alias),
Select(column),
order_by=alias + 1,
limit=1,
offset=1,
all=True)
state = State()
statement = compile(expr, state)
self.assertEquals(
statement, 'SELECT * FROM '
'((SELECT 1 AS id) UNION ALL (SELECT 1)) AS "_1" '
'ORDER BY id+? LIMIT 1 OFFSET 1')
self.assertVariablesEqual(state.parameters, [Variable(1)])
result = self.connection.execute(expr)
self.assertEquals(result.get_one(), (1, ))
def test_returning_update_with_columns(self):
update = Update({column1: elem1},
table=table1,
primary_columns=(column2, column3))
assert compile(Returning(update, columns=[
column3
])) == ('UPDATE "table 1" SET column1=elem1 RETURNING column3')
def test_array_support_with_empty(self):
try:
self.connection.execute("DROP TABLE array_test")
self.connection.commit()
except:
self.connection.rollback()
self.connection.execute("CREATE TABLE array_test "
"(id SERIAL PRIMARY KEY, a INT[])")
variable = ListVariable(IntVariable)
variable.set([])
state = State()
statement = compile(variable, state)
self.connection.execute(
"INSERT INTO array_test VALUES (1, %s)" % statement,
state.parameters)
result = self.connection.execute("SELECT a FROM array_test WHERE id=1")
array = result.get_one()[0]
self.assertTrue(isinstance(array, list))
variable = ListVariable(IntVariable)
result.set_variable(variable, array)
self.assertEquals(variable.get(), [])
def test_compile_table_with_schema(self):
class Foo(object):
__storm_table__ = "my schema.my table"
id = Int("my.column", primary=True)
self.assertEquals(compile(Select(Foo.id)),
'SELECT "my schema"."my table"."my.column" '
'FROM "my schema"."my table"')
def test_get_insert_identity(self):
column = Column("thecolumn", "thetable")
variable = IntVariable()
result = self.connection.execute("SELECT 1")
where = result.get_insert_identity((column, ), (variable, ))
assert compile(where) == (
"thetable.thecolumn = (SELECT currval('thetable_thecolumn_seq'))")
def test_returning_update(self):
update = Update({column1: elem1},
table=table1,
primary_columns=(column2, column3))
self.assertEquals(
compile(Returning(update)), 'UPDATE "table 1" SET column1=elem1 '
'RETURNING column2, column3')
def test_compile_case(self):
"""The Case expr is compiled in a Postgres' CASE expression."""
cases = [(Column("foo") > 3, u"big"), (Column("bar") == None, 4)]
state = State()
statement = compile(Case(cases), state)
assert statement == (
"CASE WHEN (foo > ?) THEN ? WHEN (bar IS NULL) THEN ? END")
assert [3, "big", 4] == [param.get() for param in state.parameters]
def test_compile_table_with_schema(self):
class Foo(object):
__storm_table__ = "my schema.my table"
id = Int("my.column", primary=True)
assert compile(Select(
Foo.id)) == ('SELECT "my schema"."my table"."my.column" '
'FROM "my schema"."my table"')
def test_get_insert_identity(self):
column = Column("thecolumn", "thetable")
variable = IntVariable()
result = self.connection.execute("SELECT 1")
where = result.get_insert_identity((column,), (variable,))
self.assertEquals(compile(where),
"thetable.thecolumn = "
"(SELECT currval('thetable_thecolumn_seq'))")
def test_compile_case_with_default(self):
"""
If a default is provided, the resulting CASE expression includes
an ELSE clause.
"""
cases = [(Column("foo") > 3, u"big")]
state = State()
statement = compile(Case(cases, default=9), state)
assert "CASE WHEN (foo > ?) THEN ? ELSE ? END" == statement
assert [3, "big", 9] == [param.get() for param in state.parameters]
def test_compile_case_with_expression(self):
"""
If an expression is provided, the resulting CASE expression uses the
simple syntax.
"""
cases = [(1, u"one"), (2, u"two")]
state = State()
statement = compile(Case(cases, expression=Column("foo")), state)
self.assertEqual("CASE foo WHEN ? THEN ? WHEN ? THEN ? END", statement)
self.assertEqual([1, "one", 2, "two"],
[param.get() for param in state.parameters])
def test_expressions_in_union_order_by(self):
# 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
column = SQLRaw("1")
Alias.auto_counter = 0
alias = Alias(column, "id")
expr = Union(Select(alias), Select(column), order_by=alias+1,
limit=1, offset=1, all=True)
state = State()
statement = compile(expr, state)
self.assertEquals(statement,
'SELECT * FROM '
'((SELECT 1 AS id) UNION ALL (SELECT 1)) AS "_1" '
'ORDER BY id+? LIMIT 1 OFFSET 1')
self.assertVariablesEqual(state.parameters, [Variable(1)])
result = self.connection.execute(expr)
self.assertEquals(result.get_one(), (1,))
def test_currval_no_escaping(self):
expr = currval(Column("thecolumn", "theschema.thetable"))
statement = compile(expr)
expected = """currval('theschema.thetable_thecolumn_seq')"""
self.assertEquals(statement, expected)
def test_currval_escaped_schema_table_and_column(self):
expr = currval(Column("the column", "the schema.the table"))
statement = compile(expr)
expected = """currval('"the schema"."the table_the column_seq"')"""
self.assertEquals(statement, expected)
def test_currval_no_escaping(self):
expr = currval(Column("thecolumn", "theschema.thetable"))
statement = compile(expr)
expected = """currval('theschema.thetable_thecolumn_seq')"""
self.assertEquals(statement, expected)
def test_currval_escaped_column_no_schema(self):
expr = currval(Column("the column", "thetable"))
statement = compile(expr)
expected = """currval('"thetable_the column_seq"')"""
assert statement == expected
def test_returning_column_context(self):
column2 = TrackContext()
insert = Insert({column1: elem1}, table1, primary_columns=column2)
compile(Returning(insert))
self.assertEquals(column2.context, COLUMN)
def test_currval_escaped_schema_table_and_column(self):
expr = currval(Column("the column", "the schema.the table"))
statement = compile(expr)
expected = """currval('"the schema"."the table_the column_seq"')"""
self.assertEquals(statement, expected)
def test_returning_column_context(self):
column2 = TrackContext()
insert = Insert({column1: elem1}, table1, primary_columns=column2)
compile(Returning(insert))
self.assertEquals(column2.context, COLUMN)
def test_returning_update_with_columns(self):
update = Update({column1: elem1}, table=table1,
primary_columns=(column2, column3))
self.assertEquals(compile(Returning(update, columns=[column3])),
'UPDATE "table 1" SET column1=elem1 '
'RETURNING column3')
def test_returning(self):
insert = Insert({column1: elem1}, table1,
primary_columns=(column2, column3))
self.assertEquals(compile(Returning(insert)),
'INSERT INTO "table 1" (column1) VALUES (elem1) '
'RETURNING column2, column3')
