User Tools

Site Tools


podoc:submission_sql_table

Database Table Description of "submission"

Name

submission

Description

Purpose

Schema

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

References tables

Is referenced by tables

None.

None.

None.

History

Used first

Version 2.5

Used last

Version 2.33

Schema change history

= Version 2.5 =

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

= 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 TABLE submission;
Data change history
podoc/submission_sql_table.txt · Last modified: 2007/10/19 15:56 by Luud