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