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