ConTrack Open Source

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 */;
Durch Nutzung dieser Website stimmen Sie der Verwendung von Cookies für Analysen, personalisierte Inhalte und Werbung zu.