Differences

This shows you the differences between two versions of the page.

--- windows:notas [2014/04/08 21:05] – created rlunaro
+++ windows:notas [2014/04/08 21:54] (current) – removed rlunaro
@@ Line 1: / Line 1: @@
-<code>
------
------
------ 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
-</code>