The project imports metadata produced by an existing site scraper, stores it in a SQLite database, and enriches new records with metadata extracted from thumbnail images using Google Cloud Vision OCR.

Current processing flow

WCX site
   ↓
existing scraper
   ↓
import/wcx_site_index.json
   ↓
scripts/import_site.py
   ↓
database/wcx.db
   ↓
scripts/check_ocr.py
   ├── scripts/ocr.sh
   └── scripts/parse_ocr.py
   ↓
OCR metadata stored in SQLite

The current implementation supports:

inserting new movies from the scraper JSON
updating existing movies based on the site's update field
storing movie duration as seconds
running OCR for an individual movie
parsing and validating OCR output
comparing the OCR name with the database name
storing OCR metadata, raw OCR text, status, and errors

Directory structure

/storage/disk1/WCX/
├── database/
│   └── wcx.db
├── import/
│   └── wcx_site_index.json
├── scripts/
│   ├── schema.sql
│   ├── import_site.py
│   ├── ocr.sh
│   ├── parse_ocr.py
│   └── check_ocr.py
├── .gitignore
└── README.md

Requirements

The project currently uses:

Python 3
SQLite 3
Bash
curl
jq
base64
Google Cloud Vision API access

Install the local command-line dependencies on Ubuntu or Debian:

sudo apt update
sudo apt install sqlite3 jq curl

The Python scripts only use modules from the Python standard library.

Google Vision API key

The OCR script expects the Google Vision API key in the environment:

export GOOGLE_VISION_API_KEY="your-api-key"

The key must be available in the environment whenever ocr.sh or check_ocr.py is executed.

Do not commit the API key to Git.

Creating the database

Create the database directory:

mkdir -p /storage/disk1/WCX/database

Create the database from the schema:

sqlite3 /storage/disk1/WCX/database/wcx.db \
  < /storage/disk1/WCX/scripts/schema.sql

Inspect the resulting table:

sqlite3 /storage/disk1/WCX/database/wcx.db ".schema movie"

Database model

The movie table currently contains the following columns:

Column	Description
`id`	Stable and unique ID from the site
`name`	Movie title
`aka`	Alternative name
`nationality`	Nationality extracted by OCR or edited manually
`age`	Manually maintained age
`shoot_location`	Shoot location extracted by OCR or edited manually
`shoot_date`	Shoot date stored as `YYYY-MM-DD`
`duration_seconds`	Movie duration stored in seconds
`description`	Manually maintained description
`rating`	Manually maintained rating
`web_url`	URL to the movie detail page
`thumbnail`	URL to the thumbnail image
`published`	Original publication date
`updated`	Latest site update date
`created_at`	Timestamp when the database record was created
`modified_at`	Timestamp when the record was last modified
`ocr_status`	Current OCR processing status
`ocr_raw_text`	Unmodified OCR text returned by Google Vision
`ocr_error`	OCR or validation error
`ocr_processed_at`	Timestamp of the latest OCR attempt

Possible OCR statuses are currently:

pending
completed
failed
manual_review

Site JSON input

The site importer reads:

/storage/disk1/WCX/import/wcx_site_index.json

The file must contain a JSON array.

Example entry:

{
  "id": "sweet-cherry_41678",
  "titel": "Sweet Cherry",
  "details": "https://www.woodmancastingx.com/casting-x/sweet-cherry_41678.html",
  "duration": "1:37:00",
  "thumb": "https://example.com/thumbnail.jpg",
  "published": "2026-07-12"
}

An updated publication may also contain:

{
  "update": "2026-07-15"
}

The importer accepts both update and updated.

Site field mapping

JSON field	Database column
`id`	`id`
`titel`	`name`
`details`	`web_url`
`duration`	`duration_seconds`
`thumb`	`thumbnail`
`published`	`published`
`update` or `updated`	`updated`

Durations are converted to seconds.

Examples:

37:35   → 2255
1:37:00 → 5820

Importing site data

Make the importer executable:

chmod +x /storage/disk1/WCX/scripts/import_site.py

Run the import:

/storage/disk1/WCX/scripts/import_site.py

Example output:

Poster i JSON: 1250
Tillagda:       3
Uppdaterade:    1
Oförändrade:    1246

Site import rules

New movie

If the movie ID does not exist in the database, a new row is inserted.

New rows receive the default OCR status:

pending

Existing movie without a site update

If the movie already exists and the site entry has no update date, no database changes are made.

Existing movie with a site update

An existing row is updated when:

the database has no updated value and the site does, or
the site's update date is newer than the database value

When an update is triggered, all fields supplied by the site are replaced:

name
duration_seconds
web_url
thumbnail
published
updated

The movie ID and manually maintained metadata fields are left unchanged.

Dates use the ISO format YYYY-MM-DD, allowing chronological comparison as text.

Inspecting the database

Show the total number of movies:

sqlite3 /storage/disk1/WCX/database/wcx.db \
  "SELECT COUNT(*) FROM movie;"

Show some movies:

