-- -- photodb.sql -- -- jkoontz@arsdigita.com -- -- -- data model for photo management service -- written by Group 4, 6.916, 3/4/99 -- edited Oct, 1999 -- create an administration group for photo database administration begin administration_group_add ('Photo Database Staff', 'photodb', NULL, 'f', '/photodb/admin/'); end; / create sequence ph_folder_type_id_sequence start with 1 increment by 1; create table ph_folder_types ( folder_type_id integer not null primary key, folder_type varchar(50) ); insert into ph_folder_types (folder_type_id, folder_type) values ( ph_folder_type_id_sequence.nextval,'Roll'); insert into ph_folder_types (folder_type_id, folder_type) values ( ph_folder_type_id_sequence.nextval,'PhotoCD'); insert into ph_folder_types (folder_type_id, folder_type) values ( ph_folder_type_id_sequence.nextval,'Folder'); create sequence ph_folder_id_sequence start with 1 increment by 1; create table ph_folders ( folder_id integer not null primary key, user_id integer not null references users, title varchar(50), photo_cd_id integer, folder_type_id integer not null references ph_folder_types, protect_p varchar(1) check (protect_p in ('t','f')) ); create index ph_folders_by_user_id_idx on ph_folders(user_id); -- The following table is for user preferences create table ph_user_preferences ( user_id integer not null references users(user_id), images_public_p char(1) check (images_public_p in ('t','f')), photos_sort_by varchar(100), default_image_size char(1) check (default_image_size in ('s','m','l')), prefer_text_p char(1) check (prefer_text_p in ('t','f')) ); create index ph_user_prefs_by_user_idx on ph_user_preferences(user_id); create sequence ph_camera_model_id_sequence start with 1 increment by 1; create table ph_camera_models ( camera_model_id integer not null primary key, manufacturer varchar(50), -- e.g., 'Nikon' model varchar(50), -- e.g., '8008/F801' variation varchar(50), -- e.g., 'titanium' last_modified_date date, last_modifying_user references users, modified_ip_address varchar2(20) ); -- to facilitate captioning photos with tech info, we keep track of which -- cameras each photographer owns (if they want to give us this info) create sequence ph_camera_id_sequence start with 1 increment by 1; create table ph_cameras ( camera_id integer not null primary key, user_id integer not null references users, camera_model_id integer references ph_camera_models, pretty_name varchar(50), -- e.g., "EOS-5 with date back" serial_number varchar(50), date_purchased date, creation_date date, -- the numbers below are just up until -- date_surveyed; they are not kept up to date automagically -- as users enter rolls n_failures integer, n_rolls_exposed integer, -- "n sheets" for a view camera purchased_new_p char(1) check (purchased_new_p in ('t', 'f')) ); create index ph_cameras_by_user_idx on ph_cameras(user_id); create index ph_cameras_by_model_idx on ph_cameras(camera_model_id); -- we go to all this trouble because we want to be able to ask "Show -- me all the Tri-X photos" create sequence ph_film_type_id_sequence start with 1 increment by 1; create table ph_film_types ( film_type_id integer not null primary key, film_type varchar(20) -- e.g., e6, k14, c41-bw, bw ); insert into ph_film_types (film_type_id, film_type) values (ph_film_type_id_sequence.nextval, 'Digital'); insert into ph_film_types (film_type_id, film_type) values (ph_film_type_id_sequence.nextval, 'Black/White'); insert into ph_film_types (film_type_id, film_type) values (ph_film_type_id_sequence.nextval, 'C41 (Color Negative)'); insert into ph_film_types (film_type_id, film_type) values (ph_film_type_id_sequence.nextval, 'E6 (Color Slide)'); insert into ph_film_types (film_type_id, film_type) values (ph_film_type_id_sequence.nextval, 'K14 (Kodachrome)'); insert into ph_film_types (film_type_id, film_type) values (ph_film_type_id_sequence.nextval, 'Infrared'); create sequence ph_film_id_sequence start with 1 increment by 1; create table ph_films ( film_id integer not null primary key, film_type_id integer not null references ph_film_types, manufacturer varchar(50), -- e.g., Kodak, Fuji, Ilford full_name varchar(50), -- e.g., Ektachrome Professional Plus abbrev varchar(10), -- e.g., EPP, RDP, VPS last_modified_date date, last_modifying_user references users, modified_ip_address varchar2(20) ); insert into ph_films (film_id, film_type_id, manufacturer, full_name, abbrev) values (ph_film_id_sequence.nextval, 1, '(none)', 'Digital', 'Digital'); create index ph_films_by_type_idx on ph_films(film_type_id); -- The following table is for custom fields tracking -- It allows us to do smart searching on fields, figure out -- which fields are active, and which are "deleted" (since -- we can't REALLY delete fields from Oracle). -- (Now that we are use 8i we can. 8/11/1999) create sequence ph_custom_field_id_sequence start with 1 increment by 1; create table ph_custom_photo_fields ( custom_field_id integer not null primary key, user_id integer not null references users(user_id), field_name varchar(200), field_pretty_name varchar(200), field_type varchar(200), date_added date, field_active_p char(1) check (field_active_p in ('t','f')), field_comment varchar2(4000) ); create index ph_custom_fields_by_user_idx on ph_custom_photo_fields(user_id); create index ph_custom_fields_by_active_idx on ph_custom_photo_fields(field_active_p); -- A table ph_user_(user_id)_custom_info will be created to store custom photo -- info. -- It's columns include photo_id, data field's (being add on) create sequence ph_photo_id_sequence start with 1 increment by 1; create table ph_photos ( photo_id integer not null primary key, user_id integer not null references users, folder_id integer not null references ph_folders, -- Can this photo be seen in the community photo_public_p char(1) check (photo_public_p in ('t','f')), camera_id integer not null references ph_cameras, film_id integer references ph_films, file_extension varchar(10), -- eg .jpg .gif size_available_sm char(1) check (size_available_sm in ('t','f')), size_available_md char(1) check (size_available_md in ('t','f')), size_available_lg char(1) check (size_available_lg in ('t','f')), -- These are the sizes of the thumbnails. It allows the client -- to display the whole page even if the thumbnails are not yet loaded sm_width integer, sm_height integer, md_width integer, md_height integer, lg_width integer, lg_height integer, photo_cd_id integer, orphan_key varchar(50), exposure_date date, caption varchar(4000), tech_details varchar(4000), -- f-stop, shutter speed, film used -- If a recognizable person is in the photo, is the -- model_release info available model_release_p char(1) check (model_release_p in ('t','f')), -- rights grants -- we do this in six separate columns so that we can -- use an Oracle bitmap index to make queries faster rights_personal_web_p char(1) check (rights_personal_web_p in ('t','f')), rights_personal_print_p char(1) check (rights_personal_print_p in ('t','f')), rights_nonprofit_web_p char(1) check (rights_nonprofit_web_p in ('t','f')), rights_nonprofit_print_p char(1) check (rights_nonprofit_print_p in ('t','f')), rights_comm_web_p char(1) check (rights_comm_web_p in ('t','f')), rights_comm_print_p char(1) check (rights_comm_print_p in ('t','f')), -- copyright_statement is an HTML fragment, if they want to -- fundamentally refer people to their Web server, they can have -- a simple sentence with a hyperlink copyright_statement varchar(4000), file_size number, creation_date date, publisher_favorite_p char(1) default 'f' check (publisher_favorite_p in ('t','f')), -- New workflow: user uploads photos, then updates the information. -- This flag determines whether the user has done so. user_updated_p char(1) default 'f' check (unprocessed_p in ('t', 'f')), -- Category for photo category varchar(200) ); alter table ph_photos add submit_for_rating_p char(1); alter table ph_photos add views number default 0; create index ph_photos_by_user_idx on ph_photos(user_id); create index ph_photos_by_folder_idx on ph_photos(folder_id); create index ph_photos_by_public_p_idx on ph_photos(photo_public_p); create index ph_photos_by_m_release_idx on ph_photos(model_release_p); create index ph_photos_by_r_pers_web_idx on ph_photos(rights_personal_web_p); create index ph_photos_by_r_pers_print_idx on ph_photos(rights_personal_print_p); create index ph_photos_by_r_nonp_web_idx on ph_photos(rights_nonprofit_web_p); create index ph_photos_by_r_nonp_print_idx on ph_photos(rights_nonprofit_print_p); create index ph_photos_by_r_comm_web_idx on ph_photos(rights_comm_web_p); create index ph_photos_by_r_comm_print_idx on ph_photos(rights_comm_print_p); create index ph_photos_by_updated_idx on ph_photos(user_updated_p); create sequence ph_presentation_id_sequence start with 1 increment by 1; create table ph_presentations ( presentation_id integer not null primary key, user_id integer not null references users, title varchar(200), public_p char(1) check (public_p in ('t','f')), beginning_note varchar(4000), ending_note varchar(4000), use_html_code_p char(1) check (use_html_code_p in ('t','f')), html_code clob, creation_date date ); create index ph_presentation_by_user_idx on ph_presentations(user_id); create table ph_presentation_photo_map ( presentation_id integer not null references ph_presentations, photo_id integer not null references ph_photos, annotation varchar(4000), photo_order integer ); create index ph_prest_photo_by_present_idx on ph_presentation_photo_map(presentation_id); create index ph_prest_photo_by_photo_idx on ph_presentation_photo_map(photo_id); create table ph_presentation_user_map ( presentation_id integer not null references ph_presentations, user_id integer not null references users ); create index ph_prest_user_by_present_idx on ph_presentation_user_map(presentation_id); create index ph_prest_user_by_user_idx on ph_presentation_user_map(user_id); -- Links to the General Comments module insert into table_acs_properties (table_name, section_name, user_url_stub, admin_url_stub) select 'ph_photos', 'photodb photos', '/photodb/photo.tcl?photo_id=','/photodb/admin/photo.tcl?photo_id=' from dual where 0 = (select count(*) from table_acs_properties where table_name = 'ph_photos'); insert into table_acs_properties (table_name, section_name, user_url_stub, admin_url_stub) select 'ph_presentations', 'photodb presentations', '/photodb/presentation.tcl?presentation_id=','/photodb/admin/presentation.tcl?presentation_id=' from dual where 0 = (select count(*) from table_acs_properties where table_name = 'ph_presentations'); create index ph_views on ph_overall_ratings(rating_count);