Project OpenNMS
Version OpenNMS 090807
Version Date 2009-08-07

Tables
alarm_attributes (public)
Column Data Type Nullable Default PK / Index
alarmid integer null index alarm_attributes_aan_idx
index alarm_attributes_idx
attributename varchar(63) null index alarm_attributes_aan_idx
attributevalue varchar(255) null
Foreign Key Column Referenced Column
alarms (public) alarmid alarmid
Detail Table Column Referencing Column
Triggers
alarms (public)
Column Data Type Nullable Default PK / Index
alarmid integer not null PK pk_alarmid
index alarm_reduction2_idx
eventuei varchar(256) not null index alarm_reduction2_idx
index alarm_uei_idx
dpname varchar(12) not null index alarm_reduction2_idx
nodeid integer null index alarm_nodeid_idx
index alarm_reduction2_idx
ipaddr varchar(16) null
serviceid integer null index alarm_reduction2_idx
reductionkey varchar(256) null index alarm_reductionkey_idx
index alarm_reduction2_idx
alarmtype integer null
counter integer not null
severity integer not null
lasteventid integer null index alarm_eventid_idx
firsteventtime timestamp with time zone null
lasteventtime timestamp with time zone null
firstautomationtime timestamp with time zone null
lastautomationtime timestamp with time zone null
description varchar(4000) null
logmsg varchar(256) null
operinstruct varchar(1024) null
tticketid varchar(128) null
tticketstate integer null
mouseovertext varchar(64) null
suppresseduntil timestamp with time zone null
suppresseduser varchar(256) null
suppressedtime timestamp with time zone null
alarmackuser varchar(256) null
alarmacktime timestamp with time zone null
clearuei varchar(256) null
managedobjectinstance varchar(512) null
managedobjecttype varchar(512) null
applicationdn varchar(512) null index alarm_app_dn
ossprimarykey varchar(512) null index alarm_oss_primary_key
x733alarmtype varchar(31) null
x733probablecause integer not null 0
qosalarmstate varchar(31) null
clearkey varchar(256) null index alarm_clearkey_idx
Foreign Key Column Referenced Column
events (public) lasteventid eventid
Detail Table Column Referencing Column
alarm_attributes (public) alarmid alarmid
Triggers
application_service_map (public)
Column Data Type Nullable Default PK / Index
appid integer null index appid_ifserviceid_idex
index appid_idx
ifserviceid integer null index appid_ifserviceid_idex
index ifserviceid_idx
Foreign Key Column Referenced Column
applications (public) appid id
ifservices (public) ifserviceid id
Detail Table Column Referencing Column
Triggers
applications (public)
Column Data Type Nullable Default PK / Index
id integer not null PK applications_pkey
name varchar(32) not null index applications_name_idx
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
application_service_map (public) id appid
Triggers
assets (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass) PK pk_assetid
nodeid integer null index assets_nodeid_idx
category varchar(64) not null
manufacturer varchar(64) null
vendor varchar(64) null
modelnumber varchar(64) null
serialnumber varchar(64) null
description varchar(128) null
circuitid varchar(64) null
assetnumber varchar(64) null index assets_an_idx
operatingsystem varchar(64) null
rack varchar(64) null
slot varchar(64) null
port varchar(64) null
region varchar(64) null
division varchar(64) null
department varchar(64) null
address1 varchar(256) null
address2 varchar(256) null
city varchar(64) null
state varchar(64) null
zip varchar(64) null
building varchar(64) null
floor varchar(64) null
room varchar(64) null
vendorphone varchar(64) null
vendorfax varchar(64) null
vendorassetnumber varchar(64) null
userlastmodified char(20) not null
lastmodifieddate timestamp with time zone not null
dateinstalled varchar(64) null
lease varchar(64) null
leaseexpires varchar(64) null
supportphone varchar(64) null
maintcontract varchar(64) null
maintcontractexpires varchar(64) null
displaycategory varchar(64) null
notifycategory varchar(64) null
pollercategory varchar(64) null
thresholdcategory varchar(64) null
comment text null
managedobjectinstance varchar(512) null
managedobjecttype varchar(512) null
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
atinterface (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass)
nodeid integer not null PK pk_atinterface
index atinterface_nodeid_idx
index atinterface_node_ipaddr_idx
ipaddr varchar(16) not null PK pk_atinterface
index atinterface_node_ipaddr_idx
atphysaddr varchar(12) not null PK pk_atinterface
index atinterface_atphysaddr_idx
status char(1) not null
sourcenodeid integer not null
ifindex integer not null
lastpolltime timestamp without time zone not null
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
categories (public)
Column Data Type Nullable Default PK / Index
categoryid integer not null PK category_pkey
categoryname varchar(64) not null index category_idx
categorydescription varchar(256) null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
category_node (public) categoryid categoryid
Triggers
category_node (public)
Column Data Type Nullable Default PK / Index
categoryid integer null index catenode_unique_idx
index catid_idx
nodeid integer null index catenode_unique_idx
index catnode_idx
Foreign Key Column Referenced Column
categories (public) categoryid categoryid
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
datalinkinterface (public)
Column Data Type Nullable Default PK / Index
nodeid integer not null PK pk_datalinkinterface
index dlint_node_idx
ifindex integer not null PK pk_datalinkinterface
nodeparentid integer not null index dlint_nodeparent_idx
index dlint_nodeparent_paifindex_idx
parentifindex integer not null index dlint_nodeparent_paifindex_idx
status char(1) not null
lastpolltime timestamp without time zone not null
Foreign Key Column Referenced Column
node (public) nodeid nodeid
node (public) nodeparentid nodeid
Detail Table Column Referencing Column
Triggers
demandpolls (public)
Column Data Type Nullable Default PK / Index
id integer not null PK demandpoll_pkey
requesttime timestamp with time zone null index demandpoll_request_time
username varchar(32) null
description varchar(128) null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
pollresults (public) id pollid
Triggers
distpoller (public)
Column Data Type Nullable Default PK / Index
dpname varchar(12) not null PK pk_dpname
dpip varchar(16) not null
dpcomment varchar(256) null
dpdisclimit numeric(5, 2) null
dplastnodepull timestamp with time zone null
dplasteventpull timestamp with time zone null
dplastpackagepush timestamp with time zone null
dpadminstate integer null
dprunstate integer null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
node (public) dpname dpname
Triggers
element (public)
Column Data Type Nullable Default PK / Index
mapid integer not null PK pk_element
index element_mapid_elementid
elementid integer not null PK pk_element
index element_mapid_elementid
elementtype char(1) not null PK pk_element
elementlabel varchar(256) not null
elementicon varchar(256) null
elementx integer null
elementy integer null
Foreign Key Column Referenced Column
map (public) mapid mapid
Detail Table Column Referencing Column
Triggers
events (public)
Column Data Type Nullable Default PK / Index
eventid integer not null PK pk_eventid
eventuei varchar(256) not null index events_uei_idx
nodeid integer null index events_nodeid_idx
index events_nodeid_display_ackuser
eventtime timestamp with time zone not null index events_time_idx
eventhost varchar(256) null
eventsource varchar(128) not null
ipaddr varchar(16) null index events_ipaddr_idx
eventdpname varchar(12) not null
eventsnmphost varchar(256) null
serviceid integer null index events_serviceid_idx
eventsnmp varchar(256) null
eventparms text null
eventcreatetime timestamp with time zone not null
eventdescr varchar(4000) null
eventloggroup varchar(32) null
eventlogmsg varchar(256) null
eventseverity integer not null index events_severity_idx
eventpathoutage varchar(1024) null
eventcorrelation varchar(1024) null
eventsuppressedcount integer null
eventoperinstruct varchar(1024) null
eventautoaction varchar(256) null
eventoperaction varchar(256) null
eventoperactionmenutext varchar(64) null
eventnotification varchar(128) null
eventtticket varchar(128) null
eventtticketstate integer null
eventforward varchar(256) null
eventmouseovertext varchar(64) null
eventlog char(1) not null index events_log_idx
eventdisplay char(1) not null index events_nodeid_display_ackuser
index events_display_idx
eventackuser varchar(256) null index events_nodeid_display_ackuser
index events_ackuser_idx
eventacktime timestamp with time zone null index events_acktime_idx
alarmid integer null index events_alarmid_idx
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
outages (public) eventid svclosteventid
outages (public) eventid svcregainedeventid
notifications (public) eventid eventid
alarms (public) eventid lasteventid
Triggers
ifservices (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass) PK ifservices_pkey
nodeid integer not null index ifservices_nodeid_ipaddr_svc_unique
index ifservices_nodeid_ipaddr_status
index ifservices_nodeid_status
index ifservices_nodeid_idx
index ifservices_nodeid_serviceid_idx
ipaddr varchar(16) not null index ifservices_nodeid_ipaddr_svc_unique
index ifservices_nodeid_ipaddr_status
ifindex integer null
serviceid integer not null index ifservices_nodeid_ipaddr_svc_unique
index ifservices_serviceid_idx
index ifservices_nodeid_serviceid_idx
lastgood timestamp with time zone null
lastfail timestamp with time zone null
qualifier char(16) null
status char(1) null index ifservices_nodeid_ipaddr_status
index ifservices_nodeid_status
source char(1) null
notify char(1) null
ipinterfaceid integer not null index ifservicves_ipinterfaceid_idx
Foreign Key Column Referenced Column
node (public) nodeid nodeid
service (public) serviceid serviceid
ipinterface (public) ipinterfaceid id
Detail Table Column Referencing Column
outages (public) nodeid nodeid
outages (public) nodeid nodeid
outages (public) nodeid nodeid
outages (public) id ifserviceid
application_service_map (public) id ifserviceid
location_specific_status_changes (public) id ifserviceid
Triggers
setipinterfacekeysoninserttrigger ROW BEFORE INSERT
setipinterfacekeysonupdatetrigger ROW BEFORE UPDATE
inventory (public)
Column Data Type Nullable Default PK / Index
nodeid integer not null index inventory_nodeid_idx
index inventory_nodeid_name_idx
name varchar(30) not null index inventory_nodeid_name_idx
createtime timestamp without time zone not null
lastpolltime timestamp without time zone not null index inventory_lastpolltime_idx
pathtofile varchar(256) not null
status char(1) not null index inventory_status_idx
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
ipinterface (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass) PK ipinterface_pkey
nodeid integer not null index ipinterface_nodeid_ipaddr_notzero_idx
index ipinterface_nodeid_idx
index ipinterface_nodeid_ipaddr_ismanaged_idx
ipaddr varchar(16) not null index ipinterface_nodeid_ipaddr_notzero_idx
index ipinterface_nodeid_ismanaged_idx
index ipinterface_ipaddr_idx
index ipinterface_ipaddr_ismanaged_idx
index ipinterface_nodeid_ipaddr_ismanaged_idx
ifindex integer null
iphostname varchar(256) null
ismanaged char(1) null index ipinterface_ipaddr_ismanaged_idx
index ipinterface_nodeid_ipaddr_ismanaged_idx
ipstatus integer null
iplastcapsdpoll timestamp with time zone null
issnmpprimary char(1) null
snmpinterfaceid integer null index ipinterface_snmpinterfaceid_idx
Foreign Key Column Referenced Column
node (public) nodeid nodeid
snmpinterface (public) snmpinterfaceid id
Detail Table Column Referencing Column
ifservices (public) id ipinterfaceid
Triggers
setsnmpinterfacekeysoninserttrigger ROW BEFORE INSERT
setsnmpinterfacekeysonupdatetrigger ROW BEFORE UPDATE
iprouteinterface (public)
Column Data Type Nullable Default PK / Index
nodeid integer not null PK pk_iprouteinterface
index iprouteinterface_node_ifdex_idx
index iprouteinterface_nodeid_idx
routedest varchar(16) not null PK pk_iprouteinterface
routemask varchar(16) not null
routenexthop varchar(16) not null index iprouteinterface_rnh_idx
routeifindex integer not null index iprouteinterface_node_ifdex_idx
routemetric1 integer null
routemetric2 integer null
routemetric3 integer null
routemetric4 integer null
routemetric5 integer null
routetype integer null
routeproto integer null
status char(1) not null
lastpolltime timestamp without time zone not null
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
location_monitor_details (public)
Column Data Type Nullable Default PK / Index
locationmonitorid integer not null index location_monitor_details_id_property
index location_monitor_details_id
property varchar(255) not null index location_monitor_details_id_property
propertyvalue varchar(255) null
Foreign Key Column Referenced Column
location_monitors (public) locationmonitorid id
Detail Table Column Referencing Column
Triggers
location_monitors (public)
Column Data Type Nullable Default PK / Index
id integer not null PK location_monitors_pkey
status varchar(31) not null
lastcheckintime timestamp with time zone null
definitionname varchar(31) not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
location_monitor_details (public) id locationmonitorid
location_specific_status_changes (public) id locationmonitorid
Triggers
location_specific_status_changes (public)
Column Data Type Nullable Default PK / Index
id integer not null PK location_specific_status_changes_pkey
locationmonitorid integer not null index location_specific_status_changes_locationmonitorid_ifserviceid
index location_specific_status_changes_locationmonitorid
index location_specific_status_changes_locationmonitorid_loc_if_time
ifserviceid integer not null index location_specific_status_changes_locationmonitorid_ifserviceid
index location_specific_status_changes_ifserviceid
index location_specific_status_changes_locationmonitorid_loc_if_time
statuscode integer not null
statustime timestamp with time zone not null index location_specific_status_changes_statustime
index location_specific_status_changes_locationmonitorid_loc_if_time
statusreason varchar(255) null
responsetime double precision null
Foreign Key Column Referenced Column
ifservices (public) ifserviceid id
location_monitors (public) locationmonitorid id
Detail Table Column Referencing Column
Triggers
map (public)
Column Data Type Nullable Default PK / Index
mapid integer not null PK pk_mapid
mapname varchar(40) not null
mapbackground varchar(256) null
mapowner varchar(64) not null
mapcreatetime timestamp without time zone not null
mapaccess char(6) not null
userlastmodifies varchar(64) not null
lastmodifiedtime timestamp without time zone not null
mapscale double precision null
mapxoffset integer null
mapyoffset integer null
maptype char(1) null
mapwidth integer not null
mapheight integer not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
element (public) mapid mapid
Triggers
node (public)
Column Data Type Nullable Default PK / Index
nodeid integer not null PK pk_nodeid
index node_id_type_idx
dpname varchar(12) null index node_dpname_idx
nodecreatetime timestamp with time zone not null
nodeparentid integer null
nodetype char(1) null index node_id_type_idx
nodesysoid varchar(256) null
nodesysname varchar(256) null
nodesysdescription varchar(256) null
nodesyslocation varchar(256) null
nodesyscontact varchar(256) null
nodelabel varchar(256) null index node_label_idx
nodelabelsource char(1) null
nodenetbiosname varchar(16) null
nodedomainname varchar(16) null
operatingsystem varchar(64) null
lastcapsdpoll timestamp with time zone null
foreignsource varchar(64) null index node_foreign_unique_idx
foreignid varchar(64) null index node_foreign_unique_idx
Foreign Key Column Referenced Column
distpoller (public) dpname dpname
Detail Table Column Referencing Column
atinterface (public) nodeid nodeid
stpnode (public) nodeid nodeid
stpinterface (public) nodeid nodeid
iprouteinterface (public) nodeid nodeid
datalinkinterface (public) nodeid nodeid
datalinkinterface (public) nodeid nodeparentid
inventory (public) nodeid nodeid
vlan (public) nodeid nodeid
ipinterface (public) nodeid nodeid
snmpinterface (public) nodeid nodeid
ifservices (public) nodeid nodeid
outages (public) nodeid nodeid
assets (public) nodeid nodeid
notifications (public) nodeid nodeid
pathoutage (public) nodeid nodeid
category_node (public) nodeid nodeid
Triggers
notifications (public)
Column Data Type Nullable Default PK / Index
textmsg varchar(4000) not null
subject varchar(256) null
numericmsg varchar(256) null
notifyid integer not null PK pk_notifyid
pagetime timestamp with time zone null
respondtime timestamp with time zone null index notifications_respondtime_idx
answeredby varchar(256) null index notifications_answeredby_idx
nodeid integer null index notifications_nodeid_idx
interfaceid varchar(16) null index notifications_ipaddr_idx
serviceid integer null index notifications_serviceid_idx
queueid varchar(256) null
eventid integer null index notifications_eventid_idx
eventuei varchar(256) not null index notifications_eventuei_idx
notifconfigname varchar(63) null
Foreign Key Column Referenced Column
events (public) eventid eventid
node (public) nodeid nodeid
Detail Table Column Referencing Column
usersnotified (public) notifyid notifyid
Triggers
outages (public)
Column Data Type Nullable Default PK / Index
outageid integer not null PK pk_outageid
svclosteventid integer null index outages_svclostid_idx
svcregainedeventid integer null index outages_svcregainedid_idx
nodeid integer not null index outages_nodeid_ipaddr_svc_idx
index outages_nodeid_idx
ipaddr varchar(16) not null index outages_nodeid_ipaddr_svc_idx
index outages_ipaddr_idx
serviceid integer not null index outages_serviceid_idx
index outages_nodeid_ipaddr_svc_idx
iflostservice timestamp with time zone not null
ifregainedservice timestamp with time zone null index outages_regainedservice_idx
suppresstime timestamp with time zone null
suppressedby varchar(256) null
ifserviceid integer not null index outages_ifservivceid_idx
Foreign Key Column Referenced Column
events (public) svclosteventid eventid
events (public) svcregainedeventid eventid
node (public) nodeid nodeid
service (public) serviceid serviceid
ifservices (public) nodeid nodeid
ifservices (public) ifserviceid id
Detail Table Column Referencing Column
Triggers
setifservicekeysoninserttrigger ROW BEFORE INSERT
setifservicekeysonupdatetrigger ROW BEFORE UPDATE
pathoutage (public)
Column Data Type Nullable Default PK / Index
nodeid integer null index pathoutage_nodeid
criticalpathip varchar(16) not null index pathoutage_criticalpathip
criticalpathservicename varchar(32) null index pathoutage_criticalpathservicename_idx
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
pollresults (public)
Column Data Type Nullable Default PK / Index
id integer not null PK pollresult_pkey
pollid integer null index pollresults_poll_id
nodeid integer null index pollresults_service
ipaddr varchar(16) null index pollresults_service
ifindex integer null index pollresults_service
serviceid integer null index pollresults_service
statuscode integer null
statusname varchar(32) null
reason varchar(128) null
Foreign Key Column Referenced Column
demandpolls (public) pollid id
Detail Table Column Referencing Column
Triggers
qrtz_blob_triggers (public)
Column Data Type Nullable Default PK / Index
trigger_name varchar(80) not null PK pk_qrtz_blob_triggers
trigger_group varchar(80) not null PK pk_qrtz_blob_triggers
blob_data bytea null
Foreign Key Column Referenced Column
qrtz_triggers (public) trigger_name trigger_name
Detail Table Column Referencing Column
Triggers
qrtz_calendars (public)
Column Data Type Nullable Default PK / Index
calendar_name varchar(80) not null PK pk_qrtz_calendars
calendar bytea not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
qrtz_cron_triggers (public)
Column Data Type Nullable Default PK / Index
trigger_name varchar(80) not null PK pk_qrtz_cron_triggers
trigger_group varchar(80) not null PK pk_qrtz_cron_triggers
cron_expression varchar(80) not null
time_zone_id varchar(80) null
Foreign Key Column Referenced Column
qrtz_triggers (public) trigger_name trigger_name
Detail Table Column Referencing Column
Triggers
qrtz_fired_triggers (public)
Column Data Type Nullable Default PK / Index
entry_id varchar(95) not null PK pk_qrtz_fired_triggers
trigger_name varchar(80) not null
trigger_group varchar(80) not null
is_volatile boolean not null
instance_name varchar(80) not null
fired_time bigint not null
state varchar(16) not null
job_name varchar(80) null
job_group varchar(80) null
is_stateful boolean null
requests_recovery boolean null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
qrtz_job_details (public)
Column Data Type Nullable Default PK / Index
job_name varchar(80) not null PK qrtz_job_details_pkey
job_group varchar(80) not null PK qrtz_job_details_pkey
description varchar(120) null
job_class_name varchar(128) not null
is_durable boolean not null
is_volatile boolean not null
is_stateful boolean not null
requests_recovery boolean not null
job_data bytea not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
qrtz_job_listeners (public) job_name job_name
qrtz_job_listeners (public) job_name job_name
qrtz_triggers (public) job_name job_name
qrtz_triggers (public) job_name job_name
Triggers
qrtz_job_listeners (public)
Column Data Type Nullable Default PK / Index
job_name varchar(80) not null PK pk_qrtz_job_listeners
job_group varchar(80) not null PK pk_qrtz_job_listeners
job_listener varchar(80) not null PK pk_qrtz_job_listeners
Foreign Key Column Referenced Column
qrtz_job_details (public) job_name job_name
Detail Table Column Referencing Column
Triggers
qrtz_locks (public)
Column Data Type Nullable Default PK / Index
lock_name varchar(40) not null PK pk_qrtz_locks
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
qrtz_paused_trigger_grps (public)
Column Data Type Nullable Default PK / Index
trigger_group varchar(80) not null PK pk_qrtz_paused_trigger_grps
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
qrtz_scheduler_state (public)
Column Data Type Nullable Default PK / Index
instance_name varchar(80) not null PK pk_qrtz_scheduler_state
last_checkin_time bigint not null
checkin_interval bigint not null
recoverer varchar(80) null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
qrtz_simple_triggers (public)
Column Data Type Nullable Default PK / Index
trigger_name varchar(80) not null PK pk_qrtz_simple_triggers
trigger_group varchar(80) not null PK pk_qrtz_simple_triggers
repeat_count bigint not null
repeat_interval bigint not null
times_triggered bigint not null
Foreign Key Column Referenced Column
qrtz_triggers (public) trigger_name trigger_name
Detail Table Column Referencing Column
Triggers
qrtz_trigger_listeners (public)
Column Data Type Nullable Default PK / Index
trigger_name varchar(80) not null PK pk_qrtz_trigger_listeners
trigger_group varchar(80) not null PK pk_qrtz_trigger_listeners
trigger_listener varchar(80) not null PK pk_qrtz_trigger_listeners
Foreign Key Column Referenced Column
qrtz_triggers (public) trigger_name trigger_name
Detail Table Column Referencing Column
Triggers
qrtz_triggers (public)
Column Data Type Nullable Default PK / Index
trigger_name varchar(80) not null PK pk_qrtz_triggers
trigger_group varchar(80) not null PK pk_qrtz_triggers
job_name varchar(80) not null
job_group varchar(80) not null
is_volatile boolean not null
description varchar(120) null
next_fire_time bigint null
prev_fire_time bigint null
trigger_state varchar(16) not null
trigger_type varchar(8) not null
start_time bigint not null
end_time bigint null
calendar_name varchar(80) null
misfire_instr smallint null
job_data bytea null
Foreign Key Column Referenced Column
qrtz_job_details (public) job_name job_name
Detail Table Column Referencing Column
qrtz_blob_triggers (public) trigger_name trigger_name
qrtz_blob_triggers (public) trigger_name trigger_name
qrtz_cron_triggers (public) trigger_name trigger_name
qrtz_cron_triggers (public) trigger_name trigger_name
qrtz_simple_triggers (public) trigger_name trigger_name
qrtz_simple_triggers (public) trigger_name trigger_name
qrtz_trigger_listeners (public) trigger_name trigger_name
qrtz_trigger_listeners (public) trigger_name trigger_name
Triggers
reportlocator (public)
Column Data Type Nullable Default PK / Index
reportid integer not null
reportcategory varchar(256) not null
reportdate timestamp with time zone not null
reportformat varchar(256) not null
reporttype varchar(256) not null
reportlocation varchar(256) not null
reportavailable boolean not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
resourcereference (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass) PK pk_resourcereference_id
resourceid varchar(255) not null index resourcereference_resourceid
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
statisticsreportdata (public) id resourceid
Triggers
servermap (public)
Column Data Type Nullable Default PK / Index
ipaddr varchar(16) not null
servername varchar(64) not null index server_name_idx
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
service (public)
Column Data Type Nullable Default PK / Index
serviceid integer not null PK pk_serviceid
servicename varchar(32) not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ifservices (public) serviceid serviceid
outages (public) serviceid serviceid
Triggers
servicemap (public)
Column Data Type Nullable Default PK / Index
ipaddr varchar(16) not null index servicemap_ipaddr_idx
servicemapname varchar(32) not null index servicemap_name_idx
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
snmpinterface (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass) PK snmpinterface_pkey
nodeid integer not null index snmpinterface_nodeid_ifindex_unique_idx
index snmpinterface_nodeid_idx
ipaddr varchar(16) not null index snmpinterface_ipaddr_idx
snmpipadentnetmask varchar(16) null
snmpphysaddr char(12) null
snmpifindex integer not null index snmpinterface_nodeid_ifindex_unique_idx
snmpifdescr varchar(256) null
snmpiftype integer null
snmpifname varchar(96) null
snmpifspeed bigint null
snmpifadminstatus integer null
snmpifoperstatus integer null
snmpifalias varchar(256) null
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
ipinterface (public) id snmpinterfaceid
Triggers
statisticsreport (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass) PK pk_statisticsreport_id
startdate timestamp with time zone not null index statisticsreport_startdate
enddate timestamp with time zone not null
name varchar(63) not null index statisticsreport_name
description varchar(255) not null
jobstarteddate timestamp with time zone not null
jobcompleteddate timestamp with time zone not null
purgedate timestamp with time zone not null index statisticsreport_purgedate
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
statisticsreportdata (public) id reportid
Triggers
statisticsreportdata (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('opennmsnxtid'::regclass) PK pk_statsdata_id
reportid integer not null index statsdata_unique
resourceid integer not null index statsdata_unique
value double precision not null
Foreign Key Column Referenced Column
statisticsreport (public) reportid id
resourcereference (public) resourceid id
Detail Table Column Referencing Column
Triggers
stpinterface (public)
Column Data Type Nullable Default PK / Index
nodeid integer not null PK pk_stpinterface
index stpinterface_node_idx
index stpinterface_node_ifindex_idx
bridgeport integer not null PK pk_stpinterface
ifindex integer not null index stpinterface_node_ifindex_idx
stpportstate integer null
stpportpathcost integer null
stpportdesignatedroot varchar(16) null
stpportdesignatedcost integer null
stpportdesignatedbridge varchar(16) null index stpinterface_stpdesbridge_idx
stpportdesignatedport varchar(4) null
status char(1) not null
lastpolltime timestamp without time zone not null
stpvlan integer not null PK pk_stpinterface
index stpinterface_stpvlan_idx
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
stpnode (public)
Column Data Type Nullable Default PK / Index
nodeid integer not null PK pk_stpnode
index stpnode_nodeid_idx
basebridgeaddress varchar(12) not null index stpnode_basebridgeaddress_idx
basenumports integer null
basetype integer null
stpprotocolspecification integer null
stppriority integer null
stpdesignatedroot varchar(16) null index stpnode_stpdesignatedroot_idx
stprootcost integer null
stprootport integer null
status char(1) not null
lastpolltime timestamp without time zone not null
basevlan integer not null PK pk_stpnode
basevlanname varchar(32) null
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
testdatatypes (public)
Column Data Type Nullable Default PK / Index
id integer not null nextval('testdatatypes_id_seq'::regclass)
num52 numeric(5, 2) null
num63 numeric(6, 3) null
num84 numeric(8, 4) null
realvalue real null
dec52 numeric(5, 2) null
dec63 numeric(6, 3) null
dec84 numeric(8, 4) null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
usersnotified (public)
Column Data Type Nullable Default PK / Index
id integer not null PK pk_usernotificationid
userid varchar(256) not null index userid_notifyid_idx
notifyid integer null index userid_notifyid_idx
notifytime timestamp with time zone null
media varchar(32) null
contactinfo varchar(64) null
autonotify char(1) null
Foreign Key Column Referenced Column
notifications (public) notifyid notifyid
Detail Table Column Referencing Column
Triggers
vlan (public)
Column Data Type Nullable Default PK / Index
nodeid integer not null PK pk_vlan
vlanid integer not null PK pk_vlan
vlanname varchar(64) not null index vlan_vlanname_idx
vlantype integer null
vlanstatus integer null
status char(1) not null
lastpolltime timestamp without time zone not null
Foreign Key Column Referenced Column
node (public) nodeid nodeid
Detail Table Column Referencing Column
Triggers
vulnerabilities (public)
Column Data Type Nullable Default PK / Index
vulnerabilityid integer not null PK pk_vulnerabilityid
nodeid integer null index vulnerabilities_nodeid_idx
ipaddr varchar(16) null index vulnerabilities_ipaddr_idx
serviceid integer null
creationtime timestamp with time zone not null
lastattempttime timestamp with time zone not null
lastscantime timestamp with time zone not null
resolvedtime timestamp with time zone null
severity integer not null index vulnerabilities_severity_idx
pluginid integer not null
pluginsubid integer not null
logmsg varchar(256) null
descr text null
port integer null index vulnerabilities_port_idx
protocol varchar(32) null index vulnerabilities_protocol_idx
cveentry varchar(255) null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
vulnplugins (public)
Column Data Type Nullable Default PK / Index
pluginid integer not null index vulnplugins_plugin_idx
pluginsubid integer not null index vulnplugins_plugin_idx
name varchar(128) null
category varchar(32) null
copyright varchar(128) null
descr text null
summary varchar(256) null
family varchar(32) null
version varchar(32) null
cveentry varchar(255) null
md5 varchar(32) null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
Views
Functions
getmanagedoutageforintfinwindow (public)
CREATE OR REPLACE FUNCTION public.getmanagedoutageforintfinwindow(integer, character varying, timestamp without time zone, timestamp without time zone)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	ipid ALIAS FOR $2;
	xtime ALIAS FOR $3;
	ytime ALIAS FOR $4;
	downtime float8 := 0.0;
	orec RECORD;
   BEGIN
	FOR orec IN SELECT distinct ifservices.nodeid, ifservices.ipaddr, ifservices.serviceid FROM ipinterface, ifservices where ifservices.nodeid = nid AND ifservices.ipaddr = ipid AND ipinterface.nodeid = nid AND ipinterface.ipaddr = ipid AND ipinterface.ismanaged = 'M' AND ifservices.status = 'A'
	LOOP
		BEGIN
			downtime := downtime + getOutageTimeInWindow( orec.nodeid, orec.ipaddr, orec.serviceid, xtime, ytime);
		END;
	END LOOP;
	RETURN downtime;
   END;
$function$
getmanagedoutagefornodeinwindow (public)
CREATE OR REPLACE FUNCTION public.getmanagedoutagefornodeinwindow(integer, timestamp without time zone, timestamp without time zone)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	xtime ALIAS FOR $2;
	ytime ALIAS FOR $3;
	downtime float8 := 0.0;
	orec RECORD;
   BEGIN
	FOR orec IN SELECT distinct ifservices.nodeid, ifservices.ipaddr, ifservices.serviceid FROM ipinterface, ifservices, node where ifservices.nodeid = nid AND ipinterface.nodeid = nid AND ipinterface.ismanaged = 'M' AND ifservices.ipaddr = ipinterface.ipaddr AND ifservices.status = 'A' AND node.nodeid = nid and node.nodetype = 'A'
	LOOP
		BEGIN
			downtime := downtime + getOutageTimeInWindow( orec.nodeid, orec.ipaddr, orec.serviceid, xtime, ytime);
		END;
	END LOOP;
	RETURN downtime;
   END;
$function$
getmanagedservicecountforintf (public)
CREATE OR REPLACE FUNCTION public.getmanagedservicecountforintf(integer, character varying)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	ipid ALIAS FOR $2;
	orec RECORD;
	counter float8;
   BEGIN
	counter = 0;
	FOR orec IN SELECT DISTINCT ifservices.nodeid, ifservices.ipaddr, ifservices.serviceid 
		FROM ipinterface, ifservices 
		WHERE ifservices.nodeid = nid 
			AND ifservices.ipaddr = ipid 
			AND ipinterface.nodeid = nid 
			AND ipinterface.ipaddr = ipid 
			AND ipinterface.ismanaged = 'M' 
			AND ifservices.status = 'A'
	LOOP
		BEGIN
			counter := counter + 1;
		END;
	END LOOP;
	RETURN counter;
   END;
$function$
getmanagedservicecountfornode (public)
CREATE OR REPLACE FUNCTION public.getmanagedservicecountfornode(integer)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
        nid ALIAS FOR $1;
        orec RECORD;
        counter float8;
   BEGIN
        counter = 0;
         FOR orec IN SELECT distinct ifservices.nodeid, ifservices.serviceid, ifservices.ipaddr
                FROM ipinterface, ifservices
                WHERE ifservices.nodeid = nid
                        AND ipinterface.nodeid = nid
                        AND ipinterface.ismanaged = 'M'
                        AND ifservices.ipaddr = ipinterface.ipaddr
                        AND ifservices.status = 'A'
        LOOP
                BEGIN
                         counter := counter + 1;
                END;
        END LOOP;
        RETURN counter;
   END;
$function$
getmanagepercentavailintfwindow (public)
CREATE OR REPLACE FUNCTION public.getmanagepercentavailintfwindow(integer, character varying, timestamp without time zone, timestamp without time zone)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	ipid ALIAS FOR $2;
	xtime ALIAS FOR $3;
	ytime ALIAS FOR $4;
	downtime float8 := 0.0;
	count integer := 0;
	rollingWindow float := 0;
	totalServiceTime float := 0;
   BEGIN
	IF xtime < ytime THEN
		rollingWindow := EXTRACT (EPOCH FROM (ytime - xtime));
		downtime := getManagedOutageForIntfInWindow(nid, ipid, ytime, xtime)/1000;
	ELSE
		rollingWindow := EXTRACT (EPOCH FROM (xtime - ytime));
		downtime := getManagedOutageForIntfInWindow(nid, ipid, xtime, ytime)/1000;
	END IF;
	count := getManagedServiceCountForIntf(nid, ipid);
	totalServiceTime := count * rollingWindow;

	IF totalServiceTime > 0 THEN
		RETURN	100 * (1 - (downtime / totalServiceTime));
	ELSE
                IF totalServiceTime = 0 THEN
                        RETURN 100;
                ELSE
                        RETURN -1;
                END IF;
	END IF;    
   END;
$function$
getmanagepercentavailnodewindow (public)
CREATE OR REPLACE FUNCTION public.getmanagepercentavailnodewindow(integer, timestamp without time zone, timestamp without time zone)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	xtime ALIAS FOR $2;
	ytime ALIAS FOR $3;
	downtime float8 := 0.0;
	count integer := 0;
	rollingWindow float := 0;
	totalServiceTime float := 0;
   BEGIN
	IF xtime < ytime THEN
		rollingWindow := EXTRACT (EPOCH FROM (ytime - xtime));
		downtime := getManagedOutageForNodeInWindow(nid, ytime, xtime)/1000;
	ELSE
		rollingWindow := EXTRACT (EPOCH FROM (xtime - ytime));
		downtime := getManagedOutageForNodeInWindow(nid, xtime, ytime)/1000;
	END IF;
	count := getManagedServiceCountForNode(nid);
	totalServiceTime := count * rollingWindow;

	IF totalServiceTime > 0 THEN
		RETURN 100 * (1 - (downtime / totalServiceTime));
	ELSE
		IF totalServiceTime = 0 THEN
                        RETURN 100;
                ELSE
                        RETURN -1;
                END IF;
	END IF;
   END;
$function$
getoutagetimeinwindow (public)
CREATE OR REPLACE FUNCTION public.getoutagetimeinwindow(integer, character varying, integer, timestamp without time zone, timestamp without time zone)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	ipid ALIAS FOR $2;
	sid ALIAS FOR $3;
	xtime ALIAS FOR $4;
	ytime ALIAS FOR $5;
	orec RECORD;
	lostTime timestamp without time zone;
	gainTime timestamp without time zone;
	downtime float8;
	zero CONSTANT float8 := 0.0;
	epochTime CONSTANT timestamp without time zone := to_timestamp('01 Jan 1970 00:00:00', 'DD Mon YYYY HH24:MI:SS');
   BEGIN
   	downtime = zero;
   	FOR orec IN SELECT ifLostService,ifRegainedService
		FROM outages WHERE (nodeID = nid AND ipAddr = ipid AND serviceID = sid AND
		                   ((ifRegainedService IS NULL AND ifLostService <= xtime)  OR
				    (ifRegainedService > ytime)))
	LOOP
	 BEGIN
		gainTime := epochTime;
		lostTime := orec.ifLostService;
		IF orec.ifRegainedService IS NOT NULL THEN
			gainTime := orec.ifRegainedService;
		END IF;
		--
		-- Find the appropriate records
		--
		IF xtime > lostTime THEN
		 --
		 -- for any outage to be in window of 
		 -- opportunity the lost time must ALWAYS be
		 -- less that the x time.
		 --
		 IF gainTime = epochTime THEN
		  --
		  -- if the gain time is epochTime then the outage
		  -- does not have an uptime.
		  --
		   IF ytime > lostTime THEN
		    downtime := downtime + EXTRACT(EPOCH FROM (xtime - ytime));
		   ELSE
		    downtime := downtime + EXTRACT(EPOCH FROM (xtime - lostTime));
		   END IF;
		 ELSE
		  IF xtime > gainTime AND gainTime > ytime THEN
		   --
		   -- regain time between x & y
		   --
		    IF ytime > lostTime THEN
		     downtime := downtime + EXTRACT (EPOCH FROM (gainTime - ytime));
		    ELSE
		     downtime := downtime + EXTRACT (EPOCH FROM (gainTime - lostTime));
		    END IF; 
		  ELSE
		   IF gainTime > xtime THEN
		   --
		   -- regain time greater than x, lost less that x
		   --
		    IF ytime > lostTime THEN
		     downtime := downtime + EXTRACT (EPOCH FROM (xtime - ytime));
		    ELSE
		     downtime := downtime + EXTRACT (EPOCH FROM (xtime - lostTime));
		    END IF;
		   -- end gainTime > xtime
		   END IF;
		  -- end xtime > gainTime AND gainTime > ytime
		  END IF;
		 -- end gaintime == epochTime
		 END IF;
		-- end xtime > lostTime
		END IF;
	 END;
	END LOOP;
	RETURN downtime*1000.0;
   END;
$function$
getpercentavailabilityinwindow (public)
CREATE OR REPLACE FUNCTION public.getpercentavailabilityinwindow(integer, character varying, integer, timestamp without time zone, timestamp without time zone)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	ipid ALIAS FOR $2;
	sid ALIAS FOR $3;
	xtime ALIAS FOR $4;
	ytime ALIAS FOR $5;
	downtime float8;
   BEGIN
	downtime := getOutageTimeInWindow(nid, ipid, sid, xtime, ytime);
	IF xtime > ytime THEN
		RETURN 100 * (1 - (downtime / (EXTRACT(EPOCH FROM (xtime - ytime))* 1000)));
	ELSE
		RETURN 100 * (1 - (downtime / (EXTRACT(EPOCH FROM (ytime - xtime))* 1000)));
	END IF;
   END;
$function$
Trigger Functions
setifservicekeysoninsert (public)
CREATE OR REPLACE FUNCTION public.setifservicekeysoninsert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

  --
  -- (Used with Trigger Insert with old style foreign key)
  -- This condition keeps the ifServiceID inSync with the composite foreign key of nodeid, ipaddr, serviceid
  -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
  -- code has no knowledge of the new keys
  --
  IF NEW.ifServiceId IS NULL 
  THEN
     SELECT ifsvc.id INTO NEW.ifserviceid
       FROM ifservices ifsvc
       WHERE (ifsvc.nodeid = NEW.nodeid AND ifsvc.ipAddr = NEW.ipAddr AND ifsvc.serviceid = NEW.serviceid);
       
     IF NOT FOUND 
     THEN
        RAISE EXCEPTION 'Outages Trigger Exception, Condition 1: No service found for... nodeid: %  ipaddr: %  serviceid: %', NEW.nodeid, NEW.ipAddr, NEW.serviceid;
     END IF;
  
  --
  -- (Used with Trigger Insert with new style foreign key)
  -- This condition keeps the composite foreign key of nodeid, ipaddr, serviceid inSync with the ifserviceid
  -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
  -- the composite key columns
  --
  ELSIF NEW.ifServiceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ipAddr IS NULL OR NEW.serviceId IS NULL)
  THEN
     SELECT ifsvc.nodeId, ifsvc.ipAddr, ifsvc.serviceId INTO NEW.nodeId, NEW.ipAddr, NEW.serviceId
       FROM ifservices ifsvc
      WHERE (ifsvc.id = NEW.ifServiceId);
      
      IF NOT FOUND THEN
         RAISE EXCEPTION 'Outages Trigger Exception, Condition 2: No service found for serviceID: %', NEW.ifServiceId;
      END IF;

  END IF;
  
  RETURN NEW;
