Changeset 5711 in subversion


Ignore:
Timestamp:
Jan 5, 2012 3:45:22 AM (17 months ago)
Author:
alec
Message:
  • Fix SQL Error when saving a contact with many email addresses (#1488286)
Location:
trunk/roundcubemail
Files:
9 edited

Legend:

Unmodified
Added
Removed

  • trunk/roundcubemail/CHANGELOG

    r5708 r5711  
    22=========================== 
    33 
     4- Fix SQL Error when saving a contact with many email addresses (#1488286) 
    45- Fix strict email address searching if contact has more than one address 
    56- Use proper timezones from PHP's internal timezonedb (#1485592) 

  • trunk/roundcubemail/SQL/mssql.initial.sql

    r5460 r5711  
    4141        [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL , 
    4242        [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
    43         [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL , 
     43        [email] [text] COLLATE Latin1_General_CI_AI NOT NULL , 
    4444        [firstname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 
    4545        [surname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL , 

  • trunk/roundcubemail/SQL/mssql.upgrade.sql

    r5468 r5711  
    245245GO 
    246246 
     247-- Updates from version 0.7 
     248 
     249ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL 
     250GO 
     251 

  • trunk/roundcubemail/SQL/mysql.initial.sql

    r5460 r5711  
    4141 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', 
    4242 `data` longtext NOT NULL, 
    43  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     43 `user_id` int(10) UNSIGNED NOT NULL, 
    4444 PRIMARY KEY(`cache_id`), 
    4545 CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) 
    … …  
    5353 
    5454CREATE TABLE `cache_index` ( 
    55  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     55 `user_id` int(10) UNSIGNED NOT NULL, 
    5656 `mailbox` varchar(255) BINARY NOT NULL, 
    5757 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', 
    … …  
    6868 
    6969CREATE TABLE `cache_thread` ( 
    70  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     70 `user_id` int(10) UNSIGNED NOT NULL, 
    7171 `mailbox` varchar(255) BINARY NOT NULL, 
    7272 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', 
    … …  
    8282 
    8383CREATE TABLE `cache_messages` ( 
    84  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     84 `user_id` int(10) UNSIGNED NOT NULL, 
    8585 `mailbox` varchar(255) BINARY NOT NULL, 
    8686 `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', 
    … …  
    102102 `del` tinyint(1) NOT NULL DEFAULT '0', 
    103103 `name` varchar(128) NOT NULL DEFAULT '', 
    104  `email` varchar(255) NOT NULL, 
     104 `email` text NOT NULL DEFAULT '', 
    105105 `firstname` varchar(128) NOT NULL DEFAULT '', 
    106106 `surname` varchar(128) NOT NULL DEFAULT '', 
    107107 `vcard` longtext NULL, 
    108108 `words` text NULL, 
    109  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     109 `user_id` int(10) UNSIGNED NOT NULL, 
    110110 PRIMARY KEY(`contact_id`), 
    111111 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) 
    112112   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, 
    113  INDEX `user_contacts_index` (`user_id`,`email`) 
     113 INDEX `user_contacts_index` (`user_id`,`del`) 
    114114) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; 
    115115 
    … …  
    118118CREATE TABLE `contactgroups` ( 
    119119  `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    120   `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     120  `user_id` int(10) UNSIGNED NOT NULL, 
    121121  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', 
    122122  `del` tinyint(1) NOT NULL DEFAULT '0', 
    … …  
    130130CREATE TABLE `contactgroupmembers` ( 
    131131  `contactgroup_id` int(10) UNSIGNED NOT NULL, 
    132   `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     132  `contact_id` int(10) UNSIGNED NOT NULL, 
    133133  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', 
    134134  PRIMARY KEY (`contactgroup_id`, `contact_id`), 
    … …  
    145145CREATE TABLE `identities` ( 
    146146 `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    147  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     147 `user_id` int(10) UNSIGNED NOT NULL, 
    148148 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', 
    149149 `del` tinyint(1) NOT NULL DEFAULT '0', 
    … …  
    179179CREATE TABLE `searches` ( 
    180180 `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    181  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', 
     181 `user_id` int(10) UNSIGNED NOT NULL, 
    182182 `type` int(3) NOT NULL DEFAULT '0', 
    183183 `name` varchar(128) NOT NULL, 

  • trunk/roundcubemail/SQL/mysql.update.sql

    r5618 r5711  
    217217 
    218218ALTER TABLE `session` CHANGE `sess_id` `sess_id` varchar(128) NOT NULL; 
     219 
     220-- Updates from version 0.7 
     221 
     222ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`; 
     223ALTER TABLE `contacts` DROP INDEX `user_contacts_index`; 
     224ALTER TABLE `contacts` MODIFY `email` text NOT NULL DEFAULT ''; 
     225ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`del`); 
     226ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) 
     227   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; 
     228 
     229ALTER TABLE `cache` ALTER `user_id` DROP DEFAULT; 
     230ALTER TABLE `cache_index` ALTER `user_id` DROP DEFAULT; 
     231ALTER TABLE `cache_thread` ALTER `user_id` DROP DEFAULT; 
     232ALTER TABLE `cache_messages` ALTER `user_id` DROP DEFAULT; 
     233ALTER TABLE `contacts` ALTER `user_id` DROP DEFAULT; 
     234ALTER TABLE `contactgroups` ALTER `user_id` DROP DEFAULT; 
     235ALTER TABLE `contactgroupmembers` ALTER `contact_id` DROP DEFAULT; 
     236ALTER TABLE `identities` ALTER `user_id` DROP DEFAULT; 
     237ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT; 

  • trunk/roundcubemail/SQL/postgres.initial.sql

    r5460 r5711  
    108108    del smallint DEFAULT 0 NOT NULL, 
    109109    name varchar(128) DEFAULT '' NOT NULL, 
    110     email varchar(255) DEFAULT '' NOT NULL, 
     110    email text DEFAULT '' NOT NULL, 
    111111    firstname varchar(128) DEFAULT '' NOT NULL, 
    112112    surname varchar(128) DEFAULT '' NOT NULL, 
    … …  
    115115); 
    116116 
    117 CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); 
     117CREATE INDEX contacts_user_id_idx ON contacts (user_id, del); 
    118118 
    119119-- 

  • trunk/roundcubemail/SQL/postgres.update.sql

    r5627 r5711  
    170170 
    171171ALTER TABLE "session" ALTER sess_id TYPE varchar(128); 
     172 
     173-- Updates from version 0.7 
     174 
     175DROP INDEX contacts_user_id_idx; 
     176CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del); 
     177ALTER TABLE contacts ALTER email TYPE text; 

  • trunk/roundcubemail/SQL/sqlite.initial.sql

    r5615 r5711  
    2525CREATE TABLE contacts ( 
    2626  contact_id integer NOT NULL PRIMARY KEY, 
    27   user_id integer NOT NULL default '0', 
     27  user_id integer NOT NULL, 
    2828  changed datetime NOT NULL default '0000-00-00 00:00:00', 
    2929  del tinyint NOT NULL default '0', 
    3030  name varchar(128) NOT NULL default '', 
    31   email varchar(255) NOT NULL default '', 
     31  email text NOT NULL default '', 
    3232  firstname varchar(128) NOT NULL default '', 
    3333  surname varchar(128) NOT NULL default '', 
    … …  
    3636); 
    3737 
    38 CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); 
     38CREATE INDEX ix_contacts_user_id ON contacts(user_id, del); 
    3939 
    4040 

  • trunk/roundcubemail/SQL/sqlite.update.sql

    r5615 r5711  
    294294  vars text NOT NULL 
    295295); 
    296  
    297296CREATE INDEX ix_session_changed ON session (changed); 
     297 
     298-- Updates from version 0.7 
     299 
     300CREATE TABLE contacts_tmp ( 
     301  contact_id integer NOT NULL PRIMARY KEY, 
     302  user_id integer NOT NULL, 
     303  changed datetime NOT NULL default '0000-00-00 00:00:00', 
     304  del tinyint NOT NULL default '0', 
     305  name varchar(128) NOT NULL default '', 
     306  email text NOT NULL default '', 
     307  firstname varchar(128) NOT NULL default '', 
     308  surname varchar(128) NOT NULL default '', 
     309  vcard text NOT NULL default '', 
     310  words text NOT NULL default '' 
     311); 
     312 
     313INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) 
     314    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts; 
     315 
     316DROP TABLE contacts; 
     317 
     318CREATE TABLE contacts ( 
     319  contact_id integer NOT NULL PRIMARY KEY, 
     320  user_id integer NOT NULL, 
     321  changed datetime NOT NULL default '0000-00-00 00:00:00', 
     322  del tinyint NOT NULL default '0', 
     323  name varchar(128) NOT NULL default '', 
     324  email text NOT NULL default '', 
     325  firstname varchar(128) NOT NULL default '', 
     326  surname varchar(128) NOT NULL default '', 
     327  vcard text NOT NULL default '', 
     328  words text NOT NULL default '' 
     329); 
     330 
     331INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) 
     332    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp; 
     333 
     334CREATE INDEX ix_contacts_user_id ON contacts(user_id, del); 
     335DROP TABLE contacts_tmp; 
Note: See TracChangeset for help on using the changeset viewer.