Sqlite Overview

使用 select typeof (xx) 来查看 storage class

• integer

61

• real

61.0

• text

"a"

• blob

x'61'

distinct 可以同时修饰多个字段

select distinct id, name from xxx;

表示只有 (id,name) 都相同时才算相同

• for `select`

select _id, (select name from xx where _id=f._id ) from xx as f;

• for `from`

select _id from (select _id,name from xxx);

• for `order by`

select _id from xxx as f order by (select score from xxx where _id=f._id);

• for `where'

select _id from xxx where _id in (select _id from xxx);

Note:

• sub-query 几乎可以用在任何地方
• sub-query 通常需要设置别名
• sub-query 只能返回一列.
• sub-query 有时需要返回一行 (例如在 order by 的场合), 这时若返回多行, 则系统只会使用第一行.

compound query 要求各个查询返回相同的例, 且只能在 compound query 最后有一个 order by

used to transform column values

case value
  when x then value_x
  when y then value_y
  when z then value_z
  else default_value
end

select name,(select
              case
              when count(*) > 4 then 'Very High'
              when count(*) = 4 then 'High'
              when count(*) in (2,3) then 'Moderate'
              else 'Low'
              end
              from foods_episodes
              where food_id=f.id) as frequency
from foods f
where frequency like '%High'

• last_insert_rowid()

• coalesce (x, y, z, …) return the first not null value, or null

  e.g. coalesce (null, 1, 2, null) returns 1

• ifnull (x, y) ifnull (x, y) <==> coalesce(x, y) e.g. SELECT Name, IFNULL(Age, 'unknown') AS Age FROM People
• nullif (x, y) 若 x,y 相同返回 null, 否则返回 x. e.g. SELECT COUNT(NULLIF(Bonus, 0)) FROM Employees
• glob
• like
• substr
• trim
• ltrim
• rtrim
• instr
• quote
• length
• lower
• upper
• abs
• max
• min
• random
• replace
• hex
• round
• date

• avg
• sum
• total
• max
• min
• count (x)
• count (*)

例如, 若 A 表为 3 条, 其中 _id 分别为 1,2,3. B 表为 4 条, _id 分别为 5,6,7,8, 则:

• select * from A, B where A._id = B._id 返回 0 条
• select * from A full outer join B on A._id = B._id 返回 7 条 (此处为假设 full-outer-join 是支持的)

• haveing 发生在 group by 之后, 而 where 发生在 group by 之前;
• 可以使用 aggregation 函数

select name, id from xxx group by id;

同理适用于 aggregation functions, 例如:

select *, count(*) from xxx;

这一点需要与 integer primary key 区别

storage class 与列的定义无关, 它取决了输入的数据的格式: 61, 61,0, "a", x'61', null 的 storage class 分别为 integer, real, text, blob, null

不同的 storage class 的排序规则: null < integer = real < text < blob

see also

(x,y,z)三个字段的索引,使用 x, x and y, x and y and z 会使用索引, 但 y, y and z, z 不会.

create index m1_index on foo(x); create index m1_index on foo(y); select x from foo where x="a" or y="a"; 0|0|0|SEARCH TABLE foo USING INDEX m1_index (x=?) (~10 rows) 0|0|0|SEARCH TABLE foo USING INDEX m2_index (y=?) (~10 rows)

• glob "a*"
• like (测试了下似乎 sqlite 并不会对 like 进行优化, 可能和版本有关?)
• 多列索引时有些情况
• select xx from xx where length(x)=5, 等

当 commit 一个 write transaction 时, rollback journal 的作法是先刷新所有修改到 journal, 然后再刷新 cache 到数据库. 而 WAL 的作法是只将修改刷新到 wal journal 中, 而不修改数据库. 只有当用户调用 pragma wal_checkpoint 或 超过 1000 页被修改时, wal journal 中的修改才会被写回到数据库.

为了使 read transaction 能读到最新的数据, 每个 read transaction 开始时会扫描 wal journal, 并在 wal file 中记下一个 mark point, 在读数据库时, 根据一个 wal index shm 判断要读的 page 是否在 wal journal 中, 若存在, 并且不超过 mark point, 则使用 wal journal 中的数据, 否则使用数据库中的数据. 这样可以使得读写不用互相等待. (但这样有一点问题, 就是无法保证读到最新的数据, 因为有可能在读的过程中, 又有新的数据追加到 wal journal file 中 makr point 之后)

http://en.wikipedia.org/wiki/Stemming

A stemmer for English, for example, should identify the string "cats" (and possibly "catlike", "catty" etc.) as based on the root "cat".and "stemmer", "stemming", "stemmed" as based on "stem".

A stemming algorithm reduces the words "fishing", "fished", "fish", and "fisher" to the root word, "fish". On the other hand, "argue", "argued", "argues", "arguing", and "argus" reduce to the stem "argu"

android contacts provider 就是使用的一种叫做 porter 的 stemmer 算法, 所以用 cat 可以查找到名为 catty 的人也是正常的.

全文检索是基于`关键字`匹配的检索, 与传统的 grep 等检索方法不同, 全文检索需要先建立索引文件, 建立索引文件的第一步就是将全文分解为一系列的关键字. 例如:

"A stemmer for English, for example, should identify the string "cats" (and possibly "catlike", "catty" etc.) as based on the root "cat""

这句话可能会被分解为以下的关键字:

stem, english, example, identify, string, cat, possible, base, root

tokenize 的过程主要分为三步:

1. 分词, 对英文来说, 基本就是以空格来分词
2. 抛弃一些 stop words, 如 a, for, should, and ,etc, as, on 等
3. 对剩下的非 stop words 使用 stemming 算法, 例如 cats->cat, stemmer->stem

通过分词算法确定关键词后, FTS 会使用倒排索引建立索引, 例如全文有两句话:

`今天天气怎么样. 今天天气不错. `

• 分词的结果

  今天/天气/怎么样/今天/天气/不错

• 倒排索引结果

  今天->1,0;2,0 天气->1,2;2,2 怎么样->1,4 不错->2,4

倒排索引的结果通常会以一种高效的利于查找的形式保存到索引文件中, 例如根据关键字排序, 或使用 B 树

根据索引文件格式的不同, 查找的过程有所区别, 以 B 树为例, 查找过程就是以查找字符串为 KEY 在 B 树中查找该关键字,查到的 VALUE 就是该关键字在文档中的行列位置.

全文查找速度很快,但有一个明显的缺点: 检索的效果依赖于关键字的选择.

例如,

• 使用 "tty" 无法检索到 " hello kitty "这句话
• 使用 "天天" 可能无法检索到 "今天天气不错"

• create virtual table search_index using fts3(content TEXT, tokenize=porter); tokenize 可以为 simple, porter, icu, 但默认情况下 icu tokenizer 功能没有被编译到 sqlite3 中
• select * from search_index where content match "token1 token2"
• select * from search_index where content match "tok*"