Redis 数据库封装在 Sheets 中

Redis 已重新获得开源许可！

¥Redis has relicensed away from open source!

原来的 BSD-3 条款仍然适用于版本 7.2.4。

¥The original BSD-3-Clause still applies to version 7.2.4.

该演示已使用 KeyDB 和其他支持 "Redis 序列化协议" (RESP) 的服务器进行了测试。

¥This demo has been tested with KeyDB and other servers that support the "Redis serialization protocol" (RESP).

KeyDB 是一个与 Redis 兼容的内存数据存储。它能够存储集合、列表和其他简单的数据结构。

¥KeyDB is a Redis-compatible in-memory data store. It is capable of storing sets, lists and other simple data structures.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

该演示定义了用于在电子表格中存储 Redis 数据库的架构。我们将探讨如何使用 SheetJS 和 Redis NodeJS 连接器模块将数据从 XLSX 文件提取到 Redis 数据库，并将数据库序列化到工作簿。

¥This demo defines a schema for storing Redis databases in spreadsheets. We'll explore how to use SheetJS and Redis NodeJS connector modules to pull data from XLSX files to a Redis database and to serialize a database to a workbook.

测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

服务器	连接器模块	日期
密钥数据库 6.3.4	redis(4.6.13)	2024-04-23
Redis 6.2.14	redis(4.6.13)	2024-04-23
Redis 7.2.4	redis(4.6.13)	2024-04-23
瓦尔基 7.2.5	redis(4.6.13)	2024-04-23

概述

¥Overview

Redis 有 5 种核心数据类型："字符串"，List","Set","Sorted Set"， 和"Hash”。由于键和值仅限于简单的字符串（和数字），因此可以在单个工作表中存储完整的数据库。

¥Redis has 5 core data types: "String", List", "Set", "Sorted Set", and "Hash". Since the keys and values are limited to simple strings (and numbers), it is possible to store complete databases in a single worksheet.

集成详情

¥Integration Details

SheetJSRedis.mjs 导出方法：

¥SheetJSRedis.mjs exports the methods:

• redis_to_ws 通过查询 redis 客户端创建 SheetJS 工作表

  ¥redis_to_ws creates a SheetJS worksheet by querying a redis client

• ws_to_redis 从 SheetJS 工作表创建查询对象数组

  ¥ws_to_redis creates an array of query objects from the SheetJS worksheet

数组的数组

¥Array of Arrays

共享数据表示形式是 "数组的数组"[^1]。结构中的每个数组对应一行。

¥The shared data representation is an "array of arrays"[^1]. Each array within the structure corresponds to one row.

Redis 到 SheetJS 转换器通过运行查询从数据库获取数据来生成数据数组的数组。SheetJS aoa_to_sheet 和 sheet_add_aoa[^2] 方法从数组数组构建工作表。创建工作表后，可以将其添加到 SheetJS 工作簿对象 [^3] 中并使用 writeFile[^4] 导出。

¥The Redis to SheetJS converter generates an array of arrays of the data by running queries to fetch data from the database. The SheetJS aoa_to_sheet and sheet_add_aoa[^2] methods build up worksheets from arrays of arrays. Once the worksheet is created, it can be added to a SheetJS workbook object[^3] and exported using writeFile[^4].

SheetJS 到 Redis 转换器的工作原理相反。使用 SheetJS readFile 方法 [^5] 解析工作簿文件，并从工作簿对象中提取所需的工作表。可以使用 sheet_to_json[^6] 实用函数创建数组的数组。可以扫描数据结构以生成 Redis 查询。

¥The SheetJS to Redis converter works in reverse. Workbook files are parsed with the SheetJS readFile method[^5] and the desired worksheet is pulled from the workbook object. An array of arrays can be created with the sheet_to_json[^6] utility function. The data structure can be scanned to generate Redis queries.

追加列

¥Appending Columns

由于数据是面向列的，因此目标是从数据后面的列的第一行开始添加数据。

¥Since the data is column-oriented, the goal is to add the data starting on the first row of the column after the data.

为了计算写入数据的起点，SheetJS 提供了 encode_cell 和 decode_range 实用函数来处理地址和范围 [^7]。

¥To calculate the starting point for writing data, SheetJS provides encode_cell and decode_range utility functions for working with addresses and ranges[^7].

以下代码片段采用值数组的数组，并将值从工作表范围后面的列开始写入工作表：

¥The following snippet takes an array of arrays of values and writes the values to a worksheet starting from the column after the worksheet range:

function add_aoa_to_next_column(worksheet, aoa) {
  /* get range of worksheet */
  const range = XLSX.utils.decode_range(worksheet["!ref"])
  /* the origin to write new data will start in the column after the range */
  const origin = XLSX.utils.encode_cell({
    r: 0, // start on first row
    c: range.e.c + 1 // column after end
  });
  /* add data */
  XLSX.utils.sheet_add_aoa(worksheet, aoa, { origin });
}

字符串

¥Strings

