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 ##### --------------------------- End of definitions ---------------------------
145 ### DB init, read list of tables
146 print "Reading database information.\n";
147 my $DBHandle = InitDB(\%Conf,1);
148 my %TablesInDB = %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
150 if (!$Options{'u'}) {
151 ##### installation mode
152 print "----------\nStarting database table generation.\n";
153 # check for tables and create them, if they don't exist yet
154 foreach my $Table (keys %DBCreate) {
155 &CreateTable($Table);
157 print "Database table generation done.\n";
159 # Display install instructions
163 print "----------\nStarting upgrade process.\n";
164 $PackageVersion = '0.03';
165 if ($Options{'u'} < $PackageVersion) {
166 if ($Options{'u'} < 0.02) {
169 # print "v0.02: Database upgrades ...\n";
170 # &PrintInstructions('0.02',<<" INSTRUCTIONS");
174 # Display general upgrade instructions
179 $DBHandle->disconnect;
183 ################################# Subroutines ##################################
187 if (defined($TablesInDB{$Conf{$Table}})) {
188 printf("Database table %s.%s already exists, skipping ....\n",$Conf{'DBDatabase'},$Conf{$Table});
191 my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
192 $DBQuery->execute() or die sprintf("$MySelf: E: Can't create table %s in database %s: %s%\n",$Table,$Conf{'DBDatabase'},$DBI::errstr);
193 printf("Database table %s.%s created succesfully.\n",$Conf{'DBDatabase'},$Conf{$Table});
199 my $DBQuery = $DBHandle->prepare($SQL);
200 $DBQuery->execute() or warn sprintf("$MySelf: E: Database error: %s\n",$DBI::errstr);
204 sub PrintInstructions {
205 my ($UpVersion,$Instructions) = @_;
206 print "v$UpVersion: Upgrade Instructions >>>>>\n";
207 my $Padding = ' ' x (length($UpVersion) + 3);
208 $Instructions =~ s/^ /$Padding/mg;
210 print "<" x (length($UpVersion) + 29) . "\n";
216 ################################ Documentation #################################
220 install - installation script
228 See doc/README: Perl 5.8.x itself and the following modules from CPAN:
244 This script will create database tables as necessary and configured.
248 F<install.pl> will read its configuration from F<newsstats.conf> via
251 See doc/INSTALL for an overview of possible configuration options.
257 =item B<-V> (version)
259 Print out version and copyright information on B<yapfaq> and exit.
263 Print this man page and exit.
275 =item F<NewsStats.pm>
277 Library functions for the NewsStats package.
279 =item F<newsstats.conf>
281 Runtime configuration file for B<yapfaq>.
287 Please report any bugs or feature requests to the author or use the
288 bug tracker at L<http://bugs.th-h.de/>!
304 This script is part of the B<NewsStats> package.
308 Thomas Hochstein <thh@inter.net>
310 =head1 COPYRIGHT AND LICENSE
312 Copyright (c) 2010 Thomas Hochstein <thh@inter.net>
314 This program is free software; you may redistribute it and/or modify it
315 under the same terms as Perl itself.