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.7.0)	2025-01-08
Redis 6.2.17	redis(4.7.0)	2025-01-08
瓦尔基 8.0.2	redis(4.7.0)	2025-01-08
Garnet 1.0.49	redis(4.7.0)	2025-01-08

概述

¥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 $(brew config | grep HOMEBREW_PREFIX | awk '{print $2}')/etc/redis.conf

Redis 6

Redis 6 was installed with:

brew install redis@6.2

The following command started the server process:

redis-server $(brew config | grep HOMEBREW_PREFIX | awk '{print $2}')/etc/redis.conf

When this demo was last tested, brew install redis installed a proprietary version of Redis.

Garnet

After installing dotnet and .NET runtime, install the garnet-server tool:

dotnet tool install --global garnet-server
export PATH="$PATH:~/.dotnet/tools"
garnet-server

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)

The order of lines may be different in different servers. The following output is from KeyDB 6.3.4:

Output from KeyDB 6.3.4

SET [ 'baz', '0' ]
SET [ 'foo', 'bar' ]
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 }
  ]
]
SADD [ 'birdpowers', [ 'pecking', 'flight' ] ]
SADD [ 'superpowers', [ 'x-ray vision', 'flight' ] ]
HSET [
  'user:1001',
  {
    name: 'Mary Jones',
    email: 'mjones@example.com',
    password: 'hunter2'
  }
]
HSET [
  'user:1000',
  {
    name: 'John Smith',
    email: 'john.smith@example.com',
    password: 's3cret',
    visits: '1'
  }
]

打开 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.