why not use id field in the table of JDBCRealm

[Shanbo Li <sh...@gmail.com>]

Hi there,

i am thinking of it is a good design of JDBCRealm implementation.

the table in HOW-TO looks like this:


------------------------------------------------------------------------------------------------------------------------------------------------------

 create table users (
  user_name         varchar(15) not null primary key,
  user_pass         varchar(15) not null
);

create table user_roles (
  user_name         varchar(15) not null,
  role_name         varchar(15) not null,
  primary key (user_name, role_name)
);

------------------------------------------------------------------------------------------------------------------------------------------------------




obviously *it missed the id field*. it would be much better that each table
have an id field. and make the table looks like this:

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(225) NOT NULL,
  `password` varchar(225) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for roles
-- ----------------------------
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` int(11) NOT NULL auto_increment,
  `role` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique_role` (`role`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for users_roles
-- ----------------------------
DROP TABLE IF EXISTS `users_roles`;
CREATE TABLE `users_roles` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique_user_id_and_role_id` (`user_id`,`role_id`),
  KEY `fk_roles_by_role_id` (`role_id`),
  CONSTRAINT `fk_roles_by_role_id` FOREIGN KEY (`role_id`) REFERENCES
`roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_users_by_user_id` FOREIGN KEY (`user_id`) REFERENCES
`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


i mean the bridge-table use id to associate users and roles


-- 
Shanbo Li

Master student
Software Engineering of Distributed Systems, KTH

Re: why not use id field in the table of JDBCRealm

[Mark Thomas <ma...@apache.org>]

Shanbo Li wrote:
>
> obviously *it missed the id field*. it would be much better that each table
> have an id field. and make the table looks like this:

Obviously *this was done for a good reason*. The how to is intended for any
database. The changes you propose are non-standard and hence will not work
in many databases.

Users are free to organise their data in any way they wish. The only
constraint is how that data is presented to the JDBC realm. Many databases
support the concept of views which can be used for this purpose.

Mark



---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@tomcat.apache.org
For additional commands, e-mail: dev-help@tomcat.apache.org