Created
October 16, 2017 16:08
-
-
Save vpzed/9066e1d4db2e7aeb9fff6a9fe2c6c9d6 to your computer and use it in GitHub Desktop.
PostgreSQL prototyping
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| PostgreSQL: | |
| user A (non-super-user admin account with createdb and createuser) | |
| destiny database: | |
| # main destiny database | |
| user X (job account with read/write) | |
| user Y (api account with read-only) | |
| alliance table: | |
| # Manually created reference info for "meta-clan" groupings | |
| id (int) *primary key* | |
| name (text) | |
| members (jsonb) array of uint32 Bungie clan groupIds | |
| group table: | |
| # Destiny clan info built from alliance.members array of groupId values for each alliance in alliance table | |
| id (bigint) *primary* key which is a Bungie clan groupId | |
| alliance_id (int) *references* alliances entry | |
| updated (timestamp) | |
| detail (jsonb) GroupV2.GetGroup.Response.detail object | |
| founder (jsonb) GroupV2.GetGroup.Response.founder object | |
| members (jsonb) GroupV2.GetMembersOfGroup.Response.results object | |
| account table: | |
| # Destiny account info built from group.members array of objects for each group in group table | |
| membershiptype (int) *composite primary key1* Destiny membershipType from group.members.[index].destinyUserInfo.membershipType value | |
| membershipid (bigint) *composite primary key2* Destiny membershipId from group.members.[index].destinyUserInfo.membershipId value | |
| group_id (bigint) *references* groups entry | |
| alliance_id (int) *references* alliances entry | |
| displayname (text) Destiny displayName from group.members.[index].destinyUserInfo.displayName value | |
| iconpath (text) Destiny iconPath from group.members.[index].destinyUserInfo.iconPath value | |
| updated (timestamp) | |
| accountstats (jsonb) Destiny2.GetHistoricalStatsForAccount.Response object | |
| characterdata (jsonb) Destiny2.GetProfile.Response.characters.data object | |
| characterstats (jsonb) Destiny2.GetHistoricalStats.Response object (meta object built from multiple requests) | |
| # follow @dad2cl3 concept of manifest handling? | |
| manifest_version table: | |
| manifest table: | |
| manifest_stage table: | |
| # Remember to ask why the stage table is needed |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The attached screenshot is a suggestion on how to capture the character stats from GetHistoricalStatsForAccount that gives a fair amount of flexibility to aggregating data.
effective_date : Current date on which the stats were pulled. The field can be dropped if there is no desire to track changes over time. Example: 2017-10-16
destiny_character : Character to whom the stats belong represented as JSON in the database. Example:
{ "deleted": false, "destinyId": "4611686018441650450", "characterId": "2305843009269814818", "destinyMembershipType": 2, "classHash": "2271682572" }game_play_format : Represents the game play format from which the stat was pulled. Examples: PvE, PvP, Merged
stat_id : Represents the key field in the Destiny SQLite manifest table DestinyHistoricalStatsDefinition. Example : highestLightLevel
stats : The actual stats for the individual stat per character. Example:
{ "basic": { "value": 279, "displayValue": "279" }, "statId": "highestLightLevel" }