summaryrefslogtreecommitdiffstats
path: root/raddb/mods-config/sql/cui
diff options
context:
space:
mode:
Diffstat (limited to 'raddb/mods-config/sql/cui')
-rw-r--r--raddb/mods-config/sql/cui/mysql/queries.conf50
-rw-r--r--raddb/mods-config/sql/cui/mysql/schema.sql9
-rw-r--r--raddb/mods-config/sql/cui/postgresql/queries.conf47
-rw-r--r--raddb/mods-config/sql/cui/postgresql/schema.sql14
-rw-r--r--raddb/mods-config/sql/cui/sqlite/queries.conf47
-rw-r--r--raddb/mods-config/sql/cui/sqlite/schema.sql9
6 files changed, 176 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/cui/mysql/queries.conf b/raddb/mods-config/sql/cui/mysql/queries.conf
new file mode 100644
index 0000000..f8f18ca
--- /dev/null
+++ b/raddb/mods-config/sql/cui/mysql/queries.conf
@@ -0,0 +1,50 @@
+# -*- text -*-
+#
+# cui/mysql/queries.conf -- Queries to update a MySQL CUI table.
+#
+# $Id$
+
+post-auth {
+ query = "\
+ INSERT IGNORE INTO ${..cui_table} \
+ (clientipaddress, callingstationid, username, cui, lastaccounting) \
+ VALUES \
+ ('%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}', '%{Calling-Station-Id}', \
+ '%{User-Name}', '%{reply:Chargeable-User-Identity}', NULL) \
+ ON DUPLICATE KEY UPDATE \
+ lastaccounting='0000-00-00 00:00:00', \
+ cui='%{reply:Chargeable-User-Identity}'"
+
+}
+
+accounting {
+ reference = "%{tolower:type.%{Acct-Status-Type}.query}"
+ type {
+ start {
+ query = "\
+ UPDATE ${....cui_table} SET \
+ lastaccounting = CURRENT_TIMESTAMP \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ interim-update {
+ query ="\
+ UPDATE ${....cui_table} SET \
+ lastaccounting = CURRENT_TIMESTAMP \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ stop {
+ query ="\
+ DELETE FROM ${....cui_table} \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ }
+}
diff --git a/raddb/mods-config/sql/cui/mysql/schema.sql b/raddb/mods-config/sql/cui/mysql/schema.sql
new file mode 100644
index 0000000..da9b2f7
--- /dev/null
+++ b/raddb/mods-config/sql/cui/mysql/schema.sql
@@ -0,0 +1,9 @@
+CREATE TABLE `cui` (
+ `clientipaddress` varchar(46) NOT NULL default '',
+ `callingstationid` varchar(50) NOT NULL default '',
+ `username` varchar(64) NOT NULL default '',
+ `cui` varchar(32) NOT NULL default '',
+ `creationdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ `lastaccounting` timestamp NOT NULL default '0000-00-00 00:00:00',
+ PRIMARY KEY (`username`,`clientipaddress`,`callingstationid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/raddb/mods-config/sql/cui/postgresql/queries.conf b/raddb/mods-config/sql/cui/postgresql/queries.conf
new file mode 100644
index 0000000..6c2215f
--- /dev/null
+++ b/raddb/mods-config/sql/cui/postgresql/queries.conf
@@ -0,0 +1,47 @@
+# -*- text -*-
+#
+# cui/postgresql/queries.conf -- Queries to update a PostgreSQL CUI table.
+#
+# $Id$
+
+post-auth {
+ query = "\
+ INSERT INTO ${..cui_table} \
+ (clientipaddress, callingstationid, username, cui) \
+ VALUES \
+ ('%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}', '%{Calling-Station-Id}', \
+ '%{User-Name}', '%{reply:Chargeable-User-Identity}')"
+
+}
+
+accounting {
+ reference = "%{tolower:type.%{Acct-Status-Type}.query}"
+ type {
+ start {
+ query = "\
+ UPDATE ${....cui_table} SET \
+ lastaccounting = now() \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ interim-update {
+ query ="\
+ UPDATE ${....cui_table} SET \
+ lastaccounting = now() \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ stop {
+ query ="\
+ DELETE FROM ${....cui_table} \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ }
+}
diff --git a/raddb/mods-config/sql/cui/postgresql/schema.sql b/raddb/mods-config/sql/cui/postgresql/schema.sql
new file mode 100644
index 0000000..3b24401
--- /dev/null
+++ b/raddb/mods-config/sql/cui/postgresql/schema.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);
+
diff --git a/raddb/mods-config/sql/cui/sqlite/queries.conf b/raddb/mods-config/sql/cui/sqlite/queries.conf
new file mode 100644
index 0000000..41741eb
--- /dev/null
+++ b/raddb/mods-config/sql/cui/sqlite/queries.conf
@@ -0,0 +1,47 @@
+# -*- text -*-
+#
+# cui/sqlite/queries.conf -- Queries to update a sqlite CUI table.
+#
+# $Id$
+
+post-auth {
+ query = "\
+ INSERT OR REPLACE INTO ${..cui_table} \
+ (clientipaddress, callingstationid, username, cui, lastaccounting) \
+ VALUES \
+ ('%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}', '%{Calling-Station-Id}', \
+ '%{User-Name}', '%{reply:Chargeable-User-Identity}', NULL)"
+
+}
+
+accounting {
+ reference = "%{tolower:type.%{Acct-Status-Type}.query}"
+ type {
+ start {
+ query = "\
+ UPDATE ${....cui_table} SET \
+ lastaccounting = CURRENT_TIMESTAMP \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ interim-update {
+ query ="\
+ UPDATE ${....cui_table} SET \
+ lastaccounting = CURRENT_TIMESTAMP \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ stop {
+ query ="\
+ DELETE FROM ${....cui_table} \
+ WHERE clientipaddress = '%{%{Packet-Src-IPv6-Address}:-%{Packet-Src-IP-Address}}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND username = '%{User-Name}' \
+ AND cui = '%{Chargeable-User-Identity}'"
+ }
+ }
+}
diff --git a/raddb/mods-config/sql/cui/sqlite/schema.sql b/raddb/mods-config/sql/cui/sqlite/schema.sql
new file mode 100644
index 0000000..8473534
--- /dev/null
+++ b/raddb/mods-config/sql/cui/sqlite/schema.sql
@@ -0,0 +1,9 @@
+CREATE TABLE `cui` (
+ `clientipaddress` varchar(46) NOT NULL default '',
+ `callingstationid` varchar(50) NOT NULL default '',
+ `username` varchar(64) NOT NULL default '',
+ `cui` varchar(32) NOT NULL default '',
+ `creationdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ `lastaccounting` timestamp NOT NULL default '0000-00-00 00:00:00',
+ PRIMARY KEY (`username`,`clientipaddress`,`callingstationid`)
+);