如何导出（转储）WebSQL 数据

Question

我正在开发一个使用 WebSQL 来存储历史数据的 Chrome 扩展。 作为WebSQL，数据库存储在客户端。

我想添加一个选项来导出/导入此类数据，以便用户可以与其他用户或其他电脑共享/使用这些数据。

这是我在纯客户端数据库上的第一步，所以我想知道如何做到这一点。 我正在考虑将数据库转换为一个巨大的 json 字符串，用户可以复制/粘贴，但看起来不太用户友好。

还有更好的解决办法吗？

Answer 1

我在 HTML5 数据库客户端 上运行了一个单表转储解决方案，我写了一些几天前。

查看 http://html5db.desalasworks.com/script.js 并向下滚动到 SqlClient .exportTable，这有一个需要扩展以覆盖整个数据库的示例。

步骤如下：

步骤 1：创建模式：

SELECT sql FROM sqlite_master

步骤 2：获取表列表：

SELECT tbl_name from sqlite_master WHERE type = 'table'

步骤 3：循环遍历每个表并使用结果创建一个 INSERT 脚本

transaction.executeSql("SELECT * FROM " + _tbl_name + ";", [], 
    function(transaction, results) {
        if (results.rows) {
            for (var i = 0; i < results.rows.length; i++) {
                var row = results.rows.item(i);
                var _fields = [];
                var _values = [];
                for (col in row) {
                    _fields.push(col);
                    _values.push('"' + row[col] + '"');
                }
                _exportSql += ";\nINSERT INTO " + _tbl_name + "(" + _fields.join(",") + ") VALUES (" + _values.join(",") + ")";
            }
        }
    }
);

希望这很有用。

2016 年 1 月更新 - 全数据库导出

我有一个 JS websqldump 库，您也可以从 github 下载。

这将导出整个数据库。查看代码：

https://github.com/sdesalas/websqldump

用法如下

websqldump.export({
  database: 'NorthwindLite',
  success: function(sql) {alert(sql);}
});

Answer 2

不是最优雅的方式，但却是最方便的。
只需将脚本粘贴到 Chrome 调试器工具中，然后调用 c()，您就应该获取该文件。

var looongSQL = "";

