summaryrefslogtreecommitdiffstats
path: root/setup
diff options
context:
space:
mode:
authorSebastian Wagner2011-06-24 13:36:42 +0200
committerSebastian Wagner2011-06-24 13:36:42 +0200
commit8a17d8093a8f8e5d3b86f0f7d4f3aadceb14d088 (patch)
treec7f085afb39a6d07e0d15bd4064f28d91e9dde70 /setup
parentBootOS API Fehler korrigiert (diff)
downloadpbs2-8a17d8093a8f8e5d3b86f0f7d4f3aadceb14d088.tar.gz
pbs2-8a17d8093a8f8e5d3b86f0f7d4f3aadceb14d088.tar.xz
pbs2-8a17d8093a8f8e5d3b86f0f7d4f3aadceb14d088.zip
poolctrl datenbank erstellt
Diffstat (limited to 'setup')
-rw-r--r--setup/poolctrl.sql91
1 files changed, 91 insertions, 0 deletions
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;