END;
$function$
setifservicekeysonupdate (public)
CREATE OR REPLACE FUNCTION public.setifservicekeysonupdate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

  --
  -- (Update with old style foreign key)
  -- This condition keeps ifserviceid inSync with the composite foreign key of nodeid, ipaddr, serviceid
  -- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
  -- one or more of the composite key values, the ifServiceId needs to be updated
  --
  IF (NEW.ifserviceID = OLD.ifServiceId) AND (NEW.nodeId != OLD.nodeId OR NEW.ipAddr != OLD.ipAddr OR NEW.serviceId != OLD.serviceID) 
  THEN
     SELECT ifsvc.id INTO NEW.ifserviceid
       FROM ifservices ifsvc
       WHERE (ifsvc.nodeid = NEW.nodeid AND ifsvc.ipAddr = NEW.ipAddr AND ifsvc.serviceid = NEW.serviceid);
       
     IF NOT FOUND THEN
        RAISE EXCEPTION 'Outages Trigger Exception, Condition 3: No service found for... nodeid: %  ipaddr: %  serviceid: %', NEW.nodeid, NEW.ipAddr, NEW.serviceid;
     END IF;
  --
  -- (Update with new style foreign key)
  -- This condition keeps the composite foreign key of nodeid, ipaddr, serviceid inSync with the ifserviceid
  -- This usually happens with the Hibernate DAOs decide to change the ifserviceid (MonitoredService) represented
  -- by the outage.
  --
  ELSIF NEW.ifServiceId != OLD.ifServiceId
  THEN
     SELECT ifsvc.nodeId, ifsvc.ipAddr, ifsvc.serviceId INTO NEW.nodeId, NEW.ipAddr, NEW.serviceId
       FROM ifservices ifsvc
      WHERE (ifsvc.id = NEW.ifServiceId);
      
      IF NOT FOUND THEN
         RAISE EXCEPTION 'Outages Trigger Exception, Condition 4: No service found for serviceID: %', NEW.ifServiceId;
      END IF;
  END IF;

  RETURN NEW;
