webapp.py: improve parameter validation for /schedule
[~helmut/crossqa.git] / fetchbugs.py
1 #!/usr/bin/python3
2 # SPDX-License-Identifier: GPL-2.0+
3
4 import logging
5
6 import sqlalchemy
7
8 logger = logging.getLogger()
9
10 def strip_title(title, package):
11     for prefix in (package + ": ", package + " "):
12         if title.startswith(prefix):
13             return title[len(prefix):]
14     return title
15
16 def get_affects(result, kind):
17     for bugnum, package, affected, title, patched in result:
18         row = dict(bugnum=bugnum, package=package, patched=patched, kind=kind)
19         somesource = False
20         for pkg in package.split(","):
21             if not pkg.startswith("src:"):
22                 continue
23             arow = row.copy()
24             arow.update(affects=pkg[4:], title=strip_title(title, pkg[4:]))
25             yield arow
26             if somesource:
27                 logger.warning("%s #%d assigned to multiple source packages",
28                                kind, bugnum)
29             somesource = True
30         if not somesource and affected:
31             for aff in affected.split(","):
32                 if not aff.startswith("src:"):
33                     continue
34                 arow = row.copy()
35                 arow.update(affects=aff[4:], title=strip_title(title, aff[4:]))
36                 yield arow
37                 somesource = True
38         if not somesource:
39             logger.warning("%s #%d affects no packages", kind, bugnum)
40
41 def make_table(name, columns):
42     return sqlalchemy.table(name, *map(sqlalchemy.column, columns.split()))
43
44 udd_all_bugs = make_table(
45     "all_bugs", "id package affected_packages title severity affects_unstable")
46 udd_bugs_tags = make_table("bugs_tags", "id tag")
47 udd_bugs_usertags = make_table("bugs_usertags", "id email tag")
48
49 def tagged_clause(id_, tag):
50     return sqlalchemy.exists().where(
51         sqlalchemy.and_(udd_bugs_tags.c.id == id_, udd_bugs_tags.c.tag == tag))
52
53 def get_bugs_where(conn, whereclause, kind):
54     query = sqlalchemy.select(
55         [udd_all_bugs.c.id, udd_all_bugs.c.package,
56          udd_all_bugs.c.affected_packages, udd_all_bugs.c.title,
57          tagged_clause(udd_all_bugs.c.id, "patch")],
58         sqlalchemy.and_(whereclause, udd_all_bugs.c.affects_unstable == 't'))
59     return get_affects(conn.execute(query), kind)
60
61 def get_ftbfs(conn):
62     clause = sqlalchemy.and_(
63         tagged_clause(udd_all_bugs.c.id, "ftbfs"),
64         udd_all_bugs.c.severity.in_(["serious", "critical", "grave"]))
65     return get_bugs_where(conn, clause, 'ftbfs')
66
67 def get_usertagged(conn, email, tag, kind):
68     clause = sqlalchemy.exists().where(
69         sqlalchemy.and_(udd_bugs_usertags.c.id == udd_all_bugs.c.id,
70                         udd_bugs_usertags.c.email == email,
71                         udd_bugs_usertags.c.tag == tag))
72     return get_bugs_where(conn, clause, kind)
73
74 def get_bugs(conn):
75     yield from get_ftbfs(conn)
76     email = 'debian-cross@lists.debian.org'
77     yield from get_usertagged(conn, email, 'cross-satisfiability', 'bdsat')
78     yield from get_usertagged(conn, email, 'ftcbfs', 'ftcbfs')
79
80 def main():
81     udde = sqlalchemy.create_engine(
82         'postgresql://udd-mirror:udd-mirror@udd-mirror.debian.net/'
83         'udd?client_encoding=utf8')
84     with udde.connect() as conn:
85         bugs = list(get_bugs(conn))
86
87     query = sqlalchemy.text("""
88         INSERT INTO bugs (kind, bugnum, package, affects, title, patched)
89             VALUES (:kind, :bugnum, :package, :affects, :title, :patched);""")
90     crosse = sqlalchemy.create_engine('sqlite:///db')
91     with crosse.connect() as conn:
92         with conn.begin():
93             conn.execute("DELETE FROM bugs;")
94             conn.execute(query, bugs)
95
96 if __name__ == "__main__":
97     main()