1 files changed, 82 insertions, 0 deletions
diff --git a/weblog-adhocquery-html.lsp b/weblog-adhocquery-html.lsp
new file mode 100644
index 0000000..538bd54
--- /dev/null
+++ b/weblog-adhocquery-html.lsp
@@ -0,0 +1,82 @@
+<% local form, viewlibrary, page_info = ... 
+require("viewfunctions")
+%>
+
+<% if form.value.result then %>
+<H1><%= html.html_escape(form.value.result.label) %></H1>
+<% require("html") %>
+<%= html.cfe_unpack(form.value.result.value) %>
+<form action="/cgi-bin/acf/weblog/weblog/downloadadhocquery" method="POST">
+<input class="hidden" type="hidden"  name="query"  value="<%= html.html_escape(form.value.query.value) %>" >
+<DL>
+<DT>Download query result</DT><DD><input class="submit" type="submit" name="Download" value="Download"></DD>
+</DL>
+</FORM>
+<% end %>
+
+<H1><%= html.html_escape(form.label) %></H1>
+<% displayformstart(form, page_info) %>
+<DL>
+<% displayformitem(form.value.query, "query") %>
+</DL>
+<% displayformend(form) %>
+
+This form accepts a Postgresql SELECT statement and displays the results. Examples:
+<ul>
+<li><pre>SELECT clientuserid, sum(bytes) AS total FROM weblog GROUP BY clientuserid ORDER BY total DESC</pre>
+<li><pre>SELECT extract(hour from date) AS hour, sum(numrequest) AS numrequest, sum(numblock) AS numblock FROM usagestat GROUP BY extract(hour from date) ORDER BY hour</pre>
+</ul>
+The available database tables and descriptions are as follows:<br>
+<H3>PubWeblog and PubBlocklog</H3>
+These tables contain the combined squid access log and dansguardian log for every access and blocked accesses respectively. The definition of the table is as follows:
+<pre>
+(
+    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
+)
+</pre>
+<H3>dbHistLog</H3>
+This table contains the database history, including such information as which log files were loaded and how many entries they contained. The definition of the table is as follows:
+<pre>
+(
+    logdatetime timestamp(0) without time zone NOT NULL,
+    msgtext text
+)
+</pre>
+<H3>Source</H3>
+This table contains the list of log file sources. The definition of the table is as follows:
+<pre>
+(
+    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
+)
+</pre>
+<H3>Usagestat</H3>
+This table contains a historical record of pages requested and blocked by hour. The definition of the table is as follows:
+<pre>
+(
+    sourcename character varying(40) NOT NULL,
+    date timestamp(0) without time zone NOT NULL,
+    numrequest integer,
+    numblock integer
+)
+</pre>
+<H3>Watchlist</H3>
+This table contains the user watch list. The definition of the table is as follows:
+<pre>
+(
+    clientuserid character varying(64) NOT NULL,
+    expiredatetime timestamp(0) without time zone NOT NULL
+)
+</pre>