ManagementDBTools.py

# 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.
from common.tools.timestamp.Converters import timestamp_utcnow_to_float

import sqlite3 as sl
import os
ROOT_DIR = os.path.abspath(os.curdir)

class ManagementDB():
    def __init__(self, database):
        self.client = sl.connect(database, check_same_thread=False)
        self.create_monitoring_table()
        self.create_subscription_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 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 ?",(device_id,kpi_sample_type,endpoint_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 delete_KPI(self,device_id,kpi_sample_type):
        c = self.client.cursor()
        c.execute("SELECT kpi_id FROM kpi WHERE device_id is ? AND kpi_sample_type is ?",(device_id,kpi_sample_type))       
        data=c.fetchone()
        if data is None:
            return False
        else:
            c.execute("DELETE FROM kpi WHERE device_id is ? AND kpi_sample_type is ?",(device_id,kpi_sample_type))
            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_kpid_id(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 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_KPIS(self):
        data = self.client.execute("SELECT * FROM kpi")
        #print("\n")
        #for row in data:
        #    print(row)
        return data.fetchall()

    def get_subscriptions(self):
        data = self.client.execute("SELECT * FROM subscription")
        #print("\n")
        #for row in data:
        #    print(row)
        return data.fetchall()

def main():
    managementdb = ManagementDB("monitoring.db")
    subs_id = managementdb.insert_subscription(2,"localhost",20,2,timestamp_utcnow_to_float(),timestamp_utcnow_to_float() + 10)
    print("subs id: ", str(subs_id))

if __name__ == '__main__':
    main()