59 lines
1.5 KiB
Python
59 lines
1.5 KiB
Python
import sqlite3
|
|
|
|
DB_NAME = "ats_doku.db"
|
|
|
|
def initialize_db():
|
|
connection = sqlite3.connect(DB_NAME)
|
|
cursor = connection.cursor()
|
|
|
|
cursor.execute("PRAGMA foreign_keys = ON")
|
|
|
|
# --- Tabelle 1: BA Träger ---
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS ba_wearer (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE,
|
|
code TEXT UNIQUE
|
|
)
|
|
''')
|
|
|
|
# --- Tabelle 2: BA Geräte (muss VOR entrys erstellt werden!) ---
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS ba_devices (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE,
|
|
code TEXT UNIQUE
|
|
)
|
|
''')
|
|
|
|
# --- Tabelle 3: Das Logbuch ---
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS entrys (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
location TEXT,
|
|
name TEXT,
|
|
date TEXT,
|
|
time INTEGER,
|
|
etype TEXT,
|
|
device_id INTEGER,
|
|
wearer_id INTEGER,
|
|
FOREIGN KEY (wearer_id) REFERENCES ba_wearer(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (device_id) REFERENCES ba_devices(id) ON DELETE SET NULL
|
|
)
|
|
''')
|
|
|
|
# Optional: Ein paar Test-ats anlegen, falls die Tabelle leer ist
|
|
cursor.execute("SELECT count(*) FROM ats")
|
|
if cursor.fetchone()[0] == 0:
|
|
ats_liste = [("Tim Grubmüller",), ("Phil Langer",), ("Max Hämmerle",)]
|
|
cursor.executemany("INSERT INTO ats (name) VALUES (?)", ats_liste)
|
|
|
|
connection.commit()
|
|
connection.close()
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|