· 6 years ago · May 04, 2019, 12:38 PM
1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2SET time_zone = "+00:00";
3--
4-- Compatible with newer MySQL versions. (After MySQL-5.5)
5-- This SQL uses utf8mb4 and has CURRENT_TIMESTAMP function.
6--
7
8
9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12/*!40101 SET NAMES utf8mb4 */;
13
14--
15-- Database: `altislife`
16-- Default Schema
17--
18CREATE DATABASE IF NOT EXISTS `altislife` DEFAULT CHARACTER SET utf8mb4;
19USE `altislife`;
20
21--
22-- Drop procedures to ensure no conflicts
23--
24DROP PROCEDURE IF EXISTS `resetLifeVehicles`;
25DROP PROCEDURE IF EXISTS `deleteDeadVehicles`;
26DROP PROCEDURE IF EXISTS `deleteOldHouses`;
27DROP PROCEDURE IF EXISTS `deleteOldGangs`;
28DROP PROCEDURE IF EXISTS `deleteOldContainers`;
29DROP PROCEDURE IF EXISTS `deleteOldWanted`;
30
31DELIMITER $$
32--
33-- Procedures
34-- Edit arma3 to match a user in MySQL
35-- For external databases: Edit localhost to match arma3server IP
36--
37
38CREATE DEFINER=`root`@`localhost` PROCEDURE `resetLifeVehicles`()
39BEGIN
40 UPDATE `vehicles` SET `active`= 0;
41END$$
42
43CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDeadVehicles`()
44BEGIN
45 DELETE FROM `vehicles` WHERE `alive` = 0;
46END$$
47
48CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteOldHouses`()
49BEGIN
50 DELETE FROM `houses` WHERE `owned` = 0;
51END$$
52
53CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteOldGangs`()
54BEGIN
55 DELETE FROM `gangs` WHERE `active` = 0;
56END$$
57
58CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteOldContainers`()
59BEGIN
60 DELETE FROM `containers` WHERE `owned` = 0;
61END$$
62
63CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteOldWanted`()
64BEGIN
65 DELETE FROM `wanted` WHERE `active` = 0;
66END$$
67
68DELIMITER ;
69
70-- --------------------------------------------------------
71
72--
73-- Table structure for table `players`
74--
75
76CREATE TABLE IF NOT EXISTS `players` (
77 `uid` int(6) NOT NULL AUTO_INCREMENT,
78 `name` varchar(32) NOT NULL,
79 `aliases` text NOT NULL,
80 `pid` varchar(17) NOT NULL,
81 `cash` int(100) NOT NULL DEFAULT '0',
82 `bankacc` int(100) NOT NULL DEFAULT '0',
83 `coplevel` enum('0','1','2','3','4','5','6','7') NOT NULL DEFAULT '0',
84 `mediclevel` enum('0','1','2','3','4','5') NOT NULL DEFAULT '0',
85 `civ_licenses` text NOT NULL,
86 `cop_licenses` text NOT NULL,
87 `med_licenses` text NOT NULL,
88 `civ_gear` text NOT NULL,
89 `cop_gear` text NOT NULL,
90 `med_gear` text NOT NULL,
91 `civ_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
92 `cop_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
93 `med_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
94 `arrested` tinyint(1) NOT NULL DEFAULT '0',
95 `adminlevel` enum('0','1','2','3','4','5') NOT NULL DEFAULT '0',
96 `donorlevel` enum('0','1','2','3','4','5') NOT NULL DEFAULT '0',
97 `blacklist` tinyint(1) NOT NULL DEFAULT '0',
98 `civ_alive` tinyint(1) NOT NULL DEFAULT '0',
99 `civ_position` varchar(64) NOT NULL DEFAULT '"[]"',
100 `playtime` varchar(32) NOT NULL DEFAULT '"[0,0,0]"',
101 `insert_time` timestamp DEFAULT CURRENT_TIMESTAMP,
102 `last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
103 PRIMARY KEY (`uid`),
104 UNIQUE KEY `pid` (`pid`),
105 KEY `name` (`name`),
106 KEY `blacklist` (`blacklist`)
107) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=12 ;
108
109-- --------------------------------------------------------
110
111--
112-- Table structure for table `vehicles`
113--
114
115CREATE TABLE IF NOT EXISTS `vehicles` (
116 `id` int(6) NOT NULL AUTO_INCREMENT,
117 `side` varchar(16) NOT NULL,
118 `classname` varchar(64) NOT NULL,
119 `type` varchar(16) NOT NULL,
120 `pid` varchar(17) NOT NULL,
121 `alive` tinyint(1) NOT NULL DEFAULT '1',
122 `blacklist` tinyint(1) NOT NULL DEFAULT '0',
123 `active` tinyint(1) NOT NULL DEFAULT '0',
124 `plate` int(20) NOT NULL,
125 `color` int(20) NOT NULL,
126 `inventory` text NOT NULL,
127 `gear` text NOT NULL,
128 `fuel` double NOT NULL DEFAULT '1',
129 `damage` varchar(256) NOT NULL,
130 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
131 PRIMARY KEY (`id`),
132 KEY `side` (`side`),
133 KEY `pid` (`pid`),
134 KEY `type` (`type`)
135) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=2 ;
136
137-- --------------------------------------------------------
138
139--
140-- Table structure for table `houses`
141-- Needed for extDB latest update on git
142--
143
144CREATE TABLE IF NOT EXISTS `houses` (
145 `id` int(6) NOT NULL AUTO_INCREMENT,
146 `pid` varchar(17) NOT NULL,
147 `pos` varchar(64) DEFAULT NULL,
148 `owned` tinyint(1) DEFAULT '0',
149 `garage` tinyint(1) NOT NULL DEFAULT '0',
150 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
151 PRIMARY KEY (`id`,`pid`)
152) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=4 ;
153
154-- --------------------------------------------------------
155
156--
157-- Table structure for table `gangs`
158-- Needed for extDB latest update on git
159--
160
161CREATE TABLE IF NOT EXISTS `gangs` (
162 `id` int(6) NOT NULL AUTO_INCREMENT,
163 `owner` varchar(32) DEFAULT NULL,
164 `name` varchar(32) DEFAULT NULL,
165 `members` text,
166 `maxmembers` int(3) DEFAULT '8',
167 `bank` int(100) DEFAULT '0',
168 `active` tinyint(1) DEFAULT '1',
169 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
170 PRIMARY KEY (`id`),
171 UNIQUE KEY `name_UNIQUE` (`name`)
172) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
173
174-- --------------------------------------------------------
175
176--
177-- Table structure for table `containers`
178-- Needed for extDB latest update on git
179--
180
181CREATE TABLE IF NOT EXISTS `containers` (
182 `id` int(6) NOT NULL AUTO_INCREMENT,
183 `pid` varchar(17) NOT NULL,
184 `classname` varchar(32) NOT NULL,
185 `pos` varchar(64) DEFAULT NULL,
186 `inventory` text NOT NULL,
187 `gear` text NOT NULL,
188 `dir` varchar(128) DEFAULT NULL,
189 `active` tinyint(1) NOT NULL DEFAULT '0',
190 `owned` tinyint(1) DEFAULT '0',
191 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
192 PRIMARY KEY (`id`,`pid`)
193) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=4;
194
195-- --------------------------------------------------------
196
197--
198-- Table structure for table `businessapplications`
199-- Used to manage businesses
200--
201
202CREATE TABLE `businessapplications` (
203 `id` int(11) NOT NULL AUTO_INCREMENT,
204 `owner` varchar(255) NOT NULL,
205 `name` varchar(500) NOT NULL,
206 `application` varchar(2000) NOT NULL,
207 `size` int(2) NOT NULL DEFAULT '0',
208 `desiredPaycheck` int(10) NOT NULL DEFAULT '0',
209 `status` varchar(25) NOT NULL DEFAULT 'in Bearbeitung',
210 `answer` varchar(2000) NOT NULL DEFAULT '',
211 PRIMARY KEY (`id`)
212) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
213
214
215-- --------------------------------------------------------
216
217--
218-- Table structure for table `wanted`
219-- Needed for extDB latest update on git
220--
221
222CREATE TABLE IF NOT EXISTS `wanted` (
223 `wantedID` varchar(64) NOT NULL,
224 `wantedName` varchar(32) NOT NULL,
225 `wantedCrimes` text NOT NULL,
226 `wantedBounty` int(100) NOT NULL,
227 `active` tinyint(1) NOT NULL DEFAULT '0',
228 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
229 PRIMARY KEY (`wantedID`)
230) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
231
232-- --------------------------------------------------------
233
234/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
235/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
236/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;