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