import sys
import os
import pyodbc as driver
from data_handler import DataHandler
from defs import *


class DBHandler(DataHandler):

    def create_tables(self):
        """ executes the create tables query
        """
        try:
            with driver.connect(CONNECTION_SETTINGS) as conn:
                conn.autocommit = True
                cur = conn.cursor()
                cur.execute(CREATE_QUERY)
                cur.close()
                conn.close()
        except SystemError:
            raise User_Exit_Interrupt

    def insert(self, vul_entry_dicts: list, string_entry_dicts: list):
        """ inserts lists of vulnerability and string entries

        Args:
            vul_entry_dicts (list): list of entries to add to vulnerability db
            string_entry_dicts (list): list of entries to add to strings db
        """
        try:
            with driver.connect(CONNECTION_SETTINGS) as conn:
                conn.autocommit = False
                cur = conn.cursor()
                self._execute_inserts(
                    vul_entry_dicts, VULNERABILITY_TABLE_NAME, cur)
                self._execute_inserts(string_entry_dicts,
                                      STRINGS_TABLE_NAME, cur)
        except SystemError:
            raise User_Exit_Interrupt
        else:
            conn.commit()

    def should_scan_library(self, version: str, package_name: str, package_manager: str):
        """checks if a library was already scanned by the checker

        Args:
            version (str): package version
            package_name (str): package name
            package_manager (str): package manager
        """
        try:
            query = f"""
            SELECT * FROM {VULNERABILITY_TABLE_NAME} 
            WHERE {VERSION} = '{version}' 
            AND {PACKAGE} = '{package_name}' 
            AND {PACKAGE_MANAGER} = '{package_manager}'
            """
            result = self._execute_select(query)
            return len(result) == 0
        except KeyboardInterrupt:
            raise User_Exit_Interrupt

    def should_check(self, path: str, version: str, package_name: str, package_manager: str):
        """checks if a file was already scanned by the checker

        Args:
            path (str): path of file to be checked
            version (str): package version
            package_name (str): package name
            package_manager (str): package manager
        """

        try:
            query = f"""
            SELECT * FROM {VULNERABILITY_TABLE_NAME} 
            WHERE {FULL_PATH} = '{path}' 
            AND {VERSION} = '{version}' 
            AND {PACKAGE} = '{package_name}' 
            AND {PACKAGE_MANAGER} = '{package_manager}'
            """
            result = self._execute_select(query)
            return len(result) == 0
        except KeyboardInterrupt as identifier:
            raise User_Exit_Interrupt

    def _build_insert_query(self, entry_dict: dict, table_name):
        """ recieves dictionariy that represents entries in a table and creates
            an insert query based on the entry. The keys of the entry MUST be written 
            exactly as the respective column in the table

        Args:
            entry_dict (dict): dictionary that represents a db entry
            table_name ([type]): name of table to insert
        """

        column_string = ','.join(entry_dict.keys())
        q_mark_string = '?,'*len(entry_dict)
        q_mark_string = q_mark_string[:-1]
        query = f"""INSERT INTO [dbo].[{table_name}] ({column_string})
                    VALUES ({q_mark_string})
                """
        params = list(entry_dict.values())
        return query, params

    def _execute_inserts(self, entries: list, table_name: str, cursor):
        """iterates over list of entries and executes insert for each entry

        Args:
            entries (list): list of entries (dictionaries) to insert
            table_name (str): name of table to insert to
            cursor ([type]): the cursor used to insert
        """
        for entry in entries:
            try:
                query = self._build_insert_query(entry, table_name)
                cursor.execute(*query)
            except SystemError:
                raise User_Exit_Interrupt
            except driver.DatabaseError as err:
                print(err)
                print(f'problematic entry is {str(entry)}')

    def _execute_select(self, query: str):
        """ executes the given select query and returns the corresponding rows

        Args:
            query (str): the "select" query to be executed
        """
        try:
            with driver.connect(CONNECTION_SETTINGS) as conn:
                conn.autocommit = True
                cur = conn.cursor()
                cur.execute(query)
                rows = cur.fetchall()
                cur.close()
                conn.close()
                return rows
        except SystemError:
            raise User_Exit_Interrupt

import sys
import os
import pyodbc as driver
from data_handler import DataHandler
from defs import *


