DataUtils.lua #1

local DataUtils = {}

local sqlite3 = require( "lsqlite3" )

function DataUtils.init()
  DataUtils.db = sqlite3.open( Helix.Core.Server.GetDataDirFileName( "swarm.db" ) )

  local mt = {
    __gc = function( self )
        self.db:close()
        -- Note that Helix.Core.Server.* functions are unavailable at this point.
        -- E.g. Helix.Core.Server.log()
      end
  }

  setmetatable( DataUtils, mt )

  assert(
  DataUtils.db:exec [[
    PRAGMA journal_mode = WAL;
    PRAGMA foreign_keys = ON;

    CREATE TABLE IF NOT EXISTS
        reviews ( id INTEGER PRIMARY KEY NOT NULL, user TEXT NOT NULL,
                  state TEXT NOT NULL );

    CREATE INDEX IF NOT EXISTS
        reviews_idx on reviews ( id );

    CREATE TABLE IF NOT EXISTS
        changes ( id INTEGER REFERENCES reviews ( id ) ON DELETE CASCADE,
                  cl INTEGER NOT NULL, UNIQUE ( id, cl ) );

    CREATE INDEX IF NOT EXISTS
        changes_idx on reviews ( cl );

    ]])

  local rstr = "INSERT INTO reviews ( id, user, state ) VALUES ( ?, ?, ? ) ON CONFLICT DO NOTHING"
  local cstr = "INSERT INTO changes ( id,   cl        ) VALUES ( ?, ?    ) ON CONFLICT DO NOTHING"
  local istr = "SELECT id, cl FROM CHANGES WHERE id IN ( SELECT id FROM changes WHERE cl = ? )"
  local astr = "SELECT state FROM reviews WHERE id = ?"

  DataUtils.insReviewsStmt = DataUtils.db:prepare( rstr )
  DataUtils.insChangesStmt = DataUtils.db:prepare( cstr )
  DataUtils.SlcChangesStmt = DataUtils.db:prepare( istr )
  DataUtils.SlcReviewsStmt = DataUtils.db:prepare( astr )

  Helix.Core.Server.log( { ["DataUtils.db"] = tostring( DataUtils.db ) } )
  Helix.Core.Server.log( { ["DataUtils.insReviewsStmt"] = tostring( DataUtils.insReviewsStmt ) } )
  Helix.Core.Server.log( { ["DataUtils.SlcChangesStmt"] = tostring( DataUtils.SlcChangesStmt ) } )

  local emsg = DataUtils.db:errmsg()

  if emsg ~= "not an error" then
    Helix.Core.Server.log( { ["dberr"] = emsg } )
  end
end

-- Note that there's no cleanup yet.

function DataUtils.insert( data )
  DataUtils.db:exec "BEGIN TRANSACTION;"

  for k, v in pairs( data[ "reviews" ] ) do
    DataUtils.insReviewsStmt:reset()
    local id = math.floor( v[ "id" ] )
    DataUtils.insReviewsStmt:bind_values( id, v[ "author" ], v[ "state" ] )
    if DataUtils.insReviewsStmt:step() ~= sqlite3.DONE then
       -- todo record the error
       goto END
    end
    DataUtils.insReviewsStmt:reset()

    for kc, vc in pairs( v[ "changes" ] ) do
      DataUtils.insChangesStmt:bind_values( id, math.floor( vc ) )
      if DataUtils.insChangesStmt:step() ~= sqlite3.DONE then
         -- todo record the error
         goto END
      end
      DataUtils.insChangesStmt:reset()
    end

  end

  DataUtils.db:exec "COMMIT;"

::END::

  DataUtils.insReviewsStmt:reset()
  DataUtils.insChangesStmt:reset()
end

function DataUtils.select( change )
  local changes = {}
  local id
  local state

  DataUtils.SlcChangesStmt:bind_values( change )

  for row in DataUtils.SlcChangesStmt:nrows() do
    id = row.id
    table.insert( changes, row.cl )
  end

  DataUtils.SlcChangesStmt:reset()

  DataUtils.SlcReviewsStmt:bind_values( id )

  for row in DataUtils.SlcReviewsStmt:nrows() do
    state = row.state
  end

  DataUtils.SlcReviewsStmt:reset()

  return id, state, changes
end

return DataUtils