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

#! /usr/bin/perl
#
# 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 warnings;

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