db_update.py

#!/usr/bin/env python3
#======================================================================
# Copyright (C) 2012 Diego Duclos
#
# This file is part of eos.
#
# eos is free software: you can redistribute it and/or modify
# it under the terms of the GNU Lesser General Public License as
# published by the Free Software Foundation, either version 3 of
# the License, or (at your option) any later version.
#
# eos is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public
# License along with eos.  If not, see <http://www.gnu.org/licenses/>.
#======================================================================


import datetime
import functools
import itertools
import json
import os
import re
import sqlite3
import sys

import sqlalchemy.orm
from sqlalchemy import or_, and_


# todo: need to set the EOS language to en, becasuse this assumes it's being run within an English context
# Need to know what that would do if called from pyfa
ROOT_DIR = os.path.realpath(os.path.dirname(__file__))
DB_PATH = os.path.join(ROOT_DIR, 'eve.db')
JSON_DIR = os.path.join(ROOT_DIR, 'staticdata')
if ROOT_DIR not in sys.path:
    sys.path.insert(0, ROOT_DIR)
GAMEDATA_SCHEMA_VERSION = 4


def db_needs_update():
    """True if needs, false if it does not, none if we cannot check it."""
    try:
        with open(os.path.join(JSON_DIR, 'phobos', 'metadata.0.json')) as f:
            data_version = next((r['field_value'] for r in json.load(f) if r['field_name'] == 'client_build'))
    except (KeyboardInterrupt, SystemExit):
        raise
    # If we have no source data - return None; should not update in this case
    except:
        return None
    if not os.path.isfile(DB_PATH):
        print('Gamedata DB not found')
        return True
    db_data_version = None
    db_schema_version = None
    try:
        db = sqlite3.connect(DB_PATH)
        cursor = db.cursor()
        cursor.execute('SELECT field_value FROM metadata WHERE field_name = \'client_build\'')
        for row in cursor:
            db_data_version = int(row[0])
        cursor.execute('SELECT field_value FROM metadata WHERE field_name = \'schema_version\'')
        for row in cursor:
            db_schema_version = int(row[0])
        cursor.close()
        db.close()
    except (KeyboardInterrupt, SystemExit):
        raise
    except:
        print('Error when fetching gamedata DB metadata')
        return True
    if data_version != db_data_version:
        print('Gamedata DB data version mismatch: needed {}, DB has {}'.format(data_version, db_data_version))
        return True
    if GAMEDATA_SCHEMA_VERSION != db_schema_version:
        print('Gamedata DB schema version mismatch: needed {}, DB has {}'.format(GAMEDATA_SCHEMA_VERSION, db_schema_version))
        return True
    return False


