Gammon Forum

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE muclient>

<muclient>
<plugin
   name="Zmud_Map_Database_Converter"
   author="Josh MacDaniel"
   id="cbc68333f85cb95232154fff"
   language="Lua"
   purpose="Converts a Zmud Map Database into a SQLite Database"
   save_state="y"
   date_written="2010-09-22 04:00:00"
   requires="4.50"
   version="1.0"
   >
<description trim="y">
<![CDATA[
Type "create map database" to run.

* Ensure you have set up a ODBC source named "Map" to your Zmud Map in your Control Panel/Administrative Tools/Data Sources (ODBC).
* Ensure you have the ODBC DLL (http://www.gammon.com.au/forum/bbshowpost.php?id=6324) downloaded and installed in your MUSHclient
Program directory.

If any of the above have not been done, the script WILL NOT work.

Source may need to be modified to fit your MUD.  Origionally written for WOTMUD (www.wotmud.org).

Code assumes Zmud did not have access/did not record UIDs from the mud for each room, so it builds one based on the room name, room
description, and room exits.  You will need to change the "ExitLine" variable to match your mud's output, or modify that portion of
the code to match the way your actual Mapper plugin creates UIDs for rooms.

Code also assumes you want to keep the Room Colors from Zmud Maps by saving them as Environments.  If this is not desired, you will
have to modify the code to fit your purposes (A peice of code is commented out that changes the "Info" or "Environment" of a room
depending on it's color.
]]>
</description>

</plugin>

<aliases>
  <alias
   script="create_database"
   match="create map database"
   enabled="y"
   sequence="100"
  >
  </alias>
</aliases>

<!--  Script  -->

<script>
<![CDATA[

count = 0
roomcount = 0
areacount = 0
exitcount = 0
environmentcount = 0

function process_area (args)
 
  if not args.id and args.name then
    return "Need id and name"
  end -- if
  
  areacount = areacount + 1
  
  SetStatus ("Processing area " .. areacount .. " (" .. args.id ..  " - " .. args.name .. ")")
  
  DatabaseExec ("mapdb", (string.format (
    "INSERT INTO areas (uid, name, date_added) VALUES (%s, %s, DATETIME('NOW'));",
      fixsql (args.id), 
      fixsql (args.name)
    )))
          
end --  process_area 


function  process_environment (args)
 
  if not args.id and args.name and args.color then
    return "Need id, name, color"
  end -- if
  
  environmentcount = environmentcount + 1
  
  SetStatus ("Processing environment " .. environmentcount .. " (" .. args.id ..  " - " .. args.name .. " - " .. args.color .. ")")
  DatabaseExec ("mapdb", (string.format (
    "INSERT INTO environments (uid, name, color, date_added) VALUES (%s, %s, %i, DATETIME('NOW'));",
      fixsql (args.id), 
      fixsql (args.name),
      args.color
    )))
               
end --  process_environment 
 

function process_room (room)
  roomcount = roomcount + 1
  SetStatus ("Processing room " .. roomcount .. " (" .. room.UID ..  " - " .. room.Name .. ")")
  local building = 0
  local x, y, z =  0, 0, 0
  
  DatabaseExec ("mapdb", (string.format (
    "INSERT INTO rooms (uid, name, description, area, terrain, building, info, notes, x, y, z, date_added) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, DATETIME('NOW'));",
      fixsql (room.UID), 
      fixsql (room.Name),
      fixsql (room.Description),
      fixsql (room.Area),
      fixsql (room.Terrain),
      fixsql (building),
      fixsql (room.Info),
      fixsql (room.Notes),
      fixsql (x),
      fixsql (y),
      fixsql (z)
    )))

  DatabaseExec ("mapdb", (string.format ([[
    INSERT INTO rooms_lookup (uid, name) VALUES (%s, %s);
  ]], fixsql  (room.UID),       
      fixsql  (room.Name) 
      )))
            
end -- process_room


function process_exit (args)
  exitcount = exitcount + 1
  
  SetStatus ("Processing Exit " .. exitcount .. " from " .. (args.fromUID or "") ..  " " .. args.dir .. " to " .. (args.toUID or ""))
  DatabaseExec ("mapdb", (string.format ([[
        INSERT INTO exits (dir, fromuid, touid, date_added) 
            VALUES (%s, %s, %s, DATETIME('NOW'));
      ]], fixsql  (args.dir),  -- direction (eg. "n")
          fixsql  (args.fromUID),         -- from current room
          fixsql  (args.toUID)    -- destination room
          )))
end -- process_exit


function fixsql (s)
  if s then
    return "'" .. (string.gsub (s, "'", "''")) .. "'" -- replace single quotes with two lots of single quotes
  else
    return "NULL"
  end -- if
end -- fixsql


function load_database()

  -- load the ODBC dll
  assert (package.loadlib ("odbc.dll", "luaopen_luasqlodbc")) ()
  -- create environment object
  env = assert (luasql.odbc())
  -- connect to data source
  con = assert (env:connect ("Map"))
               
  -------------------------GETTING ZONE UID AND NAMES--------------------               
  -- retrieve a cursor
  cur = assert (con:execute ("SELECT * from ZoneTbl" ))

  -- read first row, then start looping through rows while putting desired data into the areas table
  row = cur:fetch ({})

  local areas = {}
  while row do
    areas = {["id"] = row[1], ["name"] = row[2]}
    
    process_area(areas)
    
    -- read next row
    row = cur:fetch (row)
  end  -- while loop
  --Note(areas.id..": "..areas.name)  --Zone UID: Zone Name
  cur:close()    
  --------------------------END ZONE READING----------------------
  
  -------------------------GETTING ROOM INFO----------------------
  -- retrieve a cursor
  cur = assert (con:execute ("SELECT * from ObjectTbl" ))

  -- read first row, then start looping through rows while putting desired data into the rooms table
  row = cur:fetch ({})
  
  local rooms = {}
  local enviro = {}
  local environment = {}
  RoomUIDRef = {}
  

  while row do    
    --Get Room Exits to make UID Hash
    local ObjID = row[1]
    cur2 = assert (con:execute ("SELECT DirType from ExitTbl WHERE FROMID = " ..ObjID))
    local ExitID = cur2:fetch({})
    local ExitLine = "[ obvious exits: "                                                                                        --Modify to fit your mud
    local RoomExits = {}
    while ExitID do
      local ExitDirs = { [0] = "N", [1] = " NE", [2] = "E", [3] = "SE", [4] = "S", [5] = "SW", [6] = "W", [7] = "NW", [8] = "U", [9] = "D"}
      RoomExits[ExitID[1]] = ExitDirs[ExitID[1]]
      ExitID = cur2:fetch (ExitID)
    end --while loop
    local RoomDirs = {}
    for i = 0, 10 do
      if RoomExits[i] ~= nil then
        table.insert(RoomDirs, RoomExits[i])
      end
    end
    
    for k, v in ipairs (RoomDirs) do
      ExitLine = ExitLine..RoomDirs[k].." "
    end -- for
    ExitLine = ExitLine.."]"                              --Creates: [ obvious exits: N E S W U D ]                             --Modify to fit your mud
    cur2:close()  --closing ExitTbl
    
    --creating UID
    local ToHash = row[2]
    if row[5] then DescString = string.gsub (row[5], "\n", " ") else DescString = "" end
    ToHash = ToHash .. " " .. DescString .. " " .. ExitLine
    
    --Transfering Fields to Rooms Table
    rooms["UID"] = utils.tohex (utils.md5 (ToHash))
    rooms["Name"] = row[2]
    rooms["Description"] = row[5]
    rooms["Area"] = row[32]
    rooms["Notes"] = (row[3] or "") .. " " .. (row[4] or "")
  
     --Maintaining Colors from Zmud Database by converting them to Environments
    local color = row[19]
    if enviro[color] == nil then
      environment["id"] = environmentcount + 1
      environment["name"] = "Environment " .. tostring(environmentcount + 1)
      environment["color"] = color
      enviro[color] = environment["id"]
      rooms["Terrain"] = environmentcount + 1
      process_environment(environment)
    else
      rooms["Terrain"] = enviro[color]
    end

    rooms["Info"] = ""
 
 --[[  --Code specific to WOTMUD's conversion.
    --Determining and Adding Terrain to Rooms Table
    if row[19] == 10485760 or row[19] == 32896 or row[19] == 8421376 or row[19] == 8421504 or row[19] == 32768 then
      local terr = {[10485760] = "deepwater", [32896] = "road", [8421376] = "saplings", [8421504] = "indoor", [32768] = "trees"}
      rooms["Terrain"] = terr[row[19]]
 --[[   elseif row[19] == 0 and row[18] == 2147483647 then
      rooms["Terrain"] = "deathtrap"
    else
      rooms["Terrain"] = ""
    end 
    
    --Determining and Adding Info to Rooms Table
    if row[19] == 16776960 or row[19] ==  65535 or row[19] == 65280 or row[19] == 16711935 or row[19] == 255 or 
       row[19] == 33023 or row[19] == 16711680 or row[19] == 8388863 or row[19] == 16744576 or row[19] == 8421631 or
       row[19] == 8388736 or row[19] == 4227200 or row[19] == 210816 then
      local info = {[6776960] = "stables", [65535] = "armory", [65280] = "rent", [16711935] = "food", [255] = "dangerous", [33023] = "bank", [16711680] = "water", 
                    [8388863] = "herbalist", [16744576] = "weapons", [8421631] = "grocer", [8388736] = "clothes", [4227200] = "postoffice", [4210816] = "WarTrain"}
      rooms["Info"] = info[row[19]]
--[[    else
      rooms["Info"] = ""
    end

   ]]--
   
    --Creating Zmud UID to MUSHclient UID reference table
    RoomUIDRef[row[1]] = rooms["UID"]
    
    process_room(rooms)
    -- read next row
    row = cur:fetch (row)
  end  -- while loop
  
  --[[rooms["UID"] = UID,
      rooms["Name"] = RoomName,
      rooms["Description"] = Desc,
      rooms["Area"] = Area,
      rooms["Notes"] = Notes,
      rooms["Terrain"] = Terrain,
      rooms["Info"] = Info
  ]]--
  --row[2] = name, row[5] = description, row[4] = info, row[3] = Notes, row[32] = area,
  --row[19] = color, row[18] = cost (2147483647 = dt), 
  --terrains: 10485760 = deepwater, 32896 = road, 8421376 = saplings, 8421504 = indoor, 32768 = trees, 0 = deathtrap
  --info: 16776960 = stables, 65535 = armory, 65280 = rent, 16711935 = food, 255 = dangerous, 33023 = bank, 16711680 = water, 
  --      8388863 = herbalist, 16744576= weapons, 8421631= grocer, 8388736= clothes, 4227200= postoffice, 4210816 = WarTrain,
  --      [ obvious exits: N E S W U D ]
  cur:close()    
  --------------------------END ROOM INFO READING----------------------
  
  -------------------------GETTING EXIT INFORMATION--------------------               
  -- retrieve a cursor
  cur = assert (con:execute ("SELECT * from ExitTbl" ))

  -- read first row, then start looping through rows while putting desired data into the areas table
  row = cur:fetch ({})

  
  exits = {}
  while row do
    local ExitDirs = { [0] = "n", [1] = " ne", [2] = "e", [3] = "se", [4] = "s", [5] = "sw", [6] = "w", [7] = "nw", [8] = "u", [9] = "d"}
    
    exits["dir"] = ExitDirs[row[19]]  --Set Direction (e.g. "n", "s", etc)
    exits["fromUID"] = RoomUIDRef[row[2]]  --Set FromUID
    exits["toUID"] = RoomUIDRef[row[3]]  --Set ToUID
    
    process_exit(exits)
    -- read next row
    row = cur:fetch (row)
  end  -- while loop
  cur:close()    
  --------------------------END EXIT PROCESSING----------------------
  
-- close everything
cur:close()
con:close()
env:close()
end --function load_database


function create_database()
  -- open database on disk 
  DatabaseOpen("mapdb", 
               GetInfo (66) .. Trim(GetInfo(2)) .. "_" .. GetInfo(1) .. ".sqlite",
               6);
  
  -- create tables
  DatabaseExec ("mapdb", [[

  
  PRAGMA foreign_keys = ON;
  
  DROP TABLE IF EXISTS exits;
  DROP TABLE IF EXISTS rooms;
  DROP TABLE IF EXISTS areas;
  DROP TABLE IF EXISTS environments;
  
  CREATE TABLE areas (
      areaid      INTEGER PRIMARY KEY AUTOINCREMENT,
      uid         TEXT    NOT NULL,   -- vnum or how the MUD identifies the area
      name        TEXT,               -- name of area
      date_added  DATE,               -- date added to database
      UNIQUE (uid)
    );

  
  CREATE TABLE environments (
      environmentid INTEGER PRIMARY KEY AUTOINCREMENT,
      uid           TEXT    NOT NULL,   -- code for the environment
      name          TEXT,               -- name of environment
      color         INTEGER,            -- ANSI colour code
      date_added    DATE,               -- date added to database
      UNIQUE (uid)
    );
    
  CREATE INDEX IF NOT EXISTS name_index ON environments (name);

  CREATE TABLE rooms (
      roomid        INTEGER PRIMARY KEY AUTOINCREMENT,
      uid           TEXT NOT NULL,   -- vnum or how the MUD identifies the room
      name          TEXT,            -- name of room
      description   TEXT,            -- room's description
      area          TEXT,            -- which area
      building      TEXT,            -- which building it is in
      terrain       TEXT,            -- eg. road OR water
      info          TEXT,            -- eg. shop,postoffice
      notes         TEXT,            -- player notes
      x             INTEGER,
      y             INTEGER,
      z             INTEGER,
      date_added    DATE,            -- date added to database
      UNIQUE (uid)
    );

  CREATE INDEX IF NOT EXISTS info_index ON rooms (info);
  CREATE INDEX IF NOT EXISTS terrain_index ON rooms (terrain);
  CREATE INDEX IF NOT EXISTS area_index ON rooms (area);
   
           
  CREATE TABLE exits (
      exitid      INTEGER PRIMARY KEY AUTOINCREMENT,
      dir         TEXT    NOT NULL, -- direction, eg. "n", "s"
      fromuid     STRING  NOT NULL, -- exit from which room (in rooms table)
      touid       STRING  NOT NULL, -- exit to which room (in rooms table)
      date_added  DATE,             -- date added to database
      FOREIGN KEY(fromuid) REFERENCES rooms(uid)
    );
  CREATE INDEX IF NOT EXISTS fromuid_index ON exits (fromuid);
  CREATE INDEX IF NOT EXISTS touid_index   ON exits (touid);
  
  DROP TABLE IF EXISTS rooms_lookup;
      
  CREATE VIRTUAL TABLE rooms_lookup USING FTS3(uid, name);
  ]])
  
  
  DatabaseExec ("mapdb", "BEGIN TRANSACTION;")
  
  load_database() ---add all shit

  DatabaseExec ("mapdb", "COMMIT;")
  
  count = areacount+roomcount+exitcount+environmentcount
  SetStatus("")
  print ("Processed " .. count .. " number of records.")
  print ("Found", areacount, "areas")
  print ("Found", roomcount, "rooms")
  print ("Found", exitcount, "exits")
  print ("Found", environmentcount, "environments")
end --function create_database()
]]>
</script>

</muclient>