[civicrm-core.git] / sql / civicrm_upgradedb_v1.8_v1.9_41.mysql

-- +--------------------------------------------------------------------+
-- | CiviCRM version 4.6                                                |
-- +--------------------------------------------------------------------+
-- | Copyright CiviCRM LLC (c) 2004-2015                                |
-- +--------------------------------------------------------------------+
-- | This file is a part of CiviCRM.                                    |
-- |                                                                    |
-- | CiviCRM is free software; you can copy, modify, and distribute it  |
-- | under the terms of the GNU Affero General Public License           |
-- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
-- |                                                                    |
-- | CiviCRM is distributed in the hope that it will be useful, but     |
-- | WITHOUT ANY WARRANTY; without even the implied warranty of         |
-- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
-- | See the GNU Affero General Public License for more details.        |
-- |                                                                    |
-- | You should have received a copy of the GNU Affero General Public   |
-- | License and the CiviCRM Licensing Exception along                  |
-- | with this program; if not, contact CiviCRM LLC                     |
-- | at info[AT]civicrm[DOT]org. If you have questions about the        |
-- | GNU Affero General Public License or the licensing of CiviCRM,     |
-- | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
-- +--------------------------------------------------------------------+
-- /*******************************************************
-- *
-- * Select DomainID
-- *
-- *******************************************************/
  SELECT @domain_id := id from civicrm_domain;

-- /*******************************************************
-- *
-- * civicrm_mailing_spool
-- *
-- *******************************************************/
CREATE TABLE civicrm_mailing_spool (


     id int(10) unsigned NOT NULL AUTO_INCREMENT  COMMENT '',
     domain_id int(10) unsigned NOT NULL COMMENT 'The ID of the civicrm domain.',
     job_id int(10) unsigned NOT NULL COMMENT 'The ID of the Job .',
     recipient_email text NULL DEFAULT NULL COMMENT 'The email of the receipients this mail is to be sent.',
     headers text NULL DEFAULT NULL COMMENT 'The header information of this mailing .',
     body text NULL DEFAULT NULL COMMENT 'The body of this mailing.',
     added_at datetime NULL DEFAULT NULL COMMENT 'date on which this job was added.',
     removed_at datetime NULL DEFAULT NULL COMMENT 'date on which this job was removed.'
,
    PRIMARY KEY ( id )
,
     CONSTRAINT FK_civicrm_mailing_spool_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)

,    CONSTRAINT FK_civicrm_mailing_spool_job_id FOREIGN KEY (job_id) REFERENCES civicrm_mailing_job(id)

)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;


-- /*******************************************************
-- *
-- * Modifying Tables
-- *
-- *******************************************************/

-- /*******************************************************
-- *
-- * civicrm_email
-- *
-- *******************************************************/
  ALTER TABLE civicrm_email
      ADD is_bulkmail tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Is this address for bulk mail ?';

-- /*******************************************************
-- *
-- * civicrm_payment_processor_type
-- *
-- *******************************************************/

  ALTER TABLE civicrm_payment_processor_type
       DROP INDEX UI_name,
       ADD UNIQUE INDEX UI_name_domain_id( name, domain_id );


-- /*******************************************************
-- *
-- * civicrm_domain
-- *
-- *******************************************************/
  ALTER TABLE civicrm_domain
      ADD email_name varchar(255) NULL DEFAULT NULL COMMENT 'The default email name that is used in the from address for all outgoing emails',
            ADD email_address varchar(255) NULL DEFAULT NULL COMMENT 'The default email address that is used as the from address for all outgoing emails',
      DROP contact_name;

    UPDATE `civicrm_domain` SET `email_name`='FIXME';
    UPDATE `civicrm_domain` SET `email_address`='info@EXAMPLE.ORG';
    UPDATE `civicrm_domain` SET `email_domain`='EXAMPLE.ORG';

-- /*******************************************************
-- *
-- * civicrm_mailing_job
-- *
-- *******************************************************/
  ALTER TABLE civicrm_mailing_job
      ADD is_test tinyint(4) DEFAULT 0 COMMENT 'Is this job for a test mail?',
      DROP is_retry;

    UPDATE civicrm_mailing_job
    SET    is_test = 0;


-- /*******************************************************
-- *
-- * civicrm_currency
-- *
-- *******************************************************/
  ALTER TABLE civicrm_currency
      ADD numeric_code varchar(3) NULL DEFAULT NULL COMMENT 'Numeric currency code',
            ADD full_name varchar(64) NULL DEFAULT NULL COMMENT 'Full currency name';

-- /*******************************************************
-- *
-- * civicrm_mailing
-- *
-- *******************************************************/
  ALTER TABLE civicrm_mailing
        ADD resubscribe_id int(10) unsigned NOT NULL,
  ADD msg_template_id int(10) unsigned NULL DEFAULT NULL ,
      ADD CONSTRAINT FK_civicrm_mailing_msg_template_id FOREIGN KEY (msg_template_id) REFERENCES civicrm_msg_template(id),
      DROP is_template;

-- /*******************************************************
-- *
-- * civicrm_mailing_component
-- *
-- *******************************************************/
  ALTER TABLE civicrm_mailing_component
  MODIFY component_type enum('Header','Footer','Subscribe','Welcome','Unsubscribe','OptOut','Reply','Resubscribe') NULL DEFAULT NULL;

-- /*******************************************************
-- *
-- * civicrm_group
-- *
-- *******************************************************/
  ALTER TABLE civicrm_group
      ADD group_type varchar(128) NULL DEFAULT NULL COMMENT 'FK to group type',
            ADD INDEX index_group_type (group_type);

-- /*******************************************************
-- *
-- * civicrm_contribution_page
-- *
-- *******************************************************/
  ALTER TABLE civicrm_contribution_page
      MODIFY default_amount_id int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to civicrm_custom_option.';

