· 5 years ago · Mar 17, 2020, 05:22 PM
1-- --------------------------------------------------------
2-- Host: 127.0.0.1
3-- Server Version: 10.1.39-MariaDB - mariadb.org binary distribution
4-- Server Betriebssystem: Win64
5-- HeidiSQL Version: 10.3.0.5771
6-- --------------------------------------------------------
7
8/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9/*!40101 SET NAMES utf8 */;
10/*!50503 SET NAMES utf8mb4 */;
11/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
12/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
13
14
15-- Exportiere Datenbank Struktur für yourstate_life
16CREATE DATABASE IF NOT EXISTS `yourstate_life` /*!40100 DEFAULT CHARACTER SET latin1 */;
17USE `yourstate_life`;
18
19-- Exportiere Struktur von Prozedur yourstate_life.deleteDeadVehicles
20DELIMITER $$
21DROP PROCEDURE IF EXISTS `resetLifeServer` $$
22CREATE DEFINER=CURRENT_USER PROCEDURE `resetLifeServer`()
23BEGIN
24 DELETE FROM `vehicles` WHERE `alive` = 0 AND `update_time` < NOW() - INTERVAL 2 WEEK;
25 DELETE FROM `groups` WHERE `active` = 0;
26 DELETE FROM `houses` WHERE `owned` = 0;
27 DELETE FROM `containers` WHERE `owned` = 0;
28 DELETE FROM `wanted` WHERE `active` = 0;
29 UPDATE `vehicles` SET `active`= 0;
30END$$
31DELIMITER ;
32
33-- Exportiere Struktur von Tabelle yourstate_life.adminlogs
34DROP TABLE IF EXISTS `adminlogs`;
35CREATE TABLE `adminlogs` (
36 `id` int(11) NOT NULL AUTO_INCREMENT,
37 `name_admin` varchar(255) NOT NULL,
38 `uid_admin` varchar(17) NOT NULL,
39 `name_player` varchar(255) NOT NULL,
40 `uid_player` varchar(17) NOT NULL,
41 `log` text NOT NULL,
42 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
43 PRIMARY KEY (`id`)
44) ENGINE=InnoDB DEFAULT CHARSET=latin1;
45
46-- Exportiere Struktur von Tabelle yourstate_life.players
47DROP TABLE IF EXISTS `players`;
48CREATE TABLE `players` (
49 `id` int(6) NOT NULL AUTO_INCREMENT,
50 `pid` varchar(17) NOT NULL,
51 `guid` varchar(64) NOT NULL,
52 `last_name` varchar(64) NOT NULL,
53 `adminlevel` enum('0', '1', '2', '3', '4', '5') NOT NULL DEFAULT '0',
54 `donorlevel` enum('0', '1', '2', '3', '4', '5') NOT NULL DEFAULT '0',
55 `last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
56 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
57 PRIMARY KEY (`id`),
58 UNIQUE INDEX `pid_unique_index` (`pid`)
59) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
60
61-- Exportiere Struktur von Tabelle yourstate_life.profiles
62DROP TABLE IF EXISTS `profiles`;
63CREATE TABLE `profiles` (
64 `uid` int(11) NOT NULL AUTO_INCREMENT,
65 `pid` int(11) NOT NULL,
66 `side` varchar(32) NOT NULL DEFAULT 'civ',
67 `name` varchar(32) NOT NULL,
68 `cash` int(100) NOT NULL DEFAULT '0',
69 `bank` int(100) NOT NULL DEFAULT '0',
70 `level` int(11) NOT NULL DEFAULT '0',
71 `licenses` text NOT NULL,
72 `gear` text NOT NULL,
73 `stats` varchar(25) NOT NULL DEFAULT '[100, 100]',
74 `damage` text NOT NULL,
75 `skills` varchar(255) NOT NULL,
76 `pass` text NOT NULL,
77 `jail_time` int(11) NOT NULL DEFAULT '0',
78 `playtime` int(11) NOT NULL DEFAULT '0',
79 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
80 `last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
81 PRIMARY KEY (`uid`),
82 UNIQUE INDEX `pid_side_unique_index` (`side`, `pid`),
83 INDEX `pid_index` (`pid`),
84 CONSTRAINT `FK_profiles_players` FOREIGN KEY (`pid`) REFERENCES `players` (`id`) ON DELETE CASCADE
85) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
86
87-- Exportiere Struktur von Trigger yourstate_life.profile_duplicate_name_trigger_BI
88DELIMITER $$
89DROP TRIGGER IF EXISTS `profile_duplicate_name_trigger_BI` $$
90CREATE DEFINER=CURRENT_USER TRIGGER `profile_duplicate_name_trigger_BI`
91BEFORE INSERT ON `profiles` FOR EACH ROW
92BEGIN
93 IF ((SELECT COUNT(uid) FROM profiles WHERE name = NEW.name AND pid <> NEW.pid) > 0) THEN
94 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Name existiert bereits";
95 END IF;
96END$$
97DELIMITER ;
98
99-- Exportiere Struktur von Tabelle yourstate_life.houses
100DROP TABLE IF EXISTS `houses`;
101CREATE TABLE `houses` (
102 `id` int(11) NOT NULL AUTO_INCREMENT,
103 `profile_id` int(11) DEFAULT NULL,
104 `pos` varchar(32) NOT NULL,
105 `owned` tinyint(1) NOT NULL DEFAULT '0',
106 `garage` tinyint(1) NOT NULL DEFAULT '0',
107 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
108 PRIMARY KEY (`id`),
109 INDEX `profile_index` (`profile_id`),
110 CONSTRAINT `FK_houses_profiles` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`uid`) ON DELETE CASCADE
111) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
112
113-- Exportiere Struktur von Trigger yourstate_life.trigger_wanted_active
114DELIMITER $$
115DROP TRIGGER IF EXISTS `houses_delete_trigger_AD` $$
116CREATE DEFINER=CURRENT_USER TRIGGER `houses_delete_trigger_AD`
117AFTER DELETE ON `houses` FOR EACH ROW
118BEGIN
119 UPDATE groups SET house_id = NULL WHERE house_id = OLD.id;
120END$$
121DELIMITER ;
122
123-- Exportiere Struktur von Tabelle yourstate_life.containers
124DROP TABLE IF EXISTS `containers`;
125CREATE TABLE `containers` (
126 `id` int(11) NOT NULL AUTO_INCREMENT,
127 `house_id` int(11) NOT NULL,
128 `classname` varchar(32) NOT NULL,
129 `pos` varchar(32) NOT NULL,
130 `inventory` text NOT NULL,
131 `gear` text NOT NULL,
132 `dir` varchar(128) NOT NULL,
133 `owned` tinyint(1) NOT NULL DEFAULT '1',
134 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
135 PRIMARY KEY (`id`),
136 INDEX `house_index` (`house_id`),
137 CONSTRAINT `FK_containers_houses` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
138) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
139
140-- Exportiere Struktur von Tabelle yourstate_life.groups
141DROP TABLE IF EXISTS `groups`;
142CREATE TABLE `groups` (
143 `id` int(11) NOT NULL AUTO_INCREMENT,
144 `name` varchar(32) NOT NULL,
145 `tag` varchar(16) NOT NULL,
146 `maxmembers` int(11) DEFAULT '8',
147 `bank` int(11) DEFAULT '0',
148 `house_id` int(11) DEFAULT NULL,
149 `active` tinyint(1) NOT NULL DEFAULT '1',
150 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
151 PRIMARY KEY (`id`),
152 UNIQUE INDEX `name_unique_index` (`name`),
153 UNIQUE INDEX `tag_unique_index` (`tag`),
154 INDEX `house_index` (`house_id`),
155 INDEX `groups_idx_active_id` (`active`, `id`)
156) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
157
158-- Exportiere Struktur von Tabelle yourstate_life.groups_ranks
159DROP TABLE IF EXISTS `groups_ranks`;
160CREATE TABLE `groups_ranks` (
161 `id` int(11) NOT NULL AUTO_INCREMENT,
162 `group_id` int(11) NOT NULL,
163 `name` varchar(32) NOT NULL,
164 `rights` varchar(16) NOT NULL DEFAULT '[]',
165 `description` varchar(255) DEFAULT NULL,
166 PRIMARY KEY (`id`),
167 INDEX `group_index` (`group_id`),
168 CONSTRAINT `FK_groups_ranks_groups` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE
169) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
170
171-- Exportiere Struktur von Tabelle yourstate_life.groups_members
172DROP TABLE IF EXISTS `groups_members`;
173CREATE TABLE `groups_members` (
174 `id` int(11) NOT NULL AUTO_INCREMENT,
175 `group_id` int(11) NOT NULL,
176 `profile_id` int(11) NOT NULL,
177 `rank_id` int(11) DEFAULT NULL,
178 `owner` tinyint(1) DEFAULT '0',
179 PRIMARY KEY (`id`),
180 INDEX `group_index` (`group_id`),
181 INDEX `profile_index` (`profile_id`),
182 INDEX `rank_index` (`rank_id`),
183 CONSTRAINT `FK_groups_members_groups` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE,
184 CONSTRAINT `FK_groups_members_profiles` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`uid`) ON DELETE CASCADE,
185 CONSTRAINT `FK_groups_members_groups_ranks` FOREIGN KEY (`rank_id`) REFERENCES `groups_ranks` (`id`) ON DELETE SET NULL
186) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
187
188-- Exportiere Struktur von Trigger yourstate_life.groups_members_duplicate_owner_trigger_BI
189DELIMITER $$
190DROP TRIGGER IF EXISTS `groups_members_duplicate_owner_trigger_BI` $$
191CREATE DEFINER=CURRENT_USER TRIGGER `groups_members_duplicate_owner_trigger_BI`
192BEFORE INSERT ON `groups_members` FOR EACH ROW
193BEGIN
194 IF ((SELECT COUNT(id) FROM groups_members WHERE owner = '1' AND group_id = NEW.group_id) > 0) THEN
195 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Es gibt bereits einen Besitzer der Gang";
196 END IF;
197END$$
198DELIMITER ;
199
200-- Exportiere Struktur von Tabelle yourstate_life.logs
201DROP TABLE IF EXISTS `logs`;
202CREATE TABLE `logs` (
203 `id` int(11) NOT NULL AUTO_INCREMENT,
204 `profile_id` int(11) NOT NULL,
205 `type` varchar(255) DEFAULT NULL,
206 `message` varchar(255) DEFAULT NULL,
207 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
208 PRIMARY KEY (`id`),
209 INDEX `profile_index` (`profile_id`),
210 CONSTRAINT `FK_logs_profiles` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`uid`)
211) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
212
213-- Exportiere Struktur von Tabelle yourstate_life.vehicles
214DROP TABLE IF EXISTS `vehicles`;
215CREATE TABLE `vehicles` (
216 `id` int(11) NOT NULL AUTO_INCREMENT,
217 `profile_id` int(11) NOT NULL,
218 `classname` varchar(64) NOT NULL,
219 `type` varchar(16) NOT NULL,
220 `alive` tinyint(1) NOT NULL DEFAULT '1',
221 `active` tinyint(1) NOT NULL DEFAULT '1',
222 `plate` varchar(16) NOT NULL,
223 `color` int(11) NOT NULL,
224 `rvmat` int(11) DEFAULT NULL,
225 `inventory` text NOT NULL,
226 `gear` text NOT NULL,
227 `fuel` double NOT NULL DEFAULT '1',
228 `damage` text NOT NULL,
229 `insurance` tinyint(1) NOT NULL DEFAULT '0',
230 `impounded` tinyint(1) NOT NULL DEFAULT '0',
231 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
232 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
233 PRIMARY KEY (`id`),
234 INDEX `type_index` (`type`),
235 INDEX `profile_index` (`profile_id`),
236 CONSTRAINT `FK_vehicles_profiles` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`uid`) ON DELETE CASCADE
237) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
238
239-- Exportiere Struktur von Tabelle yourstate_life.wanted
240DROP TABLE IF EXISTS `wanted`;
241CREATE TABLE `wanted` (
242 `id` int(11) NOT NULL AUTO_INCREMENT,
243 `profile_id` int(11) NOT NULL,
244 `active` tinyint(1) NOT NULL DEFAULT '0',
245 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
246 PRIMARY KEY (`id`),
247 INDEX `profile_index` (`profile_id`),
248 CONSTRAINT `FK_wanted_profiles` FOREIGN KEY (`id`) REFERENCES `profiles` (`uid`) ON DELETE CASCADE
249) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
250
251-- Exportiere Struktur von Tabelle yourstate_life.crimes
252DROP TABLE IF EXISTS `crimes`;
253CREATE TABLE `crimes` (
254 `id` int(11) NOT NULL AUTO_INCREMENT,
255 `name` int(11) NOT NULL,
256 `bounty` int(128) NOT NULL DEFAULT '0',
257 PRIMARY KEY (`id`)
258) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
259
260-- Exportiere Struktur von Tabelle yourstate_life.wanted_crimes
261DROP TABLE IF EXISTS `wanted_crimes`;
262CREATE TABLE `wanted_crimes` (
263 `id` int(11) NOT NULL AUTO_INCREMENT,
264 `wanted_id` int(11) NOT NULL,
265 `crime_id` int(11) NOT NULL,
266 PRIMARY KEY (`id`),
267 INDEX `wanted_index` (`wanted_id`),
268 INDEX `crime_index` (`crime_id`),
269 CONSTRAINT `FK_wanted_crimes_wanted` FOREIGN KEY (`wanted_id`) REFERENCES `wanted` (`id`) ON DELETE CASCADE,
270 CONSTRAINT `FK_wanted_crimes_crime` FOREIGN KEY (`crime_id`) REFERENCES `crimes` (`id`) ON DELETE CASCADE
271) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
272
273-- Exportiere Struktur von Tabelle yourstate_life.params
274DROP TABLE IF EXISTS `params`;
275CREATE TABLE `params` (
276 `id` int(11) NOT NULL AUTO_INCREMENT,
277 `param_key` varchar(150) NOT NULL,
278 `param_value` text NOT NULL,
279 PRIMARY KEY (`id`),
280 UNIQUE INDEX `param_key_unique_index` (`param_key`)
281) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
282
283/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
284/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
285/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;