5 # This script will create database tables as necessary.
7 # It is part of the NewsStats package.
9 # Copyright (c) 2010-2013 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.02";
17 # we're in .../install, so our module is in ../lib
18 push(@INC, dirname($0).'/../lib');
23 use NewsStats qw(:DEFAULT);
26 use Getopt::Long qw(GetOptions);
27 Getopt::Long::config ('bundling');
29 ################################# Main program #################################
31 ### read commandline options
32 my ($OptUpdate,$OptConfFile);
33 GetOptions ('u|update=s' => \$OptUpdate,
34 'conffile=s' => \$OptConfFile,
35 'h|help' => \&ShowPOD,
36 'V|version' => \&ShowVersion) or exit 1;
38 ### read configuration
39 print("Reading configuration.\n");
40 my %Conf = %{ReadConfig($OptConfFile)};
42 ##### --------------------------------------------------------------------------
43 ##### Database table definitions
44 ##### --------------------------------------------------------------------------
46 my $DBCreate = <<SQLDB;
47 CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8;
50 my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableParse' => <<PARSE, 'DBTableGrps' => <<GRPS);
52 -- Table structure for table DBTableRaw
55 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
56 `id` bigint(20) unsigned NOT NULL auto_increment,
58 `mid` varchar(250) character set ascii NOT NULL,
59 `date` datetime NOT NULL,
60 `timestamp` bigint(20) NOT NULL,
61 `token` varchar(80) character set ascii NOT NULL,
62 `size` bigint(20) NOT NULL,
63 `peer` varchar(250) NOT NULL,
64 `path` varchar(1000) NOT NULL,
65 `newsgroups` varchar(1000) NOT NULL,
66 `headers` longtext NOT NULL,
67 `disregard` tinyint(1) default '0',
72 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data';
75 -- Table structure for table DBTableParse
78 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableParse'}` (
79 `id` bigint(20) unsigned NOT NULL auto_increment,
81 `mid` varchar(250) character set ascii NOT NULL,
82 `refs` varchar(1000) character set ascii,
83 `date` varchar(100) NOT NULL,
84 `path` varchar(1000) NOT NULL,
85 `newsgroups` varchar(1000) NOT NULL,
88 `from_parsed` varchar(200),
89 `from_name` varchar(200),
90 `from_address` varchar(200),
91 `sender` varchar(500),
92 `sender_parsed` varchar(200),
93 `sender_name` varchar(200),
94 `sender_address` varchar(200),
95 `replyto` varchar(500),
96 `replyto_parsed` varchar(200),
97 `replyto_name` varchar(200),
98 `replyto_address` varchar(200),
99 `subject` varchar(1000) NOT NULL,
100 `subject_parsed` varchar(1000),
101 `organization` varchar(1000),
102 `linecount` int(4) unsigned,
103 `approved` varchar(250),
104 `supersedes` varchar(250),
105 `expires` varchar(100),
106 `useragent` varchar(500),
107 `xnewsreader` varchar(500),
108 `xmailer` varchar(500),
109 `xnoarchive` varchar(100),
110 `contenttype` varchar(500),
111 `contentencoding` varchar(500),
112 `cancellock` varchar(500),
113 `injectioninfo` varchar(500),
114 `xtrace` varchar(500),
115 `postinghost` varchar(1000),
117 `disregard` tinyint(1) default '0',
121 KEY `newsgroups` (`newsgroups`)
122 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Parsed data';
125 -- Table structure for table DBTableGrps
128 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
129 `id` bigint(20) unsigned NOT NULL auto_increment,
130 `month` varchar(7) character set ascii NOT NULL,
131 `newsgroup` varchar(100) NOT NULL,
132 `postings` int(11) NOT NULL,
133 `revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
135 UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
136 KEY `newsgroup` (`newsgroup`),
137 KEY `postings` (`postings`)
138 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
141 ##### --------------------------------------------------------------------------
142 ##### Installation / upgrade instructions
143 ##### --------------------------------------------------------------------------
145 my $Install = <<INSTALL;
149 1) Setup an INN feed to feedlog.pl
151 a) Edit your 'newsfeeds' file and insert something like
153 ## gather statistics for NewsStats
156 :Tc,WmtfbsPNH,Ac:$HomePath/bin/feedlog.pl
160 * check that you got the path to feedlog.pl right
161 * check that feedlog.pl can be executed by the news user
162 * adapt the pattern (here: 'de.*') to your needs
164 b) Check your 'newsfeeds' syntax:
168 and reload 'newsfeeds':
170 # ctlinnd reload newsfeeds 'Adding newsstats! feed'
172 c) Watch your 'news.notice' and 'errlog' files:
174 # tail -f /var/log/news/news.notice
176 # tail -f /var/log/news/errlog
178 2) Watch your $Conf{'DBTableRaw'} table fill.
180 3) Read the documentation. ;)
184 -thh <thh\@inter.net>
189 $Upgrade = <<UPGRADE;
191 Your installation was upgraded from $OptUpdate to $PackageVersion.
193 Don't forget to restart your INN feed so that it can pick up the new version:
195 # ctlinnd begin 'newsstats!'
197 (or whatever you called your feed).
201 ##### --------------------------- End of definitions ---------------------------
203 ### create DB, if necessary
205 print "----------\nStarting database creation.\n";
207 # we can't use InitDB() as that will use a table name of
208 # the table that doesn't exist yet ...
209 my $DBHandle = DBI->connect(sprintf('DBI:%s:host=%s',$Conf{'DBDriver'},
210 $Conf{'DBHost'}), $Conf{'DBUser'},
211 $Conf{'DBPw'}, { PrintError => 0 });
212 my $DBQuery = $DBHandle->prepare($DBCreate);
213 $DBQuery->execute() or &Bleat(2, sprintf("Can't create database %s: %s%\n",
214 $Conf{'DBDatabase'}, $DBI::errstr));
216 printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'});
217 $DBHandle->disconnect;
220 ### DB init, read list of tables
221 print "Reading database information.\n";
222 my $DBHandle = InitDB(\%Conf,1);
224 %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
227 ##### installation mode
228 # check for tables and create them, if they don't exist yet
229 foreach my $Table (keys %DBCreate) {
230 &CreateTable($Table);
232 print "Database table generation done.\n";
234 # Display install instructions
238 print "----------\nStarting upgrade process.\n";
239 $PackageVersion = '0.03';
240 if ($OptUpdate < $PackageVersion) {
241 if ($OptUpdate < 0.02) {
244 # print "v0.02: Database upgrades ...\n";
245 # &PrintInstructions('0.02',<<" INSTRUCTIONS");
249 # Display general upgrade instructions
254 $DBHandle->disconnect;
258 ################################# Subroutines ##################################
262 if (defined($TablesInDB{$Conf{$Table}})) {
263 printf("Database table %s.%s already exists, skipping ....\n",
264 $Conf{'DBDatabase'},$Conf{$Table});
267 my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
268 $DBQuery->execute() or
269 &Bleat(2, sprintf("Can't create table %s in database %s: %s%\n",$Table,
270 $Conf{'DBDatabase'},$DBI::errstr));
271 printf("Database table %s.%s created succesfully.\n",
272 $Conf{'DBDatabase'},$Conf{$Table});
278 my $DBQuery = $DBHandle->prepare($SQL);
279 $DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr));
283 sub PrintInstructions {
284 my ($UpVersion,$Instructions) = @_;
285 print "v$UpVersion: Upgrade Instructions >>>>>\n";
286 my $Padding = ' ' x (length($UpVersion) + 3);
287 $Instructions =~ s/^ /$Padding/mg;
289 print "<" x (length($UpVersion) + 29) . "\n";
295 ################################ Documentation #################################
299 install - installation script
303 B<install> [B<-Vh> [--update I<version>] [B<--conffile> I<filename>]
311 This script will create database tables as necessary and configured.
315 B<install> will read its configuration from F<newsstats.conf> which should
316 be present in etc/ via Config::Auto or from a configuration file submitted
317 by the B<--conffile> option.
319 See L<doc/INSTALL> for an overview of possible configuration options.
325 =item B<-V>, B<--version>
327 Print out version and copyright information and exit.
329 =item B<-h>, B<--help>
331 Print this man page and exit.
333 =item B<-u>, B<--update> I<version>
335 Don't do a fresh install, but update from I<version>.
337 =item B<--conffile> I<filename>
339 Load configuration from I<filename> instead of F<newsstats.conf>.
347 =item F<install/install.pl>
351 =item F<lib/NewsStats.pm>
353 Library functions for the NewsStats package.
355 =item F<etc/newsstats.conf>
357 Runtime configuration file.
363 Please report any bugs or feature requests to the author or use the
364 bug tracker at L<http://bugs.th-h.de/>!
380 This script is part of the B<NewsStats> package.
384 Thomas Hochstein <thh@inter.net>
386 =head1 COPYRIGHT AND LICENSE
388 Copyright (c) 2010-2013 Thomas Hochstein <thh@inter.net>
390 This program is free software; you may redistribute it and/or modify it
391 under the same terms as Perl itself.