#!/usr/bin/env python3
"""
Initialize SQLite database with sample data for MCP demo.
Creates tables and seeds data for the database query tool demo.
"""
import sqlite3
import os
from pathlib import Path
def init_db():
"""Create and seed the SQLite database."""
db_path = Path(__file__).parent / "demo.db"
# Remove existing DB if present (for fresh starts)
if db_path.exists():
os.remove(db_path)
conn = sqlite3.connect(str(db_path))
cursor = conn.cursor()
# Create users table
cursor.execute("""
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
role TEXT DEFAULT 'user',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
# Create products table
cursor.execute("""
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
price REAL NOT NULL,
stock INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
# Create orders table
cursor.execute("""
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
total REAL NOT NULL,
status TEXT DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
)
""")
# Create order_items table
cursor.execute("""
CREATE TABLE order_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price REAL NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
)
""")
# Seed users
users = [
("Alice Johnson", "alice@example.com", "admin"),
("Bob Smith", "bob@example.com", "user"),
("Carol White", "carol@example.com", "user"),
("David Brown", "david@example.com", "user"),
("Eve Davis", "eve@example.com", "moderator"),
]
cursor.executemany(
"INSERT INTO users (name, email, role) VALUES (?, ?, ?)",
users
)
# Seed products
products = [
("Laptop", "High-performance laptop for developers", 1299.99, 15),
("Mechanical Keyboard", "RGB mechanical keyboard, cherry switches", 149.99, 45),
("Monitor", "4K ultra-wide monitor for productivity", 599.99, 8),
("Wireless Mouse", "Ergonomic wireless mouse", 49.99, 120),
("Headphones", "Noise-canceling Bluetooth headphones", 299.99, 22),
("USB-C Hub", "Multi-port USB-C adapter", 79.99, 60),
("External SSD", "1TB portable SSD", 129.99, 35),
("Webcam", "4K web camera for streaming", 199.99, 12),
]
cursor.executemany(
"INSERT INTO products (name, description, price, stock) VALUES (?, ?, ?, ?)",
products
)
# Seed orders
orders = [
(1, 1449.98, "completed"),
(2, 249.98, "completed"),
(3, 1729.97, "pending"),
(1, 349.98, "shipped"),
(4, 179.99, "pending"),
]
cursor.executemany(
"INSERT INTO orders (user_id, total, status) VALUES (?, ?, ?)",
orders
)
# Seed order items
order_items = [
(1, 1, 1, 1299.99), # Order 1: 1x Laptop
(1, 2, 1, 149.99), # Order 1: 1x Keyboard
(2, 3, 1, 599.99), # Order 2: 1x Monitor
(2, 2, 1, 149.99), # Order 2: 1x Keyboard
(3, 1, 1, 1299.99), # Order 3: 1x Laptop
(3, 5, 1, 299.99), # Order 3: 1x Headphones
(3, 7, 1, 129.99), # Order 3: 1x SSD
(4, 4, 2, 99.98), # Order 4: 2x Mouse
(4, 6, 1, 79.99), # Order 4: 1x USB-C Hub
(5, 8, 1, 179.99), # Order 5: 1x Webcam
]
cursor.executemany(
"INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)",
order_items
)
conn.commit()
conn.close()
print(f"✅ Database initialized at: {db_path}")
print("Tables created: users, products, orders, order_items")
print(f"Sample data seeded: 5 users, 8 products, 5 orders")
if __name__ == "__main__":
init_db()
