· 8 years ago · Feb 26, 2018, 09:04 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
17CREATE DATABASE IF NOT EXISTS `altislife` DEFAULT CHARACTER SET utf8mb4;
18USE `altislife`;
19
20--
21-- Drop procedures to ensure no conflicts
22--
23DROP PROCEDURE IF EXISTS `resetLifeVehicles`;
24DROP PROCEDURE IF EXISTS `deleteDeadVehicles`;
25DROP PROCEDURE IF EXISTS `deleteOldHouses`;
26DROP PROCEDURE IF EXISTS `deleteOldGangs`;
27DROP PROCEDURE IF EXISTS `deleteOldContainers`;
28
29DELIMITER $$
30--
31-- Procedures
32-- Edit root to match a user in MySQL
33-- For external databases: Edit localhost to match arma3server IP
34--
35
36CREATE DEFINER=`root`@`localhost` PROCEDURE `resetLifeVehicles`()
37BEGIN
38 UPDATE `vehicles` SET `active`= 0;
39END$$
40
41CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDeadVehicles`()
42BEGIN
43 DELETE FROM `vehicles` WHERE `alive` = 0;
44END$$
45
46CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteOldHouses`()
47BEGIN
48 DELETE FROM `houses` WHERE `owned` = 0;
49END$$
50
51CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteOldGangs`()
52BEGIN
53 DELETE FROM `gangs` WHERE `active` = 0;
54END$$
55
56CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteOldContainers`()
57BEGIN
58 DELETE FROM `containers` WHERE `owned` = 0;
59END$$
60
61DELIMITER ;
62
63-- --------------------------------------------------------
64
65--
66-- Table structure for table `players`
67--
68
69CREATE TABLE IF NOT EXISTS `players` (
70 `uid` int(12) NOT NULL AUTO_INCREMENT,
71 `name` varchar(32) NOT NULL,
72 `aliases` text NOT NULL,
73 `playerid` varchar(64) NOT NULL,
74 `cash` int(100) NOT NULL DEFAULT '0',
75 `bankacc` int(100) NOT NULL DEFAULT '0',
76 `coplevel` enum('0','1','2','3','4','5','6','7','8','9','10') NOT NULL DEFAULT '0',
77 `mediclevel` enum('0','1','2','3','4','5','6','7','8','9','10') NOT NULL DEFAULT '0',
78 `civ_licenses` text NOT NULL,
79 `cop_licenses` text NOT NULL,
80 `med_licenses` text NOT NULL,
81 `civ_gear` text NOT NULL,
82 `cop_gear` text NOT NULL,
83 `med_gear` text NOT NULL,
84 `civ_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
85 `cop_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
86 `med_stats` varchar(32) NOT NULL DEFAULT '"[100,100,0]"',
87 `arrested` tinyint(1) NOT NULL DEFAULT '0',
88 `adminlevel` enum('0','1','2','3','4','5') NOT NULL DEFAULT '0',
89 `donorlevel` enum('0','1','2','3','4','5') NOT NULL DEFAULT '0',
90 `blacklist` tinyint(1) NOT NULL DEFAULT '0',
91 `civ_alive` tinyint(1) NOT NULL DEFAULT '0',
92 `civ_position` varchar(64) NOT NULL DEFAULT '"[]"',
93 `playtime` varchar(32) NOT NULL DEFAULT '"[0,0,0]"',
94 `insert_time` timestamp DEFAULT CURRENT_TIMESTAMP,
95 `last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
96 PRIMARY KEY (`uid`),
97 UNIQUE KEY `playerid` (`playerid`),
98 KEY `name` (`name`),
99 KEY `blacklist` (`blacklist`)
100) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=12 ;
101
102-- --------------------------------------------------------
103
104--
105-- Table structure for table `vehicles`
106--
107
108CREATE TABLE IF NOT EXISTS `vehicles` (
109 `id` int(12) NOT NULL AUTO_INCREMENT,
110 `side` varchar(16) NOT NULL,
111 `classname` varchar(64) NOT NULL,
112 `type` varchar(16) NOT NULL,
113 `pid` varchar(32) NOT NULL,
114 `alive` tinyint(1) NOT NULL DEFAULT '1',
115 `blacklist` tinyint(1) NOT NULL DEFAULT '0',
116 `active` tinyint(1) NOT NULL DEFAULT '0',
117 `plate` int(20) NOT NULL,
118 `color` int(20) NOT NULL,
119 `inventory` text NOT NULL,
120 `gear` text NOT NULL,
121 `fuel` double NOT NULL DEFAULT '1',
122 `damage` varchar(256) NOT NULL,
123 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
124 PRIMARY KEY (`id`),
125 KEY `side` (`side`),
126 KEY `pid` (`pid`),
127 KEY `type` (`type`)
128) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=2 ;
129
130-- --------------------------------------------------------
131
132--
133-- Table structure for table `houses`
134-- Needed for extDB latest update on git
135--
136
137CREATE TABLE IF NOT EXISTS `houses` (
138 `id` int(11) NOT NULL AUTO_INCREMENT,
139 `pid` varchar(32) NOT NULL,
140 `pos` varchar(64) DEFAULT NULL,
141 `owned` tinyint(1) DEFAULT '0',
142 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
143 PRIMARY KEY (`id`,`pid`)
144) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=4 ;
145
146-- --------------------------------------------------------
147
148--
149-- Table structure for table `gangs`
150-- Needed for extDB latest update on git
151--
152
153CREATE TABLE IF NOT EXISTS `gangs` (
154 `id` int(11) NOT NULL AUTO_INCREMENT,
155 `owner` varchar(32) DEFAULT NULL,
156 `name` varchar(32) DEFAULT NULL,
157 `members` text,
158 `maxmembers` int(3) DEFAULT '8',
159 `bank` int(100) DEFAULT '0',
160 `active` tinyint(1) DEFAULT '1',
161 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
162 PRIMARY KEY (`id`),
163 UNIQUE KEY `name_UNIQUE` (`name`)
164) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
165
166-- --------------------------------------------------------
167
168--
169-- Table structure for table `containers`
170-- Needed for extDB latest update on git
171--
172
173CREATE TABLE IF NOT EXISTS `containers` (
174 `id` int(11) NOT NULL AUTO_INCREMENT,
175 `pid` varchar(32) NOT NULL,
176 `classname` varchar(32) NOT NULL,
177 `pos` varchar(64) DEFAULT NULL,
178 `inventory` text NOT NULL,
179 `gear` text NOT NULL,
180 `dir` varchar(128) DEFAULT NULL,
181 `active` tinyint(1) NOT NULL DEFAULT '0',
182 `owned` tinyint(1) DEFAULT '0',
183 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
184 PRIMARY KEY (`id`,`pid`)
185) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=4;
186
187-- --------------------------------------------------------
188
189--
190-- Table structure for table `wanted`
191-- Needed for extDB latest update on git
192--
193
194CREATE TABLE IF NOT EXISTS `wanted` (
195 `wantedID` varchar(64) NOT NULL,
196 `wantedName` varchar(32) NOT NULL,
197 `wantedCrimes` text NOT NULL,
198 `wantedBounty` int(100) NOT NULL,
199 `active` tinyint(1) NOT NULL DEFAULT '0',
200 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
201 PRIMARY KEY (`wantedID`)
202) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
203
204-- --------------------------------------------------------
205--
206-- Creates default user `arma3` with password `changeme` unless it already exists
207-- Granting permissions to user `arma3`, created below
208-- Reloads the privileges from the grant tables in the mysql system database.
209--
210
211CREATE USER IF NOT EXISTS `arma3`@`localhost` IDENTIFIED BY '123';
212GRANT SELECT, UPDATE, INSERT, EXECUTE ON `altislife`.* TO 'arma3'@'localhost';
213FLUSH PRIVILEGES;
214
215/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
216/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
217/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;