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