Skip to content
Permalink
master
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

ApartVW/dump_with_procedures.sql

Go to file
 
 
Cannot retrieve contributors at this time
1773 lines (1581 sloc) 72.7 KB
Raw Blame
Open in GitHub Desktop
-- MySQL dump 10.13 Distrib 5.7.13, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.13-0ubuntu0.16.04.2
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `apartVwNeu`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `apartVwNeu` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `apartVwNeu`;
--
-- Table structure for table `Mitarbeiter`
--
DROP TABLE IF EXISTS `Mitarbeiter`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Mitarbeiter` (
`ma_ID` int(11) NOT NULL,
`userid` varchar(20) DEFAULT '',
`nachname` varchar(50) NOT NULL,
`vorname` varchar(50) NOT NULL,
`abt_name` char(10) DEFAULT NULL,
`anwesend` bit(1) DEFAULT b'0',
`statusa` varchar(50) DEFAULT NULL,
`message` varchar(50) DEFAULT NULL,
`aktiv` tinyint(4) DEFAULT NULL COMMENT 'inaktiver MA=0; aktiver MA=1; Objekt=2',
PRIMARY KEY (`ma_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `apartment`
--
DROP TABLE IF EXISTS `apartment`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apartment` (
`apt_ID` smallint(6) NOT NULL,
`apt_bez` varchar(50) NOT NULL,
`apt_kz` char(1) DEFAULT NULL COMMENT 'Kennzeichen Apartment/Gästewohnung',
PRIMARY KEY (`apt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `apt_preis`
--
DROP TABLE IF EXISTS `apt_preis`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apt_preis` (
`p_ID` smallint(6) NOT NULL AUTO_INCREMENT,
`apt_ID` smallint(6) NOT NULL,
`p_anzp` tinyint(4) NOT NULL COMMENT 'ab Anzahl Personen',
`p_tag` double NOT NULL,
`p_mon` double NOT NULL,
`ab_dat` date NOT NULL COMMENT 'gültig ab',
PRIMARY KEY (`p_ID`),
KEY `fk_apt_preis_apartment_idx` (`apt_ID`),
CONSTRAINT `fk_apt_preis_apartment` FOREIGN KEY (`apt_ID`) REFERENCES `apartment` (`apt_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `gast`
--
DROP TABLE IF EXISTS `gast`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gast` (
`g_ID` smallint(6) NOT NULL AUTO_INCREMENT,
`nname` varchar(50) NOT NULL,
`vname` varchar(50) NOT NULL,
`titel` varchar(30) DEFAULT NULL,
`strasse` varchar(50) DEFAULT NULL,
`plz` varchar(20) DEFAULT NULL,
`ort` varchar(50) DEFAULT NULL,
`land` varchar(50) DEFAULT NULL,
`tel` varchar(30) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`institut` varchar(100) NOT NULL,
`i_strasse` varchar(100) NOT NULL,
`i_plz` varchar(20) NOT NULL,
`i_ort` varchar(50) NOT NULL,
`i_land` varchar(50) NOT NULL,
`i_tel` varchar(30) DEFAULT NULL,
`i_email` varchar(100) DEFAULT NULL,
`mwst` double NOT NULL DEFAULT '0' COMMENT 'MPG: =0, sonst: >0',
PRIMARY KEY (`g_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `kst_abt`
--
DROP TABLE IF EXISTS `kst_abt`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `kst_abt` (
`kst` varchar(10) NOT NULL,
`abt_ID` varchar(3) NOT NULL,
`abt_name` varchar(10) DEFAULT NULL,
`abt_name_long` varchar(100) DEFAULT NULL,
PRIMARY KEY (`kst`,`abt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Kostenstelle je Abteilung';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `reserv`
--
DROP TABLE IF EXISTS `reserv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `reserv` (
`r_ID` smallint(6) NOT NULL AUTO_INCREMENT,
`g_ID` smallint(6) NOT NULL,
`p_ID` smallint(6) NOT NULL,
`von_dat` datetime NOT NULL,
`bis_dat` datetime NOT NULL,
`r_anzp` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'max(p_anzp) <= r_anzp',
`resv_status` enum('vorreserviert','bestätigt','storniert') DEFAULT NULL,
`rechn_status` enum('erstellt','bezahlt') DEFAULT NULL,
`rechn_dat` datetime DEFAULT NULL,
`anf_persknr` int(11) DEFAULT NULL COMMENT 'Anforderer',
`anf_name` varchar(100) DEFAULT NULL,
`betr_persknr` int(11) DEFAULT NULL COMMENT 'Betreuer',
`aufschlag` double DEFAULT '0',
`telefon_kost` double DEFAULT '0',
`kst` varchar(10) DEFAULT NULL COMMENT 'Kostenübernahme auf KST',
`r_bem` varchar(100) DEFAULT NULL,
PRIMARY KEY (`r_ID`),
KEY `fk_reserv_gast_idx` (`g_ID`),
KEY `fk_reserv_apt_preis_idx` (`p_ID`),
KEY `fk_reserv_kst_abt_idx` (`kst`),
CONSTRAINT `fk_reserv_apt_preis` FOREIGN KEY (`p_ID`) REFERENCES `apt_preis` (`p_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_reserv_gast` FOREIGN KEY (`g_ID`) REFERENCES `gast` (`g_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_recht`
--
DROP TABLE IF EXISTS `user_recht`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_recht` (
`persknr` int(11) NOT NULL,
`userid` varchar(10) NOT NULL,
`recht` varchar(2) NOT NULL COMMENT 'Recht Apartmentverwaltung',
`rechtwv` varchar(2) DEFAULT NULL COMMENT 'Recht Wohnungsverwaltung',
PRIMARY KEY (`persknr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping routines for database 'apartVwNeu'
--
/*!50003 DROP PROCEDURE IF EXISTS `aptpreise_flist` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `aptpreise_flist`(
IN sort INT,
IN dir VARCHAR(4),
IN ffnr INT,
IN filter VARCHAR(50),
IN stt INT,
IN zeil INT,
OUT anz INT
)
BEGIN
-- Author: Bettina Schwarzer, FHI
-- Create Date: 19.08.2015
-- Liste Apartments/Wohnungen und Preise
-- Sort nach vorgegebenen Feld in SELECT-Liste
-- Filter nach Apartment/Gaestewohnung, ab-Datum
DECLARE strsql VARCHAR(500);
DECLARE wher VARCHAR(100);
SET @strsql = 'SELECT a.apt_ID,p_ID,apt_bez,apt_kz,p_anzp,p_tag,p_mon,ab_dat
FROM apartVwNeu.apartment a LEFT OUTER JOIN apartVwNeu.apt_preis p ON a.apt_ID=p.apt_ID';
IF (sort<3 OR sort>8) THEN SET sort = 3; END IF;
IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
IF (ffnr<3 OR ffnr>8 OR ISNULL(filter) OR filter<='!') THEN
SET @strsql = CONCAT(@strsql, ' WHERE 1=1 ');
ELSE
BEGIN
IF ((ffnr>1 AND ffnr<9) AND filter>'!') THEN
CASE ffnr
WHEN 3 THEN SET wher = CONCAT(' apt_bez LIKE \'%',filter,'%\'');
WHEN 4 THEN SET wher = CONCAT(' apt_kz = \'',filter,'\'');
WHEN 5 THEN SET wher = CONCAT(' p_anzp = \'',filter,'\'');
WHEN 8 THEN SET wher = CONCAT(' ab_dat LIKE \'%',filter,'%\'');
END CASE;
END IF;
END;
SET @strsql = CONCAT(@strsql, ' WHERE ' ,wher);
END IF;
SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sort AS CHAR), ' ',dir);
SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
-- SELECT @strsql;
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
IF NOT ISNULL(wher) THEN
BEGIN
SET @strsql = CONCAT('SELECT COUNT(*) FROM apartVwNeu.apartment a LEFT OUTER JOIN apartVwNeu.apt_preis p ON a.apt_ID=p.apt_ID WHERE ', wher, ' INTO @cnt');
PREPARE strsql FROM @strsql;
EXECUTE strsql;
SET anz = @cnt;
DEALLOCATE PREPARE strsql;
END;
ELSE
SELECT COUNT(*) FROM apartVwNeu.apartment a LEFT OUTER JOIN apartVwNeu.apt_preis p ON a.apt_ID=p.apt_ID INTO anz;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `reserv_flist1` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `reserv_flist1`(
IN sort INT,
IN dir VARCHAR(4),
IN ffnr INT,
IN filter VARCHAR(50),
IN stt INT,
IN zeil INT,
IN akz VARCHAR(1), -- Kz Apartment=a / Gästewohnung=w
OUT anz INT
)
BEGIN
-- Author: Bettina Schwarzer, Carla Tschentscher FHI
-- Create Date: 28.10.2013, 17.10.2014
-- Liste der Reservierungen mit Status Rechnung
-- Sort nach vorgegebenen Feld in SELECT-Liste
-- Filter nach Name, Apartment, von, bis, Reservierungs-, Rechnungs-Status
-- Kz Apartment=a / Gästewohnung=w
DECLARE strsql VARCHAR(500);
DECLARE wher VARCHAR(100);
DECLARE vid,rid SMALLINT;
DECLARE abts,abtn VARCHAR(20);
DECLARE done INT DEFAULT 0;
DROP TABLE IF EXISTS __rv;
CREATE TEMPORARY TABLE __rv
( r_ID SMALLINT,
apt_ID SMALLINT,
gast VARCHAR(100),
nname VARCHAR(50),
von DATETIME,
bis DATETIME,
tage SMALLINT,
r_anzp SMALLINT,
resv_status VARCHAR(15),
rechn_status VARCHAR(10),
rechn_dat DATETIME,
rechn_nr VARCHAR(10),
apt_kz VARCHAR(1),
abt VARCHAR(20),
PRIMARY KEY (r_ID)) ENGINE=MEMORY;
INSERT INTO __rv (r_ID,apt_ID,gast,nname,von,bis,tage,r_anzp,resv_status,rechn_status,rechn_dat,rechn_nr,apt_kz,abt)
SELECT r_ID,CASE WHEN (a.apt_kz='a') THEN p.apt_ID ELSE SUBSTRING(apt_bez,4) END AS apt_ID,
CONCAT (IFNULL(CONCAT(titel,' '),''), IFNULL(CONCAT(vname,' '),''),nname) AS gast,nname,
von_dat,bis_dat,DATEDIFF(bis_dat,von_dat)+1 AS tage,r_anzp,resv_status,rechn_status,rechn_dat,
CONCAT(r_ID,'/',YEAR(von_dat)), apt_kz, NULL
FROM reserv r
LEFT OUTER JOIN gast g ON r.g_ID = g.g_ID
LEFT OUTER JOIN apt_preis p ON r.p_ID = p.p_ID
LEFT OUTER JOIN apartment a ON p.apt_ID = a.apt_ID;
-- SELECT * FROM __rv;
SET @strsql = 'SELECT r_ID,apt_ID,nname,von,bis,tage,r_anzp,resv_status,rechn_status,rechn_dat,rechn_nr,abt,gast FROM __rv';
IF (sort<2 OR sort>12) THEN SET sort = 2; END IF;
IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
IF (ffnr<2 OR ffnr>12 OR ISNULL(filter) OR filter<='!') THEN
SET @strsql = CONCAT(@strsql, ' WHERE 1=1 ');
ELSE
BEGIN
IF ((ffnr>1 AND ffnr<12) AND filter>'!') THEN
CASE ffnr
WHEN 2 THEN SET wher = CONCAT(' apt_ID LIKE \'',filter,'%\'');
WHEN 3 THEN SET wher = CONCAT(' gast LIKE \'%',filter,'%\'');
WHEN 4 THEN SET wher = CONCAT(' von LIKE \'%',filter,'%\'');
WHEN 5 THEN SET wher = CONCAT(' bis LIKE \'%',filter,'%\'');
WHEN 6 THEN SET wher = CONCAT(' tage = ',filter);
WHEN 7 THEN SET wher = CONCAT(' r_anzp = ',filter);
WHEN 8 THEN SET wher = CONCAT(' resv_status LIKE \'%',filter,'%\'');
WHEN 9 THEN SET wher = CONCAT(' rechn_status LIKE \'%',filter,'%\'');
WHEN 10 THEN SET wher = CONCAT(' rechn_dat LIKE \'%',filter,'%\'');
WHEN 11 THEN SET wher = CONCAT(' rechn_nr LIKE \'%',filter,'%\'');
WHEN 12 THEN SET wher = CONCAT(' abt LIKE \'%',filter,'%\'');
END CASE;
IF (ffnr = 9 AND filter = 'null') THEN SET wher = ' rechn_status IS NULL'; END IF;
END IF;
SET @strsql = CONCAT(@strsql, ' WHERE ' ,wher);
END;
END IF;
IF (akz>'!') THEN SET @strsql = CONCAT(@strsql, ' AND apt_kz = \'',akz,'\''); END IF;
SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sort AS CHAR), ' ',dir);
SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
IF NOT ISNULL(wher) THEN
BEGIN
SET @strsql = CONCAT('SELECT COUNT(*) FROM __rv WHERE ', wher, ' INTO @cnt');
PREPARE strsql FROM @strsql;
EXECUTE strsql;
SET anz = @cnt;
DEALLOCATE PREPARE strsql;
END;
ELSE
SELECT COUNT(*) FROM __rv INTO anz;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `userrecht_flist` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `userrecht_flist`(
IN sort INT,
IN dir VARCHAR(4),
IN ffnr INT,
IN filter VARCHAR(50),
IN stt INT,
IN zeil INT,
OUT anz INT
)
BEGIN
-- Author: Bettina Schwarzer, FHI
-- Create Date: 15.04.2015, 14.12.2015
-- Liste der User und Rechte
-- Sort nach vorgegebenen Feld in SELECT-Liste
-- Filter nach Name, Recht Apartment, Recht Gaestewohnung
DECLARE strsql VARCHAR(500);
DECLARE wher VARCHAR(100);
-- SET @strsql = 'SELECT r.persknr, CONCAT(nachname, IFNULL(CONCAT('', '',vorname),'''')) AS nvname, lower(substring_index(email,''@'',1)) as username, recht, rechtwv
-- FROM apartVwNeu.Mitarbeiter m JOIN apartVwNeu.user_recht r ON m.ma_ID=r.persknr';
SET @strsql = 'SELECT r.persknr, CONCAT(nachname, IFNULL(CONCAT('', '',vorname),'''')) AS nvname, r.userid as username, recht, rechtwv
FROM apartVwNeu.Mitarbeiter m JOIN apartVwNeu.user_recht r ON m.ma_ID=r.persknr';
IF (sort<2 OR sort>5) THEN SET sort = 2; END IF;
IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
IF (ffnr<2 OR ffnr>5 OR ISNULL(filter) OR filter<='!') THEN
SET @strsql = CONCAT(@strsql, ' WHERE 1=1 ');
ELSE
BEGIN
IF ((ffnr>1 AND ffnr<6) AND filter>'!') THEN
CASE ffnr
WHEN 2 THEN SET wher = CONCAT(' CONCAT(nachname, IFNULL(CONCAT('', '',vorname),'''')) LIKE \'',filter,'%\'');
WHEN 3 THEN SET wher = CONCAT(' lower(substring_index(email,''@'',1)) LIKE \'',filter,'%\'');
WHEN 4 THEN SET wher = CONCAT(' recht = \'',filter,'\'');
WHEN 5 THEN SET wher = CONCAT(' rechtwv = \'',filter,'\'');
END CASE;
IF (ffnr = 5 AND filter = 'null') THEN SET wher = ' rechtwv IS NULL'; END IF;
END IF;
END;
SET @strsql = CONCAT(@strsql, ' WHERE ' ,wher);
END IF;
SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sort AS CHAR), ' ',dir);
SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
-- SELECT @strsql;
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
IF NOT ISNULL(wher) THEN
BEGIN
SET @strsql = CONCAT('SELECT COUNT(*) FROM apartVwNeu.Mitarbeiter m JOIN apartVwNeu.user_recht r ON m.ma_ID=r.persknr WHERE ', wher, ' INTO @cnt');
PREPARE strsql FROM @strsql;
EXECUTE strsql;
SET anz = @cnt;
DEALLOCATE PREPARE strsql;
END;
ELSE
SELECT COUNT(*) FROM apartVwNeu.Mitarbeiter m JOIN apartVwNeu.user_recht r ON m.ma_ID=r.persknr INTO anz;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Current Database: `apartvw`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `apartvw` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `apartvw`;
--
-- Table structure for table `Mitarbeiter`
--
DROP TABLE IF EXISTS `Mitarbeiter`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Mitarbeiter` (
`ma_ID` int(11) NOT NULL,
`userid` varchar(20) CHARACTER SET latin1 DEFAULT '',
`nachname` varchar(50) CHARACTER SET latin1 NOT NULL,
`vorname` varchar(50) CHARACTER SET latin1 NOT NULL,
`abt_name` char(10) CHARACTER SET latin1 DEFAULT NULL,
`anwesend` bit(1) DEFAULT b'0',
`statusa` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`message` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`aktiv` tinyint(4) DEFAULT NULL COMMENT 'inaktiver MA=0; aktiver MA=1; Objekt=2',
PRIMARY KEY (`ma_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `apartment`
--
DROP TABLE IF EXISTS `apartment`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apartment` (
`apt_ID` smallint(6) NOT NULL,
`apt_bez` varchar(50) CHARACTER SET latin1 NOT NULL,
`apt_kz` char(1) CHARACTER SET latin1 DEFAULT NULL COMMENT 'Kennzeichen Apartment/Gästewohnung',
PRIMARY KEY (`apt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `apt_preis`
--
DROP TABLE IF EXISTS `apt_preis`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apt_preis` (
`p_ID` smallint(6) NOT NULL AUTO_INCREMENT,
`apt_ID` smallint(6) NOT NULL,
`p_anzp` tinyint(4) NOT NULL COMMENT 'ab Anzahl Personen',
`p_tag` double NOT NULL,
`p_mon` double NOT NULL,
`ab_dat` date NOT NULL COMMENT 'gültig ab',
PRIMARY KEY (`p_ID`),
KEY `fk_apt_preis_apartment_idx` (`apt_ID`),
CONSTRAINT `fk_apt_preis_apartment` FOREIGN KEY (`apt_ID`) REFERENCES `apartment` (`apt_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `gast`
--
DROP TABLE IF EXISTS `gast`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gast` (
`g_ID` smallint(6) NOT NULL AUTO_INCREMENT,
`nname` varchar(50) CHARACTER SET latin1 NOT NULL,
`vname` varchar(50) CHARACTER SET latin1 NOT NULL,
`titel` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
`strasse` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`plz` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`ort` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`land` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`tel` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
`email` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`institut` varchar(100) CHARACTER SET latin1 NOT NULL,
`i_strasse` varchar(100) CHARACTER SET latin1 NOT NULL,
`i_plz` varchar(20) CHARACTER SET latin1 NOT NULL,
`i_ort` varchar(50) CHARACTER SET latin1 NOT NULL,
`i_land` varchar(50) CHARACTER SET latin1 NOT NULL,
`i_tel` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
`i_email` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`mwst` double NOT NULL DEFAULT '0' COMMENT 'MPG: =0, sonst: >0',
PRIMARY KEY (`g_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=855 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `kst_abt`
--
DROP TABLE IF EXISTS `kst_abt`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `kst_abt` (
`kst` varchar(10) CHARACTER SET latin1 NOT NULL,
`abt_ID` varchar(3) CHARACTER SET latin1 NOT NULL,
`abt_name` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`abt_name_long` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`kst`,`abt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Kostenstelle je Abteilung';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `reserv`
--
DROP TABLE IF EXISTS `reserv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `reserv` (
`r_ID` smallint(6) NOT NULL AUTO_INCREMENT,
`g_ID` smallint(6) NOT NULL,
`p_ID` smallint(6) NOT NULL,
`von_dat` datetime NOT NULL,
`bis_dat` datetime NOT NULL,
`r_anzp` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'max(p_anzp) <= r_anzp',
`resv_status` enum('vorreserviert','bestätigt','storniert') CHARACTER SET latin1 DEFAULT NULL,
`rechn_status` enum('erstellt','bezahlt') CHARACTER SET latin1 DEFAULT NULL,
`rechn_dat` datetime DEFAULT NULL,
`anf_persknr` int(11) DEFAULT NULL COMMENT 'Anforderer',
`anf_name` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`betr_persknr` int(11) DEFAULT NULL COMMENT 'Betreuer',
`aufschlag` double DEFAULT '0',
`telefon_kost` double DEFAULT '0',
`kst` varchar(10) CHARACTER SET latin1 DEFAULT NULL COMMENT 'Kostenuebernahme auf KST',
`r_bem` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`r_ID`),
KEY `fk_reserv_gast_idx` (`g_ID`),
KEY `fk_reserv_apt_preis_idx` (`p_ID`),
KEY `fk_reserv_kst_abt_idx` (`kst`),
CONSTRAINT `fk_reserv_apt_preis` FOREIGN KEY (`p_ID`) REFERENCES `apt_preis` (`p_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_reserv_gast` FOREIGN KEY (`g_ID`) REFERENCES `gast` (`g_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1781 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_recht`
--
DROP TABLE IF EXISTS `user_recht`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_recht` (
`persknr` int(11) NOT NULL,
`userid` varchar(10) CHARACTER SET latin1 NOT NULL,
`recht` varchar(2) CHARACTER SET latin1 NOT NULL COMMENT 'Recht Apartmentverwaltung',
`rechtwv` varchar(2) CHARACTER SET latin1 DEFAULT NULL COMMENT 'Recht Wohnungsverwaltung',
PRIMARY KEY (`persknr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `wover_import`
--
DROP TABLE IF EXISTS `wover_import`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wover_import` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`tel` varchar(255) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`apt` smallint(6) DEFAULT NULL,
`pers` tinyint(4) DEFAULT NULL,
`von` date DEFAULT NULL,
`bis` date DEFAULT NULL,
`tele` double DEFAULT '0',
`aufschlag` double DEFAULT '0',
`a1` varchar(255) DEFAULT NULL,
`a2` varchar(255) DEFAULT NULL,
`a3` varchar(255) DEFAULT NULL,
`f_bezahlt` tinyint(4) DEFAULT NULL,
`anf` varchar(255) DEFAULT NULL,
`betr` varchar(255) DEFAULT NULL,
`com` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping routines for database 'apartvw'
--
/*!50003 DROP PROCEDURE IF EXISTS `aptpreise_flist` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `aptpreise_flist`(
IN sort INT,
IN dir VARCHAR(4),
IN ffnr INT,
IN filter VARCHAR(50),
IN stt INT,
IN zeil INT,
OUT anz INT
)
BEGIN
-- Author: Bettina Schwarzer, FHI
-- Create Date: 19.08.2015
-- Liste Apartments/Wohnungen und Preise
-- Sort nach vorgegebenen Feld in SELECT-Liste
-- Filter nach Apartment/Gaestewohnung, ab-Datum
DECLARE strsql VARCHAR(500);
DECLARE wher VARCHAR(100);
SET @strsql = 'SELECT a.apt_ID,p_ID,apt_bez,apt_kz,p_anzp,p_tag,p_mon,ab_dat
FROM apartvw.apartment a LEFT OUTER JOIN apartvw.apt_preis p ON a.apt_ID=p.apt_ID';
IF (sort<3 OR sort>8) THEN SET sort = 3; END IF;
IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
IF (ffnr<3 OR ffnr>8 OR ISNULL(filter) OR filter<='!') THEN
SET @strsql = CONCAT(@strsql, ' WHERE 1=1 ');
ELSE
BEGIN
IF ((ffnr>1 AND ffnr<9) AND filter>'!') THEN
CASE ffnr
WHEN 3 THEN SET wher = CONCAT(' apt_bez LIKE \'%',filter,'%\'');
WHEN 4 THEN SET wher = CONCAT(' apt_kz = \'',filter,'\'');
WHEN 5 THEN SET wher = CONCAT(' p_anzp = \'',filter,'\'');
WHEN 8 THEN SET wher = CONCAT(' ab_dat LIKE \'%',filter,'%\'');
END CASE;
END IF;
END;
SET @strsql = CONCAT(@strsql, ' WHERE ' ,wher);
END IF;
SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sort AS CHAR), ' ',dir);
SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
-- SELECT @strsql;
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
IF NOT ISNULL(wher) THEN
BEGIN
SET @strsql = CONCAT('SELECT COUNT(*) FROM apartvw.apartment a LEFT OUTER JOIN apartvw.apt_preis p ON a.apt_ID=p.apt_ID WHERE ', wher, ' INTO @cnt');
PREPARE strsql FROM @strsql;
EXECUTE strsql;
SET anz = @cnt;
DEALLOCATE PREPARE strsql;
END;
ELSE
SELECT COUNT(*) FROM apartvw.apartment a LEFT OUTER JOIN apartvw.apt_preis p ON a.apt_ID=p.apt_ID INTO anz;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `reserv_flist` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`hyperion.rz-berlin.mpg.de` PROCEDURE `reserv_flist`(
IN sort INT,
IN dir VARCHAR(4),
IN ffnr INT,
IN filter VARCHAR(50),
IN stt INT,
IN zeil INT,
OUT anz INT
)
BEGIN
-- Author: Bettina Schwarzer, FHI
-- Create Date: 28.10.2013
-- Liste der Reservierungen mit Status Rechnung
-- Sort nach vorgegebenen Feld in SELECT-Liste
-- Filter nach Name, Apartment, von, bis, Reservierungs-, Rechnungs-Status
DECLARE strsql VARCHAR(500);
DECLARE wher VARCHAR(100);
DECLARE vid,rid SMALLINT;
DECLARE abts,abtn VARCHAR(20);
DECLARE done INT DEFAULT 0;
DROP TABLE IF EXISTS __rv;
CREATE TEMPORARY TABLE __rv
( r_ID SMALLINT,
apt_ID SMALLINT,
gast VARCHAR(100),
nname VARCHAR(50),
von DATETIME,
bis DATETIME,
tage SMALLINT,
r_anzp SMALLINT,
resv_status VARCHAR(15),
rechn_status VARCHAR(10),
rechn_dat DATETIME,
rechn_nr VARCHAR(10),
abt VARCHAR(20),
PRIMARY KEY (r_ID)) ENGINE=MEMORY;
INSERT INTO __rv (r_ID,apt_ID,gast,nname,von,bis,tage,r_anzp,resv_status,rechn_status,rechn_dat,rechn_nr,abt)
SELECT r_ID,CASE WHEN (a.apt_kz='a') THEN p.apt_ID ELSE SUBSTRING(apt_bez,4) END AS apt_ID,
CONCAT (IFNULL(CONCAT(titel,' '),''), IFNULL(CONCAT(vname,' '),''),nname) AS gast,nname,
von_dat,bis_dat,DATEDIFF(bis_dat,von_dat)+1 AS tage,r_anzp,resv_status,rechn_status,rechn_dat,
CONCAT(r_ID,'/',YEAR(von_dat)), NULL
FROM reserv r
LEFT OUTER JOIN gast g ON r.g_ID = g.g_ID
LEFT OUTER JOIN apt_preis p ON r.p_ID = p.p_ID
LEFT OUTER JOIN apartment a ON p.apt_ID = a.apt_ID;
BEGIN
DECLARE acurs CURSOR FOR
SELECT DISTINCT r_ID,CASE WHEN abt_tel<> 7 THEN a.abt_name ELSE (CASE WHEN rag.abt_ID = 7 THEN rag.rag_bezk ELSE '' END) END AS abt_name
FROM apartvw.reserv r
LEFT OUTER JOIN fhiiqm.Mitarbeiter m ON r.anf_persknr=m.persknr
LEFT OUTER JOIN fhiiqm.Abteilung a ON m.abt_tel=a.abt_ID
LEFT OUTER JOIN fhiiqm.MA_RaumArbGruppe ma ON r.anf_persknr=ma.persknr
LEFT OUTER JOIN fhiiqm.RaumArb_Gruppe rag ON ma.rag_ID=rag.rag_ID
ORDER BY r_ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET done=0;
OPEN acurs;
FETCH acurs INTO rid, abtn;
IF done = 0 THEN
SET vid = rid;
SET abts = IFNULL(abtn,'');
WHILE done=0 DO
FETCH acurs INTO rid, abtn;
IF (rid=vid AND done=0) THEN
SET abts = CONCAT(IF(abts>'',CONCAT(abts,', '),''),IFNULL(abtn,''));
ELSE
BEGIN
UPDATE __rv SET abt = abts WHERE r_ID=vid;
SET abts = IFNULL(abtn,'');
SET vid = rid;
END;
END IF;
END WHILE;
END IF;
CLOSE acurs;
END;
-- SELECT * FROM __rv;
SET @strsql = 'SELECT r_ID,apt_ID,nname,von,bis,tage,r_anzp,resv_status,rechn_status,rechn_dat,rechn_nr,abt,gast FROM __rv';
IF (sort<2 OR sort>12) THEN SET sort = 2; END IF;
IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
IF (ffnr<2 OR ffnr>12 OR ISNULL(filter) OR filter<='!') THEN
SET @strsql = @strsql;
ELSE
BEGIN
IF ((ffnr>1 AND ffnr<12) AND filter>'!') THEN
CASE ffnr
WHEN 2 THEN SET wher = CONCAT(' apt_ID = ',filter);
WHEN 3 THEN SET wher = CONCAT(' gast LIKE \'%',filter,'%\'');
WHEN 4 THEN SET wher = CONCAT(' von LIKE \'%',filter,'%\'');
WHEN 5 THEN SET wher = CONCAT(' bis LIKE \'%',filter,'%\'');
WHEN 6 THEN SET wher = CONCAT(' tage = ',filter);
WHEN 7 THEN SET wher = CONCAT(' r_anzp = ',filter);
WHEN 8 THEN SET wher = CONCAT(' resv_status LIKE \'%',filter,'%\'');
WHEN 9 THEN SET wher = CONCAT(' rechn_status LIKE \'%',filter,'%\'');
WHEN 10 THEN SET wher = CONCAT(' rechn_dat LIKE \'%',filter,'%\'');
WHEN 11 THEN SET wher = CONCAT(' rechn_nr LIKE \'%',filter,'%\'');
WHEN 12 THEN SET wher = CONCAT(' abt LIKE \'%',filter,'%\'');
END CASE;
IF (ffnr = 9 AND filter = 'null') THEN SET wher = ' rechn_status IS NULL'; END IF;
END IF;
SET @strsql = CONCAT(@strsql, ' WHERE ' ,wher);
END;
END IF;
SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sort AS CHAR), ' ',dir);
SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
IF NOT ISNULL(wher) THEN
BEGIN
SET @strsql = CONCAT('SELECT COUNT(*) FROM __rv WHERE ', wher, ' INTO @cnt');
PREPARE strsql FROM @strsql;
EXECUTE strsql;
SET anz = @cnt;
DEALLOCATE PREPARE strsql;
END;
ELSE
SELECT COUNT(*) FROM __rv INTO anz;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `reserv_flist1` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `reserv_flist1`(
IN sort INT,
IN dir VARCHAR(4),
IN ffnr INT,
IN filter VARCHAR(50),
IN stt INT,
IN zeil INT,
IN akz VARCHAR(1), -- Kz Apartment=a / Gästewohnung=w
OUT anz INT
)
BEGIN
-- Author: Bettina Schwarzer, Carla Tschentscher FHI
-- Create Date: 28.10.2013, 17.10.2014
-- Liste der Reservierungen mit Status Rechnung
-- Sort nach vorgegebenen Feld in SELECT-Liste
-- Filter nach Name, Apartment, von, bis, Reservierungs-, Rechnungs-Status
-- Kz Apartment=a / Gästewohnung=w
DECLARE strsql VARCHAR(500);
DECLARE wher VARCHAR(100);
DECLARE vid,rid SMALLINT;
DECLARE abts,abtn VARCHAR(20);
DECLARE done INT DEFAULT 0;
DROP TABLE IF EXISTS __rv;
CREATE TEMPORARY TABLE __rv
( r_ID SMALLINT,
apt_ID SMALLINT,
gast VARCHAR(100),
nname VARCHAR(50),
von DATETIME,
bis DATETIME,
tage SMALLINT,
r_anzp SMALLINT,
resv_status VARCHAR(15),
rechn_status VARCHAR(10),
rechn_dat DATETIME,
rechn_nr VARCHAR(10),
apt_kz VARCHAR(1),
abt VARCHAR(20),
PRIMARY KEY (r_ID)) ENGINE=MEMORY;
INSERT INTO __rv (r_ID,apt_ID,gast,nname,von,bis,tage,r_anzp,resv_status,rechn_status,rechn_dat,rechn_nr,apt_kz,abt)
SELECT r_ID,CASE WHEN (a.apt_kz='a') THEN p.apt_ID ELSE SUBSTRING(apt_bez,4) END AS apt_ID,
CONCAT (IFNULL(CONCAT(titel,' '),''), IFNULL(CONCAT(vname,' '),''),nname) AS gast,nname,
von_dat,bis_dat,DATEDIFF(bis_dat,von_dat)+1 AS tage,r_anzp,resv_status,rechn_status,rechn_dat,
CONCAT(r_ID,'/',YEAR(von_dat)), apt_kz, NULL
FROM reserv r
LEFT OUTER JOIN gast g ON r.g_ID = g.g_ID
LEFT OUTER JOIN apt_preis p ON r.p_ID = p.p_ID
LEFT OUTER JOIN apartment a ON p.apt_ID = a.apt_ID;
-- SELECT * FROM __rv;
SET @strsql = 'SELECT r_ID,apt_ID,nname,von,bis,tage,r_anzp,resv_status,rechn_status,rechn_dat,rechn_nr,abt,gast FROM __rv';
IF (sort<2 OR sort>12) THEN SET sort = 2; END IF;
IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
IF (ffnr<2 OR ffnr>12 OR ISNULL(filter) OR filter<='!') THEN
SET @strsql = CONCAT(@strsql, ' WHERE 1=1 ');
ELSE
BEGIN
IF ((ffnr>1 AND ffnr<12) AND filter>'!') THEN
CASE ffnr
WHEN 2 THEN SET wher = CONCAT(' apt_ID LIKE \'',filter,'%\'');
WHEN 3 THEN SET wher = CONCAT(' gast LIKE \'%',filter,'%\'');
WHEN 4 THEN SET wher = CONCAT(' von LIKE \'%',filter,'%\'');
WHEN 5 THEN SET wher = CONCAT(' bis LIKE \'%',filter,'%\'');
WHEN 6 THEN SET wher = CONCAT(' tage = ',filter);
WHEN 7 THEN SET wher = CONCAT(' r_anzp = ',filter);
WHEN 8 THEN SET wher = CONCAT(' resv_status LIKE \'%',filter,'%\'');
WHEN 9 THEN SET wher = CONCAT(' rechn_status LIKE \'%',filter,'%\'');
WHEN 10 THEN SET wher = CONCAT(' rechn_dat LIKE \'%',filter,'%\'');
WHEN 11 THEN SET wher = CONCAT(' rechn_nr LIKE \'%',filter,'%\'');
WHEN 12 THEN SET wher = CONCAT(' abt LIKE \'%',filter,'%\'');
END CASE;
IF (ffnr = 9 AND filter = 'null') THEN SET wher = ' rechn_status IS NULL'; END IF;
END IF;
SET @strsql = CONCAT(@strsql, ' WHERE ' ,wher);
END;
END IF;
IF (akz>'!') THEN SET @strsql = CONCAT(@strsql, ' AND apt_kz = \'',akz,'\''); END IF;
SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sort AS CHAR), ' ',dir);
SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
IF NOT ISNULL(wher) THEN
BEGIN
SET @strsql = CONCAT('SELECT COUNT(*) FROM __rv WHERE ', wher, ' INTO @cnt');
PREPARE strsql FROM @strsql;
EXECUTE strsql;
SET anz = @cnt;
DEALLOCATE PREPARE strsql;
END;
ELSE
SELECT COUNT(*) FROM __rv INTO anz;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `userrecht_flist` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `userrecht_flist`(
IN sort INT,
IN dir VARCHAR(4),
IN ffnr INT,
IN filter VARCHAR(50),
IN stt INT,
IN zeil INT,
OUT anz INT
)
BEGIN
-- Author: Bettina Schwarzer, FHI
-- Create Date: 15.04.2015, 14.12.2015
-- Liste der User und Rechte
-- Sort nach vorgegebenen Feld in SELECT-Liste
-- Filter nach Name, Recht Apartment, Recht Gaestewohnung
DECLARE strsql VARCHAR(500);
DECLARE wher VARCHAR(100);
-- SET @strsql = 'SELECT r.persknr, CONCAT(nachname, IFNULL(CONCAT('', '',vorname),'''')) AS nvname, lower(substring_index(email,''@'',1)) as username, recht, rechtwv
-- FROM apartvw.Mitarbeiter m JOIN apartvw.user_recht r ON m.ma_ID=r.persknr';
SET @strsql = 'SELECT r.persknr, CONCAT(nachname, IFNULL(CONCAT('', '',vorname),'''')) AS nvname, r.userid as username, recht, rechtwv
FROM apartvw.Mitarbeiter m JOIN apartvw.user_recht r ON m.ma_ID=r.persknr';
IF (sort<2 OR sort>5) THEN SET sort = 2; END IF;
IF (dir > '!') THEN SET dir = dir; ELSE SET dir = ''; END IF;
IF (ffnr<2 OR ffnr>5 OR ISNULL(filter) OR filter<='!') THEN
SET @strsql = CONCAT(@strsql, ' WHERE 1=1 ');
ELSE
BEGIN
IF ((ffnr>1 AND ffnr<6) AND filter>'!') THEN
CASE ffnr
WHEN 2 THEN SET wher = CONCAT(' CONCAT(nachname, IFNULL(CONCAT('', '',vorname),'''')) LIKE \'',filter,'%\'');
WHEN 3 THEN SET wher = CONCAT(' lower(substring_index(email,''@'',1)) LIKE \'',filter,'%\'');
WHEN 4 THEN SET wher = CONCAT(' recht = \'',filter,'\'');
WHEN 5 THEN SET wher = CONCAT(' rechtwv = \'',filter,'\'');
END CASE;
IF (ffnr = 5 AND filter = 'null') THEN SET wher = ' rechtwv IS NULL'; END IF;
END IF;
END;
SET @strsql = CONCAT(@strsql, ' WHERE ' ,wher);
END IF;
SET @strsql = CONCAT(@strsql, ' ORDER BY ', CAST(sort AS CHAR), ' ',dir);
SET @strsql = CONCAT(@strsql, ' LIMIT ', stt, ', ', zeil);
-- SELECT @strsql;
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
IF NOT ISNULL(wher) THEN
BEGIN
SET @strsql = CONCAT('SELECT COUNT(*) FROM apartvw.Mitarbeiter m JOIN apartvw.user_recht r ON m.ma_ID=r.persknr WHERE ', wher, ' INTO @cnt');
PREPARE strsql FROM @strsql;
EXECUTE strsql;
SET anz = @cnt;
DEALLOCATE PREPARE strsql;
END;
ELSE
SELECT COUNT(*) FROM apartvw.Mitarbeiter m JOIN apartvw.user_recht r ON m.ma_ID=r.persknr INTO anz;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
--
-- Table structure for table `columns_priv`
--
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `db`
--
DROP TABLE IF EXISTS `db`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `engine_cost`
--
DROP TABLE IF EXISTS `engine_cost`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `engine_cost` (
`engine_name` varchar(64) NOT NULL,
`device_type` int(11) NOT NULL,
`cost_name` varchar(64) NOT NULL,
`cost_value` float DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`comment` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`cost_name`,`engine_name`,`device_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `event`
--
DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char(64) NOT NULL DEFAULT '',
`body` longblob NOT NULL,
`definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`execute_at` datetime DEFAULT NULL,
`interval_value` int(11) DEFAULT NULL,
`interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_executed` datetime DEFAULT NULL,
`starts` datetime DEFAULT NULL,
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`originator` int(10) unsigned NOT NULL,
`time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob,
PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `func`
--
DROP TABLE IF EXISTS `func`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `func` (
`name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`ret` tinyint(1) NOT NULL DEFAULT '0',
`dl` char(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`type` enum('function','aggregate') CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `gtid_executed`
--
DROP TABLE IF EXISTS `gtid_executed`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gtid_executed` (
`source_uuid` char(36) NOT NULL COMMENT 'uuid of the source where the transaction was originally executed.',
`interval_start` bigint(20) NOT NULL COMMENT 'First number of interval.',
`interval_end` bigint(20) NOT NULL COMMENT 'Last number of interval.',
PRIMARY KEY (`source_uuid`,`interval_start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `help_category`
--
DROP TABLE IF EXISTS `help_category`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_category` (
`help_category_id` smallint(5) unsigned NOT NULL,
`name` char(64) NOT NULL,
`parent_category_id` smallint(5) unsigned DEFAULT NULL,
`url` text NOT NULL,
PRIMARY KEY (`help_category_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help categories';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `help_keyword`
--
DROP TABLE IF EXISTS `help_keyword`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_keyword` (
`help_keyword_id` int(10) unsigned NOT NULL,
`name` char(64) NOT NULL,
PRIMARY KEY (`help_keyword_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help keywords';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `help_relation`
--
DROP TABLE IF EXISTS `help_relation`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_relation` (
`help_topic_id` int(10) unsigned NOT NULL,
`help_keyword_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`help_keyword_id`,`help_topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='keyword-topic relation';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `help_topic`
--
DROP TABLE IF EXISTS `help_topic`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_topic` (
`help_topic_id` int(10) unsigned NOT NULL,
`name` char(64) NOT NULL,
`help_category_id` smallint(5) unsigned NOT NULL,
`description` text NOT NULL,
`example` text NOT NULL,
`url` text NOT NULL,
PRIMARY KEY (`help_topic_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help topics';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `innodb_index_stats`
--
DROP TABLE IF EXISTS `innodb_index_stats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `innodb_table_stats`
--
DROP TABLE IF EXISTS `innodb_table_stats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `ndb_binlog_index`
--
DROP TABLE IF EXISTS `ndb_binlog_index`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ndb_binlog_index` (
`Position` bigint(20) unsigned NOT NULL,
`File` varchar(255) NOT NULL,
`epoch` bigint(20) unsigned NOT NULL,
`inserts` int(10) unsigned NOT NULL,
`updates` int(10) unsigned NOT NULL,
`deletes` int(10) unsigned NOT NULL,
`schemaops` int(10) unsigned NOT NULL,
`orig_server_id` int(10) unsigned NOT NULL,
`orig_epoch` bigint(20) unsigned NOT NULL,
`gci` int(10) unsigned NOT NULL,
`next_position` bigint(20) unsigned NOT NULL,
`next_file` varchar(255) NOT NULL,
PRIMARY KEY (`epoch`,`orig_server_id`,`orig_epoch`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `plugin`
--
DROP TABLE IF EXISTS `plugin`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `plugin` (
`name` varchar(64) NOT NULL DEFAULT '',
`dl` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='MySQL plugins';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `proc`
--
DROP TABLE IF EXISTS `proc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proc` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char(64) NOT NULL DEFAULT '',
`type` enum('FUNCTION','PROCEDURE') NOT NULL,
`specific_name` char(64) NOT NULL DEFAULT '',
`language` enum('SQL') NOT NULL DEFAULT 'SQL',
`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
`param_list` blob NOT NULL,
`returns` longblob NOT NULL,
`body` longblob NOT NULL,
`definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob,
PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `procs_priv`
--
DROP TABLE IF EXISTS `procs_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `procs_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,
`Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
`Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `proxies_priv`
--
DROP TABLE IF EXISTS `proxies_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proxies_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`With_grant` tinyint(1) NOT NULL DEFAULT '0',
`Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `server_cost`
--
DROP TABLE IF EXISTS `server_cost`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `server_cost` (
`cost_name` varchar(64) NOT NULL,
`cost_value` float DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`comment` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`cost_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `servers`
--
DROP TABLE IF EXISTS `servers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL DEFAULT '',
`Host` char(64) NOT NULL DEFAULT '',
`Db` char(64) NOT NULL DEFAULT '',
`Username` char(64) NOT NULL DEFAULT '',
`Password` char(64) NOT NULL DEFAULT '',
`Port` int(4) NOT NULL DEFAULT '0',
`Socket` char(64) NOT NULL DEFAULT '',
`Wrapper` char(64) NOT NULL DEFAULT '',
`Owner` char(64) NOT NULL DEFAULT '',
PRIMARY KEY (`Server_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='MySQL Foreign Servers table';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `slave_master_info`
--
DROP TABLE IF EXISTS `slave_master_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
`Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
`Tls_version` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Tls version',
PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `slave_relay_log_info`
--
DROP TABLE IF EXISTS `slave_relay_log_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `slave_relay_log_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
`Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
`Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
`Number_of_workers` int(10) unsigned NOT NULL,
`Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
`Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `slave_worker_info`
--
DROP TABLE IF EXISTS `slave_worker_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `slave_worker_info` (
`Id` int(10) unsigned NOT NULL,
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Relay_log_pos` bigint(20) unsigned NOT NULL,
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_seqno` int(10) unsigned NOT NULL,
`Checkpoint_group_size` int(10) unsigned NOT NULL,
`Checkpoint_group_bitmap` blob NOT NULL,
`Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
PRIMARY KEY (`Channel_name`,`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `tables_priv`
--
DROP TABLE IF EXISTS `tables_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `time_zone`
--
DROP TABLE IF EXISTS `time_zone`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone` (
`Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
PRIMARY KEY (`Time_zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zones';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `time_zone_leap_second`
--
DROP TABLE IF EXISTS `time_zone_leap_second`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_leap_second` (
`Transition_time` bigint(20) NOT NULL,
`Correction` int(11) NOT NULL,
PRIMARY KEY (`Transition_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Leap seconds information for time zones';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `time_zone_name`
--
DROP TABLE IF EXISTS `time_zone_name`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_name` (
`Name` char(64) NOT NULL,
`Time_zone_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zone names';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `time_zone_transition`
--
DROP TABLE IF EXISTS `time_zone_transition`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_transition` (
`Time_zone_id` int(10) unsigned NOT NULL,
`Transition_time` bigint(20) NOT NULL,
`Transition_type_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`Time_zone_id`,`Transition_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zone transitions';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `time_zone_transition_type`
--
DROP TABLE IF EXISTS `time_zone_transition_type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_transition_type` (
`Time_zone_id` int(10) unsigned NOT NULL,
`Transition_type_id` int(10) unsigned NOT NULL,
`Offset` int(11) NOT NULL DEFAULT '0',
`Is_DST` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Abbreviation` char(8) NOT NULL DEFAULT '',
PRIMARY KEY (`Time_zone_id`,`Transition_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zone transition types';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping routines for database 'mysql'
--
--
-- Table structure for table `general_log`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `general_log` (
`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `slow_log`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-09-06 11:33:58