Subversion Repositories general

Compare Revisions

No changes between revisions

Ignore whitespace Rev 41 → Rev 42

/sun/TrafficCount/trunk/sql/db-update/update-1_1_0-1_2_0.sql
0,0 → 1,127
--
-- TraficCounter
-- Copyleft: Anatoli Klassen (anatoli@aksoft.net)
--
-- SQL script for PostgeSQL
--
-- Update from 1.1.0 to 1.2.0
--
 
update tversion set major=1, minor=2, build=0;
 
drop function insert_count(varchar(100), numeric(20,0));
drop function insert_count_stage2(
varchar(100), numeric(20,0), numeric(20,0), numeric(20,0));
 
CREATE TABLE tdays (
source varchar(100) not null,
day DATE not null,
beginvalue numeric(20,0) not null,
endvalue numeric(20,0) not null,
stamp timestamp not null,
 
CONSTRAINT cdays_source FOREIGN KEY (source) REFERENCES tsources(name)
);
create index idays_main on tdays using btree (source, day);
create index idays_general on tdays using btree (source);
 
insert into tdays (source, day, beginvalue, endvalue, stamp)
select c.source, c.day,
(select COALESCE(max(c2.value), 0) from tcount c2
where c2.source = c.source and c2.stamp < c.day) as beginvalue,
(select max(c2.value) from tcount c2
where c2.source = c.source and date_trunc('day', c2.stamp) = c.day) as endvalue,
CURRENT_TIMESTAMP
from
(select distinct source, date_trunc('day', stamp) as day from tcount) c
;
 
drop table tcount;
 