字符串可以存储在统一的字符串表中。第一列保存键，第二列保存值：

¥Strings can be stored in a unified String table. The first column holds keys and the second column holds values:

XXX|    A    |   B   |
---+---------+-------+
 1 | Strings |       |
 2 |         |       |
 3 | Hello   | World |
 4 | Sheet   | JS    |

字符串表的 SheetJS 数组表示是一个键/值对数组。

¥The SheetJS array-of-arrays representation of the string table is an array of key/value pairs.

可以通过使用 KEYS[^8] 方法查询所有键、使用 TYPE[^9] 方法测试相应的值是否为字符串以及使用 GET[^10] 方法获取字符串值，从 Redis 生成这些对：

¥The pairs can be generated from Redis by querying for all of the keys using the KEYS[^8] method, testing if the corresponding value is a string using the TYPE[^9] method, and fetching string values using the GET[^10] method:

const aoa = ["Strings"]; aoa.length = 2; // [ "Strings", empty ]
const keys = await client.KEYS("*");
for(let key of keys) {
  const type = await client.TYPE(key);
  if(type == "string") aoa.push([key, await client.GET(key)]);
}

列表

¥Lists

列表是一维的，可以存储在自己的列中。

¥Lists are unidimensional and can be stored in their own columns.

XXX|    C    |
---+---------+
 1 | List    |
 2 | List1   |
 3 | List1V1 |
 4 | List1V2 |

SheetJS 的数组数组表示列表是一列值。

¥The SheetJS array-of-arrays representation of lists is a column of values.

Redis LRANGE[^11] 返回一个简单的值数组。sheet_add_aoa 将结果解释为一行，因此应转置数据。

¥Redis LRANGE[^11] returns a simple array of values. sheet_add_aoa interprets the result as one row, so the data should be transposed.

该代码将结果转置为 values.map(v => [v])。

¥The code transposes the result with values.map(v => [v]).

const values = await client.LRANGE(key, 0, -1);
const aoa = [ ["List"], [key] ].concat(values.map(v => [v]));

集合

¥Sets

集合是一维的，可以存储在自己的列中。

¥Sets are unidimensional and can be stored in their own columns.

XXX|   D   |
---+-------+
 1 | Set   |
 2 | Set1  |
 3 | Set1A |
 4 | Set1B |

SheetJS 数组的数组表示集是一列值。

¥The SheetJS array-of-arrays representation of sets is a column of values.

Redis SMEMBERS[^12] 返回一个值数组。sheet_add_aoa 将结果解释为一行，因此应转置数据。

¥Redis SMEMBERS[^12] returns an array of values. sheet_add_aoa interprets the result as one row, so the data should be transposed.

该代码将结果转置为 values.map(v => [v])。

¥The code transposes the result with values.map(v => [v]).

const values = await client.SMEMBERS(key);
const aoa = [ ["Set"], [key] ].concat(values.map(v => [v]));

排序集

¥Sorted Sets

排序集有一个关联的分数，可以存储在第二列中。

¥Sorted Sets have an associated score which can be stored in the second column.

XXX|    E    | F |
---+---------+---+
 1 | Sorted  |   |
 2 | ZSet1   |   |
 3 | Key1    | 1 |
 4 | Key2    | 2 |

SheetJS 数组的数组表示是键/分数对的数组。

¥The SheetJS array-of-arrays representation is an array of key/score pairs.

ZRANGE_WITHSCORES[^13] 返回一个可以重塑的对象数组。

¥ZRANGE_WITHSCORES[^13] returns an array of objects which can be reshaped.

const values = await client.ZRANGE_WITHSCORES(key, 0, -1);
const aoa = [ ["Sorted"], [key] ].concat(values.map(v => [v.value, v.score]));

哈希值

¥Hashes

哈希值的存储方式与字符串表类似，键和值列按顺序排列。

¥Hashes are stored like the string table, with key and value columns in order.

XXX|   G   |   H   |
---+-------+-------+
 1 | Hash  |       |
 2 | Hash1 |       |
 3 | Key1  | Val1  |
 4 | Key2  | Val2  |

SheetJS 数组的数组表示形式是键/值对的数组。

¥The SheetJS array-of-arrays representation is an array of key/value pairs.

HGETALL[^14] 返回一个可以使用 Object.entries 转换的对象：

¥HGETALL[^14] returns an object which can be converted using Object.entries:

const values = await client.HGETALL(key);
const aoa = [ ["Hash"], [key] ].concat(Object.entries(values));

完整示例

¥Complete Example

最新版本的 redis 节点模块不适用于大多数版本的 NodeJS。它是 "仅 ESM"，需要 NodeJS 18 或更高版本。因此，该演示还需要 NodeJS 版本 18 或更高版本。

¥The most recent version of the redis node module does not work with most versions of NodeJS. It is "ESM-only", requiring NodeJS 18 or later. As a result, this demo also requires NodeJS version 18 or later.

