| 1 | #! /usr/bin/perl |
| 2 | # |
| 3 | # install.pl |
| 4 | # |
| 5 | # This script will create database tables as necessary. |
| 6 | # |
| 7 | # It is part of the NewsStats package. |
| 8 | # |
| 9 | # Copyright (c) 2010-2013 Thomas Hochstein <thh@inter.net> |
| 10 | # |
| 11 | # It can be redistributed and/or modified under the same terms under |
| 12 | # which Perl itself is published. |
| 13 | |
| 14 | BEGIN { |
| 15 | our $VERSION = "0.01"; |
| 16 | use File::Basename; |
| 17 | # we're in .../install, so our module is in ../lib |
| 18 | push(@INC, dirname($0).'/../lib'); |
| 19 | } |
| 20 | use strict; |
| 21 | use warnings; |
| 22 | |
| 23 | use NewsStats qw(:DEFAULT); |
| 24 | |
| 25 | use Cwd; |
| 26 | |
| 27 | use DBI; |
| 28 | use Getopt::Long qw(GetOptions); |
| 29 | Getopt::Long::config ('bundling'); |
| 30 | |
| 31 | ################################# Main program ################################# |
| 32 | |
| 33 | ### read commandline options |
| 34 | my ($OptUpdate,$OptConfFile); |
| 35 | GetOptions ('u|update=s' => \$OptUpdate, |
| 36 | 'conffile=s' => \$OptConfFile, |
| 37 | 'h|help' => \&ShowPOD, |
| 38 | 'V|version' => \&ShowVersion) or exit 1; |
| 39 | |
| 40 | ### change working directory to .. (as we're in .../install) |
| 41 | chdir dirname($FullPath).'/..'; |
| 42 | my $Path = cwd(); |
| 43 | |
| 44 | ### read configuration |
| 45 | print("Reading configuration.\n"); |
| 46 | my %Conf = %{ReadConfig($OptConfFile)}; |
| 47 | |
| 48 | ##### -------------------------------------------------------------------------- |
| 49 | ##### Database table definitions |
| 50 | ##### -------------------------------------------------------------------------- |
| 51 | |
| 52 | my $DBCreate = <<SQLDB; |
| 53 | CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8; |
| 54 | SQLDB |
| 55 | |
| 56 | my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableParse' => <<PARSE, 'DBTableGrps' => <<GRPS); |
| 57 | -- |
| 58 | -- Table structure for table DBTableRaw |
| 59 | -- |
| 60 | |
| 61 | CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` ( |
| 62 | `id` bigint(20) unsigned NOT NULL auto_increment, |
| 63 | `day` date NOT NULL, |
| 64 | `mid` varchar(250) character set ascii NOT NULL, |
| 65 | `date` datetime NOT NULL, |
| 66 | `timestamp` bigint(20) NOT NULL, |
| 67 | `token` varchar(80) character set ascii NOT NULL, |
| 68 | `size` bigint(20) NOT NULL, |
| 69 | `peer` varchar(250) NOT NULL, |
| 70 | `path` varchar(1000) NOT NULL, |
| 71 | `newsgroups` varchar(1000) NOT NULL, |
| 72 | `headers` longtext NOT NULL, |
| 73 | `disregard` tinyint(1) default '0', |
| 74 | PRIMARY KEY (`id`), |
| 75 | KEY `day` (`day`), |
| 76 | KEY `mid` (`mid`), |
| 77 | KEY `peer` (`peer`) |
| 78 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data'; |
| 79 | RAW |
| 80 | -- |
| 81 | -- Table structure for table DBTableParse |
| 82 | -- |
| 83 | |
| 84 | CREATE TABLE IF NOT EXISTS `$Conf{'DBTableParse'}` ( |
| 85 | `id` bigint(20) unsigned NOT NULL auto_increment, |
| 86 | `day` date NOT NULL, |
| 87 | `mid` varchar(250) character set ascii NOT NULL, |
| 88 | `refs` varchar(1000) character set ascii, |
| 89 | `date` varchar(100) NOT NULL, |
| 90 | `path` varchar(1000) NOT NULL, |
| 91 | `newsgroups` varchar(1000) NOT NULL, |
| 92 | `fupto` varchar(200), |
| 93 | `from_` varchar(500), |
| 94 | `from_parsed` varchar(200), |
| 95 | `from_name` varchar(200), |
| 96 | `from_address` varchar(200), |
| 97 | `sender` varchar(500), |
| 98 | `sender_parsed` varchar(200), |
| 99 | `sender_name` varchar(200), |
| 100 | `sender_address` varchar(200), |
| 101 | `replyto` varchar(500), |
| 102 | `replyto_parsed` varchar(200), |
| 103 | `replyto_name` varchar(200), |
| 104 | `replyto_address` varchar(200), |
| 105 | `subject` varchar(1000) NOT NULL, |
| 106 | `subject_parsed` varchar(1000), |
| 107 | `organization` varchar(1000), |
| 108 | `linecount` int(4) unsigned, |
| 109 | `approved` varchar(250), |
| 110 | `supersedes` varchar(250), |
| 111 | `expires` varchar(100), |
| 112 | `useragent` varchar(500), |
| 113 | `xnewsreader` varchar(500), |
| 114 | `xmailer` varchar(500), |
| 115 | `xnoarchive` varchar(100), |
| 116 | `contenttype` varchar(500), |
| 117 | `contentencoding` varchar(500), |
| 118 | `cancellock` varchar(500), |
| 119 | `injectioninfo` varchar(500), |
| 120 | `xtrace` varchar(500), |
| 121 | `postinghost` varchar(1000), |
| 122 | `headers` longtext, |
| 123 | `disregard` tinyint(1) default '0', |
| 124 | PRIMARY KEY (`id`), |
| 125 | KEY `day` (`day`), |
| 126 | KEY `mid` (`mid`), |
| 127 | KEY `newsgroups` (`newsgroups`) |
| 128 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Parsed data'; |
| 129 | PARSE |
| 130 | -- |
| 131 | -- Table structure for table DBTableGrps |
| 132 | -- |
| 133 | |
| 134 | CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` ( |
| 135 | `id` bigint(20) unsigned NOT NULL auto_increment, |
| 136 | `month` varchar(7) character set ascii NOT NULL, |
| 137 | `newsgroup` varchar(100) NOT NULL, |
| 138 | `postings` int(11) NOT NULL, |
| 139 | `revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
| 140 | PRIMARY KEY (`id`), |
| 141 | UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`), |
| 142 | KEY `newsgroup` (`newsgroup`), |
| 143 | KEY `postings` (`postings`) |
| 144 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup'; |
| 145 | GRPS |
| 146 | |
| 147 | ##### -------------------------------------------------------------------------- |
| 148 | ##### Installation / upgrade instructions |
| 149 | ##### -------------------------------------------------------------------------- |
| 150 | |
| 151 | my $Install = <<INSTALL; |
| 152 | ---------- |
| 153 | Things left to do: |
| 154 | |
| 155 | 1) Setup an INN feed to feedlog.pl |
| 156 | |
| 157 | a) Edit your 'newsfeeds' file and insert something like |
| 158 | |
| 159 | ## gather statistics for NewsStats |
| 160 | newsstats!\\ |
| 161 | :!*,de.*\\ |
| 162 | :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl |
| 163 | |
| 164 | Please |
| 165 | |
| 166 | * check that you got the path to feedlog.pl right |
| 167 | * check that feedlog.pl can be executed by the news user |
| 168 | * adapt the pattern (here: 'de.*') to your needs |
| 169 | |
| 170 | b) Check your 'newsfeeds' syntax: |
| 171 | |
| 172 | # ctlinnd checkfile |
| 173 | |
| 174 | and reload 'newsfeeds': |
| 175 | |
| 176 | # ctlinnd reload newsfeeds 'Adding newsstats! feed' |
| 177 | |
| 178 | c) Watch your 'news.notice' and 'errlog' files: |
| 179 | |
| 180 | # tail -f /var/log/news/news.notice |
| 181 | ... |
| 182 | # tail -f /var/log/news/errlog |
| 183 | |
| 184 | 2) Watch your $Conf{'DBTableRaw'} table fill. |
| 185 | |
| 186 | 3) Read the documentation. ;) |
| 187 | |
| 188 | Enjoy! |
| 189 | |
| 190 | -thh <thh\@inter.net> |
| 191 | INSTALL |
| 192 | |
| 193 | my $Upgrade =''; |
| 194 | if ($OptUpdate) { |
| 195 | $Upgrade = <<UPGRADE; |
| 196 | ---------- |
| 197 | Your installation was upgraded from $OptUpdate to $PackageVersion. |
| 198 | |
| 199 | Don't forget to restart your INN feed so that it can pick up the new version: |
| 200 | |
| 201 | # ctlinnd begin 'newsstats!' |
| 202 | |
| 203 | (or whatever you called your feed). |
| 204 | UPGRADE |
| 205 | } |
| 206 | |
| 207 | ##### --------------------------- End of definitions --------------------------- |
| 208 | |
| 209 | ### create DB, if necessary |
| 210 | if (!$OptUpdate) { |
| 211 | print "----------\nStarting database creation.\n"; |
| 212 | # create database |
| 213 | # we can't use InitDB() as that will use a table name of |
| 214 | # the table that doesn't exist yet ... |
| 215 | my $DBHandle = DBI->connect(sprintf('DBI:%s:host=%s',$Conf{'DBDriver'}, |
| 216 | $Conf{'DBHost'}), $Conf{'DBUser'}, |
| 217 | $Conf{'DBPw'}, { PrintError => 0 }); |
| 218 | my $DBQuery = $DBHandle->prepare($DBCreate); |
| 219 | $DBQuery->execute() or &Bleat(2, sprintf("Can't create database %s: %s%\n", |
| 220 | $Conf{'DBDatabase'}, $DBI::errstr)); |
| 221 | |
| 222 | printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'}); |
| 223 | $DBHandle->disconnect; |
| 224 | }; |
| 225 | |
| 226 | ### DB init, read list of tables |
| 227 | print "Reading database information.\n"; |
| 228 | my $DBHandle = InitDB(\%Conf,1); |
| 229 | my %TablesInDB = |
| 230 | %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')}; |
| 231 | |
| 232 | if (!$OptUpdate) { |
| 233 | ##### installation mode |
| 234 | # check for tables and create them, if they don't exist yet |
| 235 | foreach my $Table (keys %DBCreate) { |
| 236 | &CreateTable($Table); |
| 237 | }; |
| 238 | print "Database table generation done.\n"; |
| 239 | |
| 240 | # Display install instructions |
| 241 | print $Install; |
| 242 | } else { |
| 243 | ##### upgrade mode |
| 244 | print "----------\nStarting upgrade process.\n"; |
| 245 | $PackageVersion = '0.03'; |
| 246 | if ($OptUpdate < $PackageVersion) { |
| 247 | if ($OptUpdate < 0.02) { |
| 248 | # 0.01 -> 0.02 |
| 249 | # &DoMySQL('...;'); |
| 250 | # print "v0.02: Database upgrades ...\n"; |
| 251 | # &PrintInstructions('0.02',<<" INSTRUCTIONS"); |
| 252 | # INSTRUCTIONS |
| 253 | }; |
| 254 | }; |
| 255 | # Display general upgrade instructions |
| 256 | print $Upgrade; |
| 257 | }; |
| 258 | |
| 259 | # close handle |
| 260 | $DBHandle->disconnect; |
| 261 | |
| 262 | exit(0); |
| 263 | |
| 264 | ################################# Subroutines ################################## |
| 265 | |
| 266 | sub CreateTable { |
| 267 | my $Table = shift; |
| 268 | if (defined($TablesInDB{$Conf{$Table}})) { |
| 269 | printf("Database table %s.%s already exists, skipping ....\n", |
| 270 | $Conf{'DBDatabase'},$Conf{$Table}); |
| 271 | return; |
| 272 | }; |
| 273 | my $DBQuery = $DBHandle->prepare($DBCreate{$Table}); |
| 274 | $DBQuery->execute() or |
| 275 | &Bleat(2, sprintf("Can't create table %s in database %s: %s%\n",$Table, |
| 276 | $Conf{'DBDatabase'},$DBI::errstr)); |
| 277 | printf("Database table %s.%s created succesfully.\n", |
| 278 | $Conf{'DBDatabase'},$Conf{$Table}); |
| 279 | return; |
| 280 | }; |
| 281 | |
| 282 | sub DoMySQL { |
| 283 | my $SQL = shift; |
| 284 | my $DBQuery = $DBHandle->prepare($SQL); |
| 285 | $DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr)); |
| 286 | return; |
| 287 | }; |
| 288 | |
| 289 | sub PrintInstructions { |
| 290 | my ($UpVersion,$Instructions) = @_; |
| 291 | print "v$UpVersion: Upgrade Instructions >>>>>\n"; |
| 292 | my $Padding = ' ' x (length($UpVersion) + 3); |
| 293 | $Instructions =~ s/^ /$Padding/mg; |
| 294 | print $Instructions; |
| 295 | print "<" x (length($UpVersion) + 29) . "\n"; |
| 296 | }; |
| 297 | |
| 298 | |
| 299 | __END__ |
| 300 | |
| 301 | ################################ Documentation ################################# |
| 302 | |
| 303 | =head1 NAME |
| 304 | |
| 305 | install - installation script |
| 306 | |
| 307 | =head1 SYNOPSIS |
| 308 | |
| 309 | B<install> [B<-Vh> [--update I<version>] [--conffile I<filename>] |
| 310 | |
| 311 | =head1 REQUIREMENTS |
| 312 | |
| 313 | See L<doc/README>. |
| 314 | |
| 315 | =head1 DESCRIPTION |
| 316 | |
| 317 | This script will create database tables as necessary and configured. |
| 318 | |
| 319 | =head2 Configuration |
| 320 | |
| 321 | B<install> will read its configuration from F<newsstats.conf> via |
| 322 | Config::Auto. |
| 323 | |
| 324 | See L<doc/INSTALL> for an overview of possible configuration options. |
| 325 | |
| 326 | =head1 OPTIONS |
| 327 | |
| 328 | =over 3 |
| 329 | |
| 330 | =item B<-V>, B<--version> |
| 331 | |
| 332 | Print out version and copyright information and exit. |
| 333 | |
| 334 | =item B<-h>, B<--help> |
| 335 | |
| 336 | Print this man page and exit. |
| 337 | |
| 338 | =item B<-u>, B<--update> I<version> |
| 339 | |
| 340 | Don't do a fresh install, but update from I<version>. |
| 341 | |
| 342 | =item B<--conffile> I<filename> |
| 343 | |
| 344 | Load configuration from I<filename> instead of F<newsstats.conf>. |
| 345 | |
| 346 | =back |
| 347 | |
| 348 | =head1 FILES |
| 349 | |
| 350 | =over 4 |
| 351 | |
| 352 | =item F<install/install.pl> |
| 353 | |
| 354 | The script itself. |
| 355 | |
| 356 | =item F<lib/NewsStats.pm> |
| 357 | |
| 358 | Library functions for the NewsStats package. |
| 359 | |
| 360 | =item F<etc/newsstats.conf> |
| 361 | |
| 362 | Runtime configuration file. |
| 363 | |
| 364 | =back |
| 365 | |
| 366 | =head1 BUGS |
| 367 | |
| 368 | Please report any bugs or feature requests to the author or use the |
| 369 | bug tracker at L<http://bugs.th-h.de/>! |
| 370 | |
| 371 | =head1 SEE ALSO |
| 372 | |
| 373 | =over 2 |
| 374 | |
| 375 | =item - |
| 376 | |
| 377 | L<doc/README> |
| 378 | |
| 379 | =item - |
| 380 | |
| 381 | L<doc/INSTALL> |
| 382 | |
| 383 | =back |
| 384 | |
| 385 | This script is part of the B<NewsStats> package. |
| 386 | |
| 387 | =head1 AUTHOR |
| 388 | |
| 389 | Thomas Hochstein <thh@inter.net> |
| 390 | |
| 391 | =head1 COPYRIGHT AND LICENSE |
| 392 | |
| 393 | Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net> |
| 394 | |
| 395 | This program is free software; you may redistribute it and/or modify it |
| 396 | under the same terms as Perl itself. |
| 397 | |
| 398 | =cut |