-- --
--	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:	1-APR-2008
--
--	Abstract: SQL Module interface
--
--	Usage:
--		SQLMOD BBS_SQL.SQLMOD/C_PROTO/LIS
--
-- ++

-----------------------------------------------------------------------------
-- Header	Information	Section
-----------------------------------------------------------------------------
MODULE		BBS_SQL
DIALECT		SQL99
LANGUAGE	C
-- LANGUAGE	GENERAL - !!! Please don't set the GENERAL for this module !!!
AUTHORIZATION	LAISHEV
DEFAULT	DATE FORMAT	VMS
PARAMETER COLONS
-----------------------------------------------------------------------------
-- DECLARE Statements	Section
-----------------------------------------------------------------------------
-- Declare the alias using the file	name.

DECLARE	ALIAS	FOR	FILENAME	BBS

DECLARE	uat_cur READ ONLY TABLE CURSOR
	FOR SELECT *
	FROM uat

DECLARE	cat_cur READ ONLY TABLE CURSOR
	FOR SELECT *
	FROM cat

DECLARE	subcat_cur READ ONLY TABLE CURSOR
	FOR SELECT *
	FROM subcat
	WHERE cat = :p_catid

DECLARE	topic_cur READ ONLY TABLE CURSOR
	FOR SELECT *
	FROM topic
	WHERE subcat = :p_subcatid

-----------------------------------------------------------------------------

PROCEDURE	db_open_catcur(
	SQLCODE
			);
	OPEN	cat_cur;

PROCEDURE	db_close_catcur(
	SQLCODE
			);
	CLOSE	cat_cur;

PROCEDURE	db_open_subcatcur(
	SQLCODE,
	:p_catid	dom_ref
			);
	OPEN	subcat_cur;

PROCEDURE	db_close_subcatcur(
	SQLCODE
			);
	CLOSE	subcat_cur;

PROCEDURE	db_rollback(
	SQLCODE
			);
	ROLLBACK;

PROCEDURE	db_open_uatcur(
	SQLCODE
			);
	OPEN	uat_cur;

PROCEDURE	db_close_uatcur(
	SQLCODE
			);
	CLOSE	uat_cur;


PROCEDURE	db_open_topic(
	SQLCODE,
	:p_subcatid	dom_ref
			);
	OPEN	topic_cur;

PROCEDURE	db_close_topic(
	SQLCODE
			);
	CLOSE	topic_cur;


