[Из песочницы] Конвертируем форум из 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 версии поста.
В форуме в основном лежат объекты PostItem, которые вместо «account@domain.com» хранят ExchangeUserAddressEntry в виде «O=FIRST ORGANIZATION/OU=EXCHANGE ADMINISTRATIVE GROUP (FYDIBOHF23SPDLT)/CN=RECIPIENTS/CN=account». Поэтому для начала надо получить необходимые данные о пользователях из адресной книги, которые потом можно будет сопоставить с ExchangeUserAddressEntry:
Для этого нужно подключиться к нужной нам папке, получить список всех объектов и вытащить из каждого необходимые нам данные. К сожалению outlook позволяет копировать в форум файлы (типа xls, и т.п.), поэтому нас интересуют только объекты PostItem и MailItem, а остальное отправляем в garbage.
Выполняем, проверяем garbage, у меня туда попало несколько файлов xls, которые закинули в форум копи-пастом, в форуме мне точно это не нужно. В папке store появились rtf файлы с постами и файлы с вложениями. Осталось из rtf файлов сделать html-текст, заполнить им post_text и вытащить остальные вложения.
Как я выше уже писал, для обработки rtf я буду использовать линуксовый UnRtf, поэтому папку store копируем на linux машину. UnRtf преобразует rtf файл в html код, извлекая из файла картинки и вложения, заменяя их тегом img. Вложения получают расширение .wmf, если это было изображение, то все хорошо, оно потом откроется, а если это какой-нибудь doc файл, то он уже нечитаем. К счастью все такие вложения мы уже вытащили с помощью Attachments.Item ().SaveAsFile, поэтому наш скрипт не только извлечет html, но и сразу исправит теги img с wmf на правильные ссылки.
Выполняем, — наш форум теперь сконвертирован в mysql, папка с вложениями находится в /opt/unrtf/store/. Оттуда можно удалить *.rtf и выложить на веб-сервер с форумом в /path/. А можно не удалять и добавить в посты ссылки на оригинальный rtf файл, т. к. RTF — это проклятый формат, с которым даже продукты MS не могут корректно работать.
Для превращения БД в полноценный форум необходимо проделать еще кучу работы (сделать свой форум/конвертер для переноса в phpbb/yaf.net и т. п.), но первый шаг уже завершен.
Для начала необходимо понять, что же я хочу получить в итоге, какие нужны таблицы, чтобы существующий контент соответствовал структуре обычного форума.
Таблица с постами
Т. к. основная часть контента в 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
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:\temp\low\store\"
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_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('%s'%(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('%s'%(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