var x = function (options) {
if (options.n < options.sqlTables.length) {
    onTheMove.openLocalDatabase().transaction(
        function (tx) {
            var sqlStatement = "SELECT * FROM " + options.sqlTables[options.n];
            tx.executeSql(sqlStatement, [],
                function (tx, rslt) {
                    if (rslt.rows) {
                        for (var m = 0; m < rslt.rows.length; m++) {
                            var dataRow = rslt.rows.item(m);
                            var _fields = [];
                            var _values = [];
                            for (col in dataRow) {
                                _fields.push(col);
                                _values.push('"' + dataRow[col] + '"');
                            }
                            looongSQL += "INSERT INTO " + options.sqlTables[options.n] + "(" + _fields.join(",") + ") VALUES (" + _values.join(",") + ");\n";
                        }
                    }
                    options.n++;
                    x(options);
                }
            );
        });
}else
{
document.location = 'data:Application/octet-stream,' +
            encodeURIComponent(looongSQL);
}

};
var c = function () {
onTheMove.openLocalDatabase().transaction(
    function (transaction) {
        transaction.executeSql("SELECT sql FROM sqlite_master;", [],
            function (transaction, results) {
                var sqlStatements = [];

                if (results.rows) {
                    for (var i = 0; i < results.rows.length; i++) {
                        console.log(results.rows.item(i));
                        var row = results.rows.item(i);
                        if (row.sql != null && row.sql.indexOf("CREATE TABLE ") != -1 && row.sql.indexOf("__") == -1) {
                            var tableName = row.sql.replace("CREATE TABLE ", "").split(/ |\(/)[0];
                            sqlStatements.push('DROP TABLE IF EXISTS  ' + tableName);
                        }if(row.sql != null && row.sql.indexOf("__") == -1){
                        sqlStatements.push(row.sql);}
                    }
                }

                for (var j = 0; j < sqlStatements.length; j++) {
                    if (sqlStatements[j] != null) {
                        looongSQL += sqlStatements[j] + ';\r\n';
                    }
                }

                transaction.executeSql("SELECT tbl_name from sqlite_master WHERE type = 'table'", [],
                    function (transaction, res) {
                        var sqlTables = [];
                        for (var k = 0; k < res.rows.length; k++) {
                            if (res.rows.item(k).tbl_name.indexOf("__") == -1) {
                                sqlTables.push(res.rows.item(k).tbl_name);
                            }
                        }
                        x({
                            sqlTables: sqlTables,
                            n: 0
                        });
                    });

            }
        );
    });
};

另一个将其导出为 JSON 的版本

var looongSQL = "[\n";
var stringidiedLocalStorage = JSON.stringify(JSON.stringify(localStorage));
looongSQL += "/* 1 */ " + stringidiedLocalStorage + ",\n";
var x = function (options) {
if (options.n < options.sqlTables.length) {
    onTheMove.openLocalDatabase().transaction(
        function (tx) {
            var sqlStatement = "SELECT * FROM " + options.sqlTables[options.n];
            tx.executeSql(sqlStatement, [],
                function (tx, rslt) {
                    if (rslt.rows && rslt.rows.length > 0) {
                        var _fields = [];

                        for (var col in rslt.rows.item(0)) {
                            _fields.push(col);
                        }
                        var insertTableSQL = "\"INSERT INTO " + options.sqlTables[options.n] + "(" + _fields.join(",") + ") ";
                        looongSQL += "/* " + options.count + " */ " + insertTableSQL;


                        for (var m = 0; m < rslt.rows.length; m++) {
                            var dataRow = rslt.rows.item(m);

                            var _values = [];
                            for (var col in dataRow) {
                                _values.push('\'' + dataRow[col] + '\'');
                            }
                            looongSQL += "SELECT " + _values.join(",");
                            if (m < rslt.rows.length - 1 && (m % 499 != 0 || m == 0)) {
                                looongSQL += " UNION ALL ";
                            }
                            if (m % 499 == 0 && m != 0) {
                                options.count++;
                                looongSQL += "\",\r\n/* " + options.count + " */ " + insertTableSQL;
                            }
                        }

                        looongSQL += "\",\r\n";
                        options.count++;
                    }
                    options.n++;
                    x(options);
                }
            );
        });
} else {
    looongSQL += ']';
    document.location = 'data:Application/octet-stream,' +
        encodeURIComponent(looongSQL);
}

};
var c = function () {
onTheMove.openLocalDatabase().transaction(
    function (transaction) {
        transaction.executeSql("SELECT sql FROM sqlite_master;", [],
            function (transaction, results) {
                var sqlStatements = [];
                var count = 2;
                if (results.rows) {
                    for (var i = 0; i < results.rows.length; i++) {
                        console.log(results.rows.item(i));
                        var row = results.rows.item(i);
                        if (row.sql != null && row.sql.indexOf("CREATE ") != -1) {
                            var objectType = row.sql.replace("CREATE ", "").split(/ |\(/)[0];
                            if (row.sql.indexOf("CREATE " + objectType + " ") != -1 && row.sql.indexOf("__") == -1) {
                                var objectName = row.sql.replace("CREATE " + objectType + " ", "").split(/ |\(/)[0];
                                sqlStatements.push('/* ' + count + ' */ "DROP ' + objectType + ' IF EXISTS ' + objectName + '"');
                                count++;
                            }
                            if (row.sql != null && row.sql.indexOf("__") == -1) {
                                sqlStatements.push('/* ' + count + ' */ "' + row.sql.replace(/(\r\n|\n|\r)/gm, " ") + '"');
                                count++;
                            }

                        }
                    }
                }

                for (var j = 0; j < sqlStatements.length; j++) {
                    if (sqlStatements[j] != null) {
                        looongSQL += sqlStatements[j] + ',\r\n';
                    }
                }

                transaction.executeSql("SELECT tbl_name from sqlite_master WHERE type = 'table'", [],
                    function (transaction, res) {
                        var sqlTables = [];
                        for (var k = 0; k < res.rows.length; k++) {
                            if (res.rows.item(k).tbl_name.indexOf("__") == -1) {
                                sqlTables.push(res.rows.item(k).tbl_name);
                            }
                        }
                        x({
                            sqlTables: sqlTables,
                            n: 0,
                            count: count
                        });
                    });

            }
        );
    });
};