Читаем почту (IMAP). CLR-сборка.

by Alexey Knyazev 4. февраля 2010 11:22

В одной из предыдущих статей я писал, как читать почту в 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): сеанс завершается.

 

 

Переходы, обозначенные цифрами:

  1. соединение без предварительной аутентификации;
  2. соединение с предварительной аутентификацией;
  3. отвергнутое соединение;
  4. успешная аутентификация;
  5. успешное выполнение команды SELECT или EXAMINE;
  6. команда CLOSE или неудачное завершение команды SELECT или EXAMINE;
  7. команда 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

Пример результата:

 

Tags: , , , , ,

SQL Server

Комментарии (3) -

Ilya
Ilya Russia
17.04.2010 14:27:29 #

Как пример обращения по IMAP с SQL сервера - хорошо, но я бы побоялся его использовать в продуктиве.
Вы работаете с классом - TcpClient, использующий неуправляемые ресурсы. Обычно такие компоненты реализуют интерфейс IDisposable и у вас нет ни try/finaly, ни using (синтаксический сахан на try/catch)

Reply

Ilya
Ilya Russia
17.04.2010 14:29:40 #

Sorry, опечатлся using - синтаксический сахар try/finally

Reply

Admin
Admin Russia
19.04.2010 12:33:56 #

Сборка написана за несколько минут с целью демонстрации возможностей CLR, само собой тут не уделялось никакого внимания обработке возможных исключений

Reply

Pingbacks and trackbacks (1)+

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading