astrbot_plugin_imagestudio/database/migrations/0016_advanced_materials.sql

110 行
4 KiB
MySQL

CREATE TABLE IF NOT EXISTS advanced_material_imports (
import_id TEXT PRIMARY KEY,
category_id TEXT,
display_name TEXT NOT NULL,
source_image_uri TEXT NOT NULL,
source_thumbnail_uri TEXT,
status TEXT NOT NULL DEFAULT 'pending_recognition',
recognition_prompt TEXT,
recognition_negative_prompt TEXT,
recognition_tags_json TEXT,
recognition_result_json TEXT,
recognition_model_route TEXT,
prompt_revision INTEGER NOT NULL DEFAULT 0,
current_attempt_id TEXT,
accepted_material_id TEXT,
idempotency_key TEXT,
created_by TEXT,
error_json TEXT,
metadata_json TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(category_id) REFERENCES material_categories(category_id) ON DELETE SET NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_advanced_material_imports_idempotency
ON advanced_material_imports(idempotency_key)
WHERE idempotency_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_advanced_material_imports_status
ON advanced_material_imports(status, updated_at);
CREATE INDEX IF NOT EXISTS idx_advanced_material_imports_category
ON advanced_material_imports(category_id, created_at);
CREATE TABLE IF NOT EXISTS advanced_material_validation_attempts (
attempt_id TEXT PRIMARY KEY,
import_id TEXT NOT NULL,
attempt_number INTEGER NOT NULL,
status TEXT NOT NULL DEFAULT 'queued',
adjustment_text TEXT,
prompt TEXT NOT NULL,
negative_prompt TEXT,
model_route TEXT,
provider_id TEXT,
provider_model TEXT,
task_id TEXT,
validation_image_uri TEXT,
validation_thumbnail_uri TEXT,
seed INTEGER,
width INTEGER,
height INTEGER,
usage_json TEXT,
fidelity_score REAL,
decision TEXT,
decision_notes TEXT,
error_json TEXT,
metadata_json TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed_at TIMESTAMP,
decided_at TIMESTAMP,
FOREIGN KEY(import_id) REFERENCES advanced_material_imports(import_id) ON DELETE CASCADE,
FOREIGN KEY(task_id) REFERENCES generation_tasks(task_id) ON DELETE SET NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_advanced_material_attempts_sequence
ON advanced_material_validation_attempts(import_id, attempt_number);
CREATE INDEX IF NOT EXISTS idx_advanced_material_attempts_status
ON advanced_material_validation_attempts(status, updated_at);
CREATE INDEX IF NOT EXISTS idx_advanced_material_attempts_task
ON advanced_material_validation_attempts(task_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_advanced_material_attempts_one_active
ON advanced_material_validation_attempts(import_id)
WHERE status IN ('queued', 'running');
CREATE TABLE IF NOT EXISTS advanced_materials (
material_id TEXT PRIMARY KEY,
import_id TEXT NOT NULL UNIQUE,
accepted_attempt_id TEXT NOT NULL UNIQUE,
category_id TEXT,
display_name TEXT NOT NULL,
source_image_uri TEXT NOT NULL,
source_thumbnail_uri TEXT,
validation_image_uri TEXT NOT NULL,
validation_thumbnail_uri TEXT,
prompt TEXT NOT NULL,
negative_prompt TEXT,
tags_json TEXT,
priority INTEGER NOT NULL DEFAULT 100,
status TEXT NOT NULL DEFAULT 'active',
capsule_json TEXT,
metadata_json TEXT,
accepted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
archived_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(import_id) REFERENCES advanced_material_imports(import_id) ON DELETE RESTRICT,
FOREIGN KEY(accepted_attempt_id) REFERENCES advanced_material_validation_attempts(attempt_id) ON DELETE RESTRICT,
FOREIGN KEY(category_id) REFERENCES material_categories(category_id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_advanced_materials_catalog
ON advanced_materials(status, priority, accepted_at);
CREATE INDEX IF NOT EXISTS idx_advanced_materials_category
ON advanced_materials(category_id, status, priority, accepted_at);