path: root/weblog-model.lua

module(..., package.seeall)

-- Load libraries
require("modelfunctions")
require("fs")
require("format")
require("validator")
require("luasql.postgres")

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.conf"
local configcontent = fs.read_file(configfile) or ""
local config = format.parse_ini_file(configcontent, "") or {}

local database_creation_script = {
	"CREATE TABLE dbhistlog (logdatetime timestamp(3) without time zone NOT NULL, msgtext text)",
	"CREATE TABLE pubblocklog(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 integer NOT NULL, reason text, score integer, shortreason text)",
	"CREATE TABLE blocklog(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(0) without time zone NOT NULL, uri text NOT NULL, bytes integer NOT NULL, reason text, score integer, shortreason 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 integer NOT NULL, reason text, score integer, shortreason text)",
	"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 integer NOT NULL, reason text, score integer, shortreason 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)",
	"CREATE TABLE watchlist (clientuserid character varying(64) NOT NULL, expiredatetime timestamp(0) without time zone NOT NULL)",
	"ALTER TABLE ONLY source ADD CONSTRAINT source_pkey PRIMARY KEY (sourcename)",
	"CREATE INDEX blocklogclientididx ON blocklog USING btree (clientuserid)",
	"CREATE INDEX blocklogclientidx ON blocklog USING btree (clientip, clientuserid)",
	"CREATE INDEX dbhistlogdatetimeidx ON dbhistlog USING btree (logdatetime)",
	"CREATE INDEX pubblocklogclientididx ON pubblocklog USING btree (clientuserid)",
	"CREATE INDEX pubblocklogclientidx ON pubblocklog USING btree (clientip, clientuserid)",
	"CREATE INDEX pubweblogclientdateidx ON pubweblog USING btree (logdatetime, clientuserid)",
	"CREATE INDEX pubweblogclientidx ON pubweblog USING btree (clientip, clientuserid)",
	"CREATE INDEX pubweblogclientipidx ON pubweblog USING btree (clientip)",
	"CREATE INDEX pubweblogclientuserididx ON pubweblog USING btree (clientuserid)",
	"CREATE INDEX pubwebloglogdatetimeidx ON pubweblog USING btree (logdatetime)",
	"CREATE INDEX weblogclientidx ON weblog USING btree (clientip, clientuserid)",
	"CREATE INDEX weblogclientipidx ON weblog USING btree (clientip)",
	"CREATE INDEX weblogclientuserididx ON weblog USING btree (clientuserid)",
	"GRANT SELECT ON dbhistlog TO "..DatabaseUser,
	"GRANT SELECT ON pubblocklog TO "..DatabaseUser,
	"GRANT SELECT ON pubweblog TO "..DatabaseUser,
	"GRANT SELECT, UPDATE, INSERT, DELETE ON source TO "..DatabaseUser,
	"GRANT SELECT ON usagestat TO "..DatabaseUser,
	"GRANT SELECT, UPDATE, INSERT, DELETE ON watchlist 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
local escape = function(sql)
	sql = sql or ""
	sql = string.gsub(sql, "'", "''")
	return string.gsub(sql, "\\", "\\\\")
end

-- List the postgres databases on this system
local listdatabases = function()
	local dbs = {}
	local cmd = path.."psql -U postgres -tl 2>&1"
	local f = io.popen(cmd)
	local result = f:read("*a") or ""
	f:close()
	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 = path..'psql -U postgres -c "CREATE USER '..DatabaseOwner..' WITH PASSWORD \''..password..'\'" 2>&1'
	local f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()

	-- Create the database
	cmd = path..'psql -U postgres -c "CREATE DATABASE '..DatabaseName..' WITH OWNER '..DatabaseOwner..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()

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

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

	local cmd = path..'psql -U postgres -c "DROP DATABASE '..DatabaseName..'" 2>&1'
	local f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseUser..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseOwner..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()

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

-- Run an SQL script
local runSQLscript = function(filename)
	-- Create the database
	local cmd = path..'psql -U postgres -f "'..filename..'" '..DatabaseName..' 2>&1'
	local f = io.popen(cmd)
	local result = f:read("*a") or ""
	f:close()
	-- Create the tables
	print (result)
	return 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 importsquidlog = function(entry, sourcename)
	if entry then
		local sql = string.format("INSERT INTO weblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
			escape(sourcename), escape(entry.clientip), escape(entry.clientuserid):lower(),
			escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes))
		local res = assert (con:execute(sql))
	end
end