END;
$function$
setipinterfacekeysoninsert (public)
CREATE OR REPLACE FUNCTION public.setipinterfacekeysoninsert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

  -- ifServices must have an IP address that is *not* 0.0.0.0
  IF NEW.ipAddr IS NOT NULL AND NEW.ipAddr = '0.0.0.0'
  THEN
    RAISE EXCEPTION 'IfServices Trigger Exception, Condition 0: ipAddr of 0.0.0.0 is not allowed in ifServices table';
  END IF;
  
  --
  -- (Insert with old style foreign key)
  -- This condition keeps the ipInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
  -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
  -- code has no knowledge of the new keys
  --
  IF NEW.ipInterfaceId IS NULL 
  THEN
     SELECT ipif.id INTO NEW.ipInterfaceId
       FROM ipinterface ipif
       WHERE (ipif.nodeid = NEW.nodeid AND ipif.ipAddr = NEW.ipAddr AND ipif.ipAddr != '0.0.0.0');
       
       IF NOT FOUND 
       THEN
          RAISE EXCEPTION 'IfServices Trigger Exception, Condition 1: No IpInterface found for... nodeid: %  ipaddr: %', NEW.nodeid, NEW.ipAddr;
       END IF;
       
  --
  -- (Insert with new style foreign key)
  -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipInterfaceId
  -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
  -- the composite key columns
  --

  ELSIF NEW.ipInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ipAddr IS NULL)
  THEN
     SELECT ipif.nodeid, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeid, NEW.ipAddr, NEW.ifIndex
       FROM ipinterface ipif
      WHERE (ipif.id = NEW.ipInterfaceId);
      
      IF NOT FOUND
      THEN
         RAISE EXCEPTION 'IfServices Trigger Exception: No ipinterface found for ipInterfaceId: %', NEW.ipInterfaceId;
      END IF;
      
      IF NEW.ipAddr = '0.0.0.0'
      THEN
         RAISE EXCEPTION 'IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr', NEW.ipInterfaceId;
      END IF;
  END IF;
  RETURN NEW;
