summaryrefslogblamecommitdiffstats
path: root/setup/poolctrl.sql
blob: 85466c732358d88789471522e1fa9f281c6680d6 (plain) (tree)
1
2
3
4
5
6
7
8
9

                                                                                  





                                             
                                                       
                                                                                 
                                               
                                     
                                                           
                              
                                
                                      
                                      
                                      

                                        
                                      
                                       



                                                      
                                          
                                        
                                     
                                                     



                                              
                                      

                                          
                              

                                    
                               

                                                                              
                                                   
                                                  
                                                              












                                                                               

                                                                               


                                                              
                                                              


                                                                               

                                                   
                                                                     
                                        
                          
                             
                                                         
                           

                            

                                                                               
              
                                  
                                                                                                                              
                                                                                                                                      
 
                            
                                                                                                                                               


                                                                                                                                                  

                                                                             
CREATE DATABASE `##poolctrl##` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE ##poolctrl##;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- PoolCtrl Tabellen

CREATE TABLE IF NOT EXISTS `poolctrl_event` (
  `eventID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `start` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `end` timestamp COLLATE utf8_unicode_ci NULL,
  `participants` int(4) DEFAULT NULL,
  `note` varchar(140) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category` int(11) NOT NULL,
  `pbs_poolID` int(11) NOT NULL,
  `pbs_membershipID` int(11) NOT NULL,
  `pbs_bootosID` int(11) DEFAULT NULL,
  `pbs_configID` int(11) DEFAULT NULL,
  `pbs_bootmenuID` int(11) DEFAULT NULL,
  `pbs_filterID` int(11) DEFAULT NULL,
  `force` bool NOT NULL DEFAULT false,
  `repeat` bool NOT NULL DEFAULT false,
  `repeattype` int(11) NULL,
  `repeatend` int(11) NULL,
  `repeatdate` timestamp COLLATE utf8_unicode_ci NULL,
  `repeatings` int(4) NULL,
  `immediate` bool NOT NULL DEFAULT false,
  `running` bool NOT NULL DEFAULT false,
  `runningtype` int(11) DEFAULT NULL,
  `created` varchar(14) COLLATE utf8_unicode_ci NULL,
  PRIMARY KEY (`eventID`),
  KEY `pbs_poolID` (`pbs_poolID`),
  KEY `pbs_membershipID` (`pbs_membershipID`),
  KEY `pbs_bootosID` (`pbs_bootosID`),
  KEY `pbs_configID` (`pbs_configID`),
  KEY `pbs_bootmenuID` (`pbs_bootmenuID`),
  KEY `pbs_filterID` (`pbs_filterID`),
  KEY `category` (`category`),
  KEY `runningtype` (`runningtype`),
  KEY `repeattype` (`repeattype`),
  KEY `repeatend` (`repeatend`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `poolctrl_runningtype` (
  `runningtypeID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  PRIMARY KEY (`runningtypeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `poolctrl_repeattype` (
  `repeattypeID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  PRIMARY KEY (`repeattypeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `poolctrl_repeatend` (
  `repeatendID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  PRIMARY KEY (`repeatendID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `poolctrl_eventcategory` (
  `eventcategoryID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `color` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  PRIMARY KEY (`eventcategoryID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `poolctrl_eventreport` (
  `reportID` int(11) NOT NULL AUTO_INCREMENT,
  `result` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'successful',
  `errors` text COLLATE utf8_unicode_ci,
  `type` int(11) NOT NULL,
  `eventID` int(11) NOT NULL,
  `created` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`reportID`),
  KEY `eventID` (`eventID`),
  KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

-- Constraints
ALTER TABLE `poolctrl_eventreport`
  ADD CONSTRAINT `pbs_eventreport_eventidC` FOREIGN KEY (`eventID`) REFERENCES `poolctrl_event` (`eventID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_eventreport_typeidC` FOREIGN KEY (`type`) REFERENCES `poolctrl_runningtype` (`runningtypeID`) ON DELETE CASCADE;

ALTER TABLE `poolctrl_event`
  ADD CONSTRAINT `poolctrl_event_categoryC` FOREIGN KEY (`category`) REFERENCES `poolctrl_eventcategory` (`eventcategoryID`) ON DELETE CASCADE,
  ADD CONSTRAINT `poolctrl_event_repeattypeC` FOREIGN KEY (`repeattype`) REFERENCES `poolctrl_repeattype` (`repeattypeID`) ON DELETE SET NULL,
  ADD CONSTRAINT `poolctrl_event_runningtypeC` FOREIGN KEY (`runningtype`) REFERENCES `poolctrl_runningtype` (`runningtypeID`) ON DELETE SET NULL,
  ADD CONSTRAINT `poolctrl_event_repeatendC` FOREIGN KEY (`repeatend`) REFERENCES `poolctrl_repeatend` (`repeatendID`) ON DELETE SET NULL,
  ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()),
  ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);