mirror of
https://github.com/immich-app/immich.git
synced 2026-03-23 12:49:48 +03:00
41f013387f
add schemas sync variants formatting initial implementation use existing db, wip move to separate folder fix table definitions wip wiring it up
79 lines
3.3 KiB
Plaintext
79 lines
3.3 KiB
Plaintext
CREATE TABLE upload_tasks
|
|
(
|
|
id integer primary key autoincrement,
|
|
attempts integer not null,
|
|
created_at integer not null,
|
|
file_path text,
|
|
is_live_photo integer,
|
|
last_error integer,
|
|
live_photo_video_id text,
|
|
local_id text not null,
|
|
method integer not null,
|
|
priority real not null,
|
|
retry_after integer,
|
|
status integer not null
|
|
);
|
|
|
|
CREATE TABLE upload_task_stats
|
|
(
|
|
pending_downloads integer not null,
|
|
pending_uploads integer not null,
|
|
queued_downloads integer not null,
|
|
queued_uploads integer not null,
|
|
failed_downloads integer not null,
|
|
failed_uploads integer not null,
|
|
completed_uploads integer not null,
|
|
skipped_uploads integer not null
|
|
);
|
|
|
|
CREATE TRIGGER update_stats_insert
|
|
BEFORE INSERT
|
|
ON upload_tasks
|
|
BEGIN
|
|
UPDATE upload_task_stats
|
|
SET pending_downloads = pending_downloads + (NEW.status = 0),
|
|
queued_downloads = queued_downloads + (NEW.status = 1),
|
|
failed_downloads = failed_downloads + (NEW.status = 2),
|
|
pending_uploads = pending_uploads + (NEW.status = 3),
|
|
queued_uploads = queued_uploads + (NEW.status = 4),
|
|
failed_uploads = failed_uploads + (NEW.status = 5),
|
|
completed_uploads = completed_uploads + (NEW.status = 6),
|
|
skipped_uploads = skipped_uploads + (NEW.status = 7);
|
|
END;
|
|
|
|
CREATE TRIGGER update_stats_update
|
|
BEFORE UPDATE OF status
|
|
ON upload_tasks
|
|
WHEN OLD.status != NEW.status
|
|
BEGIN
|
|
UPDATE upload_task_stats
|
|
SET pending_downloads = pending_downloads - (OLD.status = 0) + (NEW.status = 0),
|
|
queued_downloads = queued_downloads - (OLD.status = 1) + (NEW.status = 1),
|
|
failed_downloads = failed_downloads - (OLD.status = 2) + (NEW.status = 2),
|
|
pending_uploads = pending_uploads - (OLD.status = 3) + (NEW.status = 3),
|
|
queued_uploads = queued_uploads - (OLD.status = 4) + (NEW.status = 4),
|
|
failed_uploads = failed_uploads - (OLD.status = 5) + (NEW.status = 5),
|
|
completed_uploads = completed_uploads - (OLD.status = 6) + (NEW.status = 6),
|
|
skipped_uploads = skipped_uploads - (OLD.status = 7) + (NEW.status = 7);
|
|
END;
|
|
|
|
CREATE TRIGGER update_stats_delete
|
|
BEFORE DELETE
|
|
ON upload_tasks
|
|
BEGIN
|
|
UPDATE upload_task_stats
|
|
SET pending_downloads = pending_downloads - (OLD.status = 0),
|
|
queued_downloads = queued_downloads - (OLD.status = 1),
|
|
failed_downloads = failed_downloads - (OLD.status = 2),
|
|
pending_uploads = pending_uploads - (OLD.status = 3),
|
|
queued_uploads = queued_uploads - (OLD.status = 4),
|
|
failed_uploads = failed_uploads - (OLD.status = 5),
|
|
completed_uploads = completed_uploads - (OLD.status = 6),
|
|
skipped_uploads = skipped_uploads - (OLD.status = 7);
|
|
END;
|
|
|
|
CREATE UNIQUE INDEX idx_upload_tasks_local_id ON upload_tasks (local_id, live_photo_video_id);
|
|
CREATE INDEX idx_upload_tasks_asset_data ON upload_tasks (status, priority DESC, created_at);
|
|
|
|
@create: INSERT INTO upload_task_stats VALUES (0, 0, 0, 0, 0, 0, 0, 0);
|