schema differences between fresh 3 and upgrade to 3

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