Re: [opennms-devel] Opennms port to firebird and DB2.

[David H. <da...@op...>]

On Jul 9, 2007, at 12:16 AM, DJ Gregor wrote:

> On Sun, 8 Jul 2007 23:41:44 -0400, "Stephen Ince"  
> <si...@op...>
> said:
>> 1) DB2 doesn't like primay keys to be nullable. Is this intentional?
>
> Our primary keys aren't nullable under PostgreSQL.  PostgreSQL assumes
> "NOT NULL" for primary keys.  If you look at the table definition  
> in the
> PostgreSQL database for a column that is a primary key, you'll see the
> NOT NULL constraint.  E.g.:
>
> opennms=# \d categories
>                 Table "public.categories"
>        Column        |          Type          | Modifiers
> ---------------------+------------------------+-----------
>  categoryid          | integer                | not null
>  categoryname        | character varying(64)  | not null
>  categorydescription | character varying(256) |
> Indexes:
>     "category_pkey" PRIMARY KEY, btree (categoryid)
>     "category_idx" UNIQUE, btree (categoryname)
>
>> From the PostgreSQL docs:
>
> "5.3.4. Primary Keys"
> "Technically, a primary key constraint is simply a combination of a
> unique constraint and a not-null constraint."
>
> http://www.postgresql.org/docs/8.2/interactive/ddl- 
> constraints.html#AEN2080
>
> All of the examples that you provided are indeed primary keys, so they
> have the assumed NOT NULL constraint in PostgreSQL.  We probably  
> should
> add the NOT NULL constraints back to be explicit, or at least discuss
> it.  Want to provide a patch (open up an enhancement bug and attach it
> there)?
>
>> 2) DB2 doesn't like unique keys to be nullable. Is this intentional?
>
> I'm not sure about all of the specific cases you mention.  I do know
> that this functionality is allowed with PostgreSQL:
>
> "5.3.3. Unique Constraints"
> "In general, a unique constraint is violated when there are two or  
> more
> rows in the table where the values of all of the columns included  
> in the
> constraint are equal. However, two null values are not considered  
> equal
> in this comparison. That means even in the presence of a unique
> constraint it is possible to store duplicate rows that contain a null
> value in at least one of the constrained columns. This behavior  
> conforms
> to the SQL standard, but we have heard that other SQL databases may  
> not
> follow this rule. So be careful when developing applications that are
> intended to be portable."
>
> http://www.postgresql.org/docs/8.2/interactive/ddl- 
> constraints.html#AEN2058
>
>> e.g.
>> create table alarms (
>>  reductionKey            VARCHAR(256),
>
> David?  Should this be allowed to be nullable or should it have a NOT
> NULL constraint?

I'm willing to accept a NOT NULL constraint with a DEFAULT set to  
"reduction-key-required".  The reduction-key attribute is required by  
the alarm-data element in the event schema.


>> CREATE TABLE alarm_attributes (
>>      attributeName   VARCHAR(63),
>>     attributeValue  VARCHAR(255)
>> );
>
> I think that these should probably have NOT NULL constraints.   
> David, do
> you agree?

When I think of a NOT NULL constrained field, I try to imagine a  
functional default value and in this case, I can't think of one and  
my DB experience inclines me to not add the constraint in this  
situation.  Perhaps after the use case is added to alarms that  
requires these attributes it will make more sense.  I planned  to do  
this work at DevJam since that hard part is going to be the GUI work.


>> create table application_service_map (
>>  appId  integer,
>>  ifServiceId integer,
>
> These should have NOT NULL constraints, as well.

Yup.  However, if there were foreign-key constraints on them (at  
there might possibly be some), that would imply NOT NULL.


>> 3) DB2 doesn't like constraint or index names to be longer that 18
>> character. Easy one to get around. I just truncated the index  
>> names to
>> 18.
>> ...
>> I also just removed the constraint names. The constraint names are
>> probably
>> only necessary if you are going to disable them temporarily.
>
> Ah, cool.  The installer might complain about constraints with no  
> names
> (not sure), but we can always cross that bridge when we come to it.

Constraints without names?


>> FYI. The current schema (opennms 1.3.1) is not possible with hsqldb,
>> derby
>> and firebird. I was really hoping I could find an embedded database.
>>
>> Hsqldb -- doesn't have good support for triggers. You can't get the
>> current
>> jdbc connection via
>> DriverManager.getConnection("jdbc:default:connection").
>> Derby -- poor trigger support. You can't do a "before update  
>> trigger". It
>> also doesn't have sequences. I wrote my own sequence manager but  
>> the poor
>> trigger support was really the dead end.
>> Firebird -- no sql functions. e.g create function ... with sql
>> statements.
>> It only has support for UDF "C" functions. Also firebird does not  
>> like
>> table
>> names to be longer than 31 chars.

>         Thanks for your hard work!

Ditto!


We do need to be careful about the schema changes we make with  
respect to being consistent with Hibernate's behavior in this DB  
constrained environment.  There was a page of catchyas I ran a across  
ages ago.  I'll see if I can find it, again.


Thanks,

David Hustace
The OpenNMS Group, Inc.