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, '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 DBTableGrps
84 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
85 `id` bigint(20) unsigned NOT NULL auto_increment,
86 `month` varchar(7) character set ascii NOT NULL,
87 `newsgroup` varchar(100) NOT NULL,
88 `postings` int(11) NOT NULL,
89 `revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
91 UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
92 KEY `newsgroup` (`newsgroup`),
93 KEY `postings` (`postings`)
94 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
97 ##### --------------------------------------------------------------------------
98 ##### Installation / upgrade instructions
99 ##### --------------------------------------------------------------------------
101 my $Install = <<INSTALL;
105 1) Setup an INN feed to feedlog.pl
107 a) Edit your 'newsfeeds' file and insert something like
109 ## gather statistics for NewsStats
112 :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl
116 * check that you got the path to feedlog.pl right
117 * check that feedlog.pl can be executed by the news user
118 * adapt the pattern (here: 'de.*') to your needs
120 b) Check your 'newsfeeds' syntax:
124 and reload 'newsfeeds':
126 # ctlinnd reload newsfeeds 'Adding newsstats! feed'
128 c) Watch your 'news.notice' and 'errlog' files:
130 # tail -f /var/log/news/news.notice
132 # tail -f /var/log/news/errlog
134 2) Watch your $Conf{'DBTableRaw'} table fill.
136 3) Read the documentation. ;)
140 -thh <thh\@inter.net>
145 $Upgrade = <<UPGRADE;
147 Your installation was upgraded from $OptUpdate to $PackageVersion.
149 Don't forget to restart your INN feed so that it can pick up the new version:
151 # ctlinnd begin 'newsstats!'
153 (or whatever you called your feed).
157 ##### --------------------------- End of definitions ---------------------------
159 ### create DB, if necessary
161 print "----------\nStarting database creation.\n";
163 # we can't use InitDB() as that will use a table name of
164 # the table that doesn't exist yet ...
165 my $DBHandle = DBI->connect(sprintf('DBI:%s:host=%s',$Conf{'DBDriver'},
166 $Conf{'DBHost'}), $Conf{'DBUser'},
167 $Conf{'DBPw'}, { PrintError => 0 });
168 my $DBQuery = $DBHandle->prepare($DBCreate);
169 $DBQuery->execute() or &Bleat(2, sprintf("Can't create database %s: %s%\n",
170 $Conf{'DBDatabase'}, $DBI::errstr));
172 printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'});
173 $DBHandle->disconnect;
176 ### DB init, read list of tables
177 print "Reading database information.\n";
178 my $DBHandle = InitDB(\%Conf,1);
180 %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
183 ##### installation mode
184 # check for tables and create them, if they don't exist yet
185 foreach my $Table (keys %DBCreate) {
186 &CreateTable($Table);
188 print "Database table generation done.\n";
190 # Display install instructions
194 print "----------\nStarting upgrade process.\n";
195 $PackageVersion = '0.03';
196 if ($OptUpdate < $PackageVersion) {
197 if ($OptUpdate < 0.02) {
200 # print "v0.02: Database upgrades ...\n";
201 # &PrintInstructions('0.02',<<" INSTRUCTIONS");
205 # Display general upgrade instructions
210 $DBHandle->disconnect;
214 ################################# Subroutines ##################################
218 if (defined($TablesInDB{$Conf{$Table}})) {
219 printf("Database table %s.%s already exists, skipping ....\n",
220 $Conf{'DBDatabase'},$Conf{$Table});
223 my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
224 $DBQuery->execute() or
225 &Bleat(2, sprintf("Can't create table %s in database %s: %s%\n",$Table,
226 $Conf{'DBDatabase'},$DBI::errstr));
227 printf("Database table %s.%s created succesfully.\n",
228 $Conf{'DBDatabase'},$Conf{$Table});
234 my $DBQuery = $DBHandle->prepare($SQL);
235 $DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr));
239 sub PrintInstructions {
240 my ($UpVersion,$Instructions) = @_;
241 print "v$UpVersion: Upgrade Instructions >>>>>\n";
242 my $Padding = ' ' x (length($UpVersion) + 3);
243 $Instructions =~ s/^ /$Padding/mg;
245 print "<" x (length($UpVersion) + 29) . "\n";
251 ################################ Documentation #################################
255 install - installation script
259 B<install> [B<-Vh> [--update I<version>] [--conffile I<filename>]
267 This script will create database tables as necessary and configured.
271 B<install> will read its configuration from F<newsstats.conf> via
274 See L<doc/INSTALL> for an overview of possible configuration options.
280 =item B<-V>, B<--version>
282 Print out version and copyright information and exit.
284 =item B<-h>, B<--help>
286 Print this man page and exit.
288 =item B<-u>, B<--update> I<version>
290 Don't do a fresh install, but update from I<version>.
292 =item B<--conffile> I<filename>
294 Load configuration from I<filename> instead of F<newsstats.conf>.
302 =item F<install/install.pl>
306 =item F<lib/NewsStats.pm>
308 Library functions for the NewsStats package.
310 =item F<etc/newsstats.conf>
312 Runtime configuration file.
318 Please report any bugs or feature requests to the author or use the
319 bug tracker at L<http://bugs.th-h.de/>!
335 This script is part of the B<NewsStats> package.
339 Thomas Hochstein <thh@inter.net>
341 =head1 COPYRIGHT AND LICENSE
343 Copyright (c) 2010-2013 Thomas Hochstein <thh@inter.net>
345 This program is free software; you may redistribute it and/or modify it
346 under the same terms as Perl itself.