Gammon Forum

function save_full_exits_to_database (uid, exits)
--  Note ("Mapper doing: save_full_exits_to_database.")
  
  local room = rooms [uid]
  
  db:exec ("BEGIN TRANSACTION;") 

  for exit in string.gmatch (exits, "[^,]+") do

    dir, touid = string.match (exit, "^(%a+)%((%w+)%)$")	-- Modified note: changed %d to %w to match alphanumeric room uids
    
    touid = tostring (touid) -- instead of tonumber
    
    if dir then
      -- fix up in and out
      dir = ({ ['i'] = "in", o = "out", }) [dir] or dir
      
      dbcheck (db:execute (string.format ([[
        INSERT INTO exits (dir, fromuid, touid, date_added) 
            VALUES (%s, %s, %s, DATETIME('NOW'));
      ]], fixsql  (dir),  -- direction (eg. "n")
          fixsql  (uid),  -- from current room
          fixsql  (touid) -- destination room 
          )))
      if show_database_mods then
        mapper.mapprint ("Added full exit", dir, "from room", uid, "to room", touid, "to database.")
      end -- if
      
      room.exits [dir] = touid
    else
      mapper.maperror ("Cannot make sense of:", exit)
    end -- if can decode
    
  end -- for each exit
  
  db:exec ("COMMIT;") 
  
end -- function save_full_exits_to_database

function load_room_from_database (uid)
--	Note ("Mapper doing: load_room_from_database.")
	
  local room
  
  uid = tostring (uid)	-- Geas: from tonumber to tostring
 
  assert (uid, "No UID supplied to load_room_from_database")
  
  -- if not in database, don't look again
  if room_not_in_database [uid] then
    return nil
  end -- no point looking
  
  for row in db:nrows(string.format ("SELECT * FROM rooms WHERE uid = %s", fixsql (uid))) do
     room = {
       name = row.name,
       area = row.area,
       building = row.building,
       terrain = row.terrain,
       info = row.info,
       notes = row.notes,
       x = row.x,
       y = row.y,
       z = row.z,
  
       exits = {} }
      
    for exitrow in db:nrows(string.format ("SELECT * FROM exits WHERE fromuid = %s", fixsql (uid))) do
       room.exits [exitrow.dir] = tostring (exitrow.touid)	-- Modified note: changed from tonumber to tostring touid
    end -- for each exit
    
  end   -- finding room

  if room then
    rooms [uid] = room
    for row in db_bm:nrows(string.format ("SELECT * FROM bookmarks WHERE uid = %s", fixsql (uid))) do
      rooms [uid].notes = row.notes
    end   -- finding room
        
    return room
  end -- if found
  
  room_not_in_database [uid] = true
  return nil
    
end -- load_room_from_database

room.exits [exitrow.dir] = tonumber (exitrow.touid)

room.exits [exitrow.dir] = tostring (exitrow.touid)

.schema exits

60949403 * 10^41

= 

6094940300000000000000000000000000000000000000000

But you got:

6094940299999999944756163936158435001692602761216
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
         41 zeroes, however not completely accurate

6094940299999999944756163936158435001692602761216
^^^^^^^^^^^^^^^^^
17 digits are accurate (apart from rounding)

-- here when location changes, eg. : Room.Num 7476
function got_room_number (s)
--  Note ("Mapper doing: got_room_number.")
  local room_number = tostring (s)
    
  if not room_number then
    return
  end -- no room number

  current_room = room_number
--  Note ("Mapper doing: current room is " .. current_room)
  mapper.draw (tostring (room_number))
  
  if expected_exit == "0" and from_room then
    fix_up_exit ()
  end -- exit was wrong

end -- got_room_number

function fix_up_exit ()

  local room = rooms [from_room]
  
  dbcheck (db:execute (string.format ([[
      UPDATE exits SET touid = %s WHERE fromuid = %s AND dir = %s;
    ]], 
        fixsql  (current_room),     -- destination room
        fixsql  (from_room),       -- from previous room
        fixsql  (last_direction_moved)  -- direction (eg. "n")
        )))
        
  if show_database_mods then
    mapper.mapprint ("Fixed exit", last_direction_moved, "from room", from_room, "to be to", current_room)
  end -- if
  
  room.exits [last_direction_moved] = current_room
    
  last_direction_moved = nil
  from_room = nil
  
