SQL Server 2012 aggiunge, alla funzionalità di FILESTREAM (tempo fa ne avevo parlato qui), la possibilità di interagire direttamente con le API di Windows, quindi consente ad un’applicazione di copiare dei files (e/o folder) in un path e lasciare al database engine tutta la sua gestione.
Diciamo che è il pezzo che mancava… :-)
Le cose interessanti, poi, sono diverse. Ne cito, per iniziare, due:
- costruire dei trigger che intercettino copie di file “non voluti” e quindi intervenire “al volo”
- gestire un catalogo fulltext e, quindi, indicizzare il contenuto dei files copiati e gestiti
Vediamo come fare…
/*
Enable and Configure FILESTREAM
http://msdn.microsoft.com/en-us/library/cc645923(v=sql.110).aspx
EXEC sp_configure Filestream_Access_level , 2;
RECONFIGURE;
GO
*/
USE MASTER;
GO
IF DB_ID( 'FileTableDB' )IS NOT NULL
BEGIN
DROP DATABASE FileTableDB;
END;
GO
/*
Creazione del database
con FILESTREAM
*/
CREATE DATABASE FileTableDB ON PRIMARY
(
NAME = FileTableDB ,
FILENAME = 'C:\SQL2012\db\FileTableDb\FileTableDB_Data.mdf'
) ,
FILEGROUP FileTableDBFS CONTAINS
FILESTREAM
(
NAME = FileTableDBFS ,
FILENAME = 'C:\SQL2012\db\FileTableDb\FileTableDbFileStream'
)
LOG ON
(
NAME = FileTableDBLog ,
FILENAME = 'C:\SQL2012\db\FileTableDb\FileTableDB_Log.ldf'
);
GO
/*
Specifico il livello di accesso non transazionale a livello di database
Questo per poter ottenere un file handle senza la necessità di una transazione
*/
ALTER DATABASE FileTableDB
SET FILESTREAM
(
NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTableDbFileStream'
)
GO
/*
Passo al database utente
*/
USE FileTableDB;
GO
/*
Creo una tabella FileTable
*/
CREATE TABLE tblFileTableTest AS FileTable
WITH
(
FileTable_Directory = 'tblFileTableTest',
FileTable_Collate_Filename = database_default
)
GO
/*
A questo punto posso aprire una share di rete composta come:
\\nomeMacchina\nomeistanza\directoryName\fileTableDirectory
Quindi, nel mio caso, posso aprire:
\\Abalien\sql2012\FileTableDbFileStream\tblFileTableTest
*/
/* Ecco come posso recuperare il path alla document library: */
SELECT FileTableRootPath() AS 'FileTablePath'
GO
/*
vedo la tabella (vuota)
*/
SELECT *
FROM tblFileTableTest;
GO
/*
Aggiungo un trigger alla tabella perchè, per ipotesi,
voglio interrompere operazioni di copia se contenenti file TXT
*/
CREATE TRIGGER dbo.ControlFileTXT ON tblFileTableTest
FOR INSERT , UPDATE
AS
IF EXISTS( SELECT 1
FROM inserted
WHERE inserted.file_type = 'txt' )
BEGIN
ROLLBACK;
END;
GO
A questo punto posso fare drag & drop di files all’interno del mio folder e ritrovarli in tabella:
Oppure posso provare a copiare dei TXT e ricevere un errore di copia:
Fatto questo abilitiamo l’indicizzazione FULLTEXT sul nostro database e sulla nostra tabella:
/* Abilitiamo catalogo e indicizzazione FULLTEXT */
CREATE FULLTEXT CATALOG filetableCat;
GO
CREATE UNIQUE INDEX ui_document ON tblFileTableTest( stream_id );
GO
CREATE FULLTEXT INDEX ON tblFileTableTest
( file_stream TYPE COLUMN name LANGUAGE 1033 )
KEY INDEX ui_document ON filetableCat WITH CHANGE_TRACKING AUTO;
GO
/* Vediamo lo stato di indicizzazione */
SELECT CASE FULLTEXTCATALOGPROPERTY( 'filetableCat' , 'Populatestatus' )
WHEN 0 THEN 'Idle'
WHEN 1 THEN 'Full population in progress '
WHEN 2 THEN 'Paused'
WHEN 3 THEN 'Throttled'
WHEN 4 THEN 'Recovering'
WHEN 5 THEN 'Shutdown'
WHEN 6 THEN 'Incremental population in progress'
WHEN 7 THEN 'Building index'
WHEN 8 THEN 'Disk is full. Paused.'
WHEN 9 THEN 'Change tracking'
END AS populateStatus ,
CASE FULLTEXTCATALOGPROPERTY( 'filetableCat' , 'AccentSensitivity' )
WHEN 0 THEN 'Accent insensitive'
WHEN 1 THEN 'Accent sensitive'
END AS AccentSensitivity ,
FULLTEXTCATALOGPROPERTY( 'filetableCat' , 'ItemCount' )AS ItemCount;
GO
Attenzione: ricordiamoci che NON tutti i tipi di file sono immediatamente indicizzabili (per alcuni, come il PDF, serve installare il suo iFilter)
Per vedere le estensioni conosciute:
SELECT * FROM sys.fulltext_document_types;
Una volta copiati files nella nostra tabella filetable possiamo farne una ricerca testuale con una query come:
/* Test di ricerca */
DECLARE @SearchWord nvarchar( 300 );
SET @SearchWord = '"enabling end users"';
SELECT name
FROM dbo.tblFileTableTest FT
JOIN CONTAINSTABLE
( dbo.tblFileTableTest , file_stream , @SearchWord ) AS T
ON FT.stream_id = T.[key];
GO