From f5dd41212f89f4392b0d33b87bd5fda8648c2bd3 Mon Sep 17 00:00:00 2001
From: gongzt <gong_zhengtang@163.com>
Date: Tue, 10 Oct 2023 16:02:17 +0800
Subject: Optimize cve query performance
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 apollo/database/proxy/cve.py  | 260 +++++++++-------------------------
 apollo/database/proxy/host.py |  70 +++++----
 apollo/database/table.py      |   1 +
 database/apollo.sql           | 104 +++++++++++++-
 4 files changed, 215 insertions(+), 220 deletions(-)

diff --git a/apollo/database/proxy/cve.py b/apollo/database/proxy/cve.py
index 3a6ec01..4125894 100644
--- a/apollo/database/proxy/cve.py
+++ b/apollo/database/proxy/cve.py
@@ -17,11 +17,10 @@ Description: Host table operation
 """
 import math
 import copy
-from typing import List, Tuple
 from collections import defaultdict
 
 from elasticsearch import ElasticsearchException
-from sqlalchemy import func, tuple_, case, distinct, or_
+from sqlalchemy import func, tuple_, case
 from sqlalchemy.exc import SQLAlchemyError
 from vulcanus.database.helper import sort_and_page, judge_return_code
 from vulcanus.database.proxy import MysqlProxy, ElasticsearchProxy
@@ -83,40 +82,15 @@ class CveMysqlProxy(MysqlProxy):
         """
         result = {"Critical": 0, "High": 0, "Medium": 0, "Low": 0, "Unknown": 0}
         username = data["username"]
-        cve_overview_query = self._query_cve_overview(username).all()
+        cve_overview = self.session.execute("CALL GET_CVE_OVERVIEW_PRO(:username)", {"username": username}).fetchall()
 
-        for severity, count in cve_overview_query:
+        for severity, count in cve_overview:
             if severity not in result:
                 LOGGER.debug("Unknown cve severity '%s' when getting overview." % severity)
                 continue
             result[severity] = count
         return {"result": result}
 
-    def _query_cve_overview(self, username):
-        """
-        query cve overview
-        Args:
-            username (str): user name of the request
-
-        Returns:
-            sqlalchemy.orm.query.Query
-        """
-        cve_id_with_severity = (
-            self.session.query(
-                distinct(CveHostAssociation.cve_id),
-                case([(Cve.severity == None, "Unknown")], else_=Cve.severity).label("severity"),
-            )
-            .select_from(CveHostAssociation)
-            .outerjoin(Cve, CveHostAssociation.cve_id == Cve.cve_id)
-            .outerjoin(Host, CveHostAssociation.host_id == Host.host_id)
-            .filter(CveHostAssociation.affected == 1, CveHostAssociation.fixed == 0, Host.user == username)
-            .subquery()
-        )
-        cve_overview_query = self.session.query(
-            cve_id_with_severity.c.severity, func.count(cve_id_with_severity.c.severity)
-        ).group_by(cve_id_with_severity.c.severity)
-        return cve_overview_query
-
     def get_cve_host(self, data):
         """
         Get hosts info of a cve
@@ -250,7 +224,7 @@ class CveMysqlProxy(MysqlProxy):
                 CveHostAssociation.fixed,
             )
             .join(CveHostAssociation, Host.host_id == CveHostAssociation.host_id)
-            .filter(Host.user == username, CveHostAssociation.cve_id == cve_id)
+            .filter(CveHostAssociation.cve_id == cve_id, Host.user == username)
             .filter(*filters)
             .group_by(Host.host_id)
         )
@@ -501,7 +475,7 @@ class CveMysqlProxy(MysqlProxy):
         if host_list:
             filters.add(Host.host_id.in_(host_list))
 
-        cve_query = (
+        cve_host_list = (
             self.session.query(
                 CveHostAssociation.cve_id,
                 Host.host_id,
@@ -514,8 +488,9 @@ class CveMysqlProxy(MysqlProxy):
             .join(CveHostAssociation, Host.host_id == CveHostAssociation.host_id)
             .filter(CveHostAssociation.cve_id.in_(cve_list))
             .filter(*filters)
+            .all()
         )
-        return cve_query
+        return cve_host_list
 
     def _get_cve_source_pkg(self, cve_list: list) -> dict:
         """
