schema differences between fresh 3 and upgrade to 3

[steve_tekell]

in the process of sort through 2.1-3c upgrade problems, I generated a schema diff between my upgraded database and fresh install.
FWIW this 2.1 had been upgraded from 2.0 and 1.4 before that.

I ignored differences in NULLABILITY since it might be required for added columns and a few trivial things in the diff, but there's still some things I wonder if I should change.  Other than nullability, would it be a good idea to make my upgraded database like a fresh install?  Or are there valid reasons for these?

There seems like potential problems:

alf_ace_context
The upgraded database uses VARCHAR where the fresh database uses TEXT

alf_node_properties
The upgraded database has smaller min/max limits on list_index

alf_attributes
The upgraded database is missing a Constraint: fk_alf_attr_acl.

JBPM_PROCESSINSTANCE
The upgraded database is missing a Index: IDX_PROCIN_KEY

I don't know if any the defaults matter, but the upgraded database has several that a fresh install doesn't, and there's one the other way around.

here's the summary report:

=====================================
Summary
=====================================
Schema comparison performed: Wed Dec 31 12:51:46 MST 2008
Source DB Server: localhost
Source Database: alfresco3 (Fresh install, also referred to as 'other' database)
Destination DB Server: localhost
Destination Database: alfresco (upgraded to 3C, also referred to as 'this' database)
Total/Matching Tables: 81/66
   Total/Matching Columns: 635/565
   Total/Matching Indexes: 315/314
   Total/Matching Constraints: 236/235
   Total/Matching Triggers: 0/0
=====================================
Tables
=====================================

  alf_access_control_entry
  =====================================
  Columns
    applies : Only one of the columns has a default. Destination : 0

  alf_access_control_list
  =====================================
  Columns
    acl_id : Only one of the columns has a default. Destination : UNSET
    latest : Only one of the columns has a default. Destination : 1
    acl_version : Only one of the columns has a default. Destination : 1
    type : Only one of the columns has a default. Destination : 0
    is_versioned : Only one of the columns has a default. Destination : 0
    requires_version : Only one of the columns has a default. Destination : 0

  alf_ace_context
  =====================================
  Columns
    class_context : Incompatible data types TEXT and VARCHAR
    property_context : Incompatible data types TEXT and VARCHAR
    kvp_context : Incompatible data types TEXT and VARCHAR

  alf_attributes
  =====================================
  Constraints
    fk_alf_attr_acl : Constraint fk_alf_attr_acl is missing from this side.

  alf_locale
  =====================================
  Columns
    version : Only one of the columns has a default. Destination : 1

  alf_node_properties
  =====================================
  Columns
    list_index : Different minimum / maximum values for integer types. Source : min=-2,147,483,648, max=2,147,483,647 Destination : min=-32,768, max=32,767

  JBPM_MESSAGE : Missing from the other side
  =====================================

  JBPM_PROCESSINSTANCE
  =====================================
  Indexes
    IDX_PROCIN_KEY : Index IDX_PROCIN_KEY is missing from this side.

  JBPM_TIMER : Missing from the other side
  =====================================
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[steve_tekell]

In case anyone is interested, here's generated script to sync such an upgraded database to be like a fresh install.  (still ignoring NULLABILITY, etc).

–
– FOREIGN KEYS [DROP]
– 

ALTER TABLE JBPM_MESSAGE DROP CONSTRAINT FK_CMD_ACTION;
ALTER TABLE JBPM_MESSAGE DROP CONSTRAINT FK_CMD_NODE;
ALTER TABLE JBPM_MESSAGE DROP CONSTRAINT FK_CMD_TASKINST;
ALTER TABLE JBPM_MESSAGE DROP CONSTRAINT FK_MSG_TOKEN;
ALTER TABLE JBPM_TIMER DROP CONSTRAINT FK_TIMER_ACTION;
ALTER TABLE JBPM_TIMER DROP CONSTRAINT FK_TIMER_PRINST;
ALTER TABLE JBPM_TIMER DROP CONSTRAINT FK_TIMER_TOKEN;
ALTER TABLE JBPM_TIMER DROP CONSTRAINT FK_TIMER_TSKINST;

–
– alf_access_control_entry
– 

ALTER TABLE alf_access_control_entry ALTER COLUMN applies DROP DEFAULT;

–
– alf_access_control_list
– 

ALTER TABLE alf_access_control_list ALTER COLUMN acl_id DROP DEFAULT;
ALTER TABLE alf_access_control_list ALTER COLUMN latest DROP DEFAULT;
ALTER TABLE alf_access_control_list ALTER COLUMN acl_version DROP DEFAULT;
ALTER TABLE alf_access_control_list ALTER COLUMN type DROP DEFAULT;
ALTER TABLE alf_access_control_list ALTER COLUMN is_versioned DROP DEFAULT;
ALTER TABLE alf_access_control_list ALTER COLUMN requires_version DROP DEFAULT;

–
– alf_ace_context
– 

ALTER TABLE alf_ace_context CHANGE COLUMN class_context class_context TEXT NULL COMMENT '';
ALTER TABLE alf_ace_context CHANGE COLUMN property_context property_context TEXT NULL COMMENT '';
ALTER TABLE alf_ace_context CHANGE COLUMN kvp_context kvp_context TEXT NULL COMMENT '';

–
– alf_attributes
– 


–
– alf_locale
– 

ALTER TABLE alf_locale ALTER COLUMN version DROP DEFAULT;

–
– alf_node_properties
– 

ALTER TABLE alf_node_properties CHANGE COLUMN list_index list_index INT NOT NULL COMMENT '';

–
– JBPM_MESSAGE
– 

DROP TABLE JBPM_MESSAGE;

–
– JBPM_PROCESSINSTANCE
– 

CREATE INDEX IDX_PROCIN_KEY ON JBPM_PROCESSINSTANCE (KEY_ ASC);

–
– JBPM_TIMER
– 

DROP TABLE JBPM_TIMER;


–
– FOREIGN KEYS [CREATE]
– 

ALTER TABLE alf_attributes ADD CONSTRAINT fk_alf_attr_acl FOREIGN KEY (acl_id) REFERENCES alf_access_control_list (id)
                                                          ON DELETE NO ACTION
                                                          ON UPDATE NO ACTION;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