database Phonegap 离线数据库

Phonegap Offline Database

提问by noway

I want to store some largeoffline data in user phone (more than 100 MB) in an encrypteddatabase. If possible I also want to distribute the database pre-populated. I have also seen this.

我想在加密数据库中存储用户手机中的一些大型离线数据（超过 100 MB）。如果可能，我还想分发预先填充的数据库。我也见过这个。

I know about the webdatabasething, but because it is depreciated, I am advised not to work with that.

我知道网络数据库的事情，但是因为它已经折旧了，所以建议我不要使用它。

I also have seen some third party plugins such as SQLite Plugin, but it works only for iOS and Android devices, but I target 4 platforms (ios, android, blackberry, windows)

我也见过一些第三方插件，例如SQLite Plugin，但它只适用于 iOS 和 Android 设备，但我的目标是 4 个平台（ios、android、blackberry、windows）

Is there any other solution, other than writing down my own?

除了写下我自己的解决方案，还有其他解决方案吗？

回答by SashaZd

I made an app recently that required this, targetting the same OS's. You can use a combination of 2 databases :

我最近制作了一个需要这个的应用程序，针对相同的操作系统。您可以使用 2 个数据库的组合：

1. LocalStorage ::

1.本地存储::

Check for localStorage

检查本地存储

function supports_html5_storage() {
  try {
    return 'localStorage' in window && window['localStorage'] !== null;
  } catch (e) {
    return false;
  }
}

Set an item into LocalStorage

将项目设置为 LocalStorage

localStorage.setItem("bar", foo);

or

或者

localStorage["bar"] = foo;

Get an item from LocalStorage

从 LocalStorage 获取项目

var foo = localStorage.getItem("bar");

or

或者

var foo = localStorage["bar"];

2. SQLite Database (more convenient, more persistive)

2. SQLite 数据库（更方便，更持久）

Set up your DB

设置您的数据库

var shortName = 'BHCAppDB'; 
var version = '1.0'; 
var displayName = 'BHCAppDB'; 
var maxSize = 65535; 
if (!window.openDatabase){ 
     alert('!! Databases are not supported in this Device !! \n\n We are sorry for the inconvenience and are currently working on a version that will work on your phone'); 
}
db = openDatabase(shortName, version, displayName,maxSize);
createAllTables(db);

Create your Tables

创建你的表

function createAllTables(db){
    db.transaction(function(transaction){
        transaction.executeSql("CREATE TABLE IF NOT EXISTS Profile(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, gender TEXT,age INTEGER)");
}

Execute an SQL Query

执行 SQL 查询

transaction(function(transaction){
        var rowCount = 'SELECT * FROM Profile';
        transaction.executeSql(rowCount,[],function(transaction,result){
            if(result.rows.length == 0){
                var sqlString = 'INSERT INTO Profile (name,gender,age) VALUES("自己","Female",18)';
                transaction.executeSql(sqlString);

            }
        });
    });

EDIT :: I forgot to add in the last option :)

编辑 :: 我忘了添加最后一个选项 :)

3. Native Storage on all devices

3.所有设备上的本机存储

This is the best part of Phonegap. You can call a native plugin class on all the devices using the Phonegap plugin call. During the call, you can pass parameters to the class, and the native class can store your data in the OS itself.

这是Phonegap最好的部分。您可以使用 Phonegap 插件调用在所有设备上调用本机插件类。在调用过程中，您可以将参数传递给类，本机类可以将您的数据存储在 OS 本身中。

For example :: in iOS, you create a plugin .h & .m class and register it with the Cordova.plist file. Once that's done, you need to send a call to the class from JavaScript using Phonegap. Once the parameters have been received using NSDictionary or any other NSArray type, you can call a CoreData class to store UNLIMITED amounts of data. You'll never run out of memory .

例如 :: 在 iOS 中，您创建了一个插件 .h & .m 类并将其注册到 Cordova.plist 文件中。完成后，您需要使用 Phonegap 从 JavaScript 向该类发送调用。一旦使用 NSDictionary 或任何其他 NSArray 类型接收到参数，您就可以调用 CoreData 类来存储无限量的数据。你永远不会耗尽内存。

This can be done in a similar fashion for all the rest of the OS's also :)

对于所有其余的操作系统，这也可以以类似的方式完成:)

For Encryption try the following :: SQLCipher

对于加密，请尝试以下 :: SQLCipher

Here is some additional information on working with an existing SQLite database. In this example encrypted.db is that brand new database you create and pragma.

以下是有关使用现有 SQLite 数据库的一些附加信息。在此示例中， encrypted.db 是您创建的全新数据库和编译指示。

ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret'; -- create a new encrypted database
CREATE TABLE encrypted.t1(a,b); -- recreate the schema in the new database (you can inspect all objects using SELECT * FROM sqlite_master)
INSERT INTO encrypted.t1 SELECT * FROM t1; -- copy data from the existing tables to the new tables in the encrypted database
DETACH DATABASE encrypted;

回答by Apostolos Emmanouilidis

In the W3C specification for webdatabaseit is mentioned that the Web Applications Working Group continues work on two other storage-related specifications: Web Storageand Indexed Database API.

在Web数据库的 W3C 规范中提到，Web 应用程序工作组继续致力于另外两个与存储相关的规范：Web 存储和索引数据库 API。

So the webdatabase specification is no longer active but the other two specifications are active.

所以 webdatabase 规范不再活跃，但其他两个规范是活跃的。

The Web Storage can be used to store data locally within the user's browser. There are the following objects to achieve that:

Web Storage 可用于在用户浏览器中本地存储数据。有以下目标可以实现：

• localStorage which stores data without expiration date
• sessionStorage which stores data for one session

