Page contents
UML class diagram
Entity-relationship diagram
DDL
create table accepted_state
(
accepted_state_id bigint generated by default as identity,
status_tag varchar(255) not null,
status_tag_description varchar(255) not null,
primary key (accepted_state_id),
constraint uk_accepted_state_status_tag unique (status_tag)
);
create table issue_report
(
accepted_state_id bigint not null,
issue_report_id bigint generated by default as identity,
report_location_id bigint not null unique,
time_first_reported timestamp(6) with time zone not null,
time_last_modified timestamp(6) with time zone not null,
user_profile_id bigint not null,
issue_report_external_id uuid,
text_description varchar(255) not null,
primary key (issue_report_id)
);
create table issue_report_issue_type
(
issue_report_id bigint not null,
issue_type_id bigint not null
);
create table issue_type
(
issue_type_id bigint generated by default as identity,
issue_type_description varchar(255) not null,
issue_type_tag varchar(255) not null,
primary key (issue_type_id),
constraint uk_issue_type_issue_type_tag unique (issue_type_tag)
);
create table report_image
(
album_order integer not null,
issue_report_id bigint not null,
report_image_id bigint generated by default as identity,
report_image_external_key uuid,
filename varchar(255) not null,
image_locator varchar(255) not null,
mime_type varchar(255) not null,
primary key (report_image_id)
);
create table report_location
(
latitude float(53),
longitude float(53),
report_location_id bigint generated by default as identity,
location_description varchar(255),
street_coordinate varchar(255),
primary key (report_location_id)
);
create table user_profile
(
is_manager boolean not null,
user_enabled boolean not null,
time_created timestamp(6) with time zone not null,
user_profile_id bigint generated by default as identity,
user_profile_external_id uuid,
avatar varchar(255),
display_name varchar(255) not null,
email varchar(255) not null,
oauth_key varchar(255) not null,
primary key (user_profile_id),
constraint uk_user_profile_oauth_key unique (oauth_key)
);
create index ix_issue_report_user_profile_id on issue_report (user_profile_id);
create index ix_issue_report_accepted_state_id on issue_report (accepted_state_id);
create index ix_issue_report_time_first_reported on issue_report (time_first_reported);
create index ix_issue_report_time_last_modified on issue_report (time_last_modified);
create index ix_report_image_issue_report_id on report_image (issue_report_id);
create index ix_user_profile_email on user_profile (email);
create index ix_user_profile_user_enabled on user_profile (user_enabled);
alter table if exists issue_report
add constraint FK6vcgwtxylbnwq16lmm4i30rpc foreign key (accepted_state_id) references accepted_state;
alter table if exists issue_report
add constraint FK98ka33yg288ge0k7k5xw95q0 foreign key (report_location_id) references report_location;
alter table if exists issue_report
add constraint FKq1x6v89s7solfu8stxbsavyx8 foreign key (user_profile_id) references user_profile;
alter table if exists issue_report_issue_type
add constraint FKcwkd6ncsrbo795wtdnod6278p foreign key (issue_type_id) references issue_type;
alter table if exists issue_report_issue_type
add constraint FKfcvled0bwve03nkxyp2r9ynk9 foreign key (issue_report_id) references issue_report;
alter table if exists report_image
add constraint FK1b2rbjjpi0lu4mvvtrv2wj4an foreign key (issue_report_id) references issue_report;
Entity classes
The following JPA entity classes correspond to the tables shown in the ERD.