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