В этой статье приведен пример практического применения Base64, использования ADODB да и просто полезных штук.
Похожие статьи:
А вот тут результат поиска, можно посмотреть.
Пример и разработка велись на платформе 1С:Предприятие 8.3 (8.3.4.365)
Сервер 1С крутится на MS Windows 2012
Внешняя база на MSSQL 2012 Standart
Подразумевается, что вы знаете:
- что такое 1С и ADODB
- что такое MSSQL и умеете с ним работать/админить (или сможете, если захотите)
- что такое Сериализация
- у вас прямые руки
Интро:
В нашей организации ведется собственная разработка на базе 1С Предприятие 83 (было сначала 82, потом перешли на 83... на месте не стоим...)
Это CRM система для работы с обращениями пользователей продуктов нашей компании.
Таки компания растет, развивается и всё такое и безусловно все пользователи как любые пользователи "косячат" или чего-то не понимают и естественно что сам продукт не без изъянов (каимся), и приходится им обращатся в техподдержку различными способами.
Один из таких способов - писать письмо e-mail.
Письма мы эти обрабатываем с помощью COM объекта OUTLOOK'a и складываем в нашу базу , а вложения писем кладём рядышком в "общую папку" расшареную для пользователя сервера 1С (типа секьюрно)... почему так - "так исторически сложилось"...
Ну так вот, речь пойдет как раз об этих вложениях.
Предыдущий наш вариант - это так называемый SMB. Оно всё работало, у нас многоранговая сеть и всё секьюрно и круто и вообще бы никто не начал чесаться, пока не заметили что объём вложений уже больше 300гигов. И задумались мы, наверное там много мусора, давай сразу удалим всякие медиа и исполняемые файлы и ещё что-нибудь.
Ну классно, давай, получилось всякого хлама гигов на 10. Написали обработку и запустили... и Всё.
Смотрю я на наши папочки и они медленно исчезают... Все без разбору. Холодный пот побежал по моей спине.
Я резко начал завершать процессы, но это не помогло. Ведь я написал хитрую обработку, оно запустилось фоном на сервере и методично всё уничтожало, в итоге довел до того что в панике остановил службу. Но это тоже не помогло, уж я не знаю почему, но оно всё равно всё удаляло, как будто в памяти запустился процесс и висел. В итоге - reboot сервера.
Фу... Осматриваем пепелище... из 4 лет архивов уцелел только последний...
Всё, надо с этим что-то дешлать! Тем более мысли уже были, но как всегда не было времени.. или лень.
Вот несколько вариантов как можно хранить эти самые доп.файлы для нашей базы. Эти варианты первые пришли на ум, у каждого свои плюсы и минусы. По мне самым выигрышным показался вариант с внейшней базой данных.
Рассматривая эти варианты, немного идеализируем среду. Пусть будет что у нас надежные raid ы , хорошая и быстрая сеть.
Варианты решения:
| SMB (файловый вариант) |
FTP (не пробовал) |
Хранить внутри Базы (не пробовал) |
Внешняя БД(Наш вариант) | |
| + | - быстро - просто |
- возможно быстро - чуть более безопаснее - возможно надежнее (смотря как организовано хранилище) |
- надежно - безопасно - доступ рулится самой 1С - проще |
- безопасно (доступ на уровне SQL) - надежно (можно делать быстрый бэкап всех вложений, или быстро перенести на другой сервер) |
| - | - не безопасно - не надежно (опять же это может быть сетевое хранилище) |
- чуть по сложнее | -"раздувает" базу, как следствие база начинает работать (для файлового варианта, для серверного возможно нет разницы) | - сложно |
p.s.
Потери восстановили с помощью обычных утилилит восстановления удаленных файлов, но не всё...
Решение:
И так, выбираем хранить файлы в MSSQL.
Основное отличие этой статьи и решения от похожей которую я привел выше - мы не будем использовать временные temp файлы. Наверное и всё. Ну еще может быть мы не пользуем ADODB.Command...
Во вложении к этой статье помещена специальная мини конфа с полезным и я надеюсь понятным кодом внутри, которую можно скачать и расковырять как вам нужно.
Структура от нашей боевой отличается, здесь приведен упрощенный вариант.
Структура в 1С:
Документ "Письмо"
- Номер - Тип Строка(9)
РегистрСведений "Вложения"
- Измерения:
- Письмо - Тип ДокументСсылка.Письмо
- FileUID - Тип УникальныйИдентификатор
- Ресурсы:
- ИмяФайла - Тип Строка
- Размер (в байтах) - Тип Число
- Расширение - Тип Строка
Методом проб и ошибок пришел к определенной схеме таблицы в которой буду хранить данные на MSSQL сервере
//Table Name: Attachs
//FileUID uniqueidentifier
//MailUID uniqueidentifier
//MailNumber VarChar(9)
//MailDate DateTime
//FileName VarChar(255)
//FileSize int //bites не более 20 Мб 20971520 байт
//FileDATA VarBinary(MAX)
Почему тип = uniqueidentifier. Сначала был типа char(36), в целом так и есть, Уникальный Идентификатор полученый методом Новый УникальныйИдентификатор(), при приведении к строке имеет длину 36 символов. Но, мы будем пользовать специальный тип и приводить к нему передаваемые значения наших UIDов, потому что мы будем индексировать нашу таблицу по этип двум полям. Но можно не индексировать, смотря какое количество записей планируется хранить. У нас это >2 000 000 записей, поэтому нам это даже нужно.
Для быстрого создания, выполняем такой скрипт в консольке нашего MS SQL сервера
use %YOUR_dBASE_NAME%
go
create table Attachs
(FileUID uniqueidentifier
,MailUID uniqueidentifier
,MailNumber VarChar(9)
,MailDate DateTime
,FileName VarChar(255)
,FileSize int
,FileDATA VarBinary(MAX))
Самый интерес для нас представляет поле FileDATA с типом VARBINARY(MAX). Это тип именно Двоичные Данные, как в 1С. Почему так - потому что я подумал, мало ли что нам понадобиться или вдруг мы захотим читать данные из других приложений, а этот тип универсальный. По идее можно хранить VARCHAR(MAX) и передавать туда строку типа Base64 полученную методом Base64Строка(Значение). Но это не универсальное решение(я так думаю, убедите меня в обратном!), потому что эта ерунда будет получена в 1С... а я привык, что придумано в 1С - то подходит только для 1С (возможно я опять заблуждаюсь... Но тогда какого хрена нельзя через ADODB.RecordSet получить двоичные данные, а только через временные temp файлы и другие костыли...)
И так, новая задача. Как передавать двоичные данные в MSSQL да еще и через COM объект?
Ладно, запросы мы умеем выполнять, и даже что-то инсертить. Круто, но нам надо инсертить Двоичные Данные а не Число или Строку.
В общем становится понятно, что надо передавать что-то сериализууемое... как раз это и будет Base64 строка, полученная методом Base64Строка(Значение). Только осталось найти способ, как в MSSQL запросе всё это переварить и заинсертить то что нужно и потом еще и получить.
Не скрою, сначала я долго и упорно гуглил, вообще сразу попадаются варианты кода и даже рабочие, например такой (очень популярный):
-- Encode the string "TestData" in Base64 to get "VGVzdERhdGE="
SELECT
CAST(N'' AS XML).value(
'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
, 'VARCHAR(MAX)'
) Base64Encoding
FROM (
SELECT CAST('TestData' AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;
-- Decode the Base64-encoded string "VGVzdERhdGE=" to get back "TestData"
SELECT
CAST(
CAST(N'' AS XML).value(
'xs:base64Binary("VGVzdERhdGE=")'
, 'VARBINARY(MAX)'
)
AS VARCHAR(MAX)
) ASCIIEncoding
;
Но, после некоторых тестов, я столкнулся с неведомой проблемой. Наблюдалось невозможность чтения помещенных ранее данных, скрипт отваливался с таймаутом. В консоле сервера были такие же проблемы, и я заподозрил что проблемы именно в самом скрипте, а не сервере или данных как я подумал сразу. Пришлось курить мануалы, помогло вот это:
Метод Value() типа данных XML t-sql
Вся загвостка оказалась именно в функциях XQuery подзапроса, функция могла работать только со скалярными переменными, и по каким-то неведомым причинам оно воспринимало что в запросе может быть больше 1 значения... Но это только мои догадки.
Для чтения данных пришлось модернизировать функцию, и теперь она 100% рабочая всегда.
Для insert'а данных всё оказалось немного проще.
- Запись данных (insert):
Пример SQL запроса для помещения данных в БД
INSERT INTO %tableName%
(FileUID,MailUID,MailDate,MailNumber,FileName,FileSize,FileData)
VALUES
(CAST('%FileUID%' AS uniqueidentifier)
,CAST('%MailUID%' AS uniqueidentifier)
,CAST('%MailDate%' AS datetime)
,'%MailNumber%'
,'%FileName%'
,%FileSize%
,CAST(N'' AS XML).value(
'xs:base64Binary(""%FileData%"")'
, 'VARBINARY(MAX)'
)
)
- Чтение данных (select):
Рабочий запрос. Если данные не найдены, возвращается 0, это нужно так же обрабатывать.
declare @_bin varbinary(max), @_xml xml, @MailUID char(36), @FileUID char(36)
set @_xml =''
set @MailUID = '%MailUID%'
set @FileUID = '%FileUID%'
set @_bin =
(SELECT top 1
FileDATA AS bin
FROM Attachs
where MailUID = CAST(@MailUID AS uniqueidentifier)
AND FileUID = CAST(@FileUID AS uniqueidentifier))
IF (@_bin is null)
BEGIN
SELECT 0 AS FileData
END
ELSE
BEGIN
SELECT @_xml.value('xs:base64Binary(xs:hexBinary(sql:variable("@_bin")))'
, 'VARCHAR(MAX)'
) FileData
END
Итого. Имея эти штуки в руках, можем применить их в деле.
В нашем случае большенство вложений это картинки, сканы и прочая изобразительная инфа. Поэтому у нас есть полезная область формы где можно посмотреть превью файла. На управляемой форме реквизит Превью с типом Строка и форматом отображения - ПолеHTMLДокумента.
В этот реквизит мы помещаем полезный HTML код, которые в зависимости от типа вложения отобразит либо PDF либо Изображение.
АдресВременногоХранилища - это переменная, в которая в себе реально хранит адрес временного хранилища, полученный методом ПоместитьВоВременноеХранилище(ДвоичныеДанные,Новый УникальныйИдентификатор)
Где ДвоичныеДанные - Это переменная с типом ДвоичныеДанные, которые получены с помощью функции глобального контекста Base64Значение(Результат)
Где в свою очередь Результат - это данные полученные с помощью ADODB
Выглядит примерно так:
//Расширение = .pdf
//Расширение = .jpg
htmlТекст = "
|< !DOCTYPE HTML >
|< html >
|< head >
|< meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"" >
|< style type=""text/css"" >
|html, body {
|margin: 0px;
|padding: 0px;
|width: 100%;
|height: 100%;
|}
|#footer {
|width: 100%;
|height: 100%;
|}
|#pdfEMBED {
|width: 100%;
|height: 100%;
|}
|< /style >
|< /head >
|< body >
|< div id=""footer"" >%previewTAG%< /div >
|< /body >
|< /html >";
Если Расширение = ".pdf" Тогда
previewTAG = "< EMBED id=""pdfEMBED"" type=""application/pdf"" src=""%ПутьКФайлу%"" >< /EMBED >";
ИначеЕсли Расширение = ".jpg" Тогда
previewTAG = "< img src='%ПутьКФайлу%' class='turn'/ >";
Иначе
previewTAG = "< img src='%ПутьКФайлу%' class='turn'/ >";
КонецЕсли;
previewTAG = СтрЗаменить(previewTAG,"%ПутьКФайлу%",АдресВременногоХранилища);
htmlТекст = СтрЗаменить(htmlТекст,"%previewTAG%",previewTAG);
Превью = htmlТекст;
*Обращаю внимание, что здесь тэги с лишними пробелами, для красивого отображения, иначе всё "едет"
Основные процедуры, где ADOconnect - COMОбъект, полученые функцией ПолучитьКоннектКБД()
//Возвращает COMОбъект коннекта
//В случае ОТКАЗ = Истина, возвращается строка с описанием ошибки
Функция ПолучитьКоннектКБД(Отказ = Ложь) Экспорт
DatabaseName = Константы.Вложения_ИмяБазыДанных.Получить();
ServerName = Константы.Вложения_ИмяСервера.Получить();
IntegratedSecurity = Константы.Вложения_IntegratedSecurity.Получить();
UserID = Константы.Вложения_ИмяПользователя.Получить();
Password = Константы.Вложения_ПарольПользователя.Получить();
Попытка
//http://technet.microsoft.com/en-us/library/aa905872(v=sql.80).aspx
cn = Новый COMОбъект("ADODB.Connection");
cn.Provider = "sqloledb";
cn.Properties("Data Source").Value = ServerName;
cn.Properties("Initial Catalog").Value = DatabaseName;
Если НЕ ПустаяСтрока(IntegratedSecurity) Тогда
//Если параметр пустой, то будет использоваться обычная аутентификация по логину паролю
//' Windows NT authentication.
cn.Properties("Integrated Security").Value = IntegratedSecurity; //
Иначе
//Обычная мсскл аутентификация по логину паролю
cn.Properties("User ID").Value = UserID;
cn.Properties("Password").Value = Password;
КонецЕсли;
cn.Open();
Возврат cn;
Исключение
//Ошибка
ОписаниеОшибки = ОписаниеОшибки();
Отказ = Истина;
Возврат ОписаниеОшибки;
КонецПопытки;
КонецФункции
//Возвращает Base64 строку
//Параметры:
// ПараметрыДанных - Структура -
// ADOconnect - COMОбъект
// Отказ - Булево -
Функция ПолучитьДанныеИзБД(ПараметрыДанных,ADOconnect,Отказ = Ложь) Экспорт
tableName = "Attachs";
selectString =
"declare @_bin varbinary(max), @_xml xml, @MailUID char(36), @FileUID char(36)
|set @_xml =''
|set @MailUID = '%MailUID%'
|set @FileUID = '%FileUID%'
|
|set @_bin =
| (SELECT top 1
| FileDATA AS bin
|FROM Attachs
|where MailUID = CAST(@MailUID AS uniqueidentifier)
| AND FileUID = CAST(@FileUID AS uniqueidentifier))
|
|IF (@_bin is null)
| BEGIN
| SELECT 0 AS FileData
| END
|ELSE
| BEGIN
| SELECT @_xml.value(
| 'xs:base64Binary(xs:hexBinary(sql:variable(""@_bin"")))'
| , 'VARCHAR(MAX)'
| ) FileData
| END";
selectString = СтрЗаменить(selectString,"%tableName%" ,tableName);
selectString = СтрЗаменить(selectString,"%FileUID%" ,ПараметрыДанных.FileUID);
selectString = СтрЗаменить(selectString,"%MailUID%" ,ПараметрыДанных.MailUID);
rs = Новый COMОбъект("ADODB.Recordset");
Попытка
rs.Open(selectString, ADOconnect);
Исключение
ОписаниеОшибки = ОписаниеОшибки();
Отказ = Истина;
Возврат ОписаниеОшибки
КонецПопытки;
Пока rs.EOF = 0 Цикл //Ложь
Base64String = rs.Fields("FileData").Value;
Если Base64String = 0 Тогда
Отказ = Истина;
Возврат "Ошибка получения данных: Данные не найдены";
КонецЕсли;
rs.MoveNext();
КонецЦикла;
Возврат Base64String;
КонецФункции
//Поместить данные в БД
//Параметры:
// ПараметрыДанных - Структура - Структура содержащая парамтеры с ключами идентичными именам колонок в тиблице Attachs БД
// ADOconnect - COMОбъект
// Отказ - Булево
Процедура ПоместитьДанныеВБД(ПараметрыДанных,ADOconnect,Отказ = Ложь) Экспорт
tableName = "Attachs";
insertString = "INSERT INTO %tableName%
|(FileUID,MailUID,MailDate,MailNumber,FileName,FileSize,FileData)
|VALUES
//base64String to VARBINARY
|(CAST('%FileUID%' AS uniqueidentifier),CAST('%MailUID%' AS uniqueidentifier),CAST('%MailDate%' AS datetime),'%MailNumber%','%FileName%',%FileSize%,CAST(N'' AS XML).value('xs:base64Binary(""%FileData%"")', 'VARBINARY(MAX)'))";
insertString = СтрЗаменить(insertString,"%tableName%" ,tableName);
insertString = СтрЗаменить(insertString,"%FileUID%" ,ПараметрыДанных.FileUID);
insertString = СтрЗаменить(insertString,"%MailUID%" ,ПараметрыДанных.MailUID);
insertString = СтрЗаменить(insertString,"%MailDate%" ,Формат(ПараметрыДанных.MailDate,"ДФ=yyyy-MM-ddTЧЧ:мм:сс.000"));
insertString = СтрЗаменить(insertString,"%MailNumber%" ,ФорматированиеСтроки(ПараметрыДанных.MailNumber));
insertString = СтрЗаменить(insertString,"%FileName%" ,ФорматированиеСтроки(ПараметрыДанных.FileName));
insertString = СтрЗаменить(insertString,"%FileSize%" ,Формат(ПараметрыДанных.FileSize,"ЧГ="));
insertString = СтрЗаменить(insertString,"%FileData%" ,ПараметрыДанных.FileData);
//Сразу выполняем INSERT запрос
cd = Новый COMObject("ADODB.Command");
cd.ActiveConnection = ADOconnect;
cd.CommandText = insertString;
cd.CommandType = 1;
Попытка
cd.Execute();
Исключение
ОписаниеОшибки = ОписаниеОшибки();
Отказ = Истина;
Сообщить(ОписаниеОшибки);
КонецПопытки;
КонецПроцедуры
Вот и всё, остальное можно увидеть внутри мини конфигурации, лепил специально для статьи.
Если решите использовать этот же вариант хранения файлов, то вам придется написать свою обработку для переноса вложений в базу. У меня есть свои обработки, но к сожалению они заточены под нашу конфигурацию. Да и так бы было совсем не интересно.
Прошу критику и предложения в комменты или личку.
