BLOBs, FILESTREAM i Full-Text Search

-- Primjer: Tabela za čuvanje slika profila
CREATE TABLE Users.ProfilePictures (
    ProfilePictureID INT IDENTITY(1,1) PRIMARY KEY,
    UserID INT NOT NULL,
    PictureData VARBINARY(MAX) NOT NULL,
    FileName NVARCHAR(255),
    ContentType NVARCHAR(100), -- npr. 'image/jpeg', 'image/png'
    FileSize BIGINT, -- veličina u bajtovima
    UploadDate DATETIME2 DEFAULT SYSDATETIME()
);
GO

-- Primjer: Tabela za dokumente
CREATE TABLE Documents.Contracts (
    ContractID INT IDENTITY(1,1) PRIMARY KEY,
    ContractNumber NVARCHAR(50) UNIQUE,
    Title NVARCHAR(200) NOT NULL,
    DocumentContent VARBINARY(MAX) NOT NULL,
    DocumentType NVARCHAR(50), -- 'PDF', 'DOCX', 'XLSX'
    CreatedDate DATETIME2 DEFAULT SYSDATETIME(),
    CreatedBy NVARCHAR(100)
);
GO

-- Metoda 1: Umetanje iz fajla pomoću OPENROWSET (zahtijeva permisije)
INSERT INTO Documents.Contracts (Title, DocumentContent, DocumentType)
SELECT 
    'Ugovor o radu',
    BulkColumn,
    'PDF'
FROM OPENROWSET(BULK 'C:\Documents\Contract.pdf', SINGLE_BLOB) AS Document;
GO

-- Metoda 2: Umetanje iz aplikacije (C#, Python, itd.)
-- Aplikacija čita fajl u byte array i šalje kao parametar
-- INSERT INTO Documents.Contracts (Title, DocumentContent) 
-- VALUES (@Title, @DocumentBytes);

-- Metoda 3: Konverzija iz string-a (za male podatke)
DECLARE @SmallImage VARBINARY(MAX) = CONVERT(VARBINARY(MAX), 'Binary data here');
GO

-- Korak 1: Omogućiti FILESTREAM na SQL Server instance nivou
-- Ovo se radi kroz SQL Server Configuration Manager ili T-SQL:
EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;
GO

-- Vrijednosti:
-- 0 = FILESTREAM onemogućen
-- 1 = Omogućen samo za Transact-SQL pristup
-- 2 = Omogućen za T-SQL i Windows file I/O pristup (preporučeno)

-- Korak 2: Kreirati FILESTREAM filegroup u bazi
ALTER DATABASE MyDatabase
ADD FILEGROUP FileStreamFG CONTAINS FILESTREAM;
GO

-- Korak 3: Dodati FILESTREAM fajl u filegroup
ALTER DATABASE MyDatabase
ADD FILE (
    NAME = 'FileStreamData',
    FILENAME = 'C:\SQLData\FileStreamData' -- Folder gdje će se čuvati fajlovi
)
TO FILEGROUP FileStreamFG;
GO

-- Napomena: Folder mora postojati, ali SQL Server će ga kreirati ako ne postoji
-- FILENAME mora biti putanja do foldera, ne fajla

-- Važno: FILESTREAM tabele MORAJU imati UNIQUEIDENTIFIER kolonu sa ROWGUIDCOL
CREATE TABLE Legal.Contracts (
    ContractID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWID(),
    ContractNumber NVARCHAR(50) UNIQUE NOT NULL,
    Title NVARCHAR(200) NOT NULL,
    Document VARBINARY(MAX) FILESTREAM NULL, -- FILESTREAM kolona
    FileName NVARCHAR(255),
    FileSize BIGINT,
    UploadDate DATETIME2 DEFAULT SYSDATETIME(),
    CreatedBy NVARCHAR(100)
);
GO

-- Napomena: 
-- - ROWGUIDCOL označava kolonu koja čuva GUID za FILESTREAM
-- - FILESTREAM kolona može biti NULL
-- - Maksimalna veličina fajla je ograničena samo disk prostorom (ne 2 GB kao VARBINARY)

-- Metoda 1: Umetanje iz fajla pomoću OPENROWSET
INSERT INTO Legal.Contracts (ContractNumber, Title, Document, FileName, FileSize)
SELECT 
    'CONTRACT-2024-001',
    'Ugovor o radu',
    BulkColumn,
    'Contract.pdf',
    DATALENGTH(BulkColumn)
FROM OPENROWSET(BULK 'C:\Documents\Contract.pdf', SINGLE_BLOB) AS Document;
GO

-- Metoda 2: Umetanje iz aplikacije (preporučeno)
-- Aplikacija koristi SqlFileStream klasu (.NET) ili slično za direktan pristup
-- Ovo omogućava streaming bez kopiranja cijelog fajla u memoriju

-- Metoda 3: Umetanje praznog FILESTREAM-a i kasnije popunjavanje
INSERT INTO Legal.Contracts (ContractNumber, Title, Document)
VALUES ('CONTRACT-2024-002', 'Novi ugovor', 0x); -- 0x = prazan BLOB
GO

-- Čitanje FILESTREAM podataka
SELECT 
    ContractID,
    ContractNumber,
    Title,
    Document, -- Vraća VARBINARY(MAX)
    FileName,
    FileSize
FROM Legal.Contracts
WHERE ContractID = '12345678-1234-1234-1234-123456789012';
GO

-- Za eksport u fajl, koristite aplikaciju koja čita VARBINARY(MAX) i piše u fajl
-- Ili koristite bcp utility ili SQL Server Integration Services (SSIS)

-- Korak 1: Omogućiti FILESTREAM (kao što je prikazano ranije)
EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;
GO

-- Korak 2: Kreirati FILESTREAM filegroup (ako već nije kreiran)
ALTER DATABASE MyDatabase
ADD FILEGROUP FileStreamFG CONTAINS FILESTREAM;
GO

ALTER DATABASE MyDatabase
ADD FILE (
    NAME = 'FileStreamData',
    FILENAME = 'C:\SQLData\FileStreamData'
)
TO FILEGROUP FileStreamFG;
GO

-- Korak 3: Omogućiti non-transacted access (potrebno za FileTable)
ALTER DATABASE MyDatabase
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'MyFileTableShare');
GO

