Create Ranking History -> (It will allow player graphs)

[koburk]

First of all, we need to set a few things on our DB

Enable events:
SET GLOBAL event_scheduler = ON;
In the server configuration file (my.cnf, or my.ini on Windows systems), include the line where it will be read by the server (for example, in a [mysqld] section):
event_scheduler=DISABLED

https://dev.mysql.com/doc/refman/5.7/en/events-configuration.html

Create our history table:

CREATE TABLE `uni1_statpoints_hist` (
`id` int(11) unsigned NOT NULL auto_increment,
`date` DATETIME DEFAULT NULL,
  `id_owner` int(11) unsigned NOT NULL DEFAULT '0',
  `id_ally` int(11) unsigned NOT NULL DEFAULT '0',
  `stat_type` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `universe` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `tech_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `tech_old_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `tech_points` double(50,0) unsigned NOT NULL DEFAULT '0',
  `tech_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `build_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `build_old_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `build_points` double(50,0) unsigned NOT NULL DEFAULT '0',
  `build_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `defs_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `defs_old_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `defs_points` double(50,0) unsigned NOT NULL DEFAULT '0',
  `defs_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `fleet_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `fleet_old_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `fleet_points` double(50,0) unsigned NOT NULL DEFAULT '0',
  `fleet_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `total_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `total_old_rank` int(11) unsigned NOT NULL DEFAULT '0',
  `total_points` double(50,0) unsigned NOT NULL DEFAULT '0',
  `total_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `date` (`date`),
  KEY `id_owner` (`id_owner`),
  KEY `universe` (`universe`),
  KEY `stat_type` (`stat_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Create the procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `statpoints_to_hist`()
BEGIN
   declare v_now datetime;
   set v_now = now();
    
    INSERT INTO `uni1_statpoints_hist`	
(`date`,`id_owner`,`id_ally`,`stat_type`,`universe`,`tech_rank`,`tech_old_rank`,`tech_points`,`tech_count`,`build_rank`,`build_old_rank`,`build_points`,`build_count`,`defs_rank`,`defs_old_rank`,`defs_points`,`defs_count`,`fleet_rank`,`fleet_old_rank`,`fleet_points`,`fleet_count`,`total_rank`,`total_old_rank`,`total_points`,`total_count`)
    SELECT v_now,`id_owner`,`id_ally`,`stat_type`,`universe`,`tech_rank`,`tech_old_rank`,`tech_points`,`tech_count`,`build_rank`,`build_old_rank`,`build_points`,`build_count`,`defs_rank`,`defs_old_rank`,`defs_points`,`defs_count`,`fleet_rank`,`fleet_old_rank`,`fleet_points`,`fleet_count`,`total_rank`,`total_old_rank`,`total_points`,`total_count`
		FROM `uni1_statpoints`;

	DELETE FROM `uni1_statpoints_hist` WHERE `date` < NOW() - INTERVAL 365 DAY;
END

Create the event:

CREATE EVENT `event_statpoints_hist`
  ON SCHEDULE
    EVERY 1 day
    STARTS NOW()
  DO
	call `statpoints_to_hist`;

After setting this up, we'll start collecting a history, from there we can get that information to build graphs and show player progression over time.
Also, on the stored procedure we're deleting info older than a year, but i'd like to have as much as possible, or even better to comment it at all :) to store everything (based on dedicated space).

Cheers