# Use of this source code is governed by an MIT-style license that can be
# found in the LICENSE file.
# instructions for running this script:
# go to the gamedata/ directory
# PYTHONPATH=../gameserver ./make_ai_base_example.py
# (optional: use > to write output to a file instead of printing to the console)
import SpinJSON # JSON reading/writing library
import SpinConfig
import AtomicFileWrite # little library for safely overwriting files atomically
import sys, copy, getopt, os, random # import some misc. Python libraries
import tr_base_generator_helper as bgh
# load in gamedata
gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename()))
ncells = 180
class cannotFindCoordinateError(Exception):
# def __init__(self):
#
def __str__(self):
return "Error"
BASE_LAYOUT = {
"sectors": [{
"cluster": "toc_cluster",
"positioning": "near_the_middle"
}, {
do_unit_cost = False
opts, args = getopt.gnu_getopt(
sys.argv[1:], 'g:c:q', ['prune', 'optimize', 'dry-run', 'unit-cost'])
for key, val in opts:
if key == '-g': game_id = val
elif key == '-c': commit_interval = int(val)
elif key == '-q': verbose = False
elif key == '--dry-run': dry_run = True
elif key == '--prune': do_prune = True
elif key == '--optimize': do_optimize = True
elif key == '--unit-cost': do_unit_cost = True
gamedata = SpinJSON.load(
open(SpinConfig.gamedata_filename(override_game_id=game_id)))
sql_util = SpinSQLUtil.MySQLUtil()
if not verbose: sql_util.disable_warnings()
cfg = SpinConfig.get_mysql_config(game_id + '_upcache')
con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
store_table = cfg['table_prefix'] + game_id + '_store'
store_daily_summary_table = cfg[
'table_prefix'] + game_id + '_store_daily_summary'
store_top_spenders_28d_table = cfg[
'table_prefix'] + game_id + '_store_top_spenders_28d'
unit_cost_table = cfg['table_prefix'] + game_id + '_unit_cost'
unit_cost_daily_summary_table = cfg[
'table_prefix'] + game_id + '_unit_cost_daily_summary'
try:
import simplejson as json
except:
import json
import SpinConfig
# load some standard Python libraries
import sys, time, calendar
import csv
# load SpinPunch Upcache S3 access library
import SpinS3, SpinUpcacheIO
# load gamedata so we can reference it if necessary
# e.g. gamedata['units']['motion_cannon']['armor']
gamedata = json.load(open(SpinConfig.gamedata_filename()))
time_now = int(time.time())
def stream_userdb():
bucket, name = SpinConfig.upcache_s3_location(SpinConfig.game())
return SpinUpcacheIO.S3Reader(SpinS3.S3(SpinConfig.aws_key_file()), bucket,
name).iter_all()
# main program
if __name__ == '__main__':
# days at which to evaluate spend_dX
SPEND_MARKS = (0, 1, 3, 5, 7, 14, 30, 60, 90, 120)
do_reset = False
do_mongo_drop = False
dry_run = 0
opts, args = getopt.gnu_getopt(sys.argv[1:], 'g:c:q', ['reset','mongo-drop','dry-run','force'])
for key, val in opts:
if key == '-g': game_id = val
elif key == '-c': commit_interval = int(val)
elif key == '-q': verbose = False
elif key == '--mongo-drop': do_mongo_drop = True
elif key == '--reset': do_reset = True
elif key == '--dry-run': dry_run = 1
elif key == '--force': force = True
gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id = game_id)))
if not verbose: sql_util.disable_warnings()
cfg = SpinConfig.get_pgsql_config(SpinConfig.config['game_id']+'_scores2')
if (not force) and \
(SpinConfig.in_maintenance_window(cfg, time_now = time_now) or SpinConfig.in_maintenance_window(cfg, time_now = time_now + 1800)): # allow for 30min to operate
if verbose: print 'in database maintenance window, aborting'
sys.exit(0)
with SpinSingletonProcess.SingletonProcess('scores2-to-sql-%s' % (game_id)):
con = psycopg2.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
tbl = { 'player': cfg['table_prefix']+'player_scores2',
'alliance': cfg['table_prefix']+'alliance_scores2' }
def do_slave(task):
date = task['date']
game_id = task['game_id']
verbose = task['verbose']
dry_run = task['dry_run']
commit_interval = task['commit_interval']
start_time = SpinConfig.cal_to_unix((int(date[0:4]),int(date[4:6]),int(date[6:8])))
end_time = start_time + 86400
gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id=game_id)))
STORE = {}
[get_store_items(STORE, sku) for sku in gamedata['store']['catalog']]
if verbose:
print >> sys.stderr, 'converting date', date, 'start_time', start_time, 'end_time', end_time, '...'
if not verbose: filterwarnings('ignore', category = MySQLdb.Warning)
cfg = SpinConfig.get_mysql_config(game_id+'_upcache')
con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
store_table = cfg['table_prefix']+game_id+'_store'
s3 = SpinS3.S3(SpinConfig.aws_key_file())
bucket = 'spinpunch-logs'
batch = 0
total = 0
cur = con.cursor()
for entry in s3.list_bucket(bucket, prefix='%s/%s-%s-metrics.json' % (date[0:6], SpinConfig.game_id_long(override_game_id=game_id), date)):
filename = entry['name'].split('/')[-1]
if verbose: print >> sys.stderr, 'reading', filename
if entry['name'].endswith('.zip'):
tf = tempfile.NamedTemporaryFile(prefix='old_metrics_to_mysql-'+filename, suffix='.zip')
s3.get_file(bucket, entry['name'], tf.name)
unzipper = subprocess.Popen(['unzip', '-q', '-p', tf.name],
stdout = subprocess.PIPE)
elif entry['name'].endswith('.gz'):
fd = s3.get_open(bucket, entry['name'], allow_keepalive = False)
unzipper = subprocess.Popen(['gunzip', '-c', '-'],
stdin = fd.fileno(),
stdout = subprocess.PIPE)
for line in unzipper.stdout.xreadlines():
if '5120_buy_item' in line:
#and ('item:token' in line):
entry = SpinJSON.loads(line)
if entry['event_name'] != '5120_buy_item': continue
if 'price_currency' not in entry:
# old metric, need to fill in manually
if entry['items'][0]['spec'] in STORE:
entry['price_currency'] = 'item:token'
entry['price'] = STORE[entry['items'][0]['spec']]
if verbose: print >> sys.stderr, SpinJSON.dumps(entry)
if entry.get('price_currency','unknown') != 'item:token': continue
if '_id' in entry:
entry_id = entry['_id']
else:
id_generator.set_time(int(time.time()))
entry_id = id_generator.generate() # arbitrary
assert len(entry['items']) == 1
item = entry['items'][0]
keyvals = [('_id', entry_id),
('time', entry['time']),
('user_id', entry['user_id']),
('price', entry['price']),
('currency', entry['price_currency']),
('item', item['spec']),
('stack', item.get('stack',1))]
query = "INSERT INTO " + store_table + \
"("+', '.join(['`'+k+'`' for k,v in keyvals])+")"+ \
" VALUES ("+', '.join(['%s'] * len(keyvals)) +")"
if dry_run:
print >> sys.stderr, query, [v for k,v in keyvals]
else:
cur.execute(query, [v for k,v in keyvals])
batch += 1
total += 1
if commit_interval > 0 and batch >= commit_interval:
batch = 0
con.commit()
cur = con.cursor()
if verbose: print >> sys.stderr, total, 'inserted'
if not dry_run:
con.commit()
def main(args):
global gamedata
global basedata
global hivedata
global lootdata
global time_now
opts, args = getopt.gnu_getopt(args, 'cg:', [])
game_id = 'ALL'
output = 'txt'
for key, val in opts:
if key == '-g':
game_id = val
if key == '-c':
output = 'csv'
time_now = int(time.time())
date_now = datetime.datetime.utcfromtimestamp(time_now)
gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename()))
cur_week = SpinConfig.get_pvp_week(gamedata['matchmaking']['week_origin'], time_now)
today5pm = SpinConfig.cal_to_unix(SpinConfig.unix_to_cal(time_now)) + 17*60*60
if time_now < today5pm:
cur_day = ((date_now.weekday() + 3) % 7 + 1)
else:
cur_day = ((date_now.weekday() + 4) % 7 + 1)
if output == 'txt':
print 'Sauron v0.01.015 | %s, %s %s (Week %s, Day %s) %s GMT:<br>' % (date_now.strftime('%A')[:3].upper(), date_now.strftime("%B")[:3].upper(), date_now.strftime('%d, %Y'), cur_week, cur_day, date_now.strftime('%H:%M:%S'))
print '======================================================================='
print '\n TITLES :\n'
print ' TR - Thunder Run'
print ' BFM - Battlefront Mars'
print ''
print ' ALL TITLES :\n'
print ' TEST CALENDAR (Coming soon)'
print ' EVENT CALENDAR (Coming soon)'
print ''
print '======================================================================='
print ' LEGEND'
print '=======================================================================\n'
print ' EVENT DETAILS :\n'
print ' ONP: Ops Needs Points event'
print ' IMM: Immortal event'
print ' TUT: Tutorial event'
print ' ID: AI base/attack id'
print ' UNT: Number of AI units'
print ' DPS: Total harmful damage per second of AI units/turrets'
print ' HLT: Total max health of AI units/turrets'
print ' SPC: Total unit space taken up by AI units'
print ' TUR: Number of turrets'
print ' SPT: Total sprite count of AI buildings, units, and scenery for gauging frame rates.'
print ' CCL: Estimated difficulty in terms of CC level.'
print ''
if output == 'csv':
print 'Level, Type, Base ID, Estimate CCL, ONP, Unit Count, Unit Health, Unit DPS, Unit Space, Turret Count, Turret Health, Turret DPS, Sprite Count, Loot'
if game_id is 'ALL':
for id in GAME_IDS:
try:
gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id = id)))
basedata = SpinJSON.load(open('./%s/built/%s_ai_bases_compiled.json' % (id, id)))
hivedata = SpinJSON.load(open('./%s/built/%s_hives_compiled.json' % (id, id)))
lootdata = SpinJSON.load(open('./%s/built/%s_loot_tables_compiled.json' % (id, id)))
except IOError:
print 'ERROR: Can\'t find compiled gamedata files for %s. Please run cd ../gameserver; ./make-gamedata.sh -u -g %s\n' % (id, id)
return
if output == 'txt':
print_event_details_txt(id)
elif output == 'csv':
print_event_details_csv(id)
else:
if game_id not in GAME_IDS:
print 'Invalid game id: %s' % game_id
return
try:
gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id = game_id)))
basedata = SpinJSON.load(open('./%s/built/%s_ai_bases_compiled.json' % (game_id, game_id)))
hivedata = SpinJSON.load(open('./%s/built/%s_hives_compiled.json' % (game_id, game_id)))
lootdata = SpinJSON.load(open('./%s/built/%s_loot_tables_compiled.json' % (game_id, game_id)))
except IOError:
print 'ERROR: Can\'t find compiled gamedata files for %s. Please run cd ../gameserver; ./make-gamedata.sh -u -g %s\n' % (game_id, game_id)
return
if output == 'txt':
print_event_details_txt(game_id)
elif output == 'csv':
print_event_details_csv(game_id)
def do_slave(input):
cache = open_cache(input['game_id'], input['cache_info'])
batch = 0
total = 0
gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id = input['game_id'])))
gamedata['ai_bases'] = SpinJSON.load(open(SpinConfig.gamedata_component_filename('ai_bases_compiled.json', override_game_id = input['game_id'])))
gamedata['loot_tables'] = SpinJSON.load(open(SpinConfig.gamedata_component_filename('loot_tables.json', override_game_id = input['game_id'])))
if input['mode'] == 'get_fields':
fields = {'money_spent': 'FLOAT4', # force this column into existence because analytics_views.sql depends on it
'account_creation_time': 'INT8', # same here
'country_tier': 'CHAR(1)', 'country': 'CHAR(2)',
'acquisition_campaign': 'VARCHAR(64)',
'acquisition_ad_skynet': 'VARCHAR(128)',
# these fields are extracted from compound objects inside of "user"
'connection_method': 'VARCHAR(32)',
'last_ping': 'FLOAT4',
'last_direct_ssl_ping': 'FLOAT4',
'playfield_speed': 'INT2',
}
for user in cache.iter_segment(input['segnum']):
for key, val in user.iteritems():
if key not in fields:
field = setup_field(gamedata, key, val, field_mode = input['field_mode'])
if field is not None:
fields[key] = field
batch += 1
total += 1
if batch >= 1000:
batch = 0
if input['verbose']: print >> sys.stderr, 'seg', input['segnum'], 'user', total
return fields
elif input['mode'] == 'get_rows':
sql_util = SpinSQLUtil.MySQLUtil()
if not input['verbose']: sql_util.disable_warnings()
sorted_field_names = input['sorted_field_names']
cfg = input['dbconfig']
con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
cur = con.cursor()
# buffer up keyvals to be updated in the achievement tables
upgrade_achievement_counters = {}
def flush():
con.commit() # commit other tables first
# MySQL often throws deadlock exceptions when doing upserts that reference existing rows (!)
# in the upgrade_achievements table, so we need to loop on committing these updates
deadlocks = 0
while True:
try:
cur.executemany("INSERT INTO "+sql_util.sym(input['upgrade_achievement_table']) + \
" (" + ','.join([x[0] for x in sql_util.summary_out_dimensions()]) + ", kind, spec, level, is_maxed, num_players) " + \
" VALUES (" + ','.join(['%s'] * len(sql_util.summary_out_dimensions())) + ", %s, %s, %s, %s, %s) " + \
" ON DUPLICATE KEY UPDATE num_players = num_players + %s",
[k + (v,v) for k,v in upgrade_achievement_counters.iteritems()])
con.commit()
upgrade_achievement_counters.clear()
break
except MySQLdb.OperationalError as e:
if e.args[0] == 1213: # deadlock
con.rollback()
deadlocks += 1
continue
else:
raise
if input['verbose']: print >> sys.stderr, 'seg', input['segnum'], total, 'flushed', deadlocks, 'deadlocks'
for user in cache.iter_segment(input['segnum']):
user_id = user['user_id']
keys = [x for x in sorted_field_names if x in user]
values = [user[x] for x in keys]
# manual parsing of sprobe fields
if ('last_sprobe_result' in user):
connection_method = None
if ('connection' in user['last_sprobe_result']['tests']):
connection_method = user['last_sprobe_result']['tests']['connection'].get('method',None)
if connection_method:
keys.append('connection_method')
values.append(connection_method)
if (connection_method in user['last_sprobe_result']['tests']) and ('ping' in user['last_sprobe_result']['tests'][connection_method]):
keys.append('last_ping')
values.append(user['last_sprobe_result']['tests'][connection_method]['ping'])
if ('direct_ssl' in user['last_sprobe_result']['tests']) and ('ping' in user['last_sprobe_result']['tests']['direct_ssl']):
keys.append('last_direct_ssl_ping')
values.append(user['last_sprobe_result']['tests']['direct_ssl']['ping'])
# manual parsing of other compound fields
prefs = user.get('player_preferences', None)
if prefs:
if 'playfield_speed' in prefs:
keys.append('playfield_speed')
values.append(prefs['playfield_speed'])
cur.execute("INSERT INTO " + input['upcache_table'] + \
"(user_id, "+', '.join(['`'+x+'`' for x in keys])+")"+ \
" VALUES (%s, "+', '.join(['%s'] * len(values)) +")",
[user_id,] + values)
# we need the summary dimensions for achievement tables
summary_keyvals = [('frame_platform', user.get('frame_platform',None)),
('country_tier', str(user['country_tier']) if user.get('country_tier',None) else None),
('townhall_level', user.get(gamedata['townhall']+'_level',1)),
('spend_bracket', sql_util.get_spend_bracket(user.get('money_spent',0)))]
# parse townhall progression
if input['do_townhall'] and ('account_creation_time' in user):
ts_key = gamedata['townhall']+'_level_at_time'
if ts_key in user:
cur.executemany("INSERT INTO " +sql_util.sym(input['townhall_table']) + \
" (user_id,townhall_level,time) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE user_id=user_id;",
[(user['user_id'], level, user['account_creation_time'] + int(sage)) for sage, level in user[ts_key].iteritems()]
)
# parse tech unlock timing
if input['do_tech']:
cur.executemany("INSERT INTO "+sql_util.sym(input['tech_table']) + " (user_id, tech_name, level, time) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE user_id=user_id;",
[(user['user_id'], tech, level, user['account_creation_time'] + int(sage)) \
for tech in gamedata['tech'] \
for sage, level in user.get('tech:'+tech+'_at_time', {}).iteritems()
])
# summary dimensions, kind, spec, level, is_maxed
for spec, level in user.get('tech',{}).iteritems():
if spec in gamedata['tech']:
is_maxed = 1 if (len(gamedata['tech'][spec]['research_time']) > 1 and level >= len(gamedata['tech'][spec]['research_time'])) else 0
k = tuple(x[1] for x in summary_keyvals) + ('tech', spec, level, is_maxed)
upgrade_achievement_counters[k] = upgrade_achievement_counters.get(k,0) + 1
if is_maxed:
# one row for "any" maxed tech
km = tuple(x[1] for x in summary_keyvals) + ('tech', 'ANY', None, 1)
upgrade_achievement_counters[km] = upgrade_achievement_counters.get(km,0) + 1
# parse building upgrade timing
if input['do_buildings']:
cur.executemany("INSERT INTO "+sql_util.sym(input['buildings_table']) + " (user_id, building, max_level, time) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE user_id=user_id;",
[(user['user_id'], building, level, user['account_creation_time'] + int(sage)) \
for building in gamedata['buildings'] \
for sage, level in user.get(building+'_level_at_time', user.get('building:'+building+':max_level_at_time', {})).iteritems()
])
# summary dimensions, kind, spec, level, is_maxed
for spec in gamedata['buildings']:
level = max(user.get('building:'+spec+':max_level_at_time',{'asdf':0}).itervalues())
if level >= 1:
is_maxed = 1 if (len(gamedata['buildings'][spec]['build_time']) > 1 and level >= len(gamedata['buildings'][spec]['build_time'])) else 0
k = tuple(x[1] for x in summary_keyvals) + ('building', spec, level, is_maxed)
upgrade_achievement_counters[k] = upgrade_achievement_counters.get(k,0) + 1
if is_maxed:
# one row for "any" maxed building
km = tuple(x[1] for x in summary_keyvals) + ('building', 'ANY', None, 1)
upgrade_achievement_counters[km] = upgrade_achievement_counters.get(km,0) + 1
# parse sessions
if input['do_sessions'] and ('sessions' in user):
cur.executemany("INSERT INTO "+sql_util.sym(input['sessions_table']) + " (user_id,start,end,frame_platform,country_tier,townhall_level,prev_receipts) VALUES (%s,%s,%s,%s,%s,%s,%s)",
[(user['user_id'], s[0], s[1], user.get('frame_platform','fb'), user.get('country_tier',None),
SpinUpcache.building_level_at_age(user, gamedata['townhall'], s[1] - user['account_creation_time']),
SpinUpcache.receipts_at_age(user, s[1] - user['account_creation_time'])) for s in user['sessions'] if (s[0] > 0 and s[1] > 0 and s[1]>=s[0])])
# parse activity
ACTIVITY_MIN_CC_LEVEL = 5 # only record for CCL5+ players (same as ANALYTICS2)
# note! the source data, from gameserver, omits gamebucks_spent for players who never paid. This is by design to reduce bloat.
if input['do_activity'] and ('activity' in user) and ('account_creation_time' in user) and user.get(gamedata['townhall']+'_level',1) >= ACTIVITY_MIN_CC_LEVEL:
def parse_activity(user, stime, data):
ntime = long(stime)
age = ntime - user['account_creation_time']
cc_level = SpinUpcache.building_level_at_age(user, gamedata['townhall'], age)
if cc_level < ACTIVITY_MIN_CC_LEVEL: return None
act = SpinUpcache.classify_activity(gamedata, data)
return (user['user_id'], ntime, act['state'], act.get('ai_tag', None) or act.get('ai_ui_name', None), data.get('gamebucks_spent',None), data.get('money_spent',None),
user.get('frame_platform','fb'), user.get('country_tier',None), cc_level, SpinUpcache.receipts_at_age(user, age))
cur.executemany("INSERT INTO "+sql_util.sym(input['activity_table']) + \
" (user_id, time, state, ai_ui_name, gamebucks_spent, receipts, frame_platform, country_tier, townhall_level, prev_receipts)" + \
" VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
filter(lambda x: x is not None, (parse_activity(user, stime, data) for stime, data in user['activity'].iteritems() if data['state'] not in ('idle','harvest'))))
# update LTV estimate
if input['do_ltv']:
ltv_est = SkynetLTV.ltv_estimate(input['game_id'], gamedata, user, cache.update_time(), use_post_install_data = 9999999)
if ltv_est is not None:
cur.execute("INSERT INTO "+sql_util.sym(input['ltv_table']) + " (user_id, est_90d) VALUES (%s,%s) ON DUPLICATE KEY UPDATE user_id=user_id;",
(user['user_id'], ltv_est))
batch += 1
total += 1
if input['commit_interval'] > 0 and batch >= input['commit_interval']:
batch = 0
flush()
# flush last commits
flush()
