path: root/weblog-model.lua

local mymodule = {}

-- Load libraries
modelfunctions = require("modelfunctions")
fs = require("acf.fs")
format = require("acf.format")
validator = require("acf.validator")
luasql = require("luasql.postgres")
posix = require("posix")
subprocess = require("subprocess")

local DatabaseName = "webproxylog"
local DatabaseOwner = "weblogowner"
local DatabaseUser = "webloguser"

local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin "
local env
local con
local configfile = "/etc/weblog/weblog.conf"
local configcontent = fs.read_file(configfile) or ""
local config = format.parse_ini_file(configcontent, "") or {}
local goodwordslist = "/etc/weblog/goodwords"
local goodwords
local badwordslist = "/etc/weblog/badwords"
local badwords
local ignorewordslist = "/etc/weblog/ignorewords"
local ignorewords
local files = {badwordslist, goodwordslist, ignorewordslist, configfile}

local database_creation_script = {
	"CREATE TABLE dbhistlog (logdatetime timestamp(3) without time zone NOT NULL, msgtext text)",
	"CREATE TABLE pubweblog(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(3) without time zone NOT NULL, uri text NOT NULL, bytes bigint NOT NULL, reason text, score integer, shortreason text, badyesno int, deniedyesno int, bypassyesno int, wordloc text, goodwordloc text, selected boolean, id serial)",
	"CREATE TABLE pubweblog_history(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(3) without time zone NOT NULL, uri text NOT NULL, bytes bigint NOT NULL, reason text, score integer, shortreason text, badyesno int, deniedyesno int, bypassyesno int, wordloc text, goodwordloc text, selected boolean, id int)",
	"CREATE TABLE weblog(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(3) without time zone NOT NULL, uri text NOT NULL, bytes bigint NOT NULL, reason text, score integer, shortreason text, badyesno int, deniedyesno int, bypassyesno int, wordloc text, goodwordloc text)",
	"CREATE TABLE source (sourcename character varying(40) NOT NULL, method character varying(100) NOT NULL, userid character varying(32), passwd character varying(255), source character varying(255) NOT NULL, tzislocal boolean, enabled boolean)",
	"CREATE TABLE usagestat (sourcename character varying(40) NOT NULL, date timestamp(0) without time zone NOT NULL, numrequest integer, numblock integer)",
	"ALTER TABLE ONLY source ADD CONSTRAINT source_pkey PRIMARY KEY (sourcename)",
	"CREATE INDEX dbhistlogdatetimeidx ON dbhistlog USING btree (logdatetime)",
	"CREATE INDEX pubweblogclientdateidx ON pubweblog USING btree (logdatetime, clientuserid)",
	"CREATE INDEX pubweblogclientuserididx ON pubweblog USING btree (clientuserid)",
	"CREATE INDEX pubwebloglogdatetimeidx ON pubweblog USING btree (logdatetime)",
	"CREATE INDEX pubweblog_historyclientdateidx ON pubweblog_history USING btree (logdatetime, clientuserid)",
	"CREATE INDEX pubweblog_historyclientuserididx ON pubweblog_history USING btree (clientuserid)",
	"CREATE INDEX pubweblog_historylogdatetimeidx ON pubweblog_history USING btree (logdatetime)",
	"GRANT SELECT ON dbhistlog TO "..DatabaseUser,
	"GRANT SELECT ON pubweblog TO "..DatabaseUser,
	"GRANT SELECT ON pubweblog_history TO "..DatabaseUser,
	"GRANT SELECT, UPDATE, INSERT, DELETE ON source TO "..DatabaseUser,
	"GRANT SELECT ON usagestat TO "..DatabaseUser,
}

-- ################################################################################
-- DATABASE FUNCTIONS

local function assert (v, m)
	if not v then
		m = m or "Assertion failed!"
		error(m, 0)
	end
	return v, m
end

-- Escape special characters in sql statements and truncate to length
local escape = function(sql, length)
	sql = sql or ""
	if length then sql = string.sub(sql, 1, length) end
	return con:escape(sql)
end

-- List the postgres databases on this system
local listdatabases = function()
	local dbs = {}
	local result = modelfunctions.run_executable({"psql", "-U", "postgres", "-tl"})
	for line in string.gmatch(result, "[^\n]+") do
		dbs[#dbs+1] = string.match(line, "^ (%S+)")
	end
	return dbs
end

-- Create the necessary database
local createdatabase = function(password)
	local result = {}

	-- First, create the users
	local cmd = "CREATE USER "..DatabaseOwner.." WITH PASSWORD '"..password.."'"
	local cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)

	cmd = "CREATE USER "..DatabaseUser
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)

	-- Create the database
	cmd = "CREATE DATABASE "..DatabaseName.." WITH OWNER "..DatabaseOwner
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)

	return table.concat(result, "\n")
end

-- Delete the database and roles
local deletedatabase = function()
	local result = {}

	local cmd = "DROP DATABASE "..DatabaseName
	local cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)

	cmd = "DROP ROLE "..DatabaseUser
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)

	cmd = "DROP ROLE "..DatabaseOwner
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)

	return table.concat(result, "\n")
end

-- Run an SQL script
local runSQLscript = function(filename)
	local result, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-f", filename, DatabaseName}, true)
	return errtxt or result
end

-- Create the database and tables
-- pg_dump -U postgres -c webproxylog > makeweblog.postgres
--runSQLscript("/root/work/weblog/makeweblog.postgres")

local databaseconnect = function(username, password)
	if not con then
		-- create environment object
		env = assert (luasql.postgres())
		-- connect to data source
		con = assert (env:connect(DatabaseName, username, password))
	end
end

local databasedisconnect = function()
	if env then
		env:close()
		env = nil
	end
	if con then
		con:close()
		con = nil
	end
end

local logme = function(message)
	local sql = string.format("INSERT INTO dbhistlog VALUES ('now', '%s')", escape(message))
	local res = assert (con:execute(sql))
end

local listhistorylogentries = function()
	local entries = {}
	-- retrieve a cursor
	cur = assert (con:execute"SELECT logdatetime, msgtext from dbhistlog ORDER BY logdatetime")
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {logdatetime = row.logdatetime, msgtext = row.msgtext}
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local importlogentry = function(entry, sourcename)
	if entry then
		local sql = string.format("INSERT INTO weblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')",
			escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(),
			escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score or "0"), escape(entry.shortreason), escape(entry.badyesno or "0"), escape(entry.deniedyesno or "0"), escape(entry.bypassyesno or "0"), escape(entry.wordloc), escape(entry.goodwordloc))
		local res = assert (con:execute(sql))
	end
