Redmine

#!/usr/bin/env python3

# SQL check for the "Assignee's group" grouping patch (Redmine #7655, v2).

#

# This script writes test.sql, which builds a minimal schema (users,

# groups_users, issues) and runs SQL of the same shape as the patch generates

# (the joins of IssueQuery#joins_for_assignee_group and

# IssueQuery#assignee_group_statement) for grouped counts, grouped totals and

# ordering. The SQL strings below are written by hand to mirror the patch;

# they are not extracted from Redmine.

#

# Test data:

#   groups 10 "B Team" and 11 "A Team"

#   user 1 in group 10, user 2 in group 11, user 3 in both, user 4 in none

#   issues 100-103 assigned to users 1-4, issue 104 assigned to group 11,

#   issue 105 unassigned

#

# Usage:

#   python3 assignee_group_sql_check.py

#   sqlite3 :memory: < test.sql

#   psql -d <empty database> -f test.sql

#   mysql <empty database> < test.sql

#

# Checked on SQLite 3.45, PostgreSQL 16 and MariaDB 10.11 (same groups,

# counts, totals and ordering on all three, apart from where NULLs sort).

# Not checked on SQL Server.

# Mirrors joins_for_assignee_group / assignee_group_statement in the patch

def subq(ids):

    cond = f"gu.group_id IN ({','.join(map(str,ids))})" if ids else "1=0"

    return f"(SELECT gu.user_id, MIN(gu.group_id) AS group_id FROM groups_users gu WHERE {cond} GROUP BY gu.user_id)"

STMT="COALESCE(assignee_group_self.id, assignee_group_preferred.group_id, assignee_group_any.group_id)"

def joins(pref, vis):

    return " ".join([

      "LEFT OUTER JOIN users assignee_group_self ON assignee_group_self.id = issues.assigned_to_id AND assignee_group_self.type = 'Group'",

      f"LEFT OUTER JOIN {subq(pref)} assignee_group_preferred ON assignee_group_preferred.user_id = issues.assigned_to_id",

      f"LEFT OUTER JOIN {subq(vis)} assignee_group_any ON assignee_group_any.user_id = issues.assigned_to_id",

      f"LEFT OUTER JOIN users assignee_group ON assignee_group.id = {STMT}"])

schema = """

CREATE TABLE users (id INTEGER PRIMARY KEY, type VARCHAR(30), lastname VARCHAR(255));

CREATE TABLE groups_users (group_id INTEGER, user_id INTEGER);

CREATE TABLE issues (id INTEGER PRIMARY KEY, assigned_to_id INTEGER, estimated_hours FLOAT);

INSERT INTO users VALUES (10,'Group','B Team'),(11,'Group','A Team'),(12,'GroupNonMember','Non member'),

 (1,'User','a'),(2,'User','b'),(3,'User','ab'),(4,'User','none');

INSERT INTO groups_users VALUES (10,1),(11,2),(10,3),(11,3);

INSERT INTO issues VALUES (100,1,1.5),(101,2,4),(102,3,2),(103,4,NULL),(104,11,NULL),(105,NULL,NULL);

"""

cases = {"no filter":([], [10,11]), "filter=11":([11],[10,11]), "only 11 visible":([],[11]), "no visible":([],[])}

out=[schema]

for name,(pref,vis) in cases.items():

    j=joins(pref,vis)

    out.append(f"SELECT '{name}: count' AS label;")

    out.append(f"SELECT COUNT(*) AS count_all, {STMT} AS grp FROM issues {j} GROUP BY {STMT} ORDER BY grp;")

    out.append(f"SELECT SUM(issues.estimated_hours) AS total, {STMT} AS grp FROM issues {j} GROUP BY {STMT} ORDER BY grp;")

    out.append(f"SELECT issues.id, assignee_group.lastname FROM issues {j} ORDER BY assignee_group.lastname ASC, issues.id DESC;")

open("test.sql","w").write("\n".join(out)+"\n")