conn.pragma

[mnemnion]

Hi,

I thought this was better opened as a feature, rather than wrapping it in a pull request.

I have an implementation for SQLite pragmas. It's currently a 'monkey-patch', which I would rewrite slightly to merge into core. I'll put the implementation into a block by itself.

[mnemnion]

-- get a conn object via in-memory DB
local conn = sql.open ":memory:"
local conn_mt = ffi.reflect.getmetatable(conn)
local stmt = conn:prepare "CREATE TABLE IF NOT EXISTS test(a,b);"
local stmt_mt = ffi.reflect.getmetatable(stmt)
conn:close() -- polite
conn, stmt = nil, nil

local pragma_pre = "PRAGMA "

-- Builds and returns a pragma string
local function __pragma(prag, value)
   local val
   if value == nil then
      return pragma_pre .. prag .. ";"
   end
   if type(value) == "boolean" then
      val = value and " = 1" or " = 0"
   elseif type(value) == "string" then
      val = "('" .. san(value) .. "')"
   elseif type(value) == "number" then
      val = " = " .. tostring(value)
   else
      error(false, "value of type " .. type(value) .. ", " .. c.ts(value))
   end
   return pragma_pre .. prag .. val .. ";"
end

-- Sets a pragma and checks its new value
local function _prag_set(conn, prag)
   return function(value)
      local prag_str = __pragma(prag, value)
      conn:exec(prag_str)
      -- check for a boolean
      -- #todo make sure this gives sane results for a method-call pragma
      local answer = conn:exec(pragma_pre .. prag .. ";")
      if answer[1] and answer[1][1] then
         if answer[1][1] == 1 then
            return true
         elseif answer[1][1] == 0 then
            return false
         else
            return nil
         end
      end
   end
end

-- create a function for __index 

local function new_conn_index(conn, key)
   local function _prag_index(f, prag)
      return _prag_set(conn, prag)
   end
   if key == "pragma" then
      return setmetatable({}, {__index = _prag_index})
   else
      return conn_mt[key]
   end
end

conn_mt.__index = new_conn_index

[mnemnion]

I don't know that the logic of this is completely transparent but it lets you do both of these:

conn.pragma("foreign_keys", true)

conn.pragma.foreign_keys(true)

Both of which return 'true', since this pragma always succeeds.

[mnemnion]

the ffi.reflect bit is non-standard, now that I think about it.

As I said, monkey-patch. Should be testable just by appending it before the return in the library, without the metatable fetch.

[mnemnion]

Also not included is san, which, as it implies, sanitizes SQL input, as a simple ' -> '' substitution:

local function san(str)
   return gsub(str, "'", "''")
end

I'm not sure that's sufficient, it's what I've been using.