Revision de793207
b/pithos/tools/statscalculator.sql  

1 
# Top level 

2 
create temporary table tmp_stats as select 0 as "level", 0 as "node", 0 as "parent", count(serial) as "population", sum(size) as "bytes", max(mtime) as "mtime", cluster, false as "final" from versions group by cluster; 

3  
4 
# Account level 

5 
insert into tmp_stats select 1 as "level", n.node, n.parent, count(v.serial) as "population", sum(v.size) as "bytes", max(v.mtime) as "mtime", cluster, false as "final" from versions v, nodes n where n.node=v.node and n.parent=0 and n.node!=0 group by node, cluster; 

6 
create temporary table tmp_nodes select distinct node, level from tmp_stats where level=1; 

7  
8 
# Container level 

9 
insert into tmp_stats select 2 as "level", n.node, n.parent, count(v.serial) as "population", sum(v.size) as "bytes", max(v.mtime) as "mtime", cluster, false as "final" from versions v, nodes n where n.node=v.node and n.parent in (select node from tmp_nodes where level=1) group by node, cluster; 

10 
insert into tmp_nodes select distinct node, level from tmp_stats where level=2; 

11  
12 
# Object level 

13 
insert into tmp_stats select 3 as "level", n.node, n.parent, count(v.serial) as "population", sum(v.size) as "bytes", max(v.mtime) as "mtime", cluster, false as "final" from versions v, nodes n where n.node=v.node and n.parent in (select node from tmp_nodes where level=2) group by node, cluster; 

14 
insert into tmp_nodes select distinct node, level from tmp_stats where level=3; 

15  
16 
# Update containers 

17 
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=3 group by parent, cluster; 

18 
insert into tmp_stats select 2 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; 

19 
drop table tmp_sums; 

20  
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; 

24 
drop table tmp_sums; 

25  
26 
# TODO: Fix population... 

27  
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; 

31 
drop table tmp_sums; 

32  
33 
# Clean up 

34 
drop table tmp_nodes; 

35 
delete from tmp_stats where final=false; 
Also available in: Unified diff