-- /*******************************************************
-- *
-- * CRM-2260
-- *
-- *******************************************************/

  REPLACE
      civicrm_currency (id, name, symbol, numeric_code, full_name)
  VALUES
    (  1, "AUD",  "$", "036", "Australian Dollar"),
    (  2, "CAD",  "$", "124", "Canadian Dollar"),
    (  3, "EUR",  "â‚¬", "978", "Euro"),
    (  4, "GBP",  "Â£", "826", "Pound Sterling"),
    (  5, "ILS",  "â‚ª", "826", "New Israeli Shekel"),
    (  6, "INR",  "â‚¨", "356", "Indian Rupee"),
    (  7, "JPY",  "Â¥", "392", "Japanese Yen"),
    (  8, "KRW",  "â‚©", "410", "South Korean Won"),
    (  9, "LAK",  "â‚", "418", "Lao Kip"),
    ( 10, "MNT",  "â‚®", "496", "Mongolian Tugrik"),
     ( 11, "NGN",  "â‚¦", "566", "Nigerian Naira"),
    ( 12, "PLN", "zÅ‚", "985", "Polish ZÅ‚oty"),
    ( 13, "THB",  "à¸¿", "764", "Thai Baht"),
    ( 14, "USD",  "$", "840", "US Dollar"),
    ( 15, "VND",  "â‚«", "704", "Viet Nam Dong"),
    ( 16, "ZAR",  "R", "710", "South African Rand"),
    ( 17, "AED", NULL, "784", "UAE Dirham"),
    ( 18, "AFN",  "Ø‹", "971", "Afghani"),
    ( 19, "ALL","Lek", "008", "Albanian Lek"),
    ( 20, "AMD", NULL, "051", "Armenian Dram"),
    ( 21, "ANG",  "Æ’", "532", "Netherlands Antillian Guilder"),
    ( 22, "AOA", NULL, "973", "Angola Kwanza"),
    ( 23, "ARS",  "$", "032", "Argentine Peso"),
    ( 24, "AWG",  "Æ’", "533", "Aruban Guilder"),
    ( 25, "AZN","Ð¼Ð°Ð½", "944", "Azerbaijanian Manat"),
    ( 26, "BAM", "KM", "977", "Convertible Marks"),
    ( 27, "BBD",  "$", "052", "Barbados Dollar"),
    ( 28, "BDT", NULL, "050", "Bangladeshi Taka"),
      ( 29, "BGN", "Ð»Ð²", "975", "Bulgarian Lev"),
    ( 30, "BHD", NULL, "048", "Bahraini Dinar"),
    ( 31, "BIF", NULL, "108", "Burundi Franc"),
      ( 32, "BMD",  "$", "060", "Bermudian Dollar"),
    ( 33, "BND",  "$", "096", "Brunei Dollar"),
    ( 34, "BOB", "$b", "068", "Boliviano"),
    ( 35, "BOV", NULL, "984", "Bolivian Mvdol"),
    ( 36, "BRL", "R$", "986", "Brazilian Real"),
    ( 37, "BSD",  "$", "044", "Bahamian Dollar"),
    ( 38, "BTN", NULL, "064", "Bhutan Ngultrum"),
    ( 39, "BWP",  "P", "072", "Botswana Pula"),
    ( 40, "BYR", "p.", "974", "Belarussian Rouble"),
    ( 41, "BZD","BZ$", "084", "Belize Dollar"),
    ( 42, "CDF", NULL, "976", "Franc Congolais"),
    ( 43, "CHE", NULL, "947", "WIR Euro"),
    ( 44, "CHF","CHF", "756", "Swiss Franc"),
    ( 45, "CHW", NULL, "948", "WIR Franc"),
    ( 46, "CLF", NULL, "990", "Unidades de fomento"),
    ( 47, "CLP",  "$", "152", "Chilean Peso"),
    ( 48, "CNY", "Ã¥
Âƒ", "156", "Chinese Yuan Renminbi"),
    ( 49, "COP",  "$", "170", "Colombian Peso"),
    ( 50, "COU", NULL, "970", "Unidad de Valor Real"),
    ( 51, "CRC",  "â‚¡", "188", "Costa Rican Colon"),
    ( 52, "CSD","Ð”Ð¸Ð½.","891", "Serbian Dinar"),
    ( 53, "CUP",  "â‚±", "192", "Cuban Peso"),
    ( 54, "CVE", NULL, "132", "Cape Verde Escudo"),
    ( 55, "CYP",  "Â£", "196", "Cyprus Pound"),
    ( 56, "CZK", "KÄ�", "203", "Czech Koruna"),
    ( 57, "DJF", NULL, "262", "Djibouti Franc"),
    ( 58, "DKK", "kr", "208", "Danish Krone"),
    ( 59, "DOP","RD$", "214", "Dominican Peso"),
    ( 60, "DZD", NULL, "012", "Algerian Dinar"),
    ( 61, "EEK", "kr", "233", "Estonian Kroon"),
    ( 62, "EGP",  "Â£", "818", "Egyptian Pound"),
    ( 63, "ERN", NULL, "232", "Eritrean Nakfa"),
    ( 64, "ETB", NULL, "230", "Ethiopian Birr"),
    ( 65, "FJD",  "$", "242", "Fiji Dollar"),
    ( 66, "FKP",  "Â£", "238", "Falkland Islands Pound"),
    ( 67, "GEL", NULL, "981", "Georgian Lari"),
    ( 68, "GHC",  "Â¢", "288", "Ghanaian Cedi"),
    ( 69, "GIP",  "Â£", "292", "Gibraltar Pound"),
    ( 70, "GMD", NULL, "270", "Gambian Dalasi"),
    ( 71, "GNF", NULL, "324", "Guinea Franc"),
    ( 72, "GTQ",  "Q", "320", "Guatemalan Quetzal"),
    ( 73, "GWP", NULL, "624", "Guinea-Bissau Peso"),
    ( 74, "GYD",  "$", "328", "Guyana Dollar"),
    ( 75, "HKD","HK$", "344", "Hong Kong Dollar"),
    ( 76, "HNL",  "L", "340", "Honduran Lempira"),
    ( 77, "HRK", "kn", "191", "Croatian Kuna"),
    ( 78, "HTG", NULL, "332", "Haitian Gourde"),
    ( 79, "HUF", "Ft", "348", "Hungarian Forint"),
    ( 80, "IDR", "Rp", "360", "Indonesian Rupiah"),
    ( 81, "IQD", NULL, "368", "Iraqi Dinar"),
    ( 82, "IRR",  "ï·¼", "364", "Iranian Rial"),
    ( 83, "ISK", "kr", "352", "Iceland Krona"),
    ( 84, "JMD", "J$", "388", "Jamaican Dollar"),
    ( 85, "JOD", NULL, "400", "Jordanian Dinar"),
    ( 86, "KES", NULL, "404", "Kenyan Shilling"),
    ( 87, "KGS", "Ð»Ð²", "417", "Kyrgyzstan Som"),
    ( 88, "KHR",  "áŸ›", "116", "Cambodian Riel"),
    ( 89, "KMF", NULL, "174", "Comoro Franc"),
    ( 90, "KPW",  "â‚©", "408", "North Korean Won"),
    ( 91, "KWD", NULL, "414", "Kuwaiti Dinar"),
    ( 92, "KYD",  "$", "136", "Cayman Islands Dollar"),
    ( 93, "KZT", "Ð»Ð²", "398", "Kazakhstan Tenge"),
    ( 94, "LBP",  "Â£", "422", "Lebanese Pound"),
    ( 95, "LKR",  "â‚¨", "144", "Sri Lanka Rupee"),
    ( 96, "LRD",  "$", "430", "Liberian Dollar"),
    ( 97, "LSL", NULL, "426", "Lesotho Loti"),
    ( 98, "LTL", "Lt", "440", "Lithuanian Litas"),
    ( 99, "LVL", "Ls", "428", "Latvian Lats"),
    (100, "LYD", NULL, "434", "Libyan Dinar"),
    (101, "MAD", NULL, "504", "Moroccan Dirham"),
    (102, "MDL", NULL, "498", "Moldovan Leu"),
    (103, "MGA", NULL, "969", "Malagascy Ariary"),
    (104, "MKD","Ð´ÐµÐ½", "807", "Macedonian Denar"),
    (105, "MMK", NULL, "104", "Myanmar Kyat"),
    (106, "MOP", NULL, "446", "Macao Pataca"),
    (107, "MRO", NULL, "478", "Mauritanian Ouguiya"),
    (108, "MTL", "Lm", "470", "Maltese Lira"),
    (109, "MUR",  "â‚¨", "480", "Mauritius Rupee"),
    (110, "MVR", NULL, "462", "Maldive Rufiyaa"),
    (111, "MWK", NULL, "454", "Malawi Kwacha"),
    (112, "MXN",  "$", "484", "Mexican Peso"),
    (113, "MXV", NULL, "979", "Mexican Unidad de Inversion (UID)"),
     (114, "MYR", "RM", "458", "Malaysian Ringgit"),
      (115, "MZN", "MT", "943", "Mozambique Metical"),
    (116, "NAD",  "$", "516", "Namibian Dollar"),
    (117, "NIO", "C$", "558", "Nicaraguan Cordoba Oro"),
    (118, "NOK", "kr", "578", "Norwegian Krone"),
    (119, "NPR",  "â‚¨", "524", "Nepalese Rupee"),
    (120, "NZD",  "$", "554", "New Zealand Dollar"),
    (121, "OMR",  "ï·¼", "512", "Rial Omani"),
    (122, "PAB","B/.", "590", "Panamanian Balboa"),
    (123, "PEN","S/.", "604", "Peruvian Nuevo Sol"),
    (124, "PGK", NULL, "598", "Papua New Guinea Kina"),
    (125, "PHP","Php", "608", "Philippine Peso"),
    (126, "PKR",  "â‚¨", "586", "Pakistan Rupee"),
    (127, "PYG", "Gs", "600", "Paraguay Guarani"),
    (128, "QAR",  "ï·¼", "634", "Qatari Rial"),
    (129, "ROL", NULL, "642", "Romanian Old Leu"),
    (130, "RON","lei", "946", "Romanian New Leu"),
    (131, "RUB","Ñ€ÑƒÐ±", "643", "Russian Rouble"),
    (132, "RWF", NULL, "646", "Rwanda Franc"),
    (133, "SAR",  "ï·¼", "682", "Saudi Riyal"),
    (134, "SBD",  "$", "090", "Solomon Islands Dollar"),
    (135, "SCR",  "â‚¨", "690", "Seychelles Rupee"),
    (136, "SDD", NULL, "736", "Sudanese Dinar"),
      (137, "SEK", "kr", "752", "Swedish Krona"),
    (138, "SGD",  "$", "702", "Singapore Dollar"),
    (139, "SHP",  "Â£", "654", "Saint Helena Pound"),
    (140, "SIT", NULL, "705", "Slovenian Tolar"),
    (141, "SKK","SIT", "703", "Slovak Koruna"),
    (142, "SLL", NULL, "694", "Leone"),
    (143, "SOS",  "S", "706", "Somali Shilling"),
    (144, "SRD",  "$", "968", "Surinam Dollar"),
    (145, "STD", NULL, "678", "SÃ£o Tome and Principe Dobra"),
    (146, "SVC",  "$", "222", "El Salvador Colon"),
    (147, "SYP",  "Â£", "760", "Syrian Pound"),
    (148, "SZL", NULL, "748", "Swaziland Lilangeni"),
    (149, "TJS", NULL, "972", "Tajik Somoni"),
    (150, "TMM", NULL, "795", "Turkmenistan Manat"),
    (151, "TND", NULL, "788", "Tunisian Dinar"),
    (152, "TOP", NULL, "776", "Tongan Pa'anga"),
    (153, "TRY","YTL", "949", "New Turkish Lira"),
    (154, "TTD","TT$", "780", "Trinidad and Tobago Dollar"),
    (155, "TWD","NT$", "901", "New Taiwan Dollar"),
    (156, "TZS", NULL, "834", "Tanzanian Shilling"),
    (157, "UAH",  "â‚´", "980", "Ukrainian Hryvnia"),
    (158, "UGX", NULL, "800", "Ugandan Shilling"),
    (159, "USN", NULL, "997", "US Dollar (Next day)"),
    (160, "USS", NULL, "998", "US Dollar (Same day)"),
    (161, "UYU", "$U", "858", "Peso Uruguayo"),
    (162, "UZS", "Ð»Ð²", "860", "Uzbekistan Sum"),
    (163, "VEB", "Bs", "862", "Venezuela Bolivar"),
    (164, "VUV", NULL, "548", "Vanuatu Vatu"),
    (165, "WST", NULL, "882", "Samoan Tala"),
    (166, "XAF", NULL, "950", "CFA Franc BEAC"),
    (167, "XAG", NULL, "961", "Silver"),
    (168, "XAU", NULL, "959", "Gold"),
    (169, "XBA", NULL, "955", "Bond Markets Units European Composite Unit (EURCO)"),
    (170, "XBB", NULL, "956", "European Monetary Unit (E.M.U.-6)"),
    (171, "XBC", NULL, "957", "European Unit of Account 9 (E.U.A.-9)"),
    (172, "XBD", NULL, "958", "European Unit of Account 17 (E.U.A.-17)"),
    (173, "XCD",  "$", "951", "East Caribbean Dollar"),
    (174, "XDR", NULL, "960", "Special Drawing Right"),
    (175, "XFO", NULL,  NULL, "Gold-Franc"),
    (176, "XFU", NULL,  NULL, "UIC-Franc"),
    (177, "XOF", NULL, "952", "CFA Franc BCEAO"),
    (178, "XPD", NULL, "964", "Palladium"),
    (179, "XPF", NULL, "953", "CFP Franc"),
    (180, "XPT", NULL, "962", "Platinum"),
    (181, "XTS", NULL, "963", "Code for testing purposes"),
    (182, "XXX", NULL, "999", "No currency involved"),
    (183, "YER",  "ï·¼", "886", "Yemeni Rial"),
    (184, "ZMK", NULL, "894", "Zambian Kwacha"),
    (185, "ZWD", "Z$", "716", "Zimbabwe Dollar");


-- /*******************************************************
-- *
-- * civicrm_option_group and civicrm_option_value
-- *
-- *******************************************************/

  INSERT INTO `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
  VALUES
        (@domain_id, 'group_type', 'Group Type', 0, 1);


  SELECT @option_group_id_gType := max(id) from civicrm_option_group where name = 'group_type';

  INSERT INTO
     civicrm_option_value (option_group_id, label, value, name, grouping, filter, is_default, weight, description, is_optgroup, is_reserved, is_active)
  VALUES
    (@option_group_id_gType, 'Access Control'  , 1, NULL, NULL, 0, NULL, 1, NULL, 0, 1, 1 ),
    (@option_group_id_gType, 'Mailing List'    , 2, NULL, NULL, 0, NULL, 2, NULL, 0, 1, 1 );


-- /*******************************************************
-- *
-- * civicrm_payment_processor_type (new processor plugins)
-- *
-- *******************************************************/
    INSERT INTO
        civicrm_payment_processor_type (domain_id, name, title, description, is_active, is_default, user_name_label, password_label, signature_label, subject_label, class_name, url_site_default, url_recur_default, url_button_default, url_site_test_default, url_recur_test_default, url_button_test_default, billing_mode, is_recur )
    VALUES
        (@domain_id,'PayJunction','PayJunction',NULL,1,0,'User Name','Password',NULL,NULL,'Payment_PayJunction','https://payjunction.com/quick_link',NULL,NULL,'https://payjunction.com/quick_link',NULL,NULL,1,0);


-- /*******************************************************
-- *
-- * civicrm_mailing_component
-- *
-- *******************************************************/

  INSERT IGNORE INTO
      civicrm_mailing_component (domain_id, name, component_type, subject, body_html, body_text, is_default, is_active)
  VALUES
          (@domain_id, 'Resubscribe Message', 'Resubscribe', 'Re-subscribe Confirmation', 'You have been re-subscribed to the following groups: {resubscribe.group}. You can un-subscribe by mailing {action.unsubscribe} or clicking {action.unsubscribeUrl}', 'You have been re-subscribed to the following groups: {resubscribe.group}. You can un-subscribe by mailing {action.unsubscribe} or clicking {action.unsubscribeUrl}', 1, 1);

-- /*******************************************************
-- *
-- * CRM-2349
-- *
-- *******************************************************/
    UPDATE civicrm_state_province SET name = 'Torfaen' WHERE name = 'Torfasn';

-- /*******************************************************
-- *
-- * fix Google Checkout URL
-- *
-- *******************************************************/
    UPDATE civicrm_payment_processor_type SET url_site_test_default = 'https://sandbox.google.com/checkout/' WHERE name = 'Google_Checkout';
Commit	Line	Data
6a488035	1	-- +--------------------------------------------------------------------+
9242538c	2	-- \| CiviCRM version 4.6 \|
6a488035	3	-- +--------------------------------------------------------------------+
e7112fa7	4	-- \| Copyright CiviCRM LLC (c) 2004-2015 \|
6a488035 TO	5	-- +--------------------------------------------------------------------+
	6	-- \| This file is a part of CiviCRM. \|
	7	-- \| \|
	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. \|
	11	-- \| \|
	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. \|
	16	-- \| \|
	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	-- +--------------------------------------------------------------------+
	24	-- /*******************************************************
	25	-- *
	26	-- * Select DomainID
	27	-- *
	28	-- *******************************************************/
c213dee5	29	SELECT @domain_id := id from civicrm_domain;
6a488035 TO	30
	31	-- /*******************************************************
	32	-- *
	33	-- * civicrm_mailing_spool
	34	-- *
	35	-- *******************************************************/
	36	CREATE TABLE civicrm_mailing_spool (
	37
	38
	39	id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
	40	domain_id int(10) unsigned NOT NULL COMMENT 'The ID of the civicrm domain.',
	41	job_id int(10) unsigned NOT NULL COMMENT 'The ID of the Job .',
	42	recipient_email text NULL DEFAULT NULL COMMENT 'The email of the receipients this mail is to be sent.',
	43	headers text NULL DEFAULT NULL COMMENT 'The header information of this mailing .',
66cae705 EM	44	body text NULL DEFAULT NULL COMMENT 'The body of this mailing.',
66cae705 EM	45	added_at datetime NULL DEFAULT NULL COMMENT 'date on which this job was added.',
6a488035 TO	46	removed_at datetime NULL DEFAULT NULL COMMENT 'date on which this job was removed.'
	47	,
	48	PRIMARY KEY ( id )
66cae705	49	,
6a488035 TO	50	CONSTRAINT FK_civicrm_mailing_spool_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
	51
	52	, CONSTRAINT FK_civicrm_mailing_spool_job_id FOREIGN KEY (job_id) REFERENCES civicrm_mailing_job(id)
	53
	54	) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
	55
	56
	57	-- /*******************************************************
	58	-- *
	59	-- * Modifying Tables
	60	-- *
	61	-- *******************************************************/
	62
	63	-- /*******************************************************
	64	-- *
	65	-- * civicrm_email
	66	-- *
	67	-- *******************************************************/
c213dee5	68	ALTER TABLE civicrm_email
c213dee5	69	ADD is_bulkmail tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Is this address for bulk mail ?';
6a488035 TO	70
	71	-- /*******************************************************
	72	-- *
	73	-- * civicrm_payment_processor_type
	74	-- *
	75	-- *******************************************************/
	76
c213dee5	77	ALTER TABLE civicrm_payment_processor_type
	78	DROP INDEX UI_name,
	79	ADD UNIQUE INDEX UI_name_domain_id( name, domain_id );
66cae705	80
6a488035 TO	81
	82	-- /*******************************************************
	83	-- *
	84	-- * civicrm_domain
	85	-- *
	86	-- *******************************************************/
66cae705	87	ALTER TABLE civicrm_domain
c213dee5	88	ADD email_name varchar(255) NULL DEFAULT NULL COMMENT 'The default email name that is used in the from address for all outgoing emails',
6a488035	89	ADD email_address varchar(255) NULL DEFAULT NULL COMMENT 'The default email address that is used as the from address for all outgoing emails',
c213dee5	90	DROP contact_name;
6a488035	91
66cae705 EM	92	UPDATE `civicrm_domain` SET `email_name`='FIXME';
66cae705 EM	93	UPDATE `civicrm_domain` SET `email_address`='info@EXAMPLE.ORG';
6a488035	94	UPDATE `civicrm_domain` SET `email_domain`='EXAMPLE.ORG';
66cae705	95
6a488035 TO	96	-- /*******************************************************
	97	-- *
	98	-- * civicrm_mailing_job
	99	-- *
	100	-- *******************************************************/
66cae705	101	ALTER TABLE civicrm_mailing_job
c213dee5	102	ADD is_test tinyint(4) DEFAULT 0 COMMENT 'Is this job for a test mail?',
c213dee5	103	DROP is_retry;
66cae705	104
6a488035 TO	105	UPDATE civicrm_mailing_job
6a488035 TO	106	SET is_test = 0;
66cae705	107
6a488035 TO	108
	109	-- /*******************************************************
	110	-- *
	111	-- * civicrm_currency
	112	-- *
	113	-- *******************************************************/
66cae705	114	ALTER TABLE civicrm_currency
c213dee5	115	ADD numeric_code varchar(3) NULL DEFAULT NULL COMMENT 'Numeric currency code',
6a488035	116	ADD full_name varchar(64) NULL DEFAULT NULL COMMENT 'Full currency name';
66cae705	117
6a488035 TO	118	-- /*******************************************************
	119	-- *
	120	-- * civicrm_mailing
	121	-- *
	122	-- *******************************************************/
c213dee5	123	ALTER TABLE civicrm_mailing
6a488035	124	ADD resubscribe_id int(10) unsigned NOT NULL,
c213dee5	125	ADD msg_template_id int(10) unsigned NULL DEFAULT NULL ,
	126	ADD CONSTRAINT FK_civicrm_mailing_msg_template_id FOREIGN KEY (msg_template_id) REFERENCES civicrm_msg_template(id),
	127	DROP is_template;
6a488035 TO	128
	129	-- /*******************************************************
	130	-- *
	131	-- * civicrm_mailing_component
	132	-- *
	133	-- *******************************************************/
c213dee5	134	ALTER TABLE civicrm_mailing_component
c213dee5	135	MODIFY component_type enum('Header','Footer','Subscribe','Welcome','Unsubscribe','OptOut','Reply','Resubscribe') NULL DEFAULT NULL;
6a488035 TO	136
	137	-- /*******************************************************
	138	-- *
	139	-- * civicrm_group
	140	-- *
	141	-- *******************************************************/
66cae705	142	ALTER TABLE civicrm_group
c213dee5	143	ADD group_type varchar(128) NULL DEFAULT NULL COMMENT 'FK to group type',
6a488035 TO	144	ADD INDEX index_group_type (group_type);
	145
	146	-- /*******************************************************
	147	-- *
	148	-- * civicrm_contribution_page
	149	-- *
	150	-- *******************************************************/
66cae705	151	ALTER TABLE civicrm_contribution_page
c213dee5	152	MODIFY default_amount_id int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to civicrm_custom_option.';
6a488035 TO	153
	154	-- /*******************************************************
	155	-- *
	156	-- * CRM-2260
	157	-- *
	158	-- *******************************************************/
	159
66cae705 EM	160	REPLACE
66cae705 EM	161	civicrm_currency (id, name, symbol, numeric_code, full_name)
c213dee5	162	VALUES
	163	( 1, "AUD", "$", "036", "Australian Dollar"),
	164	( 2, "CAD", "$", "124", "Canadian Dollar"),
	165	( 3, "EUR", "â‚¬", "978", "Euro"),
	166	( 4, "GBP", "Â£", "826", "Pound Sterling"),
	167	( 5, "ILS", "â‚ª", "826", "New Israeli Shekel"),
	168	( 6, "INR", "â‚¨", "356", "Indian Rupee"),
	169	( 7, "JPY", "Â¥", "392", "Japanese Yen"),
	170	( 8, "KRW", "â‚©", "410", "South Korean Won"),
	171	( 9, "LAK", "â‚", "418", "Lao Kip"),
	172	( 10, "MNT", "â‚®", "496", "Mongolian Tugrik"),
	173	( 11, "NGN", "â‚¦", "566", "Nigerian Naira"),
	174	( 12, "PLN", "zÅ‚", "985", "Polish ZÅ‚oty"),
	175	( 13, "THB", "à¸¿", "764", "Thai Baht"),
	176	( 14, "USD", "$", "840", "US Dollar"),
	177	( 15, "VND", "â‚«", "704", "Viet Nam Dong"),
	178	( 16, "ZAR", "R", "710", "South African Rand"),
	179	( 17, "AED", NULL, "784", "UAE Dirham"),
	180	( 18, "AFN", "Ø‹", "971", "Afghani"),
	181	( 19, "ALL","Lek", "008", "Albanian Lek"),
	182	( 20, "AMD", NULL, "051", "Armenian Dram"),
	183	( 21, "ANG", "Æ’", "532", "Netherlands Antillian Guilder"),
	184	( 22, "AOA", NULL, "973", "Angola Kwanza"),
	185	( 23, "ARS", "$", "032", "Argentine Peso"),
	186	( 24, "AWG", "Æ’", "533", "Aruban Guilder"),
	187	( 25, "AZN","Ð¼Ð°Ð½", "944", "Azerbaijanian Manat"),
	188	( 26, "BAM", "KM", "977", "Convertible Marks"),
	189	( 27, "BBD", "$", "052", "Barbados Dollar"),
	190	( 28, "BDT", NULL, "050", "Bangladeshi Taka"),
	191	( 29, "BGN", "Ð»Ð²", "975", "Bulgarian Lev"),
	192	( 30, "BHD", NULL, "048", "Bahraini Dinar"),
	193	( 31, "BIF", NULL, "108", "Burundi Franc"),
	194	( 32, "BMD", "$", "060", "Bermudian Dollar"),
	195	( 33, "BND", "$", "096", "Brunei Dollar"),
	196	( 34, "BOB", "$b", "068", "Boliviano"),
	197	( 35, "BOV", NULL, "984", "Bolivian Mvdol"),
	198	( 36, "BRL", "R$", "986", "Brazilian Real"),
	199	( 37, "BSD", "$", "044", "Bahamian Dollar"),
	200	( 38, "BTN", NULL, "064", "Bhutan Ngultrum"),
	201	( 39, "BWP", "P", "072", "Botswana Pula"),
	202	( 40, "BYR", "p.", "974", "Belarussian Rouble"),
	203	( 41, "BZD","BZ$", "084", "Belize Dollar"),
	204	( 42, "CDF", NULL, "976", "Franc Congolais"),
	205	( 43, "CHE", NULL, "947", "WIR Euro"),
	206	( 44, "CHF","CHF", "756", "Swiss Franc"),
	207	( 45, "CHW", NULL, "948", "WIR Franc"),
	208	( 46, "CLF", NULL, "990", "Unidades de fomento"),
	209	( 47, "CLP", "$", "152", "Chilean Peso"),
	210	( 48, "CNY", "Ã¥
	211	Âƒ", "156", "Chinese Yuan Renminbi"),
	212	( 49, "COP", "$", "170", "Colombian Peso"),
	213	( 50, "COU", NULL, "970", "Unidad de Valor Real"),
	214	( 51, "CRC", "â‚¡", "188", "Costa Rican Colon"),
	215	( 52, "CSD","Ð”Ð¸Ð½.","891", "Serbian Dinar"),
	216	( 53, "CUP", "â‚±", "192", "Cuban Peso"),
	217	( 54, "CVE", NULL, "132", "Cape Verde Escudo"),
	218	( 55, "CYP", "Â£", "196", "Cyprus Pound"),
	219	( 56, "CZK", "KÄ�", "203", "Czech Koruna"),
	220	( 57, "DJF", NULL, "262", "Djibouti Franc"),
	221	( 58, "DKK", "kr", "208", "Danish Krone"),
	222	( 59, "DOP","RD$", "214", "Dominican Peso"),
	223	( 60, "DZD", NULL, "012", "Algerian Dinar"),
	224	( 61, "EEK", "kr", "233", "Estonian Kroon"),
	225	( 62, "EGP", "Â£", "818", "Egyptian Pound"),
226	( 63, "ERN", NULL, "232", "Eritrean Nakfa"),
227	( 64, "ETB", NULL, "230", "Ethiopian Birr"),
228	( 65, "FJD", "$", "242", "Fiji Dollar"),
229	( 66, "FKP", "Â£", "238", "Falkland Islands Pound"),
230	( 67, "GEL", NULL, "981", "Georgian Lari"),
231	( 68, "GHC", "Â¢", "288", "Ghanaian Cedi"),
232	( 69, "GIP", "Â£", "292", "Gibraltar Pound"),
233	( 70, "GMD", NULL, "270", "Gambian Dalasi"),
234	( 71, "GNF", NULL, "324", "Guinea Franc"),
235	( 72, "GTQ", "Q", "320", "Guatemalan Quetzal"),
236	( 73, "GWP", NULL, "624", "Guinea-Bissau Peso"),
237	( 74, "GYD", "$", "328", "Guyana Dollar"),
238	( 75, "HKD","HK$", "344", "Hong Kong Dollar"),
239	( 76, "HNL", "L", "340", "Honduran Lempira"),
240	( 77, "HRK", "kn", "191", "Croatian Kuna"),
241	( 78, "HTG", NULL, "332", "Haitian Gourde"),
242	( 79, "HUF", "Ft", "348", "Hungarian Forint"),
243	( 80, "IDR", "Rp", "360", "Indonesian Rupiah"),
244	( 81, "IQD", NULL, "368", "Iraqi Dinar"),
245	( 82, "IRR", "ï·¼", "364", "Iranian Rial"),
246	( 83, "ISK", "kr", "352", "Iceland Krona"),
247	( 84, "JMD", "J$", "388", "Jamaican Dollar"),
248	( 85, "JOD", NULL, "400", "Jordanian Dinar"),
249	( 86, "KES", NULL, "404", "Kenyan Shilling"),
250	( 87, "KGS", "Ð»Ð²", "417", "Kyrgyzstan Som"),
251	( 88, "KHR", "áŸ›", "116", "Cambodian Riel"),
252	( 89, "KMF", NULL, "174", "Comoro Franc"),
253	( 90, "KPW", "â‚©", "408", "North Korean Won"),
254	( 91, "KWD", NULL, "414", "Kuwaiti Dinar"),
255	( 92, "KYD", "$", "136", "Cayman Islands Dollar"),
256	( 93, "KZT", "Ð»Ð²", "398", "Kazakhstan Tenge"),
257	( 94, "LBP", "Â£", "422", "Lebanese Pound"),
258	( 95, "LKR", "â‚¨", "144", "Sri Lanka Rupee"),
259	( 96, "LRD", "$", "430", "Liberian Dollar"),
260	( 97, "LSL", NULL, "426", "Lesotho Loti"),
261	( 98, "LTL", "Lt", "440", "Lithuanian Litas"),
262	( 99, "LVL", "Ls", "428", "Latvian Lats"),
263	(100, "LYD", NULL, "434", "Libyan Dinar"),
264	(101, "MAD", NULL, "504", "Moroccan Dirham"),
265	(102, "MDL", NULL, "498", "Moldovan Leu"),
266	(103, "MGA", NULL, "969", "Malagascy Ariary"),
267	(104, "MKD","Ð´ÐµÐ½", "807", "Macedonian Denar"),
268	(105, "MMK", NULL, "104", "Myanmar Kyat"),
269	(106, "MOP", NULL, "446", "Macao Pataca"),
270	(107, "MRO", NULL, "478", "Mauritanian Ouguiya"),
271	(108, "MTL", "Lm", "470", "Maltese Lira"),
272	(109, "MUR", "â‚¨", "480", "Mauritius Rupee"),
273	(110, "MVR", NULL, "462", "Maldive Rufiyaa"),
274	(111, "MWK", NULL, "454", "Malawi Kwacha"),
275	(112, "MXN", "$", "484", "Mexican Peso"),
276	(113, "MXV", NULL, "979", "Mexican Unidad de Inversion (UID)"),
277	(114, "MYR", "RM", "458", "Malaysian Ringgit"),
278	(115, "MZN", "MT", "943", "Mozambique Metical"),
279	(116, "NAD", "$", "516", "Namibian Dollar"),
280	(117, "NIO", "C$", "558", "Nicaraguan Cordoba Oro"),
281	(118, "NOK", "kr", "578", "Norwegian Krone"),
282	(119, "NPR", "â‚¨", "524", "Nepalese Rupee"),
283	(120, "NZD", "$", "554", "New Zealand Dollar"),
284	(121, "OMR", "ï·¼", "512", "Rial Omani"),
285	(122, "PAB","B/.", "590", "Panamanian Balboa"),
286	(123, "PEN","S/.", "604", "Peruvian Nuevo Sol"),
287	(124, "PGK", NULL, "598", "Papua New Guinea Kina"),
288	(125, "PHP","Php", "608", "Philippine Peso"),
289	(126, "PKR", "â‚¨", "586", "Pakistan Rupee"),
290	(127, "PYG", "Gs", "600", "Paraguay Guarani"),
291	(128, "QAR", "ï·¼", "634", "Qatari Rial"),
292	(129, "ROL", NULL, "642", "Romanian Old Leu"),
293	(130, "RON","lei", "946", "Romanian New Leu"),
294	(131, "RUB","Ñ€ÑƒÐ±", "643", "Russian Rouble"),
295	(132, "RWF", NULL, "646", "Rwanda Franc"),
296	(133, "SAR", "ï·¼", "682", "Saudi Riyal"),
297	(134, "SBD", "$", "090", "Solomon Islands Dollar"),
298	(135, "SCR", "â‚¨", "690", "Seychelles Rupee"),
299	(136, "SDD", NULL, "736", "Sudanese Dinar"),
300	(137, "SEK", "kr", "752", "Swedish Krona"),
301	(138, "SGD", "$", "702", "Singapore Dollar"),
302	(139, "SHP", "Â£", "654", "Saint Helena Pound"),
303	(140, "SIT", NULL, "705", "Slovenian Tolar"),
304	(141, "SKK","SIT", "703", "Slovak Koruna"),
305	(142, "SLL", NULL, "694", "Leone"),
306	(143, "SOS", "S", "706", "Somali Shilling"),
307	(144, "SRD", "$", "968", "Surinam Dollar"),
308	(145, "STD", NULL, "678", "SÃ£o Tome and Principe Dobra"),
309	(146, "SVC", "$", "222", "El Salvador Colon"),
310	(147, "SYP", "Â£", "760", "Syrian Pound"),
311	(148, "SZL", NULL, "748", "Swaziland Lilangeni"),
312	(149, "TJS", NULL, "972", "Tajik Somoni"),
313	(150, "TMM", NULL, "795", "Turkmenistan Manat"),
314	(151, "TND", NULL, "788", "Tunisian Dinar"),
315	(152, "TOP", NULL, "776", "Tongan Pa'anga"),
316	(153, "TRY","YTL", "949", "New Turkish Lira"),
317	(154, "TTD","TT$", "780", "Trinidad and Tobago Dollar"),
318	(155, "TWD","NT$", "901", "New Taiwan Dollar"),
319	(156, "TZS", NULL, "834", "Tanzanian Shilling"),
320	(157, "UAH", "â‚´", "980", "Ukrainian Hryvnia"),
321	(158, "UGX", NULL, "800", "Ugandan Shilling"),
322	(159, "USN", NULL, "997", "US Dollar (Next day)"),
323	(160, "USS", NULL, "998", "US Dollar (Same day)"),
324	(161, "UYU", "$U", "858", "Peso Uruguayo"),
325	(162, "UZS", "Ð»Ð²", "860", "Uzbekistan Sum"),
326	(163, "VEB", "Bs", "862", "Venezuela Bolivar"),
327	(164, "VUV", NULL, "548", "Vanuatu Vatu"),
328	(165, "WST", NULL, "882", "Samoan Tala"),
329	(166, "XAF", NULL, "950", "CFA Franc BEAC"),
330	(167, "XAG", NULL, "961", "Silver"),
331	(168, "XAU", NULL, "959", "Gold"),
332	(169, "XBA", NULL, "955", "Bond Markets Units European Composite Unit (EURCO)"),
333	(170, "XBB", NULL, "956", "European Monetary Unit (E.M.U.-6)"),
334	(171, "XBC", NULL, "957", "European Unit of Account 9 (E.U.A.-9)"),
335	(172, "XBD", NULL, "958", "European Unit of Account 17 (E.U.A.-17)"),
336	(173, "XCD", "$", "951", "East Caribbean Dollar"),
337	(174, "XDR", NULL, "960", "Special Drawing Right"),
338	(175, "XFO", NULL, NULL, "Gold-Franc"),
339	(176, "XFU", NULL, NULL, "UIC-Franc"),
340	(177, "XOF", NULL, "952", "CFA Franc BCEAO"),
341	(178, "XPD", NULL, "964", "Palladium"),
342	(179, "XPF", NULL, "953", "CFP Franc"),
343	(180, "XPT", NULL, "962", "Platinum"),
344	(181, "XTS", NULL, "963", "Code for testing purposes"),
345	(182, "XXX", NULL, "999", "No currency involved"),
346	(183, "YER", "ï·¼", "886", "Yemeni Rial"),
347	(184, "ZMK", NULL, "894", "Zambian Kwacha"),
348	(185, "ZWD", "Z$", "716", "Zimbabwe Dollar");
6a488035 TO	349
	350
	351	-- /*******************************************************
	352	-- *
	353	-- * civicrm_option_group and civicrm_option_value
	354	-- *
	355	-- *******************************************************/
	356
66cae705 EM	357	INSERT INTO `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
66cae705 EM	358	VALUES
c213dee5	359	(@domain_id, 'group_type', 'Group Type', 0, 1);
66cae705	360
6a488035	361
c213dee5	362	SELECT @option_group_id_gType := max(id) from civicrm_option_group where name = 'group_type';
6a488035	363
66cae705 EM	364	INSERT INTO
66cae705 EM	365	civicrm_option_value (option_group_id, label, value, name, grouping, filter, is_default, weight, description, is_optgroup, is_reserved, is_active)
c213dee5	366	VALUES
	367	(@option_group_id_gType, 'Access Control' , 1, NULL, NULL, 0, NULL, 1, NULL, 0, 1, 1 ),
	368	(@option_group_id_gType, 'Mailing List' , 2, NULL, NULL, 0, NULL, 2, NULL, 0, 1, 1 );
6a488035 TO	369
	370
	371	-- /*******************************************************
	372	-- *
	373	-- * civicrm_payment_processor_type (new processor plugins)
	374	-- *
	375	-- *******************************************************/
66cae705	376	INSERT INTO
6a488035	377	civicrm_payment_processor_type (domain_id, name, title, description, is_active, is_default, user_name_label, password_label, signature_label, subject_label, class_name, url_site_default, url_recur_default, url_button_default, url_site_test_default, url_recur_test_default, url_button_test_default, billing_mode, is_recur )
66cae705	378	VALUES
6a488035 TO	379	(@domain_id,'PayJunction','PayJunction',NULL,1,0,'User Name','Password',NULL,NULL,'Payment_PayJunction','https://payjunction.com/quick_link',NULL,NULL,'https://payjunction.com/quick_link',NULL,NULL,1,0);
	380
	381
	382	-- /*******************************************************
	383	-- *
	384	-- * civicrm_mailing_component
	385	-- *
	386	-- *******************************************************/
	387
c213dee5	388	INSERT IGNORE INTO
6a488035	389	civicrm_mailing_component (domain_id, name, component_type, subject, body_html, body_text, is_default, is_active)
c213dee5	390	VALUES
6a488035 TO	391	(@domain_id, 'Resubscribe Message', 'Resubscribe', 'Re-subscribe Confirmation', 'You have been re-subscribed to the following groups: {resubscribe.group}. You can un-subscribe by mailing {action.unsubscribe} or clicking {action.unsubscribeUrl}', 'You have been re-subscribed to the following groups: {resubscribe.group}. You can un-subscribe by mailing {action.unsubscribe} or clicking {action.unsubscribeUrl}', 1, 1);
	392
	393	-- /*******************************************************
	394	-- *
	395	-- * CRM-2349
	396	-- *
	397	-- *******************************************************/
	398	UPDATE civicrm_state_province SET name = 'Torfaen' WHERE name = 'Torfasn';
	399
	400	-- /*******************************************************
	401	-- *
	402	-- * fix Google Checkout URL
	403	-- *
	404	-- *******************************************************/
	405	UPDATE civicrm_payment_processor_type SET url_site_test_default = 'https://sandbox.google.com/checkout/' WHERE name = 'Google_Checkout';
	406