===== Schema documentation ===== These pages document the database schema of Photo Organizer. The specific pages for the database elements (tables, views, sequences, indexes and functions) will contain the history of the database all the way back to the 2.05 release of Photo Organizer. === Status === * Databases schema is up to date with the current 2.33c release. === To Do === * Verify final schema with schema as reported by SQL dump of 2.34. * A few cross references between tables, views, sequences, indexes and functions may be missing. * Follow up on [[print_id_sequence sql sequence|print_id_sequence]] and [[export_id_sequence sql sequence|export_id_sequence]] as the documentation on these is probably wrong. * Follow up on documentation of functions. There are some tentative descriptions that need verifying. * For all tables, describe their use in the source code. What are the procedures for updating and using tables? * All views need documentation on what they do and what their purpose is. * Create a coherent picture of how photos are handled. Make a link between database and the physical reality. === Retrieving the current schema of your PO installation === To get the current database schema out of PostgreSQL you can use [[http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html|pg_dump]]: $ pg_dump --schema po_db > po.schema A dump of po-2.33c can be found [[schema-2.33c|here]]. Another way to inspect your PO database is by using [[http://www.pgaccess.org/|pgAccess]]. -> Not available for current versions of PostgreSQL it seems. ===== Tables ===== * [[access_type sql table|access_type]] * [[album sql table|album]] * [[album_content sql table|album_content]] * [[aperture sql table|aperture]] * [[bulk_update_rule sql table|bulk_update_rule]] removed in 2.32 * [[calendar sql table|calendar]] * [[camera sql table|camera]] * [[camera_metering sql table|camera_metering]] * [[camera_program sql table|camera_program]] * [[camera_type sql table|camera_type]] * [[client sql table|client]] * [[client_status sql table|client_status]] * [[contact_info sql table|contact_info]] * [[country sql table|country]] * [[currency sql table|currency]] removed in 2.34 * [[dcraw_options sql table|dcraw_options]] removed in 2.34 * [[ev_comp sql table|ev_comp]] * [[files sql table|files]] * [[film sql table|film]] * [[film_format sql table|film_format]] * [[film_iso sql table|film_iso]] * [[film_type sql table|film_type]] * [[filter sql table|filter]] * [[filter_type sql table|filter_type]] * [[flash sql table|flash]] * [[flash_mode sql table|flash_mode]] * [[flash_type sql table|flash_type]] * [[focal_length sql table|focal_length]] * [[folder sql table|folder]] * [[imagemagick_composite_options sql table|imagemagick_composite_options]] removed in 2.34 * [[imagemagick_options sql table|imagemagick_options]] removed in 2.34 * [[label sql table|label]] * [[lens sql table|lens]] * [[lens_type sql table|lens_type]] * [[location sql table|location]] * [[manufacturer sql table|manufacturer]] * [[orientations sql table|orientations]] * [[paper sql table|paper]] * [[photo sql table|photo]] * [[photo_dupe sql table|photo_dupe]] * [[photo_format sql table|photo_format]] removed in 2.19 * [[photo_item sql table|photo_item]] removed in 2.34 * [[photo_keywords sql table|photo_keywords]] * [[photo_spooler sql table|photo_spooler]] removed in 2.34 * [[photo_tech sql table|photo_tech]] * [[photo_version sql table|photo_version]] * [[preferences sql table|preferences]] removed in 2.34 * [[printer_spooler sql table|printer_spooler]] removed in 2.15 * [[rating sql table|rating]] * [[scan_bitdepth sql table|scan_bitdepth]] * [[scan_multiscan sql table|scan_multiscan]] * [[scan_resolution sql table|scan_resolution]] * [[scanner sql table|scanner]] * [[scanner_type sql table|scanner_type]] * [[shop_category sql table|shop_category]] removed in 2.34 * [[shop_item sql table|shop_item]] removed in 2.34 * [[shop_item_generator_rule sql table|shop_item_generator_rule]] removed in 2.34 * [[shop_order sql table|shop_order]] removed in 2.34 * [[shop_transaction sql table|shop_transaction]] removed in 2.34 * [[shop_transaction_status sql table|shop_transaction_status]] removed in 2.34 * [[shutter sql table|shutter]] * [[state sql table|state]] * [[submission sql table|submission]] removed in 2.34 * [[submission_status sql table|submission_status]] removed in 2.34 * [[support sql table|support]] * [[support_type sql table|support_type]] * [[system_preferences sql table|system_preferences]] removed in 2.34 * [[user_preferences sql table|user_preferences]] * [[user_type sql table|user_type]] * [[users sql table|users]] * [[version sql table|version]] Strike-through Text ===== Views ===== * [[view_camera sql view|view_camera]] * [[view_camera_type sql view|view_camera_type]] * [[view_client sql view|view_client]] * [[view_contact sql view|view_contact]] * [[view_contact_info sql view|view_contact_info]] * [[view_film sql view|view_film]] * [[view_film_type sql view|view_film_type]] * [[view_filter sql view|view_filter]] * [[view_filter_type sql view|view_filter_type]] * [[view_flash sql view|view_flash]] * [[view_flash_type sql view|view_flash_type]] * [[view_lens sql view|view_lens]] * [[view_lens_type sql view|view_lens_type]] * [[view_location sql view|view_location]] * [[view_photo sql view|view_photo]] * [[view_random_photo sql view|view_random_photo]] * [[view_rating sql view|view_rating]] * [[view_scanner sql view|view_scanner]] * [[view_scanner_type sql view|view_scanner_type]] * [[view_shop_item_generator_rule sql view|view_shop_item_generator_rule]] removed in 2.34 * [[view_submission sql view|view_submission]] removed in 2.34 * [[view_support sql view|view_support]] * [[view_support_type sql view|view_support_type]] * [[view_users_subscribed_to sql view|view_users_subscribed_to]] ===== Functions ===== * [[album_and_subalbums sql function|album_and_subalbums]] * [[can_access_album sql function|can_access_album]] * [[can_access_folder sql function|can_access_folder]] * [[can_access_photo sql function|can_access_photo]] * [[concat_ignore_null sql function|concat_ignore_null]] * [[count_albums_by_user sql function|count_albums_by_user]] * [[count_equipment_by_user sql function|count_equipment_by_user]] * [[count_folders_by_user sql function|count_folders_by_user]] * [[count_photo_versions sql function|count_photo_versions]] * [[count_photos sql function|count_photos]] * [[count_photo_versions_by_user sql function|count_photo_versions_by_user]] * [[count_photos_by_album sql function|count_photos_by_album]] * [[count_photos_by_folder sql function|count_photos_by_folder]] * [[count_photos_by_user sql function|count_photos_by_user]] * [[count_subalbums_by_album sql function|count_subalbums_by_album]] * [[count_subfolders_by_folder sql function|count_subfolders_by_folder]] * [[count_users sql function|count_users]] * [[is_photo_for_sale sql function|is_photo_for_sale]] removed in 2.34 * [[is_user_my_client sql function|is_user_my_client]] * [[folder_and_subfolders sql function|folder_and_subfolders]] * [[get_image_path sql function|get_image_path]] * [[get_user_pref sql function|get_user_pref]] * [[number_of_camera_references sql function|number_of_camera_references]] * [[number_of_dupes sql function|number_of_dupes]] * [[number_of_film_references sql function|number_of_film_references]] * [[number_of_filter_references sql function|number_of_filter_references]] * [[number_of_flash_references sql function|number_of_flash_references]] * [[number_of_lens_references sql function|number_of_lens_references]] * [[number_of_location_references sql function|number_of_location_references]] * [[number_of_manufacturer_ref sql function|number_of_manufacturer_ref]] * [[number_of_scanner_references sql function|number_of_scanner_references]] * [[number_of_support_references sql function|number_of_support_references]] * [[photo_shop_transaction_references sql function|photo_shop_transaction_references]] * [[tokenized_ilike sql function|tokenized_ilike]] * [[tokenized_like sql function|tokenized_like]] ===== Sequences ===== * [[access_type_id_sequence sql sequence|access_type_id_sequence]] * [[album_content_id_sequence sql sequence|album_content_id_sequence]] * [[album_id_sequence sql sequence|album_id_sequence]] * [[aperture_id_sequence sql sequence|aperture_id_sequence]] * [[bulk_update_rule_id_sequence sql sequence|bulk_update_rule_id_sequence]] removed in 2.32 * [[bulk_upload_id_sequence sql sequence|bulk_upload_id_sequence]] removed in 2.31 * [[calendar_id_sequence sql sequence|calendar_id_sequence]] * [[camera_id_sequence sql sequence|camera_id_sequence]] * [[camera_metering_id_sequence sql sequence|camera_metering_id_sequence]] * [[camera_program_id_sequence sql sequence|camera_program_id_sequence]] * [[camera_type_id_sequence sql sequence|camera_type_id_sequence]] * [[client_id_sequence sql sequence|client_id_sequence]] * [[client_status_id_sequence sql sequence|client_status_id_sequence]] * [[contact_info_id_sequence sql sequence|contact_info_id_sequence]] * [[country_id_sequence sql sequence|country_id_sequence]] * [[currency_id_sequence sql sequence|currency_id_sequence]] removed in 2.34 * [[dcraw_options_id_sequence sql sequence|dcraw_options_id_sequence]] removed in 2.34 * [[ev_comp_id_sequence sql sequence|ev_comp_id_sequence]] * [[export_id_sequence sql sequence|export_id_sequence]] removed in 2.31 * [[film_format_id_sequence sql sequence|film_format_id_sequence]] * [[film_id_sequence sql sequence|film_id_sequence]] * [[film_iso_id_sequence sql sequence|film_iso_id_sequence]] * [[film_type_id_sequence sql sequence|film_type_id_sequence]] * [[filter_id_sequence sql sequence|filter_id_sequence]] * [[filter_type_id_sequence sql sequence|filter_type_id_sequence]] * [[flash_id_sequence sql sequence|flash_id_sequence]] * [[flash_mode_id_sequence sql sequence|flash_mode_id_sequence]] * [[flash_type_id_sequence sql sequence|flash_type_id_sequence]] * [[focal_length_id_sequence sql sequence|focal_length_id_sequence]] * [[files_id_sequence sql sequence|files_id_Sequence]] * [[folder_id_sequence sql sequence|folder_id_sequence]] * [[imagemagick_composite_options_id_sequence sql sequence|imagemagick_composite_options_id_sequence]] removed in 2.34 * [[imagemagick_options_id_sequence sql sequence|imagemagick_options_id_sequence]] removed in 2.34 * [[label_id_sequence sql sequence|label_id_sequence]] * [[lens_id_sequence sql sequence|lens_id_sequence]] * [[lens_type_id_sequence sql sequence|lens_type_id_sequence]] * [[location_id_sequence sql sequence|location_id_sequence]] * [[manufacturer_id_sequence sql sequence|manufacturer_id_sequence]] * [[orientations_id_sequence sql sequence|orientations_id_sequence]] * [[paper_id_sequence sql sequence|paper_id_sequence]] * [[photo_dupe_id_sequence sql sequence|photo_dupe_id_sequence]] * [[photo_format_id_sequence sql sequence|photo_format_id_sequence]] removed in 2.19 * [[photo_id_sequence sql sequence|photo_id_sequence]] * [[photo_item_id_sequence sql sequence|photo_item_id_sequence]] removed in 2.34 * [[photo_spooler_id_sequence sql sequence|photo_spooler_id_sequence]] removed in 2.34 * [[photo_tech_id_sequence sql sequence|photo_tech_id_sequence]] * [[photo_version_id_sequence sql sequence|photo_version_id_sequence]] * [[preferences_id_sequence sql sequence|preferences_id_sequence]] removed in 2.34 * [[print_id_sequence sql sequence|print_id_sequence]] * [[printer_spooler_id_sequence sql sequence|printer_spooler_id_sequence]] removed in 2.15 * [[rating_id_sequence sql sequence|rating_id_sequence]] * [[scan_bitdepth_id_sequence sql sequence|scan_bitdepth_id_sequence]] * [[scan_multiscan_id_sequence sql sequence|scan_multiscan_id_sequence]] * [[scan_resolution_id_sequence sql sequence|scan_resolution_id_sequence]] * [[scanner_id_sequence sql sequence|scanner_id_sequence]] * [[scanner_type_id_sequence sql sequence|scanner_type_id_sequence]] * [[shop_category_id_sequence sql sequence|shop_category_id_sequence]] removed in 2.34 * [[shop_item_generator_rule_id_sequence sql sequence|shop_item_generator_rule_id_sequence]] removed in 2.34 * [[shop_order_id_sequence sql sequence|shop_order_id_sequence]] removed in 2.34 * [[shop_transaction_id_sequence sql sequence|shop_transaction_id_sequence]] removed in 2.34 * [[shop_transaction_status_id_sequence sql sequence|shop_transaction_status_id_sequence]] removed in 2.34 * [[shutter_id_sequence sql sequence|shutter_id_sequence]] * [[state_id_sequence sql sequence|state_id_sequence]] * [[submission_id_sequence sql sequence|submission_id_sequence]] * [[submission_status_id_sequence sql sequence|submission_status_id_sequence]] * [[support_id_sequence sql sequence|support_id_sequence]] * [[support_type_id_sequence sql sequence|support_type_id_sequence]] * [[user_type_id_sequence sql sequence|user_type_id_sequence]] * [[users_id_sequence sql sequence|users_id_sequence]] ===== Indexes ===== * [[album_content_album_idx sql index|album_content_album_idx]] * [[album_content_id_idx sql index|album_content_id_idx]] removed in 2.34 * [[album_content_photo_idx sql index|album_content_photo_idx]] * [[album_event_idx sql index|album_event_idx]] * [[album_id_idx sql index|album_id_idx]] removed in 2.34 * [[album_users_idx sql index|album_users_idx]] * [[calendar_client_idx sql index|calendar_client_idx]] * [[calendar_id_idx sql index|calendar_id_idx]] * [[calendar_user_idx sql index|calendar_user_idx]] * [[camera_type_idx sql index|camera_type_idx]] * [[camera_type_manufacturer_idx sql index|camera_type_manufacturer_idx]] * [[camera_users_idx sql index|camera_users_idx]] * [[client_id_idx sql index|client_id_idx]] * [[contact_info_idx sql index|contact_info_idx]] * [[files_version_idx sql index|files_version_idx]] * [[film_type_idx sql index|film_type_idx]] * [[film_type_manufacturer_idx sql index|film_type_manufacturer_idx]] * [[film_users_idx sql index|film_users_idx]] * [[filter_type_idx sql index|filter_type_idx]] * [[filter_type_manufacturer_idx sql index|filter_type_manufacturer_idx]] * [[filter_users_idx sql index|filter_users_idx]] * [[flash_type_idx sql index|flash_type_idx]] * [[flash_type_manufacturer_idx sql index|flash_type_manufacturer_idx]] * [[flash_users_idx sql index|flash_users_idx]] * [[folder_event_idx sql index|folder_event_idx]] * [[folder_id_idx sql index|folder_id_idx]] removed in 2.34 * [[folder_parent_idx sql index|folder_parent_idx]] * [[folder_users_idx sql index|folder_users_idx]] * [[label_idx sql index|label_idx]] * [[lens_type_idx sql index|lens_type_idx]] * [[lens_type_manufacturer_idx sql index|lens_type_manufacturer_idx]] * [[lens_users_idx sql index|lens_users_idx]] * [[paper_idx sql index|paper_idx]] * [[photo_dupe_key sql index|photo_dupe_key]] * [[photo_dupe_version_idx sql index|photo_dupe_version_idx]] * [[photo_folder sql index|photo_folder]] * [[photo_id_idx sql index|photo_id_idx]] removed in 2.34 * [[photo_keywords_photo sql index|photo_keywords_photo]] * [[photo_location_idx sql index|photo_location_idx]] * [[photo_tech_id_idx sql index|photo_tech_id_idx]] * [[photo_tech_photo_idx sql index|photo_tech_photo_idx]] * [[photo_users_idx sql index|photo_users_idx]] * [[photo_version_photo_idx sql index|photo_version_photo_idx]] * [[photo_version_photo sql index|photo_version_photo]] * [[client_client_idx sql index|client_client_idx sql index]] * [[client_users_idx sql index|client_users_idx]] * [[rating_id_idx sql index|rating_id_idx]] * [[rating_photo_idx sql index|rating_photo_idx]] * [[rating_users_idx sql index|rating_users_idx]] * [[rating_version_idx sql index|rating_version_idx]] * [[scanner_type_idx sql index|scanner_type_idx]] * [[scanner_type_manufacturer_idx sql index|scanner_type_manufacturer_idx]] * [[scanner_users_idx sql index|scanner_users_idx]] * [[support_type_idx sql index|support_type_idx]] * [[support_type_manufacturer_idx sql index|support_type_manufacturer_idx]] * [[support_users_idx sql index|support_users_idx]] * [[users_idx sql index|users_idx]] removed in 2.34 * [[user_preferences_owner_idx sql index|user_preferences_owner_idx]]