Persistent Tables Sqlite

wiki

Code follows: ----8<---- snip ----8<----

--[[ luapersist3.lua  2004-Aug-31 e

  The author disclaims copyright to this source code.  In place of
  a legal notice, here is a blessing:
      May you be healthy and well.
      May you be free of all suffering.
      May you be happy, giving more than you take.
   
  Lua Persistent Tables
  loosely based on the wiki page http://lua-users.org/wiki/PersistentTables
  uses Lua SQLite 3 (see http://luaforge.net/projects/luasqlite/)
  handles circular structures
  
  DB Schema
  the Lua tables are stored in a single SQL table with five columns
  TID : Table ID that identifies the Lua table -- string
  Key : index in the Lua Table -- TID, string, number, or boolean
  Kyp : the data type of Key
  Val : value in the Lua Table at index -- TID, string, number, or boolean
  Vyp : the data type of Val
  
  A row is created for each Lua Persistent Table to reserve its TID.
  The Key, Kyp, Val, and Vyp columns are all set to "_".
  
  Both Kyp and Vyp use the following encoding:
  "b" -- boolean (Key/Val = "t" or "f")
  "n" -- number
  "t" -- TID
  "s" -- string
  "e" -- encoded string (the raw string has embedded NULs)
   "_" -- null
 
  Caveats
  1. Strings used for Key and Val may contain embedded '\0' NUL characters; 
     they are stored using sqlite blobs.
     Strings used for TIDs must not have embedded NULs.
  2. A table may not be used as a key unless the table is already persistent;
      tables may always be used as vals.
  3. Functions, threads, and userdata are not supported as keys or vals.
  4. Lua Persistent Tables may not have user metatables (they will not be
     persisted, and they may conflict with Lua Persistent Table events).  
  
  Implementation
  
  Lua Persistent Tables are represented by an empty Lua Table and a
  corresponding metatable. 
  
  Lua Persistent Table Metatable events:
  "lpt_TID" -- the TID for this table
  "index" -- handler for unmarshalling Key/Val from the DB
  "newindex" -- handler for marshalling Key/Val to the DB
  "lpt_cache" -- a Lua Table that caches Key/Val pairs
  "lpt_db" -- the Lua Persistent Table database descriptor for this table's db
  
  LuaPersist maintains one global weak table, map, that is used to find
  Lua Persistent Tables that are already open. This insures that there
  is at most one version of each Lua Persistent Table in memory.
]]

--[[ examples...

require "luapersist3"

lp=assert(persist.open"ptest.db") -- or --
lp=assert(persist.open":memory:")

t=lp:new_table("foo")

assert(t==lp.map.foo)

t["baz"] = 7

for TID,Key,Kyp,Val,Vyp in lp.db:urows("SELECT * FROM luat") do
  print(TID, Key, Kyp, Val, Vyp) end 

t["baz"] = 9
t["bar"] = 9
t["baz"] = 3

t.tt = {["a"]=1,["b"]=2} 

tt[{1,2,3}]="t123" -- error

-- close and open

t=lp:get_table("foo")

for Key,Kyp,Val,Vyp
 in lp.db:urows("SELECT Key,Kyp,Val,Vyp FROM luat where TID='foo'")
 do print(Key, Kyp, Val, Vyp) end

lp:cache"foo"

ca = assert(getmetatable(t).__lpt_cache,"there is no __lpt_cache for this persistent table")

for k,v in pairs(ca) do print(k,v) end

=t.tt.a

t["123\000567"]="abc\000efg"

for k,v in pairs(ca)
 do local x = 0
    if(type(v)=='string') then x = string.len (v) end
    print(k,v,string.len(k),x)
 end

for Key,Kyp,Val,Vyp
 in lp.db:urows("SELECT Key,Kyp,Val,Vyp FROM luat where TID='tid1'")
 do print(Key, Kyp, Val, Vyp) end

-- debugging

for r in lp.db:nrows("SELECT * FROM SQLITE_MASTER") do  
     for k,v in pairs(r) do print(k,v) end  
end 

-- maintanance -- LOSES ALL DATA!

for r in lp.db:nrows("DROP TABLE luat") do  
     for k,v in pairs(r) do print(k,v) end  
end

]]

require "sqlite3"

persist = {}

local _persist_make_TID = function (lp,tbl)
  -- return "TID"..math.random() -- fix me: confirm it's unique
  local TID
  repeat
    lp.seq = lp.seq + 1
    TID = string.format('tid%d',lp.seq)
  until(not lp:exists(TID))
  return TID
end

local _persist_val_vyp = function (lp,k)
  local ty = type(k)
  if(ty=="string") then
    if(string.find(k,"%z")) then
      return k,"e"
    else
      return k,"s"
    end
  end
  if(ty=="number") then return tostring(k),"n" end
  if(ty=="boolean") then if k then v="t" else v="f" end return v,"b" end
  if(ty=="table") then
    local mt = getmetatable(k)
    local TID
    if(mt~=nil) then TID = mt.__lpt_TID end
    if(mt==nil or TID==nil) then
      TID = _persist_make_TID(lp,k)
      local t = persist.new_table(lp,TID)
      persist.set_table(lp,TID,k)
    end
    return TID,"t"
  end
  assert(nil,"non-persistent type: "..ty.."!?")
end

local _persist_key_kyp = function (lp,k)
  if(type(k)=="table") then
    local mt = getmetatable(k)
    local TID
    if(mt~=nil) then TID = mt.__lpt_TID end
    if(mt==nil or TID==nil) then
      error("use of non-persistent table as key not supported")
    end
    return TID,"t"
  end
  return _persist_val_vyp(lp,k)
end

local _persist_raw_to_val = function (lp,ty,raw)
  if(ty=="s") then return raw end
  if(ty=="n") then return raw+0 end
  if(ty=="b") then return raw=="t" end
  if(ty=="t") then return lp:get_table(raw) end
  if(ty=="e") then return raw end
  assert(nil,"fix me -- what type is: "..ty.."?")
end

local _persist_vm_setup = function (lp,vm,TID,k)
  local key,kyp = _persist_key_kyp(lp,k) -- this must come before vm:reset since it might use vm
  assert(vm:reset()==sqlite3.OK,"db reset error")
  assert(vm:bind(1,TID)==sqlite3.OK,"db TID bind error")
  if kyp=="e"
  then
    assert(vm:bind_blob(2,key)==sqlite3.OK,"db Key bind error")
  else
    assert(vm:bind(2,key)==sqlite3.OK,"db Key bind error")
  end
  assert(vm:bind(3,kyp)==sqlite3.OK,"db Kyp bind error")
end

local _persist_index = function (t,k)
  assert(k,"key of nil not permitted")
  local ca = assert(getmetatable(t).__lpt_cache,"there is no __lpt_cache for this persistent table")
  local v = rawget(ca,k)
  if(v~=nil) then return v end
  local lp = assert(getmetatable(t).__lpt_db,"there is no __lpt_db for this persistent table")
  local TID = assert(getmetatable(t).__lpt_TID,"there is no __lpt_TID for this persistent table")
  _persist_vm_setup(lp,lp.vm_get,TID,k)
  local rc = lp.vm_get:step()
  if(rc==sqlite3.ROW) then
    local da
    da = lp.vm_get:get_values()
    lp.vm_get:reset() -- releases locks afer step
    assert(da,"db lp.vm_get:data error")
    v = _persist_raw_to_val(lp, da[2], da[1])
    rawset(ca,k,v)
  else
    lp.vm_get:reset() -- releases locks afer step
    v = nil
  end
  return v
end

local _persist_exists = function (lp,TID)
  _persist_vm_setup(lp,lp.vm_get,TID,"_")
  assert(lp.vm_get:bind(3,"_")==sqlite3.OK,"db _ bind error")
  local rc = lp.vm_get:step()
  lp.vm_get:reset() -- releases locks afer step
  return(rc==sqlite3.ROW)
end

local _persist_newindex = function (t,k,v)
  assert(k,"key of nil not permitted")
  local ca = assert(getmetatable(t).__lpt_cache,"there is no __lpt_cache for this persistent table")
  -- local pv = rawget(ca,k)
  -- if(pv~=nil) then end -- maybe remove tables -- but need gc since there may be circular refs!
  local lp = assert(getmetatable(t).__lpt_db,"there is no __lpt_db for this persistent table")
  local TID = assert(getmetatable(t).__lpt_TID,"there is no __lpt_TID for this persistent table")
  local rc, vm
  if(v~=nil) then
    local val,vyp = _persist_val_vyp(lp,v) -- this must come before vm_setup since it might use vm
    vm = lp.vm_new
    _persist_vm_setup(lp,vm,TID,k)
    if vyp=="e"
    then
        assert(lp.vm_new:bind_blob(4,val)==sqlite3.OK,"db Key bind error:"..val)
    else
        assert(lp.vm_new:bind(4,val)==sqlite3.OK,"db Key bind error:"..val)
    end
    assert(lp.vm_new:bind(5,vyp)==sqlite3.OK,"db Kyp bind error:"..vyp)
    rc = lp.vm_new:step()
  else
    vm = lp.vm_del
    _persist_vm_setup(lp,vm,TID,k)
    rc = lp.vm_del:step()
  end
  vm:reset() -- releases locks afer step
  if(rc==sqlite3.DONE) then
    rawset(ca,k,v)
  else
    error("bad result code: "..rc.."")
  end
end

local _persist_pairs = function (t)
  assert(type(t)=='table',"arg is not a table")
  local ca = assert(getmetatable(t).__lpt_cache,"arg is not a persistent table")
  return pairs(ca)
end

local _persist_check = function (lp)
  assert(lp.LPT,"the db must be opened first with persist.open")
  assert(lp.db,"the db must be opened first with persist.open")
end

persist.close = function (lp)
  _persist_check(lp)
  lp.db:close()
  lp.db=nil;
  -- nice for GC?
  lp.vm_new = nil
  lp.vm_del = nil
  lp.vm_get = nil
  lp.vm_set = nil
  lp.vm_delt = nil
  lp.map = nil  
end

local _persist_new_table = function (lp,TID)
  local t = {}  
  setmetatable(t,{["__lpt_db"] = lp,
                  ["__lpt_TID"] = TID,
                  ["__lpt_cache"] = {},
                  ["__index"] = _persist_index,
                  ["__newindex"] = _persist_newindex,
                  ["__unm"] = _persist_pairs})
  return t
end

persist.exists = _persist_exists

persist.new_table = function (lp,TID)
  _persist_check(lp)
  assert(lp.map[TID]==nil,"the table is already created and open")
  assert(not lp:exists(TID),"a persistent table with TID "..TID.." already exists")
  local t = _persist_new_table(lp,TID)
  assert(lp.vm_new:reset()==sqlite3.OK,"reset error")
  assert(lp.vm_new:bind(1,TID)==sqlite3.OK,"bind 1 error")
  assert(lp.vm_new:bind(2,"_")==sqlite3.OK,"bind 2 error")
  assert(lp.vm_new:bind(3,"_")==sqlite3.OK,"bind 3 error")
  assert(lp.vm_new:bind(4,"_")==sqlite3.OK,"bind 4 error")
  assert(lp.vm_new:bind(5,"_")==sqlite3.OK,"bind 5 error")
  local rc = lp.vm_new:step()
  lp.vm_new:reset() -- release locks
  if(rc==sqlite3.DONE) then
    lp.map[TID] = t
  else
    error("step error")
  end
  return t
end

persist.cache = function (lp,TID)
  _persist_check(lp)
  local t = lp.map[TID]
  assert(t~=nil,"the table does not exist")
  local ca = assert(getmetatable(t).__lpt_cache,"there is no __lpt_cache for this persistent table")
  -- whack the vm
  fn,vm,rc = lp.db:urows("SELECT Key,Kyp,Val,Vyp FROM luat where TID=?")
  assert(vm,"lp.db:rows failed")
  vm:bind(1,TID)
  for Key,Kyp,Val,Vyp in fn,vm,rc do  
    if( Key ~= "_" ) then
      rawset(ca, _persist_raw_to_val(lp,Kyp,Key), _persist_raw_to_val(lp,Vyp,Val))
    end
  end
end

persist.get_table = function (lp,TID)
  _persist_check(lp)
  local t = lp.map[TID]
  if(t~=nil) then return t end
  assert(lp:exists(TID),"no persistent table has the TID "..TID)
  t = _persist_new_table(lp,TID)
  lp.map[TID] = t
  -- this needn't cache the whole table! we can make it lazy
  -- persist.cache(lp,TID)
  return t  
end

persist.set_table = function (lp,TID,s)
  _persist_check(lp)
  local t = lp.map[TID]
  assert(t~=nil,"the table does not exist")
  for k,v in pairs(s) do
    t[k]=v
  end
  return t
end

persist.delete_table = function (lp,TID)
  _persist_check(lp)
  local t = lp.map[TID]
  if(t~=nil) then lp.map[TID] = nil end
  --assert(lp:exists(TID),"no persistent table has the TID "..TID)
  assert(lp.vm_delt:reset()==sqlite3.OK,"db reset error")
  assert(lp.vm_delt:bind(1,TID)==sqlite3.OK,"db TID bind error")
  local rc = lp.vm_delt:step()
  lp.vm_delt:reset() -- releases locks afer step
  return(rc)
  -- to do: what is rc supposed to be? 101
  --return t  
end

persist.open = function (dbname)
  local lp = {}
  lp.LPT = true -- a tag
  lp.seq = 0 -- tid generator -- would be nice to select max (TID) where TID like 'tid%'
  lp.db = assert(sqlite3.open(dbname))
  -- initialize the db; ignore error from CREATE as it may already have been done
  local err,str = lp.db:exec("create table luat (TID,Key,Kyp,Val,Vyp, primary key (TID,Key,Kyp) on conflict replace)")
  if (err==26) then
    lp.db:close() -- bad database format
    return nil,str
  end
  lp.vm_new = assert(lp.db:compile("insert into luat values (?,?,?,?,?)"))
  lp.vm_del = assert(lp.db:compile("delete from luat where TID=? and Key=? and Kyp=?"))
  lp.vm_get = assert(lp.db:compile("select Val,Vyp from luat where TID=? and Key=? and Kyp=?"))
  --lp.vm_set = assert(lp.db:compile("update luat set Val=?,Vyp=? where TID=? and Key=? and Kyp=?"))
  lp.vm_delt = assert(lp.db:compile("delete from luat where TID=?"))
  -- convenience oo functions
  lp.new_table = persist.new_table
  lp.get_table = persist.get_table
  lp.set_table = persist.set_table
  lp.cache = persist.cache
  lp.exists = persist.exists
  lp.delete_table = persist.delete_table 
  lp.close = persist.close
  -- initialize the in-memory map
  lp.map = {}
  setmetatable(lp.map,{["__mode"]="v"}) -- weak on values
  return lp
end

-- end of luapersist3.lua