Just an FYI - HSQLDB says that 'unique' indexing is deprecated and that you should instead use a constraint instead.

On 6/29/07, DJ Gregor < dj@opennms.org> wrote:
First off, cool work!  It would be great to see OpenNMS be able to run
on multiple databases.  We've been slowly working in this direction over

I'll respond to the pieces that I know about.

On Fri, 29 Jun 2007 09:49:26 -0400, "Stephen Ince"
< since@opendemand.com> said:
> 2) Contraint location_monitor_fkey1 is defined twice, in
> location_specific_status_changes and location_monitor_details. Is this
> intentional? Hsqldb and derby don't support using the same constraint
> name?

This is a goof.  I just committed a fix to revision 6630.  Thanks for
noticing this!

> 3) Hsqldb and derby don't support a where clause for an index. Is it okay
> just to drop it.
> change
> create unique index ipinterface_nodeid_ipaddr_notzero_idx on ipInterface
> (nodeID, ipAddr) WHERE ipAddr != '';
> to
> create unique index ipinterface_nodeid_ipaddr_notzero_idx on ipInterface
> (nodeID, ipAddr);

If you remove the WHERE clause, you'll need to make the index
non-unique.   Non-IP interfaces (ones that we discover with SNMP that
can't be mapped to an IP interface) are inserted into the ipInterface
table with a '' ipAddr, and there are often multiple non-IP
interfaces on a node.  IP interfaces *must* be unique on a node, hence
why we created that index, but unfortunately they are mixed with non-IP
interfaces in the same table and we insert those records into the table
with '' instead of null.

Here is what you want:

create index ipinterface_nodeid_ipaddr_idx on ipInterface (nodeID,

> > 4) Table outages has the following contraint (nodeId, ipAddr, serviceId)
> REFERENCES ifServices (nodeId, ipAddr, serviceId). Hsqldb complains that
> the
> tuple ifServices (nodeId, ipAddr, serviceId) is not unique.
> Should there be a unique  (nodeId, ipAddr, serviceId) contraint on table
> ifServices.

Hmmm... this doesn't cover it?

create unique index ifservices_nodeid_ipaddr_svc_unique on
ifservices(nodeID, ipAddr, serviceId);

> 5) The following ipInterface constraint is trying to set id to null on
> delete. Id is not null column? Is this right.
> create table ipInterface (
>     id              INTEGER DEFAULT nextval('opennmsNxtId') NOT NULL,
> :
> -- CONSTRAINT snmpinterface_fkey2 FOREIGN KEY (snmpInterfaceId)
> snmpInterface (id) ON DELETE SET NULL
> );

I wonder if that means that we should make the snmpInterfaceId column
null if the referenced row is deleted.... the PostgreSQL docs should
say, and Matt Brozowski or David might know off the top of their heads.

A few additional things:
1) Don't forget about our create/update triggers
2) Don't forget about the stored procedures used for querying
3) Don't forget about iplike.
4) Don't forget that you'll have to tweak the Hibernate configuration to
create proper queries for hsqldb, derby.
5) Some configuration files have database-specific configuration.  Do:
grep -i nextval $OPENNMS_HOME/*.xml
6) We *might* have some SQL elsewhere outside of test code that does
PostgreSQL-specific things, like multi-column subselects.

This documents some of that:

Good luck, and let us know if you have any questions or issues.

        - djg

This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
Please read the OpenNMS Mailing List FAQ:

opennms-devel mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:

Matt Raykowski