@@ -675,178 +650,70 @@ class CveProxy(CveMysqlProxy, CveEsProxy):
             EsOperationError
         """
         result = {"total_count": 0, "total_page": 0, "result": []}
+        cve_list, total = self._query_cve_list(data)
 
-        filters = self._get_cve_list_filters(data.get("filter", {}), data["username"])
-        cve_query = self._query_cve_list(filters)
-
-        total_count = len(cve_query.all())
-        if not total_count:
-            return result
-
-        cve_info_list, cve_pkg_dict = self._preprocess_cve_list_query(cve_query)
-        processed_cve_list, total_page = self._sort_and_page_cve_list(cve_info_list, data)
-        description_dict = self._get_cve_description([cve_info["cve_id"] for cve_info in processed_cve_list])
+        description_dict = self._get_cve_description([cve_info["cve_id"] for cve_info in cve_list])
 
-        result['result'] = self._add_additional_info_to_cve_list(processed_cve_list, description_dict, cve_pkg_dict)
-        result['total_page'] = total_page
-        result['total_count'] = total_count
+        result['result'] = self._add_description_to_cve(cve_list, description_dict)
+        result['total_page'] = math.ceil(total / data["per_page"])
+        result['total_count'] = total
 
         return result
 
     @staticmethod
-    def _get_cve_list_filters(filter_dict, username):
-        """
-        Generate filters
+    def _sort_and_page_cve_list(data) -> dict:
+        start_limt = int(data["per_page"]) * (int(data["page"]) - 1)
+        end_limt = int(data["per_page"]) * int(data["page"])
 
-        Args:
-            filter_dict(dict): filter dict to filter cve list, e.g.
-                {
-                    "cve_id": "2021",
-                    "severity": ["high"],
-                    "affected": True,
-                    "fixed": True,
-                    "package": "kernel"
-                }
-            username(str): admin
-
-        Returns:
-            set
-        """
-        filters = {Host.user == username}
-        if not filter_dict:
-            return filters
+        # sort by host num by default
+        order_by_filed = data.get('sort', "cve_host_user_count.host_num")
+        if order_by_filed == "host_num":
+            order_by_filed = "cve_host_user_count.host_num"
+        order_by = "dsc" if data.get("direction") == "desc" else "asc"
 
-        if filter_dict.get("search_key"):
-            filters.add(
-                or_(
-                    CveHostAssociation.cve_id.like("%" + filter_dict["search_key"] + "%"),
-                    CveAffectedPkgs.package.like("%" + filter_dict["search_key"] + "%"),
-                )
-            )
-        if filter_dict.get("severity"):
-            filters.add(Cve.severity.in_(filter_dict["severity"]))
-        if "fixed" in filter_dict:
-            filters.add(CveHostAssociation.fixed == filter_dict["fixed"])
-        if "affected" in filter_dict:
-            filters.add(CveHostAssociation.affected == filter_dict["affected"])
-        return filters
+        return {"start_limt": start_limt, "end_limt": end_limt, "order_by_filed": order_by_filed, "order_by": order_by}
 
-    def _query_cve_list(self, filters):
+    def _query_cve_list(self, data):
         """
         query needed cve info
         Args:
