Revision de793207
b/pithos/tools/stats-calculator.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