diff options
Diffstat (limited to 'opendc-web/opendc-web-server/src/main/resources/db')
| -rw-r--r-- | opendc-web/opendc-web-server/src/main/resources/db/migration/V3.0__core.sql (renamed from opendc-web/opendc-web-server/src/main/resources/db/migration/V1.0.0__core.sql) | 74 | ||||
| -rw-r--r-- | opendc-web/opendc-web-server/src/main/resources/db/testing/V3.0.1__entities.sql | 24 |
2 files changed, 63 insertions, 35 deletions
diff --git a/opendc-web/opendc-web-server/src/main/resources/db/migration/V1.0.0__core.sql b/opendc-web/opendc-web-server/src/main/resources/db/migration/V3.0__core.sql index 1a0e4046..40654b6b 100644 --- a/opendc-web/opendc-web-server/src/main/resources/db/migration/V1.0.0__core.sql +++ b/opendc-web/opendc-web-server/src/main/resources/db/migration/V3.0__core.sql @@ -7,19 +7,21 @@ create table projects id bigint not null, created_at timestamp not null, name varchar(255) not null, - portfolios_created integer not null, - scenarios_created integer not null, - topologies_created integer not null, + portfolios_created integer not null default 0, + scenarios_created integer not null default 0, + topologies_created integer not null default 0, updated_at timestamp not null, primary key (id) ); +create type project_role as enum ('OWNER', 'EDITOR', 'VIEWER'); + -- Project authorizations authorize users specific permissions to a project. create table project_authorizations ( project_id bigint not null, user_id varchar(255) not null, - role integer not null, + role project_role not null, primary key (project_id, user_id) ); @@ -55,7 +57,6 @@ create table scenarios phenomena jsonb not null, scheduler_name varchar(255) not null, sampling_fraction double precision not null, - job_id bigint, portfolio_id bigint not null, project_id bigint not null, topology_id bigint not null, @@ -63,16 +64,19 @@ create table scenarios primary key (id) ); +create type job_state as enum ('PENDING', 'CLAIMED', 'RUNNING', 'FINISHED', 'FAILED'); + create table jobs ( - id bigint not null, - created_by varchar(255) not null, - created_at timestamp not null, - repeats integer not null, - results jsonb, - state integer not null, - runtime integer not null, - updated_at timestamp not null, + id bigint not null, + created_by varchar(255) not null, + created_at timestamp not null, + repeats integer not null, + results jsonb, + state job_state not null default 'PENDING', + runtime integer not null default 0, + updated_at timestamp not null, + scenario_id bigint not null, primary key (id) ); @@ -97,60 +101,60 @@ create table traces -- Relations alter table project_authorizations - add constraint FK824hw0npe6gwiamwb6vohsu19 + add constraint fk_project_authorizations foreign key (project_id) references projects; -create index fn_topologies_number on topologies (project_id, number); +create index ux_topologies_number on topologies (project_id, number); alter table topologies - add constraint UK2s5na63qtu2of4g7odocmwi2a unique (project_id, number); + add constraint uk_topologies_number unique (project_id, number); alter table topologies - add constraint FK1kpw87pylq7m2ct9lq0ed1u3b + add constraint fk_topologies_project foreign key (project_id) references projects; -create index fn_portfolios_number on portfolios (project_id, number); +create index ux_portfolios_number on portfolios (project_id, number); alter table portfolios - add constraint FK31ytuaxb7aboxueng9hq7owwa + add constraint fk_portfolios_project foreign key (project_id) references projects; alter table portfolios - add constraint UK56dtskxruwj22dvxny2hfhks1 unique (project_id, number); - -create index fn_scenarios_number on scenarios (project_id, number); - -alter table scenarios - add constraint UKd0bk6fmtw5qiu9ty7t3g9crqd unique (project_id, number); + add constraint uk_portfolios_number unique (project_id, number); -alter table scenarios - add constraint FK9utvg0i5uu8db9pa17a1d77iy - foreign key (job_id) - references jobs; +create index ux_scenarios_number on scenarios (project_id, number); alter table scenarios - add constraint FK181y5hv0uibhj7fpbpkdy90s5 - foreign key (portfolio_id) - references portfolios; + add constraint uk_scenarios_number unique (project_id, number); alter table scenarios - add constraint FKbvwyh4joavs444rj270o3b8fr + add constraint fk_scenarios_project foreign key (project_id) references projects; alter table scenarios - add constraint FKrk6ltvaf9lp0aukp9dq3qjujj + add constraint fk_scenarios_topology foreign key (topology_id) references topologies; alter table scenarios - add constraint FK5m05tqeekqjkbbsaj3ehl6o8n + add constraint fk_scenarios_portfolio + foreign key (portfolio_id) + references portfolios; + +alter table scenarios + add constraint fk_scenarios_trace foreign key (trace_id) references traces; +alter table jobs + add constraint fk_scenarios_job + foreign key (scenario_id) + references scenarios; + -- Initial data insert into traces (id, name, type) values ('bitbrains-small', 'Bitbrains Small', 'vm'); diff --git a/opendc-web/opendc-web-server/src/main/resources/db/testing/V3.0.1__entities.sql b/opendc-web/opendc-web-server/src/main/resources/db/testing/V3.0.1__entities.sql new file mode 100644 index 00000000..1b702f4e --- /dev/null +++ b/opendc-web/opendc-web-server/src/main/resources/db/testing/V3.0.1__entities.sql @@ -0,0 +1,24 @@ +-- Test entities + +alter sequence hibernate_sequence restart with 500; + +insert into projects (id, created_at, name, portfolios_created, scenarios_created, topologies_created, updated_at) +values (1, current_timestamp(), 'Test Project', 1, 2, 1, current_timestamp()); +insert into project_authorizations (project_id, user_id, role) +values (1, 'owner', 'OWNER'), + (1, 'editor', 'EDITOR'), + (1, 'viewer', 'VIEWER'); + +insert into portfolios (id, name, number, targets, project_id) +values (1, 'Test Portfolio', 1, '{ "metrics": [] }' format json, 1); + +insert into topologies (id, created_at, name, number, rooms, updated_at, project_id) +values (1, current_timestamp(), 'Test Topology', 1, '[]' format json, current_timestamp(), 1); + +insert into scenarios (id, name, number, phenomena, scheduler_name, sampling_fraction, portfolio_id, project_id, topology_id, trace_id) +values (1, 'Test Scenario', 1, '{ "failures": false, "interference": false }' format json, 'mem', 1.0, 1, 1, 1, 'bitbrains-small'), + (2, 'Test Scenario', 2, '{ "failures": false, "interference": false }' format json, 'mem', 1.0, 1, 1, 1, 'bitbrains-small'); + +insert into jobs (id, created_by, created_at, repeats, updated_at, scenario_id) +values (1, 'owner', current_timestamp(), 1, current_timestamp(), 1), + (2, 'owner', current_timestamp(), 1, current_timestamp(), 2); |