-- Create schema for monolith chat widget.
-- Copyright (C) 2002 Richard W.M. Jones <rich@annexia.org>
-- This code is NOT REDISTRIBUTABLE. To use this widget you must purchase
-- a license at http://www.annexia.org/
--
-- $Id: ml_chat_create.sql,v 1.1 2003/02/22 12:49:21 rich Exp $
--
-- Depends: monolith_core, monolith_users, monolith_auth, monolith_resources

begin work;

create table ml_chat_rooms
(
	resid int4
		constraint ml_chat_rooms_resid_pk
		primary key
		references ml_resources (resid)
		on delete cascade,
	allow_anon boolean	-- If true, allow anonymous postings
		default 'f'
		constraint ml_chat_rooms_allow_anon_nn
		not null
);

-- Postings are logged in this table. The chat software doesn't
-- actually pull postings out of this table when displaying the
-- page, because that would be too slow. This table is consulted
-- when the server starts up to populate the chat server's internal
-- data structures.

create table ml_chat_log
(
	id serial,		-- Unique posting ID
	resid int4		-- Which room does this belong to?
		constraint ml_chat_log_resid_nn
		not null
		references ml_chat_rooms (resid)
		on delete cascade,
	body text		-- The posting
		constraint ml_chat_log_body_nn
		not null,
	posted_date timestamp	-- When posted
		default current_timestamp
		constraint ml_chat_log_posted_date_nn
		not null,
	author int4		-- Who posted (null == anonymous)
		references ml_users (userid)
		on delete set null,
	original_ip inet	-- Originating IP address when posted
		constraint ml_chat_log_original_ip_nn
		not null
);

create table ml_chat_fill_buffers
(
	size int2		-- Number of bytes to send
		constraint ml_chat_fill_buffers_size_nn
		not null
		constraint ml_chat_fill_buffers_pk
		primary key
);

create table ml_chat_userprefs
(
	userid int4		-- The user ID.
		constraint ml_chat_userprefs_userid_nn
		not null
		references ml_users (userid)
		on delete cascade,
	fill_buffer int2	-- Size of the fill buffer.
		default 4096
		constraint ml_chat_userprefs_fill_buffer_nn
		not null
		references ml_chat_fill_buffers (size)
);

-- Populate the tables.

insert into ml_chat_fill_buffers (size) values (0);
insert into ml_chat_fill_buffers (size) values (1024);
insert into ml_chat_fill_buffers (size) values (2048);
insert into ml_chat_fill_buffers (size) values (4096);
insert into ml_chat_fill_buffers (size) values (8192);
insert into ml_chat_fill_buffers (size) values (16384);

-- Grant access to the webservers

grant select on ml_chat_rooms to nobody;
grant select, insert on ml_chat_log to nobody;
grant select, update on ml_chat_log_id_seq to nobody;
grant select on ml_chat_fill_buffers to nobody;
grant select, insert, update, delete on ml_chat_userprefs to nobody;

commit work;