class QueryTestCase(unittest.TestCase): def prettifySQL(self, sql): """Returns a prettified version of the SQL as a list of lines to help in creating a useful diff between two SQL statements.""" pretty = [] for line in sql.split("\n"): pretty.extend(["%s,\n" % x for x in line.split(",")]) return pretty def assertEqualSQL(self, sql, correct_sql): sql_split = self.prettifySQL(sql) correct_sql_split = self.prettifySQL(correct_sql) sql_diff = "".join(list(difflib.unified_diff(correct_sql_split, sql_split))) failure_message = "%r != %r\n" % (sql, correct_sql) + sql_diff self.assertEqual(sql, correct_sql, failure_message) def setUp(self): self.env = EnvironmentStub(default_data=True) self.req = Mock(href=self.env.href, authname="anonymous", tz=utc, locale=locale_en, lc_time=locale_en) def tearDown(self): self.env.reset_db() def test_all_ordered_by_id(self): query = Query(self.env, order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_desc(self): query = Query(self.env, order="id", desc=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_verbose(self): query = Query(self.env, order="id", verbose=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_from_unicode(self): query = Query.from_string(self.env, u"order=id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority(self): query = Query(self.env) # priority is default order sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % {"cast_priority": self.env.get_read_db().cast("priority.value", "int")}, ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % {"cast_priority": self.env.get_read_db().cast("priority.value", "int")}, ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version(self): query = Query(self.env, order="version") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='',COALESCE(version.time,0)=0,version.time,t.version,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version_desc(self): query = Query(self.env, order="version", desc=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone(self): query = Query.from_string(self.env, "milestone=milestone1", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["milestone1"], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone(self): query = Query(self.env, order="id", group="milestone") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone_desc(self): query = Query(self.env, order="id", group="milestone", groupdesc=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.completed,0)=0 DESC,milestone.completed DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_grouped_by_priority(self): query = Query(self.env, group="priority") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % {"cast_priority": self.env.get_read_db().cast("priority.value", "int")}, ) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone_not(self): query = Query.from_string(self.env, "milestone!=milestone1", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')!=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["milestone1"], args) tickets = query.execute(self.req) def test_constrained_by_status(self): query = Query.from_string(self.env, "status=new|assigned|reopened", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["new", "assigned", "reopened"], args) tickets = query.execute(self.req) def test_constrained_by_owner_containing(self): query = Query.from_string(self.env, "owner~=someone", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"like": self.env.get_read_db().like()}, ) self.assertEqual(["%someone%"], args) tickets = query.execute(self.req) def test_constrained_by_owner_not_containing(self): query = Query.from_string(self.env, "owner!~=someone", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') NOT %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"like": self.env.get_read_db().like()}, ) self.assertEqual(["%someone%"], args) tickets = query.execute(self.req) def test_constrained_by_owner_beginswith(self): query = Query.from_string(self.env, "owner^=someone", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"like": self.env.get_read_db().like()}, ) self.assertEqual(["someone%"], args) tickets = query.execute(self.req) def test_constrained_by_owner_endswith(self): query = Query.from_string(self.env, "owner$=someone", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"like": self.env.get_read_db().like()}, ) self.assertEqual(["%someone"], args) tickets = query.execute(self.req) def test_constrained_by_custom_field(self): self.env.config.set("ticket-custom", "foo", "text") query = Query.from_string(self.env, "foo=something", order="id") sql, args = query.get_sql() foo = self.env.get_read_db().quote("foo") self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,c.%s AS %s FROM ticket AS t LEFT JOIN (SELECT id AS ticket, (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='foo') AS %s FROM ticket t) AS c ON (c.ticket=t.id) LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(c.%s,'')=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % ((foo,) * 4), ) self.assertEqual(["something"], args) tickets = query.execute(self.req) def test_grouped_by_custom_field(self): self.env.config.set("ticket-custom", "foo", "text") query = Query(self.env, group="foo", order="id") sql, args = query.get_sql() foo = self.env.get_read_db().quote("foo") self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,c.%s AS %s FROM ticket AS t LEFT JOIN (SELECT id AS ticket, (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='foo') AS %s FROM ticket t) AS c ON (c.ticket=t.id) LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(c.%s,'')='',c.%s,COALESCE(t.id,0)=0,t.id""" % ((foo,) * 5), ) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_id_ranges(self): query = Query.from_string(self.env, "id=42,44,51-55&order=id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((t.id BETWEEN %s AND %s OR t.id IN (42,44))) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([51, 55], args) def test_constrained_by_id_and_custom_field(self): self.env.config.set("ticket-custom", "foo", "text") ticket = Ticket(self.env) ticket["reporter"] = "joe" ticket["summary"] = "Foo" ticket["foo"] = "blah" ticket.insert() query = Query.from_string(self.env, "id=%d-42&foo=blah" % ticket.id) tickets = query.execute(self.req) self.assertEqual(1, len(tickets)) self.assertEqual(ticket.id, tickets[0]["id"]) query = Query.from_string(self.env, "id=%d,42&foo=blah" % ticket.id) tickets = query.execute(self.req) self.assertEqual(1, len(tickets)) self.assertEqual(ticket.id, tickets[0]["id"]) query = Query.from_string(self.env, "id=%d,42,43-84&foo=blah" % ticket.id) tickets = query.execute(self.req) self.assertEqual(1, len(tickets)) self.assertEqual(ticket.id, tickets[0]["id"]) def test_too_many_custom_fields(self): fields = ["col_%02d" % i for i in xrange(100)] for f in fields: self.env.config.set("ticket-custom", f, "text") ticket = Ticket(self.env) ticket["reporter"] = "joe" ticket["summary"] = "Foo" for idx, f in enumerate(fields): ticket[f] = "%d.%s" % (idx, f) ticket.insert() string = "col_00=0.col_00&order=id&col=id&col=reporter&col=summary" + "".join("&col=" + f for f in fields) query = Query.from_string(self.env, string) tickets = query.execute(self.req) self.assertEqual(ticket.id, tickets[0]["id"]) self.assertEqual("joe", tickets[0]["reporter"]) self.assertEqual("Foo", tickets[0]["summary"]) self.assertEqual("0.col_00", tickets[0]["col_00"]) self.assertEqual("99.col_99", tickets[0]["col_99"]) query = Query.from_string(self.env, "col_00=notfound") self.assertEqual([], query.execute(self.req)) def test_constrained_by_multiple_owners(self): query = Query.from_string(self.env, "owner=someone|someone_else", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["someone", "someone_else"], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_not(self): query = Query.from_string(self.env, "owner!=someone|someone_else", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') NOT IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["someone", "someone_else"], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_contain(self): query = Query.from_string(self.env, "owner~=someone|someone_else", order="id") sql, args = query.get_sql() self.assertEqual(["%someone%", "%someone/_else%"], args) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s OR COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"like": self.env.get_read_db().like()}, ) tickets = query.execute(self.req) def test_constrained_by_empty_value_contains(self): query = Query.from_string(self.env, "owner~=|", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_startswith(self): query = Query.from_string(self.env, "owner^=|", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_endswith(self): query = Query.from_string(self.env, "owner$=|", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_time_range(self): query = Query.from_string(self.env, "created=2008-08-01..2008-09-01", order="id") sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"cast_time": self.env.get_read_db().cast("t.time", "int64")}, ) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_exclusion(self): query = Query.from_string(self.env, "created!=2008-08-01..2008-09-01", order="id") sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((NOT (%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"cast_time": self.env.get_read_db().cast("t.time", "int64")}, ) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_right(self): query = Query.from_string(self.env, "created=2008-08-01..", order="id") sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s>=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"cast_time": self.env.get_read_db().cast("t.time", "int64")}, ) self.assertEqual([1217548800000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_left(self): query = Query.from_string(self.env, "created=..2008-09-01", order="id") sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s<%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"cast_time": self.env.get_read_db().cast("t.time", "int64")}, ) self.assertEqual([1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_modified(self): query = Query.from_string(self.env, "modified=2008-08-01..2008-09-01", order="id") sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.changetime AS changetime,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_changetime)s>=%%s AND %(cast_changetime)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"cast_changetime": self.env.get_read_db().cast("t.changetime", "int64")}, ) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_keywords(self): query = Query.from_string(self.env, "keywords~=foo -bar baz", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.keywords AS keywords,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((COALESCE(t.keywords,'') %(like)s AND COALESCE(t.keywords,'') NOT %(like)s AND COALESCE(t.keywords,'') %(like)s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {"like": self.env.get_read_db().like()}, ) self.assertEqual(["%foo%", "%bar%", "%baz%"], args) tickets = query.execute(self.req) def test_constrained_by_milestone_or_version(self): query = Query.from_string(self.env, "milestone=milestone1&or&version=version1", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.version AS version,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) OR ((COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["milestone1", "version1"], args) tickets = query.execute(self.req) def test_equal_in_value(self): query = Query.from_string(self.env, r"status=this=that&version=version1", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.status AS status,t.time AS time,t.changetime AS changetime,t.version AS version,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.status,'')=%s) AND (COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["this=that", "version1"], args) tickets = query.execute(self.req) def test_special_character_escape(self): query = Query.from_string(self.env, r"status=here\&now|maybe\|later|back\slash", order="id") sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["here&now", "maybe|later", "back\\slash"], args) tickets = query.execute(self.req) def test_repeated_constraint_field(self): like_query = Query.from_string(self.env, "owner!=someone|someone_else", order="id") query = Query.from_string(self.env, "owner!=someone&owner!=someone_else", order="id") like_sql, like_args = like_query.get_sql() sql, args = query.get_sql() self.assertEqualSQL(sql, like_sql) self.assertEqual(args, like_args) tickets = query.execute(self.req) def test_user_var(self): query = Query.from_string(self.env, "owner=$USER&order=id") sql, args = query.get_sql(req=self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""", ) self.assertEqual(["anonymous"], args) tickets = query.execute(self.req) def test_csv_escape(self): query = Mock( get_columns=lambda: ["col1"], execute=lambda r: [{"id": 1, "col1": "value, needs escaped"}], time_fields=["time", "changetime"], ) content, mimetype = QueryModule(self.env).export_csv(Mock(href=self.env.href, perm=MockPerm()), query) self.assertEqual('\xef\xbb\xbfcol1\r\n"value, needs escaped"\r\n', content) def test_template_data(self): req = Mock(href=self.env.href, perm=MockPerm(), authname="anonymous", tz=None, locale=None) context = web_context(req, "query") query = Query.from_string(self.env, "owner=$USER&order=id") tickets = query.execute(req) data = query.template_data(context, tickets, req=req) self.assertEqual(["anonymous"], data["clauses"][0]["owner"]["values"]) query = Query.from_string(self.env, "owner=$USER&order=id") tickets = query.execute(req) data = query.template_data(context, tickets) self.assertEqual(["$USER"], data["clauses"][0]["owner"]["values"])
class ReportTestCase(unittest.TestCase): def setUp(self): self.env = EnvironmentStub() self.report_module = ReportModule(self.env) def tearDown(self): self.env.reset_db() def _make_environ(self, scheme='http', server_name='example.org', server_port=80, method='GET', script_name='/trac', **kwargs): environ = {'wsgi.url_scheme': scheme, 'wsgi.input': StringIO(''), 'REQUEST_METHOD': method, 'SERVER_NAME': server_name, 'SERVER_PORT': server_port, 'SCRIPT_NAME': script_name} environ.update(kwargs) return environ def test_sub_var_no_quotes(self): sql, values, missing_args = self.report_module.sql_sub_vars( "$VAR", {'VAR': 'value'}) self.assertEqual("%s", sql) self.assertEqual(['value'], values) self.assertEqual([], missing_args) def test_sub_var_digits_underscore(self): sql, values, missing_args = self.report_module.sql_sub_vars( "$_VAR, $VAR2, $2VAR", {'_VAR': 'value1', 'VAR2': 'value2'}) self.assertEqual("%s, %s, $2VAR", sql) self.assertEqual(['value1', 'value2'], values) self.assertEqual([], missing_args) def test_sub_var_quotes(self): sql, values, missing_args = self.report_module.sql_sub_vars( "'$VAR'", {'VAR': 'value'}) self.assertEqual(self.env.get_read_db().concat("''", '%s', "''"), sql) self.assertEqual(['value'], values) self.assertEqual([], missing_args) def test_sub_var_missing_args(self): sql, values, missing_args = self.report_module.sql_sub_vars( "$VAR, $PARAM, $MISSING", {'VAR': 'value'}) self.assertEqual("%s, %s, %s", sql) self.assertEqual(['value', '', ''], values) self.assertEqual(['PARAM', 'MISSING'], missing_args) def test_csv_escape(self): buf = StringIO() def start_response(status, headers): return buf.write environ = self._make_environ() req = Request(environ, start_response) cols = ['TEST_COL', 'TEST_ZERO'] rows = [('value, needs escaped', 0)] try: self.report_module._send_csv(req, cols, rows) except RequestDone: pass self.assertEqual('\xef\xbb\xbfTEST_COL,TEST_ZERO\r\n"value, needs escaped",0\r\n', buf.getvalue()) def test_saved_custom_query_redirect(self): query = u'query:?type=résumé' db = self.env.get_db_cnx() cursor = db.cursor() cursor.execute("INSERT INTO report (title,query,description) " "VALUES (%s,%s,%s)", ('redirect', query, '')) id = db.get_last_id(cursor, 'report') db.commit() headers_sent = {} def start_response(status, headers): headers_sent.update(dict(headers)) environ = self._make_environ() req = Request(environ, start_response) req.authname = 'anonymous' req.session = Mock(save=lambda: None) self.assertRaises(RequestDone, self.report_module._render_view, req, id) self.assertEqual('http://example.org/trac/query?' + \ 'type=r%C3%A9sum%C3%A9&report=' + str(id), headers_sent['Location'])
class QueryTestCase(unittest.TestCase): def prettifySQL(self, sql): """Returns a prettified version of the SQL as a list of lines to help in creating a useful diff between two SQL statements.""" pretty = [] for line in sql.split('\n'): pretty.extend([ "%s,\n" % x for x in line.split(',')]) return pretty def assertEqualSQL(self, sql, correct_sql): sql_split = self.prettifySQL(sql) correct_sql_split = self.prettifySQL(correct_sql) sql_diff = ''.join(list( difflib.unified_diff(correct_sql_split, sql_split) )) failure_message = "%r != %r\n" % (sql, correct_sql) + sql_diff self.assertEqual(sql, correct_sql, failure_message) def setUp(self): self.env = EnvironmentStub(default_data=True) locale = Locale.parse('en_US') if Locale else None self.req = Mock(href=self.env.href, authname='anonymous', tz=utc, locale=locale, lc_time=locale) def tearDown(self): self.env.reset_db() def test_all_ordered_by_id(self): query = Query(self.env, order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_desc(self): query = Query(self.env, order='id', desc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_verbose(self): query = Query(self.env, order='id', verbose=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_from_unicode(self): query = Query.from_string(self.env, u'order=id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority(self): query = Query(self.env) # priority is default order sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version(self): query = Query(self.env, order='version') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='',COALESCE(version.time,0)=0,version.time,t.version,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version_desc(self): query = Query(self.env, order='version', desc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone(self): query = Query.from_string(self.env, 'milestone=milestone1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1'], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone(self): query = Query(self.env, order='id', group='milestone') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone_desc(self): query = Query(self.env, order='id', group='milestone', groupdesc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.completed,0)=0 DESC,milestone.completed DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_grouped_by_priority(self): query = Query(self.env, group='priority') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone_not(self): query = Query.from_string(self.env, 'milestone!=milestone1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')!=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1'], args) tickets = query.execute(self.req) def test_constrained_by_status(self): query = Query.from_string(self.env, 'status=new|assigned|reopened', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['new', 'assigned', 'reopened'], args) tickets = query.execute(self.req) def test_constrained_by_owner_containing(self): query = Query.from_string(self.env, 'owner~=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_not_containing(self): query = Query.from_string(self.env, 'owner!~=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') NOT %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_beginswith(self): query = Query.from_string(self.env, 'owner^=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_endswith(self): query = Query.from_string(self.env, 'owner$=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone'], args) tickets = query.execute(self.req) def test_constrained_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query.from_string(self.env, 'foo=something', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,%s.value AS %s FROM ticket AS t LEFT OUTER JOIN ticket_custom AS %s ON (id=%s.ticket AND %s.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(%s.value,'')=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % ((foo,) * 6)) self.assertEqual(['something'], args) tickets = query.execute(self.req) def test_grouped_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query(self.env, group='foo', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,%s.value AS %s FROM ticket AS t LEFT OUTER JOIN ticket_custom AS %s ON (id=%s.ticket AND %s.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(%s.value,'')='',%s.value,COALESCE(t.id,0)=0,t.id""" % ((foo,) * 7)) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners(self): query = Query.from_string(self.env, 'owner=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['someone', 'someone_else'], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_not(self): query = Query.from_string(self.env, 'owner!=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') NOT IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['someone', 'someone_else'], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_contain(self): query = Query.from_string(self.env, 'owner~=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqual(['%someone%', '%someone/_else%'], args) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s OR COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) tickets = query.execute(self.req) def test_constrained_by_empty_value_contains(self): query = Query.from_string(self.env, 'owner~=|', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_startswith(self): query = Query.from_string(self.env, 'owner^=|', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_endswith(self): query = Query.from_string(self.env, 'owner$=|', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_time_range(self): query = Query.from_string(self.env, 'created=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_exclusion(self): query = Query.from_string(self.env, 'created!=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((NOT (%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_right(self): query = Query.from_string(self.env, 'created=2008-08-01..', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s>=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_left(self): query = Query.from_string(self.env, 'created=..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s<%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_modified(self): query = Query.from_string(self.env, 'modified=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.changetime AS changetime,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_changetime)s>=%%s AND %(cast_changetime)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_changetime': self.env.get_read_db().cast('t.changetime', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_keywords(self): query = Query.from_string(self.env, 'keywords~=foo -bar baz', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.keywords AS keywords,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((COALESCE(t.keywords,'') %(like)s AND COALESCE(t.keywords,'') NOT %(like)s AND COALESCE(t.keywords,'') %(like)s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%foo%', '%bar%', '%baz%'], args) tickets = query.execute(self.req) def test_constrained_by_milestone_or_version(self): query = Query.from_string(self.env, 'milestone=milestone1&or&version=version1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.version AS version,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) OR ((COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1', 'version1'], args) tickets = query.execute(self.req) def test_equal_in_value(self): query = Query.from_string(self.env, r'status=this=that&version=version1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.status AS status,t.time AS time,t.changetime AS changetime,t.version AS version,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.status,'')=%s) AND (COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['this=that', 'version1'], args) tickets = query.execute(self.req) def test_special_character_escape(self): query = Query.from_string(self.env, r'status=here\&now|maybe\|later|back\slash', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['here&now', 'maybe|later', 'back\\slash'], args) tickets = query.execute(self.req) def test_repeated_constraint_field(self): like_query = Query.from_string(self.env, 'owner!=someone|someone_else', order='id') query = Query.from_string(self.env, 'owner!=someone&owner!=someone_else', order='id') like_sql, like_args = like_query.get_sql() sql, args = query.get_sql() self.assertEqualSQL(sql, like_sql) self.assertEqual(args, like_args) tickets = query.execute(self.req) def test_user_var(self): query = Query.from_string(self.env, 'owner=$USER&order=id') sql, args = query.get_sql(req=self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['anonymous'], args) tickets = query.execute(self.req) def test_csv_escape(self): query = Mock(get_columns=lambda: ['col1'], execute=lambda r: [{'id': 1, 'col1': 'value, needs escaped'}], time_fields=['time', 'changetime']) content, mimetype = QueryModule(self.env).export_csv( Mock(href=self.env.href, perm=MockPerm()), query) self.assertEqual('\xef\xbb\xbfcol1\r\n"value, needs escaped"\r\n', content) def test_template_data(self): req = Mock(href=self.env.href, perm=MockPerm(), authname='anonymous', tz=None, locale=None) context = web_context(req, 'query') query = Query.from_string(self.env, 'owner=$USER&order=id') tickets = query.execute(req) data = query.template_data(context, tickets, req=req) self.assertEqual(['anonymous'], data['clauses'][0]['owner']['values']) query = Query.from_string(self.env, 'owner=$USER&order=id') tickets = query.execute(req) data = query.template_data(context, tickets) self.assertEqual(['$USER'], data['clauses'][0]['owner']['values'])
class StringsTestCase(unittest.TestCase): def setUp(self): self.env = EnvironmentStub() def tearDown(self): self.env.reset_db() def test_insert_unicode(self): self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-unicode', u'ünicöde')) self.assertEqual([(u'ünicöde',)], self.env.db_query( "SELECT value FROM system WHERE name='test-unicode'")) def test_insert_empty(self): from trac.util.text import empty self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-empty', empty)) self.assertEqual([(u'',)], self.env.db_query( "SELECT value FROM system WHERE name='test-empty'")) def test_insert_markup(self): from genshi.core import Markup self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-markup', Markup(u'<em>märkup</em>'))) self.assertEqual([(u'<em>märkup</em>',)], self.env.db_query( "SELECT value FROM system WHERE name='test-markup'")) def test_quote(self): db = self.env.get_db_cnx() cursor = db.cursor() cursor.execute('SELECT 1 AS %s' % \ db.quote(r'alpha\`\"\'\\beta``gamma""delta')) self.assertEqual(r'alpha\`\"\'\\beta``gamma""delta', get_column_names(cursor)[0]) def test_quoted_id_with_percent(self): db = self.env.get_read_db() name = """%?`%s"%'%%""" def test(db, logging=False): cursor = db.cursor() if logging: cursor.log = self.env.log cursor.execute('SELECT 1 AS ' + db.quote(name)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.execute('SELECT %s AS ' + db.quote(name), (42,)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.executemany("UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", []) cursor.executemany("UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", [('42',), ('43',)]) test(db) test(db, logging=True) def test_prefix_match_case_sensitive(self): @self.env.with_transaction() def do_insert(db): cursor = db.cursor() cursor.executemany("INSERT INTO system (name,value) VALUES (%s,1)", [('blahblah',), ('BlahBlah',), ('BLAHBLAH',), (u'BlähBlah',), (u'BlahBläh',)]) db = self.env.get_read_db() cursor = db.cursor() cursor.execute("SELECT name FROM system WHERE name %s" % db.prefix_match(), (db.prefix_match_value('Blah'),)) names = sorted(name for name, in cursor) self.assertEqual('BlahBlah', names[0]) self.assertEqual(u'BlahBläh', names[1]) self.assertEqual(2, len(names)) def test_prefix_match_metachars(self): def do_query(prefix): db = self.env.get_read_db() cursor = db.cursor() cursor.execute("SELECT name FROM system WHERE name %s " "ORDER BY name" % db.prefix_match(), (db.prefix_match_value(prefix),)) return [name for name, in cursor] @self.env.with_transaction() def do_insert(db): values = ['foo*bar', 'foo*bar!', 'foo?bar', 'foo?bar!', 'foo[bar', 'foo[bar!', 'foo]bar', 'foo]bar!', 'foo%bar', 'foo%bar!', 'foo_bar', 'foo_bar!', 'foo/bar', 'foo/bar!', 'fo*ob?ar[fo]ob%ar_fo/obar'] cursor = db.cursor() cursor.executemany("INSERT INTO system (name,value) VALUES (%s,1)", [(value,) for value in values]) self.assertEqual(['foo*bar', 'foo*bar!'], do_query('foo*')) self.assertEqual(['foo?bar', 'foo?bar!'], do_query('foo?')) self.assertEqual(['foo[bar', 'foo[bar!'], do_query('foo[')) self.assertEqual(['foo]bar', 'foo]bar!'], do_query('foo]')) self.assertEqual(['foo%bar', 'foo%bar!'], do_query('foo%')) self.assertEqual(['foo_bar', 'foo_bar!'], do_query('foo_')) self.assertEqual(['foo/bar', 'foo/bar!'], do_query('foo/')) self.assertEqual(['fo*ob?ar[fo]ob%ar_fo/obar'], do_query('fo*')) self.assertEqual(['fo*ob?ar[fo]ob%ar_fo/obar'], do_query('fo*ob?ar[fo]ob%ar_fo/obar'))
class QueryTestCase(unittest.TestCase): def prettifySQL(self, sql): """Returns a prettified version of the SQL as a list of lines to help in creating a useful diff between two SQL statements.""" pretty = [] for line in sql.split('\n'): pretty.extend(["%s,\n" % x for x in line.split(',')]) return pretty def assertEqualSQL(self, sql, correct_sql): sql_split = self.prettifySQL(sql) correct_sql_split = self.prettifySQL(correct_sql) sql_diff = ''.join( list(difflib.unified_diff(correct_sql_split, sql_split))) failure_message = "%r != %r\n" % (sql, correct_sql) + sql_diff self.assertEqual(sql, correct_sql, failure_message) def setUp(self): self.env = EnvironmentStub(default_data=True) locale = Locale.parse('en_US') if Locale else None self.req = Mock(href=self.env.href, authname='anonymous', tz=utc, locale=locale, lc_time=locale) def tearDown(self): self.env.reset_db() def test_all_ordered_by_id(self): query = Query(self.env, order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_desc(self): query = Query(self.env, order='id', desc=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_verbose(self): query = Query(self.env, order='id', verbose=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_from_unicode(self): query = Query.from_string(self.env, u'order=id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority(self): query = Query(self.env) # priority is default order sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int') }) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int') }) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version(self): query = Query(self.env, order='version') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='',COALESCE(version.time,0)=0,version.time,t.version,t.id""" ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version_desc(self): query = Query(self.env, order='version', desc=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""" ) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone(self): query = Query.from_string(self.env, 'milestone=milestone1', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1'], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone(self): query = Query(self.env, order='id', group='milestone') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(t.id,0)=0,t.id""" ) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone_desc(self): query = Query(self.env, order='id', group='milestone', groupdesc=1) sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.completed,0)=0 DESC,milestone.completed DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""" ) self.assertEqual([], args) tickets = query.execute(self.req) def test_grouped_by_priority(self): query = Query(self.env, group='priority') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int') }) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone_not(self): query = Query.from_string(self.env, 'milestone!=milestone1', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')!=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1'], args) tickets = query.execute(self.req) def test_constrained_by_status(self): query = Query.from_string(self.env, 'status=new|assigned|reopened', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['new', 'assigned', 'reopened'], args) tickets = query.execute(self.req) def test_constrained_by_owner_containing(self): query = Query.from_string(self.env, 'owner~=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_not_containing(self): query = Query.from_string(self.env, 'owner!~=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') NOT %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_beginswith(self): query = Query.from_string(self.env, 'owner^=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_endswith(self): query = Query.from_string(self.env, 'owner$=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone'], args) tickets = query.execute(self.req) def test_constrained_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query.from_string(self.env, 'foo=something', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,%s.value AS %s FROM ticket AS t LEFT OUTER JOIN ticket_custom AS %s ON (id=%s.ticket AND %s.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(%s.value,'')=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % ((foo, ) * 6)) self.assertEqual(['something'], args) tickets = query.execute(self.req) def test_grouped_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query(self.env, group='foo', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,%s.value AS %s FROM ticket AS t LEFT OUTER JOIN ticket_custom AS %s ON (id=%s.ticket AND %s.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(%s.value,'')='',%s.value,COALESCE(t.id,0)=0,t.id""" % ((foo, ) * 7)) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners(self): query = Query.from_string(self.env, 'owner=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['someone', 'someone_else'], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_not(self): query = Query.from_string(self.env, 'owner!=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') NOT IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['someone', 'someone_else'], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_contain(self): query = Query.from_string(self.env, 'owner~=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqual(['%someone%', '%someone/_else%'], args) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s OR COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) tickets = query.execute(self.req) def test_constrained_by_empty_value_contains(self): query = Query.from_string(self.env, 'owner~=|', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_startswith(self): query = Query.from_string(self.env, 'owner^=|', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_endswith(self): query = Query.from_string(self.env, 'owner$=|', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_time_range(self): query = Query.from_string(self.env, 'created=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_exclusion(self): query = Query.from_string(self.env, 'created!=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((NOT (%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_right(self): query = Query.from_string(self.env, 'created=2008-08-01..', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s>=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_left(self): query = Query.from_string(self.env, 'created=..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s<%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_modified(self): query = Query.from_string(self.env, 'modified=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.changetime AS changetime,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_changetime)s>=%%s AND %(cast_changetime)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_changetime': self.env.get_read_db().cast('t.changetime', 'int64') }) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_keywords(self): query = Query.from_string(self.env, 'keywords~=foo -bar baz', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.keywords AS keywords,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((COALESCE(t.keywords,'') %(like)s AND COALESCE(t.keywords,'') NOT %(like)s AND COALESCE(t.keywords,'') %(like)s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%foo%', '%bar%', '%baz%'], args) tickets = query.execute(self.req) def test_constrained_by_milestone_or_version(self): query = Query.from_string(self.env, 'milestone=milestone1&or&version=version1', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.version AS version,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) OR ((COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1', 'version1'], args) tickets = query.execute(self.req) def test_equal_in_value(self): query = Query.from_string(self.env, r'status=this=that&version=version1', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.status AS status,t.time AS time,t.changetime AS changetime,t.version AS version,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.status,'')=%s) AND (COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['this=that', 'version1'], args) tickets = query.execute(self.req) def test_special_character_escape(self): query = Query.from_string(self.env, r'status=here\&now|maybe\|later|back\slash', order='id') sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['here&now', 'maybe|later', 'back\\slash'], args) tickets = query.execute(self.req) def test_repeated_constraint_field(self): like_query = Query.from_string(self.env, 'owner!=someone|someone_else', order='id') query = Query.from_string(self.env, 'owner!=someone&owner!=someone_else', order='id') like_sql, like_args = like_query.get_sql() sql, args = query.get_sql() self.assertEqualSQL(sql, like_sql) self.assertEqual(args, like_args) tickets = query.execute(self.req) def test_csv_escape(self): query = Mock(get_columns=lambda: ['col1'], execute=lambda r: [{ 'id': 1, 'col1': 'value, needs escaped' }], time_fields=['time', 'changetime']) content, mimetype = QueryModule(self.env).export_csv( Mock(href=self.env.href, perm=MockPerm()), query) self.assertEqual('\xef\xbb\xbfcol1\r\n"value, needs escaped"\r\n', content)
class QueryTestCase(unittest.TestCase): def prettifySQL(self, sql): """Returns a prettified version of the SQL as a list of lines to help in creating a useful diff between two SQL statements.""" pretty = [] for line in sql.split('\n'): pretty.extend([ "%s,\n" % x for x in line.split(',')]) return pretty def assertEqualSQL(self, sql, correct_sql): sql_split = self.prettifySQL(sql) correct_sql_split = self.prettifySQL(correct_sql) sql_diff = ''.join(list( difflib.unified_diff(correct_sql_split, sql_split) )) failure_message = "%r != %r\n" % (sql, correct_sql) + sql_diff self.assertEqual(sql, correct_sql, failure_message) def setUp(self): self.env = EnvironmentStub(default_data=True) self.req = Mock(href=self.env.href, authname='anonymous', tz=utc, locale=locale_en, lc_time=locale_en) def tearDown(self): self.env.reset_db() def test_all_ordered_by_id(self): query = Query(self.env, order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_desc(self): query = Query(self.env, order='id', desc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_verbose(self): query = Query(self.env, order='id', verbose=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_id_from_unicode(self): query = Query.from_string(self.env, u'order=id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority(self): query = Query(self.env) # priority is default order sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version(self): query = Query(self.env, order='version') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='',COALESCE(version.time,0)=0,version.time,t.version,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_ordered_by_version_desc(self): query = Query(self.env, order='version', desc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone(self): query = Query.from_string(self.env, 'milestone=milestone1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1'], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone(self): query = Query(self.env, order='id', group='milestone') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_all_grouped_by_milestone_desc(self): query = Query(self.env, order='id', group='milestone', groupdesc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.completed,0)=0 DESC,milestone.completed DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_grouped_by_priority(self): query = Query(self.env, group='priority') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_milestone_not(self): query = Query.from_string(self.env, 'milestone!=milestone1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')!=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1'], args) tickets = query.execute(self.req) def test_constrained_by_status(self): query = Query.from_string(self.env, 'status=new|assigned|reopened', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['new', 'assigned', 'reopened'], args) tickets = query.execute(self.req) def test_constrained_by_owner_containing(self): query = Query.from_string(self.env, 'owner~=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_not_containing(self): query = Query.from_string(self.env, 'owner!~=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') NOT %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_beginswith(self): query = Query.from_string(self.env, 'owner^=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['someone%'], args) tickets = query.execute(self.req) def test_constrained_by_owner_endswith(self): query = Query.from_string(self.env, 'owner$=someone', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%someone'], args) tickets = query.execute(self.req) def test_constrained_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query.from_string(self.env, 'foo=something', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,c.%s AS %s FROM ticket AS t LEFT JOIN (SELECT id AS ticket, (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='foo') AS %s FROM ticket t) AS c ON (c.ticket=t.id) LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(c.%s,'')=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % ((foo,) * 4)) self.assertEqual(['something'], args) tickets = query.execute(self.req) def test_grouped_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query(self.env, group='foo', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,c.%s AS %s FROM ticket AS t LEFT JOIN (SELECT id AS ticket, (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='foo') AS %s FROM ticket t) AS c ON (c.ticket=t.id) LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(c.%s,'')='',c.%s,COALESCE(t.id,0)=0,t.id""" % ((foo,) * 5)) self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_id_ranges(self): query = Query.from_string(self.env, 'id=42,44,51-55&order=id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((t.id BETWEEN %s AND %s OR t.id IN (42,44))) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([51, 55], args) def test_constrained_by_id_and_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') ticket = Ticket(self.env) ticket['reporter'] = 'joe' ticket['summary'] = 'Foo' ticket['foo'] = 'blah' ticket.insert() query = Query.from_string(self.env, 'id=%d-42&foo=blah' % ticket.id) tickets = query.execute(self.req) self.assertEqual(1, len(tickets)) self.assertEqual(ticket.id, tickets[0]['id']) query = Query.from_string(self.env, 'id=%d,42&foo=blah' % ticket.id) tickets = query.execute(self.req) self.assertEqual(1, len(tickets)) self.assertEqual(ticket.id, tickets[0]['id']) query = Query.from_string(self.env, 'id=%d,42,43-84&foo=blah' % ticket.id) tickets = query.execute(self.req) self.assertEqual(1, len(tickets)) self.assertEqual(ticket.id, tickets[0]['id']) def test_too_many_custom_fields(self): fields = ['col_%02d' % i for i in xrange(100)] for f in fields: self.env.config.set('ticket-custom', f, 'text') ticket = Ticket(self.env) ticket['reporter'] = 'joe' ticket['summary'] = 'Foo' for idx, f in enumerate(fields): ticket[f] = '%d.%s' % (idx, f) ticket.insert() string = 'col_00=0.col_00&order=id&col=id&col=reporter&col=summary' + \ ''.join('&col=' + f for f in fields) query = Query.from_string(self.env, string) tickets = query.execute(self.req) self.assertEqual(ticket.id, tickets[0]['id']) self.assertEqual('joe', tickets[0]['reporter']) self.assertEqual('Foo', tickets[0]['summary']) self.assertEqual('0.col_00', tickets[0]['col_00']) self.assertEqual('99.col_99', tickets[0]['col_99']) query = Query.from_string(self.env, 'col_00=notfound') self.assertEqual([], query.execute(self.req)) def test_constrained_by_multiple_owners(self): query = Query.from_string(self.env, 'owner=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['someone', 'someone_else'], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_not(self): query = Query.from_string(self.env, 'owner!=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.owner,'') NOT IN (%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['someone', 'someone_else'], args) tickets = query.execute(self.req) def test_constrained_by_multiple_owners_contain(self): query = Query.from_string(self.env, 'owner~=someone|someone_else', order='id') sql, args = query.get_sql() self.assertEqual(['%someone%', '%someone/_else%'], args) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'') %(like)s OR COALESCE(t.owner,'') %(like)s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) tickets = query.execute(self.req) def test_constrained_by_empty_value_contains(self): query = Query.from_string(self.env, 'owner~=|', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_startswith(self): query = Query.from_string(self.env, 'owner^=|', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_empty_value_endswith(self): query = Query.from_string(self.env, 'owner$=|', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req) def test_constrained_by_time_range(self): query = Query.from_string(self.env, 'created=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_exclusion(self): query = Query.from_string(self.env, 'created!=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((NOT (%(cast_time)s>=%%s AND %(cast_time)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_right(self): query = Query.from_string(self.env, 'created=2008-08-01..', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s>=%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1217548800000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_open_left(self): query = Query.from_string(self.env, 'created=..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((%(cast_time)s<%%s)) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_time': self.env.get_read_db().cast('t.time', 'int64')}) self.assertEqual([1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_time_range_modified(self): query = Query.from_string(self.env, 'modified=2008-08-01..2008-09-01', order='id') sql, args = query.get_sql(self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.changetime AS changetime,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((%(cast_changetime)s>=%%s AND %(cast_changetime)s<%%s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % { 'cast_changetime': self.env.get_read_db().cast('t.changetime', 'int64')}) self.assertEqual([1217548800000000L, 1220227200000000L], args) tickets = query.execute(self.req) def test_constrained_by_keywords(self): query = Query.from_string(self.env, 'keywords~=foo -bar baz', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.keywords AS keywords,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (((COALESCE(t.keywords,'') %(like)s AND COALESCE(t.keywords,'') NOT %(like)s AND COALESCE(t.keywords,'') %(like)s))) ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': self.env.get_read_db().like()}) self.assertEqual(['%foo%', '%bar%', '%baz%'], args) tickets = query.execute(self.req) def test_constrained_by_milestone_or_version(self): query = Query.from_string(self.env, 'milestone=milestone1&or&version=version1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.version AS version,t.milestone AS milestone,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.milestone,'')=%s)) OR ((COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['milestone1', 'version1'], args) tickets = query.execute(self.req) def test_equal_in_value(self): query = Query.from_string(self.env, r'status=this=that&version=version1', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.status AS status,t.time AS time,t.changetime AS changetime,t.version AS version,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.status,'')=%s) AND (COALESCE(t.version,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['this=that', 'version1'], args) tickets = query.execute(self.req) def test_special_character_escape(self): query = Query.from_string(self.env, r'status=here\&now|maybe\|later|back\slash', order='id') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE (COALESCE(t.status,'') IN (%s,%s,%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['here&now', 'maybe|later', 'back\\slash'], args) tickets = query.execute(self.req) def test_repeated_constraint_field(self): like_query = Query.from_string(self.env, 'owner!=someone|someone_else', order='id') query = Query.from_string(self.env, 'owner!=someone&owner!=someone_else', order='id') like_sql, like_args = like_query.get_sql() sql, args = query.get_sql() self.assertEqualSQL(sql, like_sql) self.assertEqual(args, like_args) tickets = query.execute(self.req) def test_user_var(self): query = Query.from_string(self.env, 'owner=$USER&order=id') sql, args = query.get_sql(req=self.req) self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE ((COALESCE(t.owner,'')=%s)) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual(['anonymous'], args) tickets = query.execute(self.req) def test_csv_escape(self): query = Mock(get_columns=lambda: ['col1'], execute=lambda r: [{'id': 1, 'col1': 'value, needs escaped'}], time_fields=['time', 'changetime']) content, mimetype = QueryModule(self.env).export_csv( Mock(href=self.env.href, perm=MockPerm()), query) self.assertEqual('\xef\xbb\xbfcol1\r\n"value, needs escaped"\r\n', content) def test_template_data(self): req = Mock(href=self.env.href, perm=MockPerm(), authname='anonymous', tz=None, locale=None) context = web_context(req, 'query') query = Query.from_string(self.env, 'owner=$USER&order=id') tickets = query.execute(req) data = query.template_data(context, tickets, req=req) self.assertEqual(['anonymous'], data['clauses'][0]['owner']['values']) query = Query.from_string(self.env, 'owner=$USER&order=id') tickets = query.execute(req) data = query.template_data(context, tickets) self.assertEqual(['$USER'], data['clauses'][0]['owner']['values'])
class StringsTestCase(unittest.TestCase): def setUp(self): self.env = EnvironmentStub() def tearDown(self): self.env.reset_db() def test_insert_unicode(self): self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-unicode', u'ünicöde')) self.assertEqual([(u'ünicöde',)], self.env.db_query( "SELECT value FROM system WHERE name='test-unicode'")) def test_insert_empty(self): from trac.util.text import empty self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-empty', empty)) self.assertEqual([(u'',)], self.env.db_query( "SELECT value FROM system WHERE name='test-empty'")) def test_insert_markup(self): from genshi.core import Markup self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-markup', Markup(u'<em>märkup</em>'))) self.assertEqual([(u'<em>märkup</em>',)], self.env.db_query( "SELECT value FROM system WHERE name='test-markup'")) def test_quote(self): db = self.env.get_db_cnx() cursor = db.cursor() cursor.execute('SELECT 1 AS %s' % \ db.quote(r'alpha\`\"\'\\beta``gamma""delta')) self.assertEqual(r'alpha\`\"\'\\beta``gamma""delta', get_column_names(cursor)[0]) def test_quoted_id_with_percent(self): db = self.env.get_read_db() name = """%?`%s"%'%%""" def test(db, logging=False): cursor = db.cursor() if logging: cursor.log = self.env.log cursor.execute('SELECT 1 AS ' + db.quote(name)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.execute('SELECT %s AS ' + db.quote(name), (42,)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.executemany("UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", []) cursor.executemany("UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", [('42',), ('43',)]) test(db) test(db, logging=True)
class ReportTestCase(unittest.TestCase): def setUp(self): self.env = EnvironmentStub() self.report_module = ReportModule(self.env) def _make_environ(self, scheme='http', server_name='example.org', server_port=80, method='GET', script_name='/trac', **kwargs): environ = { 'wsgi.url_scheme': scheme, 'wsgi.input': StringIO(''), 'REQUEST_METHOD': method, 'SERVER_NAME': server_name, 'SERVER_PORT': server_port, 'SCRIPT_NAME': script_name } environ.update(kwargs) return environ def test_sub_var_no_quotes(self): sql, values, missing_args = self.report_module.sql_sub_vars( "$VAR", {'VAR': 'value'}) self.assertEqual("%s", sql) self.assertEqual(['value'], values) self.assertEqual([], missing_args) def test_sub_var_digits_underscore(self): sql, values, missing_args = self.report_module.sql_sub_vars( "$_VAR, $VAR2, $2VAR", { '_VAR': 'value1', 'VAR2': 'value2' }) self.assertEqual("%s, %s, $2VAR", sql) self.assertEqual(['value1', 'value2'], values) self.assertEqual([], missing_args) def test_sub_var_quotes(self): sql, values, missing_args = self.report_module.sql_sub_vars( "'$VAR'", {'VAR': 'value'}) self.assertEqual(self.env.get_read_db().concat("''", '%s', "''"), sql) self.assertEqual(['value'], values) self.assertEqual([], missing_args) def test_sub_var_missing_args(self): sql, values, missing_args = self.report_module.sql_sub_vars( "$VAR, $PARAM, $MISSING", {'VAR': 'value'}) self.assertEqual("%s, %s, %s", sql) self.assertEqual(['value', '', ''], values) self.assertEqual(['PARAM', 'MISSING'], missing_args) def test_csv_escape(self): buf = StringIO() def start_response(status, headers): return buf.write environ = self._make_environ() req = Request(environ, start_response) cols = ['TEST_COL', 'TEST_ZERO'] rows = [('value, needs escaped', 0)] try: self.report_module._send_csv(req, cols, rows) except RequestDone: pass self.assertEqual( '\xef\xbb\xbfTEST_COL,TEST_ZERO\r\n"value, needs escaped",0\r\n', buf.getvalue())
class StringsTestCase(unittest.TestCase): def setUp(self): self.env = EnvironmentStub() def tearDown(self): self.env.reset_db() def test_insert_unicode(self): self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-unicode', u'ünicöde')) self.assertEqual( [(u'ünicöde', )], self.env.db_query( "SELECT value FROM system WHERE name='test-unicode'")) def test_insert_empty(self): from trac.util.text import empty self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-empty', empty)) self.assertEqual( [(u'', )], self.env.db_query( "SELECT value FROM system WHERE name='test-empty'")) def test_insert_markup(self): from genshi.core import Markup self.env.db_transaction( "INSERT INTO system (name,value) VALUES (%s,%s)", ('test-markup', Markup(u'<em>märkup</em>'))) self.assertEqual( [(u'<em>märkup</em>', )], self.env.db_query( "SELECT value FROM system WHERE name='test-markup'")) def test_quote(self): db = self.env.get_db_cnx() cursor = db.cursor() cursor.execute('SELECT 1 AS %s' % \ db.quote(r'alpha\`\"\'\\beta``gamma""delta')) self.assertEqual(r'alpha\`\"\'\\beta``gamma""delta', get_column_names(cursor)[0]) def test_quoted_id_with_percent(self): db = self.env.get_read_db() name = """%?`%s"%'%%""" def test(db, logging=False): cursor = db.cursor() if logging: cursor.log = self.env.log cursor.execute('SELECT 1 AS ' + db.quote(name)) self.assertEqual(name, get_column_names(cursor)[0]) cursor.execute('SELECT %s AS ' + db.quote(name), (42, )) self.assertEqual(name, get_column_names(cursor)[0]) cursor.executemany( "UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", []) cursor.executemany( "UPDATE system SET value=%s WHERE " "1=(SELECT 0 AS " + db.quote(name) + ")", [('42', ), ('43', )]) test(db) test(db, logging=True) def test_prefix_match_case_sensitive(self): @self.env.with_transaction() def do_insert(db): cursor = db.cursor() cursor.executemany("INSERT INTO system (name,value) VALUES (%s,1)", [('blahblah', ), ('BlahBlah', ), ('BLAHBLAH', ), (u'BlähBlah', ), (u'BlahBläh', )]) db = self.env.get_read_db() cursor = db.cursor() cursor.execute( "SELECT name FROM system WHERE name %s" % db.prefix_match(), (db.prefix_match_value('Blah'), )) names = sorted(name for name, in cursor) self.assertEqual('BlahBlah', names[0]) self.assertEqual(u'BlahBläh', names[1]) self.assertEqual(2, len(names)) def test_prefix_match_metachars(self): def do_query(prefix): db = self.env.get_read_db() cursor = db.cursor() cursor.execute( "SELECT name FROM system WHERE name %s " "ORDER BY name" % db.prefix_match(), (db.prefix_match_value(prefix), )) return [name for name, in cursor] @self.env.with_transaction() def do_insert(db): values = [ 'foo*bar', 'foo*bar!', 'foo?bar', 'foo?bar!', 'foo[bar', 'foo[bar!', 'foo]bar', 'foo]bar!', 'foo%bar', 'foo%bar!', 'foo_bar', 'foo_bar!', 'foo/bar', 'foo/bar!', 'fo*ob?ar[fo]ob%ar_fo/obar' ] cursor = db.cursor() cursor.executemany("INSERT INTO system (name,value) VALUES (%s,1)", [(value, ) for value in values]) self.assertEqual(['foo*bar', 'foo*bar!'], do_query('foo*')) self.assertEqual(['foo?bar', 'foo?bar!'], do_query('foo?')) self.assertEqual(['foo[bar', 'foo[bar!'], do_query('foo[')) self.assertEqual(['foo]bar', 'foo]bar!'], do_query('foo]')) self.assertEqual(['foo%bar', 'foo%bar!'], do_query('foo%')) self.assertEqual(['foo_bar', 'foo_bar!'], do_query('foo_')) self.assertEqual(['foo/bar', 'foo/bar!'], do_query('foo/')) self.assertEqual(['fo*ob?ar[fo]ob%ar_fo/obar'], do_query('fo*')) self.assertEqual(['fo*ob?ar[fo]ob%ar_fo/obar'], do_query('fo*ob?ar[fo]ob%ar_fo/obar'))
class WikiSyncModelTestCase(unittest.TestCase): def setUp(self): self.env = EnvironmentStub() self.plugin = WikiSyncEnvironment(self.env) self.plugin.upgrade_environment(self.env.get_db_cnx()) self.dao = WikiSyncDao(self.env) file = resource_filename(__name__, "data.sql") @self.env.with_transaction() def do_save(db): cursor = db.cursor() with open(file, "rb") as f: for sql in f: if sql: cursor.execute(sql) def test_sync_wiki_data(self): self.dao.sync_wiki_data() for name in ["CamelCase", "InterMapTxt", "NewPage", "WikiRestructuredTextLinks"]: self.assertTrue(self.dao.find(name) is not None) def test_all(self): results = [item for item in self.dao.all()] self.assertEqual(len(results), 3) def test_validate(self): item = self.dao.factory() self.assertRaises(AssertionError, item.validate) item.replace(name="Test").validate() def test_read(self): item = self.dao.find("Test1") self.assertEqual(self.dao.find("Unknown"), None) def test_create(self): self.dao.create( self.dao.factory( name="Test2", remote_version=2 ) ) db = self.env.get_read_db() cursor = db.cursor() cursor.execute(""" SELECT name, remote_version FROM wikisync WHERE name='Test2' """) results = cursor.fetchall() self.assertEqual(len(results), 1) self.assertEqual(results[0], (u'Test2', 2)) def test_delete(self): item = self.dao.find("Test1") self.assertTrue(item is not None) self.dao.delete(item) self.assertTrue(self.dao.find("Test1") is None) def test_update(self): item = self.dao.find("Test1") item = item.replace(sync_remote_version=1, remote_version=11) self.dao.update(item) db = self.env.get_read_db() cursor = db.cursor() cursor.execute(""" SELECT sync_remote_version, remote_version FROM wikisync WHERE name='Test1' """) results = cursor.fetchall() self.assertEqual(len(results), 1) self.assertEqual(results[0], (1, 11))