[opennms-devel] Changes to create.sql from 1.1.3 to 1.1.4

[DJ G. <dj...@gr...>]

I've spent a few hours analyzing the differences between the create.sql 
file in 1.1.3 and 1.1.4 to help me make sure that the installer won't 
hiccup when translating people's data into the new table formats.  I 
think that I've found one set of "bugs" that should be reviewed in 
create.sql before 1.1.4 goes out, and the rest is mostly informational. 
  Some installer changes should be made to clean up after old indexes, 
but this isn't critical for the release (please let me know if you 
think this is wrong).

I have *not* analyzed if the changes introduced in 1.1.4 will cause any 
problems for people using pre-1.1.3 database schema.

All of the changes to create.sql fall into these categories:

1) New foreign key constraints (they all have "ON DELETE CASCADE");
2) Existing foreign key constraints that have "ON DELETE CASCADE" added;
3) Existing foreign key constraints where the constrained column has 
had "NOT NULL" removed.  I believe this was done in error;
4) Existing indexes removed (excluding those in #6) or new indexes 
added;
5) "CASCADE" added to all of the "DROP TABLE" commands;
6) Unique indexes that have been removed because their functionality is 
duplicated by primary key constraints on the same columns;
7) "NOT NULL" constraints removed from columns because their 
functionality is duplicated by primary key constraints on the same 
columns.

The three new foreign key constraints in #1 are the most likely to 
cause problems for people who have fooled around with their databases.  
Bug #931 has SQL code that should take care of the offending rows:

	http://bugzilla.opennms.org/cgi-bin/bugzilla/show_bug.cgi?id=931#c5

The other foreign key constraints that have "ON DELETE CASCADE" added 
(#2) shouldn't cause a problem as the foreign key constraints already 
existed in 1.1.3, and if the target of the constraint was removed, it 
would just fail with a foreign key constraint error.

Matt and/or David, please look into #3, as I believe these changes were 
made in error.  Primary key constraints imply NOT NULL, however foreign 
key constraints do not.

I don't think that any of the changes in #4 will cause problems, as 
only one unique index is created, and it's actually identical to a 
unique index that existed in 1.1.3, just under a different name.  The 
installer does *not* catch when an index is removed or when its name 
has changed, so I just committed a change to create.sql to use the 
1.1.3 name.  Anyone who played around with a post-1.1.3 CVS snapshot 
should do "drop index vulnplugins_pluginid_pluginsubid_idx".

#5 is pretty trivial, and it doesn't matter for the installer because 
it ignores everything after the table name in "DROP TABLE" commands 
when it is reading in the database schema.  The installer will add 
"CASCADE" if the PostgreSQL database version is >= 7.3 (7.2 doesn't 
support "DROP TABLE ... CASCADE").

#6 is also trivial, but the installer should remove those indexes if 
they already exist.  I'll have to see about adding that...

Lastly, #7 is also handled by the installer, and if that is the only 
change made to a table, the installer shouldn't make any changes to the 
table, since it knows that the column has a NOT NULL constraint no 
matter what, since that is implied by the primary key constraint.

The one interesting note that I have is that there are only two foreign 
key constraints left that do not have "ON DELETE CASCADE".  I'm just 
mentioning this here for completeness, I guess, and in case one or both 
of these should have "ON DELETE CASCADE":

fk_dpName: node(dpName) -> distPoller(dpName)
fk_nodeID5: assets(nodeID) -> node(nodeID)

Another thing that I just stumbled upon: there are four columns which 
have foreign key constraints and don't have "NOT NULL" set.  They 
aren't listed in #2 because they either didn't have the constraint in 
1.1.3 (the ones in the outages table), or they are new constraints.  
Again, this might be planned (and probably is), but I'm just mentioning 
it for completeness:

events(nodeID) / fk_nodeID6 -> node(nodeID)
outages(svcLostEventID) / fk_eventID1 -> events(eventID)
outages(svcRegainedEventID) / fk_eventID2 -> events(eventID)
notifications(nodeID) / fk_nodeID7 -> node(nodeID)


Here are the gory details:


1) New foreign keys (they all have "ON DELETE CASCADE"):

fk_nodeID6:     events(nodeID) -> node(nodeID)
fk_nodeID7:     notifications(nodeID) -> node(nodeID)
fk_notifID2:    usersNotified(notifyID) -> notifications(notifyID)


2) Existing foreign keys that have "ON DELETE CASCADE" added:

fk_nodeID1:     ipInterface(nodeID) -> node(nodeID)
fk_nodeID2:     snmpInterface(nodeID) -> node(nodeID)
fk_nodeID3:     ifServices(nodeID) -> node(nodeID)
fk_serviceID1:  ifServices(serviceID) -> service(serviceID)
fk_eventID1:    outages(svcLostEventID) -> events(eventID)
fk_eventID2:    outages(svcRegainedEventID) -> events(eventID)
fk_nodeID4:     outages(nodeID) -> node(nodeID)
fk_serviceID2:  outages(serviceID) -> service(serviceID)
fk_eventID3:    notifications(eventID) -> events(eventID)


3) Existing foreign keys where the constrained column has had "NOT 
NULL" removed.  I believe this was done in error:

node(dpName) had NOT NULL removed: not the same functionality as 
fk_dpName on node(dpName) -> distPoller(dpName)
ipInterface(nodeID) had NOT NULL removed: not the same functionality as 
fk_nodeID1 on ipInterface(nodeID) -> node(nodeID)
snmpInterface(nodeID) had NOT NULL removed: not the same functionality 
as fk_nodeID2 on snmpInterface(nodeID) -> node(nodeID)
ifServices(nodeID) had NOT NULL removed: not the same functionality as 
fk_nodeID3 on ifServices(nodeID) -> node(nodeID)
ifServices(serviceID) had NOT NULL removed: not the same functionality 
as fk_serviceID1 on ifServices(serviceID) -> service(serviceID)
outages(nodeID) had NOT NULL removed: not the same functionality as 
fk_nodeID4 on outages(nodeID) -> node(nodeID)
outages(serviceID) had NOT NULL removed: not the same functionality as 
fk_serviceID2 on outages(serviceID) -> service(serviceID)
notifications(eventID) had NOT NULL removed: not the same functionality 
as fk_eventID3 on notifications(eventID) -> events(eventID)
usersNotified(notifyID) had NOT NULL removed: not the same 
functionality as fk_notifID2 on usersNotified(notifyID) -> 
notifications(notifyID)
assets(nodeID) had NOT NULL removed: not the same functionality as 
fk_nodeID5 on assets(nodeID) -> node(nodeID)


4) Existing indexes removed (excluding those in #6) or new indexes 
added:

ifservices_nodeid_serviceid_idx added: index on ifservices(nodeID, 
serviceID)
vulnplugins_pluginid_pluginsubid_idx added: unique index on 
vulnPlugins(pluginID, pluginSubID)
vulnplugins_plugin_idx removed: unique index on vulnPlugins(pluginID, 
pluginSubID)
notifications_nodeid_idx removed: index on notifications(nodeID)
userid_notifyid_idx added: index on usersNotified(userID, notifyID)


5) "CASCADE" added to all of the "DROP TABLE" commands:

"CASCADE" has been added to all of the "DROP TABLE" commands.  No need 
to list all of them here. :-)


6) Unique indexes that have been removed because their functionality is 
duplicated by primary key constraints on the same columns:

unique index node_id_idx removed on node(nodeID): same functionality as 
pk_nodeID on node(nodeID)
unique index events_id_idx removed on events(eventID): same 
functionality as pk_eventID on events(eventID)
unique index outages_id_idx removed on outages(outageID): same 
functionality as pk_outageID on outages(outageID)
unique index vulnerabilities_id_idx on 
vulnerabilities(vulnerabilityID): same functionality as 
pk_vulnerabilityID on vulnerabilities(vulnerabilityID)
unique index notifications_id_idx on notifications(notifyID): same 
functionality as pk_notifyID on notifications(notifyID)


7) "NOT NULL" constraints removed from columns because their 
functionality is duplicated by primary key constraints on the same 
columns:

removed NOT NULL from distPoller(dpName): same functionality as 
pk_dpName on distPoller(dpName)
removed NOT NULL from node(nodeID): same functionality as pk_nodeID on 
node(nodeID)
removed NOT NULL from service(serviceID): same functionality as 
pk_serviceID on service(serviceID)
removed NOT NULL from events(eventID): same functionality as pk_eventID 
on events(eventID)
removed NOT NULL from outages(outageID): same functionality as 
pk_outageID on outages(outageID)
removed NOT NULL from vulnerabilties(vulnerabilityID): same 
functionality as pk_vulnerabilityID on vulnerabilities(vulnerabilityID)
removed NOT NULL from notifications(notifyID): same functionality as 
pk_notifyID on notifications(notifyID)



	That's all, folks!
	- djg