Create a CRUD web app using JQuery Mobile & WebSQL

Introduction

I seek to demonstrate how to one can create a CRUD web mobile application using JQuery Mobile and WebSQL. Whilst the future of WebSQL seems to be dwingling due to it no longer being supported by W3C as more is being put on IndexedDB. WebSQL is a relational database that sits on a browser and IE and FireFox does not support it unfortunately but any webkit related web browser has its functionality.

To make this work, we will create a javascript file to handle all the database operations, like creating a database, creating a table, creating indexes, inserting records, updating records and deleting records and returning all records from a table.

Some silly assumptions: I will assume that you know some WebSQL and how to perform SQL queries. I will also assume that you know some JQuery Mobile Development and how to link javascript files via code to your html files.

Download AddressBookSQL.zip

Background

CRUD applications are developed for basically three things, CR - create records, U - update records and D - delete records. In the past, I have written a couple of articles that basically spoke about how one can perform those functionalities using different frameworks. These are:

1. CRUD using LocalStorage
2. CRUD using single JSON with PHP for a web server

and now this framework, using WebSQL. For more details about WebSQL, you can read this wiki document here.

Using the code

WEBSQL.JS - database maintenance

Using a separation of concerns, I have separated the code that reads and writes to the database in a single javascript file. This ensures that only objects can be passed to the WebSQL file to manage the record manipulation. I will first talk about the attached websql.js file attached in the source code and how you can use it in your own apps.

// variables for database table maintenance
var DB_REAL = "REAL";
var DB_INTEGER = "INTEGER";
var DB_BLOB = "BLOB";
var DB_TEXT = "TEXT";
var DB_FLOAT = "FLOAT";
var DB_NUMERIC = "NUMERIC";

These are the various field types that one can create for the WebSQL database, however the one most used in the example application here is DB_TEXT. I declare these as constants that will be accessed by my app code.

function Left(str, n) {
    // return a left part of a string
    var s = str + '';
    var iLen = s.length;
    if (n <= 0) {
        return "";
        } else if (n >= iLen) {
        return str;
        } else {
        return s.substr(0, n);
    }
}

This is helper function that will return the Left part of a string, e.g. Left("My Name", 2) will return "My".

function Len(str) {
    // return the length of a string
    if (typeof (str) === 'object') {
        return str.length;
    }
    str += '';
    return str.length;
}

This is also another helper function to return the length of a string, e.g. Len("Anele Mbanga") will return 12. It is used by the WebSQL script file to maintain the databases.

Creating and Opening the database

function SqlOpenDb(shortName, version, displayName, maxSize) {
    // code to open the database and returns a variable, one can open different
    // databases, database size is 1MB, increase dbsize to <= 5
    var db, dbsize = 1;
    try {
        if (!window.openDatabase) {
            return 0;
            } else {
            if (typeof (shortName) === 'undefined') {
                return 0;
            }
            if (typeof (version) === 'undefined') version = "";
            if (typeof (displayName) === 'undefined') displayName = shortName;
            if (typeof (maxSize) === 'undefined') maxSize = dbsize * (1024 * 1024);
            db = openDatabase(shortName, version, displayName, maxSize);
        }
        } catch (e) {
        return 0;
    }
    return db;
}

The SqlOpenDb script is the main script that creates and opens the database if it does not exist. The maximum database size for websql as on writing is 5MB. The usage of this function is that an object is returned by this function which will hold all functions to pass to the database. If the browser does not support WebSQL, a zero is returned.

To open the database, one can call this function like this...

var db = SqlOpenDb("AddressBook");

this will create a 1MB database on the browser for storing all your records. After opening a database you need to create the tables that will store the information and then insert records therein. I forgot to mention, the way the database maintenance functions are called here is though using a deferred method of JQuery. I found this helpful not to create extra functions to call for the success or failure of execute methods. This then means that a database transaction is called and when thats done other functions can be performed depending on success or failure. You will see this soon.

Creating a Table

