sde.py

#!/usr/bin/env python

from sqlalchemy import (
    create_engine,
    Column,
    Float,
    Integer,
    MetaData,
    String,
    Table,
    )
from sqlalchemy.orm import create_session
from sqlalchemy.ext.declarative import declarative_base

import math
import unittest

_initialized = False
def initialize(db_uri):
    global _initialized
    assert not _initialized
    _initialized = True

    global engine, metadata, session
    engine = create_engine(db_uri, encoding='iso-8859-15')
    metadata = MetaData(bind=engine)

    global Items, Reactions, FuelConsumption, Groups, MarketGroups, SolarSystems, Regions, MapDenormalize, TypeAttributes, AttributeTypes

    Base = declarative_base()

    class Items(Base):
        # Can't use the autogenerated table here, because the SDE DB has the typeDescription column in invTypes falsely declared as UTF-8, but actually has CP 1252 data in it. Haha! Therefore, do this by hand to avoid sqlalchemy trying to read the description column.
        __tablename__ = 'invTypes'
#    __table__ = Table('invTypes', metadata, autoload=True)
        typeID = Column(Integer, primary_key=True)
        groupID = Column(Integer)
        typeName = Column(String)
        volume = Column(Float)
        capacity = Column(Float)
        marketGroupID = Column(Integer)

    class Reactions(Base):
        __table__ = Table('invTypeReactions', metadata, autoload=True)

    class FuelConsumption(Base):
        __table__ = Table('invControlTowerResources', metadata, autoload=True)

    class Groups(Base):
        __table__ = Table('invGroups', metadata, autoload=True)

    class MarketGroups(Base):
        __table__ = Table('invMarketGroups', metadata, autoload=True)

    class SolarSystems(Base):
        __table__ = Table('mapSolarSystems', metadata, autoload=True)

    class Regions(Base):
        __table__ = Table('mapRegions', metadata, autoload=True)

    class MapDenormalize(Base):
        __table__ = Table('mapDenormalize', metadata, autoload=True)

    class TypeAttributes(Base):
        __table__ = Table('dgmTypeAttributes', metadata, autoload=True)

    class AttributeTypes(Base):
        __table__ = Table('dgmAttributeTypes', metadata, autoload=True)

    session = create_session(bind=engine)

    global sde
    sde = SDE()

class Location:
    def __init__(self, locationid=None, location_string=None):
        if location_string and not locationid:
            q = session.query(MapDenormalize).filter_by(itemName=location_string).first()
            locationid = q.itemID

        solar_min = 30000000
        solar_max = 39999999
        orbit_min = 40000000
        orbit_max = 49999999
        self._location_id = locationid
        if locationid >= solar_min and locationid <= solar_max:
            q = session.query(SolarSystems).filter_by(solarSystemID=locationid).first()
            self._region_id = q.regionID
            self._system_id = q.solarSystemID
            self._system_name = q.solarSystemName
            self.type = 'system'
        elif locationid >= orbit_min and locationid <= orbit_max:
            q = session.query(MapDenormalize).filter_by(itemID=locationid).first()
            self._region_id = q.regionID
            self._constellation_id = q.constellationID
            self._system_id = q.solarSystemID
            self._orbit_id = locationid
            self.type = 'orbit'
        else:
            self.type = '???'

    def _nameof(self, locationid):
        q = session.query(MapDenormalize).filter_by(itemID=locationid).first()
        return q.itemName

    @property
    def region(self):
        if not hasattr(self, "_region_name"):
            self._region_name = self._nameof(self._region_id)
        return self._region_name

    @property
    def system(self):
        if not hasattr(self, "_system_name"):
            self._system_name = self._nameof(self._system_id)
        return self._system_name

    @property
    def orbit(self):
        if not hasattr(self, "_orbit_name"):
            self._orbit_name = self._nameof(self._orbit_id)
        return self._orbit_name

    def __str__(self):
        if hasattr(self, "_orbit_id"):
            return self.orbit
        elif hasattr(self, "_system_id"):
            return self.system
        else:
            return "%s" % self._location_id

def market_groups_rec(session, parent):
    """Return the set of market groups that are descendents of the supplied parent group (including the parent)."""
    tocheck = [parent]
    result = []
    while tocheck:
        t = tocheck.pop()
        result.append(t)
        s = session.query(MarketGroups).filter_by(parentGroupID=t).all()
        tocheck += [x.marketGroupID for x in s if x.marketGroupID not in tocheck]
    return result

