52 lines
2.7 KiB
SQL
52 lines
2.7 KiB
SQL
CREATE TABLE IF NOT EXISTS visu_csv_export_jobs (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
project_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
selection_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
name VARCHAR(190) NOT NULL,
|
|
export_kind ENUM('trend_csv', 'lastvalues_csv') NOT NULL DEFAULT 'trend_csv',
|
|
csv_delimiter VARCHAR(4) NOT NULL DEFAULT ';',
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
schedule_kind ENUM('interval', 'daily', 'weekly', 'monthly') NOT NULL DEFAULT 'daily',
|
|
interval_minutes INT UNSIGNED NOT NULL DEFAULT 60,
|
|
time_of_day CHAR(5) NOT NULL DEFAULT '00:00',
|
|
weekdays VARCHAR(32) NOT NULL DEFAULT '',
|
|
month_day TINYINT UNSIGNED NOT NULL DEFAULT 1,
|
|
target_dir VARCHAR(255) NOT NULL DEFAULT 'csv_exports',
|
|
filename_pattern VARCHAR(255) NOT NULL DEFAULT '{selection}_{yyyy}-{mm}-{dd}_{hh}-{ii}-{ss}.csv',
|
|
retention_days INT UNSIGNED NOT NULL DEFAULT 30,
|
|
last_run_at DATETIME NULL DEFAULT NULL,
|
|
next_run_at DATETIME NULL DEFAULT NULL,
|
|
last_status ENUM('idle', 'running', 'success', 'error') NOT NULL DEFAULT 'idle',
|
|
last_error TEXT NULL DEFAULT NULL,
|
|
created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
KEY idx_visu_csv_export_jobs_selection (selection_id),
|
|
KEY idx_visu_csv_export_jobs_project (project_id),
|
|
KEY idx_visu_csv_export_jobs_enabled_next_run (enabled, next_run_at),
|
|
KEY idx_visu_csv_export_jobs_status (last_status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS visu_csv_export_files (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
job_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
project_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
selection_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
status ENUM('queued', 'running', 'success', 'error', 'deleted') NOT NULL DEFAULT 'queued',
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_path VARCHAR(500) NOT NULL,
|
|
file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
row_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
export_started_at DATETIME NULL DEFAULT NULL,
|
|
export_finished_at DATETIME NULL DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
error_message TEXT NULL DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
KEY idx_visu_csv_export_files_job (job_id),
|
|
KEY idx_visu_csv_export_files_selection (selection_id),
|
|
KEY idx_visu_csv_export_files_project (project_id),
|
|
KEY idx_visu_csv_export_files_status (status),
|
|
KEY idx_visu_csv_export_files_created_at (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|