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.01";
17 # we're in .../install, so our module is in ../lib
18 push(@INC, dirname($0).'/../lib');
23 use NewsStats qw(:DEFAULT);
28 use Getopt::Long qw(GetOptions);
29 Getopt::Long::config ('bundling');
31 ################################# Main program #################################
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;
40 ### change working directory to .. (as we're in .../install)
41 chdir dirname($FullPath).'/..';
44 ### read configuration
45 print("Reading configuration.\n");
46 my %Conf = %{ReadConfig($OptConfFile)};
48 ##### --------------------------------------------------------------------------
49 ##### Database table definitions
50 ##### --------------------------------------------------------------------------
52 my $DBCreate = <<SQLDB;
53 CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8;
56 my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableParse' => <<PARSE, 'DBTableGrps' => <<GRPS);
58 -- Table structure for table DBTableRaw
61 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
62 `id` bigint(20) unsigned NOT NULL auto_increment,
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',
78 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data';
81 -- Table structure for table DBTableParse
84 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableParse'}` (
85 `id` bigint(20) unsigned NOT NULL auto_increment,
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,
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),
123 `disregard` tinyint(1) default '0',
127 KEY `newsgroups` (`newsgroups`)
128 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Parsed data';
131 -- Table structure for table DBTableGrps
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,
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';
147 ##### --------------------------------------------------------------------------
148 ##### Installation / upgrade instructions
149 ##### --------------------------------------------------------------------------
151 my $Install = <<INSTALL;
155 1) Setup an INN feed to feedlog.pl
157 a) Edit your 'newsfeeds' file and insert something like
159 ## gather statistics for NewsStats
162 :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl
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
170 b) Check your 'newsfeeds' syntax:
174 and reload 'newsfeeds':
176 # ctlinnd reload newsfeeds 'Adding newsstats! feed'
178 c) Watch your 'news.notice' and 'errlog' files:
180 # tail -f /var/log/news/news.notice
182 # tail -f /var/log/news/errlog
184 2) Watch your $Conf{'DBTableRaw'} table fill.
186 3) Read the documentation. ;)
190 -thh <thh\@inter.net>
195 $Upgrade = <<UPGRADE;
197 Your installation was upgraded from $OptUpdate to $PackageVersion.
199 Don't forget to restart your INN feed so that it can pick up the new version:
201 # ctlinnd begin 'newsstats!'
203 (or whatever you called your feed).
207 ##### --------------------------- End of definitions ---------------------------
209 ### create DB, if necessary
211 print "----------\nStarting database creation.\n";
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));
222 printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'});
223 $DBHandle->disconnect;
226 ### DB init, read list of tables
227 print "Reading database information.\n";
228 my $DBHandle = InitDB(\%Conf,1);
230 %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
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);
238 print "Database table generation done.\n";
240 # Display install instructions
244 print "----------\nStarting upgrade process.\n";
245 $PackageVersion = '0.03';
246 if ($OptUpdate < $PackageVersion) {
247 if ($OptUpdate < 0.02) {
250 # print "v0.02: Database upgrades ...\n";
251 # &PrintInstructions('0.02',<<" INSTRUCTIONS");
255 # Display general upgrade instructions
260 $DBHandle->disconnect;
264 ################################# Subroutines ##################################
268 if (defined($TablesInDB{$Conf{$Table}})) {
269 printf("Database table %s.%s already exists, skipping ....\n",
270 $Conf{'DBDatabase'},$Conf{$Table});
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});
284 my $DBQuery = $DBHandle->prepare($SQL);
285 $DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr));
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;
295 print "<" x (length($UpVersion) + 29) . "\n";
301 ################################ Documentation #################################
305 install - installation script
309 B<install> [B<-Vh> [--update I<version>] [--conffile I<filename>]
317 This script will create database tables as necessary and configured.
321 B<install> will read its configuration from F<newsstats.conf> via
324 See L<doc/INSTALL> for an overview of possible configuration options.
330 =item B<-V>, B<--version>
332 Print out version and copyright information and exit.
334 =item B<-h>, B<--help>
336 Print this man page and exit.
338 =item B<-u>, B<--update> I<version>
340 Don't do a fresh install, but update from I<version>.
342 =item B<--conffile> I<filename>
344 Load configuration from I<filename> instead of F<newsstats.conf>.
352 =item F<install/install.pl>
356 =item F<lib/NewsStats.pm>
358 Library functions for the NewsStats package.
360 =item F<etc/newsstats.conf>
362 Runtime configuration file.
368 Please report any bugs or feature requests to the author or use the
369 bug tracker at L<http://bugs.th-h.de/>!
385 This script is part of the B<NewsStats> package.
389 Thomas Hochstein <thh@inter.net>
391 =head1 COPYRIGHT AND LICENSE
393 Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
395 This program is free software; you may redistribute it and/or modify it
396 under the same terms as Perl itself.