Back to Details

how-to-store-sqlite-as-nosql-store

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

How to store SQLite as NoSQL Store

如何将SQLite用作NoSQL存储

SQLite is a very capable edge database that can store various shapes of data.
NoSQL databases are very popular due to the schema-less nature of storing of the data but it is totally possible to store these documents in SQLite.
SQLite actually has great JSON support and even supports JSONB.
SQLite是一款功能强大的边缘数据库，能够存储各种格式的数据。
NoSQL数据库因其无模式的数据存储特性而广受欢迎，但实际上我们完全可以在SQLite中存储这类文档。
SQLite实际上具备出色的JSON支持，甚至还支持JSONB。

Create the table 

创建表

To store JSON documents we need to create a table to store the values as strings.
CREATE TABLE documents (
  path TEXT NOT NULL PRIMARY KEY,
  data TEXT,
  ttl INTEGER,
  created INTEGER NOT NULL,
  updated INTEGER NOT NULL,
  UNIQUE(path)
);
path
data
ttl
created
updated
/posts/1
{"id":1}
NULL
0
0
/posts/2
{"id":2}
NULL
0
0
/users/1
{"id":1}
NULL
0
0
The basic idea is to store a JSON object and an unique path.
There is an optional TTL to automatically delete rows when they reach the stale date.
要存储JSON文档，我们需要创建一个表来将值以字符串形式存储。
CREATE TABLE documents (
  path TEXT NOT NULL PRIMARY KEY,
  data TEXT,
  ttl INTEGER,
  created INTEGER NOT NULL,
  updated INTEGER NOT NULL,
  UNIQUE(path)
);
path
data
ttl
created
updated
/posts/1
{"id":1}
NULL
0
0
/posts/2
{"id":2}
NULL
0
0
/users/1
{"id":1}
NULL
0
0
核心思路是存储一个JSON对象和一个唯一路径。 我们还可以设置可选的TTL（存活时间）字段，当记录达到过期日期时自动删除。

Save a document 

保存文档

To save a document we can encode our JSON as a string or binary and save in in the table with a unique path.
INSERT OR REPLACE 
INTO documents (path, data, ttl, created, updated) 
VALUES (:path, :data, :ttl, :created, :updated)
RETURNING *;
You can also use JSON functions to save the Object to a valid JSON string.
INSERT OR REPLACE 
INTO documents (path, data, ttl, created, updated) 
VALUES ("/posts/1", json('{"id" 1}'), NULL, 0, 0)
RETURNING *;
path
data
ttl
created
updated
/posts/1
{"id":1}
NULL
0
0
要保存文档，我们可以将JSON编码为字符串或二进制格式，然后连同唯一路径一起存入表中。
INSERT OR REPLACE 
INTO documents (path, data, ttl, created, updated) 
VALUES (:path, :data, :ttl, :created, :updated)
RETURNING *;
你也可以使用JSON函数将对象保存为有效的JSON字符串。
INSERT OR REPLACE 
INTO documents (path, data, ttl, created, updated) 
VALUES ("/posts/1", json('{"id" 1}'), NULL, 0, 0)
RETURNING *;
path
data
ttl
created
updated
/posts/1
{"id":1}
NULL
0
0

Reading a document 

读取文档

To read a document we just need the path. If a TTL is set we can calculate if the current date is greater than the offset and not return the document.
SELECT * FROM documents 
WHERE path = :path
AND (
	(ttl IS NOT NULL AND ttl + updated < unixepoch())
	OR
	ttl IS NULL
);
path
data
ttl
created
updated
/posts/1
{"id":1}
NULL
0
0
要读取文档，我们只需要对应的路径。如果设置了TTL，我们可以计算当前日期是否超过过期时间，若过期则不返回该文档。
SELECT * FROM documents 
WHERE path = :path
AND (
	(ttl IS NOT NULL AND ttl + updated < unixepoch())
	OR
	ttl IS NULL
);
path
data
ttl
created
updated
/posts/1
{"id":1}
NULL
0
0

Get documents for a collection 

获取集合下的所有文档

We can query all the docs for a given collection using some built-in functions and a path prefix:
SELECT *
FROM documents 
WHERE (
	path LIKE :prefix
	AND
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) = (LENGTH(:prefix) - LENGTH(REPLACE(:prefix, '/', '')))
)
AND (
	(ttl IS NOT NULL AND ttl + updated < unixepoch())
	OR
	ttl IS NULL
)
ORDER BY created;
It is expected to search for a :prefix with the 
/%
 at the end:
"/my/path/%" // search for /my/path
我们可以使用内置函数和路径前缀来查询指定集合下的所有文档：
SELECT *
FROM documents 
WHERE (
	path LIKE :prefix
	AND
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) = (LENGTH(:prefix) - LENGTH(REPLACE(:prefix, '/', '')))
)
AND (
	(ttl IS NOT NULL AND ttl + updated < unixepoch())
	OR
	ttl IS NULL
)
ORDER BY created;
查询时需要在
:prefix
末尾加上
/%
：
"/my/path/%" // 搜索 /my/path 下的文档

Deleting expired documents 

删除过期文档

Using the TTL field we can delete all expired documents:
DELETE FROM documents
WHERE ttl IS NOT NULL
AND ttl + updated < unixepoch();
利用TTL字段，我们可以删除所有过期的文档：
DELETE FROM documents
WHERE ttl IS NOT NULL
AND ttl + updated < unixepoch();

Demo

演示