78 lines
3.1 KiB
SQL
78 lines
3.1 KiB
SQL
-- =============================================================================
|
||
-- etl_feiqiu / meta（ETL 调度元数据）
|
||
-- 生成日期：2026-02-23
|
||
-- 来源：测试库（通过脚本自动导出）
|
||
-- =============================================================================
|
||
|
||
CREATE SCHEMA IF NOT EXISTS meta;
|
||
|
||
-- 序列
|
||
CREATE SEQUENCE IF NOT EXISTS meta.etl_cursor_cursor_id_seq AS bigint;
|
||
CREATE SEQUENCE IF NOT EXISTS meta.etl_run_run_id_seq AS bigint;
|
||
CREATE SEQUENCE IF NOT EXISTS meta.etl_task_task_id_seq AS bigint;
|
||
|
||
-- 表
|
||
CREATE TABLE meta.etl_cursor (
|
||
cursor_id bigint DEFAULT nextval('meta.etl_cursor_cursor_id_seq'::regclass) NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
store_id bigint NOT NULL,
|
||
last_start timestamp with time zone,
|
||
last_end timestamp with time zone,
|
||
last_id bigint,
|
||
last_run_id bigint,
|
||
extra jsonb DEFAULT '{}'::jsonb,
|
||
created_at timestamp with time zone DEFAULT now(),
|
||
updated_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE meta.etl_run (
|
||
run_id bigint DEFAULT nextval('meta.etl_run_run_id_seq'::regclass) NOT NULL,
|
||
run_uuid text NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
store_id bigint NOT NULL,
|
||
status text NOT NULL,
|
||
started_at timestamp with time zone DEFAULT now(),
|
||
ended_at timestamp with time zone,
|
||
window_start timestamp with time zone,
|
||
window_end timestamp with time zone,
|
||
window_minutes integer,
|
||
overlap_seconds integer,
|
||
fetched_count integer DEFAULT 0,
|
||
loaded_count integer DEFAULT 0,
|
||
updated_count integer DEFAULT 0,
|
||
skipped_count integer DEFAULT 0,
|
||
error_count integer DEFAULT 0,
|
||
unknown_fields integer DEFAULT 0,
|
||
export_dir text,
|
||
log_path text,
|
||
request_params jsonb DEFAULT '{}'::jsonb,
|
||
manifest jsonb DEFAULT '{}'::jsonb,
|
||
error_message text,
|
||
extra jsonb DEFAULT '{}'::jsonb
|
||
);
|
||
|
||
CREATE TABLE meta.etl_task (
|
||
task_id bigint DEFAULT nextval('meta.etl_task_task_id_seq'::regclass) NOT NULL,
|
||
task_code text NOT NULL,
|
||
store_id bigint NOT NULL,
|
||
enabled boolean DEFAULT true,
|
||
cursor_field text,
|
||
window_minutes_default integer DEFAULT 30,
|
||
overlap_seconds integer DEFAULT 600,
|
||
page_size integer DEFAULT 200,
|
||
retry_max integer DEFAULT 3,
|
||
params jsonb DEFAULT '{}'::jsonb,
|
||
created_at timestamp with time zone DEFAULT now(),
|
||
updated_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
-- 约束（主键 / 唯一 / 外键）
|
||
ALTER TABLE meta.etl_cursor ADD CONSTRAINT etl_cursor_task_id_fkey FOREIGN KEY (task_id) REFERENCES meta.etl_task(task_id) ON DELETE CASCADE;
|
||
ALTER TABLE meta.etl_cursor ADD CONSTRAINT etl_cursor_pkey PRIMARY KEY (cursor_id);
|
||
ALTER TABLE meta.etl_cursor ADD CONSTRAINT etl_cursor_task_id_store_id_key UNIQUE (task_id, store_id);
|
||
ALTER TABLE meta.etl_run ADD CONSTRAINT etl_run_task_id_fkey FOREIGN KEY (task_id) REFERENCES meta.etl_task(task_id) ON DELETE CASCADE;
|
||
ALTER TABLE meta.etl_run ADD CONSTRAINT etl_run_pkey PRIMARY KEY (run_id);
|
||
ALTER TABLE meta.etl_task ADD CONSTRAINT etl_task_pkey PRIMARY KEY (task_id);
|
||
ALTER TABLE meta.etl_task ADD CONSTRAINT etl_task_task_code_store_id_key UNIQUE (task_code, store_id);
|
||
|