How to export (dump) WebSQL data How to export (dump) WebSQL data sqlite sqlite

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_master

Step 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                        });                    });            }        );    });};