Show
Ignore:
Timestamp:
10/07/08 14:29:57 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1223389797 +0000
git-parent:

[1a8ddc4c1234e664bfd96ff9a8ef95d4b07079f2]

git-author:
Denish Patel <denish@omniti.com> 1223389797 +0000
Message:

pg ddl export

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/reconnoiter_ddl_dump.sql

    rfb7be7b r848c999  
    429429    rollup_time timestamp with time zone NOT NULL, 
    430430    count_rows integer, 
    431     avg_value numeric 
     431    avg_value numeric, 
     432    counter_dev numeric 
    432433); 
    433434 
     
    444445    rollup_time timestamp with time zone NOT NULL, 
    445446    count_rows integer, 
    446     avg_value numeric 
     447    avg_value numeric, 
     448    counter_dev numeric 
    447449); 
    448450 
     
    459461    rollup_time timestamp with time zone NOT NULL, 
    460462    count_rows integer, 
    461     avg_value numeric 
     463    avg_value numeric, 
     464    counter_dev numeric 
    462465); 
    463466 
     
    474477    rollup_time timestamp with time zone NOT NULL, 
    475478    count_rows integer, 
    476     avg_value numeric 
     479    avg_value numeric, 
     480    counter_dev numeric 
    477481); 
    478482 
     
    489493    rollup_time timestamp with time zone NOT NULL, 
    490494    count_rows integer, 
    491     avg_value numeric 
     495    avg_value numeric, 
     496    counter_dev numeric 
    492497); 
    493498 
     
    705710 
    706711CREATE FUNCTION trig_update_tsvector_saved_graphs() RETURNS trigger 
    707     AS $$ 
    708 DECLARE 
     712    AS $$DECLARE 
    709713 BEGIN 
    710714 IF TG_OP != 'INSERT' THEN 
    711    IF (NEW.graph_tags <> OLD.graph_tags) THEN 
     715   IF (NEW.graph_tags <> OLD.graph_tags OR NEW.title <> OLD.title) THEN 
    712716           UPDATE prism.saved_graphs SET ts_search_all=prism.saved_graphs_tsvector(NEW.graphid) where graphid=NEW.graphid; 
    713717   END IF;     
     
    14741478 
    14751479CREATE FUNCTION rollup_matrix_numeric_12hours() RETURNS void 
    1476     AS $$ 
    1477 DECLARE 
     1480    AS $$DECLARE 
    14781481  rec stratcon.rollup_matrix_numeric_12hours%rowtype; 
    14791482  v_sql TEXT; 
     
    15341537   
    15351538    FOR rec IN  
    1536                 SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 
     1539                SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     1540                       (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 
    15371541         FROM stratcon.rollup_matrix_numeric_6hours 
    15381542           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval 
     
    15421546        
    15431547          INSERT INTO stratcon.rollup_matrix_numeric_12hours 
    1544           (sid,name,rollup_time,count_rows,avg_value) VALUES 
    1545           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
     1548          (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 
     1549          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 
    15461550           
    15471551     END LOOP; 
     
    15761580 
    15771581CREATE FUNCTION rollup_matrix_numeric_20m() RETURNS void 
    1578     AS $$ 
    1579 DECLARE 
     1582    AS $$DECLARE 
    15801583  
    15811584 rec stratcon.rollup_matrix_numeric_20m%rowtype; 
     
    16371640 FOR rec IN  
    16381641                SELECT sid , name,v_min_whence as rollup_time, 
    1639                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 
     1642                       SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     1643                       (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 
    16401644       FROM stratcon.rollup_matrix_numeric_5m 
    16411645                      WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval 
     
    16461650         
    16471651        INSERT INTO stratcon.rollup_matrix_numeric_20m 
    1648          (sid,name,rollup_time,count_rows,avg_value) VALUES  
    1649          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
     1652         (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES  
     1653         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 
    16501654         
    16511655   END LOOP; 
     
    16821686 
    16831687CREATE FUNCTION rollup_matrix_numeric_5m() RETURNS void 
    1684     AS $$ 
    1685 DECLARE 
     1688    AS $$DECLARE 
    16861689  
    16871690 rec stratcon.rollup_matrix_numeric_5m%rowtype; 
     
    17411744 END IF; 
    17421745 
    1743  FOR rec IN  
    1744                 SELECT sid , name,v_min_whence as rollup_time, 
    1745                       COUNT(1) as count_rows ,AVG(value) as avg_value 
    1746                       FROM stratcon.loading_dock_metric_numeric_s 
    1747                       WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval 
    1748                 GROUP BY rollup_time,sid,name 
     1746 FOR rec IN 
     1747 
     1748    select n.sid, n.name, n.rollup_time, n.count_rows, n.avg_value, 
     1749           case when n.avg_value - l.avg_value >= 0 
     1750                then (n.avg_value - l.avg_value)/300.0 
     1751                else null end as counter_dev 
     1752      from (SELECT sid, name, v_min_whence as rollup_time, 
     1753                   COUNT(1) as count_rows, avg(value) as avg_value 
     1754              FROM stratcon.loading_dock_metric_numeric_s 
     1755             WHERE whence <= v_min_whence AND whence > v_min_whence -'5 minutes'::interval 
     1756          GROUP BY rollup_time,sid,name) as n 
     1757 left join stratcon.rollup_matrix_numeric_5m as l 
     1758        on (n.sid=l.sid and n.name=l.name and 
     1759            n.rollup_time - '5 minute'::interval = l.rollup_time) 
    17491760  
    17501761       LOOP 
     
    17521763         
    17531764        INSERT INTO stratcon.rollup_matrix_numeric_5m 
    1754          (sid,name,rollup_time,count_rows,avg_value) VALUES  
    1755          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
     1765         (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES  
     1766         (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 
    17561767         
    17571768   END LOOP; 
     
    17881799 
    17891800CREATE FUNCTION rollup_matrix_numeric_60m() RETURNS void 
    1790     AS $$ 
    1791 DECLARE 
     1801    AS $$DECLARE 
    17921802  rec stratcon.rollup_matrix_numeric_60m%rowtype; 
    17931803  v_sql TEXT; 
     
    18471857   
    18481858    FOR rec IN  
    1849                 SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 
     1859                SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     1860                       (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 
    18501861         FROM stratcon.rollup_matrix_numeric_20m 
    18511862           WHERE date_hour(rollup_time)= v_min_whence 
     
    18541865       
    18551866          INSERT INTO stratcon.rollup_matrix_numeric_60m 
    1856           (sid,name,rollup_time,count_rows,avg_value) VALUES 
    1857           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
     1867          (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 
     1868          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 
    18581869           
    18591870     END LOOP; 
     
    18881899 
    18891900CREATE FUNCTION rollup_matrix_numeric_6hours() RETURNS void 
    1890     AS $$ 
    1891 DECLARE 
     1901    AS $$DECLARE 
    18921902  rec stratcon.rollup_matrix_numeric_6hours%rowtype; 
    18931903  v_sql TEXT; 
     
    19471957   
    19481958    FOR rec IN  
    1949                 SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value 
     1959                SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value, 
     1960                       (SUM(counter_dev*count_rows)/SUM(count_rows)) as counter_dev 
    19501961         FROM stratcon.rollup_matrix_numeric_60m 
    19511962           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval 
     
    19551966        
    19561967          INSERT INTO stratcon.rollup_matrix_numeric_6hours 
    1957           (sid,name,rollup_time,count_rows,avg_value) VALUES 
    1958           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value); 
     1968          (sid,name,rollup_time,count_rows,avg_value,counter_dev) VALUES 
     1969          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.counter_dev); 
    19591970           
    19601971     END LOOP;