Re: [phpOpenTracker-User] Call to any PostgreSQl users?

Doh! Silly me, I made a mistake. accesslog_id should *not* have been a
primary key ... and definitely no duplicate_check() function needed for
that table. No wonder my apge impressions weren't changing.

Here's the new table definitions:

--
-- character varying (varchar) fields changed to type TEXT
-- since there is no real performance gain in postgres for
-- using varchar versus text
--

CREATE TABLE "pot_accesslog" (
  "accesslog_id"   integer NOT NULL,
  "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_accesslog_id   on pot_accesslog(accesslog_id);
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