def update_db():

    print('Building gamedata DB...')

    if os.path.isfile(DB_PATH):
        os.remove(DB_PATH)

    import eos.db
    import eos.gamedata
    import eos.config

    # Create the database tables
    eos.db.gamedata_meta.create_all(eos.db.gamedata_engine)

    def _readData(minerName, jsonName, keyIdName=None):
        compiled_data = None
        for i in itertools.count(0):
            try:
                with open(os.path.join(JSON_DIR, minerName, '{}.{}.json'.format(jsonName, i)), encoding='utf-8') as f:
                    rawData = json.load(f)
                    if i == 0:
                        compiled_data = {} if type(rawData) == dict else []
                    if type(rawData) == dict:
                        compiled_data.update(rawData)
                    else:
                        compiled_data.extend(rawData)
            except FileNotFoundError:
                break

        if not keyIdName:
            return compiled_data
        # IDs in keys, rows in values
        data = []
        for k, v in compiled_data.items():
            row = {}
            row.update(v)
            row[keyIdName] = int(k)
            data.append(row)
        return data

    def _translate(translations, lang):
        if not translations:
            return None
        text = translations.get('en-us' if lang == 'en' else lang)
        if text is None:
            text = translations.get('en-us')
        return text

    def _addRows(data, cls, fieldMap=None, translatedMap=None):
        """
        Translated fields come as {language code: text} maps; translatedMap tells which column
        prefix each of them goes to (the language suffix is appended), or None to skip it.
        """
        if fieldMap is None:
            fieldMap = {}
        if translatedMap is None:
            translatedMap = {}
        for row in data:
            instance = cls()
            for k, v in row.items():
                if k in translatedMap:
                    prefix = translatedMap[k]
                    if prefix is None:
                        continue
                    for lang, suffix in eos.config.translation_mapping.items():
                        text = _translate(v, lang)
                        if isinstance(text, str):
                            text = text.strip()
                        setattr(instance, prefix + suffix, text)
                    continue
                if isinstance(v, str):
                    v = v.strip()
                setattr(instance, fieldMap.get(k, k), v)
            eos.db.gamedata_session.add(instance)

    def processEveTypes():
        print('processing evetypes')
        data = _readData('fsd_built', 'types', keyIdName='typeID')
        for row in data:
            name = _translate(row['typeName'], 'en') or ''
            if (
                # Apparently people really want Civilian modules available
                (name.startswith('Civilian') and "Shuttle" not in name)
                or name == 'Capsule'
                or row['groupID'] == 4033  # destructible effect beacons
                or row['typeID'] == 82941  # Metenox service
                or row['typeID'] in (87164, 87177)  # Trig buff carriers
                or re.match(r'AIR .+Booster.*', name)
            ):
                row['published'] = True
            # Nearly useless and clutter search results too much
            elif (
                name.startswith('Limited Synth ')
                or name.startswith('Expired ')
                or re.match(r'Grand Prix .*Booster', name)
                or re.match(r'Mining Blitz .+ Booster Dose .+', name)
                or name.endswith(' Filament') and (
                    "'Needlejack'" not in name and
                    "'Devana'" not in name and
                    "'Pochven'" not in name and
                    "'Extraction'" not in name and
                    "'Krai Veles'" not in name and
                    "'Krai Perun'" not in name and
                    "'Krai Svarog'" not in name
                )
            ):
                row['published'] = False

        newData = []
        for row in data:
            if (
                row['published'] or
                # group Ship Modifiers, for items like tactical t3 ship modes
                row['groupID'] == 1306 or
                # Micro Bombs (Fighters)
                row['typeID'] in (41549, 41548, 41551, 41550) or
                # Abyssal weather (environment)
                row['groupID'] in (1882, 1975, 1971, 1983) or
                # FW-specific proximity beacons
                row['typeID'] in (92609, 95625)
            ):
                newData.append(row)
        _addRows(newData, eos.gamedata.Item, translatedMap={'typeName': 'typeName', 'description': '_description'})
        return newData

    def processEveGroups():
        print('processing evegroups')
        data = _readData('fsd_built', 'groups', keyIdName='groupID')
        _addRows(data, eos.gamedata.Group, translatedMap={'groupName': 'name'})
        return data

    def processEveCategories():
        print('processing evecategories')
        data = _readData('fsd_built', 'categories', keyIdName='categoryID')
        _addRows(data, eos.gamedata.Category, translatedMap={'categoryName': 'name'})

    def processDogmaAttributes():
        print('processing dogmaattributes')
        data = _readData('fsd_built', 'dogmaattributes', keyIdName='attributeID')
        _addRows(data, eos.gamedata.AttributeInfo, translatedMap={
            'displayName': 'displayName',
            'tooltipDescription': None})

    def processDogmaTypeAttributes(eveTypesData):
        print('processing dogmatypeattributes')
        data = _readData('fsd_built', 'typedogma', keyIdName='typeID')
        eveTypeIds = set(r['typeID'] for r in eveTypesData)
        newData = []
        seenKeys = set()

        def checkKey(key):
            if key in seenKeys:
                return False
            seenKeys.add(key)
            return True

        for typeData in data:
            if typeData['typeID'] not in eveTypeIds:
                continue
            for row in typeData.get('dogmaAttributes', ()):
                row['typeID'] = typeData['typeID']
                if checkKey((row['typeID'], row['attributeID'])):
                    newData.append(row)
        for row in eveTypesData:
            for attrId, attrName in {4: 'mass', 38: 'capacity', 161: 'volume', 162: 'radius'}.items():
                if attrName in row and checkKey((row['typeID'], attrId)):
                    newData.append({'typeID': row['typeID'], 'attributeID': attrId, 'value': row[attrName]})

        _addRows(newData, eos.gamedata.Attribute)
        return newData

    def processDynamicItemAttributes():
        print('processing dynamicitemattributes')
        data = _readData('fsd_built', 'dynamicitemattributes')
        for mutaID, mutaData in data.items():
            muta = eos.gamedata.DynamicItem()
            muta.typeID = mutaID
            muta.resultingTypeID = mutaData['inputOutputMapping'][0]['resultingType']
            eos.db.gamedata_session.add(muta)

            for x in mutaData['inputOutputMapping'][0]['applicableTypes']:
                item = eos.gamedata.DynamicItemItem()
                item.typeID = mutaID
                item.applicableTypeID = x
                eos.db.gamedata_session.add(item)

            for attrID, attrData in mutaData['attributeIDs'].items():
                attr = eos.gamedata.DynamicItemAttribute()
                attr.typeID = mutaID
                attr.attributeID = attrID
                attr.min = attrData['min']
                attr.max = attrData['max']
                eos.db.gamedata_session.add(attr)

    def processDogmaEffects():
        print('processing dogmaeffects')
        data = _readData('fsd_built', 'dogmaeffects', keyIdName='effectID')
        _addRows(
            data, eos.gamedata.Effect,
            fieldMap={'resistanceAttributeID': 'resistanceID'},
            translatedMap={'displayName': None, 'description': None})

    def processDogmaTypeEffects(eveTypesData):
        print('processing dogmatypeeffects')
        data = _readData('fsd_built', 'typedogma', keyIdName='typeID')
        eveTypeIds = set(r['typeID'] for r in eveTypesData)
        newData = []
        for typeData in data:
            if typeData['typeID'] not in eveTypeIds:
                continue
            for row in typeData.get('dogmaEffects', ()):
                row['typeID'] = typeData['typeID']
                newData.append(row)
        _addRows(newData, eos.gamedata.ItemEffect)
        return newData

    def processDogmaUnits():
        print('processing dogmaunits')
        data = _readData('fsd_built', 'dogmaunits', keyIdName='unitID')
        _addRows(
            data, eos.gamedata.Unit,
            fieldMap={'name': 'unitName'},
            translatedMap={'displayName': 'displayName', 'description': None})

    def processMarketGroups():
        print('processing marketgroups')
        data = _readData('fsd_built', 'marketgroups', keyIdName='marketGroupID')
        _addRows(data, eos.gamedata.MarketGroup, translatedMap={
            'name': 'marketGroupName',
            'description': '_description'})

    def processMetaGroups():
        print('processing metagroups')
        data = _readData('fsd_built', 'metagroups', keyIdName='metaGroupID')
        _addRows(data, eos.gamedata.MetaGroup, translatedMap={
            'name': 'metaGroupName',
            'description': None})

    def processCloneGrades():
        print('processing clonegrades')
        data = _readData('fsd_lite', 'clonegrades')

        newData = []
        # December, 2017 - CCP decided to use only one set of skill levels for alpha clones. However, this is still
        # represented in the data as a skillset per race. To ensure that all skills are the same, we store them in a way
        # that we can check to make sure all races have the same skills, as well as skill levels
        check = {}
        for ID in data:
            for skill in data[ID]['skills']:
                newData.append({
                    'alphaCloneID': int(ID),
                    'alphaCloneName': 'Alpha Clone',
                    'typeID': skill['typeID'],
                    'level': skill['level']})
                if ID not in check:
                    check[ID] = {}
                check[ID][int(skill['typeID'])] = int(skill['level'])
        if not functools.reduce(lambda a, b: a if a == b else False, [v for _, v in check.items()]):
            raise Exception('Alpha Clones not all equal')
        newData = [x for x in newData if x['alphaCloneID'] == 1]
        if len(newData) == 0:
            raise Exception('Alpha Clone processing failed')

        tmp = []
        for row in newData:
            if row['alphaCloneID'] not in tmp:
                cloneParent = eos.gamedata.AlphaClone()
                setattr(cloneParent, 'alphaCloneID', row['alphaCloneID'])
                setattr(cloneParent, 'alphaCloneName', row['alphaCloneName'])
                eos.db.gamedata_session.add(cloneParent)
                tmp.append(row['alphaCloneID'])
        _addRows(newData, eos.gamedata.AlphaCloneSkill)

    def processTraits():
        print('processing traits')
        data = _readData('phobos', 'traits')

        def convertSection(sectionData):
            sectionLines = []
            headerText = '<b>{}</b>'.format(sectionData['header'])
            sectionLines.append(headerText)
            for bonusData in sectionData['bonuses']:
                prefix = '{} '.format(bonusData['number']) if 'number' in bonusData else ''
                bonusText = '{}{}'.format(prefix, bonusData['text'].replace('\u00B7', '\u2022 '))
                sectionLines.append(bonusText)
            sectionLine = '<br />\n'.join(sectionLines)
            return sectionLine

        newData = []
        for row in data:
            try:
                newRow = {
                    'typeID': row['typeID'],
                }
                for (k, v) in eos.config.translation_mapping.items():
                    typeLines = []
                    traitData = _translate(row['traits'], k)
                    for skillData in sorted(traitData.get('skills', ()), key=lambda i: i['header']):
                        typeLines.append(convertSection(skillData))
                    if 'role' in traitData:
                        typeLines.append(convertSection(traitData['role']))
                    if 'misc' in traitData:
                        typeLines.append(convertSection(traitData['misc']))
                    traitLine = '<br />\n<br />\n'.join(typeLines)
                    newRow['traitText{}'.format(v)] = traitLine

                newData.append(newRow)
            except:
                pass
        _addRows(newData, eos.gamedata.Traits)

    def processMetadata():
        print('processing metadata')
        data = _readData('phobos', 'metadata')
        _addRows(data, eos.gamedata.MetaData)

    def processReqSkills(eveTypesData):
        print('processing requiredskillsfortypes')

        def composeReqSkills(raw):
            reqSkills = {}
            for skillTypeID, skillLevel in raw.items():
                reqSkills[int(skillTypeID)] = skillLevel
            return reqSkills

        eveTypeIds = set(r['typeID'] for r in eveTypesData)
        data = _readData('fsd_built', 'requiredskillsfortypes')
        reqsByItem = {}
        itemsByReq = {}
        for typeID, skillreqData in data.items():
            typeID = int(typeID)
            if typeID not in eveTypeIds:
                continue
            for skillTypeID, skillLevel in composeReqSkills(skillreqData).items():
                reqsByItem.setdefault(typeID, {})[skillTypeID] = skillLevel
                itemsByReq.setdefault(skillTypeID, {})[typeID] = skillLevel
        for item in eos.db.gamedata_session.query(eos.gamedata.Item).all():
            if item.typeID in reqsByItem:
                item.reqskills = json.dumps(reqsByItem[item.typeID])
            if item.typeID in itemsByReq:
                item.requiredfor = json.dumps(itemsByReq[item.typeID])

    def processReplacements(eveTypesData, eveGroupsData, dogmaTypeAttributesData, dogmaTypeEffectsData):
        print('finding item replacements')

        def compareAttrs(attrs1, attrs2):
            # Consider items as different if they have no attrs
            if len(attrs1) == 0 and len(attrs2) == 0:
                return False
            if set(attrs1) != set(attrs2):
                return False
            if all(attrs1[aid] == attrs2[aid] for aid in attrs1):
                return True
            return False

        skillReqAttribs = {
            182: 277,
            183: 278,
            184: 279,
            1285: 1286,
            1289: 1287,
            1290: 1288}
        skillReqAttribsFlat = set(skillReqAttribs.keys()).union(skillReqAttribs.values())
        # Get data on type groups
        # Format: {type ID: group ID}
        typesGroups = {}
        for row in eveTypesData:
            typesGroups[row['typeID']] = row['groupID']
        # Get data on item effects
        # Format: {type ID: set(effect, IDs)}
        typesEffects = {}
        for row in dogmaTypeEffectsData:
            typesEffects.setdefault(row['typeID'], set()).add(row['effectID'])
        # Get data on type attributes
        # Format: {type ID: {attribute ID: attribute value}}
        typesNormalAttribs = {}
        typesSkillAttribs = {}
        for row in dogmaTypeAttributesData:
            attributeID = row['attributeID']
            if attributeID in skillReqAttribsFlat:
                typeSkillAttribs = typesSkillAttribs.setdefault(row['typeID'], {})
                typeSkillAttribs[row['attributeID']] = row['value']
            # Ignore these attributes for comparison purposes
            elif attributeID in (
                # We do not need mass as it affects final ship stats only when carried by ship itself
                # (and we're not going to replace ships), but it's wildly inconsistent for other items,
                # which otherwise would be the same
                4,  # mass
                124,  # mainColor
                162,  # radius
                422,  # techLevel
                633,  # metaLevel
                1692,  # metaGroupID
                1768  # typeColorScheme
            ):
                continue
            else:
                typeNormalAttribs = typesNormalAttribs.setdefault(row['typeID'], {})
                typeNormalAttribs[row['attributeID']] = row['value']
        # Get data on skill requirements
        # Format: {type ID: {skill type ID: skill level}}
        typesSkillReqs = {}
        for typeID, typeAttribs in typesSkillAttribs.items():
            typeSkillAttribs = typesSkillAttribs.get(typeID, {})
            if not typeSkillAttribs:
                continue
            typeSkillReqs = typesSkillReqs.setdefault(typeID, {})
            for skillreqTypeAttr, skillreqLevelAttr in skillReqAttribs.items():
                try:
                    skillType = int(typeSkillAttribs[skillreqTypeAttr])
                    skillLevel = int(typeSkillAttribs[skillreqLevelAttr])
                except (KeyError, ValueError):
                    continue
                typeSkillReqs[skillType] = skillLevel
        # Format: {group ID: category ID}
        groupCategories = {}
        for row in eveGroupsData:
            groupCategories[row['groupID']] = row['categoryID']
        # As EVE affects various types mostly depending on their group or skill requirements,
        # we're going to group various types up this way
        # Format: {(group ID, frozenset(skillreq, type, IDs), frozenset(type, effect, IDs): [type ID, {attribute ID: attribute value}]}
        groupedData = {}
        for row in eveTypesData:
            typeID = row['typeID']
            # Ignore items outside of categories we need
            if groupCategories[typesGroups[typeID]] not in (
                6,  # Ship
                7,  # Module
                8,  # Charge
                18,  # Drone
                20,  # Implant
                22,  # Deployable
                23,  # Starbase
                32,  # Subsystem
                35,  # Decryptors
                65,  # Structure
                66,  # Structure Module
                87,  # Fighter
            ):
                continue
            typeAttribs = typesNormalAttribs.get(typeID, {})
            # Ignore items w/o attributes
            if not typeAttribs:
                continue
            # We need only skill types, not levels for keys
            typeSkillreqs = frozenset(typesSkillReqs.get(typeID, {}))
            typeGroup = typesGroups[typeID]
            typeEffects = frozenset(typesEffects.get(typeID, ()))
            groupData = groupedData.setdefault((typeGroup, typeSkillreqs, typeEffects), [])
            groupData.append((typeID, typeAttribs))
        # Format: {type ID: set(type IDs)}
        replacements = {}
        # Now, go through composed groups and for every item within it
        # find items which are the same
        for groupData in groupedData.values():
            for type1, type2 in itertools.combinations(groupData, 2):
                if compareAttrs(type1[1], type2[1]):
                    replacements.setdefault(type1[0], set()).add(type2[0])
                    replacements.setdefault(type2[0], set()).add(type1[0])
        # Update DB session with data we generated
        for item in eos.db.gamedata_session.query(eos.gamedata.Item).all():
            itemReplacements = replacements.get(item.typeID)
            if itemReplacements is not None:
                item.replacements = ','.join('{}'.format(tid) for tid in sorted(itemReplacements))

    def processImplantSets(eveTypesData):
        print('composing implant sets')
        # Includes only implants which can be considered part of sets, not all implants
        implant_groups = (300, 1730)
        specials = {'Genolution': ('Genolution Core Augmentation', r'CA-\d+')}
        implantSets = {}
        for row in eveTypesData:
            if not row.get('published'):
                continue
            if row.get('groupID') not in implant_groups:
                continue
            typeName = _translate(row['typeName'], 'en') or ''
            # Regular sets matching
            m = re.match(r'(?P<grade>(High|Mid|Low)-grade) (?P<set>\w+) (?P<implant>(Alpha|Beta|Gamma|Delta|Epsilon|Omega))', typeName, re.IGNORECASE)
            if m:
                implantSets.setdefault((m.group('grade'), m.group('set')), set()).add(row['typeID'])
            # Special set matching
            for setHandle, (setName, implantPattern) in specials.items():
                pattern = '(?P<set>{}) (?P<implant>{})'.format(setName, implantPattern)
                m = re.match(pattern, typeName)
                if m:
                    implantSets.setdefault((None, setHandle), set()).add(row['typeID'])
                    break
        data = []
        for (gradeName, setName), implants in implantSets.items():
            if len(implants) < 2:
                continue
            implants = ','.join('{}'.format(tid) for tid in sorted(implants))
            row = {'setName': setName, 'gradeName': gradeName, 'implants': implants}
            data.append(row)
        _addRows(data, eos.gamedata.ImplantSet)

    eveTypesData = processEveTypes()
    eveGroupsData = processEveGroups()
    processEveCategories()
    processDogmaAttributes()
    dogmaTypeAttributesData = processDogmaTypeAttributes(eveTypesData)
    processDynamicItemAttributes()
    processDogmaEffects()
    dogmaTypeEffectsData = processDogmaTypeEffects(eveTypesData)
    processDogmaUnits()
    processMarketGroups()
    processMetaGroups()
    processCloneGrades()
    processTraits()
    processMetadata()

    eos.db.gamedata_session.flush()
    processReqSkills(eveTypesData)
    processReplacements(eveTypesData, eveGroupsData, dogmaTypeAttributesData, dogmaTypeEffectsData)
    processImplantSets(eveTypesData)

    # Add schema version to prevent further updates
    metadata_schema_version = eos.gamedata.MetaData()
    metadata_schema_version.field_name = 'schema_version'
    metadata_schema_version.field_value = GAMEDATA_SCHEMA_VERSION
    eos.db.gamedata_session.add(metadata_schema_version)

    eos.db.gamedata_session.flush()

    # CCP still has 5 subsystems assigned to T3Cs, even though only 4 are available / usable. They probably have some
    # old legacy requirement or assumption that makes it difficult for them to change this value in the data. But for
    # pyfa, we can do it here as a post-processing step
    for attr in eos.db.gamedata_session.query(eos.gamedata.Attribute).filter(eos.gamedata.Attribute.ID == 1367).all():
        attr.value = 4.0
    for item in eos.db.gamedata_session.query(eos.gamedata.Item).filter(or_(
        eos.gamedata.Item.name.like('%abyssal%'),
        eos.gamedata.Item.name.like('%mutated%'),
        eos.gamedata.Item.name.like('%_PLACEHOLDER%'),
        # Drifter weapons are published for some reason
        eos.gamedata.Item.name.in_(('Lux Kontos', 'Lux Xiphos', 'Lux Ballistra', 'Lux Kopis'))
    )).all():
        if item.name in ('Abyssal Ore Processing', 'Mutated Drone Specialization'):
            continue
        if 'Asteroid Mining Crystal' in item.name:
            continue
        item.published = False

    # Remove boosters which were expired at the time data dump was generated
    attr = eos.db.gamedata_session.query(eos.gamedata.AttributeInfo).filter(eos.gamedata.AttributeInfo.name == 'boosterLastInjectionDatetime').first()
    dumpTime = eos.db.gamedata_session.query(eos.gamedata.MetaData).filter(eos.gamedata.MetaData.field_name == 'dump_time').first()
    if attr is not None and dumpTime is not None and dumpTime.field_value.isdigit():
        epochDelta = (datetime.datetime.fromtimestamp(timestamp=int(dumpTime.field_value), tz=datetime.timezone.utc)
                      - datetime.datetime(year=1970, month=1, day=1, tzinfo=datetime.timezone.utc))
        dumpDatetime = epochDelta.days + epochDelta.seconds / (24 * 60 * 60)
        query = (
            eos.db.gamedata_session.query(eos.gamedata.Item)
            .join(eos.gamedata.Item.group)
            .join(eos.gamedata.Group.category)
            .join(eos.gamedata.Item._Item__attributes)
            .filter(and_(
                eos.gamedata.Item.published,
                eos.gamedata.Category.name == 'Implant',
                eos.gamedata.Attribute.ID == attr.ID,
                eos.gamedata.Attribute.value < dumpDatetime)))
        if query.count() > 0:
            print(f'Removing {query.count()} expired boosters')
            for eveType in query:
                eos.db.gamedata_session.delete(eveType)

    for x in [
        30  # Apparel
    ]:
        cat = eos.db.gamedata_session.query(eos.gamedata.Category).filter(eos.gamedata.Category.ID == x).first()
        print ('Removing Category: {}'.format(cat.name))
        eos.db.gamedata_session.delete(cat)

    # Unused normally, can be useful for customizing items
    def _copyItem(srcName, tgtTypeID, tgtName, tgtGroupName = None):
        eveType = eos.db.gamedata_session.query(eos.gamedata.Item).filter(eos.gamedata.Item.name == srcName).one()
        eos.db.gamedata_session.expunge(eveType)
        sqlalchemy.orm.make_transient(eveType)
        eveType.ID = tgtTypeID
        if tgtGroupName is not None:
            eveGroup = eos.db.gamedata_session.query(eos.gamedata.Group).filter(eos.gamedata.Group.name == tgtGroupName).one()
            eveType.group = eveGroup
        for suffix in eos.config.translation_mapping.values():
            setattr(eveType, f'typeName{suffix}', tgtName)
        eos.db.gamedata_session.add(eveType)
        eos.db.gamedata_session.flush()

    def _hardcodeAttribs(typeID, attrMap):
        for attrName, value in attrMap.items():
            try:
                attr = eos.db.gamedata_session.query(eos.gamedata.Attribute).filter(and_(
                    eos.gamedata.Attribute.name == attrName, eos.gamedata.Attribute.typeID == typeID)).one()
            except sqlalchemy.orm.exc.NoResultFound:
                attrInfo = eos.db.gamedata_session.query(eos.gamedata.AttributeInfo).filter(eos.gamedata.AttributeInfo.name == attrName).one()
                attr = eos.gamedata.Attribute()
                attr.attributeID = attrInfo.ID
                attr.typeID = typeID
                attr.value = value
                eos.db.gamedata_session.add(attr)
            else:
                attr.value = value

    def processCloneGradeRestrictions():
        print('processing clone grade restrictions')
        alpha_caps = {}
        for clone in eos.db.gamedata_session.query(eos.gamedata.AlphaClone).all():
            for skill in clone.skills:
                alpha_caps[skill.typeID] = skill.level
        for item in eos.db.gamedata_session.query(eos.gamedata.Item).all():
            if not item.reqskills:
                continue
            try:
                reqskills = json.loads(item.reqskills)
            except (ValueError, TypeError):
                continue
            for skill_type_id, required_level in reqskills.items():
                max_alpha_level = alpha_caps.get(int(skill_type_id))
                if max_alpha_level is None or required_level > max_alpha_level:
                    _hardcodeAttribs(item.ID, {"cloneGradeRestriction": 1})
                    break

    processCloneGradeRestrictions()

    def _hardcodeEffects(typeID, effectMap, clearEffects=True):
        item = eos.db.gamedata_session.query(eos.gamedata.Item).filter(eos.gamedata.Item.ID == typeID).one()
        if clearEffects:
            item.effects.clear()
        for effectID, effectName in effectMap.items():
            try:
                effect = eos.db.gamedata_session.query(eos.gamedata.Effect).filter(eos.gamedata.Effect.ID == effectID).one()
            except sqlalchemy.orm.exc.NoResultFound:
                effect = eos.gamedata.Effect()
                effect.effectID = effectID
                effect.effectName = effectName
            item.effects[effectName] = effect

    def hardcodeSuppressionTackleRange():
        beaconTypeID = 79839
        attrMap = {
            'warfareBuff1ID': 2405,
            'warfareBuff1Value': 10}
        effectMap = {100000: 'pyfaCustomSuppressionTackleRange'}
        _hardcodeAttribs(beaconTypeID, attrMap)
        _hardcodeEffects(beaconTypeID, effectMap)
        eos.db.gamedata_session.flush()

    def hardcodeSovUpgradeBuffs():
        typeBuffMap = {
            # Gamma
            87815: {
                'warfareBuff1ID': 2433,
                'warfareBuff1Value': 5,
                'warfareBuff2ID': 2434,
                'warfareBuff2Value': 10,
                'warfareBuff3ID': 2441,
                'warfareBuff3Value': 5},
            # Plasma
            87949: {
                'warfareBuff1ID': 2442,
                'warfareBuff1Value': 5,
                'warfareBuff2ID': 2435,
                'warfareBuff2Value': 5,
                'warfareBuff3ID': 2436,
                'warfareBuff3Value': 10},
            # Electric
            87950: {
                'warfareBuff1ID': 2437,
                'warfareBuff1Value': -25,
                'warfareBuff2ID': 2438,
                'warfareBuff2Value': 25},
            # Exotic
            87951: {
                'warfareBuff1ID': 2440,
                'warfareBuff1Value': 2,
                'warfareBuff2ID': 2439,
                'warfareBuff2Value': 25}}
        effectMap = {100001: 'pyfaCustomSovUpgradeBuffEffect'}
        for typeID, attrMap in typeBuffMap.items():
            _hardcodeAttribs(typeID, attrMap)
            _hardcodeEffects(typeID, effectMap, clearEffects=False)
            eos.db.gamedata_session.flush()

    def hardcodeTrigSystemEffects():
        typeBuffMap = {
            # Final Liminality / Pochven
            87164: ('Final Liminality', {
                'warfareBuff1ID': 2534,
                'warfareBuff1Value': -50,
                'warfareBuff2ID': 2535,
                'warfareBuff2Value': -30,
                'warfareBuff3ID': 2538,
                'warfareBuff3Value': 30,
                'warfareBuff4ID': 2539,
                'warfareBuff4Value': 30}),
            # Minor Victory
            87177: ('Triglavian Minor Victory', {
                'warfareBuff1ID': 2534,
                'warfareBuff1Value': -50,
                'warfareBuff2ID': 2538,
                'warfareBuff2Value': 15,
                'warfareBuff3ID': 2539,
                'warfareBuff3Value': 15})}
        effectMap = {100002: 'pyfaCustomTrigSystemBuffEffect'}
        for typeID, (name, attrMap) in typeBuffMap.items():
            item = eos.db.gamedata_session.query(eos.gamedata.Item).filter(eos.gamedata.Item.ID == typeID).one()
            item.published = True
            item.name = name
            _hardcodeAttribs(typeID, attrMap)
            _hardcodeEffects(typeID, effectMap, clearEffects=True)
            eos.db.gamedata_session.flush()

    def hardcodeFwProxyEffects():
        typeBuffMap = {
            # BC level up
            92609: ('FW Moderate Site BC Skill Bonus', {
                'warfareBuff1ID': 2544,
                'warfareBuff1Value': 20}),
            # T3D HP
            95625: ('FW ELT-5 Site T3D HP Bonus', {
                'warfareBuff1ID': 2545,
                'warfareBuff1Value': 500})}
        effectMap = {100003: 'pyfaCustomFwProxyBuffEffect'}
        for typeID, (name, attrMap) in typeBuffMap.items():
            item = eos.db.gamedata_session.query(eos.gamedata.Item).filter(eos.gamedata.Item.ID == typeID).one()
            item.published = True
            item.name = name
            _hardcodeAttribs(typeID, attrMap)
            _hardcodeEffects(typeID, effectMap, clearEffects=True)
            eos.db.gamedata_session.flush()


    def hardcodeHerald():
        heraldTypeID = 1000000
        _copyItem(
            srcName='Sentinel',
            tgtTypeID=heraldTypeID,
            tgtName='Herald',
            tgtGroupName='Assault Frigate')
        attrMap = {
            # Fitting
            'powerOutput': 69,
            'cpuOutput': 222,
            'capacitorCapacity': 675,
            'rechargeRate': 187500,
            # Slots
            'hiSlots': 2,
            'medSlots': 5,
            'lowSlots': 5,
            'turretSlotsLeft': 2,
            'launcherSlotsLeft': 0,
            # Rigs
            'rigSlots': 2,
            'rigSize': 1,
            'upgradeCapacity': 400,
            # Shield
            'shieldCapacity': 664,
            'shieldRechargeRate': 625000,
            'shieldEmDamageResonance': 1 - 0.0,
            'shieldThermalDamageResonance': 1 - 0.2,
            'shieldKineticDamageResonance': 1 - 0.55,
            'shieldExplosiveDamageResonance': 1 - 0.875,
            # Armor
            'armorHP': 1337,
            'armorEmDamageResonance': 1 - 0.5,
            'armorThermalDamageResonance': 1 - 0.35,
            'armorKineticDamageResonance': 1 - 0.625,
            'armorExplosiveDamageResonance': 1 - 0.80,
            # Structure
            'hp': 1100,
            'emDamageResonance': 1 - 0.33,
            'thermalDamageResonance': 1 - 0.33,
            'kineticDamageResonance': 1 - 0.33,
            'explosiveDamageResonance': 1 - 0.33,
            'mass': 1010100,
            'volume': 28100,
            'capacity': 165,
            # Navigation
            'maxVelocity': 420,
            'agility': 3.3,
            'warpSpeedMultiplier': 5,
            # Drones
            'droneCapacity': 60,
            'droneBandwidth': 20,
            # Targeting
            'maxTargetRange': 69000,
            'maxLockedTargets': 7,
            'scanRadarStrength': 21,
            'scanLadarStrength': 0,
            'scanMagnetometricStrength': 0,
            'scanGravimetricStrength': 0,
            'signatureRadius': 35,
            'scanResolution': 650,
            # Misc
            'energyWarfareResistance': 0.5,
            'sensorDampenerResistance': 1,
            'stasisWebifierResistance': 1,
            'weaponDisruptionResistance': 1}
        effectMap = {
            100100: 'pyfaCustomHeraldAsfSetOptimal',
            100101: 'pyfaCustomHeraldAsfSetDmg',
            100102: 'pyfaCustomHeraldAsfArmorRes',
            100103: 'pyfaCustomHeraldEafCapRech',
            100104: 'pyfaCustomHeraldAmfWdStr',
            100105: 'pyfaCustomHeraldAmfSetRof',
            100106: 'pyfaCustomHeraldRoleMwdSigBlow'}
        _hardcodeAttribs(heraldTypeID, attrMap)
        _hardcodeEffects(heraldTypeID, effectMap)

    def hardcodePenitence():
        penitenceTypeID = 1000001
        _copyItem(
            srcName='Curse',
            tgtTypeID=penitenceTypeID,
            tgtName='Penitence',
            tgtGroupName='Heavy Interdiction Cruiser')
        attrMap = {
            # Fitting
            'powerOutput': 1450,
            'cpuOutput': 460,
            'capacitorCapacity': 3800,
            'rechargeRate': 360000,
            'hiSlots': 5,
            'medSlots': 6,
            'lowSlots': 7,
            'turretSlotsLeft': 2,
            'launcherSlotsLeft': 4,
            # Rigs
            'rigSlots': 2,
            'rigSize': 2,
            'upgradeCapacity': 400,
            # Shield
            'shieldCapacity': 2730,
            'shieldRechargeRate': 1250000,
            'shieldEmDamageResonance': 1 - 0.0,
            'shieldThermalDamageResonance': 1 - 0.2,
            'shieldKineticDamageResonance': 1 - 0.7,
            'shieldExplosiveDamageResonance': 1 - 0.875,
            # Armor
            'armorHP': 3540,
            'armorEmDamageResonance': 1 - 0.5,
            'armorThermalDamageResonance': 1 - 0.35,
            'armorKineticDamageResonance': 1 - 0.625,
            'armorExplosiveDamageResonance': 1 - 0.80,
            # Structure
            'hp': 2400,
            'emDamageResonance': 1 - 0.33,
            'thermalDamageResonance': 1 - 0.33,
            'kineticDamageResonance': 1 - 0.33,
            'explosiveDamageResonance': 1 - 0.33,
            'mass': 11810000,
            'volume': 118000,
            'capacity': 475,
            # Navigation
            'maxVelocity': 230,
            'agility': 0.48,
            'warpSpeedMultiplier': 4,
            # Drones
            'droneCapacity': 300,
            'droneBandwidth': 50,
            # Targeting
            'maxTargetRange': 110500,
            'maxLockedTargets': 10,
            'scanRadarStrength': 28,
            'scanLadarStrength': 0,
            'scanMagnetometricStrength': 0,
            'scanGravimetricStrength': 0,
            'signatureRadius': 115,
            'scanResolution': 330,
            # Misc
            'energyWarfareResistance': 1,
            'sensorDampenerResistance': 1,
            'stasisWebifierResistance': 1,
            'weaponDisruptionResistance': 1}
        effectMap = {
            100200: 'pyfaCustomPenitenceRecEneOptimal',
            100201: 'pyfaCustomPenitenceRecEneFalloff',
            100202: 'pyfaCustomPenitenceRecEneAmount',
            100203: 'pyfaCustomPenitenceHicWdfgOptimal',
            100204: 'pyfaCustomPenitenceAmcWdStr',
            100205: 'pyfaCustomPenitenceAmcDroneHpDmg',
            100206: 'pyfaCustomPenitenceAmcDroneControl',
            100207: 'pyfaCustomPenitenceRoleArmorRes',
            100208: 'pyfaCustomPenitenceRoleDroneSpeed',
            100209: 'pyfaCustomPenitenceRoleWdfgSig'}
        _hardcodeAttribs(penitenceTypeID, attrMap)
        _hardcodeEffects(penitenceTypeID, effectMap)

    hardcodeSuppressionTackleRange()
    hardcodeSovUpgradeBuffs()
    hardcodeTrigSystemEffects()
    hardcodeFwProxyEffects()

    hardcodeHerald()
    hardcodePenitence()

    eos.db.gamedata_session.commit()
    with eos.db.gamedata_engine.begin() as connection:
        connection.exec_driver_sql('VACUUM')

    print('done')


if __name__ == '__main__':
    update_db()