# Core schema
# Copyright (C) 2009, AllWorldIT
# Copyright (C) 2008, LinuxRulz
# 
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

@PRELOAD@


/* 
Priorities...
	0      - System policy priority (fallthrough)
	1-50   - System policies
	50-100 - Custom policies
*/

/* Policies */
CREATE TABLE policies (
	ID			@SERIAL_TYPE@,

	Name			VARCHAR(255) NOT NULL,

	Priority		SMALLINT NOT NULL,

	Description		TEXT,

	Disabled		SMALLINT NOT NULL DEFAULT '0'

) @CREATE_TABLE_SUFFIX@;

INSERT INTO policies (Name,Priority,Description) VALUES ('Default',0,'Default System Policy');
INSERT INTO policies (Name,Priority,Description) VALUES ('Default Outbound',10,'Default Outbound System Policy');
INSERT INTO policies (Name,Priority,Description) VALUES ('Default Inbound',10,'Default Inbound System Policy');
INSERT INTO policies (Name,Priority,Description) VALUES ('Default Internal',20,'Default Internal System Policy');
INSERT INTO policies (Name,Priority,Description) VALUES ('Test',50,'Test policy');


/* Member list for policies */
CREATE TABLE policy_members (
	ID			@SERIAL_TYPE@,

	PolicyID		@SERIAL_REF_TYPE@,

	/* 
		Format of key: 
		NULL = any
		a.b.c.d/e = IP address with optional /e
		@domain = domain specification, 
		%xyz = xyz group, 
		abc@domain = abc user specification

		all options support negation using !<key>
	*/
	Source			TEXT,
	Destination		TEXT,

	Comment			VARCHAR(1024),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	FOREIGN KEY (PolicyID) REFERENCES policies(ID)
) @CREATE_TABLE_SUFFIX@;


/* Default System Policy */
INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
	(1,NULL,NULL);
/* Default Outbound System Policy */
INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
	(2,'%internal_ips,%internal_domains','!%internal_domains');
/* Default Inbound System Policy */
INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
	(3,'!%internal_ips,!%internal_domains','%internal_domains');
/* Default Internal System Policy */
INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
	(4,'%internal_ips,%internal_domains','%internal_domains');
/* Test Policy */
INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
	(5,'@example.net',NULL);



/* Groups usable in ACL */
CREATE TABLE policy_groups (
	ID			@SERIAL_TYPE@,

	Name			VARCHAR(255) NOT NULL,


	Disabled		SMALLINT NOT NULL DEFAULT '0',

	Comment			VARCHAR(1024),


	UNIQUE (Name)
)  @CREATE_TABLE_SUFFIX@;

INSERT INTO policy_groups (Name) VALUES ('internal_ips');
INSERT INTO policy_groups (Name) VALUES ('internal_domains');



/* Group members */
CREATE TABLE policy_group_members (
	ID			@SERIAL_TYPE@,

	PolicyGroupID		@SERIAL_REF_TYPE@,

	/* Format of member: a.b.c.d/e = ip,  @domain = domain, %xyz = xyz group, abc@domain = abc user */
	Member			VARCHAR(255) NOT NULL,
	

	Disabled		SMALLINT NOT NULL DEFAULT '0',
	Comment			VARCHAR(1024),


	FOREIGN KEY (PolicyGroupID) REFERENCES policy_groups(ID)
)  @CREATE_TABLE_SUFFIX@;

INSERT INTO policy_group_members (PolicyGroupID,Member) VALUES (1,'10.0.0.0/8');
INSERT INTO policy_group_members (PolicyGroupID,Member) VALUES (2,'@example.org');
INSERT INTO policy_group_members (PolicyGroupID,Member) VALUES (2,'@example.com');



/* Message session tracking */
CREATE TABLE session_tracking (
	Instance		VARCHAR(255),
	QueueID			VARCHAR(255),

	Timestamp		BIGINT NOT NULL,

	ClientAddress		VARCHAR(64),
	ClientName		VARCHAR(255),
	ClientReverseName	VARCHAR(255),

	Protocol		VARCHAR(255),

	EncryptionProtocol	VARCHAR(255),
	EncryptionCipher	VARCHAR(255),
	EncryptionKeySize	VARCHAR(255),

	SASLMethod		VARCHAR(255),
	SASLSender		VARCHAR(255),
	SASLUsername		VARCHAR(255),

	Helo			VARCHAR(255),

	Sender			VARCHAR(255),

	Size			@BIG_INTEGER_UNSIGNED@,

	RecipientData		TEXT,  /* Policy state information */

	UNIQUE (Instance)
)  @CREATE_TABLE_SUFFIX@;
CREATE INDEX session_tracking_idx1 ON session_tracking (QueueID,ClientAddress,Sender);
CREATE INDEX session_tracking_idx2 ON session_tracking (Timestamp);



