source: trunk/www/SQL/Updates/75.sql

CREATE TABLE IF NOT EXISTS `Module` (
  `Id` int(11) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `PermissionAssignment` (
  `User` int(11) default NULL,
  `Group` int(11) default NULL,
  `Operation` int(11) default NULL,
  `SubGroup` int(11) default NULL,
  KEY `Group` (`Group`),
  KEY `Operation` (`Operation`),
  KEY `SubGroup` (`SubGroup`),
  KEY `User` (`User`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `PermissionGroup` (
  `Id` int(11) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `PermissionOperation` (
  `Id` int(11) NOT NULL auto_increment,
  `Module` int(11) NOT NULL,
  `Action` varchar(255) NOT NULL,
  `Item` int(11) NOT NULL,
  PRIMARY KEY  (`Id`),
  KEY `Module` (`Module`),
  KEY `Action` (`Action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `PermissionAssignment`
  ADD CONSTRAINT `PermissionAssignment_ibfk_1` FOREIGN KEY (`Group`) REFERENCES `PermissionGroup` (`Id`),
  ADD CONSTRAINT `PermissionAssignment_ibfk_2` FOREIGN KEY (`Operation`) REFERENCES `PermissionOperation` (`Id`),
  ADD CONSTRAINT `PermissionAssignment_ibfk_3` FOREIGN KEY (`SubGroup`) REFERENCES `PermissionGroup` (`Id`),
  ADD CONSTRAINT `PermissionAssignment_ibfk_4` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);

ALTER TABLE `PermissionOperation`
  ADD CONSTRAINT `PermissionOperation_ibfk_1` FOREIGN KEY (`Module`) REFERENCES `Module` (`Id`);


ALTER TABLE `User`  DROP `Role`;