Re: FR, SQL backend and Foreign keys

2011-09-28 Thread Fabien COMBERNOUS 

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.sql2011-09-28 10:42:08.0 +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.sql2011-09-28 10:46:42.0 +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 
*Tel: +33(0)9 5279 5202*
Kezia
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: FR, SQL backend and Foreign keys

2011-09-28 Thread Fabien COMBERNOUS 

On 28/09/2011 11:52, Fajar A. Nugraha wrote:

On Wed, Sep 28, 2011 at 3:50 PM, Fabien COMBERNOUS
  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 
*Tel: +33(0)9 5279 5202*
Kezia
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: FR, SQL backend and Foreign keys

2011-09-28 Thread Fajar A. Nugraha 
On Wed, Sep 28, 2011 at 3:50 PM, Fabien COMBERNOUS
 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

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: FR, SQL backend and Foreign keys

2011-09-28 Thread Alan DeKok 
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.sql2011-09-28 10:42:08.0 +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.sql2011-09-28 10:46:42.0 +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

Re: FR, SQL backend and Foreign keys

2011-09-28 Thread Fabien COMBERNOUS 

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.sql2011-09-28 10:42:08.0 +0200
+++ schema_withFK.sql2011-09-28 10:46:42.0 +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 
*Tel: +33(0)9 5279 5202*
Kezia
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: FR, SQL backend and Foreign keys

2011-09-27 Thread Arran Cudbard-Bell 

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.cudba...@freeradius.org

Betelwiki, Betelwiki, Betelwiki http://wiki.freeradius.org/ !


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: FR, SQL backend and Foreign keys

2011-09-27 Thread Alan DeKok 
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.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

FR, SQL backend and Foreign keys

2011-09-27 Thread Fabien COMBERNOUS 

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 
*Tel: +33(0)9 5279 5202*
Kezia
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html