Чтение почты (POP3) в SQL Server 2005/2008

Источник: t-sql

Продолжаю тему ~~без~~полезных примеров CLR-сборок в помощь администратору(разработчику) БД.
В этот раз решил читать почту в SQL Server 2005/2008/2008R2 через протокол POP3. Многие скажут, что сервер БД не предназначен для этого, но ВДРУГ!?
Конечно для этого у нас есть SQL Mail, но учитывая ряд ограничений, о которых чуть ниже, пришлось посмотреть в сторону собственной сборки.
Для чтения почты используются, как правило, протокол POP3 либо IMAP(о нём в следующий раз, возможно так же в качестве очередной CLR-сборки).

Ну и для начала всё-таки об ограничениях SQL Mail:

Примечание

В будущей версии Microsoft SQL Server эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется. Чтобы отправить почту из SQL Server, используйте компонент Database Mail.

…но как читать-то почту!? Ведь Database Mail только отправляет почту через SMTP-протокол…

Примечание

Служба SQL Mail требует наличия подключения к почтовой станции, почтовый ящик, почтовый профиль и учетную запись пользователя домена Microsoft, используемую для входа на SQL Server (учетная запись пользователя должна быть в том же домене, что и SQL Server). Служба MSSQLServer должна быть запущена под этой учетной записью домена пользователя. С помощью расширенных хранимых процедур службы SQL Mail можно отправлять сообщения из триггера или хранимой процедуры. Хранимые процедуры службы SQL Mail могут обрабатывать запросы, полученные по электронной почте, и возвращать результирующий набор, создавая ответное электронное сообщение.

Эти ограничения заставили взглянуть в сторону изучения протоколов POP3/IMAP.

Разбирём POP3 чуть подробнее:
Краткое, но достаточно емкое описание почтового протокола POP3.

Перед работой через протокол POP3 сервер прослушивает порт 110. Когда клиент хочет использовать этот протокол, он должен создать TCP соединение с сервером. Когда соединение установлено, сервер отправляет приглашение. Затем клиент и POP3 сервер обмениваются информацией пока соединение не будет закрыто или прервано.

Команды POP3 состоят из ключевых слов, за некоторыми следует один или более аргументов. Все команды заканчиваются парой CRLF (в Visual Basic константа vbCrLf). Ключевые слова и аргументы состоят из печатаемых ASCII символов. Ключевое слово и аргументы разделены одиночным пробелом. Ключевое слово состоит от 3-х до 4-х символов, а аргумент может быть длиной до 40-ка символов.

Ответы в POP3 состоят из индикатора состояния и ключевого слова, за которым может следовать дополнительная информация. Ответ заканчивается парой CRLF. Существует только два индикатора состояния: "+OK" - положительный и "-ERR" - отрицательный.

Ответы на некоторые команды могут состоять из нескольких строк. В этих случаях каждая строка разделена парой CRLF, а конец ответа заканчивается ASCII символом 46 (".") и парой CRLF.

POP3 сессия состоит из нескольких режимов. Как только соединение с сервером было установлено и сервер отправил приглашение, то сессия переходит в режим авторизации. В этом режиме клиент должен идентифицировать себя на сервере. После успешной идентификации сессия переходит в режим транзакции. В этом режиме клиент запрашивает сервер выполнить определённые команды. Когда клиент отправляет команду QUIT, сессия переходит в режим обновления. В этом режиме POP3 сервер освобождает все занятые ресурсы и завершает работу. После этого TCP соединение закрывается.

Список команд:

USER - Когда РОРЗ -сессия находится в состоянии аутентификации (AUTHORIZATION), и клиент должен зарегистрировать себя на РОРЗ -сервере. Это может быть выполнено либо с помощью команд USER и PASS - ввод открытых пользовательского идентификатора и пароля (именно этот способ используется чаще), либо командой АРОР - аутентификация цифровой подписью, на базе секретного ключа. Любой РОРЗ -сервер должен поддерживать хотя бы один из механизмов аутентификации.
Аргументом - "name" является строка, идентифицирующая почтовый ящик системы. Этот идентификатор должен быть уникальным в данной почтовой системе РОРЗ -сервера. Если ответом на эту команду является строка индикатора "+OK", клиент может отправлять команду PASS - ввод пароля или QUIT - завершить сессию. Если ответом является строка "-ERR", клиент может либо повторить команду USER, либо закрыть сессию

