In the last tutorial, I examined some additional techniques for querying an existing database. Today I want to explain how you create a database and create a table inside of that database.. If you are good with SQL and have followed along with this series, this concept will work as expected.

Creating Databases

I briefly touch on creating databases in a previous tutorial. The concept is very similar to working with a pre-populated database. Just as with a pre-populated database you will need to create an instance of the File class that points to a file. If that file does not exist, then the database will be created when the open or openAsync methods are called.

...
// Create an instance of the SQLConnection Class
var conn:SQLConnection = new SQLConnection();
// Create an instance of the File Class that points to the file
var db:File = File.applicationStorageDirectory.resolvePath("newDB.db");
// Open the connection - which will create the DB
conn.openAsync( db );
...

If you do not pass an instance of the File class to the open or openAsync methods, a database is created in memory, and it will remain as long as the application is open.

Affinity Types

Before you can create any tables, you will have to know about affinity types (data types) within SQLite. AIR contains ten different affinity types that can be used:

• TEXT (also called STRING)
• NUMERIC
• INTEGER:
• REAL (also called NUMBER)
• BOOLEAN
• DATE
• XML
• XMLLIST
• OBJECT
• NONE

NOTE: Each affinity can handle data differently. You will want to consult the information on Affinity Types in the documentation before you design your database schema.

Creating Tables

To create tables within AIR, you simply need to execute an SQLStatement just as you did to query the data. For example, let's say that you wanted to create a table names 'Contacts' with the following fields:

1. firstName: This will just be a string
2. lastName: This will just be a string
3. age: This will be a uint
4. gender: This will be a Boolean value

The table is created in the example below:

private function init():void {
 
 	dbFile = File.applicationDirectory.resolvePath( "contacts.db" );
 	conn = new SQLConnection();
 	conn.addEventListener(SQLEvent.OPEN, openSuccess);
 	conn.addEventListener(SQLErrorEvent.ERROR, openFailure);
 	conn.openAsync( dbFile );
 
}

private function openSuccess( event:SQLEvent ):void {
 	createTable();
}

private function openFailure( event:SQLEvent ):void {
 	throw new Error( 'Database Connection Failed' );
}

private function createTable():void {
 	
 	var q:SQLStatement = new SQLStatement();
 	q.sqlConnection = conn;
 	
 	var sql:String = "CREATE TABLE IF NOT EXISTS contacts( " +
 				"firstName TEXT, " +
 				"lastName TEXT, " +
 				"age INTEGER, " +
 				"gender BOOLEAN )";
 					
 	q.text = sql;
 	q.addEventListener( SQLEvent.RESULT, queryResult );
 	q.addEventListener( SQLErrorEvent.ERROR, queryError );
 	q.execute();
 	
}

private function queryError( event:SQLErrorEvent ):void {
 	trace( 'Create Table Failed' );
}

private function queryResult( event:SQLEvent ):void {
 	trace( 'Query Created Successfully' );
}

The actual SQL statement is found inside of the createTable method. There are a few things that could have been added to this table (such as an auto-incrementing ID field), but hopefully now you feel comfortable creating tables with the embedded SQLite database in AIR.

Deleting Tables

If you are in a situation where you need to delete a table, you will need to execute the following SQLStatement:

DROP TABLE IF EXISTS contacts;

If you omit the 'IF EXISTS' portion of the query, and the table does not exists (for example, if it was already deleted) the query response will be an error.

Resources

Column Affinity Descriptions

Read more from David Tucker.