source: branches/mvc/SQL/Updates/40.sql

RENAME TABLE `measure` TO `Measure` 
ALTER TABLE `Measure`  ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


CREATE TABLE IF NOT EXISTS `User` (
  `Id` int(11) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL,
  `Login` varchar(255) NOT NULL,
  `Password` varchar(255) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `LastIpAddress` varchar(16) NOT NULL,
  `LastLoginTime` datetime NOT NULL,
  `RegistrationTime` datetime NOT NULL,
  `Locked` int(11) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

INSERT INTO `User` (`Id`, `Name`, `Login`, `Password`, `Email`, `LastIpAddress`, `LastLoginTime`, `RegistrationTime`, `Locked`) VALUES
(10, 'Návštěvník', 'anonym', 'dasdsada', '', '', '2009-08-28 11:28:31', '2009-08-28 11:28:31', 0);

CREATE TABLE IF NOT EXISTS `UserOnline` (
  `Id` int(11) NOT NULL auto_increment,
  `User` int(11) NOT NULL default '0',
  `ActivityTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `LoginTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `SessionId` varchar(255) character set utf8 collate utf8_czech_ci NOT NULL default '',
  `IpAddress` varchar(16) character set utf8 collate utf8_czech_ci NOT NULL default '',
  `HostName` varchar(255) character set utf8 collate utf8_czech_ci NOT NULL default '',
  `ScriptName` varchar(255) character set utf8 collate utf8_czech_ci NOT NULL,
  PRIMARY KEY  (`Id`),
  KEY `User` (`User`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `UserOnline`
  ADD CONSTRAINT `UserOnline_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);


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 AUTO_INCREMENT=1;

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 `Action` (`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 AUTO_INCREMENT=1 ;

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 AUTO_INCREMENT=1 ;

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

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

CREATE TABLE `MeasureGroup` (
`Id` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 255 ) NOT NULL ,
`Parent` INT NOT NULL ,
PRIMARY KEY ( `Id` )
) ENGINE = InnoDB ;

ALTER TABLE `MeasureGroup` ADD INDEX ( `Parent` );
ALTER TABLE `MeasureGroup` ADD FOREIGN KEY ( `Parent` ) REFERENCES `MeasureGroup` ( `Id` ); 
ALTER TABLE `MeasureGroup` CHANGE `Parent` `Parent` INT( 11 ) NULL ;

ALTER TABLE `Measure` ADD `Group` INT NULL AFTER `Id` ;
ALTER TABLE `Measure` ADD INDEX ( `Group` ) ;
ALTER TABLE `Measure` ADD FOREIGN KEY ( `Group` ) REFERENCES `MeasureGroup` ( `Id` );
ALTER TABLE `Measure` DROP `OldName` ;
ALTER TABLE `Measure` DROP `PermissionView`,   DROP `PermissionAdd`;

ALTER TABLE `Measure` CHANGE `Name` `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `Description` `Description` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `Info` `Info` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `DataTable` `DataTable` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'data',
CHANGE `DataType` `DataType` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;