function SqlCreateTable(db, TableName, FieldsAndTypes, PrimaryKey, AutoIncrement) {
    // code to create a table in the websql database
    // fieldsandtypes is a json object
    // autoincrement is the field name to autoincrement
    var sb = "(";
    for (item in FieldsAndTypes) {
        sb += "[" + item + "] " + FieldsAndTypes[item];
        if (item == PrimaryKey) {
            sb += " NOT NULL PRIMARY KEY";
        }
        if (item == AutoIncrement) {
            sb += " AUTOINCREMENT";
        }
        sb += ", ";
    }
    sb = Left(sb, (Len(sb) - 2));
    sb += ")";
    sb = "CREATE TABLE IF NOT EXISTS [" + TableName + "] " + sb + ";";
    return Execute(db, sb);
}

This method above is the method used to create a table in the database. This method checks if the database table exists and if not its created. As you can see, the Left and Len method defined previously are used in this function.

To create a table, you pass the database object that was used in SqlOpenDb function, then the table name to create and then the FieldsAndTypes, the field name that will be the primarykey and the fieldname that will autoincrement. Remember, javascript is case sensitive. This function loops through the object passed, gets the field names, field types and builds up the CREATE TABLE statement. If the PrimaryKey given matches any of the given field names this will be marked as so and also same as the auto increment field.

Specifying Field Names and Types

When creating tables, you need to specify the field names and types first before calling the method. This is actually easier that you think, however if you checked the source code above, you will know that you just pass an object that can be looped. Lets define the object to pass, in this example below. We will create a contact table with some few fields.

var FT = {};

FT.FullName = DB_TEXT;

FT.MobileNumber = DB_NUMERIC;

SqlCreateTable(db, "Contacts", FT, "FullName", "");

Putting that in a function will easily create your table, and like I said, if it does not exist.

Insert a Record

We have opened a database, created a table and now its time to add records to it. The same approach to create a table is followed here.

function SqlInsertRecord(db, tblName, tblRecord) {
    // code to insert a record into the database
    // fields are passed as parameters
    var qry, flds = "", vals = "", avals = [];
    for (var key in tblRecord) {
        flds += "[" + key + "],";
        vals += "?,";
        avals.push(tblRecord[key]);
    }
    flds = Left(flds, Len(flds) - 1);
    vals = Left(vals, Len(vals) - 1);
    qry = "INSERT INTO [" + tblName + "] (" + flds + ") VALUES (" + vals + ");";
    return Execute(db, qry, avals);
}

SqlInsertRecord gets passed the database object, the table name to update and then the record object to add. One defines the tblRecord like this.

var tblRec = {};

tblRec.FullName = "Anele Mbanga";

tblRec.MobileNumber = 123456789

SqlInsertRecord(db, "Contacts", tblRec);

As you can see, the database methods are fairly straightforward but what will differ will be the objects passed to them to maintain the records in your database.

Get Records from a table

Now that you have opened a database, created a table and added up a record, how do you read it? Records read from a table are returned as a json object. You can read a single record or return all records from a table.

function SqlGetRecordWhere(db, tblName, tblWhere) {
    // code to get a record from database using a where clause
    // tblWhere should be objects
    var qry = "", vals = "", avals = [];
    for (item in tblWhere) {
        vals += "[" + item + "] = ? AND ";
        avals.push(tblWhere[item]);
    }
    vals = Left(vals, Len(vals) - 5);
    qry = "SELECT * FROM [" + tblName + "] WHERE " + vals + ";";
    return Execute(db, qry, avals);
}

SqlGetRecordWhere will return a single / multiple records from a table depending on your WHERE clause. The tblWhere clause is also an object that we parse to return our records. This uses a parametised query to extract the records from the database. Also you pass the database object, the table name and the where object clause. For example, to return records with FullName = "Anele Mbanga", you would define a where clause like this.

var rWhere = {};

rWhere.FullName = "Anele Mbanga";

SqlGetRecordWhere(db, "Contacts", rWhere);

// return all records from a table sorted by primary key

