Хранение графики в SQL-сервере

В инете между разными группами программеров постоянно ведутся различные религиозные войны. Религиозные - потому, что участвующих в них программеров уже ВСЕ РАВНО ни в чем переубедить не получится никогда.

Одна из таких бесконечных религиозных войн - месторасположение хранилища бинарных файлов. Участником таких войн - еще со времен появления SQL 6,5 - являюсь и я. Мое кредо - всегда! Всегда и все - только в базе! Долой наследников дискеток - файловые системы NTFS, FAT32, HPFS - ну и все остальные, не упомянутые тоже !!!

Все мои проги, описанные на этом сайте, хранят все бинарники только в базе. Например, ProjectExplorer (моя альтернатива TEAM Foundation Server). Все прочие проги, не упомянутые на моем сайте, но которые делал я - например различные CMS электронных магазинов, которыми я занимался несколько лет - также хранят все бинарники в базе.

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

Для начала сошлемся на "авторитные" источники. Авторитетные взято в кавычки - понятно почему - ангажированность MSDN и микрософтовских коперфильдов типо Экспозито - вряд-ли вызывает у серьезных людей какие-то сомнения, но тем не менее для начала две цитаты.

Экспозито. ASP NET 2. Углубленное изучение. Стр 349.

Используйте файловую систему, когда имеете дело с изображениями, размер которых превышает 1МБ, а также когда изображений много, несколько сот тысяч и более. В граничном решении оба решения равно допустимы. SQL обеспечивает очень быстрое чтение данных с диска и имеет прекрасный механизм кеширования, но протокол TDS (Tabular Data Stream - поток табличных данных) явно не рассчитан на передачу больших BLOB-обьектов.

Ну и вторая цитата. SQL 2005. Экзамен 70-431. Стр 121.

Чтобы найти строку в таблице, содержащей 2,5 миллиона строк, SQL Server должен просмотреть всего три страницы данных. И лишь когда в таблице станет более 300 миллионов строк, SQL Server придется просматривать четыре страницы для поиска нужной строки.

Я лично полагаю, что возможости SQL еще выше, чем в двух приведенных цитатах и не вижу необходимости вообще использовать файловую систему ни в каких случаях, по следующим причинам:

• Не могу себе вообще представить каталог в файловой системе из 300 миллионов файлов. Если даже такой каталог создать возможно, и файловая система не сдохнет от таких обьемов - выборка одного файла из нее будет продолжать целую вечность - а не мгновения, как в SQL. Ибо работа с такими обьемами (не говоря уже о меньших) - это и есть предназначение SQL сервера.
• В большинстве случаев SQL-серверу вообще не придется обращаться к диску - ибо под размеры буферов в SQL отводится весь доступный обьем ОЗУ (по умолчанию). Это размер при желании можно гибко регулировать. Никакого сравнения по гибкости и мощности с механизмом кеширования IIS. Ну не говоря уж о наследниках дискеток - разве для чтения из NTFS мы можем хоть как-то задать например 1Гигабайтный буфер в OЗУ? Сравнивать мехнаизмы кеширования голой файловой системы (и даже с надстройкой в виде буферизации IIS) - с механизмами кеширования SQL просто смешно. Единственный практический минус работы с такими большими кешами - ПЕРВЫЙ ЗАПРОС ИДЕТ ВСЕГДА МЕДЛЕННО (хотя все равно несравненно быстрее чем из файловой системы). Потом все просто летает. Но ПЕРВЫЙ запрос в SQL может и в 10 раз (а может и в 100 раз) медленнее пройти чем последующие.
• От указанных в букварике базовых показателей производительности SQL мы можем уйти ЕЩЕ ВЫШЕ. А что нам предлагают наследники дискеток? RAID? Но как им управлять? Номерочками - RAID5 - RAID10? Механизмы секционирования таблиц в SQL просто смешно сравнивать с технологиями чередования например или зеркалирования NTFS.
• Что нам мешает мультипоточную подкачку больших файлов организовать из секционированных таблиц и делать Response.Write в поток браузера хоть через сто байт (ну примерно аналогично пиринговым сетям)? А где аналогичные механизмы (хоть сколько нибудь похожие на это - не только в голой NTFS, но даже с надстройкой в виде IIS)? Кстати мы совсем не упомянули УПРЕЖДАЮЩЕЕ чтение оптимизатора SQL, который предвосхищая следующие запросы к данным - предварительно их считывает в свои буфера в все свободное время ПРО ЗАПАС.
• То, что сказал Экспозито насчет маркеров в несколько байт при передаче потоковых данных SQL - я не считаю необходимым вообще принимать во внимание. Ну какой размер этих маркеров - 0,05% ? Или даже меньше. Не вижу в этом вообще аргумента.
• Это все касается пока производительности. Теперь посмотрим на другие не менее важные характеристики SQL. Прежде всего для файловой системы - а учет всех файлов, понятное дело - все равно в базе - НИКАК НЕ ВОЗМОЖНО ОБЕСПЕЧИТЬ ЦЕЛОСТНОСТЬ ДАННЫХ.
• Наконец, реализация механизма доступа к файловой системе. Что нам предлагает убогая NTFS? Разграничение по правам доступа (а они у на без имперсонализации всегда одни - от имени учетной записи ASP.NET) - да плюс параметр в конфиге GET/POST на некие учетные записи юзеров. Это все. Это конечно даже обсуждать несерьезно. Какое по этой позиции может быть сравнение с теми способами доступа, которые мы можем реализовать САМИ в своем коде при доступе к записям SQL.

