Alloy Collection CRUD

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.definition = {
    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"]
   }
 });

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"]
   }
 });

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"]
   }
 });

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"
   }
 });

Query : DELETE FROM Employee
WHERE Name="JOHN"; 

 

5. Delete All Records to Employee Collection : 

Alloy.Collections.Employee.deleteAllRecords();

Query : DELETE FROM Employee;