Friday, November 30, 2012

Application Craft - Easy CRUD

Background

This is one of a series of blogs I'm writing as I build Easy Peasy Task List.  If you want to know about the other related blogs, visit Status of discovery / learning exercises.

Overview

This blog will address setting up a database with one table representing the users, performing CRUD operations, run performance tests between the AC database vs Xeround.  I want to see if there's any advantage of one cloud database provider vs the other.

Video / Caution with that SessionObject

Please review the video as it shows the results of this analysis.  It appears that either using AC Database or Xeround is sufficient.  The question may be more to space available as Xerounds free account limits you to 10Mb.  

I bumped into something that was rather interesting.  When running the tests it appeared that the longer I ran the worse the performance became - quite large in fact.  

It took me some time to finally figure out what was causing it - the ssj.SessionObject.  You can watch the video and see all the test results are look at the pictures following for comparison when SessionObject is not used.


The following two pictures demonstrate the difference between running the same number of tests (20 CRUDs) with and with out using the SessionObject:

Note in the above charts that the number next to AC Database and Xeround is the time the entire test took in milliseconds.

Now the results for the same number of tests with the "useSession" checked:


Notice how both graphs increase duration of test run over time.  And look at the total for entire run!  It went from less then 2 seconds to around 15 seconds!  Oh my.  I entered a defect in the AC portal Issue # 6807

Source code

You can clone my Git repository or just use the Zip option.  Once you have the source code you will find the  "app_Step+2+-+Persistence.acft" which can be imported to AC by going to "Apps" -> Import.  My github account is here: https://github.com/bartonhammond/TaskList

Database

I'm creating a a database table with the fields I've captured from the Social Network Login.  See this blog Easy oAuth for more info.  I determined that the three Social Networks that AC supports (Facebook, Twitter and Dropbox), have two fields that I want to retain, namely the id and name.  The id is unique to the social network but together with the social network, a unique key can be created.  Since I don't have any intention of contacting the users directly with email, I don't need it.  And besides, Twitter doesn't provide it.

The table I've created is called AppUser.  The schema looks like this:

-- Create Table: AppUser
CREATE TABLE AppUser
(
`userId` INT NOT NULL AUTO_INCREMENT
,PRIMARY KEY (userId)
,`name` VARCHAR(250) NOT NULL
,`source` VARCHAR(10) NOT NULL
,`id` INT NOT NULL
,`created` TIMESTAMP NOT NULL
        , index sourceid (id, source)
)
ENGINE=INNODB
The  userId is auto increment - each time an insert is created, a incremented int is assigned.  The name is the field that the social network provides.  The source is going to be either "facebook", "twitter" or "dropbox.  The id is the unique id the social network provided.  The created timestamp lets me keep track of modifications.  Note there is a index call sourceid which is made up of two columns, id and source.

Creating and establishing the database

AC currently will support setting up a database for you by asking their support@applicationcraft.com.  That's what I did and Ian Jobling set it up and got back to me the next day with account information.  What you get is a userid/password and a url to a PhPMyAdmin account.  You can easily create the table using the script I provided above. 

Setting up Xeround was just as simple, in fact they use the same PhpMyAdmin tool.

So once you setup the database and have the table defined you need to set that up in AC as a Connection.  Here's a screen shot of what that looks like:

Note that the Server value is *not* http://.  I put that in first and it caused some problems.  The other issue I had was getting the Database field to respond correctly.  That field is actually a drop down list box that is populated from the connection information provided.  I finally got it to work correctly by first Save'ing it and then try selecting the Database option.

Let's look at the CRUD UI

So my objective with the following UI was simply to kick off some tests from the client and compare the response time between AC Database and Xeround.   The test I am considering is simply a CRUD on the AppUser table.  I only need to capture the time for each start/end of the separate CRUD operations and then display them within a chart.


Now there are a couple of check boxes that need explanation.  When I first started with this I wanted to see how much time it took to get a connection, start a transaction, execute the sql, and commit the transaction.  As you'll see in the code later, I put some controls to limit how much data was being generated.  The checkbox "Detail" controls that behavior.  

The next checkbox, "useSession" is rather interesting.  I wasn't sure how to capture the logging information I was going to generate.  At first I was going to drive the test from the client in that each request would be initiated from the client and the SSJS would only perform the requested operation (Create, Read, Update, Delete).  But I decided that I wanted the client to be a little more dumbed down.  That meant I need to save the logs on the server.  My first thought was just to use the SessionObject so that's what I did.  This checkbox controls the use of that SessionObject and when unchecked, just stores the logs in a global array.

