Thursday, 29 May 2014

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;

 

 

 

 

 

6 comments:

  1. If I wanna do a simple SELECT for example to create a login function I can write
    var sql = "SELECT * FROM " + table + " WHERE "+ whereQ.join(" AND ");

    db = Ti.Database.open(collection.config.adapter.db_name);
    var rows= db.execute(sql);
    var loggedIn= false;
    while (rows.isValidRow()) {
    loggedIn= true;
    }
    db.close();
    collection.trigger('sync');

    return loggedIn;

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Great job, man. But, in the operation of update the correct property is values and not value (example).

    One more correction: in deleteAllRecords this code should be removed:

    for (var k in opts.query.columns) {
    names.push(opts.query.columns[k]);
    q.push("?");
    }

    Tks!

    ReplyDelete
  4. Great Job, but please update your code when someone found mistakes :)

    ReplyDelete
  5. And for the update, i should update twice before the alloy view is updated

    ReplyDelete