1 -- Create schema for monolith discussion widget.
2 -- Copyright (C) 2002 Richard W.M. Jones <rich@annexia.org>
3 -- This code is NOT REDISTRIBUTABLE. To use this widget you must purchase
4 -- a license at http://www.annexia.org/
6 -- $Id: ml_discussion_create.sql,v 1.1 2003/02/22 12:49:21 rich Exp $
8 -- Depends: monolith_core, monolith_users, monolith_auth, monolith_resources
12 create table ml_discussion_group
15 constraint ml_discussion_group_resid_pk
17 references ml_resources (resid)
19 expiry_days int2, -- Number of days to expire (null = never)
20 allow_anon boolean -- If true, allow anonymous postings
22 constraint ml_discussion_group_allow_anon_nn
24 default_view char(1) -- Default view (unless user overrides)
26 constraint ml_discussion_group_default_view_nn
28 constraint ml_discussion_group_default_view_ck
29 check (default_view in ('1', '2'))
32 create table ml_discussion_article
34 id serial, -- Unique article number.
35 resid int4 -- Which group is this in?
36 constraint ml_discussion_article_resid_nn
38 references ml_resources (resid)
40 parent int4 -- Parent of this article (for threading)
41 references ml_discussion_article (id),
42 subject text -- Subject line of the message
43 constraint ml_discussion_article_subject_nn
45 author int4 -- Author of the article (null = anonymous)
46 references ml_users (userid)
48 body text -- The actual body of the article
49 constraint ml_discussion_article_content_nn
51 body_type char(1) -- Type: plain, smart, HTML
52 constraint ml_discussion_article_body_type_nn
54 constraint ml_discussion_article_body_type_ck
55 check (body_type in ('p', 's', 'h')),
56 posted_date timestamp -- When posted
57 default current_timestamp
58 constraint ml_discussion_article_posted_date_nn
60 original_ip inet -- Originating IP address when posted
61 constraint ml_discussion_article_original_ip_nn
65 -- This table stores which articles each user has read. To keep the size
66 -- compact, we store ranges of article IDs, rather than having one row
67 -- per (user, article) which could potentially make the table huge.
69 create table ml_discussion_read
71 resid int4 -- The group.
72 constraint ml_discussion_read_resid_nn
74 references ml_discussion_group (resid)
76 userid int4 -- The user ID.
77 constraint ml_discussion_read_userid_nn
79 references ml_users (userid)
81 low int4 -- User has read articles [ low, high-1 ]
82 constraint ml_discussion_read_low_nn
85 constraint ml_discussion_read_high_nn
91 create table ml_discussion_userprefs
93 userid int4 -- The user ID.
94 constraint ml_discussion_userprefs_userid_nn
96 references ml_users (userid)
98 view char(1) -- 1-pane or 2-pane view
100 constraint ml_discussion_userprefs_view_nn
102 constraint ml_discussion_userprefs_view_ck
103 check (view in ('1', '2')),
104 sort_order char(1) -- Sort by date, username, subject
106 constraint ml_discussion_userprefs_sort_order_nn
108 constraint ml_discussion_userprefs_sort_order_ck
109 check (view in ('d', 'D', 'n', 'N', 's', 'S'))
112 -- Function to count the number of unread articles in the given
115 \i ml_discussion_functions.sql
117 -- Grant access to the webservers
119 grant select on ml_discussion_group to nobody;
120 grant select, insert, update, delete on ml_discussion_article to nobody;
121 grant select, update on ml_discussion_article_id_seq to nobody;
122 grant select, insert, update, delete on ml_discussion_read to nobody;
123 grant select, insert, update, delete on ml_discussion_userprefs to nobody;