Documentation updates (sql_counter with postgresql)

[Stephen Gran]

Hello all,

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.

There are 3 long lines in doc/rlm_sqlcounter that I see wrap badly -
sorry about that.  I was just trying to stay with the style of the file.

Thanks,

Index: doc/rlm_sqlcounter
===================================================================
--- doc/rlm_sqlcounter  (revision 939)
+++ doc/rlm_sqlcounter  (working copy)
@@ -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,39 @@

 #-----#

+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

Index: raddb/radiusd.conf.in
===================================================================
--- raddb/radiusd.conf.in       (revision 939)
+++ raddb/radiusd.conf.in       (working copy)
@@ -1454,24 +1454,42 @@
                # This query properly handles calls that span from the
                # previous reset period into the current period but
                # involves more work for the SQL server than those
-               # below
+               # 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:
+               # Note - if you are running 7.x, you may not have a GREATER
+               # function.  Please see doc/sql_counter for an example.
+               # 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
+               # 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 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 {
@@ -1484,7 +1502,9 @@
                # This query properly handles calls that span from the
                # previous reset period into the current period but
                # involves more work for the SQL server than those
-               # below
+               # 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 \

-- 
 --------------------------------------------------------------------------
|  Stephen Gran                  | What nonsense people talk about happy   |
|  steve at lobefin.net             | marriages!  A man can be happy with any |
|  http://www.lobefin.net/~steve | woman so long as he doesn't love her.   |
|                                | -- Oscar Wilde                          |
 --------------------------------------------------------------------------
-------------- 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/20060728/391a6ed5/attachment.pgp>