Titanium Alloy - Migration with Model Tutorial

Hi Friends,

I am busy with my work. Here I am going to write post about "Migration".

What is Migration?
     A migration is a description of incremental changes to a database, which takes your database from version 1 to version X, with a migration file for each step in the evolution of your database schema. This is helpful to keep different versions of a database in sync. For example, when version 7 of your application is deployed, migrations are able to successfully update the database from versions 1 through 6.

Where I put migration files in my Titanium project?
      In Alloy, migrations are defined by JavaScript files located in the app/migrations folder of the project.

Is there is any naming conversion require for migration files?
     Yes, The file should be named the same as the model JavaScript file prefixed with 'YYYYMMDDHHmmss_' (datetime code followed by an underscore), for example, 20120610049877_movies.js(**your model file name should be movies.js). Alloy applies the migrations from oldest to newest, according to the datetime code at the beginning of the file name.

What are the functions used in migration files for upgrade my db?
     The migration file contains two functions that need to be implemented :  migration.up(migrator)  and migration.down(migrator), where migrator is a special migration object that provides references to the database and table as well as some convenient functions for table operations (see table below). The migration.up function upgrades the database from the previous version, while the migration.down function rolls back the changes to the previous version.

Is migration supports all type of adapters?
    Currently, migrations are only used with the sql sync adapter.

Example Project:
    In this tutorial i am creating simple project you can download source code from following github repository.   AlloyMigrationExample

1. Create simple Alloy project
2. Put you preloaded sqlite db file into asset folder.
3. My sqlite db name is "sampledb.sqlite" and the db has "user_accounts"  table with some preloaded data

We are going to add migration files for add a new table "movies" and insert the preloaded data.

4. In Titanium Studio, Right click on the project --> New --> Alloy Migration -->Enter your migration table name. (No need to add timedate , Titanium Studio add this for you). In this example we enter "movies". Now you can see migration file in app/migrations/20130812093432_movies.js.  (Timedate string will be different ignore it. You can edit timedate string as per your need).

5. Open the 20130812093432_movies.js. file and you can see two function called migration.up and migration.down.  Modify code as follow,

 
migration.up = function(migrator) {
 migrator.createTable({
  columns: {
   id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
   title: 'TEXT',
   subtitle: 'TEXT',
   image: 'TEXT'
  }
 });
};

migration.down = function(migrator) {
 migrator.dropTable("movies");
};

6. migrator.createTable() function

•     This method used to create table at run time and added into database.
•      columns: In this field you can define your table columns with type. 

7. Create Another migrator file for insert the preloaded data in movies table.

• Repeat the Step 4. (Exactly same). Finally you get new migrator file with different time date string.
• Which means older get executed first. 
• we get 201308120941308_movies.js  , new migration file open this file and modify it.

 
var products = [];
for (var i = 0; i < 500; i++) {
 if (i%7 === 0) {
  products.push({
   title: 'This is the title',
   subtitle: 'This is the slightly more verbose subtitle',
   image: i%2 ? '/appc.png' : '/alloy.png'
  });
 } else if (i%2) {
  products.push({
   title: 'This is the title with subtitle',
   subtitle: 'This is the slightly more verbose subtitle'
  });
 } else {
  products.push({
   title: 'This is the lonely title'
  });
 }
}

migration.up = function(migrator) {
 for (var i = 0; i < products.length; i++) {
  migrator.insertRow(products[i]);
 }
};

migration.down = function(migrator) {
 for (var i = 0; i < products.length; i++) {
  migrator.deleteRow(products[i]);
 }
};

8. Create model file for movies

•     Right click on project --> New --> Alloy Model --> Enter Model Name "movies"
•     Modify the app/Model/movies.js

exports.definition = {
 config: {
  columns: {
   id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
   title: 'TEXT',
   subtitle: 'TEXT',
   image: 'TEXT'
  },
  
  adapter: {
   type: "sql",
   collection_name: "movies",
   idAttribute: 'id',
  }
 }
}; 

Now you can use this model same as other models used in Alloy. You can download working example from github.  Download full working example source code from AlloyMigrationExample

Thanks.

Alloy - Sqlite CreateTable Example

I created the sample project for createTable into preloaded sqlite database in Alloy Framework

download working example form gist alloySqliteCreateTable

