summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/install.sql65
-rw-r--r--db/update.sql27
2 files changed, 54 insertions, 38 deletions
diff --git a/db/install.sql b/db/install.sql
index 2ce71d9e..3b45fa27 100644
--- a/db/install.sql
+++ b/db/install.sql
@@ -3,7 +3,7 @@
-- http://www.phpmyadmin.net
--
-- Host: localhost
--- Erstellungszeit: 25. Dez 2014 um 22:28
+-- Erstellungszeit: 12. Jul 2015 um 14:45
-- Server Version: 5.6.12
-- PHP-Version: 5.5.3
@@ -25,7 +25,7 @@ CREATE TABLE IF NOT EXISTS `AngelTypes` (
`description` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Name` (`name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Daten für Tabelle `AngelTypes`
@@ -37,27 +37,6 @@ INSERT INTO `AngelTypes` (`id`, `name`, `restricted`, `description`) VALUES
-- --------------------------------------------------------
--
--- Tabellenstruktur für Tabelle `Counter`
---
-
-DROP TABLE IF EXISTS `Counter`;
-CREATE TABLE IF NOT EXISTS `Counter` (
- `URL` varchar(255) NOT NULL DEFAULT '',
- `Anz` bigint(20) NOT NULL DEFAULT '0',
- PRIMARY KEY (`URL`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Counter der Seiten';
-
---
--- Daten für Tabelle `Counter`
---
-
-INSERT INTO `Counter` (`URL`, `Anz`) VALUES
-('login', 2),
-('news', 1);
-
--- --------------------------------------------------------
-
---
-- Tabellenstruktur für Tabelle `GroupPrivileges`
--
@@ -155,7 +134,7 @@ DROP TABLE IF EXISTS `LogEntries`;
CREATE TABLE IF NOT EXISTS `LogEntries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
- `nick` varchar(23) NOT NULL,
+ `nick` text NOT NULL,
`message` text NOT NULL,
PRIMARY KEY (`id`),
KEY `timestamp` (`timestamp`)
@@ -198,7 +177,7 @@ CREATE TABLE IF NOT EXISTS `NeededAngelTypes` (
KEY `room_id` (`room_id`,`angel_type_id`),
KEY `shift_id` (`shift_id`),
KEY `angel_type_id` (`angel_type_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=107 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
@@ -216,7 +195,7 @@ CREATE TABLE IF NOT EXISTS `News` (
`Treffen` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `UID` (`UID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
@@ -310,7 +289,7 @@ CREATE TABLE IF NOT EXISTS `Questions` (
PRIMARY KEY (`QID`),
KEY `UID` (`UID`),
KEY `AID` (`AID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fragen und Antworten' AUTO_INCREMENT=2 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fragen und Antworten' AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
@@ -328,7 +307,7 @@ CREATE TABLE IF NOT EXISTS `Room` (
`Number` int(11) DEFAULT NULL,
PRIMARY KEY (`RID`),
UNIQUE KEY `Name` (`Name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
@@ -350,7 +329,7 @@ CREATE TABLE IF NOT EXISTS `ShiftEntry` (
KEY `UID` (`UID`),
KEY `SID` (`SID`,`TID`),
KEY `freeloaded` (`freeloaded`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
@@ -372,7 +351,7 @@ CREATE TABLE IF NOT EXISTS `Shifts` (
UNIQUE KEY `PSID` (`PSID`),
KEY `RID` (`RID`),
KEY `shifttype_id` (`shifttype_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=191 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
@@ -388,7 +367,14 @@ CREATE TABLE IF NOT EXISTS `ShiftTypes` (
`description` text NOT NULL,
PRIMARY KEY (`id`),
KEY `angeltype_id` (`angeltype_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
+
+--
+-- Daten für Tabelle `ShiftTypes`
+--
+
+INSERT INTO `ShiftTypes` (`id`, `name`, `angeltype_id`, `description`) VALUES
+(4, 'asdf', NULL, '');
-- --------------------------------------------------------
@@ -418,7 +404,6 @@ CREATE TABLE IF NOT EXISTS `User` (
`Tshirt` tinyint(4) DEFAULT '0',
`color` tinyint(4) DEFAULT '10',
`Sprache` char(64) NOT NULL,
- `Avatar` int(11) DEFAULT '0',
`Menu` char(1) NOT NULL DEFAULT 'L',
`lastLogIn` int(11) NOT NULL,
`CreateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
@@ -426,19 +411,23 @@ CREATE TABLE IF NOT EXISTS `User` (
`kommentar` text,
`Hometown` varchar(255) NOT NULL DEFAULT '',
`api_key` varchar(32) NOT NULL,
+ `got_voucher` tinyint(1) NOT NULL,
+ `arrival_date` int(11) DEFAULT NULL,
+ `planned_arrival_date` int(11) NOT NULL,
PRIMARY KEY (`UID`),
UNIQUE KEY `Nick` (`Nick`),
KEY `api_key` (`api_key`),
KEY `password_recovery_token` (`password_recovery_token`),
- KEY `force_active` (`force_active`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
+ KEY `force_active` (`force_active`),
+ KEY `arrival_date` (`arrival_date`,`planned_arrival_date`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Daten für Tabelle `User`
--
-INSERT INTO `User` (`UID`, `Nick`, `Name`, `Vorname`, `Alter`, `Telefon`, `DECT`, `Handy`, `email`, `email_shiftinfo`, `jabber`, `Size`, `Passwort`, `password_recovery_token`, `Gekommen`, `Aktiv`, `force_active`, `Tshirt`, `color`, `Sprache`, `Avatar`, `Menu`, `lastLogIn`, `CreateDate`, `Art`, `kommentar`, `Hometown`, `api_key`) VALUES
-(1, 'admin', 'Gates', 'Bill', 42, '', '-', '', 'admin@example.com', 0, '', 'XL', '$6$rounds=5000$hjXbIhoRTH3vKiRa$Wl2P2iI5T9iRR.HHu/YFHswBW0WVn0yxCfCiX0Keco9OdIoDK6bIAADswP6KvMCJSwTGdV8PgA8g8Xfw5l8BD1', NULL, 1, 0, 1, 0, 2, 'de_DE.UTF-8', 115, 'L', 1419542882, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235');
+INSERT INTO `User` (`UID`, `Nick`, `Name`, `Vorname`, `Alter`, `Telefon`, `DECT`, `Handy`, `email`, `email_shiftinfo`, `jabber`, `Size`, `Passwort`, `password_recovery_token`, `Gekommen`, `Aktiv`, `force_active`, `Tshirt`, `color`, `Sprache`, `Menu`, `lastLogIn`, `CreateDate`, `Art`, `kommentar`, `Hometown`, `api_key`, `got_voucher`, `arrival_date`, `planned_arrival_date`) VALUES
+(1, 'admin', 'Gates', 'Bill', 42, '', '-', '', 'admin@example.com', 0, '', 'XL', '$6$rounds=5000$hjXbIhoRTH3vKiRa$Wl2P2iI5T9iRR.HHu/YFHswBW0WVn0yxCfCiX0Keco9OdIoDK6bIAADswP6KvMCJSwTGdV8PgA8g8Xfw5l8BD1', NULL, 1, 0, 0, 0, 2, 'de_DE.UTF-8', 'L', 1436705059, '0000-00-00 00:00:00', '', '', '', '038850abdd1feb264406be3ffa746235', 0, NULL, 0);
-- --------------------------------------------------------
@@ -458,7 +447,7 @@ CREATE TABLE IF NOT EXISTS `UserAngelTypes` (
KEY `angeltype_id` (`angeltype_id`),
KEY `confirm_user_id` (`confirm_user_id`),
KEY `coordinator` (`coordinator`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;
--
-- Daten für Tabelle `UserAngelTypes`
@@ -481,7 +470,7 @@ CREATE TABLE IF NOT EXISTS `UserGroups` (
PRIMARY KEY (`id`),
KEY `uid` (`uid`,`group_id`),
KEY `group_id` (`group_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
--
-- Daten für Tabelle `UserGroups`
diff --git a/db/update.sql b/db/update.sql
index 8364e2e5..3dbd613a 100644
--- a/db/update.sql
+++ b/db/update.sql
@@ -1,3 +1,30 @@
+/* more shift infos */
+ALTER TABLE `Shifts` ADD `created_by_user_id` INT NOT NULL ,
+ADD `created_at_timestamp` INT NOT NULL ,
+ADD `edited_by_user_id` INT NOT NULL ,
+ADD `edited_at_timestamp` INT NOT NULL;
+ALTER TABLE `Shifts` ADD INDEX ( `created_by_user_id` );
+ALTER TABLE `Shifts` ADD INDEX ( `edited_by_user_id` );
+ALTER TABLE `Shifts` CHANGE `created_by_user_id` `created_by_user_id` INT( 11 ) NULL ;
+ALTER TABLE `Shifts` CHANGE `edited_by_user_id` `edited_by_user_id` INT( 11 ) NULL ;
+update Shifts set created_by_user_id=null, edited_by_user_id=null;
+ALTER TABLE `Shifts` ADD FOREIGN KEY ( `created_by_user_id` ) REFERENCES `engelsystem`.`User` (`UID`) ON DELETE SET NULL ON UPDATE CASCADE ;
+ALTER TABLE `Shifts` ADD FOREIGN KEY ( `edited_by_user_id` ) REFERENCES `engelsystem`.`User` (`UID`) ON DELETE SET NULL ON UPDATE CASCADE ;
+
+/* Introduce planned departure date */
+ALTER TABLE `User` ADD `planned_departure_date` INT NULL, ADD INDEX ( `planned_departure_date` );
+
+/* Allow longer angeltype names */
+ALTER TABLE `AngelTypes` CHANGE `name` `name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';
+
+/* Enable multiple vouchers */
+ALTER TABLE `User` CHANGE `got_voucher` `got_voucher` INT NOT NULL;
+
+/* introduce user arrival date */
+ALTER TABLE `User` ADD `arrival_date` INT NULL ,
+ADD `planned_arrival_date` INT NOT NULL ,
+ADD INDEX ( `arrival_date` , `planned_arrival_date` ) ;
+
/* fix log */
ALTER TABLE `LogEntries` CHANGE `nick` `nick` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;