· 9 years ago · Mar 02, 2017, 10:36 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 = "localhost";
20my $MAIN_PROG_DBPORT = 3306;
21my $MAIN_PROG_DBUSER = "duckhunt";
22my $MAIN_PROG_DBPASS = "********";
23
24my $duck_hunt_players_table_url = "http://host.domain.com/players_table.txt";
25
26# +---------------------------------------------------------------------------+
27# ! SUBS !
28# +---------------------------------------------------------------------------+
29
30sub clean_and_exit(@);
31sub dbConnect(@);
32sub truncateStatsTable();
33sub getIdChannel(@);
34sub createChannel(@);
35sub getIdUser(@);
36sub createUser(@);
37
38# +---------------------------------------------------------------------------+
39# ! MAIN !
40# +---------------------------------------------------------------------------+
41
42# Establish a MySQL connection
43my $dbh = dbConnect($MAIN_PROG_DDBNAME,$MAIN_PROG_DBHOST,$MAIN_PROG_DBPORT,$MAIN_PROG_DBUSER,$MAIN_PROG_DBPASS);
44unless (defined($dbh)) {
45 print "Could not connect to database\n";
46 clean_and_exit(1);
47}
48
49unless (truncateStatsTable()) {
50 print "Could not truncate table USER_CHANNEL_STATS\n";
51 clean_and_exit(2);
52}
53
54# Get stats with curl
55unless ( open CURL, "curl -f -s $duck_hunt_players_table_url |") {
56 print STDERR "Could not open curl $duck_hunt_players_table_url\n";
57 clean_and_exit(3);
58}
59
60my ($version,$strDate,$strTime);
61my $line;
62
63# Skip first line
64if (defined($line=<CURL>)) {}
65
66# Grab version and generated time
67if (defined($line=<CURL>)) {
68 (undef,undef,undef,$version,undef,undef,undef,undef,undef,undef,undef,undef,undef,$strDate,undef,$strTime) = split(/\s+/,$line);
69 print "$version $strDate $strTime\n";
70}
71
72while (defined($line=<CURL>)) {
73 # Skip lines until channel
74 while (defined($line=<CURL>) && !($line =~/^#/)) {}
75 unless (defined($line)) { clean_and_exit(0); }
76 chomp($line);
77
78 # Check if channel exists
79 my $channel = $line;
80 print "Channel : $channel\n";
81 my $id_channel = getIdChannel($channel);
82 unless (defined($id_channel)) {
83 $id_channel = createChannel($channel);
84 }
85
86 unless (defined($id_channel)) {
87 print "Error creating channel $channel\n";
88 clean_and_exit(4);
89 }
90
91 my $i = 0;
92 while (defined($line=<CURL>) && ($i < 2)) { $i++; }
93 $i = 0;
94 while (defined($line=<CURL>) && !($line =~ /^\s*$/)) {
95 chomp($line);
96 my ($nick,$creation_date,$last_modified,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite,$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);
97 print "$nick\n";
98 my $id_user = getIdUser($nick);
99 unless (defined($id_user)) {
100 $id_user = createUser($nick);
101 }
102 unless (defined($id_user)) {
103 print "Could not create user $nick\n";
104 clean_and_exit(5);
105 }
106
107 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,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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
108 my $sth = $dbh->prepare($sQuery);
109 unless ($sth->execute($id_channel,$id_user,$creation_date,$last_modified,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite,$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)) {
110 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
111 }
112 else {
113 print "Stats added for $nick\n";
114 }
115
116 $i++;
117 }
118 print "$channel $i joueurs traités\n";
119}
120
121close CURL;
122
123clean_and_exit(0);
124
125# +---------------------------------------------------------------------------+
126# ! SUBS IMPLEMENTATION !
127# +---------------------------------------------------------------------------+
128
129sub clean_and_exit(@) {
130 my ($iRetValue) = @_;
131 print "Cleaning and exiting...\n";
132 if (defined($dbh) && ($dbh != 0)) {
133 $dbh->disconnect();
134 }
135 exit $iRetValue;
136}
137
138sub dbConnect(@) {
139 my ($dbname,$dbhost,$dbport,$dbuser,$dbpasswd) = @_;
140 my $connectionInfo="DBI:mysql:database=$dbname;$dbhost:$dbport"; # Database connection string
141 my $dbh; # Database handle
142
143 print "dbConnect() Connecting to Database : " . $dbname;
144
145 unless ( $dbh = DBI->connect($connectionInfo,$dbuser,$dbpasswd) ) {
146 print "dbConnect() DBI Error : " . $DBI::errstr . "\n";
147 print "dbConnect() DBI Native error code : " . $DBI::err . "\n";
148 if ( defined( $DBI::err ) && ( $DBI::err == 1049 ) ) {
149 clean_and_exit($DBI::err);
150 }
151 return undef;
152 }
153 print "dbConnect() Connected to $dbname.\n";
154 return $dbh;
155}
156
157sub getIdChannel(@) {
158 my ($channel) = @_;
159 my $id_channel;
160 my $sQuery = "SELECT * FROM CHANNEL WHERE name=?";
161 my $sth = $dbh->prepare($sQuery);
162 unless ($sth->execute($channel)) {
163 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
164 }
165 else {
166 if (my $ref = $sth->fetchrow_hashref()) {
167 $id_channel = $ref->{'id_channel'};
168 }
169 }
170 return $id_channel;
171}
172
173sub createChannel(@) {
174 my ($channel) = @_;
175 my $id_channel;
176 my $sQuery = "INSERT INTO CHANNEL (name) VALUES (?)";
177 my $sth = $dbh->prepare($sQuery);
178 unless ($sth->execute($channel)) {
179 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
180 }
181 else {
182 $id_channel = getIdChannel($channel);
183 }
184 return $id_channel;
185}
186
187sub getIdUser(@) {
188 my ($nick) = @_;
189 my $id_user;
190 my $sQuery = "SELECT * FROM USER WHERE nick=?";
191 my $sth = $dbh->prepare($sQuery);
192 unless ($sth->execute($nick)) {
193 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
194 }
195 else {
196 if (my $ref = $sth->fetchrow_hashref()) {
197 $id_user = $ref->{'id_user'};
198 }
199 }
200 return $id_user;
201}
202
203sub createUser(@) {
204 my ($nick) = @_;
205 my $id_user;
206 my $sQuery = "INSERT INTO USER (nick) VALUES (?)";
207 my $sth = $dbh->prepare($sQuery);
208 unless ($sth->execute($nick)) {
209 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
210 }
211 else {
212 $id_user = getIdUser($nick);
213 }
214 return $id_user;
215}
216
217sub truncateStatsTable() {
218 my $sQuery = "TRUNCATE TABLE USER_CHANNEL_STATS";
219 my $sth = $dbh->prepare($sQuery);
220 unless ($sth->execute()) {
221 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
222 return 0;
223 }
224 else {
225 print "Table USER_CHANNEL_STATS truncated\n";
226 return 1;
227 }
228}