· 4 years ago · Mar 08, 2021, 12:30 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-- Creates database `altislife` unless it already exists and uses `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-- CURRENT_USER function returns the name of the current user in the SQL Server database.
35--
36
37CREATE DEFINER=CURRENT_USER PROCEDURE `resetLifeVehicles`()
38BEGIN
39 UPDATE `vehicles` SET `active`= 0;
40END$$
41
42CREATE DEFINER=CURRENT_USER PROCEDURE `deleteDeadVehicles`()
43BEGIN
44 DELETE FROM `vehicles` WHERE `alive` = 0;
45END$$
46
47CREATE DEFINER=CURRENT_USER PROCEDURE `deleteOldHouses`()
48BEGIN
49 DELETE FROM `houses` WHERE `owned` = 0;
50END$$
51
52CREATE DEFINER=CURRENT_USER PROCEDURE `deleteOldGangs`()
53BEGIN
54 DELETE FROM `gangs` WHERE `active` = 0;
55END$$
56
57CREATE DEFINER=CURRENT_USER PROCEDURE `deleteOldContainers`()
58BEGIN
59 DELETE FROM `containers` WHERE `owned` = 0;
60END$$
61
62CREATE DEFINER=CURRENT_USER PROCEDURE `deleteOldWanted`()
63BEGIN
64 DELETE FROM `wanted` WHERE `active` = 0;
65END$$
66
67DELIMITER ;
68
69-- --------------------------------------------------------
70
71--
72-- Table structure for table `players`
73--
74
75CREATE TABLE IF NOT EXISTS `players` (
76 `uid` INT NOT NULL AUTO_INCREMENT,
77 `pid` VARCHAR(17) NOT NULL,
78 `name` VARCHAR(32) NOT NULL,
79 `aliases` TEXT NOT NULL,
80 `cash` INT NOT NULL DEFAULT 0,
81 `bankacc` INT NOT NULL DEFAULT 0,
82 `coplevel` ENUM('0','1','2','3','4','5','6','7') NOT NULL DEFAULT '0',
83 `mediclevel` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
84 `civ_licenses` TEXT NOT NULL,
85 `cop_licenses` TEXT NOT NULL,
86 `med_licenses` TEXT NOT NULL,
87 `civ_gear` TEXT NOT NULL,
88 `cop_gear` TEXT NOT NULL,
89 `med_gear` TEXT NOT NULL,
90 `civ_stats` VARCHAR(25) NOT NULL DEFAULT '"[100,100,0]"',
91 `cop_stats` VARCHAR(25) NOT NULL DEFAULT '"[100,100,0]"',
92 `med_stats` VARCHAR(25) NOT NULL DEFAULT '"[100,100,0]"',
93 `arrested` TINYINT NOT NULL DEFAULT 0,
94 `adminlevel` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
95 `donorlevel` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
96 `blacklist` TINYINT NOT NULL DEFAULT 0,
97 `civ_alive` TINYINT NOT NULL DEFAULT 0,
98 `civ_position` VARCHAR(32) NOT NULL DEFAULT '"[]"',
99 `playtime` VARCHAR(32) NOT NULL DEFAULT '"[0,0,0]"',
100 `insert_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
101 `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
102
103 PRIMARY KEY (`pid`),
104 UNIQUE KEY `unique_uid` (`uid`),
105 INDEX `index_name` (`name`),
106 INDEX `index_blacklist` (`blacklist`)
107) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
108
109-- --------------------------------------------------------
110
111--
112-- Table structure for table `vehicles`
113--
114
115CREATE TABLE IF NOT EXISTS `vehicles` (
116 `id` INT NOT NULL AUTO_INCREMENT,
117 `pid` VARCHAR(17) NOT NULL,
118 `side` VARCHAR(10) NOT NULL,
119 `classname` VARCHAR(64) NOT NULL,
120 `type` VARCHAR(16) NOT NULL,
121 `alive` TINYINT NOT NULL DEFAULT 1,
122 `blacklist` TINYINT NOT NULL DEFAULT 0,
123 `active` TINYINT NOT NULL DEFAULT 0,
124 `plate` MEDIUMINT NOT NULL,
125 `color` INT 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
132 PRIMARY KEY (`id`),
133 INDEX `fkIdx_players_vehicles` (`pid`),
134 CONSTRAINT `FK_players_vehicles` FOREIGN KEY `fkIdx_players_vehicles` (`pid`)
135 REFERENCES `players` (`pid`)
136 ON UPDATE CASCADE ON DELETE CASCADE,
137 INDEX `index_side` (`side`),
138 INDEX `index_type` (`type`)
139) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
140
141-- --------------------------------------------------------
142
143--
144-- Table structure for table `houses`
145-- Needed for extDB latest update on git
146--
147
148CREATE TABLE IF NOT EXISTS `houses` (
149 `id` INT NOT NULL AUTO_INCREMENT,
150 `pid` VARCHAR(17) NOT NULL,
151 `pos` VARCHAR(32) DEFAULT NULL,
152 `owned` TINYINT DEFAULT 0,
153 `garage` TINYINT NOT NULL DEFAULT 0,
154 `insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
155
156 PRIMARY KEY (`id`),
157 INDEX `fkIdx_players_houses` (`pid`),
158 CONSTRAINT `FK_players_houses` FOREIGN KEY `fkIdx_players_houses` (`pid`)
159 REFERENCES `players` (`pid`)
160 ON UPDATE CASCADE ON DELETE CASCADE
161) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
162
163-- --------------------------------------------------------
164
165--
166-- Table structure for table `gangs`
167-- Needed for extDB latest update on git
168--
169
170CREATE TABLE IF NOT EXISTS `gangs` (
171 `id` INT NOT NULL AUTO_INCREMENT,
172 `owner` VARCHAR(17) NOT NULL,
173 `name` VARCHAR(32) DEFAULT NULL,
174 `members` TEXT,
175 `maxmembers` INT DEFAULT 8,
176 `bank` INT DEFAULT 0,
177 `active` TINYINT NOT NULL DEFAULT 1,
178 `insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
179
180 PRIMARY KEY (`id`),
181 UNIQUE KEY `unique_name` (`name`),
182 INDEX `fkIdx_players_gangs` (`owner`),
183 CONSTRAINT `FK_players_gangs` FOREIGN KEY `fkIdx_players_gangs` (`owner`)
184 REFERENCES `players` (`pid`)
185 ON UPDATE CASCADE ON DELETE CASCADE
186) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
187
188-- --------------------------------------------------------
189
190--
191-- Table structure for table `containers`
192-- Needed for extDB latest update on git
193--
194
195CREATE TABLE IF NOT EXISTS `containers` (
196 `id` INT NOT NULL AUTO_INCREMENT,
197 `pid` VARCHAR(17) NOT NULL,
198 `classname` VARCHAR(32) NOT NULL,
199 `pos` VARCHAR(32) DEFAULT NULL,
200 `inventory` TEXT NOT NULL,
201 `gear` TEXT NOT NULL,
202 `dir` VARCHAR(128) DEFAULT NULL,
203 `active` TINYINT NOT NULL DEFAULT 0,
204 `owned` TINYINT NOT NULL DEFAULT 0,
205 `insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
206
207 PRIMARY KEY (`id`),
208 INDEX `fkIdx_players_containers` (`pid`),
209 CONSTRAINT `FK_players_containers` FOREIGN KEY `fkIdx_players_containers` (`pid`)
210 REFERENCES `players` (`pid`)
211 ON UPDATE CASCADE ON DELETE CASCADE
212) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
213
214-- --------------------------------------------------------
215
216--
217-- Table structure for table `wanted`
218-- Needed for extDB latest update on git
219--
220
221CREATE TABLE IF NOT EXISTS `wanted` (
222 `wantedID` VARCHAR(17) NOT NULL,
223 `wantedName` VARCHAR(32) NOT NULL,
224 `wantedCrimes` TEXT NOT NULL,
225 `wantedBounty` INT NOT NULL,
226 `active` TINYINT NOT NULL DEFAULT 0,
227 `insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
228
229 PRIMARY KEY (`wantedID`),
230 CONSTRAINT `FK_players_wanted` FOREIGN KEY `fkIdx_players_wanted` (`wantedID`)
231 REFERENCES `players` (`pid`)
232 ON UPDATE CASCADE ON DELETE CASCADE
233) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
234
235-- --------------------------------------------------------
236--
237-- Creates default user `arma3` with password `changeme` unless it already exists
238-- Granting permissions to user `arma3`, created below
239-- Reloads the privileges from the grant tables in the MySQL system database.
240--
241
242CREATE USER IF NOT EXISTS `arma3`@`localhost` IDENTIFIED BY '6YKr9umtrVBtMGWPH1NJDzYc';
243GRANT SELECT, UPDATE, INSERT, EXECUTE ON `altislife`.* TO 'arma3'@'localhost';
244FLUSH PRIVILEGES;
245
246/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
247/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
248/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;