File size: 2,532 Bytes
58f6928 | 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 79 80 81 82 83 | 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
# SQLite'ta foreign key kontrolleri bağlantı başına açılır.
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}") |