local importdglog = function(entry, sourcename)
	if entry then
		local sql = string.format("INSERT INTO blocklog VALUES ('%s', '0.0.0.0', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
			escape(sourcename), escape(entry.clientuserid:lower()), escape(entry.logdatetime), escape(entry.URL),
			escape(entry.bytes), escape(entry.reason), escape(entry.score or "0"), escape(entry.shortreason))
		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

-- Add blocked users (from blocklog) to the watchlist, removing old entries if updated.
local addtowatchlist = function()
	local watchdays = config.watchdays or 14
	local sql = "insert into watchlist select clientuserid, " ..
		"(max(logdatetime) + INTERVAL '"..watchdays.." days') as expiredatetime " ..
		"from blocklog group by clientuserid"
	local res1 = assert (con:execute(sql))
	sql = "delete from watchlist where exists " ..
		"(select * from watchlist w where w.clientuserid = watchlist.clientuserid " ..
		"and w.expiredatetime > watchlist.expiredatetime)"
	local res2 = assert (con:execute(sql))
	logme("There are now " .. (res1-res2) .. " new names in the watchlist")
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(*), 0 from weblog group by sourcename,date"
	local res = assert (con:execute(sql))

	-- update the usagestat table from blocklog
	-- (will result in multiple rows where logs rotated on partial hours)
	-- (also, numrequest and numblock end up in different rows)
	sql= "insert into usagestat select blocklog.sourcename, " ..
		"date_trunc('hour', blocklog.logdatetime) as date, " ..
		"0, count(*) from blocklog group by sourcename,date"
	res = assert (con:execute(sql))
end

-- Process weblog and blocklog, combine into pubweblog and pubblocklog
-- empties weblog and blocklog
local importpubweblog = function()
	-- Merge equal blocks into weblog
	sql = "update weblog set reason=blocklog.reason, " ..
		"score=blocklog.score, shortreason=blocklog.shortreason from blocklog where " ..
		"blocklog.logdatetime >= date_trunc('second', weblog.logdatetime) and " ..
		"blocklog.logdatetime < (weblog.logdatetime + INTERVAL '45' second) and " ..
		"blocklog.sourcename=weblog.sourcename and "..
		"blocklog.clientuserid=weblog.clientuserid and "..
		"weblog.uri like (blocklog.uri || '%')"
	res = assert (con:execute(sql))
	logme("importpubweblog merged " .. res .. " blocks with matching timestamps.")

	sql = "insert into weblog select * from blocklog where " ..
		" NOT EXISTS (select * from weblog where " ..
		"blocklog.logdatetime >= date_trunc('second', weblog.logdatetime) and " ..
		"blocklog.logdatetime < (weblog.logdatetime + INTERVAL '45' second) and " ..
		"blocklog.sourcename=weblog.sourcename and "..
		"blocklog.clientuserid=weblog.clientuserid and "..
		"weblog.uri like (blocklog.uri || '%') )"
	res = assert (con:execute(sql))
	if ( res > 0 ) then
		logme("NOTE: importpubweblog found and merged " .. res .. " unmatched blocks (source IP will not match)")
	end

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

	-- update pubblocklog table
	sql = "insert into pubblocklog select * from weblog where reason is not null"
	res = assert (con:execute(sql))
	logme("importpubweblog imported " .. res .. " new blocks into block table.")

	-- grab the bypass info
	sql = "insert into pubblocklog select * from weblog where uri like '%?GBYPASS%'"
	res = assert (con:execute(sql))
	logme("importpubweblog imported " .. res .. " dg bybass events into block table.")

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

