70 lines
2.6 KiB
Python
70 lines
2.6 KiB
Python
"""Add original_filename column to content table.
|
|
|
|
This preserves the pristine uploaded filename even after a player edits the
|
|
content (which repoints content.filename at the edited_media/<id>/... file).
|
|
Without it the original image appears "overwritten" because the UI can no
|
|
longer tell which file was the original upload.
|
|
"""
|
|
import sys
|
|
sys.path.insert(0, '/app')
|
|
|
|
from app.app import create_app
|
|
from app.extensions import db
|
|
from sqlalchemy import text
|
|
|
|
app = create_app()
|
|
|
|
with app.app_context():
|
|
print("Adding 'original_filename' column to content table...")
|
|
try:
|
|
db.session.execute(text("ALTER TABLE content ADD COLUMN original_filename VARCHAR(255)"))
|
|
db.session.commit()
|
|
print("✓ 'original_filename' column added to content table.")
|
|
except Exception as e:
|
|
if 'duplicate column' in str(e).lower() or 'already exists' in str(e).lower():
|
|
print("✓ 'original_filename' column already exists, skipping.")
|
|
else:
|
|
print(f"✗ Error: {e}")
|
|
raise
|
|
|
|
# Backfill: for content whose filename already points at an edited_media/...
|
|
# file, recover the pristine original from the first (v1) PlayerEdit record.
|
|
print("Backfilling original_filename from PlayerEdit v1 records...")
|
|
try:
|
|
db.session.execute(text("""
|
|
UPDATE content
|
|
SET original_filename = (
|
|
SELECT pe.original_name
|
|
FROM player_edit pe
|
|
WHERE pe.content_id = content.id
|
|
ORDER BY pe.version ASC, pe.created_at ASC
|
|
LIMIT 1
|
|
)
|
|
WHERE original_filename IS NULL
|
|
AND filename LIKE 'edited_media/%'
|
|
AND EXISTS (SELECT 1 FROM player_edit pe2 WHERE pe2.content_id = content.id)
|
|
"""))
|
|
db.session.commit()
|
|
print("✓ Backfilled original_filename from PlayerEdit records.")
|
|
except Exception as e:
|
|
print(f"✗ Backfill error (non-fatal): {e}")
|
|
db.session.rollback()
|
|
|
|
# Backfill fallback: any remaining content with a plain filename is itself
|
|
# the original.
|
|
print("Backfilling original_filename = filename for remaining content...")
|
|
try:
|
|
db.session.execute(text("""
|
|
UPDATE content
|
|
SET original_filename = filename
|
|
WHERE original_filename IS NULL
|
|
AND filename NOT LIKE 'edited_media/%'
|
|
"""))
|
|
db.session.commit()
|
|
print("✓ Backfilled original_filename = filename.")
|
|
except Exception as e:
|
|
print(f"✗ Backfill error (non-fatal): {e}")
|
|
db.session.rollback()
|
|
|
|
print("Migration complete.")
|