C6aJW5CA

1/* Transfer Pet and Thrall ownership ID's to our custom tables before we remove old event logs*/
2
3
4
5create table if not exists z_pet_ownership(pet_id bigint null, player_owner_id bigint null, clan_owner_id bigint null);
6
7create table if not exists z_thrall_ownership(thrall_id bigint null, player_owner_id bigint null, clan_owner_id bigint null);
8
9insert into z_pet_ownership (pet_id, player_owner_id, clan_owner_id) select distinct objectid, ownerid, ownerguildid from game_events where not exists ( select pet_id from z_pet_ownership where z_pet_ownership.pet_id = game_events.objectid ) and eventtype = 89 and objectname like '%pet_%';
10
11insert into z_thrall_ownership (thrall_id, player_owner_id, clan_owner_id) select distinct objectid, ownerid, ownerguildid from game_events where not exists ( select thrall_id from z_thrall_ownership where z_thrall_ownership.thrall_id = game_events.objectid ) and eventtype = 89 and objectname like '%Aquilonian%' or objectname like '%Cimmerian%' or objectname like '%Darfari%' or objectname like '%Hyborian%' or objectname like '%Hyrkanian%' or objectname like '%Kambujan%' or objectname like '%Khitan%' or objectname like '%Kushite%' or objectname like '%Lemurian%' or objectname like '%Nordheimer%' or objectname like '%Shemite%' or objectname like '%Stygian%' or objectname like '%Zamorian%' or objectname like '%Zingarian%' or objectname like '%Black_Hand%' or objectname like '%Darfari%' or objectname like '%Dogs%' or objectname like '%Exile%' or objectname like '%Forgotten%' or objectname like '%Heir%' or objectname like '%Lemurian%' or objectname like '%Relic_%' or objectname like '%Votaries%' or objectname like '%Alchemist%' or objectname like '%Archer%' or objectname like '%Armorer%' or objectname like '%Bearer%' or objectname like '%Blacksmith%' or objectname like '%Carpenter%' or objectname like '%Cook%' or objectname like '%Entertainer%' or objectname like '%Fighter%' or objectname like '%Priest%' or objectname like '%Smelter%' or objectname like '%Tanner%' or objectname like '%Taskmaster%' or objectname like '%Witch_Queen%' or objectname like '%broodwarden%';
12
13
14
15/* Remove duplicate owned npc id rows from our custom tables above */
16
17delete from z_thrall_ownership where rowid not in ( select min(rowid) from z_thrall_ownership group by thrall_id ) ; 
18
19delete from z_pet_ownership where rowid not in ( select min(rowid) from z_pet_ownership group by pet_id ) ;
20
21
22
23/* Replace 0 values with Null value */
24
25update `z_pet_ownership` set `player_owner_id` = null where player_owner_id = 0;
26
27update `z_pet_ownership` set `clan_owner_id` = null where clan_owner_id = 0;
28
29update `z_thrall_ownership` set `player_owner_id` = null where player_owner_id = 0;
30
31update `z_thrall_ownership` set `clan_owner_id` = null where clan_owner_id = 0;
32
33
34
35/*remove inactive player/clan pets*/
36
37delete from properties where object_id in (select distinct pet_id from z_pet_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
38
39delete from properties where object_id in (select distinct pet_id from z_pet_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
40
41delete from actor_position where id in (select distinct pet_id from z_pet_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
42
43delete from actor_position where id in (select distinct pet_id from z_pet_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
44
45delete from item_inventory where owner_id in (select distinct pet_id from z_pet_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
46
47delete from item_inventory where owner_id in (select distinct pet_id from z_pet_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
48
49delete from character_stats where char_id in (select distinct pet_id from z_pet_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
50
51delete from character_stats where char_id in (select distinct pet_id from z_pet_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
52
53
54
55/*remove inactive player/clan thralls*/
56
57delete from properties where object_id in (select distinct thrall_id from z_thrall_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
58
59delete from properties where object_id in (select distinct thrall_id from z_thrall_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
60
61delete from actor_position where id in (select distinct thrall_id from z_thrall_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
62
63delete from actor_position where id in (select distinct thrall_id from z_thrall_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
64
65delete from item_inventory where owner_id in (select distinct thrall_id from z_thrall_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
66
67delete from item_inventory where owner_id in (select distinct thrall_id from z_thrall_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
68
69delete from character_stats where char_id in (select distinct thrall_id from z_thrall_ownership where player_owner_id in (select id from characters where lastTimeOnline < strftime('%s', 'now', '-10 days')) and player_owner_id not in (select id from characters where guild in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
70
71delete from character_stats where char_id in (select distinct thrall_id from z_thrall_ownership where clan_owner_id in (select guildid from guilds where guildid not in (select distinct guild from characters where lastTimeOnline > strftime('%s', 'now', '-10 days') and guild is not null)));
72
73
74
75/* Remove old event logs */
76
77delete from game_events where worldTime < strftime('%s', 'now', '-3 days');
78
79VACUUM;
80
81REINDEX;
82
83ANALYZE;
84
85pragma integrity_check;
86
87.quit