Create a gist now

Instantly share code, notes, and snippets.

Embed
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

This comment has been minimized.

Show comment
Hide comment
@Skoua

Skoua Oct 15, 2013

Very useful, thanks!

Skoua commented Oct 15, 2013

Very useful, thanks!

@sukima

This comment has been minimized.

Show comment
Hide comment
@sukima

sukima Dec 23, 2013

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

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

This comment has been minimized.

Show comment
Hide comment
@sukima

sukima Dec 23, 2013

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

sukima commented Dec 23, 2013

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

@arthurpadilha

This comment has been minimized.

Show comment
Hide comment
@arthurpadilha

arthurpadilha Jan 13, 2014

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

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

This comment has been minimized.

Show comment
Hide comment
@aaronksaunders

aaronksaunders Feb 22, 2014

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

Owner

aaronksaunders commented Feb 22, 2014

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

@Adriano72

This comment has been minimized.

Show comment
Hide comment
@Adriano72

Adriano72 May 26, 2014

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

};

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

This comment has been minimized.

Show comment
Hide comment
Owner

aaronksaunders commented May 28, 2014

@Adriano72 updated

@Adriano72

This comment has been minimized.

Show comment
Hide comment
@Adriano72

Adriano72 Jun 28, 2014

Thx! Big hug from TiConf 2014 Amsterdam!

Thx! Big hug from TiConf 2014 Amsterdam!

@ksouthworth

This comment has been minimized.

Show comment
Hide comment
@ksouthworth

ksouthworth Oct 9, 2014

Thank You! So useful

Thank You! So useful

@xcash

This comment has been minimized.

Show comment
Hide comment
@xcash

xcash 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.

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

This comment has been minimized.

Show comment
Hide comment
@belevski

belevski Jul 14, 2015

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

@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