SQLiteで高速全文検索〜日本語編〜

Transcript

1. 1.

   42-JUFͰ
   ߴ଎શจݕࡧ
   ʙ೔ຊޠฤʙ @shobyshoby

2. 2.

   ࣗݾ঺հ pixivͰΞϓϦͭͬͯ͘·͢

3. 3.

   ຊ୊

4. 4.

   iOSΞϓϦͰ SQLiteΛ࢖ͬͯ ೔ຊޠΛ ߴ଎ʹશจݕࡧ͍ͨ͠

5. 5.

   ֓ཁ

6. 6.

   42-JUFͷશจݕࡧ֦ு
   ೔ຊޠͷτʔΫϯ෼ׂ
   ݕࡧॱҐ
   σϞ

7. 7.

   42-JUFͷ
   શจݕࡧ֦ு

8. 8.

   SQLite FTS3 and FTS4 Extensions http://www.sqlite.org/fts3.html

9. 9.

   iOS 6 SDK͔Β σϑΥϧτͰ࢖͑Δ

10. 10.

    جຊతͳ࢖͍ํ

11. 11.

    CREATE VIRTUAL TABLE article USING fts4 (title, body); FTS4 ςʔϒϧ

12. 12.

    INSERT INSERT INTO article(title, body) VALUES ('Apple', 'I have iPhone4

    and iPhone5s');
13. 13.

    جຊతͳQuery SELECT * FROM article WHERE body MATCH 'iPhone5s'; term

    match SELECT * FROM article WHERE body MATCH 'iPhone*'; term-prefix match
14. 14.

    phrase match SELECT * FROM article WHERE body MATCH 'have

    iPhone*'; ◦I have iPhone5s ×I have Android and iPhone5s
15. 15.

    ಛघͳQuery SELECT * FROM article WHERE article MATCH 'iPhone*'; all

    columns SELECT * FROM article WHERE article MATCH 'title:Apple iPhone*'; specified column
16. 16.

    ؆୯ʂ

17. 17.

    ※ͨͩ͠ӳޠʹݶΔ

18. 18.

    ೔ຊޠͷ৔߹ɺ FTSͷTokenizer͕ ͏·͘tokenʹ෼͚ΒΕͳ͍

19. 19.

    FTSͷTokenizer͕ ͏·͘ѻ͑Δܗࣜʹ Ճ޻ͯ͠σʔλΛೖΕΑ͏ʂ

20. 20.

    ೔ຊޠͷ
    τʔΫϯ෼ׂ

21. 21.

    FTSͷςʔϒϧʹ͸ ൒֯εϖʔεͰ ෼ׂͨ͠σʔλΛೖΕΔ

22. 22.

    CREATE TABLE article (id, title, body); CREATE VIRTUAL TABLE articleTokens

    USING fts4 (articleId, titleTokens, bodyTokens); ݩσʔλΛೖΕΔςʔϒϧ FTSςʔϒϧ
23. 23.

    INSERT͍ͨ͠σʔλ INSERT INTO article(id, title, body) VALUES (1, 'Ξοϓϧ', 'ࢲ͸ΞΠϑΥʔϯ4ͱΞΠϑΥʔϯ5sΛ͍࣋ͬͯ·

    ͢'); ! INSERT INTO articleTokens(articleId, titleTokens, bodyTokens) VALUES (1, 'Ξοϓϧ', 'ࢲ͸ ΞΠϑΥʔϯ4 ͱ ΞΠϑΥʔϯ5s Λ ࣋ͬ ͍ͯ·͢');
24. 24.

    ౤͍͛ͨSELECT SELECT * FROM article JOIN ( SELECT articleId FROM

    articleTokens WHERE bodyTokens MATCH 'ΞΠϑΥʔϯ*' LIMIT 100; ) AS result ON article.id = result.articleId ORDER BY article.id;
25. 25.

    τʔΫϯ෼ׂ͢Δ

26. 26.

    CFStringTokenizer

27. 27.

    ಺෦ͰMeCabΛ࢖ͬͯ ܗଶૉղੳͯ͠ΔΒ͍͠ http://stackoverflow.com/questions/8280824/how-to-use- cfstringtokenizer-with-chinese-and-japanese

28. 28.

    - (NSArray *)tokenArrayWithString:(NSString *)string { NSLocale *locale = [[NSLocale alloc]

    initWithLocaleIdentifier:@"ja"]; CFRange range = CFRangeMake(0, CFStringGetLength((CFStringRef)string)); CFStringTokenizerRef tokenizer = CFStringTokenizerCreate(kCFAllocatorDefault, (CFStringRef)string, range, kCFStringTokenizerUnitWordBoundary, (CFLocaleRef)locale); NSMutableArray *tokenArray = [NSMutableArray array]; while(CFStringTokenizerAdvanceToNextToken(tokenizer) != kCFStringTokenizerTokenNone) { CFRange tokenRange = CFStringTokenizerGetCurrentTokenRange(tokenizer); if(range.location != kCFNotFound) { NSString *token = [string substringWithRange:NSMakeRange(tokenRange.location, tokenRange.length)]; [tokenArray addObject:token]; } } CFRelease(tokenizer); return tokenArray; }
29. 29.

    INSERT

30. 30.

    tokenArrayΛ ൒֯εϖʔεͰ࿈݁ͯ͠ INSERT͢Δ [tokenArray componentsJoinedByString:@" "];

31. 31.

    ݕࡧ

32. 32.

    ֤Tokenʹ * Λ௥Ճ͠ ൒֯εϖʔεͰ࿈݁ͯ͠ QueryΛ૊ΈཱͯΔ NSMutableArray *searchTokens = [NSMutableArray arrayWithCapacity:tokenArray.count];

    for (NSString *token in tokenArray) { [searchTokens addObject:[token stringByAppendingString:@"*"]]; } [searchTokens componentsJoinedByString:@" "];
33. 33.

    ݕࡧॱҐ

34. 34.

    Ϛον౓͕ߴ͍ॱʹ ݁ՌΛฦ͍ͨ͠

35. 35.

    SQLite͸ ಠࣗͷSQLؔ਺Λ ఆٛͰ͖Δ

36. 36.

    rank()ͱ͍͏ SQLؔ਺͕ ͋ͬͨͱ͢Δͱ…

37. 37.

    ౤͍͛ͨSELECT SELECT * FROM article JOIN ( SELECT articleId, rank(matchinfo(articleTokens,

    'pcnalx')) AS rank FROM articleTokens WHERE bodyTokens MATCH 'ΞΠϑΥʔϯ*' LIMIT 100; ) AS result ON article.id = result.articleId ORDER BY rank DESC;
38. 38.

    match_info() ! Ϛον৘ใΛฦ͢
 FTSͷؔ਺ http://www.sqlite.org/fts3.html#matchinfo

39. 39.

    match_info()͔Β ϚονείΞΛฦ͢ SQLؔ਺Λ࡞Ζ͏ʂ

40. 40.

    CݴޠͰ…

41. 41.

    ϚονείΞͷ ΞϧΰϦζϜ

42. 42.

    Okapi BM25 http://en.wikipedia.org/wiki/Okapi_BM25

43. 43.

    sqlite-okapi-bm25 https://github.com/rads/sqlite-okapi-bm25

44. 44.

    ंྠͷ࠶ൃ໌͸ ආ͚ΒΕͨ ※஫ҙ ୯Ұͷcolumnʹ͔͠ରԠ͍ͯ͠ͳ͍ͷͰ ඞཁͳΒFork͢Δ

45. 45.

    σϞ

46. 46.

    ·ͱΊ

47. 47.

    42-JUFͷ'54Λ࢖͏ͱɺߴ଎ʹ શจݕࡧͰ͖Δ
    ೔ຊޠ͸ࣄલʹ5PLFO෼ׂ͕ඞཁ
    ݕࡧॱҐ͸ಠࣗ42-ؔ਺Λ࡞ͬͯ ରԠ͢Δ

48. 48.

    ͓͠·͍