end -- fix_up_exit

function room_change_exit (room, uid)

local available =  {
  n = "North",
  s = "South",
  e = "East",
  w = "West",
  u = "Up",
  d = "Down",
  ne = "Northeast",
  sw = "Southwest",
  nw = "Northwest",
  se = "Southeast",
  nu = "nu",
  su = "su",
  eu = "eu",
  wu = "wu",
  neu = "neu",
  swu = "swu",
  nwu = "nwu",
  seu = "seu",
  nd = "nd",
  sd = "sd",
  ed = "ed",
  wd = "wd",
  ned = "ned",
  swd = "swd",
  nwd = "nwd",
  sed = "sed",
  ['in'] = "In",
  out = "Out",
  }  -- end of available

  -- remove non-existent exits
  for k in pairs (available) do
    if room.exits [k] then
      available [k] = available [k] .. " --> " .. room.exits [k] 
    else
      available [k] = nil
    end -- if not a room exit
  end -- for
  
  if next (available) == nil then
    utils.msgbox ("There are no exits from this room.", "No exits!", "ok", "!", 1)
    return
  end -- not known
  
  local chosen_exit = utils.listbox ("Choose exit to change destination of:", "Exits ...", available )
  if not chosen_exit then
    return
  end

  exit_destination = utils.inputbox ("Enter destination room identifier (number) for " .. available [chosen_exit], room.name, "")

  if not exit_destination then
    return
  end -- cancelled
  
    -- look it up
  local dest_room = rooms [exit_destination]
  
  -- not cached - see if in database
  if not dest_room then
    dest_room = load_room_from_database (exit_destination)
    rooms [exit_destination] = dest_room -- cache for later
  end -- not in cache
  
  if not dest_room then
    utils.msgbox ("Room " .. exit_destination .. " does not exist.", "Room does not exist!", "ok", "!", 1)
    return
  end -- if still not there
    
  dbcheck (db:execute (string.format ([[
    UPDATE exits SET touid = %s WHERE dir = %s AND fromuid = %s;
  ]], fixsql  (exit_destination),
      fixsql  (chosen_exit),  -- direction (eg. "n")
      fixsql  (uid)  -- from current room
      )))
      
  if show_database_mods then
    mapper.mapprint ("Modified exit", available [chosen_exit], "from room", uid, "to be to room", exit_destination, "in database.")
  end -- if
  
  -- update in-memory table
  rooms [uid].exits [chosen_exit] = exit_destination
  mapper.draw (tostring (current_room))
   
end -- room_change_exit

function dbcheck (code)
 if code ~= sqlite3.OK and    -- no error
    code ~= sqlite3.ROW and   -- completed OK with another row of data
    code ~= sqlite3.DONE then -- completed OK, no more rows
    local err = db:errmsg ()  -- the rollback will change the error message
    db:exec ("ROLLBACK")      -- rollback any transaction to unlock the database
    error (err, 2)            -- show error in caller's context
  end -- if
end -- dbcheck 

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

-- start here

db    = assert (sqlite3.open(GetInfo (66) .. "test.db"))

dbcheck (db:execute[[
  CREATE TABLE IF NOT EXISTS test ( t text );
  DELETE FROM test;
  INSERT INTO test (t) VALUES ('060949403E41');
  ]])

for row in db:nrows(string.format ("SELECT * FROM test")) do
  print (row.t, type (row.t))
end   -- finding room

db:close()

060949403E41 string

060949403E41 string

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

Added full exit s from room 1A8549F81F6C to room 060949403E41 to database.

"s"="060949403E41"

"s"="6.0949403e+048"

"s"="060949403E41"

"s"="6.0949403e+048"

db    = assert (sqlite3.open(GetInfo (66) .. "test.db"))

--> ADD THIS:

if old_db_execute == nil then
  old_db_execute = getmetatable (db).execute
end -- if

getmetatable (db).execute = function (this, what)
  ColourNote ("orange", "", "** SQL debugging: About to run: " .. what)
  return old_db_execute  (this, what)
