| 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'); |
| |