Na potrzeby tego artykułu załóżmy, że chcemy przechowywać bazę zeskanowanych dokumentów. Analizowane będą trzy sposoby postępowania.
- Meta dane pliku umieszczamy w bazie, a pliki niezależnie na dysku (jedną z meta danych jest ścieżka)
- Pliki przechowywane w postaci obiektów BLOB w bazie
W kolejnych artykułach omówiony będą jeszcze:
- Pliki przechowywane przy użyciu FileStream w SQL 2008 (z punktu widzenia bazy widziane, jako BLOB, ale fizycznie przechowywane w postaci pliku na dysku)
- Własne typ .NET
Plik na dysku + baza
W takim rozwiązaniu, plik znajduje się na dysku, a w bazie znajduje się opis metadanych wraz z lokalizacją pliku).
Z zalet takie podejścia można wymienić względną prostotę i fakt, że nie wykorzystuje się specyficznych możliwości konkretnej bazy danych. Można zbudować sensowne API, które "opakuje" operację na plikach i z punktu widzenia kodu aplikacji będzie to rozwiązanie wygodne. Ale pozostaną wyzwania związane z backupem (mamy 2 miejsca gdzie są przechowywane dane) i zawsze ryzyko "ręcznej" zmiany zawartości pliku (czy wręcz jego skasowanie).
Najwięcej problemów pojawi się, jeżeli np. zostanie utworzony wpis w bazie, ale fizycznie plik nie będzie zapisany. Wtedy – konieczny jest jakiś program administracyjny, który albo pozwoli ręcznie pousuwać nadmiarowe wpisy albo – automatycznie wyszuka takie braki. Czynność dodawanie nowego wpisu można potraktować, jako prosty proces biznesowy – i np. wykorzystać mechanizm Workflow Foundation do "pilnowania" spójności transakcyjnej. Wtedy, proces zapisu czy kasowania miałby 2 operacje – gdzie w przypadku niepowodzenia można by zdefiniować kompensację, która "posprząta" automatycznie stan systemu.
Ale niestety – nie ma sposób by elegancko rozwiązać problem, gdy przez przypadek zostanie skasowany plik na dysku bez kasowania wpisu w bazie.
Obiekty BLOB w bazie
Jest to rozwiązanie "najbardziej klasyczne". W specjalnym polu BLOB (typy danych to nvarchar(max) czy varbinary(max) przechowywana jest postać binarna danego pliku.
Największym problemem związanym z tym podejściem jest rozmiar pliku mdf/ldf (plików bazy danych). Jeżeli plik są małe i jest ich niewiele, to nie jest to duży problem, jednak próba przechowywania w taki sposób plików video spowoduje, że sama baza bardzo urośnie. Na marginesie, warto dodać, że w części systemów, które wykorzystują taki sposób przechowywania definiowane są 2 bazy – jedna na "BLOB-y" a druga na metadane. Transakcja może obejmować 2 bazy – i spójność ACID operacji będzie zachowana.
Typy danych binarnych I "dużych" tekstowych
W SQL 2008 do przechowywania dużych porcji informacji można użyć następujących typów danych
- image
- binary(n) – gdy dane mają stałą długość n
- varbinary(n) – gdy znana maksymalna długość danych
- varbinary(max) – typ zalecany, gdy informacje mają różne długości
Jeżeli dane są natury tekstowej:
- varchar(max) – typ zalecany
- nvarchar(max) – typ zalecany; Unicode
- ntext / text
Zalecane jest stosowanie typów nvarchar(max), varchar(max) i (var)binary(max), ponieważ inne typy (na pewno ntext, text i image) zostaną usunięte z przyszłych wersji SQL Server. Dodatkowo, na przykład na (n)varchar(max) można postawić normalny indeks (choć – warto się zastanowić czy to się na pewno opłaca).
Warto się chwilę zastanowić, kiedy wybierać typ danych tekstowy a kiedy binarny. Na danych tekstowych można wykonywać operacje "wybierające" porcje danych. Na przykład STUFF czy inne z tej listy. Dane binarne mają tylko operację SUBSTRING ( value_expression ,start_expression , length_expression ) (wbrew nazwie może ona także operować na danych binarnych – zwraca wtedy typ varbinary)
Uwaga! Polecenia typu TEXTPTR, WRITETEXT czy UPDATETEXT i inne, w których pobierany jest "wskaźnik" do BLOB-a, który używany do odczytu porcji danych będą wycofane w przyszłych wersjach Sql Server – więc lepiej ich nie stosować.
Schemat przykładowej bazy danych
Przykładowy schemat bazy danych:
CREATE DATABASE [ScannedDocument_BLOB] ON PRIMARY
( NAME = N'ScannedDocument_BLOB', FILENAME = N'C:\SQLEXPRESS\ScannedDocument_BLOB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ScannedDocument_BLOB_log', FILENAME = N'C:\SQLEXPRESS\ScannedDocument_BLOB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [ScannedDocument_BLOB] SET COMPATIBILITY_LEVEL = 100
GO
CREATE TABLE [dbo].[Document](
[id] [int] IDENTITY(1,1) NOT NULL,
[documentImage] [varbinary](max) NOT NULL,
[author] [nvarchar](50) SPARSE NULL,
[…]
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Dokumenty będą przechowywane w tabeli Document, w polu documentImage. Operacje na tym polu nie są wykonywane tak samo jak na dowolnym innym typie. Pola metadanych (tu author) ma atrybut SPARSE, co powoduje, że jeżeli nie jest wypełnione to w ogóle nie zajmuje miejsca w bazie danych (więcej o tej możliwości SQL 2008 można przeczytać tutaj).
Na przykład, procedura składowana do dodawania nowego elementu może mieć postać:
CREATE procedure [dbo].[spAddScannedDocument](
@documentImage varbinary(max)
) as
insert into Document(documentImage) values (@documentImage)
select cast(SCOPE_IDENTITY() as int)
Z punktu widzenia aplikacji klienckiej, jej wywołanie to (przy drobnej pomocy Enterprise Library by "ukryć" nieistotny kod):
public static int Create(byte[] arr)
{
int id;
Database db = DatabaseFactory.CreateDatabase();
object o;
id = (int)db.ExecuteScalar("spAddScannedDocument", arr);
return id;
}
Jeżeli źródłem pliku będzie strumień, to przy takim sposobie zapisu, trzeba ten strumień (po stronie aplikacji klienckiej) wczytać do pamięci, po czym przekazać go, jako parametr typu tablica, do procedury przechowywanej. Co – w przypadku, gdy jest to na przykład plik z filmem o wielkości 2GB – spowoduje, że w celu zapisu (czy – aktualizacji) informacji na chwilę będzie musiał być zaalokowany duży bufor.
public static int Create(Stream docStream)
{
byte[] arr = new byte[docStream.Length];
docStream.Read(arr, 0, (int)docStream.Length);
return Create(arr);
}
Jednak można zdefiniować inne API, w którym na serwer będą przesyłane tylko małe porcje informacji. Polecenie T-SQL UPDATE w SQL 2008 ma dodatkową klauzulę .WRITE, które pozwala zapisać fragment BLOB-a. Definiując odpowiednią procedurę składowaną można pozwolić klientowi określić, jaka porcja danych będzie uaktualniana:
create procedure spUpdateScannedDocument
( @id as int,
@value varbinary(max),
@offset bigint,
@length bigint
)
as
update Document set documentImage.Write(@value,@offset,@length) where id=@id
Metoda w C#, która będzie np. doklejać informacje do pliku może mieć postać:
public static void Append(int id, Stream str, int chunkSize) {
Database db = DatabaseFactory.CreateDatabase();
byte[] arr = new byte[chunkSize];
int read;
long pos=0;
while ((read = str.Read(arr, 0, chunkSize)) > 0) {
if (read == chunkSize) {
db.ExecuteNonQuery("spUpdateScannedDocument", id, arr, pos, read);
pos += read;
} else {
db.ExecuteNonQuery("spUpdateScannedDocument", id, arr, pos, read);
pos += read;
db.ExecuteNonQuery("spUpdateScannedDocument", id, DBNull.Value, pos, DBNull.Value); //Obcinamy
break;
}
}
}
W podobny sposób można już zdefiniować funkcję dopisywania nowego dokumentu bez konieczności jego buforowania po stronie klienckiej:
public static int CreateByStream(Stream str) {
int id = -1;
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required)) {
Database db = DatabaseFactory.CreateDatabase();
id = (int)db.ExecuteScalar("spAddEmptyScannedDocument");
Append(id, str, 1024);
ts.Complete();
}
return id;
}
Na koniec tej części, warto wspomnieć o jeszcze innym sposobie aktualizacji BLOB w bazie – przy założeniu, że dokument fizycznie znajdzie się na serwerze bazodanowym (w jakimś folderze) lub udziale do którego SQL Server ma dostęp. Wtedy można wykonać operację (T-SQL):
UPDATE Document
SET documentImage = (
SELECT *
FROM OPENROWSET(BULK 'c:\myDocument.docx', SINGLE_BLOB) AS x )
WHERE id = 1
OpenRowset pozwala otworzyć dowolne źródło danych OLE DB. Opcja BULK powoduje, że plik jest traktowany jako całość. SINGLE_BLOB oznacza, że plik będzie traktowany jako ciąg danych varbinary(max) – dokładnie to co jest potrzebne w tym przypadku. Można jeszcze wykorzystywać normalny mechanizm Bulk Copy (BCP) czy pakiet SSIS.
Odczyt danych z bazy
Odczyt danych binarnych z bazy można zrealizować na 3 sposoby. Pierwszy – po prostu "pobrać" całą kolumnę:
Database db = DatabaseFactory.CreateDatabase();
IDataReader dr = db.ExecuteReader("spGetDocumentPart", _id, _position, count);
int read = 0;
byte[] tmp;
if (dr.Read()) {
tmp = (byte[])dr.GetValue(0);
read = tmp.Length;
tmp.CopyTo(buffer, 0);
}
dr.Close();
Oczywiście w tym momencie pobieramy cały plik "na raz".
Drugi sposób wykorzystuje mechanizm opcji dostępu sekwencyjnego w ADO.NET. Wtedy, można napisać własny strumień, na przykład w taki sposób (wersja uproszczona; bez żadnej obsługi błędów czy wyjątków):
public class SqlBlobStreamTDS : Stream {
DbDataReader _dr;
long _position;
long _length;
public SqlBlobStreamTDS(int id) {
Database db = DatabaseFactory.CreateDatabase();
_length = (long)db.ExecuteScalar("spGetDocumentLength", id);
_position = 0;
DbCommand cmd = db.GetStoredProcCommand("spGetDocument", id);
cmd.Connection = db.CreateConnection();
cmd.Connection.Open();
_dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection);
_dr.Read(); //Czytamy pierwszy rekord, ale ustawione jest do odczytu sekwencyjnego
}
[…]
public override long Length { get { return _length; } }
public override long Position { get { return _position; } set { throw new NotImplementedException();}}
public override int Read(byte[] buffer, int offset, int count) {
long read = (int)_dr.GetBytes(0, _position, buffer, offset, count);
_position += read;
return (int)read;
}
[…]
public override void Close() {
_dr.Close();
}
}
Jeżeli w ExecuteReader zostanie podany parametr CommandBehavior.SequentialAccess to wtedy można pobrać kolejne bajty ze strumienia przy użyciu metody DataReader GetBytes(<id pola>, pozycja, bufor, offset, count), co w łatwy sposób pozwoliło zaimplementować "szkielet" strumienia.
Uwaga! API GetBytes(…) wykorzystuje mechanizm ReadByteArray(..) z klasy TdsParserStateObject. "Strumień" zwracany jest na poziomie kanału komunikacyjnego. W SQL Profiler zobaczymy po prostu "odczyt" pola – z tym, że z czasem wykonania zależnym od tego jak szybko klient będzie pobierał dane (to będzie widoczne dopiero dla bardzo dużych BLOB-ów).
Jeżeli byśmy chcieli mieć API gdzie strumień "zwracany" jest po kawałku – należy, używając wspomnianego wcześniej SUBSTRING zdefiniować odpowiednią procedurę, na przykład:
CREATE PROCEDURE [dbo].[spGetDocumentPart]
@id int = 0,
@starting_position bigint ,
@length bigint
AS
BEGIN
select SUBSTRING(documentImage,@starting_position,@length) from Document where id=@id
END
Uwaga! SUBSTRING pozycję w BLOB liczy od 1 a nie od zera! Czyli implementacja strumienia może wyglądać w następujący sposób:
public class SqlBlobStreamByPart : Stream {
long _position;
long _length;
int _id;
public SqlBlobStreamByPart(int id) {
Database db = DatabaseFactory.CreateDatabase();
_length = (long)db.ExecuteScalar("spGetDocumentLength", id);
_position = 0;
_id = id;
}
[…]
public override int Read(byte[] buffer, int offset, int count) {
Database db = DatabaseFactory.CreateDatabase();
IDataReader dr = db.ExecuteReader("spGetDocumentPart", _id, (_position + 1), (long)count);
int read = 0;
byte[] tmp;
if (dr.Read()) {
tmp = (byte[])dr.GetValue(0);
read = tmp.Length;
tmp.CopyTo(buffer, 0);
}
dr.Close();
_position += read;
return read;
}
[…]
}
Warto też zauważyć, że ten sposób obsługi plików BLOB ma jeszcze jedną cechę – można w dowolny sposób poruszać się po strumieniu (zmieniając pozycję, od której SUBSTRING czyta informacje).
Na koniec warto pokazać kilka sposobów wywołania zdefiniowanego "API":
int id,id1;
int read;
FileStream fs=new FileStream(@"C:\Documents\Bigfile1.txt",FileMode.Open);
id=DocumentAPI.Create(fs);
fs.Close();
fs=new FileStream(@"C:\Documents\Bigfile1.txt",FileMode.Open);
id1=DocumentAPI.CreateByStream(fs);
fs.Close();
byte[] arr=new byte[10000];
using(SqlBlobStreamTDS tds=new SqlBlobStreamTDS(id)) {
tds.Read(arr,0,arr.Length);
tds.Read(arr,0,arr.Length);
}
using(SqlBlobStreamByPart tds=new SqlBlobStreamByPart(id1)) {
tds.Read(arr,0,arr.Length);
tds.Seek(0,SeekOrigin.Begin);
tds.Read(arr,0,arr.Length);
}
Pełny plik z przykładami można ściągnąć tu: SimpleBlob.zip (282,22 kb)
W kolejnej części opublikowany będzie sposób postępowania przy wykorzystaniu FileStream i kilka uwag na temat typów .NET.