-- NON_TRANSACTED_ACCESS = FULL omogućava Windows pristup fajlovima
-- DIRECTORY_NAME je ime Windows share-a koji će biti kreiran

-- Kreiranje FileTable-a
CREATE TABLE Documents.DocumentStore AS FileTable
WITH (
    FileTable_Directory = 'DocumentStore', -- Ime foldera u Windows share-u
    FileTable_Collate_Filename = database_default
);
GO

-- FileTable automatski kreira sljedeće kolone:
-- - stream_id (UNIQUEIDENTIFIER, ROWGUIDCOL) - jedinstveni ID
-- - file_stream (VARBINARY(MAX), FILESTREAM) - stvarni fajl
-- - name (NVARCHAR(255)) - ime fajla
-- - path_locator (HIERARCHYID) - pozicija u hijerarhiji
-- - parent_path_locator (HIERARCHYID) - parent folder
-- - file_type (NVARCHAR(255)) - ekstenzija fajla
-- - cached_file_size (BIGINT) - veličina fajla
-- - creation_time (DATETIME2) - vrijeme kreiranja
-- - last_write_time (DATETIME2) - vrijeme zadnje izmjene
-- - last_access_time (DATETIME2) - vrijeme zadnjeg pristupa
-- - is_directory (BIT) - da li je folder
-- - is_archive (BIT) - archive atribut
-- - is_hidden (BIT) - hidden atribut
-- - is_readonly (BIT) - read-only atribut
-- - is_offline (BIT) - offline atribut
-- - is_system (BIT) - system atribut
-- - is_temporary (BIT) - temporary atribut

-- Metoda 1: Kroz Windows Explorer
-- Putanja: \\ServerName\MyFileTableShare\DocumentStore\
-- Možete kopirati, brisati, i mijenjati fajlove direktno iz Windows-a

-- Metoda 2: Kroz T-SQL (kao običnu tabelu)
SELECT 
    name,
    file_type,
    cached_file_size,
    creation_time,
    file_stream
FROM Documents.DocumentStore
WHERE is_directory = 0; -- Samo fajlovi, ne folderi
GO

-- Metoda 3: Umetanje fajla kroz T-SQL
INSERT INTO Documents.DocumentStore (name, file_stream)
SELECT 
    'MyDocument.pdf',
    BulkColumn
FROM OPENROWSET(BULK 'C:\Temp\MyDocument.pdf', SINGLE_BLOB) AS Document;
GO

-- Metoda 4: Kreiranje foldera
INSERT INTO Documents.DocumentStore (name, is_directory)
VALUES ('MyFolder', 1);
GO

-- Korak 1: Kreirati Full-Text Catalog
-- Catalog je kontejner za Full-Text indekse
CREATE FULLTEXT CATALOG ft_ProductCatalog 
WITH ACCENT_SENSITIVITY = ON
AS DEFAULT; -- Ovo čini katalog default-om za bazu
GO

-- ACCENT_SENSITIVITY = ON znači da razlikuje "č" od "c"
-- ACCENT_SENSITIVITY = OFF tretira "č" i "c" isto

-- Važno: Tabela MORA imati jedinstveni indeks (obično PRIMARY KEY)
-- Prije kreiranja Full-Text Index-a, provjerite da tabela ima unique index

-- Kreiranje Full-Text Index-a na koloni ReviewBody
CREATE FULLTEXT INDEX ON dbo.ProductReview (
    ReviewBody LANGUAGE 'Bosnian' -- Specifikacija jezika
)
KEY INDEX PK_ProductReview -- Jedinstveni indeks na tabeli
ON ft_ProductCatalog -- Catalog gdje će se čuvati
WITH (
    CHANGE_TRACKING AUTO, -- Automatsko praćenje promjena
    STOPLIST = SYSTEM -- Koristi sistem stop listu (riječi koje se ignoriraju)
);
GO

