In getting ready to update Horse Crazy!, the “most reviled app in the world”, I needed to see if a column existed in the database. I want people to be able to name their horses and so I needed a column named ‘name” in the horses table.
While I could just copy in the new database with all needed columns, that would blow away any changes people had made when they upgrade, so the proper way to do things is to check for the existence of the column and if it’s not there, use ALTER TABLE to create it.
Sidebar: It turns out I had already included the column I needed in an earlier update to the database but I didn’t know that until I had the following routine working. D’oh! Of course, I’ll need the routine from here on out, so it’s all good.
While dealing with databases in Corona SDK isn’t as straightforward as I’d like, getting the info I needed from the DB wasn’t as hard as I thought it might be. First, here’s the code:
function dbColumnExists(dbase, tbl, col) local sql = [[select * from ]] .. tbl .. [[ limit 1;]] local stmt = dbase:prepare(sql) local tb = stmt:get_names() local found = false for v = 1, stmt:columns() do if tb[v] == col then found = true end end return found end
SQLite has these things called prepared statements and while I could have done a lot of research into exactly what those are and what all you can do with them, my programming career only requires that I know enough to get my tasks done.
Sidebar: Knowledge just for the sake of knowledge is not a bad thing — but it rarely matches up with deadlines. I’ll dig back into prepared statements later when I’m relaxing…
To use the prepared statement we create a SQL statement that pulls a single record from the table in question and instead of using db:exec() like with most queries, we use db:prepare(). That returns a stmt (statement) object that can be used for many different operations. For example:
- stmt:columns()- Returns the number of columns in the result set.
- stmt:get_name(n)- Returns the name of column n in the result set.
- stmt:get_type(n)- Returns the type of column n in the result set.
- stmt:rows()- Returns an function that iterates over the values of the result set.
- stmt:get_names() – Returns an array with the names of all columns in the result set.
Our routine uses the first and last examples.
In local tb = stmt:get_names() we’re filling the table tb with the names of all the columns in the specified table. Then we loop over all the columns (from 1 to stmt:columns()) and see if the column we’re looking for is there. If so, we set the previously defined variable of found to true.
Using the routine is easy — pass in the database (you will have already connected to it using something like local db = sqlite3.open( dbFile )), the name of the table, and name of the column you’re looking for.
It returns true or false letting you know whether the column exists or whether you need to go ahead and create it.
While SQLite database stuff in Lua/Corona SDK isn’t as “easy” as a lot of things, it’s not insurmountable and the cool thing is you only have to write it one time…
…get a library routine of database functions written and debugged and just use them from here on out in all your projects.