class SDE:
    @property
    def towers(self):
        if not hasattr(self, "_towers"):
            tower_group = session.query(Groups).filter_by(groupName='Control Tower').first().groupID
            self._towers = [x.typeID for x in session.query(Items).filter_by(groupID=tower_group).all()]
        return self._towers

    @property
    def fuels(self):
        if not hasattr(self, "_fuels"):
            fuel_group = session.query(Groups).filter_by(groupName='Fuel Block').first().groupID
            self._fuels = [x.typeID for x in session.query(Items).filter_by(groupID=fuel_group).all()]
        return self._fuels

    @property
    def moongoo_mods(self):
        if not hasattr(self, "_moongoo_mods"):
            self._moongoo_mods = self.reactor_mods + self.silo_mods + self.harvester_mods
        return self._moongoo_mods

    @property
    def reactor_mods(self):
        if not hasattr(self, "_reactor_mods"):
            reactor_mods_group = session.query(MarketGroups).filter_by(marketGroupName='Reactors').first().marketGroupID
            self._reactor_mods = [x.typeID for x in session.query(Items).filter_by(marketGroupID = reactor_mods_group).all()]
        return self._reactor_mods

    @property
    def silo_mods(self):
        if not hasattr(self, "_silo_mods"):
            silo_mods_group = session.query(MarketGroups).filter_by(marketGroupName='Silos').first().marketGroupID
            self._silo_mods = [x.typeID for x in session.query(Items).filter_by(marketGroupID = silo_mods_group).all()]
        return self._silo_mods

    @property
    def harvester_mods(self):
        if not hasattr(self, "_harvester_mods"):
            harvester_mods_group = session.query(MarketGroups).filter_by(marketGroupName='Moon Harvesting Arrays').first().marketGroupID
            self._harvester_mods = [x.typeID for x in session.query(Items).filter_by(marketGroupID = harvester_mods_group).all()]
        return self._harvester_mods

    def _fetch_tower_mods(self):
        tower_mods_group = session.query(MarketGroups).filter_by(marketGroupName='Starbase Structures').first().marketGroupID
        all_mod_groups = market_groups_rec(session, tower_mods_group)
        all_mods = [x.typeID for x in session.query(Items).filter(Items.marketGroupID.in_(all_mod_groups)).all()]
        return all_mods

    @property
    def tower_mods(self):
        if not hasattr(self, "_tower_mods"):
            self._tower_mods = self._fetch_tower_mods()
        return self._tower_mods

    def typeid(self, typename):
        return session.query(Items).filter_by(typeName=typename).first().typeID

    def typename(self, typeid):
        return session.query(Items).filter_by(typeID=typeid).first().typeName

    def capacity(self, typeid):
        return session.query(Items).filter_by(typeID=typeid).first().capacity

    def volume(self, typeid):
        return session.query(Items).filter_by(typeID=typeid).first().volume

    def location(self, locationid):
        return Location(locationid)

    def reaction(self, type_id):
        if not hasattr(self, "_reactions"):
            self._reactions = {}
        if not type_id in self._reactions:
            self._reactions[type_id] = Reaction(type_id)
        return self._reactions[type_id]

    def harvest(self, type_id):
        if not hasattr(self, "_harvests"):
            self._harvests = {}
        if not type_id in self._harvests:
            self._harvests[type_id] = Harvest(type_id)
        return self._harvests[type_id]

class Harvest:
    def __init__(self, type_id):
        self._type_id = type_id
        self._inputs = {}
        self._outputs = {}

        quantity_attribute = session.query(AttributeTypes).filter_by(attributeName = 'moonMiningAmount').first().attributeID
        quantity = session.query(TypeAttributes).filter_by(attributeID = quantity_attribute, typeID = self._type_id).first().valueInt
        if not quantity:
            quantity = 1
        self._outputs[self._type_id] = quantity

        self._name = 'Harvest %s' % sde.typename(self._type_id)

    def __str__(self):
        return '%s [=> %s]' % (self._name,
                               {str(sde.typename(x)): self._outputs[x] for x in self._outputs})

