Duplicate Exits in DB

[fiendish]

From musidan@gmail.com on December 24, 2010 09:40:23

The exits table should never contain more than one exit in a particular direction from the same room. Not sure how, but mine contained 911 fromuid-dir pairs that were inserted multiple times...

I wrote this function to detect those pairs and then updated it to delete the newer version of that exit. Seems like it would be a good idea to add a UNIQUE(fromuid, dir) to the exits table...

-- Spartacus - 12/24/2010
function map_dupe_exits (name, line, wildcards)
-- I want to know about duplicate exits in my map db (exits table)
-- select count(exitid) as exit_count, fromuid || dir as room_dir as from exits group by room_dir
local query = "select fromuid, count(exitid) as exit_count, fromuid || '-' || dir as room_dir from exits group by room_dir"
local line
local room_uid
local room_dir
local count = 0
local oldexit
print ("Scanning the exits table for dupes:")
for row in db:nrows(query) do
if row.exit_count > 1 then
room_uid = string.gsub(row.room_dir, "%-(%w+)", "")
room_dir = string.gsub(row.room_dir, "(%d+)%-", "")
line = string.format("There are %s exits from %s in direction %s", row.exit_count, row.fromuid, room_dir)
print(line)
query = string.format("select * from exits where fromuid = %s and dir = %s",fixsql(room_uid), fixsql(room_dir))
oldexit = nil
for subrow in db:nrows(query) do
line = string.format(" exit %s leads to room %s", subrow.exitid, subrow.touid)
if oldexit ~= nil then
if subrow.touid == oldexit.touid then
-- these exits are identical (from, direction, to), delete the new one
line = line .. string.format(" DELETING exit %s", subrow.exitid)
dbcheck(db:execute(string.format ("DELETE FROM exits where exitid = %s", fixsql(subrow.exitid))))
end -- if subrow
end -- if oldexit
print(line)
oldexit = subrow
end -- dupe details query
count = count + 1
end -- if exit_count
end -- room direction exits count query
line = string.format("Exits table contains %s room-direction pairs that have duplicate entries", count)
print(line)
end -- map_dupe_exits

Original issue: http://code.google.com/p/aardwolfclientpackage/issues/detail?id=34

[fiendish]

From musidan@gmail.com on December 24, 2010 06:54:42

You might feel uncomfortable letting my code delete rows from your db - understandably so... If you are, just comment everything from "if oldexit ~= nil" down to "end -- if oldexit"

[fiendish]

From Fiendi...@gmail.com on December 26, 2010 23:55:28

I think this should be fixed in r258 . Please verify.

Status: Fixed
Labels: -Type-Enhancement Type-Defect