<

Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

How to save your database to Dropbox (and how to restore it) from within a Cordova application - Android only (part 2)

Published on
3,199 Points
199 Views
Last Modified:
Marco Gasi
Freelance, I like to share what I know. Find out my articles in my learner-to-learners blog codingfix.com
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.

Wellcome to the second part of my tutorial about how to save your database to Dropbox from within a Cordova mobile application. Here we'll see how to manage backup and restore of a database using a helpful plugin: SQLite Porter by Dave Alden. The plugin is fantastic, it works great and let me share with you I'm proud indeed to have joined the contributors group fixing a small bug which prevented to successfully use exportDbToSql() and importDbFromSql().


So, open your command prompt to your terminal window and navigate to your project root, then type:

cordova plugin add uk.co.workingedge.cordova.plugin.sqliteporter

That's all: the plugin is ready for us.


Managing files: Cordova-plugin-file


But files must be managed: we have to create them and their directories, we must be able to read their content and to write content to files. To do so we're going to install an important Cordova plugin which is used to read and write files within the host OS. You should still have your terminal (or command prompt) open in your project root directory (if not, then open it and go there). Type this:

cordova plugins add cordova-plugin-file

All right! We can finally type some code, now! First we'll write the function which actually exports our database and writes down a MySQL dump file: put the following code before the jQuery main function.


If you want to go deeper in file-system API, I suggest to read, besides the mandatory Cordova man page, two great articles https://www.html5rocks.com/en/tutorials/file/filesystem/ and https://www.neontribe.co.uk/cordova-file-plugin-examples/.


function exportBackup() {
    var successFn = function (sql) {
        window.resolveLocalFileSystemURL(cordova.file.dataDirectory, function (dirEntry) {
            dirEntry.getDirectory('/dropboxTestBackup', {create: true}, function (dirEntry) {
                dirEntry.getFile('dropboxTestBackup.sql', {create: true}, function (fileEntry) {
                    alert("The file " + fileEntry.name + ' has been created in the following directory: Android/data/com.example.dropboxTest/files/dropboxTestBackup/');
                    writeFile(fileEntry, sql);
                });
            }, onErrorCreateFile);
        }, onErrorCreateDir);
    };
    cordova.plugins.sqlitePorter.exportDbToSql(db, {
        successFn: successFn
    });
}

function onErrorCreateFile(e) {
    console.log('Error creating file: ' + e);
}

function onErrorCreateDir(e) {
    console.log('Error creating directory: ' + e);
}


Let's analyze this code line by line. As you can see, the function it is divided in 2 sections: the former is the callback function, the latetr is the SQLite Porter function which actually exports the database and then calls the callback function. This second part is quite simple and we have nothing interesting to note about it. But the calback function allows us to check how Cordova manages the access to and the manipulation of files and directories.


Code anatomy


