chess-baackend / scripts /migrate_json_to_postgres.py
Magar077's picture
Clean deploy for HF
f0c0e92
Raw History Blame Contribute Delete
24.1 kB
"""Migration script: import legacy JSON/log state into Postgres.
Usage:
set DATABASE_URL=postgresql+psycopg2://user:pass@host:5432/chess_db
python scripts/migrate_json_to_postgres.py
"""
import os
import sys
import json
import mimetypes
import tempfile
from uuid import uuid5, NAMESPACE_URL
from sqlalchemy import create_engine, text
PROJECT_ROOT = os.path.normpath(os.path.join(os.path.dirname(__file__), '..'))
if PROJECT_ROOT not in sys.path:
sys.path.insert(0, PROJECT_ROOT)
DATABASE_URL = os.getenv('DATABASE_URL')
if not DATABASE_URL:
print('ERROR: DATABASE_URL not set')
sys.exit(1)
engine = create_engine(DATABASE_URL, future=True)
def load_json(path):
try:
with open(path, 'r', encoding='utf-8') as f:
raw = f.read().strip()
if not raw:
print(f'Warning: {path} is empty; skipping')
return None
try:
return json.loads(raw)
except json.JSONDecodeError:
# Attempt to recover JSON from files that have accidental text prefixes.
starts = [idx for idx in (raw.find('{'), raw.find('[')) if idx != -1]
if not starts:
raise
start = min(starts)
return json.loads(raw[start:])
except FileNotFoundError:
print(f'Warning: {path} not found; skipping')
return None
def ensure_tables():
sql_path = os.path.join(os.path.dirname(__file__), '..', 'sql', 'create_tables.sql')
sql_path = os.path.normpath(sql_path)
with open(sql_path, 'r', encoding='utf-8') as f:
sql = f.read()
with engine.begin() as conn:
conn.execute(text(sql))
def migrate_users():
users = load_json(os.path.join(os.getcwd(), 'users.json'))
if not users:
return
with engine.begin() as conn:
for username, data in users.items():
stmt = text("""
INSERT INTO users (
username, email, first_name, last_name, password_hash,
profile_image, bio, fcm_token, friends, friend_requests, profile
)
VALUES (
:username, :email, :first_name, :last_name, :password_hash,
:profile_image, :bio, :fcm_token, CAST(:friends AS JSONB), CAST(:friend_requests AS JSONB), CAST(:profile AS JSONB)
)
ON CONFLICT (username) DO UPDATE SET
email = EXCLUDED.email,
first_name = EXCLUDED.first_name,
last_name = EXCLUDED.last_name,
password_hash = EXCLUDED.password_hash,
profile_image = EXCLUDED.profile_image,
bio = EXCLUDED.bio,
fcm_token = EXCLUDED.fcm_token,
friends = EXCLUDED.friends,
friend_requests = EXCLUDED.friend_requests,
profile = EXCLUDED.profile
""")
profile = json.dumps({
'bio': data.get('bio'),
'profile_image': data.get('profile_image')
})
conn.execute(stmt, {
'username': username,
'email': data.get('email'),
'first_name': data.get('first_name'),
'last_name': data.get('last_name'),
'password_hash': data.get('password_hash') or None,
'profile_image': data.get('profile_image'),
'bio': data.get('bio', ''),
'fcm_token': data.get('fcm_token'),
'friends': json.dumps(data.get('friends', [])),
'friend_requests': json.dumps(data.get('friend_requests', [])),
'profile': profile,
})
def migrate_messages():
msgs = load_json(os.path.join(os.getcwd(), 'messages.json'))
if not msgs:
return
# messages.json format may be a list or dict; handle common shapes
items = []
if isinstance(msgs, dict):
# try 'messages' key
items = msgs.get('messages') or []
if not items:
# dict of message_id -> message_object
for value in msgs.values():
if isinstance(value, dict):
items.append(value)
elif isinstance(value, list):
items.extend(value)
elif isinstance(msgs, list):
items = msgs
with engine.begin() as conn:
for m in items:
try:
conn.execute(text(
"""
INSERT INTO messages (
id, sender, receiver, message_type, text,
media_url, thumbnail_url, reply_to_id, status,
is_read, reactions, metadata, timestamp
) VALUES (
:id, :s, :r, :t, :txt,
:media_url, :thumbnail_url, :reply_to_id, :status,
:is_read, CAST(:reactions AS JSONB), CAST(:meta AS JSONB), :timestamp
)
ON CONFLICT (id) DO UPDATE SET
sender = EXCLUDED.sender,
receiver = EXCLUDED.receiver,
message_type = EXCLUDED.message_type,
text = EXCLUDED.text,
media_url = EXCLUDED.media_url,
thumbnail_url = EXCLUDED.thumbnail_url,
reply_to_id = EXCLUDED.reply_to_id,
status = EXCLUDED.status,
is_read = EXCLUDED.is_read,
reactions = EXCLUDED.reactions,
metadata = EXCLUDED.metadata,
timestamp = EXCLUDED.timestamp
"""
), {
'id': m.get('id'),
's': m.get('sender'),
'r': m.get('receiver'),
't': m.get('message_type'),
'txt': m.get('text'),
'media_url': m.get('media_url') or m.get('media_path'),
'thumbnail_url': m.get('thumbnail_url'),
'reply_to_id': m.get('reply_to_id'),
'status': m.get('status') or ('seen' if m.get('is_read') else 'sent'),
'is_read': bool(m.get('is_read', False)),
'reactions': json.dumps(m.get('reactions', {})),
'meta': json.dumps(m.get('metadata', {})),
'timestamp': m.get('timestamp'),
})
except Exception as e:
print('Warning: failed to insert message', e)
def migrate_groups():
groups = load_json(os.path.join(os.getcwd(), 'groups.json'))
if not groups:
return
if isinstance(groups, dict):
group_items = list(groups.values())
elif isinstance(groups, list):
group_items = groups
else:
return
with engine.begin() as conn:
for group in group_items:
try:
conn.execute(text("""
INSERT INTO groups (
id, name, avatar, created_by, admins, members,
created_at, updated_at, last_message, last_message_time
) VALUES (
:id, :name, :avatar, :created_by,
CAST(:admins AS JSONB), CAST(:members AS JSONB),
:created_at, :updated_at, :last_message, :last_message_time
)
ON CONFLICT (id) DO NOTHING
"""), {
'id': group.get('id'),
'name': group.get('name'),
'avatar': group.get('avatar'),
'created_by': group.get('created_by'),
'admins': json.dumps(group.get('admins', [])),
'members': json.dumps(group.get('members', [])),
'created_at': group.get('created_at'),
'updated_at': group.get('updated_at'),
'last_message': group.get('last_message'),
'last_message_time': group.get('last_message_time'),
})
except Exception as exc:
print('Warning: failed to insert group', exc)
def migrate_group_messages():
group_messages = load_json(os.path.join(os.getcwd(), 'group_messages.json'))
if not group_messages:
return
if isinstance(group_messages, dict):
items = list(group_messages.values())
elif isinstance(group_messages, list):
items = group_messages
else:
return
with engine.begin() as conn:
for message in items:
try:
conn.execute(text("""
INSERT INTO group_messages (
id, group_id, sender, text, message_type,
media_url, thumbnail_url, timestamp, seen_by
) VALUES (
:id, :group_id, :sender, :text, :message_type,
:media_url, :thumbnail_url, :timestamp, CAST(:seen_by AS JSONB)
)
ON CONFLICT (id) DO NOTHING
"""), {
'id': message.get('id'),
'group_id': message.get('group_id'),
'sender': message.get('sender'),
'text': message.get('text'),
'message_type': message.get('message_type'),
'media_url': message.get('media_url'),
'thumbnail_url': message.get('thumbnail_url'),
'timestamp': message.get('timestamp'),
'seen_by': json.dumps(message.get('seen_by', [])),
})
except Exception as exc:
print('Warning: failed to insert group message', exc)
def migrate_group_call_history():
history = load_json(os.path.join(os.getcwd(), 'group_call_history.json'))
if not history:
return
if isinstance(history, dict):
items = list(history.values())
elif isinstance(history, list):
items = history
else:
return
with engine.begin() as conn:
for call in items:
try:
conn.execute(text("""
INSERT INTO group_call_history (
id, group_id, started_by, call_type, participants,
status, started_at, ended_at
) VALUES (
:id, :group_id, :started_by, :call_type, CAST(:participants AS JSONB),
:status, :started_at, :ended_at
)
ON CONFLICT (id) DO NOTHING
"""), {
'id': call.get('id'),
'group_id': call.get('group_id'),
'started_by': call.get('started_by'),
'call_type': call.get('call_type'),
'participants': json.dumps(call.get('participants', [])),
'status': call.get('status'),
'started_at': call.get('started_at'),
'ended_at': call.get('ended_at'),
})
except Exception as exc:
print('Warning: failed to insert call history', exc)
def migrate_stories():
stories = load_json(os.path.join(os.getcwd(), 'stories.json'))
if not stories:
return
if isinstance(stories, dict):
items = list(stories.values())
elif isinstance(stories, list):
items = stories
else:
return
with engine.begin() as conn:
for story in items:
try:
conn.execute(text("""
INSERT INTO stories (
id, username, media_url, thumbnail_url, media_type,
timestamp, viewers, reactions, reaction_details
) VALUES (
:id, :username, :media_url, :thumbnail_url, :media_type,
:timestamp, CAST(:viewers AS JSONB), CAST(:reactions AS JSONB), CAST(:reaction_details AS JSONB)
)
ON CONFLICT (id) DO NOTHING
"""), {
'id': story.get('id'),
'username': story.get('username'),
'media_url': story.get('media_url'),
'thumbnail_url': story.get('thumbnail_url'),
'media_type': story.get('media_type'),
'timestamp': story.get('timestamp'),
'viewers': json.dumps(story.get('viewers', [])),
'reactions': json.dumps(story.get('reactions', {})),
'reaction_details': json.dumps(story.get('reaction_details', {})),
})
except Exception as exc:
print('Warning: failed to insert story', exc)
def migrate_notes():
notes = load_json(os.path.join(os.getcwd(), 'notes.json'))
if not notes:
return
if isinstance(notes, dict):
items = list(notes.values())
elif isinstance(notes, list):
items = notes
else:
return
with engine.begin() as conn:
for note in items:
try:
conn.execute(text("""
INSERT INTO notes (
id, username, text_content, media_url, thumbnail_url,
media_type, timestamp, viewers
) VALUES (
:id, :username, :text_content, :media_url, :thumbnail_url,
:media_type, :timestamp, CAST(:viewers AS JSONB)
)
ON CONFLICT (id) DO NOTHING
"""), {
'id': note.get('id'),
'username': note.get('username'),
'text_content': note.get('text_content'),
'media_url': note.get('media_url'),
'thumbnail_url': note.get('thumbnail_url'),
'media_type': note.get('media_type'),
'timestamp': note.get('timestamp'),
'viewers': json.dumps(note.get('viewers', [])),
})
except Exception as exc:
print('Warning: failed to insert note', exc)
def migrate_game_rooms():
rooms_dir = os.path.join(os.getcwd(), 'data', 'games')
if not os.path.isdir(rooms_dir):
return
with engine.begin() as conn:
for filename in os.listdir(rooms_dir):
if not filename.endswith('.json'):
continue
room_path = os.path.join(rooms_dir, filename)
room_data = load_json(room_path)
if not room_data:
continue
try:
conn.execute(text("""
INSERT INTO game_rooms (room_id, room_data)
VALUES (:room_id, CAST(:room_data AS JSONB))
ON CONFLICT (room_id) DO UPDATE SET
room_data = EXCLUDED.room_data,
updated_at = now()
"""), {
'room_id': room_data.get('roomId') or os.path.splitext(filename)[0],
'room_data': json.dumps(room_data),
})
except Exception as exc:
print('Warning: failed to insert game room', exc)
def migrate_audit_log():
audit_path = os.path.join(os.getcwd(), 'logs', 'audit.log')
if not os.path.exists(audit_path):
return
with open(audit_path, 'r', encoding='utf-8') as f, engine.begin() as conn:
for line in f:
line = line.strip()
if not line:
continue
try:
entry = json.loads(line)
conn.execute(text("""
INSERT INTO audit_events (event_type, payload, created_at)
VALUES (:event_type, CAST(:payload AS JSONB), :created_at)
"""), {
'event_type': entry.get('type') or 'legacy',
'payload': json.dumps(entry),
'created_at': entry.get('ts'),
})
except Exception as exc:
print('Warning: failed to insert audit log entry', exc)
def migrate_uploads_media():
uploads_root = os.path.join(os.getcwd(), 'uploads')
if not os.path.isdir(uploads_root):
return
imported = 0
media_map = {}
with engine.begin() as conn:
for root_dir, _, files in os.walk(uploads_root):
for filename in files:
filepath = os.path.join(root_dir, filename)
relpath = os.path.relpath(filepath, uploads_root).replace(os.sep, '/')
category = os.path.dirname(relpath).replace('\\', '/').strip('/') or 'misc'
media_id = str(uuid5(NAMESPACE_URL, f'legacy-media:{relpath}'))
content_type = mimetypes.guess_type(filename)[0] or 'application/octet-stream'
try:
with open(filepath, 'rb') as file_handle:
data = file_handle.read()
conn.execute(text("""
INSERT INTO media_files (id, category, filename, content_type, data)
VALUES (:id, :category, :filename, :content_type, :data)
ON CONFLICT (id) DO UPDATE SET
category = EXCLUDED.category,
filename = EXCLUDED.filename,
content_type = EXCLUDED.content_type,
data = EXCLUDED.data
"""), {
'id': media_id,
'category': category,
'filename': filename,
'content_type': content_type,
'data': data,
})
media_map[f'/uploads/{relpath}'] = media_id
media_map[f'uploads/{relpath}'] = media_id
imported += 1
except Exception as exc:
print('Warning: failed to import upload media', filepath, exc)
if media_map:
for table, columns in (
('messages', ('media_url', 'thumbnail_url')),
('group_messages', ('media_url', 'thumbnail_url')),
('stories', ('media_url', 'thumbnail_url')),
('notes', ('media_url', 'thumbnail_url')),
):
for old_url, media_id in media_map.items():
new_url = f'/media/{media_id}'
for column in columns:
try:
conn.execute(text(f"""
UPDATE {table}
SET {column} = :new_url
WHERE {column} = :old_url
"""), {
'new_url': new_url,
'old_url': old_url,
})
except Exception as exc:
print(f'Warning: failed to update {table}.{column}', exc)
print(f'Imported {imported} media files from uploads/')
def migrate_missing_message_thumbnails():
legacy_rows = []
with engine.begin() as conn:
result = conn.execute(text("""
SELECT id, media_url, thumbnail_url
FROM messages
WHERE position('/uploads/' in coalesce(thumbnail_url, '')) > 0
OR position('uploads/' in coalesce(thumbnail_url, '')) > 0
"""))
legacy_rows = [dict(row._mapping) for row in result]
if not legacy_rows:
return
updated = 0
with engine.begin() as conn:
for row in legacy_rows:
media_url = row.get('media_url') or ''
if not media_url.startswith('/media/'):
continue
media_id = media_url.removeprefix('/media/')
media_row = conn.execute(text("""
SELECT id, category, filename, content_type, data
FROM media_files
WHERE id = :media_id
"""), {'media_id': media_id}).mappings().first()
if not media_row:
continue
filename = media_row.get('filename') or ''
content_type = (media_row.get('content_type') or '').lower()
if not (content_type.startswith('video/') or filename.lower().endswith(('.mp4', '.mov', '.mkv', '.avi', '.webm'))):
continue
thumb_name = f"{os.path.splitext(filename)[0]}_thumb.jpg"
thumb_relpath = f"messages/{thumb_name}"
thumb_id = str(uuid5(NAMESPACE_URL, f'legacy-media-thumb:{thumb_relpath}'))
thumb_url = f'/media/{thumb_id}'
existing_thumb = conn.execute(text("""
SELECT id
FROM media_files
WHERE id = :thumb_id
"""), {'thumb_id': thumb_id}).mappings().first()
if not existing_thumb:
try:
from app.utils import generate_video_thumbnail
except Exception:
generate_video_thumbnail = None
if generate_video_thumbnail is not None:
with tempfile.TemporaryDirectory() as tmpdir:
source_path = os.path.join(tmpdir, filename)
thumb_path = os.path.join(tmpdir, thumb_name)
with open(source_path, 'wb') as source_file:
source_file.write(media_row['data'])
if generate_video_thumbnail(source_path, thumb_path):
with open(thumb_path, 'rb') as thumb_file:
thumb_data = thumb_file.read()
conn.execute(text("""
INSERT INTO media_files (id, category, filename, content_type, data)
VALUES (:id, :category, :filename, :content_type, :data)
ON CONFLICT (id) DO UPDATE SET
category = EXCLUDED.category,
filename = EXCLUDED.filename,
content_type = EXCLUDED.content_type,
data = EXCLUDED.data
"""), {
'id': thumb_id,
'category': media_row.get('category') or 'messages',
'filename': thumb_name,
'content_type': 'image/jpeg',
'data': thumb_data,
})
else:
thumb_url = media_url
else:
thumb_url = media_url
conn.execute(text("""
UPDATE messages
SET thumbnail_url = :thumb_url
WHERE id = :message_id
"""), {
'thumb_url': thumb_url,
'message_id': row['id'],
})
updated += 1
print(f'Backfilled {updated} legacy message thumbnails')
if __name__ == '__main__':
print('Ensuring tables...')
ensure_tables()
print('Migrating users...')
migrate_users()
print('Migrating messages...')
migrate_messages()
print('Migrating groups...')
migrate_groups()
print('Migrating group messages...')
migrate_group_messages()
print('Migrating group call history...')
migrate_group_call_history()
print('Migrating stories...')
migrate_stories()
print('Migrating notes...')
migrate_notes()
print('Migrating game rooms...')
migrate_game_rooms()
print('Migrating audit log...')
migrate_audit_log()
print('Migrating uploads media...')
migrate_uploads_media()
print('Migrating missing message thumbnails...')
migrate_missing_message_thumbnails()
print('Done')