A simpler DB container.

[tsuujin]

Updated: 1/29/11
For those of us who want more control over the actual SQL statements.

Code: [show] | [select all] lua

--[[ dba.lua
    Handles luasql in a generic way.
    Maintains a connection to the DB, and returns result sets independantly.
--]]

require "luasql.sqlite3"
require "php"

module("dba",package.seeall)

local loader_game = "<your profile name!>"
local dbFile = string.upper(loader_game)..".db"

local env = assert(luasql.sqlite3(),"Failed to load luasql.sqlite3")
local db = env:connect(string.format("%s/%s",getMudletHomeDir(),dbFile))
local transaction = false

--[[
    Function: dba.execute
    Parameters:
        sql: The actual SQL statement to be executed.
    Returns:
        Nothing.
    Description:
        Simply executes a given statement. A failure will display the statement unable to be executed.
--]]
function execute(sql)
    return assert(db:execute(sql),string.format("Failed to execute sql: %s",sql))
end

--[[
    Function: dba.query
    Parameters:
        sql: The actual SQL statement to be executed.
    Returns:
        A php:Table containing all of the results from the query.
    Description:
        Execute an SQL statement that returns results. the php:Table function is required
        in order to maintain the ordering of the results.
--]]
function query(sql)
    local cur = assert(db:execute(sql),string.format("Failed to execute sql: %s",sql))
    local results = php:Table()
    row = cur:fetch({},"a")
    local c = 1
    while row do
        results[c] = {}
        for col,value in pairs(row) do
            results[c][col] = value
        end
        row = cur:fetch(row,"a")
        c = c + 1
    end
    cur:close()
    return results
end

function begin()
    if transaction then return false end
    assert(db:execute("BEGIN TRANSACTION"),"Unable to begin transaction!")
    transaction = true
    return true
end

function commit()
    assert(db:execute("COMMIT TRANSACTION"),"Unable to commit transaction!")
    transaction = false
    return true
end