The radio button group allows me to easily toggle between the two database connections.  

Let's look at some code

On Start Test clicked

The client side code is pretty straight forward.  When the Start Test button is clicked we do the following:
function handler_startTestBtn_onClick(mouseev){
    var numberOfTests = app.getValue("numberOfTestsSlider");
    var connection = app.getValue("databaseRadio").value;
    var detail = app.getValue("detailChkbox");
    var useSession = app.getValue("useSessionChkBox");
    
    app.callSSJ('startTesting', function(error,data){
          console.log(data);
          fillZingerChart(data);
        }, [numberOfTests,connection,detail, useSession]); 
}
lines 2-5 are simply pulling out the test run configuration options.

lines 7-10 - Here we call the SSJS function "startTesting" and pass in the parameters numberOfTests, connection, detail, and useSession.  When the SSJS function completes it returns to the anonymous function and provides the results in the parameter "data".  After logging the data, the client side function "fillZingerChart" is passed the data.

SSJS logging

There are three simple functions that do the logging.  They are shown below:
logData = [];
/**
 * clear the session log
 */
function clearLog() {
    ssj.setSessionObject('logData',[]);
}
/**
 * get the sessionObject and if the value is null,
 * set the sessionObject
 */
function setLog(obj, useSession) {  
    if (useSession) {
        logData = ssj.getSessionObject('logData');
        if (logData === null) {
            logData = [];
        }
    }
    obj.time = (new Date()).getTime();
    var newObject = JSON.parse(JSON.stringify(obj));
    logData.push(newObject);
    
    if (useSession) {
        ssj.setSessionObject('logData',logData);
    }
    
}
/**
 * return the sessionObject
 */
function getLog(useSession) {
    if (useSession) {
        return ssj.getSessionObject('logData');
    } else {
        return logData;
    }
}
line 1 is a global declaration of the logData. If I don't use the SessionObject, I use this global object.

line 5- 7 the function clearLog simply clears out the SessionObject variable "logData"

lines 12 - 25 the function setLog does different things depending on the useSession value.  If useSession is false, then only lines 19-21 are utilized.  line 19 set a time variable into the object.  line 20 is a poor mans deep copy of a javascript object.  If the copy is not done, then the log contains all the entries but there all of the last object that was created.

lines 13-16 if useSession is true,  check if there's anything in the SessionObject for variable "logData" and if not, initialize the array.

lines 23 - 25 if useSession is true, set the array logData into the session object.

private function _crud


/**
 * private function
 * log various activities if detail
 * perfrom CRUD
 * always start/commit transaction
 */
function _crud(request, detail, useSession) {
    //log start connection
    if (detail) {
        setLog({ state: 'start connection', request: request}, useSession);
    }
    cObj = ssj.getConnection(request.connection);
    
    if (detail) {
        setLog({ state: 'end connection', request: request}, useSession);
    }
    
    var error = false;
    var rtn = {};
    try {
      
        //log begin transaction
        if (detail) {
            setLog({ state: 'start transaction', request: request}, useSession);
        }
        cObj.beginTransaction();    
   
        if (request.action === 'insert') {
            cObj.insert(request.table, request.data);    
            rtn = cObj.lastInsertId();
        } else if (request.action === 'update') {
            rtn = cObj.update(request.table, 
                request.data, 
                cObj.quoteInto('userId=?', request.data.userId));
        } else if (request.action === 'delete') {
            rtn = cObj.delete(request.table, 
                cObj.quoteInto('userId=?', request.data.userId));
        } else if (request.action === 'select') {
            var selectObj = cObj.select().from(request.table).where('userId=?', request.data.userId); 
            rtn =cObj.exec(selectObj);
        } else if (request.action === 'deleteAll') {
            cObj.delete(request.table);
        }
    }
    catch(e) {
        //log error
        cObj.rollback();
        error = true;
        throw e;
    } finally {
        if (!error) {
            cObj.commit();
        }
        //log end transaction
        if (detail) {
            setLog({ state: 'end transaction',  request: request}, useSession);
        }
        return rtn;
    }
}
First off, according to AC documentation, by starting a function in SSJS with the _ ("underscore"), the function is private. Now I don't know what that's important at this time 'cause I don't know how to access any SSJS functions outside of the AC client. But there is this interesting bit of documentation that gives a flavor of things to come with SSJS functions: Exposing your SSJS DB Handlers to other systems