Список этот бесконечный. Любая характеристика SQL в разы провосходит просто виндузню. Например защищенность файлов в бестолковой шифрованной файловой системе EFS и зашифрованные столбцы в SQL. Пробовали сравнить удобство управления ключами шифрования, например - в том и другом случае?

Единственная ложка дегтя, которая меня огорчает - бестолковость MS. И отсутствие видения целостной концепции - куда движется их виндузня. Поясню конкретнее - они например уже догадались отображать весь WEB-конфиг в виде таблицы, но так и не догадались чтобы IIS мог вычитывать откомпилированные DLL непосредственно из SQL. Хотя предусмотрели все-все-все для хранения сборок внутри SQL. И в самом движке ASP2 предусмотрели VirtualPathProvider, который мог бы вычитывать библиотеки из SQL. Но не уложили нормальный оттестированный класс в NET и не подключили его грамотно к IIS. Такая бестолковость - в данном случае полнейшее отсутствие интеграции IIS и SQL вызывает отвращение и жалость к кампании, которая скупила и доработала так много замечательных продуктов - но так и не сумела их соединить в ЕДИНЫЙ комплекс.

Еще одна огорчительность этой бестолковой компании - отсутствие работы с SQL - как местом хранения исходных текстов. Хотя куча их же продуктов прекрасно умеет это делать - например TEAM Foundation Server, но VS2005 умеет хранить свои проекты кроме файловой системы - лишь на FTP и во Front Page Extension. Обидно, блин. ТЕАМ великолепно хранит исходники в SQL, а VS2005 не может их там хранить. В SQL есть все для хранения там бинарных сборок - но опять же бестолковая VS2005 не умеет уложить откомпилированный сайт в SQL. Хотя просто отдельно взятые сборки превосходно туда укладывает. IIS управляется даже таблицами уже - но считать конфиг из SQL не может... Ну как относиться после этого к MS?

Если же пойти еще дальше - то непонятно, почему к исполняемым файлам SQL вообще не докрутить бутовый загрузчик и не обходится вообще БЕЗ гребаной виндузни? Наверное для MS непосильны стандартные идеи, как например у компании ACRONIS, у которой все продукты работают либо непосредственно бутаясь с компактов либо под виндузней. Никогда, наверное, у них не хватит ума докрутить бутовый загрузчик к SQL и сделать как у ACRONIS и тысяч других компаний... Эх, ну почему же дурни не распределяются РАВНОМЕРНО по всем компаниям...

Если пойти еще дальше в анализе бестолковых идей MS - то мое отвращение вызывает в первую очередь попытки нагрузить SQL несвойственными - РАСЧЕТНЫМИ задачами. Например зиповкой/раззиповкой данных. Этим ведь крадется драгоценнейшее время у проуессора, у той же например предподкачки в буфера предсказанных будущих запросов к дискам. Есть и другие тупизмы, которыми MS нагружает SQL. Ну например, введенной в SQL2008 конструкцией Select * from &Table. Вот именно в таких дебильных контрукциях - все преимущества SQL будут подсаживаться просто на нет - и он будет просто тупо превращаться в файловую систему. Без индексов, без предсказаний, без предкомпилированных планов и тд. Такие вот ПОПУЛИСТСКИЕ векторы развития SQL - не в ту сторону - меня огорчают донельзя.

Теперь после этого небольшого теоретического экскурса - рассмотрим пару простейших примеров работы с бинарными данными в SQL.

Надо сказать, что я с момента появления SQL 6,5 старался работать с бинарниками только в SQL. У меня на хомяке множество вариантов подобного рода кода лежит - ну вот хотя бы проект, который я делал для MS - естественно тут все рисунки в базе. Но для начала мы рассмотрим - как закачать фотку ПРЯМО В ПРОФИЛЬ ASP.NET. И вывести ее оттуда. Это вариант для тех - кому ЛЕНЬ даже свою структуру базы продумывать, для тех кто просто использует стандартные аутентификационные базы.

