insidethebox/mods/db/init.lua

--[[

  ITB (insidethebox) minetest game - Copyright (C) 2017-2018 sofar & nore

  This library is free software; you can redistribute it and/or
  modify it under the terms of the GNU Lesser General Public License
  as published by the Free Software Foundation; either version 2.1
  of the License, or (at your option) any later version.

  This library is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  Lesser General Public License for more details.

  You should have received a copy of the GNU Lesser General Public
  License along with this library; if not, write to the Free
  Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
  MA 02111-1307 USA

]]--

--[[

  sqlite backend for insidethebox

  This backend stores various persistent world data bits that are
  not properly stored by the minetest engine. This mod provides both
  persistent and performant data storage for the itb game until a
  better solution exists.

  Major data storage parts in this db:

  - players, player scores
    No player metadata storage exists currently. Only HP and air
    are stored in player data. We lack storage for things like
    skin choice, stamina, RPG points and skills, etc.

  - box data

--]]

-- gratuitous banner
minetest.log("action", "         _|_|_|                      _|        _|")
minetest.log("action", "           _|    _|_|_|      _|_|_|        _|_|_|    _|_|")
minetest.log("action", "           _|    _|    _|  _|_|      _|  _|    _|  _|_|_|_|")
minetest.log("action", "           _|    _|    _|      _|_|  _|  _|    _|  _|")
minetest.log("action", "         _|_|_|  _|    _|  _|_|_|    _|    _|_|_|    _|_|_|")
minetest.log("action", "")
minetest.log("action", "   _|      _|                          _|")
minetest.log("action", " _|_|_|_|  _|_|_|      _|_|            _|_|_|      _|_|    _|    _|")
minetest.log("action", "   _|      _|    _|  _|_|_|_|          _|    _|  _|    _|    _|_|")
minetest.log("action", "   _|      _|    _|  _|                _|    _|  _|    _|  _|    _|")
minetest.log("action", "     _|_|  _|    _|    _|_|_|          _|_|_|      _|_|    _|    _|")

local world_path = minetest.get_worldpath()

local insecure_env = minetest.request_insecure_environment()
assert(insecure_env, "add the \"db\" mode to secure.trusted_mods in minetest.conf")
local sqlite3 = insecure_env.require("lsqlite3")
assert(sqlite3, "please install the lsqlite3 lua plugin using e.g. luarocks")
local itb_db = sqlite3.open(world_path .. "/itb.sqlite")
assert(itb_db, "lsqlite3 not available or non-functional.")

-- we require these tables to exist:
assert(itb_db:exec[[
	CREATE TABLE box (id INTEGER PRIMARY KEY, data BLOB);
	CREATE TABLE box_meta (id INTEGER PRIMARY KEY, type INTEGER, meta BLOB);
	CREATE TABLE series (id INTEGER PRIMARY KEY, name TEXT, meta BLOB);
	CREATE TABLE series_box (series_id INTEGER, box_id INTEGER, box_order INTEGER);
	CREATE TABLE player (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, meta BLOB);
	CREATE TABLE points (player_id INTEGER, score REAL, type TEXT, box_id INTEGER);
]])

-- table structure and content
--[[

  box table: Contain the storage of the box content (not metadata) so all blocks. (one-to-one)
    - id: box identifier
    - data: box contents (nodes)

  series: Contains existing series
    - id: series ID
    - name: name of the series
    - meta: misc data

  series_box: maps boxes into series (many-to-many)
    - series_id: series ID
    - box_id: box ID
	  - box_order: series are sorted by increasing order

  player: player info
    - id: player number
    - name: player name
    - meta: random player data bits

  score: player and box score data
    - player_id: player id
    - score: score value
    - type: score type (e.g. reviewed a box)
    - box: associated box ID
--]]

db = {}

