Revision 2bdc9dc4
b/pithos/tools/statscalculator.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