From 8a17d8093a8f8e5d3b86f0f7d4f3aadceb14d088 Mon Sep 17 00:00:00 2001 From: Sebastian Wagner Date: Fri, 24 Jun 2011 13:36:42 +0200 Subject: poolctrl datenbank erstellt --- setup/poolctrl.sql | 91 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 91 insertions(+) create mode 100644 setup/poolctrl.sql (limited to 'setup') diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql new file mode 100644 index 0000000..cb7f768 --- /dev/null +++ b/setup/poolctrl.sql @@ -0,0 +1,91 @@ +CREATE DATABASE `poolctrl_local` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +USE poolctrl_local; +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` varchar(10) COLLATE utf8_unicode_ci NOT NULL, + `end` varchar(10) COLLATE utf8_unicode_ci NOT NULL, + `participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0), + `category` varchar(30) COLLATE utf8_unicode_ci NOT NULL, + `pbs_poolID` int(11) NOT NULL, + `pbs_personID` int(11) NOT NULL, + `pbs_bootisoID` int(11) NOT NULL, + `repeat` bool NOT NULL DEFAULT false, + `note` varchar(140) COLLATE utf8_unicode_ci DEFAULT NULL, + PRIMARY KEY (`eventID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; + +CREATE TABLE IF NOT EXISTS `poolctrl_statusbericht` ( + `berichtID` int(11) NOT NULL AUTO_INCREMENT, + `bericht` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'success', + `eventID` int(11) NOT NULL, + PRIMARY KEY (`berichtID`), + FOREIGN KEY (`eventID`) references poolctrl_event(eventID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5; + + +-- 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_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_bootiso` ( + `bootisoID` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(30) NOT NULL, + `description` varchar(140), + `membershipID` int(11), + `groupID` int(11) NOT NULL, + `prebootID` int(11) NOT NULL, + `serialnumber` varchar(30) NOT NULL UNIQUE, + `created` VARCHAR(14) NOT NULL, + `expires` VARCHAR(14), + `public` int(11) NOT NULL, + PRIMARY KEY (`bootisoID`), + KEY `membershipID` (`membershipID`), + KEY `groupID` (`groupID`) +) 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, + `title` varchar(30) NOT NULL, + `description` varchar(140), + `location` varchar(30), + PRIMARY KEY (`poolID`) +) 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; + +-- Constraints + +ALTER TABLE `pbs_poolentries` ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE; + +ALTER TABLE `poolctrl_event` ADD CONSTRAINT `poolctrl_event_c1` FOREIGN KEY (`pbs_poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE, + ADD CONSTRAINT `poolctrl_event_c2` FOREIGN KEY (`pbs_personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE, + ADD CONSTRAINT `poolctrl_event_c3` FOREIGN KEY (`pbs_bootisoID`) REFERENCES `pbs_bootiso` (`bootisoID`) ON DELETE CASCADE; -- cgit v1.2.3-55-g7522