Читання пошти (POP3) в SQL Server 2005/2008, Інші СУБД, Бази даних, статті

Продовжую тему безкорисних прикладів 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 (наприклад, при введенні неправильного пароля або ідентифікатора користувача):
Ця команда відправляється без аргументів і завжди має єдиний відповідь “+ OK”.


Як видно з опису список команд не великий. У збірку я включив всього 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 +
);
55.            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
56.            rows.Add(new object[] { strReader.ReadLine() +
});
57.            WriteBuffer = enc.GetBytes(“PASS “ + Pass +
);
58.            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
59.            rows.Add(new object[] { strReader.ReadLine() +
});
60.            WriteBuffer = enc.GetBytes(Command +
);
61.            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
62.            if (Command.Substring(0, 4) == “DELE”)
63.            {
64.                rows.Add(new object[] { strReader.ReadLine() +
});
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 +
});
79.                        continue;
80.                    }
81.                }
82.            }
83.            WriteBuffer = enc.GetBytes(“QUIT
);
84.            netStream.Write(WriteBuffer, 0, WriteBuffer.Length);
85.            rows.Add(new object[] { strReader.ReadLine() +
});
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:CLRPOP3CLR.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.)

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*