function SqlGetRecords(db, TableName, PrimaryKey) {
    // return all records from a table ordered by primary key
    var qry = "SELECT * FROM [" + TableName + "] ORDER BY [" + PrimaryKey +"]";
    return Execute(db, qry);
};

To return all records from the database then our code would be:

SqlGetRecords(db, "Contacts", "FullName");

Update an existing record

To update an existing record, we pass SqlUpdateRecordWhere some few objects, 1, the fields we want to update and the WHERE clause like above, the database object and the table name.

function SqlUpdateRecordWhere(db, tblName, tblRecord, tblWhere) {
    // code to update a record on a database
    // tblRecord and tblWhere should be objects
    var qry = "", vals = "", wvals = "", avals = [];
    for (item in tblRecord) {
        vals += "[" + item + "] = ?,";
        avals.push(tblRecord[item]);
    }
    for (item in tblWhere) {
        wvals += "[" + item + "] = ? AND ";
        avals.push(tblWhere[item]);
    }
    vals = Left(vals, Len(vals) - 1);
    wvals = Left(wvals, Len(wvals) - 5);
    qry = "UPDATE [" + tblName + "] SET " + vals + " WHERE " + wvals + ";";
    return Execute(db, qry, avals);
}

This creates the UPDATE statement to update the table and then runs Execute to update the table. To update the MobileNumber of our contact for example, we would define our process like this.

var rM = {}, rW = {};

rM.MobileNumber = 98765432

rW.FullName = "Anele Mbanga";

SqlUpdateRecordWhere(db, "Contacts", rM, rW);

Deleting existing record(s).

To delete existing records, you also pass the object to delete just like the examples above.

function SqlDeleteRecordWhere(db, tblName, tblWhere) {
    // delete a record from a table using a where clause
    // pass the where fields as parameters
    var qry, wvals = "", avals = [];
    for (item in tblWhere) {
        wvals += "[" + item + "] = ? AND ";
        avals.push(tblWhere[item]);
    }
    // remove last ' AND '
    wvals = Left(wvals, Len(wvals) - 5);
    qry = "DELETE FROM [" + tblName + "] WHERE " + wvals + ";";
    return Execute(db, qry, avals);
};

The function loops through each field specified and builds the DELETE statement. An example to delete a record would be:

var dR = {};

dR.FullName = "Anele Mbanga";

SqlDeleteRecordWhere(db, "Contacts", dR);

Returning Distinct Fields

At times you might want to return a distinct field from the database,

function SqlGetDistinctField(db, TableName, FldName) {
    // return distinct records from a table
    var qry = "SELECT DISTINCT [" + FldName + "] FROM [" + TableName + "] ORDER BY [" + FldName +"]";
    return Execute(db, qry);
};

SqlGetDistinctField will help with that. As you can see you can define your own SQL Statements that you can pass to the Execute function. Let's explain the Execute function then.

The Execute method is the main function that executes the SQL statements in the database in our deferred way here. Let me explain it.

function Execute(db, qry, args){
    // execute a query against the database using defer
    if (typeof (args) === 'undefined') args = [];
    return $.Deferred(function (d) {
        db.transaction(function (tx) {
            tx.executeSql(qry, args, successWrapper(d), failureWrapper(d));
        });
    });
};

The execute method gets passed the database object, the query string we want to execute and then an array that has the arguements that we want to process. This returns a JQuery Deferred object that is then processed with successWrapper and failureWrapper when our operation is successful or fails respecively.

function successWrapper(d) {
    // when sql query succeeds
    return (function (tx, data) {
        d.resolve(data)
    })
};

function failureWrapper(d) {
    // when sql query fails
    return (function (tx, error) {
        d.reject(error)
    })
};

When our database operation is succesful, a ResultSet is returned as the data object and this can be passed and processed. In most cases, you will want to return all the data of the resultset as a simple object. As I wanted my code to be compatible easily whether I developed for LocalStorage or the Php ajax calls as demonstrated in the previous articles, I followed the same partern here and convert it to a JSON array.

