Create a gist now

Instantly share code, notes, and snippets.

One way to do bulk updates and deletes with Appcelerator Alloy Collections
// add all items to collection
Alloy.Collections.Fugitive.reset([{
"name" : "Jeff Haynie"
}, {
"name" : "Nolan Wright"
}, {
"name" : "Don Thorp"
}, {
"name" : "Marshall Culpepper"
}, {
"name" : "Blain Hamon"
}]);
// save all the items
Alloy.Collections.Fugitive.saveAll();
// get the collection object
Alloy.Collections.instance("Fugitive");
// delete all items
Alloy.Collections.Fugitive.deleteAll();
exports.definition = {
config : {
"columns" : {
"name" : "TEXT",
"captured" : "integer",
"url" : "TEXT",
"capturedLat" : "real",
"capturedLong" : "real"
},
"defaults" : {
"name" : "",
"captured" : 0,
"url" : "",
"capturedLat" : "",
"capturedLong" : ""
},
"adapter" : {
"type" : "sql",
"collection_name" : "fugitives"
}
},
extendModel : function(Model) {
_.extend(Model.prototype, {
});
// end extend
return Model;
},
extendCollection : function(Collection) {
// helper functions
function S4() {
return (0 | 65536 * (1 + Math.random())).toString(16).substring(1);
}
function guid() {
return S4() + S4() + "-" + S4() + "-" + S4() + "-" + S4() + "-" + S4() + S4() + S4();
}
_.extend(Collection.prototype, {
deleteAll : function() {
var collection = this;
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');
},
saveAll : function() {
var collection = this;
var dbName = collection.config.adapter.db_name;
var table = collection.config.adapter.collection_name;
var columns = collection.config.columns;
db = Ti.Database.open(dbName);
db.execute("BEGIN;");
collection.each(function(model) {
if (!model.id) {
model.id = guid();
model.attributes[model.idAttribute] = model.id;
}
var names = [], values = [], q = [];
for (var k in columns) {
names.push(k);
values.push(model.get(k));
q.push("?");
}
var sqlInsert = "INSERT INTO " + table + " (" + names.join(",") + ") VALUES (" + q.join(",") + ");";
db.execute(sqlInsert, values);
});
db.execute("COMMIT;");
db.close();
collection.trigger('sync');
}
});
// end extend
return Collection;
}
};
@Skoua
Skoua commented Oct 15, 2013

Very useful, thanks!

@sukima
sukima commented Dec 23, 2013

Couldn't you call model.save() and have it write to the DB instead of constructing manual INSERT statements?

@sukima
sukima commented Dec 23, 2013

Never mind. model.save() is dog slow. Manual SQL is faster 😉

@arthurpadilha

Hello there.
Thank you for your model code.
However it keeps giving me erros when trying to use the saveAll function.
The following error pops up:
[ERROR] : Script Error Couldn't find module: alloy/sync/util

I've made some research but couldn't find much about the alloy/sync/util

is there any file that I should add to the app folder in order to make line 52: var util = require("alloy/sync/util");, to work?

Thank you

@aaronksaunders
Owner

@arthurpadilha see updated gist, added the guid function to the model file

@Adriano72

Dear Aaron,

first of all thanks for this useful code

Unfortunately adding the guid and s4 functions in the model file throws me an error.

The error is: Unexpected token name «S4», expected punc «:»

I'm using SDK 3.2.3

What I'm doing wrong?

Thanks!
Best
Adriano

My file:

exports.definition = {

function S4() {
    return (0 | 65536 * (1 + Math.random())).toString(16).substring(1);
}

function guid() {
    return S4() + S4() + "-" + S4() + "-" + S4() + "-" + S4() + "-" + S4() + S4() + S4();
}

config : {

    columns : {
        id : "integer"
    },

    adapter : {
        type : "sql",
        collection_name : "post_timeline"
    }
},
extendModel : function(Model) {
    _.extend(Model.prototype, {
        // extended functions and properties go here
    });

    return Model;
},
extendCollection : function(Collection) {
    _.extend(Collection.prototype, {

        deleteAll : function() {

            var collection = this;

            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');

        },

        saveAll : function() {
            var collection = this;

            var dbName = collection.config.adapter.db_name;
            var table = collection.config.adapter.collection_name;
            var columns = collection.config.columns;

            db = Ti.Database.open(dbName);
            db.execute("BEGIN;");

            collection.each(function(model) {

                if (!model.id) {
                    model.id = guid();
                    model.attributes[model.idAttribute] = model.id;
                }

                var names = [], values = [], q = [];
                for (var k in columns) {
                    names.push(k);
                    values.push(model.get(k));
                    q.push("?");
                }
                var sqlInsert = "INSERT INTO " + table + " (" + names.join(",") + ") VALUES (" + q.join(",") + ");";

                db.execute(sqlInsert, values);

            });

            db.execute("COMMIT;");
            db.close();

            collection.trigger('sync');
        }
    });

    return Collection;
}

};

@aaronksaunders
Owner

@Adriano72 updated

@Adriano72

Thx! Big hug from TiConf 2014 Amsterdam!

@ksouthworth

Thank You! So useful

@xcash
xcash commented Nov 17, 2014

@aaronksaunders minor optimization: take the names.push(k) out of collection.each and create the names array in a separate one-time cycle.
On a 100 element insert you'll save (100*numcols)-1 .push() calls.

@belevski

@aaronksaunders I'm using this SaveAll function in my model.
I have this config:
config: {
columns: {
"jobID": "INTEGER",
"jobTitle": "TEXT",
"joblastUpdated": "TEXT",
"jobLastDayOfService": "TEXT",
"jobInstructionReported": "INTEGER",
"jobInstructions": "TEXT",
"noReports": "INTEGER",
"instructionId": "INTEGER"
},
adapter: {
type: "sql",
collection_name: "jobs",
db_name: "DB",
idAttribute:'jobID'
}
},
jobID is unique and i'm receiving this id from the backed same as other values.

Insert is working fine but i can't make insert or replace to work:
var sqlInsert = "INSERT OR REPLACE INTO " + table + " (" + names.join(",") + ") VALUES (" + q.join(",") + ");";
db.execute(sqlInsert, values);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment