database.py

import aiosqlite
import os

DB_PATH = os.path.join(os.path.dirname(__file__), 'sns_api.db')

POSTS_SCHEMA = """
CREATE TABLE IF NOT EXISTS posts (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    content TEXT NOT NULL,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL,
    likes INTEGER NOT NULL DEFAULT 0,
    comments INTEGER NOT NULL DEFAULT 0
);
"""

COMMENTS_SCHEMA = """
CREATE TABLE IF NOT EXISTS comments (
    id TEXT PRIMARY KEY,
    postId TEXT NOT NULL,
    username TEXT NOT NULL,
    content TEXT NOT NULL,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL,
    FOREIGN KEY(postId) REFERENCES posts(id) ON DELETE CASCADE
);
"""

LIKES_SCHEMA = """
CREATE TABLE IF NOT EXISTS likes (
    postId TEXT NOT NULL,
    username TEXT NOT NULL,
    PRIMARY KEY(postId, username),
    FOREIGN KEY(postId) REFERENCES posts(id) ON DELETE CASCADE
);
"""

async def init_db():
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(POSTS_SCHEMA)
        await db.execute(COMMENTS_SCHEMA)
        await db.execute(LIKES_SCHEMA)
        await db.commit()