Closed penelopeysm closed 11 months ago
apribot=# SELECT id FROM pokemon WHERE name = 'Dratini';
id
-----
198
(1 row)
apribot=# SELECT id FROM pokemon WHERE name = 'Dragonair';
id
-----
199
(1 row)
apribot=# SELECT id FROM pokemon WHERE name = 'Dragonite';
id
-----
200
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT 199 AS pokemon_id, game_id, move_id, learn_method_id, level
FROM learnsets
WHERE pokemon_id = 198 AND game_id = 2 AND learn_method_id = 4;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT 200 AS pokemon_id, game_id, move_id, learn_method_id, level
FROM learnsets
WHERE pokemon_id = 198 AND game_id = 2 AND learn_method_id = 4;
UPDATE pokemon SET ha_id = (SELECT "id" FROM abilities WHERE name = 'Competitive')
WHERE name IN ('Piplup', 'Prinplup', 'Empoleon');
followed by an update to the bot's code
The only difference is in the level-up moves. Because the moves themselves are all the same and only the levels are different, we can use UPDATE
, instead of deleting and re-adding:
UPDATE learnsets
SET level = input.level
FROM (SELECT m.id, ls.level FROM (VALUES
('Tackle', 1), ('Tail Whip', 1), ('Disarming Voice', 3),
('Echoed Voice', 6), ('Mud Shot', 9), ('Covet', 12),
('Dig', 15), ('Headbutt', 17), ('Yawn', 23),
('Take Down', 28), ('Work Up', 30), ('Uproar', 34),
('Double-Edge', 39), ('Earth Power', 45), ('Belch', 51))
AS ls (move_name, level)
JOIN moves as m on ls.move_name = m.name)
AS input (move_id, level)
WHERE learnsets.move_id = input.move_id
AND learnsets.pokemon_id = (SELECT id FROM pokemon as p WHERE p.name = 'Oinkologne' AND p.form = 'Female')
AND learnsets.game_id = (SELECT id FROM games as g where g.name = 'SV')
AND learnsets.learn_method_id = (SELECT id FROM learn_methods as lm where lm.name = 'Level up');
Remove existing learnsets:
DELETE FROM learnsets
WHERE pokemon_id IN (SELECT "id" FROM pokemon WHERE form = 'Bloodmoon')
Put the right one in (I manually transcribed this from Bulbapedia):
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT p.id as pokemon_id, g.id as game_id, m.id as move_id, lm.id as learn_method_id, input.level
FROM moves as m
JOIN (VALUES
('Headlong Rush', 'Level up', 1), ('Scratch', 'Level up', 1),
('Leer', 'Level up', 1), ('Lick', 'Level up', 1),
('Moonlight', 'Reminder', NULL),
('Fury Swipes', 'Level up', 8), ('Payback', 'Level up', 13),
('Harden', 'Level up', 17), ('Slash', 'Level up', 22),
('Play Nice', 'Level up', 25), ('Scary Face', 'Level up', 35),
('Rest', 'Level up', 41), ('Snore', 'Level up', 41),
('Earth Power', 'Level up', 48), ('Moonblast', 'Level up', 56),
('Hammer Arm', 'Level up', 64), ('Blood Moon', 'Level up', 70),
('Take Down', 'TM', NULL), ('Scary Face', 'TM', NULL),
('Protect', 'TM', NULL), ('Low Kick', 'TM', NULL),
('Thief', 'TM', NULL), ('Trailblaze', 'TM', NULL),
('Facade', 'TM', NULL), ('Bulldoze', 'TM', NULL),
('Snarl', 'TM', NULL), ('Metal Claw', 'TM', NULL),
('Swift', 'TM', NULL), ('Mud Shot', 'TM', NULL),
('Rock Tomb', 'TM', NULL), ('Fling', 'TM', NULL),
('Avalanche', 'TM', NULL), ('Endure', 'TM', NULL),
('Sunny Day', 'TM', NULL), ('Rain Dance', 'TM', NULL),
('Dig', 'TM', NULL), ('Brick Break', 'TM', NULL),
('Shadow Claw', 'TM', NULL), ('Body Slam', 'TM', NULL),
('Fire Punch', 'TM', NULL), ('Thunder Punch', 'TM', NULL),
('Ice Punch', 'TM', NULL), ('Sleep Talk', 'TM', NULL),
('Seed Bomb', 'TM', NULL), ('Stomping Tantrum', 'TM', NULL),
('Rest', 'TM', NULL), ('Rock Slide', 'TM', NULL),
('Taunt', 'TM', NULL), ('Swords Dance', 'TM', NULL),
('Body Press', 'TM', NULL), ('Gunk Shot', 'TM', NULL),
('Substitute', 'TM', NULL), ('Crunch', 'TM', NULL),
('Hyper Voice', 'TM', NULL), ('Heavy Slam', 'TM', NULL),
('Calm Mind', 'TM', NULL), ('Helping Hand', 'TM', NULL),
('Earth Power', 'TM', NULL), ('Earthquake', 'TM', NULL),
('Stone Edge', 'TM', NULL), ('Giga Impact', 'TM', NULL),
('Focus Blast', 'TM', NULL), ('Hyper Beam', 'TM', NULL),
('Tera Blast', 'TM', NULL), ('Roar', 'TM', NULL),
('Smack Down', 'TM', NULL), ('Vacuum Wave', 'TM', NULL),
('High Horsepower', 'TM', NULL), ('Uproar', 'TM', NULL),
('Focus Punch', 'TM', NULL)
) AS input(name, learn_method, level)
ON input.name = m.name
JOIN learn_methods as lm ON input.learn_method = lm.name
JOIN pokemon as p ON p.name = 'Ursaluna' AND p.form = 'Bloodmoon'
JOIN games as g ON g.name = 'SV'
ORDER BY learn_method_id ASC, level ASC;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT 216 AS pokemon_id, game_id, move_id, learn_method_id, level
FROM learnsets
WHERE pokemon_id = 215 AND game_id = 4 AND learn_method_id = 4;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT 47 AS pokemon_id, game_id, move_id, learn_method_id, level
FROM learnsets
WHERE pokemon_id = 227 AND game_id = 4 AND learn_method_id = 4;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT 48 AS pokemon_id, game_id, move_id, learn_method_id, level
FROM learnsets
WHERE pokemon_id = 227 AND game_id = 4 AND learn_method_id = 4;
UPDATE pokemon SET unique_name = 'nidoran-female' WHERE id = 41;
UPDATE pokemon SET unique_name = 'nidoran-male' WHERE id = 44;
UPDATE pokemon SET unique_name = 'flabebe' WHERE id = 804;
UPDATE pokemon SET unique_name = 'oricorio-pau' WHERE id = 894;
UPDATE pokemon SET unique_name = 'type-null' WHERE id = 930;
UPDATE pokemon SET unique_name = 'zygarde-50' WHERE id = 864;
UPDATE pokemon SET unique_name = 'zygarde-10' WHERE id = 865;
DELETE FROM learnsets WHERE pokemon_id IN (SELECT id FROM pokemon WHERE unique_name = 'minior-core');
DELETE FROM pokemon WHERE unique_name = 'minior-core';
UPDATE pokemon SET form = NULL, unique_name = 'minior' WHERE id = 932;
DELETE FROM learnsets WHERE pokemon_id IN (SELECT id FROM pokemon WHERE unique_name = 'eiscue-noice');
DELETE FROM pokemon WHERE unique_name = 'eiscue-noice';
UPDATE pokemon SET form = NULL, unique_name = 'eiscue' WHERE id = 1038;
DELETE FROM learnsets WHERE pokemon_id IN (SELECT id FROM pokemon WHERE unique_name = 'morpeko-hangry');
DELETE FROM pokemon WHERE unique_name = 'morpeko-hangry';
UPDATE pokemon SET form = NULL, unique_name = 'morpeko' WHERE id = 1042;
DELETE FROM learnsets WHERE pokemon_id IN (SELECT id FROM pokemon WHERE unique_name = 'wishiwashi-school');
DELETE FROM pokemon WHERE unique_name = 'wishiwashi-school';
UPDATE pokemon SET form = NULL, unique_name = 'wishiwashi' WHERE id = 903;
DELETE FROM learnsets WHERE pokemon_id IN (SELECT id FROM pokemon WHERE unique_name = 'aegislash-blade');
DELETE FROM pokemon WHERE unique_name = 'aegislash-blade';
UPDATE pokemon SET form = NULL, unique_name = 'aegislash' WHERE id = 817;
DELETE FROM learnsets WHERE pokemon_id IN (171, 172, 173) AND game_id != 4;
# select MAX(evolution_family_id) from pokemon;
max
-----
339
(1 row)
UPDATE pokemon SET evolution_family_id = 340 WHERE evolution_family_id = 43 AND form LIKE '%Hisui%';
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT (SELECT id FROM pokemon WHERE unique_name = 'grimer-alola') as pokemon_id,
(SELECT id FROM games WHERE name = 'SV') as game_id,
m.id as move_id,
(SELECT id FROM learn_methods WHERE name = 'Egg') as learn_method_id,
NULL as level
FROM moves as m
JOIN (SELECT name FROM (VALUES ('Curse'),
('Mean Look'),
('Stockpile'),
('Spit Up'),
('Swallow'),
('Shadow Sneak'),
('Spite'),
('Recycle'),
('Assurance'),
('Clear Smog')) as gg(name) ORDER BY name) AS grimer_ems(name)
ON m.name = grimer_ems.name;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT (SELECT id FROM pokemon WHERE unique_name = 'cacnea') as pokemon_id,
(SELECT id FROM games WHERE name = 'SV') as game_id,
m.id as move_id,
(SELECT id FROM learn_methods WHERE name = 'Egg') as learn_method_id,
NULL as level
FROM moves as m
JOIN (SELECT name FROM (VALUES ('Disable'), ('Acid'), ('Counter'),
('Teeter Dance'), ('Block'), ('Switcheroo'),
('Belch'), ('Fell Stinger')) as em(name) ORDER BY name)
AS new_ems(name)
ON m.name = new_ems.name;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT (SELECT id FROM pokemon WHERE unique_name = 'luvdisc') as pokemon_id,
(SELECT id FROM games WHERE name = 'SV') as game_id,
m.id as move_id,
(SELECT id FROM learn_methods WHERE name = 'Egg') as learn_method_id,
NULL as level
FROM moves as m
JOIN (SELECT name
FROM (VALUES ('Supersonic'), ('Splash'), ('Aqua Jet'), ('Entrainment'))
AS em(name) ORDER BY name)
AS new_ems(name)
ON m.name = new_ems.name;
Low-priority because none of Apribot's commands need TM moves.
(Okay, maybe some egg moves are now available via TM.)
PokemonDB doesn't list the learnsets of these regional forms so we have to manually delete the existing data (which refer to Kanto Tauros) and add them. sigh
DELETE FROM learnsets
WHERE pokemon_id IN (SELECT id FROM pokemon WHERE unique_name in
('tauros-combat', 'tauros-blaze', 'tauros-aqua'))
AND game_id IN (SELECT id FROM games WHERE name = 'SV');
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT p.id as pokemon_id, g.id as game_id, m.id as move_id, lm.id as learn_method_id, input.level
FROM moves as m
JOIN (VALUES
('Tackle', 'Level up', 1),
('Tail Whip', 'Level up', 1),
('Work Up', 'Level up', 5),
('Double Kick', 'Level up', 10),
('Assurance', 'Level up', 15),
('Headbutt', 'Level up', 20),
('Scary Face', 'Level up', 25),
('Zen Headbutt', 'Level up', 30),
('Raging Bull', 'Level up', 35),
('Rest', 'Level up', 40),
('Swagger', 'Level up', 45),
('Thrash', 'Level up', 50),
('Double-Edge', 'Level up', 55),
('Close Combat', 'Level up', 60),
('Take Down', 'TM', NULL),
('Scary Face', 'TM', NULL),
('Protect', 'TM', NULL),
('Thief', 'TM', NULL),
('Trailblaze', 'TM', NULL),
('Facade', 'TM', NULL),
('Bulldoze', 'TM', NULL),
('Rock Tomb', 'TM', NULL),
('Endure', 'TM', NULL),
('Sunny Day', 'TM', NULL),
('Rain Dance', 'TM', NULL),
('Sandstorm', 'TM', NULL),
('Smart Strike', 'TM', NULL),
('Dig', 'TM', NULL),
('Zen Headbutt', 'TM', NULL),
('Bulk Up', 'TM', NULL),
('Body Slam', 'TM', NULL),
('Sleep Talk', 'TM', NULL),
('Stomping Tantrum', 'TM', NULL),
('Rest', 'TM', NULL),
('Rock Slide', 'TM', NULL),
('Body Press', 'TM', NULL),
('Iron Head', 'TM', NULL),
('Substitute', 'TM', NULL),
('Drill Run', 'TM', NULL),
('Surf', 'TM', NULL),
('Reversal', 'TM', NULL),
('Wild Charge', 'TM', NULL),
('Earthquake', 'TM', NULL),
('Stone Edge', 'TM', NULL),
('Giga Impact', 'TM', NULL),
('Outrage', 'TM', NULL),
('Hyper Beam', 'TM', NULL),
('Close Combat', 'TM', NULL),
('Tera Blast', 'TM', NULL),
('High Horsepower', 'TM', NULL),
('Lash Out', 'TM', NULL),
('Curse', 'Egg', NULL),
('Endeavor', 'Egg', NULL)
) AS input(name, learn_method, level)
ON input.name = m.name
JOIN learn_methods as lm ON input.learn_method = lm.name
JOIN pokemon as p ON p.unique_name = 'tauros-combat'
JOIN games as g ON g.name = 'SV'
ORDER BY learn_method_id ASC, level ASC, input.name ASC;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT p.id as pokemon_id, g.id as game_id, m.id as move_id, lm.id as learn_method_id, input.level
FROM moves as m
JOIN (VALUES
('Tackle', 'Level up', 1),
('Tail Whip', 'Level up', 1),
('Work Up', 'Level up', 5),
('Double Kick', 'Level up', 10),
('Flame Charge', 'Level up', 15),
('Headbutt', 'Level up', 20),
('Scary Face', 'Level up', 25),
('Zen Headbutt', 'Level up', 30),
('Raging Bull', 'Level up', 35),
('Rest', 'Level up', 40),
('Swagger', 'Level up', 45),
('Thrash', 'Level up', 50),
('Flare Blitz', 'Level up', 55),
('Close Combat', 'Level up', 60),
('Take Down', 'TM', NULL),
('Scary Face', 'TM', NULL),
('Protect', 'TM', NULL),
('Thief', 'TM', NULL),
('Trailblaze', 'TM', NULL),
('Fire Spin', 'TM', NULL),
('Facade', 'TM', NULL),
('Bulldoze', 'TM', NULL),
('Rock Tomb', 'TM', NULL),
('Flame Charge', 'TM', NULL),
('Endure', 'TM', NULL),
('Sunny Day', 'TM', NULL),
('Rain Dance', 'TM', NULL),
('Sandstorm', 'TM', NULL),
('Smart Strike', 'TM', NULL),
('Dig', 'TM', NULL),
('Zen Headbutt', 'TM', NULL),
('Bulk Up', 'TM', NULL),
('Body Slam', 'TM', NULL),
('Sleep Talk', 'TM', NULL),
('Stomping Tantrum', 'TM', NULL),
('Rest', 'TM', NULL),
('Rock Slide', 'TM', NULL),
('Body Press', 'TM', NULL),
('Iron Head', 'TM', NULL),
('Substitute', 'TM', NULL),
('Drill Run', 'TM', NULL),
('Will-O-Wisp', 'TM', NULL),
('Flamethrower', 'TM', NULL),
('Reversal', 'TM', NULL),
('Fire Blast', 'TM', NULL),
('Wild Charge', 'TM', NULL),
('Earthquake', 'TM', NULL),
('Stone Edge', 'TM', NULL),
('Giga Impact', 'TM', NULL),
('Outrage', 'TM', NULL),
('Overheat', 'TM', NULL),
('Hyper Beam', 'TM', NULL),
('Flare Blitz', 'TM', NULL),
('Close Combat', 'TM', NULL),
('Tera Blast', 'TM', NULL),
('High Horsepower', 'TM', NULL),
('Lash Out', 'TM', NULL),
('Curse', 'Egg', NULL),
('Endeavor', 'Egg', NULL)
) AS input(name, learn_method, level)
ON input.name = m.name
JOIN learn_methods as lm ON input.learn_method = lm.name
JOIN pokemon as p ON p.unique_name = 'tauros-blaze'
JOIN games as g ON g.name = 'SV'
ORDER BY learn_method_id ASC, level ASC, input.name ASC;
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT p.id as pokemon_id, g.id as game_id, m.id as move_id, lm.id as learn_method_id, input.level
FROM moves as m
JOIN (VALUES
('Tackle', 'Level up', 1),
('Tail Whip', 'Level up', 1),
('Work Up', 'Level up', 5),
('Double Kick', 'Level up', 10),
('Aqua Jet', 'Level up', 15),
('Headbutt', 'Level up', 20),
('Scary Face', 'Level up', 25),
('Zen Headbutt', 'Level up', 30),
('Raging Bull', 'Level up', 35),
('Rest', 'Level up', 40),
('Swagger', 'Level up', 45),
('Thrash', 'Level up', 50),
('Wave Crash', 'Level up', 55),
('Close Combat', 'Level up', 60),
('Take Down', 'TM', NULL),
('Scary Face', 'TM', NULL),
('Protect', 'TM', NULL),
('Water Pulse', 'TM', NULL),
('Thief', 'TM', NULL),
('Trailblaze', 'TM', NULL),
('Chilling Water', 'TM', NULL),
('Facade', 'TM', NULL),
('Bulldoze', 'TM', NULL),
('Rock Tomb', 'TM', NULL),
('Endure', 'TM', NULL),
('Rain Dance', 'TM', NULL),
('Sandstorm', 'TM', NULL),
('Smart Strike', 'TM', NULL),
('Dig', 'TM', NULL),
('Zen Headbutt', 'TM', NULL),
('Bulk Up', 'TM', NULL),
('Body Slam', 'TM', NULL),
('Sleep Talk', 'TM', NULL),
('Stomping Tantrum', 'TM', NULL),
('Rest', 'TM', NULL),
('Rock Slide', 'TM', NULL),
('Body Press', 'TM', NULL),
('Iron Head', 'TM', NULL),
('Substitute', 'TM', NULL),
('Drill Run', 'TM', NULL),
('Liquidation', 'TM', NULL),
('Surf', 'TM', NULL),
('Reversal', 'TM', NULL),
('Hydro Pump', 'TM', NULL),
('Wild Charge', 'TM', NULL),
('Earthquake', 'TM', NULL),
('Stone Edge', 'TM', NULL),
('Giga Impact', 'TM', NULL),
('Outrage', 'TM', NULL),
('Hyper Beam', 'TM', NULL),
('Close Combat', 'TM', NULL),
('Tera Blast', 'TM', NULL),
('High Horsepower', 'TM', NULL),
('Lash Out', 'TM', NULL),
('Curse', 'Egg', NULL),
('Endeavor', 'Egg', NULL)
) AS input(name, learn_method, level)
ON input.name = m.name
JOIN learn_methods as lm ON input.learn_method = lm.name
JOIN pokemon as p ON p.unique_name = 'tauros-aqua'
JOIN games as g ON g.name = 'SV'
ORDER BY learn_method_id ASC, level ASC, input.name ASC;
And Wooper.
DELETE FROM learnsets
WHERE pokemon_id IN (SELECT id FROM pokemon WHERE unique_name = 'wooper-paldea')
AND game_id IN (SELECT id FROM games WHERE name = 'SV');
INSERT INTO learnsets (pokemon_id, game_id, move_id, learn_method_id, level)
SELECT p.id as pokemon_id, g.id as game_id, m.id as move_id, lm.id as learn_method_id, input.level
FROM moves as m
JOIN (VALUES
('Mud Shot', 'Level up', 1),
('Tail Whip', 'Level up', 1),
('Tackle', 'Level up', 4),
('Poison Tail', 'Level up', 8),
('Toxic Spikes', 'Level up', 12),
('Slam', 'Level up', 16),
('Yawn', 'Level up', 21),
('Poison Jab', 'Level up', 24),
('Sludge Wave', 'Level up', 28),
('Amnesia', 'Level up', 32),
('Toxic', 'Level up', 36),
('Earthquake', 'Level up', 40),
('Take Down', 'TM', NULL),
('Mud-Slap', 'TM', NULL),
('Protect', 'TM', NULL),
('Water Pulse', 'TM', NULL),
('Low Kick', 'TM', NULL),
('Acid Spray', 'TM', NULL),
('Trailblaze', 'TM', NULL),
('Chilling Water', 'TM', NULL),
('Facade', 'TM', NULL),
('Poison Tail', 'TM', NULL),
('Bulldoze', 'TM', NULL),
('Mud Shot', 'TM', NULL),
('Rock Tomb', 'TM', NULL),
('Venoshock', 'TM', NULL),
('Endure', 'TM', NULL),
('Rain Dance', 'TM', NULL),
('Sandstorm', 'TM', NULL),
('Dig', 'TM', NULL),
('Body Slam', 'TM', NULL),
('Sleep Talk', 'TM', NULL),
('Waterfall', 'TM', NULL),
('Poison Jab', 'TM', NULL),
('Stomping Tantrum', 'TM', NULL),
('Rest', 'TM', NULL),
('Rock Slide', 'TM', NULL),
('Body Press', 'TM', NULL),
('Spikes', 'TM', NULL),
('Toxic Spikes', 'TM', NULL),
('Gunk Shot', 'TM', NULL),
('Substitute', 'TM', NULL),
('Liquidation', 'TM', NULL),
('Stealth Rock', 'TM', NULL),
('Surf', 'TM', NULL),
('Amnesia', 'TM', NULL),
('Helping Hand', 'TM', NULL),
('Earth Power', 'TM', NULL),
('Hydro Pump', 'TM', NULL),
('Sludge Bomb', 'TM', NULL),
('Earthquake', 'TM', NULL),
('Stone Edge', 'TM', NULL),
('Tera Blast', 'TM', NULL),
('Haze', 'TM', NULL),
('Toxic', 'TM', NULL),
('Acid Spray', 'Egg', NULL),
('After You', 'Egg', NULL),
('Ancient Power', 'Egg', NULL),
('Counter', 'Egg', NULL),
('Curse', 'Egg', NULL),
('Double Kick', 'Egg', NULL),
('Haze', 'Egg', NULL),
('Mist', 'Egg', NULL),
('Recover', 'Egg', NULL),
('Spit Up', 'Egg', NULL),
('Stockpile', 'Egg', NULL),
('Swallow', 'Egg', NULL)
) AS input(name, learn_method, level)
ON input.name = m.name
JOIN learn_methods as lm ON input.learn_method = lm.name
JOIN pokemon as p ON p.unique_name = 'wooper-paldea'
JOIN games as g ON g.name = 'SV'
ORDER BY learn_method_id ASC, level ASC, input.name ASC;
Closed in favour of issue tracking in https://github.com/penelopeysm/apripsql.
[DONE] Remove mega evolutions, we don't care about them
[DONE] Also Partner Pikachu/Eevee and Ash Greninja