def _save_packages(self, db, db_report, packages, count=1):
for package in packages:
role = "RELATED"
if "package_role" in package:
if package["package_role"] == "affected":
role = "CRASHED"
elif package["package_role"] == "selinux_policy":
role = "SELINUX_POLICY"
db_package = get_package_by_nevra(db,
name=package["name"],
epoch=package["epoch"],
version=package["version"],
release=package["release"],
arch=package["architecture"])
if db_package is None:
self.log_warn("Package {0}-{1}:{2}-{3}.{4} not found in "
"storage".format(package["name"],
package["epoch"],
package["version"],
package["release"],
package["architecture"]))
db_unknown_pkg = get_unknown_package(db, db_report, role,
package["name"],
package["epoch"],
package["version"],
package["release"],
package["architecture"])
if db_unknown_pkg is None:
db_arch = get_arch_by_name(db, package["architecture"])
if db_arch is None:
continue
db_unknown_pkg = ReportUnknownPackage()
db_unknown_pkg.report = db_report
db_unknown_pkg.name = package["name"]
db_unknown_pkg.epoch = package["epoch"]
db_unknown_pkg.version = package["version"]
db_unknown_pkg.release = package["release"]
db_unknown_pkg.semver = to_semver(package["version"])
db_unknown_pkg.semrel = to_semver(package["release"])
db_unknown_pkg.arch = db_arch
db_unknown_pkg.type = role
db_unknown_pkg.count = 0
db.session.add(db_unknown_pkg)
db_unknown_pkg.count += count
continue
db_reportpackage = get_reportpackage(db, db_report, db_package)
if db_reportpackage is None:
db_reportpackage = ReportPackage()
db_reportpackage.report = db_report
db_reportpackage.installed_package = db_package
db_reportpackage.count = 0
db_reportpackage.type = role
db.session.add(db_reportpackage)
db_reportpackage.count += count
def upgrade():
add_column('reportunknownpackages',
sa.Column('semver', custom_types.Semver(), nullable=True))
add_column('reportunknownpackages',
sa.Column('semrel', custom_types.Semver(), nullable=True))
reportunknownpackage = sa.Table(
"reportunknownpackages", metadata, sa.Column("id", sa.Integer),
sa.Column("report_id", sa.Integer),
sa.Column(
"type",
sa.Enum("CRASHED",
"RELATED",
"SELINUX_POLICY",
name="reportpackage_type")),
sa.Column("name", sa.String(length=64)),
sa.Column("epoch", sa.Integer),
sa.Column("version", sa.String(length=64)),
sa.Column("release", sa.String(length=64)),
sa.Column("arch_id", sa.Integer), sa.Column("count", sa.Integer),
sa.Column("semver", custom_types.Semver()),
sa.Column("semrel", custom_types.Semver()))
for (pkg_id, version, release) in get_bind().execute(
sa.select([
reportunknownpackage.c.id, reportunknownpackage.c.version,
reportunknownpackage.c.release
])):
semver = custom_types.to_semver(version)
semrel = custom_types.to_semver(release)
get_bind().execute((
reportunknownpackage.update() #pylint: disable=no-value-for-parameter
.where(reportunknownpackage.c.id == pkg_id).values(
semver=sa.func.to_semver(semver),
semrel=sa.func.to_semver(semrel))))
alter_column('reportunknownpackages',
sa.Column('semver', custom_types.Semver(), nullable=False))
alter_column('reportunknownpackages',
sa.Column('semrel', custom_types.Semver(), nullable=False))
create_index('ix_reportunknownpackages_semver_semrel',
'reportunknownpackages', ['semver', 'semrel'],
unique=False)
create_index('ix_builds_semver_semrel',
'builds', ['semver', 'semrel'],
unique=False)
drop_index('ix_builds_semver', table_name='builds')
drop_index('ix_builds_semrel', table_name='builds')
def upgrade():
op.add_column('builds',
sa.Column('semver', custom_types.Semver(), nullable=True))
build = sa.Table(
"builds",
metadata,
sa.Column("id", sa.Integer),
sa.Column("base_package_name", sa.String(length=64)),
sa.Column("projrelease_id", sa.Integer),
sa.Column("epoch", sa.Integer),
sa.Column("version", sa.String(length=64)),
sa.Column("release", sa.String(length=64)),
sa.Column("semver", custom_types.Semver()),
)
for b in op.get_bind().execute(sa.select([build.c.id, build.c.version])):
bid, bver = b
bver = custom_types.to_semver(bver)
op.get_bind().execute((build.update().where(build.c.id == bid).values(
semver=sa.func.to_semver(bver))))
op.alter_column('builds',
sa.Column('semver', custom_types.Semver(), nullable=False))
op.create_index('ix_builds_semver', 'builds', ['semver'])
def upgrade():
op.add_column('builds', sa.Column('semrel', custom_types.Semver(),
nullable=True))
build = sa.Table("builds", metadata,
sa.Column("id", sa.Integer),
sa.Column("base_package_name", sa.String(length=64)),
sa.Column("projrelease_id", sa.Integer),
sa.Column("epoch", sa.Integer),
sa.Column("version", sa.String(length=64)),
sa.Column("release", sa.String(length=64)),
sa.Column("semver", custom_types.Semver()),
sa.Column("semrel", custom_types.Semver()),
)
for b in op.get_bind().execute(sa.select([build.c.id, build.c.release])):
bid, brel = b
brel = custom_types.to_semver(brel)
op.get_bind().execute((build.update()
.where(build.c.id == bid)
.values(semrel=sa.func.to_semver(brel))))
op.alter_column('builds', sa.Column('semrel', custom_types.Semver(),
nullable=False))
op.create_index('ix_builds_semrel', 'builds', ['semrel'])
def upgrade():
add_column('builds',
sa.Column('semrel', custom_types.Semver(), nullable=True))
build = sa.Table(
"builds",
metadata,
sa.Column("id", sa.Integer),
sa.Column("base_package_name", sa.String(length=64)),
sa.Column("projrelease_id", sa.Integer),
sa.Column("epoch", sa.Integer),
sa.Column("version", sa.String(length=64)),
sa.Column("release", sa.String(length=64)),
sa.Column("semver", custom_types.Semver()),
sa.Column("semrel", custom_types.Semver()),
)
for b in get_bind().execute(sa.select([build.c.id, build.c.release])):
bid, brel = b
brel = custom_types.to_semver(brel)
get_bind().execute((
build.update() #pylint: disable=no-value-for-parameter
.where(build.c.id == bid).values(semrel=sa.func.to_semver(brel))))
alter_column('builds',
sa.Column('semrel', custom_types.Semver(), nullable=False))
create_index('ix_builds_semrel', 'builds', ['semrel'])
def test_to_semver(self):
"""
Test if Semver custom data type converts
version string appropriately
"""
test_cases = [
('aa11', '11.0.0'),
('1.2.3', '1.2.3'),
('1.2.3-gea123', '1.2.3-gea123'),
('1.2.3-15', '1.2.315'),
('0.1', '0.1.0'),
('.', '0.0.0'),
('.1', '0.1.0'),
(',1', '0.1.0'),
('.1.1', '0.1.1'),
('.1.', '0.1.0'),
('.1..', '0.1.0'),
('..1', '0.0.1'),
('..1..', '0.0.1'),
('.1..1.', '0.1.1'),
('1.', '1.0.0'),
('a', '0.0.0'),
('.a', '0.0.0'),
('a.b.c', '0.0.0'),
('0.2.4.25', '0.2.425'),
('0.2.4.25.3.4', '0.2.42534'),
('2.23_05', '2.2305.0'),
('20130222622', '2013022262.0.0'),
('2147483648', '214748364.0.0'),
('0.2.0201311250404', '0.2.02013112504'),
('2.5.20161005204600', '2.5.2016100520'),
]
for tc in test_cases:
self.assertEqual(to_semver(tc[0]), tc[1])
def test_to_semver(self):
"""
Test if Semver custom data type converts
version string appropriately
"""
test_cases = [
('aa11', '11.0.0'),
('1.2.3', '1.2.3'),
('1.2.3-gea123', '1.2.3-gea123'),
('1.2.3-15', '1.2.315'),
('0.1', '0.1.0'),
('.', '0.0.0'),
('.1', '0.1.0'),
(',1', '0.1.0'),
('.1.1', '0.1.1'),
('.1.', '0.1.0'),
('.1..', '0.1.0'),
('..1', '0.0.1'),
('..1..', '0.0.1'),
('.1..1.', '0.1.1'),
('1.', '1.0.0'),
('a', '0.0.0'),
('.a', '0.0.0'),
('a.b.c', '0.0.0'),
('0.2.4.25', '0.2.425'),
('0.2.4.25.3.4', '0.2.42534'),
('2.23_05', '2.2305.0'),
('20130222622', '2013022262.0.0'),
('2147483648', '214748364.0.0'),
('0.2.0201311250404', '0.2.02013112504'),
('2.5.20161005204600', '2.5.2016100520'),
]
for tc in test_cases:
self.assertEqual(to_semver(tc[0]), tc[1])
def query_problems(_,
hist_table,
hist_column,
opsysrelease_ids=[],
component_ids=[],
associate_id=None,
arch_ids=[],
exclude_taintflag_ids=[],
types=[],
since_date=None,
to_date=None,
function_names=[],
binary_names=[],
source_file_names=[],
since_version=None,
since_release=None,
to_version=None,
to_release=None,
probable_fix_osr_ids=[],
bug_filter=None,
solution=None):
"""Return all data rows of problems dashboard ordered by history counts"""
select_list = """
SELECT func.id AS id,
STRING_AGG(DISTINCT(opsyscomponents.name), ', ') AS components,
MAX(comp.comp_count) AS comp_count,
MAX(problem_count.count) AS count,
MAX(func.crashfn) AS crashfn,
COUNT(DISTINCT(reportmantis.mantisbug_id)) AS mantisbugs_count,
COUNT(DISTINCT(reportbz.bzbug_id)) AS bzbugs_count,
MAX(mantisbugs.status) AS mantis_status,
MAX(bzbugs.status) AS bz_status,
MAX(fix.pkg_name) AS pkg_name,
MAX(fix.pkg_version) AS pkg_version,
MAX(fix.pkg_release) AS pkg_release,
MAX(fix.name) AS opsys,
MIN(fix.version) AS opsys_release,
MAX(untainted.count) AS untainted_count
""".format(hist_table.__tablename__)
table_list = """
FROM (SELECT problems.id AS id, reportbacktraces.crashfn, COUNT(*) AS func_count
FROM reportbacktraces
JOIN reports ON reports.id = reportbacktraces.report_id
JOIN problems ON problems.id = reports.problem_id
GROUP BY problems.id, reportbacktraces.crashfn) AS func
JOIN (SELECT func.id, MAX(func.func_count) AS max_count
FROM (SELECT problems.id, reportbacktraces.crashfn, COUNT(*) AS func_count
FROM reportbacktraces
JOIN reports ON reports.id = reportbacktraces.report_id
JOIN problems ON problems.id = reports.problem_id
GROUP BY problems.id, reportbacktraces.crashfn) AS func
GROUP BY func.id) AS common_func
ON func.id = common_func.id AND func.func_count = common_func.max_count
JOIN (SELECT problems.id AS id, COUNT(DISTINCT(opsyscomponents.name)) AS comp_count
FROM problems
JOIN problemscomponents ON problems.id = problemscomponents.problem_id
JOIN opsyscomponents ON problemscomponents.component_id = opsyscomponents.id
GROUP BY problems.id) AS comp
ON comp.id = func.id
JOIN (SELECT problems.id AS problem_id, SUM({0}.count) AS count
FROM problems
JOIN reports ON problems.id = reports.problem_id
JOIN {0} ON reports.id = {0}.report_id
WHERE {0}.{1} >= :date_0 AND {0}.{1} <= :date_1
GROUP BY problems.id) AS problem_count
ON problem_count.problem_id = func.id
JOIN reports ON func.id = reports.problem_id
JOIN {0} ON reports.id = {0}.report_id
JOIN problemscomponents ON func.id = problemscomponents.problem_id
JOIN opsyscomponents ON problemscomponents.component_id = opsyscomponents.id
LEFT JOIN problemopsysreleases ON func.id = problemopsysreleases.problem_id
LEFT JOIN (SELECT problems.id AS id, STRING_AGG(opsys.name, ', ') AS name,
STRING_AGG(opsysreleases.version, ', ') As version,
STRING_AGG(builds.base_package_name, ', ') AS pkg_name,
STRING_AGG(builds.version, ', ') AS pkg_version,
STRING_AGG(builds.release, ', ') AS pkg_release
FROM problems
JOIN problemopsysreleases ON problems.id = problemopsysreleases.problem_id
JOIN opsysreleases ON problemopsysreleases.opsysrelease_id = opsysreleases.id
JOIN opsys ON opsys.id = opsysreleases.opsys_id
JOIN builds ON problemopsysreleases.probable_fix_build_id = builds.id
GROUP BY problems.id) AS fix
ON func.id = fix.id
LEFT JOIN (SELECT problem_id AS id, count(reportbacktraces.id)
FROM reports JOIN problems ON problems.id = reports.problem_id
JOIN reportbacktraces ON reports.id = reportbacktraces.report_id
WHERE (problem_id,reportbacktraces.id) NOT IN
(select backtrace_id, reportbacktraces.id from reportbttaintflags join reportbacktraces ON
reportbttaintflags.backtrace_id = reportbacktraces.id) group by problem_id ) as untainted
ON func.id = untainted.id
LEFT JOIN reportmantis ON reports.id = reportmantis.report_id
LEFT JOIN mantisbugs ON reportmantis.mantisbug_id = mantisbugs.id
LEFT JOIN reportbz ON reports.id = reportbz.report_id
LEFT JOIN bzbugs ON reportbz.bzbug_id = bzbugs.id
""".format(hist_table.__tablename__, hist_column.key)
search_condition = []
params_dict = {"date_0": since_date, "date_1": to_date}
if solution:
if not solution.data:
search_condition.append(
"(reports.max_certainty < 100 OR reports.max_certainty IS NULL)"
)
if opsysrelease_ids:
search_condition.append(
generate_condition(
params_dict,
hist_table.__tablename__ + ".opsysrelease_id IN ({0})",
"opsysrelease_id_", opsysrelease_ids))
if component_ids:
search_condition.append(
generate_condition(params_dict,
"problemscomponents.component_id IN ({0})",
"component_id_", component_ids))
if associate_id:
table_list += """
JOIN opsyscomponentsassociates
ON opsyscomponents.id = opsyscomponentsassociates.opsyscomponent_id
"""
search_condition.append(
"opsyscomponentsassociates.associatepeople_id = :associatepeople_id"
)
params_dict["associatepeople_id"] = associate_id
if types:
search_condition.append(
generate_condition(params_dict, "reports.type IN ({0})", "type_",
types))
if arch_ids:
search_condition.append(
generate_condition(params_dict, "reportarchs.arch_id IN ({0})",
"arch_", arch_ids))
table_list += " JOIN reportarchs ON reportarchs.report_id = reports.id "
if exclude_taintflag_ids:
flags_dict = {
"flag_id_" + str(index): item
for index, item in enumerate(exclude_taintflag_ids)
}
table_list += """
JOIN (SELECT DISTINCT reports.problem_id AS problem_id
FROM reports JOIN problems ON problems.id = reports.problem_id
WHERE (EXISTS (SELECT 1
FROM reportbacktraces
WHERE NOT (EXISTS (SELECT 1
FROM reportbttaintflags
WHERE reportbttaintflags.taintflag_id IN ({0})
AND reportbacktraces.id = reportbttaintflags.backtrace_id))
AND reports.id = reportbacktraces.report_id))
AND problems.id = reports.problem_id) AS flags
ON flags.problem_id = func.id
""".format(", ".join([":" + f for f in flags_dict.keys()]))
params_dict.update(flags_dict)
if probable_fix_osr_ids:
fix_condition = """
(problemopsysreleases.opsysrelease_id IN ({0})
AND problemopsysreleases.probable_fix_build_id IS NOT NULL)
"""
search_condition.append(
generate_condition(params_dict, fix_condition, "osr_",
probable_fix_osr_ids))
if bug_filter == "HAS_BUG":
search_condition.append("""
(reportbz.bzbug_id IS NOT NULL OR reportmantis.mantisbug_id IS NOT NULL)
""")
elif bug_filter == "NO_BUGS":
search_condition.append("""
(reportbz.bzbug_id IS NULL AND reportmantis.mantisbug_id IS NULL)
""")
elif bug_filter == "HAS_OPEN_BUG":
search_condition.append("""
(bzbugs.status != 'CLOSED' OR mantisbugs.status != 'CLOSED')
""")
elif bug_filter == "ALL_BUGS_CLOSED":
search_condition.append("""
(bzbugs.status = 'CLOSED' AND mantisbugs.status = 'CLOSED')
""")
if function_names:
func_name_dict = {
"func_name_" + str(index): item
for index, item in enumerate(function_names)
}
table_list += """
JOIN (SELECT DISTINCT reports.problem_id AS problem_id
FROM reports
JOIN reportbacktraces ON reports.id = reportbacktraces.report_id
JOIN reportbtthreads ON reportbacktraces.id = reportbtthreads.backtrace_id
JOIN reportbtframes ON reportbtthreads.id = reportbtframes.thread_id
JOIN symbolsources ON symbolsources.id = reportbtframes.symbolsource_id
JOIN symbols ON symbols.id = symbolsources.symbol_id
WHERE reportbtthreads.crashthread = True AND ({0})) AS functions_search
ON functions_search.problem_id = func.id
""".format(" OR ".join([
"symbols.name LIKE :{0} OR symbols.nice_name LIKE :{0}".format(
name) for name in func_name_dict.keys()
]))
params_dict.update(func_name_dict)
if binary_names or source_file_names:
names_subquery = """
JOIN (SELECT DISTINCT reports.problem_id AS problem_id
FROM reports
JOIN reportbacktraces ON reports.id = reportbacktraces.report_id
JOIN reportbtthreads ON reportbacktraces.id = reportbtthreads.backtrace_id
JOIN reportbtframes ON reportbtthreads.id = reportbtframes.thread_id
JOIN symbolsources ON symbolsources.id = reportbtframes.symbolsource_id
WHERE reportbtthreads.crashthread = True AND {0} AND {1}) AS binary_search
ON binary_search.problem_id = func.id
"""
if binary_names and source_file_names:
binary_name_dict = {
"binary_name_" + str(index): item
for index, item in enumerate(binary_names)
}
source_file_name_dict = {
"source_file_name_" + str(index): item
for index, item in enumerate(source_file_names)
}
table_list += names_subquery.format(
" OR ".join([
"symbolsources.path LIKE :{0} ".format(name)
for name in binary_name_dict.keys()
]), " OR ".join([
"symbolsources.source_path LIKE :{0} ".format(name)
for name in source_file_name_dict.keys()
]))
params_dict.update(binary_name_dict)
params_dict.update(source_file_name_dict)
elif binary_names:
binary_name_dict = {
"binary_name_" + str(index): item
for index, item in enumerate(binary_names)
}
table_list += names_subquery.format(
" OR ".join([
"symbolsources.path LIKE :{0} ".format(name)
for name in binary_name_dict.keys()
]), "True")
params_dict.update(binary_name_dict)
elif source_file_names:
source_file_name_dict = {
"source_file_name_" + str(index): item
for index, item in enumerate(source_file_names)
}
table_list += names_subquery.format(
" OR ".join([
"symbolsources.source_path LIKE :{0} ".format(name)
for name in source_file_name_dict.keys()
]), "True")
params_dict.update(source_file_name_dict)
if since_version or since_release or to_version or to_release:
params_dict['pkg_type_0'] = 'CRASHED'
version_subquery = """
JOIN (SELECT DISTINCT reports.problem_id AS problem_id
FROM reports
JOIN reportpackages ON reports.id = reportpackages.report_id
JOIN packages ON packages.id = reportpackages.installed_package_id
JOIN builds ON builds.id = packages.build_id
WHERE reportpackages.type = :pkg_type_0 AND ({0})) AS {1}
ON {1}.problem_id = func.id
"""
if since_version or since_release:
if since_version and since_release:
since_condition = """
builds.semver >= :since_ver_0 AND builds.semrel >= :since_rel_0
OR builds.semver >= :since_ver_0
"""
elif since_version:
since_condition = "builds.semver >= :since_ver_0"
elif since_release:
since_condition = "builds.semrel >= :since_rel_0"
table_list += version_subquery.format(since_condition, "since_version")
params_dict.update({
"since_ver_0": to_semver(since_version),
"since_rel_0": to_semver(since_release)
})
if to_version or to_release:
if to_version and to_release:
to_condition = """
builds.semver <= :to_ver_0 AND builds.semrel <= :to_rel_0
OR builds.semver <= :to_ver_0
"""
elif to_version:
to_condition = "builds.semver <= :to_ver_0"
elif to_release:
to_condition = "builds.semrel <= :to_rel_0"
table_list += version_subquery.format(to_condition, "to_version")
params_dict.update({
"to_ver_0": to_semver(to_version),
"to_rel_0": to_semver(to_release)
})
if search_condition:
search_condition = "WHERE " + " AND ".join(search_condition)
else:
search_condition = ""
search_condition += " GROUP BY func.id ORDER BY count DESC"
statement = text(select_list + table_list + search_condition)
return db.engine.execute(statement, params_dict)
