create or alter a SQLite table on Android

Yesterday I basically freaked out while coding some stuff for Android. In the sqlite api is no function for creating or altering a table. All this has to be done manually by creating sql queries! Wtf?!? :oogle:
So I sat down and wrote a small function that will take care of that for me from now on. It’s nothing freaky though but maybe someone else finds it useful too so here it comes :mrgreen:


Some Variables/Constants have to be defined to use it but that’s not a big deal. The more interesting part is the List the function expects. It has to consist out of string arrays with 2 elements. The first is the name of the column while the second is the “field type”.
E.g. new String[] {"name", "TEXT"} or new String[] {"id", "INTEGER PRIMARY KEY AUTOINCREMENT"}
Basically everything that exist in SQLite should be usable here.

public boolean createTable(String tableName, List<String[]> fields) throws Exception {
	try {
		SQLiteDatabase db = openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);			
	} catch (SQLException e) {
		Log.e(LOGGER, e.getMessage());
		return false;
	}
	try {
		/* check if the table is there */
		Cursor c = db.query(tableName, null, null, null, null, null, null);
		if(c != null) {
			/* lets check all the columns */
			for(int i = 0; i < fields.size(); i++) {
				if(fields.get(i).length != 2)
					throw new Exception("Array has the wrong size");
				if(c.getColumnIndex(fields.get(i)[0]) == -1) {
					/* column doesnt exist -> alter the table */
					String alterQuery = "";
					try {
						alterQuery = "ALTER TABLE " + tableName + " ADD COLUMN " + fields.get(i)[0] + " " + fields.get(i)[1];
						db.execSQL(alterQuery); 
					} catch(SQLException e) {
						/* Houston we have a problem... */
						Log.e(LOGGER, "Could not alter table: " + alterQuery + " Exception: " + e.getMessage());
						return false;
					}
				}
			}
		}
	} catch(SQLException e) {
		/* table doesnt exist yet -> create it with all given columns */
		String createQuery = "CREATE TABLE " + tableName + " (";
		for(int i = 0; i < fields.size(); i++) {
			if(fields.get(i).length != 2)
				throw new Exception("Array has the wrong size");
			createQuery += fields.get(i)[0] + " " + fields.get(i)[1];
			if(i < fields.size() - 1)
				createQuery += ",";
		}
		createQuery += ")";
		try {
			db.execSQL(createQuery); 
		} catch(SQLException ee) {
			/* Houston we have a problem... */
			Log.e(LOGGER, "Could not create table: " + createQuery + "\nException: " + ee.getMessage());
			return false;
		}
	}
	return true;
}

There’s probably some stuff to optimize but this works out for me and fulfills my needs. So feel free to copy and change it ;)

Leave a Reply