===== Database Table Description of "submission" =====

=== Name ===

submission

=== Description ===

=== Purpose ===

=== Schema ===

<code sql>
create table submission (
  identifier            integer not null primary key,
  photo_dupe            integer not null references photo_dupe(identifier),
  status                integer not null references submission_status(identifier),
  users                 integer references users(identifier),
  date_of_creation      timestamp,
  remark                text
);
</code>

=== References tables ===

  * photo_dupe references [[photo_dupe sql table|photo_dupe]](identifier)
  * status references [[submission_status sql table|submission_status]](identifier)
  * users references [[users sql table|users]](identifier)

=== Is referenced by tables ===

None.

=== Related sequences ===

  * [[submission_id_sequence sql sequence|submission_id_sequence]]

=== Related indexes ===

None.

=== Related functions ===

None.

=== Related views ===

  * [[view_submission sql view|view_submission]]

=== History ===

== Used first ==

Version 2.5

== Used last ==

Version 2.33

== Schema change history ==

= Version 2.5 =

<code sql>
create table submission (
  identifier            integer not null primary key,
  photo_dupe            integer not null references photo_dupe(identifier),
  status                integer not null references submission_status(identifier),
  users                 integer not null references users(identifier),
  date_of_creation      timestamp,
  remark                text
);
</code>

= Version 2.6 =

<code sql>
-- begin: drop users constraint on the submissions
create table submission_temp as
  select identifier, photo_dupe, status, users, date_of_creation, remark
    from submission;

drop view view_submission;
drop table submission;

create table submission (
  identifier            integer not null primary key,
  photo_dupe            integer not null references photo_dupe(identifier),
  status                integer not null references submission_status(identifier),
  users                 integer references users(identifier),
  date_of_creation      timestamp,
  remark                text
);


insert into submission select * from submission_temp;
drop table submission_temp;

create view view_submission as
  select submission.identifier, photo_dupe, photo_dupe.photo, photo_dupe.key,
         (select users.identifier from users where users.identifier = submission.users) as uidentifier,
         (select users.first_name from users where users.identifier = submission.users) as first_name,
         (select users.last_name from users where users.identifier = submission.users) as last_name,
         submission_status.value, submission.date_of_creation, submission.remark
    from submission, photo_dupe, submission_status
   where submission.photo_dupe = photo_dupe.identifier
     and submission.status = submission_status.identifier
   order by submission.date_of_creation, last_name, first_name;

-- end: drop users constraint on the submissions
</code>

= Version 2.34 =

<code sql>
drop table submission;
</code>

== Data change history ==