summaryrefslogblamecommitdiffstats
path: root/setup/poolctrl.sql
blob: 8c11f06d7ed4dbb0cac4c0dc7e9284d63a36b132 (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 UNIQUE,
  `start` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `end` timestamp COLLATE utf8_unicode_ci NULL,
  `participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0),
  `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) NOT NULL,
  `pbs_bootmenuID` int(11) NOT NULL,
  `pbs_filterID` int(11) NOT NULL,
  `repeat` bool NOT NULL DEFAULT false,
  `immediate` bool NOT NULL DEFAULT false,
  `running` bool NOT NULL DEFAULT false,
  `runningType` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`eventID`),
  KEY `pbs_poolID` (`pbs_poolID`),
  KEY `pbs_membershipID` (`pbs_membershipID`),
  KEY `pbs_bootosID` (`pbs_bootosID`),
  KEY `pbs_bootmenuID` (`pbs_bootmenuID`),
  KEY `pbs_filterID` (`pbs_filterID`),
  KEY `category` (`category`)
) 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,
  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,
  `report` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'success',
  `type` int(11) NOT NULL,
  `eventID` int(11) NOT NULL,
  `timestamp` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  PRIMARY KEY (`reportID`),
  KEY `eventID` (`eventID`),
  KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

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

-- PBS2 Tabellen
-- TO DO: Tabellen an dieser Stelle nicht per Hand erzeugen sondern von der PBS2-Datenbank kopieren, sodass diese auf dem neusten Stand sind
CREATE TABLE IF NOT EXISTS `pbs_bootos` (
  `bootosID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) NOT NULL,
  `membershipID` int(11),
  `title` varchar(30) NOT NULL,
  `description` varchar(140),
  `distro` varchar(30),
  `distroversion` varchar(30),
  `source` varchar(140),
  `share` varchar(30),
  `shortname` varchar(30),
  `defaultkcl` varchar(255),
  `created` VARCHAR(14) NOT NULL,
  `expires` VARCHAR(14),
  `public` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`bootosID`),
  KEY `groupID` (`groupID`),
  KEY `membershipID` (`membershipID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `pbs_config` (
  `configID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` varchar(140),
  `groupID` int(11),
  `membershipID` int(11),
  `visible` tinyint(1) DEFAULT '0',
  `created` VARCHAR(14) NOT NULL,
  `bootosID` int(11) NOT NULL,
  PRIMARY KEY (`configID`),
  KEY `bootosID` (`bootosID`),
  KEY `groupID` (`groupID`),
  KEY `membershipID` (`membershipID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `pbs_pool` (
  `poolID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) NOT NULL,
  `title` varchar(30) NOT NULL,
  `description` varchar(140),
  `location` varchar(30),
  PRIMARY KEY (`poolID`),
  KEY `groupID` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `pbs_client` (
  `clientID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) NOT NULL,
  `macadress` varchar(17) NOT NULL,
  `hardwarehash` varchar(32),
  `ip` varchar(15) NOT NULL,
  `ip6` varchar(45),
  `created` int(15),
  PRIMARY KEY (`clientID`),
  KEY `groupID` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
  `poolentriesID` INT NOT NULL AUTO_INCREMENT,
  `poolID` int(11) NOT NULL,
  `clientID` int(11) NOT NULL,
  PRIMARY KEY (`poolentriesID`),
  KEY `poolID` (`poolID`),
  KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

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

CREATE TABLE IF NOT EXISTS `pbs_person` (
  `personID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci,
  `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `firstname` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `street` varchar(30) COLLATE utf8_unicode_ci,
  `housenumber` varchar(30) COLLATE utf8_unicode_ci,
  `city` varchar(30) COLLATE utf8_unicode_ci,
  `postalcode` varchar(30) COLLATE utf8_unicode_ci,
  `logindate` varchar(14) COLLATE utf8_unicode_ci,
  `registerdate` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `login` varchar(30) COLLATE utf8_unicode_ci,
  `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `password_salt` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `loginpassword` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
  `suspend` tinyint(1) NOT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `pbs_membership` (
  `membershipID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) NOT NULL,
  `roleID` int(11) NOT NULL,
  `personID` int(11) NOT NULL,
  `suspend` tinyint(1) NOT NULL,
  `apikey` varchar(30),
  PRIMARY KEY (`membershipID`),
  KEY `groupID` (`groupID`),
  KEY `roleID` (`roleID`),
  KEY `personID` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- Constraints
ALTER TABLE `pbs_bootos`
  ADD CONSTRAINT `pbs_bootos_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_bootos_ibfk_3` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL;

ALTER TABLE `pbs_config`
  ADD CONSTRAINT `pbs_config_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
  ADD CONSTRAINT `pbs_config_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_config_ibfk_3` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE; 

ALTER TABLE `pbs_client`
  ADD CONSTRAINT `pbs_client_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;

ALTER TABLE `pbs_pool`
  ADD CONSTRAINT `pbs_pool_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;

ALTER TABLE `pbs_poolentries`
  ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_poolentries_ibfk_2` FOREIGN KEY (`clientID`) REFERENCES `pbs_client` (`clientID`) ON DELETE CASCADE;

ALTER TABLE `pbs_membership`
  ADD CONSTRAINT `pbs_membership_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_membership_ibfk_2` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE;

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_reporttype` (`reporttypeID`) ON DELETE CASCADE;

ALTER TABLE `poolctrl_event`
  ADD CONSTRAINT `poolctrl_event_poolidC` FOREIGN KEY (`pbs_poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
  ADD CONSTRAINT `poolctrl_event_bootosidC` FOREIGN KEY (`pbs_bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE,
  ADD CONSTRAINT `poolctrl_event_membershipidC` FOREIGN KEY (`pbs_membershipID`) REFERENCES `pbs_membership` (`membershipID`)  ON DELETE CASCADE,
  ADD CONSTRAINT `poolctrl_event_categoryC` FOREIGN KEY (`category`) REFERENCES `poolctrl_eventcategory` (`eventcategoryID`) ON DELETE CASCADE,
  ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()),
  ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);