This is the php:Table function required (which is public code I've modified slightly):

Code: [show] | [select all] lua

-- Make a table that behaves like PHP tables, retaining the order elements are entered in.

if not php then php = {} end

function php:Table(...)
    local newTable,keys,values={},{},{}
    newTable.pairs=function(self) -- pairs iterator
        local count=0
        return function()
            count=count+1
            return keys[count],values[keys[count]]
        end
    end
    newTable.count=function(self) -- count return
        return table.getn(keys)
    end
    setmetatable(newTable,{
        __newindex=function(self,key,value)
            if not self[key] then table.insert(keys,key)
            elseif value==nil then -- Handle item delete
                local count=1
                while keys[count]~=key do count = count + 1 end
                table.remove(keys,count)
            end
            values[key]=value -- replace/create
        end,
        __index=function(self,key) return values[key] end
    })
    return newTable
end

Edit:
Changelog 1/29/11:
Fixed the way transactions are handled. Should be more reliable now.

[Vadi]

The above post has been updated.

(and with this post, thread will be marked as new!)

[Rakon]

Tsuujin:

I'm trying to use your database container to execute SQL statements on the db file. I have imported an sqlite3 'slain' database into the RAKON.db created by your script.

Anytime I try to execute ANY sql statement with your script it is returned as failed:

dba.execute("select * from slain where name like '%rat%'")

Code: Select all

Failed to execute sql: 'select * from slain where name like '%rat%''

So I tried creating my own table first. I deleted the RAKON.db, then restarted mudlet. The container file is recreated, however when I try to CREATE a table:

Code: Select all

Failed to execute sql: 'create table test (name TEXT)'

Even if I put a semi-colon on the end of the statements, I still get a failure.

I also tried opening the RAKON.db that your script created in an SQLlite browser, and it tells me it is not a correct SQL file, and refuses to open afterwards.

Might I get a bit of help or an example for getting your script and commands to work??

[tsuujin]

Seems like it's not actually making a connection to the DB. When you load the script, do you get any errors?

[Rakon]

Negative, I have the script loading on profile load:

Code: Select all

dofile( getMudletHomeDir() .. "/dba.lua" )

There is no error when I start mudlet, or even when load luasqlite manually.

Code: Select all

/require "luasql.sqlite3"

table {
  'sqlite3': function
  '_VERSION': 'LuaSQL 2.1.1'
  '_DESCRIPTION': 'LuaSQL is a simple interface from Lua to a DBMS'
  '_COPYRIGHT': 'Copyright (C) 2003-2007 Kepler Project'
}

[tsuujin]

Verify that the DB does in fact exist (it should be in your profile directory, under whatever you put in for loader_game).

Verify that the table exists properly. You can use an external program to load the DB and make absolutely certain that it's in there properly. I use sqliteman).

Also, verify that the php module is also being loaded. I have it as a require, but you may wish to remove that and simply put the code from that file above the module() line in the dba.lua file.

[Rakon]

All of these things have been tested and verified
The RAKON.db container exist, I can browse and execute queries against it just fine using external programming (Python) and SQl Browser. The tables are there and contain data. The PHP module is being loaded, I have the code for your modified PHP directly in my 'main' script file as well, I use the PHPtable with other functions just fine.

Everything is as it should be, except for your code snippet is refusing to execute the sql statement given.

[tsuujin]

Unfortunately, I haven't found a good way to get actual error messages, and just use assert to return generic failure reports.

From what I can see, by the way, you're using the wrong function to return data. You don't want to use execute, because it won't return any data, you want to use query.

Execute is for non-returning data, here's an example from my system:

Code: [show] | [select all] lua

    dba.execute([[
        CREATE TABLE IF NOT EXISTS player_relations (
            name TEXT,
            ally TEXT,
            enemy TEXT,
            CONSTRAINT pk PRIMARY KEY (name) ON CONFLICT REPLACE
        );
    ]])

Query is for getting data sets. Here's another example from my system:

Code: [show] | [select all] lua

    local results = dba.query([[SELECT * FROM player_relations]])
    for num,tbl in results:pairs() do
        for _,i in pairs(string.split(tbl.ally,":")) do add("ally",tbl.name,i) end
        for _,i in pairs(string.split(tbl.enemy,":")) do add("enemy",tbl.name,i) end
    end

As far as the error message you're getting, try eliminating possible problems. For example, create a brand new DB file and play with it only via mudlet, see if that makes a difference. It kind of seems like it's either a connection problem (less likely) or it's having trouble reading the file (perhaps the DB file itself was created with an external program?). All the execute function does is literally pass the SQL to the db environment, so there's not much room for failure in my script.

[Rakon]

Right, I've done that as well. That is, removed the database container and only used it via creation of Mudlet, and I've tried using query function for selecting data from the table and then displaying it.(When the database contained data) and the table that was returned was 'table {}', and an error of 'failed to execute sql'. This was using a 'select * from items where name ='rapier'' query, of which there where over 100k items of name 'rapier' in the database. This query worked correctly, and properly returned results using an external program to verify, afterwards.

The reasoning for me using the execute function, is because it SHOULD be at its simplest form. I have deleted the database file created with all my data, and restarted Mudlet. This recreates the RAKON.db via your script. When I attempt to use 'execute()' function to create a table, the same error is returned. So, I can't query data, I can't create a table, I can't access the database from mudlet.

I see in your examples that you're passing sql to the functions wrapped in [[]] tags. I was passing the sql to execute as a string, so when I get home I'll try again using the example you provided and let you know the outcome.

Thanks.

[tsuujin]

Using the brackets shouldn't really make a difference, as far as I know. In Lua, brackets are just a different way of creating a string, bypassing the need to escape quotation marks, and with the added advantage of being able to be used multi-line.

You could try creating a new DB environment and playing with that without using my script, just to verify that you are, in fact, able to do so.

Code: [show] | [select all] lua

require "luasql.sqlite3"
local env = assert(luasql.sqlite3(),"Failed to load luasql.sqlite3")
local db = env:connect(string.format("%s/%s",getMudletHomeDir(),"test.db"))
db:execute([[CREATE TABLE test_table (id TEXT)]])

then use an external program to see if the table has been created or not.