SQL Server - Full-Text Indexing
Ez a cikk az SQL Server 2000 (2005) teljes szöveges keresési képességeiről szól. Ez egy könnyen használható, gyors és bővíthető szolgáltatás, mely lehetőséget ad több fajta dokumentum indexelésére. Indexelhető dokumentumok például: word, excel, adobe pdf, html, stb...
Előfeltételek
Ahhoz, hogy a mintát kitudja próbálni, rendelkeznie kell Microsoft SQL Server 2000 hozzáféréssel (legalább), azon egy adatbázissal és adatbázis tulajdonos (dbowner) jogosultsággal. Szintén szüksége lesz az SQL Server kliens eszközeire.
Tábla készítése
Ha egy adattáblát szeretnénk indexelni, két mezőt kell létrehoznunk a táblán. Az első mezőn a dokumentum tartalmát fogjuk tárolni bináris formátumban, míg a másikon a fájl kiterjesztését, például .doc, .xls. Szintén hasznos lehet, ha a táblán tároljuk a fájlok teljes nevét és méretét is, valós alkalmazásokban/helyzetekben, mert ezekre szükség lehet, bár a teljes szöveges keresés szempontjából szükségtelenek.
A fájlméretek tárolása opcionális, mert bármikor lekérdezhető a DataLength függvénnyel, de ez sok időt vehet igénybe, sokkal többet, mintha ezeket az értékeket eltárolnánk mezőkben.
A táblát létrehozó script a következő lehet:
CREATE TABLE [dbo].[Doc] (
[ID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Extension] [varchar] (10) NOT NULL ,
[Content] [image] NOT NULL ,
[FileSize] [int] NOT NULL ,
[FileName] [nvarchar] (500) NOT NULL ,
[Stamp] [timestamp] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Doc] WITH NOCHECK ADD
CONSTRAINT [PK_Doc] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Doc] ADD
CONSTRAINT [DF_Doc_ID] DEFAULT (newid()) FOR [ID]
GO
Egy egyszerű ötlet: használjuk egyedi azonosítót (uniqueidentifier) elsődleges kulcsnak a dokumentum táblán. Ez sok esetben hasznos lehet mondjuk egy webfejlesztő esetében. Lehetséges, hogy az azonosítót url paraméterként szeretnénk használni és nem szeretnénk megadni a lehetőséget a felhasználóknak, hogy egyszerű url módosítással letöltögethessék az összes dokumentumainkat, anélkül, hogy megnéznék például az összes hirdetésünket.
Vegye észre, hogy a script létrehozott egy elsődleges kulcsot (primary key) is. Ez mindig nagyon fontos mert ez garantálja, hogy nem fog két rekord ugyanarra az azonosítóra hallgatni. (az adattábla egy halmaz, amiben nem tudnánk egyébként különbséget tenni két azonos azonosítójú rekord között halmazműveletekkel). Egy ténylegesen jó clustered index jócskán növeleti a SELECT utasításaink sebességét.
Szintén elhelyeztünk egy timestamp mezőt is az adattáblán, amire szükségünk lesz a későbbiekben. Ennek jelentőségét a későbbiekben tárgyaljuk.
Full-Text index létrehozása
Véleményem szerint senki sem vágja fejből a full-text index létrehozó sql utasításokat és tárolt eljárásokat, ezért a Microsoft SQL Server 2000 Enterprise Manager-t fogjuk erre a célra használni.
Először is válasszuk ki benne szerverünket, majd a kiszemelt adatbázist és az belül kattintsunk a táblákra. Keressük ki a már létrehozott táblát (Doc).
Kattinsunk jobb egérgombbal a tábla nevére és válasszuk a “Define Full-Text indexing on a table” almenüt a “Full-Text Index table” menüből.
Ezután egy varázsló fog megjelenni. A “Select Table Columns” lépésben válasszuk ki a Content oszlopot, majd ennek a sorában a Document type oszlopben válasszuk ki az Extension mezőt. Lépjünk le a sorról, mert a Next gomb csak ekkor válik aktívvá.
Ebben a lépésben megadtuk az indexelendő oszlopot és azt is, hogy a szerver hol találja meg az indexelendő dokumentum típusát (kiterjesztését).
A “Select a Catalog” lépésben új full-text katalógust készíthetünk, vagy választhatunk egy már meglévőt is. Azt ajánlom, hogy hozzunk létre egy újat ezeknek az adatoknak, mert az összegyűjtött index állomány igencsak nagy lehet, ha nő a dokumentumok száma.
A “Select or Create Population Schedules” lépésben be lehet állítani a növekményes és a teljes indexdeffiníció frissítést a táblán, vagy a katalóguson. Jelenleg ez üresen hagyható, mert mi az sql szerver change tracking tulajdonságát kívánjuk kihasználni. Ez azt jelenti, hogy beállítjuk az sql szerverben, hogy amikor insert vagy update művelet történik a táblán, lehetőség szerint azonnal frissítse az indexeit is.
Valós helyzetekben ajánlott a teljes populációt minden héten vagy gyakrabban akár naponta, a növekményeset pedig naponta vagy gyakrabban futtatni a szerver kihasználtságtól függően. Ez azért szükséges, mert néhány utasítás használata esetén (WriteText és UpdateText) az indexek nem frissülnek.
Amikor a táblát terveztük, beletettünk egy timestamp mezőt. Erre azért van szükség, mert a teljes szöveges kereső motor ez alapján veszi észre a változásokat növekményes (incremental) indexelésnél. Ezért van az is, hogy a WriteText és UpdateText kifejezések működését a full-text engine veszi észre, mert ezek a műveletek nem módosítják a timestamp mező értékét.
Most fejezzük be a varázsló használatát néhány Next és a Finish gomb megnyomásával.
Most, hogy a full-text index deffiniálva lett, bekapcsoljuk a change tracking funkciót. Jobb egérgombbal kattintsunk a tábla nevére az Enterprise Managerben és válasszuk a "Change Tracking" almenüt a "Full-Text Index Table" menüből, majd ugyanonnan az "Update Index in Background" opciót. Ettől az időponttól kezdve a full-text index motor figyelni fogja a táblánk timestamp mezőjét változások után és frissíteni fogja az indexet, ha szükséges.
Az eredménye a kattintgatásunknak:
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name = N'Doc')exec sp_fulltext_catalog N'Doc', N'create'
GO
exec sp_fulltext_table N'[dbo].[Doc]', N'create', N'Doc', N'PK_Doc'
GO
exec sp_fulltext_column N'[dbo].[Doc]', N'Content', N'add', 1033, N'Extension'
GO
exec sp_fulltext_table N'[dbo].[Doc]', N'activate'
GO
Tesztelés
Szükségünk lesz pár rekordra, ha tesztelni akarjuk az indexelést. Ehhez le kell futtatnunk a következő lekérdezést a Query Analyzerben:
INSERT INTO [DOC] ([Extension], [Content], [FileSize], [FileName])
VALUES ('.txt', 'Hello John! It''s me: Garfield!', 30, 'Cartoon1.txt')
INSERT INTO [Doc] ([Extension], [Content], [FileSize], [FileName])
VALUES ('.txt', 'Oh my god!', 30, 'Shout.txt')
INSERT INTO [DOC] ([Extension], [Content], [FileSize], [FileName])
VALUES ('.txt', 'NOWAN’s web site: http://www.nowan.hu/', 30, 'nowan.txt')
Ezeket az egyszerű INSERT utasításokat saját magunk is megvizsgálhatjuk. A rekordok beszúrása után a SELECT kifejezéssel ellenőrízhetjük a tábla tartalmát:
SELECT * FROM [Doc]
Full-Text Keresés
A teljes szöveges kereséseket tartalmazó lekérdezésekhez készítéséhez meg kell ismerkednünk a következő kulcsszavakkal:
Az első kettő kifejezésnek két paramétere van. Az első egy oszlopnév, míg a második a keresett szöveg. A függvények egy igaz-hamis értéket adnak vissza.
A második két kifejezés sokkal érdekesebb. Ezek egy táblával térnek vissza, melynek két oszlopa van: Key és Rank. Ez azt jelenti, hogy vissza tudjuk kapni az egyedi azonosítóját a keresett rekordnak, vagy rekordoknak és vissza tudjuk kapni a találati valószínűséget is (Rank):
SELECT * FROM ContainsTable([doc], Content, '"nowan"')
Másik jó tulajdonsága a containstable és a freetexttable utasításoknak, hogy bonyolult kifejezéseket is megadhatunk keresett szövegként. Használhatjuk például az OR és az AND logikai kulcsszavakat is:
Természetesen a kifejezések által visszaadott tábla JOIN utasítással hozzáfűzhető valós táblákhoz is. Ha például vissza akarjuk kapni az eredeti rekordot az eredeti táblából használhatjuk a következő kifejezést:
SELECT Doc.* FROM [Doc]
INNER JOIN ContainsTable([doc], Content, '"nowan"') AS FT
ON Doc.ID = FT.[Key]
Indexelt típusok
Az SQL Server alapvetően a szöveges típusokat és a Microsoft Office formátumait képes indexelni. Ez a tudása azonban bővíthető az anno még kezdetben az indexing service-hez készített iFilterek telepítésével. Ilyen letölthető az Adobe webhelyéről is pdf fájlokhoz.
|