set	verify	

-- --
--  Facility:
--    BBS/Forum system on DEC RDB
--
--  Environment:
--    VMS/VAX/Alpha/IA64,
--    DEC/ORACLE RDB 7.x,
--    WASD HTTP Server.
--
--  Author:   Ruslan R. Laishev
--
--  Creation Date: 31-MAY-2007
--
--  Abstract:   This script is supposed to be used as first time to performs
--      creation of tables, stored routines and so on...
--
--  Usage:
--    SQL$ @BBS-INIT.SQL
--
-- ++

CREATE	DATABASE	FILENAME	'BBS'
	CREATE  STORAGE AREA	BBS_IDX	FILENAME	'BBS_IDX'PAGE FORMAT IS MIXED;
ATTACH	'FILENAME	BBS';

-- --
--  Create internal data types/domains :
-- ++

CREATE  DOMAIN  dom_name	VARCHAR(11);
CREATE  DOMAIN  dom_pass	VARCHAR(31);
CREATE  DOMAIN  dom_owner	VARCHAR(63)	DEFAULT '';
CREATE  DOMAIN  dom_flags	INTEGER	DEFAULT 0;
CREATE  DOMAIN  dom_privs	INTEGER	DEFAULT 0;
CREATE  DOMAIN  dom_icq		INTEGER	DEFAULT 0 EDIT STRING IS  '999-999-999';
CREATE  DOMAIN  dom_email	VARCHAR(127)	DEFAULT '';
CREATE  DOMAIN  dom_skype	VARCHAR(63)	DEFAULT '';
CREATE  DOMAIN  dom_hompage	VARCHAR(127)	DEFAULT '';
CREATE  DOMAIN  dom_signature	VARCHAR(127)	DEFAULT '';
CREATE  DOMAIN  dom_sex		CHAR(1)		DEFAULT '';
CREATE  DOMAIN  dom_city	VARCHAR(31)	DEFAULT '';
CREATE  DOMAIN  dom_country	VARCHAR(31)	DEFAULT '';
CREATE  DOMAIN  dom_avatar	VARCHAR(127)	DEFAULT '';
CREATE  DOMAIN  dom_date	DATE VMS	DEFAULT '1-JAN-2000 00:00:00';

CREATE  DOMAIN  dom_cat_name	VARCHAR(63)	DEFAULT '';
CREATE  DOMAIN  dom_cat_desc	VARCHAR(511)	DEFAULT '';

CREATE  DOMAIN	dom_subj	VARCHAR(127)	DEFAULT '';
CREATE  DOMAIN  dom_body	VARCHAR(65000)	DEFAULT '';
CREATE  DOMAIN  dom_ref		INTEGER	DEFAULT 0 EDIT STRING IS  '999-999-999';
CREATE  DOMAIN  dom_ipaddr	VARCHAR(17)	DEFAULT '';
CREATE	DOMAIN	dom_count	INTEGER DEFAULT 0;

CREATE  TABLE cat
	(
	id		dom_ref IDENTITY PRIMARY KEY,
	created				-- Category creation date
		AUTOMATIC INSERT AS SYSDATE,
	name		dom_cat_name	-- Category name
		CONSTRAINT  name_unq UNIQUE,
	ldesc		dom_cat_desc	-- Category long description
	) COMMENT IS 'Table of Categories';

CREATE  TABLE subcat
	(
	id		dom_ref IDENTITY PRIMARY KEY,
	cat		dom_ref,	-- REF# to Category
	moder1    	dom_ref,	-- SubCategory Moderator N 1
	moder2    	dom_ref,	-- SubCategory Moderator N 2
	moder3    	dom_ref,	-- SubCategory Moderator N 3
	created				-- SubCategory Creation date
		AUTOMATIC INSERT AS SYSDATE,
	topics		dom_count,
	name		dom_cat_name,	-- SubCategory short name
	ldesc		dom_cat_desc,	-- SubCategory long description
	updated		AUTOMATIC INSERT AS SYSDATE
	) COMMENT IS 'Table of SubCategories';

CREATE  TABLE topic
	(
	id		dom_ref IDENTITY PRIMARY KEY,
	subcat		dom_ref,	-- REF# to SubCategory
	name		dom_cat_name,
	created				-- Topic's Creation date
		AUTOMATIC INSERT AS SYSDATE,
	msgs		INTEGER,
	author		dom_ref,
	updated
		AUTOMATIC UPDATE AS SYSDATE DEFAULT NULL,
	flags		dom_flags,
	hits		INTEGER DEFAULT 0,
	replies		INTEGER DEFAULT 0
	) COMMENT IS 'Table of topics';

CREATE  TABLE msg
	(
	id		dom_ref IDENTITY PRIMARY KEY,
	topic		dom_ref,
	author		dom_ref,
	body		dom_body,
	created
		AUTOMATIC INSERT AS SYSDATE,
	updated
		AUTOMATIC UPDATE AS SYSDATE,
	flags		dom_flags,
	inreplyto	dom_ref
	) COMMENT IS 'Table of messages/posts';


CREATE  TABLE uat
	(
	uic		dom_ref		-- User Identification Code
		IDENTITY PRIMARY KEY,
	name		dom_name	-- Nickname/Username
		CONSTRAINT  uname_unq UNIQUE,
	pass		dom_pass,	-- User's password
	flags		dom_flags,	-- Flags, see UAT_M* constatnts
	privs		dom_privs,	-- Privileges, see UAT_M* constatnts
	created				-- The reccord creation date
		AUTOMATIC INSERT AS SYSDATE,
	lastlogin	dom_date,	-- Last Login Date
	expired		dom_date,	-- The record expiration date
	email		dom_email	-- User's E-Mail
		CONSTRAINT  email_unq UNIQUE,
	profile		dom_ref,	-- A reference to User's profile in the 'BBS Users Profiles Table';
	topics		dom_count,	-- A number of created topics
	replies		dom_count	-- A number of replies
	) COMMENT IS 'BBS Users Authorization Table';


CREATE  TABLE upt
	(
	uic		dom_ref
		IDENTITY PRIMARY KEY,
	owner		dom_owner,
	birthday	dom_date
		DEFAULT '1-JAN-2000 00:00:00',
	icq		dom_icq,
	skype		dom_skype,
	hompage		dom_hompage,
	signature	dom_signature,
	sex		dom_sex
		CONSTRAINT sex_val CHECK (sex IN ('M', 'F','?')),
	city		dom_city,
	country		dom_country,
	avatar		dom_avatar
	) COMMENT IS 'BBS Users Profiles Table';

-- --
--	Create first user = SysMan
-- ++
INSERT	INTO uat	(name,pass,email)
	VALUES	('SysMan','SysMan135','Laishev@StarLet.SPb.RU');

INSERT	INTO uat	(name,pass,email)
	VALUES	('Admin','SysMan135','STARLET::LAISHEV');


INSERT  INTO cat	(name,ldesc)
	VALUES	('General', 'General discussions/Off Topics...');
INSERT  INTO subcat	(cat,name,ldesc)
	VALUES (1,'BBS', 'Discussions about bugs & new features of the BBS Software');
INSERT  INTO subcat     (cat,name,ldesc)
	VALUES (1,'Admins & Moderators', 'Closed board of moderators & administrative');



INSERT  INTO cat	(name,ldesc)
	VALUES	('OpenVMS', 'OpenVMS operation system, AKA: VAX/VMS, OpenVMS, OpenVMS/VAX/Alpha/Itanium');
INSERT  INTO subcat	(cat,name,ldesc)
	VALUES	(2,'Networking', 'DECNet IV/OSI/Plus, LAT, TCP/IP, X.25');