There are numerous sections of this code that has to do with logging or not the request. As I was working through this and trying to understand why the performance was so bad over time I attempted to whittle away the things that might be affecting it. So this function _crud has numerous if statements to decide if logging should be done.

On line 12 the connection to the database is retrieved.  Notice how I surrounded that with logging to check how fast this was performed.

Note the  try/catch/finally statement starting at line 20.  This is because at line 26 I start a transaction.  For this exercise I wanted all my CRUD functionality to be all in one function an resuse the getConnection, beginTransaction, commit and rollback.  I will probably revisit this design latter and make it more object oriented.

By having the try/catch/finally pattern I am able to catch any exception and either rollback or commit if there is no exception.  The catch clause begins on line 45 and sets a variable if there is an exception.  That variable "error" is check in the finally clause to make sure the commit is not called.  Notice line 58 the return of the variable rtn.

The Read (select), Update and Delete SSJS functions all return a value.  In the case of the select, it's an array of rows of data that have been retrieved.  The update returns the number of updates.  The delete returns the number of deletions.

But the Create (insert) doesn't return a value but when using "auto increment" you have to ask the db what the last id was.  That occurs on line 30.

So between lines 28 and 42 the CRUD operations are performed against the "AppUser" table.  I think there is room for improvement with better javascript design but it was sufficient for me for the purpose of learning about AC persistence.

The "assert" function

/**
 * simple assertion function that logs if fails
 */
function assert(outcome, description, obj, useSession) {
    if (!outcome) {
        debugger;
        setLog({state: 'assert', description: description, data: obj},useSession );
    } 
}
The assert function is simply used to log any failures along with a description and the oject that failed.  The useSession variable is passed in so that the logging function does the right thing.

The "startTesting" function

This function is the SSJS driver code.  It basically clears out the current contents in the table being tested, and then performs 4 different actions, namely the Create, Read, Update and Deletes.  At the completion of each CRUD operation, an assertion is performed to make sure things are running as expected.
/** 
 * Run CRUD for numberOfTest using Connection
 * return logs
 * 
 * obj: {connection: 'connection', state: 'action', table: 'table', data: {stuff}}
 * action: insert/update/delete/select/assert/deleteAll
 */
function startTesting(numberOfTests,connection, detail, useSession) {
    debugger;
    //Clear the session log
    clearLog();
    
    //first clear the table
    var tableCleared = false;
    
    for (var test = 0; test < numberOfTests; test++) {
        var obj = {};
        obj.connection = connection;
        obj.table = 'AppUser';
        
        //Clear the database table
        if (!tableCleared) {
            obj.action = 'deleteAll';
            performCrud(obj, detail, useSession);
            tableCleared = true;
        }
        //Insert an object
        obj.action = 'insert';
        obj.data = {name: 'test' + test, source: 'auto', id: test};
        var userId = performCrud(obj,detail, useSession);
        assert(userId !== null, 'insert failed',obj, useSession);
        
        //Update that same row
        obj.action = 'update';
        obj.data.userId = parseInt(userId);
        obj.data.name = obj.data.name + 'changed';
        var numUpdates = performCrud(obj,detail, useSession);
        assert( 1 === numUpdates,'update failed', obj, useSession);
        
        //Select from that row
        obj.action = 'select';
        var sel = performCrud(obj,detail, useSession);
      
        assert(1 === sel.length, 'select failed - array length not 1', obj, useSession);
        assert(sel[0].userId === obj.data.userId, 'select failed - userId not equal',sel[0], useSession);
        assert(sel[0].name === obj.data.name, 'select failed - name not equal',sel[0], useSession);
        assert(sel[0].source === obj.data.source, 'select failed - source not equal',sel[0], useSession);
        assert(sel[0].id === obj.data.id, 'select failed - id not equal',sel[0], useSession);
        
        //Delete that row
        obj.action = 'delete';
        assert( 1 === performCrud(obj,detail, useSession)); 
    }
    //Get all the logs 
    return getLog(useSession);
}
I think this code is rather straight forward.
The test looping starts on line 16. I use an object called "obj" to contain the control information. On line 18, the connection variable is set. This is the value from the client selection of either "AC Database" or "Xeround". Line 19 sets what the table value is. Line 22 - 26 runs the action "deleteAll" to clear the database. It only runs when the test starts.

 Lines 27 - 31 sets up the insert. Note that the "id" is using the increment value "test". The return value from the "performCrud" method is the userId. Since that's the primary key that the other CRUD operations require, it's set into the obj.data object. Notice how the assert confirms that the userId is not null.

 Lines 33 - 38 sets up for the "update". The name value is modified. After running, the return value is asserted to be 1.

 Lines 41 -49 perform a Read (select). The return array is asserted to match all the fields against the initial object that was updated.

 Lines 50 - 52 simply delete the object and assert only one row was effected.

 Line 55 is outside of the "for loop" and returns the logs from the test.

