clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805509 fiddles created (40742 in the last week).

-- Configurations CREATE TABLE config ( id INT PRIMARY KEY, name VARCHAR(100), current_revision_id INT ); -- Have multiple revisions CREATE TABLE revision ( id INT PRIMARY KEY, config_id INT NOT NULL REFERENCES config(id), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, description VARCHAR, foo INT NOT NULL, bar BOOLEAN NOT NULL, deployed BOOLEAN NOT NULL DEFAULT FALSE ); -- A configuration has one _current_ revision ALTER TABLE config ADD CONSTRAINT current_revision_id_fk FOREIGN KEY (current_revision_id) REFERENCES revision(id); -- Revisions are automatically numbered in a view CREATE VIEW numbered_revision AS ( SELECT *, row_number() OVER ( PARTITION BY config_id ORDER BY created_at, id ) AS number FROM revision ); -- Configurations have multiple 'public names' CREATE TABLE public_name ( id INT PRIMARY KEY, revision_id INT NOT NULL REFERENCES revision(id), name VARCHAR(100), UNIQUE (revision_id, name) );
 hidden batch(es)


INSERT INTO config (id, name) VALUES (17, 'config_foo'), (42, 'config_bar'); INSERT INTO revision (id, config_id, created_at, description, foo, bar) VALUES (11, 17, '2021-05-29 09:07:18', 'Foo configuration, first draft', 81, TRUE), (19, 17, '2021-05-29 10:42:17', 'Foo configuration, second draft', 73, TRUE), (23, 42, '2021-05-29 09:36:52', 'Bar configuration, first draft', 118, FALSE); INSERT INTO public_name (id, revision_id, name) VALUES -- public names for foo configuration, first draft (83, 11, 'some.name'), (84, 11, 'other.name'), -- public names for foo configuration, second draft (85, 19, 'revised.name'), (86, 19, 'other.name'), (87, 19, 'third.name'), -- public names for bar configuration, first draft; -- some of the names here are the same used by foo configurations (88, 23, 'some.name'), (89, 23, 'unique.name'), (90, 23, 'other.name'); -- Foo configuration has a current, published revision: UPDATE config SET current_revision_id = 19 WHERE id = 17; UPDATE revision SET deployed = TRUE WHERE id in (11, 19);
2 rows affected
3 rows affected
8 rows affected
1 rows affected
2 rows affected
 hidden batch(es)


SELECT c.name AS config, rev.number AS revision, rev.deployed, CASE WHEN c.current_revision_id = rev.id THEN 'ACTIVE' ELSE '' END AS status, string_agg(p.name, ', ' ORDER BY p.name) AS names FROM config c JOIN numbered_revision AS rev ON c.id = rev.config_id JOIN public_name p ON p.revision_id = rev.id GROUP BY c.id, rev.id, rev.number , rev.deployed ORDER BY c.id, rev.number;
config revision deployed status names
config_foo 1 t other.name, some.name
config_foo 2 t ACTIVE other.name, revised.name, third.name
config_bar 1 f other.name, some.name, unique.name
 hidden batch(es)