From 0298776c0ae0ce230d2993b570abc68c9b3f9f42 Mon Sep 17 00:00:00 2001
From: gongzt <gong_zhengtang@163.com>
Date: Tue, 19 Sep 2023 10:29:18 +0800
Subject: [PATCH 1/1] add the sql script
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 aops-diana.spec                          | 10 ++-
 database/diana.sql                       | 88 ++++++++++++++++++++++++
 doc/design/aops-diana数据库设计.sql | 54 +++++++--------
 setup.py                                 |  3 +-
 4 files changed, 122 insertions(+), 33 deletions(-)
 create mode 100644 database/diana.sql

diff --git a/aops-diana.spec b/aops-diana.spec
index 259956e..ef6fcd2 100644
--- a/aops-diana.spec
+++ b/aops-diana.spec
@@ -1,5 +1,5 @@
 Name:		aops-diana
-Version:	v1.2.0
+Version:	v1.3.0
 Release:	1
 Summary:	An intelligent abnormal detection framework of aops
 License:	MulanPSL2
@@ -9,7 +9,7 @@ Source0:	%{name}-%{version}.tar.gz
 BuildRequires:  python3-setuptools
 Requires:   aops-vulcanus >= v1.2.0
 Requires:   python3-requests python3-flask python3-flask-restful python3-marshmallow >= 3.13.0
-Requires:   python3-numpy python3-pandas python3-prometheus-api-client
+Requires:   python3-numpy python3-pandas python3-prometheus-api-client python3-uWSGI
 Requires:   python3-sqlalchemy python3-PyMySQL python3-Flask-APScheduler >= 1.11.0
 Requires:   python3-scipy python3-adtk
 Provides:   aops-diana
@@ -30,6 +30,8 @@ An intelligent abnormal detection framework of aops
 
 # install for aops-diana
 %py3_install
+mkdir -p %{buildroot}/opt/aops/
+cp -r database %{buildroot}/opt/aops/
 
 
 %files
