SQL-Code für die Datenbank
Supporter:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!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 utf8mb4 */;
CREATE DATABASE IF NOT EXISTS `contrack` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `contrack`;
CREATE TABLE `log` (
`id` int(11) NOT NULL,
`time` int(11) NOT NULL,
`logData` varchar(255) NOT NULL,
`level` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `settings` (
`loginTime` int(11) NOT NULL,
`loginTrys` int(11) NOT NULL,
`ip` varchar(255) NOT NULL,
`maxClientsWait` int(11) NOT NULL,
`wsport` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `settings` (`loginTime`, `loginTrys`, `ip`, `maxClientsWait`, `wsport`) VALUES
(180, 3, '0', 30, 5000);
CREATE TABLE `trackerData` (
`id` int(11) NOT NULL,
`trackerDeviceId` int(11) NOT NULL,
`recivedTime` int(11) NOT NULL,
`imei` varchar(255) NOT NULL,
`time` int(11) NOT NULL,
`status` varchar(255) NOT NULL,
`latitude` varchar(255) NOT NULL,
`longitude` varchar(255) NOT NULL,
`speed` varchar(255) NOT NULL,
`direction` varchar(255) NOT NULL,
`event` varchar(255) NOT NULL,
`voltage` varchar(255) NOT NULL,
`voltagePercent` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `trackerDevice` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`imei` varchar(255) NOT NULL,
`phoneNumber` varchar(255) NOT NULL,
`trackerModelId` int(11) NOT NULL,
`function` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL,
`userId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `trackerModel` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`port` int(11) NOT NULL,
`protocol` varchar(255) NOT NULL,
`pattern` varchar(255) NOT NULL,
`imeiPosition` int(11) NOT NULL,
`timeHourPosition` int(11) NOT NULL,
`timeMinutePosition` int(11) NOT NULL,
`timeSecondPosition` int(11) NOT NULL,
`timeDayPosition` int(11) NOT NULL,
`timeMonthPosition` int(11) NOT NULL,
`timeYearPosition` int(11) NOT NULL,
`timeFunction` varchar(255) NOT NULL,
`statusPosition` int(11) NOT NULL,
`statusFunction` varchar(255) NOT NULL,
`coordinatesBPosition` int(11) NOT NULL,
`coordinatesNSPosition` int(11) NOT NULL,
`coordinatesBFunction` varchar(255) NOT NULL,
`coordinatesLPosition` int(11) NOT NULL,
`coordinatesEWPosition` int(11) NOT NULL,
`coordinatesLFunction` varchar(255) NOT NULL,
`speedPosition` int(11) NOT NULL,
`speedFunction` varchar(255) NOT NULL,
`directionPosition` int(11) NOT NULL,
`directionFunction` varchar(255) NOT NULL,
`eventPosition` int(11) NOT NULL,
`eventFunction` varchar(255) NOT NULL,
`voltagePosition` int(11) NOT NULL,
`voltagePercentPosition` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `trackerModel` (`id`, `name`, `port`, `protocol`, `pattern`, `imeiPosition`, `timeHourPosition`, `timeMinutePosition`, `timeSecondPosition`, `timeDayPosition`, `timeMonthPosition`, `timeYearPosition`, `timeFunction`, `statusPosition`, `statusFunction`, `coordinatesBPosition`, `coordinatesNSPosition`, `coordinatesBFunction`, `coordinatesLPosition`, `coordinatesEWPosition`, `coordinatesLFunction`, `speedPosition`, `speedFunction`, `directionPosition`, `directionFunction`, `eventPosition`, `eventFunction`, `voltagePosition`, `voltagePercentPosition`) VALUES
(1, 'Secdet', 5051, 'TCP', 'imei=(\d+)[^,]+,([\d]{2})([\d]{2})([\d]{2})([\d\.]+),([AV]),([\d\.]+),([NS]),([\d\.]+),([EW]),([\d\.]+),([\d\.]*),([\d]{2})([\d]{2})([\d]{2}),([^,]*),([^,]*),([^,]*),([^,]*),V([^,]*);([^,]*)%,([^,]*),([^,]*),([^,\n]*)', 1, 2, 3, 4, 13, 14, 15, 'timeToUnixTime', 6, ' ', 7, 8, 'degreeToDecimal', 9, 10, 'degreeToDecimal', 11, 'nauticalKnotsInKmh', 12, ' ', 19, ' ', 20, 21);
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`authorization` varchar(255) NOT NULL,
`lastlogin` int(11) NOT NULL,
`trylogin` int(11) NOT NULL,
`lastactivity` int(11) NOT NULL,
`loginbefore` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `users` (`id`, `username`, `password`, `email`, `authorization`, `lastlogin`, `trylogin`, `lastactivity`, `loginbefore`) VALUES
(1, 'user', 'y$YiSs/riIzq2xLCk8rqiOY.wvoJpuIpVSBOs0ynGftqa5f6FucL8ma', 'mail@adress.de', 'admin', 1517077574, 0, 1517077792, 1517073719);
ALTER TABLE `log`
ADD PRIMARY KEY (`id`);
ALTER TABLE `trackerData`
ADD PRIMARY KEY (`id`);
ALTER TABLE `trackerDevice`
ADD PRIMARY KEY (`id`);
ALTER TABLE `trackerModel`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `port` (`port`);
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
ALTER TABLE `log`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `trackerData`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `trackerDevice`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `trackerModel`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
/*!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 */;