Для начала, как обычно - определяем профиль. И сохраняем аплоадером в профиль фотографию вот таким классом.

00001: 'небольшой интерфейсик для сохранения/вытаскивания байтов рисунка в типизированный профиль, где допускается только строчное поле
00002: Public Class ProfileImage
00003:     Public Shared Function SaveImageToProfileObj(ByVal Uploader As Web.UI.WebControls.FileUpload, ByRef MyTypedProdfileObject As ProfileCommon) As Integer
00004:             Dim Buf1(Uploader.PostedFile.InputStream.Length) As Byte
00005:             Dim RealReadBytes As Integer = Uploader.PostedFile.InputStream.Read(Buf1, 0, Uploader.PostedFile.InputStream.Length)
00006:             MyTypedProdfileObject.usPhotoLen = RealReadBytes
00007:             Dim XMLSerializer As New System.Xml.Serialization.XmlSerializer(GetType(System.Byte()))
00008:             Dim XMLString As New IO.MemoryStream
00009:             XMLSerializer.Serialize(XMLString, Buf1)
00010:             Dim AnsiConverter As New System.Text.ASCIIEncoding
00011:             Dim XmlBase64 As String = AnsiConverter.GetString(XMLString.GetBuffer)
00012:             Dim StartPos As Integer = XmlBase64.IndexOf("<base64Binary>")
00013:             Dim EndPos As Integer = XmlBase64.IndexOf("</base64Binary>")
00014:             MyTypedProdfileObject.usPhoto = XmlBase64.Substring(StartPos + Len("<base64Binary>"), EndPos - StartPos - Len("<base64Binary>"))
00015:             XMLSerializer = Nothing
00016:             AnsiConverter = Nothing
00017:             XMLString.Close()
00018:             XMLString = Nothing
00019:             Return RealReadBytes
00020:     End Function
00021:     Public Shared Function GetImageFromProfileObj(ByVal CurrentUserName As String) As Byte()
00022:         Dim MyTypedProfileObject As ProfileCommon = Profile.ProfileBase.Create(CurrentUserName)
00023:         Dim RestoreXML As String = "<?xml version=""1.0""?>" & vbCrLf & "<base64Binary>" & MyTypedProfileObject.usPhoto & "</base64Binary>"
00024:         Dim AnsiConverter As New System.Text.ASCIIEncoding
00025:         Dim XMLStream As New IO.MemoryStream(AnsiConverter.GetBytes(RestoreXML))
00026:         Dim DeSerializer As New System.Xml.Serialization.XmlSerializer(GetType(System.Byte()))
00027:         Dim RestoredBytes() As Byte = DeSerializer.Deserialize(XMLStream)
00028:         DeSerializer = Nothing
00029:         AnsiConverter = Nothing
00030:         XMLStream.Close()
00031:         XMLStream = Nothing
00032:         Return RestoredBytes
00033:     End Function
00034: End Class

В чем тут фишка? А в том, что надо предварительно сериализовать бинарный поток в строку. Что и делается в этом классе. Кстати эта сериализация в данном случае происходит почти без прироста длины файла.

Сам по себе бинарник, уложенный в базу будет в этом случае выглядеть так:

Кстати не забудьте при работе с аплоадерами указать параметр в конфиге

- иначе загружать аплоадом длинные бинарники не получится.

А как же отобразить этот бинарник? Для этого в нужном месте мы просто ставим вместо простого тега IMG - вызов хандлера, отображающего рисунок:

А сам по себе текст вот такого хандлера, отображающего рисунок из профиля, может выглядеть вот так:

00001: <%@ WebHandler Language="VB" Class="ProfilePhoto" %>
00002: 
00003: Imports System
00004: Imports System.Web
00005: 
00006: Public Class ProfilePhoto : Implements IHttpHandler
00007:     
00008:     Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
00009:         context.Response.ContentType = "image/gif"
00010:         If context.Request.QueryString("i") = "" Then
00011:             context.Response.Redirect("~/Images/goroskop.gif")
00012:         End If
00013:         'проверим, существует ли запрошенный юзер
00014:         Try
00015:             Dim Guid1 As New Guid(context.Request.QueryString("i"))
00016:             Dim CurrentUser As MembershipUser = Membership.GetUser(Guid1)
00017:             If CurrentUser Is Nothing Then
00018:                 context.Response.Redirect("~/Images/goroskop.gif")
00019:             Else
00020:                   Dim ImageBytes() As Byte = ProfileImage.GetImageFromProfileObj(CurrentUser.UserName)
00021:                 context.Response.BinaryWrite(ImageBytes)
00022:             End If
00023:         Catch ex As Exception
00024:             context.Response.Redirect("~/Images/goroskop.gif")
00025:             Exit Sub
00026:         End Try
00027:     End Sub
00028:  
00029:     Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
00030:         Get
00031:             Return False
00032:         End Get
00033:     End Property
00034: 
00035: End Class

