def _tkt_id_conditions(column, tkt_ids):
ranges = Ranges()
ranges.appendrange(','.join(map(str, sorted(tkt_ids))))
condition = []
tkt_ids = []
for a, b in ranges.pairs:
if a == b:
tkt_ids.append(a)
elif a + 1 == b:
tkt_ids.extend((a, b))
else:
condition.append('%s BETWEEN %d AND %d' % (column, a, b))
if tkt_ids:
condition.append('%s IN (%s)' % (column, ','.join(map(str, tkt_ids))))
return ' OR '.join(condition)
def get_sql(self, req=None, cached_ids=None):
"""Return a (sql, params) tuple for the query."""
self.get_columns()
enum_columns = ('resolution', 'priority', 'severity')
# Build the list of actual columns to query
cols = self.cols[:]
def add_cols(*args):
for col in args:
if not col in cols:
cols.append(col)
if self.group and not self.group in cols:
add_cols(self.group)
if self.rows:
add_cols('reporter', *self.rows)
add_cols('status', 'priority', 'time', 'changetime', self.order)
cols.extend([c for c in self.constraints.keys() if not c in cols])
custom_fields = [f['name'] for f in self.fields if 'custom' in f]
sql = []
sql.append("SELECT " + ",".join(['t.%s AS %s' % (c, c) for c in cols
if c not in custom_fields]))
sql.append(",priority.value AS priority_value")
for k in [k for k in cols if k in custom_fields]:
sql.append(",%s.value AS %s" % (k, k))
sql.append("\nFROM ticket AS t")
# Join with ticket_custom table as necessary
for k in [k for k in cols if k in custom_fields]:
sql.append("\n LEFT OUTER JOIN ticket_custom AS %s ON " \
"(id=%s.ticket AND %s.name='%s')" % (k, k, k, k))
# Join with the enum table for proper sorting
for col in [c for c in enum_columns
if c == self.order or c == self.group or c == 'priority']:
sql.append("\n LEFT OUTER JOIN enum AS %s ON "
"(%s.type='%s' AND %s.name=%s)"
% (col, col, col, col, col))
# Join with the version/milestone tables for proper sorting
for col in [c for c in ['milestone', 'version']
if c == self.order or c == self.group]:
sql.append("\n LEFT OUTER JOIN %s ON (%s.name=%s)"
% (col, col, col))
def get_constraint_sql(name, value, mode, neg):
if name not in custom_fields:
name = 't.' + name
else:
name = name + '.value'
value = value[len(mode) + neg:]
if mode == '':
return ("COALESCE(%s,'')%s=%%s" % (name, neg and '!' or ''),
value)
if not value:
return None
db = self.env.get_db_cnx()
value = db.like_escape(value)
if mode == '~':
value = '%' + value + '%'
elif mode == '^':
value = value + '%'
elif mode == '$':
value = '%' + value
return ("COALESCE(%s,'') %s%s" % (name, neg and 'NOT ' or '',
db.like()),
value)
clauses = []
args = []
for k, v in self.constraints.items():
if req:
v = [val.replace('$USER', req.authname) for val in v]
# Determine the match mode of the constraint (contains,
# starts-with, negation, etc.)
neg = v[0].startswith('!')
mode = ''
if len(v[0]) > neg and v[0][neg] in ('~', '^', '$'):
mode = v[0][neg]
# Special case id ranges
if k == 'id':
ranges = Ranges()
for r in v:
r = r.replace('!', '')
ranges.appendrange(r)
ids = []
id_clauses = []
for a,b in ranges.pairs:
if a == b:
ids.append(str(a))
else:
id_clauses.append('id BETWEEN %s AND %s')
args.append(a)
args.append(b)
if ids:
id_clauses.append('id IN (%s)' % (','.join(ids)))
if id_clauses:
clauses.append('%s(%s)' % (neg and 'NOT ' or '',
' OR '.join(id_clauses)))
# Special case for exact matches on multiple values
elif not mode and len(v) > 1:
if k not in custom_fields:
col = 't.' + k
else:
col = k + '.value'
clauses.append("COALESCE(%s,'') %sIN (%s)"
% (col, neg and 'NOT ' or '',
','.join(['%s' for val in v])))
args += [val[neg:] for val in v]
elif len(v) > 1:
constraint_sql = filter(None,
[get_constraint_sql(k, val, mode, neg)
for val in v])
if not constraint_sql:
continue
if neg:
clauses.append("(" + " AND ".join(
[item[0] for item in constraint_sql]) + ")")
else:
clauses.append("(" + " OR ".join(
[item[0] for item in constraint_sql]) + ")")
args += [item[1] for item in constraint_sql]
elif len(v) == 1:
constraint_sql = get_constraint_sql(k, v[0], mode, neg)
if constraint_sql:
clauses.append(constraint_sql[0])
args.append(constraint_sql[1])
clauses = filter(None, clauses)
if clauses:
sql.append("\nWHERE ")
sql.append(" AND ".join(clauses))
if cached_ids:
sql.append(" OR ")
sql.append("id in (%s)" % (','.join(
[str(id) for id in cached_ids])))
sql.append("\nORDER BY ")
order_cols = [(self.order, self.desc)]
if self.group and self.group != self.order:
order_cols.insert(0, (self.group, self.groupdesc))
for name, desc in order_cols:
if name in custom_fields or name in enum_columns:
col = name + '.value'
else:
col = 't.' + name
desc = desc and ' DESC' or ''
# FIXME: This is a somewhat ugly hack. Can we also have the
# column type for this? If it's an integer, we do first
# one, if text, we do 'else'
if name in ('id', 'time', 'changetime'):
sql.append("COALESCE(%s,0)=0%s," % (col, desc))
else:
sql.append("COALESCE(%s,'')=''%s," % (col, desc))
if name in enum_columns:
# These values must be compared as ints, not as strings
db = self.env.get_db_cnx()
sql.append(db.cast(col, 'int') + desc)
elif name == 'milestone':
sql.append("COALESCE(milestone.completed,0)=0%s,"
"milestone.completed%s,"
"COALESCE(milestone.due,0)=0%s,milestone.due%s,"
"%s%s" % (desc, desc, desc, desc, col, desc))
elif name == 'version':
sql.append("COALESCE(version.time,0)=0%s,version.time%s,%s%s"
% (desc, desc, col, desc))
else:
sql.append("%s%s" % (col, desc))
if name == self.group and not name == self.order:
sql.append(",")
if self.order != 'id':
sql.append(",t.id")
return "".join(sql), args
