[phpOpenTracker-User] postgres.sql edit suggestions

Here are a few editions to the postgres.sql file I would like to submit 
for your review. I have removed the explicit creation of primary key 
constraints in tables and replaced instead with the use of the "primary 
KEY" in the table definition. I have also simplified the index creation 
statements. b-tree use and the operator class to use a being explicitly 
stated but these are the same as the default values so I have rewritten 
the index creation statements in a simpler format.

I hope these changes will make the file more readable and easier to 
understand.

The new file is below.

Jean-Christian Imbeault

----------------------------

/*
+---------------------------------------------------------------------+
| phpOpenTracker - The Website Traffic and Visitor Analysis Solution  |
+---------------------------------------------------------------------+
| Copyright (c) 2000-2003 Sebastian Bergmann. All rights reserved.    |
+---------------------------------------------------------------------+
| This source file is subject to the phpOpenTracker Software License, |
| Version 1.0, that is bundled with this package in the file LICENSE. |
| If you did not receive a copy of this file, you may either read the |
| license online at http://phpOpenTracker.de/license/1_0.txt, or send |
| a note to [email protected], so we can mail you a copy.     |
+---------------------------------------------------------------------+
| Authors: Cornelia Boenigk <[email protected]>                   |
|          Jean-Christian Imbeault <[email protected]>              |
+---------------------------------------------------------------------+

$Id: postgresql.sql,v 1.8 2003/07/03 07:47:31 bergmann Exp $
*/

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 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 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 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 TABLE "pot_operating_systems" (
   "data_id" integer                PRIMARY KEY,
   "string"  character varying(255) 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 TABLE "pot_referers" (
   "data_id" integer                PRIMARY KEY,
   "string"  character varying(255) 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 TABLE "pot_user_agents" (
   "data_id" integer                PRIMARY KEY,
   "string"  character varying(255) 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 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);



-------------------------------------------------------
This SF.Net email sponsored by: Parasoft
Error proof Web apps, automate testing & more.
Download & eval WebKing and get a free book.
www.parasoft.com/bulletproofapps