sqlite3 -header -column /storage/disk1/WCX/database/wcx.db \
  "SELECT id, name, duration_seconds, published, updated
   FROM movie
   LIMIT 10;"

Show a specific movie:

sqlite3 -header -column /storage/disk1/WCX/database/wcx.db \
  "SELECT *
   FROM movie
   WHERE id = 'susana-melo_6707';"

OCR processing

OCR processing is currently performed for one movie at a time.

The complete flow is:

movie ID
   ↓
check_ocr.py
   ↓
thumbnail URL loaded from SQLite
   ↓
ocr.sh
   ↓
Google Cloud Vision raw text
   ↓
parse_ocr.py
   ↓
name validation
   ↓
SQLite update

Raw OCR script

Run OCR directly against an image URL:

/storage/disk1/WCX/scripts/ocr.sh \
  "https://example.com/thumbnail.jpg"

Example output:

SUSANA MELO
PORTUGUESE
WOODMAN CASTINGX.COM
Budapest (Hungary), March 9, 2014

The raw OCR output is not considered a stable data format and must be parsed and validated before metadata is stored.

Expected OCR structure

The parser currently expects exactly four non-empty lines:

Line 1: Performer name
Line 2: Nationality
Line 3: Branding text, ignored
Line 4: Shoot location and shoot date

Example final line:

Budapest (Hungary), March 9, 2014

The line is split on the first comma:

shoot_location = Budapest (Hungary)
shoot_date     = March 9, 2014

The date is normalized before storage:

March 9, 2014 → 2014-03-09

Testing the OCR parser

Raw OCR output can be piped directly into the parser:

/storage/disk1/WCX/scripts/ocr.sh \
  "https://example.com/thumbnail.jpg" \
  | /storage/disk1/WCX/scripts/parse_ocr.py

Example result:

ocr_name=SUSANA MELO
nationality=PORTUGUESE
shoot_location=Budapest (Hungary)
shoot_date=2014-03-09

Processing OCR for a movie

Run the complete OCR workflow using a movie ID:

/storage/disk1/WCX/scripts/check_ocr.py susana-melo_6707

Example output:

id=susana-melo_6707
database_name=Susana Melo
ocr_name=SUSANA MELO
name_match=yes
nationality=PORTUGUESE
shoot_location=Budapest (Hungary)
shoot_date=2014-03-09
ocr_status=completed

The name comparison is case-insensitive and ignores repeated whitespace.

For example:

Susana Melo
SUSANA MELO

are considered equal.

Successful OCR processing

When OCR execution and parsing succeed and the OCR name matches the database name, the following fields are updated:

nationality
shoot_location
shoot_date
ocr_raw_text
ocr_status = completed
ocr_error = NULL
ocr_processed_at
modified_at

OCR failure handling

If the Google Vision request or ocr.sh execution fails:

ocr_status = failed

The error is stored in:

ocr_error

Manual review handling

The movie is marked for manual review when:

the OCR output cannot be parsed
the expected fields are missing
the OCR name does not match the database name
the OCR output has an unexpected structure

In this case:

ocr_status = manual_review

The raw OCR text is retained in ocr_raw_text, but the parsed metadata fields are not updated.

This prevents uncertain OCR output from silently replacing valid metadata.

Inspecting OCR results

Show the structured OCR result:

sqlite3 -header -column /storage/disk1/WCX/database/wcx.db \
  "SELECT
       id,
       name,
       nationality,
       shoot_location,
       shoot_date,
       ocr_status,
       ocr_error,
       ocr_processed_at
   FROM movie
   WHERE id = 'susana-melo_6707';"

Show the raw OCR text:

sqlite3 /storage/disk1/WCX/database/wcx.db \
  "SELECT ocr_raw_text
   FROM movie
   WHERE id = 'susana-melo_6707';"

List movies waiting for OCR:

sqlite3 -header -column /storage/disk1/WCX/database/wcx.db \
  "SELECT id, name, ocr_status
   FROM movie
   WHERE ocr_status = 'pending'
   ORDER BY published DESC;"

List movies requiring manual review:

sqlite3 -header -column /storage/disk1/WCX/database/wcx.db \
  "SELECT id, name, ocr_error
   FROM movie
   WHERE ocr_status = 'manual_review';"

List failed OCR attempts:

sqlite3 -header -column /storage/disk1/WCX/database/wcx.db \
  "SELECT id, name, ocr_error
   FROM movie
   WHERE ocr_status = 'failed';"

Recreating the database

During development, the database can be recreated from the schema and site JSON:

rm /storage/disk1/WCX/database/wcx.db

sqlite3 /storage/disk1/WCX/database/wcx.db \
  < /storage/disk1/WCX/scripts/schema.sql

/storage/disk1/WCX/scripts/import_site.py

Do not recreate the database this way after it contains manually maintained metadata unless a backup has been created first.

OCR data must also be recreated if the database is deleted.

OCR batch processing

Process all movies waiting for OCR:

scripts/process_pending_ocr.py

Limit the number of records:

