{"action":"created","issue":{"url":"https://api.github.com/repos/yasp-dota/yasp/issues/685","labels_url":"https://api.github.com/repos/yasp-dota/yasp/issues/685/labels{/name}","comments_url":"https://api.github.com/repos/yasp-dota/yasp/issues/685/comments","events_url":"https://api.github.com/repos/yasp-dota/yasp/issues/685/events","html_url":"https://github.com/yasp-dota/yasp/pull/685","id":108815445,"number":685,"title":"Sql","user":{"login":"howardchung","id":3134520,"avatar_url":"https://avatars.githubusercontent.com/u/3134520?v=3","gravatar_id":"","url":"https://api.github.com/users/howardchung","html_url":"https://github.com/howardchung","followers_url":"https://api.github.com/users/howardchung/followers","following_url":"https://api.github.com/users/howardchung/following{/other_user}","gists_url":"https://api.github.com/users/howardchung/gists{/gist_id}","starred_url":"https://api.github.com/users/howardchung/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/howardchung/subscriptions","organizations_url":"https://api.github.com/users/howardchung/orgs","repos_url":"https://api.github.com/users/howardchung/repos","events_url":"https://api.github.com/users/howardchung/events{/privacy}","received_events_url":"https://api.github.com/users/howardchung/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yasp-dota/yasp/labels/in%20progress","name":"in progress","color":"ededed"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":14,"created_at":"2015-09-29T08:06:55Z","updated_at":"2015-10-06T10:13:45Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/yasp-dota/yasp/pulls/685","html_url":"https://github.com/yasp-dota/yasp/pull/685","diff_url":"https://github.com/yasp-dota/yasp/pull/685.diff","patch_url":"https://github.com/yasp-dota/yasp/pull/685.patch"},"body":"fixes #582 \r\n\r\nImplementation is almost done :)\r\n\r\nDEVELOPMENT\r\n----\r\n- [x] Figure out a schema (parsed_data, player_match and player_rating should probably be separate tables)\r\n- [x] write migration script and test on devboxes\r\n- [x] pass single reference to db/redis\r\n- [x] update views to not ref parsedPlayer, parsed_data\r\n- [x] rename parsed_data.players.gold, lh, xp -> (gold_t, lh_t, xp_t), views\r\n- [x] rename parsed_data.players.kills -> killed, views\r\n- [x] rename last_visited -> last_login, views\r\n- [x] change player rating fields from camelcase to snake case (soloCompetitiveRank -> solo_competitive_rank)\r\n- [x] queries.js insertmatch, when inserting player_match select by match_id, player_slot to ensure uniqueness (account_id doesn't work since anonymous)\r\n- [x] aggregator.js, update to not ref parsedPlayer\r\n- [x] filter.js, update to not ref parsedPlayer\r\n- [x] filter, aggregator should accept player_matches, fellows as first arguments\r\n- [x] status.js, update queries\r\n- [x] mmr.js, update insert query\r\n- [x] buildSets.js, update queries\r\n- [x] getReplayUrl.js, update queries\r\n- [x] routes/donate.js, update queries\r\n- [x] routes/matches.js, update queries\r\n- [x] routes/players.js, select from player_matches join with matches then make separate query for played_with/played_against\r\n- [x] tasks/fullHistory.js (low priority)\r\n- [x] ag -i \"mongo\"\r\n- [x] ag -i \"parsed_data\"\r\n- [x] ag -i \"parsedPlayer\"\r\n- [x] test foreign key (dropping a match should drop all associated player_matches)\r\n- [x] figure out why players show up as bot (account_id=null) when the player record doesn't exist (should use account_id of player_match)\r\n- [x] cache of \"all\" limited to 250 player_matches, only 25 results due to conflicting match ids\r\n- [x] fix tests\r\n- [x] bigint or integer for match_id and account_id? (has to be bigint since integer only goes to 2b)\r\n- [x] swap kue for bull?\r\n- [x] implement distributed parse clients #676 \r\n- [ ] UPSERT not supported until psql 9.5, currently queries use insert, if err, update\r\n- [x] enable stop/resume in migration script\r\n- [ ] update README\r\n- [ ] race condition in cheese donation code (@albertcui)\r\n\r\nTEST\r\n----\r\n- [ ] make sure donation works (try in sandbox mode)\r\n- [ ] make sure signin works (manual test)\r\n- [ ] make sure requesting an existing match works\r\n- [ ] make sure requesting a new match works\r\n- [ ] make sure sequential parsing works (enable pro parsing)\r\n- [ ] make sure cache aggregation works\r\n- [ ] make sure old matches don't crash\r\n- [ ] make sure fullhistory works\r\n- [ ] make sure new deploy process works\r\n\r\nUPGRADE\r\n----\r\nUpgrade to latest node/npm (4.2.0, 3)\r\n`sudo npm install -g npm && sudo n latest`\r\n- [ ] yasp-core\r\n- [ ] parsers (contact admins for boxes where we don't have sudo access)\r\n- [x] devboxes\r\n- [ ] engine in readme\r\n- [ ] travis.yml\r\n- [ ] fix problem with build: contextify->nan->microtime->sifter->selectize, contextify->d3 3.5.0->c3\r\n- [ ] redis->3.0.4 (https://launchpad.net/~chris-lea/+archive/ubuntu/redis-server)\r\n\r\nDEPLOYMENT\r\n----\r\n- [ ] write release notes/blogpost\r\n- [ ] create new parser image\r\n- [ ] clear kue keys\r\n- [ ] dump caches repeatedly (updating caches slows down migration, or just disable caching while migrating)\r\n- [ ] check for null account_id/match_id in production\r\n- [ ] Install postgresql 9.5 on production/dev\r\n- [ ] set `WORK_URL`, `RETRIEVER_SECRET` on parse nodes\r\n- [ ] Migrate existing players\r\n- [ ] migrate existing matches"},"comment":{"url":"https://api.github.com/repos/yasp-dota/yasp/issues/comments/145811747","html_url":"https://github.com/yasp-dota/yasp/pull/685#issuecomment-145811747","issue_url":"https://api.github.com/repos/yasp-dota/yasp/issues/685","id":145811747,"user":{"login":"howardchung","id":3134520,"avatar_url":"https://avatars.githubusercontent.com/u/3134520?v=3","gravatar_id":"","url":"https://api.github.com/users/howardchung","html_url":"https://github.com/howardchung","followers_url":"https://api.github.com/users/howardchung/followers","following_url":"https://api.github.com/users/howardchung/following{/other_user}","gists_url":"https://api.github.com/users/howardchung/gists{/gist_id}","starred_url":"https://api.github.com/users/howardchung/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/howardchung/subscriptions","organizations_url":"https://api.github.com/users/howardchung/orgs","repos_url":"https://api.github.com/users/howardchung/repos","events_url":"https://api.github.com/users/howardchung/events{/privacy}","received_events_url":"https://api.github.com/users/howardchung/received_events","type":"User","site_admin":false},"created_at":"2015-10-06T10:13:45Z","updated_at":"2015-10-06T10:13:45Z","body":"We don't need to read the existing row because the latest data will contain all the fields we care about inserting/updating.\r\n\r\nAccording to Postgres documentation: http://www.postgresql.org/docs/9.4/static/sql-update.html\r\n\r\nUPDATE only affects the specified columns and leaves other values intact.\r\n\r\nWhen we write API data, whatever we got from the API should be whatever we write.  We don't care about anything that may or may not exist in the DB.  So we can blindly upsert safely without data loss.\r\n\r\nThis isn't strictly the case for parsed data (it won't contain everything the API gives).  However, due to the fact that omitted columns are preserved during UPDATE, the example you mentioned should always result in (pk1, A1, B1) no matter what order the operations happen in.  The key is that we don't have to UPDATE with a full row with NULLs for missing values.\r\n\r\n(I am not sure if this will change with native UPSERT in postgres 9.5).\r\n\r\nIn practice the parser will update 5-10 minutes after API.  In my current implementation INSERT is only done on API data.  Parser data and skill data (which comes from another API) is UPDATE only, meaning that it will no-op if for some reason we don't have that match in the database."}}