Blackboard Setup with SQLite

Posted by Mleo2003 on Wed 31 Aug 2022 02:15 AM — 3 posts, 16,886 views.

Mleo2003 Wed 31 Aug 2022 02:15 AM #0

I have been playing with the idea of a Blackboard system for my characters on a mud, and wanted some durable way to store data, and possibly share it with other chars. This is my attempt at somewhat simple interface for doing so.


-- BlackBoards for shared/persistant data
require "serialize"

bb = {}
DatabaseOpen("blackboard", GetInfo (66) .. "blackboard.db", 6)
DatabaseExec("blackboard", [[
CREATE TABLE IF NOT EXISTS "board" ("key" TEXT PRIMARY KEY, "character" TEXT, "value" BLOB);
]])
local mt = {
    __index = function (t,k)
        local t = {}
        local value = DatabaseGetField ("blackboard", string.format("SELECT value FROM board WHERE key = '%s' AND character = '%s';", k, GetInfo (3)))
        setfenv (assert (loadstring (value or "")), t) ()
        return t['value']
    end,
    
    __newindex = function (t,k,v)
        if v == nil then
            DatabaseExec ("blackboard", string.format("DELETE FROM board WHERE key = '%s' AND character = '%s';", k, GetInfo (3)))
        else
            local data = 'value = ' .. serialize.save_simple (v)
            local return_value = DatabaseExec ("blackboard", string.format("INSERT INTO board (key, character, value) VALUES ('%s', '%s', '%s');", k, GetInfo (3), data))
            if return_value ~= 0 and return_value ~= 100 and return_value ~= 101 then
                return_value = DatabaseExec ("blackboard", string.format("UPDATE board SET value = '%s' WHERE key = '%s' AND character = '%s';", data, k, GetInfo (3)))
            end
        end
    end
}

setmetatable(bb, mt)

You use it like any other table:

bb['hello'] = 'world'

And since it uses serialize for the saving/loading, it will remember types and do simple nested tables (could be updated to use more complex ones if needed).

The only problem I found with it was nested tables do not work as you expect them too:


bb['test'] = {name = 'Bart'}
print(bb['test']['name']) -- shows 'Bart'
bb['test']['name'] = 'Lisa'
print(bb['test']['name']) -- still shows 'Bart'

It makes sense why this happens, as what I return is a new table every time. I think if I used a hidden table to cache/record the DB results, and update those on some timer, it could work decently, but hackish.

Can anyone else think of any other improvements this could take on?

Nick Gammon Australia Forum Administrator Wed 31 Aug 2022 05:36 AM #1

I think that design is fundamentally flawed, if I can say so without giving offence. ;)

You are trying to make a simple database table with one key and one value handle two-dimensional arrays. That's never going to work.

Either set up a proper table, where each row has a major key (eg. an ascending number) and then other columns such as the message, the date, who wrote it, and so on. That gets rid of the need for two dimensions.

OR ...

Just serialize the table to disk and bypass the database altogether. There is a discussion of doing that here:

http://www.gammon.com.au/forum/?id=4960&reply=6#reply6

Mleo2003 Wed 31 Aug 2022 03:07 PM #2

No offence taken. :)

I was mainly inspired by projects like these:

https://pypi.org/project/diskcache/0.7.0/
https://github.com/RaRe-Technologies/sqlitedict

Which is more what I was wanting: a way to store data that looked like a table (as that is the main feature I wanted) while hiding the implementation of how it was actually stored. But, the issue I pointed out (of nested/2D tables) does lead to some confusion for anyone who may use it.

I could just as easily have those be global function "get and set" calls, and then it would be more obvious what would happen:

bb_get('test')['name'] = 'Lisa'

That would at least be more clear what to expect with the return.

My only other reason for wanting this, was that as a SQLite DB in a file, I could have multiple worlds open the file, and essentially have a shared space for data. I've tried passing data between worlds before, and while doable, this seemed to clean up all the assumptions around that to point externally nice enough, and get some durability of the data I was passing for free (as a lot of what I was sharing between worlds was still true/relevant from one run of Mushclient to the next).

Thanks for the input, for more structured/planned data to share/pass, I do intend on using more columns and all they give. This area was just to be for random things to share that were mainly single value in nature, but serialize gave me the ability to even store/share more complex things for free (but how I chose to make the interface can lead to confusion, I admit).