--------------------------------------------------------------------------------
begin;
create table acc_rstat (
id serial primary key,
val varchar not null unique
);
create table acc_method (
id serial primary key,
val varchar not null unique
);
create table acc_url (
id serial primary key,
val varchar not null unique
);
create table acc_ident (
id serial primary key,
val varchar not null unique
);
create table acc_hstat (
id serial primary key,
val varchar not null unique
);
create table acc_peer (
id serial primary key,
ip inet not null default ('0.0.0.0') unique
);
create table acc_client (
id serial primary key,
ip inet not null default ('0.0.0.0') unique
);
create table acc_mime (
id serial primary key,
val varchar not null unique
);
create table acc_log (
id bigserial not null primary key,
tstamp timestamptz not null default now(),
duration interval not null default ('0 s'),
client integer references acc_client(id) on update cascade,
rstatus integer references acc_rstat(id) on update cascade,
rcode smallint,
bytes bigint,
method integer references acc_method(id) on update cascade,
url integer references acc_url(id) on update cascade,
ident integer references acc_ident(id) on update cascade,
hstatus integer references acc_hstat(id) on update cascade,
peerhost integer references acc_peer(id) on update cascade,
mimetype integer references acc_mime(id) on update cascade
);
--------------------------------------------------------------------------------
create or replace function insert_rstat (varchar) returns integer as
'
declare
t_result integer;
begin
select into t_result id from acc_rstat where val = $1;
if t_result is null then
insert into acc_rstat (val) values ($1);
select into t_result id from acc_rstat where val = $1;
end if;
return t_result;
end;
' language 'plpgsql';
create or replace function insert_method (varchar) returns integer as
'
declare
t_result integer;
begin
select into t_result id from acc_method where val = $1;
if t_result is null then
insert into acc_method (val) values ($1);
select into t_result id from acc_method where val = $1;
end if;
return t_result;
end;
' language 'plpgsql';
create or replace function insert_url (varchar) returns integer as
'
declare
t_result integer;
begin
select into t_result id from acc_url where val = $1;
if t_result is null then
insert into acc_url (val) values ($1);
select into t_result id from acc_url where val = $1;
end if;
return t_result;
end;
' language 'plpgsql';
create or replace function insert_ident (varchar) returns integer as
'
declare
t_result integer;
begin
if (select id from acc_ident where val = $1) is null then
insert into acc_ident (val) values ($1);
end if;
select into t_result id from acc_ident where val = $1;
return t_result;
end;
' language 'plpgsql';
create or replace function insert_hstat (varchar) returns integer as
'
declare
t_result integer;
begin
select into t_result id from acc_hstat where val = $1;
if t_result is null then
insert into acc_hstat (val) values ($1);
select into t_result id from acc_hstat where val = $1;
end if;
return t_result;
end;
' language 'plpgsql';
create or replace function insert_mime (varchar) returns integer as
'
declare
t_result integer;
begin
select into t_result id from acc_mime where val = $1;
if t_result is null then
insert into acc_mime (val) values ($1);
select into t_result id from acc_mime where val = $1;
end if;
return t_result;
end;
' language 'plpgsql';
create or replace function insert_peer (inet) returns integer as
'
declare
t_result integer;
begin
select into t_result id from acc_peer where ip = $1;
if t_result is null then
insert into acc_peer (ip) values ($1);
select into t_result id from acc_peer where ip = $1;
end if;
return t_result;
end;
' language 'plpgsql';
create or replace function insert_client (inet) returns integer as
'
declare
t_result integer;
begin
select into t_result id from acc_client where ip = $1;
if t_result is null then
insert into acc_client (ip) values ($1);
select into t_result id from acc_client where ip = $1;
end if;
return t_result;
end;
' language 'plpgsql';
--------------------------------------------------------------------------------
create view v_acc_log as
select
acc_log.tstamp as tstamp,
acc_log.duration as duration,
acc_client.ip as client,
acc_rstat.val as rstatus,
acc_log.rcode as rcode,
acc_log.bytes as bytes,
acc_method.val as method,
acc_url.val as url,
acc_ident.val as ident,
acc_hstat.val as hstatus,
acc_peer.ip as peerhost,
acc_mime.val as mimetype
from
acc_log,
acc_client,
acc_rstat,
acc_method,
acc_url,
acc_ident,
acc_hstat,
acc_peer,
acc_mime
where
acc_log.client = acc_client.id and
acc_log.rstatus = acc_rstat.id and
acc_log.method = acc_method.id and
acc_log.url = acc_url.id and
acc_log.ident = acc_ident.id and
acc_log.hstatus = acc_hstat.id and
acc_log.peerhost = acc_peer.id and
acc_log.mimetype = acc_mime.id
;
create rule r_acc_log_ins as on insert to v_acc_log do instead
insert into acc_log values (
default,
new.tstamp,
new.duration,
(select insert_client(new.client)),
(select insert_rstat(new.rstatus)),
new.rcode,
new.bytes,
(select insert_method(new.method)),
(select insert_url(new.url)),
(select insert_ident(new.ident)),
(select insert_hstat(new.hstatus)),
(select insert_peer(new.peerhost)),
(select insert_mime(new.mimetype))
);
create view v_acc_user_bytes as
select
sum(acc_log.bytes) as total,
acc_ident.val as ident
from
acc_log,
acc_ident
where
acc_ident.id = acc_log.ident
group by
acc_ident.val
;
CREATE INDEX acc_log_tstamp_idx ON acc_log USING btree (tstamp);
CREATE INDEX acc_log_client_idx ON acc_log USING btree (client);
CREATE INDEX acc_log_rcode_idx ON acc_log USING btree (rcode);
CREATE INDEX acc_log_ident_idx ON acc_log USING btree (ident);
commit;