function ResultSetToJSON(results, PrimaryKey) {
    // process data returned by successWrapper;
    // return it as a json object using primary key as key
    var Records = {};
    var len = results.rows.length - 1, priKey, i, row;
    // loop through each row
    for (i = 0; i <= len; i++) {
        // get the row
        row = results.rows.item(i);
        // get the primary key
        priKey = row[PrimaryKey];
        // cleanse the primary key
        priKey = priKey.split(' ').join('-');
        // set row to object using primary key
        Records[priKey] = row;
    }
    return Records;
}

with this function, data returned with successWrapper, is passed to ResultSetToJSON specifying the primary key to return a JSON object that will be accessible with each primary key of all records in your table.

We have in detail explained the database operations, now lets create our user interface to use these operations. Let's create a simple Address Book CRUD app.

The User Interface

Figure 1 - Create/Update a new Contact Details

Figure 2 - Contact Address Details

Figure 3 - Address Book Listing

The figures above indicate the user interface we will create to demonstrate our CRUD app. We want to capture address details and save them and also be able to delete an address when we want.

HTML Definition - Add Contacts Screen

<div id="pgAddContact" data-role="page">
                <div data-position="left" data-display="reveal" data-position-fixed="true" id="pgAddContactPnl" data-role="panel">
                    <ul data-role="listview" id="pgAddContactPnlLV">
                        <li ><a data-transition="slide" href="#pgAddContact">New</a></li>
                        <li ><a data-transition="slide" href="#pgContactMindCity">Relationships > City</a></li>
                        <li ><a data-transition="slide" href="#pgRptContact">Report</a></li>
                        <li ><a data-transition="slide" href="#pgContact">Back</a></li>
                    </ul>
                </div>
                
                <header id="pgAddContactHdr" data-role="header" data-position="fixed">
                    <h1>Address Book</h1>
                    <a data-role="button" id="pgAddContactMenu" href="#pgAddContactPnl" data-icon="bars" class="ui-btn-left">Menu</a>
                    <div id="pgAddContactHdrNavBar" data-role="navbar">
                        <ul>
                            <li><a href="#" data-href="pgAddContactDetails" id="pgAddContactDetailsBtn" class="ui-btn-active">Details</a>
                            </li>
                            <li><a href="#" data-href="pgAddContactAddress" id="pgAddContactAddressBtn">Address</a>
                            </li>
                        </ul>
                    </div>
                </header>
                <div id="pgAddContactCnt" data-role="content">
                    <h3>Add Contact</h3><div id="pgAddContactDetails" class="tab-content">
                        <div data-role="fieldcontain">
                            <label for="pgAddContactFullName">Full Name<span style='color:red;'>*</span></label>
                            <input title="Enter full name here." type="text" name="pgAddContactFullName" id="pgAddContactFullName" placeholder="Enter full name here." autocomplete="off" data-clear-btn="true" class="required"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactCompany">Company<span style='color:red;'>*</span></label>
                            <input title="Enter company here." type="text" name="pgAddContactCompany" id="pgAddContactCompany" placeholder="Enter company here." autocomplete="off" data-clear-btn="true" class="required"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactJobTitle">Job Title<span style='color:red;'>*</span></label>
                            <input title="Enter job title here." type="text" name="pgAddContactJobTitle" id="pgAddContactJobTitle" placeholder="Enter job title here." autocomplete="off" data-clear-btn="true" class="required"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactEmailAddress">Email Address<span style='color:red;'>*</span></label>
                            <input title="Enter email address here." type="email" name="pgAddContactEmailAddress" id="pgAddContactEmailAddress" placeholder="Enter email address here." autocomplete="off" data-clear-btn="true" class="required"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactBusinessPhone">Business Phone</label>
                            <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgAddContactBusinessPhone" id="pgAddContactBusinessPhone" placeholder="Enter business phone here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactBusinessFax">Business Fax</label>
                            <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgAddContactBusinessFax" id="pgAddContactBusinessFax" placeholder="Enter business fax here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactMobilePhone">Mobile Phone</label>
                            <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgAddContactMobilePhone" id="pgAddContactMobilePhone" placeholder="Enter mobile phone here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactHomePhone">Home Phone</label>
                            <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgAddContactHomePhone" id="pgAddContactHomePhone" placeholder="Enter home phone here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                    </div>
                    <div id="pgAddContactAddress" class="tab-content">
                        <div data-role="fieldcontain">
                            <label for="pgAddContactStreetAddress1">Street Address 1</label>
                            <input type="text" name="pgAddContactStreetAddress1" id="pgAddContactStreetAddress1" placeholder="Enter street address 1 here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactStreetAddress2">Street Address 2</label>
                            <input type="text" name="pgAddContactStreetAddress2" id="pgAddContactStreetAddress2" placeholder="Enter street address 2 here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactCity">City</label>
                            <input type="text" name="pgAddContactCity" id="pgAddContactCity" placeholder="Enter city here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactState">State</label>
                            <input type="text" name="pgAddContactState" id="pgAddContactState" placeholder="Enter state here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactProvince">Province</label>
                            <input type="text" name="pgAddContactProvince" id="pgAddContactProvince" placeholder="Enter province here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                        <div data-role="fieldcontain">
                            <label for="pgAddContactPostalCode">Postal Code</label>
                            <input type="number" name="pgAddContactPostalCode" id="pgAddContactPostalCode" placeholder="Enter postal code here." autocomplete="off" data-clear-btn="true"></input>
                        </div>
                    </div>
                </div>
                
                <footer id="pgAddContactFtr" data-role="footer" data-position="fixed">
                    <div id="pgAddContactFtrNavBar" data-role="navbar">
                        <ul>
                            <li><a id="pgAddContactBack" data-icon="carat-l">Cancel</a>
                            </li>
                            <li><a type="submit" id="pgAddContactSave" data-icon="action">Save</a>
                            </li>
                        </ul>
                    </div>
                </footer></div>

