Updated schema with more restrictions on field values, from

	Thomas Huehn <huehn@eozaen.net>

diff --git a/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql b/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
index 1cc6e9d..1cb4d17 100644 (file)
--- a/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
+++ b/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
@@ -8,69 +8,36 @@
 #                                                                         #
 #                                   Mike Machado <mike@innercite.com>     #
 ###########################################################################
-# MySQL dump 4.0
-#
-# Host: localhost    Database: radius
-#--------------------------------------------------------
-
-#
-# Table structure for table 'dictionary'
-#
-CREATE TABLE dictionary (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  Type varchar(30),
-  Attribute varchar(32),
-  Value varchar(32),
-  Format varchar(20),
-  Vendor varchar(32),
-  PRIMARY KEY (id)
-);
-
-#
-# Table structure for table 'nas'
-#
-CREATE TABLE nas (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  nasname varchar(128),
-  shortname varchar(32),
-  ipaddr varchar(15),
-  type varchar(30),
-  ports int(5),
-  secret varchar(60),
-  community varchar(50),
-  snmp varchar(10),
-  PRIMARY KEY (id)
-);
-
 #
 # Table structure for table 'radacct'
 #
+
 CREATE TABLE radacct (
-  RadAcctId bigint(21) DEFAULT '0' NOT NULL auto_increment,
-  AcctSessionId varchar(32) DEFAULT '' NOT NULL,
-  AcctUniqueId  varchar(32) DEFAULT '' NOT NULL,
-  UserName varchar(32) DEFAULT '' NOT NULL,
-  Realm varchar(30) DEFAULT '',
-  NASIPAddress varchar(15) DEFAULT '' NOT NULL,
-  NASPortId int(12),
-  NASPortType varchar(32),
-  AcctStartTime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
-  AcctStopTime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
-  AcctSessionTime int(12),
-  AcctAuthentic varchar(32),
-  ConnectInfo_start varchar(32),
-  ConnectInfo_stop varchar(32),
-  AcctInputOctets int(12),
-  AcctOutputOctets int(12),
-  CalledStationId varchar(10) DEFAULT '' NOT NULL,
-  CallingStationId varchar(10) DEFAULT '' NOT NULL,
-  AcctTerminateCause varchar(32) DEFAULT '' NOT NULL,
-  ServiceType varchar(32),
-  FramedProtocol varchar(32),
-  FramedIPAddress varchar(15) DEFAULT '' NOT NULL,
-  AcctStartDelay int(12),
-  AcctStopDelay int(12),
-  PRIMARY KEY (RadAcctId),
+  RadAcctId bigint(21) NOT NULL auto_increment,
+  AcctSessionId varchar(32) NOT NULL default '',
+  AcctUniqueId varchar(32) NOT NULL default '',
+  UserName varchar(64) NOT NULL default '',
+  Realm varchar(64) default '',
+  NASIPAddress varchar(15) NOT NULL default '',
+  NASPortId int(12) default NULL,
+  NASPortType varchar(32) default NULL,
+  AcctStartTime datetime NOT NULL default '0000-00-00 00:00:00',
+  AcctStopTime datetime NOT NULL default '0000-00-00 00:00:00',
+  AcctSessionTime int(12) default NULL,
+  AcctAuthentic varchar(32) default NULL,
+  ConnectInfo_start varchar(32) default NULL,
+  ConnectInfo_stop varchar(32) default NULL,
+  AcctInputOctets int(12) default NULL,
+  AcctOutputOctets int(12) default NULL,
+  CalledStationId varchar(10) NOT NULL default '',
+  CallingStationId varchar(10) NOT NULL default '',
+  AcctTerminateCause varchar(32) NOT NULL default '',
+  ServiceType varchar(32) default NULL,
+  FramedProtocol varchar(32) default NULL,
+  FramedIPAddress varchar(15) NOT NULL default '',
+  AcctStartDelay int(12) default NULL,
+  AcctStopDelay int(12) default NULL,
+  PRIMARY KEY  (RadAcctId),
   KEY UserName (UserName),
   KEY FramedIPAddress (FramedIPAddress),
   KEY AcctSessionId (AcctSessionId),
@@ -78,87 +45,70 @@ CREATE TABLE radacct (
   KEY AcctStartTime (AcctStartTime),
   KEY AcctStopTime (AcctStopTime),
   KEY NASIPAddress (NASIPAddress)
-);
+) ;
 
 #
 # Table structure for table 'radcheck'
 #