class Reaction:
    def __init__(self, type_id):
        self._type_id = type_id
        self._inputs = {}
        self._outputs = {}

        multiplier_attribute = session.query(AttributeTypes).filter_by(attributeName = 'moonMiningAmount').first().attributeID
        reactants = session.query(Reactions).filter_by(reactionTypeID = self._type_id).all()
        for item in reactants:
            multiplier = session.query(TypeAttributes).filter_by(attributeID = multiplier_attribute, typeID = item.typeID).first().valueInt
            if not multiplier:
                multiplier = 1
            quantity = item.quantity * multiplier
            if item.input == 1:
                self._inputs[item.typeID] = quantity
            else:
                self._outputs[item.typeID] = quantity

        self._name = sde.typename(self._type_id)

    def __str__(self):
        return '%s [%s => %s]' % (self._name,
                                      {str(sde.typename(x)): self._inputs[x] for x in self._inputs},
                                      {str(sde.typename(x)): self._outputs[x] for x in self._outputs})

class TowerMod:
    def __init__(self, row, parent):
        self._type_id = row['item_type_id']
        self._item_id = row['id']
        self._parent = parent
        self._capacity = sde.capacity(self._type_id)
        if self._type_id in sde.silo_mods:
            self._capacity *= self._parent._silo_multiplier
        self._filled = 0
        self._contents = []
        if 'contents' in row:
            for c in row['contents']:
                self._contents.append((c['item_type_id'], c['quantity']))
                self._filled += sde.volume(c['item_type_id']) * c['quantity']

    def __str__(self):
        contentstr = ''
        name = sde.typename(self._type_id)
        if self._contents:
            full = (self._filled / self._capacity) * 100.0
            if len(self._contents) == 1:
                qty = self._contents[0][1]
                typ = self._contents[0][0]
                contentstr = ' %0.2f%% full [%dx %s]' % (full, qty, sde.typename(typ))
            else:
                contentstr = ' %0.2f%% full [%d items]' % (full, len(self._contents))
        return name + contentstr