PASS - Аргументом команды является строка пароля данного почтового ящика. После получения команды PASS, РОРЗ -сервер, на основании аргументов команд USER и PASS, определяет возможность доступа к заданному почтовому ящику. Если РОРЗ -сервер ответил "+OK", это означает, что аутентификация клиента прошла успешно и он может работать со своим почтовым ящиком, т. е. сессия переходит в состояние TRANSACTION. Если РОРЗ- сервер ответил "-ERR", то либо был введен неверный пароль, либо не найден указанный почтовый ящик

STAT - После того как клиент успешно прошел процедуру аутентификации в РОРЗ- сервере, и РОРЗ- сервер "закрыл" определенный почтовый ящик только для использования данным клиентом (для тех, кто работал с базами данных, это называется EXCLUSIVE ACCESS LOCK), РОРЗ- сессия переходит в режим TRANSACTION, и клиент может начать работу со своей почтой
Команда STAT (без аргументов) используется для просмотра состояния текущего почтового ящика.
В ответ РОРЗ- сервер возвращает строку, содержащую количество и общий размер в байтах сообщений, которые клиент может получить с РОРЗ- сервера. Сообщения, помеченные на удаление, не учитываются

LIST [msg] - Команда LIST может передаваться как с аргументом msg - номером сообщения, так и без аргумента.
Если команда содержит аргумент, и сообщение с указанным номером существует, ответом на нее будет "информационная строка", которая содержит номер сообщения и размер сообщения в байтах. Если аргумент не указан - ответом будет список информационных строк обо всех сообщениях в данном почтовом ящике. Сообщения, помеченные на удаление не фигурируют в этом списке

RETR msg - Используется для передачи клиенту запрашиваемого сообщения. Аргумент команды - номер сообщения. Если запрашиваемого сообщения нет, возвращается отрицательный индикатор "-ERR".

DELE msg - Аргумент команды- номер сообщения. Сообщения, помеченные на удаление, реально удаляются только после закрытия транзакции при отправке команды QUIT.

NOOP - Для проверки состояния соединения с РОРЗ- сервером используется команда NOOP. При активном соединении ответом на нее будет положительный индикатор "+ОК":

RSET - Для отката транзакции внутри сессии используется команда RSET (без аргументов). Если пользователь случайно пометил на удаление какие-либо сообщения, он может убрать эти пометки, отправив эту команду:

TOP msg n - По этой команде пользователь может получить "n" первых строк сообщения с номером "msg". РОРЗ- сервер по запросу клиента отправляет заголовок сообщения, затем пустую строку, затем требуемое количество строк сообщения (если количество строк в сообщении меньше указанного в параметре "n", пользователю передается все сообщение).

QUIT - К командам состояния AUTHORIZATION может относиться команда закрытия РОРЗ- сессии - QUIT, если она была отправлена в режиме AUTHORIZATION (например, при вводе неправильного пароля или идентификатора пользователя):
Эта команда отправляется без аргументов и всегда имеет единственный ответ "+ОК".

Как видно из описания список команд не велик. В сборку я включил всего 3 команды: LIST(список писем), RETR(чтение письма), DELE(удаление письма). Все другие команды игнорируются, но вы можете сами дополнить мой пример, для этого и выкладываю исходный код сборки:

01.using System;

02.using Microsoft.SqlServer.Server;

03.using System.Collections;

04.using System.Net.Sockets;

05.using System.Text;

06.

07.public class POP3CLR

