db.lua

luaClass{"DB"}

staticDBPath = "db/oscar.db"
DBPath = NSDocumentDirectory .. "/oscar-v001.db"

function load()
  if db then return end  
  
  local fm = NS.FileManager:defaultManager()
    
  -- Must move the db to a writable path
  if not fm:fileExistsAtPath(DBPath) then
    fm:copyItemAtPath_toPath_error(staticDBPath, DBPath, nil)  
  end  
  
  db = wax.sqlite.open(DBPath)
  puts("DB Path: %s", DBPath)
  wax.sqlite.execute(db, "PRAGMA encoding = 'UTF-8'") -- Default to UTF-8 encoding
  wax.sqlite.execute(db, "PRAGMA CACHE_SIZE=25") -- Modify cache size so we don't overload memory.  CACHE_SIZE * 1.5kb (I don't know if this makes sense)
    
  migrate()  
end

function reset()
  local fm = NS.FileManager:defaultManager()
  
  -- Forces loading of static DB
  if fm:fileExistsAtPath(DBPath) then fm:removeItemAtPath_error(DBPath, nil) end
end

function close()
  wax.sqlite.close(db)
  db = nil
end

function execute(sql, ...)
  local options = {...}
  local callback
  if #options > 0 and type(table.last(options)) == "function" then
    callback = table.remove(options)
  end

  return wax.sqlite.execute(db, escapeSQL(sql, unpack(options)), callback)
end

function escapeSQL(string, ...)
  if ... then
    local formattedOptions = {}

    formattedOptions = table.map({...}, function(v)
      if type(v) == "string" then v = string.gsub(v, "'", "''") end
      return v
    end)
    
    return string.format(string, unpack(formattedOptions))
  else 
    return string
  end
end