/*

Test STOPLISTS using filetable tables.

Words in Stop Lists are NOT used.

*/
USE [master]
go

/*

SETUP

*/

IF (DB_ID('SemanticTestFileStream') IS NOT NULL) BEGIN
	ALTER DATABASE SemanticTestFileStream SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE SemanticTestFileStream
END
go

CREATE DATABASE SemanticTestFileStream
GO

ALTER DATABASE SemanticTestFileStream ADD FILEGROUP FSSTORAGE CONTAINS FILESTREAM 
GO

ALTER DATABASE SemanticTestFileStream
ADD FILE ( NAME= 'FS', FILENAME = 'D:\FSSTORAGE' )
TO FILEGROUP FSSTORAGE
GO

ALTER DATABASE SemanticTestFileStream
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FS' )
GO

USE SemanticTestFileStream
GO

CREATE TABLE dbo.documents AS FILETABLE
GO

CREATE UNIQUE INDEX IUXC__documents__1 ON dbo.documents(stream_id)
GO

INSERT INTO dbo.documents (name, file_stream) VALUES 
	('AlwaysOn SQL Server Failover Cluster Instances.xml', CAST(CAST(N'<document xml:lang="en-US">Multi-subnet failover clusters: A SQL Server multi-subnet failover cluster is a configuration where each failover cluster node is connected to a different subnet or different set of subnets. These subnets can be in the same location or in geographically dispersed sites. Clustering across geographically dispersed sites is sometimes referred to as Stretch clusters. As there is no shared storage that all the nodes can access, data should be replicated between the data storage on the multiple subnets. With data replication, there is more than one copy of the data available. Therefore, a multi-subnet failover cluster provides a disaster recovery solution in addition to high availability. For more information, see SQL Server Multi-Subnet Clustering.</document>' AS XML) AS VARBINARY(MAX))),
	('FileTables.xml', CAST(CAST(N'<document xml:lang="en-US">The FileTable feature builds on top of the SQL Server FILESTREAM technology to bring support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. This lets an application integrate its storage and data management components, and provides integrated SQL Server services (including full-text search and semantic search) over unstructured data and metadata, along with easy policy management and administration. In summary, you can now store files and documents in special tables in SQL Server, but access them from Windows applications as if they were stored in the file system, without making any changes to the Windows applications. For more information about the FileTable features, see FileTables (SQL Server).</document>' AS XML) AS VARBINARY(MAX))),
	('SQL Server Audit Enhancements.xml', CAST(CAST(N'<document xml:lang="en-US">Support for server level auditing is expanded to include all editions of SQL Server. Database level auditing is limited to Enterprise, Developer, and Evaluation editions. SQL Server Audit is now more resilient to failures to write to the audit log. For example, if the target directory is on a remote share and the network goes down, SQL Server Audit will now be able to recover once the network connection is re-established. In addition, a new option has been introduced to fail an operation that would otherwise generate an audit event to be written to a failed audit target. For more information, see the FAIL_OPERATION option for the ON_FAILURE event in CREATE SERVER AUDIT.</document>' AS XML) AS VARBINARY(MAX)))
GO

CREATE FULLTEXT CATALOG [FTC] WITH ACCENT_SENSITIVITY = ON AS DEFAULT AUTHORIZATION [dbo]
GO

CREATE FULLTEXT STOPLIST [TestStopList] FROM SYSTEM STOPLIST AUTHORIZATION [dbo];
GO

ALTER FULLTEXT STOPLIST [TestStopList] ADD 'sql' LANGUAGE 'English';
ALTER FULLTEXT STOPLIST [TestStopList] ADD 'server' LANGUAGE 'English';
ALTER FULLTEXT STOPLIST [TestStopList] ADD 'data' LANGUAGE 'English';
GO

CREATE FULLTEXT INDEX ON [dbo].[documents] KEY INDEX IUXC__documents__1 ON ([FTC]) WITH (CHANGE_TRACKING AUTO, STOPLIST = TestStopList)
GO

ALTER FULLTEXT INDEX ON [dbo].[documents] ADD (file_stream TYPE COLUMN file_type LANGUAGE [English] STATISTICAL_SEMANTICS)
GO

ALTER FULLTEXT INDEX ON [dbo].[documents] ENABLE
GO

-- Wait a while to be sure documents are indexes
WAITFOR DELAY '00:00:05'

/*

TEST STOPLIST. 
Keyphrase "sql" should not be found anywhere since included in stoplist.
But it IS found and used everywhere

*/

SELECT * FROM semantickeyphrasetable(documents, file_stream)
WHERE keyphrase = 'sql'
GO

DECLARE @i1 UNIQUEIDENTIFIER, @i2 UNIQUEIDENTIFIER
SELECT @i1 = stream_id FROM dbo.documents WHERE name = 'FileTables.xml'
SELECT @i2 = stream_id FROM dbo.documents WHERE name = 'SQL Server Audit Enhancements.xml'

SELECT * FROM SEMANTICSIMILARITYDETAILSTABLE(dbo.documents, file_stream, @i1, file_stream, @i2)
WHERE keyphrase = 'sql'
GO