(SQL) SQL(2012 год)

FileStream в MS SQL.

MS SQL FileStream Давным-давно стало ясно, что бинарники (рисунки и видео) хранить в файловой системе тупо. Прежде всего потому что:

Поэтому все более ли менее дееспособные программисты давно выработали свои решение для хранения бинарников в базе. Давным-давно и я наработал для себя шаблон решения подобных задая - Cекционирование графики при SQL-хранении..

Но вот потянулся и микрософт и с небольшим отставанием родил в своем MS SQL этот функционал. Что ж, неплохо. Попробуем сравнить мое давнишнее решение и решение программеров Билла Гейтса.

Мое решение чудесно работает на простых дисках, ибо обо позволяет сплитировать данные на короткие фрагменты и разбросать их по многим простым дискам. Если у вас именно такая аппаратная конфигурация, то решение БилоГейтсовских проггеров вам не подойдет совсем. Но если у вас хороший аппаратный рейд, то решение микрософтовцев я оцениваю как неплохое. Мое решение со сплитированием - более тяжеловесное для такой конфигурации (хотя возможно будет работать быстрее). Но мое решение требует программирования, а управление микрософтовским решением вынесено непосредственно в GUI.

Итак, предже всего этот функционал SQL сервера надо разрешить (на уровне сервера и базы).


MS SQL FileStream MS SQL FileStream