0. 设置并启动本地 Redis 兼容服务器。

   ¥Set up and start a local Redis-compatible server.

Redis-compatible servers (click to show)

This demo was last tested on macOS.

KeyDB

KeyDB was installed with:

brew install keydb@6.3.4

The following command started the server process:

keydb-server --protected-mode no

Valkey

Valkey was installed with:

brew install valkey

The Valkey formula will create symlinks to redis-server and other commands. This conflicts with the main redis package. redis must be unlinked:

brew unlink redis
brew link valkey

The following command started the server process:

redis-server /usr/local/etc/redis.conf

Redis 6

Redis 6 was installed with:

brew install redis@6.2

The following command started the server process:

redis-server /usr/local/etc/redis.conf

Redis 7

Redis 7 was installed with:

brew install redis@7.2

The following command started the server process:

redis-server /usr/local/etc/redis.conf

When the demo was last tested, Redis 7.2.4 was installed. The output from the Redis server will display the version number:

69385:C 23 Apr 2024 13:24:48.520 * Redis version=7.2.4, bits=64, commit=00000000, modified=0, pid=69385, just started

Please raise an issue with Homebrew if a later version is installed.

1. 下载以下脚本：

   ¥Download the following scripts:

• SheetJSRedis.mjs

• SheetJSRedisTest.mjs

curl -LO https://xlsx.nodejs.cn/nosql/SheetJSRedis.mjs
curl -LO https://xlsx.nodejs.cn/nosql/SheetJSRedisTest.mjs

2. 安装依赖：

   ¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz redis@4.6.13

3. 运行测试脚本：

   ¥Run the test script:

node SheetJSRedisTest.mjs

测试

¥Testing

检查输出并与 SheetJSRedisTest.mjs 中的数据进行比较。

¥Inspect the output and compare with the data in SheetJSRedisTest.mjs.

Expected Output (click to show)

SET [ 'baz', '0' ]
SET [ 'foo', 'bar' ]
HSET [
  'user:1000',
  {
    name: 'John Smith',
    email: 'john.smith@example.com',
    password: 's3cret',
    visits: '1'
  }
]
HSET [
  'user:1001',
  {
    name: 'Mary Jones',
    email: 'mjones@example.com',
    password: 'hunter2'
  }
]
SADD [ 'superpowers', [ 'flight', 'x-ray vision' ] ]
SADD [ 'birdpowers', [ 'flight', 'pecking' ] ]
RPUSH [ 'friends', [ 'sam', 'alice', 'bob' ] ]
ZADD [
  'hackers',
  [
    { value: 'Grace Hopper', score: 1906 },
    { value: 'Alan Turing', score: 1912 },
    { value: 'Claude Shannon', score: 1916 },
    { value: 'Alan Kay', score: 1940 },
    { value: 'Richard Stallman', score: 1953 },
    { value: 'Sophie Wilson', score: 1957 },
    { value: 'Yukihiro Matsumoto', score: 1965 },
    { value: 'Linus Torvalds', score: 1969 }
  ]
]

打开 SheetJSRedis.xlsx 并验证列是否具有正确的数据。该工作表应具有与本页顶部的屏幕截图相同的结构。

¥Open SheetJSRedis.xlsx and verify the columns have the correct data. The sheet should have the same structure as the screenshot at the top of this page.

[^1]: 见 "数组的数组" 于 "实用函数"

¥See "Array of Arrays" in "Utility Functions"

[^2]: 参见 "数组的数组输入" 于 "实用函数"。

¥See "Array of Arrays Input" in "Utility Functions".

[^3]: 见 "工作簿助手" 于 "实用函数"

¥See "Workbook Helpers" in "Utility Functions"

[^4]: 见 writeFile 于 "写入文件"

¥See writeFile in "Writing Files"

[^5]: 见 readFile 于 "读取文件"

¥See readFile in "Reading Files"

[^6]: 见 sheet_to_json 于 "实用工具"

¥See sheet_to_json in "Utilities"

[^7]: 见 "实用工具" 于 "地址和范围"

¥See "Utilities" in "Addresses and Ranges"

[^8]: 请参阅 Redis 文档中的 KEYS。

¥See KEYS in the Redis documentation.

[^9]: 请参阅 Redis 文档中的 TYPE。

¥See TYPE in the Redis documentation.

[^10]: 请参阅 Redis 文档中的 GET。

¥See GET in the Redis documentation.

[^11]: 请参阅 Redis 文档中的 LRANGE。

¥See LRANGE in the Redis documentation.

[^12]: 请参阅 Redis 文档中的 SMEMBERS。

¥See SMEMBERS in the Redis documentation.

[^13]: 官方命令是 ZRANGE。ZRANGE_WITHSCORES 是 NodeJS 封装器支持的特殊命令。

¥The official command is ZRANGE. ZRANGE_WITHSCORES is a special command supported by the NodeJS wrapper.

[^14]: 请参阅 Redis 文档中的 HGETALL。

¥See HGETALL in the Redis documentation.