function db.player_get_id(name)
	local stmt = itb_db:prepare[[
		SELECT id FROM player WHERE name = :name
	]]
	stmt:bind_names{name = name}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		return row.id
	end

	-- new player
	stmt = itb_db:prepare[[
		INSERT INTO player (name, meta) VALUES (:name, :meta)
	]]
	stmt:bind_names{name = name, meta = minetest.write_json({series_progress = {}})}
	local r = stmt:step()
	stmt:finalize()
	if not r then
		minetest.log("error", "db.player_get_id(" .. name .."): unable to insert new row")
		return nil
	end

	-- now fetch it again
	stmt = itb_db:prepare[[
		SELECT id FROM player WHERE name = :name
	]]
	stmt:bind_names{name = name}
	it, state = stmt:nrows()
	row = it(state)
	stmt:finalize()
	if row then
		minetest.log("action", name .. " inserted successfully as player id " .. row.id)
		return row.id
	end

	minetest.log("error", "db.player_get_id(" .. name .. "): inserted but not found in db")
	return nil
end

function db.player_get_name(player_id)
	local stmt = itb_db:prepare[[
		SELECT name FROM player WHERE id = :player_id
	]]
	stmt:bind_names{player_id = player_id}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		return row.name
	end

	minetest.log("warning", "db.player_get_name(" .. player_id .. "): no such player in db")
	return nil
end

function db.player_points(player_id, box_id, type, values, limit)
	if limit > 0 then
		-- remove previous entries
		local stmt = itb_db:prepare("DELETE FROM points WHERE player_id=" .. player_id .. " AND " ..
				"box_id=" .. box_id .. " AND type='" .. type .. "'")
		local r = stmt:step()
		stmt:finalize()
		if not r then
			minetest.log("error", "db.player_points(" .. type .. "): error deleting")
			return false
		end

		-- add new entries
		local i, v = next(values, nil)
		while i and i <= limit do
			stmt = itb_db:prepare("INSERT INTO POINTS (player_id, box_id, type, score) VALUES" ..
				"(" .. player_id .. ", " .. box_id .. ", '" .. type .. "', " ..
				v .. ")")
			r = stmt:step()
			stmt:finalize()
			if not r then
				minetest.log("error", "db.player_points(" .. type .. "): error inserting")
				return false
			end
			i, v = next(values, i)
		end
	else
		local _, score = next(values)
		local stmt = itb_db:prepare[[
			INSERT INTO points (player_id, box_id, type, score) VALUES
				(:player_id, :box_id, :type, :score)
			]]
		stmt:bind_names{player_id = player_id, box_id = box_id,
			type = type, score = score}
		local r = stmt:step()
		stmt:finalize()
		if not r then
			minetest.log("error", "db.player_points(" .. type .. "): error adding")
			return false
		end
	end
	return true
end

function db.player_get_completed_boxes(player_id)
	local stmt = itb_db:prepare[[
	        SELECT DISTINCT box_id FROM points WHERE type='time' AND player_id = :player_id
	]]
	stmt:bind_names{player_id = player_id}
	local boxes = {}
	for row in stmt:nrows() do
		boxes[row.box_id] = 1
	end

	return boxes
end

