Using SQLite3 in MUSHclient

Posted by Meerkat on Mon 22 Sep 2008 04:54 AM — 6 posts, 30,071 views.

Meerkat Mon 22 Sep 2008 04:54 AM #0

What I want is one of these:

http://nessie.de/mroth/lua-sqlite3/index.html

http://luaforge.net/frs/?group_id=75&release_id=796

Just SQLite3 or even SQLite2 functionality. It would really help me out. How would I go about installing it so MUSHclient would be compatible?

Nick Gammon Australia Forum Administrator Mon 22 Sep 2008 05:16 AM #1

See:

http://www.gammon.com.au/forum/?id=5983

Although that discusses mySQL, the concept is the same. You might need to change the loadlib to load a different DLL (and now it is package.loadlib instead of loadlib).

Meerkat Mon 22 Sep 2008 05:27 AM #2

It's just the following that I don't know how to do properly:

-- load the MySQL dll
loadlib ("mysql.dll", "luaopen_luasqlmysql") ()

And other related loading.

I have the sqlite3.dll from http://www.keplerproject.org/luasql/ in the same dir as MUSHclient. What is the second arg I pass to package.loadlib? I just can't figure it out.

I'm really new to Lua as well as MUSHclient, so I don't have experience with any of this. The documentation for LuaSQL is pretty lacking.

If you could figure it out for me, I would appreciate that very much.

EDIT: Also, is there anything I need to do in the Sandbox?

Meerkat Mon 22 Sep 2008 06:07 AM #3

I got a bit further.

Wiped out the whole sandbox.

Then I did this:

package.loadlib("sqlite3.dll", "luaopen_luasql_sqlite3")

Seemed to work. Not sure where to go from there though.

EDIT: Got it.

package.loadlib("sqlite3.dll", "luaopen_luasql_sqlite3")()

env = assert (luasql.sqlite3())

Nick Gammon Australia Forum Administrator Mon 22 Sep 2008 06:41 AM #4

You shouldn't need to wipe the entire sandbox. Maybe set it to permit DLLs to be loaded, and to trust the main world file. Anyway, with the sqlite3.dll placed in the same directory as MUSHclient, the following example worked for me:


assert (package.loadlib("sqlite3.dll", "luaopen_luasql_sqlite3")) ()

-- create environment object
env = assert (luasql.sqlite3())

-- connect to data source
con = assert (env:connect ("databasename.sqlite3", "username", "password"))


-- empty our table
res = con:execute"DROP TABLE players"

res = assert (con:execute[[
  CREATE TABLE players(
    name  varchar(50),
    class varchar(50)
  )
]])


-- add a few elements
list = {
  { name="Nick Gammon", class="mage", },
  { name="David Haley", class="warrior", },
  { name="Shadowfyr", class="priest", },
}

for i, p in pairs (list) do
  res = assert (con:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s')]], p.name, p.class)
  ))
end

-- retrieve a cursor
cur = assert (con:execute ("SELECT * from players" ))

-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")

while row do
  print ("\n------ new row ---------\n")
  table.foreach (row, print)
  
  -- reusing the table of results
  row = cur:fetch (row, "a")
end

-- close everything
cur:close()
con:close()
env:close()

Results were:


------ new row ---------

name Nick Gammon
class mage

------ new row ---------

name David Haley
class warrior

------ new row ---------

name Shadowfyr
class priest

This code created the database file "databasename.sqlite3" in the same directory that MUSHclient.exe was in.

WillFa USA Sat 13 Dec 2008 09:05 PM #5

The sqlite3.dll should be in lua\luasql\sqlite3.dll, just a few subdirs from where Mushclient.exe is located.

Here's the same example with a few minor tweaks.


require "luasql.sqlite3"  --much simpler

-- create environment object
env = assert (luasql.sqlite3())

-- connect to data source
con = assert (env:connect ("databasename.sqlite3")) -- sqlite doesn't implement usernames and pw's. not needed.


-- empty our table
res = con:execute"DROP TABLE players"

res = assert (con:execute[[
  CREATE TABLE players(
    name  varchar(50),
    class varchar(50)
  )
]])


-- add a few elements
list = {
  { name="Nick Gammon", class="mage", },
  { name="David Haley", class="warrior", },
  { name="Shadowfyr", class="priest", },
  { name="Spellbound", class="ranger"},  --Me too, Me too!
}

for i, p in pairs (list) do
  res = assert (con:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s')]], p.name, p.class)
  ))
end

-- retrieve a cursor
cur = assert (con:execute ("SELECT * from players" ))

-- print all rows, the rows will be indexed by field names
cur:fetch (row, "a") --redundant to do row = :fetch(row) though output parameters aren't typical to Lua.

while row do
  print ("\n------ new row ---------\n")
  for k,v in pairs(row) do print(k,v) end 
  
  -- reusing the table of results
  row = cur:fetch (row, "a")
end

-- close everything
cur:close()
con:close()
env:close()