/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; |
/TrafficCount/trunk/sql/db-update/update-1_0_0-1_1_0.sql |
---|
0,0 → 1,110 |
-- |
-- TraficCounter |
-- Copyleft: Anatoli Klassen (anatoli@aksoft.net) |
-- |
-- SQL script for PostgeSQL |
-- |
-- Update from 1.0.0 to 1.1.0 |
-- |
CREATE TABLE tversion ( |
major integer not null, |
minor integer not null, |
build integer not null |
); |
insert into tversion (major, minor, build) values (1, 1, 0); |
drop function insert_count( |
character varying, character varying, numeric); |
drop function insert_count_stage2( |
character varying, character varying, numeric, timestamp without time zone); |
drop function insert_count_stage3( |
character varying, character varying, numeric, numeric, numeric); |
alter table tsources add column host varchar(100); |
update tsources set host = (select max(name) from thosts); |
alter table tsources alter column host set not null; |
alter table tsources add column base numeric(20,0); |
update tsources |
set base = (select base from tcount |
where source = tsources.name |
order by stamp desc limit 1); |
alter table tsources alter column base set not null; |
alter table tsources alter column base set default 0; |
alter table tsources add column delta numeric(20,0); |
update tsources |
set delta = (select delta from tcount |
where source = tsources.name |
order by stamp desc limit 1); |
alter table tsources alter column delta set not null; |
alter table tsources alter column delta set default 0; |
alter table tsources add column countstamp timestamp; |
update tsources set countstamp = CURRENT_TIMESTAMP; |
alter table tsources alter column countstamp set not null; |
alter table tsources alter column countstamp set default CURRENT_TIMESTAMP; |
alter table tsources add |
CONSTRAINT csources_host FOREIGN KEY (host) REFERENCES thosts(name); |
drop index tcount_main; |
drop index tcount_general; |
alter table tcount drop column host; |
alter table tcount add column value numeric(20,0); |
update tcount set value = base + delta; |
alter table tcount alter column value set not null; |
alter table tcount drop column base; |
alter table tcount drop column delta; |
create index tcount_main on tcount using btree (source, stamp); |
create index tcount_general on tcount using btree (source); |
-- **************************************************************************** |
-- * Functions * |
-- **************************************************************************** |
-- |
-- Insert a given base and delta into the traffic counter. |
-- 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_stage2( |
varchar(100), numeric(20,0), 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 |
); |
update tsources set |
base = (select (case when $2 >= $4 then $3 else ($3 + $4) end)), |
delta = $2, |
countstamp = CURRENT_TIMESTAMP |
where name = $1; |
select 1; |
' LANGUAGE SQL; |
-- |
-- Insert a new data into the traffic counter. Public |
-- |
-- @param 1 source name |
-- @param 2 value of the counter |
-- |
CREATE FUNCTION insert_count(varchar(100), numeric(20,0)) |
RETURNS integer AS ' |
select insert_count_stage2($1, $2, |
(select base from tsources where name = $1), |
(select delta from tsources where name = $1)); |
' LANGUAGE SQL; |
/TrafficCount/trunk/sql/report_per_day.sql |
---|
0,0 → 1,27 |
select (date (day || ' Sep 2003')) as the_date, |
round(( |
( |
select base + delta from tcount |
where source = 'tun0_in' |
and stamp = ( |
select max(stamp) from tcount |
where date_trunc('day', stamp) = date (day || ' Sep 2003') |
and source = 'tun0_in' |
) |
) |
- |
( |
select base + delta from tcount |
where source = 'tun0_in' |
and stamp = ( |
select max(stamp) from tcount |
where date_trunc('day', stamp) = date (day || ' Sep 2003') - interval '1 day' |
and source = 'tun0_in' |
) |
) |
) / 1024 / 1024) |
as count |
from tdays d |
where day <= 30 |
order by the_date; |
/TrafficCount/trunk/sql/tables_n_functions.sql |
---|
0,0 → 1,154 |
-- |
-- TraficCounter |
-- Copyleft: Anatoli Klassen (anatoli@aksoft.net) |
-- |
-- SQL script for PostgeSQL |
-- |
-- Version 1.2.0 |
-- |
-- **************************************************************************** |
-- * Tables * |
-- **************************************************************************** |
-- just one record with currect database structure version |
CREATE TABLE tversion ( |
major integer not null, |
minor integer not null, |
build integer not null |
); |
insert into tversion (major, minor, build) values (1, 2, 0); |
CREATE TABLE thosts ( |
name varchar(100) constraint chost_name primary key, |
description text |
); |
CREATE TABLE tsources ( |
host varchar(100) not null, |
name varchar(100) constraint csource_name primary key, |
base numeric(20,0) not null default 0, |
delta numeric(20,0) not null default 0, |
countstamp timestamp not null default CURRENT_TIMESTAMP, |
description text, |
CONSTRAINT csources_host FOREIGN KEY (host) REFERENCES thosts(name) |
); |
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); |
-- **************************************************************************** |
-- * Functions * |
-- **************************************************************************** |
-- |
-- 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; |
-- **************************************************************************** |
-- * Examples * |
-- **************************************************************************** |
-- New host |
-- insert into thosts (name, description) values ('localhost', ''); |
-- Sources |
-- insert into tsources (host, name) values ('localhost', 'fw_in'); |
-- insert into tsources (host, name) values ('localhost', 'fw_out'); |
/TrafficCount/trunk/sql/report_per_day2.sql |
---|
0,0 → 1,77 |
select |
d.day, |
round((c2.value - c3.value + d2.value - d3.value) / 1024 / 1024) as count |
from |
( select 1 as day |
union select 2 as day |
union select 3 as day |
union select 4 as day |
union select 5 as day |
union select 6 as day |
union select 7 as day |
union select 8 as day |
union select 9 as day |
union select 10 as day |
union select 11 as day |
union select 12 as day |
union select 13 as day |
union select 14 as day |
union select 15 as day |
union select 16 as day |
union select 17 as day |
union select 18 as day |
union select 19 as day |
union select 20 as day |
union select 21 as day |
union select 22 as day |
union select 23 as day |
union select 24 as day |
union select 25 as day |
union select 26 as day |
union select 27 as day |
union select 28 as day |
union select 29 as day |
union select 30 as day |
union select 31 as day |
) as d |
left join tcount c2 |
on c2.source = 'tun0_in' |
and c2.stamp = ( |
select max(stamp) as max_stamp |
from tcount c1 |
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 = 'tun0_in' |
and c3.stamp = ( |
select max(stamp) as max_stamp |
from tcount c4 |
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 = 'tun0_out' |
and d2.stamp = ( |
select max(stamp) as max_stamp |
from tcount d1 |
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 = 'tun0_out' |
and d3.stamp = ( |
select max(stamp) as max_stamp |
from tcount d4 |
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) |
) |
order by d.day; |
/TrafficCount/trunk/sql/report_simple.sql |
---|
0,0 → 1,29 |
select extract(month from stamp) as month, round((base + delta) / 1024 / 1024) |
from tcount |
where source = 'tun0_out' |
and host = 'sun' |
and stamp in ( |
select max(stamp) from tcount |
where source = 'tun0_out' |
and host = 'sun' |
and extract(year from stamp) = 2003 |
group by extract(month from stamp) |
) |
order by month; |
select extract(day from stamp) as day, round((base + delta) / 1024 / 1024) |
from tcount |
where source = 'tun0_out' |
and host = 'sun' |
and stamp in ( |
select max(stamp) from tcount |
where source = 'tun0_out' |
and host = 'sun' |
and extract(year from stamp) = 2003 |
and extract(month from stamp) = 8 |
group by extract(day from stamp) |
) |
order by day; |
/TrafficCount/trunk/sql/report_per_month.sql |
---|
0,0 → 1,31 |
select (date ('2003-' || month || '-01')) as the_date, |
round(( |
( |
select base + delta from tcount |
where source = 'tun0_out' |
and host = 'sun' |
and stamp = ( |
select max(stamp) from tcount |
where date_trunc('month', stamp) = date_trunc('month', date ('2003-' || month || '-01')) |
and source = 'tun0_out' |
and host = 'sun' |
) |
) |
- |
COALESCE(( |
select base + delta from tcount |
where source = 'tun0_out' |
and host = 'sun' |
and stamp = ( |
select max(stamp) from tcount |
where date_trunc('month', stamp) |
= date_trunc('month', date ('2003-' || month || '-01')) - interval '1 month' |
and source = 'tun0_out' |
and host = 'sun' |
) |
), 0) |
) / 1024 / 1024) |
as count |
from tmonths m |
order by the_date; |
/TrafficCount/trunk/sql/report_per_month2.sql |
---|
0,0 → 1,69 |
select |
d.month, |
round(( |
(c2.base+c2.delta) - (c3.base+c3.delta) |
+ (d2.base+d2.delta) - (d3.base+d3.delta) |
) / 1024 / 1024) as count |
from |
( select 1 as month |
union select 2 as month |
union select 3 as month |
union select 4 as month |
union select 5 as month |
union select 6 as month |
union select 7 as month |
union select 8 as month |
union select 9 as month |
union select 10 as month |
union select 11 as month |
union select 12 as month |
) as d |
left join tcount c2 |
on c2.source = 'fw_in' |
and c2.host = 'mercury' |
and c2.stamp = ( |
select max(stamp) as max_stamp |
from tcount c1 |
where c1.source = 'fw_in' |
and c1.host = 'mercury' |
and c1.stamp >= date(d.month || '/1/2003') |
and c1.stamp < date(d.month || '/1/2003') + interval '1 month' |
group by date_trunc('month', c1.stamp) |
) |
left join tcount c3 |
on c3.source = 'fw_in' |
and c3.host = 'mercury' |
and c3.stamp = ( |
select max(stamp) as max_stamp |
from tcount c4 |
where c4.source = 'fw_in' |
and c4.host = 'mercury' |
and c4.stamp >= date(d.month || '/1/2003') - interval '1 month' |
and c4.stamp < date(d.month || '/1/2003') |
group by date_trunc('month', c4.stamp) |
) |
left join tcount d2 |
on d2.source = 'fw_out' |
and d2.host = 'mercury' |
and d2.stamp = ( |
select max(stamp) as max_stamp |
from tcount d1 |
where d1.source = 'fw_out' |
and d1.host = 'mercury' |
and d1.stamp >= date(d.month || '/1/2003') |
and d1.stamp < date(d.month || '/1/2003') + interval '1 month' |
group by date_trunc('month', d1.stamp) |
) |
left join tcount d3 |
on d3.source = 'fw_out' |
and d3.host = 'mercury' |
and d3.stamp = ( |
select max(stamp) as max_stamp |
from tcount d4 |
where d4.source = 'fw_out' |
and d4.host = 'mercury' |
and d4.stamp >= date(d.month || '/1/2003') - interval '1 month' |
and d4.stamp < date(d.month || '/1/2003') |
group by date_trunc('month', d4.stamp) |
) |
order by d.month; |
/TrafficCount/trunk/sql/report.sql |
---|
0,0 → 1,11 |
-- |
-- total traffic report for mercury |
-- |
select d1.source, d2.source, d1.day, |
round((d1.endvalue-d1.beginvalue+d2.endvalue-d2.beginvalue) / 1024 / 1024) as count |
from tdays d1 |
left join tdays d2 on (d2.day = d1.day and d2.source='fw_out') |
where d1.day >= '1 Jan 2004' |
and d1.source = 'fw_in' |
order by d1.source, d1.day; |
/TrafficCount/trunk/sql/update_counter.sh |
---|
0,0 → 1,25 |
#!/bin/sh |
# Settings |
#DB_HOST='localhost' |
#DB_PORT='5432' |
DB_NAME='trafficcount' |
DB_USER='dbadmin' |
DB_PASSWORD='' |
DB_COMMAND='/usr/local/bin/psql' |
# Form DB command |
COMMAND="$DB_COMMAND" |
if test -n "$DB_HOST"; then COMMAND="$COMMAND -h $DB_HOST"; fi |
if test -n "$DB_PORT"; then COMMAND="$COMMAND -p $DB_PORT"; fi |
if test -n "$DB_USER"; then COMMAND="$COMMAND -U $DB_USER"; fi |
if test -n "$DB_PASSWORD"; then COMMAND="$COMMAND -W $DB_PASSWORD"; fi |
# Get values & update |
SOURCE='tun0_in' |
VALUE=`netstat -I tun0 -b | grep '<Link#' | awk '{print $6}'` |
$COMMAND $DB_NAME -c "select insert_count('$SOURCE', $VALUE);" > /dev/null |
SOURCE='tun0_out' |
VALUE=`netstat -I tun0 -b | grep '<Link#' | awk '{print $9}'` |
$COMMAND $DB_NAME -c "select insert_count('$SOURCE', $VALUE);" > /dev/null |
Property changes: |
Added: svn:executable |