И далее создать такую базу. Хитрость в том, что в обязательном порядке в базе должно быть поле ROWGUIDCOL. В виде скрипта для моего примера полный функционал создание базы с FileStream выглядит вот так:


   1:  SELECT SERVERPROPERTY ('FilestreamConfiguredLevel'),SERVERPROPERTY ('FilestreamEffectiveLevel');
   2:  GO
   3:  EXEC sys.sp_configure N'filestream access level', N'1'
   4:  GO
   5:  RECONFIGURE WITH OVERRIDE
   6:  GO
   7:   
   8:   
   9:   
  10:  CREATE DATABASE [FileStream] ON  PRIMARY 
  11:  ( NAME = N'FileStream', FILENAME = N'K:\FS_DB\FileStream.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
  12:   LOG ON 
  13:  ( NAME = N'FileStream_log', FILENAME = N'G:\FS_TL\FileStream_log.ldf' , SIZE = 53248KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  14:  GO
  15:   
  16:  ALTER DATABASE [FileStream] SET COMPATIBILITY_LEVEL = 100
  17:  GO
  18:   
  19:   
  20:  ALTER DATABASE FileStream
  21:     ADD FILEGROUP FS contains filestream
  22:  go
  23:   
  24:  ALTER DATABASE FileStream
  25:     ADD FILE (
  26:        NAME = FS,
  27:        FILENAME = 'U:\FileStream'
  28:        )
  29:     TO FILEGROUP FS
  30:  go   
  31:     
  32:  ALTER DATABASE FileStream
  33:     MODIFY FILEGROUP FS DEFAULT;
  34:  go
  35:   
  36:   
  37:  use FileStream
  38:   
  39:  Create table HotelFoto
  40:  (
  41:  ID char(8),
  42:  CityID char(8),
  43:  CtgID char(8),
  44:  TypeID char(8),
  45:  RowGuid uniqueidentifier not null ROWGUIDCOL unique default newid(),
  46:  Image varbinary(max) Filestream null
  47:  ) 
  48:  go
  49:   
  50:  CREATE NONCLUSTERED INDEX [index_id] ON [dbo].[HotelFoto] 
  51:  (
  52:      [ID] ASC
  53:  )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  54:  GO
  55:   
  56:  CREATE NONCLUSTERED INDEX [index_CityID] ON [dbo].[HotelFoto] 
  57:  (
  58:      [CityID] ASC
  59:  )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  60:  GO
  61:   
  62:  CREATE NONCLUSTERED INDEX [index_CtgID] ON [dbo].[HotelFoto] 
  63:  (
  64:      [CtgID] ASC
  65:  )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  66:  GO
  67:   
  68:  CREATE NONCLUSTERED INDEX [index_TypeID] ON [dbo].[HotelFoto] 
  69:  (
  70:      [TypeID] ASC
  71:  )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  72:  GO

Базу и файловую структуру, которые создаются этим скриптов - вы видите в титуле этой странички. А свойства таблы выглядят вот так:


MS SQL FileStream

Теперь посмотрим как с этим работать. Во-первых, как читать это из базы и отдавать в браузер например.

Делается это вот таким хандлером:


   1:  <%@ WebHandler Language="VB" Class="GetImage" %>
   2:   
   3:  Imports System
   4:  Imports System.Web
   5:  Imports System.Data
   6:   
   7:  Public Class GetImage : Implements IHttpHandler
   8:      
   9:      Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
  10:          Try
  11:              context.Response.ContentType = "image/bmp"
  12:              Dim CN1 As New SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SQLServer_ConnectionStrings").ConnectionString)
  13:              CN1.Open()
  14:              Dim CMD1 As New SqlClient.SqlCommand("Select Image from [FileStream].[dbo].[HotelFoto] where ID=@ID and Num=@Num and CityID=@CityID and CtgID=@CtgID and TypeID=@TypeID", CN1)
  15:              CMD1.Parameters.Add("@ID", SqlDbType.Char)
  16:              CMD1.Parameters.Add("@CityID", SqlDbType.Char)
  17:              CMD1.Parameters.Add("@CtgID", SqlDbType.Char)
  18:              CMD1.Parameters.Add("@TypeID", SqlDbType.Char)
  19:              CMD1.Parameters.Add("@Num", SqlDbType.Int)
  20:              CMD1.Parameters("@ID").Value = context.Request.QueryString("ID")
  21:              CMD1.Parameters("@CityID").Value = context.Request.QueryString("CityID")
  22:              CMD1.Parameters("@CtgID").Value = context.Request.QueryString("CtgID")
  23:              CMD1.Parameters("@TypeID").Value = context.Request.QueryString("TypeID")
  24:              CMD1.Parameters("@Num").Value = context.Request.QueryString("Num")
  25:              Dim RDR1 As SqlClient.SqlDataReader = CMD1.ExecuteReader(CommandBehavior.SingleRow)
  26:              If RDR1.Read Then
  27:                  HttpContext.Current.Response.BinaryWrite(RDR1("Image"))
  28:              End If
  29:          Catch ex As Exception
  30:              '
  31:          End Try
  32:          
  33:      End Sub
  34:   
  35:      Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
  36:          Get
  37:              Return False
  38:          End Get
  39:      End Property
  40:   
  41:  End Class

Соотвественно, вызов этого зандлера выглядит так - http://img.votpusk.ru/GetImage.ashx?ID=AB001&CityID=AB01&CtgID=5&TypeID=1&Num=1 или вот так:

<img src=GetImage.ashx?ID=AB001&CityID=AB01&CtgID=5&TypeID=1&Num=1 />

Теперь я приведу пример хандлера, который грузит рисунки в базу FileStream. Для примера - я возьму загрузчик рисунков, описанный мною здесь - Пакетный загрузчик файлов на сайт - и покажу ниже, как должен быть модифицирован хандлер, чтобы он не просто сохранял рисунки на диск, а укладывал рисунки в потоки FileStream:


   1:  <%@ WebHandler Language="VB" Class="LoadImage" %>
   2:   
   3:  Imports System
   4:  Imports System.Web
   5:  Imports System.Data
   6:   
   7:  Public Class LoadImage : Implements IHttpHandler
   8:      
   9:      Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
  10:          Try
  11:              If context.Request.RequestType = "POST" Then
  12:                  CheckURL(context.Request.RawUrl)
  13:                  Dim Hotel As String = context.Request.QueryString("Hotel")
  14:                  Dim Dir As String = HttpContext.Current.Server.MapPath("~")
  15:                  Dim PostMultipartParser As MultipartParser = New MultipartParser(context.Request.InputStream)
  16:                  If PostMultipartParser.Success Then
  17:                      SaveImageToDB(Hotel, PostMultipartParser.FileContents)
  18:                  End If
  19:                  context.Response.ContentType = "text/plain"
  20:                  context.Response.Write("OK")
  21:              Else
  22:                  context.Response.ContentType = "text/plain"
  23:                  context.Response.Write("Ready")
  24:              End If
  25:              
  26:              
  27:          Catch ex As Exception
  28:              SaveErrLog(ex.Message)
  29:              context.Response.ContentType = "text/plain"
  30:              context.Response.Write(ex.Message)
  31:          End Try
  32:        
  33:      End Sub
  34:      
  35:      Sub SaveImageToDB(Hotel As String, Binary As Byte())
  36:          Dim CNW As New SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SQLServer_ConnectionStrings").ConnectionString)
  37:          CNW.Open()
  38:          Dim CMDW As New SqlClient.SqlCommand("INSERT INTO [FileStream].[dbo].[HotelFoto]([ID],[Image])VALUES (@ID,@Image)", CNW)
  39:          CMDW.Parameters.Add("@ID", SqlDbType.Char)
  40:          CMDW.Parameters.Add("@Image", SqlDbType.VarBinary)
  41:          CMDW.Parameters("@ID").Value = Hotel
  42:          CMDW.Parameters("@Image").Value = Binary
  43:          CMDW.ExecuteScalar()
  44:          CNW.Close()
  45:      End Sub
  46:      
  47:      Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
  48:          Get
  49:              Return False
  50:          End Get
  51:      End Property
  52:   
  53:  End Class

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


  <system.web>
    <httpRuntime maxRequestLength="2097151" />
...

В целом, как видите, пользоватся FileStream гораздо проще чем в моим решением - Хранение графики в SQL-сервере. В решении микрософтовских программистов байтовый массив напрямую сохраняется в поле varbinary(max) - а в моем решении это поле разбивается на десятки мелких фрагментов и кажлый фрагмент укладывается в свою таблу, находяющуюся на отдельном диске. Но повторюсь - микрософтовское решение эффективно для хорошего аппаратного рейда, а мое решение рассчитано но множество отдельных дисков.



Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17
Link to this page: http://www.vb-net.com/FileStream/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <MAIL ME>  <ABOUT ME>  < THANKS ME>