Spaces:
Sleeping
Sleeping
File size: 2,278 Bytes
22c3c8b b85fb3c 22c3c8b b85fb3c 22c3c8b 701cf99 b85fb3c 0f3029a b85fb3c 6e6e7b3 b85fb3c 1c67908 b85fb3c 4cd385a b85fb3c 4cd385a d4a3aba 4cd385a add112f 4cd385a b85fb3c 927fdad b85fb3c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | import os
import duckdb
from dotenv import load_dotenv
load_dotenv()
# Connect to (or create) a DuckDB database file
# conn = duckdb.connect('kpai_db.db')
conn = duckdb.connect(f"md:?motherduck_token={os.getenv('MOTHERDUCK_TOKEN')}")
# Create the USERS table
conn.execute('''
CREATE TABLE IF NOT EXISTS USERS (
user_id UUID PRIMARY KEY,
username VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
''')
# Create the SESSIONS table
conn.execute('''
CREATE TABLE IF NOT EXISTS SESSIONS (
session_id UUID PRIMARY KEY,
user_id UUID NOT NULL,
session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
session_end TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL '2' HOUR),
FOREIGN KEY(user_id) REFERENCES USERS(user_id)
);
''')
# Create the RESPONSES table
conn.execute('''
CREATE TABLE IF NOT EXISTS RESPONSES (
response_id UUID PRIMARY KEY,
session_id UUID NOT NULL,
user_id UUID NOT NULL,
user_input_text TEXT NOT NULL,
response_text TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(session_id) REFERENCES SESSIONS(session_id),
FOREIGN KEY(user_id) REFERENCES USERS(user_id)
);
''')
# Create the AGENT_RESPONSES table
conn.execute('''
CREATE TABLE IF NOT EXISTS AGENT_RESPONSES (
agent_response_id UUID PRIMARY KEY,
response_id UUID NOT NULL,
agent_name VARCHAR(50) NOT NULL,
user_input TEXT NOT NULL,
agent_response_text TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(response_id) REFERENCES RESPONSES(response_id)
);
''')
# Create the FEEDBACK table
conn.execute('''
CREATE TABLE IF NOT EXISTS FEEDBACK (
feedback_id UUID PRIMARY KEY,
response_id UUID NOT NULL,
user_id UUID NOT NULL,
feedback_score TEXT,
feedback_text TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(response_id) REFERENCES RESPONSES(response_id),
FOREIGN KEY(user_id) REFERENCES USERS(user_id)
);
''')
# Close the connection
conn.close()
print("Database and tables created successfully.")
|