def get_affil_names(conn, cur):
# use select distinct to get corp and alliance ids and reslove them
alliance_ids = cur.execute('''SELECT DISTINCT alliance_id FROM characters
WHERE alliance_id IS NOT 0''').fetchall()
corp_ids = cur.execute(
"SELECT DISTINCT corp_id FROM characters WHERE corp_id IS NOT 0"
).fetchall()
ids = alliance_ids + corp_ids
ids = json.dumps(tuple([r[0] for r in ids]))
statusmsg.push_status(
"Obtaining corporation and alliance names and zKillboard data...")
try:
names = apis.post_req_ccp("universe/names/", ids)
except:
Logger.info("Failed request corporation and alliance names.",
exc_info=True)
raise Exception
alliances, corporations = (), ()
for r in names:
if r["category"] == "alliance":
alliances = alliances + ((r["id"], r["name"]), )
elif r["category"] == "corporation":
corporations = corporations + ((r["id"], r["name"]), )
if alliances:
query_string = ('''INSERT INTO alliances (id, name) VALUES (?, ?)''')
db.write_many_to_db(conn, cur, query_string, alliances)
if corporations:
query_string = (
'''INSERT INTO corporations (id, name) VALUES (?, ?)''')
db.write_many_to_db(conn, cur, query_string, corporations)
def get_char_affiliations(conn, cur):
char_ids = cur.execute("SELECT char_id FROM characters").fetchall()
char_ids = json.dumps(tuple([r[0] for r in char_ids]))
statusmsg.push_status("Retrieving character affiliation IDs...")
try:
affiliations = apis.post_req_ccp("characters/affiliation/", char_ids)
except:
Logger.info("Failed to obtain character affiliations.", exc_info=True)
raise Exception
records = ()
for r in affiliations:
corp_id = r["corporation_id"]
alliance_id = r["alliance_id"] if "alliance_id" in r else 0
faction_id = r["faction_id"] if "faction_id" in r else 0
char_id = r["character_id"]
records = records + ((corp_id, alliance_id, faction_id, char_id), )
query_string = (
'''UPDATE characters SET corp_id=?, alliance_id=?, faction_id=?
WHERE char_id=?''')
db.write_many_to_db(conn, cur, query_string, records)
def get_character_intel(conn, cur):
'''
Adds certain character killboard statistics derived from PySpy's
proprietary database to the local SQLite3 database.
:param `conn`: SQLite3 connection object.
:param `cur`: SQLite3 cursor object.
'''
char_ids = cur.execute("SELECT char_id FROM characters").fetchall()
char_intel = apis.post_proprietary_db(char_ids)
records = ()
for r in char_intel:
char_id = r["character_id"]
last_loss_date = r["last_loss_date"] if r[
"last_loss_date"] is not None else 0
last_kill_date = r["last_kill_date"] if r[
"last_kill_date"] is not None else 0
avg_attackers = r["avg_attackers"] if r[
"avg_attackers"] is not None else 0
covert_prob = r["covert_prob"] if r["covert_prob"] is not None else 0
normal_prob = r["normal_prob"] if r["normal_prob"] is not None else 0
last_cov_ship = r["last_cov_ship"] if r[
"last_cov_ship"] is not None else 0
last_norm_ship = r["last_norm_ship"] if r[
"last_norm_ship"] is not None else 0
abyssal_losses = r["abyssal_losses"] if r[
"abyssal_losses"] is not None else 0
records = records + ((last_loss_date, last_kill_date, avg_attackers,
covert_prob, normal_prob, last_cov_ship,
last_norm_ship, abyssal_losses, char_id), )
query_string = (
'''UPDATE characters SET last_loss_date=?, last_kill_date=?,
avg_attackers=?, covert_prob=?, normal_prob=?,
last_cov_ship=?, last_norm_ship=?, abyssal_losses=?
WHERE char_id=?''')
db.write_many_to_db(conn, cur, query_string, records)
def main(char_names):
conn, cur = db.connect_db()
chars_found = get_char_ids(conn, cur, char_names)
if chars_found > 0:
char_ids = cur.execute(
"SELECT char_id FROM characters ORDER BY char_name").fetchall()
q_main = queue.Queue()
t = zKillStats(char_ids, q_main)
t.start()
get_char_affiliations(conn, cur)
get_affil_names(conn, cur)
t.join()
zkill_stats = q_main.get()
query_string = (
'''UPDATE characters SET kills=?, blops_kills=?, hic_losses=?,
week_kills=?
WHERE char_id=?''')
db.write_many_to_db(conn, cur, query_string, zkill_stats)
output = output_list(cur)
conn.close()
return output
else:
return
def get_char_ids(conn, cur, char_names):
char_names = json.dumps(
char_names[0:config.MAX_NAMES]) # apis max char is 1000
statusmsg.push_status("Resolving character names to IDs...")
try:
characters = apis.post_req_ccp("universe/ids/", char_names)
characters = characters['characters']
except:
return 0
records = ()
for r in characters:
records = records + ((r["id"], r["name"]), )
query_string = (
'''INSERT INTO characters (char_id, char_name) VALUES (?, ?)''')
records_added = db.write_many_to_db(conn, cur, query_string, records)
return records_added
def main(char_names, conn_mem, cur_mem, conn_dsk, cur_dsk):
chars_found = get_char_ids(conn_mem, cur_mem, char_names)
if chars_found > 0:
# Run Pyspy remote database query in seprate thread
tp = threading.Thread(target=get_character_intel(conn_mem, cur_mem),
daemon=True)
tp.start()
# Run zKill query in seprate thread
char_ids_mem = cur_mem.execute(
"SELECT char_id, last_update FROM characters ORDER BY char_name"
).fetchall()
cache_max_age = datetime.datetime.now() - datetime.timedelta(
seconds=config.CACHE_TIME)
char_ids_dsk = cur_dsk.execute(
"SELECT char_id, last_update FROM characters WHERE last_update > ? ORDER BY char_name",
(cache_max_age, )).fetchall()
char_ids_mem_d = dict(char_ids_mem)
char_ids_dsk_d = dict(char_ids_dsk)
ids_mem = set(char_ids_mem_d.keys())
ids_dsk = set(char_ids_dsk_d.keys())
cache_hits = ids_mem & ids_dsk # Intersection of what we want and what we already have
cache_miss = ids_mem - cache_hits
Logger.debug("Cache Hits - {}".format(len(cache_hits)))
Logger.debug("Cache Miss - {}".format(len(cache_miss)))
zkill_req = [r for r in char_ids_mem if r[0] in cache_miss]
q_main = queue.Queue()
tz = zKillStats(zkill_req, q_main)
tz.start()
get_char_affiliations(conn_mem, cur_mem)
get_affil_names(conn_mem, cur_mem)
# Join zKill thread
tz.join()
update_datetime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
zkill_stats = q_main.get()
for entry in zkill_stats:
entry.insert(-1, update_datetime)
query_string = (
'''UPDATE characters SET kills=?, blops_kills=?, hic_losses=?,
week_kills=?, losses=?, solo_ratio=?, sec_status=?, last_update=?
WHERE char_id=?''')
cache_stats = []
for char_id in cache_hits:
# kills, blops_kills, hic_losses, week_kills, losses, solo_ratio, sec_status, id
cache_query = '''SELECT kills, blops_kills, hic_losses, week_kills, losses, solo_ratio,
sec_status, last_update, char_id FROM characters WHERE char_id = ?'''
stat = tuple(cur_dsk.execute(
cache_query,
(char_id, )).fetchone()) #SHOULD ONLY BE ONE ENTRY!!!
cache_stats.append(stat)
cache_char_query_string = (
'''INSERT OR REPLACE INTO characters (char_id, char_name) VALUES (?, ?)'''
)
db.write_many_to_db(conn_dsk, cur_dsk, cache_char_query_string,
zkill_req)
db.write_many_to_db(conn_dsk, cur_dsk, query_string, zkill_stats)
db.write_many_to_db(conn_mem, cur_mem, query_string, zkill_stats)
db.write_many_to_db(conn_mem, cur_mem, query_string, cache_stats)
# Join Pyspy remote database thread
tp.join()
output = output_list(cur_mem)
conn_mem.close()
return output
else:
return