pokedex/db/tables.py

   1 # encoding: utf8
   2
   3 from sqlalchemy import Column, ForeignKey, MetaData, Table
   4 from sqlalchemy.ext.declarative import declarative_base
   5 from sqlalchemy.ext.associationproxy import association_proxy
   6 from sqlalchemy.orm import backref, relation
   7 from sqlalchemy.types import *
   8 from sqlalchemy.databases.mysql import *
   9
  10 metadata = MetaData()
  11 TableBase = declarative_base(metadata=metadata)
  12
  13 class Ability(TableBase):
  14     __tablename__ = 'abilities'
  15     id = Column(Integer, primary_key=True, nullable=False)
  16     name = Column(Unicode(24), nullable=False)
  17     flavor_text = Column(Unicode(64), nullable=False)
  18     effect = Column(Unicode(255), nullable=False)
  19
  20 class ContestEffect(TableBase):
  21     __tablename__ = 'contest_effects'
  22     id = Column(Integer, primary_key=True, nullable=False)
  23     appeal = Column(SmallInteger, nullable=False)
  24     jam = Column(SmallInteger, nullable=False)
  25     flavor = Column(Unicode(255), nullable=False)
  26     effect = Column(Unicode(255), nullable=False)
  27
  28 class EggGroup(TableBase):
  29     __tablename__ = 'egg_groups'
  30     id = Column(Integer, primary_key=True, nullable=False)
  31     name = Column(Unicode(16), nullable=False)
  32
  33 class Encounter(TableBase):
  34     """Rows in this table represent encounters with wild Pokémon.
  35
  36     Within a given area in a given game, encounters are differentiated by the
  37     slot they are in and a world condition.
  38
  39     Groups of slots belong to encounter types; these are what the player is
  40     doing to get an encounter, such as surfing or walking through tall grass.
  41
  42     Within an encounter type, slots are defined primarily by rarity.  Each slot
  43     can also be affected by a world condition; for example, the 20% slot for
  44     walking in tall grass is affected by whether a swarm is in effect in the
  45     areas.  "There is a swarm" and "there is not a swarm" are conditions, and
  46     together they make a condition group.  However, since "not a swarm" is a
  47     base state rather than any sort of new state, it is omitted and instead
  48     referred to by a NULL.
  49
  50     A slot (20% walking in grass) and single world condition (NULL, i.e. no
  51     swarm) are thus enough to define a specific encounter.
  52
  53     Well, okay, almost: each slot actually appears twice.
  54     """
  55
  56     __tablename__ = 'encounters'
  57     id = Column(Integer, primary_key=True, nullable=False)
  58     version_id = Column(Integer, ForeignKey('versions.id'), nullable=False, autoincrement=False)
  59     location_area_id = Column(Integer, ForeignKey('location_areas.id'), nullable=False, autoincrement=False)
  60     encounter_type_slot_id = Column(Integer, ForeignKey('encounter_type_slots.id'), nullable=False, autoincrement=False)
  61     encounter_condition_id = Column(Integer, ForeignKey('encounter_conditions.id'), nullable=True, autoincrement=False)
  62     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), nullable=False, autoincrement=False)
  63     min_level = Column(Integer, nullable=False, autoincrement=False)
  64     max_level = Column(Integer, nullable=False, autoincrement=False)
  65
  66 class EncounterCondition(TableBase):
  67     """Rows in this table represent something different about the world that
  68     can affect what Pokémon are encountered.
  69     """
  70
  71     __tablename__ = 'encounter_conditions'
  72     id = Column(Integer, primary_key=True, nullable=False)
  73     encounter_condition_group_id = Column(Integer, ForeignKey('encounter_condition_groups.id'), primary_key=False, nullable=False, autoincrement=False)
  74     name = Column(Unicode(64), nullable=False)
  75
  76 class EncounterConditionGroup(TableBase):
  77     """Rows in this table represent a group of mutually exclusive conditions,
  78     such as morning/day/night.  "Conditions" that are part of the default state
  79     of the world, such as "not during a swarm" or "not using the PokéRadar",
  80     are not included in this table and are referred to by NULLs in other
  81     tables.
  82     """
  83
  84     __tablename__ = 'encounter_condition_groups'
  85     id = Column(Integer, primary_key=True, nullable=False)
  86     name = Column(Unicode(64), nullable=False)
  87
  88 class EncounterType(TableBase):
  89     """Rows in this table represent ways the player can enter a wild encounter;
  90     i.e. surfing, fishing, or walking through tall grass.
  91     """
  92
  93     __tablename__ = 'encounter_types'
  94     id = Column(Integer, primary_key=True, nullable=False)
  95     name = Column(Unicode(64), nullable=False)
  96
  97 class EncounterTypeSlot(TableBase):
  98     """Rows in this table represent an abstract "slot" within an encounter
  99     type, associated with both a condition group and a rarity.
 100
 101     Note that there are two encounters per slot, so the rarities will only add
 102     up to 50.
 103     """
 104
 105     __tablename__ = 'encounter_type_slots'
 106     id = Column(Integer, primary_key=True, nullable=False)
 107     encounter_type_id = Column(Integer, ForeignKey('encounter_types.id'), primary_key=False, nullable=False, autoincrement=False)
 108     encounter_condition_group_id = Column(Integer, ForeignKey('encounter_condition_groups.id'), primary_key=False, nullable=True, autoincrement=False)
 109     rarity = Column(Integer, nullable=False, autoincrement=False)
 110
 111 class EvolutionChain(TableBase):
 112     __tablename__ = 'evolution_chains'
 113     id = Column(Integer, primary_key=True, nullable=False)
 114     growth_rate_id = Column(Integer, ForeignKey('growth_rates.id'), nullable=False)
 115     steps_to_hatch = Column(Integer, nullable=False)
 116     baby_trigger_item = Column(Unicode(12))
 117
 118 class EvolutionMethod(TableBase):
 119     __tablename__ = 'evolution_methods'
 120     id = Column(Integer, primary_key=True, nullable=False)
 121     name = Column(Unicode(64), nullable=False)
 122     description = Column(Unicode(255), nullable=False)
 123
 124 class Generation(TableBase):
 125     __tablename__ = 'generations'
 126     id = Column(Integer, primary_key=True, nullable=False)
 127     name = Column(Unicode(16), nullable=False)
 128     main_region = Column(Unicode(16), nullable=False)
 129
 130 class GrowthRate(TableBase):
 131     """`formula` is written in LaTeX math notation."""
 132     __tablename__ = 'growth_rates'
 133     id = Column(Integer, primary_key=True, nullable=False)
 134     name = Column(Unicode(20), nullable=False)
 135     formula = Column(Unicode(500), nullable=False)
 136
 137 class Item(TableBase):
 138     __tablename__ = 'items'
 139     id = Column(Integer, primary_key=True, nullable=False)
 140     name = Column(Unicode(16), nullable=False)
 141
 142 class Language(TableBase):
 143     __tablename__ = 'languages'
 144     id = Column(Integer, primary_key=True, nullable=False)
 145     name = Column(Unicode(16), nullable=False)
 146
 147 class Location(TableBase):
 148     __tablename__ = 'locations'
 149     id = Column(Integer, primary_key=True, nullable=False)
 150     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 151     name = Column(Unicode(64), nullable=False)
 152
 153 class LocationArea(TableBase):
 154     __tablename__ = 'location_areas'
 155     id = Column(Integer, primary_key=True, nullable=False)
 156     location_id = Column(Integer, ForeignKey('locations.id'), nullable=False)
 157     internal_id = Column(Integer, nullable=False)
 158     name = Column(Unicode(64), nullable=True)
 159
 160 class Machine(TableBase):
 161     __tablename__ = 'machines'
 162     machine_number = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 163     generation_id = Column(Integer, ForeignKey('generations.id'), primary_key=True, nullable=False, autoincrement=False)
 164     move_id = Column(Integer, ForeignKey('moves.id'), nullable=False)
 165
 166 class MoveDamageClass(TableBase):
 167     __tablename__ = 'move_damage_classes'
 168     id = Column(Integer, primary_key=True, nullable=False)
 169     name = Column(Unicode(8), nullable=False)
 170     description = Column(Unicode(64), nullable=False)
 171
 172 class MoveEffect(TableBase):
 173     __tablename__ = 'move_effects'
 174     id = Column(Integer, primary_key=True, nullable=False)
 175     priority = Column(SmallInteger, nullable=False)
 176     short_effect = Column(Unicode(128), nullable=False)
 177     effect = Column(Unicode(255), nullable=False)
 178
 179 class MoveTarget(TableBase):
 180     __tablename__ = 'move_targets'
 181     id = Column(Integer, primary_key=True, nullable=False)
 182     name = Column(Unicode(32), nullable=False)
 183     description = Column(Unicode(128), nullable=False)
 184
 185 class Move(TableBase):
 186     __tablename__ = 'moves'
 187     id = Column(Integer, primary_key=True, nullable=False)
 188     name = Column(Unicode(12), nullable=False)
 189     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 190     type_id = Column(Integer, ForeignKey('types.id'), nullable=False)
 191     power = Column(SmallInteger)
 192     pp = Column(SmallInteger, nullable=False)
 193     accuracy = Column(SmallInteger)
 194     target_id = Column(Integer, ForeignKey('move_targets.id'), nullable=False)
 195     damage_class_id = Column(Integer, ForeignKey('move_damage_classes.id'), nullable=False)
 196     effect_id = Column(Integer, ForeignKey('move_effects.id'), nullable=False)
 197     effect_chance = Column(Integer)
 198     contest_type = Column(Unicode(8), nullable=False)
 199     contest_effect_id = Column(Integer, ForeignKey('contest_effects.id'), nullable=True)
 200     super_contest_effect_id = Column(Integer, nullable=False)
 201
 202 class Pokemon(TableBase):
 203     """The core to this whole mess.
 204
 205     Note that I use both 'forme' and 'form' in both code and the database.  I
 206     only use 'forme' when specifically referring to Pokémon that have multiple
 207     distinct species as forms—i.e., different stats or movesets.  'Form' is a
 208     more general term referring to any variation within a species, including
 209     purely cosmetic forms like Unown.
 210     """
 211     __tablename__ = 'pokemon'
 212     id = Column(Integer, primary_key=True, nullable=False)
 213     name = Column(Unicode(20), nullable=False)
 214     forme_name = Column(Unicode(16))
 215     forme_base_pokemon_id = Column(Integer, ForeignKey('pokemon.id'))
 216     generation_id = Column(Integer, ForeignKey('generations.id'))
 217     evolution_chain_id = Column(Integer, ForeignKey('evolution_chains.id'))
 218     evolution_parent_pokemon_id = Column(Integer, ForeignKey('pokemon.id'))
 219     evolution_method_id = Column(Integer, ForeignKey('evolution_methods.id'))
 220     evolution_parameter = Column(Unicode(32))
 221     height = Column(Integer, nullable=False)
 222     weight = Column(Integer, nullable=False)
 223     species = Column(Unicode(16), nullable=False)
 224     color = Column(Unicode(6), nullable=False)
 225     pokemon_shape_id = Column(Integer, ForeignKey('pokemon_shapes.id'), nullable=False)
 226     habitat = Column(Unicode(16), nullable=False)
 227     gender_rate = Column(Integer, nullable=False)
 228     capture_rate = Column(Integer, nullable=False)
 229     base_experience = Column(Integer, nullable=False)
 230     base_happiness = Column(Integer, nullable=False)
 231     gen1_internal_id = Column(Integer)
 232     is_baby = Column(Boolean, nullable=False)
 233     has_gen4_fem_sprite = Column(Boolean, nullable=False)
 234     has_gen4_fem_back_sprite = Column(Boolean, nullable=False)
 235
 236     ### Stuff to handle alternate Pokémon forms
 237
 238     @property
 239     def national_id(self):
 240         """Returns the National Pokédex number for this Pokémon.  Use this
 241         instead of the id directly; alternate formes may make the id incorrect.
 242         """
 243
 244         if self.forme_base_pokemon_id:
 245             return self.forme_base_pokemon_id
 246         return self.id
 247
 248     @property
 249     def full_name(self):
 250         """Returns the name of this Pokémon, including its Forme, if any."""
 251
 252         if self.forme_name:
 253             return "%s %s" % (self.forme_name.capitalize(), self.name)
 254         return self.name
 255
 256     @property
 257     def normal_form(self):
 258         """Returns the normal form for this Pokémon; i.e., this will return
 259         regular Deoxys when called on any Deoxys form.
 260         """
 261
 262         if self.forme_base_pokemon:
 263             return self.forme_base_pokemon
 264
 265         return self
 266
 267 class PokemonAbility(TableBase):
 268     __tablename__ = 'pokemon_abilities'
 269     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 270     ability_id = Column(Integer, ForeignKey('abilities.id'), nullable=False)
 271     slot = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 272
 273 class PokemonDexNumber(TableBase):
 274     __tablename__ = 'pokemon_dex_numbers'
 275     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 276     generation_id = Column(Integer, ForeignKey('generations.id'), primary_key=True, nullable=False, autoincrement=False)
 277     pokedex_number = Column(Integer, nullable=False)
 278
 279 class PokemonEggGroup(TableBase):
 280     __tablename__ = 'pokemon_egg_groups'
 281     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 282     egg_group_id = Column(Integer, ForeignKey('egg_groups.id'), primary_key=True, nullable=False, autoincrement=False)
 283
 284 class PokemonFlavorText(TableBase):
 285     __tablename__ = 'pokemon_flavor_text'
 286     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 287     version_id = Column(Integer, ForeignKey('versions.id'), primary_key=True, nullable=False, autoincrement=False)
 288     flavor_text = Column(Unicode(255), nullable=False)
 289
 290 class PokemonFormGroup(TableBase):
 291     __tablename__ = 'pokemon_form_groups'
 292     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 293     description = Column(Unicode(512), nullable=False)
 294
 295 class PokemonFormSprite(TableBase):
 296     __tablename__ = 'pokemon_form_sprites'
 297     id = Column(Integer, primary_key=True, nullable=False)
 298     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 299     name = Column(Unicode(16), nullable=True)
 300
 301 class PokemonItem(TableBase):
 302     __tablename__ = 'pokemon_items'
 303     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 304     version_id = Column(Integer, ForeignKey('versions.id'), primary_key=True, nullable=False, autoincrement=False)
 305     item_id = Column(Integer, ForeignKey('items.id'), primary_key=True, nullable=False, autoincrement=False)
 306     rarity = Column(Integer, nullable=False)
 307
 308 class PokemonMove(TableBase):
 309     __tablename__ = 'pokemon_moves'
 310     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 311     version_group_id = Column(Integer, ForeignKey('version_groups.id'), primary_key=True, nullable=False, autoincrement=False)
 312     move_id = Column(Integer, ForeignKey('moves.id'), primary_key=True, nullable=False, autoincrement=False, index=True)
 313     pokemon_move_method_id = Column(Integer, ForeignKey('pokemon_move_methods.id'), primary_key=True, nullable=False, autoincrement=False)
 314     level = Column(Integer, primary_key=True, nullable=True, autoincrement=False)
 315     order = Column(Integer, nullable=True)
 316
 317 class PokemonMoveMethod(TableBase):
 318     __tablename__ = 'pokemon_move_methods'
 319     id = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 320     name = Column(Unicode(64), nullable=False)
 321     description = Column(Unicode(255), nullable=False)
 322
 323 class PokemonName(TableBase):
 324     __tablename__ = 'pokemon_names'
 325     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 326     language_id = Column(Integer, ForeignKey('languages.id'), primary_key=True, nullable=False, autoincrement=False)
 327     name = Column(Unicode(16), nullable=False)
 328
 329 class PokemonShape(TableBase):
 330     __tablename__ = 'pokemon_shapes'
 331     id = Column(Integer, primary_key=True, nullable=False)
 332     name = Column(Unicode(24), nullable=False)
 333     awesome_name = Column(Unicode(16), nullable=False)
 334
 335 class PokemonStat(TableBase):
 336     __tablename__ = 'pokemon_stats'
 337     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 338     stat_id = Column(Integer, ForeignKey('stats.id'), primary_key=True, nullable=False, autoincrement=False)
 339     base_stat = Column(Integer, nullable=False)
 340     effort = Column(Integer, nullable=False)
 341
 342 class PokemonType(TableBase):
 343     __tablename__ = 'pokemon_types'
 344     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 345     type_id = Column(Integer, ForeignKey('types.id'), nullable=False)
 346     slot = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 347
 348 class Stat(TableBase):
 349     __tablename__ = 'stats'
 350     id = Column(Integer, primary_key=True, nullable=False)
 351     name = Column(Unicode(16), nullable=False)
 352
 353 class TypeEfficacy(TableBase):
 354     __tablename__ = 'type_efficacy'
 355     damage_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 356     target_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 357     damage_factor = Column(Integer, nullable=False)
 358
 359 class Type(TableBase):
 360     __tablename__ = 'types'
 361     id = Column(Integer, primary_key=True, nullable=False)
 362     name = Column(Unicode(8), nullable=False)
 363     abbreviation = Column(Unicode(3), nullable=False)
 364
 365 class VersionGroup(TableBase):
 366     __tablename__ = 'version_groups'
 367     id = Column(Integer, primary_key=True, nullable=False)
 368     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 369
 370 class Version(TableBase):
 371     __tablename__ = 'versions'
 372     id = Column(Integer, primary_key=True, nullable=False)
 373     version_group_id = Column(Integer, ForeignKey('version_groups.id'), nullable=False)
 374     name = Column(Unicode(32), nullable=False)
 375
 376
 377 ### Relations down here, to avoid ordering problems
 378 Encounter.pokemon = relation(Pokemon, backref='encounters')
 379 Encounter.version = relation(Version, backref='encounters')
 380 Encounter.location_area = relation(LocationArea, backref='encounters')
 381 Encounter.slot = relation(EncounterTypeSlot, backref='encounters')
 382 Encounter.condition = relation(EncounterCondition, backref='encounters')
 383
 384 EncounterCondition.group = relation(EncounterConditionGroup,
 385                                     backref='conditions')
 386
 387 EncounterTypeSlot.type = relation(EncounterType, backref='slots')
 388
 389 EvolutionChain.growth_rate = relation(GrowthRate, backref='evolution_chains')
 390
 391 Generation.versions = relation(Version, secondary=VersionGroup.__table__)
 392
 393 LocationArea.location = relation(Location, backref='areas')
 394
 395 Machine.generation = relation(Generation)
 396
 397 Move.damage_class = relation(MoveDamageClass, backref='moves')
 398 Move.effect = relation(MoveEffect, backref='moves')
 399 Move.generation = relation(Generation, backref='moves')
 400 Move.machines = relation(Machine, backref='move')
 401 Move.target = relation(MoveTarget, backref='moves')
 402 Move.type = relation(Type, backref='moves')
 403
 404 Pokemon.abilities = relation(Ability, secondary=PokemonAbility.__table__,
 405                                       order_by=PokemonAbility.slot,
 406                                       backref='pokemon')
 407 Pokemon.formes = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.forme_base_pokemon_id,
 408                                                backref=backref('forme_base_pokemon',
 409                                                                remote_side=[Pokemon.id]))
 410 Pokemon.dex_numbers = relation(PokemonDexNumber, backref='pokemon')
 411 Pokemon.egg_groups = relation(EggGroup, secondary=PokemonEggGroup.__table__,
 412                                         order_by=PokemonEggGroup.egg_group_id,
 413                                         backref='pokemon')
 414 Pokemon.evolution_chain = relation(EvolutionChain, backref='pokemon')
 415 Pokemon.evolution_method = relation(EvolutionMethod)
 416 Pokemon.evolution_children = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.evolution_parent_pokemon_id,
 417                                                backref=backref('evolution_parent',
 418                                                                remote_side=[Pokemon.id]))
 419 Pokemon.flavor_text = relation(PokemonFlavorText, backref='pokemon')
 420 Pokemon.foreign_names = relation(PokemonName, backref='pokemon')
 421 Pokemon.items = relation(PokemonItem)
 422 Pokemon.generation = relation(Generation, backref='pokemon')
 423 Pokemon.shape = relation(PokemonShape, backref='pokemon')
 424 Pokemon.stats = relation(PokemonStat, backref='pokemon')
 425 Pokemon.types = relation(Type, secondary=PokemonType.__table__)
 426
 427 PokemonDexNumber.generation = relation(Generation)
 428
 429 PokemonFlavorText.version = relation(Version)
 430
 431 PokemonItem.item = relation(Item, backref='pokemon')
 432 PokemonItem.version = relation(Version)
 433
 434 PokemonFormGroup.pokemon = relation(Pokemon, backref=backref('form_group',
 435                                                              uselist=False))
 436 PokemonFormSprite.pokemon = relation(Pokemon, backref='form_sprites')
 437
 438 PokemonMove.pokemon = relation(Pokemon, backref='pokemon_moves')
 439 PokemonMove.version_group = relation(VersionGroup)
 440 PokemonMove.move = relation(Move, backref='pokemon_moves')
 441 PokemonMove.method = relation(PokemonMoveMethod)
 442
 443 PokemonName.language = relation(Language)
 444
 445 PokemonStat.stat = relation(Stat)
 446
 447 Type.damage_efficacies = relation(TypeEfficacy,
 448                                   primaryjoin=Type.id
 449                                       ==TypeEfficacy.damage_type_id,
 450                                   backref='damage_type')
 451 Type.target_efficacies = relation(TypeEfficacy,
 452                                   primaryjoin=Type.id
 453                                       ==TypeEfficacy.target_type_id,
 454                                   backref='target_type')
 455
 456 Version.version_group = relation(VersionGroup, backref='versions')
 457 Version.generation = association_proxy('version_group', 'generation')
 458
 459 VersionGroup.generation = relation(Generation, backref='version_groups')