X-Git-Url: http://git.veekun.com/zzz-pokedex.git/blobdiff_plain/1fded58d8ba2c00841c0c6e297385b8d0f3a7af4..ea94f4be8b53b5708b22a1fa6336d89452a0ff21:/pokedex/db/tables.py diff --git a/pokedex/db/tables.py b/pokedex/db/tables.py index 491bfd2..e0b03fd 100644 --- a/pokedex/db/tables.py +++ b/pokedex/db/tables.py @@ -3,7 +3,8 @@ from sqlalchemy import Column, ForeignKey, MetaData, Table from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.associationproxy import association_proxy -from sqlalchemy.orm import backref, relation +from sqlalchemy.orm import backref, eagerload_all, relation +from sqlalchemy.orm.session import Session from sqlalchemy.sql import and_ from sqlalchemy.types import * @@ -17,8 +18,39 @@ class Ability(TableBase): __singlename__ = 'ability' id = Column(Integer, primary_key=True, nullable=False) name = Column(Unicode(24), nullable=False) + generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False) + effect = Column(rst.RstTextColumn(5120), nullable=False) + short_effect = Column(rst.RstTextColumn(255), nullable=False) + +class AbilityFlavorText(TableBase): + __tablename__ = 'ability_flavor_text' + ability_id = Column(Integer, ForeignKey('abilities.id'), primary_key=True, nullable=False, autoincrement=False) + version_group_id = Column(Integer, ForeignKey('version_groups.id'), primary_key=True, nullable=False, autoincrement=False) flavor_text = Column(Unicode(64), nullable=False) - effect = Column(Unicode(255), nullable=False) + +class Berry(TableBase): + __tablename__ = 'berries' + id = Column(Integer, primary_key=True, nullable=False) + item_id = Column(Integer, ForeignKey('items.id'), nullable=False) + firmness_id = Column(Integer, ForeignKey('berry_firmness.id'), nullable=False) + natural_gift_power = Column(Integer, nullable=True) + natural_gift_type_id = Column(Integer, ForeignKey('types.id'), nullable=True) + size = Column(Integer, nullable=False) + max_harvest = Column(Integer, nullable=False) + growth_time = Column(Integer, nullable=False) + soil_dryness = Column(Integer, nullable=False) + smoothness = Column(Integer, nullable=False) + +class BerryFirmness(TableBase): + __tablename__ = 'berry_firmness' + id = Column(Integer, primary_key=True, nullable=False) + name = Column(Unicode(10), nullable=False) + +class BerryFlavor(TableBase): + __tablename__ = 'berry_flavors' + berry_id = Column(Integer, ForeignKey('berries.id'), primary_key=True, nullable=False, autoincrement=False) + contest_type_id = Column(Integer, ForeignKey('contest_types.id'), primary_key=True, nullable=False, autoincrement=False) + flavor = Column(Integer, nullable=False) class ContestCombo(TableBase): __tablename__ = 'contest_combos' @@ -33,6 +65,13 @@ class ContestEffect(TableBase): flavor_text = Column(Unicode(64), nullable=False) effect = Column(Unicode(255), nullable=False) +class ContestType(TableBase): + __tablename__ = 'contest_types' + id = Column(Integer, primary_key=True, nullable=False) + name = Column(Unicode(6), nullable=False) + flavor = Column(Unicode(6), nullable=False) + color = Column(Unicode(6), nullable=False) + class EggGroup(TableBase): __tablename__ = 'egg_groups' id = Column(Integer, primary_key=True, nullable=False) @@ -141,6 +180,12 @@ class EvolutionMethod(TableBase): name = Column(Unicode(64), nullable=False) description = Column(Unicode(255), nullable=False) +class Experience(TableBase): + __tablename__ = 'experience' + growth_rate_id = Column(Integer, ForeignKey('growth_rates.id'), primary_key=True, nullable=False) + level = Column(Integer, primary_key=True, nullable=False, autoincrement=False) + experience = Column(Integer, nullable=False) + class Generation(TableBase): __tablename__ = 'generations' id = Column(Integer, primary_key=True, nullable=False) @@ -160,6 +205,55 @@ class Item(TableBase): __singlename__ = 'item' id = Column(Integer, primary_key=True, nullable=False) name = Column(Unicode(16), nullable=False) + category_id = Column(Integer, ForeignKey('item_categories.id'), nullable=False) + cost = Column(Integer, nullable=False) + fling_power = Column(Integer, nullable=True) + fling_effect_id = Column(Integer, ForeignKey('item_fling_effects.id'), nullable=True) + effect = Column(rst.RstTextColumn(5120), nullable=False) + + @property + def appears_underground(self): + return any(flag.identifier == u'underground' for flag in self.flags) + +class ItemCategory(TableBase): + __tablename__ = 'item_categories' + id = Column(Integer, primary_key=True, nullable=False) + pocket_id = Column(Integer, ForeignKey('item_pockets.id'), nullable=False) + name = Column(Unicode(16), nullable=False) + +class ItemFlag(TableBase): + __tablename__ = 'item_flags' + id = Column(Integer, primary_key=True, nullable=False) + identifier = Column(Unicode(24), nullable=False) + name = Column(Unicode(64), nullable=False) + +class ItemFlagMap(TableBase): + __tablename__ = 'item_flag_map' + item_id = Column(Integer, ForeignKey('items.id'), primary_key=True, autoincrement=False, nullable=False) + item_flag_id = Column(Integer, ForeignKey('item_flags.id'), primary_key=True, autoincrement=False, nullable=False) + +class ItemFlavorText(TableBase): + __tablename__ = 'item_flavor_text' + item_id = Column(Integer, ForeignKey('items.id'), primary_key=True, autoincrement=False, nullable=False) + version_group_id = Column(Integer, ForeignKey('version_groups.id'), primary_key=True, autoincrement=False, nullable=False) + flavor_text = Column(Unicode(255), nullable=False) + +class ItemFlingEffect(TableBase): + __tablename__ = 'item_fling_effects' + id = Column(Integer, primary_key=True, nullable=False) + effect = Column(Unicode(255), nullable=False) + +class ItemInternalID(TableBase): + __tablename__ = 'item_internal_ids' + item_id = Column(Integer, ForeignKey('items.id'), primary_key=True, autoincrement=False, nullable=False) + generation_id = Column(Integer, ForeignKey('generations.id'), primary_key=True, autoincrement=False, nullable=False) + internal_id = Column(Integer, nullable=False) + +class ItemPocket(TableBase): + __tablename__ = 'item_pockets' + id = Column(Integer, primary_key=True, nullable=False) + identifier = Column(Unicode(16), nullable=False) + name = Column(Unicode(16), nullable=False) class Language(TableBase): __tablename__ = 'languages' @@ -185,15 +279,26 @@ class LocationArea(TableBase): class LocationAreaEncounterRate(TableBase): __tablename__ = 'location_area_encounter_rates' location_area_id = Column(Integer, ForeignKey('location_areas.id'), primary_key=True, nullable=False, autoincrement=False) - encounter_type_id = Column(Integer, ForeignKey('encounter_terrain.id'), primary_key=True, nullable=False, autoincrement=False) + encounter_terrain_id = Column(Integer, ForeignKey('encounter_terrain.id'), primary_key=True, nullable=False, autoincrement=False) + version_id = Column(Integer, ForeignKey('versions.id'), primary_key=True, autoincrement=False) rate = Column(Integer, nullable=True) class Machine(TableBase): __tablename__ = 'machines' machine_number = Column(Integer, primary_key=True, nullable=False, autoincrement=False) version_group_id = Column(Integer, ForeignKey('version_groups.id'), primary_key=True, nullable=False, autoincrement=False) + item_id = Column(Integer, ForeignKey('items.id'), nullable=False) move_id = Column(Integer, ForeignKey('moves.id'), nullable=False) + @property + def is_hm(self): + return self.machine_number >= 100 + +class MoveBattleStyle(TableBase): + __tablename__ = 'move_battle_styles' + id = Column(Integer, primary_key=True, nullable=False) + name = Column(Unicode(8), nullable=False) + class MoveEffectCategory(TableBase): __tablename__ = 'move_effect_categories' id = Column(Integer, primary_key=True, nullable=False) @@ -233,7 +338,7 @@ class MoveFlagType(TableBase): class MoveFlavorText(TableBase): __tablename__ = 'move_flavor_text' move_id = Column(Integer, ForeignKey('moves.id'), primary_key=True, nullable=False, autoincrement=False) - generation_id = Column(Integer, ForeignKey('generations.id'), primary_key=True, nullable=False, autoincrement=False) + version_group_id = Column(Integer, ForeignKey('version_groups.id'), primary_key=True, nullable=False, autoincrement=False) flavor_text = Column(Unicode(255), nullable=False) class MoveName(TableBase): @@ -262,7 +367,7 @@ class Move(TableBase): damage_class_id = Column(Integer, ForeignKey('move_damage_classes.id'), nullable=False) effect_id = Column(Integer, ForeignKey('move_effects.id'), nullable=False) effect_chance = Column(Integer) - contest_type = Column(Unicode(8), nullable=False) + contest_type_id = Column(Integer, ForeignKey('contest_types.id'), nullable=True) contest_effect_id = Column(Integer, ForeignKey('contest_effects.id'), nullable=True) super_contest_effect_id = Column(Integer, ForeignKey('super_contest_effects.id'), nullable=False) @@ -273,6 +378,26 @@ class Nature(TableBase): name = Column(Unicode(8), nullable=False) decreased_stat_id = Column(Integer, ForeignKey('stats.id'), nullable=False) increased_stat_id = Column(Integer, ForeignKey('stats.id'), nullable=False) + hates_flavor_id = Column(Integer, ForeignKey('contest_types.id'), nullable=False) + likes_flavor_id = Column(Integer, ForeignKey('contest_types.id'), nullable=False) + +class NatureBattleStylePreference(TableBase): + __tablename__ = 'nature_battle_style_preferences' + nature_id = Column(Integer, ForeignKey('natures.id'), primary_key=True, nullable=False) + move_battle_style_id = Column(Integer, ForeignKey('move_battle_styles.id'), primary_key=True, nullable=False) + low_hp_preference = Column(Integer, nullable=False) + high_hp_preference = Column(Integer, nullable=False) + +class NaturePokeathlonStat(TableBase): + __tablename__ = 'nature_pokeathlon_stats' + nature_id = Column(Integer, ForeignKey('natures.id'), primary_key=True, nullable=False) + pokeathlon_stat_id = Column(Integer, ForeignKey('pokeathlon_stats.id'), primary_key=True, nullable=False) + max_change = Column(Integer, nullable=False) + +class PokeathlonStat(TableBase): + __tablename__ = 'pokeathlon_stats' + id = Column(Integer, primary_key=True, nullable=False) + name = Column(Unicode(8), nullable=False) class Pokedex(TableBase): __tablename__ = 'pokedexes' @@ -516,6 +641,18 @@ class Version(TableBase): ### Relations down here, to avoid ordering problems +Ability.flavor_text = relation(AbilityFlavorText, order_by=AbilityFlavorText.version_group_id, backref='abilities') +Ability.generation = relation(Generation, backref='abilities') + +AbilityFlavorText.version_group = relation(VersionGroup) + +Berry.berry_firmness = relation(BerryFirmness, backref='berries') +Berry.firmness = association_proxy('berry_firmness', 'name') +Berry.flavors = relation(BerryFlavor, order_by=BerryFlavor.contest_type_id, backref='berry') +Berry.natural_gift_type = relation(Type) + +BerryFlavor.contest_type = relation(ContestType) + ContestCombo.first = relation(Move, primaryjoin=ContestCombo.first_move_id==Move.id, backref='contest_combo_first') ContestCombo.second = relation(Move, primaryjoin=ContestCombo.second_move_id==Move.id, @@ -542,22 +679,44 @@ EncounterSlotCondition.condition = relation(EncounterCondition, EvolutionChain.growth_rate = relation(GrowthRate, backref='evolution_chains') +Experience.growth_rate = relation(GrowthRate, backref='experience_table') + Generation.canonical_pokedex = relation(Pokedex, backref='canonical_for_generation') Generation.versions = relation(Version, secondary=VersionGroup.__table__) Generation.main_region = relation(Region) +GrowthRate.max_experience_obj = relation(Experience, primaryjoin=and_(Experience.growth_rate_id == GrowthRate.id, Experience.level == 100), uselist=False) +GrowthRate.max_experience = association_proxy('max_experience_obj', 'experience') + +Item.berry = relation(Berry, uselist=False, backref='item') +Item.flags = relation(ItemFlag, secondary=ItemFlagMap.__table__) +Item.flavor_text = relation(ItemFlavorText, order_by=ItemFlavorText.version_group_id.asc(), backref='item') +Item.fling_effect = relation(ItemFlingEffect, backref='items') +Item.machines = relation(Machine, order_by=Machine.version_group_id.asc()) +Item.category = relation(ItemCategory) +Item.pocket = association_proxy('category', 'pocket') + +ItemCategory.items = relation(Item, order_by=Item.name) +ItemCategory.pocket = relation(ItemPocket) + +ItemFlavorText.version_group = relation(VersionGroup) + +ItemPocket.categories = relation(ItemCategory, order_by=ItemCategory.name) + Location.region = relation(Region, backref='locations') LocationArea.location = relation(Location, backref='areas') +Machine.item = relation(Item) Machine.version_group = relation(VersionGroup) Move.contest_effect = relation(ContestEffect, backref='moves') Move.contest_combo_next = association_proxy('contest_combo_first', 'second') Move.contest_combo_prev = association_proxy('contest_combo_second', 'first') +Move.contest_type = relation(ContestType, backref='moves') Move.damage_class = relation(MoveDamageClass, backref='moves') Move.flags = association_proxy('move_flags', 'flag') -Move.flavor_text = relation(MoveFlavorText, order_by=MoveFlavorText.generation_id, backref='move') +Move.flavor_text = relation(MoveFlavorText, order_by=MoveFlavorText.version_group_id, backref='move') Move.foreign_names = relation(MoveName, backref='pokemon') Move.generation = relation(Generation, backref='moves') Move.machines = relation(Machine, backref='move') @@ -579,7 +738,7 @@ MoveEffectCategoryMap.category = relation(MoveEffectCategory) MoveFlag.flag = relation(MoveFlagType) -MoveFlavorText.generation = relation(Generation) +MoveFlavorText.version_group = relation(VersionGroup) MoveName.language = relation(Language) @@ -587,6 +746,18 @@ Nature.decreased_stat = relation(Stat, primaryjoin=Nature.decreased_stat_id==Sta backref='decreasing_natures') Nature.increased_stat = relation(Stat, primaryjoin=Nature.increased_stat_id==Stat.id, backref='increasing_natures') +Nature.hates_flavor = relation(ContestType, primaryjoin=Nature.hates_flavor_id==ContestType.id, + backref='hating_natures') +Nature.likes_flavor = relation(ContestType, primaryjoin=Nature.likes_flavor_id==ContestType.id, + backref='liking_natures') +Nature.battle_style_preferences = relation(NatureBattleStylePreference, + order_by=NatureBattleStylePreference.move_battle_style_id, + backref='nature') +Nature.pokeathlon_effects = relation(NaturePokeathlonStat, order_by=NaturePokeathlonStat.pokeathlon_stat_id) + +NatureBattleStylePreference.battle_style = relation(MoveBattleStyle, backref='nature_preferences') + +NaturePokeathlonStat.pokeathlon_stat = relation(PokeathlonStat, backref='nature_effects') Pokedex.region = relation(Region, backref='pokedexes') Pokedex.version_groups = relation(VersionGroup, secondary=PokedexVersionGroup.__table__, backref='pokedexes') @@ -614,15 +785,15 @@ Pokemon.evolution_method = relation(EvolutionMethod) Pokemon.evolution_children = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.evolution_parent_pokemon_id, backref=backref('evolution_parent', remote_side=[Pokemon.id])) -Pokemon.flavor_text = relation(PokemonFlavorText, order_by=PokemonFlavorText.pokemon_id, backref='pokemon') +Pokemon.flavor_text = relation(PokemonFlavorText, order_by=PokemonFlavorText.version_id.asc(), backref='pokemon') Pokemon.foreign_names = relation(PokemonName, backref='pokemon') Pokemon.pokemon_habitat = relation(PokemonHabitat, backref='pokemon') Pokemon.habitat = association_proxy('pokemon_habitat', 'name') -Pokemon.items = relation(PokemonItem) +Pokemon.items = relation(PokemonItem, backref='pokemon') Pokemon.generation = relation(Generation, backref='pokemon') Pokemon.shape = relation(PokemonShape, backref='pokemon') Pokemon.stats = relation(PokemonStat, backref='pokemon') -Pokemon.types = relation(Type, secondary=PokemonType.__table__) +Pokemon.types = relation(Type, secondary=PokemonType.__table__, order_by=PokemonType.slot.asc()) PokemonDexNumber.pokedex = relation(Pokedex)