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