scripts/process_pending_ocr.py --limit 5

Use a test database:

scripts/process_pending_ocr.py \
  --database database/wcx-test.db \
  --limit 1

The script selects movies with ocr_status = 'pending' and invokes check_ocr.py once per movie. Processing continues if an individual movie fails.

Possible results are:

completed manual_review failed

A summary is printed after the batch has finished.

GOOGLE_VISION_API_KEY must be available in the environment.

Legacy CSV migration

The historical index is stored in:

migration/persons.utf8bom.csv

This file is version-controlled and can be used to restore manually maintained metadata that is not available from the WCX site or thumbnail OCR.

The migration source contains:

stable movie IDs
manually corrected names and aliases
nationality, age, shoot location, and shoot date
duration
descriptions and ratings
site URLs, thumbnail URLs, and publication dates

The movie ID corresponds to the filename portion of WEBURL without the .html extension.

Example:

WEBURL: https://www.woodmancastingx.com/casting-x/ysana_268.html
ID:     ysana_268

Migration script

The migration is performed by:

scripts/migrate_csv.py

The script:

reads UTF-8 CSV files with BOM support
validates required headers
validates ISO dates
converts durations to seconds
converts historical age notation to whole years
trims surrounding whitespace
verifies that ID matches the ID derived from WEBURL
updates existing database rows
inserts CSV rows that are missing from the site-derived database
skips rows without an ID
runs database writes in a transaction

Historical age values may include additional month or week notation:

21.2          → 21
18,1          → 18
19,1w         → 19
18 (same day) → 18
?             → NULL

Only whole years are stored in the database.

Dry-run validation

Always validate the migration source before applying it:

/storage/disk1/WCX/scripts/migrate_csv.py \
  --database /storage/disk1/WCX/database/wcx-test.db \
  --dry-run

A limited validation run can be performed with:

/storage/disk1/WCX/scripts/migrate_csv.py \
  --database /storage/disk1/WCX/database/wcx-test.db \
  --limit 20 \
  --dry-run

The report includes:

rows read
valid and invalid rows
duplicate IDs
rows already present in the database
rows missing from the database
ID and WEBURL mismatches
warnings and errors

Rows without an ID are skipped and reported as warnings.

Test migration

Create a test database from the current database:

cp /storage/disk1/WCX/database/wcx.db \
   /storage/disk1/WCX/database/wcx-test.db

Apply the migration to the test database:

/storage/disk1/WCX/scripts/migrate_csv.py \
  --database /storage/disk1/WCX/database/wcx-test.db \
  --apply

Inspect the test database before applying the migration to the main database.

Applying the migration

Create a backup first:

cp /storage/disk1/WCX/database/wcx.db \
   /storage/disk1/WCX/database/wcx-before-csv-migration.db

Apply the migration:

/storage/disk1/WCX/scripts/migrate_csv.py \
  --database /storage/disk1/WCX/database/wcx.db \
  --apply

Existing rows are updated from the CSV metadata. A CSV row whose ID is not present in the site-derived database is inserted. This allows locally retained movies to remain in the index even if their publication has been removed from the site.

Migrated rows with complete OCR-related metadata receive:

ocr_status = completed

Rows with incomplete OCR-related metadata receive:

ocr_status = manual_review

New site publications that are not present in the historical CSV retain:

ocr_status = pending

The migration is intended primarily for initial population or database restoration. Repeated execution is possible, but it will overwrite the corresponding movie metadata with values from the version-controlled CSV source.

Git and generated data

The Git repository contains source code, schema, and documentation.

The SQLite database and scraper output are runtime data and are not version-controlled.

Current .gitignore rules:

# SQLite database files
database/*.db
database/*.db-shm
database/*.db-wal

# Imported/generated data
import/*.json

# Python cache
__pycache__/
*.pyc

Files that should be version-controlled include:

README.md
.gitignore
scripts/schema.sql
scripts/import_site.py
scripts/ocr.sh
scripts/parse_ocr.py
scripts/check_ocr.py

Files that should not be version-controlled include:

database/wcx.db
database/wcx.db-shm
database/wcx.db-wal
import/wcx_site_index.json

Current limitations

The current OCR parser assumes a four-line OCR result.

Google Cloud Vision does not guarantee this exact structure. Unexpected output is therefore stored for manual review rather than automatically accepted.

OCR processing is currently started manually for one movie ID at a time.

Scheduled updates

WCX is updated automatically by a systemd timer.

The timer runs every night around 03:30. The wrapper script only performs a full update when at least approximately 48 hours have passed since the previous successful scheduled run.

Check the timer:

systemctl list-timers wcx-update.timer

Follow the service log:

sudo journalctl -u wcx-update.service -f

Show the latest service output:

sudo journalctl -u wcx-update.service -n 100 --no-pager

Run the scheduled service manually:

sudo systemctl start wcx-update.service

Check its status:

systemctl status wcx-update.service --no-pager

The timestamp of the latest successful scheduled update is stored in:

database/last_scheduled_update