5 # This script will create database tables as necessary.
7 # It is part of the NewsStats package.
9 # Copyright (c) 2010 Thomas Hochstein <thh@inter.net>
11 # It can be redistributed and/or modified under the same terms under
12 # which Perl itself is published.
15 our $VERSION = "0.01";
17 # we're in .../install, so our module is in ..
18 push(@INC, dirname($0).'/..');
22 use NewsStats qw(:DEFAULT);
28 ################################# Main program #################################
30 ### read commandline options
31 my %Options = &ReadOptions('u:');
33 ### change working directory to .. (as we're in .../install)
34 chdir dirname($0).'/..';
37 ### read configuration
38 print("Reading configuration.\n");
39 my %Conf = %{ReadConfig('newsstats.conf')};
41 ##### --------------------------------------------------------------------------
42 ##### Database table definitions
43 ##### --------------------------------------------------------------------------
45 my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableGrps' => <<GRPS);
47 -- Table structure for table DBTableRaw
50 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
51 `id` bigint(20) unsigned NOT NULL auto_increment,
53 `mid` varchar(250) character set ascii NOT NULL,
54 `date` datetime NOT NULL,
55 `timestamp` bigint(20) NOT NULL,
56 `token` varchar(80) character set ascii NOT NULL,
57 `size` bigint(20) NOT NULL,
58 `peer` varchar(250) NOT NULL,
59 `path` varchar(1000) NOT NULL,
60 `newsgroups` varchar(1000) NOT NULL,
61 `headers` longtext NOT NULL,
62 `disregard` tinyint(1) default '0',
67 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data';
70 -- Table structure for table DBTableGrps
73 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
74 `id` bigint(20) unsigned NOT NULL auto_increment,
75 `month` varchar(7) character set ascii NOT NULL,
76 `newsgroup` varchar(100) NOT NULL,
77 `postings` int(11) NOT NULL,
78 `revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
80 UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
81 KEY `newsgroup` (`newsgroup`),
82 KEY `postings` (`postings`)
83 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
86 ##### --------------------------------------------------------------------------
87 ##### Installation / upgrade instructions
88 ##### --------------------------------------------------------------------------
90 my $Install = <<INSTALL;
94 1) Setup an INN feed to feedlog.pl
96 a) Edit your 'newsfeeds' file and insert something like
98 ## gather statistics for NewsStats
101 :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl
105 * check that you got the path to feedlog.pl right
106 * check that feedlog.pl can be executed by the news user
107 * adapt the pattern (here: 'de.*') to your needs
109 b) Check your 'newsfeeds' syntax:
113 and reload 'newsfeeds':
115 # ctlinnd reload newsfeeds 'Adding newsstats! feed'
117 c) Watch your 'news.notice' and 'errlog' files:
119 # tail -f /var/log/news/news.notice
121 # tail -f /var/log/news/errlog
123 2) Watch your $Conf{'DBTableRaw'} table fill.
125 3) Read the documentation. ;)
129 -thh <thh\@inter.net>
132 my $Upgrade = <<UPGRADE;
134 Your installation was upgraded from $Options{'u'} to $PackageVersion.
136 Don't forget to restart your INN feed so that it can pick up the new version:
138 # ctlinnd begin 'newsstats!'
140 (or whatever you called your feed).
143 ##### --------------------------------------------------------------------------
145 ##### --------------------------------------------------------------------------
146 my (%DBUpgrade,%Instructions);
149 $DBUpgrade{'0.02'} = <<DB0point02;
152 $Instructions{'0.02'} = <<IN0point02;
156 ##### --------------------------- End of definitions ---------------------------
158 ### DB init, read list of tables
159 print "Reading database information.\n";
160 my $DBHandle = InitDB(\%Conf,1);
161 my %TablesInDB = %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
163 if (!$Options{'u'}) {
164 ##### installation mode
165 print "----------\nStarting database table generation.\n";
166 # check for tables and create them, if they don't exist yet
167 foreach my $Table (keys %DBCreate) {
168 &CreateTable($Table);
170 print "Database table generation done.\n";
172 # Display install instructions
176 print "----------\nStarting upgrade process.\n";
177 if ($Options{'u'} < $PackageVersion) {
178 # Database upgrades for each version
179 foreach my $UpVersion (sort keys %DBUpgrade) {
180 if ($UpVersion > $Options{'u'} and $UpVersion <= $PackageVersion) {
181 print "v$UpVersion: Executing database upgrade ...\n";
182 &DoMySQL($DBUpgrade{$UpVersion});
185 # Display upgrade instructions for each version
186 foreach my $UpVersion (sort keys %Instructions) {
187 if ($UpVersion > $Options{'u'} and $UpVersion <= $PackageVersion) {
188 print "v$UpVersion: Upgrade Instructions >>>>>\n";
189 my $Padding = ' ' x (length($UpVersion) + 3);
190 $Instructions{$UpVersion} =~ s/^/$Padding/;
191 print $Instructions{$UpVersion};
192 print "<" x (length($UpVersion) + 29) . "\n";
196 # Display upgrade instructions
201 $DBHandle->disconnect;
205 ################################# Subroutines ##################################
209 if (defined($TablesInDB{$Conf{$Table}})) {
210 printf("Database table %s.%s already exists, skipping ....\n",$Conf{'DBDatabase'},$Conf{$Table});
213 my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
214 $DBQuery->execute() or die sprintf("$MySelf: E: Can't create table %s in database %s: %s%\n",$Table,$Conf{'DBDatabase'},$DBI::errstr);
215 printf("Database table %s.%s created succesfully.\n",$Conf{'DBDatabase'},$Conf{$Table});
221 my $DBQuery = $DBHandle->prepare($SQL);
222 $DBQuery->execute() or warn sprintf("$MySelf: E: Database error: %s\n",$DBI::errstr);
229 ################################ Documentation #################################
233 install - installation script
241 See doc/README: Perl 5.8.x itself and the following modules from CPAN:
257 This script will create database tables as necessary and configured.
261 F<install.pl> will read its configuration from F<newsstats.conf> via
264 See doc/INSTALL for an overview of possible configuration options.
270 =item B<-V> (version)
272 Print out version and copyright information on B<yapfaq> and exit.
276 Print this man page and exit.
288 =item F<NewsStats.pm>
290 Library functions for the NewsStats package.
292 =item F<newsstats.conf>
294 Runtime configuration file for B<yapfaq>.
300 Please report any bugs or feature requests to the author or use the
301 bug tracker at L<http://bugs.th-h.de/>!
317 This script is part of the B<NewsStats> package.
321 Thomas Hochstein <thh@inter.net>
323 =head1 COPYRIGHT AND LICENSE
325 Copyright (c) 2010 Thomas Hochstein <thh@inter.net>
327 This program is free software; you may redistribute it and/or modify it
328 under the same terms as Perl itself.