Document the setup of sqlcounter against a PostgreSQL database.

	Patch from Stephen Gran <sgran@debian.org>

diff --git a/doc/rlm_sqlcounter b/doc/rlm_sqlcounter
index f778927..57857ed 100644 (file)
--- a/doc/rlm_sqlcounter
+++ b/doc/rlm_sqlcounter
@@ -19,7 +19,7 @@ of radiusd.conf
 
 [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,39 @@ sqlcounter monthlycounter {
 
 #-----#
 
+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
 

diff --git a/raddb/radiusd.conf.in b/raddb/radiusd.conf.in
index c43163c..797c835 100644 (file)
--- a/raddb/radiusd.conf.in
+++ b/raddb/radiusd.conf.in
@@ -1450,23 +1450,41 @@ $INCLUDE ${confdir}/eap.conf
                # 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 {
@@ -1480,6 +1498,8 @@ $INCLUDE ${confdir}/eap.conf
                # 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 \