def insert_entity(entity, realm_name_to_id):
realm_id = realm_name_to_id[entity.realm]
# We only care about the realm ID if it's a player or a pet (aka entity_type 1 or 3). Otherwise it doesn't matter what realm something is on
if entity.entity_type is not PLAYER_TYPE and entity.entity_type is not PET_TYPE:
realm_id = 0
owner_id = 0
if entity.pet_owner is not None:
owner = query_first_row(
"SELECT * FROM game_entity WHERE name = :name AND entity_type = 1 AND realm_id = :realmId",
{
'name': entity.pet_owner,
'realmId': realm_id
})
owner_id = owner['id']
sql_args = {
'name': entity.name,
'entity_type': entity.entity_type,
'realm_id': realm_id,
'race': getattr(entity, 'race_id', 0),
'gender': getattr(entity, 'gender_id', 0),
'class': getattr(entity, 'class_id', 0),
'owner_id': owner_id,
'source_player': entity.source_player
}
execute_update_params(
"""
INSERT INTO game_entity (`name`, `entity_type`, `realm_id`, `race`, `gender`, `class`, `owner_id`, `source_player`)
VALUES (:name, :entity_type, :realm_id, :race, :gender, :class, :owner_id, :source_player)
ON DUPLICATE KEY UPDATE `source_player` = case when `source_player` = True then `source_player` else :source_player end
""", sql_args)
def insert_event_coordinates(event, event_id):
# Could have been in a dungeon and not had any coordinates
if 'coordinates' not in event:
return
for coordinates in event['coordinates']:
sql_args = {
'event_id': event_id,
'zone_id': coordinates['mapId'],
'x': coordinates['x'],
'y': coordinates['y'],
}
execute_update_params(
"""
INSERT INTO event_coordinates (`event_id`, `zone_id`, `x`, `y`)
VALUES (:event_id, :zone_id, :x, :y)""", sql_args)
def enter_events(new_events, realm_name_to_id):
for event in new_events:
realm_id = realm_name_to_id[event['realm']]
source_player = query_first_row(
"SELECT * FROM game_entity WHERE `name` = :name AND entity_type = 1 AND realm_id = :realm_id",
{
'name': event['sourcePlayer'],
'realm_id': realm_id
})
sql_args = {
'source_player_id': source_player['id'],
'event_time': event['timestamp'],
'movement_type': event['movementType'],
'is_instance': event['isInstance'],
'zone_id': event['mapId']
}
db_result = execute_update_params(
"""
INSERT IGNORE INTO position_event (`source_player_id`, `event_time`, `movement_type`, `is_instance`)
VALUES (:source_player_id, FROM_UNIXTIME(:event_time), :movement_type, :is_instance)""",
sql_args)
event_id = db_result.lastrowid
# If it's 0 then we are inserting a row which already exists and need not insert coordinates
if event_id == 0:
print 'Duplicate row inserted from: ' + event[
'sourcePlayer'] + ' at event time: ' + str(event['timestamp'])
continue
insert_event_coordinates(event, event_id)
def get_realm_ids(new_events):
realm_names = set() # Remove duplicates
for event in new_events:
realm_names.add(event['realm'])
# Make sure new there is an entry for all of our stuff
for name in realm_names:
execute_update_params(
"INSERT IGNORE INTO realm (`name`) VALUES (:name)", {'name': name})
# Now that we know that there are records, grab them out and hold them in memory
realm_name_and_id = query_many("SELECT id, name FROM realm")
realm_name_to_id = {}
for entry in realm_name_and_id:
realm_name_to_id[entry['name']] = entry['id']
return realm_name_to_id
def insert_entity(entity, realm_name_to_id):
realm_id = realm_name_to_id[entity.realm]
sql_args = {
'name': entity.name,
'entity_type': PLAYER_TYPE,
'realm_id': realm_id,
'race': getattr(entity, 'race_id', 0),
'gender': getattr(entity, 'gender_id', 0),
'class': getattr(entity, 'class_id', 0),
'source_player': True
}
execute_update_params(
"""
INSERT INTO game_entity (`name`, `entity_type`, `realm_id`, `race`, `gender`, `class`, `owner_id`, `source_player`)
VALUES (:name, :entity_type, :realm_id, :race, :gender, :class, :owner_id, :source_player)
ON DUPLICATE KEY UPDATE `source_player` = case when `source_player` = True then `source_player` else :source_player end
""", sql_args)
def enter_events(new_events, realm_name_to_id, kill_source_name_to_id):
for event in new_events:
realm_id = realm_name_to_id[event['realm']]
source_player = query_first_row(
"SELECT * FROM game_entity WHERE `name` = :name AND entity_type = 1 AND realm_id = :realm_id",
{
'name': event['sourcePlayer'],
'realm_id': realm_id
})
killer_owner_id = 0
if 'killerPetOwner' in event:
killer_owner = query_first_row(
"SELECT * FROM game_entity WHERE name = :name AND entity_type = 1 AND realm_id = :realm_id AND owner_id = 0",
{
'name': event['killerPetOwner'],
'realm_id': realm_id
})
killer_owner_id = killer_owner['id']
killer = query_first_row(
"SELECT * FROM game_entity WHERE name = :name AND entity_type = :entity_type AND realm_id = :realm_id AND owner_id = :owner_id",
{
'name':
event['killerName'],
'entity_type':
event['killerType'],
'realm_id':
0 if event['killerType'] is not PLAYER_TYPE
and event['killerType'] is not PET_TYPE else realm_id,
'owner_id':
killer_owner_id
})
victim_owner_id = 0
if 'victimPetOwner' in event:
victim_owner = query_first_row(
"SELECT * FROM game_entity WHERE name = :name AND entity_type = 1 AND realm_id = :realm_id AND owner_id = 0",
{
'name': event['victimPetOwner'],
'realm_id': realm_id
})
victim_owner_id = victim_owner['id']
victim = query_first_row(
"SELECT * FROM game_entity WHERE name = :name AND entity_type = :entity_type AND realm_id = :realm_id AND owner_id = :owner_id",
{
'name':
event['victimName'],
'entity_type':
event['victimType'],
'realm_id':
0 if event['victimType'] is not PLAYER_TYPE
and event['victimType'] is not PET_TYPE else realm_id,
'owner_id':
victim_owner_id
})
event_hash = hashlib.sha256(event['killerId'] + event['victimId'] +
event['sourcePlayer'] + event['realm'] +
str(event['timestamp']))
# noinspection PyBroadException
try:
sql_args = {
'source_player_id': source_player['id'],
'source_player_level': event['sourceLevel'],
'killer_id': killer['id'],
'killer_level': event.get('killerLevel', -1),
'victim_id': victim['id'],
'victim_level': event.get('victimLevel', -1),
'kill_source_id': kill_source_name_to_id[event['killSource']],
'zone_id': event['mapId'],
'is_instance': event['isInstance'],
'event_time': event['timestamp'],
'realm_id': realm_id,
'event_hash': event_hash.hexdigest()
}
db_result = execute_update_params(
"""
INSERT IGNORE INTO kill_event (`source_player_id`, `killer_id`, `victim_id`, `zone_id`, `is_instance`, `kill_source_id`,
`killer_level`, `victim_level`, `source_player_level`, `event_time`, `realm_id`, `event_hash`)
VALUES (:source_player_id, :killer_id, :victim_id, :zone_id, :is_instance, :kill_source_id,
:killer_level, :victim_level, :source_player_level, FROM_UNIXTIME(:event_time), :realm_id, :event_hash)
""", sql_args)
except Exception as e:
print 'Failed to insert record. Storing in error table and continuing'
print e
print event
execute_update_params(
"""INSERT INTO error_events (`event_data`, `reason`) VALUES (:event_data, :reason)""",
{
'event_data': str(event),
'reason': str(e)
})
continue
event_id = db_result.lastrowid
# If it's 0 then we are inserting a row which already exists and need not insert coordinates
if event_id == 0:
print 'Duplicate row inserted from: ' + event[
'sourcePlayer'] + ' at event time: ' + str(event['timestamp'])
continue
insert_event_coordinates(event, event_id)