+
 CREATE TABLE radcheck (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  UserName varchar(30) DEFAULT '' NOT NULL,
-  Attribute varchar(30),
-  Value varchar(253),
-  op char(2),
-  PRIMARY KEY (id),
-  KEY UserName (UserName)
-);
+  id int(11) unsigned NOT NULL auto_increment,
+  UserName varchar(64) NOT NULL default '',
+  Attribute varchar(32)  NOT NULL default '',
+  Value varchar(64) NOT NULL default '',
+  PRIMARY KEY  (id),
+  KEY UserName (UserName(32))
+) ;
 
 #
 # Table structure for table 'radgroupcheck'
 #
+
 CREATE TABLE radgroupcheck (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  GroupName varchar(20) DEFAULT '' NOT NULL,
-  Attribute varchar(40),
-  Value varchar(253),
-  op char(2),
-  PRIMARY KEY (id),
-  KEY GroupName (GroupName)
-);
+  id int(11) unsigned NOT NULL auto_increment,
+  GroupName varchar(64) NOT NULL default '',
+  Attribute varchar(32)  NOT NULL default '',
+  Value varchar(54)  NOT NULL default '',
+  PRIMARY KEY  (id),
+  KEY GroupName (GroupName(32))
+) ;
 
 #
 # Table structure for table 'radgroupreply'
 #
+
 CREATE TABLE radgroupreply (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  GroupName varchar(20) DEFAULT '' NOT NULL,
-  Attribute varchar(40),
-  Value varchar(253),
-  op char(2),
-  PRIMARY KEY (id),
-  KEY GroupName (GroupName)
-);
+  id int(11) unsigned NOT NULL auto_increment,
+  GroupName varchar(64) NOT NULL default '',
+  Attribute varchar(32)  NOT NULL default '',
+  Value varchar(64)  NOT NULL default '',
+  prio int unsigned NOT NULL default '0',
+  PRIMARY KEY  (id),
+  KEY GroupName (GroupName(32))
+) ;
 
 #
 # Table structure for table 'radreply'
 #
+
 CREATE TABLE radreply (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  UserName varchar(30) DEFAULT '' NOT NULL,
-  Attribute varchar(30),
-  Value varchar(253),
-  op char(2),
-  PRIMARY KEY (id),
-  KEY UserName (UserName)
-);
+  id int(11) unsigned NOT NULL auto_increment,
+  UserName varchar(64) NOT NULL default '',
+  Attribute varchar(32) NOT NULL default '',
+  Value varchar(64) NOT NULL default '',
+  PRIMARY KEY  (id),
+  KEY UserName (UserName(32))
+) ;
 
-#
-# Table structure for table 'usergroup'
-#
-CREATE TABLE usergroup (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  UserName varchar(30) DEFAULT '' NOT NULL,
-  GroupName varchar(30),
-  PRIMARY KEY (id),
-  KEY UserName (UserName)
-);
 
 #
-# Table structure for table 'realmgroup'
+# Table structure for table 'usergroup'
 #
-CREATE TABLE realmgroup (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  RealmName varchar(30) DEFAULT '' NOT NULL,
-  GroupName varchar(30),
-  PRIMARY KEY (id),
-  KEY RealmName (RealmName)
-);
 
-CREATE TABLE realms (
-  id int(10) DEFAULT '0' NOT NULL auto_increment,
-  realmname varchar(64),
-  nas varchar(128),
-  authport int(5),
-  options varchar(128) DEFAULT '',
-  PRIMARY KEY (id)
-);
+CREATE TABLE usergroup (
+  id int(11) unsigned NOT NULL auto_increment,
+  UserName varchar(64) NOT NULL default '',
+  GroupName varchar(64) NOT NULL default '',
+  PRIMARY KEY  (id),
+  KEY UserName (UserName(32))
+) ;