--
-- Create a new day counter.
-- Intern use only
--
-- @param 1 source name
-- @param 2 absolute value of the counter
--
CREATE FUNCTION insert_counter_stage4(varchar(100), numeric(20,0))
RETURNS integer AS '
insert into tdays (source, day, beginvalue, endvalue, stamp)
values ($1, CURRENT_DATE,
(select COALESCE(max(endvalue), $2) from tdays
where source = $1 and day = (CURRENT_DATE - interval \'1 day\')),
$2, CURRENT_TIMESTAMP);
 
select 1;
' LANGUAGE SQL;
 
 
--
-- Update day counter.
-- Intern use only
--
-- @param 1 source name
-- @param 2 absolute value of the counter
--
CREATE FUNCTION update_counter_stage4(varchar(100), numeric(20,0))
RETURNS integer AS '
update tdays set endvalue = $2, stamp = CURRENT_TIMESTAMP
where source = $1 and day = CURRENT_DATE;
 
select 1;
' LANGUAGE SQL;
 
--
-- Update internal counters.
-- Intern use only
--
-- @param 1 source name
-- @param 2 value of the counter
-- @param 3 value of recalculated base
--
CREATE FUNCTION update_counter_stage3(
varchar(100), numeric(20,0), numeric(20,0))
RETURNS integer AS '
select (case
when (select count(*) from tdays
where day = CURRENT_DATE and source = $1) = 0
then insert_counter_stage4($1, $2 + $3)
else update_counter_stage4($1, $2 + $3)
end
);
 
update tsources set base = $3, delta = $2, countstamp = CURRENT_TIMESTAMP
where name = $1;
 
select 1;
' LANGUAGE SQL;
 
--
-- Update internal counters, calculate real counter value.
-- Intern use only
--
-- @param 1 source name
-- @param 2 value of the counter
-- @param 3 value of base
-- @param 4 value of delta
--
CREATE FUNCTION update_counter_stage2(
varchar(100), numeric(20,0), numeric(20,0), numeric(20,0))
RETURNS integer AS '
select update_counter_stage3($1, $2,
(select (case when $2 >= $4 then $3 else ($3 + $4) end)));
' LANGUAGE SQL;
 
--
-- Update current day counter for given source. Public
--
-- @param 1 source name
-- @param 2 value of the counter
--
CREATE FUNCTION update_counter(varchar(100), numeric(20,0))
RETURNS integer AS '
select update_counter_stage2($1, $2,
(select base from tsources where name = $1),
(select delta from tsources where name = $1));
' LANGUAGE SQL;
Property changes:
Added: svn:keywords
+LastChangedDate LastChangedRevision LastChangedBy HeadURL Id
\ No newline at end of property
/sun/TrafficCount/trunk/sql/report.sql
1,19 → 1,4
select host, source, round((base + delta) / 1024 / 1024) as count, stamp from tcount
where source = 'tun0_out'
and stamp in (
(
select min(stamp) from tcount
where source = 'tun0_out'
and extract(month from stamp) = 8
and extract(year from stamp) = 2003
group by extract(day from stamp)
)
union
(
select min(stamp) from tcount
where source = 'tun0_out'
and extract(month from stamp) = 9
and extract(year from stamp) = 2003
)
)
order by stamp
select source, day, round((endvalue-beginvalue) / 1024 / 1024) as count
from tdays
where date_trunc('month', day) = '1 Jan 2004'
order by source, day;
/sun/TrafficCount/trunk/sql/tables_n_functions.sql
4,7 → 4,7
--
-- SQL script for PostgeSQL
--
-- Version 1.1.0
-- Version 1.2.0
--
 
 
18,7 → 18,7
minor integer not null,
build integer not null
);
insert into tversion (major, minor, build) values (1, 1, 0);
insert into tversion (major, minor, build) values (1, 2, 0);
 
CREATE TABLE thosts (
name varchar(100) constraint chost_name primary key,
36,44 → 36,76
CONSTRAINT csources_host FOREIGN KEY (host) REFERENCES thosts(name)
);
 
CREATE TABLE tcount (
CREATE TABLE tdays (
source varchar(100) not null,
value numeric(20,0) not null,
day DATE not null,
beginvalue numeric(20,0) not null,
endvalue numeric(20,0) not null,
stamp timestamp not null,
 
CONSTRAINT ccount_source FOREIGN KEY (source) REFERENCES tsources(name)
CONSTRAINT cdays_source FOREIGN KEY (source) REFERENCES tsources(name)
);
create index tcount_main on tcount using btree (source, stamp);
create index tcount_general on tcount using btree (source);
create index idays_main on tdays using btree (source, day);
create index idays_general on tdays using btree (source);
 
 
-- ****************************************************************************
-- * Functions *
-- ****************************************************************************
 
--
-- Insert a given base and delta into the traffic counter.
-- Create a new day counter.
-- Intern use only
--
-- @param 1 source name
-- @param 2 absolute value of the counter
--
CREATE FUNCTION insert_counter_stage4(varchar(100), numeric(20,0))
RETURNS integer AS '
insert into tdays (source, day, beginvalue, endvalue, stamp)
values ($1, CURRENT_DATE,
(select COALESCE(max(endvalue), $2) from tdays
where source = $1 and day = (CURRENT_DATE - interval \'1 day\')),
$2, CURRENT_TIMESTAMP);
 
select 1;
' LANGUAGE SQL;
 
 
--
-- Update day counter.
-- Intern use only
--
-- @param 1 source name
-- @param 2 absolute value of the counter
--
CREATE FUNCTION update_counter_stage4(varchar(100), numeric(20,0))
RETURNS integer AS '
update tdays set endvalue = $2, stamp = CURRENT_TIMESTAMP
where source = $1 and day = CURRENT_DATE;
 
select 1;
' LANGUAGE SQL;
 
--
-- Update internal counters.
-- Intern use only
--
-- @param 1 source name
-- @param 2 value of the counter
-- @param 3 value of base
-- @param 4 value of delta
-- @param 3 value of recalculated base
--
CREATE FUNCTION insert_count_stage2(
varchar(100), numeric(20,0), numeric(20,0), numeric(20,0))
CREATE FUNCTION update_counter_stage3(
varchar(100), numeric(20,0), numeric(20,0))
RETURNS integer AS '
insert into tcount (source, value, stamp)
values (
$1,
(select (case when $2 >= $4 then $3 else ($3 + $4) end)) + $2,
CURRENT_TIMESTAMP
select (case
when (select count(*) from tdays
where day = CURRENT_DATE and source = $1) = 0
then insert_counter_stage4($1, $2 + $3)
else update_counter_stage4($1, $2 + $3)
end
);
 
update tsources set
base = (select (case when $2 >= $4 then $3 else ($3 + $4) end)),
delta = $2,
countstamp = CURRENT_TIMESTAMP
update tsources set base = $3, delta = $2, countstamp = CURRENT_TIMESTAMP
where name = $1;
 
select 1;
80,20 → 112,35
' LANGUAGE SQL;
 
--
-- Insert a new data into the traffic counter. Public
-- Update internal counters, calculate real counter value.
-- Intern use only
--
-- @param 1 source name
-- @param 2 value of the counter
-- @param 3 value of base
-- @param 4 value of delta
--
CREATE FUNCTION insert_count(varchar(100), numeric(20,0))
CREATE FUNCTION update_counter_stage2(
varchar(100), numeric(20,0), numeric(20,0), numeric(20,0))
RETURNS integer AS '
select insert_count_stage2($1, $2,
select update_counter_stage3($1, $2,
(select (case when $2 >= $4 then $3 else ($3 + $4) end)));
' LANGUAGE SQL;
 
--
-- Update current day counter for given source. Public
--
-- @param 1 source name
-- @param 2 value of the counter
--
CREATE FUNCTION update_counter(varchar(100), numeric(20,0))
RETURNS integer AS '
select update_counter_stage2($1, $2,
(select base from tsources where name = $1),
(select delta from tsources where name = $1));
' LANGUAGE SQL;
 
 
 
-- ****************************************************************************
-- * Examples *
-- ****************************************************************************
101,12 → 148,3
-- New host
-- insert into thosts (name, description) values ('localhost', '');
 
-- Sources
-- insert into tsources (name, description)
-- values ('tun0_in', 'Input Internet Traffic');
-- insert into tsources (name, description)
-- values ('tun0_out', 'Output Internet Traffic');
 
-- Insert new counters' values:
-- select insert_count('localhost', 'tun0_in', 10);
-- select insert_count('localhost', 'tun0_out', 20);
/sun/TrafficCount/trunk/sql/report_per_day2.sql
35,41 → 35,41
union select 31 as day
) as d
left join tcount c2
on c2.source = 'fw_in'
on c2.source = 'tun0_in'
and c2.stamp = (
select max(stamp) as max_stamp
from tcount c1
where c1.source = 'fw_in'
where c1.source = 'tun0_in'
and c1.stamp >= date(d.day || ' Jan 2004')
and c1.stamp < date(d.day || ' Jan 2004') + interval '1 day'
group by date_trunc('day', c1.stamp)
)
left join tcount c3
on c3.source = 'fw_in'
on c3.source = 'tun0_in'
and c3.stamp = (
select max(stamp) as max_stamp
from tcount c4
where c4.source = 'fw_in'
where c4.source = 'tun0_in'
and c4.stamp >= date(d.day || ' Jan 2004') - interval '1 day'
and c4.stamp < date(d.day || ' Jan 2004')
group by date_trunc('day', c4.stamp)
)
left join tcount d2
on d2.source = 'fw_out'
on d2.source = 'tun0_out'
and d2.stamp = (
select max(stamp) as max_stamp
from tcount d1
where d1.source = 'fw_out'
where d1.source = 'tun0_out'
and d1.stamp >= date(d.day || ' Jan 2004')
and d1.stamp < date(d.day || ' Jan 2004') + interval '1 day'
group by date_trunc('day', d1.stamp)
)
left join tcount d3
on d3.source = 'fw_out'
on d3.source = 'tun0_out'
and d3.stamp = (
select max(stamp) as max_stamp
from tcount d4
where d4.source = 'fw_out'
where d4.source = 'tun0_out'
and d4.stamp >= date(d.day || ' Jan 2004') - interval '1 day'
and d4.stamp < date(d.day || ' Jan 2004')
group by date_trunc('day', d4.stamp)