@@ -42,9 +44,13 @@ An intelligent abnormal detection framework of aops
 %attr(0755,root,root) /usr/lib/systemd/system/aops-diana.service
 %{python3_sitelib}/aops_diana*.egg-info
 %{python3_sitelib}/diana/*
+%attr(0755, root, root) /opt/aops/database/*
 
 
 %changelog
+* Mon Sep 18 2023 gongzhengtang<gong_zhengtang@163.com> - v1.3.0-1
+- Support sql script to create tables
+
 * Fri Mar 24 2023 gongzhengtang<gong_zhengtang@163.com> - v1.2.0-1
 - update the structure of response body; update how to get session used to
 - connect to the database
diff --git a/database/diana.sql b/database/diana.sql
new file mode 100644
index 0000000..d4452c4
--- /dev/null
+++ b/database/diana.sql
@@ -0,0 +1,88 @@
+use aops;
+
+CREATE TABLE IF NOT EXISTS `domain_check_result` (
+    `alert_id`      CHAR(32)   NOT NULL, 
+    `domain`        CHAR(20)   NOT NULL, 
+    `alert_name`    CHAR(50)   NULL, 
+    `time`          INT(11)    NULL, 
+    `workflow_name` CHAR(50)   NULL, 
+    `workflow_id`   CHAR(32)   NULL, 
+    `username`      CHAR(20)   NULL, 
+    `level`         CHAR(20)   NULL, 
+    `confirmed`     TINYINT(1) NULL, 
+    PRIMARY KEY (`alert_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
+
+CREATE TABLE IF NOT EXISTS `alert_host` (
+    `alert_id`  CHAR(32) NOT NULL, 
+    `host_id`   CHAR(32) NOT NULL, 
+    `host_ip`   CHAR(32) NULL, 
+    `host_name` CHAR(50) NULL, 
+    PRIMARY KEY (`host_id`,`alert_id`), 
+    KEY `FK__alert_id_auto_index` (`alert_id`), 
+    KEY `FK_alert_with_host_host_id_auto_index` (`host_id`),
+    CONSTRAINT `FK_alert_host_alert_id` FOREIGN KEY (`alert_id`) REFERENCES `domain_check_result` (`alert_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
+
+CREATE TABLE IF NOT EXISTS `algorithm` (
+    `algo_id`     INT(11)   NOT NULL, 
+    `path`        CHAR(150) NULL, 
+    `username`    CHAR(10)  NULL, 
+    `algo_name`   CHAR(20)  NOT NULL, 
+    `field`       CHAR(20)  NOT NULL, 
+    `description` LONGTEXT  NULL, 
+    PRIMARY KEY (`algo_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
+
+CREATE TABLE IF NOT EXISTS `host_check_result` (
+    `id`           INT(11)    NOT NULL, 
+    `time`         INT(11)    NOT NULL, 
+    `is_root`      TINYINT(1) NULL, 
+    `host_id`      CHAR(32)   NOT NULL, 
+    `metric_name`  CHAR(50)   NULL, 
+    `alert_id`     CHAR(32)   NOT NULL, 
+    `metric_label` CHAR(255)  NULL, 
+    PRIMARY KEY (`id`), 
+    KEY `host_id_auto_index` (`host_id`), 
+    KEY `alert_id_auto_index` (`alert_id`),
+    CONSTRAINT `FK_host_check_result_host_id` FOREIGN KEY (`host_id`) REFERENCES `alert_host` (`host_id`) ON DELETE RESTRICT ON UPDATE RESTRICT , 
+    CONSTRAINT `FK_host_check_result_alert_id` FOREIGN KEY (`alert_id`) REFERENCES `domain_check_result` (`alert_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
+
+CREATE TABLE IF NOT EXISTS `model` (
+    `model_id`    INT(11)   NOT NULL, 
+    `username`    CHAR(40)  NULL, 
+    `model_name`  CHAR(10)  NULL, 
+    `tag`         CHAR(255) NULL, 
+    `algo_id`     INT(11)   NULL, 
+    `create_time` INT(11)   NULL, 
+    `file_path`   CHAR(64)  NULL, 
+    `precision`   DOUBLE    NULL, 
+    PRIMARY KEY (`model_id`), 
+    KEY `algo_id_auto_index` (`algo_id`),
+    CONSTRAINT `FK_model_algo_id` FOREIGN KEY (`algo_id`) REFERENCES `algorithm` (`algo_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
+
+CREATE TABLE IF NOT EXISTS `workflow` (
+    `workflow_id`   CHAR(20) NOT NULL, 
+    `workflow_name` CHAR(10) NOT NULL, 
+    `status`        CHAR(12) NOT NULL, 
+    `description`   CHAR(50) NULL, 
+    `app_name`      CHAR(10) NOT NULL, 
+    `app_id`        CHAR(10) NOT NULL, 
+    `step`          INT(11)  NOT NULL, 
+    `period`        INT(11)  NOT NULL, 
+    `domain`        CHAR(20) NOT NULL, 
+    `username`      CHAR(40) NOT NULL, 
+    PRIMARY KEY (`workflow_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
+
+CREATE TABLE IF NOT EXISTS `workflow_host` (
+    `host_id`     CHAR(32) NOT NULL, 
+    `workflow_id` CHAR(10) NULL, 
+    `host_name`   CHAR(20) NULL, 
+    `host_ip`     CHAR(16) NULL, 
+    PRIMARY KEY (`host_id`), 
+    KEY `workflow_id_auto_index` (`workflow_id`),
+    CONSTRAINT `FK_workflow_workflow_id` FOREIGN KEY (`workflow_id`) REFERENCES `workflow` (`workflow_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
diff --git a/doc/design/aops-diana数据库设计.sql b/doc/design/aops-diana数据库设计.sql
index 33e05f8..d4452c4 100644
--- a/doc/design/aops-diana数据库设计.sql
+++ b/doc/design/aops-diana数据库设计.sql
@@ -1,3 +1,18 @@
+use aops;
+
+CREATE TABLE IF NOT EXISTS `domain_check_result` (
+    `alert_id`      CHAR(32)   NOT NULL, 
+    `domain`        CHAR(20)   NOT NULL, 
+    `alert_name`    CHAR(50)   NULL, 
+    `time`          INT(11)    NULL, 
+    `workflow_name` CHAR(50)   NULL, 
+    `workflow_id`   CHAR(32)   NULL, 
+    `username`      CHAR(20)   NULL, 
+    `level`         CHAR(20)   NULL, 
+    `confirmed`     TINYINT(1) NULL, 
+    PRIMARY KEY (`alert_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
+
 CREATE TABLE IF NOT EXISTS `alert_host` (
     `alert_id`  CHAR(32) NOT NULL, 
     `host_id`   CHAR(32) NOT NULL, 
@@ -5,7 +20,8 @@ CREATE TABLE IF NOT EXISTS `alert_host` (
     `host_name` CHAR(50) NULL, 
     PRIMARY KEY (`host_id`,`alert_id`), 
     KEY `FK__alert_id_auto_index` (`alert_id`), 
-    KEY `FK_alert_with_host_host_id_auto_index` (`host_id`)
+    KEY `FK_alert_with_host_host_id_auto_index` (`host_id`),
+    CONSTRAINT `FK_alert_host_alert_id` FOREIGN KEY (`alert_id`) REFERENCES `domain_check_result` (`alert_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
 
 CREATE TABLE IF NOT EXISTS `algorithm` (
@@ -18,19 +34,6 @@ CREATE TABLE IF NOT EXISTS `algorithm` (
     PRIMARY KEY (`algo_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
 
-CREATE TABLE IF NOT EXISTS `domain_check_result` (
-    `alert_id`      CHAR(32)   NOT NULL, 
-    `domain`        CHAR(20)   NOT NULL, 
-    `alert_name`    CHAR(50)   NULL, 
-    `time`          INT(11)    NULL, 
-    `workflow_name` CHAR(50)   NULL, 
-    `workflow_id`   CHAR(32)   NULL, 
-    `username`      CHAR(20)   NULL, 
-    `level`         CHAR(20)   NULL, 
-    `confirmed`     TINYINT(1) NULL, 
-    PRIMARY KEY (`alert_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
-
 CREATE TABLE IF NOT EXISTS `host_check_result` (
     `id`           INT(11)    NOT NULL, 
     `time`         INT(11)    NOT NULL, 
@@ -41,7 +44,9 @@ CREATE TABLE IF NOT EXISTS `host_check_result` (
     `metric_label` CHAR(255)  NULL, 
     PRIMARY KEY (`id`), 
     KEY `host_id_auto_index` (`host_id`), 
-    KEY `alert_id_auto_index` (`alert_id`)
+    KEY `alert_id_auto_index` (`alert_id`),
+    CONSTRAINT `FK_host_check_result_host_id` FOREIGN KEY (`host_id`) REFERENCES `alert_host` (`host_id`) ON DELETE RESTRICT ON UPDATE RESTRICT , 
+    CONSTRAINT `FK_host_check_result_alert_id` FOREIGN KEY (`alert_id`) REFERENCES `domain_check_result` (`alert_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
 
 CREATE TABLE IF NOT EXISTS `model` (
@@ -54,7 +59,8 @@ CREATE TABLE IF NOT EXISTS `model` (
     `file_path`   CHAR(64)  NULL, 
     `precision`   DOUBLE    NULL, 
     PRIMARY KEY (`model_id`), 
-    KEY `algo_id_auto_index` (`algo_id`)
+    KEY `algo_id_auto_index` (`algo_id`),
+    CONSTRAINT `FK_model_algo_id` FOREIGN KEY (`algo_id`) REFERENCES `algorithm` (`algo_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
 
 CREATE TABLE IF NOT EXISTS `workflow` (
@@ -77,18 +83,6 @@ CREATE TABLE IF NOT EXISTS `workflow_host` (
     `host_name`   CHAR(20) NULL, 
     `host_ip`     CHAR(16) NULL, 
     PRIMARY KEY (`host_id`), 
-    KEY `workflow_id_auto_index` (`workflow_id`)
+    KEY `workflow_id_auto_index` (`workflow_id`),
+    CONSTRAINT `FK_workflow_workflow_id` FOREIGN KEY (`workflow_id`) REFERENCES `workflow` (`workflow_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC ;
-
-ALTER TABLE alert_host
-ADD CONSTRAINT `FK_alert_with_host_alert_id` FOREIGN KEY (`alert_id`) REFERENCES `domain_check_result` (`alert_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
-
-ALTER TABLE host_check_result
-ADD CONSTRAINT `host_id` FOREIGN KEY (`host_id`) REFERENCES `alert_host` (`host_id`) ON DELETE RESTRICT ON UPDATE RESTRICT , 
-ADD CONSTRAINT `alert_id` FOREIGN KEY (`alert_id`) REFERENCES `domain_check_result` (`alert_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
-
-ALTER TABLE model
-ADD CONSTRAINT `algo_id` FOREIGN KEY (`algo_id`) REFERENCES `algorithm` (`algo_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
-
-ALTER TABLE workflow_host
-ADD CONSTRAINT `workflow_id` FOREIGN KEY (`workflow_id`) REFERENCES `workflow` (`workflow_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
\ No newline at end of file
diff --git a/setup.py b/setup.py
index fe81380..78b696a 100644
--- a/setup.py
+++ b/setup.py
@@ -3,7 +3,7 @@
 from setuptools import setup, find_packages
 
 NAME = "aops-diana"
-VERSION = "1.1.0"
+VERSION = "1.3.0"
 
 # To install the library, run the following
 #
@@ -60,6 +60,7 @@ setup(
         ('/etc/aops/algorithm/intelligent/lvs', ['conf/model/intelligent/lvs/rule1_gala_gopher_cpu_net_rx']),
         ('/etc/aops/algorithm/intelligent/lvs', ['conf/model/intelligent/lvs/rule2_gala_gopher_nic_tc_backlog']),
         ('/usr/lib/systemd/system', ['aops-diana.service']),
+        ("/opt/aops/database", ["database/diana.sql"]),
     ],
     scripts=['aops-diana'],
     zip_safe=False,
-- 
2.33.1.windows.1