Main logic written in app/controller/index.js

 
function createTable(e) {  
 var db = Ti.Database.open('codeguru');
 if(db){
  db.execute('CREATE TABLE people (name TEXT, phone_number TEXT, city TEXT)');
  var thisName = 'Arthur';
  var thisPhoneNo = '1-617-000-0000';
  var thisCity = 'Mountain View';
  db.execute('INSERT INTO people (name, phone_number, city) VALUES (?, ?, ?)', thisName, thisPhoneNo, thisCity);

  var personArray = ['Paul','020 7000 0000', 'London'];
  db.execute('INSERT INTO people (name, phone_number, city) VALUES (?, ?, ?)', personArray);

  var rows = db.execute('SELECT rowid,name,phone_number,city FROM people');
  while (rows.isValidRow())
  {
    Ti.API.info('Person ---> ROWID: ' + rows.fieldByName('rowid') + ', name:' + rows.field(1) + ', phone_number: ' + rows.fieldByName('phone_number') + ', city: ' + rows.field(3));
    rows.next();
  }
  rows.close();
 }
    alert($.label.text);
}

This is an quick fix for creating table in runtime. And I will working on collection of data inserted through model.js 

 For now, Refer this following repository for bulk updates in sqlite db alloy framework, One way to do bulk updates and deletes with Appcelerator Alloy Collections bulk_update

Alloy Framework - Use Preloaded Sqlite Database

Most of time you may need to use the preloaded db in your application.

We are going to see How to use preloaded SQLite Database in Alloy framework in titanium.

Download the source code from git repository  AlloySqlitedb

1. Open the Titanium Studio  (I am using 3.1.1)
2. create New Alloy Mobile Project  (refer the Link for creating New Alloy Project)
3. Put your sqlite database file into app/assets folder. (I am using myapp.sqlite)
4.create collections object in alloy.js file.

 
Alloy.Collections.fighters = Alloy.createCollection('fighters');

5.create model file "fighters.js" in app/model directory.

 
exports.definition = {
  config:{
   
   adapter:{
    "type":"sql",
    "collection_name":"fighters",
    "db_file": "/myapp.sqlite",
    "db_name": "fighters",
    "idAttribute": "id",
    "remoteBackup":false
   }
  }
}

6.Modify index.xml file in app/views/ directory - create Table View.

 

    
        Fighters
        
            
        
    

7. Create controller index.js file in app/controller directory

 
var fighters = Alloy.Collections.fighters;
var counter = 1;

function showId(e) {
 if (e.row.model) {
  //alert(e.row.model);
  var detailObj=fighters.get(e.row.model);
  var win=Alloy.createController('detail',{"$model":detailObj});
     win.getView().open();
 }
}

function addTestFighter(e) {
 // create the test fighter model
 var model = Alloy.createModel('fighters', {
  name: 'Name ' + counter,
  nickname: 'Nickname ' + counter
 });
 counter++;

 // add model to the collection and save it to sqlite
 fighters.add(model);
 model.save();

 // let's refresh so we can see the ids coming from the 
 // autoincrement field in the sqlite database in the 
 // row click alerts
 fighters.fetch();
}

fighters.fetch();

$.index.open();

8. Create layout for row.xml in app/views directory

9. Create controller row.js in app/controller directory

 
// Attach the bound model ($model) to the row so 
// we can access it in a click event.
if ($model) {
 $.row.model = $model.toJSON();
}

10. Create layout for for detail.xml in app/views directory

 

 
  
   
    
    
   >
  >
 >

11. Create controller detail.js in app/controller directory.

 
var args=arguments[0]||{};
if(args.$model){
 Ti.API.info('model ='+JSON.stringify(args.$model));
 var dataJson=args.$model.toJSON();
}else{
 alert('data not passed');
}

12. Finally add the styles to your layouts in app/styles

a. index.tss

 
"Window": {
 backgroundColor: '#fff',
 navBarHidden: true,
 exitOnClose: true
}

"#title": {
 top: 0,
 height: '50dp',
 width: Ti.UI.FILL,
 color: '#fff',
 backgroundColor: '#a00',
 font: {
  fontSize: '36dp',
  fontWeight: 'bold'
 },
 textAlign: 'center'
}

"#table": {
 top: '50dp',
 bottom: 0
}

b.row.tss

 
"#row": {
 height: '60dp',
 backgroundColor: '#fff'
}

"Label": {
 height: Ti.UI.SIZE,
 width: Ti.UI.SIZE
}

"#name": {
 top: '7dp',
 left: '7dp',
 font: {
  fontSize: '24dp',
  fontWeight: 'bold'
 }
}

"#nickname": {
 bottom: '7dp',
 left: '14dp',
 font: {
  fontSize: '16dp',
  fonWeight: 'normal'
 }
}

c. detail.tss

 
"Window": {
 backgroundColor: '#fff',
 navBarHidden: true
}
"#name": {
 top: '10dp',
 font: {
  fontSize: '24dp',
  fontWeight: 'bold'
 }
}

"#nickname": {
 top: '10dp',
 font: {
  fontSize: '16dp',
  fonWeight: 'normal'
 }
}

13. Here we go and run the project.


Thanks.