-- file: create.sql
-- Lifestat

drop sequence Hit_HitID_seq;
-- create sequence Hit_HitID_seq; -- Done automatically

drop sequence Hit_SessionID_seq;
create sequence Hit_SessionID_seq;

drop table Hit;
create table Hit(
	HitID		serial, -- int4 DEFAULT nextval('"hit_HitID_seq"'),
	SessionID	int4,
	CustomerID	int4,
	IPID		int4,
	AuthUser	text,
	AuthName	text,
	hitDatetime     timestamp,
	HostID		int4,
	URLID		int4,
	Status          int4,
	Size            int4,
	RefererHostID	int4,
	RefererURLID	int4,
	BrowserID	int4,
	TimeSpent	interval,
	XferTime	int4,
	MimeTypeID	int4
);
create index Hit_hitID on Hit(HitID);
create index Hit_hitDatetime_CustomerID on Hit(hitDatetime,CustomerID);
create index Hit_SessionID on Hit(SessionID);
create index Hit_hitDatetime_CustomerID_IPID_URLID
    on Hit(hitDatetime,CustomerID,IPID,URLID);
create index Hit_CustomerID_IPID_URLID on Hit(CustomerID,IPID,URLID);

drop sequence Customer_CustomerID_seq;
drop table Customer;
create table Customer(
	CustomerID	serial,
	CustomerText    text	-- (aeiwi.com)
);
create index Customer_CustomerID on Customer(CustomerID);
create index Customer_CustomerText on Customer(CustomerText);

drop sequence IP_IPID_seq;
drop table IP;
create table IP(
	IPID		serial,
	IPText		text,	-- (123.45.67.89)
	IPname		text,	-- (dial-ip192.inet)
	IPdomain	text,	-- (tele)
	IPcountry	text	-- (dk)
);
create index IP_IPID on IP(IPID);
create index IP_IPText on IP(IPText);

drop sequence Host_HostID_seq;
drop table Host;
create table Host(
	HostID		serial,
	HostText	text	-- (www.aeiwi.com)
);
create index Host_HostID on Host(HostID);
create index Host_HostText on Host(HostText);

drop sequence URL_URLID_seq;
drop table URL;
create table URL(
	URLID		serial,
--	URLMD5		text 	-- (MD5 af URLText)
	URLText		text 	-- (/cgi-bin/add?k=588)
);
create index URL_URLID on URL(URLID);
--create index URL_URLMD5 on URL(URLMD5);
create index URL_URLText on URL(URLText);

drop sequence Browser_BrowserID_seq;
drop table Browser;
create table Browser(
	BrowserID	serial,
	BrowserText	text,	-- (Mozilla 7/9.13)
	BrowserClass	int4
);
create index Browser_BrowserID on Browser(BrowserID);
create index Browser_BrowserText on Browser(BrowserText);

drop sequence MimeType_MimeTypeID_seq;
drop table MimeType;
create table MimeType(
	MimeTypeID	serial,
	MimeTypeText    text	-- (text/html)
);
create index MimeType_MimeTypeID on MimeType(MimeTypeID);
create index MimeType_MimeTypeText on MimeType(MimeTypeText);

drop sequence BrowserClass_BrowserClassID_seq;
drop table BrowserClass;
create table BrowserClass(
	BrowserClassID	serial,
	BrowserClassText	text	-- (Netscape 4.7x)
);
create index BrowserClass_BrowserClassID on BrowserClass(BrowserClassID);
create index BrowserClass_BrowserClassText on BrowserClass(BrowserClassText);


