docker/init.sql - master - DaniilVinichenko/SberVersionControll

docker/init.sql
73 строки3 KB
GrishaRubitel
Build before DB reconfig
08 апр 2025, 00:31
08 апр 2025, 00:31aaea884
CREATE TABLE public.software (
	id serial4 NOT NULL,
	"name" varchar NULL,
	description varchar NULL,
	"owner" varchar NULL,
	file_server_path varchar NOT NULL,
	CONSTRAINT software_pk PRIMARY KEY (id),
	CONSTRAINT software_unique UNIQUE (file_server_path)
);

CREATE TABLE public.event_types (
	event_type varchar NOT NULL,
	CONSTRAINT event_types_pk PRIMARY KEY (event_type)
);

CREATE TABLE public."version" (
	id serial4 NOT NULL,
	software_id int4 NULL,
	version_number int4 NULL,
	released_at timestamp NULL,
	changes text NULL,
	CONSTRAINT version_pk PRIMARY KEY (id)
);

ALTER TABLE public."version" ADD CONSTRAINT version_software_fk FOREIGN KEY (software_id) REFERENCES public.software(id) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE public.events (
	id serial4 NOT NULL,
	entity_id int4 NULL,
	event_type varchar NULL,
	payload text NULL,
	created_at timestamp NULL,
	CONSTRAINT events_pk PRIMARY KEY (id)
);

ALTER TABLE public.events ADD CONSTRAINT events_event_types_fk FOREIGN KEY (event_type) REFERENCES public.event_types(event_type) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE public.events ADD CONSTRAINT events_software_fk FOREIGN KEY (entity_id) REFERENCES public.software(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.events ADD CONSTRAINT events_version_fk FOREIGN KEY (entity_id) REFERENCES public."version"(id) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO public.event_types (event_type) VALUES
('SOFTWARE_CREATED'),
('SOFTWARE_UPDATED'),
('SOFTWARE_DELETED'),
('VERSION_ADDED'),
('VERSION_UPDATED'),
('VERSION_DELETED'),
('VERSION_ROLLBACK'),
('SOFTWARE_DOWNLOADED');

INSERT INTO public.software ("name", description, "owner", file_server_path) VALUES
('MyApp', 'Описание первого ПО', 'John Doe', '/software/myapp'),
('CoolApp', 'Описание второго ПО', 'Jane Doe', '/software/coolapp'),
('OldApp', 'Описание старого ПО', 'Bob Smith', '/software/oldapp');

INSERT INTO public."version" (software_id, version_number, released_at, changes) VALUES
(1, 1, '2024-01-01 10:00:00', 'Initial release'),
(1, 2, '2024-02-01 12:00:00', 'Bug fixes and improvements'),
(2, 1, '2024-03-01 15:00:00', 'First release of CoolApp'),
(3, 1, '2023-12-01 09:00:00', 'Last release of OldApp');

INSERT INTO public.events (entity_id, event_type, payload, created_at) VALUES
(1, 'CREATED', '{"message": "MyApp was created"}', '2024-01-01 10:00:00'),
(1, 'UPDATED', '{"message": "MyApp was updated to version 2"}', '2024-02-01 12:00:00'),
(1, 'RELEASED', '{"version": "2.0", "released_at": "2024-02-01"}', '2024-02-01 12:00:00'),
(1, 'DELETED', '{"message": "MyApp was deleted"}', '2024-03-01 14:00:00');

INSERT INTO public.events (entity_id, event_type, payload, created_at) VALUES
(2, 'CREATED', '{"message": "CoolApp was created"}', '2024-03-01 15:00:00'),
(2, 'RELEASED', '{"version": "1.0", "released_at": "2024-03-01"}', '2024-03-01 15:00:00');

INSERT INTO public.events (entity_id, event_type, payload, created_at) VALUES
(3, 'CREATED', '{"message": "OldApp was created"}', '2023-12-01 09:00:00'),
(3, 'RELEASED', '{"version": "1.0", "released_at": "2023-12-01"}', '2023-12-01 09:00:00');