import sqlite3

con = sqlite3.connect('python/5ch.db')

con.execute("""
    CREATE TABLE IF NOT EXISTS threads (
        id INTEGER PRIMARY KEY,
        thread_key TEXT NOT NULL UNIQUE,
        title TEXT,
        url TEXT
    );
""")

con.execute("""
    CREATE TABLE IF NOT EXISTS names (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE

    );
""")

con.execute("""
    CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY,
        thread_id INTEGER NOT NULL,
        post_no   INTEGER NOT NULL,
        posted_at TEXT,
        name_id   INTEGER,
        post_id   TEXT,
        body      TEXT NOT NULL,

        FOREIGN KEY (thread_id) REFERENCES threads(id),
        FOREIGN KEY (name_id) REFERENCES names(id),
        UNIQUE (thread_id, post_no)
    );
""")

con.close()