[usenet/newsstats.git] / install / install.pl

#! /usr/bin/perl -W
#
# install.pl
#
# This script will create database tables as necessary.
# 
# It is part of the NewsStats package.
#
# Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
#
# It can be redistributed and/or modified under the same terms under 
# which Perl itself is published.

BEGIN {
  our $VERSION = "0.01";
  use File::Basename;
  # we're in .../install, so our module is in ..
  push(@INC, dirname($0).'/..');
}
use strict;

use NewsStats qw(:DEFAULT);

use Cwd;

use DBI;
use Getopt::Long qw(GetOptions);
Getopt::Long::config ('bundling');

################################# Main program #################################

### read commandline options
my ($OptUpdate);
GetOptions ('u|update=s' => \$OptUpdate,
            'h|help'     => \&ShowPOD,
            'V|version'  => \&ShowVersion) or exit 1;

### change working directory to .. (as we're in .../install)
chdir dirname($FullPath).'/..';
my $Path = cwd();

### read configuration
print("Reading configuration.\n");
my %Conf = %{ReadConfig($Path.'/newsstats.conf')};

##### --------------------------------------------------------------------------
##### Database table definitions
##### --------------------------------------------------------------------------

my $DBCreate = <<SQLDB;
CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8;
SQLDB

my %DBCreate = ('DBTableRaw'  => <<RAW, 'DBTableGrps' => <<GRPS);
-- 
-- Table structure for table DBTableRaw
-- 

CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `day` date NOT NULL,
  `mid` varchar(250) character set ascii NOT NULL,
  `date` datetime NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  `token` varchar(80) character set ascii NOT NULL,
  `size` bigint(20) NOT NULL,
  `peer` varchar(250) NOT NULL,
  `path` varchar(1000) NOT NULL,
  `newsgroups` varchar(1000) NOT NULL,
  `headers` longtext NOT NULL,
  `disregard` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `day` (`day`),
  KEY `mid` (`mid`),
  KEY `peer` (`peer`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Raw data';
RAW
-- 
-- Table structure for table DBTableGrps
-- 

CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `month` varchar(7) character set ascii NOT NULL,
  `newsgroup` varchar(100) NOT NULL,
  `postings` int(11) NOT NULL,
  `revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
  KEY `newsgroup` (`newsgroup`),
  KEY `postings` (`postings`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
GRPS

##### --------------------------------------------------------------------------
##### Installation / upgrade instructions
##### --------------------------------------------------------------------------

my $Install = <<INSTALL;
----------
Things left to do:

1) Setup an INN feed to feedlog.pl

   a) Edit your 'newsfeeds' file and insert something like

          ## gather statistics for NewsStats
          newsstats!\\
                  :!*,de.*\\
                  :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl

      Please

      * check that you got the path to feedlog.pl right
      * check that feedlog.pl can be executed by the news user
      * adapt the pattern (here: 'de.*') to your needs

   b) Check your 'newsfeeds' syntax:

         # ctlinnd checkfile

      and reload 'newsfeeds':

         # ctlinnd reload newsfeeds 'Adding newsstats! feed'

   c) Watch your 'news.notice' and 'errlog' files:

         # tail -f /var/log/news/news.notice
         ...
         # tail -f /var/log/news/errlog

2) Watch your $Conf{'DBTableRaw'} table fill.

3) Read the documentation. ;)

Enjoy!

-thh <thh\@inter.net>
INSTALL

my $Upgrade ='';
if ($OptUpdate) {
 $Upgrade = <<UPGRADE;
----------
Your installation was upgraded from $OptUpdate to $PackageVersion.

Don't forget to restart your INN feed so that it can pick up the new version:

   # ctlinnd begin 'newsstats!'

(or whatever you called your feed).
UPGRADE
}

##### --------------------------- End of definitions ---------------------------

### create DB, if necessary
if (!$OptUpdate) {
  print "----------\nStarting database creation.\n";
  # create database
  # we can't use InitDB() as that will use a table name of
  # the table that doesn't exist yet ...
  my $DBHandle = DBI->connect(sprintf('DBI:%s:host=%s',$Conf{'DBDriver'},
                                      $Conf{'DBHost'}), $Conf{'DBUser'},
                                      $Conf{'DBPw'}, { PrintError => 0 });
  my $DBQuery = $DBHandle->prepare($DBCreate);
  $DBQuery->execute() or &Bleat(2, sprintf("Can't create database %s: %s%\n",
                                           $Conf{'DBDatabase'}, $DBI::errstr));
  
  printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'});
  $DBHandle->disconnect;
};

### DB init, read list of tables
print "Reading database information.\n";
my $DBHandle = InitDB(\%Conf,1);
my %TablesInDB =
   %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};

if (!$OptUpdate) {
  ##### installation mode
  # check for tables and create them, if they don't exist yet
  foreach my $Table (keys %DBCreate) {
    &CreateTable($Table);
  };
  print "Database table generation done.\n";
 
  # Display install instructions
  print $Install;
} else {
  ##### upgrade mode
  print "----------\nStarting upgrade process.\n";
  $PackageVersion = '0.03';
  if ($OptUpdate < $PackageVersion) {
    if ($OptUpdate < 0.02) {
      # 0.01 -> 0.02
      # &DoMySQL('...;');
      # print "v0.02: Database upgrades ...\n";
      # &PrintInstructions('0.02',<<"      INSTRUCTIONS");
      # INSTRUCTIONS
    };
  };
  # Display general upgrade instructions
  print $Upgrade;
};

# close handle
$DBHandle->disconnect;

exit(0);

################################# Subroutines ##################################

sub CreateTable {
  my $Table = shift;
  if (defined($TablesInDB{$Conf{$Table}})) {
    printf("Database table %s.%s already exists, skipping ....\n",
           $Conf{'DBDatabase'},$Conf{$Table});
    return;
  };
  my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
  $DBQuery->execute() or
    &Bleat(2, sprintf("Can't create table %s in database %s: %s%\n",$Table,
                      $Conf{'DBDatabase'},$DBI::errstr));
  printf("Database table %s.%s created succesfully.\n",
         $Conf{'DBDatabase'},$Conf{$Table});
  return;
};

sub DoMySQL {
  my $SQL = shift;
  my $DBQuery = $DBHandle->prepare($SQL);
  $DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr));
  return;
};

sub PrintInstructions {
  my ($UpVersion,$Instructions) = @_;
  print "v$UpVersion: Upgrade Instructions >>>>>\n";
  my $Padding = ' ' x (length($UpVersion) + 3);
    $Instructions =~ s/^      /$Padding/mg;
    print $Instructions;
    print "<" x (length($UpVersion) + 29) . "\n";
};


__END__

################################ Documentation #################################

=head1 NAME

install - installation script

=head1 SYNOPSIS

B<install> [B<-Vh> [--update I<version>]

=head1 REQUIREMENTS

See L<doc/README>.

=head1 DESCRIPTION

This script will create database tables as necessary and configured.

=head2 Configuration

B<install> will read its configuration from F<newsstats.conf> via
Config::Auto.

See L<doc/INSTALL> for an overview of possible configuration options.

=head1 OPTIONS

=over 3

=item B<-V>, B<--version>

Print out version and copyright information and exit.

=item B<-h>, B<--help>

Print this man page and exit.

=item B<-u>, B<--update> I<version>

Don't do a fresh install, but update from I<version>.

=back

=head1 FILES

=over 4

=item F<install.pl>

The script itself.

=item F<NewsStats.pm>

Library functions for the NewsStats package.

=item F<newsstats.conf>

Runtime configuration file.

=back

=head1 BUGS

Please report any bugs or feature requests to the author or use the
bug tracker at L<http://bugs.th-h.de/>!

=head1 SEE ALSO

=over 2

=item -

L<doc/README>

=item -

L<doc/INSTALL>

=back

This script is part of the B<NewsStats> package.

=head1 AUTHOR

Thomas Hochstein <thh@inter.net>

=head1 COPYRIGHT AND LICENSE

Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>

This program is free software; you may redistribute it and/or modify it
under the same terms as Perl itself.

=cut
Commit	Line	Data
2832c235 TH	1	#! /usr/bin/perl -W
	2	#
	3	# install.pl
	4	#
	5	# This script will create database tables as necessary.
	6	#
	7	# It is part of the NewsStats package.
	8	#
1fa94799	9	# Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
2832c235 TH	10	#
	11	# It can be redistributed and/or modified under the same terms under
	12	# which Perl itself is published.
	13
	14	BEGIN {
	15	our $VERSION = "0.01";
	16	use File::Basename;
	17	# we're in .../install, so our module is in ..
	18	push(@INC, dirname($0).'/..');
	19	}
	20	use strict;
	21
	22	use NewsStats qw(:DEFAULT);
	23
	24	use Cwd;
	25
	26	use DBI;
1fa94799 TH	27	use Getopt::Long qw(GetOptions);
1fa94799 TH	28	Getopt::Long::config ('bundling');
2832c235 TH	29
	30	################################# Main program #################################
	31
	32	### read commandline options
1fa94799 TH	33	my ($OptUpdate);
	34	GetOptions ('u\|update=s' => \$OptUpdate,
	35	'h\|help' => \&ShowPOD,
	36	'V\|version' => \&ShowVersion) or exit 1;
2832c235 TH	37
2832c235 TH	38	### change working directory to .. (as we're in .../install)
512781bd	39	chdir dirname($FullPath).'/..';
f6d15ca7	40	my $Path = cwd();
2832c235 TH	41
	42	### read configuration
	43	print("Reading configuration.\n");
512781bd	44	my %Conf = %{ReadConfig($Path.'/newsstats.conf')};
2832c235 TH	45
	46	##### --------------------------------------------------------------------------
	47	##### Database table definitions
	48	##### --------------------------------------------------------------------------
	49
36cffe7a TH	50	my $DBCreate = <<SQLDB;
	51	CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8;
	52	SQLDB
	53
2832c235 TH	54	my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableGrps' => <<GRPS);
	55	--
	56	-- Table structure for table DBTableRaw
	57	--
	58
	59	CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
	60	`id` bigint(20) unsigned NOT NULL auto_increment,
	61	`day` date NOT NULL,
	62	`mid` varchar(250) character set ascii NOT NULL,
	63	`date` datetime NOT NULL,
	64	`timestamp` bigint(20) NOT NULL,
	65	`token` varchar(80) character set ascii NOT NULL,
	66	`size` bigint(20) NOT NULL,
	67	`peer` varchar(250) NOT NULL,
	68	`path` varchar(1000) NOT NULL,
	69	`newsgroups` varchar(1000) NOT NULL,
	70	`headers` longtext NOT NULL,
	71	`disregard` tinyint(1) default '0',
	72	PRIMARY KEY (`id`),
	73	KEY `day` (`day`),
	74	KEY `mid` (`mid`),
	75	KEY `peer` (`peer`)
	76	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data';
	77	RAW
	78	--
	79	-- Table structure for table DBTableGrps
	80	--
	81
	82	CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
	83	`id` bigint(20) unsigned NOT NULL auto_increment,
	84	`month` varchar(7) character set ascii NOT NULL,
	85	`newsgroup` varchar(100) NOT NULL,
	86	`postings` int(11) NOT NULL,
	87	`revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	88	PRIMARY KEY (`id`),
	89	UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
	90	KEY `newsgroup` (`newsgroup`),
	91	KEY `postings` (`postings`)
	92	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
	93	GRPS
	94
f6d15ca7 TH	95	##### --------------------------------------------------------------------------
	96	##### Installation / upgrade instructions
	97	##### --------------------------------------------------------------------------
2832c235	98
f6d15ca7 TH	99	my $Install = <<INSTALL;
f6d15ca7 TH	100	----------
2832c235 TH	101	Things left to do:
	102
	103	1) Setup an INN feed to feedlog.pl
	104
	105	a) Edit your 'newsfeeds' file and insert something like
	106
	107	## gather statistics for NewsStats
