SQLite ו-Full Text Search
15/05/2008 22:15 ע"י כתריאל טראוםכאשר הוספתי יכולות חיפוש ל-Jiyuu, החלטתי קודם לבדוק האם יש ל-SQLite יכולות של Full Text Searching. לשמחתי ולדאבוני, גיליתי כי יכולת כזו קיימת, אך רק כהרחבה, אשר אינה זמינה ברוב אם לא בכל ההפצות כיום. מסיבה זו החלטתי להשתמש בשאילתות סטנדרטיות עם LIKE.
כאשר בחנתי את הרחבת ה-Full Text Search גיליתי כי היא מעניינת ומלאת אפשרויות, ולכן החלטתי לחקור אותה קצת ולשתף אתכם עם המסקנות.
הקדמה קצרה
Full Text Search היא טכניקת חיפוש שמאפשרת חיפוש מהיר (בד"כ) בכמות גדולה של מסמכים או כל מקור טקסט אחר. כאשר כמות המסמכים שיש לחפש בהם היא קטנה, ביצע חיפוש רגיל (חיפוש סריאלי או סדרתי) בד"כ יספק תוצאות במהירות סבירה יחסית. אבל מה עושים כאשר יש מאות או אלפי מסמכים לחפש בהם?
Full Text Searching (או FTS) היא טכניקה אשר מחולקת ל-2 חלקים: יצירת Index וחיפוש:
- יצירת Index הוא תהליך של מיפוי הטקסט או טקסטים ויצירת Index בר-חיפוש של הטקסט.
- תהליך החיפוש הוא תהליך שליפת המידע. החיפוש עצמו לא ניגש לטקסטים אלא ל-Index שנוצר מבעוד מועד ומאפשר שליפה מהירה של הטקסט, בלי סריקה מלאה שלו.
דוגמה קלאסית מעולם הקוד הפתוח לאפליקציית Full Text Searching היא Beagle אשר משתמש במנוע Indexing בשם Lucene, ליצירת Index של מידע אישי ואחזור מהיר שלו (מסמכים\אימיילים ועוד).
FTS ו-SQLite
בעולם מסדי הנתונים, שימוש ב-FTS אינו קיים או מוגדר כתקן (ANSI SQL), ובד"כ אם יש אפשרות כזו, זו היא הרחבה של מסד הנתונים.
יש הטוענים, ובצדק, כי מסד נתונים הוא אינו המקום לאחסן כמויות או את סוג המידע שיצריך שימוש ב-FTS. לראיה, מספר הפרויקטים\מוצרים (פתוחים או לא) אשר מיישים FTS לאחזור מידע וטקסט (ואינם מסדי נתונים) הוא די גדול.
אבל אני טוען, כי בימינו, כמות המידע אשר נדחס לתוך מסדי נתונים הולך וגדל. עם העליה של כמות הבלוגים והתוכן בהם, כמות אתרי החדשות והתוכן בהם וכו', השימוש ב-FTS כבר לא כל כך מופרך במסדי נתונים.
SQLite, כמו מסדי נתונים אחרים, מיישם גם הוא FTS, כהרחבה, אשר לצערי עדיין אינה נכללת ברוב ההפצות. קימפול של sqlite עם FTS הוא יחסית פשוט ומוסבר בקישור הבא. שימו לב כי ההוראות הן לגבי fts1 אבל נכונות גם לגרסאות החדשות יותר fts2\fts3 אשר נכללות בגרסאות האחרונות של sqlite.
בניית טבלאת FTS
לאחר בנייה של sqlite עם fts3, ניתן להתחיל לצור טבלאות אשר יבנו Index של המידע שנוסף אליהן. כדי לבנות טבלה אשר תשתמש ב-FTS משתמשים בתחביר הבא:
create virtual table email using fts3(subject, content)
פקודה זו תצור טבלה אשר יבנה לה Index מסוג FTS, ותאפשר אחזור מידע בעזרת פקודות SQL מתאימות.
הכנסת מידע
הכנסת מידע לטבלה נעשית בצורה סטנדרטית, ללא צורך בשינוי התחביר הסטנדרטי של SQLite
INSERT INTO email (subject, content) VALUES \
('meeting summary','summary for tuesday 23rd meeting \
about bugs in the soup');
INSERT INTO email (subject, content) VALUES \
('trip plans','trip plans for upcoming flight to europe');
אחזור מידע
באחזור המידע מתגלה הכח והגמישות של הרחבת ה-FTS של SQLite. ההרחבה מאפשרת שליפה לא רק מהירה יותר כאשר מדובר בכמות מסמכים גדולה, אלא גם גמישה. שליפה סטנדרטית היא בעזרת שימוש במילת הברירה MATCH:
sqlite> SELECT subject, content FROM email \ WHERE content MATCH 'soup'; meeting summary|summary for tuesday 23rd meeting about bugs in the soup
FTS מאפשר שליפות שונות נוספות, למשל שליפה עם שלילה של מילים:
sqlite> SELECT * FROM email WHERE content MATCH 'for -meeting'; trip plans|trip plans for upcoming flight to europe
מכיוון ששאילתות FTS יכולות להכיל רק MATCH אחד, ניתן גם להשתמש בשם העמודה בשאילתה:
sqlite> SELECT subject FROM email where email MATCH \ 'subject:plans content:europe'; trip plans
מכיוון שכל המידע שנשמר בטבלאות FTS הוא מידע שעובר אינדוקס מלא, לפעמים נוטים לחלק את הטבלאות ל-2: טבלאות metadata וטבלאות FTS. ניתן לאחזר מידע ע"י שימוש ב-JOIN וכך לקבל את כל המידע בשאילתה אחת. למשל הרצף הבא יחזיר את כל כותרות האימיילים והשולח שלהם שמכילים את המילה "some":
sqlite> create virtual table email using fts3(subject, content);
sqlite> create table email_meta (sender text, date integer);
sqlite> insert into email_meta values ('abc@def.com', 1210878295);
sqlite> insert into email values ('email from me','with some content');
sqlite> select sender, subject from email_meta join email_body \
on email_meta.rowid = email_body.rowid where content match 'some';
abc@def.com|email from me
אלו הן, על קצה המזלג, היכולות של הרחבת ה-FTS של SQLite. ניתן למצוא מידע נוסף בויקי של SQLite