-            filters (set): filter given by user
+            data (set): filter given by user
 
         Returns:
             sqlalchemy.orm.query.Query: attention, two rows may have same cve id with different source package.
         """
-        cve_query = (
-            self.session.query(
-                CveHostAssociation.cve_id,
-                case([(Cve.publish_time == None, "")], else_=Cve.publish_time).label("publish_time"),
-                case([(CveAffectedPkgs.package == None, "")], else_=CveAffectedPkgs.package).label("package"),
-                case([(Cve.severity == None, "")], else_=Cve.severity).label("severity"),
-                case([(Cve.cvss_score == None, "")], else_=Cve.cvss_score).label("cvss_score"),
-                func.count(distinct(CveHostAssociation.host_id)).label("host_num"),
-            )
-            .outerjoin(Cve, CveHostAssociation.cve_id == Cve.cve_id)
-            .outerjoin(Host, Host.host_id == CveHostAssociation.host_id)
-            .outerjoin(CveAffectedPkgs, CveAffectedPkgs.cve_id == CveHostAssociation.cve_id)
-            .filter(*filters)
-            .group_by(CveHostAssociation.cve_id, CveAffectedPkgs.package)
-        )
-        return cve_query
+        filters = {"username": data["username"], "search_key": None, "severity": None, "affected": True}
+        filters.update(data.get("filter", {}))
+        filters.update(self._sort_and_page_cve_list(data))
+        if filters["severity"]:
+            filters["severity"] = ",".join(["'" + serverity + "'" for serverity in filters["severity"]])
 
-    @staticmethod
-    def _preprocess_cve_list_query(cve_list_query) -> Tuple[List[dict], dict]:
-        """
-        get each cve's source package set and deduplication rows by cve id
-        Args:
-            cve_list_query(sqlalchemy.orm.query.Query): rows of cve list info (two rows may have same cve id
-                with different source package)
-        Returns:
-            list: list of cve info without package and description.
-            dict: key is cve id, value is cve affected source package joined with ',', e.g. 'kernel,vim'
-        """
-        cve_pkgs_dict = defaultdict(set)
-        cve_info_list = []
-        for row in cve_list_query:
-            cve_id = row.cve_id
-            if cve_id not in cve_pkgs_dict:
-                cve_info = {
-                    "cve_id": cve_id,
-                    "publish_time": row.publish_time,
-                    "severity": row.severity,
-                    "cvss_score": row.cvss_score,
-                    "host_num": row.host_num,
-                }
-                cve_info_list.append(cve_info)
-            cve_pkgs_dict[cve_id].add(row.package)
-
-        final_cve_pkgs_dict = {}
-        for cve_id, pkg_set in cve_pkgs_dict.items():
-            final_cve_pkgs_dict[cve_id] = ",".join(list(pkg_set))
-        return cve_info_list, final_cve_pkgs_dict
-
-    @staticmethod
-    def _sort_and_page_cve_list(cve_info_list, data) -> Tuple[list, int]:
-        """
-        sort and page cve info
-        Args:
-            cve_info_list (list): cve info list. not empty.
-            data (dict): parameter, e.g.
-                {
-                    "sort": "cve_id",
-                    "direction": "asc",
-                    "page": 1,
-                    "per_page": 10,
-                    "username": "admin",
-                    "filter": {
-                        "cve_id": "cve-2021",
-                        "severity": "medium",
-                        "affected": True,
-                        "fixed": True,
-                        "package": "kernel"
-                    }
-                }
-
-        Returns:
-            list: sorted cve info list
-            int: total page
-        """
-        page = data.get('page')
-        per_page = data.get('per_page')
-        # sort by host num by default
-        sort_column = data.get('sort', "host_num")
-        reverse = True if data.get("direction") == "desc" else False
-
-        total_page = 1
-        total_count = len(cve_info_list)
-
-        cve_info_list.sort(key=lambda cve_info: cve_info[sort_column], reverse=reverse)
-
-        if page and per_page:
-            total_page = math.ceil(total_count / per_page)
-            return cve_info_list[per_page * (page - 1) : per_page * page], total_page
-
-        return cve_info_list, total_page
+        # Call stored procedure: GET_CVE_LIST_PRO
+        pro_result_set = self.session.execute(
+            "CALL GET_CVE_LIST_PRO(:username,:search_key,:severity,:fixed,:affected,:order_by_filed,:order_by,:start_limt,:end_limt)",
+            filters,
+        )
+        cursor = pro_result_set.cursor
+        columns = [column[0] for column in cursor.description]
+        cve_list = [dict(zip(columns, cve)) for cve in cursor.fetchall()]
+        cursor.nextset()
+        total = cursor.fetchone()[0]
+        return cve_list, total
 
     @staticmethod
-    def _add_additional_info_to_cve_list(cve_info_list, description_dict, cve_package_dict):
+    def _add_description_to_cve(cve_info_list, description_dict):
         """
-        add description and affected source packages for each cve
+        add description for each cve
         Args:
             cve_info_list: list of cve info without description and package
             description_dict (dict): key is cve's id, value is cve's description
