mud_server.db.database ====================== .. py:module:: mud_server.db.database .. autoapi-nested-parse:: Database initialization and management for the MUD server. This module provides all database operations for the MUD server using SQLite. It handles: - Database schema initialization - User account management (create, authentication, roles) - Character management (creation, locations, inventory) - Session tracking (login/logout, active users) - Chat message storage and retrieval Database Design: Tables: - users: Account identities (login, role, status) - characters: World-facing personas owned by users - character_locations: Per-character room state - sessions: Active login sessions with activity tracking - chat_messages: All chat messages with room and recipient info Security Considerations: - Passwords hashed with bcrypt (never plain text) - Email stored as hashed value only (privacy-first) - SQL injection prevented using parameterized queries - Session IDs are UUIDs (hard to guess) Performance Notes: - SQLite handles basic concurrency (~50-100 players) - No connection pooling (single file database) - Suitable for small-medium deployments Attributes ---------- .. autoapisummary:: mud_server.db.database.DEFAULT_WORLD_ID Functions --------- .. autoapisummary:: mud_server.db.database.init_database mud_server.db.database.resolve_character_name mud_server.db.database.get_connection mud_server.db.database.create_user_with_password mud_server.db.database.create_character_for_user mud_server.db.database.user_exists mud_server.db.database.get_user_id mud_server.db.database.get_username_by_id mud_server.db.database.get_user_role mud_server.db.database.get_user_account_origin mud_server.db.database.set_user_role mud_server.db.database.verify_password_for_user mud_server.db.database.is_user_active mud_server.db.database.deactivate_user mud_server.db.database.activate_user mud_server.db.database.change_password_for_user mud_server.db.database.tombstone_user mud_server.db.database.delete_user mud_server.db.database.character_exists mud_server.db.database.get_character_by_name mud_server.db.database.get_character_by_id mud_server.db.database.get_character_name_by_id mud_server.db.database.get_user_characters mud_server.db.database.get_user_character_world_ids mud_server.db.database.unlink_characters_for_user mud_server.db.database.get_character_room mud_server.db.database.set_character_room mud_server.db.database.get_characters_in_room mud_server.db.database.get_character_inventory mud_server.db.database.set_character_inventory mud_server.db.database.add_chat_message mud_server.db.database.get_room_messages mud_server.db.database.create_session mud_server.db.database.set_session_character mud_server.db.database.remove_session_by_id mud_server.db.database.remove_sessions_for_user mud_server.db.database.update_session_activity mud_server.db.database.get_session_by_id mud_server.db.database.get_active_session_count mud_server.db.database.cleanup_expired_sessions mud_server.db.database.clear_all_sessions mud_server.db.database.get_active_characters mud_server.db.database.cleanup_expired_guest_accounts mud_server.db.database.get_world_by_id mud_server.db.database.list_worlds mud_server.db.database.list_worlds_for_user mud_server.db.database.get_table_names mud_server.db.database.list_tables mud_server.db.database.get_table_rows mud_server.db.database.get_all_users_detailed mud_server.db.database.get_all_users mud_server.db.database.get_character_locations mud_server.db.database.get_all_sessions mud_server.db.database.get_active_connections mud_server.db.database.get_all_chat_messages mud_server.db.database.player_exists mud_server.db.database.get_player_role mud_server.db.database.get_player_account_origin mud_server.db.database.set_player_role mud_server.db.database.is_player_active mud_server.db.database.deactivate_player mud_server.db.database.activate_player mud_server.db.database.create_player_with_password mud_server.db.database.get_player_room mud_server.db.database.set_player_room mud_server.db.database.get_player_inventory mud_server.db.database.set_player_inventory mud_server.db.database.get_active_players mud_server.db.database.get_players_in_room mud_server.db.database.get_all_players_detailed mud_server.db.database.get_all_players mud_server.db.database.get_player_locations mud_server.db.database.delete_player mud_server.db.database.cleanup_temporary_accounts mud_server.db.database.remove_session Module Contents --------------- .. py:data:: DEFAULT_WORLD_ID :value: 'pipeworks_web' .. py:function:: init_database(*, skip_superuser = False) Initialize the SQLite database with required tables. Creates all necessary tables if they don't exist. If MUD_ADMIN_USER and MUD_ADMIN_PASSWORD environment variables are set and no users exist, creates a superuser with those credentials (unless skip_superuser=True). :param skip_superuser: If True, skip superuser creation from env vars. Side Effects: - Creates data/mud.db file if it doesn't exist - Creates tables if they don't exist - Creates superuser if env vars set and no users exist .. py:function:: resolve_character_name(name, *, world_id = None) Public wrapper for resolving character names from usernames or character names. This preserves legacy call sites that still supply usernames while the character model is being adopted across the codebase. .. py:function:: get_connection() Create a new SQLite connection to the database file. :returns: sqlite3.Connection object .. py:function:: create_user_with_password(username, password, *, role = 'player', account_origin = 'legacy', email_hash = None, is_guest = False, guest_expires_at = None, create_default_character = True, world_id = DEFAULT_WORLD_ID) Create a new user account and (optionally) a default character. :param username: Unique account username. :param password: Plain text password (hashed with bcrypt). :param role: Role string. :param account_origin: Provenance marker for cleanup/auditing. :param email_hash: Hashed email value (nullable during development). :param is_guest: Whether this is a guest account. :param guest_expires_at: Expiration timestamp for guest accounts. :param create_default_character: If True, create a character with the same name. :param world_id: World to bind the default character to. :returns: True if created successfully, False if username already exists. .. py:function:: create_character_for_user(user_id, name, *, is_guest_created = False, room_id = 'spawn', world_id = DEFAULT_WORLD_ID) Create a character for an existing user. :param user_id: Owning user id. :param name: Character name (globally unique for now). :param is_guest_created: Marks characters created from guest flow. :param room_id: Initial room id. :param world_id: World the character belongs to. :returns: True if character created, False on constraint violation. .. py:function:: user_exists(username) Return True if a user account exists. .. py:function:: get_user_id(username) Return user id for the given username, or None if not found. .. py:function:: get_username_by_id(user_id) Return username for a user id, or None if not found. .. py:function:: get_user_role(username) Return the role for a username, or None if not found. .. py:function:: get_user_account_origin(username) Return account_origin for the given username. .. py:function:: set_user_role(username, role) Update a user's role. .. py:function:: verify_password_for_user(username, password) Verify a password against stored bcrypt hash. Uses a dummy hash for timing safety when user doesn't exist. .. py:function:: is_user_active(username) Return True if the user is active (not banned). .. py:function:: deactivate_user(username) Deactivate (ban) a user account. .. py:function:: activate_user(username) Activate (unban) a user account. .. py:function:: change_password_for_user(username, new_password) Change a user's password (hashes with bcrypt). .. py:function:: tombstone_user(user_id) Tombstone a user account without deleting rows. .. py:function:: delete_user(username) Delete a user account while preserving character data. This performs: - Unlink characters from the user (user_id -> NULL) - Remove all sessions - Tombstone the user row (soft delete) .. py:function:: character_exists(name) Return True if a character with this name exists. .. py:function:: get_character_by_name(name) Return character row by name. .. py:function:: get_character_by_id(character_id) Return character row by id. .. py:function:: get_character_name_by_id(character_id) Return character name for the given id, or None if not found. .. py:function:: get_user_characters(user_id, *, world_id = None) Return all characters owned by the given user for a world. When world_id is omitted, the default world is used to keep legacy code paths functional during the migration. .. py:function:: get_user_character_world_ids(user_id) Return the set of world ids in which the user has characters. This is used to enforce allow_multi_world_characters when creating new characters. .. py:function:: unlink_characters_for_user(user_id) Detach characters from a user (used when tombstoning guest accounts). .. py:function:: get_character_room(name, *, world_id = None) Return the current room for a character by name within a world. .. py:function:: set_character_room(name, room, *, world_id = None) Set the current room for a character by name within a world. .. py:function:: get_characters_in_room(room, *, world_id = None) Return character names in a room with active sessions for a world. .. py:function:: get_character_inventory(name) Return the character inventory as a list of item ids. .. py:function:: set_character_inventory(name, inventory) Set the character inventory. .. py:function:: add_chat_message(character_name, message, room, recipient_character_name = None, recipient = None, *, world_id = None) Add a chat message for a character. Supports optional whisper recipient and uses world scoping. If world_id is omitted, the default world is used during migration. .. py:function:: get_room_messages(room, *, limit = 50, character_name = None, username = None, world_id = None) Get recent messages from a room. Filters whispers based on character. Messages are scoped to the provided world_id; default world is used when omitted to preserve legacy code paths during migration. .. py:function:: create_session(user_id, session_id, *, client_type = 'unknown', character_id = None, world_id = None) Create a new session record for a user. Behavior depends on configuration: - allow_multiple_sessions = False: remove existing sessions for the user - allow_multiple_sessions = True: keep existing sessions If world_id is omitted, the default world is used. This is temporary compatibility behavior until the API layer is updated to provide it. .. py:function:: set_session_character(session_id, character_id, *, world_id = None) Attach a character (and optionally world) to an existing session. .. py:function:: remove_session_by_id(session_id) Remove a specific session by its session_id. .. py:function:: remove_sessions_for_user(user_id) Remove all sessions for a user (used for forced logout/ban). .. py:function:: update_session_activity(session_id) Update last_activity for a session and extend expiry when sliding is enabled. .. py:function:: get_session_by_id(session_id) Return session record by session_id (or None if not found). .. py:function:: get_active_session_count() Count active sessions within the configured activity window. .. py:function:: cleanup_expired_sessions() Remove expired sessions based on expires_at timestamp. .. py:function:: clear_all_sessions() Remove all sessions from the database. .. py:function:: get_active_characters(*, world_id = None) Return character names with active sessions for a world. .. py:function:: cleanup_expired_guest_accounts() Delete expired guest accounts and unlink their characters. :returns: Number of guest users deleted. .. py:function:: get_world_by_id(world_id) Return a world catalog entry by id. :param world_id: World identifier (primary key). :returns: Dict with world fields or None if not found. .. py:function:: list_worlds(*, include_inactive = False) Return all worlds in the catalog. :param include_inactive: When False, only active worlds are returned. .. py:function:: list_worlds_for_user(user_id, *, role = None, include_inactive = False) Return worlds accessible to the given user. Admins and superusers have implicit access to all worlds. Other roles must have a world_permissions row with can_access=1. .. py:function:: get_table_names() Return a sorted list of user-defined table names (excludes sqlite_*). .. py:function:: list_tables() Return table metadata for admin database browsing. .. py:function:: get_table_rows(table_name, limit = 100) Return column names and rows for a given table. .. py:function:: get_all_users_detailed() Return detailed user list for admin database viewer. .. py:function:: get_all_users() Return basic user list for admin summaries. .. py:function:: get_character_locations(*, world_id = None) Return character location rows with names for admin display. .. py:function:: get_all_sessions(*, world_id = None) Return all active (non-expired) sessions. .. py:function:: get_active_connections(*, world_id = None) Return active sessions with activity age in seconds. .. py:function:: get_all_chat_messages(limit = 100, *, world_id = None) Return recent chat messages across all rooms. .. py:function:: player_exists(username) Backward-compatible alias for user_exists(). .. py:function:: get_player_role(username) Backward-compatible alias for get_user_role(). .. py:function:: get_player_account_origin(username) Backward-compatible alias for get_user_account_origin(). .. py:function:: set_player_role(username, role) Backward-compatible alias for set_user_role(). .. py:function:: is_player_active(username) Backward-compatible alias for is_user_active(). .. py:function:: deactivate_player(username) Backward-compatible alias for deactivate_user(). .. py:function:: activate_player(username) Backward-compatible alias for activate_user(). .. py:function:: create_player_with_password(username, password, role = 'player', account_origin = 'legacy') Backward-compatible alias for create_user_with_password(). .. py:function:: get_player_room(username) Backward-compatible alias for get_character_room(). .. py:function:: set_player_room(username, room) Backward-compatible alias for set_character_room(). .. py:function:: get_player_inventory(username) Backward-compatible alias for get_character_inventory(). .. py:function:: set_player_inventory(username, inventory) Backward-compatible alias for set_character_inventory(). .. py:function:: get_active_players() Backward-compatible alias for get_active_characters(). .. py:function:: get_players_in_room(room) Backward-compatible alias for get_characters_in_room(). .. py:function:: get_all_players_detailed() Backward-compatible alias for get_all_users_detailed(). .. py:function:: get_all_players() Backward-compatible alias for get_all_users(). .. py:function:: get_player_locations() Backward-compatible alias for get_character_locations(). .. py:function:: delete_player(username) Backward-compatible alias for delete_user(). .. py:function:: cleanup_temporary_accounts(max_age_hours = 24, origin = 'visitor') Backward-compatible alias for cleanup_expired_guest_accounts(). Args are ignored because guest expiry is timestamp-driven. .. py:function:: remove_session(username) Backward-compatible alias for removing sessions by username.