--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema.sql	Wed May 06 14:33:31 1998 +0000
@@ -0,0 +1,83 @@
+-- Create schema for the SCS member and event database.
+
+-- Drop tables and sequences used
+DROP FUNCTION mailto(int4);
+DROP TABLE mem_ev_lnk;
+DROP TABLE events;
+DROP SEQUENCE event_id;
+DROP TABLE members;
+DROP SEQUENCE member_id;
+
+-- Sequence for Member ID's
+CREATE SEQUENCE member_id INCREMENT 1 START 1;
+
+-- Member table.
+CREATE TABLE members (
+	memberid		int4 NOT NULL,
+	firstname		char(25) NOT NULL,
+	lastname		char(25) NOT NULL,
+	nickname		char(20),
+	pin				char(10),
+	age				int2,
+	phone1			char(20),
+	ph1_pub			bool,
+	phone2			char(20),
+	ph2_pub			bool,
+	email			char(70) NOT NULL,
+	email_pub		bool,
+	address1		char(30),
+	address2		char(30),
+	address3		char(30),
+	addy_pub		bool,
+	comments		text,
+	joined			date,
+	lastmembfee		money,
+	lastpaiddate	date,
+
+	PRIMARY KEY (memberid),
+
+	CONSTRAINT memcon CHECK (firstname <> ''), CHECK (lastname <> ''), CHECK (nickname <> ''),
+	CHECK (memberid > 0)
+	);
+
+-- Sequence for Event ID's
+CREATE SEQUENCE event_id INCREMENT 1 START 1;
+
+-- Event table.
+CREATE TABLE events (
+	eventid		int4 NOT NULL,
+	name		text NOT NULL,
+	description	text NOT NULL,
+	when		datetime NOT NULL,
+	duration	timespan,
+	cost		text NOT NULL,
+	contact		text NOT NULL,
+
+	PRIMARY KEY (eventid),
+
+	CONSTRAINT evcon CHECK (eventid > 0), CHECK (name <> ''), CHECK (description <> ''),
+					 CHECK (cost <> ''), CHECK (contact <> '')
+	);
+
+-- Table to link the Event table and the Member ID table
+CREATE TABLE mem_ev_lnk (
+	memberid	int4,
+	eventid		int4,
+	contact		bool,
+
+	FOREIGN KEY (memberid) REFERENCES members,
+	FOREIGN KEY (eventid) REFERENCES events,
+
+	CONSTRAINT lnkcon CHECK (memberid > 0), CHECK (eventid > 0)
+	);
+
+-- Function which returns a list of email addresses to mail about a
+-- given eventid
+CREATE FUNCTION mailto(int4) RETURNS SETOF bpchar AS
+	'SELECT email FROM members WHERE memberid IN
+		(SELECT memberid FROM mem_ev_lnk WHERE eventid = $1 AND contact = \'yes\')'
+	LANGUAGE 'sql';
+
+GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO www;
+GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO darius;
+