Refering externe urls, hvormange
Pagecounts antal,
exclude robotter
Ignore case

url skal vre:
urlid
urltext
urlmd5

drop table timing;
create table timing (tim timestamp,why text);
insert into timing values('now'::timestamp, 'why');


commonlog format:

# Drligt
create view IPnames as
select IPID, IPname || '.' || IPdomain || '.' || IPcountry as IPText from IP
where not IPname = null and not IPname = ''
union
select IPID, IPdomain || '.' || IPcountry from IP
where not IPname = null and IPname = ''
union
select IPID, IPText from IP
where IPname = null;

# bedre:
create view IPnames as
map { if IPname null: IPText
      elseif IPname='': IPdomain || '.' || IPcountry
      else IPname || '.' || IPdomain || '.' || IPcountry
 } IPID



select H.HitID, Ho.HostText,
       IP.IPText, H.AuthUser, H.AuthName, H.HitDatetime,
       C.CustomerText, U.URLText, H.Status, H.Size,
	RH.HostText, RU.UrlText, H.TimeSpent
from IP	IP, Hit H, Customer C, URL U, Host Ho, Host RH, URL RU
 , HI hi
where H.ipid=ip.ipid 
  and H.URLID=U.URLID
  and H.CustomerID=C.CustomerID
  and H.HostID=Ho.HostID
  and H.RefererHostID=RH.HostID
  and H.RefererURLID=RU.URLID
  and HI.HitID=H.HitID

order by H.HitDateTime desc limit 1;

select H.HitID, Ho.HostText,
       IP.IPText, H.HitDatetime,
       C.CustomerText, U.URLText, H.Status, 
	RH.HostText, RU.UrlText, H.TimeSpent,
	M.MimeTypeText
from IP	IP, Hit H, Customer C, URL U, Host Ho, Host RH, URL RU, MimeType M
 , HI hi
where H.ipid=ip.ipid 
  and H.URLID=U.URLID and H.CustomerID=C.CustomerID
  and H.HostID=Ho.HostID and H.RefererHostID=RH.HostID
  and H.RefererURLID=RU.URLID and H.MimeTypeID=M.MimeTypeID
  and HI.HitID=H.HitID

order by H.HitDateTime desc limit 1;

----
DEBUG

select min(tim) as start, which
into temp timingstart
from timing
group by which

join

select TS.which, 
  PI.tim-start as preinsert,
  GM.tim-start as gotmime
into temp timingpreinsert
from timingstart TS, timing PI, timing GM
where PI.which = TS.which
and PI.why = 'preinsert'
and GM.which = TS.which
and GM.why = 'got mimIDs'
;


select which, tim-start as got_mime
into temp timingpreinsert
from timingstart TS, timing T
where T.which = TS.which;

