-- Copyright (C) 2000-2002 Carnegie Mellon University
--
-- Maintainer: Roman Danyliw <rdd@cert.org>, <roman@danyliw.com>
--
-- Original Author(s): Jed Pickel <jed@pickel.net>    (2000-2001)
--                     Roman Danyliw <rdd@cert.org>
--                     Todd Schrubb <tls@cert.org>
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License Version 2 as
-- published by the Free Software Foundation.  You may not use, modify or
-- distribute this program under any other version of the GNU General
-- Public License.
--
-- 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., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.

CREATE TABLE schema ( vseq        INT4     NOT NULL,
                      ctime       TIMESTAMP without time zone NOT NULL,
                      PRIMARY KEY (vseq));
INSERT INTO schema  (vseq, ctime) VALUES ('107', now());

CREATE TABLE sig_class (sig_class_id						SERIAL,
						sig_class_name						varchar NOT NULL UNIQUE,
						sig_class_description				varchar NOT NULL UNIQUE,
						sig_total_count						INT4 NOT NULL DEFAULT 0,
						sig_enabled_count					INT4 NOT NULL DEFAULT 0,
						sig_priority						INT8 NOT NULL,
						PRIMARY KEY (sig_class_id));
CREATE INDEX sig_class_sig_class_id_idx ON sig_class (sig_class_id);

CREATE TABLE signature (sig_sid				INT8 NOT NULL,
						sig_rev				INT8 NOT NULL,
						sig_name			varchar NOT NULL,
						sig_class_id		INT8 NOT NULL,
						sig_default_action	varchar(16) NOT NULL,
						sig_action			varchar(16) NOT NULL,
						sig_protocol		varchar(16) NOT NULL,
						sig_ip_src			varchar NOT NULL,
						sig_ip_dst			varchar NOT NULL,
						sig_port_src		varchar NOT NULL,
						sig_port_dst		varchar NOT NULL,
						sig_noalert			BOOLEAN NOT NULL,
						sig_ref				varchar NOT NULL,
						sig_raw_rule		TEXT NOT NULL,
						sig_using			BOOLEAN NOT NULL DEFAULT 'true',
						PRIMARY KEY (sig_sid, sig_rev),
						FOREIGN KEY (sig_class_id) REFERENCES sig_class (sig_class_id));
CREATE INDEX signature_sig_sid_rev_idx ON signature (sig_sid, sig_rev);
CREATE INDEX signature_sig_class_idx ON signature (sig_class_id);

CREATE TABLE modified_signature (sig_sid				SERIAL,
								 sig_rev				INT4 NOT NULL DEFAULT 1,
								 sig_class_id			INT8 NOT NULL,
								 type					INT2 NOT NULL,
								 raw_sid				INT4 NOT NULL,
								 raw_rev				INT4 NOT NULL,
								 sig_name				varchar NOT NULL,
								 sig_action				varchar(16) NOT NULL,
								 sig_ip_src				varchar NOT NULL,
								 sig_ip_dst				varchar NOT NULL,
								 PRIMARY KEY (sig_sid, sig_rev));
ALTER SEQUENCE modified_signature_sig_sid_seq RESTART WITH 4000000;

CREATE TABLE event  (sid								INT4 NOT NULL,
                     cid								SERIAL,
					 mac_src							char(20) default '',
					 mac_dst							char(20) default '',
                     sig_sid							INT4 NOT NULL,
                     sig_rev							INT4 NOT NULL,
                     sig_class_id						INT4 NOT NULL,
					 sig_name							varchar(300) NOT NULL default '',
                     timestamp							timestamp without time zone NOT NULL,
                     ts_epoch							bigint,
                     impact_flag						INT2 NOT NULL default -1,
                     PRIMARY KEY (sid,cid));
CREATE INDEX event_idx ON event (sid, cid);
CREATE INDEX timestamp_idx ON event (timestamp);
CREATE INDEX ts_epoch_idx ON event (ts_epoch);

-- All of the fields of an ip header
CREATE TABLE iphdr  ( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      ip_src      INT8 NOT NULL,
                      ip_dst      INT8 NOT NULL,
                      ip_ver      INT2,
                      ip_hlen     INT2,
                      ip_tos  	  INT2,
                      ip_len 	  INT4,
                      ip_id    	  INT4,
                      ip_flags    INT2,
                      ip_off      INT4,
                      ip_ttl   	  INT2,
                      ip_proto 	  INT2 NOT NULL,
                      ip_csum 	  INT4,
                      PRIMARY KEY (sid, cid),
                      FOREIGN KEY (sid, cid) REFERENCES event(sid, cid) ON DELETE CASCADE);
