Конвертируем форум из MS Exchange в MySQL с помощью Python

В общих папках ms exchange есть форум, куда пишут сотрудники, он очень неудобный и тормозной. Чтобы перенести форум на какое-то адекватное решение, необходимо также перенести уже и созданный контент. Гугл не нашел готовых конверторов в *bb, поэтому я решил сделать свой на python, для начала в БД.

Для начала необходимо понять, что же я хочу получить в итоге, какие нужны таблицы, чтобы существующий контент соответствовал структуре обычного форума.

Таблица с постами

Т. к. основная часть контента в rtf, то сразу я её в html записать не смогу. Outlook конечно поддерживает SaveAsHTML, но на выходе получается дикая порнография. Изучил несколько бесплатных библиотек по конвертированию rtf в html, ничего хорошего не нашел. Лучше всего конвертирует линуксовая утилита UnRtf(у нее есть виндовый порт, но кириллицу он переваривает плохо). Поэтому, помимо стандартных post_id, user_id, post_time, topic_id, post_text мне нужен столбец для хранения rtf версии поста.

CREATE TABLE `posts` (
  `post_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `post_time` int(11) DEFAULT NULL,
  `topic_id` int(11) DEFAULT NULL,
  `post_text` text,
  `rtf_file` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Таблица с пользователями

CREATE TABLE `users` (
  `id` int(11) NOT NULL, --локальный id
  `mail` varchar(45) DEFAULT NULL, --ExchangeUserAddressEntry
  `exmail` varchar(100) DEFAULT NULL, --exchange аккаунт, 
  `exist` tinyint(1) DEFAULT NULL, --аккаунт существует в форуме, т. е. с него был сделан хотя бы один пост. 
  `name` varchar(100) DEFAULT NULL, --Имя с фамилией
  `inab` tinyint(1) DEFAULT NULL, --Находится ли аккаунт в адресной книге
  PRIMARY KEY (`id`),
  UNIQUE KEY `mail_UNIQUE` (`mail`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Таблица с темами

CREATE TABLE `topics` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title_UNIQUE` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Таблица с вложениями

CREATE TABLE `attachments` (
  `filename` varchar(45) NOT NULL, --имя файла
  `name` varchar(255) DEFAULT NULL, --отображаемое имя
  `post_id` int(11) DEFAULT NULL, --id поста
  `att_id` int(11) DEFAULT NULL, --id вложения
  PRIMARY KEY (`filename`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Таблица с некорректно обработанными outlook объектами

CREATE TABLE `garbage` (
  `id` int(11) NOT NULL,
  `rtf_file` varchar(45) DEFAULT NULL, --имя post.rtf файла
  `class` int(11) DEFAULT NULL, -- Outlook Item class, 
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Наполняем таблицы данными

В форуме в основном лежат объекты PostItem, которые вместо «account@domain.com» хранят ExchangeUserAddressEntry в виде «O=FIRST ORGANIZATION/OU=EXCHANGE ADMINISTRATIVE GROUP (FYDIBOHF23SPDLT)/CN=RECIPIENTS/CN=account». Поэтому для начала надо получить необходимые данные о пользователях из адресной книги, которые потом можно будет сопоставить с ExchangeUserAddressEntry:

import win32com.client # для работы с  Outlook.Application
import pymysql.cursors # для работы с mysql

# создаем COM объект и получаем все AddressEntries
object = win32com.client.Dispatch("Outlook.Application")
ns = object.GetNamespace("MAPI")
als =  ns.AddressLists
gal =  als.Item("Global Address List")
ent =  gal.AddressEntries

#заносим данные в mysql для каждой записи 
cnx = pymysql.connect(use_unicode=True, charset='utf8',user='outlook', password='password', host='server',database='outlook')
cursor = cnx.cursor()
id = 0
for rec in ent:
    id += 1
    exmail = rec.Address # ExchangeUserAddressEntry
    name = rec.Name  # Имя с фамилией
    mail = rec.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x39FE001E") # получаем аккаунт в виде «account@domain.com»
    cursor.execute("INSERT INTO users (id, mail, exmail, name, inab) VALUES (%s, %s, %s, %s, 1);", (id, mail,exmail,name)) # здесь же заполняем inab =1, т. к. запись присутствует в адресной книге
cursor.close()
cnx.close()

Подготовка завершена, приступаем к парсингу контента

Для этого нужно подключиться к нужной нам папке, получить список всех объектов и вытащить из каждого необходимые нам данные. К сожалению outlook позволяет копировать в форум файлы (типа xls, и т.п.), поэтому нас интересуют только объекты PostItem и MailItem, а остальное отправляем в garbage.

import win32com.client
import pymysql.cursors
object = win32com.client.Dispatch("Outlook.Application")
ns = object.GetNamespace("MAPI")
tf = ns.GetFolderFromID('<EntryID>') # обращаемся к общей папке с форумом по EntryID
i = 0
tmp = tf.items # получаем все посты/письма
tmp.sort('[ReceivedTime]',False) # сортируем в хронологическом порядке
cnx = pymysql.connect(use_unicode=True, charset='utf8',user='outlook', password='password', host='server',database='outlook')
cursor = cnx.cursor()
for aaa in tmp:
   i +=1
   rtf_file = "post_%d.rtf" %i #задаем имя rtf файла
   if (aaa.Class == 45) or (aaa.Class ==  43): # если объект postitem или mailitem
      aaa.SaveAs('c:\temp\low\store\%s' %rtf_file ,1) #Save as rtf
      #Извлекаем вложения во временную папку,в моем случае это "c:templowstore"
      for ac in range(1,aaa.Attachments.Count,1):
         if aaa.Attachments.Item(ac).Type <> 6: # для всех типов, кроме OLE document, с ним пусть разбирается unrtf
            name =  aaa.Attachments.Item(ac).FileName
            ext = name.split('.')[-1]
            filename = 'att_%d_%d.%s' %(i,ac,ext)
            aaa.Attachments.Item(ac).SaveAsFile('c:\temp\low\store\'+filename)
            cursor.execute("INSERT IGNORE INTO attachments (filename, name, post_id, att_id) VALUES (%s, %s, %s, %s);" ,(filename, name,i,ac))
      #Заносим данные пользователя
      exmail = aaa.SenderEmailAddress
      name = aaa.SenderName
      #Проверяем, есть ли уже такой в таблице users, и если нет, то сразу получаем новый user_id
      cursor.execute("SELECT id FROM users WHERE exmail = '%s' UNION SELECT max(id)+1 FROM users;" %(exmail)) 
      res = cursor.fetchall()
      if len(res) == 2:
         user_id =  res[0][0]
         cursor.execute("UPDATE users SET exist=1 WHERE id=%s;",user_id) #помечаем,что пользователь делал посты 
      elif len(res) == 1:
         #Создаем нового, обязательно задав inab=0, т. к. пользователь неактивный
         user_id =  res[0][0]
         mail = exmail.split('=')[-1]
         if '@' not in mail:
            mail = mail+'@not.exist' #часть писем может быть откуда-то скопирована и к AD отношения не иметь
         cursor.execute("INSERT INTO users (id,exist, exmail,mail, name, inab) VALUES (%s,1, %s, %s, %s, 0);" ,(user_id, exmail,mail,name)) 
      #Разбираемся с темами
      topic = aaa.ConversationTopic # аналогом тем в outlook является ConversationTopic
      tq = """set @mmm = (SELECT IFNULL(max(id), 0)+1 FROM topics);
            INSERT IGNORE INTO
            topics(id,title)
            values (@mmm,%s);"""
      cursor.execute(tq,topic) #заносим в базу
      #Заносим данные в posts
      cursor.execute("SELECT id FROM topics WHERE title = %s;",topic) 
      topic_id =cursor.fetchall()[0][0]
      post_time = int(aaa.ReceivedTime)
      cursor.execute("INSERT IGNORE INTO posts (post_id, user_id, post_time, topic_id, rtf_file) VALUES (%s, %s, %s, %s, %s);",(i,user_id,post_time,topic_id,rtf_file))
   else:
      #Garbage
      cursor.execute("INSERT IGNORE INTO garbage (id, rtf_file,class) VALUES (%s, %s,%s);",(i,rtf_file,aaa.Class))
cursor.close()
cnx.close()

Выполняем, проверяем garbage, у меня туда попало несколько файлов xls, которые закинули в форум копи-пастом, в форуме мне точно это не нужно. В папке store появились rtf файлы с постами и файлы с вложениями. Осталось из rtf файлов сделать html-текст, заполнить им post_text и вытащить остальные вложения.

Последний штрих

Как я выше уже писал, для обработки rtf я буду использовать линуксовый UnRtf, поэтому папку store копируем на linux машину. UnRtf преобразует rtf файл в html код, извлекая из файла картинки и вложения, заменяя их тегом img. Вложения получают расширение .wmf, если это было изображение, то все хорошо, оно потом откроется, а если это какой-нибудь doc файл, то он уже нечитаем. К счастью все такие вложения мы уже вытащили с помощью Attachments.Item().SaveAsFile, поэтому наш скрипт не только извлечет html, но и сразу исправит теги img с wmf на правильные ссылки.

#!/usr/bin/python
import lxml.etree, pymysql, subprocess, os
#lxml нам понадобится для работы с тегами
#Пишем функцию для парсинга, cpost - это post_id. /tmp/2del - каталог, куда unrtf выложит извлеченные картинки
def parsertf(cpost):
    p = subprocess.Popen('unrtf /opt/unrtf/store/post_%d.rtf'%cpost, stdout=subprocess.PIPE, stderr=subprocess.PIPE, universal_newlines=True,  shell=True, cwd = '/tmp/2del/')
    f = p.stdout.read()
    root = lxml.etree.HTML(f)[1]
    img_id = 0
    for img in root.xpath('//img'): # вложения и картинки определяются как <img>, надо прописать правильные пути
        img_id += 1
        if img.attrib['src'][-4:] == '.wmf': #если img src = *.wmf, - то это вложение.
            #Проверяем, есть ли уже такое извлеченное с помощью outlook
            cursor.execute('SELECT filename,name FROM attachments WHERE post_id=%s AND att_id=%s;'%(cpost,img_id))
            try:
                res = cursor.fetchall()[0]
                img.addnext(lxml.etree.fromstring('<a href="/path/%s">%s</a>'%(res[0],res[1])))
                #Вложение есть, правим ссылку на путь, где оно будет располагаться на сервере
            except:
                    #Вложения нет, добавляем wmf. 
                    attname = 'att_%d_%d'%(cpost,img_id)+img.attrib['src'][-4:]
                    subprocess.Popen('mv -f /tmp/2del/%s /opt/unrtf/store/%s'%(img.attrib['src'], attname), shell=True)
                    img.addnext(lxml.etree.fromstring('<a href="/path/%s">%s</a>'%(attname,attname)))
            img.getparent().remove(img)
        else:
            #Картинки
            imgname = 'img_%d_%d'%(cpost,img_id)+img.attrib['src'][-4:]
            subprocess.Popen('mv -f /tmp/2del/%s /opt/unrtf/store/%s'%(img.attrib['src'], imgname), shell=True)
            img.attrib['src'] = imgname
    root.remove(root[0]) # удаляем шапку письма from/to, и т.п.
    htmltext = lxml.etree.tostring(root)
    cursor.execute('UPDATE posts SET post_text=%s WHERE post_id=%s;',(htmltext,cpost))
    subprocess.Popen('rm -rf /tmp/2del/*', shell=True)
    return htmltext
cnx = pymysql.connect(use_unicode=True, charset='utf8',user='outlook', password='password', host='server',database='outlook')
cursor = cnx.cursor()
#Применяем функцию к каждому посту
cursor.execute('SELECT post_id FROM posts;')
posts =  cursor.fetchall()
for cpost in posts:
    parsertf(cpost[0])
cursor.close()
cnx.close()

Выполняем, — наш форум теперь сконвертирован в mysql, папка с вложениями находится в /opt/unrtf/store/. Оттуда можно удалить *.rtf и выложить на веб-сервер с форумом в /path/. А можно не удалять и добавить в посты ссылки на оригинальный rtf файл, т. к. RTF — это проклятый формат, с которым даже продукты MS не могут корректно работать.

Заключение

Для превращения БД в полноценный форум необходимо проделать еще кучу работы(сделать свой форум/конвертер для переноса в phpbb/yaf.net и т. п.), но первый шаг уже завершен.

» Код на git

Автор: mike_Z

Источник