-- Delete people from the watchlist when they have expired out
local groomwatchlist = function()
	local res = assert (con:execute("delete from watchlist where expiredatetime < now()"))
	logme("removed " .. res .. " names from watchlist")
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 watchdays = config.watchdays or 14
	local historydays = config.historydays or 14
	local now = os.time()

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

	-- purge anything older than purgedate
	sql = "delete from pubweblog where logdatetime < '" .. temp .."'"
	res = assert (con:execute(sql))
	logme("Purged " .. res .. " old records from pubweblog")

	sql = "delete from pubblocklog where logdatetime < '" .. temp .. "'"
	res = assert (con:execute(sql))
	logme("Purged " .. res .. " old records from pubblocklog")

	-- purge anything older than startddate+historydays+watchdays
	local temp = config.auditstart
	if not temp or temp == "" then temp = os.date("%Y-%m-%d %H:%M:%S") end
	logme("Purge date since last audit is " .. tostring(watchdays+historydays) .. " days before " .. temp .. ".")

	sql = "delete from pubweblog where logdatetime < (timestamp '"..temp.."' - INTERVAL '"..tostring(watchdays+historydays).." days')"
	res = assert (con:execute(sql))
        logme("removed " .. res .. " old pubweblog records that are older than history+watchdays")

	sql = "delete from pubblocklog where logdatetime < (timestamp '"..temp.."' - INTERVAL '"..tostring(watchdays+historydays).." days')"
	res = assert (con:execute(sql))
        logme("removed " .. res .. " old pubblocklog records that are older than history+watchdays")

	-- purge good people after historydays
	logme("The delete date for non-watchlist users is " .. tostring(historydays) .. " days before " .. temp .. ".")

	sql = "delete from pubweblog where logdatetime < (timestamp '".. temp.."' - INTERVAL '"..tostring(historydays).." days') and clientuserid NOT IN (select clientuserid from watchlist)"
	res = assert (con:execute(sql))
	logme("removed " .. res .. " records for users not on the watchlist.")
end

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

local importwatchlistentry = function(clientuserid, expiredatetime)
	local sql = string.format("INSERT INTO watchlist VALUES ('%s', '%s')",
		escape(clientuserid), escape(expiredatetime))
	local res = assert (con:execute(sql))
	return res
end

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

local generatewhereclause = function(clientuserid, starttime, endtime, clientip)
	local sql = ""
	local where = {}
	if clientuserid and clientuserid ~= "" then
		where[#where+1] = "clientuserid = '"..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 #where > 0 then
		sql = " WHERE " .. table.concat(where, " AND ")
	end
	return sql
end

local listlogentries = function(logname, clientuserid, starttime, endtime, clientip)
	local entries = {}
	-- retrieve a cursor
	local sql = "SELECT * from "..logname
	sql = sql .. generatewhereclause(clientuserid, starttime, endtime, clientip)
	sql = sql .. " ORDER BY logdatetime"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		local uridescr = row.uri
		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}
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local listpubblocklogentries = function(...)
	return listlogentries("pubblocklog", ...)
end

local listpubweblogentries = function(...)
	return listlogentries("pubweblog", ...)
end

