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