Documentation updates (sql_counter with postgresql)

[Stephen Gran]

On Fri, Jul 28, 2006 at 11:32:25AM +0200, Nicolas Baradakis said:
> Stephen Gran wrote:
> 
> > After making this work for myself, I thought I would send the
> > documentation on so that others could do it more easily.  There are some
> > duplicates that could be cut, but I leave that to you to decide.  I have
> > tried to be clear without being completely repetitive.  Hopefully others
> > will find it useful.
> 
> Sorry, I can't apply the patch as it is: it seems your editor (or your
> mail client) has replaced tabs with spaces.

Sorry, second try attached.
-- 
 --------------------------------------------------------------------------
|  Stephen Gran                  | Most general statements are false,      |
|  steve at lobefin.net             | including this one.   -- Alexander      |
|  http://www.lobefin.net/~steve | Dumas                                   |
 --------------------------------------------------------------------------
-------------- next part --------------
--- doc/rlm_sqlcounter.old	2006-07-29 17:26:51.000000000 +0100
+++ doc/rlm_sqlcounter	2006-07-29 17:27:35.000000000 +0100
@@ -19,7 +19,7 @@
 
 [1] Create a text file called sqlcounter.conf in the same
 directory where radiusd.conf resides (usually /usr/local/etc/raddb)
-with the following content:
+with the following content (for mysql):
 
 #-----#
 sqlcounter noresetcounter {
@@ -59,6 +59,41 @@
 
 #-----#
 
+The respective lines for postgresql are:
+
+query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
+query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4
+ AcctSessionTime > '%b'"
+query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4
+ AcctSessionTime > '%b'"
+
+If you are running postgres 7.x, you may not have a GREATER function.
+
+An example of one is:
+
+CREATE OR REPLACE FUNCTION "greater"(integer, integer) RETURNS integer AS '
+DECLARE
+  res INTEGER;
+  one INTEGER := 0;
+  two INTEGER := 0;
+BEGIN
+  one = $1;
+  two = $2;
+  IF one IS NULL THEN
+    one = 0;
+  END IF;
+  IF two IS NULL THEN
+    two = 0;
+  END IF;
+  IF one > two THEN
+    res := one;
+  ELSE
+    res := two;
+  END IF;
+  RETURN res;
+END;
+' LANGUAGE 'plpgsql';
+
 [2] Include the above file to radiusd.conf by adding a line in
 modules{ } section
 
--- raddb/radiusd.conf.in.old	2006-07-29 17:29:09.000000000 +0100
+++ raddb/radiusd.conf.in	2006-07-29 17:34:09.000000000 +0100
@@ -1455,23 +1455,42 @@
 		# previous reset period into the current period but
 		# involves more work for the SQL server than those
 		# below
+		# for mysql:
 		query = "SELECT SUM(AcctSessionTime - \
                  GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
                  FROM radacct WHERE UserName='%{%k}' AND \
                  UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
 
+		# for postgresql:
+		#query = "SELECT SUM(AcctSessionTime - \
+                # GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \
+                # FROM radacct WHERE UserName='%{%k}' AND \
+                # AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
+
 		# This query ignores calls that started in a previous
 		# reset period and continue into into this one. But it
 		# is a little easier on the SQL server
+		# For mysql:
 #		query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
 #                UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"
 
+		# For postgresql:
+#		query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
+#                UserName='%{%k}' AND AND AcctStartTime::ABSTIME::INT4 > '%b'"
+
 		# This query is the same as above, but demonstrates an
 		# additional counter parameter '%e' which is the
 		# timestamp for the end of the period
+		# For mysql:
 #		query = "SELECT SUM(AcctSessionTime) FROM radacct \
 #                WHERE UserName='%{%k}' AND AcctStartTime BETWEEN \
 #                FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
+
+		# For postgresql:
+#		query = "SELECT SUM(AcctSessionTime) FROM radacct \
+#                WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 \
+#                BETWEEN '%b' AND '%e'"
+
 	}
 
 	sqlcounter monthlycounter {
@@ -1485,6 +1504,8 @@
 		# previous reset period into the current period but
 		# involves more work for the SQL server than those
 		# below
+		# The same notes above about the differences between mysql
+		# versus postgres queries apply here.
 		query = "SELECT SUM(AcctSessionTime - \
                  GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
                  FROM radacct WHERE UserName='%{%k}' AND \
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20060729/04fc0959/attachment.pgp>