function db.player_get_points(filtertbl)
	if not filtertbl then
		filtertbl = {}
	end
	assert(type(filtertbl) == "table")

	local q = "SELECT player_id, box_id, type, score from POINTS"
	local f = {}
	for k, v in pairs(filtertbl) do
		if type(v) == "string" then
			f[#f + 1] = k .. " = '" .. v .. "'"
		else
			f[#f + 1] = k .. " = " .. v
		end
	end
	if #f > 0 then
		q = q .. " WHERE " .. table.concat(f, " AND ")
	end

	local stmt = itb_db:prepare(q)
	local points = {}
	local points_count = 1
	for row in stmt:nrows() do
		points[points_count] = {
			player_id = row.player_id,
			box_id = row.box_id,
			type = row.type,
			score = row.score
		}
		points_count = points_count + 1
	end

	stmt:finalize()

	return points
end

function db.player_get_meta(name)
	local stmt = itb_db:prepare[[
		SELECT meta FROM player WHERE name = :name
	]]
	stmt:bind_names{name = name}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		local meta = {}
		if row.meta then
			meta = minetest.parse_json(row.meta) or {}
		end
		if not meta.series_progress then
			meta.series_progress = {}
		end
		return meta
	end

	-- trigger generation of ID record
	if not db.player_get_id(name) then
		minetest.log("error", "db.player_get_meta(" .. name .. "): no such player in db, unable to insert")
	end
	return {series_progress = {}}
end

function db.player_set_meta(name, meta)
	assert(name ~= "")
	local stmt = itb_db:prepare[[
		UPDATE player SET meta = :meta WHERE name = :name
	]]
	stmt:bind_names{name = name, meta = minetest.write_json(meta)}
	local r = stmt:step()
	stmt:finalize()
	if not r then
		minetest.log("error", "db.player_set_meta(" .. name .. "): error writing")
		return false
	else
		return true
	end
end

function db.player_get_players()
	local stmt = itb_db:prepare[[
		SELECT id, name FROM player ORDER BY id
	]]

	local players = {}
	local players_count = 1
	for row in stmt:nrows() do
		players[players_count] = {player_id = row.id, name = row.name}
		players_count = players_count + 1
	end

	stmt:finalize()
	return players
end

function db.player_get_series_boxes(player_id, series_id)
	local stmt = itb_db:prepare[[
		SELECT box_id FROM points WHERE player_id=:player_id AND type="time" AND box_id
			IN (SELECT box_id FROM series_box WHERE series_id=:series_id)
	]]
	stmt:bind_names{
		player_id = player_id,
		series_id = series_id
	}
	local completed = {}
	local completed_count = 1
	for row in stmt:nrows() do
		completed[completed_count] = row.box_id
		completed_count = completed_count + 1
	end

	stmt:finalize()

	return completed
end

function db.series_create(name)
	assert(name ~= "")
	local stmt = itb_db:prepare[[
		INSERT INTO series (name, meta) VALUES(:name, :meta)
	]]
	stmt:bind_names{
		name = name,
		meta = minetest.write_json({
			meta = {}
		})
	}
	local r = stmt:step()
	stmt:finalize()

	if not r then
		minetest.log("error", "db.series_create(" .. name .. "): error writing")
		return nil
	end

	local lastrow = itb_db:last_insert_rowid()

	if lastrow ~= 0 then
		local stmt2 = itb_db:prepare[[
			SELECT id FROM series WHERE rowid = :lastrow
		]]
		stmt2:bind_names{lastrow = lastrow}
		local it, state = stmt2:nrows()
		local row = it(state)
		stmt2:finalize()
		if row then
			return row.id
		end
	end

	minetest.log("error", "db.series_create(" .. name .. "): failed")
	return nil
end

function db.series_destroy(series_id)
	assert(series_id)

	local stmt = itb_db:prepare[[
		DELETE FROM series WHERE id = :series_id
	]]
	stmt:bind_names{series_id = series_id}
	local r = stmt:step()
	stmt:finalize()

	if not r then
		minetest.log("error", "db.series_remove(" .. series_id .. "): error")
		return nil
	end

	return true
end

function db.series_get_series()
	local stmt = itb_db:prepare[[
		SELECT id, name FROM series ORDER BY id
	]]

	local series = {}
	local series_count = 1
	for row in stmt:nrows() do
		series[series_count] = {id = row.id, name = row.name}
		series_count = series_count + 1
	end

	stmt:finalize()
	return series
end

db.SEQUENTIAL_TYPE = 0
db.RANDOM_ACCESS_TYPE = 1

function db.series_get_meta(series_id)
	local stmt = itb_db:prepare[[
		SELECT name, meta FROM series WHERE id = :series_id
	]]
	stmt:bind_names{series_id = series_id}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		local result = {
			name = row.name,
			meta = minetest.parse_json(row.meta) or {},
		}
		if not result.meta.type then
			result.meta.type = db.SEQUENTIAL_TYPE
		end
		return result
	end

	minetest.log("warning", "db.series_get_meta(" .. series_id .. "): no such series meta for that id")
	return nil
end

function db.series_set_meta(series_id, meta)
	local stmt = itb_db:prepare[[
		REPLACE INTO series (id, name, meta) VALUES(:series_id, :name, :meta)
	]]
	stmt:bind_names{
		series_id = series_id,
		name = meta.name,
		meta = minetest.write_json(meta.meta),
	}
	local r = stmt:step()
	stmt:finalize()
	if not r then
		minetest.log("error", "db.series_set_meta(" .. series_id .. "): error writing")
		return false
	else
		return true
	end
end

function db.series_get_boxes(series_id)
	local stmt = itb_db:prepare[[
		SELECT box_id FROM series_box WHERE series_id = :series_id ORDER BY box_order
	]]
	stmt:bind_names{series_id = series_id}

	local boxes = {}
	local boxes_index = 1
	for row in stmt:nrows() do
		boxes[boxes_index] = row.box_id
		boxes_index = boxes_index + 1
	end

	stmt:finalize()
	return boxes
end

function db.series_insert_box(series_id, box_id, box_order)
	local stmt = itb_db:prepare[[
		INSERT INTO series_box (series_id, box_id, box_order) VALUES(:series_id, :box_id, :box_order)
	]]
	stmt:bind_names{
		series_id = series_id,
		box_id = box_id,
		box_order = box_order,
	}
	local r = stmt:step()
	stmt:finalize()
	if not r then
		minetest.log("error", "db.series_insert_box(" .. series_id .. ", " .. box_id .."): error writing")
		return false
	else
		return true
	end
end

function db.series_add_at_end(series_id, box_id)
	local stmt = itb_db:prepare[[
		SELECT MAX(box_order) FROM series_box WHERE series_id = :series_id
	]]
	stmt:bind_names{series_id = series_id}
	local mx = 0
	for row in stmt:nrows() do
		mx = row["MAX(box_order)"] or 0
	end
	stmt:finalize()
	return db.series_insert_box(series_id, box_id, mx + 1)
end

function db.series_delete_box(series_id, box_id)
	local stmt = itb_db:prepare[[
		DELETE FROM series_box WHERE series_id = :series_id AND box_id = :box_id
	]]
	stmt:bind_names{
		series_id = series_id,
		box_id = box_id,
	}
	local r = stmt:step()
	stmt:finalize()
	if not r then
		minetest.log("error", "db.series_delete_box(" .. series_id .. ", " .. box_id .. "): error writing")
		return false
	else
		return true
	end
end

function db.box_get_data(box_id)
	local stmt = itb_db:prepare[[
		SELECT data FROM box WHERE id = :box_id
	]]
	stmt:bind_names{box_id = box_id}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		return row.data
	end

	minetest.log("error", "db.box_get_data(" .. box_id .."): no such box")
	return nil

end

function db.box_set_data(box_id, data)
	local stmt = itb_db:prepare[[
		REPLACE INTO box (id, data) VALUES(:box_id, :box_data)
	]]
	stmt:bind_names{box_id = box_id, box_data = data}
	local r = stmt:step()
	stmt:finalize()
	if not r then
		minetest.log("error", "db.box_set_data(" .. box_id .."): error writing")
		return false
	else
		return true
	end
end

db.BOX_TYPE = 0
db.ENTRY_TYPE = 1
db.EXIT_TYPE = 2

db.STATUS_EDITING = 0
db.STATUS_SUBMITTED = 1
db.STATUS_ACCEPTED = 2

local last_box_id = -1
for row in itb_db:nrows[[ SELECT MAX(id) FROM box_meta ]] do
	last_box_id = row["MAX(id)"] or 0 --otherwise crashes on first startup?
end

function db.get_last_box_id()
	return last_box_id
end

function db.box_exists(box_id)
	local stmt = itb_db:prepare[[
		SELECT id FROM box_meta WHERE id = :box_id
	]]
	stmt:bind_names{box_id = box_id}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		return true
	else
		return false
	end
end

function db.box_get_meta(box_id)
	local stmt = itb_db:prepare[[
		SELECT type, meta FROM box_meta WHERE id = :box_id
	]]
	stmt:bind_names{box_id = box_id}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		local result = {
			type = row.type,
			meta = minetest.parse_json(row.meta),
		}
		if not result.meta.status then
			result.meta.status = db.STATUS_EDITING
		end
		if result.type == db.BOX_TYPE and not result.meta.num_items then
			result.meta.num_items = 1
		end
		if result.type == db.BOX_TYPE and (not result.meta.time) then
			result.meta.num_completed_players = 0
			result.meta.time = {
				overall_best = 1e100,
				average_first = 0,
				average_best = 0,
			}
			result.meta.deaths = {
				overall_best = 1e100,
				average_first = 0,
				average_best = 0,
			}
			result.meta.damage = {
				overall_best = 1e100,
				average_first = 0,
				average_best = 0,
			}
		end
		if result.type == db.BOX_TYPE and not result.meta.box_name then
			result.meta.box_name = "(No name)"
		end
		if result.type == db.BOX_TYPE and not result.meta.skybox then
			result.meta.skybox = 0
		end
		if result.type == db.BOX_TYPE and not result.meta.build_time then
			result.meta.builder = ""
			result.meta.build_time = 0
		end
		if result.type == db.EXIT_TYPE and not result.meta.signs_to_update then
			result.meta.signs_to_update = {}
		end
		if result.type == db.EXIT_TYPE and not result.meta.star_positions then
			result.meta.star_positions = {}
		end
		if result.type == db.EXIT_TYPE and not result.meta.category_positions then
			result.meta.category_positions = {}
		end
		return result
	end

	minetest.log("error", "db.box_get_data(" .. box_id .. "): no such box meta")
	return nil
end

function db.box_get_num_completed_players(box_id)
	local stmt = itb_db:prepare[[
		select count(distinct player_id) from points where box_id=:box_id and type="time"
	]]
	stmt:bind_names{box_id = box_id}
	local it, state = stmt:nrows()
	local row = it(state)
	stmt:finalize()
	if row then
		return row.count
	end

	minetest.log("error", "db.box_get_num_completed_players(" .. box_id .. "): failed")
	return nil
end

function db.box_set_meta(box_id, meta)
	local stmt = itb_db:prepare[[
		REPLACE INTO box_meta (id, type, meta) VALUES(:box_id, :box_type, :box_meta)
	]]
	stmt:bind_names{
		box_id = box_id,
		box_type = meta.type,
		box_meta = minetest.write_json(meta.meta),
	}
	local r = stmt:step()
	stmt:finalize()
	last_box_id = math.max(last_box_id, box_id)
	if not r then
		minetest.log("error", "db.box_set_data(" .. box_id .. "): error writing")
		return false
	else
		return true
	end
end

function db.series_get_boxes_not_in(series_id)
	local stmt
	if series_id then
		stmt = itb_db:prepare[[
			SELECT box_id FROM series_box WHERE series_id != :series_id
			UNION
			SELECT id FROM box WHERE id NOT IN (SELECT box_id FROM series_box)
		]]
		stmt:bind_names{series_id = series_id}
	else
		stmt = itb_db:prepare[[
			SELECT id FROM box WHERE id NOT IN (SELECT box_id FROM series_box)
		]]
	end

	local boxes = {}
	local boxes_index = 1
	for row in stmt:nrows() do
		if row.id then
			boxes[boxes_index] = row.id
		elseif row.box_id then
			boxes[boxes_index] = row.box_id
		end
		boxes_index = boxes_index + 1
	end

	stmt:finalize()
	return boxes
end

function db.shutdown()
	itb_db:close()
end