From 5c869aa26666215ec4ea5315b40fea3025a1ae83 Mon Sep 17 00:00:00 2001 From: schneefux Date: Wed, 29 Mar 2017 20:31:05 +0200 Subject: import db schema from vainsocial --- model.js | 127 ++++++---------------------------------------------------- package.json | 1 + worker.js | 129 ++++++++++++++++++++++------------------------------------- 3 files changed, 61 insertions(+), 196 deletions(-) diff --git a/model.js b/model.js index fa7c517..0ff813e 100644 --- a/model.js +++ b/model.js @@ -4,120 +4,19 @@ var JsonField = require("sequelize-json"); module.exports = (seq, Seq) => { - let Player = seq.define("player", { - id: { type: Seq.STRING, unique: true, primaryKey: true }, - /* attributes */ - name: Seq.STRING, - shardId: Seq.STRING, - /* stats */ - level: Seq.INTEGER, - lifetimeGold: Seq.DECIMAL, - lossStreak: Seq.INTEGER, - played: Seq.INTEGER, - played_ranked: Seq.INTEGER, - winStreak: Seq.INTEGER, - wins: Seq.INTEGER, - xp: Seq.INTEGER - }, { - freezeTableName: true, - underscored: true - }); - let Team = seq.define("team", { - id: { type: Seq.STRING, unique: true, primaryKey: true }, - /* attributes */ - name: Seq.STRING, - shardId: Seq.STRING - /* stats */ - }, { - freezeTableName: true, - underscored: true - }); - let Asset = seq.define("asset", { - id: { type: Seq.STRING, unique: true, primaryKey: true }, - /* attributes */ - URL: Seq.STRING, - contentType: Seq.STRING, - createdAt: Seq.DATE, - description: Seq.TEXT, - filename: Seq.STRING, - name: Seq.STRING - }, { - freezeTableName: true, - underscored: true, - createdAt: false - }); - let Match = seq.define("match", { - id: { type: Seq.STRING, unique: true, primaryKey: true }, - /* attributes */ - createdAt: Seq.DATE, - duration: Seq.INTEGER, - gameMode: Seq.STRING, - patchVersion: Seq.STRING, - shardId: Seq.STRING, - /* stats */ - endGameReason: Seq.STRING, - queue: Seq.STRING - }, { - freezeTableName: true, - underscored: true, - createdAt: false - }); - let Roster = seq.define("roster", { - id: { type: Seq.STRING, unique: true, primaryKey: true }, - /* attributes */ - /* stats */ - acesEarned: Seq.INTEGER, - gold: Seq.INTEGER, - heroKills: Seq.INTEGER, - krakenCaptures: Seq.INTEGER, - side: Seq.STRING, - turretKills: Seq.INTEGER, - turretsRemaining: Seq.INTEGER, - }, { - freezeTableName: true, - underscored: true - }); - let Participant = seq.define("participant", { - id: { type: Seq.STRING, unique: true, primaryKey: true }, - /* attributes */ - actor: Seq.STRING, - /* stats */ - assists: Seq.INTEGER, - crystalMineCaptures: Seq.INTEGER, - deaths: Seq.INTEGER, - farm: Seq.DECIMAL, - firstAfkTime: Seq.INTEGER, - goldMineCaptures: Seq.INTEGER, - itemGrants: JsonField(seq, "Participant", "itemGrants"), - itemSells: JsonField(seq, "Participant", "itemSells"), - itemUses: JsonField(seq, "Participant", "itemUses"), - items: JsonField(seq, "Participant", "items"), - jungleKills: Seq.INTEGER, - karmaLevel: Seq.INTEGER, - kills: Seq.INTEGER, - krakenCaptures: Seq.INTEGER, - level: Seq.INTEGER, - minionKills: Seq.INTEGER, - nonJungleMinionKills: Seq.INTEGER, - skillTier: Seq.INTEGER, - skinKey: Seq.STRING, - turretCaptures: Seq.INTEGER, - wentAfk: Seq.BOOLEAN, - winner: Seq.BOOLEAN, - }, { - freezeTableName: true, - underscored: true - }); + let Match = seq.import("./models/match.js"), + Roster = seq.import("./models/roster.js"), + Participant = seq.import("./models/participant.js"), + Player = seq.import("./models/player.js"); - Match.hasMany(Roster, {as: "Rosters"}); - Match.hasMany(Asset, {as: "Assets"}); - Asset.belongsTo(Match); - Roster.belongsTo(Match); - Roster.hasMany(Participant, {as: "Participants"}); - Roster.hasOne(Team, {as: "Team"}); - Team.belongsTo(Roster); - Participant.belongsTo(Roster); - Participant.belongsTo(Player); + //Match.hasMany(Asset, {as: "Assets"}); + //Asset.belongsTo(Match); + Roster.belongsTo(Match, { foreignKey: "match_api_id", targetKey: "api_id" }); + //Roster.hasOne(Team, {as: "Team"}); + //Team.belongsTo(Roster); + Participant.belongsTo(Roster, { foreignKey: "roster_api_id", targetKey: "api_id" }); + Participant.hasOne(Player, { foreignKey: "player_api_id", targetKey: "api_id" }); - return {Match, Roster, Team, Participant, Player, Asset}; + //return {Match, Roster, Team, Participant, Player, Asset}; + return {Match, Roster, Participant, Player}; }; diff --git a/package.json b/package.json index 191e5cb..55adacc 100644 --- a/package.json +++ b/package.json @@ -7,6 +7,7 @@ "amqplib": "^0.5.1", "mysql": "^2.13.0", "sequelize": "^3.30.4", + "sequelize-auto": "^0.4.25", "sequelize-json": "^2.1.2", "superagent-jsonapify": "^1.4.4" }, diff --git a/worker.js b/worker.js index aec51b9..7cefbdb 100644 --- a/worker.js +++ b/worker.js @@ -3,9 +3,7 @@ 'use strict'; var amqp = require("amqplib"), - Seq = require("sequelize"), - Bluebird = require("bluebird"), - jsonapi = Bluebird.promisifyAll(require("superagent-jsonapify/common")); + Seq = require("sequelize"); var RABBITMQ_URI = process.env.RABBITMQ_URI || "amqp://localhost", DATABASE_URI = process.env.DATABASE_URI || "sqlite:///db.sqlite"; @@ -20,100 +18,67 @@ var RABBITMQ_URI = process.env.RABBITMQ_URI || "amqp://localhost", await seq.query("SET FOREIGN_KEY_CHECKS=0"); await seq.sync({force: true}); */ - await seq.sync(); + //await seq.sync(); await ch.assertQueue("process", {durable: true}); await ch.prefetch(1); ch.consume("process", async (msg) => { - let data = await jsonapi.parse(msg.content); + let match = JSON.parse(msg.content); // TODO commit less often if possible, avoid deadlocks let transaction = await seq.transaction({ autocommit: false }); - data.data.forEach((match_data) => { - function flatten(obj) { - let attrs = obj.attributes || {}, - stats = attrs.stats || {}, - o = Object.assign({}, obj, attrs, stats); - delete o.type; - delete o.attributes; - delete o.stats; - delete o.relationships; - return o; - } - let match = JSON.parse(JSON.stringify(match_data)); // deep clone - - /* bring jsonapi response into our db structure-like shape */ - match.rosters = match.rosters.map((roster) => { - roster.participants = roster.participants.map((participant) => { - participant.player = flatten(participant.player); - return flatten(participant); - }); - return flatten(roster); - }); - match = flatten(match); - - /* upsert everything */ - model.Match.upsert(match, { - include: [ - { - model: model.Roster, - as: "Rosters", - include: [ - { - model: model.Participant, - as: "Participants", - include: [ - model.Player - ] - }, - { - model: model.Team, - as: "Team" - } - ] - }, - { - model: model.Asset, - as: "Assets" - } - ] + function flatten(obj) { + let attrs = obj.attributes || {}, + stats = attrs.stats || {}, + o = Object.assign({}, obj, attrs, stats); + o.api_id = o.id; // rename + delete o.id; + delete o.type; + delete o.attributes; + delete o.stats; + delete o.relationships; + return o; + } + + /* bring jsonapi nested response into our db structure-like shape */ + match.rosters = match.rosters.map((roster) => { + roster.participants = roster.participants.map((participant) => { + participant.player = flatten(participant.player); + return flatten(participant); }); - - match.rosters.forEach((roster) => { - model.Roster.upsert(roster, { - include: [ - { - model: model.Participant, - as: "Participants", - include: [ - model.Player - ] - }, - { - model: model.Team, - as: "Team" - } - ] + return flatten(roster); + }); + match = flatten(match); + console.log(match); + + /* upsert everything */ + await match.rosters.forEach(async (roster) => { + await roster.participants.forEach(async (participant) => { + await model.Player.upsert(participant.player); + + participant.roster_api_id = roster.api_id; + participant.player_api_id = participant.player.api_id; + await model.Participant.upsert(participant, { + include: [ model.Player ] }); + }); - roster.participants.forEach((participant) => { - model.Participant.upsert(participant, { - include: [ - model.Player - ] - }); - - model.Player.upsert(participant.player); - }); + //if (roster.team != null) model.Team.upsert(roster.team); - if (roster.team != null) model.Team.upsert(roster.team); + roster.match_api_id = match.api_id; + await model.Roster.upsert(roster, { + include: [ model.Participant/*, model.Team*/ ] }); + }); - match.assets.forEach((asset) => { - model.Asset.upsert(asset); - }); + /*match.assets.forEach((asset) => { + model.Asset.upsert(asset); + });*/ + + await model.Match.upsert(match, { + include: [ model.Roster/*, model.Asset*/ ] }); await transaction.commit(); // TODO rollback on err -- cgit v1.3.1