local grouppubblocklogentries = function(starttime, endtime, groupby)
	groupby = groupby or "clientuserid"
	local entries = {}
	-- retrieve a cursor
	local sql = "SELECT "..groupby..", count(*) AS numblock, max(score) AS maxscore FROM pubblocklog"
	sql = sql .. generatewhereclause(nil, starttime, endtime)
	sql = sql .. " GROUP BY "..groupby.. " ORDER BY numblock DESC"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {numblock=row.numblock, 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 ('"..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 "..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
			APP.logevent(name.." = "..val..", ")
		end
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	APP.logevent("Table "..tablename.." contains "..count.." rows")
end

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

local function parsesquidlog(line)
	-- Format of squid log (space separated):
	-- time elapsed remotehost code/status bytes method URL rfc931 peerstatus/peerhost
	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 "", "[^/]*$")}

	logentry.logdatetime = os.date("%Y-%m-%d %H:%M:%S", logentry.logdatetime)..string.match(logentry.logdatetime, "%..*")
	-- Don't care about local requests (from DG)
	if logentry.clientip ~= "127.0.0.1" then
		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]}
	if 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")
		local cmd = "wget -O - --no-check-certificate --save-cookies "..cookiesfile.." --keep-session-cookies --post-file '"..cookiesfile.."' '"..source.method.."://"..format.escapespecialcharacters(source.source).."/cgi-bin/acf/acf-util/logon/logon' 2>/dev/null"
		local f = io.popen(cmd)
		local resultpage = f:read("*a")
		f:close()
		if resultpage == "" then
			logme("Failed to connect to "..source.sourcename)
		elseif string.find(resultpage, "Log in") then
			logme("Failed to log in 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 cmd = "wget -O - --no-check-certificate --load-cookies "..cookiesfile.." '"..source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/status' 2>/dev/null"
		local f = io.popen(cmd)
		local resultpage = f:read("*a")
		f:close()
		for file in string.gmatch(resultpage, "download%?name=([^\"]+)") do
			candidates[#candidates+1] = file
		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
	if source.method == "http" or source.method == "https" then
		local cmd = "wget -O - --no-check-certificate --load-cookies "..cookiesfile.." --post-data 'name="..logfile.."' '"..source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/download' 2>/dev/null"
		if string.find(logfile, "%.gz$") then
			cmd = cmd.." | gunzip -c"
		end
		handle = io.popen(cmd)
	elseif source.method == "local" then
		if string.find(logfile, "%.gz$") then
			local cmd = "gunzip -c "..logfile
			handle = io.popen(cmd)
		else
			handle = io.open(logfile)
		end
	end
	return handle
end

local deletelogfile = function(source, cookiesfile, logfile)
	if source.method == "http" or source.method == "https" then
		local cmd = "wget -O - --no-check-certificate --load-cookies "..cookiesfile.." --post-data 'name="..logfile.."' '"..source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/delete' 2>/dev/null"
		local f = io.popen(cmd)
		f:close()
	elseif source.method == "local" then
		os.remove(logfile)
	end
end

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

function getsourcelist()
	local retval = cfe({ type="list", 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 getsource(sourcename)
	local sourcedata = getnewsource()
	sourcedata.value.sourcename.value = sourcename
	sourcedata.value.sourcename.errtxt = "Source name does not exist"

	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 updatesource(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 getnewsource()
	local source = {}
	source.sourcename = cfe({ label="Source Name" })
	source.method = cfe({ type="select", value="local", label="Method", option={"http", "https", "local"} })
	source.userid = cfe({ label="UserID" })
	source.passwd = cfe({ label="Password" })
	source.source = cfe({ value="/var/log", label="Source Location / Address" })
	source.tzislocal = cfe({ type="boolean", value=false, label="Using local timezone" })
	source.enabled = cfe({ type="boolean", value=false, label="Enabled" })
	return cfe({ type="group", value=source, label="Source" })
end

function createsource(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
	else
	end

	return sourcedata
end

function deletesource(sourcename)
	local result = cfe({ errtxt="Failed to delete source", label="Delete source result" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local number = deletesourceentry(sourcename)
		databasedisconnect()
		if number > 0 then
			result.errtxt = nil
			result.value = "Source Deleted"
		end
	end)
	if not res and err then
		result.errtxt = result.errtxt .. "\n" .. err
	end
	return result
end

function testsource(sourcename)
	local result = cfe({ label="Test source result" })

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

	local cookiesfile = "/tmp/cookies-"..tostring(os.time())
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local sources = listsourceentries(sourcename)
		databasedisconnect()
		if #sources < 1 then
			result.errtxt = "Failed to find source"
		else
			local source = sources[1]
			-- run the test
			if connecttosource(source, cookiesfile) then
				local files = getlogcandidates(source, cookiesfile)
				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 not res then
		result.errtxt = "Failed to connect to source"
		if err then
			result.errtxt = result.errtxt .. "\n" .. err
		end
	end
	os.remove(cookiesfile)

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

	return result
end

-- import either squid or dg log file.
-- delete logfile after
local function importlogfile(source, cookiesfile, file, parselog_func, importlog_func)
	logme("Processing " .. file )
	logme("Getting " .. file )
	local loghandle = openlogfile(source, cookiesfile, file)
	con:execute("START TRANSACTION")
	for line in loghandle:lines() do
		local logentry = parselog_func(line)
		importlog_func(logentry, source.sourcename)
	end
	con:execute("COMMIT")
	loghandle:close()
	logme("Deleting " .. file )
	deletelogfile(source, cookiesfile, file)
end

function importlogs()
	local result = cfe({ label="Weblog Import Logs Result" })
	local count = 0

	local res, err = pcall(function()
		databaseconnect(DatabaseOwner, config.password)
		-- Determine sources
		local sources = listsourceentries(sourcename)

		-- Download, parse, and import the logs
		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
							importlogfile(source, cookiesfile, file, parsedglog, importdglog)
						elseif string.match(file, "squid/access%.log[%.%-]") then
							count = count + 1
							importlogfile(source, cookiesfile, file, parsesquidlog, importsquidlog)
						end
					end
				end
				os.remove(cookiesfile)
			end
		end

		-- Process the logs
		addtowatchlist()
		updateusagestats()
		importpubweblog()
		-- Purge old database entries
		groomwatchlist()
		groomusagestat()
		groomdbhistlog()
		groompublogs()

		databasedisconnect()
	end)
	if not res then
		result.errtxt = "Import Logs Failure"
		if err then
			pcall(function() logme(err) end)
			result.errtxt = result.errtxt .. "\n" .. err
		end
	end

	result.value = "Imported "..tostring(count).." logs"

	return result
end

function getactivitylog()
	local retval = cfe({ type="list", 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 getwatchlist()
	local retval = cfe({ type="list", value={}, label="Weblog Watchlist" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		retval.value = listwatchlistentries() or {}
		databasedisconnect()
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end

function getnewwatchlistentry()
	local watch = {}
	watch.clientuserid = cfe({ label="User ID" })
	local watchdays = config.watchdays or 14
	watch.expiredatetime = cfe({ value=os.date("%Y-%m-%d %H:%M:%S", os.time() + watchdays*86400), label="Expiration Date" })

	return cfe({ type="group", value=watch, label="Watchlist Entry" })
end

local function validatewatchlistentry(watch)
	local success = true
	for i,name in ipairs({"clientuserid", "expiredatetime"}) do
		if watch.value[name].value == "" then
			watch.value[name].errtxt = "Cannot be empty"
			success = false
		end
	end
	if not watch.value.expiredatetime.errtxt then
		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			local s
			s,watch.value.expiredatetime.errtxt = testdatabaseentry("TIMESTAMP", watch.value.expiredatetime.value)
			databasedisconnect()
			success = success and s
		end)
		if not res and err then
			watch.value.expiredatetime.errtxt = err
			success = false
		end
	end
	return success
end

function createwatchlistentry(watch)
	watch.value.clientuserid.value = watch.value.clientuserid.value:lower()
	watch.errtxt = "Failed to create watchlist entry"
	local success = validatewatchlistentry(watch)
	if success then
		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			local watchlist = listwatchlistentries() or {}
			for i,w in ipairs(watchlist) do
				if w.clientuserid == watch.value.clientuserid.value then
					watch.value.clientuserid.errtxt = "User ID already exists"
					success = false
					break
				end
			end
			if success then
				local count = importwatchlistentry(watch.value.clientuserid.value, watch.value.expiredatetime.value)
				if count > 0 then
					watch.errtxt = nil
				end
			end
			databasedisconnect()
		end)
		if not res and err then
			watch.errtxt = watch.errtxt .. "\n" .. err
		end
	end

	return watch
end

function deletewatchlistent(clientuserid)
	local result = cfe({ errtxt="Failed to delete watchlist entry", label="Delete watchlist entry result" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local number = deletewatchlistentry(clientuserid)
		databasedisconnect()
		if number > 0 then
			result.value = "Watchlist entry deleted"
			result.errtxt = nil
		end
	end)
	if not res and err then
		result.errtxt = result.errtxt .. "\n" .. err
	end

	return result
end

local validateparameters = function(params)
	local success = true
	if params.clientip.value ~= "" and string.find(params.clientip.value, "[^%d%.]") then
		params.clientip.errtxt = "Invalid IP Address"
		success = false
	end
	if params.window.value ~= "" and not validator.is_integer(params.window.value) then
		params.window.errtxt = "Must be an integer"
		success = false
	end
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local s
		if params.starttime.value ~= "" then
			s,params.starttime.errtxt,params.starttime.value = convertdatabaseentry("TIMESTAMP", params.starttime.value)
			success = success and s
		end
		if params.endtime.value ~= "" then
			s,params.endtime.errtxt,params.endtime.value = convertdatabaseentry("TIMESTAMP", params.endtime.value)
			success = success and s
		end
		if params.focus.value ~= "" then
			s,params.focus.errtxt,params.focus.value = convertdatabaseentry("TIMESTAMP", params.focus.value)
			success = success and s
		end
		databasedisconnect()
	end)
	if not res and err then
		params.starttime.errtxt = err
		params.endtime.errtxt = err
		params.focus.errtxt = err
		success = false
	end
	return success
end

local handleparameters = function(clientuserid, starttime, endtime, clientip, focus)
	local result = {}
	result.clientuserid = cfe({ value=clientuserid or "", label="User ID" })
	result.starttime = cfe({ value=starttime or "", label="Start Time" })
	result.endtime = cfe({ value=endtime or "", label="End Time" })
	result.clientip = cfe({ value=clientip or "", label="Client IP" })
	result.window = cfe({ value=config.window or "5", label="Time Window" })
	result.focus = cfe({ value=focus or "", label="Focus Time" })
	return result
end

function getweblog(clientuserid, starttime, endtime, clientip, focus)
	if (not starttime or starttime=="") and (not endtime or endtime=="") and config.auditstart~="" and config.auditend~="" then
		starttime = config.auditstart
		endtime = config.auditend
	end
	local result = handleparameters(clientuserid, starttime, endtime, clientip, focus)
	result.log = cfe({ type="list", value={}, label="Weblog Access Log" })
	local success = validateparameters(result)
	if success then
		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			result.log.value = listpubweblogentries(clientuserid, starttime, endtime, clientip) or {}
			databasedisconnect()
		end)
	else
		err = "Invalid search parameters"
	end
	return cfe({ type="group", value=result, errtxt=err, label="Weblog Access Log" })
end

function getblocklog(clientuserid, starttime, endtime, clientip, focus)
	if (not starttime or starttime=="") and (not endtime or endtime=="") and config.auditstart~="" and config.auditend~="" then
		starttime = config.auditstart
		endtime = config.auditend
	end
	local result = handleparameters(clientuserid, starttime, endtime, clientip, focus)
	result.log = cfe({ type="list", value={}, label="Weblog Block Log" })
	local success = validateparameters(result)
	if success then
		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			result.log.value = listpubblocklogentries(clientuserid, starttime, endtime, clientip) or {}
			databasedisconnect()
		end)
	else
		err = "Invalid search parameters"
	end
	return cfe({ type="group", value=result, errtxt=err, label="Weblog Block Log" })
end

function getusagestats()
	local retval = cfe({ type="list", 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 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="list", value={}, label="Audit Block Statistics" })
	local res, err = pcall(function()
		if config.auditstart ~= "" and config.auditend ~= "" then
			databaseconnect(DatabaseUser)
			result.stats.value = grouppubblocklogentries(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 completeaudit(timestamp)
	local conf = getconfig()
	conf.value.auditstart.value = conf.value.auditend.value
	local now = os.time()
	conf.value.auditend.value = timestamp or os.date("%Y-%m-%d %H:%M:%S", now - now%86400 - 86400)
	conf = updateconfig(conf)
	local retval = cfe({ value="Audit completed", label="Complete Audit Result" })
	if conf.errtxt then
		retval.value = ""
		retval.errtxt = "Failed to complete audit\n"..conf.errtxt.."\n"..conf.value.auditend.errtxt
	end
	return retval
end

function getconfig()
	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.window = cfe({ value=config.window or "5", label="Time Window", descr="Minutes of activity to display before and after selected block" })
	result.watchdays = cfe({ value=config.watchdays or "14", label="Days to Watch", descr="Number of additional days to keep history for users in watchlist" })
	result.purgedays = cfe({ value=config.purgedays or "30", label="Days before Purge", descr="Days to keep history, regardless of audit" })
	result.historydays = cfe({ value=config.historydays or "14", label="Days to keep History", descr="Days beyond Audit Start Time to keep complete log history" })
	result.groupby = cfe({ type="select", value=config.groupby or "clientuserid", label="Group results by", option={"clientuserid", "clientip"} })
	result.shorturi = cfe({ type="boolean", value=false, label="Truncate URLs", descr="You can limit the length of displayed URLs by enabling this option"})
	if config.shorturi == "true" then result.shorturi.value = true end
	return cfe({ type="group", value=result, label="Weblog Config" })
end

local function validateconfig(newconfig)
	local success = modelfunctions.validateselect(newconfig.value.groupby)
	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.watchdays.value) then
		newconfig.value.watchdays.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
	if not validator.is_integer(newconfig.value.historydays.value) then
		newconfig.value.historydays.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 updateconfig(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, "", "watchdays", newconfig.value.watchdays.value)
		configcontent = format.update_ini_file(configcontent, "", "purgedays", newconfig.value.purgedays.value)
		configcontent = format.update_ini_file(configcontent, "", "historydays", newconfig.value.historydays.value)
		configcontent = format.update_ini_file(configcontent, "", "groupby", newconfig.value.groupby.value)
		configcontent = format.update_ini_file(configcontent, "", "shorturi", tostring(newconfig.value.shorturi.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 getnewadhocquery()
	local query = {}
	query.query = cfe({ label="Query select statement" })
	return cfe({ type="group", value=query, label="Ad-hoc Query" })
end

function adhocquery(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.result = cfe({ type="list", 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 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 getnewdatabase()
	local database = {}
	local errtxt
	database.password = cfe({ label="Password" })
	database.password_confirm = cfe({ label="Password (confirm)" })
	local test = 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 create_database(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 = testdatabase()
	if test.value then
		errtxt = "Database already exists!"
		success = false
	end

	if success then
		errtxt = createdatabase(database.value.password.value)
		test = 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