{"id":106,"date":"2011-04-20T20:56:42","date_gmt":"2011-04-20T18:56:42","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=106"},"modified":"2011-04-20T21:06:12","modified_gmt":"2011-04-20T19:06:12","slug":"dokumenten-verwaltung-mit-filestream-und-der-volltextsuche","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=106","title":{"rendered":"Dokumenten Verwaltung mit Filestream und der Volltextsuche"},"content":{"rendered":"

\"\"<\/a>In diesem Artikel geht es um die Speicherung von Dateien und dem Auffinden dieser im SQL Server. Um Dateien nicht komplett in der Datenbank zu speichern, bietet sich Filestream an. Bei dieser Technologie werden lediglich Meta Daten innerhalb der Datenbank gespeichert und die eigentliche Datei befindet sich in einem File-Share und werden dort physikalisch als Datei abgelegt. So spart man sich den Speicherplatz innerhalb der Datenbank.
\nUm diese Dateien aber auch wiederzufinden, bzw. nach dem Inhalt zu suchen, kann die Volltext-Indizierung eingesetzt werden.<\/p>\n

Folgendes Fallbeispiel stellt sich:<\/strong>
\nWir verf\u00fcgen \u00fcber verschiedenste Dokumente, bspw. im DOC, TXT und RTF Format die innerhalb der Datenbank im Filestream gespeichert und auch vollst\u00e4ndig durchsucht werden sollen<\/p>\n

Zu erst muss Filestream \u00fcber den SQL Server Configuration Manager aktiviert werden.
\nSiehe hierf\u00fcr BOL:
\nhttp:\/\/msdn.microsoft.com\/de-de\/library\/cc645923.aspx<\/a><\/p>\n

Zuerst brauchen wir eine Datenbank:
\nCREATE DATABASE [DMS<\/strong>] ON PRIMARY
\n( NAME = N'DMS',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\MSSQL\\DATA\\DMS.mdf' ,
\nSIZE = 3072KB , FILEGROWTH = 1024KB )
\nLOG ON
\n( NAME = N'DMS_log',
\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL\\MSSQL\\DATA\\DMS_log.ldf' ,
\nSIZE = 1024KB , FILEGROWTH = 10%)
\nGO<\/code><\/p>\n

Nun wird das Filestream Feature auf der Datenbank mit dem Access Level 2 (Zugriff auch von externen Ressourcen, wie bspw. .NET) aktiviert:
\nEXEC sys.sp_configure N'filestream access level', N'2'
\nGO
\nRECONFIGURE WITH OVERRIDE
\nGO<\/code><\/p>\n

Nun wird eine weitere Dateigruppe f\u00fcr die Speicherung der Filestream Daten angelegt mit der entsprechenden Ressource. Hier wird auch konfiguriert, wo sich der Filestream Ordner im Netzwerk oder Lokal befindet:
\nALTER DATABASE [DMS] ADD FILEGROUP [dateien] CONTAINS FILESTREAM
\nGO
\nALTER DATABASE [DMS] ADD FILE ( NAME = N'Dateien', FILENAME = N'C:\\filestream\\Dateien' ) TO FILEGROUP [dateien]
\nGO<\/code><\/p>\n

Nun ben\u00f6tigen wir noch eine Tabelle in der die Daten gespeichert werden sollen:
\nCREATE TABLE Dokumente (
\nuid uniqueidentifier default newid() not null primary key ROWGUIDCOL,
\nname varchar(255) not null,
\nerweiterung varchar(255) not null,
\ndatei varbinary(MAX) FILESTREAM NOT NULL,
\nhinzugefuegt datetime default getdate() not null)
\nON [PRIMARY] FILESTREAM_ON [dateien];
\nGO<\/code><\/p>\n

Um Filestream Daten speichern zu k\u00f6nnen, ist die Angabe von ROWGUIDCOL als Index notwendig.<\/p>\n

Nun werden noch 3 Dateien \u00fcber OPENROWSET hinzugef\u00fcgt:
\nINSERT INTO dbo.Dokumente(name, erweiterung, datei)
\nSELECT
\n'Mein RTF Dokument' AS Name,
\n'rtf' AS Erweiterung,
\n* FROM OPENROWSET(BULK N'C:\\temp\\dokument.rtf', SINGLE_BLOB) AS datei;
\nGO<\/code><\/p>\n

INSERT INTO dbo.Dokumente(name, erweiterung, datei)
\nSELECT
\n'Word 2003 Dokument' AS Name,
\n'doc' AS Erweiterung,
\n* FROM OPENROWSET(BULK N'C:\\temp\\beschreibung.doc', SINGLE_BLOB) AS datei;
\nGO<\/code><\/p>\n

<\/code><\/p>\n

INSERT INTO dbo.Dokumente(name, erweiterung, datei)
\nSELECT
\n'Mein TXT Dokument' AS name,
\n'txt' AS erweiterung,
\n* FROM OPENROWSET(BULK N'C:\\temp\\test.txt', SINGLE_BLOB) AS datei
\nGO<\/code><\/p>\n

Nun sind diese 3 Dateien im Filestream Container enthalten.
\nMit folgender Query kann der Pfad ermittelt werden:
\nSELECT name, datei.PathName() FROM dbo.Dokumente<\/code><\/p>\n

Ergebnis:
\n\"\"<\/a><\/p>\n

Die RTF Datei die eingef\u00fcgt wurde, sieht folgenderma\u00dfen aus:
\n\"\"<\/a><\/p>\n

Nun ist das n\u00e4chste Ziel, die Datei wiederzufinden indem nach „Welt“ gesucht wird.
\nDaf\u00fcr ben\u00f6tigen wir den Volltext Index (Achtung: Dieser muss installiert sein!)<\/p>\n

Zuerst wird ein Volltext Katalog ben\u00f6tigt:
\nCREATE FULLTEXT CATALOG FS_Datei_FT_Katalog AS DEFAULT;
\nGO<\/code><\/p>\n

Nun wird der eigentliche Volltext Index erstellt. Dieser geht \u00fcber die Felder Name, Datei und Erweiterung innerhalb der Tabelle damit diese durchsucht werden k\u00f6nnen. Da das Feld „datei“ Filestream Daten enth\u00e4lt, wird dieses mit dem Feld Erweiterung als Typ gebunden. So kann der Volltext Index unterscheiden, um was f\u00fcr Daten es sich handelt.
\nCREATE FULLTEXT INDEX ON [dbo].[Dokumente] KEY INDEX [PK__Dokument__DD7012647F60ED59]
\nON ([FS_Datei_FT_Katalog]) WITH (CHANGE_TRACKING AUTO)
\nGO
\nALTER FULLTEXT INDEX ON [dbo].[Dokumente] ADD ([datei] TYPE COLUMN [erweiterung])
\nGO
\nALTER FULLTEXT INDEX ON [dbo].[Dokumente] ADD ([erweiterung])
\nGO
\nALTER FULLTEXT INDEX ON [dbo].[Dokumente] ADD ([name])
\nGO
\nALTER FULLTEXT INDEX ON [dbo].[Dokumente] ENABLE
\nGO<\/code><\/p>\n

Um zu pr\u00fcfen welche Erweiterungen der Volltext Index unterst\u00fctzt, kann dies mit folgender Abfrage \u00fcberpr\u00fcft werden:
\nSELECT * FROM sys.fulltext_document_types;<\/code><\/p>\n

Weitere Treiber k\u00f6nnen bei Bedarf nachinstalliert werden. Siehe hier (bspw. f\u00fcr Office 2007 oder 2010 Dokumente):
\nhttp:\/\/support.microsoft.com\/default.aspx?scid=kb;en-us;945934<\/a><\/p>\n

Nun kann der Volltext Index die Dateien durchsuchen:
\nselect name, datei.PathName() from dbo.dokumente where FREETEXT(datei, '\"*welt*\"')<\/code><\/p>\n

Ergebnis:
\n\"\"<\/a><\/p>\n

Weitere Informationen:<\/strong>
\nVolltext Filter: http:\/\/technet.microsoft.com\/en-us\/library\/ms142571.aspx
\n<\/a>Filestream: http:\/\/technet.microsoft.com\/en-us\/library\/bb933993.aspx<\/a><\/p>\n

Das wars.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

In diesem Artikel geht es um die Speicherung von Dateien und dem Auffinden dieser im SQL Server. Um Dateien nicht komplett in der Datenbank zu speichern, bietet sich Filestream an. Bei dieser Technologie werden lediglich Meta Daten innerhalb der Datenbank … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[17,10,3,16],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/106"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=106"}],"version-history":[{"count":5,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions\/115"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}