1 -- +--------------------------------------------------------------------+
2 -- | CiviCRM version 3.2 |
3 -- +--------------------------------------------------------------------+
4 -- | Copyright CiviCRM LLC (c) 2004-2010 |
5 -- +--------------------------------------------------------------------+
6 -- | This file is a part of CiviCRM. |
8 -- | CiviCRM is free software; you can copy, modify, and distribute it |
9 -- | under the terms of the GNU Affero General Public License |
10 -- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
12 -- | CiviCRM is distributed in the hope that it will be useful, but |
13 -- | WITHOUT ANY WARRANTY; without even the implied warranty of |
14 -- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
15 -- | See the GNU Affero General Public License for more details. |
17 -- | You should have received a copy of the GNU Affero General Public |
18 -- | License and the CiviCRM Licensing Exception along |
19 -- | with this program; if not, contact CiviCRM LLC |
20 -- | at info[AT]civicrm[DOT]org. If you have questions about the |
21 -- | GNU Affero General Public License or the licensing of CiviCRM, |
22 -- | see the CiviCRM license FAQ at http://civicrm.org/licensing |
23 -- +--------------------------------------------------------------------+
25 -- /*******************************************************
27 -- * civicrm_membership_type
29 -- * Sites can configure multiple types of memberships. They encode the owner organization, fee, and the rules needed to set start and end (expire) dates when a member signs up for that type.
31 -- *******************************************************/
32 CREATE TABLE civicrm_membership_type (
35 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
36 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact',
37 name varchar(128) COMMENT 'Name of Membership Type',
38 description varchar(255) COMMENT 'Description of Membership Type',
39 member_of_contact_id int unsigned NOT NULL COMMENT 'Owner organization for this membership type. FK to Contact ID',
40 contribution_type_id int unsigned NOT NULL COMMENT 'If membership is paid by a contribution - what contribution type should be used. FK to Contribution Type ID',
41 minimum_fee decimal(20,2) DEFAULT 0 COMMENT 'Minimum fee for this membership (0 for free/complimentary memberships).',
42 duration_unit enum('day', 'month', 'year', 'lifetime') COMMENT 'Unit in which membership period is expressed.',
43 duration_interval int COMMENT 'Number of duration units in membership period (e.g. 1 year, 12 months).',
44 period_type enum('rolling', 'fixed') COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.',
45 fixed_period_start_day int DEFAULT 0101 COMMENT 'For fixed period memberships, month and day (mmdd) on which subscription/membership will start. Period start is back-dated unless after rollover day.',
46 fixed_period_rollover_day int DEFAULT 1231 COMMENT 'For fixed period memberships, signups after this day (mmdd) rollover to next period.',
47 relationship_type_id int unsigned COMMENT 'FK to Relationship Type ID',
48 visibility varchar(64) ,
50 is_active tinyint DEFAULT 1 COMMENT 'Is this membership_type enabled'
56 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
58 FOREIGN KEY (member_of_contact_id) REFERENCES civicrm_contact(id)
60 FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id)
62 FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id)
64 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
66 -- /*******************************************************
68 -- * civicrm_membership_status
70 -- * Membership Status stores admin configurable rules for assigning status to memberships.
72 -- *******************************************************/
73 CREATE TABLE civicrm_membership_status (
76 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
77 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact',
78 name varchar(128) COMMENT 'Name for Membership Status',
79 start_event enum('start_date', 'end_date', 'join_date') COMMENT 'Event when this status starts.',
80 start_event_adjust_unit enum('day', 'month', 'year') COMMENT 'Unit used for adjusting from start_event.',
81 start_event_adjust_interval int COMMENT 'Status range begins this many units from start_event.',
82 end_event enum('start_date', 'end_date', 'join_date') COMMENT 'Event after which this status ends.',
83 end_event_adjust_unit enum('day', 'month', 'year') COMMENT 'Unit used for adjusting from the ending event.',
84 end_event_adjust_interval int COMMENT 'Status range ends this many units from end_event.',
85 is_current_member tinyint COMMENT 'Does this status aggregate to current members (e.g. New, Renewed, Grace might all be TRUE... while Unrenewed, Lapsed, Inactive would be FALSE).',
86 is_admin tinyint COMMENT 'Is this status for admin/manual assignment only.',
88 is_default tinyint COMMENT 'Assign this status to a membership record if no other status match is found.',
89 is_active tinyint DEFAULT 1 COMMENT 'Is this membership_status enabled.'
95 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
97 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
99 -- /*******************************************************
101 -- * civicrm_membership
103 -- * Contact Membership records.
105 -- *******************************************************/
106 CREATE TABLE civicrm_membership (
109 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
110 contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID',
111 membership_type_id int unsigned NOT NULL COMMENT 'FK to Membership Type',
112 join_date date COMMENT 'Beginning of initial membership period (member since...).',
113 start_date date COMMENT 'Beginning of current uninterrupted membership period.',
114 end_date date COMMENT 'Current membership period expire date.',
115 source varchar(128) ,
116 status_id int unsigned NOT NULL COMMENT 'FK to Membership Status',
117 is_override tinyint COMMENT 'Admin users may set a manual status which overrides the calculated status. When this flag is true, automated status update scripts should NOT modify status for the record.'
123 FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
125 FOREIGN KEY (membership_type_id) REFERENCES civicrm_membership_type(id)
127 FOREIGN KEY (status_id) REFERENCES civicrm_membership_status(id)
129 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
132 -- /*******************************************************
134 -- * civicrm_membership_block
136 -- * A Membership Block stores admin configurable status options and rules
138 -- *******************************************************/
139 CREATE TABLE civicrm_membership_block (
142 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
143 entity_table varchar(64) COMMENT 'Name for Membership Status',
144 entity_id int unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id',
145 membership_types varchar(255) COMMENT 'Membership types to be exposed by this block',
146 membership_type_default int unsigned COMMENT 'Optional foreign key to membership_type',
147 display_min_fee tinyint DEFAULT 1 COMMENT 'Display minimum membership fee',
148 is_separate_payment tinyint DEFAULT 1 COMMENT 'Should membership transactions be processed separately',
149 new_title varchar(255) COMMENT 'Title to display at top of block',
150 new_text text COMMENT 'Text to display below title',
151 renewal_title varchar(255) COMMENT 'Title for renewal',
152 renewal_text text COMMENT 'Text to display for member renewal',
153 is_required tinyint DEFAULT 0 COMMENT 'Is membership sign up optional',
154 is_active tinyint DEFAULT 1 COMMENT 'Is this membership_block enabled'
160 FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id)
162 FOREIGN KEY (membership_type_default) REFERENCES civicrm_membership_type(id)
164 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
166 -- /*******************************************************
168 -- * civicrm_membership_log
170 -- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
172 -- *******************************************************/
173 CREATE TABLE civicrm_membership_log (
176 id int unsigned NOT NULL AUTO_INCREMENT ,
177 membership_id int unsigned NOT NULL COMMENT 'FK to Membership table',
178 status_id int unsigned NOT NULL COMMENT 'New status assigned to membership by this action. FK to Membership Status',
179 start_date date COMMENT 'New membership period start date',
180 end_date date COMMENT 'New membership period expiration date.',
181 modified_id int unsigned COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
182 modified_date date COMMENT 'Date this membership modification action was logged.'
188 FOREIGN KEY (membership_id) REFERENCES civicrm_membership(id)
190 FOREIGN KEY (status_id) REFERENCES civicrm_membership_status(id)
192 FOREIGN KEY (modified_id) REFERENCES civicrm_contact(id)
194 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
196 -- /*******************************************************
198 -- * civicrm_membership_payment
200 -- * Membership Payment
202 -- *******************************************************/
203 CREATE TABLE civicrm_membership_payment (
206 id int unsigned NOT NULL AUTO_INCREMENT ,
207 membership_id int unsigned NOT NULL COMMENT 'FK to Membership table',
208 payment_entity_table varchar(128) COMMENT 'Table being referenced for payment entity.',
209 payment_entity_id int unsigned COMMENT 'FK to table with payment record (expected usage is civicrm_contribution).'
213 , INDEX index_payment_entity(
219 FOREIGN KEY (membership_id) REFERENCES civicrm_membership(id)
221 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
223 -- /*******************************************************
225 -- * civicrm_option_group
227 -- *******************************************************/
228 CREATE TABLE civicrm_option_group (
231 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option Group ID',
232 domain_id int unsigned NOT NULL COMMENT 'Which domain owns this group of options.',
233 name varchar(64) NOT NULL COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
234 description varchar(255) COMMENT 'Option group description.',
235 is_reserved tinyint COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
236 is_active tinyint COMMENT 'Is this option group active?'
240 , UNIQUE INDEX option_group_name_domain_id(
246 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
248 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
250 -- /*******************************************************
252 -- * civicrm_option_value
254 -- *******************************************************/
255 CREATE TABLE civicrm_option_value (
258 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Option ID',
259 option_group_id int unsigned NOT NULL COMMENT 'Group which this option belongs to.',
260 label varchar(64) NOT NULL COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
261 value int unsigned NOT NULL COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
262 name varchar(64) COMMENT 'May be used to store an option string that is different from the display title. One use case is when a non-translated value needs to be set / sent to another application (e.g. for Credit Card type).',
263 grouping varchar(64) COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
264 filter int unsigned NOT NULL COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
265 is_default tinyint DEFAULT 0 COMMENT 'Is this the default option for the group?',
266 weight int unsigned NOT NULL COMMENT 'Controls display sort order.',
267 description varchar(255) COMMENT 'Optional description.',
268 is_optgroup tinyint DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
269 is_reserved tinyint DEFAULT 0 COMMENT 'Is this a predefined system object?',
270 is_active tinyint DEFAULT 1 COMMENT 'Is this option active?'
274 , INDEX index_option_group_id_value(
280 FOREIGN KEY (option_group_id) REFERENCES civicrm_option_group(id)
282 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
284 -- /*******************************************************
288 -- * Instantiate projects, programs, campaigns, etc.
290 -- *******************************************************/
291 CREATE TABLE civicrm_project (
294 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Project ID',
295 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this record.',
296 title varchar(64) COMMENT 'Project name.',
297 description text COMMENT 'Optional verbose description of the project. May be used for display - HTML allowed.',
298 logo varchar(255) COMMENT 'Full or relative URL to optional uploaded logo image for project.',
299 owner_entity_table varchar(64) NOT NULL COMMENT 'Name of table where project owner being referenced is stored (e.g. civicrm_contact or civicrm_group).',
300 owner_entity_id int unsigned NOT NULL COMMENT 'Foreign key to project owner (contact, group, etc.).',
301 start_date datetime COMMENT 'Project start date.',
302 end_date datetime COMMENT 'Project end date.',
303 is_active tinyint COMMENT 'Is this record active? For Projects: can tasks be created for it, does it appear on project listings, etc.',
304 status_id int unsigned COMMENT 'Configurable status value (e.g. Planned, Active, Closed...). FK to civicrm_option_value.'
308 , UNIQUE INDEX UI_project_owner(
315 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
317 FOREIGN KEY (status_id) REFERENCES civicrm_option_value(value)
319 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
321 -- /*******************************************************
325 -- * To-do items. Can be assigned to self or other entities.
327 -- *******************************************************/
328 CREATE TABLE civicrm_task (
331 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Task ID',
332 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this record.',
333 title varchar(64) COMMENT 'Task name.',
334 description varchar(255) COMMENT 'Optional verbose description of the Task. May be used for display - HTML allowed.',
335 task_type_id int unsigned COMMENT 'Configurable task type values (e.g. App Submit, App Review...). FK to civicrm_option_value.',
336 owner_entity_table varchar(64) NOT NULL COMMENT 'Name of table where Task owner being referenced is stored (e.g. civicrm_contact or civicrm_group).',
337 owner_entity_id int unsigned NOT NULL COMMENT 'Foreign key to Task owner (contact, group, etc.).',
338 parent_entity_table varchar(64) COMMENT 'Name of table where optional Task parent is stored (e.g. civicrm_project, or civicrm_task for sub-tasks).',
339 parent_entity_id int unsigned COMMENT 'Optional foreign key to Task Parent (project, another task, etc.).',
340 due_date datetime COMMENT 'Task due date.',
341 priority_id int unsigned COMMENT 'Configurable priority value (e.g. Critical, High, Medium...). FK to civicrm_option_value.',
342 task_class varchar(255) COMMENT 'Optional key to a process class related to this task (e.g. CRM_Quest_PreApp).',
343 is_active tinyint COMMENT 'Is this record active? For tasks: can it be assigned, does it appear on open task listings, etc.'
347 , UNIQUE INDEX UI_task_parent(
349 , parent_entity_table
352 , UNIQUE INDEX UI_task_owner(
359 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
361 FOREIGN KEY (task_type_id) REFERENCES civicrm_option_value(value)
363 FOREIGN KEY (priority_id) REFERENCES civicrm_option_value(id)
365 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
367 -- /*******************************************************
369 -- * civicrm_task_status
371 -- * Tracks the responsible entity, optional target entity and status of a task. Tasks can have multiple task_status entries if assigned to multiple responsible entities and-or there are multiple targets.
373 -- *******************************************************/
374 CREATE TABLE civicrm_task_status (
377 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Task ID',
378 task_id int unsigned NOT NULL COMMENT 'Status is for which task.',
379 responsible_entity_table varchar(64) NOT NULL COMMENT 'Entity responsible for this task_status instance (table where entity is stored e.g. civicrm_contact or civicrm_group).',
380 responsible_entity_id int unsigned NOT NULL COMMENT 'Foreign key to responsible entity (contact, group, etc.).',
381 target_entity_table varchar(64) NOT NULL COMMENT 'Optional target entity for this task_status instance, i.e. review this membership application-prospect member contact record is target (table where entity is stored e.g. civicrm_contact or civicrm_group).',
382 target_entity_id int unsigned NOT NULL COMMENT 'Foreign key to target entity (contact, group, etc.).',
383 status_detail text COMMENT 'Encoded array of status details used for programmatic progress reporting and tracking.',
384 status_id int unsigned COMMENT 'Configurable status value (e.g. Not Started, In Progress, Completed, Deferred...). FK to civicrm_option_value.',
385 create_date datetime COMMENT 'Date this record was created (date work on task started).',
386 modified_date datetime COMMENT 'Date-time of last update to this task_status record.'
390 , INDEX index_task_status_responsible(
392 , responsible_entity_table
393 , responsible_entity_id
395 , INDEX index_task_status_target(
397 , target_entity_table
402 FOREIGN KEY (task_id) REFERENCES civicrm_task(id)
404 FOREIGN KEY (status_id) REFERENCES civicrm_option_value(value)
406 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
409 -- /*******************************************************
413 -- * Log can be linked to any object in the application.
415 -- *******************************************************/
416 CREATE TABLE civicrm_log (
419 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Log ID',
420 entity_table varchar(64) NOT NULL COMMENT 'Name of table where item being referenced is stored.',
421 entity_id int unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
422 data text COMMENT 'Updates does to this object if any.',
423 modified_id int unsigned COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
424 modified_date datetime COMMENT 'When was the referenced entity created or modified or deleted.'
428 , INDEX index_entity(
434 FOREIGN KEY (modified_id) REFERENCES civicrm_contact(id)
436 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
439 -- /*******************************************************
443 -- * Data store for uploaded (attached) files (pointer to file on disk OR blob). Maybe be joined to entities via custom_value.file_id or entity_file table.
445 -- *******************************************************/
446 CREATE TABLE civicrm_file (
449 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
450 file_type_id int unsigned COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.',
451 mime_type varchar(255) COMMENT 'mime type of the document',
452 uri varchar(255) COMMENT 'uri of the file on disk',
453 document mediumblob COMMENT 'contents of the document',
454 description varchar(255) COMMENT 'Additional descriptive text regarding this attachment (optional).',
455 upload_date datetime COMMENT 'Date and time that this attachment was uploaded or written to server.'
461 FOREIGN KEY (file_type_id) REFERENCES civicrm_option_value(value)
463 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
465 -- /*******************************************************
467 -- * civicrm_entity_file
469 -- * Attaches (joins) uploaded files (images, documents, etc.) to entities (Contacts, Groups, Actions).
471 -- *******************************************************/
472 CREATE TABLE civicrm_entity_file (
475 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
476 entity_table varchar(64) COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
477 entity_id int unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
478 file_id int unsigned NOT NULL COMMENT 'FK to civicrm_file'
482 , INDEX index_entity(
486 , INDEX index_entity_file_id(
493 FOREIGN KEY (file_id) REFERENCES civicrm_file(id)
495 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
499 -- /*******************************************************
501 -- * Modify the civicrm_individual Table Structure
502 -- *******************************************************/
504 ALTER TABLE `civicrm_individual` ADD deceased_date date COMMENT 'Date of deceased';
506 -- /*******************************************************
508 -- * Modify the civicrm_contact Table Structure
509 -- *******************************************************/
510 ALTER TABLE `civicrm_contact` DROP INDEX `index_sort_name`;
511 ALTER TABLE `civicrm_contact` DROP `hash`;
513 ALTER TABLE `civicrm_contact` ADD hash varchar(32) DEFAULT NULL COMMENT 'Key for validating requests related to this contact.';
514 ALTER TABLE `civicrm_contact` ADD contact_sub_type varchar(64) DEFAULT NULL COMMENT 'May be used to over-ride contact view and edit templates.';
516 ALTER TABLE `civicrm_contact` MODIFY preferred_communication_method varchar(255) DEFAULT NULL COMMENT 'What is the preferred mode of communication.';
518 ALTER TABLE `civicrm_contact` ADD INDEX index_sort_name_domain(sort_name, domain_id, hash);
520 ALTER TABLE `civicrm_contact` ADD INDEX index_hash_domain(hash, domain_id);
522 -- /*******************************************************
524 -- * fix preferred communication data
525 -- *******************************************************/
526 SELECT @domain_id := id from civicrm_domain;
528 `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
530 (@domain_id, 'preferred_communication_method', 'Preferred Communication Method' , 0, 1);
531 SELECT @option_group_id_pcm := max(id) from civicrm_option_group where name = 'preferred_communication_method';
534 `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
536 (@option_group_id_pcm, 'Phone', 1, NULL, NULL, 0, NULL, 1, NULL, 0, 0, 1),
537 (@option_group_id_pcm, 'Email', 2, NULL, NULL, 0, NULL, 2, NULL, 0, 0, 1),
538 (@option_group_id_pcm, 'Postal Mail', 3, NULL, NULL, 0, NULL, 3, NULL, 0, 0, 1),
539 (@option_group_id_pcm, 'SMS', 4, NULL, NULL, 0, NULL, 4, NULL, 0, 0, 1),
540 (@option_group_id_pcm, 'Fax', 5, NULL, NULL, 0, NULL, 5, NULL, 0, 0, 1);
542 UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Phone', 1);
543 UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Email', 2);
544 UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Post', 3);
545 UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'SMS', 4);
546 UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Fax', 5);
548 -- /*******************************************************
550 -- * Modify the civicrm_contribution_page Table Structure
551 -- *******************************************************/
552 ALTER TABLE `civicrm_contribution_page` ADD amount_block_is_active tinyint unsigned DEFAULT 1 COMMENT 'Is this property active?';
553 ALTER TABLE `civicrm_contribution_page` ADD goal_amount decimal(20,2) COMMENT 'The target goal for this page, allows people to build a goal meter';
554 ALTER TABLE `civicrm_contribution_page` ADD is_thermometer int(4) unsigned DEFAULT 1 COMMENT 'Should this contribution have the thermometer block enabled?';
555 ALTER TABLE `civicrm_contribution_page` ADD thermometer_title varchar(255) DEFAULT NULL COMMENT 'Title for contribution page thermometer block.';
557 -- /*******************************************************
559 -- * Modify the civicrm_contribution Table Structure
560 -- *******************************************************/
561 ALTER TABLE `civicrm_contribution` ADD `contribution_page_id` int(10) unsigned DEFAULT NULL COMMENT 'The Contribution Page which triggered this contribution';
562 ALTER TABLE `civicrm_contribution` ADD INDEX (`contribution_page_id`);
563 ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`);
565 -- /*******************************************************
567 -- * Modify the civicrm_custom_field Table Structure
568 -- *******************************************************/
570 ALTER TABLE `civicrm_custom_field` CHANGE `html_type` `html_type` ENUM( 'Text', 'TextArea', 'Select', 'Multi-Select', 'Radio', 'CheckBox', 'Select Date', 'Select State/Province', 'Select Country','File') DEFAULT NULL COMMENT 'HTML types plus several built-in extended types.';
572 ALTER TABLE `civicrm_custom_field` CHANGE `data_type` `data_type` ENUM('String', 'Int', 'Float', 'Money', 'Memo', 'Date', 'Boolean', 'StateProvince', 'Country', 'File') DEFAULT NULL COMMENT 'Controls location of data storage in extended_data table.';
575 -- /*******************************************************
577 -- * Modify the civicrm_custom_group Table Structure
579 -- *******************************************************/
581 ALTER TABLE `civicrm_custom_group` CHANGE `extends` `extends` ENUM( 'Contact', 'Individual', 'Household', 'Organization', 'Location', 'Address', 'Contribution', 'Activity', 'Phonecall', 'Meeting', 'Group','Relationship') DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';
583 -- /*******************************************************
585 -- * Modify the civicrm_custom_value Table Structure
587 -- *******************************************************/
589 ALTER TABLE `civicrm_custom_value` ADD file_id int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_file';
590 ALTER TABLE `civicrm_custom_value` ADD INDEX (`file_id`);
591 ALTER TABLE `civicrm_custom_value` ADD FOREIGN KEY (`file_id`) REFERENCES `civicrm_file`(`id`);
594 -- /*******************************************************
596 -- * Modify the civicrm_email Table Structure
598 -- *******************************************************/
600 ALTER TABLE `civicrm_email` ADD INDEX `UI_email` (`email`);
602 -- /*******************************************************
604 -- * Modify the civicrm_mapping Table Structure
606 -- *******************************************************/
609 ALTER TABLE `civicrm_mapping` CHANGE `mapping_type` `mapping_type` ENUM('Export', 'Import', 'Export Contributions', 'Import Contributions', 'Import Activity History', 'Search Builder') default NULL COMMENT 'Type of Mapping.';
612 -- /*******************************************************
614 -- * Modify the civicrm_mapping_field Table Structure
616 -- *******************************************************/
618 ALTER TABLE `civicrm_mapping_field` ADD grouping int(10) unsigned DEFAULT 1 COMMENT 'Used to group mapping_field records into related sets (e.g. for criteria sets in search builder mappings).';
620 ALTER TABLE `civicrm_mapping_field` ADD operator ENUM('=','!=','>','<','>=','<=','IN','NOT IN','LIKE','NOT LIKE') DEFAULT NULL COMMENT 'SQL WHERE operator for search-builder mapping fields (search criteria).';
622 ALTER TABLE `civicrm_mapping_field` ADD value varchar(255) DEFAULT NULL COMMENT 'SQL WHERE value for search-builder mapping fields.';
624 -- /*******************************************************
626 -- * Modify the civicrm_uf_group Table Structure
628 -- *******************************************************/
630 ALTER TABLE `civicrm_uf_group` ADD add_to_group_id int unsigned DEFAULT NULL COMMENT 'foreign key to civicrm_group_id';
631 ALTER TABLE `civicrm_uf_group` ADD INDEX (`add_to_group_id`);
632 ALTER TABLE `civicrm_uf_group` ADD FOREIGN KEY (`add_to_group_id`) REFERENCES `civicrm_group` (`id`);
634 ALTER TABLE `civicrm_uf_group` ADD add_captcha tinyint DEFAULT 0 COMMENT 'Should a CAPTCHA widget be included this Profile form.';
636 ALTER TABLE `civicrm_uf_group` ADD cancel_URL varchar(255) DEFAULT NULL COMMENT 'Redirect to URL when Cancle button clik .';
637 ALTER TABLE `civicrm_uf_group` ADD is_map tinyint DEFAULT 0 COMMENT 'Do we want to map results from this profile.';
638 ALTER TABLE `civicrm_uf_group` ADD collapse_display int(10) unsigned DEFAULT 0 COMMENT 'Will this group be in collapsed or expanded mode on initial display ?';
640 -- /*******************************************************
642 -- * Modify the civicrm_uf_match Table Structure
644 -- *******************************************************/
646 ALTER TABLE `civicrm_uf_match` ADD INDEX `UI_uf_id` (`uf_id`);
648 -- /*******************************************************
650 -- * Modify the civicrm_note Table Structure
652 -- *******************************************************/
655 ALTER TABLE `civicrm_note` ADD `subject` varchar(255) DEFAULT NULL COMMENT 'subject of note description';
657 -- /*******************************************************
659 -- * Modify the civicrm_relationship Table Structure
661 -- *******************************************************/
664 ALTER TABLE `civicrm_relationship` ADD `description` varchar(255) COMMENT 'Optional verbose description for the relationship.' ;
666 -- /*******************************************************
668 -- * Modify the civicrm_saved_search Table Structure
670 -- *******************************************************/
672 ALTER TABLE `civicrm_saved_search` ADD `mapping_id` INT UNSIGNED COMMENT 'Foreign key to civicrm_mapping used for saved search-builder searches.';
673 ALTER TABLE `civicrm_saved_search` ADD INDEX (`mapping_id`);
674 ALTER TABLE `civicrm_saved_search` ADD FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping` (`id`);
676 ALTER TABLE `civicrm_saved_search` DROP FOREIGN KEY `civicrm_saved_search_ibfk_1`;
678 ALTER TABLE `civicrm_saved_search` DROP `query`,
681 -- /*******************************************************
683 -- * Modify the civicrm_custom_value Table Structure
685 -- *******************************************************/
688 UPDATE `civicrm_custom_value`, `civicrm_custom_field`
689 SET `char_data` = CONCAT(char(1),`char_data`,char(1))
690 WHERE `custom_field_id` = `civicrm_custom_field`.`id`
691 AND `html_type` IN ('multi-Select','CheckBox');