import os import sqlite3 import sys import time import urllib2 import xml.etree.ElementTree as ET # Set up our environment and import settings os.environ['DJANGO_SETTINGS_MODULE'] = 'evething.settings' from django.conf import settings from thing.models import * # --------------------------------------------------------------------------- SDE_FILE = 'esc10-sqlite3-v1.db' STATION_URL = '%s/eve/ConquerableStationList.xml.aspx' % (settings.API_HOST) # Override volume for ships, assembled volume is mostly useless :ccp: PACKAGED = { 25: 2500, # frigate 26: 10000, # cruiser 27: 50000, # battleship 28: 20000, # industrial 31: 500, # shuttle 324: 2500, # assault ship 358: 10000, # heavy assault ship 380: 20000, # transport ship 419: 15000, # battlecruiser 420: 5000, # destroyer 463: 3750, # mining barge 540: 15000, # command ship 541: 5000, # interdictor 543: 3750, # exhumer 830: 2500, # covert ops 831: 2500, # interceptor 832: 10000, # logistics 833: 10000, # force recon 834: 2500, # stealth bomber 893: 2500, # electronic attack ship 894: 10000, # heavy interdictor 898: 50000, # black ops 900: 50000, # marauder 906: 10000, # combat recon 963: 5000, # strategic cruiser } # --------------------------------------------------------------------------- def time_func(text, f): start = time.time() print '=> %s:' % (text), sys.stdout.flush() added = f() print '%d (%0.2fs)' % (added, time.time() - start) class Importer: def __init__(self): if os.path.isfile(SDE_FILE): self.conn = sqlite3.connect(SDE_FILE) self.cursor = self.conn.cursor() else: self.conn = None def import_all(self): if self.conn is not None: time_func('Region', self.import_region) time_func('Constellation', self.import_constellation) time_func('System', self.import_system) time_func('Station', self.import_station) time_func('MarketGroup', self.import_marketgroup) time_func('ItemCategory', self.import_itemcategory) time_func('ItemGroup', self.import_itemgroup) time_func('Item', self.import_item) time_func('Blueprint', self.import_blueprint) time_func('Skill', self.import_skill) time_func('InventoryFlag', self.import_inventoryflag) time_func('Conquerable Station', self.import_conquerable_station) # ----------------------------------------------------------------------- # Regions def import_region(self): added = 0 self.cursor.execute('SELECT regionID, regionName FROM mapRegions WHERE regionName != "Unknown"') bulk_data = {} for row in self.cursor: bulk_data[int(row[0])] = row[1:] data_map = Region.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if id in data_map: continue region = Region( id=id, name=data[0], ) region.save() added += 1 return added # ----------------------------------------------------------------------- # Constellations def import_constellation(self): added = 0 self.cursor.execute('SELECT constellationID,constellationName,regionID FROM mapConstellations') bulk_data = {} for row in self.cursor: id = int(row[0]) if id: bulk_data[id] = row[1:] data_map = Constellation.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if id in data_map or not data[0] or not data[1]: continue con = Constellation( id=id, name=data[0], region_id=data[1], ) con.save() added += 1 return added # ----------------------------------------------------------------------- # Systems def import_system(self): added = 0 self.cursor.execute('SELECT solarSystemID, solarSystemName, constellationID FROM mapSolarSystems') bulk_data = {} for row in self.cursor: id = int(row[0]) if id: bulk_data[id] = row[1:] data_map = System.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if id in data_map or not data[0] or not data[1]: continue system = System( id=id, name=data[0], constellation_id=data[1], ) system.save() added += 1 return added # ----------------------------------------------------------------------- # Stations def import_station(self): added = 0 self.cursor.execute('SELECT stationID, stationName, solarSystemID FROM staStations') bulk_data = {} for row in self.cursor: id = int(row[0]) if id: bulk_data[id] = row[1:] data_map = Station.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if id in data_map or not data[0] or not data[1]: continue station = Station( id=id, name=data[0], system_id=data[1], ) station.save() added += 1 return added # ----------------------------------------------------------------------- # Conquerable stations def import_conquerable_station(self): added = 0 data = urllib2.urlopen(STATION_URL).read() root = ET.fromstring(data) bulk_data = {} # for row in root.findall('result/rowset/row'): bulk_data[int(row.attrib['stationID'])] = row data_map = Station.objects.in_bulk(bulk_data.keys()) for id, row in bulk_data.items(): station = data_map.get(id, None) if station is not None: # update the station name if station.name != row.attrib['stationName']: station.name = row.attrib['stationName'] station.save() continue station = Station( id=id, name=row.attrib['stationName'], system_id=row.attrib['solarSystemID'], ) station.save() added += 1 return added # ----------------------------------------------------------------------- # Market groups def import_marketgroup(self): added = 0 self.cursor.execute('SELECT marketGroupID, marketGroupName, parentGroupID FROM invMarketGroups') bulk_data = {} for row in self.cursor: id = int(row[0]) if id: bulk_data[id] = row[1:] data_map = MarketGroup.objects.in_bulk(bulk_data.keys()) while bulk_data: items = list(bulk_data.items()) for id, data in items: # if we've already added this marketgroup, cache and skip if id in data_map: del bulk_data[id] continue if data[1] is None: parent = None else: # if the parent id doesn't exist yet we have to do this later try: parent = MarketGroup.objects.get(pk=data[1]) except MarketGroup.DoesNotExist: continue mg = MarketGroup( id=id, name=data[0], parent=parent, ) mg.save() added += 1 del bulk_data[id] return added # ----------------------------------------------------------------------- # Item Categories def import_itemcategory(self): added = 0 self.cursor.execute('SELECT categoryID, categoryName FROM invCategories') bulk_data = {} for row in self.cursor: id = int(row[0]) if id and row[1]: bulk_data[id] = row[1:] data_map = ItemCategory.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if id in data_map or not data[0]: continue ic = ItemCategory( id=id, name=data[0], ) ic.save() added += 1 return added # ----------------------------------------------------------------------- # Item Groups def import_itemgroup(self): added = 0 self.cursor.execute('SELECT groupID, groupName, categoryID FROM invGroups') bulk_data = {} for row in self.cursor: id = int(row[0]) if id and row[2]: bulk_data[id] = row[1:] data_map = ItemGroup.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if data[1]: continue ig = data_map.get(id, None) if ig is not None: if ig.name != data[0]: print '==> Renamed %r to %r' % (ig.name, data[0]) ig.name = data[0] ig.save() continue ig = ItemGroup( id=id, name=data[0], category_id=data[1], ) ig.save() added += 1 return added # ----------------------------------------------------------------------- # Items def import_item(self): added = 0 self.cursor.execute('SELECT typeID, typeName, groupID, marketGroupID, portionSize, volume FROM invTypes') bulk_data = {} for row in self.cursor: bulk_data[int(row[0])] = row[1:] data_map = Item.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if not data[1]: continue # handle renamed items item = data_map.get(id, None) if item is not None: if item.name != data[0]: print '==> Renamed %r to %r' % (item.name, data[0]) item.name = data[0] item.save() continue item = Item( id=id, name=data[0], item_group_id=data[1], market_group_id=data[2], portion_size=data[3], volume=PACKAGED.get(int(data[1]), data[4]), ) item.save() added += 1 return added # ----------------------------------------------------------------------- def import_blueprint(self): # Blueprints added = 0 self.cursor.execute(""" SELECT b.blueprintTypeID, t.typeName, b.productTypeID, b.productionTime, b.productivityModifier, b.materialModifier, b.wasteFactor FROM invBlueprintTypes AS b INNER JOIN invTypes AS t ON b.blueprintTypeID = t.typeID WHERE t.published = 1 """) bulk_data = {} for row in self.cursor: bulk_data[int(row[0])] = row[1:] data_map = Blueprint.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if not data[0] or not data[1]: continue bp = data_map.get(id, None) if bp is not None: if bp.name != data[0]: print '==> Renamed %r to %r' % (bp.name, data[0]) bp.name = data[0] bp.save() continue bp = Blueprint( id=id, name=data[0], item_id=data[1], production_time=data[2], productivity_modifier=data[3], material_modifier=data[4], waste_factor=data[5], ) bp.save() added += 1 # Base materials self.cursor.execute('SELECT materialTypeID, quantity FROM invTypeMaterials WHERE typeID=?', (data[1],)) for baserow in self.cursor: bpc = BlueprintComponent( blueprint_id=id, item_id=baserow[0], count=baserow[1], needs_waste=True, ) bpc.save() added += 1 # Extra materials. activityID 1 is manufacturing - categoryID 16 is skill requirements self.cursor.execute(""" SELECT r.requiredTypeID, r.quantity FROM ramTypeRequirements AS r INNER JOIN invTypes AS t ON r.requiredTypeID = t.typeID INNER JOIN invGroups AS g ON t.groupID = g.groupID WHERE r.typeID = ? AND r.activityID = 1 AND g.categoryID <> 16 """, (id,)) for extrarow in self.cursor: bpc = BlueprintComponent( blueprint_id=id, item_id=extrarow[0], count=extrarow[1], needs_waste=False, ) bpc.save() added += 1 return added # ----------------------------------------------------------------------- # Skills def import_skill(self): added = 0 skills = {} self.cursor.execute(""" SELECT DISTINCT invTypes.typeID, CAST(dgmTypeAttributes.valueFloat AS integer) AS rank FROM invTypes INNER JOIN invGroups ON (invTypes.groupID = invGroups.groupID) INNER JOIN dgmTypeAttributes ON (invTypes.typeID = dgmTypeAttributes.typeID) WHERE invGroups.categoryID = 16 AND invTypes.published = 1 AND dgmTypeAttributes.attributeID = 275 AND dgmTypeAttributes.valueFloat IS NOT NULL ORDER BY invTypes.typeID """) for row in self.cursor: skills[row[0]] = { 'rank': row[1], } # Primary/secondary attributes self.cursor.execute(""" SELECT typeID, attributeID, valueInt, valueFloat FROM dgmTypeAttributes WHERE attributeID IN (180, 181) """) for row in self.cursor: # skip unpublished skill = skills.get(row[0], None) if skill is None: continue if row[1] == 180: k = 'pri' else: k = 'sec' if row[2]: skill[k] = row[2] else: skill[k] = row[3] # filter skills I guess skill_map = {} for skill in Skill.objects.all(): skill_map[skill.item_id] = skill for id, data in skills.items(): # TODO: add value verification if id in skill_map: continue skill = Skill( item_id=id, rank=data['rank'], primary_attribute=data['pri'], secondary_attribute=data['sec'], ) skill.save() added += 1 return added # :skills: # :prerequisite: # These are the attribute ids for skill prerequisites. [item, level] # 1: [182, 277] # 2: [183, 278] # 3: [184, 279] # 4: [1285, 1286] # 5: [1289, 1287] # 6: [1290, 1288] # :primary_attribute: 180 # database attribute ID for primary attribute # :secondary_attribute: 181 # database attribute ID for secondary attribute # :attributes: # Mapping of id keys to the actual attribute # 165: :intelligence # 164: :charisma # 166: :memory # 167: :perception # 168: :willpower # ----------------------------------------------------------------------- # InventoryFlags def import_inventoryflag(self): added = 0 self.cursor.execute('SELECT flagID, flagName, flagText FROM invFlags') bulk_data = {} for row in self.cursor: bulk_data[int(row[0])] = row[1:] data_map = InventoryFlag.objects.in_bulk(bulk_data.keys()) for id, data in bulk_data.items(): if not data[0] or not data[1]: continue # handle renamed flags flag = data_map.get(id, None) if flag is not None: if flag.name != data[0] or flag.text != data[1]: print '==> Renamed %r to %r' % (flag.name, data[0]) flag.name = data[0] flag.text = data[1] flag.save() continue flag = InventoryFlag( id=id, name=data[0], text=data[1], ) flag.save() added += 1 return added if __name__ == '__main__': importer = Importer() importer.import_all()