Add Pillage Table

[AardWolf]

I would like to start moving some hunt_details to proper tables. PR #216 addresses this for is_lucky_catch. This issue is for pillage information.

Currently there are two hunt details related to pillages - pillage_type and pillage_amount. Pillage type can be bait, gold, or points. Pillage amount is a number.

I propose adding a hunt_pillage table with these columns: hunt_id -- matches hunts.id and is a foreign key pillage_type -- a string that will contain the value bait, gold, or points pillage_amount -- an integer

None of the columns will allow nulls. We will only populate this table if both values are present in the hunt_details.

It means any PR that addresses this issue will have to also deploy that table.

[AardWolf]

CREATE TABLE mhhunthelper.hunt_pillages (
    hunt_id INT UNSIGNED NOT NULL,
    pillage_type CHAR(6) NOT NULL,
    amount INT UNSIGNED NOT NULL
)

[AardWolf]

If we want to be really awesome we could move the pillage types to a lookup table and use a smallint instead...

[AardWolf]

select hunt_id, max(type), max(amount)
from (
    select hd.hunt_id, 
        case when hd.detail_type_id = 81 then dv.name end as type,
        case when hd.detail_type_id = 80 then dv.name end as amount
    from hunt_details hd 
        inner join detail_values dv 
            on hd.detail_value_id = dv.id 
    where hd.detail_type_id in (80, 81) -- 80 is amount, 81 is type 
    ) as big
group by hunt_id
;

This can be used to populate the hunt_pillage table. We can add a primary key constraint to the hunt_id, too.

[AardWolf]

Populate the new table with old data (run without insert to see rows it would insert):

insert into hunt_pillages 
select hunt_id, max(type), max(amount)
from (
    select hd.hunt_id, 
        case when hd.detail_type_id = 81 then dv.name end as type,
        case when hd.detail_type_id = 80 then dv.name end as amount
    from hunt_details hd 
        inner join detail_values dv 
            on hd.detail_value_id = dv.id 
    where hd.detail_type_id in (80, 81) -- 80 is amount, 81 is type 
    ) as big
group by hunt_id
;

[AardWolf]

Clean up hunt_details part 1:

delete 
-- select count(*)
from hunt_details as hd
where hd.hunt_id in (select hunt_id from hunt_pillages)
  and hd.detail_type_id in (80, 81);

[AardWolf]

Clean up detail_values (part 2):

delete from detail_values as dvd where dvd.id in (
select id from (
select dv.id, dv.name, max(hd.hunt_id)
from detail_values dv 
    left join hunt_details hd 
        on dv.id = hd.detail_value_id 
group by dv.id, dv.name
having max(hd.hunt_id) is null
) as counter
);

The image I used had about 1.8M pillage rows in hunt_details and about 42K in detail_values that got cleaned up.

[AardWolf]

In a fresh copy on my dev environment I ran this:

CREATE TABLE mhhunthelper.hunt_pillages (
    hunt_id INT UNSIGNED NOT NULL,
    pillage_type CHAR(1) NOT NULL,
    amount INT UNSIGNED NOT NULL,
    PRIMARY KEY (hunt_id)
);

insert into hunt_pillages  (hunt_id, pillage_type, amount)
select hunt_id, left(max(type), 1), max(amount)
from (
    select hd.hunt_id, 
        case when hd.detail_type_id = 81 then dv.name end as type,
        case when hd.detail_type_id = 80 then dv.name end as amount
    from hunt_details hd 
        inner join detail_values dv 
            on hd.detail_value_id = dv.id 
    where hd.detail_type_id in (80, 81) -- 80 is amount, 81 is type 
    ) as big
group by hunt_id
;

delete 
-- select count(*)
from hunt_details as hd
where hd.hunt_id in (select hunt_id from hunt_pillages)
  and hd.detail_type_id in (80, 81);

delete from detail_values as dvd where dvd.id in (
    select id from (
    select dv.id, dv.name, max(hd.hunt_id)
    from detail_values dv 
        left join hunt_details hd 
            on dv.id = hd.detail_value_id 
    group by dv.id, dv.name
    having max(hd.hunt_id) is null
    ) as counter
);

-- before
-- hunt_details - 952M
-- detail_values - 4M
optimize table hunt_details , detail_values ;
-- after
-- hunt_details - 791M
-- detail_values - 2.5M

[AardWolf]

This is completed.