END;
$function$
setipinterfacekeysonupdate (public)
CREATE OR REPLACE FUNCTION public.setipinterfacekeysonupdate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

  -- ifServices must have an IP address that is *not* 0.0.0.0
  IF NEW.ipAddr IS NOT NULL AND NEW.ipAddr = '0.0.0.0'
  THEN
    RAISE EXCEPTION 'IfServices Trigger Exception, Condition 0: ipAddr of 0.0.0.0 is not allowed in ifServices table';
  END IF;

  --
  -- (Used with Trigger Update with old style foreign key)
  -- This condition keeps ipinterfaceid inSync with the composite foreign key of nodeid, ipaddr, ifindex
  -- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
  -- one or more of the composite key values, the ipInterfaceId needs to be updated
  --
  IF (NEW.ipInterfaceId = OLD.ipInterfaceId) AND (NEW.nodeId != OLD.nodeId OR NEW.ipAddr != OLD.ipAddr) 
  THEN
     SELECT ipif.id INTO NEW.ipInterfaceId
       FROM ipinterface ipif
       WHERE (ipif.nodeid = NEW.nodeid AND ipif.ipAddr = NEW.ipAddr AND ipif.ipAddr != '0.0.0.0');
       
     IF NOT FOUND THEN
        RAISE EXCEPTION 'IfServices Trigger Exception, Condition 3: No IpInterface found for... nodeid: %  ipaddr: % ', NEW.nodeid, NEW.ipAddr;
     END IF;
     
  --
  -- (Used with Trigger Update with new style foreign key)
  -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipinterfaceid
  -- This usually happens with the Hibernate DAOs decide to change the ipinterfaceid represented
  -- by the ifservices.
  --
  ELSIF NEW.ipInterfaceId != OLD.ipInterfaceId
  THEN
     SELECT ipif.nodeId, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeId, NEW.ipAddr, NEW.ifIndex
       FROM ipinterface ipif
      WHERE (ipif.id = NEW.ipInterfaceId);
      
      IF NOT FOUND THEN
         RAISE EXCEPTION 'IfServices Trigger Exception, Condition 4: No IpInterface found for ipInterfaceId: %', NEW.ipInterfaceId;
      END IF;
      
      IF NEW.ipAddr = '0.0.0.0'
      THEN
         RAISE EXCEPTION 'IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr', NEW.ipInterfaceId;
      END IF;
  END IF;

  RETURN NEW;
