Changeset 1bd826b in iPeer


Ignore:
Timestamp:
2013-08-28T15:13:36-07:00 (4 years ago)
Author:
Compass <pan.luo@…>
Branches:
3.1.x, dev, master
Children:
3ccd390
Parents:
9c2832e
git-author:
Compass <pan.luo@…> (06/27/2013 05:04:15 PM)
git-committer:
Compass <pan.luo@…> (08/28/2013 03:13:36 PM)
Message:

Update delta script to clean up some invalid characters in database

File:
1 edited

Legend:

Unmodified
Added
Removed
  • app/config/sql/delta_6.sql

    r9c2832e r1bd826b  
    111111ALTER TABLE user_tutors MODIFY created datetime, MODIFY modified datetime;
    112112
     113-- clean up some wired characters, the conversion below doesn't like them
     114UPDATE users SET first_name = TRIM(REPLACE(first_name, "�", ' ')) WHERE  `first_name` LIKE  '%�%';
     115UPDATE users SET last_name = TRIM(REPLACE(last_name, "�", ' ')) WHERE  `last_name` LIKE  '%�%';
     116UPDATE users SET first_name = TRIM(REPLACE(first_name, concat(0x89,0xF7,0xBC), ' ')) WHERE  `first_name` LIKE  concat('%',0x89,0xF7,0xBC,'%');
     117UPDATE users SET last_name = TRIM(REPLACE(last_name, concat(0x89,0xF7,0xBC), ' ')) WHERE  `last_name` LIKE  concat('%',0x89,0xF7,0xBC,'%');
     118UPDATE users SET first_name = TRIM(REPLACE(first_name, 0xa0, ' ')) WHERE  `first_name` LIKE  concat('%',0xa0,'%');
     119UPDATE users SET last_name = TRIM(REPLACE(last_name, 0xa0, ' ')) WHERE  `last_name` LIKE  concat('%',0xa0,'%');
     120UPDATE users SET first_name = TRIM(REPLACE(first_name, 0xf8, ' ')) WHERE  `first_name` LIKE  concat('%',0xf8,'%');
     121UPDATE users SET last_name = TRIM(REPLACE(last_name, 0xf8, ' ')) WHERE  `last_name` LIKE  concat('%',0xf8,'%');
     122UPDATE users SET first_name = TRIM(REPLACE(first_name, 0x81, ' ')) WHERE  `first_name` LIKE  concat('%',0x81,'%');
     123UPDATE users SET last_name = TRIM(REPLACE(last_name, 0x81, ' ')) WHERE  `last_name` LIKE  concat('%',0x81,'%');
     124UPDATE users SET first_name = TRIM(REPLACE(first_name, 0xff, ' ')) WHERE  `first_name` LIKE  concat('%',0xff,'%');
     125UPDATE users SET last_name = TRIM(REPLACE(last_name, 0xff, ' ')) WHERE  `last_name` LIKE  concat('%',0xff,'%');
     126UPDATE users SET first_name = TRIM(REPLACE(first_name, 0xe5, '')) WHERE  `first_name` LIKE  concat('%',0xe5,'%');
     127UPDATE users SET last_name = TRIM(REPLACE(last_name, 0xe5, '')) WHERE  `last_name` LIKE  concat('%',0xe5,'%');
     128UPDATE users SET first_name = TRIM(REPLACE(first_name, 0xe6, '')) WHERE  `first_name` LIKE  concat('%',0xe6,'%');
     129UPDATE users SET last_name = TRIM(REPLACE(last_name, 0xe6, '')) WHERE  `last_name` LIKE  concat('%',0xe6,'%');
     130UPDATE users SET first_name = TRIM(REPLACE(first_name, 0xe9, '')) WHERE  `first_name` LIKE  concat('%',0xe9,'%');
     131UPDATE users SET last_name = TRIM(REPLACE(last_name, 0xe9, '')) WHERE  `last_name` LIKE  concat('%',0xe9,'%');
     132
    113133-- Missed one table's char set conversion during the last upgrade
    114134-- Do this here or MySQL will complain about invalid dates.
     
    150170ALTER TABLE events CHANGE title title VARBINARY(255);
    151171ALTER TABLE events CHANGE title title VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT '';
    152 ALTER TABLE events CHANGE description description VARBINARY(255);
     172ALTER TABLE events CHANGE description description BLOB;
    153173ALTER TABLE events CHANGE description description TEXT CHARACTER SET utf8;
    154174ALTER TABLE events CHANGE self_eval self_eval VARBINARY(11);
Note: See TracChangeset for help on using the changeset viewer.