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)