class Tower:
    def __init__(self, row, config):
        self._type_id = row['type_id']
        self._item_id = row['id']
        self.loc = Location(row['moon_id'])
        self._state = row['state']
        if self._state == 'onlining':
            self._online_at = row['online_ts']
        if self._state == 'reinforced':
            self._exits_at = row['state_ts']
        self._type_name = sde.typename(self._type_id)
        self._fuel = 0
        self._stront = 0
        self._fph = 10
        self._sph = 100
        self._xyz = (0, 0, 0)
        self._name = self._type_name
        self._mods = {}
        self._modtypes = {}
        self._moongoo_mods = []
        self._warnings = []
        self.warnings_config = config

        silo_bonus_attribute = session.query(AttributeTypes).filter_by(attributeName = 'controlTowerSiloCapacityBonus').first().attributeID
        silo_bonus_percent = session.query(TypeAttributes).filter_by(typeID = self._type_id,
                                                                     attributeID = silo_bonus_attribute).first()
        self._silo_multiplier = 1.0 + (silo_bonus_percent.valueFloat / 100.0) if silo_bonus_percent else 1.0

    def enrich(self, details, sov, my_alliance_id):
        if details is None:
            self._warnings.append('No info, something is up!!')
            return

        for f in details['fuel']:
            quantity = details['fuel'][f]
            if f in sde.fuels:
                self._fuel = quantity
            elif f == sde.typeid('Strontium Clathrates'):
                self._stront = quantity
        fuel = session.query(FuelConsumption).filter_by(controlTowerTypeID=self._type_id).all()
        self._fph = [x.quantity for x in fuel if x.purpose == 1 and not x.minSecurityLevel][0]
        self._sph = [x.quantity for x in fuel if x.purpose == 4][0]
        if sov:
            self._fph = math.ceil(self._fph * 0.75)
            self._sph = math.ceil(self._sph * 0.75)

        stront_hours = self._stront / self._sph
        if stront_hours <= self.warnings_config['stront_min'] or stront_hours >= self.warnings_config['stront_max']:
            self._warnings.append('Incorrect stront: stronted for {current}h but needs to be between {min}h and {max}h.'
                                  .format(current=stront_hours, min=self.warnings_config['stront_min'],
                                          max=self.warnings_config['stront_max']))
        fuel_hours = self._fuel / self._fph
        if fuel_hours <= self.warnings_config['critical_fuel']:
            self._warnings.append('CRITICAL: Pinging tower with %d hours of fuel left.' % fuel_hours)
        elif fuel_hours <= self.warnings_config['low_fuel']:
            self._warnings.append('Low fuel: tower with %d hours of fuel left.' % fuel_hours)

        ff = details['permissions']['forcefield']
        if ff['corp'] != self.warnings_config['corp_access']:
            self._warnings.append('Access: corp access {}set.'.format("not " if not ff['corp'] else ""))
        if ff['alliance'] != self.warnings_config['alliance_access']:
            self._warnings.append('Access: alliance access {}set.'.format("not " if not ff['alliance'] else ""))

        combat = details['combat']
        if combat['hostility']['standing']['enabled'] != self.warnings_config['use_standings']:
            self._warnings.append('Standings: POS misconfigured to {}use standings for combat.'
                                  .format("not " if self.warnings_config['use_standings'] else ""))
        if self.warnings_config['use_standings']:
            if (my_alliance_id == combat['standings_owner_id']) != self.warnings_config['alliance_standings']:
                self._warnings.append('Standings: use alliance standings {}set.'\
                    .format("not " if self.warnings_config['alliance_standings'] else ""))
            if not (self.warnings_config['min_standing'] <=
                    combat['hostility']['standing']['threshold'] <=
                    self.warnings_config['max_standing']):
                self._warnings.append('Standings: POS misconfigured to shoot people with less than {0} standings, '
                                      ' should be between {1} and {2}.'
                                      .format(combat['hostility']['standing']['threshold'],
                                              self.warnings_config['min_standing'],
                                              self.warnings_config['max_standing']))
        if combat['hostility']['sec_status']['enabled'] != self.warnings_config['sec_status']:
            self._warnings.append('Standings: POS misconfigured to {}shoot on sec status.'
                                  .format("not " if self.warnings_config['sec_status'] else ""))
        if self.warnings_config['sec_status']:
            if not (self.warnings_config['min_security'] <=
                    combat['hostility']['sec_status']['threshold'] <=
                    self.warnings_config['max_security']):
                self._warnings.append('Standings: POS misconfigured to shoot people with less than {0} security, '
                                      'should be between {1} and {2}.'
                                      .format(combat['hostility']['sec_status']['threshold'],
                                              self.warnings_config['min_security'],
                                              self.warnings_config['max_security']))


    def set_name(self, name):
        self._name = name

    def set_xyz(self, xyz):
        self._xyz = xyz

    def dist_from(self, xyz1):
        xyz0 = self._xyz
        return math.sqrt((xyz0[0] - xyz1[0]) ** 2
                       + (xyz0[1] - xyz1[1]) ** 2
                       + (xyz0[2] - xyz1[2]) ** 2)

    def add_mod(self, mod):
        type = mod['item_type_id']
        if type in sde.towers:
            return
        self._mods[mod['id']] = TowerMod(mod, self)
        mname = sde.typename(type)
        if mname not in self._modtypes:
            self._modtypes[mname] = 0
        self._modtypes[mname] += 1
        if type in sde.moongoo_mods:
            self._moongoo_mods.append(mod['id'])

    def mod_str(self):
        modtypes = sorted(self._modtypes.keys())
        return '\n'.join(map(lambda x: '  %s' % self._mods[x], self._mods))

    def tower_str(self):
        return '%s: %s %s "%s" [%dF (%0.2fd) %dS (%dh)]' % (self.loc, self._state, self._type_name, self._name,
                                                 self._fuel, self._fuel / (self._fph * 24.),
                                                 self._stront, self._stront / self._sph)

    def __str__(self):
        return '%s\n%s\n' % (self.tower_str(), self.mod_str())

    def eval_moongoo(self):
        reactions = [sde.reaction(self._mods[x]._contents[0][0]) for x in self._mods
                     if self._mods[x]._type_id in sde.reactor_mods and len(self._mods[x]._contents) == 1]

        found_reactor = False
        found_mha = False
        harvest_type = None
        for mod_id in self._moongoo_mods:
            container = self._mods[mod_id]
            if container._type_id in sde.reactor_mods:
                found_reactor = True
                break
            if container._type_id in sde.harvester_mods:
                found_mha = True
            if container._type_id in sde.silo_mods:
                if len(container._contents) == 1:
                    if harvest_type:
                        # ambiguous harvest type
                        harvest_type = None
                        break
                    harvest_type = container._contents[0][0]

        if harvest_type and not found_reactor and found_mha:
            reactions += [Harvest(harvest_type)]

        annotated_modules = []
        for mod_id in self._moongoo_mods:
            container = self._mods[mod_id]
            annotated_modules.append(self.eval_container(container, reactions))

        return '%s\nReactions: %s\n%s\n' % (
            self.tower_str(),
            ','.join([str(x) for x in reactions]),
            '\n'.join(annotated_modules))

    def eval_container(self, container, reactions):
        if len(container._contents) != 1:
            if container._type_id in sde.silo_mods:
                self._warnings.append('Empty silo.')
            return '  %s (unrecognized)' % str(container)

        content_type = container._contents[0][0]
        quantity = container._contents[0][1]
        headroom = (container._capacity - container._filled) / sde.volume(content_type)

        for reaction in reactions:
            if content_type in reaction._inputs:
                remaining = quantity / reaction._inputs[content_type]
                if remaining < self.warnings_config['low_input']:
                    self._warnings.append('Low input: %d hours of reactant %s left.' % (remaining, sde.typename(content_type)))
                return '  %s (input, remaining %0.2fd)' % (str(container), remaining / 24.)
            if content_type in reaction._outputs:
                remaining = headroom / reaction._outputs[content_type]
                if remaining < self.warnings_config['low_output']:
                      self._warnings.append('Too much output: %d hours of room for product %s left.' % (remaining, sde.typename(content_type)))
                return  '  %s (output, remaining %0.2fd)' % (str(container), remaining / 24.)
        return '  %s (unrecognized)' % str(container)

