CUI Postgres Patch

[Scott Armitage]

Hi,

a patch for adding postgres support to CUI:



diff --git a/raddb/mods-available/cui b/raddb/mods-available/cui
index 2464613..b1ba225 100644
--- a/raddb/mods-available/cui
+++ b/raddb/mods-available/cui
@@ -5,10 +5,15 @@
 #
 #  Write Chargeable-User-Identity to the database.
 #
-#  Schema      raddb/sql/mysql/cui.sql
-#  Queries     raddb/sql/mysql/cui.conf
+#  Schema      raddb/sql/DB/cui.sql
+#  Queries     raddb/sql/DB/cui.conf
 #
 sql cui {
+       #
+       #  Set the database to one of:
+       #
+       #       mysql, postgresql
+       #
        database = "mysql"
        driver = "rlm_sql_${database}"
        server = "localhost"
diff --git a/raddb/sql/postgresql/cui.conf b/raddb/sql/postgresql/cui.conf
new file mode 100644
index 0000000..d90eccc
--- /dev/null
+++ b/raddb/sql/postgresql/cui.conf
@@ -0,0 +1,31 @@
+# -*- text -*-
+
+##
+##  Queries to update the CUI table.
+##
+postauth_query = "INSERT INTO ${cui_table} \
+       (clientipaddress, callingstationid, username, cui) \
+        VALUES \
+       ('%{Client-IP-Address}', '%{Calling-Station-Id}', '%{User-Name}', '%{reply:Chargeable-User-Identity}')";
+
+accounting_start_query = "UPDATE ${cui_table} \
+       SET \
+                lastaccounting = now() \
+       WHERE clientipaddress = '%{Client-IP-Address}' \
+        AND callingstationid = '%{Calling-Station-Id}' \
+        AND username = '%{User-Name}' \
+       AND cui = '%{Chargeable-User-Identity}'";
+  
+accounting_update_query = "UPDATE ${cui_table} \
+       SET \
+                lastaccounting = now() \
+       WHERE clientipaddress = '%{Client-IP-Address}' \
+        AND callingstationid = '%{Calling-Station-Id}' \
+        AND username = '%{User-Name}' \
+       AND cui = '%{Chargeable-User-Identity}'";
+
+accounting_stop_query = "DELETE FROM ${cui_table} WHERE \
+       clientipaddress = '%{Client-IP-Address}' \
+       AND callingstationid = '%{Calling-Station-Id}' \
+       AND username = '%{User-Name}' \
+       AND cui = '%{Chargeable-User-Identity}'";
diff --git a/raddb/sql/postgresql/cui.sql b/raddb/sql/postgresql/cui.sql
new file mode 100644
index 0000000..34346da
--- /dev/null
+++ b/raddb/sql/postgresql/cui.sql
@@ -0,0 +1,14 @@
+CREATE TABLE cui (
+  clientipaddress INET NOT NULL DEFAULT '0.0.0.0',
+  callingstationid varchar(50) NOT NULL DEFAULT '',
+  username varchar(64) NOT NULL DEFAULT '',
+  cui varchar(32) NOT NULL DEFAULT '',
+  creationdate TIMESTAMP with time zone NOT NULL default 'now()',
+  lastaccounting TIMESTAMP with time zone NOT NULL default '-infinity'::timestamp,
+  PRIMARY KEY  (username, clientipaddress, callingstationid)
+);
+
+CREATE RULE postauth_query AS ON INSERT TO cui
+        WHERE EXISTS(SELECT 1 FROM cui WHERE (username, clientipaddress, callingstationid)=(NEW.username, NEW.clientipaddress, NEW.callingstationid)) 
+        DO INSTEAD UPDATE cui SET lastaccounting ='-infinity'::timestamp with time zone, cui=NEW.cui WHERE (username, clientipaddress, callingstationid)=(NEW.username, NEW.clientipaddress, NEW.callingstationid);
+

-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 203 bytes
Desc: This is a digitally signed message part
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20120628/2d8cc622/attachment.pgp>