-- ----------------------------------------------
--
-- Nimsoft QOS_Views SQL Script for MySQL Databases
--
-- Version:	1.1
-- Date	26 Nov 2012
--
-- Version history:
-- 1.0	Cash Shurley - This is the first release, based on QOS Views for MS SQL
-- 1.1	Andreas Schessner - handling special chars in QOS object names

-- Description:
-- This sql procedure generates easy-to-use views in the NIS for accessing raw qos data.
-- The Script creates views named V_qosname, for example V_QOS_CPU_USAGE.
--
-- **** Please note that this is not supported by Nimsoft Support. This Script is intended to 
-- be used as is, feel free to modify for your own purpose. ****
--
-- Install Notes:
--
-- Run the script in a SQL Client and it will create the procedure.  
-- Then, exectute the following query in an SQL Client: call qosviews
-- This will create views based on the current contents of S_QOS_DATA 
--
-- If new qos types are add added to the S_QOS_DATA table then "call qosviews" will need to be rerun.
--
-- Usage Examples:
-- When you want to see the raw CPU data for a server called Apollo, use a query like this:
--
-- SELECT * FROM V_QOS_CPU_USAGE
-- WHERE source='Apollo'
--
-- To get TOP 20 the average, minimum and maximum values measured in the past hour, use this:
--
-- select TOP 20 source, AVG(samplevalue) as avg ,MIN(samplevalue) as min,MAX(samplevalue) as max
-- from V_QOS_CPU_USAGE
-- where sampletime > NOW() - INTERVAL 1 HOUR
-- order by avg desc
--
-- ----------------------------------------------

DELIMITER //
drop procedure if exists qosviews//

CREATE PROCEDURE qosviews()
READS SQL DATA
BEGIN
    DECLARE done BOOLEAN;
    DECLARE vqos char(255);
    DECLARE vr_table, vview char(255);
    DECLARE vcview char(255);
    DECLARE qos_cursor CURSOR FOR SELECT DISTINCT qos,r_table FROM S_QOS_DATA ORDER BY 2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN qos_cursor;

-- Begin read_loop

read_loop: LOOP FETCH qos_cursor INTO vqos, vr_table;

    IF done THEN
      CLOSE qos_cursor;
      LEAVE read_loop;
    END IF;

-- Create view query
    set vview = CONCAT('V_',vqos);
    
-- Replace the '-' character with '_' if exist
    set vview = REPLACE(vview, '-', '_');

-- Replace the '/' character with '_' if exist
    set vview = REPLACE(vview, '/', '_');

-- Replace the '(' character with '_' if exist
    set vview = REPLACE(vview, '(', '_');

-- Replace the ')' character with '_' if exist
    set vview = REPLACE(vview, ')', '_');

-- Check QOS name for ':' character & remove it if exist
    set vview = REPLACE(vview, '%', '');
	
-- Check QOS name for ':' character & remove it if exist
    set vview = REPLACE(vview, ':', '');
	
-- Check QOS name for '.' character & remove it if exist
    set vview = REPLACE(vview, '.', '');	


-- Add samplemax field if existing for in focus QoS

    IF EXISTS (SELECT 1 from information_schema.`COLUMNS` where table_name = CONVERT(vr_table using utf8) and column_name = 'samplemax')    THEN 
    set @query = CONCAT('CREATE OR REPLACE VIEW ' ,vview, ' AS SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplemax,D.samplestdev,D.samplerate,D.tz_offset FROM S_QOS_DATA Q JOIN ' ,vr_table, ' D ON Q.table_id=D.table_id') ;
    ELSE
    set @query = CONCAT('CREATE OR REPLACE VIEW ' ,vview, ' AS SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.tz_offset FROM S_QOS_DATA Q JOIN ' ,vr_table, ' D ON Q.table_id=D.table_id');  
    END IF;
     
-- Execute create view query


select @query;
PREPARE stmt from @query; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 
  


-- This is executed as long as the previous fetch succeeds.


END LOOP read_loop;

END//

DELIMITER ;