Databases - rebaserHEAD/NexusForever GitHub Wiki

The server's seven databases: their names, who owns each, the rules for adding to them, and every table in each. Which process reads which database is on Server architecture processes and projects.

Database	Aspire name	Name in the example configs	Owned by	Shared between servers
Auth	authdb	nexus_forever_auth	STS, auth and world servers, both APIs	Yes
Character	characterdb	nexus_forever_character	World server, character API	No: one per realm
World	worlddb	nexus_forever_world	World server	Yes
Chat	chatdb	nexus_forever_chat	Chat server
Group	groupdb	nexus_forever_group	Group server
Friendship	friendshipdb	nexus_forever_friendship	Friendship server
Query	querydb	nexus_forever_query	Character server

Under the Aspire host all seven live in one MySQL container, in the mysql-data volume.

From upstream's database style, plus the conventions its existing tables follow:

Rule	Detail
Account data goes in auth	Anything that belongs to the account rather than one character. Several servers may share it
Character and realm data goes in character	One character database per realm, used by one world server
Static data goes in world	Content that doesn't change in play. Several servers may share it
Tables are snake_case	character_archive_article
Columns are lowerCamel	unlockedFlags
Primary keys are named PRIMARY	Usually composite where a row belongs to a character: the character id plus the row's own id
Foreign keys are named FK__<table>_<column>__<parent>_<column>	FK__character_archive_article_id__character_id
Schema changes are EF Core code-first migrations	One migration per change, in the database's own project
No logic in the database	No stored procedures or functions

Group	Tables
Accounts	account, account_costume_unlock, account_currency, account_entitlement, account_external_reference, account_generic_unlock, account_keybinding, account_suspension
Permissions	permission, role, role_permission, account_permission, account_role
Realms	server, server_message

Group	Tables
The character	character, character_appearance, character_bone, character_create, character_customisation, character_stats, property_base
Progress	character_achievement, character_archive_article, character_datacube, character_path, character_quest, character_quest_objective, character_reputation, character_title, character_zonemap_hexgroup
Abilities	character_action_set_amp, character_action_set_shortcut, character_spell
Belongings	item, character_currency, character_costume, character_costume_item, character_pet_customisation, character_pet_flair, character_tradeskill_materials
Account-like, per character	character_entitlement, character_keybinding
Mail	character_mail, character_mail_attachment
Guilds	guild, guild_achievement, guild_guild_data, guild_member, guild_rank
Housing	residence, residence_decor, residence_plot
Chat channels	chat_channel, chat_channel_member

Group	Tables
Spawns	entity, entity_emote, entity_event, entity_spline, entity_stats
Vendors	entity_vendor, entity_vendor_category, entity_vendor_item
The store	store_category, store_offer_group, store_offer_group_category, store_offer_item, store_offer_item_data, store_offer_item_price
Maps and the rest	map_entrance, tutorial, disable, version

version records which world database .sql files have been imported, so a file is only applied once.

Each side server keeps its own mirror of the characters it deals with, filled from the character API. Chat, group and friendship also keep an outbox.

Database	Tables
Chat	chat_channel, chat_channel_member, chat_channel_owner, character, character_chat_channel, internal_message
Group	group, group_invite, group_leader, group_marker, group_member, group_request, character, character_group, character_property, character_stat, internal_message
Friendship	friendship, friendship_account, friendship_account_invite, friendship_invite, account, account_friend, account_friend_inverse, account_friend_invite, account_friend_invite_pending, character, character_friend, character_friend_inverse, character_friend_invite, character_friend_invite_pending, character_stat
Query	character

internal_message is the outbox: messages saved in the same transaction as the change that caused them, and published by a scheduled job.

Where	When
The Aspire migration step	Migrates all seven databases before any server starts
The world server	Migrates the auth, character and world databases at start-up if any migration is pending
dotnet ef	By hand, for a new migration; see Adding a player feature

Piece	Path
Each database's context, models and migrations	Source/NexusForever.Database.<Name>/
Shared database plumbing	Source/NexusForever.Database/
The migration step	Source/NexusForever.Aspire.Database.Migrations/
Design-time factories for dotnet ef	Source/NexusForever.WorldServer/Design/