end -- function

** SQL debugging: About to run:   CREATE TABLE IF NOT EXISTS test ( t text );
  DELETE FROM test;
  INSERT INTO test (t) VALUES ('060949403E41');

function OnPluginInstall ()
  
  config = {}  -- in case not found

  -- get saved configuration
  assert (loadstring (GetVariable ("config") or "")) ()

  -- allow for additions to config
  for k, v in pairs (default_config) do
    config [k] = config [k] or v
  end -- for
  
  -- initialize mapper engine
  mapper.init { config = config,            -- colours, timing etc.
                get_room = get_room,        -- get_room (uid) called to get room info
                show_help = OnHelp,         -- to show help
                room_click = room_click,    -- called on RH click on room square
                timing = show_timing,       -- want to see timing
                show_completed = show_completed,  -- want to see "Speedwalk completed." message
                show_other_areas = show_other_areas,  -- want to see areas other than the current one?
                show_up_down = show_up_down,          -- want to follow up/down exits?
                show_area_exits = show_area_exits,    -- want to see area exits?
                speedwalk_prefix = speedwalk_prefix,  -- how to speedwalk
                } 
  
  mapper.mapprint (string.format ("MUSHclient mapper installed, version %0.1f", mapper.VERSION))
  
  -- open databases on disk 
  db    = assert (sqlite3.open(GetInfo (66) .. Trim (WorldAddress ()) .. "_" .. WorldPort () .. ".db"))
  db_bm = assert (sqlite3.open(GetInfo (66) .. Trim (WorldAddress ()) .. "_" .. WorldPort () .. "_bookmarks.db"))
  
  
  --> ADD THIS:

if old_db_execute == nil then
  old_db_execute = getmetatable (db).execute
end -- if

getmetatable (db).execute = function (this, what)
  ColourNote ("orange", "", "** SQL debugging: About to run: " .. what)
  return old_db_execute  (this, what)
end -- function


  create_tables ()    -- create database structure if necessary
  
  -- grab all area names
  for row in db:nrows("SELECT * FROM areas") do
    areas [row.uid] = row.name
  end   -- finding areas
  
  -- grab all user terrain info
  for row in db_bm:nrows("SELECT * FROM terrain") do
    user_terrain_colour [row.name] = row.color
  end   -- finding terrains
 
  -- grab all environment names
  for row in db:nrows("SELECT * FROM environments") do
    environments [tostring (row.uid)] = row.name		-- Geas: changed from tonumber
    terrain_colours [row.name] = tonumber (row.color)
  end   -- finding environments
  
end -- OnPluginInstall

** SQL debugging: About to run: INSERT INTO rooms (uid, name, area, date_added) VALUES ('1A8549F81F6C', 'The entrance to a light forest', '0', DATETIME('NOW'));
** SQL debugging: About to run:         INSERT INTO rooms_lookup (uid, name) VALUES ('1A8549F81F6C', 'The entrance to a light forest');
      
Added room 1A8549F81F6C to database. Name: The entrance to a light forest
** SQL debugging: About to run:         INSERT INTO exits (dir, fromuid, touid, date_added) 
            VALUES ('s', '1A8549F81F6C', '060949403E41', DATETIME('NOW'));
      
Added full exit s from room 1A8549F81F6C to room 060949403E41 to database.

** SQL debugging: About to run: INSERT INTO rooms (uid, name, area, date_added) VALUES ('060949403E41', 'A road junction surrounded by forest', '0', DATETIME('NOW'));
** SQL debugging: About to run:         INSERT INTO rooms_lookup (uid, name) VALUES ('060949403E41', 'A road junction surrounded by forest');
      
Added room 060949403E41 to database. Name: A road junction surrounded by forest

** SQL debugging: About to run:         INSERT INTO exits (dir, fromuid, touid, date_added) 
            VALUES ('n', '060949403E41', '1A8549F81F6C', DATETIME('NOW'));
      
Added full exit n from room 060949403E41 to room 1A8549F81F6C to database.

SELECT * FROM exits WHERE touid = '1A8549F81F6C';

CREATE TABLE IF NOT EXISTS 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
    );