Хранение графики в 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> |