SQLite
FTS3 and FTS4
Extensions
http://www.sqlite.org/fts3.html
Slide 9
Slide 9 text
iOS 6 SDK͔Β
σϑΥϧτͰ͑Δ
Slide 10
Slide 10 text
جຊతͳ͍ํ
Slide 11
Slide 11 text
CREATE VIRTUAL TABLE article USING fts4 (title, body);
FTS4 ςʔϒϧ
Slide 12
Slide 12 text
INSERT
INSERT INTO article(title, body)
VALUES ('Apple', 'I have iPhone4 and iPhone5s');
Slide 13
Slide 13 text
جຊతͳQuery
SELECT * FROM article WHERE body MATCH 'iPhone5s';
term match
SELECT * FROM article WHERE body MATCH 'iPhone*';
term-prefix match
Slide 14
Slide 14 text
phrase match
SELECT * FROM article WHERE body
MATCH 'have iPhone*';
○I have iPhone5s
×I have Android and iPhone5s
Slide 15
Slide 15 text
ಛघͳQuery
SELECT * FROM article WHERE article MATCH 'iPhone*';
all columns
SELECT * FROM article WHERE article
MATCH 'title:Apple iPhone*';
specified column
͍͛ͨ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;
͍͛ͨ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;