FR, SQL backend and Foreign keys
Hi, In the samples schema.sql proposed for SQL backend, no foreign keys are used. Any reason ? With ON DELETE CASCADE or ON UPDATE CASCADE constraint it should be easier to manage updates or deletetion of records ? Am i missing something ? -- *Fabien COMBERNOUS* /unix system engineer/ www.kezia.com <http://www.kezia.com/> *Tel: +33(0)9 5279 5202* Kezia
Fabien COMBERNOUS wrote:
In the samples schema.sql proposed for SQL backend, no foreign keys are used. Any reason ?
The schema is designed to be simple. What foreign keys would you propose it use?
With ON DELETE CASCADE or ON UPDATE CASCADE constraint it should be easier to manage updates or deletetion of records ?
How? Please explain using the existing schema.
Am i missing something ?
Propose a new schema which is (a) compatible with the existing usage, and (b) has the features you want. Alan DeKok.
On 27/09/2011 11:25, Alan DeKok wrote:
Fabien COMBERNOUS wrote:
In the samples schema.sql proposed for SQL backend, no foreign keys are used. Any reason ? The schema is designed to be simple. What foreign keys would you propose it use?
here a patch proposed : --- schema_orig.sql 2011-09-28 10:42:08.000000000 +0200 +++ schema_withFK.sql 2011-09-28 10:46:42.000000000 +0200 @@ -63,8 +63,8 @@ op char(2) NOT NULL DEFAULT '==', value varchar(253) NOT NULL default '', PRIMARY KEY (id), - KEY username (username(32)) -) ; + KEY username (username) +) ENGINE=InnoDB; # # Table structure for table 'radgroupcheck' @@ -117,8 +117,10 @@ username varchar(64) NOT NULL default '', groupname varchar(64) NOT NULL default '', priority int(11) NOT NULL default '1', - KEY username (username(32)) -) ; + INDEX username (username), + FOREIGN KEY (`username`) REFERENCES radcheck(username) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; -- *Fabien COMBERNOUS* /unix system engineer/ www.kezia.com <http://www.kezia.com/> *Tel: +33(0)9 5279 5202* Kezia
Fabien COMBERNOUS wrote:
here a patch proposed :
A bit of explanation would help. It looks reasonable, but I'd want someone to try it before putting it into the server.
--- schema_orig.sql 2011-09-28 10:42:08.000000000 +0200
*PLEASE* use full paths. There are 4-5 SQL backends in the server. Which one is this modifying? Don't make us guess...
+++ schema_withFK.sql 2011-09-28 10:46:42.000000000 +0200 @@ -63,8 +63,8 @@ op char(2) NOT NULL DEFAULT '==', value varchar(253) NOT NULL default '', PRIMARY KEY (id), - KEY username (username(32)) -) ; + KEY username (username) +) ENGINE=InnoDB;
# # Table structure for table 'radgroupcheck' @@ -117,8 +117,10 @@ username varchar(64) NOT NULL default '', groupname varchar(64) NOT NULL default '', priority int(11) NOT NULL default '1', - KEY username (username(32)) -) ; + INDEX username (username), + FOREIGN KEY (`username`) REFERENCES radcheck(username) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB;
On 28/09/2011 11:45, Alan DeKok wrote:
Fabien COMBERNOUS wrote:
here a patch proposed : A bit of explanation would help. Foreign key : add a username in radusergroup table needs to exist in radcheck table and be equal. On cascade : if you update ou delete a username in radcheck table, the same action is done in radusergroup table.
It looks reasonable, but I'd want someone to try it before putting it into the server.
--- schema_orig.sql 2011-09-28 10:42:08.000000000 +0200 *PLEASE* use full paths. There are 4-5 SQL backends in the server. Which one is this modifying? Don't make us guess...
+++ schema_withFK.sql 2011-09-28 10:46:42.000000000 +0200 @@ -63,8 +63,8 @@ op char(2) NOT NULL DEFAULT '==', value varchar(253) NOT NULL default '', PRIMARY KEY (id), - KEY username (username(32)) -) ; + KEY username (username) +) ENGINE=InnoDB;
# # Table structure for table 'radgroupcheck' @@ -117,8 +117,10 @@ username varchar(64) NOT NULL default '', groupname varchar(64) NOT NULL default '', priority int(11) NOT NULL default '1', - KEY username (username(32)) -) ; + INDEX username (username), + FOREIGN KEY (`username`) REFERENCES radcheck(username) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB;
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- *Fabien COMBERNOUS* /unix system engineer/ www.kezia.com <http://www.kezia.com/> *Tel: +33(0)9 5279 5202* Kezia
On Wed, Sep 28, 2011 at 3:50 PM, Fabien COMBERNOUS <fcombernous@kezia.com> wrote:
# Table structure for table 'radgroupcheck' @@ -117,8 +117,10 @@ username varchar(64) NOT NULL default '', groupname varchar(64) NOT NULL default '', priority int(11) NOT NULL default '1', - KEY username (username(32)) -) ; + INDEX username (username),
You should stick with either "KEY" or "INDEX" for consistency (although both are synonyms in mysql).
+ FOREIGN KEY (`username`) REFERENCES radcheck(username) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB;
If you use foreign key for radgroupcheck, is there a reason not to use it on other tables (e.g. radreply, radgroupreply)? One exception might be radacct, where you might want to keep accounting records even for old/deleted accounts. -- Fajar
On 28/09/2011 11:52, Fajar A. Nugraha wrote:
On Wed, Sep 28, 2011 at 3:50 PM, Fabien COMBERNOUS <fcombernous@kezia.com> wrote:
# Table structure for table 'radgroupcheck' @@ -117,8 +117,10 @@ username varchar(64) NOT NULL default '', groupname varchar(64) NOT NULL default '', priority int(11) NOT NULL default '1', - KEY username (username(32)) -) ; + INDEX username (username), You should stick with either "KEY" or "INDEX" for consistency (although both are synonyms in mysql).
You are right. It is a mistake from me. We can stick with KEY.
+ FOREIGN KEY (`username`) REFERENCES radcheck(username) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; If you use foreign key for radgroupcheck, is there a reason not to use it on other tables (e.g. radreply, radgroupreply)?
One exception might be radacct, where you might want to keep accounting records even for old/deleted accounts.
I proposed a patch with only radcheck and radusergroup because i can test this here. I'm not a radius specialist, i prefer to make a first step before to run. -- *Fabien COMBERNOUS* /unix system engineer/ www.kezia.com <http://www.kezia.com/> *Tel: +33(0)9 5279 5202* Kezia
On 27 Sep 2011, at 11:13, Fabien COMBERNOUS wrote:
Hi,
In the samples schema.sql proposed for SQL backend, no foreign keys are used. Any reason ?
With ON DELETE CASCADE or ON UPDATE CASCADE constraint it should be easier to manage updates or deletetion of records ?
Am i missing something ?
Nope... Feel free to send patches... -Arran Arran Cudbard-Bell a.cudbardb@freeradius.org Betelwiki, Betelwiki, Betelwiki.... http://wiki.freeradius.org/ !
participants (4)
-
Alan DeKok -
Arran Cudbard-Bell -
Fabien COMBERNOUS -
Fajar A. Nugraha