search

veekun / pokedex

more than you ever wanted to know about Pokémon
MIT License
1.44k stars 637 forks source link

Versioning Type Efficacy and Pokémon Types #213

Open GCSBOSS opened 7 years ago

GCSBOSS commented 7 years ago

Summary

In this branch I have added generation versioning to the TypeEfficacy table and to the PokemonTypes table. I have also included the versioned (previously missing) data to those tables.

Type Efficacy

The following SQL would change the schema and add the data for TypeEfficacy:

-- Changes the schema for TypeEfficacy
delete from type_efficacy where damage_factor = '100';
alter table type_efficacy add column first_generation_id varchar default 1;
alter table type_efficacy add column last_generation_id varchar default null;
alter table type_efficacy rename column damage_factor to damage_modifier;

-- Add versioning info to types introduced in generation 2.
update type_efficacy set first_generation_id = '2' where damage_type_id in ('9', '17') or target_type_id in ('9', '17');

-- Add versioning info to types introduced in generation 6.
update type_efficacy set first_generation_id = '6' where damage_type_id = '18' or target_type_id = '18';

-- Add data refering to other type changes in generation 2.
update type_efficacy set first_generation_id = '2' where damage_type_id = '7' and target_type_id = '4';
insert into type_efficacy values (default, '7', '4', '200', '1', '1');

insert into type_efficacy values (default, '4', '7', '200', '1', '1');

update type_efficacy set first_generation_id = '2' where damage_type_id = '8' and target_type_id = '14';
insert into type_efficacy values (default, '8', '14', '0', '1', '1');

update type_efficacy set first_generation_id = '2' where damage_type_id = '15' and target_type_id = '10';

-- Add data refering to other type changes in generation 6.
insert into type_efficacy values (default, '8', '9', '50', '2', '5');
insert into type_efficacy values (default, '17', '9', '50', '2', '5');

The new TypeEfficacy schema treats each row as a damage modifier instead of the former damage factor. Being a modifier means that when no row is present for a type match up, no modification applies to the damage (former 100%). This change reduces the amount of rows and allow easy versioning (without duplication for every change).

This data was handcrafted from info found here and applied on top of the csv data in branch master .

Pokemon Types

The following SQL would change the schema and add the data for PokemonTypes versioning:

-- Add the versioning columns to the schema.
alter table pokemon_types add column first_generation_id int default '1';
alter table pokemon_types add column last_generation_id int default null;

-- Update all pokemons from each generation.
update pokemon_types
set    first_generation_id = 2
where  pokemon_id in (select pm.id
                       from pokemon pm
                       inner join pokemon_species ps
                             on   ps.id = pm.species_id
                       where ps.generation_id = '2');
update pokemon_types
set    first_generation_id = 3
where  pokemon_id in (select pm.id
                       from pokemon pm
                       inner join pokemon_species ps
                             on   ps.id = pm.species_id
                       where ps.generation_id = '3');
update pokemon_types
set    first_generation_id = 4
where  pokemon_id in (select pm.id
                       from pokemon pm
                       inner join pokemon_species ps
                             on   ps.id = pm.species_id
                       where ps.generation_id = '4');
update pokemon_types
set    first_generation_id = 5
where  pokemon_id in (select pm.id
                       from pokemon pm
                       inner join pokemon_species ps
                             on   ps.id = pm.species_id
                       where ps.generation_id = '5');
update pokemon_types
set    first_generation_id = 6
where  pokemon_id in (select pm.id
                       from pokemon pm
                       inner join pokemon_species ps
                             on   ps.id = pm.species_id
                       where ps.generation_id = '6');
update pokemon_types
set    first_generation_id = 7
where  pokemon_id in (select pm.id
                       from pokemon pm
                       inner join pokemon_species ps
                             on   ps.id = pm.species_id
                       where ps.generation_id = '7');

-- Azumarill.
update pokemon_types set first_generation_id = 6 where pokemon_id = '184' and slot = '2';

-- Azurill
update pokemon_types set first_generation_id = 6 where pokemon_id = '298' and slot = '2';

-- Clefable
update pokemon_types set first_generation_id = 6 where pokemon_id = '36' and slot = '1';
insert into pokemon_types values (default, '36', '1', '1', 1, 5);

-- Clefairy
update pokemon_types set first_generation_id = 6 where pokemon_id = '35' and slot = '1';
insert into pokemon_types values (default, '35', '1', '1', 1, 5);

-- Cleffa
update pokemon_types set first_generation_id = 6 where pokemon_id = '173' and slot = '1';
insert into pokemon_types values (default, '173', '1', '1', 2, 5);

-- Cottonee
update pokemon_types set first_generation_id = 6 where pokemon_id = '546' and slot = '2';

-- Gardevoir
update pokemon_types set first_generation_id = 6 where pokemon_id = '282' and slot = '2';

-- Granbull
update pokemon_types set first_generation_id = 6 where pokemon_id = '210' and slot = '1';
insert into pokemon_types values (default, '210', '1', '1', 2, 5);

-- Igglybuff
update pokemon_types set first_generation_id = 6 where pokemon_id = '174' and slot = '2';

-- Jigglypuff
update pokemon_types set first_generation_id = 6 where pokemon_id = '39' and slot = '2';

-- Kirlia
update pokemon_types set first_generation_id = 6 where pokemon_id = '281' and slot = '2';

-- Magnemite
update pokemon_types set first_generation_id = 2 where pokemon_id = '81' and slot = '2';

-- Magneton
update pokemon_types set first_generation_id = 2 where pokemon_id = '82' and slot = '2';

-- Marill
update pokemon_types set first_generation_id = 6 where pokemon_id = '183' and slot = '2';

-- Mawile
update pokemon_types set first_generation_id = 6 where pokemon_id = '303' and slot = '2';

-- Mime Jr.
update pokemon_types set first_generation_id = 6 where pokemon_id = '439' and slot = '2';

-- Mr. Mime
update pokemon_types set first_generation_id = 6 where pokemon_id = '122' and slot = '2';

-- Ralts
update pokemon_types set first_generation_id = 6 where pokemon_id = '280' and slot = '2';

-- Snubbul
update pokemon_types set first_generation_id = 6 where pokemon_id = '209' and slot = '1';
insert into pokemon_types values (default, '209', '1', '1', 2, 5);

-- Togekiss
update pokemon_types set first_generation_id = 6 where pokemon_id = '468' and slot = '1';
insert into pokemon_types values (default, '468', '1', '1', 4, 5);

-- Togepi
update pokemon_types set first_generation_id = 6 where pokemon_id = '175' and slot = '1';
insert into pokemon_types values (default, '175', '1', '1', 2, 5);

-- Togetic
update pokemon_types set first_generation_id = 6 where pokemon_id = '176' and slot = '1';
insert into pokemon_types values (default, '176', '1', '1', 2, 5);

-- Whimsicott
update pokemon_types set first_generation_id = 6 where pokemon_id = '547' and slot = '2';

-- Whigglytuff
update pokemon_types set first_generation_id = 6 where pokemon_id = '40' and slot = '2';

This data was handcrafted from info found around here and applied on top of the csv data in branch master.

Considerations

I am no Python guy and don't really know what else must be changed (classes and documentation probably) in order to merge these changes, so I would appreciate if someone could tell me.