Data structure for image editor

report-images.sql

CREATE TABLE job (
  id SERIAL PRIMARY KEY,
  alias VARCHAR(20)
);

CREATE TABLE container (
  id SERIAL PRIMARY KEY,
  job INT REFERENCES job(id) ON DELETE CASCADE NOT NULL,
  index INT NOT NULL, -- keep the order of containers
  name VARCHAR(100)
);

CREATE TABLE image (
  id SERIAL PRIMARY KEY,
  container INT REFERENCES container(id) ON DELETE CASCADE NOT NULL,
  index INT NOT NULL,
  name VARCHAR(100)
);

-- insert a job
INSERT INTO job(alias) VALUES ('fist-job'), ('second-job');

-- insert a container into job
INSERT INTO container(job, index, name) VALUES (1, 1, 'Building #1'), (1, 2, 'Building #2');

-- insert images into 'Building #1' container
INSERT INTO image(container, index, name) VALUES (1, 1, 'image-1-1.jpg');
INSERT INTO image(container, index, name) VALUES (1, 2, 'image-1-2.jpg');
INSERT INTO image(container, index, name) VALUES (1, 3, 'image-1-3.jpg');
INSERT INTO image(container, index, name) VALUES (1, 4, 'image-1-4.jpg');

-- insert images into 'Building #2' container
INSERT INTO image(container, index, name) VALUES (2, 1, 'image-2-1.jpg');
INSERT INTO image(container, index, name) VALUES (2, 2, 'image-2-2.jpg');
INSERT INTO image(container, index, name) VALUES (2, 3, 'image-2-3.jpg');
INSERT INTO image(container, index, name) VALUES (2, 4, 'image-2-4.jpg');

-- select all the images of job = 1
SELECT
  container.*,
  json_agg(image.*) as images
FROM container
RIGHT JOIN image
    ON container.id = image.container
WHERE container.job = 1
GROUP BY container.id;

-- reorder images image-2-1.jpg and image-2-3.jpg in the same container
UPDATE image SET
  index = t.index
  FROM (VALUES
    (5, 1), -- image.id, image.index
    (7, 3)
  ) AS t(id, index)
  WHERE image.id = t.id;

-- reorder images between different containers
BEGIN;
  \set imageId 7
  \set imageIndex 3
  \set toIndex 2
  \set fromContainer 2
  \set toContainer 1

  UPDATE image SET
    index = index + 1
  WHERE image.container = :toContainer AND image.index >= :toIndex;

  UPDATE image SET
    container = :toContainer,
    index = :toIndex
  WHERE image.id = :imageId;

  UPDATE image SET
    index = index - 1
  WHERE image.container = :fromContainer AND image.index > :imageIndex;
COMMIT;

-- remove an image
BEGIN;
  \set imageId 7
  \set fromContainer 1
  \set imageIndex 2
  DELETE FROM image WHERE image.id = :imageId;
  UPDATE image SET
    index = index - 1
  WHERE image.container = :fromContainer AND image.index > :imageIndex;
COMMIT;

-- select all the images of job = 1
SELECT
  container.*,
  json_agg(image.*) as images
FROM container
RIGHT JOIN image
    ON container.id = image.container
WHERE container.job = 1
GROUP BY container.id;