END;
$function$
setsnmpinterfacekeysoninsert (public)
CREATE OR REPLACE FUNCTION public.setsnmpinterfacekeysoninsert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

  --
  -- (Used for Trigger insert with old style foreign key)
  -- This condition keeps the snmpInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
  -- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
  -- code has no knowledge of the new keys
  --
  IF NEW.snmpInterfaceId IS NULL 
  THEN
     IF NEW.ifIndex IS NOT NULL
     THEN
       SELECT snmpif.id INTO NEW.snmpInterfaceId
         FROM snmpinterface snmpif
         WHERE (snmpif.nodeid = NEW.nodeid AND snmpif.snmpIfIndex = NEW.ifIndex);
       
       IF NOT FOUND 
       THEN
         RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 1: No SnmpInterface found for... nodeid: % ifindex: %', NEW.nodeid, NEW.ifIndex;
       END IF;
     END IF;
       
  --
  -- (Used for Insert with new style foreign key)
  -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the SnmpInterfaceId
  -- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
  -- the composite key columns
  --

  ELSIF NEW.snmpInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ifIndex IS NULL)
  THEN
     SELECT snmpif.nodeid, snmpif.snmpIfIndex INTO NEW.nodeid, NEW.ifIndex
       FROM snmpinterface snmpif
      WHERE (snmpif.id = NEW.snmpInterfaceId);
      
      IF NOT FOUND
      THEN
         RAISE EXCEPTION 'IpInterface Trigger Notice: No SnmpInterface found for snmpInterfaceId: %', NEW.snmpInterfaceId;
      END IF;
  END IF;
  RETURN NEW;
