User Tools

Site Tools


podoc:view_submission_sql_view

This is an old revision of the document!


Database View Description of "view_submission"

Name

view_submission

Description

Purpose

Schema

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;

History

Used first

Version 2.5

Used last

Still in use

Change history

= Version 2.5 =

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

= Version 2.6 =

= Version 2.6 =

-- 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
podoc/view_submission_sql_view.1176454872.txt.gz · Last modified: 2007/04/13 09:01 by Luud