使用 WebSQL 的工作表

Chrome 或 Safari 不再支持 WebSQL。

¥WebSQL is no longer supported in Chrome or Safari.

对于浏览器中的 SQL，有几种选择：

¥For SQL in the browser, there are a few alternatives:

• SQL.js 是 SQLite 的编译版本

  ¥SQL.js is a compiled version of SQLite

• AlaSQL 是一个由 IndexedDB 支持的纯 JS SQL 引擎

  ¥AlaSQL is a pure-JS SQL engine backed by IndexedDB

WebSQL（正式名称为 "Web SQL 数据库"）是一种流行的基于 SQL 的浏览器内数据库，可在 Chromium 和 Safari 中使用。实际上，它由 SQLite 提供支持。许多与 SQLite 兼容的查询都受 WebSQL 引擎支持。

¥WebSQL (formally "Web SQL Database") was a popular SQL-based in-browser database available in Chromium and Safari. In practice, it was powered by SQLite. Many SQLite-compatible queries were supported by WebSQL engines.

历史背景

Google 和 Apple 开发并支持 WebSQL。传统浏览器供应商反对标准化，并最终通过强制弃用历史悠久的 API 破坏了网络。

¥Google and Apple developed and supported WebSQL. Legacy browser vendors fought against standardization and ultimately broke the web by forcing the deprecation of the storied API.

利用新技术，许多网站都附带浏览器内 SQL 数据库。

¥Leveraging new technologies, many websites ship with an in-browser SQL database.

公共演示 https://sheetjs.com/sql 从工作簿生成数据库。

¥The public demo https://sheetjs.com/sql generates a database from workbook.

WebSQL 在服务器端平台上并不常见。通常，脚本将使用连接器模块直接查询 SQLite 数据库。

¥WebSQL is not commonly available on server-side platforms. Typically scripts will directly query SQLite databases using connector modules.

"SQLite" 演示 涵盖 NodeJS 和其他平台。

¥The "SQLite" demo covers NodeJS and other platforms.

概述

¥Overview

支持 WebSQL 的环境公开 openDatabase 全局方法。它需要 4 个参数：

¥Environments that support WebSQL expose the openDatabase global method. It takes 4 arguments:

• 内部数据库名称

  ¥internal database name

• 版本字符串 (1.0)

  ¥version string (1.0)

• 公开显示名称

  ¥public display name

• 数据库大小（以字节为单位）

  ¥database size (measured in bytes)

以下命令尝试连接到名为 sheetql 的数据库。如果数据库不存在，它将创建一个新数据库，并提示分配 2MB 空间。

¥The following command attempts to connect to the database named sheetql. If the database does not exist, it will create a new database with a hint to allocate 2MB of space.

const db = openDatabase('sheetql', '1.0', 'SheetJS WebSQL Test', 2097152);

事务和查询

¥Transactions and Queries

查询在事务内执行。

¥Queries are performed within transactions.

Database#transaction 将事务对象传递给回调参数：

¥Database#transaction passes a transaction object to the callback argument:

db.transaction(function(tx) {
  /* tx is a transaction object */
});

在事务内，使用 Transaction#executeSql 执行查询。该方法有 4 个参数：

¥Within a transaction, queries are performed with Transaction#executeSql. The method takes 4 arguments:

• SQL 语句存储在字符串中

  ¥SQL statement stored in a string

• 参数化查询参数数组

  ¥Array of parameterized query arguments

• 成功回调

  ¥Success callback

• 错误回调

  ¥Error callback

如果查询成功，将使用两个参数调用成功回调：

¥If the query succeeds, the success callback will be invoked with two arguments:

• 事务对象

  ¥Transaction object

• 查询结果

  ¥Result of the query

如果查询失败，将使用两个参数调用错误回调：

¥If the query fails, the error callback will be invoked with two arguments:

• 事务对象

  ¥Transaction object

• 错误信息

  ¥Error information

Web SQL 数据库 API 是基于回调的。以下代码片段运行一个查询，并将执行封装在一个 promise 中，该 promise 解析为查询结果或因错误而拒绝：

¥The Web SQL Database API is callback-based. The following snippet runs one query and wraps the execution in a promise that resolves to the query result or rejects with the error:

function execute_simple_query(db, query) {
  return new Promise((resolve, reject) => {
    db.transaction(tx =>
      tx.executeSQL(query, [],
        (tx, data) => resolve(data),
        (tx, err) => reject(err)
      )
    );
  });
}

导入数据

¥Importing Data

使用 "生成表" 中的 generate_sql 辅助函数从电子表格导入数据非常简单

¥Importing data from spreadsheets is straightforward using the generate_sql helper function from "Generating Tables"

const stmts = generate_sql(ws, wsname);

// NOTE: tx.executeSql and db.transaction use callbacks. This wraps in Promises
for(var stmt of stmts) await new Promise((res, rej) => {
  db.transaction(tx =>
    tx.executeSql(stmt, [],
      (tx, data) => res(data), // if the query is successful, return the data
      (tx, err) => rej(err) // if the query fails, reject with the error
  ));
});

