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;
Related tables
Related indexes
Related functions
Related views
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