diff options
author | Ted Trask <ttrask01@yahoo.com> | 2009-10-08 16:40:58 +0000 |
---|---|---|
committer | Ted Trask <ttrask01@yahoo.com> | 2009-10-08 16:40:58 +0000 |
commit | ec988ea69c7391292777cacdc66f670b513d99f4 (patch) | |
tree | 896f8542744b05befaeea4f3047f801f8084fc04 /weblog-adhocquery-html.lsp | |
parent | 878989065d9e92ab74ad310eca886323178d44f0 (diff) | |
download | acf-weblog-8eed894129053e8a99059c6914ecc0ff98e651fa.tar.bz2 acf-weblog-8eed894129053e8a99059c6914ecc0ff98e651fa.tar.xz |
Fixed bug with adhoc query, display results in a table, example descriptions, bumped to 0.4.7v0.4.7
Diffstat (limited to 'weblog-adhocquery-html.lsp')
-rw-r--r-- | weblog-adhocquery-html.lsp | 63 |
1 files changed, 49 insertions, 14 deletions
diff --git a/weblog-adhocquery-html.lsp b/weblog-adhocquery-html.lsp index e1c79f2..d7aee5c 100644 --- a/weblog-adhocquery-html.lsp +++ b/weblog-adhocquery-html.lsp @@ -2,29 +2,61 @@ require("viewfunctions") %> +<style type="text/css"> + #content table { border-collapse: collapse; width: 100%; } + #content table td { white-space: nowrap; padding-right:20px; border-bottom:1px solid #999; } + #content table tr.mark { background: #E9E9E9; } +</style> + <% if form.value.result then %> -<H1><%= html.html_escape(form.value.result.label) %></H1> -<DL> -<% 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) %>" > -<DT>Download query result</DT><DD><input class="submit" type="submit" name="Download" value="Download"></DD> -</FORM> -</DL> + <H1><%= html.html_escape(form.value.result.label) %></H1> + <% if #form.value.result.value == 0 then %> + <p>No results, try adjusting query</p> + <% else %> + <TABLE> + <TR style="font-weight:bold;"> + <% names = {} + for name,val in pairs(form.value.result.value[1]) do + names[#names+1] = name %> + <TD class="header"><%= html.html_escape(name) %></TD> + <% end %> + </TR> + <% for i,row in ipairs(form.value.result.value) do + local a,b = math.modf((i/2)) + local mark = '' + if (b == 0) then mark=' class="mark"' end %> + <TR<%= mark %>> + <% for j,name in ipairs(names) do %> + <TD><%= html.html_escape(row[name]) %></TD> + <% end %> + </TR> + <% end %> + </TABLE> + + <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 %> <% end %> <H1><%= html.html_escape(form.label) %></H1> -<% displayformstart(form, page_info) %> -<% displayformitem(form.value.query, "query") %> 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> +<li>This statement will return the total bytes transferred by each user for the entire weblog history<pre>SELECT clientuserid, sum(bytes) AS total FROM pubweblog GROUP BY clientuserid ORDER BY total DESC</pre> +<li>This statement limits the above statement to a specific range of dates (just yesterday)<pre>SELECT clientuserid, sum(bytes) AS total FROM pubweblog WHERE logdatetime >= 'yesterday' and logdatetime < 'today' GROUP BY clientuserid ORDER BY total DESC</pre> +<li>This statement will return the number of requests and blocks by hour over the course of the entire usage history<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> +<% displayformstart(form, page_info) %> +<DL> +<% displayformitem(form.value.query, "query") %> +</DL> <% displayformend(form) %> +<br> +The available database tables and descriptions are as follows: <H3>PubWeblog and PubBlocklog</H3> <DL> These tables contain the combined squid access log and dansguardian log for every access and blocked access respectively. The definition of the table is as follows: @@ -41,6 +73,7 @@ These tables contain the combined squid access log and dansguardian log for ever shortreason text ) </pre></DL> + <H3>dbHistLog</H3> <DL> 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: @@ -65,6 +98,7 @@ This table contains the list of log file sources. The definition of the table is enabled boolean ) </pre></DL> + <H3>Usagestat</H3> <DL> This table contains a historical record of pages requested and blocked by hour. The definition of the table is as follows: @@ -76,6 +110,7 @@ This table contains a historical record of pages requested and blocked by hour. numblock integer ) </pre></DL> + <H3>Watchlist</H3> <DL> This table contains the user watch list. The definition of the table is as follows: |