Archive for January, 2013

SQLite: How to Determine if a Column Exists in a Table

January 20th, 2013

As part of developing a new application using the SQLite database I need to perform some standard database checks when the application starts. Firstly I need to check that the application’s database exists and then either create the database if it doesn’t exist, or, if the database does exist, check that the tables in the SQLite database contain the columns expected by the app.

Checking whether the Database Exists

Checking that the SQLite database exists is easy, I can simply use code like:

if (!File.Exists(mPathName))

i.e. I can use the normal System.IO methods in .Net to check whether the database is present.

Creating a SQLite Database

Creating a SQLite database is also straightforward in SQLite. I can create the database file as follows:

// Create the database file.

// Open a connection to the database.
using (SQLiteConnection Conn = new SQLiteConnection("Data Source = " + mPathName))

    // Create the required tables. In this example, I'm creating a Customers table with 3 fields - ID, UNIQUEID and DATAFIELD.

    using (SQLiteCommand Command = new SQLiteCommand(SQL, Conn))


This is all pretty standard SQL and ADO.Net.

Checking if Columns Exist in the SQLite Database

Checking whether columns exist was a little harder to work out for SQLite. Some other databases I’ve used has special methods to get the tables, columns and their attributes. SQLite didn’t have a class to enable me to do this quite so obviously but after some research and experimentation, I found the following code worked:

// Open a connection to the database.
using (SQLiteConnection Conn = new SQLiteConnection("Data Source = " + mPathName))

    // Get the schema for the columns in the database.
    DataTable ColsTable = Conn.GetSchema("Columns");

    // Query the columns schema using SQL statements to work out if the required columns exist.
    bool IDExists       = ColsTable.Select("COLUMN_NAME='ID' AND TABLE_NAME='Customers'").Length != 0;
    bool UNIQUEIDExists = ColsTable.Select("COLUMN_NAME='UNIQUEID' AND TABLE_NAME='Customers'").Length != 0;
    bool ElephantExists = ColsTable.Select("COLUMN_NAME='ELEPHANT' AND TABLE_NAME='Customers'").Length != 0;


A statement like

ColsTable.Select("COLUMN_NAME='ID' AND TABLE_NAME='Customers'")

returns an array of DataRows. If the column doesn’t exist an array of length 0 will be returned, hence the .Length != 0 check.

Adding a Column if it doesn’t Exist in the SQLite Database

Adding a column to a SQLite database can be performed using standard SQL statements.


using (SQLiteCommand Command = new SQLiteCommand(SQL, Conn))

It turns out that performing these basic operations on a SQLite database is pretty straightforward when you know how. One thing I haven’t bothered to find out yet is to determine whether a column has the correct attributes, e.g. my example ELEPHANT column may change from a 100 character to a 200 character wide column between different versions of the application. However, I’ve never had a good reason for doing such a database update in the past. I’m also relying on the SQLite feature where the database will store any sort of data of almost any length in any column.

For more SQLite tips, see:

Human-Centered Design – An Introduction to Designing for Ease of Use

January 18th, 2013

Designing products that are easy to use is not a simple exercise. After a number of years spent designing a range of PC and industrial products, my experience is that those products which appear the simplest, cleanest and easiest to use are, more often than not, the ones that took the most amount of effort to design and complete. Constructing a product that simply exposes every possible feature at once to the user is relatively easy. What’s more difficult is prioritising the user interface, making the most frequent and essential elements readily available while pushing the more advanced, less frequently used features into the background.


One of the biggest obstacles to overcome when beginning to design anything that is easy to use is the designer’s ego. Almost all designers, whatever their field, e.g.  software, mechanical, usually hold some sort of innate belief about what the user wants and what the best solution should be. This is a natural and essential requirement for any designer. Without the self-belief and initiative to construct a solution a designer would not be able to form an innovative solution for a user need. However, in any mature development process, the ultimate design of a product should not be allowed to rely on what may turn out to be a prejudice, personal desire or pet theory of the loudest or most assertive engineer (or manager) on the team.

One process that can be adopted in any development program is Human-Centred or User-Centred Design. For a process that has sounds rather complicated, it’s actually based on some very simple and low-tech ideas. To describe HCD, let’s break the development process into 3 distinct stages:-

1. Understand the user’s requirements for the product.

In particular try to gain an understanding of the user’s pain point or difficulties.

2. Innovate and design a solution for the user’s product requirements.

3. Test and evaluate the solution with users.

Take the lessons learned and go back to stage 1.

Repeat steps 1, 2 and 3 as required. Any development is a compromise and engineering judgement needs to be used to decide when the improvements are no longer worth the effort of repeating the cycle. HCD techniques come into play mainly in stages 2 and 3 giving the solution developers a set of techniques for presenting and evaluating solutions with users as well as techniques for prioritising evaluation results.

I opened this post by saying that designing for ease of use is not a simple exercise. Although there are only 3 steps listed above they all involve a lot of work, usually over a significant period of time. Adopting HCD techniques can help you increase the chances of a successful development. I’ll elaborate on the HCD techniques in a follow-up post.

Apple Mac: How to Take a Screenshot

January 13th, 2013

One nifty feature I’ve found with my new Apple Mac Mini is that taking screenshots is real easy. If you want to capture an image of a portion of the desktop, simply type the key sequence:


i.e. press down both the cmd key and the shift key then press the 4 key at the same time.

The Mac then displays cursors that you can drag with the mouse to grab any portion of the screen you like – press the left mouse button to begin capture then move the mouse to encompass the desired are before releasing the left mouse button to complete the capture. The image is then automatically saved as a PNG file on the desktop.

After the image is saved onto the desktop, double-clicking the image with the mouse displays the image in the Preview tool. The Preview tool contains a number of handy features, e.g. the Tools menu lets you resize the image and the File menu contains options to export the image in different formats, e.g. as a .JPG file.

Apple Mac: Enabling the Mouse Right-Click

January 13th, 2013

Having recently bought an Apple Mac, I’d begun working my way through tutorials on how to use XCode, Apple’s IDE for iPhone/ iPad development. Coming from a PC, I had a learning curve on how to use some of the basic Mac features. I’m not saying the Mac is better or worse than a PC, just different. It’s always good to learn something new!

One thing that did confuse me for a while was that the tutorials I was following required me to right-click the mouse on certain items to bring up a context menu. Unfortunately this simply didn’t work, I clicked on the right side of the mouse but no menu appeared. My confusion was increased as the mouse I’d bought (for over £30!) didn’t appear to have separate left and right buttons. Eventually I figured out that the mouse was configurable and that I had to enable use of the right mouse button feature as it wasn’t enabled by default. To enable the right mouse, I had to open the System Preferences by clicking on this button:

Apple-Mac-System-Preferencesthen navigate to the Mouse preferences under the Hardware section in the preferences. Finally I had to enable the Secondary button for the right mouse button as shown in this picture:


After doing this, I could right-click the mouse and bring up the context menu, just like on a PC.