798 lines
17 KiB
Markdown
798 lines
17 KiB
Markdown
# WCX Index
|
|
|
|
Local SQLite-based index for WCX publications.
|
|
|
|
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
|
|
|
|
```text
|
|
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
|
|
|
|
```text
|
|
/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:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
mkdir -p /storage/disk1/WCX/database
|
|
```
|
|
|
|
Create the database from the schema:
|
|
|
|
```bash
|
|
sqlite3 /storage/disk1/WCX/database/wcx.db \
|
|
< /storage/disk1/WCX/scripts/schema.sql
|
|
```
|
|
|
|
Inspect the resulting table:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```text
|
|
pending
|
|
completed
|
|
failed
|
|
manual_review
|
|
```
|
|
|
|
## Site JSON input
|
|
|
|
The site importer reads:
|
|
|
|
```text
|
|
/storage/disk1/WCX/import/wcx_site_index.json
|
|
```
|
|
|
|
The file must contain a JSON array.
|
|
|
|
Example entry:
|
|
|
|
```json
|
|
{
|
|
"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:
|
|
|
|
```json
|
|
{
|
|
"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:
|
|
|
|
```text
|
|
37:35 → 2255
|
|
1:37:00 → 5820
|
|
```
|
|
|
|
## Importing site data
|
|
|
|
Make the importer executable:
|
|
|
|
```bash
|
|
chmod +x /storage/disk1/WCX/scripts/import_site.py
|
|
```
|
|
|
|
Run the import:
|
|
|
|
```bash
|
|
/storage/disk1/WCX/scripts/import_site.py
|
|
```
|
|
|
|
Example output:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```bash
|
|
sqlite3 /storage/disk1/WCX/database/wcx.db \
|
|
"SELECT COUNT(*) FROM movie;"
|
|
```
|
|
|
|
Show some movies:
|
|
|
|
```bash
|
|
sqlite3 -header -column /storage/disk1/WCX/database/wcx.db \
|
|
"SELECT id, name, duration_seconds, published, updated
|
|
FROM movie
|
|
LIMIT 10;"
|
|
```
|
|
|
|
Show a specific movie:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```bash
|
|
/storage/disk1/WCX/scripts/ocr.sh \
|
|
"https://example.com/thumbnail.jpg"
|
|
```
|
|
|
|
Example output:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
Line 1: Performer name
|
|
Line 2: Nationality
|
|
Line 3: Branding text, ignored
|
|
Line 4: Shoot location and shoot date
|
|
```
|
|
|
|
Example final line:
|
|
|
|
```text
|
|
Budapest (Hungary), March 9, 2014
|
|
```
|
|
|
|
The line is split on the first comma:
|
|
|
|
```text
|
|
shoot_location = Budapest (Hungary)
|
|
shoot_date = March 9, 2014
|
|
```
|
|
|
|
The date is normalized before storage:
|
|
|
|
```text
|
|
March 9, 2014 → 2014-03-09
|
|
```
|
|
|
|
## Testing the OCR parser
|
|
|
|
Raw OCR output can be piped directly into the parser:
|
|
|
|
```bash
|
|
/storage/disk1/WCX/scripts/ocr.sh \
|
|
"https://example.com/thumbnail.jpg" \
|
|
| /storage/disk1/WCX/scripts/parse_ocr.py
|
|
```
|
|
|
|
Example result:
|
|
|
|
```text
|
|
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:
|
|
|
|
```bash
|
|
/storage/disk1/WCX/scripts/check_ocr.py susana-melo_6707
|
|
```
|
|
|
|
Example output:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
ocr_status = failed
|
|
```
|
|
|
|
The error is stored in:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
sqlite3 /storage/disk1/WCX/database/wcx.db \
|
|
"SELECT ocr_raw_text
|
|
FROM movie
|
|
WHERE id = 'susana-melo_6707';"
|
|
```
|
|
|
|
List movies waiting for OCR:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
scripts/process_pending_ocr.py
|
|
```
|
|
Limit the number of records:
|
|
```bash
|
|
scripts/process_pending_ocr.py --limit 5
|
|
```
|
|
Use a test database:
|
|
```bash
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
WEBURL: https://www.woodmancastingx.com/casting-x/ysana_268.html
|
|
ID: ysana_268
|
|
```
|
|
|
|
### Migration script
|
|
|
|
The migration is performed by:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```bash
|
|
/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:
|
|
|
|
```bash
|
|
/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:
|
|
|
|
```bash
|
|
cp /storage/disk1/WCX/database/wcx.db \
|
|
/storage/disk1/WCX/database/wcx-test.db
|
|
```
|
|
|
|
Apply the migration to the test database:
|
|
|
|
```bash
|
|
/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:
|
|
|
|
```bash
|
|
cp /storage/disk1/WCX/database/wcx.db \
|
|
/storage/disk1/WCX/database/wcx-before-csv-migration.db
|
|
```
|
|
|
|
Apply the migration:
|
|
|
|
```bash
|
|
/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:
|
|
|
|
```text
|
|
ocr_status = completed
|
|
```
|
|
|
|
Rows with incomplete OCR-related metadata receive:
|
|
|
|
```text
|
|
ocr_status = manual_review
|
|
```
|
|
|
|
New site publications that are not present in the historical CSV retain:
|
|
|
|
```text
|
|
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:
|
|
|
|
```gitignore
|
|
# 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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```text
|
|
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:
|
|
|
|
```bash
|
|
systemctl list-timers wcx-update.timer
|
|
```
|
|
|
|
Follow the service log:
|
|
```bash
|
|
sudo journalctl -u wcx-update.service -f
|
|
```
|
|
Show the latest service output:
|
|
```bash
|
|
sudo journalctl -u wcx-update.service -n 100 --no-pager
|
|
````
|
|
Run the scheduled service manually:
|
|
```bash
|
|
sudo systemctl start wcx-update.service
|
|
```
|
|
Check its status:
|
|
```bash
|
|
systemctl status wcx-update.service --no-pager
|
|
```
|
|
The timestamp of the latest successful scheduled update is stored in:
|
|
|
|
database/last_scheduled_update
|
|
|