summaryrefslogtreecommitdiffstats
path: root/setup
diff options
context:
space:
mode:
authorBjörn Geiger2011-06-25 16:38:26 +0200
committerBjörn Geiger2011-06-25 16:38:26 +0200
commit6e7030589692d559b7fcb2139b2a6292277a64ea (patch)
tree412bc331facbf1f2ebb97ae0c887da8c1daa4456 /setup
parentverschobenes wieder zurückverschoben (diff)
downloadpoolctrl-6e7030589692d559b7fcb2139b2a6292277a64ea.tar.gz
poolctrl-6e7030589692d559b7fcb2139b2a6292277a64ea.tar.xz
poolctrl-6e7030589692d559b7fcb2139b2a6292277a64ea.zip
Datenbank hinzugefügt
Diffstat (limited to 'setup')
-rw-r--r--setup/poolctrl.sql94
-rw-r--r--setup/poolctrl_data.sql48
2 files changed, 142 insertions, 0 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
new file mode 100644
index 0000000..ba93efe
--- /dev/null
+++ b/setup/poolctrl.sql
@@ -0,0 +1,94 @@
+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 NOT NULL,
+ `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,
+ `repeat` bool NOT NULL DEFAULT false,
+ `immediate` 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_eventreport` (
+ `reportID` int(11) NOT NULL AUTO_INCREMENT,
+ `report` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'success',
+ `eventID` int(11) NOT NULL,
+ PRIMARY KEY (`reportID`),
+ 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_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);
diff --git a/setup/poolctrl_data.sql b/setup/poolctrl_data.sql
new file mode 100644
index 0000000..acef3a0
--- /dev/null
+++ b/setup/poolctrl_data.sql
@@ -0,0 +1,48 @@
+USE poolctrl;
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+-- Adding person test2
+INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`, `loginpassword`) VALUES
+(2, 'Herr', 'Test 2', 'Test 2', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test2', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.');
+
+-- Adding person test3
+INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`, `loginpassword`) VALUES
+(3, 'Herr', 'Test 3', 'Test 3', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test3', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.');
+
+-- Adding bootiso
+INSERT INTO `pbs_bootiso` (`bootisoID`, `title`, `membershipID`, `groupID`, `prebootID`, `serialnumber`, `created`, `expires`, `public`, `description`) VALUES
+(1, 'BootMedium 1', 1, 1, '1', '10-20-30-40-50', '123', '2012-03-09', 0, 'Description 1'),
+(2, 'BootMedium 2', 1, 1, '2', '11-20-30-40-50', '456', '2012-03-09', 0, 'Description 2'),
+(3, 'BootMedium 3', 1, 1, '3', '12-20-30-40-50', '789', '2012-03-09', 0, 'Description 3');
+
+-- Adding pools
+INSERT INTO `pbs_pool` (`poolID`, `title`, `description`, `location`) VALUES
+(1, 'Pool 1', 'Description 1', 'Keller'),
+(2, 'Pool 2', 'Description 2', 'EG'),
+(3, 'Pool 3', 'Description 3', '1 OG'),
+(4, 'Pool 4', 'Description 4', '2 OG');
+
+-- Adding client to pools (poolentries)
+INSERT INTO `pbs_poolentries` (`poolentriesID`, `poolID`, `clientID`) VALUES
+(1, 1, 1),
+(2, 1, 2),
+(3, 1, 3),
+(4, 1, 4),
+(5, 2, 5),
+(6, 2, 6),
+(7, 3, 7),
+(8, 3, 8),
+(9, 4, 9),
+(10, 4, 10);
+
+-- Adding events
+INSERT INTO `poolctrl_event` (`title`, `start`, `end`, `category`, `pbs_poolID`, `pbs_personID`, `pbs_bootisoID`, `note`) VALUES
+('Systeme I', '2011-06-24 12:10:00', '2011-06-24 14:00:00', 'Vorlesung', 1, 2, 1, 'Systeme I Vorlesung'),
+('Systeme II', '2011-06-24 14:10:00', '2011-06-24 16:00:00', 'Vorlesung', 1, 2, 1, 'Systeme II Vorlesung'),
+('Systeme III', '2011-06-24 16:10:00', '2011-06-24 18:00:00', 'Vorlesung', 1, 2, 1, 'Systeme III Vorlesung');
+
+-- Adding eventreport
+INSERT INTO `poolctrl_eventreport` (`report`, `eventID`) VALUES
+(DEFAULT, 1),
+(DEFAULT, 2),
+('wake-on-lan failed', 3);