TruckstopDBHelper.py

import os
import sqlite3

class DataBaseObject:
    def __init__(self, sqlConn, sqlCursor):
        self.sqlConn = sqlConn
        self.sqlCursor = sqlCursor

def GetDBConnection(dbName:str):
    dbFilePath = "c:/TruckstopApp"
    os.makedirs(dbFilePath, exist_ok=True)
    sqlConn = sqlite3.connect(f'{dbFilePath}/{dbName}.sqlite')
    sqlCursor = sqlConn.cursor()
    dataBaseObject = DataBaseObject(sqlConn, sqlCursor)
    __CreateTables(dataBaseObject)
    return dataBaseObject

def InsertIntoTable(table:str, dataBaseObject:DataBaseObject, fields:list[str]=None, values:list[str]=None, object:object = None, whereFields:list[str] = None, whereValues:list[str]=None):
    if object is not None:
        fields = list(vars(object).keys())
        values = list(vars(object).values())    
    
    fields_string = ', '.join(fields)
    placeHolders = ', '.join(['?'] * len(values))
    
    try:
        #Insert the data
        sqlQuery = f'INSERT INTO {table} ({fields_string}) values ({placeHolders})'
        dataBaseObject.sqlCursor.execute(sqlQuery, values)
        
        #Log the insert
        loqQuery = f'INSERT INTO CarrierLogs (ClientID, CarrierRMISID, Event, Message) values (?, ?, ?, ?)'
        logData = [object.clientID if object.clientID else None, object.CarrierRMISID if object.CarrierRMISID else None, f"Insert to {table}", f"Inserted {fields_string} with values {values}"]
        dataBaseObject.sqlCursor.execute(loqQuery, logData)
        
        dataBaseObject.sqlConn.commit()
    except Exception as e:
        # If there is already a record for this, update the record instead
        if 'UNIQUE constraint failed' in str(e) and table != "Documents":
            # Create the set clause dynamically for the UPDATE query
            update_fields = ', '.join([f"{field}=?" for field in fields])

            if whereFields is not None:
                where_clause = " AND ".join([f"{whereField}=?" for whereField in whereFields])
            else:
                where_clause = ""
            # Construct the UPDATE query with WHERE conditions for ClientID and CarrierRMISID
            sqlQuery = f'''UPDATE {table} SET {update_fields} WHERE {where_clause}'''
    
            update_values = values + (whereValues if whereValues is not None else [])
            #Insert the data            
            dataBaseObject.sqlCursor.execute(sqlQuery, update_values)
            
            # Log the update
            loqQuery = f'INSERT INTO CarrierLogs (ClientID, Event, Message) values (?, ?, ?)'
            logData = [object.clientID, f"Update {table}", f"Updated {fields_string} with values {values}"]
            dataBaseObject.sqlCursor.execute(loqQuery, logData)
            dataBaseObject.sqlConn.commit()
        else:
            print (str(e))

def __CreateTables(dataBaseObject:DataBaseObject):
    dataBaseObject.sqlCursor.execute('''CREATE TABLE IF NOT EXISTS Documents (
                      ID INTEGER PRIMARY KEY, 
                      ClientID varchar(5), 
                      CarrierRMISID varchar(10), 
                      DocumentID varchar(10), 
                      DocumentType varchar(50), 
                      DocumentSizeInBytes varchar(50), 
                      DocumentTitle varchar(255),
                      DocumentDate varchar(255),
                      DocumentFileType varchar(10),
                      DocumentData blob,
                      UNIQUE(ClientID, DocumentID))
                    ''')
    
    dataBaseObject.sqlCursor.execute('''CREATE TABLE IF NOT EXISTS CarrierLogs (
                      ID INTEGER PRIMARY KEY, 
                      ClientID varchar(5),
                      CarrierRMISID varchar(10), 
                      Event varchar(50),
                      Message TEXT,
                      Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)
                    ''')
    
    dataBaseObject.sqlCursor.execute('''CREATE TABLE IF NOT EXISTS Carriers (
                      ID INTEGER PRIMARY KEY, 
                      ClientID varchar(5), 
                      CarrierName varchar(255), 
                      CarrierMC varchar(10), 
                      CarrierDOT varchar(50), 
                      CarrierRMISID varchar(10),
                      LastUpdated varchar(50),
                      UNIQUE(ClientID, CarrierRMISID))
                    ''')