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