160 lines
8.6 KiB
SQL
160 lines
8.6 KiB
SQL
-- ==========================================
|
||
-- CLUB DAGUERRE CAMERA DATABASE
|
||
-- Part 8: Seed Data
|
||
-- ==========================================
|
||
|
||
-- ==========================================
|
||
-- CONTENT SOURCES
|
||
-- ==========================================
|
||
INSERT INTO content_sources (source_name, source_type, rights_status, legal_notes, verified_by, verified_date) VALUES
|
||
('Club Daguerre - Photo Antiquaria', 'internal', 'owned', 'Club owns publication and digitization rights', 'Walter Jekat', CURRENT_DATE),
|
||
('Club Daguerre aktuell', 'internal', 'owned', 'Club publication, full rights', 'Walter Jekat', CURRENT_DATE),
|
||
('Harald Goergens Collection', 'licensed', 'owned', 'Harald grants perpetual license for his photos/data to Club Daguerre', 'Walter Jekat', CURRENT_DATE),
|
||
('Steimer Database - Images', 'uncertain', 'questionable', 'Source unknown. DO NOT publish without clearance. Internal research only.', NULL, NULL),
|
||
('Steimer Database - Text', 'uncertain', 'questionable', 'Text from various publications. Rights status unknown. Use metadata only.', NULL, NULL),
|
||
('User Contributed', 'licensed', 'licensed', 'Users grant CC-BY-SA license on upload', 'System', CURRENT_DATE),
|
||
('Pre-1923 Public Domain', 'public_domain', 'public_domain', 'Works published before 1923 are public domain', 'Walter Jekat', CURRENT_DATE),
|
||
('Manufacturer Marketing', 'public_domain', 'fair_use', 'Brochures, ads, price lists - marketing materials (fair use)', 'Walter Jekat', CURRENT_DATE);
|
||
|
||
-- ==========================================
|
||
-- CORE TERMINOLOGY CONCEPTS
|
||
-- ==========================================
|
||
|
||
-- Body Types
|
||
INSERT INTO concepts (concept_code, category, sort_order) VALUES
|
||
('BODY_TYPE_LB', 'body_type', 1),
|
||
('BODY_TYPE_FG', 'body_type', 2),
|
||
('BODY_TYPE_KL', 'body_type', 3),
|
||
('BODY_TYPE_SP', 'body_type', 4),
|
||
('BODY_TYPE_RK', 'body_type', 5),
|
||
('BODY_TYPE_BO', 'body_type', 6),
|
||
('BODY_TYPE_TU', 'body_type', 7),
|
||
('BODY_TYPE_MG', 'body_type', 8);
|
||
|
||
-- Body Type Terms
|
||
INSERT INTO concept_terms (concept_id, language_code, term, term_type) VALUES
|
||
-- Laufboden (Lb)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_LB'), 'de', 'Laufbodenkamera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_LB'), 'de', 'Lb', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_LB'), 'de', 'Laufboden', 'synonym'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_LB'), 'en', 'Strut folder camera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_LB'), 'en', 'SF', 'abbreviation'),
|
||
|
||
-- Festgehäuse (Fg)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_FG'), 'de', 'Festgehäuse', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_FG'), 'de', 'Fg', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_FG'), 'en', 'Rigid body camera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_FG'), 'en', 'Rigid', 'synonym'),
|
||
|
||
-- Klappkamera (Kl)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_KL'), 'de', 'Klappkamera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_KL'), 'de', 'Kl', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_KL'), 'en', 'Folding camera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_KL'), 'en', 'Folder', 'synonym'),
|
||
|
||
-- Spiegelreflex (Sp)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_SP'), 'de', 'Spiegelreflexkamera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_SP'), 'de', 'Sp', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_SP'), 'de', 'Spiegelreflex', 'synonym'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_SP'), 'en', 'Single-lens reflex', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_SP'), 'en', 'SLR', 'abbreviation'),
|
||
|
||
-- Reflexkamera (Rk)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_RK'), 'de', 'Reflexkamera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_RK'), 'de', 'Rk', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_RK'), 'en', 'Twin-lens reflex', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_RK'), 'en', 'TLR', 'abbreviation'),
|
||
|
||
-- Boxkamera (Bo)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_BO'), 'de', 'Boxkamera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_BO'), 'de', 'Bo', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_BO'), 'de', 'Box', 'synonym'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_BO'), 'en', 'Box camera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_BO'), 'en', 'Box', 'abbreviation'),
|
||
|
||
-- Tubus (Tu)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_TU'), 'de', 'Tubuskamera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_TU'), 'de', 'Tu', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_TU'), 'en', 'Tube camera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_TU'), 'en', 'Tube', 'abbreviation'),
|
||
|
||
-- Magazine (Mg)
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_MG'), 'de', 'Magazinkamera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_MG'), 'de', 'Mg', 'abbreviation'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_MG'), 'en', 'Magazine camera', 'primary'),
|
||
((SELECT id FROM concepts WHERE concept_code = 'BODY_TYPE_MG'), 'en', 'Mag', 'abbreviation');
|
||
|
||
-- ==========================================
|
||
-- COMMON FILM FORMATS
|
||
-- ==========================================
|
||
INSERT INTO formats (format_code, width_mm, height_mm, format_type, description_de, description_en, sort_order) VALUES
|
||
('9x12 Pl', 90, 120, 'plate', 'Glasplatte 9×12 cm', 'Glass plate 9×12 cm', 10),
|
||
('13x18 Pl', 130, 180, 'plate', 'Glasplatte 13×18 cm', 'Glass plate 13×18 cm', 20),
|
||
('6x9 RF', 60, 90, 'rollfilm', 'Rollfilm 6×9 cm', 'Rollfilm 6×9 cm', 30),
|
||
('6x6 RF', 60, 60, 'rollfilm', 'Rollfilm 6×6 cm', 'Rollfilm 6×6 cm', 40),
|
||
('24x36 KB', 24, 36, '35mm', 'Kleinbild 24×36 mm', '35mm film', 50);
|
||
|
||
-- ==========================================
|
||
-- SAMPLE LENSES
|
||
-- ==========================================
|
||
INSERT INTO lenses (name, manufacturer, focal_length_mm, max_aperture, lens_type, notes) VALUES
|
||
('Tessar', 'Zeiss', 50, 3.5, 'prime', 'Classic 4-element lens'),
|
||
('Tessar', 'Zeiss', 105, 4.5, 'prime', 'Medium telephoto version'),
|
||
('Xenar', 'Schneider', 50, 2.8, 'prime', 'High-speed Tessar type'),
|
||
('Anastigmat', 'Various', NULL, NULL, 'prime', 'Generic designation for corrected lenses');
|
||
|
||
-- ==========================================
|
||
-- SAMPLE SHUTTERS
|
||
-- ==========================================
|
||
INSERT INTO shutters (name, manufacturer, shutter_type, speed_range, notes) VALUES
|
||
('Compur', 'Deckel', 'leaf', '1-1/300s, B', 'Most common leaf shutter'),
|
||
('Prontor', 'Gauthier', 'leaf', '1-1/300s, B', 'Alternative to Compur'),
|
||
('Ibsor', 'Deckel', 'leaf', '1-1/100s, B', 'Budget version of Compur');
|
||
|
||
-- ==========================================
|
||
-- SYSTEM CONFIGURATION
|
||
-- ==========================================
|
||
INSERT INTO system_config (key, value, description, updated_by, updated_at) VALUES
|
||
('schema_version', '1.0', 'Current database schema version', 'Installation', NOW()),
|
||
('last_import', NULL, 'Timestamp of last Goergens data import', NULL, NULL),
|
||
('research_password_changed', NULL, 'Date of last research password change', NULL, NULL);
|
||
|
||
-- ==========================================
|
||
-- ANALYSIS REPORT
|
||
-- ==========================================
|
||
DO $$
|
||
DECLARE
|
||
table_count INT;
|
||
index_count INT;
|
||
view_count INT;
|
||
function_count INT;
|
||
BEGIN
|
||
SELECT COUNT(*) INTO table_count
|
||
FROM information_schema.tables
|
||
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';
|
||
|
||
SELECT COUNT(*) INTO index_count
|
||
FROM pg_indexes
|
||
WHERE schemaname = 'public';
|
||
|
||
SELECT COUNT(*) INTO view_count
|
||
FROM information_schema.views
|
||
WHERE table_schema = 'public';
|
||
|
||
SELECT COUNT(*) INTO function_count
|
||
FROM pg_proc
|
||
WHERE pronamespace = 'public'::regnamespace;
|
||
|
||
RAISE NOTICE '===========================================';
|
||
RAISE NOTICE 'CLUB DAGUERRE CAMERA DATABASE';
|
||
RAISE NOTICE 'Schema Installation Complete';
|
||
RAISE NOTICE '===========================================';
|
||
RAISE NOTICE 'Tables created: %', table_count;
|
||
RAISE NOTICE 'Indexes created: %', index_count;
|
||
RAISE NOTICE 'Views created: %', view_count;
|
||
RAISE NOTICE 'Functions created: %', function_count;
|
||
RAISE NOTICE '===========================================';
|
||
RAISE NOTICE 'Ready for Goergens data import';
|
||
RAISE NOTICE '===========================================';
|
||
END $$;
|