Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
# Copyright 2021-2023 H2020 TeraFlow (https://www.teraflow-h2020.eu/)
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
import sqlite3 as sl
class ManagementDB():
def __init__(self, database):
self.client = sl.connect(database, check_same_thread=False)
self.create_monitoring_table()
self.create_subscription_table()
self.create_alarm_table()
def create_monitoring_table(self):
self.client.execute("""
CREATE TABLE IF NOT EXISTS kpi(
kpi_id INTEGER PRIMARY KEY AUTOINCREMENT,
kpi_description TEXT,
kpi_sample_type INTEGER,
device_id INTEGER,
endpoint_id INTEGER,
service_id INTEGER
);
""")
def create_subscription_table(self):
self.client.execute("""
CREATE TABLE IF NOT EXISTS subscription(
subs_id INTEGER PRIMARY KEY AUTOINCREMENT,
kpi_id INTEGER,
subscriber TEXT,
sampling_duration_s REAL,
sampling_interval_s REAL,
start_timestamp REAL,
end_timestamp REAL
);
""")
def create_alarm_table(self):
self.client.execute("""
CREATE TABLE IF NOT EXISTS alarm(
alarm_id INTEGER PRIMARY KEY AUTOINCREMENT,
alarm_description TEXT,
alarm_name TEXT,
kpi_id INTEGER,
kpi_min_value REAL,
kpi_max_value REAL,
in_range INTEGER,
include_min_value INTEGER,
include_max_value INTEGER
);
""")
def insert_KPI(self,kpi_description,kpi_sample_type,device_id,endpoint_id,service_id):
c = self.client.cursor()
c.execute("SELECT kpi_id FROM kpi WHERE device_id is ? AND kpi_sample_type is ? AND endpoint_id is ? AND service_id is ?",(device_id,kpi_sample_type,endpoint_id,service_id))
data=c.fetchone()
if data is None:
c.execute("INSERT INTO kpi (kpi_description,kpi_sample_type,device_id,endpoint_id,service_id) VALUES (?,?,?,?,?)", (kpi_description,kpi_sample_type,device_id,endpoint_id,service_id))
self.client.commit()
return c.lastrowid
else:
return data[0]
def insert_subscription(self,kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp):
c = self.client.cursor()
c.execute("SELECT subs_id FROM subscription WHERE kpi_id is ? AND subscriber is ? AND sampling_duration_s is ? AND sampling_interval_s is ? AND start_timestamp is ? AND end_timestamp is ?",(kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp))
data=c.fetchone()
if data is None:
c.execute("INSERT INTO subscription (kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp) VALUES (?,?,?,?,?,?)", (kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp))
self.client.commit()
return c.lastrowid
else:
print("already exists")
return data[0]
def insert_alarm(self,alarm_description,alarm_name,kpi_id,kpi_min_value,kpi_max_value,in_range,include_min_value,include_max_value):
c = self.client.cursor()
c.execute("SELECT alarm_id FROM alarm WHERE alarm_description is ? AND alarm_name is ? AND kpi_id is ? AND kpi_min_value is ? AND kpi_max_value is ? AND in_range is ? AND include_min_value is ? AND include_max_value is ?",(alarm_description,alarm_name,kpi_id,kpi_min_value,kpi_max_value,in_range,include_min_value,include_max_value))
data=c.fetchone()
if data is None:
c.execute("INSERT INTO alarm (alarm_description, alarm_name, kpi_id, kpi_min_value, kpi_max_value, in_range, include_min_value, include_max_value) VALUES (?,?,?,?,?,?,?,?)", (alarm_description,alarm_name,kpi_id,kpi_min_value,kpi_max_value,in_range,include_min_value,include_max_value))
self.client.commit()
return c.lastrowid
else:
print("already exists")
return data[0]
def delete_KPI(self,kpi_id):
c = self.client.cursor()
c.execute("SELECT * FROM kpi WHERE kpi_id is ?",(kpi_id,))
data=c.fetchone()
if data is None:
return False
else:
c.execute("DELETE FROM kpi WHERE kpi_id is ?",(kpi_id,))
self.client.commit()
return True
def delete_subscription(self,subs_id):
c = self.client.cursor()
c.execute("SELECT * FROM subscription WHERE subs_id is ?",(subs_id,))
data=c.fetchone()
if data is None:
return False
else:
c.execute("DELETE FROM subscription WHERE subs_id is ?",(subs_id,))
self.client.commit()
return True
def delete_alarm(self,alarm_id):
c = self.client.cursor()
c.execute("SELECT * FROM alarm WHERE alarm_id is ?",(alarm_id,))
data=c.fetchone()
if data is None:
return False
else:
c.execute("DELETE FROM alarm WHERE alarm_id is ?",(alarm_id,))
self.client.commit()
return True
def get_KPI(self,kpi_id):
data = self.client.execute("SELECT * FROM kpi WHERE kpi_id is ?",(kpi_id,))
return data.fetchone()
def get_subscription(self,subs_id):
data = self.client.execute("SELECT * FROM subscription WHERE subs_id is ?",(subs_id,))
return data.fetchone()
def get_alarm(self,alarm_id):
data = self.client.execute("SELECT * FROM alarm WHERE alarm_id is ?",(alarm_id,))
return data.fetchone()
def get_KPIS(self):
data = self.client.execute("SELECT * FROM kpi")
return data.fetchall()
def get_subscriptions(self):
data = self.client.execute("SELECT * FROM subscription")
return data.fetchall()
def get_alarms(self):
data = self.client.execute("SELECT * FROM alarm")
return data.fetchall()