Minor Database Schema Modifications from 2.0 Beta 2 to 2.0 Beta 3

[Dave Greenberg]

Although we try to avoid any schema changes once a beta cycle has begun - our fantastic community testers have uncovered some issues that  require minor changes to the schema to fix. If you've installed 2.0 beta 2 or beta 1 - or have upgraded to one of those versions - and you don't want to start the install or upgrade from scratch (when beta 3 is released) - you can fix your database by running the following SQL statements:

Code:

// *** Change domain_id in the first statement if you're site uses a domain other than the default domain (1)

SELECT @og_id_at := id FROM civicrm_option_group WHERE domain_id = 1 AND name = 'activity_type';
SELECT @email_id := id FROM civicrm_option_value WHERE option_group_id = @og_id_at AND label like 'Email';
UPDATE `civicrm_option_value` ov SET filter=1 WHERE id = @email_id;
ALTER TABLE civicrm_activity DROP FOREIGN KEY `FK_civicrm_activity_activity_type_id`;
ALTER TABLE civicrm_activity DROP FOREIGN KEY `FK_civicrm_activity_status_id`;
ALTER TABLE civicrm_activity DROP FOREIGN KEY `FK_civicrm_activity_priority_id`;
ALTER TABLE civicrm_case DROP FOREIGN KEY `FK_civicrm_case_status_id`;

[speleo]

Dave,

looking at my v.2.0 tables I don't have the following keys

        civicrm_activity FK_civicrm_activity_activity_type_id
        civicrm_case FK_civicrm_case_status_id

but do have

        civicrm_activity UI_activity_type_id
        civicrm_case UI_status_id

A bit of investigation shows that the beta 2 distro has civicrm.mysql file would create these keys:

Code:

CREATE TABLE civicrm_activity ......
PRIMARY KEY ( id ),
INDEX UI_source_contact_id(source_contact_id),
INDEX UI_activity_type_id(activity_type_id),
CONSTRAINT FK_civicrm_activity_source_contact_id FOREIGN KEY (source_contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,     
CONSTRAINT FK_civicrm_activity_activity_type_id  FOREIGN KEY (activity_type_id) REFERENCES civicrm_option_value(id) ,     
CONSTRAINT FK_civicrm_activity_phone_id    FOREIGN KEY (phone_id) REFERENCES civicrm_phone(id) ON DELETE SET NULL,     
CONSTRAINT FK_civicrm_activity_status_id FOREIGN KEY (status_id) REFERENCES civicrm_option_value(id) ON DELETE SET NULL,     
CONSTRAINT FK_civicrm_activity_priority_id FOREIGN KEY (priority_id) REFERENCES civicrm_option_value(id) ON DELETE SET NULL,     
CONSTRAINT FK_civicrm_activity_parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE 

Code:

CREATE TABLE civicrm_case .....
PRIMARY KEY ( id ),
     INDEX index_case_type_id(case_type_id),
     INDEX UI_status_id(status_id),     
     CONSTRAINT FK_civicrm_case_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,     
     CONSTRAINT FK_civicrm_case_status_id FOREIGN KEY (status_id) REFERENCES civicrm_option_value(id)   

civicrm_activity FK_civicrm_activity_activity_type_id doesn't exist in a new 1.9 install, so it looked like the upgrade process didn't create all the keys:

Upgrade/TwoZero/sql/activity.mysql

Code:

ALTER TABLE `civicrm_activity`…
    ADD INDEX `UI_source_contact_id` (`source_contact_id`),
    DROP FOREIGN KEY FK_civicrm_activity_source_contact_id,
    DROP FOREIGN KEY FK_civicrm_activity_parent_id;


ALTER TABLE `civicrm_activity`
    ADD CONSTRAINT `FK_civicrm_activity_parent_id`         FOREIGN KEY (`parent_id`)         REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE,
    ADD CONSTRAINT `FK_civicrm_activity_phone_id`          FOREIGN KEY (`phone_id`)          REFERENCES `civicrm_phone` (`id`) ON DELETE SET NULL,
    ADD CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;

civicrm_case didn't exist in 1.9 so was created during the upgrade.
Upgrade/TwoZero/sql/others.mysql

Code:

CREATE TABLE IF NOT EXISTS `civicrm_case` (
    .....
    PRIMARY KEY (id),
    INDEX `index_case_type_id` (`case_type_id`),
    INDEX `UI_status_id` (`status_id`),
    CONSTRAINT `FK_civicrm_case_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
    CONSTRAINT `FK_civicrm_case_status_id` FOREIGN KEY (`status_id`) REFERENCES `civicrm_option_value` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This indicates that the   civicrm_case FK_civicrm_case_status_id should have been created but neither my offline upgraded db or my test db show that key. don't understand that one.

Before upgrading I went though the normalisation process for my 1.9 db so it should be a clean upgrade from 1.9. When's beta 3 out? Might be easier to just process my 1.9 db again.

[Donald Lobo]

we are trying to make wed our release day, so beta will be out tomorrow.

here is the diff that i checked in yesterday.

http://biryani.osuosl.org:8181/browse/CiviCRM/branches/v2.0/CRM/Upgrade/TwoZero/sql/activity.mysql?r1=13413&r2=13425

lobo

[speleo]

Thanks Lobo, will roll with beta 3 when it hits the streets. Lots of testing will make a more robust platform. Cheers, Henry

[speleo]

running the beta 3 upgrade process against a 1.9 normalised db looks like it has done the trick with correcting the issues with is_primary I had had. cheers!

[sonicthoughts]

just to clarify - if we have beta 2, do we just over-write the civicrm module directory and apply the SQL ?

[Donald Lobo]

For drupal, you can just overwrite the code. You should also clean your templates_c directory and truncate the session table

For joomla, you will need to treat it as an upgrade, and follow the upgrade steps. You can skip the db upgrade step (step since the db has not changed

lobo