Організація файлового сховища на базі MS SQL Server з доступом із 1С

На даний момент у конфігураціях 1С найбільш поширені два способи зберігання прикріплених файлів:

  1. Безпосередньо у базі 1С
  2. У файловому сховищі.

Про достоїнства та недоліки цих способів розказано докладно.

Існує і третій метод - зберігання файлів у зовнішній основі, наприклад, MS SQL Server. До версії MS SQL Server 2008 цей спосіб особливо відрізнявся від першого способу, тобто. зберігання безпосередньо у базі даних. Але починаючи з MS SQL Server 2008 розробникам було запропоновано нову технологію зберігання неструктурованих даних FileStream.

Якщо коротко - технологія FileStream надає особливий спосіб зберігання та доступу до файлів:

  • На рівні СУБД файл міститься у певне поле таблиці, але зберігається у своїй у спеціальній файлової групі.
  • Кожному запису відповідає фізичний файл на диску.
  • Доступ до файлів забезпечує MS SQL Server, тобто. підтримується транзакційне читання/запис та обмеження доступу на основі прав доступу користувача СУБД, а не Windows.
  • Файли зберігаються у спеціальній файловій групі, тому можливе розташування їх у окремому фізичному диску.
  • Файлову групу FileStream можна включати в бекап бази.
  • Доступ до файлів можливий як через T-SQL-запити, так і потоковим доступом (API файлової системи win32).
  • При потоковому доступі пам'ять MS SQL Server не використовується, відсутнє високе навантаження на сервер, для кешування файлів використовується системний кеш Windows.

Приємним доповненням є повноцінна підтримка FileStream безкоштовним SQL Express, при цьому немає жодних обмежень на розмір файлової групи FileStream.

Існують і ряднедоліків технології FileStream:

  • На диску всі файли розміщуються в одному каталозі, що при великій кількості файлів призводить до проблем зі швидкістю доступу. Microsoft дає кілька рекомендацій для використання томів сховища FileStream.
  • З дрібними файлами (менше 1 Мб), особливо у потоковому доступі, забезпечується нижча швидкість доступу, ніж із зберіганні файлів безпосередньо у базі. Порівняння продуктивності різних технологій доступу до файлів
  • Для реалізації потокового доступу до файлів потрібно написання спеціальної клієнтської програми, у разі 1С - створення зовнішньої компоненти.

Як правило, файли, що прикріплюються в 1С мають різний розмір і стверджувати, що більшість будуть мати розмір більше 1 Мб або навпаки, було б помилково. Тому було вирішено створити базу MS SQL, яка зберігає файли, як у базі, і у файлової групі FileStream.

База MS SQL складається з трьох таблиць:

1) innerStorage для зберігання файлів безпосередньо в базі,

2) outerStorage для зовнішнього зберігання файлів FileStream,

3) locationStorage - визначення в якій таблиці зберігається файл.

Скрипт для створення бази з усіма таблицями та процедурами, що зберігаються, доданий в архіві.

Як ідентифікатор файлу виступає ГУІД, в MS SQL він перетворюється на тип uniqueidentifier. Для передачі параметрів між системами використовується перетворення до рядка.

У базі 1С вказується прикордонний розмір файла: якщо файл, що міститься, менше зазначеного розміру, то файл поміщається в таблицю innerStorage, більше - outerStorage. Одночасно робиться запис у таблицю locationStorage про розташування файлу.

При отриманні файлу в 1С спочатку робиться запит про розташування файлу, а потімзапит до відповідної таблиці.

При оновленні файлу також спочатку робиться запит про розташування файлу, обчислюється розмір визначення нового типу розташування. Якщо тип розташування змінюється, старий файл видаляється зі старого місця і додається до нового.

Запис файлів у базу даних досить тривіальна завдання. Описів рішень існує безліч, наприклад, //infostart.ru/public/283336/ Описані методи кодування/декодування з двійкових даних Base64 успішно працюють. Для невеликих файлів швидкість роботи цілком задовільна.

З особливостей моєї реалізації цієї підсистеми можна назвати:

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

В принципі, можна було все реалізувати більш традиційним способом через створення ADO з'єднання, але підхід, коли база надає все необхідне АПИ для роботи є більш правильним. Використання зовнішніх джерел 1С дозволило зробити код 1С ще мінімальним:

З організацією зберігання великих файлів з використанням технології FileStream все виявилося дещо складнішим. Безпосередньо створення бази та таблиці для зберігання файлів не викликає жодних питань. Усі докладно описано. Безпосередньо доступ до файлів можливий як через Transact-SQL-запити, так і використання API файлової системи.

У разі Transact-SQL-запитів рішення було б практично ідентичним першому способу - зміни лише на внутрішньому рівні зберігання файлів. При цьому на MS SQL Server лягло б високе і не властиве йому навантаження із вилучення, кодування/декодування великих файлів.

Для потокового доступу до файлів на рівні клієнтських програм існує dotNet клас SqlFileStream. Приклади його використання різними мовами є в надлишку.

Тільки одна проблема – 1С не вміє працювати безпосередньо з dotNet класами. Тому довелося реалізувати COM-об'єкт для роботи з класом SqlFileStream із 1С. В архів додані 32-розрядна версія зовнішньої компоненти SqlStreamAccess.dll і 64-розрядна SqlStreamAccess64.dll. Реєстрація Com-об'єкту здійснюється шляхом виклику regasm.exe потрібної розрядності під повними правами.

Реалізований COM-об'єкт має одну властивість ConnString і два методи: InsertFile та SelectFile, які дозволяють додавати, оновлювати та отримувати файли.

Приклад роботи з COM-об'єктом SqlStreamAccess:

Як ідентифікатор файлу також виступає ГУІД, наведений до рядка. Як ім'я файлу вказується повний шлях до файлу, який має бути поміщений/отриманий з бази. Звичайно, користувач Windows, під яким запущено сервіс 1С, повинен мати повний доступ до цього файлу. Також цей користувач повинен мати права доступу до бази MS SQL за допомогою Windows-аутентифікації.

Окремого пояснення вимагає третій параметр методу InsertFile. Він має цілий тип даних і може приймати три значення:

1 - відбувається додавання файлу

2 – відбувається оновлення файлу без зміни типу сховища

3 - відбувається оновлення файлу зі зміною типу сховища (видаляється з innerStorage, додає до OuterStorage).

Приклади роботи із зовнішнім сховищем файлів можна переглянути в прикладеній в архіві конфігурації 1С. Вбудована обробка "РоботаЗовнішнімСховищемФайлів" надає всі високорівні методи для отримання, додавання, оновлення та видалення файлів.