Adding Undo Functionality to Ionic Using Sqlite

Posted on

Allowing a user to undo actions they have taken is basic functionality in most computer applications.  Recently, I was working on a custom LOB application for a client that would allow them to interact with an offline repository of their data. They needed the ability to undo actions that affected several Sqlite tables at once. There was a further requirement that the end user be able to walk back all changes made since the last synchronization to the master database (accessed via a WebAPI service). I therefore needed to come up with a mechanism to store each action that a user had taken, along with all the changes that were made in the database.

In this post, I will build on the basic structure I built in a previous blog post, where I created a very basic Contact management app. I will add functionality to “undo” changes as they are made. Changes are tied to the controller, so that selecting Undo on the Contacts controller will not roll back changes made in a different controller.

 

  1. Update the Database configuration to store data in “auditTransaction” and ”auditAction” tables. 
    angular.module('app.config', [])
    .constant('DB_CONFIG', {
        name: 'DB',
        tables: [
            {
                name: 'contact',
                columns: [
                   ....
                ]
            }, 
            {
                name: 'auditTransaction',
                columns: [
                    { name: 'id', type: 'integer primary key autoincrement' },
                    { name: 'userName', type: 'text' },
                    { name: 'title', type: 'text' },
                    { name: 'controller', type: 'text' },                    
                    { name: 'changeDate', type: 'text' }           
                ]
            },
            {
                name: 'auditAction',
                columns: [
                    { name: 'id', type: 'integer primary key autoincrement' },
                    { name: 'userName', type: 'text' },
                    { name: 'transactionId', type: 'integer' },
                    { name: 'objTableName', type: 'text' },                    
                    { name: 'obj', type: 'text' },
                    { name: 'changeType', type: 'text' },
                    { name: 'changeDate', type: 'text' }           
                ]
            }
        ]
    });

     

  2. Add models for Audit Action and Audit Transaction 
    function AuditTransaction (o) {
    this.tableName = "auditTransaction";
    this.keyFieldName = "id";
    this.getIdValue = function() { return this.id; }
    this.setIdValue = function(id) { this.id = id; }
    this.id = null;
    this.userName = null;
    this.title = null;
    this.changeDate = null;
    this.AuditActions = [];
    this.controller = null;
    this.parseObject = function(o){
        if (!!o.id) { this.id = o.id; }
        if (!!o.userName) { this.userName = o.userName; }
        if (!!o.title) { this.title = o.title; }
        if (!!o.changeDate) { this.changeDate = o.changeDate; }
        if (!!o.controller) { this.controller = o.controller; }
    }
    this.getInsertStatement = function() {
        // Clean out any existing records for this userName;
        return "INSERT INTO " + this.tableName +  
        "( title, userName, changeDate, controller )" +
        " Values ( ? , ? , ? , ? )";
    }
    this.getInsertParams = function () {
        return [
            this.title,
            this.userName,
            this.changeDate,
            this.controller
        ]
    }
    this.getUpdateStatement = function() {
        return "UPDATE " + this.tableName + " SET " +
        "  title = ? " +
        ", userName = ? " + 
        ", changeDate = ? " +
        ", controller = ? " +
        " WHERE " + this.keyFieldName + " = ? ";
    }
    this.getUpdateParams = function () {
        return [
            this.title,
            this.userName,
            this.changeDate,
            this.controller,
            this.id
        ];
    }
    this.getDeleteStatement = function() {
        return "DELETE FROM " + this.tableName + " WHERE " + this.keyFieldName + " = '" + this.id + "' ";
    }
    this.GetDeleteAllStatement = function() {
        return "DELETE FROM " + this.tableName + "; ";
    }
    if (!!o) {
        this.parseObject(o);
    }
}
    function AuditAction (o) {
    this.tableName = "auditAction";
    this.keyFieldName = "id";
    this.getIdValue = function() { return this.id; }
    this.setIdValue = function(id) { this.id = id; }
    this.id = null;
    this.userName = null;
    this.transactionId = null;
    this.objTableName = null;
    this.obj = null;
    this.changeType = null;
    this.changeDate = null;
    this.parseObject = function(o){
        if (!!o.id) { this.id = o.id; }
        if (!!o.objTableName) { this.objTableName = o.objTableName; }
        if (!!o.userName) { this.userName = o.userName; }
        if (!!o.transactionId) { this.transactionId = o.transactionId; }
        if (!!o.obj) { this.obj = JSON.parse(o.obj); }
        if (!!o.changeType) { this.changeType = o.changeType; }
        if (!!o.changeDate) { this.changeDate = o.changeDate; }
    }
    this.getInsertStatement = function() {
        // Clean out any existing records for this userName;
        return "INSERT INTO " + this.tableName +  
        "( userName, transactionId, changeType, objTableName, obj, changeDate )" +
        " Values ( ?, ?, ?, ?, ? , ? )";
    }
    this.getInsertParams = function () {
        return [
            this.userName,
            this.transactionId,
            this.changeType,
            this.objTableName,
            this.obj,
            this.changeDate
        ]
    }
    this.getUpdateStatement = function() {
        return "UPDATE " + this.tableName + " SET " +
        "  userName = ? " + 
        ", transactionId = ? " +
        ", changeType = ? " + 
        ", objTableName = ? " + 
        ", obj = ? " + 
        ", changeDate = ? " +
        " WHERE " + this.keyFieldName + " = ? ";
    }
    this.getUpdateParams = function () {
        return [
            this.userName,
            this.transactionId,
            this.changeType,
            this.objTableName,
            this.obj,
            this.changeDate,
            this.id
        ];
    }
    this.getDeleteStatement = function() {
        return "DELETE FROM " + this.tableName + " WHERE " + this.keyFieldName + " = '" + this.id + "' ";
    }
    this.GetDeleteAllStatement = function() {
        return "DELETE FROM " + this.tableName + "; ";
    }
    this.getByTransactionId = function() {
        return "SELECT * FROM " + this.tableName + " WHERE transactionId = '" + this.transactionId + "'"
    }
    if (!!o) {
        this.parseObject(o);
    }
}

     

  3. Add the Audit Service. This service will handle creating transactions, linking transactions to actions taken, and actually performing Table CRUD operations. In addition, the rollback/undo functionality will be handled in this service. 
    angular.module('AuditService', [])
    .factory('AuditService', function ($http, $q, Table) {
        var factory = {};
        factory.BeginTransaction = function (title, userName, controllerName) {
            var transaction = new AuditTransaction();
            transaction.userName = userName;
            transaction.title = title;
            transaction.controller = controllerName;
            transaction.changeDate = new Date();
            var deferred = $q.defer();
            Table.create(transaction).then(function (result) {
                if (result) {
                    transaction.setIdValue(result.insertId);
                    deferred.resolve({ success: true, transactionId: transaction.id });
                }
                else {
                    deferred.resolve({ success: false, transactionId: -1 });
                }
            });
            return deferred.promise;
        }
        factory.Update = function (object, transactionId, userName, callback) {
            var deferred = $q.defer();
            _getOriginalFromDatabaseById(object).then(function (result) {
                var original = result;
                var auditAction = new AuditAction();
                auditAction.userName = userName;
                auditAction.transactionId = transactionId;
                auditAction.objTableName = original.tableName;
                auditAction.obj = JSON.stringify(original);
                auditAction.changeType = "Update";
                auditAction.changeDate = new Date();
                Table.create(auditAction).then(function (result) {
                    deferred.resolve(true);
                });
            });
            return deferred.promise;
        }
        factory.Create = function (object, transactionId, userName, callback) {
            var deferred = $q.defer();
            Table.create(object).then(function (result) {
                object.setIdValue(result.insertId);
                var auditAction = new AuditAction();
                auditAction.userName = userName;
                auditAction.transactionId = transactionId;
                auditAction.objTableName = object.tableName;
                auditAction.obj = JSON.stringify(object);
                auditAction.changeType = "Create";
                auditAction.changeDate = new Date();
                Table.create(auditAction).then(function (result2) {
                    deferred.resolve(result2);
                });
            })
            return deferred.promise;
        }
        factory.Delete = function (object, transactionId, userName, callback) {
            var deferred = $q.defer();
            _getOriginalFromDatabaseById(object).then(function (result) {
                var original = result;
                var auditAction = new AuditAction();
                auditAction.userName = userName;
                auditAction.transactionId = transactionId;
                auditAction.objTableName = original.tableName;
                auditAction.obj = JSON.stringify(original);
                auditAction.changeType = "Delete";
                auditAction.changeDate = new Date();
                Table.create(auditAction).then(function (result) {
                    Table.delete(object).then(function (result) {
                        deferred.resolve(true);
                    });
                });
            });
            return deferred.promise;
        }
        factory.LoadAuditLog = function (controllerName, callback) {
            var deferred = $q.defer();
            var retVal = [];
            _getTransactions(controllerName).then(function (transactions) {
                _getAuditActions().then(function (auditActions) {
                    angular.forEach(transactions, function (transaction) {
                        transaction.Actions = auditActions.filter(function (a) { return a.TransactionId == transaction.id; });
                        retVal.push(transaction);
                    })
                    deferred.resolve(retVal);
                });
            });
            return deferred.promise;
        }
        var _callbacks = 0;
        factory.UndoTransaction = function (controllerName, transactionId) {
            var deferred = $q.defer();
            _callbacks = 0;
            _getTransactions(controllerName).then(function (transactions) {
                var filteredTransactions = transactions.filter(function (t) { return t.id == transactionId; });
                _getAuditActions().then(function (auditActions) {
                    angular.forEach(filteredTransactions, function (transaction) {
                        transaction.Actions = auditActions.filter(function (a) { return a.transactionId == transaction.id; });
                        _callbacks++;

                        if (transaction.Actions && transaction.Actions.length > 0) {
                            var n = transaction.Actions.length;
                            while (n--) {
                                var action = transaction.Actions[n];
                                if (action.changeType == 'Create') {
                                    _callbacks++;
                                    _undoCreate(action).then(function (result) {
                                        _callbacks--;
                                        if (_callbacks <= 0) { deferred.resolve(true); }
                                    });
                                }
                                else if (action.changeType == 'Delete') {
                                    _callbacks++;
                                    _undoDelete(action).then(function (result) {
                                        _callbacks--;
                                        if (_callbacks <= 0) { deferred.resolve(true); }
                                    });
                                }
                                else if (action.changeType == 'Update') {
                                    _callbacks++;
                                    _undoUpdate(action).then(function (result) {
                                        _callbacks--;
                                        if (_callbacks <= 0) { deferred.resolve(true); }
                                    });
                                }
                            }
                        }
                        _removeAuditTransaction(transaction).then(function (result) {
                            _callbacks--;
                            if (_callbacks <= 0) { deferred.resolve(true); }
                        });
                    })
                });
            });
            return deferred.promise;
        }
        var _getOriginalFromDatabaseById = function (model) {
            var deferred = $q.defer();
            Table.getById(model.tableName, model.keyFieldName, model.getIdValue()).then(function (objects) {
                if (objects && objects.length > 0) {
                    deferred.resolve(_castObjectToClass(model, objects[0]));
                }
                else {
                    deferred.resolve(null);
                }
                
            });
            return deferred.promise;
        }
        var _getTransactions = function (controllerName) {
            var deferred = $q.defer();
            var model = new AuditTransaction();
            Table.getAllOrderedByColumn(model.tableName, model.keyFieldName, true)
                .then(function (objects) {
                    var audits = [];
                    angular.forEach(objects, function (o) {
                        if (controllerName && o.controller == controllerName) {
                            audits.push(new AuditTransaction(o));
                        }
                    })
                    deferred.resolve(audits);
                });
            return deferred.promise;
        }
        var _getAuditActions = function () {
            var deferred = $q.defer();
            var model = new AuditAction();
            Table.getAll(model.tableName).then(function (objects) {
                var audits = [];
                angular.forEach(objects, function (o) {
                    var aa = new AuditAction(o);
                    aa.obj = _castObjectToClass(aa.obj, aa.obj);
                    audits.push(aa);
                })
                deferred.resolve(audits);
            });
            return deferred.promise;
        }
        var _undoCreate = function (auditAction) {
            var d = $q.defer();
            Table.delete(auditAction.obj).then(function (result) {
                Table.delete(auditAction).then(function (result) { d.resolve(true); });
            })
            return d.promise;
        }
        var _undoDelete = function (auditAction) {
            var d = $q.defer();
            Table.create(auditAction.obj).then(function (result) {
                Table.delete(auditAction).then(function (result) { d.resolve(true); });
            })
            return d.promise;
        }
        var _undoUpdate = function (auditAction) {
            var d = $q.defer();
            Table.update(auditAction.obj).then(function (result) {
                Table.delete(auditAction).then(function (result) {
                    d.resolve(true);
                });
            });
            return d.promise;
        }
        var _removeAuditTransaction = function (auditTransaction) {
            var d = $q.defer();
            Table.delete(auditTransaction).then(function (result) {
                d.resolve(true);
            })
            return d.promise;
        }
        var _castObjectToClass = function (model, obj) {
            if (model) {
                var dbObject = new Object();
                if (model.tableName == 'auditAction') { return new AuditAction(obj); }
                else if (model.tableName == 'auditTransaction') { return new AuditTransaction(obj); }
                else if (model.tableName == 'contact') { return new Contact(obj); }
                else { return dbObject; }
            }
        }
        return factory;
    });

     

  4. Add to the Contacts controller:
    • Load the $scope.AuditLog array with transactions eligible to be rolled back.
    • Wrap calls to the database in calls to the Audit Service.
    • Add calls for undo.
    angular.module('starter.controllers', [])
  .controller('ContactsCtrl', function ($scope, $ionicModal, $ionicPopup, Table, AuditService) {
    $scope.Init = function () {
      $scope.currentController = 'ContactsCtrl';
      $scope.currentUserId = 'user'; //TODO: pull from your local authentication
      Table.getAll(new Contact().tableName).then(function (contacts) {
        var o = [];
        angular.forEach(contacts, function (contact) {
          o.push(new Contact(contact));
        })
        $scope.contacts = o;
      })
      $scope.AuditLog = [];
      LoadAuditLog();
      $ionicModal.fromTemplateUrl('templates/modals/contact-add.html', {
        scope: $scope,
        animation: 'slide-in-up'
      }).then(function (modal) {
        $scope.AddModal = modal;
      });
    }
    $scope.remove = function (contact) {
      var title = "Delete '" + contact.firstName + ' ' + contact.lastName + "'";
      AuditService.BeginTransaction(title, $scope.currentUserId, $scope.currentController).then(function (result) {
        if (result.success) {
          $scope.transactionId = result.transactionId;
          AuditService.Delete(contact, $scope.transactionId, $scope.currentUserId).then(function (result) {
            $scope.contacts.splice($scope.contacts.indexOf(contact), 1);
            LoadAuditLog();
          });
        }
      });
    };

    $scope.add = function () {
      $scope.newContact = new Contact();
      $scope.AddModal.show();
    };
    $scope.save = function () {
      var title = "Save '" + $scope.newContact.firstName + ' ' + $scope.newContact.lastName + "'";
      AuditService.BeginTransaction(title, $scope.currentUserId, $scope.currentController).then(function (result) {
        if (result.success) {
          $scope.transactionId = result.transactionId;
          AuditService.Create($scope.newContact, $scope.transactionId, $scope.currentUserId).then(function (result) {
            if (result) {
              $scope.newContact.id = result.insertId;
              $scope.contacts.push($scope.newContact);
            }
            $scope.AddModal.hide();
            LoadAuditLog();
          });
        }
      });
    }
    $scope.cancelSave = function () {
      $scope.AddModal.hide();
    }

    LoadAuditLog = function () {
      AuditService.LoadAuditLog($scope.currentController).then(function (result) {
        $scope.AuditLog = result;
        $scope.transactionId = -1;
      })
    }
    $scope.undo = function () {
      if ($scope.AuditLog && $scope.AuditLog.length > 0) {
        var transaction = $scope.AuditLog[0];
        var title = "Reverse action?";
        var msg = "Reverse the action <br>'" + transaction.title + "'? <br><br><b>This cannot be undone.</b><br>";
        var go = function () { 
          AuditService.UndoTransaction($scope.currentController, transaction.id).then(function () { $scope.Init(); }); }
        _confirm(title, msg, go, null);
      }
    }
    var _confirm = function (title, message, trueAction, falseAction) {
      var confirmPopup = $ionicPopup.confirm({
        title: title, template: message
      });
      confirmPopup.then(function (res) {
        if (res) {
          if (trueAction) { trueAction(); }
        }
        else if (falseAction) { falseAction(); }
      });
    };
    $scope.Init();
  });

     

  5. Add undo button to the Contacts Template. 
    <ion-view view-title="Contacts">
    <ion-nav-buttons side="secondary">
        <button class="button button-icon icon ion-ios-undo" ng-click="undo()" ng-disabled="AuditLog.length<=0"></button>
    </ion-nav-buttons>
    <ion-content>
        ....
    </ion-content>
</ion-view>

     

  6. Run the app, and click the Contacts tab.Add contacts to the list and verify that you can remove them via the undo functionality.
  7.  contacts-undo-available  contacts-undo-confirm  contacts-undo-done

 

While this example is relatively simple, you can chain numerous steps into a single transaction, allowing complex functionality to be reversed via a single action.

Leave a Reply

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

↑
©2024 eimagine. All rights reserved.