Разумеется, это наиболее простой вариант. Никто нам не мешает например наложить на рисунок логотип. Или преобразовать его как-то иначе.

Если жа мы просто укладываем бинарник в базу, то не требуется ВООБЩЕ никакого специального кода - кроме вызова процедуры - например вот как тут:

В данном случае сама процедура выглядит вот так:

Разумеется тут только самые примитивные варианты работы упомянуты. Ничего не стоит организовать например АСИНХРОННУЮ ЗАГРУЗКУ. Можно на сервере считывать длиннные файлы мультипоточно из отдеьных фрагментов, можно делать Flush в выходном Response-буфере хоть через сто байт, преобразования всякие можно делать. Сложно только придумать - ЧЕГО НИЗЗЯ. Ведь это байтовый поток, которым манипулирует САМ программист, а не тупая NTFS с неуправляемой кеширующей надстройкой в виде IIS.

Вывод бинарного потока из базы в таком случае (когда есть специальная табла с полем Varbinary(max), а не просто профиль) производится опять же хандлером. Например таким:

00001: <%@ WebHandler Language="VB" Class="GetImage" %>
00002: 
00003: Imports System
00004: Imports System.Web
00005: 
00006: Public Class GetImage : Implements IHttpHandler
00007:     
00008:     Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
00009:         If context.Current.Request.QueryString("J") <> Nothing Then
00010:             Dim PP8 As ParmProtector8.ParmProtector8 = CType(context.Current.Application("ParmProtector8"), ParmProtector8.ParmProtector8)
00011:             'номер отображаемого рисунка в UserData
00012:            
00013:             Dim J As Integer = PP8.UnMask(context.Current.Request.QueryString("J"))
00014:             'ширина для преобразованного рисунка или 0 - без преобразования
00015:             Dim W As Integer
00016:             If context.Current.Request.QueryString("W") <> Nothing Then W = CInt(context.Current.Request.QueryString("W")) Else W = 0
00017:             Dim CN As New System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Votpusk").ConnectionString)
00018:             CN.Open()
00019:             Dim CMD As New System.Data.SqlClient.SqlCommand("GetUserData", CN)
00020:             CMD.CommandType = Data.CommandType.StoredProcedure
00021:             CMD.Parameters.AddWithValue("I", J)
00022:             Dim RDR As Data.SqlClient.SqlDataReader = CMD.ExecuteReader()
00023:             If RDR.Read Then
00024:                 If Not IsDBNull(RDR("Data")) Then
00025:                     If W = 0 Then
00026:                         'вывод КАК ЕСТЬ - без массштабированния
00027:                         context.Response.ContentType = "image/bmp"
00028:                         context.Response.BinaryWrite(RDR("Data"))
00029:                     Else
00030:                         'вывод с масштабированием
......
00039:                     End If
00040:                 End If
00041:             End If
00042:             RDR.Close()
00043:             RDR = Nothing
00044:             CMD = Nothing
00045:             CN.Close()
00046:         End If
00047:     End Sub
00048:  
00049:     Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
00050:         Get
00051:             Return False
00052:         End Get
00053:     End Property
00054: 
00055: End Class

Кстати, обратите внимание еще на одну фишку этого хандлера - криптографически защищенный порядковый номер рисунка в базе. Чуствуете гибкость и управляемость механизма доступа к бинарникам в этом случае, относительно доступа к ним в файловой системе? А целостность струкртуры данных относительно того, когда в базе лежат лишь названия каталогов, а в самих каталогах NTFS - не пойми чего лежит вообще?

В этом случае внешняя QueryString-ссылка на рисунок для закладок будет выглядеть вот так:

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

Удивительные вещи нас ждут в этой теме использовании инструментов VS2005. От этого убогого чудища мало чего хорошего можно было бы ожидать, ведь оно даже не умеет обратиться к стандартным базам, с ключами на основе GUID, но... оно имеет великолепный предпросмотр бинарных данных, хранимых в SQL, который как ни странно - работает!

А вообще, я уже писал на своем сайте, что SQL - это тяжелая и незлечимая болезнь. И кто заболел ею, назад - к убогим наследнникам дискеток - уже не вернется никогда... Продолжение темы о бинарниках в SQL - смотрите здесь.

<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>