/* Navicat Premium Data Transfer Source Server : Flyback Source Server Type : MySQL Source Server Version : 50077 Source Host : flyback.cnaf.infn.it Source Database : PortalUser Target Server Type : MySQL Target Server Version : 50077 File Encoding : utf-8 Date: 11/10/2011 14:57:38 PM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `IDP` -- ---------------------------- DROP TABLE IF EXISTS `IDP`; CREATE TABLE `IDP` ( `idIDP` int(5) NOT NULL auto_increment, `IDPName` varchar(75) collate utf8_bin NOT NULL, `IDPAddress` varchar(100) collate utf8_bin NOT NULL, `IDPLoginAddress` varchar(200) collate utf8_bin NOT NULL, `IDPParameterRequest` varchar(200) collate utf8_bin NOT NULL, `IDPAttributeRequest` varchar(200) collate utf8_bin NOT NULL, `IDPDescription` text collate utf8_bin NOT NULL, PRIMARY KEY (`idIDP`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Table structure for `VO` -- ---------------------------- DROP TABLE IF EXISTS `VO`; CREATE TABLE `VO` ( `idVO` int(5) NOT NULL auto_increment, `VO` varchar(100) collate utf8_bin NOT NULL, `Host` varchar(100) collate utf8_bin NOT NULL, `Description` text collate utf8_bin NOT NULL, `VoManagerMail` varchar(100) collate utf8_bin default NULL, `EnrollementURL` varchar(100) collate utf8_bin default NULL, `Discipline` varchar(100) collate utf8_bin NOT NULL, `Middleware` enum('ARC','gLite','UNICORE','GLOBUS','EMI') collate utf8_bin NOT NULL, `Insert_Time` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP, `AUP` text collate utf8_bin NOT NULL, PRIMARY KEY (`idVO`) ) ENGINE=InnoDB AUTO_INCREMENT=440 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Table structure for `certificate` -- ---------------------------- DROP TABLE IF EXISTS `certificate`; CREATE TABLE `certificate` ( `idCert` int(5) NOT NULL auto_increment, `userId` int(19) NOT NULL, `subject` varchar(100) collate utf8_bin NOT NULL, `issuer` varchar(100) collate utf8_bin NOT NULL, `expirationDate` date NOT NULL, `CAonline` enum('true','false') collate utf8_bin NOT NULL, `primaryCert` enum('true','false') collate utf8_bin NOT NULL, `usernameCert` varchar(100) collate utf8_bin NOT NULL, PRIMARY KEY (`idCert`), KEY `userId` (`userId`), CONSTRAINT `userIdtoCert` FOREIGN KEY (`userId`) REFERENCES `userInfo` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=191 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Table structure for `userInfo` -- ---------------------------- DROP TABLE IF EXISTS `userInfo`; CREATE TABLE `userInfo` ( `userId` int(19) NOT NULL auto_increment, `firstName` varchar(75) collate utf8_bin NOT NULL, `lastName` varchar(75) collate utf8_bin NOT NULL, `institute` varchar(75) collate utf8_bin NOT NULL, `phone` varchar(75) collate utf8_bin default NULL, `mail` varchar(75) collate utf8_bin NOT NULL, `idp` int(5) NOT NULL, `username` varchar(75) collate utf8_bin NOT NULL, `registrationComplete` enum('true','false') collate utf8_bin NOT NULL, PRIMARY KEY (`userId`), KEY `idp_idx` (`idp`), CONSTRAINT `idp` FOREIGN KEY (`idp`) REFERENCES `IDP` (`idIDP`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=219 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Table structure for `userToVO` -- ---------------------------- DROP TABLE IF EXISTS `userToVO`; CREATE TABLE `userToVO` ( `userId` int(19) NOT NULL, `idVO` int(5) NOT NULL, `isDefault` enum('true','false') collate utf8_bin default NULL, `FQANs` text collate utf8_bin, `idCert` int(5) NOT NULL, PRIMARY KEY (`userId`,`idVO`), KEY `userId_idx` (`userId`), KEY `idVO_idx` (`idVO`), KEY `idRole_idx` (`isDefault`), KEY `idCert` (`idCert`), CONSTRAINT `idCertUserToVo` FOREIGN KEY (`idCert`) REFERENCES `certificate` (`idCert`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `idVO` FOREIGN KEY (`idVO`) REFERENCES `VO` (`idVO`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `userId` FOREIGN KEY (`userId`) REFERENCES `userInfo` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; SET FOREIGN_KEY_CHECKS = 1;