-- Copyright (C) 2009 Trend Micro Inc. -- All rights reserved. -- -- This program is a 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 FSF - Free Software -- Foundation. BEGIN; CREATE TABLE category ( cat_id SERIAL NOT NULL, cat_name VARCHAR(32) NOT NULL UNIQUE, PRIMARY KEY (cat_id) ); CREATE INDEX cat_name ON category (cat_name); CREATE TABLE signature ( id SERIAL NOT NULL, rule_id INT8 NOT NULL UNIQUE, level INT4, description VARCHAR(255) NOT NULL, PRIMARY KEY (id) ); CREATE INDEX signature_level ON signature (level); CREATE INDEX signature_rule_id ON signature (rule_id); CREATE TABLE signature_category_mapping ( id SERIAL NOT NULL, rule_id INT8 NOT NULL, cat_id INT4 NOT NULL, PRIMARY KEY (id, rule_id, cat_id) ); CREATE TABLE server ( id SERIAL NOT NULL, last_contact INT8 NOT NULL, version VARCHAR(32) NOT NULL, hostname VARCHAR(64) NOT NULL UNIQUE, information TEXT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE agent ( id SERIAL NOT NULL, server_id INT8 NOT NULL, last_contact INT8 NOT NULL, ip_address VARCHAR(46) NOT NULL, version VARCHAR(32) NOT NULL, name VARCHAR(64) NOT NULL, information VARCHAR(128) NOT NULL, PRIMARY KEY (id, server_id) ); CREATE TABLE location ( id SERIAL NOT NULL, server_id INT8 NOT NULL, name VARCHAR(128) NOT NULL, PRIMARY KEY (id, server_id) ); CREATE TABLE data ( id INT8 NOT NULL, server_id INT4 NOT NULL, "user" TEXT NOT NULL, full_log TEXT NOT NULL, PRIMARY KEY (id, server_id) ); CREATE TABLE alert ( id bigserial NOT NULL, server_id INT4 NOT NULL, rule_id INT8 NOT NULL, level INT2, timestamp INT8 NOT NULL, location_id INT4 NOT NULL, src_ip VARCHAR(46), dst_ip VARCHAR(46), src_port INT4, dst_port INT4, alertid TEXT DEFAULT NULL, "user" TEXT, full_log TEXT NOT NULL, is_hidden INT2 NOT NULL DEFAULT '0', tld VARCHAR(32) NOT NULL DEFAULT '', PRIMARY KEY (id, server_id) ); CREATE INDEX alertid on alert(alertid); CREATE INDEX alert_level on alert(level); CREATE INDEX timestamp on alert(timestamp); CREATE INDEX alert_rule_id on alert(rule_id); CREATE INDEX src_ip on alert(src_ip); CREATE INDEX tld on alert(tld); COMMIT;