CREATE INDEX ip_src_idx ON iphdr (ip_src);
CREATE INDEX ip_dst_idx ON iphdr (ip_dst);

-- All of the fields of a tcp header
CREATE TABLE tcphdr(  sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      tcp_sport   INT4 NOT NULL,
                      tcp_dport   INT4 NOT NULL,
                      tcp_seq     INT8,
                      tcp_ack     INT8,
                      tcp_off     INT2,
                      tcp_res     INT2,
                      tcp_flags   INT2 NOT NULL,
                      tcp_win     INT4,
                      tcp_csum    INT4,
                      tcp_urp     INT4,
                      PRIMARY KEY (sid,cid),
                      FOREIGN KEY (sid,cid) REFERENCES event(sid, cid) ON DELETE CASCADE);
CREATE INDEX tcp_sport_idx ON tcphdr (tcp_sport);
CREATE INDEX tcp_dport_idx ON tcphdr (tcp_dport);
CREATE INDEX tcp_flags_idx ON tcphdr (tcp_flags);

-- All of the fields of a udp header
CREATE TABLE udphdr(  sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      udp_sport   INT4 NOT NULL,
                      udp_dport   INT4 NOT NULL,
                      udp_len     INT4,
                      udp_csum    INT4,
                      PRIMARY KEY (sid,cid),
                      FOREIGN KEY (sid,cid) REFERENCES event(sid, cid) ON DELETE CASCADE);
CREATE INDEX udp_sport_idx ON udphdr (udp_sport);
CREATE INDEX udp_dport_idx ON udphdr (udp_dport);

-- All of the fields of an icmp header
CREATE TABLE icmphdr( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      icmp_type   INT2 NOT NULL,
                      icmp_code   INT2 NOT NULL,
                      icmp_csum   INT4, 
                      icmp_id     INT4,
                      icmp_seq    INT4,
                      PRIMARY KEY (sid,cid),
                      FOREIGN KEY (sid,cid) REFERENCES event(sid, cid) ON DELETE CASCADE);
CREATE INDEX icmp_type_idx ON icmphdr (icmp_type);

-- Packet payload
CREATE TABLE data   ( sid          INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      data_payload TEXT,
                      PRIMARY KEY (sid,cid),
                      FOREIGN KEY (sid,cid) REFERENCES event(sid, cid) ON DELETE CASCADE);

-- mapping mac to device name
CREATE TABLE device				(mac					TEXT NOT NULL,
								 device_name			TEXT NOT NULL,
								 detect					BOOLEAN NOT NULL,
								 loading_score			INT8 NOT NULL DEFAULT 0,
								 PRIMARY KEY (mac));
CREATE INDEX mac_idx ON device (mac);
CREATE INDEX detect_idx ON device (detect);

--------------------------- policy related table ---------------------------

CREATE TABLE policy_class		(sig_class_id		INT4 NOT NULL,
								 action				varchar(20) NOT NULL,
								 comment			varchar(200) NOT NULL default '',
								 PRIMARY KEY (sig_class_id),
								 FOREIGN KEY (sig_class_id) REFERENCES sig_class (sig_class_id));

CREATE TABLE policy_signature	(raw_sid				INT4 NOT NULL,
								 sig_class_id			INT4 NOT NULL,
								 sig_name				varchar(300) NOT NULL,
								 action					varchar(20) NOT NULL,
								 comment				varchar(200) NOT NULL default '',
								 PRIMARY KEY (raw_sid));

CREATE TABLE policy_filter		(raw_sid				INT4 NOT NULL,
								 filter_sid				INT4 NOT NULL,
								 filter_rev				INT4 NOT NULL,
								 sig_class_id			INT4 NOT NULL,
								 sig_name				varchar(300) NOT NULL,
								 action					varchar(20) NOT NULL,
								 ip_src					INT8 NOT NULL,
								 ip_dst					INT8 NOT NULL,
								 comment				varchar(200) NOT NULL default '',
								 PRIMARY KEY (raw_sid, ip_src, ip_dst));

--------------------------- loading related table ---------------------------
CREATE TABLE loading			(start_time				INT8 NOT NULL,
								 end_time				INT8 NOT NULL,
								 ip						varchar(46) NOT NULL,
								 packet_number			INT4 NOT NULL);
