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