First we find the resolveLocalFileSystemURL() (see here: https://cordova.apache.org/docs/en/latest/reference/cordova-plugin-file/index.html#where-to-store-files). As from version 1.2, for each important file-system directory is provided an URL:

Each URL is in the form file:///path/to/spot/, and can be converted to a DirectoryEntry using window.resolveLocalFileSystemURL().

We pass this function the parameter cordova.file.dataDirectory: we're telling Cordova we want to store our file in the Internal Storage. This is the recommended to store any persistent application data because it is accessible exclusively by our application. We'll check this later, when we'll have already created our backup file. So cordova.file.dataDirectory is equal to the path /sdcardAndroid/data/com.codingfix.dropboxTest/ and it is converted to an object of type DirectoryEntry we have called dirEntry. dirEntry object is returned by the callback anonymous function we use in resolveLocalFileSystemURL(): window.resolveLocalFileSystemURL(cordova.file.dataDirectory, function (dirEntry) {

Let's examine our callback function: we immediately see we use a method of the object DirectoryEntry, the method getDirectory() and we pass it 2 parameters and another anonymous callback function: dirEntry.getDirectory('/dropboxTestBackup', {create: true}, function (dirEntry) {

The method getDirectory() accept four parameters (here we only use three): a string which represents the relative path of a directory, a boolean value which set if the directory must be created if not found and two callback functions for success and error (this one is not used in our example). In other words, our app is telling Android to look for directory /sdcardAndroid/data/com.codingfix.dropboxTest/ and to create in that directory a new directory called dropboxTestBackup where we'll store our backup files.

The callback function uses the new object of type DirectoryEntry created by the method getDirectory() to create the backup file itself: dirEntry.getFile('dropboxTestBackup.sql', {create: true}, function (fileEntry) { . This time we use the method getFile() which is almost identical to the method getDirectory() used above with the obvious difference that it creates a file instead of a directory and returns a reference to that file. We use the object fileEntry in the last callback function where we alert the application user that the file has been successfully created and we write our data to the file:

alert("The file: " + fileEntry.name + ' has been created in the following directory: Android/data/com.example.dropboxTest/files/dropboxTestBackup/'); writeFile(fileEntry, sql);

Hey, what's that?!? We never talked about a writeFile() function!!! - I hear you say. Don't worry, guys: we're going to look at that right now :)


Writing files


Yes, we have to write data to a file, we knew that, isn't it? So look at this function (and keep you ready to know about reading files, later in this article):

function writeFile(fileEntry, dataObj) {
    // Create a FileWriter object for our FileEntry.
    fileEntry.createWriter(function (fileWriter) {
        fileWriter.onwriteend = function () {
            console.log("Successful file write...");
            readFile(fileEntry);
        };
        fileWriter.write(dataObj);
    }, onErrorWriteFile);
}

function onErrorWriteFile(e) {
    console.log('Error writing file: ' + e.toString());
}

function readFile(fileEntry) {
    fileEntry.file(function (file) {
        var reader = new FileReader();
        reader.onloadend = function () {
            console.log("Successful file read: " + fileEntry.fullPath + " - content: " + this.result);
            return this.result;
        };
        reader.readAsText(file);
    }, onErrorReadFile);
}

function onErrorReadFile(e) {
    console.log('Error reading file: ' + e);
}


The function is quite simple: it just accept 2 params, the file object which points to the physical file created in our file system and the data to write to the file: writeFile(fileEntry, sql); This function then uses internally the readFile() function to read the just created file and output its content in the console. As we have noticed above, storing the file in the Internal storage (as it is recommended for security reasons) doesn't allow us to find this file using any third party application. Nor your pre-installed file manager nor a third-party one can see this file (dropboxTestBackup.sql) nor the directory where the file is created in (/dropboxTestBackup), so this is the only one way we have to check if everything went fine.


If you want to create a public file, accessible by your Android file manager or even by your computer when your Android device is connected via a USB cable, you have to use externalDataDirectory instead of dataDirectory, but this exposes your files to be accessed by third party software. So feel free to use externalDataDirectory while in development, to make easier to debug your app, but keep in mind to replace it with dataDirectory when in production.


Okay. So far we have learnt how to export our database in a sql format and how to write these data ta a file within our file system. Now we have to import these data into our database (if something inadvertently had wiped our data).


Importing a backup file into our database


So now we want to perform the opposite operation, read data from a file and use them to restore our application database. The code is easily understandable considering what we have learnt before.

function importBackup(fromDropbox) {
    var pathToFile = cordova.file.dataDirectory + '/dropboxTestBackup/dropboxTestBackup.sql';
    window.resolveLocalFileSystemURL(pathToFile, function (fileEntry) {
        fileEntry.file(function (file) {
            var reader = new FileReader();

            reader.onloadend = function (e) {
                var successFn = function () {
                    alert('Database restored successfully!');
                    loadCountries();
                    loadUsers();
                };
                cordova.plugins.sqlitePorter.importSqlToDb(db, this.result, {
                    successFn: successFn
                });
            };
            reader.readAsText(file);
        }, onErrorLoadFile);
    }, onErrorLoadFs);
}

function onErrorLoadFile(e){
    console.log('Error reading file: ' + e.toString());
}

function onErrorLoadFs(e) {
    console.log('Error loading file system: ' + e);
}

We set first the path to our backup file, then we use resolveLocalFileSystemURL() to get the fileEntry to read file's content and to use them in callback function onloadend() as parameter to give to cordova.plugins.sqlitePorter.importSqlToDb(). Once the file is loaded, we alert the user and call our two functions to fill out our controls. Voilà, les jeux sont faits!


Make buttons to work!


Do you remember the code we had written in our index.js file to attach the event handler to our buttons? It looked this way:

    $('#createDB').click(function (e) {
        e.preventDefault();
        createTables();
    });

    $('#exportDB').click(function (e) {
        e.preventDefault();

    });

    $('#emptyDB').click(function (e) {
        e.preventDefault();
                dropTables();
    });

    $('#importDB').click(function (e) {
        e.preventDefault();

    });

Now we have to add a call to exportBackup() and importBackup() functions in order to make this block of code look slightly different:

    $('#createDB').click(function (e) {
        e.preventDefault();
        createTables();
    });

    $('#exportDB').click(function (e) {
        e.preventDefault();
        exportBackup(false);
    });

    $('#emptyDB').click(function (e) {
        e.preventDefault();
        dropTables();
    });

    $('#importDB').click(function (e) {
        e.preventDefault();
        importBackup(false);
    });


I show you how the whole index.js file should look so far:

var app = {
    initialize: function () {
        document.addEventListener('deviceready', this.onDeviceReady.bind(this), false);
    },
    onDeviceReady: function () {
        this.receivedEvent('deviceready');
    },
    receivedEvent: function (id) {
        var parentElement = document.getElementById(id);
        var listeningElement = parentElement.querySelector('.listening');
        var receivedElement = parentElement.querySelector('.received');

        listeningElement.setAttribute('style', 'display:none;');
        receivedElement.setAttribute('style', 'display:block;');

        console.log('Received Event: ' + id);
    }
};

app.initialize();

var db = window.openDatabase("dropbox_test", "1.0", "Testing import/export of data process with Dropbox", 200000);

function createCountryTable() {
    db.transaction(function (tx) {
        tx.executeSql("DROP TABLE IF EXISTS countries");
        tx.executeSql("CREATE TABLE IF NOT EXISTS countries (id INTEGER PRIMARY KEY AUTOINCREMENT, country INTEGER, code TEXT)", [], countryCreatedSuccess, countryCreatedError);
        tx.executeSql("INSERT INTO countries (id, country, code) VALUES (1, 'Afghanistan', 'AF'),(2, 'Albania', 'AL'),(3, 'Algeria', 'DZ'),(4, 'Andorra', 'AD'),(5, 'Angola', 'AO'),(6, 'Antigua and Barbuda', 'AG'),(7, 'Argentina', 'AR'),(8, 'Armenia', 'AM'),(9, 'Australia', 'AU'),(10, 'Austria', 'AT'),    (11, 'Azerbaijan', 'AZ'),(12, 'Bahamas, The', 'BS'),(13, 'Bahrain', 'BH'),(14, 'Bangladesh', 'BD'),(15, 'Barbados', 'BB'),(16, 'Belarus', 'BY'),(17, 'Belgium', 'BE'),(18, 'Belize', 'BZ'),    (19, 'Benin', 'BJ'),(20, 'Bhutan', 'BT'),(21, 'Bolivia', 'BO'),(22, 'Bosnia and Herzegovina', 'BA'),(23, 'Botswana', 'BW'),(24, 'Brazil', 'BR'),(25, 'Brunei', 'BN'),(26, 'Bulgaria', 'BG'),(27, 'Burkina Faso', 'BF'),(28, 'Burundi', 'BI'),(29, 'Cambodia', 'KH'),(30, 'Cameroon', 'CM'),(31, 'Canada', 'CA'),(32, 'Cape Verde', 'CV'),(33, 'Central African Republic', 'CF'),(34, 'Chad', 'TD'),(35, 'Chile', 'CL'),(36, 'China, People''s Republic of', 'CN'),(37, 'Colombia', 'CO'),(38, 'Comoros', 'KM'),(39, 'Congo, (Congo ? Kinshasa)', 'CD'),(40, 'Congo, (Congo ? Brazzaville)', 'CG'),(41, 'Costa Rica', 'CR'),    (42, 'Cote d''Ivoire (Ivory Coast)', 'CI'),    (43, 'Croatia', 'HR'),    (44, 'Cuba', 'CU'),    (45, 'Cyprus', 'CY'),    (46, 'Czech Republic', 'CZ'),(47, 'Denmark', 'DK'),    (48, 'Djibouti', 'DJ'),    (49, 'Dominica', 'DM'),    (50, 'Dominican Republic', 'DO'),    (51, 'Ecuador', 'EC'),    (52, 'Egypt', 'EG'),    (53, 'El Salvador', 'SV'),    (54, 'Equatorial Guinea', 'GQ'),    (55, 'Eritrea', 'ER'),    (56, 'Estonia', 'EE'),    (57, 'Ethiopia', 'ET'),    (58, 'Fiji', 'FJ'),    (59, 'Finland', 'FI'),    (60, 'France', 'FR'),    (61, 'Gabon', 'GA'),(62, 'Gambia, The', 'GM'),(63, 'Georgia', 'GE'),(64, 'Germany', 'DE'),    (65, 'Ghana', 'GH'),    (66, 'Greece', 'GR'),    (67, 'Grenada', 'GD'),    (68, 'Guatemala', 'GT'),    (69, 'Guinea', 'GN'),    (70, 'Guinea-Bissau', 'GW'),    (71, 'Guyana', 'GY'),    (72, 'Haiti', 'HT'),    (73, 'Honduras', 'HN'),    (74, 'Hungary', 'HU'),    (75, 'Iceland', 'IS'),    (76, 'India', 'IN'),    (77, 'Indonesia', 'ID'),    (78, 'Iran', 'IR'),    (79, 'Iraq', 'IQ'),    (80, 'Ireland', 'IE'),    (81, 'Israel', 'IL'),    (82, 'Italy', 'IT'),(83, 'Jamaica', 'JM'),    (84, 'Japan', 'JP'),    (85, 'Jordan', 'JO'),    (86, 'Kazakhstan', 'KZ'),    (87, 'Kenya', 'KE'),    (88, 'Kiribati', 'KI'),    (89, 'Korea, North', 'KP'),    (90, 'Korea, South', 'KR'),    (91, 'Kuwait', 'KW'),(92, 'Kyrgyzstan', 'KG'),    (93, 'Laos', 'LA'),    (94, 'Latvia', 'LV'),    (95, 'Lebanon', 'LB'),    (96, 'Lesotho', 'LS'),    (97, 'Liberia', 'LR'),    (98, 'Libya', 'LY'),    (99, 'Liechtenstein', 'LI'),    (100, 'Lithuania', 'LT'),    (101, 'Luxembourg', 'LU'),    (102, 'Macedonia', 'MK'),    (103, 'Madagascar', 'MG'),    (104, 'Malawi', 'MW'),    (105, 'Malaysia', 'MY'),    (106, 'Maldives', 'MV'),    (107, 'Mali', 'ML'),    (108, 'Malta', 'MT'),    (109, 'Marshall Islands', 'MH'),    (110, 'Mauritania', 'MR'),    (111, 'Mauritius', 'MU'),    (112, 'Mexico', 'MX'),    (113, 'Micronesia', 'FM'),    (114, 'Moldova', 'MD'),    (115, 'Monaco', 'MC'),    (116, 'Mongolia', 'MN'),    (117, 'Montenegro', 'ME'),    (118, 'Morocco', 'MA'),    (119, 'Mozambique', 'MZ'),    (120, 'Myanmar (Burma)', 'MM'),    (121, 'Namibia', 'NA'),    (122, 'Nauru', 'NR'),    (123, 'Nepal', 'NP'),    (124, 'Netherlands', 'NL'),    (125, 'New Zealand', 'NZ'),    (126, 'Nicaragua', 'NI'),    (127, 'Niger', 'NE'),    (128, 'Nigeria', 'NG'),    (129, 'Norway', 'NO'),    (130, 'Oman', 'OM'),    (131, 'Pakistan', 'PK'),    (132, 'Palau', 'PW'),    (133, 'Panama', 'PA'),    (134, 'Papua New Guinea', 'PG'),    (135, 'Paraguay', 'PY'),    (136, 'Peru', 'PE'),    (137, 'Philippines', 'PH'),    (138, 'Poland', 'PL'),    (139, 'Portugal', 'PT'),    (140, 'Qatar', 'QA'),    (141, 'Romania', 'RO'),    (142, 'Russia', 'RU'),    (143, 'Rwanda', 'RW'),    (144, 'Saint Kitts and Nevis', 'KN'),    (145, 'Saint Lucia', 'LC'),    (146, 'Saint Vincent and the Grenadines', 'VC'),    (147, 'Samoa', 'WS'),    (148, 'San Marino', 'SM'),    (149, 'Sao Tome and Principe', 'ST'),    (150, 'Saudi Arabia', 'SA'),    (151, 'Senegal', 'SN'),    (152, 'Serbia', 'RS'),    (153, 'Seychelles', 'SC'),    (154, 'Sierra Leone', 'SL'),    (155, 'Singapore', 'SG'),    (156, 'Slovakia', 'SK'),    (157, 'Slovenia', 'SI'),    (158, 'Solomon Islands', 'SB'),    (159, 'Somalia', 'SO'),    (160, 'South Africa', 'ZA'),    (161, 'Spain', 'ES'),    (162, 'Sri Lanka', 'LK'),    (163, 'Sudan', 'SD'),    (164, 'Suriname', 'SR'),    (165, 'Swaziland', 'SZ'),    (166, 'Sweden', 'SE'),    (167, 'Switzerland', 'CH'),    (168, 'Syria', 'SY'),    (169, 'Tajikistan', 'TJ'),    (170, 'Tanzania', 'TZ'),    (171, 'Thailand', 'TH'),    (172, 'Timor-Leste (East Timor)', 'TL'),    (173, 'Togo', 'TG'),    (174, 'Tonga', 'TO'),    (175, 'Trinidad and Tobago', 'TT'),    (176, 'Tunisia', 'TN'),    (177, 'Turkey', 'TR'),    (178, 'Turkmenistan', 'TM'),    (179, 'Tuvalu', 'TV'),    (180, 'Uganda', 'UG'),    (181, 'Ukraine', 'UA'),    (182, 'United Arab Emirates', 'AE'),    (183, 'United Kingdom', 'GB'),    (184, 'United States', 'US'),    (185, 'Uruguay', 'UY'),    (186, 'Uzbekistan', 'UZ'),    (187, 'Vanuatu', 'VU'),    (188, 'Vatican City', 'VA'),    (189, 'Venezuela', 'VE'),    (190, 'Vietnam', 'VN'),    (191, 'Yemen', 'YE'),    (192, 'Zambia', 'ZM'),    (193, 'Zimbabwe', 'ZW'),    (194, 'Abkhazia', 'GE'),    (195, 'China, Republic of (Taiwan)', 'TW'),    (196, 'Nagorno-Karabakh', 'AZ'),    (197, 'Northern Cyprus', 'CY'),    (198, 'Pridnestrovie (Transnistria)', 'MD'),    (199, 'Somaliland', 'SO'),    (200, 'South Ossetia', 'GE'),    (201, 'Ashmore and Cartier Islands', 'AU'),    (202, 'Christmas Island', 'CX'),    (203, 'Cocos (Keeling) Islands', 'CC'),    (204, 'Coral Sea Islands', 'AU'),    (205, 'Heard Island and McDonald Islands', 'HM'),    (206, 'Norfolk Island', 'NF'),    (207, 'New Caledonia', 'NC'),    (208, 'French Polynesia', 'PF'),    (209, 'Mayotte', 'YT'),    (210, 'Saint Barthelemy', 'GP'),    (211, 'Saint Martin', 'GP'),    (212, 'Saint Pierre and Miquelon', 'PM'),    (213, 'Wallis and Futuna', 'WF'),    (214, 'French Southern and Antarctic Lands', 'TF'),    (215, 'Clipperton Island', 'PF'),    (216, 'Bouvet Island', 'BV'),    (217, 'Cook Islands', 'CK'),    (218, 'Niue', 'NU'),    (219, 'Tokelau', 'TK'),    (220, 'Guernsey', 'GG'),    (221, 'Isle of Man', 'IM'),    (222, 'Jersey', 'JE'),    (223, 'Anguilla', 'AI'),    (224, 'Bermuda', 'BM'),    (225, 'British Indian Ocean Territory', 'IO'),    (226, 'British Sovereign Base Areas', ''),    (227, 'British Virgin Islands', 'VG'),    (228, 'Cayman Islands', 'KY'),    (229, 'Falkland Islands (Islas Malvinas)', 'FK'),    (230, 'Gibraltar', 'GI'),    (231, 'Montserrat', 'MS'),    (232, 'Pitcairn Islands', 'PN'),    (233, 'Saint Helena', 'SH'),    (234, 'South Georgia & South Sandwich Islands', 'GS'),    (235, 'Turks and Caicos Islands', 'TC'),    (236, 'Northern Mariana Islands', 'MP'),    (237, 'Puerto Rico', 'PR'),    (238, 'American Samoa', 'AS'),    (239, 'Baker Island', 'UM'),    (240, 'Guam', 'GU'),    (241, 'Howland Island', 'UM'),    (242, 'Jarvis Island', 'UM'),    (243, 'Johnston Atoll', 'UM'),    (244, 'Kingman Reef', 'UM'),    (245, 'Midway Islands', 'UM'),    (246, 'Navassa Island', 'UM'),    (247, 'Palmyra Atoll', 'UM'),    (248, 'U.S. Virgin Islands', 'VI'),    (249, 'Wake Island', 'UM'),    (250, 'Hong Kong', 'HK'),    (251, 'Macau', 'MO'),    (252, 'Faroe Islands', 'FO'),    (253, 'Greenland', 'GL'),    (254, 'French Guiana', 'GF'),    (255, 'Guadeloupe', 'GP'),    (256, 'Martinique', 'MQ'),    (257, 'Reunion', 'RE'),    (258, 'Aland', 'AX'),    (259, 'Aruba', 'AW'),    (260, 'Netherlands Antilles', 'AN'),    (261, 'Svalbard', 'SJ'),    (262, 'Ascension', 'AC'),    (263, 'Tristan da Cunha', 'TA'),    (268, 'Australian Antarctic Territory', 'AQ'),    (269, 'Ross Dependency', 'AQ'),    (270, 'Peter I Island', 'AQ'),    (271, 'Queen Maud Land', 'AQ'),    (272, 'British Antarctic Territory', 'AQ');", [], countryFilledSuccess, countryFilledError);
        function countryCreatedSuccess() {
            console.log('Country table successfully created!');
        }
        function countryCreatedError(tx, error) {
            console.log(error.message);
        }
        function countryFilledSuccess() {
            console.log('Country table successfully filled!');
            loadCountries();
        }
        function countryFilledError(tx, error) {
            console.log(error.message);
        }

    });
}

function createUsersTable() {
    db.transaction(function (tx) {
        tx.executeSql("DROP TABLE IF EXISTS users");
        tx.executeSql("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT, email_address TEXT, country TEXT)", [], userCreatedSuccess, userCreatedError);
        tx.executeSql("INSERT INTO users (id, first_name, last_name, email_address, country) VALUES (1, 'John', 'Doe', 'john.doe@email.com', 'USA'), (2, 'Miguel', 'Olivares', 'miguel.olivares.Doe@email.es', 'Spain'), (3, 'Franz', 'Kuttermeyer', 'frankut@email.de', 'Germany'), (4, 'Marianne', 'Jolie', 'mariannejolie@email.fr', 'France')", [], userFilledSuccess, userFilledError);
        function userCreatedSuccess() {
            console.log('Users table successfully created!');
        }
        function userCreatedError(tx, error) {
            console.log(error.message);
        }
        function userFilledSuccess() {
            console.log('Users table successfully filled!');
            loadUsers();
        }
        function userFilledError(tx, error) {
            console.log(error.message);
        }
    });
}

function createTables() {
    createCountryTable();
    createUsersTable();
}

function dropCountriesTable() {
    db.transaction(function (tx) {
        tx.executeSql("DROP TABLE IF EXISTS countries", [], dropCountriesSuccess, dropCountriesError);
        function dropCountriesSuccess() {
            console.log('Countries table successfully dropped!');
            loadCountries();
        }
        function dropCountriesError(tx, error) {
            console.log(error.message);
        }
    });
}

function dropUsersTable() {
    db.transaction(function (tx) {
        tx.executeSql("DROP TABLE IF EXISTS users", [], dropUsersSuccess, dropUsersError);
        function dropUsersSuccess() {
            console.log('Users table successfully dropped!');
            loadUsers();
        }
        function dropUsersError(tx, error) {
            console.log(error.message);
        }
    });
}
function dropTables() {
    dropUsersTable();
    dropCountriesTable();
}

function loadCountries() {
    var qry = "SELECT id, country FROM countries";
    db.transaction(function (tx) {
        tx.executeSql(qry, [], querySuccess, queryError);
        function querySuccess(tx, data) {
            $('select#countries').children().remove();
            var countries = {};
            for (var i = 0; i < data.rows.length; i++) {
                $('select#countries').append('<option value="' + data.rows[i].id + '">' + data.rows[i].country + '</option>');
            }
        }
        function queryError(transaction, error) {
            console.log('Query errorHandler ' + error.message + ' in query ' + qry);
            //we use the error callback function to empty page controls   
            $('select#countries').children().remove();
            $('select#countries').append('<option>No country data was found!</option>');
        }
    });
}

function loadUsers() {
    var qry = "SELECT first_name, last_name, email_address, country FROM users";
    db.transaction(function (tx) {
        tx.executeSql(qry, [], querySuccess, queryError);
        function querySuccess(tx, data) {
            $('#users').children().remove();
            for (var i = 0; i < data.rows.length; i++) {
                $('#users').append('<li class="dropdown">'
                        + data.rows[i].first_name
                        + ' '
                        + data.rows[i].last_name
                        + '<ul class="submenu"><li>'
                        + data.rows[i].email_address
                        + '</li><li>'
                        + data.rows[i].country
                        + '</li></ul></li>');
            }
        }
        function queryError(transaction, error) {
            console.log('Query errorHandler ' + error.message + ' in query ' + qry);
            //we use the error callback function to empty page controls   
            $('#users').children().remove();
            $('#users').append('<option>No user data was found!</option>');
        }
    });
}

function exportBackup() {
    var successFn = function (sql) {
        window.resolveLocalFileSystemURL(cordova.file.dataDirectory, function (dirEntry) {
            dirEntry.getDirectory('/dropboxTestBackup', {create: true}, function (dirEntry) {
                dirEntry.getFile('dropboxTestBackup.sql', {create: true}, function (fileEntry) {
                    alert("The file " + fileEntry.name + ' has been created in the following directory: Android/data/com.example.dropboxTest/files/dropboxTestBackup/');
                    writeFile(fileEntry, sql);
                });
            }, onErrorCreateFile);
        }, onErrorCreateDir);
    };
    cordova.plugins.sqlitePorter.exportDbToSql(db, {
        successFn: successFn
    });
}

function onErrorCreateFile(e) {
    console.log('Error creating file: ' + e);
}

function onErrorCreateDir(e) {
    console.log('Error creating directory: ' + e);
}

function writeFile(fileEntry, dataObj) {
    // Create a FileWriter object for our FileEntry.
    fileEntry.createWriter(function (fileWriter) {
        fileWriter.onwriteend = function () {
            console.log("Successful file write...");
            readFile(fileEntry);
        };
        fileWriter.write(dataObj);
    }, onErrorWriteFile);
}

function onErrorWriteFile(e) {
    console.log('Error writing file: ' + e.toString());
}

function readFile(fileEntry) {
    fileEntry.file(function (file) {
        var reader = new FileReader();
        reader.onloadend = function () {
            console.log("Successful file read: " + fileEntry.fullPath + " - content: " + this.result);
            return this.result;
        };
        reader.readAsText(file);
    }, onErrorReadFile);
}

function onErrorReadFile(e) {
    console.log('Error reading file: ' + e);
}

function importBackup(fromDropbox) {
    var pathToFile = cordova.file.dataDirectory + '/dropboxTestBackup/dropboxTestBackup.sql';
    window.resolveLocalFileSystemURL(pathToFile, function (fileEntry) {
        fileEntry.file(function (file) {
            var reader = new FileReader();

            reader.onloadend = function (e) {
                var successFn = function () {
                    alert('Database restored successfully!');
                    loadCountries();
                    loadUsers();
                };
                cordova.plugins.sqlitePorter.importSqlToDb(db, this.result, {
                    successFn: successFn
                });
            };
            reader.readAsText(file);
        }, onErrorLoadFile);
    }, onErrorLoadFs);
}

function onErrorLoadFile(e){
    console.log('Error reading file: ' + e.toString());
}

function onErrorLoadFs(e) {
    console.log('Error loading file system: ' + e);
}

$(document).ready(function () {
    
    loadCountries();
    loadUsers();
    
    $('#createDB').click(function (e) {
        e.preventDefault();
        createTables();
    });

    $('#exportDB').click(function (e) {
        e.preventDefault();
        exportBackup(false);
    });

    $('#emptyDB').click(function (e) {
        e.preventDefault();
        dropTables();
    });

    $('#importDB').click(function (e) {
        e.preventDefault();
        importBackup(false);
    });
    
    $('#users').on('click', 'li.dropdown', function (e) {
        e.preventDefault();
        console.log($(this).text());
        var items = $(this).siblings().find('ul.submenu');
        items.each(function () {
            if ($(this).is(':visible')) {
                $(this).slideUp('slow');
            }
        });
        $(this).find('ul.submenu').slideToggle();
    });    
    
});

Now we can export our database to a local file and if it is necessary, restore it from the backup file. Just run the app, and follow these steps to test the app:


  1. Tap Create database to create the database (if you didn't do it yet)
  2. Export it tapping Export database button
  3. Now tap  Empty database button to drop out all your data
  4. and then tap Import database button to see your data live again!


Great, isn't it?


But we want to be sure our user can get back his data even if he has uninstalled our application in a moment of mental disorder and then, once he has returned to reason, he has reinstalled it. So we need to offer him the option to save his backup file to some external host. Do you guess? Yeah, Dropbox! And this will be the argument of the next, final chapter of this tutorial.


Read Part 1 


Read Part 3



0
Comment
Author:Marco Gasi
1 Comment
 
LVL 31

Author Comment

by:Marco Gasi
Okay, I dropped all the SQLPorter fixing story: since the fix has been included in the official repo it doesn't make sense explain how to fix a bug which doesn't exist :)
Just a question: can I add to these articles a link to my blog?
Thank you for your work here.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month