summaryrefslogtreecommitdiffstats
path: root/setup/poolctrl.sql
diff options
context:
space:
mode:
authorBjörn Geiger2011-08-19 00:45:34 +0200
committerBjörn Geiger2011-08-19 00:45:34 +0200
commit74d2e51da636f8ec981a0b004efb7e1f04b08e08 (patch)
treec62968896bfc4fb4cb6715b17894c58bdfba9418 /setup/poolctrl.sql
parentmore ps-logic (diff)
downloadpoolctrl-74d2e51da636f8ec981a0b004efb7e1f04b08e08.tar.gz
poolctrl-74d2e51da636f8ec981a0b004efb7e1f04b08e08.tar.xz
poolctrl-74d2e51da636f8ec981a0b004efb7e1f04b08e08.zip
verschiedene Datenbankänderungen
Diffstat (limited to 'setup/poolctrl.sql')
-rw-r--r--setup/poolctrl.sql23
1 files changed, 16 insertions, 7 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
index e1badbd..96db9d7 100644
--- a/setup/poolctrl.sql
+++ b/setup/poolctrl.sql
@@ -10,6 +10,7 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` (
`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,
@@ -18,7 +19,7 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` (
`pbs_filterID` 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,
+ `running` bool NOT NULL DEFAULT false,
PRIMARY KEY (`eventID`),
KEY `pbs_poolID` (`pbs_poolID`),
KEY `pbs_membershipID` (`pbs_membershipID`),
@@ -28,22 +29,29 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` (
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 `eventID` (`eventID`),
+ KEY `type` (`type`)
) 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,
+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 (`eventcategoryID`)
+ 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` (
@@ -177,7 +185,8 @@ ALTER TABLE `pbs_membership`
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_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,