Migrating identities

This is a small MySQL script that migrates email addresses and full names from old IMP 2/3 databases to the identity arrays used in Horde 3/IMP 4. By tweaking the sub-select statement, you can use the same script to migrate existing user data from any external source.

INSERT INTO horde_prefs

    (pref_uid, pref_scope, pref_name, pref_value)

    SELECT

        SUBSTRING(user, 1, LOCATE('@', user) - 1),

        'horde',

        'identities',

        CONCAT('a:1:{i:0;a:5:{s:2:"id";s:16:"Default Identity";s:8:"fullname";s:',

               LENGTH(IFNULL(fullname, '')), ':"', IFNULL(fullname, ''),

               '";s:9:"from_addr";s:', LENGTH(IFNULL(replyto, '')), ':"', IFNULL(replyto, ''),

               '";s:16:"default_identity";s:1:"0";s:9:"signature";s:', LENGTH(IFNULL(sig, '')), ':"', IFNULL(sig, ''), '";}}')

         FROM imp_pref WHERE user LIKE '%@localhost' OR user LIKE '%@example.com';

This code takes all entries from the horde_prefs table that have the user name ending with @localhost or @example.com. This domain part is then stripped from the user name (SUBSTRING(...)) and a string representing a serialized PHP array is build (CONCAT(...)) from the fullname, replyto, and sig columns. The IFNULL(...) expressions make sure that this works with both, empty and NULL values in the table fields.

Please note that this script only makes sense for one-time migration of existing data. If you have this kind of user data in an external source permanently, e.g. in a company addressbook, LDAP directory or similar, you should use preference hooks to create the user identities instead.