Fv4zgAz4

1import clr;
2import System;
3
4clr.AddReference("TCAdmin.DatabaseProviders.MySql");
5clr.AddReference("TCAdmin.SDK");
6from TCAdmin.DatabaseProviders.MySql import MySqlManager;
7from System import String;
8
9mysql_server="localhost";
10mysql_root="root";
11mysql_password="Password111";
12
13with MySqlManager() as mysql:
14 escapeduser=mysql.PrepareSqlValue(ThisService.Variables["MySQLUser"]);
15 escapedpass=mysql.PrepareSqlValue(ThisService.Variables["MySQLPassword"]);
16 mysql.Connect(String.Format("Data Source={0};User Id={1};Password={2};Pooling=False;", mysql_server, mysql_root, mysql_password));
17
18 mysql.ExecuteNonQuery(String.Format("DROP DATABASE IF EXISTS {0};", escapeduser));
19 if mysql.Execute(String.Format("SELECT * FROM mysql.user WHERE user='{0}' AND host='localhost';", escapeduser)).Rows.Count == 1 :
20  mysql.ExecuteNonQuery(String.Format("DROP USER {0}@localhost;", escapeduser));
21
22 mysql.ExecuteNonQuery(String.Format("CREATE DATABASE {0};", escapeduser));
23 mysql.ExecuteNonQuery(String.Format("GRANT ALL PRIVILEGES ON {0}.* TO '{0}'@'localhost' IDENTIFIED BY '{1}';", escapeduser, escapedpass));
24 mysql.ExecuteNonQuery(String.Format("USE '{0}';", escapeduser));
25 mysql.ExecuteNonQuery(String.Format("""CREATE TABLE `account` (
26
27  `uid` varchar(32) NOT NULL,
28  `clan_id` int(11) UNSIGNED DEFAULT NULL,
29  `name` varchar(64) NOT NULL,
30  `money` double NOT NULL DEFAULT '0',
31  `score` int(11) NOT NULL DEFAULT '0',
32  `kills` int(11) UNSIGNED NOT NULL DEFAULT '0',
33  `deaths` int(11) UNSIGNED NOT NULL DEFAULT '0',
34  `first_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
35  `last_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
36  `last_disconnect_at` datetime DEFAULT NULL,
37  `total_connections` int(11) UNSIGNED NOT NULL DEFAULT '1'
38) ENGINE=InnoDB DEFAULT CHARSET=utf8;
39CREATE TABLE `clan` (
40  `id` int(11) UNSIGNED NOT NULL,
41  `name` varchar(64) NOT NULL,
42  `leader_uid` varchar(32) NOT NULL,
43  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
44  `insignia_texture` varchar(255) DEFAULT NULL
45) ENGINE=InnoDB DEFAULT CHARSET=utf8;
46
47CREATE TABLE `construction` (
48  `id` int(11) UNSIGNED NOT NULL,
49  `class` varchar(64) NOT NULL,
50  `account_uid` varchar(32) NOT NULL,
51  `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
52  `position_x` double NOT NULL DEFAULT '0',
53  `position_y` double NOT NULL DEFAULT '0',
54  `position_z` double NOT NULL DEFAULT '0',
55  `direction_x` double NOT NULL DEFAULT '0',
56  `direction_y` double NOT NULL DEFAULT '0',
57  `direction_z` double NOT NULL DEFAULT '0',
58  `up_x` double NOT NULL DEFAULT '0',
59  `up_y` double NOT NULL DEFAULT '0',
60  `up_z` double NOT NULL DEFAULT '0',
61  `is_locked` tinyint(1) NOT NULL DEFAULT '0',
62  `pin_code` varchar(6) NOT NULL DEFAULT '000000',
63  `territory_id` int(11) UNSIGNED DEFAULT NULL,
64  `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
65) ENGINE=InnoDB DEFAULT CHARSET=utf8;
66
67CREATE TABLE `container` (
68  `id` int(11) UNSIGNED NOT NULL,
69  `class` varchar(64) NOT NULL,
70  `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
71  `account_uid` varchar(32) DEFAULT NULL,
72  `is_locked` tinyint(1) NOT NULL DEFAULT '0',
73  `position_x` double NOT NULL DEFAULT '0',
74  `position_y` double NOT NULL DEFAULT '0',
75  `position_z` double NOT NULL DEFAULT '0',
76  `direction_x` double NOT NULL DEFAULT '0',
77  `direction_y` double NOT NULL DEFAULT '0',
78  `direction_z` double NOT NULL DEFAULT '0',
79  `up_x` double NOT NULL DEFAULT '0',
80  `up_y` double NOT NULL DEFAULT '0',
81  `up_z` double NOT NULL DEFAULT '1',
82  `cargo_items` text NOT NULL,
83  `cargo_magazines` text NOT NULL,
84  `cargo_weapons` text NOT NULL,
85  `cargo_container` text NOT NULL,
86  `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
87  `pin_code` varchar(6) NOT NULL DEFAULT '000000',
88  `territory_id` int(11) UNSIGNED DEFAULT NULL,
89  `abandoned` DATETIME DEFAULT NULL
90) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
91
92CREATE TABLE `player` (
93  `id` int(11) UNSIGNED NOT NULL,
94  `name` varchar(64) NOT NULL,
95  `account_uid` varchar(32) NOT NULL,
96  `damage` double UNSIGNED NOT NULL DEFAULT '0',
97  `hunger` double UNSIGNED NOT NULL DEFAULT '100',
98  `thirst` double UNSIGNED NOT NULL DEFAULT '100',
99  `alcohol` double UNSIGNED NOT NULL DEFAULT '0',
100  `temperature` double NOT NULL DEFAULT '37',
101  `wetness` double UNSIGNED NOT NULL DEFAULT '0',
102  `oxygen_remaining` double UNSIGNED NOT NULL DEFAULT '1',
103  `bleeding_remaining` double UNSIGNED NOT NULL DEFAULT '0',
104  `hitpoints` varchar(255) NOT NULL DEFAULT '[]',
105  `direction` double NOT NULL DEFAULT '0',
106  `position_x` double NOT NULL DEFAULT '0',
107  `position_y` double NOT NULL DEFAULT '0',
108  `position_z` double NOT NULL DEFAULT '0',
109  `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
110  `assigned_items` text NOT NULL,
111  `backpack` varchar(64) NOT NULL,
112  `backpack_items` text NOT NULL,
113  `backpack_magazines` text NOT NULL,
114  `backpack_weapons` text NOT NULL,
115  `current_weapon` varchar(64) NOT NULL,
116  `goggles` varchar(64) NOT NULL,
117  `handgun_items` text NOT NULL,
118  `handgun_weapon` varchar(64) NOT NULL,
119  `headgear` varchar(64) NOT NULL,
120  `binocular` varchar(64) NOT NULL,
121  `loaded_magazines` text NOT NULL,
122  `primary_weapon` varchar(64) NOT NULL,
123  `primary_weapon_items` text NOT NULL,
124  `secondary_weapon` varchar(64) NOT NULL,
125  `secondary_weapon_items` text NOT NULL,
126  `uniform` varchar(64) NOT NULL,
127  `uniform_items` text NOT NULL,
128  `uniform_magazines` text NOT NULL,
129  `uniform_weapons` text NOT NULL,
130  `vest` varchar(64) NOT NULL,
131  `vest_items` text NOT NULL,
132  `vest_magazines` text NOT NULL,
133  `vest_weapons` text NOT NULL,
134  `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
135) ENGINE=InnoDB DEFAULT CHARSET=utf8;
136
137CREATE TABLE `player_history` (
138  `id` int(11) UNSIGNED NOT NULL,
139  `account_uid` varchar(32) NOT NULL,
140  `name` varchar(64) NOT NULL,
141  `died_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
142  `position_x` double NOT NULL,
143  `position_y` double NOT NULL,
144  `position_z` double NOT NULL
145) ENGINE=InnoDB DEFAULT CHARSET=utf8;
146
147CREATE TABLE `territory` (
148  `id` int(11) UNSIGNED NOT NULL,
149  `owner_uid` varchar(32) NOT NULL,
150  `name` varchar(64) NOT NULL,
151  `position_x` double NOT NULL,
152  `position_y` double NOT NULL,
153  `position_z` double NOT NULL,
154  `radius` double NOT NULL,
155  `level` int(11) NOT NULL,
156  `flag_texture` varchar(255) NOT NULL,
157  `flag_stolen` tinyint(1) NOT NULL DEFAULT '0',
158  `flag_stolen_by_uid` varchar(32) DEFAULT NULL,
159  `flag_stolen_at` datetime DEFAULT NULL,
160  `flag_steal_message` varchar(255) DEFAULT NULL,
161  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
162  `last_paid_at` datetime DEFAULT CURRENT_TIMESTAMP,
163  `build_rights` varchar(640) NOT NULL DEFAULT '0',
164  `moderators` varchar(320) NOT NULL DEFAULT '0'
165) ENGINE=InnoDB DEFAULT CHARSET=utf8;
166
167CREATE TABLE `vehicle` (
168  `id` int(11) UNSIGNED NOT NULL,
169  `class` varchar(64) NOT NULL,
170  `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
171  `account_uid` varchar(32) DEFAULT NULL,
172  `is_locked` tinyint(1) NOT NULL DEFAULT '0',
173  `fuel` double UNSIGNED NOT NULL DEFAULT '0',
174  `damage` double UNSIGNED NOT NULL DEFAULT '0',
175  `hitpoints` text NOT NULL,
176  `position_x` double NOT NULL DEFAULT '0',
177  `position_y` double NOT NULL DEFAULT '0',
178  `position_z` double NOT NULL DEFAULT '0',
179  `direction_x` double NOT NULL DEFAULT '0',
180  `direction_y` double NOT NULL DEFAULT '0',
181  `direction_z` double NOT NULL DEFAULT '0',
182  `up_x` double NOT NULL DEFAULT '0',
183  `up_y` double NOT NULL DEFAULT '0',
184  `up_z` double NOT NULL DEFAULT '1',
185  `cargo_items` text NOT NULL,
186  `cargo_magazines` text NOT NULL,
187  `cargo_weapons` text NOT NULL,
188  `cargo_container` text NOT NULL,
189  `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
190  `pin_code` varchar(6) NOT NULL DEFAULT '000000',
191  `vehicle_texture` text NOT NULL
192) ENGINE=InnoDB DEFAULT CHARSET=utf8;
193
194ALTER TABLE `account`
195  ADD PRIMARY KEY (`uid`),
196  ADD KEY `clan_id` (`clan_id`);
197
198ALTER TABLE `clan`
199  ADD PRIMARY KEY (`id`),
200  ADD KEY `leader_uid` (`leader_uid`);
201
202ALTER TABLE `construction`
203  ADD PRIMARY KEY (`id`),
204  ADD KEY `account_uid` (`account_uid`),
205  ADD KEY `territory_id` (`territory_id`);
206
207--
208-- Indexes for table `container`
209--
210ALTER TABLE `container`
211  ADD PRIMARY KEY (`id`),
212  ADD KEY `account_uid` (`account_uid`),
213  ADD KEY `territory_id` (`territory_id`);
214
215ALTER TABLE `player`
216  ADD PRIMARY KEY (`id`),
217  ADD KEY `player_uid` (`account_uid`);
218
219ALTER TABLE `player_history`
220  ADD PRIMARY KEY (`id`);
221
222ALTER TABLE `territory`
223  ADD PRIMARY KEY (`id`),
224  ADD KEY `owner_uid` (`owner_uid`),
225  ADD KEY `flag_stolen_by_uid` (`flag_stolen_by_uid`);
226
227ALTER TABLE `vehicle`
228  ADD PRIMARY KEY (`id`),
229  ADD KEY `account_uid` (`account_uid`);
230
231ALTER TABLE `clan`
232  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
233
234ALTER TABLE `construction`
235  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
236
237ALTER TABLE `container`
238  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
239
240ALTER TABLE `player`
241  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;
242
243ALTER TABLE `player_history`
244  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
245
246ALTER TABLE `territory`
247  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
248
249ALTER TABLE `vehicle`
250  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
251
252ALTER TABLE `account`
253  ADD CONSTRAINT `account_ibfk_1` FOREIGN KEY (`clan_id`) REFERENCES `clan` (`id`) ON DELETE SET NULL;
254
255ALTER TABLE `clan`
256  ADD CONSTRAINT `clan_ibfk_1` FOREIGN KEY (`leader_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE;
257
258ALTER TABLE `construction`
259  ADD CONSTRAINT `construction_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
260  ADD CONSTRAINT `construction_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE;
261
262ALTER TABLE `container`
263  ADD CONSTRAINT `container_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
264  ADD CONSTRAINT `container_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE;
265
266ALTER TABLE `player`
267  ADD CONSTRAINT `player_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE;
268
269ALTER TABLE `territory`
270  ADD CONSTRAINT `territory_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
271  ADD CONSTRAINT `territory_ibfk_2` FOREIGN KEY (`flag_stolen_by_uid`) REFERENCES `account` (`uid`) ON DELETE SET NULL;
272
273ALTER TABLE `vehicle`
274  ADD CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE;""", escapeduser));