def minimize(set, f):
    mv = None
    mk = None
    for k in set:
        v = f(k)
        if mv == None or v < mv:
            mv = v
            mk = k
    return mk

class TowerSet:
    def __init__(self, sde, config):
        self._sde = sde
        self.config = config
        self._towers = {}

    def add_all(self, r):
        for item_id in r:
            self._towers[item_id] = Tower(r[item_id], self.config[r[item_id]['moon_id']] if r[item_id]['moon_id'] in self.config else self.config['default'])

    def enrich(self, detail_callback, sov_callback, my_alliance_id):
        for t in self._towers:
            sys_id = self._towers[t].loc._system_id if hasattr(self._towers[t].loc, '_system_id') else None
            self._towers[t].enrich(detail_callback(t), sov_callback(sys_id), my_alliance_id)

    def eval_moongoo(self):
        return '\n'.join([self._towers[t].eval_moongoo() for t in sorted(self._towers, key=lambda x: str(self._towers[x].loc))])

    def find_warnings(self):
        return {t: self._towers[t]._warnings for t in self._towers if len(self._towers[t]._warnings) > 0}

    def _find_closest_to(self, sys, xyz):
        towers = [x for x in self._towers.values() if x.loc._system_id == sys]
        return minimize(towers, lambda x: x.dist_from(xyz))

    def add_mods(self, locations, assets):
        # Extract tower positions, assign them to the towers
        for l in locations:
            if l in self._towers:
                loc = locations[l]
                xyz = (loc['x'], loc['y'], loc['z'])
                self._towers[l].set_name(loc['name'])
                self._towers[l].set_xyz(xyz)

        # Unanchored towers...
        for tk in self._towers.keys():
            tv = self._towers[tk]
            if tv.loc.type == '???':
                if tk in assets:
                    print 'Fixing up bogus location for %s' % tv
                    tv.loc = Location(assets[tk]['location_id'])
                else:
                    print "GIVING UP on %s" % tv
                    del self._towers[tk]

        # Now bind modules to towers
        for l in locations:
            a = assets[l]
            loc = self._sde.location(a['location_id'])
            if not hasattr(loc, '_system_id'):
                continue
            sys = loc._system_id
            item = locations[l]
            xyz = (item['x'], item['y'], item['z'])
            tower = self._find_closest_to(sys, xyz)
            tower.add_mod(a)

    def __str__(self):
        return '\n'.join([str(x) for x in self._towers.values()])

class SDETest(unittest.TestCase):
    def test_towers(self):
        self.assertTrue(20060 in sde.towers) # Amarr Small
        self.assertFalse(2048 in sde.towers) # DC2

if __name__ == '__main__':
    unittest.main()

# Use in client programs:
# import sde
# sde.initialize(db_path)
# Then either import sde and access sde.towers, or import session/Groups/Items and access them using sqlalchemy filter stuff.