Look for Existing Columns in a SQLite Database Table

[Updated Jul 30, 2020]

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 Solar2D 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

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

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/Solar2D 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 to…

…get a library routine of database functions written and debugged and just use them from here on out in all your projects.

Previously published referencing Corona SDK, the old name of Solar2D.

database, sqlite

3 comments on “Look for Existing Columns in a SQLite Database Table”

Mario says:

January 5, 2012 at 5:35 am

Epic! I always love useful hints on SQLite. I only know JUST enough to be dangerous with it, so the more help the better!! 🙂

Thanks for the tips!

-Mario

MarkRosewood says:

January 7, 2012 at 11:21 am

Seems you don’t need to use the prepare stmt (unless you want it for other reasons). The db:exec can include a callback function which has access to a lua table containing the column names (and other stuff) so just to get column names you could do this:

function printColumns(udata, cols, values, names)
for i, v in pairs(names) do
print(v) — print column name
end
end
db:exec(“SELECT * FROM myTable”, printColumns)

The ‘other stuff’ are..
udata – whatever you add as an optional 3rd parm to the exec after function
cols – number of columns in row
values – a table containing data items in the first returned row from the SELECT

– see LuaSQLite 3 docs db:exec for full info.

- Jay says:
  
  February 17, 2012 at 3:31 pm
  
  That’s good to know, Mark. No, I didn’t have any other reason to use the prepare stmt — it was simply the way I discovered first. – Jay