SQLite文档存储——JSON1扩展

这几天在写一个Pixiv的图片抓取器，Chrome插件和接口都写好了。项目使用Sqlite存储数据，主要是看中它的嵌入式特性，零配置，容易部署和备份。但是项目中存在很不满意的地方，就是sql部分过于复杂和臃肿，原生sql的多表查询的结果集实在是太难处理了。

例如

SELECT 
    illusts.id, 
    illusts.title, 
    illusts.description,
    illusts.author_id,
    illusts.author,
    illusts.account,
    illusts.r18,
    illusts.create_date,
    illusts.upload_date,
    tags.tag,
    pages.p, 
    pages.width, 
    pages.height, 
    pages.thumb, 
    pages.small, 
    pages.regular, 
    pages.original
FROM illusts
    LEFT JOIN illust_tags AS tags 
    ON tags.illust_id = illusts.id
    LEFT JOIN illust_pages AS pages 
    ON pages.illust_id = illusts.id
WHERE ...;

这里查询三张表illusts，tags，pages，使用Join查询返回的结果集实在是有点难解析，返回的行数是 illusts x tags x pagee。

上面的 sql 只是查询单条数据，但是返回的结果集可能有几十上百行。手动将它们解析成单个实体的逻辑相对复杂，开发成本非常高，还不如拆分为三条查询语句后再组装。而ORM框架基本上也都是这么做的，但是我个人不喜欢ORM，而且在Golang上我更倾向于使用内置的driver。

真实情况是golang上的orm库不太好用（也可能是我不会 :doge）。在Android上我还是很喜欢使用Room的……

Sqlite的文档存储能力（Json1扩展）

这不由得想到了文档存储，其实我没必要分表存储，像Mongodb一样直接存储json文档就非常方便，但是我不一定需要NoJson，因为现在很多关系型数据库已经支持文档存储了，比如Postgres，Mysql…

查看官方文档之后发现Sqlite也有这一方面的能力。Sqlite的文档存储功能依赖Json1扩展，并且以后很有可能内置到Sqlite中，而不是作为扩展存在。

性能方面官方声称Json1扩展解析Json的速度能达到1G B/s的速度。但其实我并不关心这个，真要注重性能的话我就不用Sqlite了。

Json1是一个可加载扩展，已经内置到了Sqlite中，默认是禁用状态。如果想要在Android中使用Json1需要通过NDK重新编译Sqlite。

而通过官网下载的二进制包内置的命令行工具已经启用了所有扩展功能，可以下载下来尝试下Json1，执行下面Demo的Sql试试。

Json1 使用Demo

这里不会对Json1函数的作用做额外的说明，官方文档已经非常详细了：The JSON1 Extension

根据上面的案例，我们需要记录Illust对象如下 (省略了一些字段方便演示)。

type Illust struct {
    ID    int
    Title string
    Tags  []string
    Pages []IllustPage
}

type IllustPage struct {
    Width  int
    Height int
    Url    string
}

因为Tags和Pages是Illust的内嵌结构，所以通常情况下还要再创建两张表用来存储它们，并添加外键关联，这也是导致查询语句冗长的原因之一。而文档存储则不需要这些，可以将内嵌结构的数据用json存储，而下面的Demo我打算整个Illust对象使用文档存储。

建表和索引： body列用来存储json，然后通过生成列（Generated Columns）并添加索引提高查询效率。

create table illusts(
    body text,
    id integer generated always as 
        (json_extract(body, '$.id')) virtual not null,
    r18 boolean generated always as 
        (json_extract(body, '$.tags[0]') like 'R-18') virtual not null
);

create index xid on illusts(id);
create index xr18 on illusts(r18);

这里的 not null 约束很重要，它可以校验插入的 json 的有效性，避免你胡乱的插入一些无效数据，毕竟这一列是的类型是text，插入什么都可以。
当插入数据不是有效的json，或者json中没有 id 和 tags时就会抛出Error: malformed JSON异常。

插入一条记录：

insert into illusts values(json('{
    "id":1,
    "title":"test json1",
    "tags":[
        "R-18",
        "tag2"
    ],
    "pages":[
        {
            "width":1080,
            "height":1920,
            "url":"https://example.com/test.png"
        }
    ]
}'));

查询记录：

查询所有字段：
```
select * from illusts;
```

查询url：

select json_extract(body, '$.pages[0].url') from illusts;

查询tags中包含tag2的记录：

select * from illusts where 'tag2' in (select value from json_each(body, '$.tags'));

或者使用like操作符匹配

select * from illusts, json_each(body, '$.tags') where json_each.value like 'tag2';

CURD在这里就不再赘述了，平时怎么使用sql就怎么用，Json1扩展只是提供了几个函数用来解析Json文档数据。

使用总结

文档存储不一定需要mongodb这类nosql数据库。在已经熟悉sql的情况下再去学习一套不通用的查询语言可能不是很有用。

现在大部分流行的数据库都已经支持了文档存储功能，使用熟悉的sql去操作文档数据真的有点令人兴奋，你不必为一些不共享的嵌套结构的数据创建额外的表，甚至可以像Demo中那样All in文档存储，整张表只使用一个列来存json。

Sqlite的文档存储能力（Json1扩展）#

Json1 使用Demo#

使用总结#

References#

Sqlite的文档存储能力（Json1扩展）

Json1 使用Demo

使用总结

References