byeongwook.choiandClaude Fable 5.1
11ae3629b2
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
57 lines
2.9 KiB
SQL
57 lines
2.9 KiB
SQL
-- 계획서 §6.1 원안 — PostgreSQL 16 + pgvector 전환 시 사용 (ASSUMPTIONS.md §1)
|
|
-- 임베딩 차원(1024)은 모델 확정 후 조정할 것.
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|
|
|
CREATE TABLE IF NOT EXISTS package(
|
|
devclass TEXT PRIMARY KEY, text_ko TEXT, summary_json JSONB, embedding vector(1024)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS program(
|
|
name TEXT PRIMARY KEY, devclass TEXT, title_ko TEXT,
|
|
created_on DATE, changed_on DATE, source_hash TEXT,
|
|
summary_json JSONB, summary_status TEXT DEFAULT 'none', has_source BOOLEAN DEFAULT FALSE,
|
|
embedding vector(1024), fts tsvector
|
|
);
|
|
CREATE TABLE IF NOT EXISTS include(
|
|
program TEXT, include TEXT, code_hash TEXT, line_count INT, code TEXT,
|
|
PRIMARY KEY(program, include)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS unit(
|
|
unit_id TEXT PRIMARY KEY, program TEXT, include TEXT, unit_type TEXT, name TEXT,
|
|
line_start INT, line_end INT, code_hash TEXT, signature TEXT, header_comment TEXT,
|
|
loc INT, refs_json JSONB, sub_chunks_json JSONB,
|
|
summary_json JSONB, summary_status TEXT DEFAULT 'none', prompt_version INT DEFAULT 0,
|
|
embedding vector(1024), fts tsvector
|
|
);
|
|
CREATE TABLE IF NOT EXISTS symbol(
|
|
program TEXT, name TEXT, scope TEXT, unit_id TEXT, decl_include TEXT,
|
|
decl_line INT, type_text TEXT, ddic_ref TEXT, kind TEXT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS symbol_write(
|
|
id BIGSERIAL PRIMARY KEY,
|
|
program TEXT, symbol TEXT, unit_id TEXT, include TEXT, line INT, kind TEXT,
|
|
source_symbols TEXT[], source_tables TEXT[], stmt_text TEXT, callee TEXT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS symbol_read(
|
|
program TEXT, symbol TEXT, unit_id TEXT, include TEXT, line INT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS table_ref(
|
|
program TEXT, unit_id TEXT, table_name TEXT, mode TEXT, line INT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS call_edge(
|
|
program TEXT, from_unit TEXT, to_unit TEXT, external_name TEXT, call_type TEXT, line INT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS topo(program TEXT, unit_id TEXT, ord INT, PRIMARY KEY(program, unit_id));
|
|
CREATE TABLE IF NOT EXISTS ddic_table(name TEXT PRIMARY KEY, text_ko TEXT);
|
|
CREATE TABLE IF NOT EXISTS ddic_field(tabname TEXT, field TEXT, text_ko TEXT, data_element TEXT);
|
|
CREATE TABLE IF NOT EXISTS tcode(tcode TEXT PRIMARY KEY, program TEXT, text_ko TEXT);
|
|
CREATE TABLE IF NOT EXISTS eval_question(id TEXT PRIMARY KEY, question TEXT, expected_programs TEXT[], type TEXT);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_unit_fts ON unit USING GIN(fts);
|
|
CREATE INDEX IF NOT EXISTS idx_program_fts ON program USING GIN(fts);
|
|
CREATE INDEX IF NOT EXISTS idx_unit_emb ON unit USING hnsw(embedding vector_cosine_ops);
|
|
CREATE INDEX IF NOT EXISTS idx_program_emb ON program USING hnsw(embedding vector_cosine_ops);
|
|
CREATE INDEX IF NOT EXISTS idx_symbol_write ON symbol_write(program, symbol);
|
|
CREATE INDEX IF NOT EXISTS idx_table_ref ON table_ref(table_name);
|
|
CREATE INDEX IF NOT EXISTS idx_edge_to ON call_edge(to_unit);
|
|
CREATE INDEX IF NOT EXISTS idx_edge_ext ON call_edge(external_name);
|