Mercurial > ~darius > hgwebdir.cgi > SCS_DB

comparison schema.sql @ 1:d95e74cd12f4 RELENG_1_0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initial commit
author darius
date Wed, 06 May 1998 14:33:31 +0000
parents
children
comparison
equal deleted inserted replaced
0:019f8230ae37 1:d95e74cd12f4
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