PART 02 — テーブル定義書
カラム定義・データ型・制約・インデックス設計で定義すべきこと

-- テーブル定義書からDDLへの変換例
-- 共通カラムはCREATE TABLE の末尾に必ず追加する

CREATE TABLE m_product (
    -- 主キー（必須）
    product_id   BIGINT GENERATED ALWAYS AS IDENTITY,

    -- 業務カラム
    product_code VARCHAR(20)  NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    category_id  INTEGER      NOT NULL,
    unit_price   NUMERIC(10, 2) NOT NULL DEFAULT 0,
    tax_type     SMALLINT     NOT NULL DEFAULT 10,
    release_date DATE,
    description  TEXT,
    is_public    BOOLEAN      NOT NULL DEFAULT FALSE,

    -- 共通カラム（全テーブル共通）
    delete_flg   BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by   BIGINT       NOT NULL,
    updated_at   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by   BIGINT       NOT NULL,

    -- 制約
    CONSTRAINT pk_m_product           PRIMARY KEY (product_id),
    CONSTRAINT uq_m_product_code      UNIQUE (product_code),
    CONSTRAINT fk_m_product_category  FOREIGN KEY (category_id) REFERENCES m_category (category_id),
    CONSTRAINT chk_m_product_price    CHECK (unit_price >= 0),
    CONSTRAINT chk_m_product_tax      CHECK (tax_type IN (0, 8, 10))
);

-- インデックス
CREATE INDEX idx_m_product_category_id ON m_product (category_id);
CREATE INDEX idx_m_product_delete_flg  ON m_product (delete_flg) WHERE delete_flg = FALSE;

-- updated_at 自動更新トリガー
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_m_product_updated_at
    BEFORE UPDATE ON m_product
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

COMMENT ON TABLE m_product IS '商品マスタ';
COMMENT ON COLUMN m_product.product_code IS '商品コード（英数字・ハイフンのみ）';
COMMENT ON COLUMN m_product.tax_type IS '税区分: 10=標準税率, 8=軽減税率, 0=非課税';

"""
PostgreSQLの既存テーブルからテーブル定義書（Excel）を自動生成するスクリプト。
pip install psycopg2-binary openpyxl
"""
import psycopg2
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side


DB_CONFIG = {"host":"localhost","port":5432,"dbname":"mydb","user":"myuser","password":"mypassword"}
HEADERS = ["No.", "カラム名（物理）", "カラム名（論理）", "型", "桁数", "NULL可", "デフォルト値", "備考（コメント）"]
HEADER_COLOR = "4472C4"


def get_columns(conn, table_name: str, schema: str = "public") -> list[dict]:
    """テーブルのカラム情報を取得"""
    sql = """
    SELECT
        c.ordinal_position                       AS no,
        c.column_name,
        c.data_type,
        c.character_maximum_length,
        c.numeric_precision,
        c.numeric_scale,
        c.is_nullable,
        c.column_default,
        pg_catalog.col_description(
            (quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass,
            c.ordinal_position
        ) AS column_comment
    FROM information_schema.columns c
    WHERE c.table_schema = %s AND c.table_name = %s
    ORDER BY c.ordinal_position;
    """
    with conn.cursor() as cur:
        cur.execute(sql, (schema, table_name))
        rows = cur.fetchall()
    return [
        {
            "no": r[0], "column_name": r[1],
            "data_type": r[2],
            "max_length": r[3], "precision": r[4], "scale": r[5],
            "is_nullable": "可" if r[6] == "YES" else "不可",
            "column_default": r[7] or "",
            "comment": r[8] or "",
        }
        for r in rows
    ]


def write_table_sheet(wb: openpyxl.Workbook, table_name: str, columns: list[dict]) -> None:
    ws = wb.create_sheet(title=table_name[:31])  # シート名は31文字制限

    # ヘッダー行
    thin = Side(style='thin', color="AAAAAA")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    for col_idx, header in enumerate(HEADERS, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill("solid", fgColor=HEADER_COLOR)
        cell.alignment = Alignment(horizontal="center")
        cell.border = border

    # データ行
    for row_idx, col in enumerate(columns, 2):
        # 桁数表示
        if col["max_length"]:
            size = str(col["max_length"])
        elif col["precision"] and col["scale"]:
            size = f"{col['precision']},{col['scale']}"
        elif col["precision"]:
            size = str(col["precision"])
        else:
            size = ""

        values = [
            col["no"], col["column_name"], col["comment"],
            col["data_type"], size, col["is_nullable"],
            col["column_default"], ""
        ]
        for col_idx, value in enumerate(values, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.border = border

    # 列幅調整
    col_widths = [6, 30, 25, 22, 10, 8, 30, 40]
    for i, w in enumerate(col_widths, 1):
        ws.column_dimensions[openpyxl.utils.get_column_letter(i)].width = w


if __name__ == "__main__":
    TABLES = ["m_product", "m_customer", "t_order", "t_order_detail"]

    conn = psycopg2.connect(**DB_CONFIG)
    wb = openpyxl.Workbook()
    wb.remove(wb.active)  # デフォルトシートを削除

    for table in TABLES:
        columns = get_columns(conn, table)
        write_table_sheet(wb, table, columns)
        print(f"{table}: {len(columns)}カラム")

    conn.close()
    wb.save("table_definition.xlsx")
    print("\ntable_definition.xlsx を生成しました")