-            cve_package_dict (dict): key is cve's id, value is cve's packages joined with ','
 
         Returns:
             list
         """
         for cve_info in cve_info_list:
             cve_id = cve_info["cve_id"]
-            cve_info["description"] = description_dict[cve_id] if description_dict.get(cve_id) else ""
-            cve_info["package"] = cve_package_dict[cve_id] if cve_package_dict.get(cve_id) else ""
+            cve_info["description"] = description_dict[cve_id] if description_dict.get(cve_id) else None
         return cve_info_list
 
     def get_cve_info(self, data):
@@ -906,8 +773,7 @@ class CveProxy(CveMysqlProxy, CveEsProxy):
         cve_id = data["cve_id"]
         username = data["username"]
 
-        cve_info_query = self._query_cve_info(username, cve_id)
-        cve_info_data = cve_info_query.first()
+        cve_info_data = self._query_cve_info(cve_id)
         if cve_info_data:
             # raise exception when multiple record found
 
@@ -928,24 +794,27 @@ class CveProxy(CveMysqlProxy, CveEsProxy):
             }
         return SUCCEED, {"result": info_dict}
 
-    def _query_cve_info(self, username, cve_id):
+    def _query_cve_info(self, cve_id):
         """
         query needed cve info
         Args:
-            username (str): user name of the request
             cve_id (str): cve id
 
         Returns:
             sqlalchemy.orm.query.Query
         """
-        cve_info_query = self.session.query(
-            case([(Cve.cve_id == None, "")], else_=Cve.cve_id).label("cve_id"),
-            case([(Cve.publish_time == None, "")], else_=Cve.publish_time).label("publish_time"),
-            case([(Cve.severity == None, "")], else_=Cve.severity).label("severity"),
-            case([(Cve.cvss_score == None, "")], else_=Cve.cvss_score).label("cvss_score"),
-        ).filter(Cve.cve_id == cve_id)
+        cve_info = (
+            self.session.query(
+                case([(Cve.cve_id == None, "")], else_=Cve.cve_id).label("cve_id"),
+                case([(Cve.publish_time == None, "")], else_=Cve.publish_time).label("publish_time"),
+                case([(Cve.severity == None, "")], else_=Cve.severity).label("severity"),
+                case([(Cve.cvss_score == None, "")], else_=Cve.cvss_score).label("cvss_score"),
+            )
+            .filter(Cve.cve_id == cve_id)
+            .first()
+        )
 
-        return cve_info_query
+        return cve_info
 
     def _get_affected_pkgs(self, cve_id):
         """
@@ -979,20 +848,25 @@ class CveProxy(CveMysqlProxy, CveEsProxy):
             return []
         pkg_list = [pkg["package"] for pkg in pkg_list]
 
