From: Stefan Paetow <stefan.paetow@jisc.ac.uk>
Date: Thu, 18 May 2017 15:28:27 +0000 (+0100)
Subject: Adjust the default table names to be SQL-standard compliant. Add the other supported... 
X-Git-Tag: release_3_0_14~9^2~2
X-Git-Url: http://www.project-moonshot.org/gitweb/?p=freeradius.git;a=commitdiff_plain;h=9dd98e93fecfce7be5f7ae550759ce00e6342113

Adjust the default table names to be SQL-standard compliant. Add the other supported SQL dialects.
---

diff --git a/raddb/mods-available/moonshot-targeted-ids b/raddb/mods-available/moonshot-targeted-ids
index 87ea1b5..bcf69bc 100644
--- a/raddb/mods-available/moonshot-targeted-ids
+++ b/raddb/mods-available/moonshot-targeted-ids
@@ -3,7 +3,7 @@
 #  $Id$
 
 #
-#  Write Moonshot-*-TargetedId to the database.
+#  Write Moonshot-*-TargetedId (MSTID) to the database.
 #
 #  Schema	raddb/sql/moonshot-targeted-ids/<DB>/schema.sql
 #  Queries	raddb/sql/moonshot-targeted-ids/<DB>/queries.conf
@@ -20,7 +20,7 @@ sql moonshot_tid_sql {
 	# The sub-module to use to execute queries. This should match
 	# the database you're attempting to connect to.
 	#
-	# There are CUI queries available for:
+	# There are MSTID queries available for:
 	#    * rlm_sql_mysql
 	#    * rlm_sql_postgresql
 	#    * rlm_sql_sqlite
@@ -33,8 +33,8 @@ sql moonshot_tid_sql {
 		bootstrap = ${modconfdir}/${..:name}/moonshot-targeted-ids/sqlite/schema.sql
 	}
 
-	# Write CUI queries to a logfile. Useful for debugging.
-#	logfile = ${logdir}/cuilog.sql
+	# Write MSTID queries to a logfile. Useful for debugging.
+#	logfile = ${logdir}/moonshot-targeted-id-log.sql
 
 	pool {
 		start = 5
@@ -46,7 +46,11 @@ sql moonshot_tid_sql {
 		idle_timeout = 60
 	}
 
-	moonshot_tid_table = "moonshot-targeted-ids"
+	#  If you adjust the table name here, you must also modify the table name in
+	#  the moonshot_get_targeted_id.post-auth policy in policy.d/moonshot-targeted-ids
+	#  and the schema.sql files in the mods-config/sql/moonshot-targeted-ids tree.
+	#
+	moonshot_tid_table = "moonshot_targeted_ids"
 	sql_user_name = "%{User-Name}"
 
 	$INCLUDE ${modconfdir}/${.:name}/moonshot-targeted-ids/${dialect}/queries.conf
diff --git a/raddb/mods-config/sql/moonshot-targeted-ids/mysql/queries.conf b/raddb/mods-config/sql/moonshot-targeted-ids/mysql/queries.conf
new file mode 100644
index 0000000..68306db
--- /dev/null
+++ b/raddb/mods-config/sql/moonshot-targeted-ids/mysql/queries.conf
@@ -0,0 +1,15 @@
+# -*- text -*-
+#
+#  moonshot-targeted-ids/mysql/queries.conf -- Queries to update a MySQL Moonshot-Targeted-Ids table.
+#
+#  $Id$
+
+post-auth {
+	#  Query to store the Moonshot-*-TargetedId
+	query = "\
+		INSERT IGNORE INTO ${..moonshot_tid_table} \
+			(gss_acceptor, namespace, username, targeted_id) \
+		VALUES \
+			('%{control:Moonshot-MSTID-GSS-Acceptor}', '%{control:Moonshot-MSTID-Namespace}', \
+			'%{tolower:%{User-Name}}', '%{control:Moonshot-MSTID-TargetedId}')"
+}
diff --git a/raddb/mods-config/sql/moonshot-targeted-ids/mysql/schema.sql b/raddb/mods-config/sql/moonshot-targeted-ids/mysql/schema.sql
new file mode 100644
index 0000000..8a33dc1
--- /dev/null
+++ b/raddb/mods-config/sql/moonshot-targeted-ids/mysql/schema.sql
@@ -0,0 +1,8 @@
+CREATE TABLE `moonshot_targeted_ids` (
+  `gss_acceptor` varchar(254) NOT NULL default '',
+  `namespace` varchar(36) NOT NULL default '',
+  `username` varchar(64) NOT NULL default '',
+  `targeted_id` varchar(128) NOT NULL default '',
+  `creationdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
+  PRIMARY KEY  (`username`,`gss_acceptor`,`namespace`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/raddb/mods-config/sql/moonshot-targeted-ids/postgresql/queries.conf b/raddb/mods-config/sql/moonshot-targeted-ids/postgresql/queries.conf
new file mode 100644
index 0000000..f757a87
--- /dev/null
+++ b/raddb/mods-config/sql/moonshot-targeted-ids/postgresql/queries.conf
@@ -0,0 +1,15 @@
+# -*- text -*-
+#
+#  moonshot-targeted-ids/postgresql/queries.conf -- Queries to update a PostgreSQL Moonshot-*-Targeted-Ids table.
+#
+#  $Id$
+
+post-auth {
+	#  Query to store the Moonshot-*-TargetedId
+	query = "\
+		INSERT INTO ${..moonshot_tid_table} \
+			(gss_acceptor, namespace, username, targeted_id) \
+		VALUES \
+			('%{control:Moonshot-MSTID-GSS-Acceptor}', '%{control:Moonshot-MSTID-Namespace}', \
+			'%{tolower:%{User-Name}}', '%{control:Moonshot-MSTID-TargetedId}')"
+}
diff --git a/raddb/mods-config/sql/moonshot-targeted-ids/postgresql/schema.sql b/raddb/mods-config/sql/moonshot-targeted-ids/postgresql/schema.sql
new file mode 100644
index 0000000..649c627
--- /dev/null
+++ b/raddb/mods-config/sql/moonshot-targeted-ids/postgresql/schema.sql
@@ -0,0 +1,8 @@
+CREATE TABLE moonshot_targeted_ids (
+  gss_acceptor varchar(254) NOT NULL DEFAULT '',
+  namespace varchar(36) NOT NULL DEFAULT '',
+  username varchar(64) NOT NULL DEFAULT '',
+  targeted_id varchar(128) NOT NULL DEFAULT '',
+  creationdate TIMESTAMP with time zone NOT NULL default 'now()',
+  PRIMARY KEY  (username, gss_acceptor, namespace)
+);
diff --git a/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/queries.conf b/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/queries.conf
index 9418b17..8cdb803 100644
--- a/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/queries.conf
+++ b/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/queries.conf
@@ -7,7 +7,7 @@
 post-auth {
 	#  Query to store the Moonshot-*-TargetedId
 	query = "\
-		INSERT INTO `${..moonshot_tid_table}` \
+		INSERT INTO ${..moonshot_tid_table} \
 			(gss_acceptor, namespace, username, targeted_id) \
 		VALUES \
 			('%{control:Moonshot-MSTID-GSS-Acceptor}', '%{control:Moonshot-MSTID-Namespace}', \
diff --git a/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/schema.sql b/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/schema.sql
index 50241c7..71195ad 100644
--- a/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/schema.sql
+++ b/raddb/mods-config/sql/moonshot-targeted-ids/sqlite/schema.sql
@@ -1,4 +1,4 @@
-CREATE TABLE `moonshot-targeted-ids` (
+CREATE TABLE `moonshot_targeted_ids` (
   `gss_acceptor` varchar(254) NOT NULL default '',
   `namespace` varchar(36) NOT NULL default '',
   `username` varchar(64) NOT NULL default '',
diff --git a/raddb/policy.d/moonshot-targeted-ids b/raddb/policy.d/moonshot-targeted-ids
index 40682fa..ae8941e 100644
--- a/raddb/policy.d/moonshot-targeted-ids
+++ b/raddb/policy.d/moonshot-targeted-ids
@@ -208,7 +208,7 @@ moonshot_get_targeted_id.post-auth {
 		#
 		update control {
 			Moonshot-MSTID-TargetedId := "%{moonshot_tid_sql:\
-				SELECT targeted_id FROM `moonshot-targeted-ids` \
+				SELECT targeted_id FROM moonshot_targeted_ids \
 				WHERE gss_acceptor = '%{control:Moonshot-MSTID-GSS-Acceptor}' \
 				AND namespace = '%{control:Moonshot-MSTID-Namespace}' \
 				AND username = '%{tolower:%{User-Name}}'}"