[phpOpenTracker-User] Call to any PostgreSQl users?

Here is my attempt at a postgreSQL database layout for phpOpenTracker. 
Can anyone else using postgreSQl and phpOpenTracker try this file out 
and let me know if they see any bugs or have any suggestions?

I've tried it out on my site but since it isn't very high traffic the 
database isn't getting hit all that much.

And of course, Sebastian can you have a quick look too to make sure I 
haven't messed up your table layouts? :)

Thanks,

Jean-Christian Imbeault

----------------------
--
-- character varying (varchar) fields changed to type TEXT
-- since there is no real performance gain in postgres for
-- using varchar versus text
--
-- From:
-- 
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype-character.html
--
-- "Tip:  There are no performance differences between these
-- three types [char, varchar, text], apart from the increased
-- storage size when using the blank-padded type."

CREATE TABLE "pot_accesslog" (
   "accesslog_id"   integer PRIMARY KEY,
   "timestamp"      integer NOT NULL,
   "document_id"    integer NOT NULL,
   "exit_target_id" integer DEFAULT 0 NOT NULL,
   "entry_document" boolean NOT NULL
);

CREATE INDEX pot_accesslog_timestamp      on pot_accesslog(timestamp);
CREATE INDEX pot_accesslog_document_id    on pot_accesslog(document_id);
CREATE INDEX pot_accesslog_exit_target_id on pot_accesslog(exit_target_id);

CREATE TABLE "pot_add_data" (
   "accesslog_id" integer                NOT NULL,
   "data_field"   text                   NOT NULL,
   "data_value"   text                   NOT NULL
);

CREATE TABLE "pot_documents" (
   "data_id"      integer                PRIMARY KEY,
   "string"       text                   NOT NULL,
   "document_url" text                   NOT NULL
);

CREATE OR REPLACE FUNCTION pot_documents_duplicate_check() RETURNS 
TRIGGER AS '
BEGIN
   PERFORM 1 FROM pot_documents WHERE data_id=NEW.data_id LIMIT 1;
   IF FOUND THEN
     RETURN null;
   END IF;
   RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);

create trigger pot_documents_duplicate_check_trig
  BEFORE INSERT ON pot_documents
   for each ROW
    EXECUTE PROCEDURE pot_documents_duplicate_check();

CREATE TABLE "pot_exit_targets" (
   "data_id" integer                     PRIMARY KEY,
   "string"  text                        NOT NULL
);

CREATE TABLE "pot_hostnames" (
   "data_id" integer                     PRIMARY KEY,
   "string"  text                        NOT NULL
);

CREATE OR REPLACE FUNCTION pot_hostnames_duplicate_check() RETURNS 
TRIGGER AS '
BEGIN
   PERFORM 1 FROM pot_hostnames WHERE data_id=NEW.data_id LIMIT 1;
   IF FOUND THEN
     RETURN null;
   END IF;
   RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);

create trigger pot_hostnames_duplicate_check_trig
  BEFORE INSERT ON pot_hostnames
   for each ROW
    EXECUTE PROCEDURE pot_hostnames_duplicate_check();

CREATE TABLE "pot_operating_systems" (
   "data_id" text                        PRIMARY KEY,
   "string"  text                        NOT NULL
);

CREATE OR REPLACE FUNCTION pot_operating_systems_duplicate_check() 
RETURNS TRIGGER AS '
BEGIN
   PERFORM 1 FROM pot_operating_systems WHERE data_id=NEW.data_id LIMIT 1;
   IF FOUND THEN
     RETURN null;
   END IF;
   RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);

create trigger pot_operating_systems_duplicate_check_trig
  BEFORE INSERT ON pot_operating_systems
   for each ROW
    EXECUTE PROCEDURE pot_operating_systems_duplicate_check();

CREATE TABLE "pot_referers" (
   "data_id" integer                     PRIMARY KEY,
   "string"  text                        NOT NULL
);

CREATE OR REPLACE FUNCTION pot_referers_duplicate_check() RETURNS 
TRIGGER AS '
BEGIN
   PERFORM 1 FROM pot_referers WHERE data_id=NEW.data_id LIMIT 1;
   IF FOUND THEN
     RETURN null;
   END IF;
   RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);

create trigger pot_referers_duplicate_check_trig
  BEFORE INSERT ON pot_referers
   for each ROW
    EXECUTE PROCEDURE pot_referers_duplicate_check();

CREATE TABLE "pot_user_agents" (
   "data_id" integer                     PRIMARY KEY,
   "string"  text                        NOT NULL
);

CREATE OR REPLACE FUNCTION pot_user_agents_duplicate_check() RETURNS 
TRIGGER AS '
BEGIN
   PERFORM 1 FROM pot_user_agents WHERE data_id=NEW.data_id LIMIT 1;
   IF FOUND THEN
     RETURN null;
   END IF;
   RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);

create trigger pot_user_agents_duplicate_check_trig
  BEFORE INSERT ON pot_user_agents
   for each ROW
    EXECUTE PROCEDURE pot_user_agents_duplicate_check();

CREATE TABLE "pot_visitors" (
   "accesslog_id"        integer         PRIMARY KEY,
   "visitor_id"          integer         NOT NULL,
   "client_id"           integer         NOT NULL,
   "operating_system_id" integer         NOT NULL,
   "user_agent_id"       integer         NOT NULL,
   "host_id"             integer         NOT NULL,
   "referer_id"          integer         NOT NULL,
   "timestamp"           integer         NOT NULL,
   "returning_visitor"   boolean         NOT NULL
);

CREATE INDEX pot_visitors_client_time  on pot_visitors(client_id,timestamp);

CREATE OR REPLACE FUNCTION pot_visitors_duplicate_check() RETURNS 
TRIGGER AS '
BEGIN
   PERFORM 1 FROM pot_visitors WHERE accesslog_id=NEW.accesslog_id LIMIT 1;
   IF FOUND THEN
     RETURN null;
   END IF;
   RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);

create trigger pot_visitors_duplicate_check_trig
  BEFORE INSERT ON pot_visitors
   for each ROW
    EXECUTE PROCEDURE pot_visitors_duplicate_check();




-------------------------------------------------------
This SF.Net email sponsored by: Free pre-built ASP.NET sites including
Data Reports, E-commerce, Portals, and Forums are available now.
Download today and enter to win an XBOX or Visual Studio .NET.
http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01