9c141b0c TH	108	newsstats!\\
9c141b0c TH	109	:!,de.\\
2832c235 TH	110	:Tc,WmtfbsPNH,Ac:$Path/feedlog.pl
	111
	112	Please
	113
	114	* check that you got the path to feedlog.pl right
	115	* check that feedlog.pl can be executed by the news user
	116	* adapt the pattern (here: 'de.*') to your needs
	117
	118	b) Check your 'newsfeeds' syntax:
	119
	120	# ctlinnd checkfile
	121
	122	and reload 'newsfeeds':
	123
	124	# ctlinnd reload newsfeeds 'Adding newsstats! feed'
	125
	126	c) Watch your 'news.notice' and 'errlog' files:
	127
	128	# tail -f /var/log/news/news.notice
	129	...
	130	# tail -f /var/log/news/errlog
	131
	132	2) Watch your $Conf{'DBTableRaw'} table fill.
	133
	134	3) Read the documentation. ;)
	135
	136	Enjoy!
	137
	138	-thh <thh\@inter.net>
f6d15ca7 TH	139	INSTALL
f6d15ca7 TH	140
da6fc073 TH	141	my $Upgrade ='';
	142	if ($OptUpdate) {
	143	$Upgrade = <<UPGRADE;
f6d15ca7	144	----------
1fa94799	145	Your installation was upgraded from $OptUpdate to $PackageVersion.
f6d15ca7 TH	146
	147	Don't forget to restart your INN feed so that it can pick up the new version:
	148
	149	# ctlinnd begin 'newsstats!'
	150
	151	(or whatever you called your feed).
	152	UPGRADE
da6fc073	153	}
f6d15ca7	154
f6d15ca7 TH	155	##### --------------------------- End of definitions ---------------------------
f6d15ca7 TH	156
36cffe7a TH	157	### create DB, if necessary
	158	if (!$OptUpdate) {
	159	print "----------\nStarting database creation.\n";
	160	# create database
	161	# we can't use InitDB() as that will use a table name of
	162	# the table that doesn't exist yet ...
	163	my $DBHandle = DBI->connect(sprintf('DBI:%s:host=%s',$Conf{'DBDriver'},
	164	$Conf{'DBHost'}), $Conf{'DBUser'},
	165	$Conf{'DBPw'}, { PrintError => 0 });
	166	my $DBQuery = $DBHandle->prepare($DBCreate);
	167	$DBQuery->execute() or &Bleat(2, sprintf("Can't create database %s: %s%\n",
	168	$Conf{'DBDatabase'}, $DBI::errstr));
	169
	170	printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'});
	171	$DBHandle->disconnect;
	172	};
	173
