summaryrefslogtreecommitdiffstats
path: root/setup/poolctrl.sql
diff options
context:
space:
mode:
authorBjörn Geiger2011-06-27 12:14:17 +0200
committerBjörn Geiger2011-06-27 12:14:17 +0200
commit2befc9baa7ce01c49fb09302d120d738bce822f9 (patch)
treed34c3099eba71480a4043a19ba3cf2ed796cec21 /setup/poolctrl.sql
parentkleine layout änderung (diff)
downloadpoolctrl-2befc9baa7ce01c49fb09302d120d738bce822f9.tar.gz
poolctrl-2befc9baa7ce01c49fb09302d120d738bce822f9.tar.xz
poolctrl-2befc9baa7ce01c49fb09302d120d738bce822f9.zip
Datenbank angepasst
Diffstat (limited to 'setup/poolctrl.sql')
-rw-r--r--setup/poolctrl.sql116
1 files changed, 98 insertions, 18 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
index f565baf..7581830 100644
--- a/setup/poolctrl.sql
+++ b/setup/poolctrl.sql
@@ -12,8 +12,8 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` (
`participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0),
`category` varchar(30) COLLATE utf8_unicode_ci,
`pbs_poolID` int(11) NOT NULL,
- `pbs_personID` int(11) NOT NULL,
- `pbs_bootisoID` int(11) NOT NULL,
+ `pbs_membershipID` int(11) NOT NULL,
+ `pbs_bootosID` int(11) NOT NULL,
`repeat` bool NOT NULL DEFAULT false,
`immediate` bool NOT NULL DEFAULT false,
`note` varchar(140) COLLATE utf8_unicode_ci DEFAULT NULL,
@@ -52,21 +52,67 @@ CREATE TABLE IF NOT EXISTS `pbs_person` (
PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
-CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
- `bootisoID` int(11) NOT NULL AUTO_INCREMENT,
- `title` varchar(30) NOT NULL,
- `description` varchar(140),
- `membershipID` int(11),
+CREATE TABLE IF NOT EXISTS `pbs_bootos` (
+ `bootosID` int(11) NOT NULL AUTO_INCREMENT,
`groupID` int(11) NOT NULL,
- `prebootID` int(11) NOT NULL,
- `serialnumber` varchar(30) NOT NULL UNIQUE,
+ `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,
- PRIMARY KEY (`bootisoID`),
+ `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 `groupID` (`groupID`),
KEY `membershipID` (`membershipID`),
+ KEY `bootosID` (`bootosID`)
+) 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_groupgroups` (
+ `parentID` int(11) NOT NULL,
+ `groupID` int(11) NOT NULL,
+ PRIMARY KEY (`parentID`,`groupID`),
+ KEY `parentID` (`parentID`),
KEY `groupID` (`groupID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
+) 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,
+ `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 ;
CREATE TABLE IF NOT EXISTS `pbs_pool` (
`poolID` int(11) NOT NULL AUTO_INCREMENT,
@@ -76,6 +122,16 @@ CREATE TABLE IF NOT EXISTS `pbs_pool` (
PRIMARY KEY (`poolID`)
) 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,
+ `macadress` varchar(17) NOT NULL,
+ `hardwarehash` varchar(32),
+ `created` int(15),
+ PRIMARY KEY (`clientID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
+ALTER TABLE `pbs_client` ADD `groupID` INT NOT NULL AFTER `clientID` ;
+ALTER TABLE `pbs_client` ADD UNIQUE (`groupID` ,`macadress`);
+
CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
`poolentriesID` INT NOT NULL AUTO_INCREMENT,
`poolID` int(11) NOT NULL,
@@ -84,11 +140,35 @@ CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
-- Constraints
+ALTER TABLE `pbs_config`
+ ADD CONSTRAINT `pbs_config_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE,
+ 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_groupgroups`
+ ADD CONSTRAINT `pbs_groupgroups_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `pbs_groupgroups_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) 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 `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_client`
+ ADD CONSTRAINT `pbs_client_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;
-ALTER TABLE `pbs_poolentries` ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE;
+ALTER TABLE `pbs_eventreport`
+ ADD CONSTRAINT `pbs_eventreport_eventidC` FOREIGN KEY (`evenID`) REFERENCES `pbs_event` (`eventID`) 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_personidC` FOREIGN KEY (`pbs_personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE,
- ADD CONSTRAINT `poolctrl_event_boolisoidC` FOREIGN KEY (`pbs_bootisoID`) REFERENCES `pbs_bootiso` (`bootisoID`) ON DELETE CASCADE,
- ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()),
- ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);
+ALTER TABLE `poolctrl_event`
+ ADD CONSTRAINT `poolctrl_event_poolidC` FOREIGN KEY (`pbs_poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `poolctrl_event_membershipC` FOREIGN KEY (`pbs_membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `poolctrl_event_boolisoidC` FOREIGN KEY (`pbs_bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()),
+ ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);