Spaces:
Sleeping
Sleeping
Download scripts/migrate_json_to_postgres.py from Anil1515/chess-baackend: direct link, hf CLI and curl.
- Browser
- Download file 24.1 kB
-
https://huggingface.co/spaces/Anil1515/chess-baackend/resolve/main/scripts/migrate_json_to_postgres.py
- Command line
-
hf download hf://spaces/Anil1515/chess-baackend/scripts/migrate_json_to_postgres.py
-
curl -L -o migrate_json_to_postgres.py https://huggingface.co/spaces/Anil1515/chess-baackend/resolve/main/scripts/migrate_json_to_postgres.py
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') | |