通常，工作表对象是从 SheetJS read 或 readFile 方法 [^2] 生成的工作簿对象 [^1] 中提取的。

¥Typically worksheet objects are extracted from workbook objects[^1] generated from the SheetJS read or readFile methods[^2].

导出数据

¥Exporting Data

SQL SELECT 语句的结果是 SQLResultSet。rows 属性是 SQLResultSetRowList。它是一个 "类数组" 结构，具有 length 和 0、1 等属性。但是，这不是真正的 Array 对象！

¥The result of a SQL SELECT statement is a SQLResultSet. The rows property is a SQLResultSetRowList. It is an "array-like" structure that has length and properties like 0, 1, etc. However, this is not a real Array object!

可以使用 Array.from 创建真正的数组。SheetJS json_to_sheet 方法 [^3] 可以从真实数组生成工作表对象 [^4]：

¥A real Array can be created using Array.from. The SheetJS json_to_sheet method[^3] can generate a worksheet object[^4] from the real array:

db.readTransaction(tx =>
  tx.executeSQL("SELECT * FROM DatabaseTable", [], (tx, data) => {
    // data.rows is "array-like", so `Array.from` can make it a real array
    const aoo = Array.from(data.rows);
    const ws = XLSX.utils.json_to_sheet(aoo);
    // ... perform an export here OR wrap in a Promise
  })
);

使用 book_new 和 book_append_sheet[^5]，可以创建工作簿对象。该工作簿通常导出到带有 writeFile[^6] 的文件系统。

¥Using book_new and book_append_sheet[^5], a workbook object can be created. This workbook is typically exported to the filesystem with writeFile[^6].

在线演示

¥Live Demo

测试部署

该浏览器演示在以下环境中进行了测试：

¥This browser demo was tested in the following environments:

浏览器	日期
Chrome 118	2024-06-29

不支持 WebSQL 的浏览器会抛出错误：

¥Browsers that do not support WebSQL will throw errors:

浏览器	日期	错误信息
Chrome 126	2024-06-29	openDatabase is not defined
Safari 17.1	2024-06-29	Web SQL is deprecated
Firefox 127	2024-06-29	openDatabase is not defined

导出演示

¥Export Demo

以下演示生成一个包含 5 条固定 SQL 语句的数据库。可以在实时编辑器中更改查询。可以在开发者工具的 "应用" 选项卡的 "WebSQL" 部分检查 WebSQL 数据库：

¥The following demo generates a database with 5 fixed SQL statements. Queries can be changed in the Live Editor. The WebSQL database can be inspected in the "WebSQL" section of the "Application" Tab of Developer Tools:

Result

Loading...

Live Editor

function SheetQL() {
  const [out, setOut] = React.useState("");
  const queries = [
    'DROP TABLE IF EXISTS Presidents',
    'CREATE TABLE Presidents (Name TEXT, Idx REAL)',
    'INSERT INTO Presidents  (Name, Idx) VALUES ("Barack Obama", 44)',
    'INSERT INTO Presidents  (Name, Idx) VALUES ("Donald Trump", 45)',
    'INSERT INTO Presidents  (Name, Idx) VALUES ("Joseph Biden", 46)'
  ];
  const xport = React.useCallback(async() => {
    /* prep database */
    const db = openDatabase('sheetql', '1.0', 'SheetJS WebSQL Test', 2097152);

    for(var q of queries) await new Promise((res, rej) => {
      db.transaction((tx) => {
        tx.executeSql(q, [], (tx, data) => res(data), (tx, err) => rej(err));
      });
    });

    /* pull data and generate rows */
    db.readTransaction(tx => {
      tx.executeSql("SELECT * FROM Presidents", [], (tx, data) => {
        const aoo = Array.from(data.rows);
        setOut("QUERY RESULT:\n" + aoo.map(r => JSON.stringify(r)).join("\n") + "\n")
        const ws = XLSX.utils.json_to_sheet(aoo);
        const wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, ws, "Presidents");
        XLSX.writeFile(wb, "SheetQL.xlsx");
      });
    });
  });
  return ( <pre>{out}<button onClick={xport}><b>Fetch!</b></button></pre> );
}

服务器端 SQLite

¥Server-Side SQLite

该展览已移至单独的页面。

¥The exposition has been moved to a separate page.

[^1]: 见 "工作簿对象"

¥See "Workbook Object"

[^2]: 见 "读取文件" 中的 read 和 readFile

¥See read and readFile in "Reading Files"

[^3]: 见 json_to_sheet 于 "实用工具"

¥See json_to_sheet in "Utilities"

[^4]: 见 "Sheet 对象"

¥See "Sheet Objects"

[^5]: 有关 book_new 和 book_append_sheet 的详细信息，请参阅 "工作簿助手" 于 "实用工具"。

¥See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet.

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

¥See writeFile in "Writing Files"