Alloy Collection CRUD Operations :

In Titanium Alloy, We are using many ways to Update, Delete, Insert or Replace Operation to Database Table.

I have using below way to CRUD Operation on Database Table.
Let’s Start From Example :

Models : Employee.js

exports.defination = {
     config : {
         columns : {
              "Name" : "TEXT",
              "Description" : "TEXT",
              "Gender" : "TEXT"
         },
         adapter : {
              type : "sql",
              collection_name : "employee"
         }
     },
     extendModel : function(Model) {
          _.extend(Model.prototype, {
               // extended functions and properties go here
          });
          return Model;
     },
     extendCollection : function(Collection) {
          _.extend(Collection.prototype, {
          // extended functions and properties go here
          
          insertRecord : function(opts) {
             var collection = this;
             var dbName = collection.config.adapter.db_name;
             var table = collection.config.adapter.collection_name;
             var columns = collection.config.columns;
             var names = [], q = [];
             for (var k in opts.query.columns) {
                  names.push(opts.query.columns[k]);
                  q.push("?");
             }
             var sql = "INSERT INTO " + table + " (" + names.join(",") + ") VALUES (" + q.join(",") + ");";
             db = Ti.Database.open(collection.config.adapter.db_name);
             db.execute(sql, opts.query.value);
             db.close();
             collection.trigger('sync');
         },

         insertORReplaceRecord : function(opts) {
             var collection = this;
             var dbName = collection.config.adapter.db_name;
             var table = collection.config.adapter.collection_name;
             var columns = collection.config.columns;
             var names = [], q = [];
             for (var k in opts.query.columns) {
                  names.push(opts.query.columns[k]);
                  q.push("?");
             }
             var sql = "INSERT OR REPLACE INTO " + table + " (" + names.join(",") + ") VALUES (" + q.join(",") + ");";
             db = Ti.Database.open(collection.config.adapter.db_name);
             db.execute(sql, opts.query.value);
             db.close();
             collection.trigger('sync');
         },

        updateRecord : function(opts) { 
             var collection = this;
             var dbName = collection.config.adapter.db_name;
             var table = collection.config.adapter.collection_name;
             var columns = collection.config.columns;
             var names = [], whereQ = [], values=[]; 
             
             for (var i in opts.query.columns) {
                  names.push(opts.query.columns[i]+”=?”);
             }
            for (var i in opts.query.whereKey) {
                 whereQ.push(opts.query.whereKey[i]+”=?”);
            }
 
            //Values of Set Columns and Where Condition
            for (var j in opts.query.values) {
                 values.push(opts.query.values[j]);
            }
            for (var k in opts.query.whereValue) {
                 values.push(opts.query.whereValue[k]);
            }
            
            var sql = “UPDATE “ + table + “ SET “ + names.join(“,”) + “ WHERE “+ whereQ.join(“ AND “);
            db = Ti.Database.open(collection.config.adapter.db_name);
            db.execute(sql, values);
            db.close();
            collection.trigger(‘sync’);
        },

       deleteRecord : function(opts) {
           var collection = this;
           var dbName = collection.config.adapter.db_name;
           var table = collection.config.adapter.collection_name;
           var columns = collection.config.columns;
           var names = [], q = [];
           
           for (var k in opts.query.columns) {
               names.push(opts.query.columns[k]);
               q.push(“?”);
           }
           
          var sql = “DELETE FROM “ + table + “ “ + opts.query.sql;
          db = Ti.Database.open(collection.config.adapter.db_name);
          db.execute(sql, opts.query.params);
          db.close();
          collection.trigger(‘sync’);
      },

     deleteAllRecords : function() {
         var collection = this;
         var dbName = collection.config.adapter.db_name;
         var table = collection.config.adapter.collection_name;
         var sql = “DELETE FROM “ + collection.config.adapter.collection_name;
         db = Ti.Database.open(collection.config.adapter.db_name);
         db.execute(sql);
         db.close();
         collection.trigger(‘sync’);
    }
});
return Collection;
}};
 

You have checked above js file of Employee.js which is a model file of the sample project.
we are going to apply Operations on this Employee mode:

Lets start the operation mean how can i cal the all operation from js files.

1. Insert Record to Employee Collection :
============================

Alloy.Collections.Employee.insertRecord({
     query : {
          columns : [“Name”, “Description”, “Gender”],
          value : [“JOHN”, “NO WORDS”, “MALE”]
     }
});

SQL Query : INSERT INTO Employee (“NAME”, “Description”, “Gender”) VALUES (“JOHN”, “NO WORDS”, “MALE”);

2. Insert OR Replace Record to Employee Collection :
=====================================

Alloy.Collections.Employee.insertORReplaceRecord({
     query : {
         columns : [“Name”, “Description”, “Gender”],
         value : [“CARTER”, “NO WORDS”, “FEMALE”]
     }
});

SQL Query : INSERT OR REPLACE INTO Employee (“NAME”, “Description”, “Gender”) VALUES (“CARTER”, “NO WORDS”, “FEMALE”);

3. Upadate Record to Employee Collection :
===============================

Alloy.Collections.Employee.updateRecord({
     query : {
          columns : [“Name”, “Description”],
          value : [“JOHN Deny”, “I AM DEVELOPER”],
          whereKey : [“Name”, “Gender”],
          whereValue : [“JOHN”, “MALE”]
     }
});

SQL Query : UPDATE Employee SET Name=”JOHN Deny”,Description=”I AM DEVELOPER” WHERE NAME=”JOHN” AND Gender=”MALE”;

4. Delete Record to Employee Collection :
===============================

Alloy.Collections.Employee.deleteRecord({
     query : {
          sql : “WHERE Name=?”,
          params : “JOHN”
     }
});

SQL Query : DELETE FROM Employee WHERE Name=”JOHN”;

5. Delete All Records to Employee Collection :
================================

Alloy.Collections.Employee.deleteAllRecords();

SQL Query : DELETE FROM Employee;

Check out other Blogs :
http://titaniumtuts.blogspot.in/

Author :
Jigar Maheshwari