f6d15ca7 TH	174	### DB init, read list of tables
	175	print "Reading database information.\n";
	176	my $DBHandle = InitDB(\%Conf,1);
1fa94799 TH	177	my %TablesInDB =
1fa94799 TH	178	%{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
f6d15ca7	179
1fa94799	180	if (!$OptUpdate) {
f6d15ca7	181	##### installation mode
f6d15ca7 TH	182	# check for tables and create them, if they don't exist yet
	183	foreach my $Table (keys %DBCreate) {
	184	&CreateTable($Table);
	185	};
	186	print "Database table generation done.\n";
	187
	188	# Display install instructions
	189	print $Install;
	190	} else {
	191	##### upgrade mode
	192	print "----------\nStarting upgrade process.\n";
59d0c5ef	193	$PackageVersion = '0.03';
1fa94799 TH	194	if ($OptUpdate < $PackageVersion) {
1fa94799 TH	195	if ($OptUpdate < 0.02) {
59d0c5ef TH	196	# 0.01 -> 0.02
	197	# &DoMySQL('...;');
	198	# print "v0.02: Database upgrades ...\n";
	199	# &PrintInstructions('0.02',<<" INSTRUCTIONS");
	200	# INSTRUCTIONS
f6d15ca7 TH	201	};
f6d15ca7 TH	202	};
59d0c5ef	203	# Display general upgrade instructions
f6d15ca7 TH	204	print $Upgrade;
	205	};
	206
	207	# close handle
	208	$DBHandle->disconnect;
	209
	210	exit(0);
	211
	212	################################# Subroutines ##################################
	213
59d0c5ef	214	sub CreateTable {
f6d15ca7 TH	215	my $Table = shift;
f6d15ca7 TH	216	if (defined($TablesInDB{$Conf{$Table}})) {
1fa94799 TH	217	printf("Database table %s.%s already exists, skipping ....\n",
1fa94799 TH	218	$Conf{'DBDatabase'},$Conf{$Table});
f6d15ca7 TH	219	return;
	220	};
	221	my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
1fa94799 TH	222	$DBQuery->execute() or
	223	&Bleat(2, sprintf("Can't create table %s in database %s: %s%\n",$Table,
	224	$Conf{'DBDatabase'},$DBI::errstr));
	225	printf("Database table %s.%s created succesfully.\n",
	226	$Conf{'DBDatabase'},$Conf{$Table});
f6d15ca7 TH	227	return;
	228	};
	229
59d0c5ef	230	sub DoMySQL {
f6d15ca7 TH	231	my $SQL = shift;
f6d15ca7 TH	232	my $DBQuery = $DBHandle->prepare($SQL);
1fa94799	233	$DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr));
f6d15ca7 TH	234	return;
	235	};
	236