END;
$function$
setsnmpinterfacekeysonupdate (public)
CREATE OR REPLACE FUNCTION public.setsnmpinterfacekeysonupdate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

  --
  -- (Used for Trigger update with old style foreign key)
  -- This condition keeps snmpinterfaceid inSync with the composite foreign key of nodeid, ipaddr, ifindex
  -- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
  -- one or more of the composite key values, the snmpInterfaceId needs to be updated
  --
  IF ((NEW.snmpInterfaceId = OLD.snmpInterfaceId OR (NEW.snmpInterfaceId IS NULL AND OLD.snmpInterfaceId IS NULL)) AND 
      (NEW.nodeId != OLD.nodeId OR NEW.ifIndex != OLD.ifIndex OR (NEW.ifIndex IS NULL AND OLD.ifIndex IS NOT NULL) OR (NEW.ifIndex IS NOT NULL AND OLD.ifIndex IS NULL)))
  THEN
    IF NEW.ifIndex IS NULL AND NEW.snmpInterfaceId IS NOT NULL
    THEN
       SELECT NULL INTO NEW.snmpInterfaceId;
    ELSIF NEW.ifIndex IS NOT NULL
    THEN
     SELECT snmpif.id INTO NEW.snmpInterfaceId
       FROM snmpinterface snmpif
       WHERE (snmpif.nodeid = NEW.nodeid AND snmpif.snmpIfIndex = NEW.ifIndex);
       
     IF NOT FOUND THEN
       RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 3: No SnmpInterface found for... nodeid: % ifindex: %', NEW.nodeid, NEW.ifIndex;
     END IF;
    END IF;
     
  --
  -- (Used for Trigger update with new style foreign key)
  -- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the snmpinterfaceid
  -- This usually happens with the Hibernate DAOs decide to change the snmpinterfaceid represented
  -- by the ipinterface.
  --
  -- We dont match on the case where NEW.snmpInterfaceId IS NULL, because we use it in the WHERE clause.
  --
  ELSIF (NEW.snmpInterfaceId != OLD.snmpInterfaceId OR (NEW.snmpInterfaceId IS NOT NULL AND OLD.snmpInterfaceId IS NULL))
  THEN
     SELECT snmpif.nodeId, snmpif.snmpIfIndex INTO NEW.nodeId, NEW.ifIndex
       FROM snmpinterface snmpif
      WHERE (snmpif.id = NEW.snmpInterfaceId);
      
      IF NOT FOUND THEN
         RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 4: No SnmpInterface found for snmpInterfaceId: %', NEW.snmpInterfaceId;
      END IF;
  END IF;

  RETURN NEW;
END;
$function$
Sequences
alarmsnxtid (public)
CREATE SEQUENCE public.alarmsnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
catnxtid (public)
CREATE SEQUENCE public.catnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
demandpollnxtid (public)
CREATE SEQUENCE public.demandpollnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
eventsnxtid (public)
CREATE SEQUENCE public.eventsnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
mapnxtid (public)
CREATE SEQUENCE public.mapnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
nodenxtid (public)
CREATE SEQUENCE public.nodenxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
notifynxtid (public)
CREATE SEQUENCE public.notifynxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
opennmsnxtid (public)
CREATE SEQUENCE public.opennmsnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
outagenxtid (public)
CREATE SEQUENCE public.outagenxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
pollresultnxtid (public)
CREATE SEQUENCE public.pollresultnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
reportnxtid (public)
CREATE SEQUENCE public.reportnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
servicenxtid (public)
CREATE SEQUENCE public.servicenxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
testdatatypes_id_seq (public)
CREATE SEQUENCE public.testdatatypes_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
usernotifnxtid (public)
CREATE SEQUENCE public.usernotifnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 
vulnnxtid (public)
CREATE SEQUENCE public.vulnnxtid
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1 NO CYCLE
 

generated by dbscript