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;
