How to check if a table exists (Lua + SQLite)?

Posted by VBMeireles on Sat 26 Mar 2016 11:07 PM — 5 posts, 25,324 views.

VBMeireles Brazil Sat 26 Mar 2016 11:07 PM #0

I found this:

http://stackoverflow.com/questions/1601151/how-do-i-check-in-sqlite-whether-a-table-exists

However I simply don't know what to do with it. :P If I DatabaseExec() that and assign the result to a variable, it returns 0 (which means OK?).

I know that the file is empty (there are no tables in it) because it was created through DatabaseOpen() and nothing else was done, however how is that DatabaseExec() telling me anything?

What I want to do is to code all the database manipulation in very safe ways in order to prevent errors. I want my script to check to see if a table exists and, if it doesn't, create it etc before any attempt to retrieve, insert or modify data.

As a side question, can somebody point me to the best/most up to date "tutorial" on MUSHclient Lua SQLite?

Fiendish USA Global Moderator Sat 26 Mar 2016 11:50 PM #1


local table_found = false
for row in db:nrows("SELECT name FROM sqlite_master WHERE type='table' AND name='YOUR_TABLE_NAME_GOES_HERE';") do
  table_found = true
end

But for what you want to do, you can also use


create table if not exists ...

VBMeireles Brazil Sat 26 Mar 2016 11:59 PM #2

I hope I'm not asking too much but would you care to explain what each part of that means? :P

Where did db_bm:nrows() come from?

Also can you point me to a good source of MUSHclient Lua SQLite beginner information?

Fiendish USA Global Moderator Sun 27 Mar 2016 12:57 AM #3

VBMeireles said:

Where did db_bm:nrows() come from?

I actually meant that to be db:nrows(). I've amended the post to reflect that.

The answer to where it comes from is two-fold:
1) DatabaseExec documentation says "DatabaseExec is not suitable for executing SQL code that queries the database for data (like a SELECT statement), as there is no provision for getting the results back. In that situation use DatabasePrepare / DatabaseStep / DatabaseFinalize."
2) db:nrows is a Lua-ish replacement for that DatabasePrepare/DatabaseStep/DatabaseFinalize process using the LuaSQLite3 interface instead of MUSHclient's other interface.

It's documented here:
http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#db_nrows
and
http://www.mushclient.com/scripts/doc.php?lua=db:nrows

What is "db", you ask? db is your opened database accessor created using


db = sqlite3.open("NAME_OF_YOUR_DATABASE_FILE")

which is described at http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#sqlite3_open

Quote:
Also can you point me to a good source of MUSHclient Lua SQLite beginner information?

No, because that's the wrong way to think about it.
There's nothing special about SQLite in MUSHclient. There's only SQLite statements and then accessing those statements through the Lua interface.

Just use the official LuaSQLite documentation at
http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki

There, under the section called "Database methods" you'll see the following statement:

Quote:
After opening a database with sqlite3.open() or sqlite3.open_memory() the returned database object should be used for all further method calls in connection with that database. An open database object supports the following methods.

The methods you will be most interested in are db:exec for creating data with INSERT statements and db:nrows for querying data with SELECT statements.

Nick Gammon Australia Forum Administrator Sun 27 Mar 2016 02:54 AM #4

VBMeireles said:

Also can you point me to a good source of MUSHclient Lua SQLite beginner information?

http://gammon.com.au/db

http://gammon.com.au/sql