· 9 years ago · Mar 13, 2017, 04:58 AM
1#!/usr/bin/perl
2
3# +---------------------------------------------------------------------------+
4# ! MODULES !
5# +---------------------------------------------------------------------------+
6
7use strict;
8use warnings;
9use diagnostics;
10
11use File::Basename;
12use DBI;
13
14# +---------------------------------------------------------------------------+
15# ! CONSTANTS !
16# +---------------------------------------------------------------------------+
17
18my $MAIN_PROG_DDBNAME = "duckhunt";
19my $MAIN_PROG_DBHOST = "";
20my $MAIN_PROG_DBPORT = "3306";
21my $MAIN_PROG_DBUSER = "duckhunt";
22my $MAIN_PROG_DBPASS = "xh5WmZSSyi";
23
24my $RET_SUCCESS = 0;
25my $ERR_FAILED_PARAMS = 1;
26my $ERR_CURL_OPEN = 2;
27my $ERR_FILE_OPEN = 3;
28my $ERR_DB_CONNECT = 4;
29my $ERR_TRUNCATE_STATS = 5;
30my $ERR_UTF8_QUERIES = 6;
31my $ERR_UPDATE_INFOS = 7;
32my $ERR_CREATE_CHANNEL = 8;
33my $ERR_CREATE_USER = 9;
34
35# +---------------------------------------------------------------------------+
36# ! SUBS !
37# +---------------------------------------------------------------------------+
38
39sub clean_and_exit(@);
40sub dbConnect(@);
41sub setMysqlUTF8();
42sub updateInfosTable(@);
43sub truncateStatsTable();
44sub getIdChannel(@);
45sub createChannel(@);
46sub getIdUser(@);
47sub createUser(@);
48
49# +---------------------------------------------------------------------------+
50# ! MAIN !
51# +---------------------------------------------------------------------------+
52
53my $players_table_location;
54
55# Check command line
56unless (defined($ARGV[0])) {
57 print "Usage : " . basename($0) . " <players_table.txt location>\n";
58 print "Example " . basename($0) . " /path/to/players_table.txt\n";
59 print "Example " . basename($0) . " http://host.domain.tld/path/to/players_table.txt\n";
60 clean_and_exit($ERR_FAILED_PARAMS);
61}
62else {
63 $players_table_location = $ARGV[0];
64 if ( $players_table_location =~ /^http[s]*:\/\// ) {
65 # Get stats with curl
66 unless ( open STATS_EXPORT, "curl -ks -f -s $players_table_location |") {
67 print STDERR "Could not open curl $players_table_location\n";
68 clean_and_exit($ERR_CURL_OPEN);
69 }
70 }
71 else {
72 # Get stats
73 unless ( open STATS_EXPORT, "$players_table_location") {
74 print STDERR "Could not open $players_table_location\n";
75 clean_and_exit($ERR_FILE_OPEN);
76 }
77 }
78}
79
80my $duck_hunt_players_table_url = "http://statik.chickenkiller.com/players_table.txt";
81
82# Establish a MySQL connection
83my $dbh = dbConnect($MAIN_PROG_DDBNAME,$MAIN_PROG_DBHOST,$MAIN_PROG_DBPORT,$MAIN_PROG_DBUSER,$MAIN_PROG_DBPASS);
84unless (defined($dbh)) {
85 print "Could not connect to database\n";
86 clean_and_exit($ERR_DB_CONNECT);
87}
88
89unless (truncateStatsTable()) {
90 print "Could not truncate table USER_CHANNEL_STATS\n";
91 clean_and_exit($ERR_TRUNCATE_STATS);
92}
93
94unless (setMysqlUTF8()) {
95 print "Could not execute UTF-8 queries\n";
96 clean_and_exit($ERR_UTF8_QUERIES);
97}
98
99
100
101
102
103my ($version,$strDate,$strTime);
104my $line;
105
106# Skip first line
107if (defined($line=<STATS_EXPORT>)) {}
108
109# Grab version and generated time
110if (defined($line=<STATS_EXPORT>)) {
111 (undef,undef,undef,$version,undef,undef,undef,undef,undef,undef,undef,undef,undef,$strDate,undef,$strTime) = split(/\s+/,$line);
112 unless(defined($version) && ($version ne "") && (defined($strDate)) && ($strDate ne "") && (defined($strTime)) && ($strTime ne "")) {
113 clean_and_exit($ERR_UPDATE_INFOS);
114 }
115 print "$version $strDate $strTime\n";
116 unless (updateInfosTable($version,$strDate,$strTime)) {
117 print "Could not update INFOS table";
118 clean_and_exit($ERR_UPDATE_INFOS);
119 }
120}
121
122while (defined($line=<STATS_EXPORT>)) {
123 # Skip lines until channel
124 while (defined($line=<STATS_EXPORT>) && !($line =~/^#/)) {}
125 unless (defined($line)) { clean_and_exit($RET_SUCCESS); }
126 chomp($line);
127
128 # Check if channel exists
129 my $channel = $line;
130 my $id_channel = getIdChannel($channel);
131 unless (defined($id_channel)) {
132 $id_channel = createChannel($channel);
133 }
134
135 unless (defined($id_channel)) {
136 print "Error creating channel $channel\n";
137 clean_and_exit($ERR_CREATE_CHANNEL);
138 }
139
140 my $i = 0;
141 while (defined($line=<STATS_EXPORT>) && ($i < 2)) { $i++; }
142 $i = 0;
143 while (defined($line=<STATS_EXPORT>) && !($line =~ /^\s*$/)) {
144 chomp($line);
145 my ($nick,$creation_date,$creation_time,$last_modified_date,$last_modified_time,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang);
146 my (undef,$test_creation,$test_last_modified) = split (/\s+/,$line);
147
148 if (( $test_creation eq '-' ) &&( $test_last_modified eq '-' )){
149 ($nick,$creation_date,$last_modified_date,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang) = split(/\s+/,$line,39);
150 }
151 elsif ( $test_creation eq '-' ) {
152 ($nick,$creation_date,$last_modified_date,$last_modified_time,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang) = split(/\s+/,$line,40);
153 }
154 else {
155 ($nick,$creation_date,$creation_time,$last_modified_date,$last_modified_time,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang) = split(/\s+/,$line,41);
156 }
157
158 my $id_user = getIdUser($nick);
159 unless (defined($id_user)) {
160 $id_user = createUser($nick);
161 }
162 unless (defined($id_user)) {
163 print "Could not create user $nick\n";
164 clean_and_exit($ERR_CREATE_USER);
165 }
166 my $strCreationDate;
167 unless ( $creation_date eq '-' ) {
168 my (undef,undef,$sYear) = split (/\//,$creation_date);
169 my (undef,$sMonth,undef) = split (/\//,$creation_date);
170 my ($sDay,undef,undef) = split (/\//,$creation_date);
171 $strCreationDate = "$sYear-$sMonth-$sDay $creation_time";
172 }
173 my $strLastModifiedDate;
174 unless ( $last_modified_date eq '-' ) {
175 my (undef,undef,$sYear) = split (/\//,$last_modified_date);
176 my (undef,$sMonth,undef) = split (/\//,$last_modified_date);
177 my ($sDay,undef,undef) = split (/\//,$last_modified_date);
178 $strLastModifiedDate = "$sYear-$sMonth-$sDay $last_modified_time";
179 }
180
181 my $sQuery = "INSERT INTO USER_CHANNEL_STATS (id_channel,id_user,creation_date,last_modified,xp,lvl,xp_level_sup,mun,mun_max,charg,charg_max,prec_theorique,efficacite_tirs,deflex,armure,enrayage,enraye,nbr_enrayages,arme,nbr_confisc,canards,super_canards,rates,tirs_a_vide,tirs_enrayes,recharg_compulsifs,accidents,tirs_sauvages,mun_utilis,tirs_recus,tirs_encaisses,tirs_devies,deces,meilleur_tps,tps_react_moyen,fatigue,karma,rentabilite,depense,rang) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
182 my $sth = $dbh->prepare($sQuery);
183
184 unless ($sth->execute($id_channel,$id_user,$strCreationDate,$strLastModifiedDate,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang)) {
185 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
186 }
187 $i++;
188 }
189 print "$channel $i joueurs traités\n";
190}
191
192close STATS_EXPORT;
193
194# This line shoud be useless but still ;)
195clean_and_exit($RET_SUCCESS);
196
197# +---------------------------------------------------------------------------+
198# ! SUBS IMPLEMENTATION !
199# +---------------------------------------------------------------------------+
200
201sub clean_and_exit(@) {
202 my ($iRetValue) = @_;
203 print "Cleaning and exiting...\n";
204 if (defined($dbh) && ($dbh != 0)) {
205 $dbh->disconnect();
206 }
207 if ( $iRetValue ) {
208 print "Something went wrong while importing Duck Hunt stats from export file\n";
209 }
210 exit $iRetValue;
211}
212
213sub dbConnect(@) {
214 my ($dbname,$dbhost,$dbport,$dbuser,$dbpasswd) = @_;
215 my $connectionInfo="DBI:mysql:database=$dbname;$dbhost:$dbport"; # Database connection string
216 my $dbh; # Database handle
217
218 print "dbConnect() Connecting to Database : " . $dbname;
219
220 unless ( $dbh = DBI->connect($connectionInfo,$dbuser,$dbpasswd) ) {
221 print "dbConnect() DBI Error : " . $DBI::errstr . "\n";
222 print "dbConnect() DBI Native error code : " . $DBI::err . "\n";
223 if ( defined( $DBI::err ) && ( $DBI::err == 1049 ) ) {
224 clean_and_exit($DBI::err);
225 }
226 return undef;
227 }
228 print "dbConnect() Connected to $dbname.\n";
229 return $dbh;
230}
231
232sub getIdChannel(@) {
233 my ($channel) = @_;
234 my $id_channel;
235 my $sQuery = "SELECT * FROM CHANNEL WHERE name=?";
236 my $sth = $dbh->prepare($sQuery);
237 unless ($sth->execute($channel)) {
238 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
239 }
240 else {
241 if (my $ref = $sth->fetchrow_hashref()) {
242 $id_channel = $ref->{'id_channel'};
243 }
244 }
245 return $id_channel;
246}
247
248sub createChannel(@) {
249 my ($channel) = @_;
250 my $id_channel;
251 my $sQuery = "INSERT INTO CHANNEL (name) VALUES (?)";
252 my $sth = $dbh->prepare($sQuery);
253 unless ($sth->execute($channel)) {
254 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
255 }
256 else {
257 $id_channel = getIdChannel($channel);
258 }
259 return $id_channel;
260}
261
262sub getIdUser(@) {
263 my ($nick) = @_;
264 my $id_user;
265 my $sQuery = "SELECT * FROM USER WHERE nick=?";
266 my $sth = $dbh->prepare($sQuery);
267 unless ($sth->execute($nick)) {
268 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
269 }
270 else {
271 if (my $ref = $sth->fetchrow_hashref()) {
272 $id_user = $ref->{'id_user'};
273 }
274 }
275 return $id_user;
276}
277
278sub createUser(@) {
279 my ($nick) = @_;
280 my $id_user;
281 my $sQuery = "INSERT INTO USER (nick) VALUES (?)";
282 my $sth = $dbh->prepare($sQuery);
283 unless ($sth->execute($nick)) {
284 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
285 }
286 else {
287 $id_user = getIdUser($nick);
288 }
289 return $id_user;
290}
291
292sub setMysqlUTF8() {
293 my $sQuery = "SET NAMES 'utf8'";
294 my $sth = $dbh->prepare($sQuery);
295 unless ($sth->execute()) {
296 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
297 return 0;
298 }
299 $sQuery = "SET CHARACTER SET utf8";
300 $sth = $dbh->prepare($sQuery);
301 unless ($sth->execute()) {
302 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
303 return 0;
304 }
305 $sQuery = "SET COLLATION_CONNECTION = 'utf8_general_ci'";
306 $sth = $dbh->prepare($sQuery);
307 unless ($sth->execute()) {
308 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
309 return 0;
310 }
311 else {
312 return 1;
313 }
314}
315
316sub truncateStatsTable() {
317 my $sQuery = "TRUNCATE TABLE USER_CHANNEL_STATS";
318 my $sth = $dbh->prepare($sQuery);
319 unless ($sth->execute()) {
320 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
321 return 0;
322 }
323 else {
324 print "Table USER_CHANNEL_STATS truncated\n";
325 return 1;
326 }
327}
328
329sub updateInfosTable(@) {
330 my ($version,$strDate,$strTime) = @_;
331 my $sQuery = "TRUNCATE TABLE INFOS";
332 my $sth = $dbh->prepare($sQuery);
333 unless ($sth->execute()) {
334 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
335 return 0;
336 }
337 else {
338 print "Table INFOS truncated\n";
339 my (undef,undef,$sYear) = split(/\//,$strDate);
340 my (undef,$sMonth,undef) = split(/\//,$strDate);
341 my ($sDay,undef,undef) = split(/\//,$strDate);
342 my $last_modified = "$sYear-$sMonth-$sDay $strTime";
343 $sQuery = "INSERT INTO INFOS (version,last_modified) VALUES (?,?)";
344 $sth = $dbh->prepare($sQuery);
345 unless ($sth->execute($version,$last_modified)) {
346 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
347 return 0;
348 }
349 else {
350 print "Infos updated\n";
351 return 1;
352 }
353 }
354}