Create a database table with parsed raw data.
[usenet/newsstats.git] / install / install.pl
... / ...
CommitLineData
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
14BEGIN {
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}
20use strict;
21use warnings;
22
23use NewsStats qw(:DEFAULT);
24
25use Cwd;
26
27use DBI;
28use Getopt::Long qw(GetOptions);
29Getopt::Long::config ('bundling');
30
31################################# Main program #################################
32
33### read commandline options
34my ($OptUpdate,$OptConfFile);
35GetOptions ('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)
41chdir dirname($FullPath).'/..';
42my $Path = cwd();
43
44### read configuration
45print("Reading configuration.\n");
46my %Conf = %{ReadConfig($OptConfFile)};
47
48##### --------------------------------------------------------------------------
49##### Database table definitions
50##### --------------------------------------------------------------------------
51
52my $DBCreate = <<SQLDB;
53CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8;
54SQLDB
55
56my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableParse' => <<PARSE, 'DBTableGrps' => <<GRPS);
57--
58-- Table structure for table DBTableRaw
59--
60
61CREATE 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';
79RAW
80--
81-- Table structure for table DBTableParse
82--
83
84CREATE 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';
129PARSE
130--
131-- Table structure for table DBTableGrps
132--
133
134CREATE 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';
145GRPS
146
147##### --------------------------------------------------------------------------
148##### Installation / upgrade instructions
149##### --------------------------------------------------------------------------
150
151my $Install = <<INSTALL;
152----------
153Things left to do:
154
1551) 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
1842) Watch your $Conf{'DBTableRaw'} table fill.
185
1863) Read the documentation. ;)
187
188Enjoy!
189
190-thh <thh\@inter.net>
191INSTALL
192
193my $Upgrade ='';
194if ($OptUpdate) {
195 $Upgrade = <<UPGRADE;
196----------
197Your installation was upgraded from $OptUpdate to $PackageVersion.
198
199Don'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).
204UPGRADE
205}
206
207##### --------------------------- End of definitions ---------------------------
208
209### create DB, if necessary
210if (!$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
227print "Reading database information.\n";
228my $DBHandle = InitDB(\%Conf,1);
229my %TablesInDB =
230 %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
231
232if (!$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
262exit(0);
263
264################################# Subroutines ##################################
265
266sub 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
282sub 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
289sub 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
305install - installation script
306
307=head1 SYNOPSIS
308
309B<install> [B<-Vh> [--update I<version>] [--conffile I<filename>]
310
311=head1 REQUIREMENTS
312
313See L<doc/README>.
314
315=head1 DESCRIPTION
316
317This script will create database tables as necessary and configured.
318
319=head2 Configuration
320
321B<install> will read its configuration from F<newsstats.conf> via
322Config::Auto.
323
324See 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
332Print out version and copyright information and exit.
333
334=item B<-h>, B<--help>
335
336Print this man page and exit.
337
338=item B<-u>, B<--update> I<version>
339
340Don't do a fresh install, but update from I<version>.
341
342=item B<--conffile> I<filename>
343
344Load 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
354The script itself.
355
356=item F<lib/NewsStats.pm>
357
358Library functions for the NewsStats package.
359
360=item F<etc/newsstats.conf>
361
362Runtime configuration file.
363
364=back
365
366=head1 BUGS
367
368Please report any bugs or feature requests to the author or use the
369bug tracker at L<http://bugs.th-h.de/>!
370
371=head1 SEE ALSO
372
373=over 2
374
375=item -
376
377L<doc/README>
378
379=item -
380
381L<doc/INSTALL>
382
383=back
384
385This script is part of the B<NewsStats> package.
386
387=head1 AUTHOR
388
389Thomas Hochstein <thh@inter.net>
390
391=head1 COPYRIGHT AND LICENSE
392
393Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
394
395This program is free software; you may redistribute it and/or modify it
396under the same terms as Perl itself.
397
398=cut
This page took 0.011198 seconds and 4 git commands to generate.