Save Cordova database to Dropbox

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

Leave a Comment / Cordova / By

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().

SQLite Porter plugin

SQLite Porter allow you to export and import a database using just a few lines of code. You can export the database to a json file or to a sql file using exportDbToJson() and exportDbToSql() functions. To import a databse you can use the correspondent functions importJsonToDb() and importSqlToDb().

So, open your command prompt o 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

Exporting the database to a backup 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 follwing 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 phisical 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 ouput 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 vis a USB cable, you have to use externalDataDirectory instead of dataDirectory, but this expose 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.

 

 

Leave a Comment

Your email address will not be published. Required fields are marked *