create.sql

来自「opennms得相关源码 请大家看看」· SQL 代码 · 共 876 行 · 第 1/3 页

--# create.sql -- SQL to build the initial tables for the OpenNMS Project
--#
--# Copyright (C) 2005 The OpenNMS Group, Inc., Inc.  All rights reserved.
--# Parts Copyright (C) 1999-2001 Oculan Corp.  All rights reserved.
--#
--# 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., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--#
--# For more information contact:
--#      OpenNMS Licensing       <[e-mail]>
--#      http://www.opennms.org/
--#      http://www.sortova.com/
--#
--# Modified: 2004-08-30
--# Note: See create.sql.changes

drop table assets cascade;
drop table usersNotified cascade;
drop table notifications cascade;
drop table outages cascade;
drop table ifServices cascade;
drop table snmpInterface cascade;
drop table ipInterface cascade;
drop table node cascade;
drop table service cascade;
drop table distPoller cascade;
drop table events cascade;
drop table vulnerabilities cascade;
drop table vulnPlugins cascade;
drop table serverMap cascade;
drop table serviceMap cascade;
drop sequence nodeNxtId;
drop sequence serviceNxtId;
drop sequence eventsNxtId;
drop sequence outageNxtId;
drop sequence notifyNxtId;
drop sequence vulnNxtId;

--########################################################################
--# serverMap table - Contains a list of IP Addresses mapped to
--#                   OpenNMS servers
--#
--# This table contains the following fields:
--#
--#  ipAddr      : IP address of the device to be monitored
--#  serverName  : Text field to store the server name
--#
--########################################################################

create table serverMap (
	ipAddr			varchar(16) not null,
	serverName		varchar(64) not null );

create index server_name_idx on serverMap(serverName);

--########################################################################
--# serviceMap table - Contains a list of IP Addresses mapped to
--#                    OpenNMS services
--#
--# This table contains the following fields:
--#
--#  ipAddr          : IP address of the device to be monitored
--#  serviceName     : Text field to store the service name
--#
--########################################################################

create table serviceMap (
	ipAddr			varchar(16) not null,
	serviceMapName		varchar(32) not null
);
create index servicemap_name_idx on serviceMap(serviceMapName);
create index serviceMap_ipaddr_idx on serviceMap(ipAddr);

--########################################################################
--# distPoller table - Contains information on Distributed Pollers
--#                    installed in this OpenNMS instance.
--#
--# This table contains the following fields:
--#
--#  dpName      : A human-readable name for each system.  Typically,
--#                the system's hostname (not fully qualified).
--#  dpIP        : IP address of the distributed poller.
--#  dpComment   : Free-form text field
--#  dpDiscLimit : Numeric representation of percentage of interface speed
--#                available to discovery process.  See documentation for
--#                "bandwidth troll"
--#  dpLastNodePull 	: Time of last pull of new nodes from the DP
--#  dpLastEventPull	: Time of last pull of events from the DP
--#  dpLastPackagePush	: Time of last push of Package (config) to the DP
--#  dpAdminState: Reflects desired state for this distributed poller.
--#                1 = Up, 0 = Down
--#  dpRunState  : Reflects the current perceived state of the distributed
--#                poller.  1 = Up, 0 = Down
--#
--########################################################################

create table distPoller (
	dpName			varchar(12) not null,
	dpIP			varchar(16) not null,
	dpComment		varchar(256),
	dpDiscLimit		numeric(5,2),
	dpLastNodePull		timestamp without time zone,
	dpLastEventPull		timestamp without time zone,
	dpLastPackagePush	timestamp without time zone,
	dpAdminState 		integer,
	dpRunState		integer,

	constraint pk_dpName primary key (dpName)
);

--########################################################################
--# node Table - Contains information on nodes discovered and potentially
--#              managed by OpenNMS.  nodeSys* fields map to SNMP MIB 2
--#              system table information.
--#
--# This table contains the following fields:
--#
--#  nodeID          : Unique identifier for node.  Note that this is the
--#                    enabler for overlapping IP ranges and that uniquity
--#                    is dependent on combination of dpName & IP address
--#  dpName          : Distributed Poller responsible for this node
--#  nodeCreateTime  : Time node was added to the database
--#  nodeParentID    : In the case that the node is virtual or an independent
--#                    device in a chassis that should be reflected as a
--#                    subcomponent or "child", this field reflects the nodeID
--#                    of the chassis/physical node/"parent" device.
--#                    Currently unused.
--#  nodeType        :  Flag indicating status of node
--#			'A' - active
--#  			'D' - deleted
--#  nodeSysOID      : SNMP MIB-2 system.sysObjectID.0
--#  nodeSysName     : SNMP MIB-2 system.sysName.0
--#  nodeSysDescription    : SNMP MIB-2 system.sysDescr.0
--#  nodeSysLocation : SNMP MIB-2 system.sysLocation.0
--#  nodeSysContact  : SNMP MIB-2 system.sysContact.0
--#  nodeLabel	     : User-friendly name associated with the node.
--#  nodeLabelSource : Flag indicating source of nodeLabel
--#                      'U' = user defined
--#                      'H' = IP hostname
--#                      'S' = sysName
--#                      'A' = IP address
--# nodeNetBIOSName  : NetBIOS workstation name associated with the node.
--# nodeDomainName   : NetBIOS damain name associated with the node.
--# operatingSystem  : Operating system running on the node.
--# lastCapsdPoll    : Date and time of last Capsd scan.
--########################################################################

