Mudlet 2.1 Database Problem

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

Re: Mudlet 2.1 Database Problem

Post by Vadi »

This change is in now. I'll look at colour echoing things next and come back to reviewing editing dates in this.

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

Re: Mudlet 2.1 Database Problem

Post by Vadi »

Jor'Mox wrote:Specifically, this happens. You create a new record. You retrieve that record using a fetch command, and all the data is there as expected. You then try to change any field, and update the record, and an error is generated because the Timestamp format is not recognized.
This has been fixed now, the following code updates the records without errors or breaking them:
Code: [show] | [select all] lua
results = db:fetch(mydb.eggs)
for _, row in ipairs(results) do
  echo("You found " .. row.color .. " at: " .. (row.last_found and row.last_found:as_string() or "no time") .."\n")
end

db:update(mydb.eggs, results[1])

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

Re: Mudlet 2.1 Database Problem

Post by Vadi »

Jor'Mox wrote:Then even when that problem is overcome, you have no way of automatically generating a new Timestamp for the record if desired, which leads to problems, since the most obvious solution is to replace the epoch value currently stored with one generated by your computer, which will be in local time. So if the record is retrieved again, and the change was made shortly after the initial record was created, it will actually show the record as having a Timestamp from before it was created.
This has been fixed as well. Test with the following example using the latest DB.lua as a script before the examples. Please make sure to remove your copy of DB.lua when you upgrade Mudlet - if you don't, you'll never get any DB-related upgrades via Mudlet updates (as your old copy overwrites).
Code: [show] | [select all] lua
mydb = db:create("egg database", {eggs = {color = "", last_found = db:Timestamp("CURRENT_TIMESTAMP"), found = 0}})
local result, msg = db:add(mydb.eggs,
  {color = "Red"},
  {color = "Blue"},
  {color = "Green"},
  {color = "Yellow"},
  {color = "Black"}
)

if not result then print("error with db:add: "..msg) end
Code: [show] | [select all] lua
results = db:fetch(mydb.eggs)
for _, row in ipairs(results) do
  echo("You found " .. row.color .. " at: " .. (row.last_found and row.last_found:as_string() or "no time") .."\n")
end

-- update timestamp to current
local row = results[1]
row.last_found._timestamp = os.time()
echo("The first rows, " .. row.color .. " was now found at: " .. (row.last_found and row.last_found:as_string() or "no time") .."\n")
db:update(mydb.eggs, results[1])
The example above will always set the time to the current time, showing that modification is working okay.

Setting the _timestamp field directly when the timestamp is supposed to be an object isn't the best, so I'll add a number of set methods for it to be nicer.

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

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

Once I'm not on my phone, I'll have to check out how you did it. I'm a curious kind of guy. ;-)

Question for you though, does this save the time stamps to the database as a string (matching the format used when they are created) or as epoch values? I know my "fix" just made it accept epoch values as valid values, but I would think that preserving the format would be nicer.

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

Re: Mudlet 2.1 Database Problem

Post by Vadi »

It still saves them in DATETIME format as it did. SQLite presents an option of using TIME, DATE or DATETIME for doing so.

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

Re: Mudlet 2.1 Database Problem

Post by Vadi »

Any luck in confirming the fixes?

Post Reply