-        exist_cve_query = (
+        exist_cve = (
             self.session.query(CveHostAssociation.cve_id)
-            .join(Host, Host.host_id == CveHostAssociation.host_id)
-            .filter(Host.user == username, CveHostAssociation.affected == 1, CveHostAssociation.fixed == 0)
+            .filter(
+                CveHostAssociation.host_user == username,
+                CveHostAssociation.fixed == 0,
+                CveHostAssociation.affected == 1,
+            )
             .distinct()
+            .all()
         )
-
-        related_cve_query = (
+        cve_ids = [cve.cve_id for cve in exist_cve]
+        related_cve = (
             self.session.query(CveAffectedPkgs.cve_id)
-            .filter(CveAffectedPkgs.package.in_(pkg_list), CveAffectedPkgs.cve_id.in_(exist_cve_query.subquery()))
+            .filter(CveAffectedPkgs.package.in_(pkg_list), CveAffectedPkgs.cve_id.in_(cve_ids))
             .distinct()
+            .all()
         )
 
-        related_cve = [row[0] for row in related_cve_query.all() if row[0] != cve_id]
+        related_cve = [cve.cve_id for cve in related_cve if cve.cve_id != cve_id]
 
         return related_cve
 
diff --git a/apollo/database/proxy/host.py b/apollo/database/proxy/host.py
index c46394f..4b75f1c 100644
--- a/apollo/database/proxy/host.py
+++ b/apollo/database/proxy/host.py
@@ -103,20 +103,44 @@ class HostMysqlProxy(MysqlProxy):
         if not total_count:
             return result
 
-        sort_column = self._get_host_list_sort_column(data.get('sort'), host_query)
+        sort_column = self._get_host_list_sort_column(data.get('sort'))
         direction, page, per_page = data.get('direction'), data.get('page'), data.get('per_page')
 
         processed_query, total_page = sort_and_page(host_query, sort_column, direction, per_page, page)
 
         host_rows = processed_query.all()
-        result['result'] = self._host_list_row2dict(host_rows)
+        host_ids = [host.host_id for host in host_rows]
+        host_cve_fixed_info = self._get_host_cve_fixed_info(host_ids)
+        result['result'] = self._host_list_row2dict(host_rows, host_cve_fixed_info)
         result['total_page'] = total_page
         result['total_count'] = total_count
 
         return result
 
+    def _get_host_cve_fixed_info(self, host_ids):
+        """
+        Get host cve fixed info
+        Args:
+            host_ids(list): host id list
+
+        Returns:
+            dict
+        """
+
+        host_cve_fixed_list = (
+            self.session.query(
+                CveHostAssociation.host_id,
+                func.COUNT(func.IF(CveHostAssociation.fixed == True, 1, None)).label("fixed_cve_num"),
+                func.COUNT(func.IF(CveHostAssociation.fixed == False, 1, None)).label("unfixed_cve_num"),
+            )
+            .filter(CveHostAssociation.host_id.in_(host_ids))
+            .group_by(CveHostAssociation.host_id)
+            .all()
+        )
+        return host_cve_fixed_list
+
     @staticmethod
-    def _get_host_list_sort_column(column_name, query=None):
+    def _get_host_list_sort_column(column_name):
         """
         get column or aggregation column of table by name
         Args:
@@ -127,8 +151,6 @@ class HostMysqlProxy(MysqlProxy):
         """
         if not column_name:
             return None
-        if column_name == "cve_num" and query is not None:
-            return query.c.cve_num
         return getattr(Host, column_name)
 
     def _query_host_list(self, filters):
@@ -140,7 +162,6 @@ class HostMysqlProxy(MysqlProxy):
         Returns:
             sqlalchemy.orm.query.Query
         """
-        subquery = self._query_host_cve_info().distinct().subquery()
         query = (
             self.session.query(
                 Host.host_id,
@@ -149,39 +170,30 @@ class HostMysqlProxy(MysqlProxy):
                 Host.host_group_name,
                 Host.repo_name,
                 Host.last_scan,
-                func.COUNT(func.IF(subquery.c.fixed == True, 1, None)).label("fixed_cve_num"),
-                func.COUNT(func.IF(subquery.c.fixed == False, 1, None)).label("unfixed_cve_num"),
             )
-            .outerjoin(subquery, Host.host_id == subquery.c.host_id)
             .group_by(Host.host_id)
             .filter(*filters)
         )
 
         return query
 
-    def _query_host_cve_info(self):
-        """
-        query host with its cve
-
-        Returns:
-            sqlalchemy.orm.query.Query
-        """
-        return self.session.query(
-            CveHostAssociation.host_id, CveHostAssociation.cve_id, CveHostAssociation.fixed, CveHostAssociation.affected
-        )
-
     @staticmethod
-    def _host_list_row2dict(rows):
+    def _host_list_row2dict(rows, host_cve_fixed_info):
         result = []
+        host_cve_fixed_info_dict = {host_cve.host_id: host_cve for host_cve in host_cve_fixed_info}
         for row in rows:
+            unfixed_cve_num, fixed_cve_num = 0, 0
+            if row.host_id in host_cve_fixed_info_dict:
+                unfixed_cve_num = host_cve_fixed_info_dict[row.host_id].unfixed_cve_num
+                fixed_cve_num = host_cve_fixed_info_dict[row.host_id].fixed_cve_num
             host_info = {
                 "host_id": row.host_id,
                 "host_name": row.host_name,
                 "host_ip": row.host_ip,
                 "host_group": row.host_group_name,
                 "repo": row.repo_name,
-                "unfixed_cve_num": row.unfixed_cve_num,
-                "fixed_cve_num": row.fixed_cve_num,
+                "unfixed_cve_num": unfixed_cve_num,
+                "fixed_cve_num": fixed_cve_num,
                 "last_scan": row.last_scan,
             }
             result.append(host_info)
@@ -373,7 +385,17 @@ class HostMysqlProxy(MysqlProxy):
         Returns:
             sqlalchemy.orm.query.Query
         """
-        subquery = self._query_host_cve_info().distinct().subquery()
+        subquery = (
+            self.session.query(
+                CveHostAssociation.host_id,
+                CveHostAssociation.fixed,
+                CveHostAssociation.affected,
+                CveHostAssociation.cve_id,
+            )
+            .filter(CveHostAssociation.host_id == host_id)
+            .distinct()
+            .subquery()
+        )
         query = (
             self.session.query(
                 Host.host_id,
diff --git a/apollo/database/table.py b/apollo/database/table.py
index 2e607d3..04b1e4b 100644
--- a/apollo/database/table.py
+++ b/apollo/database/table.py
@@ -126,6 +126,7 @@ class CveHostAssociation(Base, MyBase):
     hp_status = Column(String(20))
     installed_rpm = Column(String(100))
     available_rpm = Column(String(100))
+    host_user = Column(String(100))
 
 
 class CveAffectedPkgs(Base, MyBase):
diff --git a/database/apollo.sql b/database/apollo.sql
index d90376f..4e87727 100644
--- a/database/apollo.sql
+++ b/database/apollo.sql
@@ -48,8 +48,11 @@ CREATE TABLE IF NOT EXISTS `cve_host_match`  (
   `hp_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
   `installed_rpm` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
   `available_rpm` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
+  `host_user` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
   PRIMARY KEY (`id`) USING BTREE,
   INDEX `ix_cve_host_match_host_id`(`host_id`) USING BTREE,
+  INDEX `ix_cve_host_match_cve_id`(`cve_id`) USING BTREE,
+  INDEX `ix_cve_hsot_match_user`(`host_user`) USING BTREE,
   CONSTRAINT `cve_host_match_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `host` (`host_id`) ON DELETE CASCADE ON UPDATE RESTRICT
 ) ENGINE = InnoDB AUTO_INCREMENT = 2621 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
 
@@ -77,7 +80,7 @@ CREATE TABLE IF NOT EXISTS `task_cve_host`  (
   `task_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `cve_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `host_id` int(11) NOT NULL,
-  `host_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
+  `host_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `host_ip` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
   `hotpatch` tinyint(4) NULL DEFAULT NULL,
@@ -88,7 +91,7 @@ CREATE TABLE IF NOT EXISTS `task_cve_host`  (
 CREATE TABLE IF NOT EXISTS `task_host_repo` (
   `task_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `host_id` int(11) NOT NULL,
-  `host_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
+  `host_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `host_ip` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `repo_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
@@ -119,4 +122,99 @@ CREATE TABLE IF NOT EXISTS `task_rollback`(
   `dnf_event_start` int(11) NULL DEFAULT NULL,
   `dnf_event_end` int(11) NULL DEFAULT NULL,
   PRIMARY KEY (`id`) USING BTREE
-) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
\ No newline at end of file
+) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
+
+CREATE PROCEDURE GET_CVE_LIST_PRO(IN username VARCHAR(20), IN search_key VARCHAR(100), IN severity VARCHAR(20), IN fixed TINYINT, IN affected TINYINT,IN order_by_filed VARCHAR(50),IN order_by VARCHAR(20),IN start_limt INT,IN end_limt INT)
+BEGIN
+		
+		DROP TABLE IF EXISTS cve_host_user_count;
+    SET @tmp_cve_host_count_sql = 'CREATE TEMPORARY TABLE cve_host_user_count SELECT
+    cve_id,
+    COUNT(host_id) AS host_num
+    FROM
+        cve_host_match FORCE INDEX (ix_cve_host_match_host_id)
+    WHERE 1=1 ';
+
+    IF search_key is not null and search_key !='' THEN
+        SET @tmp_cve_host_count_sql = CONCAT(@tmp_cve_host_count_sql, ' AND LOCATE("', search_key, '", cve_id) > 0 ');
+    END IF;
+    IF fixed is not null THEN
+        SET @tmp_cve_host_count_sql = CONCAT(@tmp_cve_host_count_sql, ' AND fixed = ', fixed, ' ');
+    END IF;
+    IF affected is not null THEN
+        SET @tmp_cve_host_count_sql = CONCAT(@tmp_cve_host_count_sql, ' AND affected = ', affected, ' ');
+    END IF;
+
+    SET @tmp_cve_host_count_sql = CONCAT(@tmp_cve_host_count_sql, ' AND host_user = "', username, '" GROUP BY cve_id');
+		
+		prepare stmt from @tmp_cve_host_count_sql;
+    EXECUTE stmt;
+		DEALLOCATE PREPARE stmt;
+
+    SET @cve_list_sql = 'SELECT
+        cve_host_user_count.cve_id,
+        cve.publish_time,
+        cve_pkg.package,
+        cve.severity,
+        cve.cvss_score,
+				cve_host_user_count.host_num
+    FROM
+        cve_host_user_count
+        LEFT JOIN cve ON cve.cve_id = cve_host_user_count.cve_id
+				LEFT JOIN (select DISTINCT cve_id,  GROUP_CONCAT(DISTINCT package SEPARATOR ",") AS package from cve_affected_pkgs group by cve_id) as cve_pkg ON cve_host_user_count.cve_id = cve_pkg.cve_id where 1=1 ';
+				
+		set @cve_list_page_count_sql='SELECT
+        count(1) as total
+    FROM
+        cve_host_user_count
+        LEFT JOIN cve ON cve.cve_id = cve_host_user_count.cve_id
+        LEFT JOIN (select cve_id,package from cve_affected_pkgs GROUP BY cve_id,package) as cve_pkg ON cve_host_user_count.cve_id = cve_pkg.cve_id where 1=1 ';
+
+    IF search_key IS NOT NULL and search_key !='' THEN
+        SET @cve_list_sql = CONCAT(@cve_list_sql, 'AND ( LOCATE("', search_key, '", cve_pkg.package) > 0 ',' OR LOCATE("',search_key, '", cve_host_user_count.cve_id) > 0 ) ');
+				SET @cve_list_page_count_sql = CONCAT(@cve_list_page_count_sql, 'AND ( LOCATE("', search_key, '", cve_pkg.package) > 0 ',' OR LOCATE("',search_key, '", cve_host_user_count.cve_id) > 0 ) ');
+    END IF;
+    IF severity IS NOT NULL and severity !='' THEN
+        SET @cve_list_sql = CONCAT(@cve_list_sql, 'AND cve.severity IN (', severity, ') ');
+				SET @cve_list_page_count_sql = CONCAT(@cve_list_page_count_sql, 'AND cve.severity IN (', severity, ') ');
+    END IF;
+		
+		IF order_by_filed IS NULL or order_by_filed ='' THEN
+        SET @order_by_filed = 'cve_host_user_count.host_num';
+    END IF;
+		
+    SET @cve_list_sql = CONCAT(@cve_list_sql, ' ORDER BY ', order_by_filed ,' ', order_by,' limit ',start_limt ,' ,', end_limt);
+		
+		prepare stmt from @cve_list_sql;
+    EXECUTE stmt;
+		DEALLOCATE PREPARE stmt;
+		
+		prepare stmt from @cve_list_page_count_sql;
+    EXECUTE stmt;
+		DEALLOCATE PREPARE stmt;
+
+END;
+
+CREATE PROCEDURE GET_CVE_OVERVIEW_PRO(IN username VARCHAR(20))
+BEGIN
+		
+		DROP TABLE IF EXISTS tmp_cve_overview;
+    SET @tmp_cve_overview_sql = 'CREATE TEMPORARY TABLE tmp_cve_overview SELECT cve_id from cve_host_match where ';
+
+    SET @tmp_cve_overview_sql = CONCAT(@tmp_cve_overview_sql, ' host_user = "', username, '" and  affected=1 and fixed=0 GROUP BY cve_id ');
+		
+		prepare stmt from @tmp_cve_overview_sql;
+    EXECUTE stmt;
+		DEALLOCATE PREPARE stmt;
+		
+		select CASE WHEN cve.severity is null THEN 'Unknown' ELSE cve.severity END as severity,count( CASE WHEN cve.severity is null THEN 'Unknown' ELSE cve.severity END ) as severity_count from tmp_cve_overview left join cve on cve.cve_id=tmp_cve_overview.cve_id GROUP BY cve.severity;
+
+END;
+
+CREATE TRIGGER tri_cvehost_match_user BEFORE INSERT ON cve_host_match
+FOR EACH ROW
+begin
+	DECLARE host_user varchar(100);
+	SELECT user into @host_user from host where host_id=new.host_id;
+	set new.host_user=@host_user;
+end;
\ No newline at end of file
-- 
Gitee