59d0c5ef TH	237	sub PrintInstructions {
	238	my ($UpVersion,$Instructions) = @_;
	239	print "v$UpVersion: Upgrade Instructions >>>>>\n";
	240	my $Padding = ' ' x (length($UpVersion) + 3);
	241	$Instructions =~ s/^ /$Padding/mg;
	242	print $Instructions;
	243	print "<" x (length($UpVersion) + 29) . "\n";
	244	};
	245
2832c235 TH	246
	247	__END__
	248
	249	################################ Documentation #################################
	250
	251	=head1 NAME
	252
	253	install - installation script
	254
	255	=head1 SYNOPSIS
	256
1fa94799	257	B<install> [B<-Vh> [--update I<version>]
2832c235 TH	258
	259	=head1 REQUIREMENTS
	260
1fa94799	261	See L<doc/README>.
2832c235 TH	262
	263	=head1 DESCRIPTION
	264
	265	This script will create database tables as necessary and configured.
	266
	267	=head2 Configuration
	268
1fa94799	269	B<install> will read its configuration from F<newsstats.conf> via
2832c235 TH	270	Config::Auto.
2832c235 TH	271
1fa94799	272	See L<doc/INSTALL> for an overview of possible configuration options.
2832c235 TH	273
	274	=head1 OPTIONS
	275
	276	=over 3
	277
1fa94799	278	=item B<-V>, B<--version>
2832c235	279
1fa94799	280	Print out version and copyright information and exit.
2832c235	281
1fa94799	282	=item B<-h>, B<--help>
2832c235 TH	283
	284	Print this man page and exit.
	285
1fa94799 TH	286	=item B<-u>, B<--update> I<version>
	287
	288	Don't do a fresh install, but update from I<version>.
	289
2832c235 TH	290	=back
	291
	292	=head1 FILES
	293
	294	=over 4
	295
	296	=item F<install.pl>
	297
	298	The script itself.
	299
	300	=item F<NewsStats.pm>
	301
	302	Library functions for the NewsStats package.
	303
	304	=item F<newsstats.conf>
	305
1fa94799	306	Runtime configuration file.
2832c235 TH	307
	308	=back
	309
	310	=head1 BUGS
	311
	312	Please report any bugs or feature requests to the author or use the
	313	bug tracker at L<http://bugs.th-h.de/>!
	314
	315	=head1 SEE ALSO
	316
	317	=over 2
	318
	319	=item -
	320
1fa94799	321	L<doc/README>
2832c235 TH	322
	323	=item -
	324
1fa94799	325	L<doc/INSTALL>
2832c235 TH	326
	327	=back
	328
	329	This script is part of the B<NewsStats> package.
	330
	331	=head1 AUTHOR
	332
	333	Thomas Hochstein <thh@inter.net>
	334
	335	=head1 COPYRIGHT AND LICENSE
	336
1fa94799	337	Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
2832c235 TH	338
	339	This program is free software; you may redistribute it and/or modify it
	340	under the same terms as Perl itself.
	341
	342	=cut