The "performCrud" function

/**
 * perform the request 
 * and log
 */
function performCrud(request, detail, useSession) {

    //log begin insert
    setLog({ state: 'start', request: request}, useSession);

    var rtn = _crud(request,detail, useSession);
   
    //log end inser
    setLog({state: 'end',request: request}, useSession);
  
    return rtn;
 
}

This function just wraps the call to the private function _crud with logging.  Essentially this was all that was necessary for the charting that shows the results.

Now back to the client!

Now that the test has completed the code handling the  handler_startTestBtn_onClick function passes the returned data to the function fillZingerChart.  This function runs through the data and finds the "start/end" states and calculates the duration and adds to the overall total.

Then for each request.action it pushes the duration into an array depending upon if it's create, read, update or delete.


function fillZingerChart(data) {  
    var createArray = [];
    var readArray =[];
    var updateArray = [];
    var deleteArray = []; 
    
    var start = 0;
    var end = 0;
    var total = 0;
    for (var log = 0; log < data.length; log++) {
        var obj = data[log];
        //Ignore the cleaning
        if (obj.request.action === 'deleteAll') {
            continue;
        }

        if (obj.state === 'start') {
            start = obj.time;
        } else if (obj.state === 'end') {
            end = obj.time;
            var duration = end - start;
            total += duration;
            if (obj.request.action === 'insert') {
                createArray.push(duration);
            } else if (obj.request.action === 'update') {
                updateArray.push(duration);
            } else if (obj.request.action === 'select') {
                readArray.push(duration);
            } else if (obj.request.action === 'delete') {
                deleteArray.push(duration);
            }
        }
        
    }
    
    var db = app.getValue("databaseRadio").label;
    var jsonData = getZinger(db + " " + total,createArray, readArray, updateArray, deleteArray);
    
    console.log(jsonData);
    
    if (db === 'AC Database') {
        app.w('ZingchartAC').fullSettings(jsonData);
    } else {
        app.w('ZingchartXE').fullSettings(jsonData);
    }
}
On line 37 the database label from the selected radio group has the total time appended and the four arrays are passed into a simple function that sets all the options up for the ZingChart.

If you want to see the function "getZinger" get the source code from gitHub and take a look - it's just a lot of JSON that controls the Zing chart.

 You may be asking how was that JSON created? How did I build those two charts? Well the secret is to use the ZingChart Builder.  It's quite impressive and rather intuitive once you start playing around with it.

Notice that once the JSON data is retreived, on lines 42 and 44, the appropriate chart is set using the "fullSettings" API.

Summary

I think using the AC Database option is fine.  I'd like to understand better what the limitations are - like how many Mbs are allowed for the cost of the Pro account.

As to the CRUD persistence framework?  It works quite nice and I really like the way the "where" "from" etc is structured.  AC, as usual, as made this quite easy.

My one concern is that SessionObject.  Hopefully AC will address this.



Application Craft - Easy Peasy Task List Project


Building an Easy Peasy TaskList with ApplicationCraft

Background

I've been working with ApplicationCraft.com for some time now though for the last few months I've not had that luxury.  I've always been impressed how great the product is and the stability and robustness is awesome.  Here's a link to a previous blog where I spelled out what makes AC exciting to me: A Word About ApplicationCraft.

In order to get my AC skill sets up to speed I thought I'd build a Task List application using ApplicationCraft.  Since everything is so easy with AC, I thought I'd call this the Easy Peasy Task List. 

I will write blogs about each of the learning activities I undertake.  I don't know about you, but my blogs provide valuable information for myself (thus the name "Notes to Myself") for future reference.  I frequently look back at my blogs to see how I did a certain thing as I can't remember everything.  Hopefully not only does it benefit me but you also.  If so, drop me a comment and let me know.

