在React App中使用或不使用PouchDB加载预建SQlite数据库的正确方法是什么 [英] What is the correct way to load prebuilt SQlite databases with or without PouchDB in a React App

问题描述

目前，我正在编写React App，并努力地从SQlite数据库中进行简单的阅读。

Currently I'm writing a React App and struggling with a simple reading from a SQlite database.

由于问题不清楚而编辑：

***目标是无需任何后端即可从数据库中读取数据，因为即使脱机也需要从数据库中读取数据。

***The goal is to read from the database without any backend, because it needs to read from the database even when it is offline.

***我的目标是一次性进行文件转换，然后只是pouchdb脱机查询。但是我不想手动进行操作，因为大约有6k多个注册表。

***I'm aiming for a ONE TIME file conversion, then just pouchdb queries offline. But I don't want to do it manually because there are around 6k+ registries.

***或者来自浏览器的SQL查询没有任何API，但是我需要支持Internet Explorer，因此不能选择WebSQL。我已经尝试过sqlite3库，但无法使其与Create React App一起使用。

***Or SQL queries from the browser without any APIs, but I need to support Internet Explorer, so WebSQL is not an option. I've tried sqlite3 library, but I can't make it work with Create React App.

我尝试的解决方案是使用PouchDB读取文件，但是我得出的结论是，如果不使用cordova，我就无法使用PouchDB PRELOAD SQlite文件（我对此不满意，我不希望任何服务器运行），甚至

The solution I tried was to use PouchDB for reading the file, but I'm coming to a conclusion that it is NOT possible to PRELOAD a SQlite file with PouchDB without using cordova (I'm not comfortable with it, I don't want any servers running), or even with some kind of adapter.

那么这是正确的处理方式吗？

So is this the right way of doing things?

有什么方法可以使我的.db数据不丢失，而必须手动转换呢？

Is there any way that I would not loose my .db data, and have to convert it all of it manually?

我应该忘记在IE上支持此功能吗？

Should I forget about supporting this features on IE?

谢谢：）

推荐答案

尝试以下操作：

sqlite3 example "DROP TABLE IF EXISTS some_table;";
sqlite3 example "CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, anattr VARCHAR, anotherattr VARCHAR);";
sqlite3 example "INSERT INTO some_table VALUES (NULL, '1stAttr', 'AttrA');";
sqlite3 example "INSERT INTO some_table VALUES (NULL, '2ndAttr', 'AttrB');";

## Create three JSON fragment files
sqlite3 example ".output result_prefix.json" "SELECT '{ \"docs\": ['";
sqlite3 example ".output rslt.json" "SELECT '{ \"_id\": \"someTable_' || SUBSTR(\"000000000\" || id, LENGTH(\"000000000\" || id) - 8, 9) || '\", \"anattr\": \"' || anattr || '\", \"anotherattr\": \"' || anotherattr || '\" },' FROM some_table;";
sqlite3 example ".output result_suffix.json" "SELECT '] }'";

## strip trailing comma of last record
sed -i '$ s/.$//' rslt.json;

## concatenate to a single file
cat result_prefix.json rslt.json result_suffix.json > result.json;

cat result.json;

您应该能够简单地将上述行粘贴到（unix）命令行上，看到输出：

You should be able simply to paste the above lines onto the (unix) command line, seeing output:

{ "docs": [
{ "_id": "someTable_000000001", "anattr": "1stAttr", "anotherattr": "AttrA" },
{ "_id": "someTable_000000002", "anattr": "2ndAttr", "anotherattr": "AttrB" }
] }

如果已安装 jq ，则可以... / p>

If you have jq installed you can do instead ...

cat result.json | jq .

...正在获取：

{
  "docs": [
    {
      "_id": "someTable_000000001",
      "anattr": "1stAttr",
      "anotherattr": "AttrA"
    },
    {
      "_id": "someTable_000000002",
      "anattr": "2ndAttr",
      "anotherattr": "AttrB"
    }
  ]
}

在博客文章使用PouchDB预先构建的数据库。

因此，如果您有可用的CouchDB服务器，则可以执行以下操作；

So, if you have a CouchDB server available you can do the following;

export COUCH_DB=example;
export COUCH_URL= *** specify yours here ***;
export FILE=result.json;

## Drop database
curl -X DELETE ${COUCH_URL}/${COUCH_DB};

## Create database
curl -X PUT ${COUCH_URL}/${COUCH_DB};

## Load database from JSON file
curl -H "Content-type: application/json" -X POST "${COUCH_URL}/${COUCH_DB}/_bulk_docs"  -d @${FILE};

## Extract database with meta data to PouchDB initialization file
pouchdb-dump ${COUCH_URL}/${COUCH_DB} > example.json

## Inspect PouchDB initialization file
cat example.json | jq .

很明显，您需要进行一些修改，但是以上内容不会给您带来任何问题。

Obviously you'll need some adaptations, but the above should give you no problems.

这篇关于在React App中使用或不使用PouchDB加载预建SQlite数据库的正确方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！