Revision 2bdc9dc4 pithos/tools/stats-calculator.sql

b/pithos/tools/stats-calculator.sql
19 19 
drop table tmp_sums;
20 20 

  		





  21
  21
  
    
# Update accounts


  		





  22
  
  
    
create table tmp_sums as select parent as "node", sum(population) as "population", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=2 group by parent, cluster;


  		





  23
  
  
    
insert into tmp_stats select 1 as "level", n.node, n.parent, t.population, t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t, nodes n where n.node=t.node;


  		





  
  22
  
    
create table tmp_sums as select parent as "node", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=2 group by parent, cluster;


  		





  
  23
  
    
create table tmp_population as select parent as "node", sum(population) as "population", cluster from tmp_stats where level=2 and final=false group by parent, cluster;


  		





  
  24
  
    
insert into tmp_stats select 1 as "level", t.node, 0 as "parent", IFNULL(p.population, 0) as "population", t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t left join tmp_population p on p.node=t.node and p.cluster=t.cluster;


  		





  24
  25
  
    
drop table tmp_sums;


  		





  25
  
  
    

  		





  26
  
  
    
# TODO: Fix population...


  		





  
  26
  
    
drop table tmp_population;


  		





  27
  27
  
    

  		





  28
  28
  
    
# Update top level


  		





  29
  
  
    
create table tmp_sums as select parent as "node", sum(population) as "population", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=1 group by parent, cluster;


  		





  30
  
  
    
insert into tmp_stats select 0 as "level", n.node, n.parent, t.population, t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t, nodes n where n.node=t.node;


  		





  
  29
  
    
create table tmp_sums as select parent as "node", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=1 group by parent, cluster;


  		





  
  30
  
    
create table tmp_population as select parent as "node", sum(population) as "population", cluster from tmp_stats where level=1 and final=false group by parent, cluster;


  		





  
  31
  
    
insert into tmp_stats select 0 as "level", t.node, 0 as "parent", IFNULL(p.population, 0) as "population", t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t left join tmp_population p on p.node=t.node and p.cluster=t.cluster;


  		





  31
  32
  
    
drop table tmp_sums;


  		





  
  33
  
    
drop table tmp_population;


  		





  32
  34
  
    

  		





  33
  35
  
    
# Clean up


  		





  34
  36
  
    
drop table tmp_nodes;


  		





  35
  
  
    
delete from tmp_stats where final=false;


  		





  
  37
  
    
delete from tmp_stats where final=false;


  		












Also available in:
  Unified diff