В одной из предыдущих статей я писал, как читать почту в SQL Server 2005/2008/2008R2 по протоколу POP3 (http://www.t-sql.ru/post/POP3CLR.aspx), там же пообещал, что в ближайшее время покажу, как осуществлять доступ к почте по протоколу IMAP.
Область применения протокола IMAP ( Internet Message Access Protocol ) аналогична области применения протокола POP3: он тоже предназначен для получения почты и используется на участке между MUA получателя и хранилищем сообщений. IMAP предоставляет более широкие возможности работы с почтовыми ящиками, чем POP3: он позволяет работать с несколькими почтовыми ящиками на одном или нескольких серверах IMAP как с файлами и каталогами на собственной машине пользователя. Обычно почтовые ящики сервера IMAP действительно представляют собой файлы в специальном каталоге сервера и его подкаталогах.
Как и в прошлый раз - это будит CLR-сборка (работает достаточно шустро). Но прежде рекомендую ознакомиться с описанием IMAP-протокола.
Полный перечень всех команд, а так же описание протокола можно прочитать в Запросе комментариев (англ. Request for Comments, RFC) — документ из серии пронумерованных информационных документов Интернета, содержащих технические спецификации и Стандарты.
RFC 3501 (IMAP версии 4 издание 1 (IMAP4rev1)), он заменил RFC 2060 (IMAP версии 4 (IMAP4)). Русскую версию спецификации так же легко найти в РУ-нете (http://opds.sut.ru/electronic_manuals/mail/5_IMAP.htm, http://book.itep.ru/4/44/imap4443.htm)
Сервер IMAP ожидает соединения от клиентов на порту TCP 143. После установления соединения сервер посылает свое приветствие клиенту, и начинается диалог, в котором клиент посылает серверу команды, а сервер сообщает о результатах их выполнения или присылает затребованную клиентом информацию. Как и сеанс POP3, сеанс IMAP делится на несколько состояний ( states ). Допустимый набор команд зависит от текущего состояния сеанса. Сеанс может находиться в одном из следующих состояний:
- -неаутентифицированное состояние (Not Authenticated State): клиент должен пройти процедуру аутентификации прежде, чем сможет выполнять большинство команд;
- -аутентифицированное состояние (Authenticated State): клиент аутентифицирован и должен выбрать почтовый ящик, прежде чем сможет работать с отдельными сообщениями;
- -выбранное состояние (Selected State): почтовый ящик выбран;
- -состояние выхода (Logout State): сеанс завершается.
Переходы, обозначенные цифрами:
- соединение без предварительной аутентификации;
- соединение с предварительной аутентификацией;
- отвергнутое соединение;
- успешная аутентификация;
- успешное выполнение команды SELECT или EXAMINE;
- команда CLOSE или неудачное завершение команды SELECT или EXAMINE;
- команда LOGOUT или потеря связи.
Сами Команды клиента и ответы сервера IMAP я в этой статье описывать не буду, так как вы сами при желании сможете с ними ознакомиться по ссылкам выше, а сразу приведу код сборки:
using System;
using Microsoft.SqlServer.Server;
using System.Net.Sockets;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
public class IMAPClr
{
private static TcpClient IMAP;
private static NetworkStream ns;
private static StreamWriter sw;
private static StreamReader sr;
[SqlFunction]
//Выводим кол-во писем
public static int MailCount(string Host, string User, string Password)
{
string s = Connect(Host, 143);
if (s.Substring(0, 4) != "* OK")
{
return -1;
}
s = Authenticate(User, Password);
if (s.Substring(0, 4) != "$ OK")
{
return -2;
}
sw.WriteLine("$ LOGIN " + User + " " + Password);
sw.Flush();
Response();
sw.WriteLine("$ STATUS INBOX (messages)");
sw.Flush();
string res = Response();
Match m = Regex.Match(res, "[0-9]*[0-9]");
Disconnect();
return Convert.ToInt32(m.ToString());
}
//Кол-во непрочитанных писем
public static int MailUnreadCount(string Host, string User, string Password)
{
string s = Connect(Host, 143);
if (s.Substring(0, 4) != "* OK")
{
return -1;
}
s = Authenticate(User, Password);
if (s.Substring(0, 4) != "$ OK")
{
return -2;
}
sw.WriteLine("$ LOGIN " + User + " " + Password);
sw.Flush();
Response();
sw.WriteLine("$ STATUS INBOX (unseen)");
sw.Flush();
string res = Response();
Match m = Regex.Match(res, "[0-9]*[0-9]");
Disconnect();
return Convert.ToInt32(m.ToString());
}
[SqlFunction(FillRowMethodName = "FillRowHeaders"
, TableDefinition = "MessageID int, MessageHeader nvarchar(max)")]
//Заголовки
public static IEnumerable GetMessagesHeaders(string Host, string User, string Password, int MessageStart, int MessageEnd)
{
ArrayList rows = new ArrayList();
string s = Connect(Host, 143);
if (s.Substring(0, 4) != "* OK")
{
rows.Add(new object[] { -1, s });
return rows;
}
s = Authenticate(User, Password);
if (s.Substring(0, 4) != "$ OK")
{
rows.Add(new object[] { -2, s });
return rows;
}
SelectInbox();
while (MessageStart <= MessageEnd)
{
rows.Add(new object[] { MessageStart, GetMessageHeaders(MessageStart).ToString() });
MessageStart += 1;
}
Disconnect();
return rows;
}
public static void FillRowHeaders(Object row, out int MessageID, out string MessageHeader)
{
object[] xrow = (object[])row;
MessageID = (int)xrow[0];
MessageHeader = (string)xrow[1];
}
[SqlFunction(FillRowMethodName = "FillRowMessages"
, TableDefinition = "MessageID int, Message nvarchar(max)")]
//Тело письма
public static IEnumerable GetMessages(string Host, string User, string Password, int MessageStart, int MessageEnd)
{
ArrayList rows = new ArrayList();
while (MessageStart <= MessageEnd)
{
string s = Connect(Host, 143);
if (s.Substring(0, 4) != "* OK")
{
rows.Add(new object[] { -1, s });
return rows;
}
s = Authenticate(User, Password);
if (s.Substring(0, 4) != "$ OK")
{
rows.Add(new object[] { -2, s });
return rows;
}
SelectInbox();
rows.Add(new object[] { MessageStart, GetMessage(MessageStart).ToString()});
Disconnect();
MessageStart += 1;
}
return rows;
}
public static void FillRowMessages(Object row, out int MessageID, out string Message)
{
object[] xrow = (object[])row;
MessageID = (int)xrow[0];
Message = (string)xrow[1];
}
//Подключение к IMAP-серверу
private static string Connect(string Host, int port)
{
try
{
IMAP = new TcpClient(Host, port);
ns = IMAP.GetStream();
sw = new StreamWriter(ns);
sr = new StreamReader(ns);
return Response();
}
catch (SocketException ex)
{
return ex.Message;
}
}
//Отключаемся от сервера
public static void Disconnect()
{
sw.WriteLine("$ LOGOUT");
sw.Flush();
IMAP.Close();
}
//Идентификация пользователя
public static string Authenticate(string User, string Password)
{
sw.WriteLine("$ LOGIN " + User + " " + Password);
sw.Flush();
return Response();
}
//Открываем доступ к указанному почтовому ящику
public static string SelectInbox()
{
sw.WriteLine("$ EXAMINE INBOX");
sw.Flush();
return Response();
}
//Заголовок письма по его номеру
public static object GetMessageHeaders(int index)
{
sw.WriteLine("$ FETCH " + index + " (body[header.fields (from subject date)])");
sw.Flush();
return Response();
}
//Письмо по номеру
public static object GetMessage(int index)
{
sw.WriteLine("$ FETCH " + index + " body[text]");
sw.Flush();
return Response();
}
private static string Response()
{
byte[] data = new byte[IMAP.ReceiveBufferSize];
int ret = ns.Read(data, 0, data.Length);
return Encoding.ASCII.GetString(data).TrimEnd();
}
}
Если у Вас возникнут вопросы по коду, то буду рад ответить в комментариях к этой статье.
Далее регистрируем сборку и создаём набор T-SQL функций для работы с ней:
--Регистрируем сборку
CREATE ASSEMBLY ClrIMAP
FROM 'C:\IMAP\IMAPClr.dll'
WITH PERMISSION_SET = UNSAFE;
GO
--Функция выводит кол-во писем в ящике
CREATE FUNCTION [dbo].[MailCount]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255)
)
RETURNS [int] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ClrIMAP].[IMAPClr].[MailCount]
GO
--Функция с кол-ом непрочитанных писем
CREATE FUNCTION [dbo].[MailUnreadCount]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255)
)
RETURNS [int] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ClrIMAP].[IMAPClr].[MailUnreadCount]
GO
--Функция чтения заголовков писем
CREATE FUNCTION [dbo].[GetMessagesHeaders]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255),
@MessageStart int,
@MessageEnd int
)
RETURNS TABLE
(
MessageID INT,
MessageHeader NVARCHAR(max)
)
EXTERNAL NAME [ClrIMAP].[IMAPClr].[GetMessagesHeaders];
GO
--Функция чтения самого письма (писем)
CREATE FUNCTION [dbo].[GetMessages]
(
@Host nvarchar(255),
@User nvarchar(255),
@Password nvarchar(255),
@MessageStart int,
@MessageEnd int
)
RETURNS TABLE
(
MessageID INT,
Message NVARCHAR(max)
)
EXTERNAL NAME [ClrIMAP].[IMAPClr].[GetMessages];
GO
Пара примеров работы с этими функциями:
--Выводим кол-во писем на сервере (всего и новых)
select
[dbo].[MailCount] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword') as MailCount,
[dbo].[MailUnreadCount] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword') as MailUnreadCount
--Выводим 5 первых писем и их заголовки (от кого, дата, тема)
select t1.MessageID, t1.MessageHeader, t2.Message from
[dbo].[GetMessagesHeaders] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword', 1, 5) t1
inner join
[dbo].[GetMessages] ('imap.MailServer.ru', 'MyMail@MailServer.ru', 'MyPassword', 1, 5) t2
on t1.MessageID=t2.MessageID
Пример результата:
Подписаться