/*
 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;