|
1
|
1 -- Create schema for the SCS member and event database.
|
|
|
2
|
|
|
3 -- Drop tables and sequences used
|
|
|
4 DROP FUNCTION mailto(int4);
|
|
|
5 DROP TABLE mem_ev_lnk;
|
|
|
6 DROP TABLE events;
|
|
|
7 DROP SEQUENCE event_id;
|
|
|
8 DROP TABLE members;
|
|
|
9 DROP SEQUENCE member_id;
|
|
|
10
|
|
|
11 -- Sequence for Member ID's
|
|
|
12 CREATE SEQUENCE member_id INCREMENT 1 START 1;
|
|
|
13
|
|
|
14 -- Member table.
|
|
|
15 CREATE TABLE members (
|
|
|
16 memberid int4 NOT NULL,
|
|
|
17 firstname char(25) NOT NULL,
|
|
|
18 lastname char(25) NOT NULL,
|
|
|
19 nickname char(20),
|
|
|
20 pin char(10),
|
|
|
21 age int2,
|
|
|
22 phone1 char(20),
|
|
|
23 ph1_pub bool,
|
|
|
24 phone2 char(20),
|
|
|
25 ph2_pub bool,
|
|
|
26 email char(70) NOT NULL,
|
|
|
27 email_pub bool,
|
|
|
28 address1 char(30),
|
|
|
29 address2 char(30),
|
|
|
30 address3 char(30),
|
|
|
31 addy_pub bool,
|
|
|
32 comments text,
|
|
|
33 joined date,
|
|
|
34 lastmembfee money,
|
|
|
35 lastpaiddate date,
|
|
|
36
|
|
|
37 PRIMARY KEY (memberid),
|
|
|
38
|
|
|
39 CONSTRAINT memcon CHECK (firstname <> ''), CHECK (lastname <> ''), CHECK (nickname <> ''),
|
|
|
40 CHECK (memberid > 0)
|
|
|
41 );
|
|
|
42
|
|
|
43 -- Sequence for Event ID's
|
|
|
44 CREATE SEQUENCE event_id INCREMENT 1 START 1;
|
|
|
45
|
|
|
46 -- Event table.
|
|
|
47 CREATE TABLE events (
|
|
|
48 eventid int4 NOT NULL,
|
|
|
49 name text NOT NULL,
|
|
|
50 description text NOT NULL,
|
|
|
51 when datetime NOT NULL,
|
|
|
52 duration timespan,
|
|
|
53 cost text NOT NULL,
|
|
|
54 contact text NOT NULL,
|
|
|
55
|
|
|
56 PRIMARY KEY (eventid),
|
|
|
57
|
|
|
58 CONSTRAINT evcon CHECK (eventid > 0), CHECK (name <> ''), CHECK (description <> ''),
|
|
|
59 CHECK (cost <> ''), CHECK (contact <> '')
|
|
|
60 );
|
|
|
61
|
|
|
62 -- Table to link the Event table and the Member ID table
|
|
|
63 CREATE TABLE mem_ev_lnk (
|
|
|
64 memberid int4,
|
|
|
65 eventid int4,
|
|
|
66 contact bool,
|
|
|
67
|
|
|
68 FOREIGN KEY (memberid) REFERENCES members,
|
|
|
69 FOREIGN KEY (eventid) REFERENCES events,
|
|
|
70
|
|
|
71 CONSTRAINT lnkcon CHECK (memberid > 0), CHECK (eventid > 0)
|
|
|
72 );
|
|
|
73
|
|
|
74 -- Function which returns a list of email addresses to mail about a
|
|
|
75 -- given eventid
|
|
|
76 CREATE FUNCTION mailto(int4) RETURNS SETOF bpchar AS
|
|
|
77 'SELECT email FROM members WHERE memberid IN
|
|
|
78 (SELECT memberid FROM mem_ev_lnk WHERE eventid = $1 AND contact = \'yes\')'
|
|
|
79 LANGUAGE 'sql';
|
|
|
80
|
|
|
81 GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO www;
|
|
|
82 GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO darius;
|
|
|
83
|
