Changeset 7281


Ignore:
Timestamp:
May 17, 2018, 2:03:40 PM (16 months ago)
Author:
mabarracus
Message:

Add drop indexes to historical tables

Location:
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server
Files:
1 deleted
1 edited

Legend:

Unmodified
Added
Removed
  • lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/purge_old_data.sql

    r7280 r7281  
    11use analytics;
     2start transaction;
     3DELIMITER $$
    24
     5DROP PROCEDURE IF EXISTS drop_index_if_exists $$
     6CREATE PROCEDURE drop_index_if_exists(in theTable varchar(128), in theIndexName varchar(128) )
     7BEGIN
     8 IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name =
     9theTable AND index_name = theIndexName) > 0) THEN
     10   SET @s = CONCAT('DROP INDEX ' , theIndexName , ' ON ' , theTable);
     11   PREPARE stmt FROM @s;
     12   EXECUTE stmt;
     13 END IF;
     14END $$
     15
     16DROP PROCEDURE IF EXISTS drop_primary_index_if_exists $$
     17CREATE PROCEDURE drop_primary_index_if_exists(in theTable varchar(128))
     18BEGIN
     19 IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name =
     20theTable AND index_name = 'PRIMARY') > 0) THEN
     21   SET @s = CONCAT('ALTER TABLE ', theTable, ' DROP PRIMARY KEY ');
     22   PREPARE stmt FROM @s;
     23   EXECUTE stmt;
     24 END IF;
     25END $$
     26
     27DELIMITER ;
     28commit;
    329start transaction;
    430
    531# PURGE OLD DATA (1 year older)
     32# CREATE TABLES
    633create table if not exists RecvPackages_historical like RecvPackages;
    734create table if not exists Client_Versions_historical like Client_Versions;
    835create table if not exists RecvPackages_purged like RecvPackages;
    936
     37call drop_primary_index_if_exists('RecvPackages_historical');
     38call drop_index_if_exists('RecvPackages_historical','fk_RecvPackages_Releases1_idx');
     39call drop_index_if_exists('RecvPackages_historical','fk_RecvPackages_Flavours1_idx');
     40call drop_index_if_exists('RecvPackages_historical','get_top_apps');
     41call drop_index_if_exists('RecvPackages_historical','get_top_apps_others');
     42
     43call drop_primary_index_if_exists('Client_Versions_historical');
     44call drop_index_if_exists('Client_Versions_historical','fk_Client_Versions_Releases1_idx');
     45call drop_index_if_exists('Client_Versions_historical','fk_Client_Versions_Flavours1_idx');
     46call drop_index_if_exists('Client_Versions_historical','get_clients');
     47call drop_index_if_exists('Client_Versions_historical','get_clients_other');
     48call drop_index_if_exists('Client_Versions_historical','ltsp_mode');
     49
     50call drop_primary_index_if_exists('RecvPackages_purged');
     51call drop_index_if_exists('RecvPackages_purged','fk_RecvPackages_Releases1_idx');
     52call drop_index_if_exists('RecvPackages_purged','fk_RecvPackages_Flavours1_idx');
     53call drop_index_if_exists('RecvPackages_purged','get_top_apps');
     54call drop_index_if_exists('RecvPackages_purged','get_top_apps_others');
     55
     56DROP PROCEDURE IF EXISTS drop_index_if_exists;
     57DROP PROCEDURE IF EXISTS drop_primary_index_if_exists;
     58
     59# BACKUP & REMOVE DATA
    1060insert into RecvPackages_historical select * from RecvPackages where date < date_sub(date_sub(now(),interval 1 month),interval 1 year) on duplicate key update uuid=values(uuid);
    1161delete from RecvPackages where date < date_sub(date_sub(now(),interval 1 month),interval 1 year);
Note: See TracChangeset for help on using the changeset viewer.