-----------------------------------------------------------------------------
-- Insert a new User Authorization Record into the BBS' UAT
--
PROCEDURE	uat_add	(
	SQLCODE,
	:p_uic		dom_ref
	:p_name		dom_name
	:p_pass		dom_pass
	:p_email	dom_email
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	INSERT	INTO uat	(name,pass,email)
		VALUES	(:p_name,:p_pass,:p_email);

	SELECT uic
		INTO :p_uic
		FROM uat
		WHERE	name = :p_name;
	COMMIT;
END;
--
-- Retrive a User Authorization Record from the BBS' UAT
--
PROCEDURE	uat_get	(
	SQLCODE,
	:p_uic		dom_ref
	:p_uat_row RECORD
		uic		dom_ref
		name		dom_name
		pass		dom_pass
		flags		dom_flags
		privs		dom_privs
		created		dom_date
		lastlogin	dom_date
		expired		dom_date
		email		dom_email
		profile		dom_ref
		topics		dom_count
		replies		dom_count
	END RECORD
			);
BEGIN
	SET TRANSACTION	READ ONLY;

	SELECT *
		INTO :p_uat_row
		FROM uat
		WHERE uic = :p_uic;
END;

--
-- Retrieve a record from Categories Table
--
PROCEDURE	cat_get	(
	SQLCODE,
	:p_id		dom_ref
	:p_created	dom_date
	:p_name		dom_cat_name
	:p_ldesc	dom_cat_desc
	:ind_array RECORD 
          INDICATOR ARRAY OF 16 INTEGER 
      END RECORD
			);
BEGIN

	SET TRANSACTION READ ONLY;

	SELECT id,created,name,ldesc
		INTO :p_id,:p_created,:p_name,:p_ldesc
		FROM cat
		WHERE id = :p_id;
END;

PROCEDURE	uat_login(
	SQLCODE,
	:p_name		dom_name
	:p_pass		dom_pass
	:p_uat_row RECORD
		uic		dom_ref
		name		dom_name
		pass		dom_pass
		flags		dom_flags
		privs		dom_privs
		created		dom_date
		lastlogin	dom_date
		expired		dom_date
		email		dom_email
		profile		dom_ref
		topics		dom_count
		replies		dom_count
	END RECORD
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	TRACE	:p_name,:p_pass;

	SET	:p_uat_row.uic = 0;

	SELECT *
		INTO :p_uat_row
		FROM uat
		WHERE (name = :p_name OR email = :p_name) AND (pass = :p_pass);

	TRACE	:p_uat_row;
	TRACE	:p_name,:p_pass;

	IF :p_uat_row.uic > 0
	THEN
		UPDATE	uat
			SET lastlogin = SYSDATE
			WHERE uic = :p_uat_row.uic
			RETURNING lastlogin INTO :p_uat_row.lastlogin;
	END IF;

	COMMIT;
END;



--
-- Retrive a User Authorization Record from the BBS' UPT
--
PROCEDURE	upt_get	(
	SQLCODE,
	:p_uic		dom_ref
	:p_upt_row	RECORD
		uic		dom_ref
		owner		dom_owner
		birthday	dom_date
		icq		dom_icq
		skype		dom_skype
		hompage		dom_hompage
		signature	dom_signature
		sex		dom_sex
		city		dom_city
		country		dom_country
		avatar		dom_avatar
	END RECORD
			);
BEGIN
	SET TRANSACTION	READ ONLY;

	SELECT *
		INTO :p_upt_row
		FROM upt
		WHERE uic = :p_uic;
END;


--
-- Retrive a User Authorization Record from the BBS' UAT
--
PROCEDURE	uat_list(
	SQLCODE,
	:p_uat_row RECORD
		uic		dom_ref
		name		dom_name
		pass		dom_pass
		flags		dom_flags
		privs		dom_privs
		created		dom_date
		lastlogin	dom_date
		expired		dom_date
		email		dom_email
		profile		dom_ref
		topics		dom_count
		replies		dom_count
	END RECORD
			);
	FETCH uat_cur
		INTO :p_uat_row;

--
-- Modify a User Authorization Record in the BBS' UAT
--
PROCEDURE	uat_mod	(
	SQLCODE,
	:p_uic		dom_ref,
	:p_name		dom_name,
	:p_pass		dom_pass,
	:p_flags	dom_flags,
	:p_privs	dom_privs,
	:p_lastlogin	dom_date
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	IF :p_pass IS	NOT	NULL
	THEN
		UPDATE	uat
			SET pass = :p_pass
			WHERE uic = :p_uic OR name = :p_name;
	END IF;

	IF :p_flags	IS NOT NULL
	THEN
		UPDATE	uat
			SET flags = :p_flags
			WHERE uic = :p_uic OR name = :p_name;
	END IF;

	IF :p_privs	IS NOT NULL
	THEN
		UPDATE	uat
			SET privs = :p_privs
			WHERE uic = :p_uic OR name = :p_name;
	END IF;


	IF :p_lastlogin	IS NOT NULL
	THEN
		UPDATE uat
			SET lastlogin =	SYSDATE
			WHERE uic = :p_uic OR name = :p_name;
	END IF;

	COMMIT;
END;
--
--  Remove a User Authorization Record from the BBS' UAT
--
PROCEDURE	uat_del	(
	SQLCODE,
	:p_uic		dom_ref,
	:p_name		dom_name
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	DELETE FROM uat
		WHERE uic = :p_uic OR name = :p_name;
END;

-----------------------------------------------------------------------------
-- Add a Category
--
PROCEDURE	cat_add	(
	SQLCODE,
	:p_id		dom_ref,
	:p_cat_name	dom_cat_name,
	:p_ldesc	dom_cat_desc
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	INSERT	INTO cat (name,ldesc)
		VALUES	(:p_cat_name,:p_ldesc);

	SELECT id
		INTO :p_id
		FROM cat
		WHERE name = :p_cat_name;

	COMMIT;
END;

--
-- Update a Category name and description
--
PROCEDURE	cat_upd	(

	SQLCODE,
	:p_id		dom_ref,
	:p_cat_name	dom_cat_name,
	:p_ldesc	dom_cat_desc
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	UPDATE cat
		SET name = :p_cat_name, ldesc = :p_ldesc
		WHERE id = :p_id;

	COMMIT;
END;

--
-- Update a Category name and description
--
PROCEDURE	cat_del	(
	SQLCODE,
	:p_id		dom_ref
			);
BEGIN
	SET TRANSACTION	READ WRITE;

--	UPDATE cat
--		SET name = :p_cat_name, ldesc = :p_ldesc
--		WHERE id = :p_id;

	COMMIT;
END;

--
-- Get Category's record
--
PROCEDURE	cat_list(
	SQLCODE,
	:p_cat_row RECORD
		id		dom_ref
		created		dom_date
		cat_name	dom_cat_name
		ldesc		dom_cat_desc
	END RECORD
			);
	FETCH cat_cur INTO :p_cat_row;

-----------------------------------------------------------------------------
-- Add SubCategory
--
PROCEDURE	subcat_add(
	SQLCODE,
	:p_cat_id	dom_ref,
	:p_id		dom_ref,
	:p_moder1	dom_ref,
	:p_moder2	dom_ref,
	:p_moder3	dom_ref,
	:p_cat_name	dom_cat_name,
	:p_ldesc	dom_cat_desc
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	--
	-- If a category is exist we can add a subcotigiry
	--
	SELECT id INTO :p_cat_id FROM cat WHERE id = :p_cat_id;

	INSERT	INTO subcat (cat,moder1,moder2,moder3,name,ldesc)
		VALUES	(:p_cat_id,:p_moder1,:p_moder2,:p_moder3,:p_cat_name,:p_ldesc);

	SELECT id
		INTO :p_id
		FROM subcat
		WHERE name = :p_cat_name;

	COMMIT;
END;

--
-- Update a Category name and description
--
PROCEDURE	subcat_upd	(
	SQLCODE,
	:p_id		dom_ref,
	:p_moder1	dom_ref,
	:p_moder2	dom_ref,
	:p_moder3	dom_ref,
	:p_cat_name	dom_cat_name,
	:p_ldesc	dom_cat_desc
			);
BEGIN
	SET TRANSACTION	READ WRITE;

	UPDATE subcat
		SET moder1 = :p_moder1, moder2 = :p_moder2, moder3 = :p_moder3, name = :p_cat_name,
			ldesc = :p_ldesc
		WHERE id = :p_id;
	COMMIT;
END;

--
-- Get Categorie's record
--
PROCEDURE	subcat_list(
	SQLCODE,
	:p_subcat_row RECORD
		id		dom_ref
		cat		dom_ref
		moder1		dom_ref
		moder2		dom_ref
		moder3		dom_ref
		created		dom_date
		topics		dom_count
		cat_name	dom_cat_name
		ldesc		dom_cat_desc
		lastupd		dom_date
	END RECORD
			);
	FETCH subcat_cur INTO :p_subcat_row;

--
-- Get Categorie's record
--
PROCEDURE	subcat_get(
	SQLCODE,
	:p_id		dom_ref
	:p_subcat_row RECORD
		id		dom_ref
		cat		dom_ref
		moder1		dom_ref
		moder2		dom_ref
		moder3		dom_ref
		created		dom_date
		topics		dom_count
		name		dom_cat_name
		ldesc		dom_cat_desc
		lastupd		dom_date
	END RECORD

			);
BEGIN
	SELECT	*
		INTO :p_subcat_row
		FROM subcat
		WHERE id = :p_id;
	TRACE	:p_subcat_row;

END;

--
-- Fetch a record from the Topics table
--
PROCEDURE	topic_list(
	SQLCODE,
	:p_topic_row RECORD
		id		dom_ref
		subcat		dom_ref
		name		dom_cat_name
		created		dom_date
		msgs		INTEGER
		author		dom_ref
		updated		dom_date
		flags		dom_flags
		hits		INTEGER
		replies		INTEGER
	END RECORD
			);
	FETCH topic_cur
		INTO :p_topic_row;