Creates a new system_faction_states table, which stores the most recent values of the faction's: pending_states, active_states, and recovering_states.
Future inspection of these sets of states may help us infer what active states take precedence for the overall state of a faction (as stored in system_factions.state), since to my knowledge it must always be an element of the active states listed here. No constraint is added however because while it makes sense, I'd rather investigate these values completely, later.
I've simply moved the SystemFaction inserts into their own function, which still upserts the values on conflict with a more recent updated time. This should keep the table's values fresh (ignoring the game client caching issue).
The new logic is to simply delete and insert new state rows for every listed state from the journal entry when a system's faction is updated. No merging or special logic made sense to me here, we simply treat the most recent journal as the authoritative truth for faction's states. This should be consistent with the game's data regardless of our understanding of state's transitions from pending to active finally to recovering. This also means that we don't need to save the updated at time for each state, since it will be fully determined by it's corresponding system_factions.updated_at value.
Here's a taste of the data: SELECT * FROM system_faction_states;
system_address | faction_id | state | status
----------------+------------+-----------------------+------------
2793666480491 | 22177 | Boom | Active
2793666480491 | 22177 | PublicHoliday | Recovering
2793666480491 | 22177 | Expansion | Recovering
2793666480491 | 22178 | PublicHoliday | Recovering
22660918683049 | 1627 | InfrastructureFailure | Recovering
7269634287009 | 18491 | CivilWar | Active
7269634287009 | 18492 | CivilWar | Active
22660918683049 | 15888 | Drought | Recovering
22660918683049 | 15890 | Lockdown | Active
22660918683049 | 15890 | Bust | Active
22660918683049 | 15890 | Outbreak | Recovering
22660918683049 | 613 | Lockdown | Active
22660918683049 | 613 | Bust | Active
22660918683049 | 613 | InfrastructureFailure | Recovering
22660918683049 | 920 | Investment | Active
22660918683049 | 920 | CivilLiberty | Active
22660918683049 | 920 | PublicHoliday | Active
8055177614034 | 9935 | War | Recovering
8055177614034 | 1450 | Expansion | Active
8055177614034 | 1444 | Expansion | Active
8055177614034 | 18507 | CivilUnrest | Active
8055177614034 | 9575 | CivilLiberty | Active
8055177614034 | 9575 | Expansion | Recovering
8055177614034 | 1448 | Expansion | Active
8055177614034 | 1448 | War | Recovering
2869709055369 | 12228 | PirateAttack | Active
2869709055369 | 768 | Expansion | Active
2869709055369 | 9793 | Expansion | Pending
2869709055369 | 9793 | Boom | Active
Creates a new
system_faction_statestable, which stores the most recent values of the faction's:pending_states,active_states, andrecovering_states.Future inspection of these sets of states may help us infer what active states take precedence for the overall state of a faction (as stored in
system_factions.state), since to my knowledge it must always be an element of the active states listed here. No constraint is added however because while it makes sense, I'd rather investigate these values completely, later.I've simply moved the
SystemFactioninserts into their own function, which still upserts the values on conflict with a more recent updated time. This should keep the table's values fresh (ignoring the game client caching issue).The new logic is to simply delete and insert new state rows for every listed state from the journal entry when a system's faction is updated. No merging or special logic made sense to me here, we simply treat the most recent journal as the authoritative truth for faction's states. This should be consistent with the game's data regardless of our understanding of state's transitions from pending to active finally to recovering. This also means that we don't need to save the updated at time for each state, since it will be fully determined by it's corresponding
system_factions.updated_atvalue.Here's a taste of the data:
SELECT * FROM system_faction_states;