BunnySync/database.py

import sqlite3
from datetime import datetime, timedelta

DB_NAME = 'my_data.db'

def get_db_conn():
    conn = sqlite3.connect(DB_NAME)
    return conn

def create_tables():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS jobs (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        folder1 TEXT NOT NULL,
                        folder2 TEXT NOT NULL,
                        interval INTEGER NOT NULL
                    )''')
    cursor.execute('''CREATE TABLE IF NOT EXISTS logs (
                        id INTEGER PRIMARY KEY,
                        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                        message TEXT NOT NULL
                    )''')
    conn.commit()
    conn.close()

def add_job_to_db(name, folder1, folder2, interval):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('INSERT INTO jobs (name, folder1, folder2, interval) VALUES (?, ?, ?, ?)', (name, folder1, folder2, interval))
    conn.commit()
    conn.close()

def get_jobs_from_db():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM jobs')
    jobs = cursor.fetchall()
    conn.close()
    return [{'name': job[1], 'folder1': job[2], 'folder2': job[3], 'interval': job[4]} for job in jobs]

def update_job_in_db(old_name, new_name, folder1, folder2, interval):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''
        UPDATE jobs SET name = ?, folder1 = ?, folder2 = ?, interval = ? WHERE name = ?
    ''', (new_name, folder1, folder2, interval, old_name))
    if old_name != new_name:
        cursor.execute(f'ALTER TABLE {old_name} RENAME TO {new_name};')
    conn.commit()
    conn.close()

def delete_job_from_db(name):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('DELETE FROM jobs WHERE name = ?', (name,))
    cursor.execute(f"DROP TABLE IF EXISTS {name}")
    conn.commit()
    conn.close()

def add_log_to_db(message):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('INSERT INTO logs (message) VALUES (?)', (message,))
    conn.commit()
    conn.close()

def get_logs_from_db():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    # cursor.execute('SELECT * FROM logs')
    cursor.execute('''
        SELECT * 
        FROM logs 
        ORDER BY timestamp DESC 
        LIMIT 30
    ''')
    logs = cursor.fetchall()
    conn.close()
    return [(log[1], log[2]) for log in logs]

def delete_old_logs():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    one_month_ago = datetime.now() - timedelta(days=30)
    cursor.execute('DELETE FROM logs WHERE timestamp < ?', (one_month_ago,))
    conn.commit()
    conn.close()