User Tools

Site Tools


podoc:view_submission_sql_view

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

Version 2.33

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 =

-- 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

= Version 2.34 =

DROP VIEW view_submission;
podoc/view_submission_sql_view.txt · Last modified: 2007/10/19 19:56 by Luud