gifts2017

Хранение файлов во внешней базе данных MSSQL.

Опубликовал artur rakhmatulin (нормальный такой) в раздел Программирование - Практика программирования

1С Двоичные Данные <=> Base64 <=> SQL.
Как с помощью ADODB и MSSQL решить проблему хранения дополнительных файлов данных используемых в 1С.

В этой статье приведен пример практического применения 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... 

Во вложении к этой статье помещена специальная мини конфа с полезным и я надеюсь понятным кодом внутри, которую можно скачать и расковырять как вам нужно.

Структура от нашей боевой отличается, здесь приведен упрощенный вариант.

Структура в :

Документ "Письмо"

  • Номер - Тип Строка(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 запросе всё это переварить и заинсертить то что нужно и потом еще и получить.

Не скрою, сначала я долго и упорно гуглил, вообще сразу попадаются варианты кода и даже рабочие, например такой (очень популярный):

base64-encoding-in-sql-server 

-- 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
;

Но, после некоторых тестов, я столкнулся с неведомой проблемой. Наблюдалось невозможность чтения помещенных ранее данных, скрипт отваливался с таймаутом. В консоле сервера были такие же проблемы, и я заподозрил что проблемы именно в самом скрипте, а не сервере или данных как я подумал сразу. Пришлось курить мануалы, помогло вот это:

Справочник по XQuery

Типы данных xquery

Метод sql:variable() 

Метод 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();
	Исключение
		ОписаниеОшибки = ОписаниеОшибки();
		Отказ = Истина;
		
		Сообщить(ОписаниеОшибки);
	КонецПопытки;
КонецПроцедуры

Вот и всё, остальное можно увидеть внутри мини конфигурации, лепил специально для статьи.

Если решите использовать этот же вариант хранения файлов, то вам придется написать свою обработку для переноса вложений в базу. У меня есть свои обработки, но к сожалению они заточены под нашу конфигурацию. Да и так бы было совсем не интересно. 

 

Прошу критику и предложения в комменты или личку. 

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
Конфигурация: Хранение файлов во внешней MSSQL БД
.cf 93,50Kb
09.06.14
43
.cf 93,50Kb 43 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Головаченко Дима (Smaylukk) 11.06.14 12:20
Собственно, а почему не сделали аналогичную конфигурацию 1С на базе MSSQL, а с рабочей базой настроить обмен через COM, веб-сервисы, обмен через файлы. Это все же свое, родное.
Ну а в целом за публикацию плюс
2. kiruha Дронов (kiruha) 11.06.14 13:07
(1) Smaylukk,
Таскать большие файлы из 1С не гуд
Com это тормоз при инициации и нужна лицензия
веб сервис - дополнительная трансформация большого файла - не гуд
+ Поддерживать простую скульную базу проще, чему кучу таблиц 1С
+ Оптимизировать проще
+ Может быть очень большой архив (Тбайты)

Также склоняюсь к внешнему хранению. Но интересны и альтернативы
3. Головаченко Дима (Smaylukk) 11.06.14 17:47
(2) kiruha, спасибо, я вас понял.
4. aspirator 23 (aspirator23) 12.06.14 15:24
Возможно для этого решения неактуально. А какая скорость записи/чтения больших файлов?
Рассматривался вариант FileStream в MSSQL для хранения файлов?
5. artur rakhmatulin (нормальный такой) 12.06.14 22:07
(4) aspirator23, Да, рассматривался.
Но я про него мало что знаю, якобы он позволяет получать доступ как к обычным файлам в папке, но при этом всё это хранится в БД...
Но вот я решил, что пусть будет такой вариант... обычный varbinary.

Скорость чуть снизилась, при сравнении с обычным чтением файлов по сети. Всё же эта конвертация в base64 влияет.
На скорость тут уже будет влиять несколько факторов... как быстры ваши диски, сеть да и сами сервера.
6. Александр Черных (begemotoff35) 10.12.14 10:33
Очень медленно читаются файлы из внешней базы.
Сохранил картинку 6Мб. Читается секунды три. (((
Из внутреннего хранилища гораздо быстрее
7. artur rakhmatulin (нормальный такой) 10.12.14 11:19
(6) begemotoff35, ну да, есть такое. об этом предупреждал :)
сильно зависит от быстродействия скуль сервера и сети.
8. Евгений Олейник (Megis) 02.02.15 13:38
Понравилось. Внедрил у себя. Только немного переписал функцию получения из БД. Сейчас получаю все файлы разом, и уже потом обрабатываю выборку.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа