# 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.