pokedex/db/tables.py

   1 from sqlalchemy import Column, ForeignKey, MetaData, Table
   2 from sqlalchemy.ext.declarative import declarative_base
   3 from sqlalchemy.orm import backref, relation
   4 from sqlalchemy.types import *
   5 from sqlalchemy.databases.mysql import *
   6
   7 metadata = MetaData()
   8 TableBase = declarative_base(metadata=metadata)
   9
  10 class Ability(TableBase):
  11     __tablename__ = 'abilities'
  12     id = Column(Integer, primary_key=True, nullable=False)
  13     name = Column(Unicode(24), nullable=False)
  14     flavor_text = Column(Unicode(64), nullable=False)
  15     effect = Column(Unicode(255), nullable=False)
  16
  17 class ContestEffect(TableBase):
  18     __tablename__ = 'contest_effects'
  19     id = Column(Integer, primary_key=True, nullable=False)
  20     appeal = Column(SmallInteger, nullable=False)
  21     jam = Column(SmallInteger, nullable=False)
  22     flavor = Column(Unicode(255), nullable=False)
  23     effect = Column(Unicode(255), nullable=False)
  24
  25 class EggGroup(TableBase):
  26     __tablename__ = 'egg_groups'
  27     id = Column(Integer, primary_key=True, nullable=False)
  28     name = Column(Unicode(16), nullable=False)
  29
  30 class Encounter(TableBase):
  31     """Rows in this table represent encounters with wild Pokémon.
  32
  33     Within a given area in a given game, encounters are differentiated by the
  34     slot they are in and a world condition.
  35
  36     Groups of slots belong to encounter types; these are what the player is
  37     doing to get an encounter, such as surfing or walking through tall grass.
  38
  39     Within an encounter type, slots are defined primarily by rarity.  Each slot
  40     can also be affected by a world condition; for example, the 20% slot for
  41     walking in tall grass is affected by whether a swarm is in effect in the
  42     areas.  "There is a swarm" and "there is not a swarm" are conditions, and
  43     together they make a condition group.  However, since "not a swarm" is a
  44     base state rather than any sort of new state, it is omitted and instead
  45     referred to by a NULL.
  46
  47     A slot (20% walking in grass) and single world condition (NULL, i.e. no
  48     swarm) are thus enough to define a specific encounter.
  49
  50     Well, okay, almost: each slot actually appears twice.
  51     """
  52
  53     __tablename__ = 'encounters'
  54     id = Column(Integer, primary_key=True, nullable=False)
  55     version_id = Column(Integer, ForeignKey('versions.id'), nullable=False, autoincrement=False)
  56     location_area_id = Column(Integer, ForeignKey('location_areas.id'), nullable=False, autoincrement=False)
  57     encounter_type_slot_id = Column(Integer, ForeignKey('encounter_type_slots.id'), nullable=False, autoincrement=False)
  58     encounter_condition_id = Column(Integer, ForeignKey('encounter_conditions.id'), nullable=True, autoincrement=False)
  59     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), nullable=False, autoincrement=False)
  60     min_level = Column(Integer, nullable=False, autoincrement=False)
  61     max_level = Column(Integer, nullable=False, autoincrement=False)
  62
  63 class EncounterCondition(TableBase):
  64     """Rows in this table represent something different about the world that
  65     can affect what Pokémon are encountered.
  66     """
  67
  68     __tablename__ = 'encounter_conditions'
  69     id = Column(Integer, primary_key=True, nullable=False)
  70     encounter_condition_group_id = Column(Integer, ForeignKey('encounter_condition_groups.id'), primary_key=False, nullable=False, autoincrement=False)
  71     name = Column(Unicode(64), nullable=False)
  72
  73 class EncounterConditionGroup(TableBase):
  74     """Rows in this table represent a group of mutually exclusive conditions,
  75     such as morning/day/night.  "Conditions" that are part of the default state
  76     of the world, such as "not during a swarm" or "not using the PokéRadar",
  77     are not included in this table and are referred to by NULLs in other
  78     tables.
  79     """
  80
  81     __tablename__ = 'encounter_condition_groups'
  82     id = Column(Integer, primary_key=True, nullable=False)
  83     name = Column(Unicode(64), nullable=False)
  84
  85 class EncounterType(TableBase):
  86     """Rows in this table represent ways the player can enter a wild encounter;
  87     i.e. surfing, fishing, or walking through tall grass.
  88     """
  89
  90     __tablename__ = 'encounter_types'
  91     id = Column(Integer, primary_key=True, nullable=False)
  92     name = Column(Unicode(64), nullable=False)
  93
  94 class EncounterTypeSlot(TableBase):
  95     """Rows in this table represent an abstract "slot" within an encounter
  96     type, associated with both a condition group and a rarity.
  97
  98     Note that there are two encounters per slot, so the rarities will only add
  99     up to 50.
 100     """
 101
 102     __tablename__ = 'encounter_type_slots'
 103     id = Column(Integer, primary_key=True, nullable=False)
 104     encounter_type_id = Column(Integer, ForeignKey('encounter_types.id'), primary_key=False, nullable=False, autoincrement=False)
 105     encounter_condition_group_id = Column(Integer, ForeignKey('encounter_condition_groups.id'), primary_key=False, nullable=True, autoincrement=False)
 106     rarity = Column(Integer, nullable=False, autoincrement=False)
 107
 108 class EvolutionChain(TableBase):
 109     __tablename__ = 'evolution_chains'
 110     id = Column(Integer, primary_key=True, nullable=False)
 111     growth_rate_id = Column(Integer, ForeignKey('growth_rates.id'), nullable=False)
 112     steps_to_hatch = Column(Integer, nullable=False)
 113     baby_trigger_item = Column(Unicode(12))
 114
 115 class EvolutionMethod(TableBase):
 116     __tablename__ = 'evolution_methods'
 117     id = Column(Integer, primary_key=True, nullable=False)
 118     name = Column(Unicode(64), nullable=False)
 119     description = Column(Unicode(255), nullable=False)
 120
 121 class Generation(TableBase):
 122     __tablename__ = 'generations'
 123     id = Column(Integer, primary_key=True, nullable=False)
 124     name = Column(Unicode(16), nullable=False)
 125     main_region = Column(Unicode(16), nullable=False)
 126
 127 class GrowthRate(TableBase):
 128     __tablename__ = 'growth_rates'
 129     id = Column(Integer, primary_key=True, nullable=False)
 130     name = Column(Unicode(16), nullable=False)
 131     formula = Column(Unicode(255), nullable=False)
 132
 133 class Language(TableBase):
 134     __tablename__ = 'languages'
 135     id = Column(Integer, primary_key=True, nullable=False)
 136     name = Column(Unicode(16), nullable=False)
 137
 138 class Location(TableBase):
 139     __tablename__ = 'locations'
 140     id = Column(Integer, primary_key=True, nullable=False)
 141     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 142     name = Column(Unicode(64), nullable=False)
 143
 144 class LocationArea(TableBase):
 145     __tablename__ = 'location_areas'
 146     id = Column(Integer, primary_key=True, nullable=False)
 147     location_id = Column(Integer, ForeignKey('locations.id'), nullable=False)
 148     internal_id = Column(Integer, nullable=False)
 149     name = Column(Unicode(64), nullable=True)
 150
 151 class MoveEffect(TableBase):
 152     __tablename__ = 'move_effects'
 153     id = Column(Integer, primary_key=True, nullable=False)
 154     priority = Column(SmallInteger, nullable=False)
 155     short_effect = Column(Unicode(128), nullable=False)
 156     effect = Column(Unicode(255), nullable=False)
 157
 158 class MoveTarget(TableBase):
 159     __tablename__ = 'move_targets'
 160     id = Column(Integer, primary_key=True, nullable=False)
 161     name = Column(Unicode(32), nullable=False)
 162     description = Column(Unicode(128), nullable=False)
 163
 164 class Move(TableBase):
 165     __tablename__ = 'moves'
 166     id = Column(Integer, primary_key=True, nullable=False)
 167     name = Column(Unicode(12), nullable=False)
 168     type_id = Column(Integer, ForeignKey('types.id'), nullable=False)
 169     power = Column(SmallInteger)
 170     pp = Column(SmallInteger, nullable=False)
 171     accuracy = Column(SmallInteger)
 172     target_id = Column(Integer, ForeignKey('move_targets.id'), nullable=False)
 173     category = Column(Unicode(8), nullable=False)
 174     effect_id = Column(Integer, ForeignKey('move_effects.id'), nullable=False)
 175     effect_chance = Column(Integer)
 176     contest_type = Column(Unicode(8), nullable=False)
 177     contest_effect_id = Column(Integer, ForeignKey('contest_effects.id'), nullable=False)
 178     super_contest_effect_id = Column(Integer, nullable=False)
 179
 180 class Pokemon(TableBase):
 181     __tablename__ = 'pokemon'
 182     id = Column(Integer, primary_key=True, nullable=False)
 183     name = Column(Unicode(20), nullable=False)
 184     forme_name = Column(Unicode(16))
 185     forme_base_pokemon_id = Column(Integer, ForeignKey('pokemon.id'))
 186     generation_id = Column(Integer, ForeignKey('generations.id'))
 187     evolution_chain_id = Column(Integer, ForeignKey('evolution_chains.id'))
 188     evolution_parent_pokemon_id = Column(Integer, ForeignKey('pokemon.id'))
 189     evolution_method_id = Column(Integer, ForeignKey('evolution_methods.id'))
 190     evolution_parameter = Column(Unicode(32))
 191     height = Column(Integer, nullable=False)
 192     weight = Column(Integer, nullable=False)
 193     species = Column(Unicode(16), nullable=False)
 194     color = Column(Unicode(6), nullable=False)
 195     pokemon_shape_id = Column(Integer, ForeignKey('pokemon_shapes.id'), nullable=False)
 196     habitat = Column(Unicode(16), nullable=False)
 197     gender_rate = Column(Integer, nullable=False)
 198     capture_rate = Column(Integer, nullable=False)
 199     base_experience = Column(Integer, nullable=False)
 200     base_happiness = Column(Integer, nullable=False)
 201     gen1_internal_id = Column(Integer)
 202     is_baby = Column(Boolean, nullable=False)
 203     has_gen4_fem_sprite = Column(Boolean, nullable=False)
 204     has_gen4_fem_back_sprite = Column(Boolean, nullable=False)
 205
 206 class PokemonAbility(TableBase):
 207     __tablename__ = 'pokemon_abilities'
 208     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 209     ability_id = Column(Integer, ForeignKey('abilities.id'), nullable=False)
 210     slot = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 211
 212 class PokemonDexNumber(TableBase):
 213     __tablename__ = 'pokemon_dex_numbers'
 214     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 215     generation_id = Column(Integer, ForeignKey('generations.id'), primary_key=True, nullable=False, autoincrement=False)
 216     pokedex_number = Column(Integer, nullable=False)
 217
 218 class PokemonEggGroup(TableBase):
 219     __tablename__ = 'pokemon_egg_groups'
 220     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 221     egg_group_id = Column(Integer, ForeignKey('egg_groups.id'), primary_key=True, nullable=False, autoincrement=False)
 222
 223 class PokemonFlavorText(TableBase):
 224     __tablename__ = 'pokemon_flavor_text'
 225     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 226     version_id = Column(Integer, ForeignKey('versions.id'), primary_key=True, nullable=False, autoincrement=False)
 227     flavor_text = Column(Unicode(255), nullable=False)
 228
 229 class PokemonName(TableBase):
 230     __tablename__ = 'pokemon_names'
 231     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 232     language_id = Column(Integer, ForeignKey('languages.id'), primary_key=True, nullable=False, autoincrement=False)
 233     name = Column(Unicode(16), nullable=False)
 234
 235 class PokemonShape(TableBase):
 236     __tablename__ = 'pokemon_shapes'
 237     id = Column(Integer, primary_key=True, nullable=False)
 238     name = Column(Unicode(24), nullable=False)
 239     awesome_name = Column(Unicode(16), nullable=False)
 240
 241 class PokemonStat(TableBase):
 242     __tablename__ = 'pokemon_stats'
 243     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 244     stat_id = Column(Integer, ForeignKey('stats.id'), primary_key=True, nullable=False, autoincrement=False)
 245     base_stat = Column(Integer, nullable=False)
 246     effort = Column(Integer, nullable=False)
 247
 248 class PokemonType(TableBase):
 249     __tablename__ = 'pokemon_types'
 250     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 251     type_id = Column(Integer, ForeignKey('types.id'), nullable=False)
 252     slot = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 253
 254 class Stat(TableBase):
 255     __tablename__ = 'stats'
 256     id = Column(Integer, primary_key=True, nullable=False)
 257     name = Column(Unicode(16), nullable=False)
 258
 259 class TypeEfficacy(TableBase):
 260     __tablename__ = 'type_efficacy'
 261     damage_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 262     target_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 263     damage_factor = Column(Integer, nullable=False)
 264
 265 class Type(TableBase):
 266     __tablename__ = 'types'
 267     id = Column(Integer, primary_key=True, nullable=False)
 268     name = Column(Unicode(8), nullable=False)
 269     abbreviation = Column(Unicode(3), nullable=False)
 270
 271 class VersionGroup(TableBase):
 272     __tablename__ = 'version_groups'
 273     id = Column(Integer, primary_key=True, nullable=False)
 274     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 275
 276 class Version(TableBase):
 277     __tablename__ = 'versions'
 278     id = Column(Integer, primary_key=True, nullable=False)
 279     version_group_id = Column(Integer, ForeignKey('version_groups.id'), nullable=False)
 280     name = Column(Unicode(32), nullable=False)
 281
 282
 283 ### Relations down here, to avoid ordering problems
 284 Encounter.pokemon = relation(Pokemon, backref='encounters')
 285 Encounter.version = relation(Version, backref='encounters')
 286 Encounter.location_area = relation(LocationArea, backref='encounters')
 287 Encounter.slot = relation(EncounterTypeSlot, backref='encounters')
 288 Encounter.condition = relation(EncounterCondition, backref='encounters')
 289
 290 EncounterCondition.group = relation(EncounterConditionGroup,
 291                                     backref='conditions')
 292
 293 EncounterTypeSlot.type = relation(EncounterType, backref='slots')
 294
 295 EvolutionChain.growth_rate = relation(GrowthRate, backref='evolution_chains')
 296
 297 LocationArea.location = relation(Location, backref='areas')
 298
 299 Pokemon.abilities = relation(Ability, secondary=PokemonAbility.__table__,
 300                                       order_by=PokemonAbility.slot,
 301                                       backref='pokemon')
 302 Pokemon.dex_numbers = relation(PokemonDexNumber, backref='pokemon')
 303 Pokemon.egg_groups = relation(EggGroup, secondary=PokemonEggGroup.__table__,
 304                                         order_by=PokemonEggGroup.egg_group_id,
 305                                         backref='pokemon')
 306 Pokemon.evolution_chain = relation(EvolutionChain, backref='pokemon')
 307 Pokemon.evolution_method = relation(EvolutionMethod)
 308 Pokemon.evolution_children = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.evolution_parent_pokemon_id,
 309                                                backref=backref('evolution_parent',
 310                                                                remote_side=[Pokemon.id]))
 311 Pokemon.flavor_text = relation(PokemonFlavorText, backref='pokemon')
 312 Pokemon.foreign_names = relation(PokemonName, backref='pokemon')
 313 Pokemon.generation = relation(Generation, backref='pokemon')
 314 Pokemon.shape = relation(PokemonShape, backref='pokemon')
 315 Pokemon.stats = relation(PokemonStat, backref='pokemon')
 316 Pokemon.types = relation(Type, secondary=PokemonType.__table__)
 317
 318 PokemonDexNumber.generation = relation(Generation)
 319
 320 PokemonFlavorText.version = relation(Version)
 321
 322 PokemonName.language = relation(Language)
 323
 324 PokemonStat.stat = relation(Stat)
 325
 326 Type.damage_efficacies = relation(TypeEfficacy,
 327                                   primaryjoin=Type.id
 328                                       ==TypeEfficacy.damage_type_id,
 329                                   backref='damage_type')
 330 Type.target_efficacies = relation(TypeEfficacy,
 331                                   primaryjoin=Type.id
 332                                       ==TypeEfficacy.target_type_id,
 333                                   backref='target_type')
 334
 335 Version.generation = relation(Generation, secondary=VersionGroup.__table__,
 336                               backref='versions')