Mudlet 2.1 Database Problem

[Jor'Mox]

So, apparently my problem was saving in the middle of writing the DB constructor, and getting things wrong that I then later fixed. I had both _index and _unique equal to {"name"} at one point, and changing it didn't do anything until I deleted the database and restarted Mudlet (because it wouldn't recreate it after it was deleted while it was running). I also apparently can't add fields even through restarts so long as the database is the same (at least not with the db:create function).

However, the timestamp still creates problems. Update worked fine without a timestamp in the database, but when I put it back in, the error came back.

[Jor'Mox]

Specifically, I modified the db:create line to add in an extra field, I called it "testing". I then restarted Mudlet, and created a new row, with a new unique identifier, in the mydb.people sheet, fetched it, and displayed it... the new field "testing" was not present.

[Vadi]

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.

I get this when I run your code:

"George : dwarf : thief : 10 : george"

"bob : human : warrior : 15 : bob"

"Frank : ogre : cleric : 31 : frank"

Which looks correct?

[Jor'Mox]

Yeah, it is. Like I said, the issue was that the database had originally been constructed with both _index and _unique equal to the same column. Changing the values later didn't actually change the structure of the database, so it didn't work correctly. Once I deleted the database and restarted so that it was recreated, it worked just fine.

So, looking at my overall results, it seems that there is an issue with updating a database sheet after it has been created.

If you delete the old database, and change the db:create call to this, you will have the problem I was experiencing:

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 = {"name"},
			_violations = "REPLACE"
		}	})

Even after I fixed the mistake, and changed _unique back to "keyword" as originally intended, it continued to behave as if it hadn't changed.
After I sorted that out, I did this:

Code: [show] | [select all] lua

local mydb = db:create("dslpnp_data",
	{
		people = {
			name = "",
			race = "",
			class = "",
			level = 0,
			org = "",
			org_type = "",
			status = "",
			keyword = "",
			testing = "",
			last_seen = db:Timestamp("CURRENT_TIMESTAMP"),
			_index = {"name"},
			_unique = {"keyword"},
			_violations = "REPLACE"
		}	})

Not only did the previously entered rows not have the new field "testing", newly created rows didn't have it either.

[Vadi]

Also, the way I had to put the fields in the db:fetch call for sorting was really weird.

sorting {"level","name"} didn't work

The docs say you should provide the fields to sort by, so not {"level", "name"} but {mydb.people.level, mydb.people.name}.

There is a sorting problem however that I can into, trying to get SQLite to sort it in descending order (levels first then names in descending) flips the results, so both levels and names are sorted right... but in ascending order. This is the raw SQL data you can run in an sqlite3 console: http://pastebin.com/7rx2BgFN and the results that follow are:

Code: Select all

194		warrior	bob			elf	bob	2013-06-28 00:04:31		1		
195		thief	george			dwarf	George	2013-06-28 00:04:31		15		
200		warrior	Bobc			human	Bobc	2013-06-28 00:04:31		15		
199		warrior	Bobb			human	Bobb	2013-06-28 00:04:31		15		
198		warrior	Boba			human	Boba	2013-06-28 00:04:31		15		
197		warrior	Bob			human	Bob	2013-06-28 00:04:31		15		
201		cleric	Frank			ogre	Frank	2013-06-28 00:04:31		31		

I'm still trying to work out what is going wrong here.

[Vadi]

Using db:create to add new fields to a database (changing them was never supported) doesn't work, due to a bug in Lua-SQL (the Lua SQL bindings we're using) not being updated for an SQLite API change, and thus the table_info pragma not working. This means that db: can't ask sqlite for which tables currently exist and add new ones appropriately.

[Vadi]

I should have read the updates you posted before posting!

Yeah, adding new columns right now is broken, due to the aforementioned Lua-SQL bug (http://lua-users.org/lists/lua-l/2013-02/msg00252.html). We'll be able to patch Lua-SQL in Windows and Mac for the next releases, I think.

[Vadi]

I'm still trying to work out what is going wrong here.

Okay, solved it, you're supposed to pass the sorting flag for each field. That's a more flexible mechanism as well.

I'll fix db:fetch to apply the flag to all fields properly, and later on adjust the syntax to allow specifying the sorting parameter on each field.

[Jor'Mox]

You may well be right about how you are supposed to provide fields for sorting, but the examples and explanation in the wiki is what I was using for guidance, and it pretty clearly spelled out sorting using {"level","name"} as the method to use. Also, since the code that is used for determining sorting is this:

Code: [show] | [select all] lua

for _, v in ipairs(order_by) do
         assert(v.name, "You must pass field instances (as obtained from yourdb.yoursheet.yourfield) to sort.")
         o[#o+1] = v.name
      end

It should be able to be changed fairly easily to this to allow for either option:

Code: [show] | [select all] lua

for _, v in ipairs(order_by) do
         if type(v) == "table" then
            assert(v.name, "You must pass field instances (as obtained from yourdb.yoursheet.yourfield) to sort.")
            o[#o+1] = v.name
         else
            o[#o+1] = v
         end
      end

You obviously may want some sort of check to be sure that the field exists, but I would think that SQL will just spit out an error if there is a problem on that end, so it isn't catastrophic if you can't.

Well, apparently giving it a bogus column name just means it doesn't sort by that column. So, no real issues with a non-existent column.

[Vadi]

Which examples? http://wiki.mudlet.org/w/Manual:Scripting#Querying has no mention of this. They need to be fixed if so.

Bogus names aren't catastrophic, but silent errors are I'd prefer that it throw the error.

I've worked out the sorting problem, the latest DB.lua (please don't forget to remove this file when you update Mudlet) sorts this code properly:

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, true)
        -- add in "bob" and "George"
        db:add(mydb.people,
                {name="bob",level=1,class="warrior",race = "elf",keyword = "bob"},
                {name="George",level=15,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="Boba",level=15,class="warrior",race = "human", keyword = "Boba"},
                {name="Bobb",level=15,class="warrior",race = "human", keyword = "Bobb"},
                {name="Bobc",level=15,class="warrior",race = "human", keyword = "Bobc"},
                {name="Frank",level=31,class="cleric",race = "ogre", keyword = "Frank"})
        local results = db:fetch(mydb.people,nil,{mydb.people.level, mydb.people.name}, true)
        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