Tag Archives: Database Programming

Creating custom tables with correct Character Set and Collation in WordPress

Sometime ago I wrote a blog post about how to properly create custom tables in WordPress that are also compatible with WordPress Multisite.

One thing that I didn’t mention in that article is setting the correct character set and collation for those custom tables.

What are character set and collation?

From MySQL docs:

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

You will be specifying the character set and collation as part of the CREATE TABLE sql query.

What values should be used for character set and collation?

WordPress has a complex logic to figure out the correct character set and collation for each instance and provides a way to override them in wp-config.php file.

WordPress also does a lot of security checks and rejects text-based on the character set and collation of the tables. So if you are creating custom tables in WordPress then instead of hard coding the character set and collation (or leaving it blank) you should use the one that WordPress had determined.

You can use the get_charset_collate() function in wpdb class to get this value. Append the value returned by this function at the end of your CREATE TABLE sql.

Posted in WordPress | Tagged , | Leave a comment

Developing Android applications in Java – Session 3 – My notes

This week in the Android course, Tony taught about the storing and retrieving information from database. Android has a bundled SQLite database and your app can store and retrieve information by creating a new database. The database that is created by an application is available only to that application and no other application can access it.

SQLiteOpenHelper

Android SDK provides a class called SQLiteOpenHelper which can be used for interfacing with this SQLite database that is associated with your application.

SQLiteOpenHelper has two methods which can be used for creating/updating the database. They are the following.

onCreate

The onCreate() method gets called when the app gets installed for the first time. The SQL code to create the database should go in this method. In addition to the SQL code we should also specify a version number for the database which will be used subsequently during upgrades.

onUpgrade

The onUpgrade() method gets called when the app is upgraded or if the version number specified in the app is greater than the one which is present in the database. Typically this function contains Alter table SQL code which will be used to upgrade the database.

In addition to the above two methods, the SQLiteOpenHelper also has other methods which can be used to access the database. One such method is getWritableDatabase()

getWritableDatabase

The getWritableDatabase() method will return a SQLiteDatabase object which has reference to the database. You can read more about this method from android documentation.

In addition to these methods, the SQLiteOpenHelper class other methods but the above there are the notable ones. You can read more about the SQLiteOpenHelper class from the android documentation.

Selecting data from the database

To selected data from the database, we have to call the query() method on the SQLiteDatabase object which is returned by the getWritableDatabase() method above.

The query() method returns an object of type Cursor, which can be iterated over to retrieve the resultset. The following code snippet explains the query() method.

private void loadTasks() {
	currentTasks = new ArrayList<Task>();
	Cursor tasksCursor = database.query(TASKS_TABLE,
			new String[] {TASK_ID, TASK_NAME, TASK_COMPLETE},
			null, null, null, null, String.format("%s,%s", TASK_COMPLETE, TASK_NAME));

	tasksCursor.moveToFirst();
	Task t;
	if (! tasksCursor.isAfterLast()) {
		do {
			int id = tasksCursor.getInt(0);
			String name = tasksCursor.getString(1);
			String boolValue = tasksCursor.getString(2);
			boolean complete = Boolean.parseBoolean(boolValue);
			t = new Task(name);
			t.setId(id);
			t.setComplete(complete);
			currentTasks.add(t);
		} while(tasksCursor.moveToNext());
	}
}

Inserting data into the database

In order to insert the data into the database we have to call the insert() method of the SQLiteDatabase. The data that needs to be inserted should be added to a ContextValues object and then passed to the insert() method. The ContextValues object is like a HashMap which contains the key and the value for each column of the row that will be inserted.

The insert() method returns the id of the row that was inserted. The following code snippet explains the insert() method.

public void addTask(Task t) {
	ContentValues values = new ContentValues();
	values.put(TASK_NAME, t.getName());
	values.put(TASK_COMPLETE, Boolean.toString(t.isComplete()));

	t.setId(database.insert(TASKS_TABLE, null, values));
	currentTasks.add(t);
}

Updating data in the database

To update data in the database we have to call the update() method of the SQLiteDatabase object. Like the insert() method, the data that needs to be updated should be passed in a ContextValues object.

The following code snippet explains the update() method.

public void saveTask(Task t) {

	ContentValues values = new ContentValues();
	values.put(TASK_NAME, t.getName());
	values.put(TASK_COMPLETE, Boolean.toString(t.isComplete()));

	long id = t.getId();
	String where = String.format("%s = %d", TASK_ID, id);
	database.update(TASKS_TABLE, values, where, null);
}

Deleting data from the database

To delete data from the database we have to call (guess what 😉 ) the delete() method of the SQLiteDatabase objet. The delete() method takes the where condition based on which the rows will be deleted.

public void deleteTasks(Long[] ids) {
	StringBuffer idList = new StringBuffer();
	for (int i =0; i< ids.length; i++) {
		idList.append(ids[i]);
		if (i < ids.length -1 ) {
			idList.append(",");
		}
	}

	String where = String.format("%s in (%s)", TASK_ID, idList);
	database.delete(TASKS_TABLE, where, null);
}

You can read more about this method from android documentation.

Demo App Sourcecode

The demo TaskManager app that we have been using in the previous classes was modified to store the tasks in the database. You can find the source code from my github page. I am also working in this week’s homework and will be posting the explanation and source code once I am done.

You can also subscribe to my blog’s RSS feed or follow me in Twitter to receive updates about my notes for the next sessions.

Posted in Android/Java | Tagged , , , , | 7 Comments