Re: [phpOpenTracker-User] new postgresql.sql file

Sorry again, I posted an old file. The creation statements at the end
had a bug. Here's the new and hopefully final file:

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_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"   character varying(32)  NOT NULL,
  "data_value"   character varying(255) NOT NULL
);

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

CREATE TABLE "pot_exit_targets" (
  "data_id" integer                PRIMARY KEY,
  "string"  character varying(255) NOT NULL
);

CREATE TABLE "pot_hostnames" (
  "data_id" integer                PRIMARY KEY,
  "string"  character varying(255) NOT NULL
);

CREATE TABLE "pot_operating_systems" (
  "data_id" integer                PRIMARY KEY,
  "string"  character varying(255) NOT NULL
);

CREATE TABLE "pot_referers" (
  "data_id" integer                PRIMARY KEY,
  "string"  character varying(255) NOT NULL
);

CREATE TABLE "pot_user_agents" (
  "data_id" integer                PRIMARY KEY,
  "string"  character varying(255) NOT NULL
);

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_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 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 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 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 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_documents_duplicate_check_trig
 BEFORE INSERT ON pot_documents
  for each ROW
   EXECUTE PROCEDURE pot_documents_duplicate_check();

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

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 trigger pot_referers_duplicate_check_trig
 BEFORE INSERT ON pot_referers
  for each ROW
   EXECUTE PROCEDURE pot_referers_duplicate_check();

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



-------------------------------------------------------
This SF.net email is sponsored by: VM Ware
With VMware you can run multiple operating systems on a single machine.
WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines at the
same time. Free trial click here: http://www.vmware.com/wl/offer/345/0