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