new_dbsync/README.md

# DBSyncer - MongoDB to PostgreSQL Data Synchronization

A comprehensive data synchronization tool that:
1. Downloads Television data from the TVMaze API and stores it in MongoDB
2. Automatically generates PostgreSQL table schemas from MongoDB document structures
3. Efficiently inserts/updates MongoDB documents into PostgreSQL with intelligent type conversion

## Features

### Schema Generation (`db/schema_generator.py`)
- **Automatic Schema Inference**: Analyzes MongoDB documents to infer PostgreSQL types
- **Type Mapping**: Intelligent conversion between MongoDB and PostgreSQL types:
  - Objects/Arrays → JSONB
  - Dates → TIMESTAMP
  - Numbers → BIGINT / DOUBLE PRECISION
  - Strings → TEXT

### Document Insertion
- **Batch Processing**: Efficiently inserts documents in configurable batches (default: 1000)
- **Type Conversion**: Automatic type conversion for:
  - Epoch timestamps ↔ ISO datetime strings
  - JSON strings ↔ Python dict/list
  - ObjectId → Text
- **UPSERT Support**: Uses PostgreSQL `ON CONFLICT ... DO UPDATE SET` for automatic upserts
- **Error Handling**: Detailed logging with per-row error reporting

## Quick Start

### Prerequisites
```bash
pip install -r requirements.txt
```

### Configuration
Edit `settings/tvsync_settings.cfg`:
```ini
[dbsettings]
hostname = localhost
pgsqlport = 5432
pgsqlusername = postgres
pgsqlpassword = your_password
dbname = media_dbsync
mghostname = localhost
mgport = 27017
mgdbname = media

[database]
dbtype = pgsql
updateschema = updates
```

### Main Scripts

#### `update_mongodb.py`
Fetches TV data from TVMaze API and stores in MongoDB:
```bash
python3 update_mongodb.py
```

#### `mongodb2postgres.py`
Generates PostgreSQL schemas from MongoDB collections and inserts documents:
```bash
python3 mongodb2postgres.py
```

This script will:
1. Analyze each MongoDB collection (series, episodes, actors, characters, crew)
2. Automatically create corresponding PostgreSQL tables in the `updates` schema
3. Insert/upsert all documents with proper type conversions
4. Report progress and any errors

## Usage Examples

### Generate Table Schema from MongoDB Collection
```python
from db.schema_generator import MongoToPostgresSchemaGenerator

generator = MongoToPostgresSchemaGenerator(sample_size=100)
schema = generator.analyze_collection(mongo_db, 'series')

# View the SQL
sql = generator.generate_create_table_sql('seriesdata', schema_name='dbo', pk_field='id')
print(sql)

# Create the table
generator.create_table_in_postgres(engine, 'seriesdata', schema_name='dbo', pk_field='id')
```

### Insert Documents with Type Conversion
```python
from db.schema_generator import MongoDocumentInserter

inserter = MongoDocumentInserter(batch_size=1000)

# From a collection
count = inserter.insert_from_collection(
    engine=engine,
    table_name='seriesdata',
    collection=mongo_db['series'],
    schema_name='dbo',
    on_conflict="ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name, updated=EXCLUDED.updated",
)
print(f"Inserted {count} documents")
```

## Architecture

### Database Classes (`db/functions.py`)
- `settype`: Manages database engine creation and API configuration
- `dbmongo`: Handles MongoDB connections and operations
- `Dbexec`: Executes raw SQL queries and batch updates

### Schema Generator (`db/schema_generator.py`)
- `MongoToPostgresSchemaGenerator`: Infers and creates table schemas
- `MongoDocumentInserter`: Handles document insertion with type conversion

### API Integration (`api/`)
- TVMaze API client for fetching show, episode, cast, and crew data
- TheTVDB API client (optional)

## Performance Optimizations

- **Batch Inserts**: Uses SQLAlchemy's parameterized queries for efficient batch operations
- **Transactional Inserts**: Groups inserts in transactions to reduce database overhead
- **Column Type Awareness**: Converts data to match target column types, avoiding casting overhead
- **Connection Pooling**: Reuses database connections (pool_size=20, max_overflow=20)
- **UPSERT Operations**: Uses PostgreSQL's `ON CONFLICT` for atomic insert-or-update

## Troubleshooting

### MetaData Schema Argument Error
If you see `sqlalchemy.exc.ArgumentError: Could not parse SQLAlchemy URL`, ensure:
- Use `MetaData(schema="updates")` instead of `MetaData("updates")`
- Pass full connection strings to `create_engine()`, not schema names

### Type Conversion Errors
The inserter automatically handles:
- Datetime strings → epoch timestamps (for BIGINT columns)
- Epoch ints → datetime objects (for TIMESTAMP columns)
- JSON strings → Python dicts (for JSONB columns)

If type errors persist, check that:
1. Table schema matches MongoDB document fields
2. Column types are correctly inferred from sample documents
3. Use appropriate `on_conflict` clauses for upserts

### Column Name Case Sensitivity
PostgreSQL stores unquoted identifiers as lowercase. The inserter automatically:
- Lowercases column names in `ON CONFLICT` clauses
- Uses case-insensitive matching when mapping MongoDB fields to PostgreSQL columns

## Contributing

When modifying:
- **Schema inference**: Update `MongoToPostgresSchemaGenerator.TYPE_MAPPING` for new type support
- **Type conversion**: Extend `MongoDocumentInserter.convert_value()` for custom conversions
- **Database operations**: Use parameterized queries via `text()` to prevent SQL injection
- **Error handling**: Add specific exception types to improve debugging

## License

See LICENSE file for details.