create table node (
	nodeID		integer not null,
	dpName		varchar(12),
	nodeCreateTime	timestamp without time zone not null,
	nodeParentID	integer,
	nodeType	char(1),
	nodeSysOID	varchar(256),
	nodeSysName	varchar(256),
	nodeSysDescription	varchar(256),
	nodeSysLocation	varchar(256),
	nodeSysContact	varchar(256),
	nodeLabel	varchar(256),
	nodeLabelSource	char(1),
        nodeNetBIOSName varchar(16),
	nodeDomainName  varchar(16),
	operatingSystem varchar(64),
	lastCapsdPoll   timestamp without time zone,

	constraint pk_nodeID primary key (nodeID),
	constraint fk_dpName foreign key (dpName) references distPoller
);

create index node_id_type_idx on node(nodeID, nodeType);
create index node_label_idx on node(nodeLabel);

--########################################################################
--# ipInterface Table - Contains information on interfaces which support
--#                     TCP/IP as well as current status information.
--#                     ipAddr is integer, to support easier filtering.
--#
--# This table contains the following information:
--#
--#  nodeID          : Unique identifier of the node that "owns" this interface
--#  ipAddr          : IP Address associated with this interface
--#  ifIndex	     : SNMP index of interface, used to uniquely identify
--# 		       unnumbered interfaces.
--#
--# NOTE: The combination of nodeID, ipAddr, and ifIndex must be unique,
--# and this must be enforced programmatically.
--#
--#  ipHostname      : IP Hostname associated with this interface
--#  isManaged       : Character used as a boolean flag
--#                     'M' - Managed
--#                     'A' - Alias
--#                     'D' - Deleted
--#                     'U' - Unmanaged
--#			'F' - Forced Unmanaged (via the user interface)
--#                     'N' - Not polled as part of any package
--#  ipStatus        : If interface supports SNMP this field will
--#                    hold a numeric representation of interface's
--#                    operational status (same as 'snmpIfOperStatus'
--#                    field in the snmpInterface table).
--#                      1 = Up, 2 = Down, 3 = Testing
--#  ipLastCapsdPoll : Date and time of last poll by capsd
--#  isSnmpPrimary   : Character used as a boolean flag
--#                      'P' - Primary SNMP
--#                      'S' - Secondary SNMP
--#                      'N' - Not eligible (does not support SNMP or
--#                               or has no ifIndex)
--#
--########################################################################

create table ipInterface (
	nodeID			integer,
	ipAddr			varchar(16) not null,
	ifIndex			integer,
	ipHostname		varchar(256),
	isManaged		char(1),
	ipStatus		integer,
	ipLastCapsdPoll		timestamp without time zone,
	isSnmpPrimary           char(1),

	constraint fk_nodeID1 foreign key (nodeID) references node ON DELETE CASCADE
);

create index ipinterface_nodeid_ipaddr_ismanaged_idx on ipInterface(nodeID, ipAddr, isManaged);
create index ipinterface_ipaddr_ismanaged_idx on ipInterface(ipAddr, isManaged);
create index ipinterface_ipaddr_idx on ipInterface(ipAddr);
create index ipinterface_nodeid_ismanaged_idx on ipInterface(ipAddr);
create index ipinterface_nodeid_idx on ipInterface(nodeID);

--#########################################################################
--# snmpInterface Table - Augments the ipInterface table with information
--#                       available from IP interfaces which also support
--#                       SNMP.
--#
--# This table provides the following information:
--#
--#  nodeID             : Unique identifier for node to which this if belongs
--#  ipAddr             : IP Address associated with this interface
--#  snmpIpAdEntNetMask : SNMP MIB-2 ipAddrTable.ipAddrEntry.ipAdEntNetMask
--#                       Value is interface's subnet mask
--#  snmpPhysAddr       : SNMP MIB-2 ifTable.ifEntry.ifPhysAddress
--#                       Value is interface's MAC Address
--#  snmpIfIndex        : SNMP MIB-2 ifTable.ifEntry.ifIndex
--#                       Value is interface's arbitrarily assigned index.
--#  snmpIfDescr        : SNMP MIB-2 ifTable.ifEntry.ifDescr
--#                       Value is interface's manufacturer/product name/version
--#  snmpIfType         : SNMP MIB-2 ifTable.ifEntry.ifType
--#                       Value is interface's physical/link protocol
--#  snmpIfName		: SNMP MIB-2 ifTable.ifEntry.ifName
--#			  Value is interface's device name
--#  snmpIfSpeed        : SNMP MIB-2 ifTable.ifEntry.ifSpeed
--#                       Value is estimate of interface's data rate
--#  snmpIfAdminStatus  : SNMP MIB-2 ifTable.ifEntry.ifAdminStatus
--#                       Value is interface's desired status
--#                       1 = Up, 2 = Down, 3 = Testing
--#  snmpIfOperStatus   : SNMP MIB-2 ifTable.ifEntry.ifOperStatus
--#                       Value is interface's current operational status
--#                       1 = Up, 2 = Down, 3 = Testing
--#  snmpIfAlias		: SNMP MIB-2 ifXTable.ifXEntry.ifAlias
--#			  Value is interface's device alias
--#
--# NOTE:  Although not marked as "not null" the snmpIfIndex field
--#        should never be null.  This table is considered to be uniquely
--#        keyed by nodeId and snmpIfIndex.  Eventually ipAddr and
--#        snmpIpAdEntNetMask will be removed and netmask added to
--#        the ipInterface table.
--########################################################################

create table snmpInterface (
	nodeID			integer,
	ipAddr			varchar(16) not null,
	snmpIpAdEntNetMask	varchar(16),
	snmpPhysAddr		char(12),
	snmpIfIndex		integer,
	snmpIfDescr		varchar(256),
	snmpIfType		integer,
	snmpIfName		varchar(32),
	snmpIfSpeed		bigint,
	snmpIfAdminStatus	integer,
	snmpIfOperStatus	integer,
	snmpIfAlias		varchar(256),