The Conversion: Trips for Keeps - Part 3

With the core methodology set up in Part 2 to get the trip data from the AngularJS front end to the CF middle tier, we are ready to save to the database. All of our custom data is stored in Oracle, but the following will apply to most, if not all, relational databases. I try to steer away from doing anything overly fancy to minimize refactoring down the road if platforms should change.

At the moment, the trip function only receives the trip data and breaks it down into a CF structure and then returns that structure back to the application.

 <cffunction name="saveTrip" returnformat="json" returntype="struct" access="remote">  
   <cfset _params = getHttpRequestData()>  
   <cfset _content = deserializeJSON(_params.content)>  
   <cfset _trip = deserializeJSON(_content.objectData)>  
   <cfreturn _trip>  
 </cffunction>  

Now I have a design decision to make. I can either create individual functions to save each aspect of the trip such as the trip details, the sponsors, waivers, participants, etc. or throw everything into one, likely very long, function. Hmmm...

I think for maintenance purposes it would be wiser to break each save element out. This would reciprocate the idea that the getTrip() function will have various functions to get all the elements of a trip. So let's start by creating a function saveTripDetails(). There will be two sections of this function: one to insert and one to update. Technically, I could create a PL/SQL stored procedure and handle both those scenarios there. Since it's the same amount of code, I'm going with the more direct route and do the database operations in Coldfusion. In my view, the fewer layers of code and number of code bases to maintain, the better. That said, if my boss came along and said "You must use PL/SQL for database operations.", I'd be okay with that.

As I began writing the saveTripDetails() function, I realized that trip_id was not included as a returned property in getTrip(). The trip_id will be used to determine of an insert or an update is needed. If the trip_id is 0, an insert will be executed; otherwise an update will be performed.

[...The Next Day...]

So after much testing and debugging, especially for a new trip, we finally have code that works. The saveTrip() function remains small with only one additional line added.

 <cffunction name="saveTrip" returnformat="json" returntype="struct" access="remote">  
   <cfset _params = getHttpRequestData()>  
   <cfset _content = deserializeJSON(_params.content)>  
   <cfset _trip = deserializeJSON(_content.objectData)>  
   <cfset _trip = saveTripDetails(_trip)>  
   <cfreturn _trip>  
 </cffunction>  

The new function, saveTripDetails(), is doing all the work.

 <cffunction name="saveTripDetails" returntype="struct">  
   <cfargument name="_tripDetails" type="struct" required="yes">  
   <cfif _tripDetails.id gt 0>  
     <cfquery datasource="#_ds#">  
       update trips  
       set   trip_name = <cfqueryparam value="#_tripDetails.name#" cfsqltype="cf_sql_varchar">,  
             author_pidm = <cfqueryparam value="#_tripDetails.authorID#" cfsqltype="cf_sql_integer">,  
             destination = <cfqueryparam value="#_tripDetails.destination#" cfsqltype="cf_sql_varchar">,  
             purpose = <cfqueryparam value="#_tripDetails.purpose#" cfsqltype="cf_sql_varchar">,  
             trip_type = <cfqueryparam value="#_tripDetails.type#" cfsqltype="cf_sql_varchar">,  
             comments = <cfqueryparam value="#_tripDetails.comments#" cfsqltype="cf_sql_varchar">,  
             last_update = sysdate,  
             last_update_pidm = <cfqueryparam value="#session.intUserPIDM#" cfsqltype="cf_sql_integer">  
       where trip_id = <cfqueryparam value="#_tripDetails.id#" cfsqltype="cf_sql_integer">  
     </cfquery>  
   </cfif>  
   <cfif _tripDetails.id is 0>  
     <cfquery name="_id" datasource="#_ds#">  
       select trips_seq.nextval as nextId from dual  
     </cfquery>  
     <cfloop query="_id">  
       <cfset _tripDetails.id = nextId>  
     </cfloop>  
     <cfif _tripDetails.id gt 0>  
       <cfquery datasource="#_ds#">  
         insert  
         into  trips (  
             trip_id,  
             author_pidm,  
             trip_name,  
             destination,  
             purpose,  
             trip_type,  
             comments,  
             last_update,  
             last_update_pidm  
         )  
         values (  
             <cfqueryparam value="#_tripDetails.id#" cfsqltype="cf_sql_integer">,  
             <cfqueryparam value="#session.userid#" cfsqltype="cf_sql_integer">,  
             <cfqueryparam value="#_tripDetails.name#" cfsqltype="cf_sql_varchar">,  
             <cfqueryparam value="#_tripDetails.destination#" cfsqltype="cf_sql_varchar">,  
             <cfqueryparam value="#_tripDetails.purpose#" cfsqltype="cf_sql_varchar">,  
             <cfqueryparam value="#_tripDetails.type#" cfsqltype="cf_sql_varchar">,  
             <cfqueryparam value="#_tripDetails.comments#" cfsqltype="cf_sql_varchar">,  
             sysdate,  
             <cfqueryparam value="#session.userid#" cfsqltype="cf_sql_integer">  
         )  
       </cfquery>  
     </cfif>  
   </cfif>  
   <cfreturn _tripDetails>  
 </cffunction>  

There is nothing really notable here as it's pretty standard stuff. The function will check to see if a trip id greater than zero was passed in. If so, then an update is made to the trips table. If the id is zero, the we get a new id by getting the next value in the sequence: select trips_seq.nextval as nextId from dual, assigning that value to the id and executing an insert into the trips table.

The function ends by returning the trip details back to saveTrip().

As I work through the application, I will be adding validation and error handling.  But for now, the groundwork has been laid. The trip.js controller has been modified to handle a parameter of zero for the trip id.

 angular.module('core').controller('TripController',['$scope','dataService','$stateParams','$state', function($scope,dataService,$stateParams,$state){  
   $scope.trip = {};  
   if ($stateParams.id == 0){  
     dataService.call('createNewTrip').then(function(data){  
       $scope.trip = data.data;  
     })  
   }  
   if ($stateParams.id > 0){  
     dataService.call('getTrip&_id=' + $stateParams.id).then(function(data){  
       $scope.trip = data.data;  
     })  
   }  
   dataService.call('getTripTypes').then(function(data){  
     $scope.tripTypes = data.data;  
   })  
   $scope.saveTrip = function(_trip){  
     dataService.process('saveTrip',_trip).then(function(data){  
       $scope.trip = data.data;  
       $state.go('trip',{id: $scope.trip.ID})  
     });  
   }  
 }]);  

I do want to point out the following line after the trip has been saved $state.go('trip',{id: $scope.trip.ID}). What this does is reload the page by going to the trip route and passing in the trip id. When a new trip is saved, it's route ended with a 0. So in order to reestablish the route, I am reloading the page with the new trip id to force the new route. By having the page essentially start over, we can ensure that all the ducks are in a row and immediately notice if there are any data concerns.

Next up is taking a second first look at the trip.html template as well as adding a new trip button on the landing page.

Thanks for reading!  Comments and feedback are always welcome.

Cheers!
[Updated Line Counts]