windows:notas
This is an old revision of the document!
-----
-----
----- creation of a higher struct value to hold
----- the channels option
-----
-----
-----
create database hg4_big
character set utf8
collate utf8_general_ci;
grant select, insert, update, delete, alter, drop, create, index, execute on hg4_big.* to hg4;
---
--- execute "08.0 normalized-model.sql"
---
---
--- execute '11.0 unified-mk-ident-function.sql'
---
---
--- execute '12.0 create-results-table.sql'
---
----
----
---- STEP 1: Import hg_version
----
----
----
drop table if exists pre_hg_version;
create table pre_hg_version
(
id_version bigint auto_increment primary key not null,
major int,
minor int,
desc_version tinytext,
created timestamp,
former_id bigint,
former_db varchar(12)
);
--- import of version information of all databases
insert into pre_hg_version
(major, minor, desc_version, created, former_id, former_db)
select major,
minor,
desc_version,
created,
id_version,
'hg4'
from hg4.hg_version;
insert into pre_hg_version
(major, minor, desc_version, created, former_id, former_db)
select major,
minor,
desc_version,
created,
id_version,
'hg4_vida'
from hg4_vida.hg_version;
insert into pre_hg_version
(major, minor, desc_version, created, former_id, former_db)
select major,
minor,
desc_version,
created,
id_version,
'hg4_canales'
from hg4_canales.hg_version;
insert into pre_hg_version
(major, minor, desc_version, created, former_id, former_db)
select major,
minor,
desc_version,
created,
id_version,
'hg4_pt'
from hg4_pt.hg_version;
----
----
---- STEP 2 : Create table hg_company
----
----
drop table if exists pre_hg_company;
create table pre_hg_company
(
id_company bigint auto_increment primary key not null,
code varchar(20),
name tinytext
);
insert into pre_hg_company (name, code ) values ( 'Mapfre Iberia', 'mapfre.es' );
insert into pre_hg_company (name, code) values ( 'Mapfre Portugal', 'mapfre.pt' );
----
----
---- STEP 3 : Import hg_period
---- Later we will shorten the list of imported periods
----
----
drop table if exists pre_hg_period;
-- id_period: consecutive integer that makes the unique identifier of this table
-- period_number: the number of this period: this should be consecutive
-- in the same company
create table pre_hg_period
(
id_period bigint auto_increment primary key not null,
period_number int,
start_date tinytext,
title text,
id_company bigint,
former_id bigint,
former_db varchar(12)
);
-- import of period information
insert into pre_hg_period
(period_number,
start_date,
title,
id_company,
former_id,
former_db )
select id_period,
start_date,
title,
(select id_company from pre_hg_company where code = 'mapfre.es') as id_company,
id_period,
'hg4'
from hg4.hg_period;
insert into pre_hg_period
(period_number,
start_date,
title,
id_company,
former_id,
former_db )
select id_period,
start_date,
title,
(select id_company from pre_hg_company where code = 'mapfre.es') as id_company,
id_period,
'hg4_canales'
from hg4_canales.hg_period;
insert into pre_hg_period
(period_number,
start_date,
title,
id_company,
former_id,
former_db )
select id_period,
start_date,
title,
(select id_company from pre_hg_company where code = 'mapfre.es') as id_company,
id_period,
'hg4_vida'
from hg4_vida.hg_period;
insert into pre_hg_period
(period_number,
start_date,
title,
id_company,
former_id,
former_db )
select id_period,
start_date,
title,
(select id_company from pre_hg_company where code = 'mapfre.pt') as id_company,
id_period,
'hg4_pt'
from hg4_pt.hg_period;
----
----
---- STEP 4.1 : Creation of table hg_channel
----
----
drop table if exists pre_hg_channel;
create table pre_hg_channel
(
id_channel bigint auto_increment primary key not null,
code varchar(20),
description varchar(255)
);
insert into pre_hg_channel (code, description) values ('no_channel', 'no_channel');
insert into pre_hg_channel (code, description) values ('agencial', 'Red Agencial');
insert into pre_hg_channel (code, description) values ('vida', 'Red Vida');
insert into pre_hg_channel (code, description) values ('brokers', 'Brokers');
insert into pre_hg_channel (code, description) values ('pt', 'Red Portugal');
----
----
---- STEP 5 : Import of hg_struct
---- It's important to set correctly the value of
---- id_struct_parent.
----
drop table if exists pre_hg_struct;
create table pre_hg_struct
(
id_struct bigint auto_increment primary key not null,
what_is varchar(10), -- "global","dgt","subcentral","oficina"
code varchar(20),
description varchar(255),
id_struct_parent bigint,
id_period bigint default 0,
id_company bigint default 0,
id_channel bigint default 0,
former_id bigint,
former_id_parent bigint,
former_db varchar(12)
);
insert into pre_hg_struct
(what_is,
code,
description,
id_period,
id_company,
former_id,
former_id_parent,
former_db)
select what_is,
code,
description,
(select id_period
from pre_hg_period
where former_id = old_struct.id_period
and former_db = 'hg4'),
(select id_company from pre_hg_company where code = 'mapfre.es') as id_company,
id_struct,
id_struct_parent,
'hg4'
from hg4.hg_struct old_struct;
insert into pre_hg_struct
(what_is,
code,
description,
id_period,
id_company,
former_id,
former_id_parent,
former_db)
select what_is,
code,
description,
(select id_period
from pre_hg_period
where former_id = old_struct.id_period
and former_db = 'hg4_canales'),
(select id_company from pre_hg_company where code = 'mapfre.es') as id_company,
id_struct,
id_struct_parent,
'hg4_canales'
from hg4_canales.hg_struct old_struct;
insert into pre_hg_struct
(what_is,
code,
description,
id_period,
id_company,
former_id,
former_id_parent,
former_db)
select what_is,
code,
description,
(select id_period
from pre_hg_period
where former_id = old_struct.id_period
and former_db = 'hg4_vida'),
(select id_company from pre_hg_company where code = 'mapfre.es') as id_company,
id_struct,
id_struct_parent,
'hg4_vida'
from hg4_vida.hg_struct old_struct;
insert into pre_hg_struct
(what_is,
code,
description,
id_period,
id_company,
former_id,
former_id_parent,
former_db)
select what_is,
code,
description,
(select id_period
from pre_hg_period
where former_id = old_struct.id_period
and former_db = 'hg4_pt'),
(select id_company from pre_hg_company where code = 'mapfre.pt') as id_company,
id_struct,
id_struct_parent,
'hg4_pt'
from hg4_pt.hg_struct old_struct;
---
---
--- update new id_struct_parent
---
---
drop procedure update_parent;
update pre_hg_struct set id_struct_parent = null;
delimiter $$
create procedure update_parent()
begin
declare v_finished integer default 0;
declare v_new_parent bigint;
declare v_id_struct bigint;
declare v_former_id bigint;
declare v_former_id_parent bigint;
declare v_former_database varchar(12);
declare c_struct cursor for
select id_struct,
former_id,
former_id_parent,
former_db
from pre_hg_struct;
declare continue handler
for not found set v_finished = 1;
open c_struct;
loop_struct: loop
fetch c_struct
into v_id_struct,
v_former_id,
v_former_id_parent,
v_former_database;
if v_finished = 1 then
leave loop_struct;
end if;
set v_new_parent := (select id_struct
from pre_hg_struct
where former_id = v_former_id_parent
and former_db = v_former_database);
update pre_hg_struct
set id_struct_parent = v_new_parent
where id_struct = v_id_struct;
end loop loop_struct;
close c_struct;
end
$$
delimiter ;
call update_parent();
drop procedure update_parent;
---
--- change 'global' for 'canal'
---
update pre_hg_struct set what_is = 'canal' where what_is = 'global';
-- identify the different channels
update pre_hg_struct
set code = (select code from pre_hg_channel where code = 'agencial'),
description = (select description from pre_hg_channel where code = 'agencial')
where what_is = 'canal' and former_db = 'hg4';
update pre_hg_struct
set code = (select code from pre_hg_channel where code = 'vida'),
description = (select description from pre_hg_channel where code = 'vida')
where what_is = 'canal' and former_db = 'hg4_vida';
update pre_hg_struct
set code = (select code from pre_hg_channel where code = 'brokers') ,
description = (select description from pre_hg_channel where code = 'brokers')
where what_is = 'canal' and former_db = 'hg4_canales';
update pre_hg_struct
set code = (select code from pre_hg_channel where code = 'pt'),
description = (select description from pre_hg_channel where code = 'pt')
where what_is = 'canal' and former_db = 'hg4_pt';
-- set the channel in hg_struct based in the former_db
-- emtpy channel
update pre_hg_struct
set id_channel = (select id_channel from pre_hg_channel where code = 'no_channel')
where former_db is null;
update pre_hg_struct
set id_channel = (select id_channel from pre_hg_channel where code = 'agencial')
where former_db = 'hg4';
update pre_hg_struct
set id_channel = (select id_channel from pre_hg_channel where code = 'vida')
where former_db = 'hg4_vida';
update pre_hg_struct
set id_channel = (select id_channel from pre_hg_channel where code = 'brokers')
where former_db = 'hg4_canales';
update pre_hg_struct
set id_channel = (select id_channel from pre_hg_channel where code = 'pt')
where former_db = 'hg4_pt';
---
--- verifications of the table pre_hg_struct
---
select * from pre_hg_struct where id_struct_parent is null;
-- expected result: only the "canal" results
----
----
---- STEP 6 : import of table hg_indicator
----
----
drop table if exists pre_hg_indicator;
create table pre_hg_indicator
(
id_indic bigint auto_increment primary key not null,
indicator_key tinytext,
title tinytext,
long_desc text,
weight int,
percentage decimal(20,10),
use_for_critic int default 1,
id_period bigint default 0,
id_struct_channel bigint not null,
former_id bigint,
former_db varchar(12)
);
insert into pre_hg_indicator
(indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
id_period,
id_struct_channel,
former_id,
former_db
)
select indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
(select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4'),
(select id_struct
from pre_hg_struct
where id_period = (select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4')
and what_is = 'canal'),
id_indic,
'hg4'
from hg4.hg_indicator old_indic;
insert into pre_hg_indicator
(indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
id_period,
id_struct_channel,
former_id,
former_db
)
select indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
(select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4_canales'),
(select id_struct
from pre_hg_struct
where id_period = (select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4_canales')
and what_is = 'canal'),
id_indic,
'hg4_canales'
from hg4_canales.hg_indicator old_indic;
insert into pre_hg_indicator
(indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
id_period,
id_struct_channel,
former_id,
former_db
)
select indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
(select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4_vida'),
(select id_struct
from pre_hg_struct
where id_period = (select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4_vida')
and what_is = 'canal'),
id_indic,
'hg4_vida'
from hg4_vida.hg_indicator old_indic;
insert into pre_hg_indicator
(indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
id_period,
id_struct_channel,
former_id,
former_db
)
select indicator_key,
title,
long_desc,
weight,
percentage,
use_for_critic,
(select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4_pt'),
(select id_struct
from pre_hg_struct
where id_period = (select id_period
from pre_hg_period
where period_number = old_indic.id_period
and former_db = 'hg4_pt')
and what_is = 'canal'),
id_indic,
'hg4_pt'
from hg4_pt.hg_indicator old_indic;
----
----
---- STEP 7: Reorganization of periods
----
---- Adjustements and reorganizations of periods:
---- It canot be done before this point because
---- we need the reference to the old period_id
---- up to this point
----
-- the periods for the channel hg_vida must be changed
-- to equivalent periods for the channel "hg4" (agent network)
--
-- Period 1 of vida goes to period 3 of red agencial
--
update pre_hg_struct set
id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4' and period_number = 3)
where id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4_vida' and period_number = 1);
update pre_hg_indicator set
id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4' and period_number = 3)
where id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4_vida' and period_number = 1);
--
-- Period 2 of vida goes to period 4 of red agencial
--
update pre_hg_struct set
id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4' and period_number = 4)
where id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4_vida' and period_number = 2);
update pre_hg_indicator set
id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4' and period_number = 4)
where id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4_vida' and period_number = 2);
--
-- Period 1 of canales goes to period 4 of red agencial
--
update pre_hg_struct set
id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4' and period_number = 4)
where id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4_canales' and period_number = 1);
update pre_hg_indicator set
id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4' and period_number = 4)
where id_period =
(select id_period
from pre_hg_period
where former_db = 'hg4_canales' and period_number = 1);
----
----
---- STEP 8: Create the "compania" struct and link it
----
---- change the global for the name of the
---- channel and create the company high structure
----
----
drop procedure create_global_struct;
delimiter $$
create procedure create_global_struct()
begin
declare v_finished integer default 0;
declare v_id_period bigint;
declare v_period_number int(11);
declare v_start_date tinytext;
declare v_id_company bigint;
declare v_last_id bigint;
declare c_period_by_company cursor for
select id_period,
period_number,
start_date,
id_company
from pre_hg_period
where former_db = 'hg4' or former_db = 'hg4_pt';
declare continue handler
for not found set v_finished = 1;
open c_period_by_company;
loop_struct: loop
fetch c_period_by_company
into v_id_period,
v_period_number,
v_start_date,
v_id_company;
if v_finished = 1 then
leave loop_struct;
end if;
insert into pre_hg_struct
(what_is,
code,
description,
id_struct_parent,
id_period,
id_company,
id_channel)
values
(
'compania',
(select code from pre_hg_company where id_company = v_id_company),
(select name from pre_hg_company where id_company = v_id_company),
null,
v_id_period,
v_id_company,
(select id_channel from hg_channel where code = 'no_channel')
);
-- get the latest id of the insert
set v_last_id := last_insert_id();
-- map the id_struct parent with its parent companies
update pre_hg_struct
set id_struct_parent = v_last_id
where what_is = 'canal'
and id_period = v_id_period
and id_company = v_id_company;
end loop loop_struct;
close c_period_by_company;
end
$$
delimiter ;
call create_global_struct();
drop procedure create_global_struct;
---
--- verifications
---
select *
from pre_hg_struct me,
pre_hg_struct parent
where me.former_db = parent.former_db
and me.former_id_parent = parent.former_id
and me.id_struct_parent <> parent.id_struct;
-- expected result: empty set
----
----
---- STEP 9: Import of table hg_valoration
----
----
drop table if exists pre_hg_valoration;
create table pre_hg_valoration
(
id_valoration bigint auto_increment primary key not null,
id_indic bigint,
indic_value double,
id_struct bigint,
former_id bigint,
former_db varchar(12)
);
insert into pre_hg_valoration
(id_indic,
indic_value,
id_struct,
former_id,
former_db
)
select (select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4') id_indic,
indic_value,
(select id_struct
from pre_hg_struct
where former_id = val.id_struct
and former_db = 'hg4') id_struct,
id_indic,
'hg4'
from hg4.hg_valoration val;
insert into pre_hg_valoration
(id_indic,
indic_value,
id_struct,
former_id,
former_db
)
select (select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4_canales') id_indic,
indic_value,
(select id_struct
from pre_hg_struct
where former_id = val.id_struct
and former_db = 'hg4_canales') id_struct,
id_indic,
'hg4_canales'
from hg4_canales.hg_valoration val;
insert into pre_hg_valoration
(id_indic,
indic_value,
id_struct,
former_id,
former_db
)
select (select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4_vida') id_indic,
indic_value,
(select id_struct
from pre_hg_struct
where former_id = val.id_struct
and former_db = 'hg4_vida') id_struct,
id_indic,
'hg4_vida'
from hg4_vida.hg_valoration val;
insert into pre_hg_valoration
(id_indic,
indic_value,
id_struct,
former_id,
former_db
)
select (select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4_pt') id_indic,
indic_value,
(select id_struct
from pre_hg_struct
where former_id = val.id_struct
and former_db = 'hg4_pt') id_struct,
id_indic,
'hg4_pt'
from hg4_pt.hg_valoration val;
----
----
---- STEP 10: Import of table pre_hg_detail
----
----
drop table if exists pre_hg_detail;
create table pre_hg_detail
(
id_detail bigint auto_increment primary key not null,
id_struct bigint,
id_indic bigint,
clave_agt varchar(20),
field_desc tinytext,
field_value tinytext,
field_hash varchar(32),
former_id bigint,
former_db varchar(12)
);
insert into pre_hg_detail
(id_struct,
id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = det.id_struct
and former_db = 'hg4') id_struct,
(select id_indic
from pre_hg_indicator
where former_id = det.id_indic
and former_db = 'hg4') id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
id_struct,
'hg4'
from hg4.hg_detail det;
insert into pre_hg_detail
(id_struct,
id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = det.id_struct
and former_db = 'hg4_canales') id_struct,
(select id_indic
from pre_hg_indicator
where former_id = det.id_indic
and former_db = 'hg4_canales') id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
id_struct,
'hg4_canales'
from hg4_canales.hg_detail det;
insert into pre_hg_detail
(id_struct,
id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = det.id_struct
and former_db = 'hg4_vida') id_struct,
(select id_indic
from pre_hg_indicator
where former_id = det.id_indic
and former_db = 'hg4_vida') id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
id_struct,
'hg4_vida'
from hg4_vida.hg_detail det;
insert into pre_hg_detail
(id_struct,
id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = det.id_struct
and former_db = 'hg4_pt') id_struct,
(select id_indic
from pre_hg_indicator
where former_id = det.id_indic
and former_db = 'hg4_pt') id_indic,
clave_agt,
field_desc,
field_value,
field_hash,
id_struct,
'hg4_pt'
from hg4_pt.hg_detail det;
-----
-----
----- STEP 11: Tables for aggregated results
-----
-----
drop table if exists pre_hg_val_high;
create table pre_hg_val_high
(id_val_high bigint auto_increment primary key not null,
id_major bigint,
what_is_major varchar(10),
id_minor bigint,
what_is_minor varchar(10),
id_indic bigint,
indic_value double,
avg_value double,
std_value double,
is_high int default 0,
is_critic int default 0,
is_medium int default 0,
is_low int default 0,
former_id bigint,
former_db varchar(12));
insert into pre_hg_val_high
(id_major,
what_is_major,
id_minor,
what_is_minor,
id_indic,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4') what_is_minor,
(select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4') indic_value,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
id_val_high,
'hg4'
from hg4.hg_val_high val;
insert into pre_hg_val_high
(id_major,
what_is_major,
id_minor,
what_is_minor,
id_indic,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_canales') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_canales') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_canales') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_canales') what_is_minor,
(select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4_canales') indic_value,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
id_val_high,
'hg4_canales'
from hg4_canales.hg_val_high val;
insert into pre_hg_val_high
(id_major,
what_is_major,
id_minor,
what_is_minor,
id_indic,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_vida') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_vida') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_vida') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_vida') what_is_minor,
(select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4_vida') indic_value,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
id_val_high,
'hg4_vida'
from hg4_vida.hg_val_high val;
insert into pre_hg_val_high
(id_major,
what_is_major,
id_minor,
what_is_minor,
id_indic,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
former_id,
former_db)
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_pt') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_pt') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_pt') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_pt') what_is_minor,
(select id_indic
from pre_hg_indicator
where former_id = val.id_indic
and former_db = 'hg4_pt') indic_value,
indic_value,
avg_value,
std_value,
is_high,
is_critic,
is_medium,
is_low,
id_val_high,
'hg4_pt'
from hg4_pt.hg_val_high val;
----
----
---- STEP 12: import of table hg_val_struct
----
----
drop table if exists pre_hg_val_struct;
create table pre_hg_val_struct
(id_val_struct bigint auto_increment primary key not null,
id_major bigint,
what_is_major varchar(10),
id_minor bigint,
what_is_minor varchar(10),
struct_value double,
avg_value double,
std_value double,
num_high int,
num_critic int,
sem_value double,
former_id bigint,
former_db varchar(12)
);
insert into pre_hg_val_struct
(id_major,
what_is_major,
id_minor,
what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
former_id,
former_db )
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4') what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
id_val_struct,
'hg4'
from hg4.hg_val_struct val ;
insert into pre_hg_val_struct
(id_major,
what_is_major,
id_minor,
what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
former_id,
former_db )
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_canales') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_canales') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_canales') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_canales') what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
id_val_struct,
'hg4_canales'
from hg4_canales.hg_val_struct val ;
insert into pre_hg_val_struct
(id_major,
what_is_major,
id_minor,
what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
former_id,
former_db )
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_vida') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_vida') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_vida') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_vida') what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
id_val_struct,
'hg4_vida'
from hg4_vida.hg_val_struct val ;
insert into pre_hg_val_struct
(id_major,
what_is_major,
id_minor,
what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
former_id,
former_db )
select (select id_struct
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_pt') id_major,
(select what_is
from pre_hg_struct
where former_id = val.id_major
and former_db = 'hg4_pt') what_is_major,
(select id_struct
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_pt') id_minor,
(select what_is
from pre_hg_struct
where former_id = val.id_minor
and former_db = 'hg4_pt') what_is_minor,
struct_value,
avg_value,
std_value,
num_high,
num_critic,
sem_value,
id_val_struct,
'hg4_pt'
from hg4_pt.hg_val_struct val ;
----
----
---- STEP 13: delete periods that we won't use
---- any longer
----
delete from pre_hg_period
where former_db = 'hg4_canales' or former_db = 'hg4_vida';
-- check that there isn't periods incorrectly set in the
-- other tables
select *
from pre_hg_indicator
where id_period not in
(select id_period from pre_hg_period) limit 3;
-- expected result: empty set
select *
from pre_hg_struct
where id_period not in
(select id_period from pre_hg_period) limit 3;
-- expected result: empty set
----
----
---- STEP 14: cleanup
----
----
-- NOTE 01/04/2014: BY NOW, EVERY DELETION OF COLUMNS
-- "FORMER_DB" AND "FORMER_ID" WILL BE AVOIDED
-- remove the "former_id" and "former_db" fields,
-- rename the 'pre_' tables
drop table if exists hg_company;
-- alter table pre_hg_company drop column former_db;
rename table pre_hg_company to hg_company;
drop table if exists hg_detail;
-- alter table pre_hg_detail drop column former_id;
-- alter table pre_hg_detail drop column former_db;
rename table pre_hg_detail to hg_detail;
drop table if exists hg_indicator;
-- alter table pre_hg_indicator drop column former_id;
-- alter table pre_hg_indicator drop column former_db;
rename table pre_hg_indicator to hg_indicator;
drop table if exists hg_period;
-- alter table pre_hg_period drop column former_id;
-- alter table pre_hg_period drop column former_db;
rename table pre_hg_period to hg_period;
drop table if exists hg_struct;
-- alter table pre_hg_struct drop column former_id;
-- alter table pre_hg_struct drop column former_db;
rename table pre_hg_struct to hg_struct;
drop table if exists hg_val_high;
-- alter table pre_hg_val_high drop column former_id;
-- alter table pre_hg_val_high drop column former_db;
rename table pre_hg_val_high to hg_val_high;
drop table if exists hg_val_struct;
-- alter table pre_hg_val_struct drop column former_id;
-- alter table pre_hg_val_struct drop column former_db;
rename table pre_hg_val_struct to hg_val_struct;
drop table if exists hg_valoration;
-- alter table pre_hg_valoration drop column former_id;
-- alter table pre_hg_valoration drop column former_db;
rename table pre_hg_valoration to hg_valoration;
drop table if exists hg_version;
-- alter table pre_hg_version drop column former_id;
-- alter table pre_hg_version drop column former_db;
rename table pre_hg_version to hg_version;
drop table if exists hg_channel;
rename table pre_hg_channel to hg_channel;
----
----
---- STEP 15: create (or recreate) indexes
----
----
--
-- make indexes
--
--
-- Table: hg_struct
--
create index hg_struct_id on hg_struct (id_struct);
create index hg_struct_id_parent on hg_struct (id_struct_parent);
create index hg_struct_id_period on hg_struct (id_period);
create index hg_struct_id_channel on hg_struct (id_channel);
--
-- Table: hg_channel
--
create index hg_channel_id on hg_channel ( id_channel );
--
-- Table: hg_detail
--
create index hg_detail_hash on hg_detail (field_hash);
create index hg_detail_id_struct on hg_detail (id_struct);
-- DUDA de si este es eficaz
--create index hg_detail_hash_id_struct on hg_detail( field_hash,id_struct );
--
-- Table: hg_indicator
--
create index hg_indicator_id on hg_indicator (id_indic);
--
-- Table: hg_valoration
--
create index hg_valoration_id_indic on hg_valoration (id_indic);
create index hg_valoration_id_struct on hg_valoration (id_struct);
--
-- Table: hg_val_high
--
create index hg_val_high_id_minor on hg_val_high( id_minor );
create index hg_val_high_id_major on hg_val_high( id_major );
create index hg_val_high_id_indic on hg_val_high( id_indic );
--
-- Table: hg_val_struct
--
create index hg_val_st_id_minor on hg_val_struct( id_minor );
create index hg_val_st_id_major on hg_val_struct( id_major );
----
----
---- STEP 16: safety measures
----
----
-- anotate in the agenda that
-- a copy of the database must be done
windows/notas.1396991115.txt.gz ยท Last modified: 2022/12/02 21:02 (external edit)