[Home] [Downloads] [Search] [Help/forum]

Gammon Forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  Lua
. . -> [Subject]  How to check if a table exists (Lua + SQLite)?
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?

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

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page


Posted by VBMeireles   Brazil  (47 posts)  [Biography] bio
Date Sat 26 Mar 2016 11:07 PM (UTC)

Amended on Sat 26 Mar 2016 11:36 PM (UTC) by VBMeireles

Message
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?

Vinícius
[Go to top] top

Posted by Fiendish   USA  (1,641 posts)  [Biography] bio   Global Moderator
Date Reply #1 on Sat 26 Mar 2016 11:50 PM (UTC)

Amended on Sun 27 Mar 2016 12:38 AM (UTC) by Fiendish

Message

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

https://github.com/fiendish/aardwolfclientpackage
[Go to top] top

Posted by VBMeireles   Brazil  (47 posts)  [Biography] bio
Date Reply #2 on Sat 26 Mar 2016 11:59 PM (UTC)
Message
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?

Vinícius
[Go to top] top

Posted by Fiendish   USA  (1,641 posts)  [Biography] bio   Global Moderator
Date Reply #3 on Sun 27 Mar 2016 12:57 AM (UTC)

Amended on Sun 27 Mar 2016 12:58 AM (UTC) by Fiendish

Message
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.

https://github.com/fiendish/aardwolfclientpackage
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #4 on Sun 27 Mar 2016 02:54 AM (UTC)
Message
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

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


2,806 views.

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page

Go to topic:           Search the forum


[Go to top] top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at FutureQuest]