<commit_before>-- anonymizing db dump
-- Anonymizes all sensitive fields of a database dump of the XCoLab
--
-- author: <NAME>
-- created: Aug 10, 2015
-- last modified: Aug 10, 2015

-- User information and emails
update User_ set emailAddress = CONCAT("test+u", userId, "@example.com"), screenName = CONCAT("user", userId), firstName = CONCAT("User ", userId), lastName = "CoLab", reminderQueryAnswer = "", openId = "", greeting = "Welcome friend!", middleName = "", loginDate = '2009-08-19 01:00:00', lastLoginDate = '2009-08-19 01:00:00', lastFailedLoginDate = '2009-08-19 01:00:00', reminderQueryQuestion = "", passwordModifiedDate = '2009-08-19 01:00:00', modifiedDate = '2009-08-19 01:00:00', createDate = '2009-08-19 01:00:00', loginIP = "127.0.0.1", lastLoginIP = "127.0.0.1", facebookId = NULL, digest = NULL, password_ = '{<PASSWORD>}<PASSWORD>=', passwordEncrypted = 1;
update Address set userName = "Tim", street1 = "Massachusetts Avenue", city = "Cambridge", zip = "02139";
update Contact_ set birthday = '2000-01-01 00:00:00', male = 0, emailAddress = CONCAT("test+u", userId, "@example.com"), firstName = CONCAT("User ", userId), lastName = "CoLab", middleName = "", twitterSn = "", facebookSn = "", aimSn = "", smsSn = "", icqSn = "", jabberSn = "", msnSn = "", myspaceSn = "", skypeSn = "", ymSn = "", employeeStatusId = "", employeeNumber = "", jobTitle = "";
update emailtoignore set email = "<EMAIL>";
delete from UserFacebookMapping;
update xcolab_EmailList set email = "<EMAIL>";
update xcolab.ExpandoValue set data_ = "" where columnId in (SELECT columnId FROM xcolab.ExpandoColumn where name = "aboutMe"); -- deletes user bios

-- delete all messages
delete from xcolab_Message;
delete from xcolab_MessagingIgnoredRecipients;
delete from xcolab_MessagingMessage;
delete from xcolab_MessagingMessageConversion;
delete from xcolab_MessagingMessageRecipient;

-- session and login information
delete from xcolab_TrackedVisit;
update xcolab_LoginLog set ipAddress = "127.0.0.1", city = "", country = "", createDate = 0;<commit_msg>Updated script for anonymous dumps<commit_after>-- Anonymizes all sensitive fields of a database dump of the XCoLab

-- User information and emails
update User_ set emailAddress = CONCAT("test+u", userId, "@example.com"), screenName = CONCAT("user", userId), firstName = CONCAT("User ", userId), lastName = "CoLab", reminderQueryAnswer = "", openId = "", greeting = "Welcome friend!", middleName = "", loginDate = '2009-08-19 01:00:00', lastLoginDate = '2009-08-19 01:00:00', lastFailedLoginDate = '2009-08-19 01:00:00', reminderQueryQuestion = "", passwordModifiedDate = '<PASSWORD>', modifiedDate = '2009-08-19 01:00:00', createDate = '2009-08-19 01:00:00', loginIP = "127.0.0.1", lastLoginIP = "127.0.0.1", facebookId = NULL, digest = NULL, password_ = '{<PASSWORD>-1}<PASSWORD>=', passwordEncrypted = 1;
update members_Member set emailAddress  = CONCAT("test+u", id_, "@example.com"), screenName = CONCAT("user", id_), firstName = CONCAT("User ", id_), lastName = "CoLab", openId = "",  loginDate = '2009-08-19 01:00:00',  passwordModifiedDate = '<PASSWORD>', modifiedDate = '2009-08-19 01:00:00', createDate = '2009-08-19 01:00:00', loginIP = "127.0.0.1", facebookId = 0, hashedPassword = <PASSWORD>=', shortBio = '';
update Address set userName = "Tim", street1 = "Massachusetts Avenue", city = "Cambridge", zip = "02139";
update Contact_ set birthday = '2000-01-01 00:00:00', male = 0, emailAddress = CONCAT("test+u", userId, "@example.com"), firstName = CONCAT("User ", userId), lastName = "CoLab", middleName = "", twitterSn = "", facebookSn = "", aimSn = "", smsSn = "", icqSn = "", jabberSn = "", msnSn = "", myspaceSn = "", skypeSn = "", ymSn = "", employeeStatusId = "", employeeNumber = "", jobTitle = "";
update emailtoignore set email = "<EMAIL>";
delete from UserFacebookMapping;
update xcolab_EmailList set email = "<EMAIL>";
update xcolab.ExpandoValue set data_ = "" where columnId in (SELECT columnId FROM xcolab.ExpandoColumn where name = "aboutMe"); -- deletes user bios

-- delete all messages
delete from xcolab_Message;
delete from xcolab_MessagingIgnoredRecipients;
delete from xcolab_MessagingMessage;
delete from xcolab_MessagingMessageConversion;
delete from xcolab_MessagingMessageRecipient;

-- session and login information
delete from xcolab_TrackedVisit;
delete from xcolab_LoginLog;
{'fraction_non_alphanumeric': 0.1551842526894026, 'fraction_numerical': 0.06660563057907988, 'mean_word_length': 5.561561561561562, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 6, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6054200', 'n_tokens_mistral': 1581, 'n_tokens_neox': 1351, 'n_words': 394}