Features

I want to build this Easy Peasy Task List with the following features ( in no particular order)
  • Task list management.  The user should be able to 
    • Create and maintain various task lists
    • Use calendar widget for date selection
    • Calendar summary view 
    • Add due dates and notifications (by date or location).  
    • History of all tasks
    • Completed tasks are archived and available for resurrection.
  • Run from everything with single code base AC provides a great way to build both the web application and mobile/smartphone application with a single code base.  They've introduced a new AC Mobile Build that I want to utilize.  I only care about iOS and Android and web app.  I need to refresh my skills in the adaptive layout  
  • App available in store.  I want to have the app available for both iOS and Android and accepted in the appropriate app store.  This means I need to learn about the certificates and such.
  • Use oAuth for authentication.  I don't want to manage user ids and lost passwords and such.  I just care that someone identifies themselves via some Social Network and they use that same authentication each time they use EasyPeasy TaskList.  At this time Facebook, Twitter and DropBox are supported.  I would like to see support for JanRain Engage Social Login where by I would be able to open up the oAuth to a lot more then just three Social Networks.  I submitted this request to the dev portal.
  • Database support - AC has come out with a better persistence story.  I want to use the new SQL features to track who logged in, their tasks, etc.
  • Off line support.  The app should be functional even w/ no internet connectivity.  AC supports SQLite database.  Once the app is on line, the SQLite database should sync up with the back end MySql db.
  • Share list.  Allow user to email someone a request to collaborate.  Permissions supported should be read/write or read only.
  • Reminders and Notifications - When tasks are entered, the EPTL user can keep track by stating due dates or locations and setting notifications
  • Testing - I really want to have unit tests and integration tests.  The integration tests should include the web app and mobile.
  • Advertising - After all this work, I need to consider how to make a buck or two.  AC has a Advertising widget available which I know nothing about.
  • Logging support - AC has built in logging but I find it rather difficult and insufficient for my use.  (See AC: Server Side Javascript and Logging.  I want logging from the client to be available online for monitoring.  If a user says they are having problems, I should be able to turn on a debug flag and watch the activity.
  • Release management - Need to address how users app is updated.  What happens if there is a database migration required for the new release? How are users kept off line while the updates are performed.

List of steps I've written about so far:

ApplicationCraft - Easy oAuth

ApplicationCraft - Easy CRUD

ApplicationCraft - Easy Testing

Status of discovery / learning exercises

The table below has the various learning exercises I need to build EasyPeasy TaskList.  Links to the various blogs I write with my analysis are available from the Task / Blog column.


Task /
Blog
Description Status Date
oAuth Web app and native app should both use oAuth In Progress
AC states here that the AC Mobile Build requires a ChildPlugin to support oAuth in mobile.
Nov 28, 2012
Database
CRUD
AC provides their own hosted MySql database.  The AC framework has new SSJS db features to perform CRUD. Learn how to use SSJS db. Simple solution needs to be refactored to be more OO Dec 1, 2012
Testing Need unit tests and integration tests. Basic requirements met  Dec 7, 2012
Notification  When user wants to be notified of a due date the server can push the notification.  AC offers a plugin using Xtify Push. Xtify works with native mobile apps.  I want to notify browser apps to so will use Pusher for that. Have a working solution for the basics. Dec 12, 2012
Use oAuth with JanRain JanRain Engage would open up the oAuth capabilities to 20+ Social Networks Submitted request to AC support.  Not sure how it's being tracked at this time.   Nov 26, 2012
Learn requirements
for app store
What is required by AC Mobile Build to qualify app to store not started
Preparation for app store Utilizing the AC Mobile Build to create an iOS and Android app that is ready for an app store will have certain requirements. not started
Offline / sync support How does the native app work with no connectivity? How does syncing work once it comes back on line? not started
Collaborate 1). Need ability to email invitation to collaborate.
2) What is impact to DB schema
3) How to resolve conflicts of concurrent task modification
not started
Location Notification Useing GPS, determine if any notifications are dependant upon location proximity not started
Calendar Need to learn how to select due dates from calendar widget and how to display summary calendar views (day, week, month, year) not started
Advertising How can I make some money? not started
Logging support Need ability to turn on/off client logging
Need ability to monitor logging
not started
Release management Ability to block access while maintenance is performed
Ability to require update of new release
Entered Release management of web / native app  Nov 28
2012
Stuff