DbTableChecker SQLite table existence checking

SQLite and the Ti.Database API provide many powerful features. This recipe demonstrates how to use the existing Ti.Database API and SQL statements to check if a table has already been created.

The ability to check if database objects exist is critical for Enterprise apps, for versioning and data migration purposes A typical usage of this process would be to check if a table already exists during schema migration. For example, a specific data migration might be performed if the client table in your app already exists while creating the table for the first time.

Getting ready

This recipe relies on the Titanium framework's Ti.Database API, requiring no dependencies. In the next section, we create a simple app.js file, demonstrating how to perform the table-exists check.

How to do it…

The following steps demonstrate how to check if a table exists within an SQLite database.

Creating our module

In Titanium Studio, create a module called db tablechecker.js. This module should have the following code snippet:

exports.tableExists = function (dbName, tableName){
  var conn = Ti.Database.open(dbName);
  var selectSQL ='SELECT name FROM sqlite_master 'selectSQL +=' WHERE type="table" AND name=?';

  var getReader = conn.execute(selectSQL,tableName);
  var doesExist = (getReader.getRowCount() > 0 );

  //Clean-up
  getReader.close();
  conn.close();
  getReader = null;
  conn = null;

  return doesExist;
};

Namespace and app setup

Next, in our application's app.js file, we create our application namespace and use the require method to import the CommonJS module into our application:

//Create our application namespace
var my = {
  dbTableChecker : require('dbtablechecker')
};

Creating our window

To help demonstrate this recipe, a window with three buttons is created. These buttons will allow you to create, test whether the table exists, and remove the myTable sample table.

var win = Ti.UI.createWindow({
  backgroundColor:'#fff',layout:'vertical'
});

Testing if a table exists

The first button in the recipe demonstrates how to call the tableExists function created earlier in the CommonJS module of dbTableChecker:

var tableExistsBtn = Ti.UI.createButton({
    title:'Does Table Exist?', height:50, right:5, left:5, top: 20
});
win.add(tableExistsBtn);

When the button is pressed, the tableExists function is called within our CommonJS module to determine if the specified table exists. The next highlighted code snippet demonstrates checking if a table named myTable exists within the myDatabase SQLite database.

tableExistsBtn.addEventListener('click',function(e){
  //Check if our table exists in our database
  var doesExist = my.dbTableChecker.
tableExists('myDatabase','myTable'),
  //Alert the user if the table exists or not
  alert('Table "myTable" ' + (doesExist ? ' exists' : "does not exist"));
});

Creating a table

The next button in this recipe is used to create a sample table, which is to be used in our tests. When exploring this recipe, this button will allow you to drop and re-create a sample table several times.

var makeTestBtn = Ti.UI.createButton({
  title:'Create Test Table', height:50, right:5, left:5, top: 20
});
win.add(makeTestBtn);

The following highlighted code demonstrates calling the my.testers.makeTable function to create a table named myTable in the myDatabase database:

makeTestBtn.addEventListener('click',function(e){
  //Create a sample table
  my.testers.makeTable('myDatabase','myTable'),

  //Alert the user a test table has been created
  alert('Table "myTable" was created.'),
});

Tip

See this recipe's How it works… section for more information on my.testers.

Removing a table

The last button in this recipe is used to drop the sample table. When exploring this recipe, this button will allow you to drop and re-create a sample table several times:

var removeTestBtn = Ti.UI.createButton({
  title:'Remove Test Table', height:50, right:5, left:5, top: 20
});
win.add(removeTestBtn);

The highlighted line in the following code snippet demonstrates how to call the my.testers.removeTable function to drop a table named myTable in our database:

removeTestBtn.addEventListener('click',function(e){
  //Create a sample table
  my.testers.removeTable('myDatabase','myTable'),
  //Alert the user a test table has been removed
  alert('Table "myTable" was removed'),
});

Tip

See this recipe's How it works… section for more information on my.testers.

How it works…

This recipe uses the SQLite data dictionary and several helper methods to support testing. The functionality of these methods and how they are composed, is discussed here.

Testing helpers

This recipe uses two helper functions, makeTable and dropTable, to manage our sample table. These methods allow for the tableExists method to be tested repeatedly without conflict.

my.testers = {
  1. The makeTable function uses dbName to open a database connection. Once the database has been opened, a table is created (if it doesn't exist) using the provided tableName parameter:
      makeTable : function(dbName,tableName){
    var conn = Ti.Database.open(dbName);
    var createSql = 'CREATE TABLE IF NOT EXISTS ' 
    createSql += tableName ;
    createSql += '(id INTEGER PRIMARY KEY AUTOINCREMENT,';
    createSql += ' my_column TEXT)';

    conn.execute(createSql);

    //Clean-up
    conn.close();
    conn = null;
  },
  2. The removeTable function uses dbName to open a database connection. Once the database has been opened, the table name provided in the tableName parameter is dropped, if the table exists:
      removeTable : function(dbName,tableName){
    var conn = Ti.Database.open(dbName);
    var dropSql = 'DROP TABLE IF EXISTS ' + tableName;

    conn.execute(dropSql);


    Conn.close();
    conn = null;
  }
};

The tableExists method

The dbTableCheck module has a single method named tableExists which returns a Boolean result if the provided table name exists within the database. This check is performed by querying the data dictionary of the SQLite database.

exports.tableExists = function (dbName, tableName){
  var conn = Ti.Database.open(dbName);
  1. The following SQL statements will query the SQLite data dictionary table, sqlite_master, for a list of tables with a specified table name. The ? character is a parameter that is replaced by the execute method.
      var selectSQL ='SELECT name FROM sqlite_master; selectSQL +=' WHERE type="table" AND name=?';
  2. The data dictionary query is then executed using the tableName variable as its parameter. A Ti.Database.DbResultSet is returned and allocated to the getReader variable:
      var getReader = conn.execute(selectSQL,tableName);
  3. Next, the getRowCount method is used to determine if any rows are returned. This is converted into a Boolean statement that will later be returned by this method:
      var doesExist = (getReader.getRowCount() > 0 );
  4. The DbResultSet and database is then closed to reduce the number of active objects:
      getReader.close();
  conn.close();
  getReader = null;
  conn = null;
  5. The Boolean result determined earlier is returned by the following method:
      return doesExist;
};
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.117.101.178