-- LANGUAGE opcije:
-- 'Bosnian', 'Croatian', 'Serbian' - za našu regiju
-- 'English' - za engleski
-- 0 ili 'Neutral' - bez jezičke analize

-- CHANGE_TRACKING opcije:
-- AUTO - automatsko praćenje promjena (preporučeno)
-- MANUAL - ručno ažuriranje sa ALTER FULLTEXT INDEX ... START UPDATE POPULATION
-- OFF - bez praćenja promjena

-- Pronalaženje riječi
SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE CONTAINS(ReviewBody, 'kvalitet');
GO

-- Pronalaženje tačne fraze
SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE CONTAINS(ReviewBody, '"odličan proizvod"');
GO

-- Boolean operatori (AND, OR, NOT)
SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE CONTAINS(ReviewBody, 'kvalitet AND cijena');
GO

SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE CONTAINS(ReviewBody, 'kvalitet OR performanse');
GO

SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE CONTAINS(ReviewBody, 'kvalitet AND NOT skup');
GO

-- Wildcard pretraga (mora biti na kraju)
SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE CONTAINS(ReviewBody, 'prog*'); -- Naći će "program", "programiranje", "programer"
GO

-- NEAR - pronalazi riječi blizu jedna drugoj
SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE CONTAINS(ReviewBody, 'NEAR((kvalitet, cijena), 5)');
-- Pronalazi redove gdje su "kvalitet" i "cijena" unutar 5 riječi jedna od druge
GO

-- Weighted pretraga (rangiranje)
SELECT 
    Title, 
    ReviewBody,
    KEY_TBL.RANK AS RelevanceScore
FROM dbo.ProductReview
INNER JOIN CONTAINSTABLE(dbo.ProductReview, ReviewBody, 
    'ISABOUT(kvalitet WEIGHT(0.8), cijena WEIGHT(0.2))') AS KEY_TBL
    ON dbo.ProductReview.ReviewID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO

-- Inteligentna pretraga - razumije različite oblike riječi
SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE FREETEXT(ReviewBody, 'trčanje');
-- Naći će: "trčanje", "trčao", "trčati", "trkač", itd.
GO

-- Pretraga sa više riječi
SELECT Title, ReviewBody
FROM dbo.ProductReview
WHERE FREETEXT(ReviewBody, 'kvalitet proizvoda i cijena');
-- FTS će razumjeti da tražite dokumente o kvalitetu, proizvodima i cijeni
GO

-- FREETEXT sa rangiranjem
SELECT 
    Title,
    ReviewBody,
    KEY_TBL.RANK AS RelevanceScore
FROM dbo.ProductReview
INNER JOIN FREETEXTTABLE(dbo.ProductReview, ReviewBody, 
    'kvalitet proizvoda i cijena') AS KEY_TBL
    ON dbo.ProductReview.ReviewID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO

-- CONTAINSTABLE vraća tabelu sa KEY i RANK kolonama
SELECT 
    pr.ReviewID,
    pr.Title,
    pr.ReviewBody,
    ct.RANK AS RelevanceScore
FROM dbo.ProductReview pr
INNER JOIN CONTAINSTABLE(dbo.ProductReview, ReviewBody, 
    'kvalitet AND cijena', 10) AS ct -- Top 10 rezultata
    ON pr.ReviewID = ct.[KEY]
ORDER BY ct.RANK DESC;
GO

-- Napredna pretraga sa više kolona
SELECT 
    pr.ReviewID,
    pr.Title,
    pr.ReviewBody,
    ct.RANK
FROM dbo.ProductReview pr
INNER JOIN CONTAINSTABLE(dbo.ProductReview, 
    (Title, ReviewBody), -- Pretraga u više kolona
    'kvalitet', 20) AS ct
    ON pr.ReviewID = ct.[KEY]
WHERE ct.RANK > 50 -- Samo relevantni rezultati
ORDER BY ct.RANK DESC;
GO

-- Kreiranje custom stop liste
CREATE FULLTEXT STOPLIST MyCustomStoplist;
GO

-- Dodavanje riječi u stop listu
ALTER FULLTEXT STOPLIST MyCustomStoplist
ADD 'riječ1' LANGUAGE 'Bosnian';
ALTER FULLTEXT STOPLIST MyCustomStoplist
ADD 'riječ2' LANGUAGE 'Bosnian';
GO

-- Korištenje custom stop liste u Full-Text Index-u
ALTER FULLTEXT INDEX ON dbo.ProductReview
SET STOPLIST MyCustomStoplist;
GO

-- Vraćanje na sistem stop listu
ALTER FULLTEXT INDEX ON dbo.ProductReview
SET STOPLIST SYSTEM;
GO