Miscellany

DELIMITER $$

DROP PROCEDURE IF EXISTS `cdr_test`.`CopyToArchive` $$
CREATE DEFINER=`root`@`%` PROCEDURE `CopyToArchive`(IN table_in varchar(10))
BEGIN


DECLARE done INT DEFAULT 0;
DECLARE year_month_v varchar(15);
DECLARE filename_v varchar(30);

declare create_archive_table varchar(500);

declare start_text varchar(500);
declare end_text varchar(500);


DECLARE file_cursor cursor for SELECT `year_month`, `filename` from table_files;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;


CREATE TABLE if not exists `move_to_archive_log` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `time` datetime default NULL,
  `query` blob NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;



set @table_in = table_in;

set @time_now = now();



set start_text = concat("Moving ", @table_in);
insert into move_to_archive_log (`time`,`query`) values (now(), start_text);


-- set @create_temp_table = concat("create temporary table table_to_move select * from ", @table_in, " where calltime < date_sub(now(), interval 62 day)");
set @create_temp_table = concat("create temporary table table_to_move select * from ", @table_in, " where calltime < date_sub(\"", @time_now, "\", interval 62 day)");
prepare create_temp_table from @create_temp_table;


drop temporary table if exists table_to_move;
execute create_temp_table;

insert into move_to_archive_log (`time`,`query`) values (now(), @create_temp_table);


drop temporary table if exists table_files;
create temporary table table_files
select distinct(date_format(calltime, "%Y-%m")) as `year_month` from table_to_move;


alter table table_files add `filename` varchar(30);

update table_files set `filename` = if(
month(concat(`year_month`,"-01")) <= 6,
concat(@table_in, "_" ,year(concat(`year_month`,"-01")),"_1"),
concat(@table_in, "_" ,year(concat(`year_month`,"-01")),"_2")
);



open file_cursor;


REPEAT
    FETCH file_cursor INTO year_month_v, filename_v;

    if not done then

    set @filename = filename_v;

    -- insert into `log` (var) values (@filename);

    set create_archive_table = concat("create table if not exists cdr_archives.", @filename, " like ", @table_in);
    set @create_archive_table = create_archive_table;
    prepare create_archive_table from @create_archive_table;
    execute create_archive_table;


    set @insert_query = concat("insert ignore into cdr_archives.", @filename, " select ttm.*
    from table_to_move ttm
    inner join table_files tf on date_format(ttm.calltime, \"%Y-%m\") = tf.`year_month`
    where tf.`filename` = '",@filename,"'");

    -- insert into `log` (var) values (@insert_query);

    prepare insert_query from @insert_query;
    execute insert_query;

    insert into move_to_archive_log (`time`,`query`) values (now(), @insert_query);


    set @delete_query = concat("delete from ", @table_in, " where calltime < date_sub(\"", @time_now, "\", interval 62 day)");

    prepare delete_query from @delete_query;
    execute delete_query;


    insert into move_to_archive_log (`time`,`query`) values (now(), @delete_query);


    end if;

UNTIL done END REPEAT;

close file_cursor;



set end_text = concat("Done moving ", @table_in);
insert into move_to_archive_log (`time`,`query`) values (now(), end_text);


select count(*) as elements_moved from table_to_move;


END $$

DELIMITER ;