makima/migrations/20241224000000_add_file_version.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- Add version column for optimistic locking
ALTER TABLE files ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Create trigger function to increment version on update
CREATE OR REPLACE FUNCTION increment_file_version()
RETURNS TRIGGER AS $$
BEGIN
    NEW.version = OLD.version + 1;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger that fires before each update
CREATE TRIGGER increment_files_version
    BEFORE UPDATE ON files
    FOR EACH ROW
    EXECUTE FUNCTION increment_file_version();