• localStorage 存储没有到期日期的数据
• sessionStorage 存储一个会话的数据

The Web Storage is not recommended for your case (more than 100MB), because the W3C specification mentions that:

不建议将 Web Storage 用于您的情况（超过 100MB），因为 W3C 规范提到：

A mostly arbitrary limit of five megabytes per origin is recommended.

建议对每个源的大多数任意限制为 5 兆字节。

In my opinion SQLite is the best available option since it is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Moreover the SQLite limitsseems to cover your needs:

在我看来，SQLite 是最好的选择，因为它是一个进程内库，它实现了一个自包含、无服务器、零配置、事务性 SQL 数据库引擎。此外，SQLite 限制似乎可以满足您的需求：

The largest possible setting for SQLITE_MAX_PAGE_COUNT is 2147483646. When used with the maximum page size of 65536, this gives a maximum SQLite database size of about 140 terabytes.

SQLITE_MAX_PAGE_COUNT 的最大可能设置为 2147483646。当与最大页面大小 65536 一起使用时，这提供了大约 140 TB 的最大 SQLite 数据库大小。

Regarding your encryption requirements you should consider the SQLCipherwhich is an SQLite extension.

关于您的加密要求，您应该考虑SQLCipher，它是一个 SQLite 扩展。

SQLCipher is an SQLite extension that provides transparent 256-bit AES encryption of database files. To date, it has been open-sourced, sponsored and maintained by Zetetic LLC. In the mobile space, SQLCipher has enjoyed widespread use in Apple's iOS, as well as Nokia / QT for quite some time.

SQLCipher 是一个 SQLite 扩展，它为数据库文件提供透明的 256 位 AES 加密。迄今为止，它已由 Zetetic LLC 开源、赞助和维护。在移动领域，SQLCipher 在 Apple 的 iOS 以及 Nokia / QT 中得到了广泛的使用。

An alternative option is to encrypt and decrypt your data when writing and reading your database.

另一种选择是在写入和读取数据库时加密和解密数据。

I hope this helps.

我希望这有帮助。

回答by jkwuc89

The mobile app I am working on has a similar requirement. It requires offline access to a parts table that contains nearly 500,000 different parts in it. The source for this table is extracted from the server by getting its JSON via a well defined GET URL.

我正在开发的移动应用程序也有类似的要求。它需要离线访问包含近 500,000 个不同零件的零件表。该表的源是通过明确定义的 GET URL 获取其 JSON 从服务器提取的。

I considered Indexed DB but the mobile browsers inside iOS and Android don't support this. Web local storage is not an option because of its hard 5 MB limit. So, I decided to use the Web SQL Database standard (http://www.w3.org/TR/webdatabase/) even though its deprecated. My experience so far with using Web SQL Database has been very good. Database operations perform very well and are very reliable on the mobile devices I support (iPad 2, iPad 3, Motorola Xyboard, Samsung Galaxy Tab 2). Plus, Phonegap exposes a JavaScript API to work with this standard (see http://docs.phonegap.com/en/2.5.0/cordova_storage_storage.md.html#Storage).

我考虑过索引数据库，但 iOS 和 Android 中的移动浏览器不支持这个。Web 本地存储不是一个选项，因为它有 5 MB 的硬限制。因此，我决定使用 Web SQL 数据库标准 ( http://www.w3.org/TR/webdatabase/)，即使它已被弃用。到目前为止，我使用 Web SQL 数据库的经验非常好。数据库操作在我支持的移动设备（iPad 2、iPad 3、Motorola Xyboard、Samsung Galaxy Tab 2）上执行得非常好并且非常可靠。此外，Phonegap 公开了一个 JavaScript API 以使用此标准（请参阅http://docs.phonegap.com/en/2.5.0/cordova_storage_storage.md.html#Storage）。

I wrote a Java utility that converts the downloaded JSON data into a SQLite database whose files are packaged as part of the Android APK or the iOS app package.

我编写了一个 Java 实用程序，将下载的 JSON 数据转换为 SQLite 数据库，该数据库的文件打包为 Android APK 或 iOS 应用程序包的一部分。

When my Phonegap mobile app starts, it uses native code to check the app's private data directory for the presence of the SQLite database files. If the files are not present, the native code copies the database files from the app package.

当我的 Phonegap 移动应用程序启动时，它使用本机代码检查应用程序的私有数据目录中是否存在 SQLite 数据库文件。如果文件不存在，本机代码会从应用程序包中复制数据库文件。

My implementation is based on the sample code I found at the link below. I hope this helps. Let me know if you have any questions about my particular implementation.

我的实现基于我在以下链接中找到的示例代码。我希望这有帮助。如果您对我的特定实现有任何疑问，请告诉我。

http://gauravstomar.blogspot.com/2011/08/prepopulate-sqlite-in-phonegap.html

http://gauravstomar.blogspot.com/2011/08/prepopulate-sqlite-in-phonegap.html

回答by ahmed wahba

I tried using LokiJSas a local database, and found it helpful in non-relational data. In my case I retrieve a data stored using MongoDB on the server, but it depends on the nature of your system

我尝试使用LokiJS作为本地数据库，发现它对非关系数据很有帮助。就我而言，我在服务器上检索使用 MongoDB 存储的数据，但这取决于您的系统的性质

回答by UBIK LOAD PACK

See those questions/answers:

请参阅这些问题/答案：

• Opening and storing encrypted documents offline in iOS

• Does PhoneGap / Apache Cordova propose an API for encrypted SQLite database

• When using Mono Touch can I also package for a standard Window application?

• 在 iOS 中离线打开和存储加密文档

• PhoneGap / Apache Cordova 是否提出了用于加密 SQLite 数据库的 API

• 使用 Mono Touch 时，我还可以打包用于标准 Window 应用程序吗？