diff options
| -rw-r--r-- | crunch_global.sql | 10 | ||||
| -rw-r--r-- | crunch_phases.sql | 12 | ||||
| -rw-r--r-- | worker.js | 78 |
3 files changed, 25 insertions, 75 deletions
diff --git a/crunch_global.sql b/crunch_global.sql index 8d618ce..2b48958 100644 --- a/crunch_global.sql +++ b/crunch_global.sql @@ -32,10 +32,7 @@ select sum(p_s.gold) as gold, sum(p_s.impact_score) as impact_score, sum(p_i.surrender) as surrender, - _p_i_items_insert, - _p_i_item_grants_insert, - _p_i_item_uses_insert, - _p_i_item_sells_insert + _p_i_item_uses_insert from participant p join participant_stats p_s on (p_s.participant_api_id = p.api_id) join participant_items p_i on (p_i.participant_api_id = p.api_id) @@ -86,7 +83,4 @@ turret_captures = turret_captures + values(turret_captures), gold = gold + values(gold), impact_score = impact_score + values(impact_score), surrender = surrender + values(surrender), -_p_i_items_update, -_p_i_item_grants_update, -_p_i_item_uses_update, -_p_i_item_sells_update +_p_i_item_uses_update diff --git a/crunch_phases.sql b/crunch_phases.sql index bc03b76..fa0a95c 100644 --- a/crunch_phases.sql +++ b/crunch_phases.sql @@ -48,11 +48,6 @@ select sum(ability_b_level), sum(ability_c_level), sum(hero_level), - - _ph_items_insert, - _ph_item_grants_insert, - _ph_item_sells_insert, - sum(ability_a_use), sum(ability_b_use), sum(ability_c_use), @@ -68,9 +63,7 @@ select sum(ability_aa_damage_dealt), sum(ability_aacrit_damage_true), sum(ability_aacrit_damage_dealt), - - _ph_item_uses_insert, - '' as player_damage + _ph_item_uses_insert from participant_phases ph join participant p on ph.participant_api_id = p.api_id join filter f on (f.dimension_on = 'global' and (f.name = 'all' or f.id in (select gpf.filter_id from global_point_filters gpf where gpf.match_api_id = p.match_api_id))) @@ -119,9 +112,6 @@ ability_a_level = ability_a_level + values(ability_a_level), ability_b_level = ability_b_level + values(ability_b_level), ability_c_level = ability_c_level + values(ability_c_level), hero_level = hero_level + values(hero_level), -_ph_items_update, -_ph_item_grants_update, -_ph_item_sells_update, ability_a_use = ability_a_use + values(ability__a_use), ability_b_use = ability_b_use + values(ability__b_use), ability_c_use = ability_c_use + values(ability__c_use), @@ -53,7 +53,7 @@ if (LOGGLY_TOKEN) amqp.connect(RABBITMQ_URI).then(async (rabbit) => { // connect to rabbit & db const seq = new Seq(DATABASE_URI, { - logging: false, + //logging: false, max: MAXCONNS }); @@ -73,76 +73,42 @@ amqp.connect(RABBITMQ_URI).then(async (rabbit) => { // for the `participant_api_id` array let script = fs.readFileSync(SCRIPT, "utf8"); - // array of all item ids - const items = (await model.Item.findAll()).map((i) => i.id); + // array of all consumable ids + const active_items = (await model.Item.findAll({ + where: { is_activable: true } + })).map((i) => i.id); // returns an SQL snippet like this: - /* column_create( - * '1', column_get(p_i.item_grants, '1'), - * … - * ) + /* sum(column_get(p_i.item_grants, '14')) as item_014_use + * or + * item_014_use = item_014_use + values(item_014_use) */ - const dynamic_sql = (doCreate, tableName, columnName) => { + const dynamic_sql = (doCreate, tableName) => { + const pad = (i) => i.toString().padStart(3, "0"); if (doCreate) { // insert - return ` -column_create(` + - (items.map((i) => - // create an array of `columnName, col_get($col, columnName)` -` '${i}', - coalesce(column_get(${tableName}.${columnName}, '${i}' as int), 0)` - ).join(",\n")) + ` -) as ${columnName}`; + return active_items.map((i) => + `sum(column_get(${tableName}.item_uses, '${i}' as int)) ` + + `as item_${pad(i)}_use` + ).join(",\n"); } else { - return ` -${columnName} = column_create(` + - (items.map((i) => - // create an array of - // `columnName, col_get($col, columnName) + col_get(values($col, columnName))` -` '${i}', - coalesce(column_get(${columnName}, '${i}' as int), 0) - + - coalesce(column_get(values(${columnName}), '${i}' as int), 0)` - ).join(",\n")) + ` -)`; + return active_items.map((i) => + `item_${pad(i)}_use = item_${pad(i)}_use + values(item_${pad(i)}_use)` + ).join(",\n"); } }; // generate const - p_i_items_insert = dynamic_sql(true, 'p_i', 'items'), - p_i_item_grants_insert = dynamic_sql(true, 'p_i', 'item_grants'), - p_i_item_uses_insert = dynamic_sql(true, 'p_i', 'item_uses'), - p_i_item_sells_insert = dynamic_sql(true, 'p_i', 'item_sells'), - p_i_items_update = dynamic_sql(false, 'p_i', 'items'), - p_i_item_grants_update = dynamic_sql(false, 'p_i', 'item_grants'), - p_i_item_uses_update = dynamic_sql(false, 'p_i', 'item_uses'), - p_i_item_sells_update = dynamic_sql(false, 'p_i', 'item_sells'), - ph_items_insert = dynamic_sql(true, 'ph', 'items'), - ph_item_grants_insert = dynamic_sql(true, 'ph', 'item_grants'), - ph_item_uses_insert = dynamic_sql(true, 'ph', 'item_uses'), - ph_item_sells_insert = dynamic_sql(true, 'ph', 'item_sells'), - ph_items_update = dynamic_sql(false, 'ph', 'items'), - ph_item_grants_update = dynamic_sql(false, 'ph', 'item_grants'), - ph_item_uses_update = dynamic_sql(false, 'ph', 'item_uses'), - ph_item_sells_update = dynamic_sql(false, 'ph', 'item_sells') + p_i_item_uses_insert = dynamic_sql(true, 'p_i'), + p_i_item_uses_update = dynamic_sql(false, 'p_i'), + ph_item_uses_insert = dynamic_sql(true, 'ph'), + ph_item_uses_update = dynamic_sql(false, 'ph') ; // replace stubs Object.entries({ - p_i_items_insert, - p_i_item_grants_insert, p_i_item_uses_insert, - p_i_item_sells_insert, - p_i_items_update, - p_i_item_grants_update, p_i_item_uses_update, - p_i_item_sells_update, - ph_items_insert, - ph_item_grants_insert, ph_item_uses_insert, - ph_item_sells_insert, - ph_items_update, - ph_item_grants_update, - ph_item_uses_update, - ph_item_sells_update, + ph_item_uses_update }).forEach(([key, value]) => script = script.replace('_' + key, value)); // fill a buffer and execute an SQL on a bigger (> 1o) batch |