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