Mudlet 2.1 Database Problem

Post Reply
maxtech
Posts: 3
Joined: Sat Mar 26, 2011 3:27 pm

Mudlet 2.1 Database Problem

Post by maxtech »

I'm having a bit of difficulty handling a database using the provided functions in Mudlet 2.1. I got most of the functions working with the DBLua script posted to this forum, but I still seem to have a bug.

As I understand it , the following 2 lines should replace an row with the new one if both x and y are the same.

_unique = { {"x", "y"} },
_violations = "REPLACE"

Unfortunately it seems to replace rows if they share either an x or y value. Have I made a mistake with the syntax, or is there another way to specify a compound unique index?

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

I don't think you've made a mistake, that looks legal. I'll check the SQL DB is generating and whenever it is right. The joys of inheriting maintainership! Exciting to find out what's not working.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

The short story is that even though the documentation claims to support it, compound keys aren't supported. Code even has this comment: "It is not presently (and likely never will be) supported to define the primary key of any sheet.".

At the moment, specifying several unique keys in the { {"x", "y"} } format just makes them have an OR relationship, not AND as advertised - so essentially the same as { "x", "y" }.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

I've taken the mentions of compound _unique specifications out of the manual. DB wasn't designed to work without _row_id as a primary key, and adding support for that would require some careful restructuring.

maxtech
Posts: 3
Joined: Sat Mar 26, 2011 3:27 pm

Re: Mudlet 2.1 Database Problem

Post by maxtech »

Thanks for looking into that. In case anyone has a similar problem, I ended up creating a single key based on my two values and used that as a unique index.

uniqueKey = (x * 10000) + y
...
_unique = {uniqueKey},

Not super elegant or efficient, but it works.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

So, if you wanted to have a key of that sort, you would basically just not have any unique keys in the table, and handle the uniqueness yourself, yes?

So, something like this?
Code: [show] | [select all] lua
db:delete(mydb.sheet,db:AND(db:eq(mydb.sheet.x = "x_val"), db:eq(mydb.sheet.y = "y_val")))
db:add(mydb.sheet,{x = "x_val", y = "y_val", stuff = "new stuff"}
Basically, deleting any current data that matches the values you want to be unique, anded together so as to require both to be the value you are looking for, and then write in the new data you have.

Or, at least in his case, it looks like you could just create a string value and make that unique (instead of relying on the math to play nice). Using his example, you could just have x .. ":" .. y put into a coordinates field so that even if y gets really big, you don't have to worry about messing with uniqueness by overwriting the portion with the x. And, if you wanted to expand to three dimension, the extension is more intuitive, rather than coming up with some new way to add it in.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

So, I think I grabbed the most recent version of DB.lua and replaced the old one. Then I started experimenting with a database to see how to make it work. This is the example script I made just to test things out. It seems to me that the unique setting is not being used at all. Am I doing something wrong? Also, the way I had to put the fields in the db:fetch call for sorting was really weird.
Code: [show] | [select all] lua
local mydb = db:create("dslpnp_data",
	{
		people = {
			name = "",
			race = "",
			class = "",
			level = 0,
			org = "",
			org_type = "",
			status = "",
			keyword = "",
			last_seen = db:Timestamp("CURRENT_TIMESTAMP"),
			_index = {"name"},
			_unique = {"keyword"},
			_violations = "REPLACE"
		}	})

function testing()
	-- delete old versions so the database doesn't get bloated with junk
	db:delete(mydb.people, db:eq(mydb.people.keyword,"bob"))
	db:delete(mydb.people, db:eq(mydb.people.keyword,"george"))
	db:delete(mydb.people, db:eq(mydb.people.keyword,"frank"))
	-- add in "bob" and "George"
	db:add(mydb.people,
		{name="bob",level=1,class="warrior",race = "elf",keyword = "bob"},
		{name="George",level=10,class="thief",race="dwarf",keyword = "george"})
	-- add in "Bob", "bob", and "Frank". Since keyword should be unique, the previous "bob" and "Bob" should be replaced by the last "bob"
	db:add(mydb.people,
		{name="Bob",level=12,class="mage",race = "elf",keyword = "bob"},
		{name="bob",level=15,class="warrior",race = "human", keyword = "bob"},
		{name="Frank",level=31,class="cleric",race = "ogre", keyword = "frank"})
	-- note the really weird structure needed for sorting {"level","name"} didn't work
	local results = db:fetch(mydb.people,nil,{{name="level"},{name="name"}})
	for k,v in pairs(results) do
		display(v.name .. " : " .. v.race .. " : " .. v.class .. " : " .. v.level .. " : " .. v.keyword)
	end
	-- displayed results indicate that UNIQUE property not obeyed
end

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

I'll check it out, but the unique field is definitely getting applied right on the SQLite level. You can also see the SQL DB generates by doing db.debug_sql = true.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

I also tried using db:update, also with bad results. Using the stuff above, I put in this to see what would happen:
Code: [show] | [select all] lua
results = db:fetch(mydb.people, db:eq(mydb.people.keyword,"bob"))[1]
results.level = 51
display(results)
db:update(mydb.people,results)
And I got this error: <./mudlet-lua/lua/DB.lua:138: attempt to index local 'm' (a nil value)>
It seems to be in the datetime:parse function. Not really sure what is up with that though. I'll take out the timestamp and see if update works then.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

I'll look at the timestamp function as well. I've never used that feature myself and I don't think anyone else did either, so there's a good chance it could be broken. Thanks for the heads up.

Post Reply