How to export (dump) WebSQL data
I got a single table dump solution working on a HTML5 database client I wrote a few days ago.
Check out http://html5db.desalasworks.com/script.js and scroll down to SqlClient.exportTable, this has an example that needs to be expanded to cover the whole database.
The steps are:
Step 1: Create the schema:
SELECT sql FROM sqlite_masterStep 2: Get a list of tables:
SELECT tbl_name from sqlite_master WHERE type = 'table'Step 3: Loop through each of them and create an INSERT script with the results
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(",") + ")"; } } });Hope this is useful.
UPDATE JAN 2016 - WHOLE DB EXPORT
I've got a JS websqldump library that you can download from github as well.
This one will export the whole database. Check out the code on:
https://github.com/sdesalas/websqldump
Usage as follows
websqldump.export({ database: 'NorthwindLite', success: function(sql) {alert(sql);}});
Not the most elegant way, yet most convenient.
Just paste the script in chrome debugger tools then call c(), and you should get the file.
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 }); }); } ); });};Another version that exports it as 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 }); }); } ); });};