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()
do_optimize = False
opts, args = getopt.gnu_getopt(sys.argv[1:], 'g:c:q',
['prune', 'optimize'])
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 == '--prune': do_prune = True
elif key == '--optimize': do_optimize = True
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'])
cur = con.cursor(MySQLdb.cursors.DictCursor)
# INPUTS
upcache_table = cfg['table_prefix'] + game_id + {
'mf': '_upcache_lite'
}.get(game_id, '_upcache')
sessions_table = cfg['table_prefix'] + game_id + '_sessions'
# OUTPUTS
acquisitions_table = cfg['table_prefix'] + game_id + '_acquisitions'
acquisitions_daily_summary_table = cfg[
'table_prefix'] + game_id + '_acquisitions_daily_summary'
sql_util.ensure_table(cur, acquisitions_table,
for key, val in opts:
if key == '-q': verbose = False
elif key == '--prune': do_prune = True
elif key == '--min-sec': min_sec = int(val)
elif key == '--dry-run': dry_run = True
elif key == '--email': email_to = val
if len(args) < 1:
sys.stderr.write('please specify mysql_servers entry\n')
sys.exit(1)
mysql_server = args[0]
sql_util = SpinSQLUtil.MySQLUtil()
if not verbose: sql_util.disable_warnings()
cfg = SpinConfig.get_mysql_config(mysql_server)
con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
cur = con.cursor(MySQLdb.cursors.DictCursor)
subject = 'Automated reminder from ' + socket.gethostname()
body = None
cur.execute(
'''SELECT start_time, user_host, TIME_TO_SEC(query_time) AS total_sec, db, sql_text
FROM mysql.slow_log
WHERE start_time >= NOW() - INTERVAL 24 HOUR and TIME_TO_SEC(query_time) >= %s
ORDER BY total_sec DESC;''', [
min_sec,
])
rows = cur.fetchall()
opts, args = getopt.gnu_getopt(sys.argv[1:], 'g:c:q',
['dry-run', 'source='])
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 == '--source':
assert val in ('mongodb', 's3')
source = val
sql_util = SpinSQLUtil.MySQLUtil()
if not verbose: sql_util.disable_warnings()
cfg = SpinConfig.get_mysql_config('skynet')
con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
conversions_table = cfg['table_prefix'] + 'conversions'
cur = con.cursor(MySQLdb.cursors.DictCursor)
if not dry_run:
sql_util.ensure_table(cur, conversions_table, conversions_schema)
con.commit()
# find most recent already-converted action
start_time = calendar.timegm(
[2013, 9, 1, 0, 0,
0]) # this is about when we started recording this data
end_time = time_now - 60 # don't get too close to "now" since we assume if we have one event for a given second, we have them all
end_time = min(
# tool to quickly invoke mysql on a database using credentials from config.json
import SpinConfig
import os, sys, getopt
if __name__=='__main__':
whichdb = SpinConfig.config['game_id']+'_upcache'
execute = None
mode = 'connect'
opts, args = getopt.gnu_getopt(sys.argv[1:], '', ['sizes'])
for key, val in opts:
if key == '--sizes': mode = 'size'
if len(args)>0:
whichdb = args[0]
if len(args) > 1:
execute = args[1]
conf = SpinConfig.get_mysql_config(whichdb)
cmd_args=['mysql', '-u', conf['username'], "-p"+conf['password'], '-h', conf['host'], '--port', '%d' % conf['port'], '--protocol=tcp', conf['dbname']]
if mode == 'size':
cmd_args += ['-e',
'''SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 0) as `size (MB)` FROM information_schema.TABLES WHERE table_schema = '%s' and (data_length+index_length) > 10*1024*1024 order by (data_length+index_length) DESC;''' % conf['dbname']]
elif execute:
cmd_args += ['-e', execute]
os.execvp(cmd_args[0], cmd_args)
def do_slave(task):
date = task['date']
game_id = task['game_id']
verbose = task['verbose']
dry_run = task['dry_run']
start_time = SpinConfig.cal_to_unix(
(int(date[0:4]), int(date[4:6]), int(date[6:8])))
end_time = start_time + 86400
if verbose:
print >> sys.stderr, 'converting date', date, 'start_time', start_time, 'end_time', end_time, '...'
# gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id = game_id)))
if not verbose: filterwarnings('ignore', category=MySQLdb.Warning)
quarries = SpinConfig.load(
SpinConfig.gamedata_component_filename('quarries_compiled.json'))
hives = SpinConfig.load(
SpinConfig.gamedata_component_filename('hives_compiled.json'))
# ensure that the spawn list is ordered by id_start - necessary for find_template() below
for spawn_list in quarries['spawn'], hives['spawn']:
spawn_list.sort(key=lambda x: x['id_start'])
cfg = SpinConfig.get_mysql_config(game_id + '_upcache')
con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
battles_table = cfg['table_prefix'] + game_id + '_battles'
if 0:
# find any already-converted battles
cur = con.cursor()
cur.execute(
"SELECT COUNT(*) FROM %s WHERE time >= %%s and time < %%s" %
battles_table, (start_time, end_time))
row = cur.fetchone()
con.commit()
if row and row[0] > 0:
print >> sys.stderr, 'there are already', row[
0], 'entries in this time range, aborting!'
return
s3 = SpinS3.S3(SpinConfig.aws_key_file())
bucket = 'spinpunch-%sprod-battle-logs' % game_id
for entry in s3.list_bucket(bucket,
prefix='%s-battles-%s/%s' %
(game_id, date[0:6], date)):
filename = entry['name'].split('/')[-1]
event_time, attacker_id, defender_id, base_id = parse_battle_log_filename(
filename)
if (not base_id) or event_time < start_time or event_time >= end_time:
continue
if base_id[0] != 'v': continue # only look at hives
print >> sys.stderr, event_time, SpinLog.pretty_time(
time.gmtime(event_time)), filename
fd = s3.get_open(bucket, entry['name'], allow_keepalive=False)
unzipper = subprocess.Popen(['gunzip', '-c', '-'],
stdin=fd.fileno(),
stdout=subprocess.PIPE)
battle_start = None
battle_end = None
for line in unzipper.stdout.xreadlines():
if '3820_battle_start' in line:
battle_start = SpinJSON.loads(line)
elif '3830_battle_end' in line:
battle_end = SpinJSON.loads(line)
if (not battle_start) or (not battle_end): continue
base_template = find_template(hives['spawn'], int(base_id[1:]))
if not base_template:
sys.stderr.write('unknown hive %s\n' % base_id)
continue
# generate a fake summary
summary = {
'time':
event_time,
'attacker_id':
battle_start['attacker_user_id'],
'attacker_level':
battle_start['attacker_level'],
'attacker_outcome':
battle_end['battle_outcome'],
'defender_id':
battle_start['opponent_user_id'],
'defender_level':
battle_start['opponent_level'],
'defender_outcome':
'victory'
if battle_end['battle_outcome'] == 'defeat' else 'defeat',
'base_damage':
battle_end['base_damage'],
'base_id':
battle_start['base_id'],
'base_type':
'hive',
'base_template':
base_template,
'loot':
battle_end['loot']
}
cur = con.cursor()
cur.execute(
"SELECT battle_id FROM %s WHERE time = %%s and attacker_id = %%s and defender_id = %%s"
% battles_table, (event_time, battle_start['attacker_user_id'],
battle_start['opponent_user_id']))
row = cur.fetchone()
con.commit()
if row:
sys.stderr.write('appears to be a duplicate, skipping!\n')
continue
id_generator.set_time(int(time.time()))
battle_id = id_generator.generate() # arbitrary
keys = [
'battle_id',
]
values = [
battle_id,
]
for kname, ktype in battle_fields.iteritems():
path = kname.split(':')
probe = summary
val = None
for i in xrange(len(path)):
if path[i] not in probe:
break
elif i == len(path) - 1:
val = probe[path[i]]
break
else:
probe = probe[path[i]]
if val is not None:
keys.append(kname)
values.append(val)
query = "INSERT INTO " + battles_table + \
"("+', '.join(['`'+x+'`' for x in keys])+")"+ \
" VALUES ("+', '.join(['%s'] * len(values)) +")"
print >> sys.stderr, query
print >> sys.stderr, values
if not dry_run:
cur = con.cursor()
cur.execute(query, values)
con.commit()
fix_missing_data = False
opts, args = getopt.gnu_getopt(sys.argv[1:], 'g:c:q',
['dry-run', 'fix-missing-data'])
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 == '--fix-missing-data': fix_missing_data = True
sql_util = SpinSQLUtil.MySQLUtil()
if not verbose: sql_util.disable_warnings()
cfg = SpinConfig.get_mysql_config(
'skynet' if fix_missing_data else 'skynet_readonly')
con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs'])
adstats_table = cfg['table_prefix'] + 'adstats_hourly'
nosql_config = SpinConfig.get_mongodb_config('skynet')
nosql_client = pymongo.MongoClient(*nosql_config['connect_args'],
**nosql_config['connect_kwargs'])
nosql_db = nosql_client[nosql_config['dbname']]
cur = con.cursor()
if not dry_run:
sql_util.ensure_table(cur, adstats_table, adstats_schema)
con.commit()
# find most recent entry
start_time = -1
