· 6 years ago · Apr 07, 2019, 08:58 AM
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(6) NOT NULL AUTO_INCREMENT,
77 `name` varchar(32) NOT NULL,
78 `aliases` text NOT NULL,
79 `pid` varchar(17) NOT NULL,
80 `cash` int(100) NOT NULL DEFAULT '0',
81 `bankacc` int(100) 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(32) NOT NULL DEFAULT '"[100,100,0]"',
91 `cop_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
92 `med_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
93 `arrested` tinyint(1) 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(1) NOT NULL DEFAULT '0',
97 `civ_alive` tinyint(1) NOT NULL DEFAULT '0',
98 `civ_position` varchar(64) 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 PRIMARY KEY (`uid`),
103 UNIQUE KEY `pid` (`pid`),
104 KEY `name` (`name`),
105 KEY `blacklist` (`blacklist`)
106) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=12 ;
107
108-- --------------------------------------------------------
109
110--
111-- Table structure for table `vehicles`
112--
113
114CREATE TABLE IF NOT EXISTS `vehicles` (
115 `id` int(6) NOT NULL AUTO_INCREMENT,
116 `side` varchar(16) NOT NULL,
117 `classname` varchar(64) NOT NULL,
118 `type` varchar(16) NOT NULL,
119 `pid` varchar(17) NOT NULL,
120 `alive` tinyint(1) NOT NULL DEFAULT '1',
121 `blacklist` tinyint(1) NOT NULL DEFAULT '0',
122 `active` tinyint(1) NOT NULL DEFAULT '0',
123 `plate` int(20) NOT NULL,
124 `color` int(20) NOT NULL,
125 `inventory` text NOT NULL,
126 `gear` text NOT NULL,
127 `fuel` double NOT NULL DEFAULT '1',
128 `damage` varchar(256) NOT NULL,
129 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
130 PRIMARY KEY (`id`),
131 KEY `side` (`side`),
132 KEY `pid` (`pid`),
133 KEY `type` (`type`)
134) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=2 ;
135
136-- --------------------------------------------------------
137
138--
139-- Table structure for table `houses`
140-- Needed for extDB latest update on git
141--
142
143CREATE TABLE IF NOT EXISTS `houses` (
144 `id` int(6) NOT NULL AUTO_INCREMENT,
145 `pid` varchar(17) NOT NULL,
146 `pos` varchar(64) DEFAULT NULL,
147 `owned` tinyint(1) DEFAULT '0',
148 `garage` tinyint(1) NOT NULL DEFAULT '0',
149 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
150 PRIMARY KEY (`id`,`pid`)
151) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=4 ;
152
153-- --------------------------------------------------------
154
155--
156-- Table structure for table `gangs`
157-- Needed for extDB latest update on git
158--
159
160CREATE TABLE IF NOT EXISTS `gangs` (
161 `id` int(6) NOT NULL AUTO_INCREMENT,
162 `owner` varchar(32) DEFAULT NULL,
163 `name` varchar(32) DEFAULT NULL,
164 `members` text,
165 `maxmembers` int(3) DEFAULT '8',
166 `bank` int(100) DEFAULT '0',
167 `active` tinyint(1) DEFAULT '1',
168 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
169 PRIMARY KEY (`id`),
170 UNIQUE KEY `name_UNIQUE` (`name`)
171) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
172
173-- --------------------------------------------------------
174
175--
176-- Table structure for table `containers`
177-- Needed for extDB latest update on git
178--
179
180CREATE TABLE IF NOT EXISTS `containers` (
181 `id` int(6) NOT NULL AUTO_INCREMENT,
182 `pid` varchar(17) NOT NULL,
183 `classname` varchar(32) NOT NULL,
184 `pos` varchar(64) DEFAULT NULL,
185 `inventory` text NOT NULL,
186 `gear` text NOT NULL,
187 `dir` varchar(128) DEFAULT NULL,
188 `active` tinyint(1) NOT NULL DEFAULT '0',
189 `owned` tinyint(1) DEFAULT '0',
190 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
191 PRIMARY KEY (`id`,`pid`)
192) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=4;
193
194-- --------------------------------------------------------
195
196--
197-- Table structure for table `wanted`
198-- Needed for extDB latest update on git
199--
200
201CREATE TABLE IF NOT EXISTS `wanted` (
202 `wantedID` varchar(64) NOT NULL,
203 `wantedName` varchar(32) NOT NULL,
204 `wantedCrimes` text NOT NULL,
205 `wantedBounty` int(100) NOT NULL,
206 `active` tinyint(1) NOT NULL DEFAULT '0',
207 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
208 PRIMARY KEY (`wantedID`)
209) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
210
211-- --------------------------------------------------------
212--
213-- Creates default user `arma3` with password `changeme` unless it already exists
214-- Granting permissions to user `arma3`, created below
215-- Reloads the privileges from the grant tables in the MySQL system database.
216--
217
218CREATE USER IF NOT EXISTS `arma3`@`localhost` IDENTIFIED BY 'changeme';
219GRANT SELECT, UPDATE, INSERT, EXECUTE ON `altislife`.* TO 'arma3'@'localhost';
220FLUSH PRIVILEGES;
221
222/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
223/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
224/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;