Make two-step import using weblog table so can calculate usage and watch users

1 files changed, 25 insertions, 8 deletions

diff --git a/weblog-model.lua b/weblog-model.lua
index 0202f74..a23a007 100644
--- a/weblog-model.lua
+++ b/weblog-model.lua
@@ -30,6 +30,7 @@ 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, selected boolean, id serial)",
 	"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)",
@@ -181,7 +182,7 @@ end
 
 local importlogentry = function(entry, sourcename)
 	if entry then
-		local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')",
+		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), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.goodwordloc))
 
@@ -237,7 +238,7 @@ local addtowatchlist = function()
 	local watchdays = config.watchdays or 14
 	local sql = "insert into watchlist select clientuserid, " ..
 		"(max(logdatetime) + INTERVAL '"..watchdays.." days') as expiredatetime " ..
-		"from pubweblog where bypassyesno > '0' group by clientuserid"
+		"from weblog where bypassyesno > '0' group by clientuserid"
 	local res1 = assert (con:execute(sql))
 	sql = "delete from watchlist where exists " ..
 		"(select * from watchlist w where w.clientuserid = watchlist.clientuserid " ..
@@ -250,13 +251,28 @@ end
 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 pubweblog.sourcename, " ..
-		"date_trunc('hour', pubweblog.logdatetime) as date, " ..
-		"count(*), SUM(deniedyesno) from pubweblog group by sourcename,date"
+	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 people from the watchlist when they have expired out
 local groomwatchlist = function()
 	local res = assert (con:execute("delete from watchlist where expiredatetime < now()"))
@@ -540,13 +556,13 @@ local function parsesquidlog(line)
 	        end
 		
 		if string.find(words[7],"*DENIED*") then
-		   logme("*Denied*")
+--		   logme("*Denied*")
 		   isdenied=1
 		elseif string.find(words[7],"GBYPASS") then
-		   logme("GBYPASS")
+--		   logme("GBYPASS")
 		   isbypass=1
 		elseif string.find(words[7],"*OVERRIDE*") then
-		   logme("*OVERRIDE*")
+--		   logme("*OVERRIDE*")
 		   isbypass=1
 		end
 	  end
@@ -1272,6 +1288,7 @@ function importlogs()
 		if success then
 			addtowatchlist()
 			updateusagestats()
+			importpubweblog()
 			groomwatchlist()
 		end
 		-- Purge old database entries