08.{

09. [SqlFunction(FillRowMethodName = "FillRow"

10. , TableDefinition = "SERVER: nvarchar(max)")

11. ]

12.

13. public static IEnumerable POP3Command(string POP3Server, string Port, string User, string Pass, string Command)

14. {

15.

16. ArrayList rows = new ArrayList();

17. if (Command.Length > 3)

18. {

19. if (Command != "LIST" && Command.Substring(0, 4) != "RETR" && Command.Substring(0, 4) != "DELE")

20. {

21. Command = "HELP";

22. }

23. }

24. else

25. {

26. Command = "HELP";

27. }

28.

29. if (Command == "HELP")

30. {

31.

32. rows.Add(new object[] {"LIST-список информационных "+

33. "строк обо всех сообщениях в данном почтовом ящике. "+

34. "Сообщения, помеченные на удаление не фигурируют в этом списке." });

35. rows.Add(new object[] {

"RETR msg-Используется для передачи клиенту запрашиваемого сообщения. "

+

36.

"Аргумент команды - номер сообщения. Если запрашиваемого сообщения нет, "

+

37. "возвращается отрицательный индикатор '-ERR'." });

38. rows.Add(new object[] {"DELE msg-Аргумент команды- номер сообщения. "+

39. "Сообщения, помеченные на удаление, реально удаляются только "+

40. "после закрытия транзакции при отправке команды QUIT." });

41. return rows;

42. }

43.

44. TcpClient tcpClient = new TcpClient();

45. tcpClient.Connect(POP3Server, Convert.ToInt32(Port));

46. NetworkStream netStream = tcpClient.GetStream();

47. System.IO.StreamReader strReader = new System.IO.StreamReader(netStream);

48.

49. if (tcpClient.Connected)

50. {

51.

52. byte[] WriteBuffer = new byte[1024];

53. ASCIIEncoding enc = new System.Text.ASCIIEncoding();

54. WriteBuffer = enc.GetBytes("USER " + User + "\r\n");

55. netStream.Write(WriteBuffer, 0, WriteBuffer.Length);

56. rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });

57. WriteBuffer = enc.GetBytes("PASS " + Pass + "\r\n");

58. netStream.Write(WriteBuffer, 0, WriteBuffer.Length);

59. rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });

60. WriteBuffer = enc.GetBytes(Command + "\r\n");

61. netStream.Write(WriteBuffer, 0, WriteBuffer.Length);

62. if (Command.Substring(0, 4) == "DELE")

63. {

64. rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });

65. }

66. else

67. {

68. string ListMessage;

69. while (true)

70. {

71. ListMessage = strReader.ReadLine();

72. if (ListMessage == ".")

73. {

74. break;

75. }

76. else

77. {

78. rows.Add(new object[] { ListMessage + "\r\n\r\n" });

79. continue;

80. }

81. }

82. }

83. WriteBuffer = enc.GetBytes("QUIT\r\n");

84. netStream.Write(WriteBuffer, 0, WriteBuffer.Length);

85. rows.Add(new object[] { strReader.ReadLine() + "\r\n\r\n" });

86. }

87. return rows;

88. }

89.

90. public static void FillRow(Object row, out string Server)

91. {

92.

93. object[] xrow = (object[])row;

94. Server = (string)xrow[0];

95.

96. }

97.

98.}

Регистрируем сборку и создаём на её основе функцию:

01.CREATE ASSEMBLY AssemblyPOP3

02.FROM 'C:\CLR\POP3CLR.dll'

03.WITH PERMISSION_SET = UNSAFE

04.GO

05.

06.--Создаём функцию

07.CREATE FUNCTION POP3Command

08.(

09.@POP3Server nvarchar(128),

10.@Port nvarchar(5),

11.@User nvarchar(128),

12.@Pass nvarchar(128),

13.@Command nvarchar(50)

14.)

15.RETURNS TABLE

16.(

17.[SERVER:] NVARCHAR(max)

18.)

19.EXTERNAL NAME AssemblyPOP3.POP3CLR.POP3Command;

Ну и пример работы с этой функцией:

1.SELECT * FROM POP3Command(

2.'pop3.MyServer.ru', 110, 'E-Mail@MyServer.ru', 'MyPassword', 'LIST'

3.)

Ссылки по теме