The past two articles discussed in detail how to create the views and the same approach has been followed here. This view has a left panel that shows when the menu is selected. The footer has a navigation bar with Cancel and Save button. Cancel takes one to the Contacts listing whilst Save will insert a new Contact to the database.

The code that gets executed when the save button is clicked on the Add Contact page does this:

JavaScript - Add Contact

// Save click event on Add page
            $('#pgAddContactSave').on('click', function (e) {
                e.preventDefault();
                e.stopImmediatePropagation();
                // save the Contact
                var ContactRec;
                //get form contents into an object
                ContactRec = pgAddContactGetRec();
                //save object to SQL
                app.addContact(ContactRec);
            });

The data input by end user is read into an object and stored into pgAddContactGetRec, this is later passed to app.addContact which inserts the record to the database.

// add a new record to SQL storage.
        app.addContact = function (ContactRec) {
            // define a record object to store the current details
            var FullName = ContactRec.FullName;
            // cleanse the record key of spaces.
            FullName = FullName.split(' ').join('-');
            ContactRec.FullName = FullName;
            // store the json object in the database
            $.when(SqlInsertRecord(dbAddressBook, "Contact", ContactRec)).done(function () {
                //show a toast message that the record has been saved
                toastr.success('Contact record successfully saved.', 'Address Book');
                //find which page are we coming from, if from sign in go back to it
                var pgFrom = $('#pgAddContact').data('from');
                switch (pgFrom) {
                    case "pgSignIn":
                    $.mobile.changePage('#pgSignIn', {transition: pgtransition});
                    break;
                    default:
                    // clear the edit page form fields
                    pgAddContactClear();
                    //stay in the same page to add more records
                }
                }).fail(function (err) {
                //show a toast message that the record has not been saved
                toastr.success('Contact record NOT successfully saved.', 'Address Book');
            });
        };

