Some time ago, I was developing a hybrid mobile application for Android using Cordova. In that application, I needed to save the whole local database built with WebSql to Dropbox.
So I started to learn how to use Dropbox API, how to integrate it with Cordova, which plugins I had to install and I have also found a couple of tricks to get everything to work. So I now want to share what I did to get the job done.
Important: Though this tutorial is focused on developing an app for Android, almost everything should work even for Ios. If you find any problems, please refer to official Cordova documentation.
The whole tutorial is divided into three chapters:
- in this first one, we’ll build a skeleton application using Cordova; we’ll create a database with some data and we’ll load data into the application.
- in the second part we’ll see how actually export our database and how to restore it; to do this we’ll use a Cordova plugin to do all the dirty job for us
- in the third and final chapter we’ll see how to connect our Cordova application with Dropbox and save there our backup file (and how to download it to restore our local database)
 |
I assume you are already familiar with Cordova so I won’t explain any superflous detail about Cordova application structure, how you have to cennect your phone or how you can build and run a Cordova project. If you don’t know Cordova or you are not sure about something, please refer to official documentation. |
Required software
- The Dropbox JavaScript SDK to manage the authentication process and the connection with a Dropbox account;
- The cordova-plugin-inappbrowser
- The cordova-plugin-file
- The cordova-sqlite-porter
We’ll install them when we’ll need having them up and running. First of all, we have to create a small testing app.
Starting a new Cordova application
So let’s start creating a small Cordova app to test our Dropbox connection. Open you command line interface (it will be the command prompt in Windows and the terminal window in Linux), navigate to the directory where you keep your Cordova apps and type:
cordova create dropboxTest com.codingfix.dropboxTest DropboxTest
And then we enter new project directory and add Android platform:
cd dropboxTest cordova platforms add android
Preparing the application main (and only) page
The poorest CSS ever
Now you can start to edit your application. First of all, open the file index.html in www folder and drop the div with class “app” and all its content: we don’t need it. Also replace the whole content of the index.css file with the following lines (our little app will be very poor form the GUI perspective, but feel free to play to improve it :)):
#users > li{
list-style: none;
margin: 10px auto 10px -40px;
border-bottom: 1px solid #333;
}
li.dropdown > ul{
display: none;
}
.centered{
text-align: center;
}
.btn,
.btn:focus,
.btn:active,
.btn:visited{
background-color: cornflowerblue;
display: block;
padding: 10px 20px;
font-size: 14px;
color: white;
width: 120px;
margin: 30px auto;
text-decoration: none;
}
The markup
Now, open index.html file and replace the div “app” with the following markup:
<h3>Registered users</h3> <ul id="users"> </ul> <br> <div class="centered"> <h3>Select a country:</h3> <select id="countries"> </select> <a href="#" id="createDB" class="btn btn-primary">Create tables</a> <a href="#" id="exportDB" class="btn btn-primary">Export database</a> <a href="#" id="emptyDB" class="btn btn-primary">Empty database</a> <a href="#" id="importDB" class="btn btn-primary">Import database</a> </div>
The unordered list will… list our fake users and the select will list all the countries we have in our database.
Okay, for the markup that’s all. Hey, I had said it was a very poor user interface!
Oh, I’m forgetting… we have to add jQuery! Download the latest version of jQuery and put jquery-x.x.x.min.js in your js folder and add a reference to it in your index.html file just before the reference to our index.js.
Starting with index.js
The only thing we have to do for the moment is to remove the following lines of code which belong to the default Cordova app:
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);
Okay, you can use your CLI to navigate to your project directory /dropboxTest/ and run the app just to check everything is in place:
cordova run android
You should see the image below:
Creating the database
Now, it’s time to create our database with a couple of tables and some data, just to have something to play with when we’ll have to export our database to Dropbox (and import it back to our app). First we need to create the database and get a reference to the database object. Put this line immediately after app.initialize();
var db = window.openDatabase("dropboxTest", "1.0", "Testing import/export of data process with Dropbox", 200000);
Now we’re going to create a users table with 4 users and a countries table which holds all countries in the world. We’ll wrap the needed code in 2 functions, createUsersTable() and createUsersTable() in order we can call them when we’ll need. So, put following code in your js/index.js after having created the database:
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();
}
Dropping tables
We also need something to drop our tables, so we’ll can test import database function. Let’s do it:
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();
}
Load data in our markup
Fine. Now we have to write the functions to load our data and fill our page with them. Go back to js/index.js file and go on adding following functions:
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>');
}
});
}
So, we have 2 functions which load data from the database and use them to fill out our controls in the application main (and unique) page. These functions are called in the success callback of the functions which create the tables.
Now we add the event handlers for our buttons: for the moment only two buttons will do something interesting. Of course, we put the event handlers withing tha main jQuery function; here we call our two functions to populate our controls with the data if the database has been already created.
$(document).ready(function () {
loadCountries();
loadUsers();
$('#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();
});
});
As a final touch, we add a few lines of javascript to make our users’ list work as an accordion (I have used here a slightly different version of the code I illustrated in my article The simplest jQuery accordion ever!):
$('#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();
});
Summarizing
Just to be sure everything is clear, I show you how each application file looks as far (I have just removed superflous comments).
index.html
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Security-Policy" content="default-src 'self' data: gap: https://ssl.gstatic.com 'unsafe-eval'; style-src 'self' 'unsafe-inline'; media-src *; img-src 'self' data: content:;">
<meta name="format-detection" content="telephone=no">
<meta name="msapplication-tap-highlight" content="no">
<meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width">
<link rel="stylesheet" type="text/css" href="css/index.css">
<title>Hello World</title>
</head>
<body>
<h3>Registered users</h3>
<ul id="users">
</ul>
<br>
<div class="centered">
<h3>Select a country:</h3>
<select id="countries">
</select>
<a href="#" id="createDB" class="btn btn-primary">Create tables</a>
<a href="#" id="exportDB" class="btn btn-primary">Export database</a>
<a href="#" id="emptyDB" class="btn btn-primary">Empty database</a>
<a href="#" id="importDB" class="btn btn-primary">Import database</a>
</div>
<script type="text/javascript" src="cordova.js"></script>
<script type="text/javascript" src="js/jquery-2.2.3.min.js"></script>
<script type="text/javascript" src="js/index.js"></script>
</body>
</html>
index.css
#users > li{
list-style: none;
margin: 10px auto 10px -40px;
border-bottom: 1px solid #333;
}
li.dropdown > ul{
display: none;
}
.centered{
text-align: center;
}
.btn,
.btn:focus,
.btn:active,
.btn:visited{
background-color: cornflowerblue;
display: block;
padding: 10px 20px;
font-size: 14px;
color: white;
width: 120px;
margin: 30px auto;
text-decoration: none;
}
index.js
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>');
}
});
}
$(document).ready(function () {
loadCountries();
loadUsers();
$('#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();
});
$('#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, if you run our app and tap on Create tables button, you should see something like this:
After having clicked on Empty database button, the app will look like the first screenshot again.
Well, now that we have our skeleton application we can go with the next part: activate a connection with the user’s Dropbox account. Let’s go to part 2 of this tutorial!