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 MoveEffect(TableBase):
 167     __tablename__ = 'move_effects'
 168     id = Column(Integer, primary_key=True, nullable=False)
 169     priority = Column(SmallInteger, nullable=False)
 170     short_effect = Column(Unicode(128), nullable=False)
 171     effect = Column(Unicode(255), nullable=False)
 172
 173 class MoveTarget(TableBase):
 174     __tablename__ = 'move_targets'
 175     id = Column(Integer, primary_key=True, nullable=False)
 176     name = Column(Unicode(32), nullable=False)
 177     description = Column(Unicode(128), nullable=False)
 178
 179 class Move(TableBase):
 180     __tablename__ = 'moves'
 181     id = Column(Integer, primary_key=True, nullable=False)
 182     name = Column(Unicode(12), nullable=False)
 183     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 184     type_id = Column(Integer, ForeignKey('types.id'), nullable=False)
 185     power = Column(SmallInteger)
 186     pp = Column(SmallInteger, nullable=False)
 187     accuracy = Column(SmallInteger)
 188     target_id = Column(Integer, ForeignKey('move_targets.id'), nullable=False)
 189     category = Column(Unicode(8), nullable=False)
 190     effect_id = Column(Integer, ForeignKey('move_effects.id'), nullable=False)
 191     effect_chance = Column(Integer)
 192     contest_type = Column(Unicode(8), nullable=False)
 193     contest_effect_id = Column(Integer, ForeignKey('contest_effects.id'), nullable=True)
 194     super_contest_effect_id = Column(Integer, nullable=False)
 195
 196 class Pokemon(TableBase):
 197     """The core to this whole mess.
 198
 199     Note that I use both 'forme' and 'form' in both code and the database.  I
 200     only use 'forme' when specifically referring to Pokémon that have multiple
 201     distinct species as forms—i.e., different stats or movesets.  'Form' is a
 202     more general term referring to any variation within a species, including
 203     purely cosmetic forms like Unown.
 204     """
 205     __tablename__ = 'pokemon'
 206     id = Column(Integer, primary_key=True, nullable=False)
 207     name = Column(Unicode(20), nullable=False)
 208     forme_name = Column(Unicode(16))
 209     forme_base_pokemon_id = Column(Integer, ForeignKey('pokemon.id'))
 210     generation_id = Column(Integer, ForeignKey('generations.id'))
 211     evolution_chain_id = Column(Integer, ForeignKey('evolution_chains.id'))
 212     evolution_parent_pokemon_id = Column(Integer, ForeignKey('pokemon.id'))
 213     evolution_method_id = Column(Integer, ForeignKey('evolution_methods.id'))
 214     evolution_parameter = Column(Unicode(32))
 215     height = Column(Integer, nullable=False)
 216     weight = Column(Integer, nullable=False)
 217     species = Column(Unicode(16), nullable=False)
 218     color = Column(Unicode(6), nullable=False)
 219     pokemon_shape_id = Column(Integer, ForeignKey('pokemon_shapes.id'), nullable=False)
 220     habitat = Column(Unicode(16), nullable=False)
 221     gender_rate = Column(Integer, nullable=False)
 222     capture_rate = Column(Integer, nullable=False)
 223     base_experience = Column(Integer, nullable=False)
 224     base_happiness = Column(Integer, nullable=False)
 225     gen1_internal_id = Column(Integer)
 226     is_baby = Column(Boolean, nullable=False)
 227     has_gen4_fem_sprite = Column(Boolean, nullable=False)
 228     has_gen4_fem_back_sprite = Column(Boolean, nullable=False)
 229
 230     ### Stuff to handle alternate Pokémon forms
 231
 232     @property
 233     def national_id(self):
 234         """Returns the National Pokédex number for this Pokémon.  Use this
 235         instead of the id directly; alternate formes may make the id incorrect.
 236         """
 237
 238         if self.forme_base_pokemon_id:
 239             return self.forme_base_pokemon_id
 240         return self.id
 241
 242     @property
 243     def full_name(self):
 244         """Returns the name of this Pokémon, including its Forme, if any."""
 245
 246         if self.forme_name:
 247             return "%s %s" % (self.forme_name.capitalize(), self.name)
 248         return self.name
 249
 250     @property
 251     def normal_form(self):
 252         """Returns the normal form for this Pokémon; i.e., this will return
 253         regular Deoxys when called on any Deoxys form.
 254         """
 255
 256         if self.forme_base_pokemon:
 257             return self.forme_base_pokemon
 258
 259         return self
 260
 261 class PokemonAbility(TableBase):
 262     __tablename__ = 'pokemon_abilities'
 263     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 264     ability_id = Column(Integer, ForeignKey('abilities.id'), nullable=False)
 265     slot = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 266
 267 class PokemonDexNumber(TableBase):
 268     __tablename__ = 'pokemon_dex_numbers'
 269     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 270     generation_id = Column(Integer, ForeignKey('generations.id'), primary_key=True, nullable=False, autoincrement=False)
 271     pokedex_number = Column(Integer, nullable=False)
 272
 273 class PokemonEggGroup(TableBase):
 274     __tablename__ = 'pokemon_egg_groups'
 275     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 276     egg_group_id = Column(Integer, ForeignKey('egg_groups.id'), primary_key=True, nullable=False, autoincrement=False)
 277
 278 class PokemonFlavorText(TableBase):
 279     __tablename__ = 'pokemon_flavor_text'
 280     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 281     version_id = Column(Integer, ForeignKey('versions.id'), primary_key=True, nullable=False, autoincrement=False)
 282     flavor_text = Column(Unicode(255), nullable=False)
 283
 284 class PokemonFormGroup(TableBase):
 285     __tablename__ = 'pokemon_form_groups'
 286     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 287     description = Column(Unicode(512), nullable=False)
 288
 289 class PokemonFormSprite(TableBase):
 290     __tablename__ = 'pokemon_form_sprites'
 291     id = Column(Integer, primary_key=True, nullable=False)
 292     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 293     name = Column(Unicode(16), nullable=True)
 294
 295 class PokemonItem(TableBase):
 296     __tablename__ = 'pokemon_items'
 297     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 298     version_id = Column(Integer, ForeignKey('versions.id'), primary_key=True, nullable=False, autoincrement=False)
 299     item_id = Column(Integer, ForeignKey('items.id'), primary_key=True, nullable=False, autoincrement=False)
 300     rarity = Column(Integer, nullable=False)
 301
 302 class PokemonMove(TableBase):
 303     __tablename__ = 'pokemon_moves'
 304     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 305     version_group_id = Column(Integer, ForeignKey('version_groups.id'), primary_key=True, nullable=False, autoincrement=False)
 306     move_id = Column(Integer, ForeignKey('moves.id'), primary_key=True, nullable=False, autoincrement=False, index=True)
 307     pokemon_move_method_id = Column(Integer, ForeignKey('pokemon_move_methods.id'), primary_key=True, nullable=False, autoincrement=False)
 308     level = Column(Integer, primary_key=True, nullable=True, autoincrement=False)
 309     order = Column(Integer, nullable=True)
 310
 311 class PokemonMoveMethod(TableBase):
 312     __tablename__ = 'pokemon_move_methods'
 313     id = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 314     name = Column(Unicode(64), nullable=False)
 315     description = Column(Unicode(255), nullable=False)
 316
 317 class PokemonName(TableBase):
 318     __tablename__ = 'pokemon_names'
 319     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 320     language_id = Column(Integer, ForeignKey('languages.id'), primary_key=True, nullable=False, autoincrement=False)
 321     name = Column(Unicode(16), nullable=False)
 322
 323 class PokemonShape(TableBase):
 324     __tablename__ = 'pokemon_shapes'
 325     id = Column(Integer, primary_key=True, nullable=False)
 326     name = Column(Unicode(24), nullable=False)
 327     awesome_name = Column(Unicode(16), nullable=False)
 328
 329 class PokemonStat(TableBase):
 330     __tablename__ = 'pokemon_stats'
 331     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 332     stat_id = Column(Integer, ForeignKey('stats.id'), primary_key=True, nullable=False, autoincrement=False)
 333     base_stat = Column(Integer, nullable=False)
 334     effort = Column(Integer, nullable=False)
 335
 336 class PokemonType(TableBase):
 337     __tablename__ = 'pokemon_types'
 338     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 339     type_id = Column(Integer, ForeignKey('types.id'), nullable=False)
 340     slot = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 341
 342 class Stat(TableBase):
 343     __tablename__ = 'stats'
 344     id = Column(Integer, primary_key=True, nullable=False)
 345     name = Column(Unicode(16), nullable=False)
 346
 347 class TypeEfficacy(TableBase):
 348     __tablename__ = 'type_efficacy'
 349     damage_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 350     target_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 351     damage_factor = Column(Integer, nullable=False)
 352
 353 class Type(TableBase):
 354     __tablename__ = 'types'
 355     id = Column(Integer, primary_key=True, nullable=False)
 356     name = Column(Unicode(8), nullable=False)
 357     abbreviation = Column(Unicode(3), nullable=False)
 358
 359 class VersionGroup(TableBase):
 360     __tablename__ = 'version_groups'
 361     id = Column(Integer, primary_key=True, nullable=False)
 362     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 363
 364 class Version(TableBase):
 365     __tablename__ = 'versions'
 366     id = Column(Integer, primary_key=True, nullable=False)
 367     version_group_id = Column(Integer, ForeignKey('version_groups.id'), nullable=False)
 368     name = Column(Unicode(32), nullable=False)
 369
 370     generation = association_proxy('version_group', 'generation')
 371
 372
 373 ### Relations down here, to avoid ordering problems
 374 Encounter.pokemon = relation(Pokemon, backref='encounters')
 375 Encounter.version = relation(Version, backref='encounters')
 376 Encounter.location_area = relation(LocationArea, backref='encounters')
 377 Encounter.slot = relation(EncounterTypeSlot, backref='encounters')
 378 Encounter.condition = relation(EncounterCondition, backref='encounters')
 379
 380 EncounterCondition.group = relation(EncounterConditionGroup,
 381                                     backref='conditions')
 382
 383 EncounterTypeSlot.type = relation(EncounterType, backref='slots')
 384
 385 EvolutionChain.growth_rate = relation(GrowthRate, backref='evolution_chains')
 386
 387 Generation.versions = relation(Version, secondary=VersionGroup.__table__)
 388
 389 LocationArea.location = relation(Location, backref='areas')
 390
 391 Machine.generation = relation(Generation)
 392
 393 Move.type = relation(Type, backref='moves')
 394 Move.generation = relation(Generation, backref='moves')
 395 Move.target = relation(MoveTarget, backref='moves')
 396 Move.effect = relation(MoveEffect, backref='moves')
 397 Move.machines = relation(Machine, backref='move')
 398
 399 Pokemon.abilities = relation(Ability, secondary=PokemonAbility.__table__,
 400                                       order_by=PokemonAbility.slot,
 401                                       backref='pokemon')
 402 Pokemon.formes = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.forme_base_pokemon_id,
 403                                                backref=backref('forme_base_pokemon',
 404                                                                remote_side=[Pokemon.id]))
 405 Pokemon.dex_numbers = relation(PokemonDexNumber, backref='pokemon')
 406 Pokemon.egg_groups = relation(EggGroup, secondary=PokemonEggGroup.__table__,
 407                                         order_by=PokemonEggGroup.egg_group_id,
 408                                         backref='pokemon')
 409 Pokemon.evolution_chain = relation(EvolutionChain, backref='pokemon')
 410 Pokemon.evolution_method = relation(EvolutionMethod)
 411 Pokemon.evolution_children = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.evolution_parent_pokemon_id,
 412                                                backref=backref('evolution_parent',
 413                                                                remote_side=[Pokemon.id]))
 414 Pokemon.flavor_text = relation(PokemonFlavorText, backref='pokemon')
 415 Pokemon.foreign_names = relation(PokemonName, backref='pokemon')
 416 Pokemon.items = relation(PokemonItem)
 417 Pokemon.generation = relation(Generation, backref='pokemon')
 418 Pokemon.shape = relation(PokemonShape, backref='pokemon')
 419 Pokemon.stats = relation(PokemonStat, backref='pokemon')
 420 Pokemon.types = relation(Type, secondary=PokemonType.__table__)
 421
 422 PokemonDexNumber.generation = relation(Generation)
 423
 424 PokemonFlavorText.version = relation(Version)
 425
 426 PokemonItem.item = relation(Item, backref='pokemon')
 427 PokemonItem.version = relation(Version)
 428
 429 PokemonFormGroup.pokemon = relation(Pokemon, backref=backref('form_group',
 430                                                              uselist=False))
 431 PokemonFormSprite.pokemon = relation(Pokemon, backref='form_sprites')
 432
 433 PokemonMove.pokemon = relation(Pokemon, backref='pokemon_moves')
 434 PokemonMove.version_group = relation(VersionGroup)
 435 PokemonMove.move = relation(Move, backref='pokemon_moves')
 436 PokemonMove.method = relation(PokemonMoveMethod)
 437
 438 PokemonName.language = relation(Language)
 439
 440 PokemonStat.stat = relation(Stat)
 441
 442 Type.damage_efficacies = relation(TypeEfficacy,
 443                                   primaryjoin=Type.id
 444                                       ==TypeEfficacy.damage_type_id,
 445                                   backref='damage_type')
 446 Type.target_efficacies = relation(TypeEfficacy,
 447                                   primaryjoin=Type.id
 448                                       ==TypeEfficacy.target_type_id,
 449                                   backref='target_type')
 450
 451 Version.version_group = relation(VersionGroup, backref='versions')
 452
 453 VersionGroup.generation = relation(Generation, backref='version_groups')