191 lines
4.9 KiB
Markdown
191 lines
4.9 KiB
Markdown
# Club Daguerre Camera Database - Schema Installation
|
|
|
|
## Overview
|
|
|
|
Complete PostgreSQL 16 schema for the Club Daguerre camera database, preserving Harald Goergens' 30+ year classification work with modern metadata management.
|
|
|
|
## Prerequisites
|
|
|
|
- PostgreSQL 16 installed
|
|
- camera_db database created
|
|
- Port 5433 (or your configured port)
|
|
|
|
## Installation Order
|
|
|
|
The schema is split into logical sections for clarity and maintainability:
|
|
|
|
1. **01-core-hierarchy.sql** - Manufacturers, models, variants, combos
|
|
2. **02-supporting.sql** - Lenses, shutters, formats, terminology
|
|
3. **03-content.sql** - Images, documents, articles (multi-level linking)
|
|
4. **04-auth.sql** - Members, research access, audit log
|
|
5. **05-history.sql** - History tables for audit trail
|
|
6. **06-triggers.sql** - Triggers and functions for automation
|
|
7. **07-views.sql** - Materialized views for performance
|
|
8. **08-seed-data.sql** - Initial data (sources, terminology, samples)
|
|
|
|
## Quick Installation
|
|
|
|
### Method 1: All at Once
|
|
|
|
```bash
|
|
cd /data/camera-database/schema
|
|
|
|
# Run all files in order
|
|
for file in 0{1..8}-*.sql; do
|
|
echo "Installing $file..."
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f "$file"
|
|
done
|
|
```
|
|
|
|
### Method 2: One by One
|
|
|
|
```bash
|
|
cd /data/camera-database/schema
|
|
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 01-core-hierarchy.sql
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 02-supporting.sql
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 03-content.sql
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 04-auth.sql
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 05-history.sql
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 06-triggers.sql
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 07-views.sql
|
|
psql -h localhost -p 5433 -U postgres -d camera_db -f 08-seed-data.sql
|
|
```
|
|
|
|
## Verification
|
|
|
|
After installation:
|
|
|
|
```sql
|
|
-- Connect to database
|
|
psql -h localhost -p 5433 -U postgres -d camera_db
|
|
|
|
-- Check tables
|
|
\dt
|
|
|
|
-- Check views
|
|
\dv
|
|
|
|
-- Check materialized views
|
|
\dm
|
|
|
|
-- Check functions
|
|
\df
|
|
|
|
-- Run validation
|
|
SELECT * FROM validate_goergens_structure();
|
|
|
|
-- Check seed data
|
|
SELECT * FROM content_sources;
|
|
SELECT * FROM terminology LIMIT 10;
|
|
|
|
-- Exit
|
|
\q
|
|
```
|
|
|
|
## Schema Statistics
|
|
|
|
Expected after installation:
|
|
- **Tables:** 35
|
|
- **Indexes:** ~80
|
|
- **Views:** 2
|
|
- **Materialized Views:** 3
|
|
- **Functions:** 5
|
|
- **Triggers:** 8
|
|
|
|
## Key Features
|
|
|
|
### 1. Goergens Hierarchy Preservation
|
|
- Manufacturers (ERNM, ZEII, etc.)
|
|
- Models (1910, 0450, etc.)
|
|
- Variants (a, b, c - color/material)
|
|
- Combos (01, 02 - lens/shutter configs)
|
|
|
|
### 2. Multi-Level Content Linking
|
|
Images, documents, and articles can link at ANY level:
|
|
- Manufacturer level (company history)
|
|
- Model level (camera manual - shared by all variants)
|
|
- Variant level (color-specific photo)
|
|
- Combo level (lens manual - combo-specific)
|
|
|
|
### 3. Rights Management
|
|
- **verified:** Publishable content
|
|
- **uncertain:** Research-only (Steimer data)
|
|
- **restricted:** Internal only
|
|
|
|
### 4. Performance Optimizations
|
|
- Denormalized fields in lens_shutter_combos
|
|
- Materialized views for dashboard/search
|
|
- GIN indexes for full-text search
|
|
- GIST indexes for range queries
|
|
|
|
### 5. Audit Trail
|
|
- Version numbers on all records
|
|
- History tables track all changes
|
|
- Comprehensive audit log
|
|
- Timestamp tracking
|
|
|
|
### 6. Multilingual Support
|
|
- German/English core (expandable)
|
|
- Separate i18n tables for content
|
|
- Language-agnostic codes
|
|
|
|
## Maintenance
|
|
|
|
### Refresh Materialized Views
|
|
|
|
```sql
|
|
-- Hourly (manufacturer stats)
|
|
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_manufacturer_stats;
|
|
|
|
-- Every 15-30 minutes (search catalog)
|
|
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_public_catalog;
|
|
|
|
-- Every 5 minutes (dashboard)
|
|
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats;
|
|
```
|
|
|
|
### Backup
|
|
|
|
```bash
|
|
# Full backup
|
|
pg_dump -h localhost -p 5433 -U postgres camera_db | gzip > /mnt/data/postgresql/backups/camera_db_$(date +%Y%m%d).sql.gz
|
|
|
|
# Schema only
|
|
pg_dump -h localhost -p 5433 -U postgres -s camera_db > /mnt/data/postgresql/backups/camera_db_schema.sql
|
|
```
|
|
|
|
## Next Steps
|
|
|
|
1. ✅ Schema installed
|
|
2. Import Goergens data (74,000+ cameras)
|
|
3. Import Photo Antiquaria articles
|
|
4. Add images and documents
|
|
5. Develop Flask web application
|
|
|
|
## Troubleshooting
|
|
|
|
### "relation already exists"
|
|
Schema already partially installed. Either:
|
|
- Drop database and recreate: `DROP DATABASE camera_db; CREATE DATABASE camera_db;`
|
|
- Or skip to the file where installation failed
|
|
|
|
### Foreign key violations
|
|
Files must be run in order (01 through 08). Foreign keys depend on tables from earlier files.
|
|
|
|
### Permission denied
|
|
Use postgres superuser: `-U postgres`
|
|
|
|
## Documentation
|
|
|
|
- **Design Document:** /data/camera-database/docs/database-design.md
|
|
- **PostgreSQL Setup:** /data/camera-database/docs/postgresql-setup.md
|
|
- **Goergens Email:** /data/camera-database/docs/goergens-email.txt
|
|
|
|
## Version
|
|
|
|
- Schema Version: 1.0
|
|
- PostgreSQL: 16+
|
|
- Date: November 2025
|
|
- Author: Walter Jekat / Club Daguerre e.V.
|