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.
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.
The following steps demonstrate how to check if a table exists within an SQLite database.
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; };
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') };
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' });
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")); });
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.'),
});
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'),
});
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.
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 = {
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; },
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 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);
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=?';
tableName
variable as its parameter. A Ti.Database.DbResultSet
is returned and allocated to the getReader
variable:var getReader = conn.execute(selectSQL,tableName);
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 );
DbResultSet
and database is then closed to reduce the number of active objects:getReader.close(); conn.close(); getReader = null; conn = null;
return doesExist; };
18.217.254.118