| /sun/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; |
| /sun/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'); |
| /sun/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; |
| /sun/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; |
| /sun/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; |
| /sun/TrafficCount/trunk/sql/db-update/CVS/Repository |
|---|
| 0,0 → 1,0 |
| TrafficCount/sql/db-update |
| /sun/TrafficCount/trunk/sql/db-update/CVS/Root |
|---|
| 0,0 → 1,0 |
| :pserver:dev@sun:/home/common/cvsroot |
| /sun/TrafficCount/trunk/sql/db-update/CVS/Entries |
|---|
| 0,0 → 1,3 |
| /update-1_0_0-1_1_0.sql/1.1/Wed Jan 7 22:25:31 2004// |
| /update-1_1_0-1_2_0.sql/1.1/Thu Jan 8 22:21:12 2004// |
| D |
| /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; |
| /sun/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; |
| /sun/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; |
| /sun/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; |
| /sun/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 |
| /sun/TrafficCount/trunk/sql/CVS/Repository |
|---|
| 0,0 → 1,0 |
| TrafficCount/sql |
| /sun/TrafficCount/trunk/sql/CVS/Root |
|---|
| 0,0 → 1,0 |
| :pserver:dev@sun:/home/common/cvsroot |
| /sun/TrafficCount/trunk/sql/CVS/Entries |
|---|
| 0,0 → 1,9 |
| D/db-update//// |
| /report_per_day.sql/1.1/Wed Jan 7 22:25:31 2004// |
| /report_per_month.sql/1.1/Wed Jan 7 22:25:31 2004// |
| /report_per_month2.sql/1.1/Wed Jan 7 22:25:31 2004// |
| /report_simple.sql/1.1/Wed Jan 7 22:25:31 2004// |
| /update_counter.sh/1.1/Wed Jan 7 22:25:31 2004// |
| /report.sql/1.2/Sat Jan 10 11:52:32 2004// |
| /report_per_day2.sql/1.2/Thu Jan 8 15:52:19 2004// |
| /tables_n_functions.sql/1.2/Thu Jan 8 15:49:52 2004// |