end

local listsourceentries = function(sourcename)
	local sources = {}
	-- retrieve a cursor
	local sql = "SELECT sourcename, method, userid, passwd, source, tzislocal, enabled FROM source"
	if sourcename then
		sql = sql .. " WHERE sourcename='" .. escape(sourcename) .. "'"
	end
	sql = sql .. " ORDER BY sourcename"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		row.tzislocal = (row.tzislocal == "t")
		row.enabled = (row.enabled == "t")
		sources[#sources+1] = row
		row = cur:fetch ({}, "a")
	end
	cur:close()
	return sources
end

local importsourceentry = function(source)
	local sql = string.format("INSERT INTO source VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')",
		escape(source.sourcename), escape(source.method), escape(source.userid), escape(source.passwd),
		escape(source.source), escape(tostring(source.tzislocal):upper()), escape(tostring(source.enabled):upper()))
	local res = assert (con:execute(sql))
	return res
end

local updatesourceentry = function(source)
	local sql = string.format("UPDATE source SET method='%s', userid='%s', passwd='%s', source='%s', tzislocal='%s', enabled='%s' WHERE sourcename='%s'",
		escape(source.method), escape(source.userid), escape(source.passwd), escape(source.source),
		escape(tostring(source.tzislocal):upper()), escape(tostring(source.enabled):upper()),
		escape(source.sourcename))
	local res = assert (con:execute(sql))
	return res
end

local deletesourceentry = function(sourcename)
	local sql = string.format("DELETE FROM source WHERE sourcename='%s'", escape(sourcename))
	local res = assert (con:execute(sql))
	return res
end

-- Generate usage statistics from weblog and blocklog
local updateusagestats = function()
	-- update the usagestat table from weblog
	-- (will result in multiple rows where logs rotated on partial hours)
	local sql = "insert into usagestat select weblog.sourcename, " ..
		"date_trunc('hour', weblog.logdatetime) as date, " ..
		"count(*), SUM(deniedyesno) from weblog group by sourcename,date"
	local res = assert (con:execute(sql))
end

-- Move weblog into pubweblog, and truncate weblog
local importpubweblog = function()
	local sql = "ANALYZE"
	res = assert (con:execute(sql))

	-- Move weblog to pubweblog
	sql= "insert into pubweblog select * from weblog"
	res = assert (con:execute(sql))
	logme("importpubweblog imported " .. res .. " new rows into database.")

	-- Truncate the staging table
	assert (con:execute("truncate weblog"))
	logme("truncated staging table")
end

-- Delete useage stats from more than a year ago
local groomusagestat = function()
	local res = assert (con:execute("delete from usagestat where " ..
		"date < (now() - INTERVAL '1 year')"))
	logme("removed " .. res .. " old usage status lines")
end

-- Delete history log information from more than a month ago
local groomdbhistlog = function()
	local res = assert (con:execute("delete from dbhistlog where " ..
		"logdatetime < (now() - INTERVAL '1 month')"))
	logme("removed " .. res .. " old dbhistlog lines")
end

-- Delete old junk from pub tables
local groompublogs = function()
	local purgedays = config.purgedays or 30

	local now = os.time()

	local temp = os.date("%Y-%m-%d %H:%M:%S", now - purgedays*86400)
	logme("Purgedate is " .. temp .. ". Nothing will exist in pubweblog from before purgedate.")

	-- Move flagged records to histoy and then purge anything older than purgedate
	sql = "Insert into pubweblog_history select * from pubweblog where logdatetime < '" .. escape(temp) .."' and (badyesno > 0 or deniedyesno > 0 or bypassyesno > 0 or selected = 'true')"
	res = assert (con:execute(sql))
	logme("Moved " .. res .. " old records to history")

	sql = "Delete from pubweblog where logdatetime < '" .. escape(temp) .."'"
	res = assert (con:execute(sql))
	logme("Deleted " .. res .. " old records from pubweblog")

	sql = "delete from pubweblog_history where logdatetime < (now() - INTERVAL '1 year')"
	res = assert (con:execute(sql))
	logme("Deleted " .. res .. " old records from pubweblog_history")
end

local generatewhereclause = function(clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, selected, sourcename)
	local sql = ""
	local where = {}
	if clientuserid and clientuserid ~= "" then
		where[#where+1] = "clientuserid LIKE '%"..escape(clientuserid).."%'"
	end
	if starttime and starttime ~= "" then
		where[#where+1] = "logdatetime >= '"..escape(starttime).."'"
	end
	if endtime and endtime ~= "" then
		where[#where+1] = "logdatetime <= '"..escape(endtime).."'"
	end
	if clientip and clientip ~= "" then
		where[#where+1] = "clientip = '"..escape(clientip).."'"
	end
	if badyesno then
		where[#where+1] = "badyesno = '1'"
	end
	if deniedyesno then
		where[#where+1] = "deniedyesno = '1'"
	end
	if bypassyesno then
		where[#where+1] = "bypassyesno = '1'"
	end
	if score and score ~= "" then
		where[#where+1] = "score >= '"..escape(score).."'"
	end
	if urisearch and urisearch ~= "" then
	    	where[#where+1] = "lower(uri) LIKE '%"..escape(urisearch).."%'"
	end
	if selected then
		where[#where+1] = "selected = 'true'"
	end
	if sourcename and #sourcename > 0 then
		tmp = {}
		for i,s in pairs(sourcename) do
			tmp[#tmp+1] = "sourcename = '"..escape(s).."'"
		end
		where[#where+1] = "("..table.concat(tmp, " OR ")..")"
	end

	if #where > 0 then
		sql = " WHERE " .. table.concat(where, " AND ")
	end

	return sql
end

local listlogentries = function(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected, sourcename)
	local entries = {}
	-- retrieve a cursor
	local sql = "SELECT * FROM "..escape(activelog)
	sql = sql .. generatewhereclause(clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, selected, sourcename)
	sql = sql .. " ORDER BY "..escape(sortby)
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		if config.shorturi == "true" then
			shorturi=string.gsub(row.uri, "[;?].*", "...")
		end
		entries[#entries+1] = {sourcename=row.sourcename, clientip=row.clientip, clientuserid=row.clientuserid, logdatetime=row.logdatetime, uri=row.uri, shorturi=shorturi, bytes=row.bytes, reason=row.reason, score=row.score, shortreason=row.shortreason, badyesno=row.badyesno, deniedyesno=row.deniedyesno, bypassyesno=row.bypassyesno, wordloc=row.wordloc, id=row.id, selected=row.selected }
		if (config.shortreason ~= "true") then
			entries[#entries].shortreason = nil
		end
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local groupflaggedlogentries = function(starttime, endtime, groupby)
	groupby = groupby or "clientuserid"
	local entries = {}
	-- retrieve a cursor
	local sql = "SELECT "..escape(groupby)..", COUNT(*) as numrecords, SUM(CASE WHEN (bypassyesno > '0' OR deniedyesno > '0' OR badyesno > '0') THEN 1 ELSE 0 END) as numflagged, sum(score) AS numhits, sum(CASE WHEN deniedyesno > '0' THEN 1 ELSE 0 END) AS numdenied, sum(CASE WHEN bypassyesno > '0' THEN 1 ELSE 0 END) AS numbypassed, max(score) as maxscore from pubweblog"
	sql = sql .. generatewhereclause(nil, starttime, endtime)
	sql = sql .. " GROUP BY " ..escape(groupby).. " ORDER BY numflagged DESC"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {numrecords=row.numrecords, numflagged=row.numflagged, numhits=row.numhits, numdenied=row.numdenied, numbypassed=row.numbypassed, maxscore=row.maxscore}
		entries[#entries][groupby] = row[groupby]
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local listusagestats = function()
	local entries = {}
	-- retrieve a cursor
	local sql = "SELECT sourcename, date, sum(numrequest) AS numrequest, sum(numblock) AS numblock " ..
		"FROM usagestat GROUP BY sourcename, date ORDER BY date, sourcename"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {sourcename=row.sourcename, date=row.date, numrequest=row.numrequest, numblock=row.numblock}
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local testdatabaseentry = function(datatype, value)
	local success = true
	local errtxt
	local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." DEFAULT '"..escape(value).."' ) ON COMMIT DROP"
	local res, err = pcall(function()
		assert (con:execute(sql))
	end)
	if not res then
		success = false
		errtxt = string.gsub(err or "", "\n.*", "")
	end
	return success, errtxt
end

local convertdatabaseentry = function(datatype, value)
	local success = true
	local errtxt
	local result = value
	local res, err = pcall(function()
		local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." )"
		assert (con:execute(sql))
		sql = "INSERT INTO testing VALUES ('"..escape(value).."')"
		assert (con:execute(sql))
		sql = "SELECT * FROM testing"
		local cur = assert (con:execute(sql))
		local row = cur:fetch ({}, "a")
		if row then
			result = row.test
		end
	end)
	if not res then
		success = false
		errtxt = string.gsub(err or "", "\n.*", "")
	end
	local res, err = pcall(function()
		local sql = "DROP TABLE testing"
		assert (con:execute(sql))
	end)
	return success, errtxt, result
end

local printtableentries = function(tablename)
	-- retrieve a cursor
	local count = 0
	cur = assert (con:execute("SELECT * from "..escape(tablename)))
	-- print all rows, the rows will be indexed by field names
	row = cur:fetch ({}, "a")
	while row do
		count = count + 1
		for name,val in pairs(row) do
			mymodule.logevent(name.." = "..val..", ")
		end
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	mymodule.logevent("Table "..tablename.." contains "..count.." rows")
end

-- ################################################################################
-- LOG FILE FUNCTIONS

local function checkwords(logentry)
	local goodwordloc={}
	local badwordloc={}
	local wrdcnt=0
	local isbad=0

	--check for ignored records first
	for i,thisline in ipairs(ignorewords) do
		if not thisline then
			break
		end

		-- ignore blank lines
		if string.find(thisline, "%S") then
			_,instcnt = string.lower(logentry.URL):gsub(format.escapemagiccharacters(thisline), " ")
			if instcnt ~= 0 then
				logentry.ignoreme = true
				--logme("ignoring...")
				break
			end
		end
	end

	if not logentry.ignoreme then
		--proceed with record analysis
		for i,thisline in ipairs(badwords) do
			if not thisline then
				break
			end

			-- ignore blank lines
			if string.find(thisline, "%S") then
				_,instcnt = string.lower(logentry.URL):gsub(format.escapemagiccharacters(thisline), " ")
				if instcnt ~= 0 then
					-- logme("instcnt =  "..instcnt)
					isbad=1
					wrdcnt= wrdcnt + instcnt
					badwordloc[#badwordloc+1] = thisline
				end
			end
		end

		--check for DansGuardian actions
		if (logentry.reason and logentry.reason ~= "") then
			if string.find(logentry.reason,"DENIED") then
				-- logme("*Denied*")
				logentry.deniedyesno=1
			elseif string.find(logentry.URL,"GBYPASS") then
				-- logme("GBYPASS")
				logentry.bypassyesno=1
			elseif string.find(logentry.reason,"OVERRIDE") then
				-- logme("*OVERRIDE*")
				logentry.bypassyesno=1
			end
		end

		--check for Squark actions
		if (logentry.squarkaction and logentry.squarkaction ~= "") then
			--logme("squarkaction="..logentry.squarkaction)
			if string.find(logentry.squarkaction, "blocked") then
				logentry.deniedyesno=1
			elseif string.find(logentry.squarkaction,"overridden") then
				logentry.bypassyesno=1
			end
		end

		--check for Squark category
		if (logentry.squarkcategory and logentry.squarkcategory ~= "") then
			logentry.reason = logentry.squarkcategory
			logentry.shortreason = logentry.squarkcategory
		end

		for i,goodline in ipairs(goodwords) do
			if not goodline then
				break
			end

			-- ignore blank lines
			if string.find(goodline, "%S") then
				_,instcnt = string.lower(logentry.URL):gsub(format.escapemagiccharacters(goodline), " ")
				--if string.find(logentry.URL,goodline) then
				if instcnt ~= 0 then
					if wrdcnt >= instcnt then
						wrdcnt = wrdcnt - instcnt
					else
						wrdcnt = 0
					end
					goodwordloc[#goodwordloc+1] = goodline
				end
			end
		end
	end
	-- Reset bad to reduce number of bad hits if score is zero
	-- if wrdcnt == 0 then
	-- isbad=0
	-- end

	logentry.score=wrdcnt
	logentry.badyesno=isbad
	logentry.wordloc=table.concat(badwordloc,"|")
	logentry.gwordloc=table.concat(goodwordloc,"|")
end

local function parsesquidlog(line)
	-- Format of squid log (space separated):
	-- time elapsed remotehost code/status bytes method URL rfc931 peerstatus/peerhost ? squarkcategory/squarkaction
	local words = {}

	for word in string.gmatch(line, "%S+") do
		words[#words+1] = word
	end

	local logentry = {logdatetime=words[1],
		elapsed=words[2],
		clientip=words[3],
		code=string.match(words[4] or "", "^[^/]*"),
		status=string.match(words[4] or "", "[^/]*$"),
		bytes=words[5],
		method=words[6],
		URL=words[7],
		clientuserid=words[8],
		peerstatus=string.match(words[9] or "", "^[^/]*"),
		peerhost=string.match(words[9] or "", "[^/]*$"),
		squarkcategory=string.match(words[11] or "", "^[^,]*"),
		squarkaction=string.match(words[11] or "", "[^,]*$")}

	checkwords(logentry)

	-- Don't care about TCP_DENIED so apps like dropbox, nokia connection
	-- suite does not flood our logs.
	if logentry.code == nil or logentry.code == "TCP_DENIED" then
		return nil
	end

	-- Don't care about local requests (from DG) (this check also removes blank lines)
	if logentry.clientip and logentry.clientip ~= "127.0.0.1" then
		logentry.logdatetime = os.date("%Y-%m-%d %H:%M:%S", logentry.logdatetime)..string.match(logentry.logdatetime, "%..*")
		return logentry
	end
	return nil
end

local function parsedglog(line)
	local words = format.string_to_table(line, "\t")
	local logentry = {logdatetime=words[1],
			clientuserid=words[2],
			clientip=words[3],
			URL=words[4],
			reason=words[5],
			method=words[6],
			bytes=words[7],
			shortreason=words[9],
			deniedyesno=1}

	checkwords(logentry)

	if logentry.reason and logentry.reason ~= "" then
           	if logentry.shortreason == "" then
           		logentry.shortreason = logentry.reason
           	end
		--logentry.score = string.match(logentry.reason, "^.*: ([0-9]+) ")
		logentry.logdatetime = string.gsub(logentry.logdatetime, "%.", "-")
	   	return logentry
	end
	return nil
end

-- ################################################################################
-- DOWNLOAD FILE FUNCTIONS

-- must do apk_add wget first

local connecttosource = function(source, cookiesfile)
	local success = false
	logme("Connecting to source "..source.sourcename)
	if source.method == "http" or source.method == "https" then
		fs.write_file(cookiesfile, "password="..source.passwd.."&userid="..source.userid.."&Logon=Logon&submit=true")
		local resultpage, errtxt = modelfunctions.run_executable({"wget", "-O", "-", "--no-check-certificate", "--save-cookies", cookiesfile, "--keep-session-cookies", "--post-file", cookiesfile, source.method.."://"..source.source.."/cgi-bin/acf/acf-util/logon/logon"})
		if resultpage == "" then
			logme("Failed to connect to "..source.sourcename)
		elseif string.find(resultpage, "/acf%-util/logon/logon") then
			logme("Failed to log on to "..source.sourcename)
		else
			success = true
		end
	elseif source.method == "local" then
		success = true
	end
	return success
end

local getlogcandidates = function(source, cookiesfile)
	local candidates = {}
	if source.method == "http" or source.method == "https" then
		local resultpage = modelfunctions.run_executable({"wget", "-O", "-", "--no-check-certificate", "--load-cookies", cookiesfile, source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/status"})
		-- This method works for view prior to acf-alpine-baselayout-0.12.0
		for file in string.gmatch(resultpage, "download%?[^\"]*name=([^\"]+)") do
			candidates[#candidates+1] = file
		end
		-- This method works for view from acf-alpine-baselayout-0.12.0
		local reversetable = {}
		for file in string.gmatch(resultpage, 'name="filename"  value="([^\"]+)"') do
			if not reversetable[file] then
				candidates[#candidates+1] = file
				reversetable[file] = true
			end
		end
	elseif source.method == "local" then
		candidates = fs.find_files_as_array(nil, source.source)
	end
	return candidates
end

local openlogfile = function(source, cookiesfile, logfile)
	local handle, handle2, errtxt
	if source.method == "http" or source.method == "https" then
		local cmd = {"wget", "-O", "-", "--no-check-certificate", "--load-cookies", cookiesfile, "--post-data", "submit=true&viewtype=stream&name="..logfile.."&filename="..logfile, source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/download"}
		cmd.stdin = "/dev/null"
		cmd.stdout = subprocess.PIPE
		cmd.stderr = "/dev/null"
		local res, err = pcall(function()
	                -- For security, set the path
	                posix.setenv("PATH", "/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin")
	                local proc, errmsg, errno = subprocess.popen(cmd)
			if proc then
				if string.find(logfile, "%.gz$") then
					local cmd2 = {"gunzip", "-c"}
					-- Pipe the output of wget into gunzip
					cmd2.stdin = proc.stdout
					cmd2.stdout = subprocess.PIPE
					cmd2.stderr = "/dev/null"
	                		local proc2, errmsg2, errno2 = subprocess.popen(cmd2)
					if proc2 then
						handle = proc2.stdout
						handle2 = proc.stdout
					else
						proc.stdout:close()
						errtxt = errmsg2 or "Unknown failure"
					end
				else
					handle = proc.stdout
				end

			else
				errtxt = errmsg or "Unknown failure"
			end
		end)
		if not res or err then
			errtxt = err or "Unknown failure"
		end
	elseif source.method == "local" then
		if string.find(logfile, "%.gz$") then
			local res, err = pcall(function()
	                	-- For security, set the path
		                posix.setenv("PATH", "/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin")
				local cmd = {"gunzip", "-c", logfile}
				cmd.stdin = "/dev/null"
				cmd.stdout = subprocess.PIPE
				cmd.stderr = "/dev/null"
	               		local proc, errmsg, errno = subprocess.popen(cmd)
				if proc then
					handle = proc.stdout
				else
					errtxt = errmsg or "Unknown failure"
				end
			end)
			if not res or err then
				errtxt = err or "Unknown failure"
			end
		else
			handle = io.open(logfile)
		end
	end
	return handle, handle2
end

local deletelogfile = function(source, cookiesfile, logfile)
	if source.method == "http" or source.method == "https" then
		modelfunctions.run_executable({"wget", "-O", "-", "--no-check-certificate", "--load-cookies", cookiesfile, "--post-data", "submit=true&name="..logfile.."&filename="..logfile, source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/delete"})
	elseif source.method == "local" then
		os.remove(logfile)
	end
end

-- ################################################################################
-- PUBLIC FUNCTIONS

function mymodule.getsourcelist()
	local retval = cfe({ type="structure", value={}, label="Weblog Source List" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		retval.value = listsourceentries()
		databasedisconnect()
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end

function mymodule.getsource(sourcename)
	local sourcedata = mymodule.getnewsource()
	sourcedata.value.sourcename.value = sourcename
	sourcedata.value.sourcename.errtxt = "Source name does not exist"
	sourcedata.value.sourcename.readonly = true

	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local sourcelist = listsourceentries()
		databasedisconnect()
		for i,source in ipairs(sourcelist) do
			if source.sourcename == sourcename then
				sourcedata.value.sourcename.errtxt = nil
				for name,val in pairs(source) do
					if sourcedata.value[name] then
						sourcedata.value[name].value = val
					end
				end
				break
			end
		end
	end)
	if not res then
		sourcedata.errtxt = err
	end

	return sourcedata
end

local validatesource = function(sourcedata)
	local success = modelfunctions.validateselect(sourcedata.value.method)
	local test = {"sourcename", "source"}
	if sourcedata.value.method.value ~= "local" then
		test[#test+1] = "userid"
		test[#test+1] = "passwd"
	end
	for i,name in ipairs(test) do
		if sourcedata.value[name].value == "" then
			sourcedata.value[name].errtxt = "Cannot be empty"
			success = false
		end
	end

	return success
end

function mymodule.updatesource(self, sourcedata)
	local success = validatesource(sourcedata)
	sourcedata.errtxt = "Failed to update source"
	if success then
		local source = {}
		for name,val in pairs(sourcedata.value) do
			source[name] = val.value
		end

		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			sourcedata.descr = updatesourceentry(source)
			databasedisconnect()
			sourcedata.errtxt = nil
		end)
		if not res and err then
			sourcedata.errtxt = sourcedata.errtxt .. "\n" .. err
		end
	end

	return sourcedata
end

function mymodule.getnewsource()
	local source = {}
	source.sourcename = cfe({ label="Source Name", seq=0 })
	source.method = cfe({ type="select", value="local", label="Method", option={"http", "https", "local"}, seq=3 })
	source.userid = cfe({ label="UserID", seq=4 })
	source.passwd = cfe({ type="password", label="Password", seq=5 })
	source.source = cfe({ value="/var/log", label="Source Location / Address", seq=2 })
	source.tzislocal = cfe({ type="boolean", value=false, label="Using local timezone", seq=6 })
	source.enabled = cfe({ type="boolean", value=false, label="Enabled", seq=1 })
	return cfe({ type="group", value=source, label="Source" })
end

function mymodule.createsource(self, sourcedata)
	local success = validatesource(sourcedata)
	sourcedata.errtxt = "Failed to create source"
	if success then
		local source = {}
		for name,val in pairs(sourcedata.value) do
			source[name] = val.value
		end
		-- remove spaces from sourcename
		source.sourcename = string.gsub(source.sourcename, "%s+$", "")

		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			sourcedata.descr = importsourceentry(source)
			databasedisconnect()
			sourcedata.errtxt = nil
		end)
		if not res and err then
			sourcedata.errtxt = sourcedata.errtxt .. "\n" .. err
		end
	end

	return sourcedata
end

function mymodule.getdeletesource(self, clientdata)
	local retval = {}
	retval.sourcename = cfe({ value=clientdata.sourcename or "", label="Source Name" })
	return cfe({ type="group", value=retval, label="Delete Source" })
end

function mymodule.deletesource(self, delsource)
	delsource.errtxt="Failed to delete source"
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local number = deletesourceentry(delsource.value.sourcename.value)
		databasedisconnect()
		if number > 0 then
			delsource.errtxt = nil
		else
			delsource.value.sourcename.errtxt = "Failed to find source"
		end
	end)
	if not res and err then
		delsource.errtxt = delsource.errtxt .. "\n" .. err
	end
	return delsource
end

function mymodule.gettestsource(self, clientdata)
	local retval = {}
	retval.sourcename = cfe({ value=clientdata.sourcename or "", label="Source Name" })
	return cfe({ type="group", value=retval, label="Test Source" })
end

function mymodule.testsource(self, test)
	-- temporary override of logme function to capture messages to result.value
	test.descr = {}
	local temp = logme
	logme = function(message) table.insert(test.descr, message) end

	test.errtxt = "Test Failed"

	local cookiesfile = "/tmp/cookies-"..tostring(os.time())
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local sources = listsourceentries(test.value.sourcename.value)
		databasedisconnect()
		if #sources < 1 then
			test.value.sourcename.errtxt = "Failed to find source"
		else
			local source = sources[1]
			-- run the test
			if connecttosource(source, cookiesfile) then
				local files = getlogcandidates(source, cookiesfile)
				test.errtxt = nil
				if #files == 0 then
					logme("No log files found")
				else
					for i,file in ipairs(files) do
						logme("Found log file "..file)
					end
				end
			end
		end
	end)
	if err and not res then
		test.errtxt = test.errtxt .. "\n" .. err
	end
	os.remove(cookiesfile)

	-- fix the result
	test.descr = table.concat(test.descr, "\n") or ""
	logme = temp

	return test
end

-- import a logfile and delete logfile after
local function importlogfile(source, cookiesfile, file, parselog_func)
	logme("Getting " .. file )
	local loghandle, loghandle2 = openlogfile(source, cookiesfile, file)
	if not loghandle then
		logme("Failed to get " .. file )
		return
	end
	logme("Processing " .. file )
	local res, err = pcall(function()
		con:execute("START TRANSACTION")
		for line in loghandle:lines() do
			assert(con:execute("SAVEPOINT before_line"))
			local res2, err2 = pcall(function()
				local logentry = parselog_func(line)
				importlogentry(logentry, source.sourcename)
			end)
			if not res2 then
				if (config.stoponerror == "true") then
					pcall(function() con:execute("ROLLBACK") end)
				else
					assert(con:execute("ROLLBACK TO before_line"))
					con:execute("COMMIT")
				end
				pcall(function() logme("Exception on line:"..line) end)
				if err2 then
					pcall(function() logme(err2) end)
				end
				if (config.stoponerror == "true") then
					assert(res2, "Import halted on exception")
				else
					con:execute("START TRANSACTION")
				end
			else
				assert(con:execute("RELEASE SAVEPOINT before_line"))
			end
		end
		con:execute("COMMIT")
	end)
	if not res then
		pcall(function() con:execute("ROLLBACK") end)
		if err then
			pcall(function() logme(err) end)
		end
	end
	loghandle:close()
	if loghandle2 then
		loghandle2:close()
	end
	if res then
		logme("Deleting " .. file )
		deletelogfile(source, cookiesfile, file)
	end
	return res
end

function mymodule.getimportlogs(self, clientdata)
	local retval = {}
	return cfe({ type="group", value=retval, label="Import Logs" })
end

function mymodule.importlogs(self, import)
	local count = 0
	local success = true

	local res, err = pcall(function()
		databaseconnect(DatabaseOwner, config.password)

		-- Download, parse, and import the logs
		logme("Executing importlogs")
		logme("Analyzing...")
		local sql = "ANALYZE"
		res = assert (con:execute(sql))

		-- Get the word lists
		goodwords = fs.read_file_as_array(goodwordslist) or {}
		badwords = fs.read_file_as_array(badwordslist) or {}
		ignorewords = fs.read_file_as_array(ignorewordslist) or {}

		-- Determine sources
		local sources = listsourceentries(sourcename)

		for i,source in ipairs(sources) do
			if source.enabled then
				logme("Getting logs from source " .. source.sourcename)
				local cookiesfile = "/tmp/cookies-"..tostring(os.time())
				if connecttosource(source, cookiesfile) then
					local files = getlogcandidates(source, cookiesfile)
					for j,file in ipairs(files) do
						if string.match(file, "dansguardian/access%.log[%.%-]") then
							count = count + 1
							success = importlogfile(source, cookiesfile, file, parsedglog) and success
						elseif string.match(file, "squid/access%.log[%.%-]") then
							count = count + 1
							success = importlogfile(source, cookiesfile, file, parsesquidlog) and success
						end
					end
				end
				os.remove(cookiesfile)
			end
		end

		-- Process the logs
		if success then
			updateusagestats()
			importpubweblog()
		end
		-- Purge old database entries
		groomusagestat()
		groomdbhistlog()
		groompublogs()

		databasedisconnect()
	end)
	if not res or not success then
		import.errtxt = "Import Logs Failure"
		if err then
			pcall(function() logme(err) end)
			import.errtxt = import.errtxt .. "\n" .. err
		end
		pcall(function() databasedisconnect() end)
	else
		import.descr = "Imported "..tostring(count).." logs"
	end

	return import
end

function mymodule.getactivitylog()
	local retval = cfe({ type="structure", value={}, label="Weblog Activity Log" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		retval.value = listhistorylogentries() or {}
		databasedisconnect()
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end

function mymodule.geteditselected()
	local result = {}
	result.select = cfe({ type="list", value={}, label="Entries to mark as selected" })
	result.deselect = cfe({ type="list", value={}, label="Entries to mark as selected" })
	return cfe({ type="group", value=result, label="Select / Deselect log entries" })
end

function mymodule.editselected(self, data)
	local res, err = pcall(function()
		databaseconnect(DatabaseOwner)
		con:execute("START TRANSACTION")
		for i,sel in ipairs(data.value.select.value) do
			assert (con:execute("UPDATE pubweblog SET selected = true WHERE id = '"..escape(sel).."'"))
		end
		for i,sel in ipairs(data.value.deselect.value) do
			assert (con:execute("UPDATE pubweblog SET selected = false WHERE id = '"..escape(sel).."'"))
		end
		con:execute("COMMIT")
		databasedisconnect()
	end)
	if not res then
		data.errtxt = err
	end
	return data
end

function mymodule.getclearselected(self, clientdata)
	local retval = {}
	return cfe({ type="group", value=retval, label="Clear select fields" })
end

function mymodule.clearselected(self, clear)
	clear.errtxt = "Failed to clear select fields"
	local res, err = pcall(function()
		sql = "UPDATE pubweblog SET selected = false WHERE selected = true"
		databaseconnect(DatabaseOwner)
		assert (con:execute(sql))
		databasedisconnect()
		clear.errtxt = nil
	end)
	if not res then
		clear.errtxt = clear.errtxt.."\n"..err
	end
	return clear
end

local validateweblogparameters = function(params)
	local success = modelfunctions.validateselect(params.value.activelog)
	success = modelfunctions.validateselect(params.value.sortby) and success
	success = modelfunctions.validatemulti(params.value.sourcename) and success
	if params.value.clientip.value ~= "" and string.find(params.value.clientip.value, "[^%d%.]") then
		params.value.clientip.errtxt = "Invalid IP Address"
		success = false
	end
	if not validator.is_integer(params.value.score.value) then
		params.value.score.errtxt = "Must be a number"
		success = false
	end
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local s
		if params.value.starttime.value ~= "" then
			s,params.value.starttime.errtxt,params.value.starttime.value = convertdatabaseentry("TIMESTAMP", params.value.starttime.value)
			success = success and s
		end
		if params.value.endtime.value ~= "" then
			s,params.value.endtime.errtxt,params.value.endtime.value = convertdatabaseentry("TIMESTAMP", params.value.endtime.value)
			success = success and s
		end
		if params.value.focus.value ~= "" then
			s,params.value.focus.errtxt,params.value.focus.value = convertdatabaseentry("TIMESTAMP", params.value.focus.value)
			success = success and s
		end
		databasedisconnect()
	end)
	if not res and err then
		params.value.starttime.errtxt = err
		params.value.endtime.errtxt = err
		params.value.focus.errtxt = err
		success = false
	end
	return success
end

function mymodule.getweblogparameters(self, clientdata)
	local c = mymodule.getconfig()
	local result = {}
	result.activelog = cfe({ type="select", value="pubweblog", option={"pubweblog", "pubweblog_history"}, label="Active Weblog", seq=1 })
	result.starttime = cfe({ value=c.value.auditstart.value, label="Start Time", seq=2 })
	result.endtime = cfe({ value=c.value.auditend.value, label="End Time", seq=3 })
	result.sourcename = cfe({ type="multi", value={}, label="Source", option={}, seq=4 })
	result.clientuserid = cfe({ value=clientdata.clientuserid or "", label="User ID", seq=5 })
	result.clientip = cfe({ value=clientdata.clientip or "", label="Client IP", seq=6 })
	result.urisearch = cfe({ value="", label="URI Contains", descr="Retrieve records where the URI contains this word", seq=7 })
	result.score = cfe({ value=c.value.score.value, label="Minimum Score", descr="Minimum score to search on", seq=8 })
	result.sortby = cfe({ type="select", value=c.value.sortby.value, option=c.value.sortby.option, label="Sort By field", descr="Sort by this field when displaying records", seq=9 })
	result.badyesno = cfe({ type="boolean", value=c.value.badyesno.value, label="Show Suspect Records", descr="Limit search to records marked as suspect", seq=10 })
	result.deniedyesno = cfe({ type="boolean", value=c.value.deniedyesno.value, label="Show Denied Records", descr="Limit search to Denied URIs", seq=11 })
	result.bypassyesno = cfe({ type="boolean", value=c.value.bypassyesno.value, label="Show Bypass Records", descr="Limit search to Bypass attempts", seq=12 })
	result.selected = cfe({ type="boolean", value=false, label="Show Selected Records", descr="Limit search to records that have been selected", seq=13 })
	result.focus = cfe({ value="", label="Focus Time", seq=14 })

	-- Get the source options
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local sources = listsourceentries()
		for i,s in ipairs(sources) do
			result.sourcename.value[#result.sourcename.value + 1] = s.sourcename
			result.sourcename.option[#result.sourcename.option + 1] = s.sourcename
		end
		databasedisconnect()
	end)
	if err and not res then
		result.sourcename.errtxt = err
	end

	return cfe({ type="group", value=result, label="Weblog Access Log" })
end

function mymodule.getweblog(self, result)
	local success = validateweblogparameters(result)
	result.value.log = cfe({ type="structure", value={}, label="Weblog Access Log" })
	result.value.window = cfe({ value=config.window or "5", label="Time Window" })
	local err
	if success then
		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			result.value.log.value = listlogentries(result.value.activelog.value, result.value.clientuserid.value, result.value.starttime.value, result.value.endtime.value, result.value.clientip.value, result.value.badyesno.value, result.value.deniedyesno.value, result.value.bypassyesno.value, result.value.score.value, result.value.urisearch.value, result.value.sortby.value, result.value.selected.value, result.value.sourcename.value ) or {}
			databasedisconnect()
		end)
		if not res then
			result.errtxt = err
		end
	else
		result.errtxt = "Invalid search parameters"
	end
	return result
end

function mymodule.getusagestats()
	local retval = cfe({ type="structure", value={}, label="Weblog Usage Stats" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		retval.value = listusagestats() or {}
		databasedisconnect()
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end

function mymodule.getauditstats()
	local result = {}
	result.auditstart = cfe({ value=config.auditstart or "", label="Audit Start Time" })
	result.auditend = cfe({ value=config.auditend or "", label="Audit End Time" })
	result.groupby = cfe({ value=config.groupby or "clientuserid", label="Group By" })
	result.stats = cfe({ type="structure", value={}, label="Audit Block Statistics" })
	local res, err = pcall(function()
		if config.auditstart ~= "" and config.auditend ~= "" then
			databaseconnect(DatabaseUser)
			result.stats.value = groupflaggedlogentries(config.auditstart, config.auditend, result.groupby.value) or {}
			databasedisconnect()
		end
	end)
	return cfe({ type="group", value=result, errtxt=err, label="Weblog Audit Statistics" })
end

function mymodule.getcompleteaudit(self, clientdata)
	local retval = {}
	retval.timestamp = cfe({ value=clientdata.timestamp or "", label="New Audit End Time" })
	return cfe({ type="group", value=retval, label="Complete Audit" })
end

function mymodule.completeaudit(self, complete)
	if "" == complete.value.timestamp.value then
		local now = os.time()
		complete.value.timestamp.value = os.date("%Y-%m-%d %H:%M:%S", now - now%86400 - 86400)
	end
	local conf = mymodule.getconfig()
	conf.value.auditstart.value = conf.value.auditend.value
	conf.value.auditend.value = complete.value.timestamp.value
	conf = mymodule.updateconfig(self, conf)
	if conf.errtxt then
		complete.errtxt = "Failed to complete audit\n"..conf.errtxt.."\n"..conf.value.auditend.errtxt
	end
	return complete
end

function mymodule.getconfig()
	local result = {}
	result.auditstart = cfe({ value=config.auditstart or "", label="Audit Start Time", seq=1 })
	result.auditend = cfe({ value=config.auditend or "", label="Audit End Time", seq=2 })
	result.groupby = cfe({ type="select", value=config.groupby or "clientuserid", label="Group results by", option={"clientuserid", "clientip"}, descr="Display audit results based on user ID or IP", seq=3 })
	result.score = cfe({ value=config.score or "0", label="Minimum Score", descr="Default minimum Score to search for", seq=4 })
	result.sortby = cfe({ type="select", value=config.sortby or "logdatetime", label="Sort By field", option={"logdatetime", "logdatetime DESC", "clientuserid", "clientuserid DESC", "clientip", "clientip DESC", "bytes", "bytes DESC", "score", "score DESC", "reason"}, descr="Default sort order", seq=5 })
	result.badyesno = cfe({ type="boolean", value=(config.badyesno == "true"), label="Display Suspect Records", descr="By default, only show records flagged as suspect", seq=6 })
	result.deniedyesno = cfe({ type="boolean", value=(config.deniedyesno == "true"), label="Display Denied Records", descr="By default, only show records with denied URI", seq=7 })
	result.bypassyesno = cfe({ type="boolean", value=(config.bypassyesno == "true"), label="Display Bypass Records", descr="By default, only show records with bypass attempts", seq=8 })
	result.shorturi = cfe({ type="boolean", value=(config.shorturi == "true"), label="Truncate URLs", descr="You can limit the length of displayed URLs by enabling this option", seq=9 })
	result.shortreason = cfe({ type="boolean", value=(config.shortreason == "true"), label="Short Reason", descr="Display a short reason (dansguardian only)", seq=10 })
	result.window = cfe({ value=config.window or "5", label="Time Window", descr="Minutes of activity to display before and after selected block", seq=11 })
	result.purgedays = cfe({ value=config.purgedays or "30", label="Days before Purge", descr="Days to keep full history, regardless of audit", seq=12 })
	result.stoponerror = cfe({ type="boolean", value=(config.stoponerror == "true"), label="Stop on Error", descr="Stop import of logs if an error is encountered", seq=13})
	return cfe({ type="group", value=result, label="Weblog Config" })
end

local function validateconfig(newconfig)
	local success = modelfunctions.validateselect(newconfig.value.groupby)
	success = modelfunctions.validateselect(newconfig.value.sortby) and success
	if not validator.is_integer(newconfig.value.score.value) then
		newconfig.value.score.errtxt = "Must be a number"
		success = false
	end
	if newconfig.value.window.value == "" then
		newconfig.value.window.errtxt = "Cannot be blank"
		success = false
	elseif not validator.is_integer(newconfig.value.window.value) then
		newconfig.value.window.errtxt = "Must be a number"
		success = false
	end
	if not validator.is_integer(newconfig.value.purgedays.value) then
		newconfig.value.purgedays.errtxt = "Must be a number"
		success = false
	end
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local s
		if newconfig.value.auditstart.value ~= "" then
			s,newconfig.value.auditstart.errtxt,newconfig.value.auditstart.value = convertdatabaseentry("TIMESTAMP", newconfig.value.auditstart.value)
			success = success and s
		end
		if newconfig.value.auditend.value ~= "" then
			s,newconfig.value.auditend.errtxt,newconfig.value.auditend.value = convertdatabaseentry("TIMESTAMP", newconfig.value.auditend.value)
			success = success and s
		end
		databasedisconnect()
	end)
	if not res and err then
		newconfig.value.auditstart.errtxt = err
		newconfig.value.auditend.errtxt = err
		success = false
	end
	return success, newconfig
end

function mymodule.updateconfig(self, newconfig)
	local success = validateconfig(newconfig)
	if success then
		configcontent = format.update_ini_file(configcontent, "", "auditstart", newconfig.value.auditstart.value)
		configcontent = format.update_ini_file(configcontent, "", "auditend", newconfig.value.auditend.value)
		configcontent = format.update_ini_file(configcontent, "", "window", newconfig.value.window.value)
		configcontent = format.update_ini_file(configcontent, "", "purgedays", newconfig.value.purgedays.value)
		configcontent = format.update_ini_file(configcontent, "", "groupby", newconfig.value.groupby.value)
		configcontent = format.update_ini_file(configcontent, "", "shorturi", tostring(newconfig.value.shorturi.value))
		configcontent = format.update_ini_file(configcontent, "", "shortreason", tostring(newconfig.value.shortreason.value))
		configcontent = format.update_ini_file(configcontent, "", "stoponerror", tostring(newconfig.value.stoponerror.value))
		configcontent = format.update_ini_file(configcontent, "", "badyesno", tostring(newconfig.value.badyesno.value))
		configcontent = format.update_ini_file(configcontent, "", "deniedyesno", tostring(newconfig.value.deniedyesno.value))
		configcontent = format.update_ini_file(configcontent, "", "bypassyesno", tostring(newconfig.value.bypassyesno.value))
		configcontent = format.update_ini_file(configcontent, "", "score", tostring(newconfig.value.score.value))
		configcontent = format.update_ini_file(configcontent, "", "sortby", tostring(newconfig.value.sortby.value))

		fs.write_file(configfile, configcontent)
		config = format.parse_ini_file(configcontent, "") or {}
	else
		newconfig.errtxt = "Failed to update config"
	end

	return newconfig
end

function mymodule.getnewadhocquery()
	local query = {}
	query.query = cfe({ label="Query select statement" })
	return cfe({ type="group", value=query, label="Ad-hoc Query" })
end

function mymodule.adhocquery(self, query)
	local success = true
	query.value.query.value = query.value.query.value:lower()
	if query.value.query.value == "" then
		query.value.query.errtxt = "Empty select statement"
		success = false
	elseif not string.find(query.value.query.value, "^%s*select%s") then
		query.value.query.errtxt = "Must be a select statement"
		success = false
	end

	if success then
		local cur
		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			cur = assert (con:execute(query.value.query.value))
			databasedisconnect()
		end)
		if not res or not cur then
			query.value.query.errtxt = err or "Select failed"
			query.errtxt = "Query failed"
		else
			query.value.names = cfe({ type="list", value={}, label="Column names" })
			query.value.names.value = cur:getcolnames()
			query.value.types = cfe({ type="list", value={}, label="Column types" })
			query.value.types.value = cur:getcoltypes()
			query.value.result = cfe({ type="structure", value={}, label="Select result" })
			local result = query.value.result.value
			local row = cur:fetch ({}, "a")
			while row do
				result[#result+1] = {}
				for name,val in pairs(row) do
					result[#result][name] = val
				end
				row = cur:fetch (row, "a")
			end
			-- close everything
			cur:close()
		end
	else
		query.errtxt = "Query failed"
	end

	return query
end

function mymodule.testdatabase()
	local retval = cfe({ type="boolean", value=false, label="Database present" })
	local dbs = listdatabases()
	for i,db in ipairs(dbs) do
		if db == DatabaseName then
			retval.value = true
			break
		end
	end
	return retval
end

function mymodule.getnewdatabase()
	local database = {}
	local errtxt
	database.password = cfe({ type="password", label="Password", seq=0 })
	database.password_confirm = cfe({ type="password", label="Password (confirm)", seq=1 })
	local test = mymodule.testdatabase()
	if test.value then
		errtxt = "Database already exists!"
		success = false
	end
	return cfe({ type="group", value=database, label="Create Database", errtxt=errtxt })
end

function mymodule.create_database(self, database)
	local success = true
	local errtxt

	if database.value.password.value == "" or string.match(database.value.password.value, "'%s") then
		database.value.password.errtxt = "Invalid password"
		success = false
	end
	if database.value.password.value ~= database.value.password_confirm.value then
		database.value.password_confirm.errtxt = "Password does not match"
		success = false
	end
	local test = mymodule.testdatabase()
	if test.value then
		errtxt = "Database already exists!"
		success = false
	end

	if success then
		errtxt = createdatabase(database.value.password.value)
		test = mymodule.testdatabase()
		if not test.value then
			success = false
		else
			local res, err = pcall(function()
				databaseconnect(DatabaseOwner, database.value.password.value)
				for i,scr in ipairs(database_creation_script) do
					assert (con:execute(scr))
				end
				databasedisconnect()
				-- put the password in the config file for future use
				configcontent = format.update_ini_file(configcontent, "", "password", database.value.password.value)
				fs.write_file(configfile, configcontent)
				config = format.parse_ini_file(configcontent, "") or {}
			end)
			if not res then
				errtxt = err
				success = false
			end
		end
		if not success then
			deletedatabase()
		end
	end

	if not success then
		database.errtxt = "Failed to create database"
		if errtxt then
			database.errtxt = database.errtxt.."\n"..errtxt
		end
	end

	return database
end

function mymodule.listfiles()
        local retval = cfe({ type="structure", value={}, label="Weblog Files" })
	for i,file in ipairs(files) do
		local details = posix.stat(file) or {}
		details.filename = file
		retval.value[#retval.value + 1] = details
	end
        return retval
end

function mymodule.readfile(self, clientdata)
	return modelfunctions.getfiledetails(clientdata.filename, files)
end

function mymodule.updatefile(self, filedetails)
	return modelfunctions.setfiledetails(self, filedetails, files)
end

return mymodule