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 PokemonName(TableBase):
 294     __tablename__ = 'pokemon_names'
 295     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 296     language_id = Column(Integer, ForeignKey('languages.id'), primary_key=True, nullable=False, autoincrement=False)
 297     name = Column(Unicode(16), nullable=False)
 298
 299 class PokemonShape(TableBase):
 300     __tablename__ = 'pokemon_shapes'
 301     id = Column(Integer, primary_key=True, nullable=False)
 302     name = Column(Unicode(24), nullable=False)
 303     awesome_name = Column(Unicode(16), nullable=False)
 304
 305 class PokemonStat(TableBase):
 306     __tablename__ = 'pokemon_stats'
 307     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 308     stat_id = Column(Integer, ForeignKey('stats.id'), primary_key=True, nullable=False, autoincrement=False)
 309     base_stat = Column(Integer, nullable=False)
 310     effort = Column(Integer, nullable=False)
 311
 312 class PokemonType(TableBase):
 313     __tablename__ = 'pokemon_types'
 314     pokemon_id = Column(Integer, ForeignKey('pokemon.id'), primary_key=True, nullable=False, autoincrement=False)
 315     type_id = Column(Integer, ForeignKey('types.id'), nullable=False)
 316     slot = Column(Integer, primary_key=True, nullable=False, autoincrement=False)
 317
 318 class Stat(TableBase):
 319     __tablename__ = 'stats'
 320     id = Column(Integer, primary_key=True, nullable=False)
 321     name = Column(Unicode(16), nullable=False)
 322
 323 class TypeEfficacy(TableBase):
 324     __tablename__ = 'type_efficacy'
 325     damage_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 326     target_type_id = Column(Integer, ForeignKey('types.id'), primary_key=True, nullable=False, autoincrement=False)
 327     damage_factor = Column(Integer, nullable=False)
 328
 329 class Type(TableBase):
 330     __tablename__ = 'types'
 331     id = Column(Integer, primary_key=True, nullable=False)
 332     name = Column(Unicode(8), nullable=False)
 333     abbreviation = Column(Unicode(3), nullable=False)
 334
 335 class VersionGroup(TableBase):
 336     __tablename__ = 'version_groups'
 337     id = Column(Integer, primary_key=True, nullable=False)
 338     generation_id = Column(Integer, ForeignKey('generations.id'), nullable=False)
 339
 340 class Version(TableBase):
 341     __tablename__ = 'versions'
 342     id = Column(Integer, primary_key=True, nullable=False)
 343     version_group_id = Column(Integer, ForeignKey('version_groups.id'), nullable=False)
 344     name = Column(Unicode(32), nullable=False)
 345
 346
 347 ### Relations down here, to avoid ordering problems
 348 Encounter.pokemon = relation(Pokemon, backref='encounters')
 349 Encounter.version = relation(Version, backref='encounters')
 350 Encounter.location_area = relation(LocationArea, backref='encounters')
 351 Encounter.slot = relation(EncounterTypeSlot, backref='encounters')
 352 Encounter.condition = relation(EncounterCondition, backref='encounters')
 353
 354 EncounterCondition.group = relation(EncounterConditionGroup,
 355                                     backref='conditions')
 356
 357 EncounterTypeSlot.type = relation(EncounterType, backref='slots')
 358
 359 EvolutionChain.growth_rate = relation(GrowthRate, backref='evolution_chains')
 360
 361 LocationArea.location = relation(Location, backref='areas')
 362
 363 Pokemon.abilities = relation(Ability, secondary=PokemonAbility.__table__,
 364                                       order_by=PokemonAbility.slot,
 365                                       backref='pokemon')
 366 Pokemon.formes = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.forme_base_pokemon_id,
 367                                                backref=backref('forme_base_pokemon',
 368                                                                remote_side=[Pokemon.id]))
 369 Pokemon.dex_numbers = relation(PokemonDexNumber, backref='pokemon')
 370 Pokemon.egg_groups = relation(EggGroup, secondary=PokemonEggGroup.__table__,
 371                                         order_by=PokemonEggGroup.egg_group_id,
 372                                         backref='pokemon')
 373 Pokemon.evolution_chain = relation(EvolutionChain, backref='pokemon')
 374 Pokemon.evolution_method = relation(EvolutionMethod)
 375 Pokemon.evolution_children = relation(Pokemon, primaryjoin=Pokemon.id==Pokemon.evolution_parent_pokemon_id,
 376                                                backref=backref('evolution_parent',
 377                                                                remote_side=[Pokemon.id]))
 378 Pokemon.flavor_text = relation(PokemonFlavorText, backref='pokemon')
 379 Pokemon.foreign_names = relation(PokemonName, backref='pokemon')
 380 Pokemon.items = relation(PokemonItem)
 381 Pokemon.generation = relation(Generation, backref='pokemon')
 382 Pokemon.shape = relation(PokemonShape, backref='pokemon')
 383 Pokemon.stats = relation(PokemonStat, backref='pokemon')
 384 Pokemon.types = relation(Type, secondary=PokemonType.__table__)
 385
 386 PokemonDexNumber.generation = relation(Generation)
 387
 388 PokemonFlavorText.version = relation(Version)
 389
 390 PokemonItem.item = relation(Item, backref='pokemon')
 391 PokemonItem.version = relation(Version)
 392
 393 PokemonFormGroup.pokemon = relation(Pokemon, backref=backref('form_group',
 394                                                              uselist=False))
 395 PokemonFormSprite.pokemon = relation(Pokemon, backref='form_sprites')
 396
 397 PokemonName.language = relation(Language)
 398
 399 PokemonStat.stat = relation(Stat)
 400
 401 Type.damage_efficacies = relation(TypeEfficacy,
 402                                   primaryjoin=Type.id
 403                                       ==TypeEfficacy.damage_type_id,
 404                                   backref='damage_type')
 405 Type.target_efficacies = relation(TypeEfficacy,
 406                                   primaryjoin=Type.id
 407                                       ==TypeEfficacy.target_type_id,
 408                                   backref='target_type')
 409
 410 Version.generation = relation(Generation, secondary=VersionGroup.__table__,
 411                               backref='versions')