class DBHandler(DataHandler):

    def create_tables(self):
        """ executes the create tables query
        """
        try:
            with driver.connect(CONNECTION_SETTINGS) as conn:
                conn.autocommit = True
                cur = conn.cursor()
                cur.execute(CREATE_QUERY)
                cur.close()
                conn.close()
        except SystemError:
            raise User_Exit_Interrupt

    def insert(self, vul_entry_dicts: list, string_entry_dicts: list):
        """ inserts lists of vulnerability and string entries

        Args:
            vul_entry_dicts (list): list of entries to add to vulnerability db
            string_entry_dicts (list): list of entries to add to strings db
        """
        try:
            with driver.connect(CONNECTION_SETTINGS) as conn:
                conn.autocommit = False
                cur = conn.cursor()
                self._execute_inserts(
                    vul_entry_dicts, VULNERABILITY_TABLE_NAME, cur)
                self._execute_inserts(string_entry_dicts,
                                      STRINGS_TABLE_NAME, cur)
        except SystemError:
            raise User_Exit_Interrupt
        else:
            conn.commit()

    def should_scan_library(self, version: str, package_name: str, package_manager: str):
        """checks if a library was already scanned by the checker

        Args:
            version (str): package version
            package_name (str): package name
            package_manager (str): package manager
        """
        try:
            query = f"""
            SELECT * FROM {VULNERABILITY_TABLE_NAME} 
            WHERE {VERSION} = '{version}' 
            AND {PACKAGE} = '{package_name}' 
            AND {PACKAGE_MANAGER} = '{package_manager}'
            """
            result = self._execute_select(query)
            return len(result) == 0
        except KeyboardInterrupt:
            raise User_Exit_Interrupt

    def should_check(self, path: str, version: str, package_name: str, package_manager: str):
        """checks if a file was already scanned by the checker

        Args:
            path (str): path of file to be checked
            version (str): package version
            package_name (str): package name
            package_manager (str): package manager
        """

        try:
            query = f"""
            SELECT * FROM {VULNERABILITY_TABLE_NAME} 
            WHERE {FULL_PATH} = '{path}' 
            AND {VERSION} = '{version}' 
            AND {PACKAGE} = '{package_name}' 
            AND {PACKAGE_MANAGER} = '{package_manager}'
            """
            result = self._execute_select(query)
            return len(result) == 0
        except KeyboardInterrupt as identifier:
            raise User_Exit_Interrupt

    def _build_insert_query(self, entry_dict: dict, table_name):
        """ recieves dictionariy that represents entries in a table and creates
            an insert query based on the entry. The keys of the entry MUST be written 
            exactly as the respective column in the table

        Args:
            entry_dict (dict): dictionary that represents a db entry
            table_name ([type]): name of table to insert
        """

        column_string = ','.join(entry_dict.keys())
        q_mark_string = '?,'*len(entry_dict)
        q_mark_string = q_mark_string[:-1]
        query = f"""INSERT INTO [dbo].[{table_name}] ({column_string})
                    VALUES ({q_mark_string})
                """
        params = list(entry_dict.values())
        return query, params

    def _execute_inserts(self, entries: list, table_name: str, cursor):
        """iterates over list of entries and executes insert for each entry

        Args:
            entries (list): list of entries (dictionaries) to insert
            table_name (str): name of table to insert to
            cursor ([type]): the cursor used to insert
        """
        for entry in entries:
            try:
                query = self._build_insert_query(entry, table_name)
                cursor.execute(*query)
            except SystemError:
                raise User_Exit_Interrupt
            except driver.DatabaseError as err:
                print(err)
                print(f'problematic entry is {str(entry)}')

    def _execute_select(self, query: str):
        """ executes the given select query and returns the corresponding rows

        Args:
            query (str): the "select" query to be executed
        """
        try:
            with driver.connect(CONNECTION_SETTINGS) as conn:
                conn.autocommit = True
                cur = conn.cursor()
                cur.execute(query)
                rows = cur.fetchall()
                cur.close()
                conn.close()
                return rows
        except SystemError:
            raise User_Exit_Interrupt

executes the create tables query inserts lists of vulnerability and string entries          Args:             vul_entry_dicts (list): list of entries to add to vulnerability db             string_entry_dicts (list): list of entries to add to strings db checks if a library was already scanned by the checker          Args:             version (str): package version             package_name (str): package name             package_manager (str): package manager SELECT * FROM {VULNERABILITY_TABLE_NAME}              WHERE {VERSION} = '{version}'              AND {PACKAGE} = '{package_name}'              AND {PACKAGE_MANAGER} = '{package_manager}' checks if a file was already scanned by the checker          Args:             path (str): path of file to be checked             version (str): package version             package_name (str): package name             package_manager (str): package manager SELECT * FROM {VULNERABILITY_TABLE_NAME}              WHERE {FULL_PATH} = '{path}'              AND {VERSION} = '{version}'              AND {PACKAGE} = '{package_name}'              AND {PACKAGE_MANAGER} = '{package_manager}' recieves dictionariy that represents entries in a table and creates             an insert query based on the entry. The keys of the entry MUST be written              exactly as the respective column in the table          Args:             entry_dict (dict): dictionary that represents a db entry             table_name ([type]): name of table to insert INSERT INTO [dbo].[{table_name}] ({column_string})                     VALUES ({q_mark_string}) iterates over list of entries and executes insert for each entry          Args:             entries (list): list of entries (dictionaries) to insert             table_name (str): name of table to insert to             cursor ([type]): the cursor used to insert executes the given select query and returns the corresponding rows          Args:             query (str): the "select" query to be executed