| import sqlite3 |
| from pathlib import Path |
|
|
|
|
| PROJECT_DIR = Path(__file__).resolve().parent |
| DATA_DIR = PROJECT_DIR / "data" |
| DATABASE_PATH = DATA_DIR / "bookstore.db" |
|
|
|
|
| def get_connection() -> sqlite3.Connection: |
| """ |
| SQLite veritabanı bağlantısı oluşturur. |
| |
| row_factory sayesinde sorgu sonuçlarına hem indeksle |
| hem de kolon adıyla erişilebilir. |
| """ |
|
|
| DATA_DIR.mkdir(parents=True, exist_ok=True) |
|
|
| connection = sqlite3.connect(DATABASE_PATH) |
| connection.row_factory = sqlite3.Row |
|
|
| |
| connection.execute("PRAGMA foreign_keys = ON") |
|
|
| return connection |
|
|
|
|
| def initialize_database() -> None: |
| """Kitap ve sipariş tablolarını oluşturur.""" |
|
|
| with get_connection() as connection: |
| connection.executescript( |
| """ |
| CREATE TABLE IF NOT EXISTS books ( |
| id INTEGER PRIMARY KEY AUTOINCREMENT, |
| title TEXT NOT NULL, |
| author TEXT NOT NULL, |
| category TEXT NOT NULL, |
| price REAL NOT NULL CHECK (price >= 0), |
| stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0), |
| created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| |
| UNIQUE(title, author) |
| ); |
| |
| CREATE TABLE IF NOT EXISTS orders ( |
| id INTEGER PRIMARY KEY AUTOINCREMENT, |
| customer_name TEXT NOT NULL, |
| book_id INTEGER NOT NULL, |
| quantity INTEGER NOT NULL CHECK (quantity > 0), |
| unit_price REAL NOT NULL CHECK (unit_price >= 0), |
| total_price REAL NOT NULL CHECK (total_price >= 0), |
| status TEXT NOT NULL DEFAULT 'Hazırlanıyor', |
| created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| |
| FOREIGN KEY (book_id) |
| REFERENCES books(id) |
| ON DELETE RESTRICT |
| ); |
| |
| CREATE INDEX IF NOT EXISTS idx_books_title |
| ON books(title); |
| |
| CREATE INDEX IF NOT EXISTS idx_books_author |
| ON books(author); |
| |
| CREATE INDEX IF NOT EXISTS idx_books_category |
| ON books(category); |
| |
| CREATE INDEX IF NOT EXISTS idx_orders_customer_name |
| ON orders(customer_name); |
| """ |
| ) |
|
|
| connection.commit() |
|
|
|
|
| if __name__ == "__main__": |
| initialize_database() |
|
|
| print("Veritabanı başarıyla oluşturuldu.") |
| print(f"Veritabanı yolu: {DATABASE_PATH}") |
