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