// store the json object in the database
            $.when(SqlInsertRecord(dbAddressBook, "Contact", ContactRec)).done(function () {

The crucial line that does the magic of inserting the record to the database is the one above. Because we are using the deferred method of websql maintenance, we call the database method to execute passing it inbetween $.when  .done and  .fail statements. This basically means that when the SqlInsertRecord method is done executing run the code in the function after done, if there is a failure perform the action in the failure. In this case, when the web methods finalises, show a toast that the message was saved, if failed, show a toast that the record could not be saved.

HTML Definition - Update Contact Screen

<div data-url="FullName" id="pgEditContact" data-role="page">
                    <div data-position="left" data-display="reveal" data-position-fixed="true" id="pgEditContactPnl" data-role="panel">
                        <ul data-role="listview" id="pgEditContactPnlLV">
                            <li ><a data-transition="slide" href="#pgAddContact">New</a></li>
                            <li ><a data-transition="slide" href="#pgContactMindCity">Relationships > City</a></li>
                            <li ><a data-transition="slide" href="#pgRptContact">Report</a></li>
                            <li ><a data-transition="slide" href="#pgContact">Back</a></li>
                        </ul>
                    </div>
                    
                    <header id="pgEditContactHdr" data-role="header" data-position="fixed">
                        <h1>Address Book</h1>
                        <a data-role="button" id="pgEditContactMenu" href="#pgEditContactPnl" data-icon="bars" class="ui-btn-left">Menu</a>
                        <div id="pgEditContactHdrNavBar" data-role="navbar">
                            <ul>
                                <li><a href="#" data-href="pgEditContactDetails" id="pgEditContactDetailsBtn" class="ui-btn-active">Details</a>
                                </li>
                                <li><a href="#" data-href="pgEditContactAddress" id="pgEditContactAddressBtn">Address</a>
                                </li>
                            </ul>
                        </div>
                    </header>
                    <div id="pgEditContactCnt" data-role="content">
                        <h3>Edit Contact</h3><div id="pgEditContactDetails" class="tab-content">
                            <div data-role="fieldcontain">
                                <label for="pgEditContactFullName">Full Name<span style='color:red;'>*</span></label>
                                <input readonly="readonly" data-clear-btn="true" autofocus="true" title="Enter full name here." type="text" name="pgEditContactFullName" id="pgEditContactFullName" placeholder="Enter full name here." autocomplete="off" class="required"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactCompany">Company<span style='color:red;'>*</span></label>
                                <input title="Enter company here." type="text" name="pgEditContactCompany" id="pgEditContactCompany" placeholder="Enter company here." autocomplete="off" data-clear-btn="true" class="required"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactJobTitle">Job Title<span style='color:red;'>*</span></label>
                                <input title="Enter job title here." type="text" name="pgEditContactJobTitle" id="pgEditContactJobTitle" placeholder="Enter job title here." autocomplete="off" data-clear-btn="true" class="required"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactEmailAddress">Email Address<span style='color:red;'>*</span></label>
                                <input title="Enter email address here." type="email" name="pgEditContactEmailAddress" id="pgEditContactEmailAddress" placeholder="Enter email address here." autocomplete="off" data-clear-btn="true" class="required"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactBusinessPhone">Business Phone</label>
                                <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgEditContactBusinessPhone" id="pgEditContactBusinessPhone" placeholder="Enter business phone here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactBusinessFax">Business Fax</label>
                                <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgEditContactBusinessFax" id="pgEditContactBusinessFax" placeholder="Enter business fax here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactMobilePhone">Mobile Phone</label>
                                <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgEditContactMobilePhone" id="pgEditContactMobilePhone" placeholder="Enter mobile phone here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactHomePhone">Home Phone</label>
                                <input type="tel" pattern="\d\d\d \d\d\d \d\d\d\d" name="pgEditContactHomePhone" id="pgEditContactHomePhone" placeholder="Enter home phone here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                        </div>
                        <div id="pgEditContactAddress" class="tab-content">
                            <div data-role="fieldcontain">
                                <label for="pgEditContactStreetAddress1">Street Address 1</label>
                                <input type="text" name="pgEditContactStreetAddress1" id="pgEditContactStreetAddress1" placeholder="Enter street address 1 here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactStreetAddress2">Street Address 2</label>
                                <input type="text" name="pgEditContactStreetAddress2" id="pgEditContactStreetAddress2" placeholder="Enter street address 2 here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactCity">City</label>
                                <input type="text" name="pgEditContactCity" id="pgEditContactCity" placeholder="Enter city here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactState">State</label>
                                <input type="text" name="pgEditContactState" id="pgEditContactState" placeholder="Enter state here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactProvince">Province</label>
                                <input type="text" name="pgEditContactProvince" id="pgEditContactProvince" placeholder="Enter province here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                            <div data-role="fieldcontain">
                                <label for="pgEditContactPostalCode">Postal Code</label>
                                <input type="number" name="pgEditContactPostalCode" id="pgEditContactPostalCode" placeholder="Enter postal code here." autocomplete="off" data-clear-btn="true"></input>
                            </div>
                        </div>
                    </div>
                    
                    <footer id="pgEditContactFtr" data-role="footer" data-position="fixed">
                        <div id="pgEditContactFtrNavBar" data-role="navbar">
                            <ul>
                                <li><a id="pgEditContactBack" data-icon="carat-l">Cancel</a>
                                </li>
                                <li><a type="submit" id="pgEditContactUpdate" data-icon="action">Update</a>
                                </li>
                                <li><a id="pgEditContactDelete" data-icon="delete">Delete</a>
                                </li>
                            </ul>
                        </div>
                    </footer></div>

To access the Contact Update screen, one needs to select a Contact from the ListView listing and this screen will appear. A user can then Update the Contact details or select Delete to delete a contact.

JavaScript - Update Contact

When a user updates a contact, the details of the screen are read and saved as an object which gets passed to the  update method inside our app.js files.

// Update click event on Edit Page
            $('#pgEditContactUpdate').on('click', function (e) {
                e.preventDefault();
                e.stopImmediatePropagation();
                // save the Contact
                var ContactRecNew;
                //get contents of Edit page controls
                ContactRecNew = pgEditContactGetRec();
                //save updated records to SQL
                app.updateContact(ContactRecNew);
            });

As soon as the Update button is clicked, the contact details are saved with app.updateContact, lets look at what app.updateContact does.

//update an existing record and save to SQL
        app.updateContact = function (ContactRec) {
            // lookup specific Contact
            var FullName = ContactRec.FullName;
            //cleanse the key of spaces
            FullName = FullName.split(' ').join('-');
            //define the record to update
            var ContactUpdate = {};
            ContactUpdate.FullName = FullName;
            $.when(SqlUpdateRecordWhere(dbAddressBook, "Contact", ContactRec, ContactUpdate)).done(function () {
                //record has been saved
                toastr.success('Contact record updated.', 'Address Book');
                // clear the edit page form fields
                pgEditContactClear();
                // show the records listing page.
                $.mobile.changePage('#pgContact', {transition: pgtransition});
                }).fail(function (err) {
                toastr.error('Contact record not updated, please try again.', 'Address Book');
                return;
            });
        };

Also here, we are deferring the execution until it finishes. $.when SqlUpdateRecordWhere, a contact details are updated using the FullName of the person is done, show a toast that the contact details are updated, if not toast that the user details are not updated.

JavaScript - Delete Contact

From the update contact screen, if an end user opts to delete a contact, clicking the Delete button will follow a series of events. The end user will be prompted if they want to delete a contact and if they click Yes, the contact details will be erased from the websql database. Message Boxes were discussed in the previous articles and will not be redone here, so please refer to that.

A contact is deleted by primary key which is the FullName in this case.

//delete a record from SQL using record key
        app.deleteContact = function (FullName) {
            FullName = FullName.split(' ').join('-');
            //define the record to delete
            var ContactDelete = {};
            ContactDelete.FullName = FullName;
            $.when(SqlDeleteRecordWhere(dbAddressBook, "Contact", ContactDelete)).done(function () {
                //record has been deleted
                toastr.success('Contact record deleted.', 'Address Book');
                // show the page to display after a record is deleted, this case listing page
                $.mobile.changePage('#pgContact', {transition: pgtransition});
                }).fail(function (err) {
                toastr.error('Contact record not deleted, please try again.', 'Address Book');
                return;
            });
        };

we build the where clause first using the FullName and then execute SqlDeleteRecordWhere. Thus when a contact is deleted, a toast is shown and then the end user is taken to the contact listing screen.

You can refer to the previous articles in terms of how to list records, but for the sake of this article, we will just talk about getting all records from the database with the CheckStorageMethod.

Before all contacts are listed in the ListView, the checkForContactStorage function is executed. This reads all records from the database and loads them to the listview.

JavaScript - Get All Contacts & Display Them

//display records if they exist or tell user no records exist.
        app.checkForContactStorage = function () {
            //get records from SQL.
            //when returned, parse then as json object
            var ContactObj = {};
            $.when(SqlGetRecords(dbAddressBook, "Contact", "FullName")).done(function (dta) {
                // return json object of all records
                ContactObj = ResultSetToJSON(dta, "FullName");
                // are there existing Contact records?
                if (!$.isEmptyObject(ContactObj)) {
                    // yes there are. pass them off to be displayed
                    app.displayContact(ContactObj);
                    } else {
                    // nope, just show the placeholder
                    $('#pgContactList').html(ContactHdr + noContact).listview('refresh');
                }
                }).fail(function (err) {
                //just show the placeholder
                $('#pgContactList').html(ContactHdr + noContact).listview('refresh');
            });
        };

$.when SqlGetRecords is .done, dta returns the ResultSet which is passed to ResultSetToJSON containing all records in the contacts database, when these are found, these are displayed with app.displayContact(ContactObj); with the listview refreshed.

JavaScript - Create Database & Tables

Before all these CRUD operations are performed, the database for this app should be created and the respective tables. This should be on init.

1. We opened the database

//open the websql database
            dbAddressBook = SqlOpenDb("AddressBook");

2. We created the table, first defining it and then calling the function to create it.

//create a websql table for SQL-Contact
            app.ContactSqlCreateTable = function () {
                var tblStructure = {};
                tblStructure.FullName = DB_TEXT;
                tblStructure.Company = DB_TEXT;
                tblStructure.JobTitle = DB_TEXT;
                tblStructure.EmailAddress = DB_TEXT;
                tblStructure.BusinessPhone = DB_TEXT;
                tblStructure.BusinessFax = DB_TEXT;
                tblStructure.MobilePhone = DB_TEXT;
                tblStructure.HomePhone = DB_TEXT;
                tblStructure.StreetAddress1 = DB_TEXT;
                tblStructure.StreetAddress2 = DB_TEXT;
                tblStructure.City = DB_TEXT;
                tblStructure.State = DB_TEXT;
                tblStructure.Province = DB_TEXT;
                tblStructure.PostalCode = DB_TEXT;
                SqlCreateTable(dbAddressBook, "Contact", tblStructure, "FullName", "");
            };
            app.ContactSqlCreateTable();

Points of Interest

Finding the deferred function of JQuery to manipulate function calls helped me in ensuring that I minimise the code to maintain the database. The websql.js code can be used in any WebSQL database that one can decide to use as one just passes objects to the functions. Whilst there is more functionality added in this app to display relationships in relation to City for the contacts we have not explained that as the focus here was basically for CRUD functionality. You can also create such CRUD apps using the three step JQM.Show mobile app that is a RAD tool that writes all this code for you to create your prototypes.

History

This is the third installment of our articles in creating CRUD web apps using different frameworks. We spoke about LocalStorage, then web related JSON and now WebSQL. The next steps will be MySQL and depending on interest and use other databases. You can leave me comments of what you would also like to see in these articles so that I can explorer and relate more information. If you find anything useful, please vote and also recommend any enhancements that I might need to add in any of my articles. Thank you for taking time to go through this. #Enjoy